206 lines
6.3 KiB
SQL
206 lines
6.3 KiB
SQL
--
|
|
-- Users
|
|
---
|
|
|
|
CREATE TABLE users
|
|
(
|
|
id uuid NOT NULL PRIMARY KEY,
|
|
email varchar(255) UNIQUE NOT NULL
|
|
);
|
|
|
|
CREATE TABLE user_roles
|
|
(
|
|
user_id uuid NOT NULL REFERENCES users (id),
|
|
role varchar(255) check ((role)::text = ANY
|
|
((ARRAY ['CUSTOMER'::character varying, 'ADMIN'::character varying])::text[]))
|
|
);
|
|
|
|
CREATE TABLE user_identity
|
|
(
|
|
identity_type varchar(31) NOT NULL, -- Type of the identity, currently only password exists
|
|
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
hash varchar(255), -- Password Hash
|
|
user_id UUID NOT NULL REFERENCES users (id)
|
|
);
|
|
|
|
--
|
|
-- Address
|
|
--
|
|
|
|
CREATE TABLE addresses
|
|
(
|
|
id uuid NOT NULL PRIMARY KEY,
|
|
street varchar(255) NOT NULL,
|
|
house_number varchar(255),
|
|
postal_code varchar(255) NOT NULL,
|
|
city varchar(255) NOT NULL
|
|
);
|
|
|
|
CREATE TABLE user_addresses
|
|
(
|
|
user_id uuid NOT NULL REFERENCES users (id),
|
|
address_id uuid NOT NULL REFERENCES addresses (id)
|
|
);
|
|
|
|
--
|
|
-- Rooms
|
|
--
|
|
|
|
CREATE TABLE room_types
|
|
(
|
|
id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
name varchar(255) NOT NULL
|
|
);
|
|
|
|
CREATE TABLE rooms
|
|
(
|
|
id uuid NOT NULL PRIMARY KEY,
|
|
room_type_id bigint NOT NULL REFERENCES room_types (id),
|
|
beds integer NOT NULL,
|
|
name varchar(255) NOT NULL,
|
|
price real NOT NULL, -- float4
|
|
image_url varchar(255)
|
|
);
|
|
|
|
CREATE TABLE room_features
|
|
(
|
|
room_id uuid NOT NULL REFERENCES rooms (id),
|
|
features varchar(255)
|
|
);
|
|
|
|
--
|
|
-- Bookings
|
|
--
|
|
|
|
CREATE TABLE room_bookings
|
|
(
|
|
id uuid NOT NULL PRIMARY KEY,
|
|
room_id uuid NOT NULL REFERENCES rooms (id),
|
|
user_id uuid NOT NULL REFERENCES users (id),
|
|
start_date date,
|
|
end_date date
|
|
);
|
|
|
|
CREATE TABLE addition_booking_types
|
|
(
|
|
id uuid NOT NULL PRIMARY KEY,
|
|
name varchar(255) NOT NULL,
|
|
price real NOT NULL
|
|
);
|
|
|
|
CREATE TABLE addition_bookings
|
|
(
|
|
id uuid NOT NULL PRIMARY KEY,
|
|
addition_booking_type_id uuid NOT NULL REFERENCES addition_booking_types (id),
|
|
room_booking_id uuid NOT NULL REFERENCES room_bookings (id),
|
|
start_date date,
|
|
end_date date
|
|
);
|
|
|
|
--
|
|
-- SEEDING
|
|
--
|
|
|
|
--
|
|
-- Rooms
|
|
--
|
|
|
|
-- Room Type Classic
|
|
INSERT INTO room_types (id, name)
|
|
VALUES (1, 'Standard-Room');
|
|
|
|
-- Room 1
|
|
INSERT INTO rooms (id, room_type_id, name, price, image_url, beds)
|
|
VALUES ('24d96973-6f4f-4e15-96c8-492592a51c1d',
|
|
1,
|
|
'Standard Doppelzimmer',
|
|
129,
|
|
'https://images.unsplash.com/photo-1566665797739-1674de7a421a?auto=format&fit=crop&w=800&q=60',
|
|
2);
|
|
INSERT INTO room_features (room_id, features)
|
|
VALUES ('24d96973-6f4f-4e15-96c8-492592a51c1d',
|
|
'Klimaanlage');
|
|
INSERT INTO room_features (room_id, features)
|
|
VALUES ('24d96973-6f4f-4e15-96c8-492592a51c1d',
|
|
'WLAN');
|
|
INSERT INTO room_features (room_id, features)
|
|
VALUES ('24d96973-6f4f-4e15-96c8-492592a51c1d',
|
|
'Flachbild-TV');
|
|
INSERT INTO room_features (room_id, features)
|
|
VALUES ('24d96973-6f4f-4e15-96c8-492592a51c1d',
|
|
'Minibar');
|
|
|
|
|
|
-- Room 2: Superior Suite
|
|
INSERT INTO rooms (id, room_type_id, name, price, image_url, beds)
|
|
VALUES ('378fd3a5-e020-4531-9685-61169b890655',
|
|
1,
|
|
'Superior Suite',
|
|
199,
|
|
'https://images.unsplash.com/photo-1578683010236-d716f9a3f461?auto=format&fit=crop&w=800&q=60',
|
|
2);
|
|
INSERT INTO room_features (room_id, features)
|
|
VALUES ('378fd3a5-e020-4531-9685-61169b890655', 'Klimaanlage'),
|
|
('378fd3a5-e020-4531-9685-61169b890655', 'WLAN'),
|
|
('378fd3a5-e020-4531-9685-61169b890655', 'Minibar'),
|
|
('378fd3a5-e020-4531-9685-61169b890655', 'Balkon');
|
|
|
|
-- Room 3: Familienzimmer
|
|
INSERT INTO rooms (id, room_type_id, name, price, image_url, beds)
|
|
VALUES ('9a85b75b-c39c-4898-b456-bf89788f2c6e',
|
|
1,
|
|
'Familienzimmer',
|
|
249,
|
|
'https://images.unsplash.com/photo-1586023492125-27b2c045efd7?auto=format&fit=crop&w=800&q=60',
|
|
4);
|
|
INSERT INTO room_features (room_id, features)
|
|
VALUES ('9a85b75b-c39c-4898-b456-bf89788f2c6e', 'Klimaanlage'),
|
|
('9a85b75b-c39c-4898-b456-bf89788f2c6e', 'WLAN'),
|
|
('9a85b75b-c39c-4898-b456-bf89788f2c6e', 'Küchenzeile'),
|
|
('9a85b75b-c39c-4898-b456-bf89788f2c6e', 'Sofabett');
|
|
|
|
-- Room 4: Einzelzimmer Economy
|
|
INSERT INTO rooms (id, room_type_id, name, price, image_url, beds)
|
|
VALUES ('06f9159f-f5dc-47f7-a2a9-2bc4702787b1',
|
|
1,
|
|
'Einzelzimmer Economy',
|
|
89,
|
|
'https://images.unsplash.com/photo-1595576508898-0ad5c879a061?auto=format&fit=crop&w=800&q=60',
|
|
1);
|
|
INSERT INTO room_features (room_id, features)
|
|
VALUES ('06f9159f-f5dc-47f7-a2a9-2bc4702787b1', 'WLAN'),
|
|
('06f9159f-f5dc-47f7-a2a9-2bc4702787b1', 'Schreibtisch');
|
|
|
|
-- Room 5: Deluxe Suite mit Seeblick
|
|
INSERT INTO rooms (id, room_type_id, name, price, image_url, beds)
|
|
VALUES ('0f2a3dd0-b2ab-4cd8-9cfb-d2f4c07e1d36',
|
|
1,
|
|
'Deluxe Suite mit Seeblick',
|
|
299,
|
|
'https://images.unsplash.com/photo-1505693416388-ac5ce068fe85?auto=format&fit=crop&w=800&q=60',
|
|
2);
|
|
INSERT INTO room_features (room_id, features)
|
|
VALUES ('0f2a3dd0-b2ab-4cd8-9cfb-d2f4c07e1d36', 'Klimaanlage'),
|
|
('0f2a3dd0-b2ab-4cd8-9cfb-d2f4c07e1d36', 'WLAN'),
|
|
('0f2a3dd0-b2ab-4cd8-9cfb-d2f4c07e1d36', 'Minibar'),
|
|
('0f2a3dd0-b2ab-4cd8-9cfb-d2f4c07e1d36', 'Seeblick'),
|
|
('0f2a3dd0-b2ab-4cd8-9cfb-d2f4c07e1d36', 'Whirlpool');
|
|
|
|
-- Room 6: Studio Apartment
|
|
INSERT INTO rooms (id, room_type_id, name, price, image_url, beds)
|
|
VALUES ('09f453b2-7930-4f68-8d64-92f7faece4a0',
|
|
1,
|
|
'Studio Apartment',
|
|
179,
|
|
'https://images.unsplash.com/photo-1522708323590-d24dbb6b0267?auto=format&fit=crop&w=800&q=60',
|
|
2);
|
|
INSERT INTO room_features (room_id, features)
|
|
VALUES ('09f453b2-7930-4f68-8d64-92f7faece4a0', 'Klimaanlage'),
|
|
('09f453b2-7930-4f68-8d64-92f7faece4a0', 'WLAN'),
|
|
('09f453b2-7930-4f68-8d64-92f7faece4a0', 'Kochnische'),
|
|
('09f453b2-7930-4f68-8d64-92f7faece4a0', 'Arbeitsbereich');
|
|
|
|
-- Addition Bookings
|
|
INSERT INTO addition_booking_types (id, name, price)
|
|
VALUES ('574abdd4-92c2-4371-9038-f4b1f989b340', 'Porsche', 200),
|
|
('a10ae929-576e-4637-aad9-71f73230d4f1', 'Wellness', 50);
|