CREATE TABLE Cities(
Id INT IDENTITY PRIMARY KEY,
[Name] NVARCHAR(20) NOT NULL,
CountryCode CHAR(2) NOT NULL
)

CREATE TABLE Hotels(
Id INT IDENTITY PRIMARY KEY,
[Name] NVARCHAR(30) NOT NULL,
CityId INT NOT NULL,
EmployeeCount INT NOT NULL,
BaseRate DECIMAL(15, 2),
CONSTRAINT FK_Hotels_Cities
FOREIGN KEY (CityId) REFERENCES Cities(Id)
)

CREATE TABLE Rooms(
Id INT IDENTITY PRIMARY KEY,
Price DECIMAL(15, 2) NOT NULL,
[Type] NVARCHAR(20) NOT NULL,
Beds INT NOT NULL,
HotelId INT NOT NULL,
CONSTRAINT FK_Rooms_Hotels
FOREIGN KEY(HotelId) REFERENCES Hotels(Id)
)

CREATE TABLE Trips(
Id INT IDENTITY PRIMARY KEY,
RoomId INT NOT NULL,
BookDate DATETIME NOT NULL,
ArrivalDate DATETIME NOT NULL,
ReturnDate DATETIME NOT NULL,
CancelDate DATETIME
CONSTRAINT FK_Trips_Rooms
FOREIGN KEY (RoomId) REFERENCES Rooms(Id),
CONSTRAINT CHK_Trips_ArrivalDate
CHECK(ArrivalDate < ReturnDate),
CONSTRAINT CHK_Trips_BookDate
CHECK(BookDate < ArrivalDate)
)

CREATE TABLE Accounts(
Id INT IDENTITY PRIMARY KEY,
FirstName NVARCHAR(50) NOT NULL,
MiddleName NVARCHAR(20),
LastName NVARCHAR(50) NOT NULL,
CityId INT NOT NULL,
BirthDate DATETIME NOT NULL,
Email VARCHAR(100) UNIQUE NOT NULL,
CONSTRAINT FK_Accounts_Cities
FOREIGN KEY (CityId) REFERENCES Cities(Id)
)

CREATE TABLE AccountsTrips(
AccountId INT NOT NULL,
TripId INT NOT NULL,
Luggage INT NOT NULL,
CONSTRAINT FK_AccountsTrips_Accounts
FOREIGN KEY(AccountId) REFERENCES Accounts(Id),
CONSTRAINT FK_AccountsTrips_Accounts_Trip
FOREIGN KEY (TripId) REFERENCES Trips(Id),
CONSTRAINT CHK_Luggage
CHECK(Luggage >= 0)
)