DROP DATABASE IF EXISTS reservate;

create database reservate;

use reservate;

CREATE TABLE employee ( EmpId VARCHAR (15)PRIMARY KEY, Nic VARCHAR(15), Fullname VARCHAR(50), Address VARCHAR(100), Mobile VARCHAR(50), Email VARCHAR(50) );

CREATE TABLE User ( UserId VARCHAR (15)PRIMARY KEY, EmpId VARCHAR (15), UserName VARCHAR(15), Password VARCHAR(15), CONSTRAINT FOREIGN KEY (EmpId)REFERENCES employee (EmpId) );

CREATE TABLE Meal ( PackageId VARCHAR (15)PRIMARY KEY, MealPlan VARCHAR (15), MealType VARCHAR (15), Description VARCHAR (200), Price DECIMAL(15,2) );

CREATE TABLE Hall ( HallNumber VARCHAR (15)PRIMARY KEY, UserId VARCHAR (15), HallType VARCHAR (15), Price Decimal (15,2), Status VARCHAR (50) );

CREATE TABLE Room ( RoomNumber VARCHAR (15)PRIMARY KEY, UserId VARCHAR (15), RoomType VARCHAR (15), Price Decimal (15,2), Status VARCHAR (50) );

CREATE TABLE Guest ( GuestId VARCHAR (15)PRIMARY KEY, UserId VARCHAR (15), Nic VARCHAR(15), Fullname VARCHAR(50), Address VARCHAR(100), Mobile VARCHAR(50), Date date, Email VARCHAR(50), CONSTRAINT FOREIGN KEY (UserId)REFERENCES User (UserId) );

CREATE TABLE MealOrder ( MealOrderId VARCHAR (15)PRIMARY KEY, Qty INT );

CREATE TABLE RoomReservation ( CheckIn time, CheckOut time, RoomReservationId VARCHAR (15)PRIMARY KEY );

CREATE TABLE HallReservation ( CheckIn time, CheckOut time, HallReservationId VARCHAR (15)PRIMARY KEY );

CREATE TABLE MealOrderDetails ( PackageId VARCHAR (15), MealOrderId VARCHAR (15), CONSTRAINT FOREIGN KEY (PackageId)REFERENCES Meal (PackageId), CONSTRAINT FOREIGN KEY (MealOrderId)REFERENCES MealOrder (MealOrderId) );

CREATE TABLE RoomReservationDetails ( RoomReservationId VARCHAR (15), RoomNumber VARCHAR (15), CONSTRAINT FOREIGN KEY (RoomNumber)REFERENCES Room (RoomNumber), CONSTRAINT FOREIGN KEY (RoomReservationId)REFERENCES RoomReservation (RoomReservationId) );

CREATE TABLE HallReservationDetails ( HallReservationId VARCHAR (15), HallNumber VARCHAR (15), CONSTRAINT FOREIGN KEY (HallNumber)REFERENCES Hall (HallNumber), CONSTRAINT FOREIGN KEY (HallReservationId)REFERENCES HallReservation (HallReservationId) );

CREATE TABLE HallMaintenance ( HallMaintenanceId VARCHAR (15)PRIMARY KEY, Date date, startTime time, EndTime time, HallReservationId VARCHAR (15), CONSTRAINT FOREIGN KEY (HallReservationId)REFERENCES HallReservation (HallReservationId) );

CREATE TABLE RoomMaintenance ( RoomMaintenanceId VARCHAR (15)PRIMARY KEY, Date date, startTime time, EndTime time, RoomReservationId VARCHAR (15), CONSTRAINT FOREIGN KEY (RoomReservationId)REFERENCES RoomReservation (RoomReservationId) );

CREATE TABLE Orders ( OrderId VARCHAR (15)PRIMARY KEY, GuestId VARCHAR (15), RoomReservationId VARCHAR (15), HallReservationId VARCHAR (15), MealOrderId VARCHAR (15), UserId VARCHAR (15), ComplaintId VARCHAR (15), OrderDate date, Qty INT, Payments Double(15,2), CONSTRAINT FOREIGN KEY (GuestId)REFERENCES Guest (GuestId), CONSTRAINT FOREIGN KEY (RoomReservationId)REFERENCES RoomReservation (RoomReservationId), CONSTRAINT FOREIGN KEY (HallReservationId)REFERENCES HallReservation (HallReservationId), CONSTRAINT FOREIGN KEY (MealOrderId)REFERENCES MealOrder (MealOrderId), CONSTRAINT FOREIGN KEY (UserId)REFERENCES User (UserId)

);

CREATE TABLE Complaints ( ComplaintId VARCHAR (15)PRIMARY KEY, OrderId VARCHAR (15), Date date, Description VARCHAR (200), ComplaintTime VARCHAR (10), CONSTRAINT FOREIGN KEY (OrderId)REFERENCES Orders (OrderId) );