-
Notifications
You must be signed in to change notification settings - Fork 7
/
schema.sql
143 lines (121 loc) · 3.32 KB
/
schema.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
create table homestay
(
id bigint generated always as identity primary key,
name text not null,
description text,
type integer,
host_id bigint,
status integer,
phone_number text,
address text,
longitude double precision,
latitude double precision,
geom geometry(Point, 3857),
images text[],
guests smallint,
bedrooms smallint,
bathrooms smallint,
extra_data jsonb,
version bigint,
created_at timestamp with time zone,
created_by bigint,
updated_at timestamp with time zone,
updated_by bigint
);
create table "user"
(
id bigint generated always as identity primary key,
username text not null,
password text not null,
email text not null,
fullname text,
type smallint not null,
status smallint not null,
extra_data jsonb,
version bigint,
created_at timestamp with time zone,
created_by bigint,
updated_at timestamp with time zone,
updated_by bigint
);
create table profile
(
user_id bigint not null,
avatar text,
work text,
about text,
interests text[],
status smallint,
extra_data jsonb,
version bigint,
created_at timestamp with time zone,
created_by bigint,
updated_at timestamp with time zone,
updated_by bigint
);
create table booking
(
id bigint generated always as identity primary key,
user_id bigint not null,
homestay_id bigint not null,
checkin_date date not null,
checkout_date date not null,
guests smallint not null,
status smallint not null,
subtotal numeric(12, 6),
fee numeric(12, 6),
discount numeric(12, 6),
total_amount numeric not null,
price_detail jsonb,
currency text not null,
note text,
request_id text not null,
version smallint,
extra_data jsonb,
created_at timestamp with time zone,
created_by bigint,
updated_at timestamp with time zone,
updated_by bigint
);
create table homestay_availability
(
homestay_id bigint not null,
date date not null,
price numeric,
status smallint,
primary key (homestay_id, date)
);
create table amenity
(
id integer generated always as identity primary key,
name text not null,
icon text not null
);
create table homestay_amenity
(
homestay_id bigint not null constraint homestay_amenity_homestay_id_fk references homestay,
amenity_id integer not null constraint homestay_amenity_amenity_id_fk references amenity
);
create table ward
(
id integer generated always as identity primary key,
ward_name text not null,
place_id text,
district_id integer
);
create table district
(
id integer generated always as identity primary key,
district_name text not null,
place_id text,
province_id integer
);
create table province
(
id integer generated always as identity primary key,
province_name text not null,
place_id text,
country_id integer
);
CREATE EXTENSION postgis;
create index idx_homestay_geom on homestay using gist (geom);