-
Notifications
You must be signed in to change notification settings - Fork 1
/
query.sql
133 lines (108 loc) · 7.12 KB
/
query.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
/*-------------| AUTHENTICATION TOKEN RELATION |-------------*/
CREATE TABLE square.public.TOKENS (
ID SERIAL PRIMARY KEY,
TOKEN VARCHAR(255) DEFAULT NULL,
CREATED_ON TIMESTAMP DEFAULT NULL
);
/*-------------| USER RELATION |-------------*/
CREATE TABLE square.public.USERS (
ID SERIAL PRIMARY KEY,
USERNAME VARCHAR(255) UNIQUE NOT NULL,
PASSWORD VARCHAR(255) NOT NULL,
EMAIL VARCHAR(255) UNIQUE NOT NULL,
STATUS BOOLEAN DEFAULT FALSE
);
/*-------------| ROLE RELATION |-------------*/
CREATE TABLE square.public.ROLES (
ID SERIAL PRIMARY KEY,
ROLE VARCHAR(15) NOT NULL
);
/*-------------| USERS ROLES RELATION |-------------*/
CREATE TABLE square.public.USERS_ROLES (
USER_ID INTEGER,
ROLE_ID INTEGER,
CONSTRAINT USERS_ROLES_USERS_FK FOREIGN KEY (USER_ID) REFERENCES square.public.USERS(ID)
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT USERS_ROLES_ROLES_FK FOREIGN KEY (ROLE_ID) REFERENCES square.public.ROLES(ID)
ON UPDATE CASCADE ON DELETE CASCADE
);
/*-------------| HOUSES RELATION |-------------*/
CREATE TABLE square.public.HOUSES (
ID SERIAL PRIMARY KEY,
HOST_ID INTEGER,
TITLE VARCHAR(255) NOT NULL,
DESCRIPTION TEXT NOT NULL,
PRICE REAL,
LOCATION VARCHAR(255) NOT NULL,
LATITUDE REAL,
LONGITUDE REAL,
STATUS BOOLEAN DEFAULT FALSE,
REJECTION BOOLEAN DEFAULT FALSE,
CONSTRAINT HOUSES_HOSTS_FK FOREIGN KEY (HOST_ID) REFERENCES square.public.USERS(ID)
ON UPDATE CASCADE ON DELETE CASCADE
);
/*-------------| BOOKINGS RELATION |-------------*/
CREATE TABLE square.public.BOOKINGS (
ID SERIAL PRIMARY KEY,
HOUSE_ID INTEGER,
CUSTOMER_ID INTEGER,
CHECK_IN DATE NOT NULL,
CHECK_OUT DATE NOT NULL,
STATUS BOOLEAN DEFAULT FALSE,
REJECTION BOOLEAN DEFAULT FALSE,
CANCEL BOOLEAN DEFAULT FALSE,
CONSTRAINT BOOKINGS_HOUSES_FK FOREIGN KEY (HOUSE_ID) REFERENCES square.public.HOUSES(ID)
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT BOOKINGS_CUSTOMERS_FK FOREIGN KEY (CUSTOMER_ID) REFERENCES square.public.USERS(ID)
ON UPDATE CASCADE ON DELETE CASCADE
);
/*-------------| CUSTOMER REVIEWS RELATION |-------------*/
CREATE TABLE square.public.REVIEWS (
ID SERIAL PRIMARY KEY,
BOOKING_ID INTEGER,
REVIEWER_ID INTEGER,
REVIEW TEXT,
STARS INTEGER NOT NULL,
CONSTRAINT REVIEWS_BOOKINGS_FK FOREIGN KEY (BOOKING_ID) REFERENCES square.public.BOOKINGS(ID)
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT REVIEWS_REVIEWERS_FK FOREIGN KEY (REVIEWER_ID) REFERENCES square.public.USERS(ID)
ON UPDATE CASCADE ON DELETE CASCADE
);
INSERT INTO square.public.USERS (USERNAME, PASSWORD, EMAIL, STATUS) VALUES ('admin','$2a$10$stY59P7EmX7HNbUKiIVBXueYq6Gcc7/rVbUM3wChykyLcBudoUkdm', '[email protected]', true);
INSERT INTO square.public.USERS (USERNAME, PASSWORD, EMAIL, STATUS) VALUES ('rafsan','$2a$10$V2y9AJRW/dLoiaENmCliiOMT0OwSVQsFSplViK44zcBqsqyO90CoW', '[email protected]', true);
INSERT INTO square.public.USERS (USERNAME, PASSWORD, EMAIL, STATUS) VALUES ('jeff','$2a$10$V2y9AJRW/dLoiaENmCliiOMT0OwSVQsFSplViK44zcBqsqyO90CoW', '[email protected]', true);
INSERT INTO square.public.USERS (USERNAME, PASSWORD, EMAIL, STATUS) VALUES ('indy','$2a$10$V2y9AJRW/dLoiaENmCliiOMT0OwSVQsFSplViK44zcBqsqyO90CoW', '[email protected]', true);
INSERT INTO square.public.USERS (USERNAME, PASSWORD, EMAIL, STATUS) VALUES ('peter','$2a$10$V2y9AJRW/dLoiaENmCliiOMT0OwSVQsFSplViK44zcBqsqyO90CoW', '[email protected]', true);
INSERT INTO square.public.USERS (USERNAME, PASSWORD, EMAIL, STATUS) VALUES ('linda','$2a$10$V2y9AJRW/dLoiaENmCliiOMT0OwSVQsFSplViK44zcBqsqyO90CoW', '[email protected]', true);
INSERT INTO square.public.ROLES (ROLE) VALUES ('ROLE_ADMIN');
INSERT INTO square.public.ROLES (ROLE) VALUES ('ROLE_USER');
INSERT INTO square.public.USERS_ROLES (USER_ID, ROLE_ID) VALUES (1, 1);
INSERT INTO square.public.USERS_ROLES (USER_ID, ROLE_ID) VALUES (2, 2);
INSERT INTO square.public.USERS_ROLES (USER_ID, ROLE_ID) VALUES (3, 2);
INSERT INTO square.public.USERS_ROLES (USER_ID, ROLE_ID) VALUES (4, 2);
INSERT INTO square.public.USERS_ROLES (USER_ID, ROLE_ID) VALUES (5, 2);
INSERT INTO square.public.USERS_ROLES (USER_ID, ROLE_ID) VALUES (6, 2);
INSERT INTO square.public.HOUSES (HOST_ID,TITLE,DESCRIPTION,PRICE,LOCATION,LATITUDE,LONGITUDE,STATUS, REJECTION)
VALUES (1, 'Ahsan Monjil', 'This is a test description', 2000, 'Dhaka', '23.70879777473861', '90.40595375269142', true, false);
INSERT INTO square.public.HOUSES (HOST_ID,TITLE,DESCRIPTION,PRICE,LOCATION,LATITUDE,LONGITUDE,STATUS, REJECTION)
VALUES (1, 'Lalbagh Fort', 'This is a test description', 3000, 'Dhaka', '23.71979002937195', '90.38807283396444', true, false);
INSERT INTO square.public.HOUSES (HOST_ID,TITLE,DESCRIPTION,PRICE,LOCATION,LATITUDE,LONGITUDE,STATUS, REJECTION)
VALUES (2, 'Star Mosque', 'This is a test description', 1500, 'Dhaka', '23.71592406878815', '90.40167083919764', true, false);
INSERT INTO square.public.HOUSES (HOST_ID,TITLE,DESCRIPTION,PRICE,LOCATION,LATITUDE,LONGITUDE,STATUS, REJECTION)
VALUES (2, 'Radisson Blu', 'This is a test description', 8600, 'Dhaka', '23.816184880760368', '90.40886153919956', true, false);
INSERT INTO square.public.HOUSES (HOST_ID,TITLE,DESCRIPTION,PRICE,LOCATION,LATITUDE,LONGITUDE,STATUS, REJECTION)
VALUES (3, 'Inter-Continental Dhaka', 'This is a test description', 15000, 'Dhaka', '23.741410111163336', '90.39687483919815', true, false);
INSERT INTO square.public.HOUSES (HOST_ID,TITLE,DESCRIPTION,PRICE,LOCATION,LATITUDE,LONGITUDE,STATUS, REJECTION)
VALUES (3, 'The Westin Dhaka', 'This is a test description', 16261, 'Dhaka', '23.793520723555943', '90.41455286803448', true, false);
INSERT INTO square.public.HOUSES (HOST_ID,TITLE,DESCRIPTION,PRICE,LOCATION,LATITUDE,LONGITUDE,STATUS, REJECTION)
VALUES (4, 'Six Season', 'This is a test description', 11747, 'Dhaka', '23.794914721813576', '90.41940230191382', true, false);
INSERT INTO square.public.HOUSES (HOST_ID,TITLE,DESCRIPTION,PRICE,LOCATION,LATITUDE,LONGITUDE,STATUS, REJECTION)
VALUES (4, 'Hotel Bengal Blueberry', 'This is a test description', 9370, 'Dhaka', '23.79623998799179', '90.41610854941173', true, false);
INSERT INTO square.public.HOUSES (HOST_ID,TITLE,DESCRIPTION,PRICE,LOCATION,LATITUDE,LONGITUDE,STATUS, REJECTION)
VALUES (5, 'The Midori by Lakeshore', 'This is a test description', 4333, 'Dhaka', '23.79711367457536', '90.41619438009462', true, false);
INSERT INTO square.public.HOUSES (HOST_ID,TITLE,DESCRIPTION,PRICE,LOCATION,LATITUDE,LONGITUDE,STATUS, REJECTION)
VALUES (5, 'Nascent Gardenia Suites', 'This is a test description', 6223, 'Dhaka', '23.801298490757695', '90.41327953927164', true, false);
INSERT INTO square.public.HOUSES (HOST_ID,TITLE,DESCRIPTION,PRICE,LOCATION,LATITUDE,LONGITUDE,STATUS, REJECTION)
VALUES (6, 'Hotel Aristocrat Inn Ltd', 'This is a test description', 8622, 'Dhaka', '23.80323630684725', '90.40915844449944', true, false);
INSERT INTO square.public.HOUSES (HOST_ID,TITLE,DESCRIPTION,PRICE,LOCATION,LATITUDE,LONGITUDE,STATUS, REJECTION)
VALUES (6, 'Ascott The Residence Dhaka', 'This is a test description', 8622, 'Dhaka', '23.80483556706111', '90.41983756632573', true, false);