CREATE TABLE Waiter(
	ID INT identity(1,1) NOT NULL PRIMARY KEY,
	WName NVARCHAR(25) NOT NULL
);

CREATE TABLE RestaurantTable (
	TableNo INT identity(1,1) NOT NULL PRIMARY KEY,
	Seats INT NOT NULL,
	WindowSite BIT NOT NULL DEFAULT 0, -- NOT BY THE WINDOW 
	Waiter INT NULL,

	CONSTRAINT TABLE_WAITER_FK 
	FOREIGN KEY (Waiter) REFERENCES Waiter(ID)
		ON DELETE NO ACTION
		ON UPDATE CASCADE
);

CREATE TABLE MenuCard (
	CardName NVARCHAR(10) NOT NULL PRIMARY KEY,
	TimeOfDay NCHAR(1) NOT NULL -- B = BREAKFAST  L = LUNCH     E = EVENING  D = Drink
			CHECK (TimeOfDay IN ('B', 'L', 'E', 'D') )
);

CREATE TABLE MenuItem (
	MenuItemNo		INT identity(1,1) NOT NULL PRIMARY KEY,
	MenuName		NVARCHAR(20) NOT NULL,
	TypeOfItem		NVARCHAR(20) NOT NULL,
	MenuDescription	NVARCHAR(100) NOT NULL,
	Price			FLOAT NOT NULL,
	MenuCardName	NVARCHAR(10) NOT NULL,

	CONSTRAINT MENU_MENUITEM_FK 
	FOREIGN KEY (MenuCardName) REFERENCES MenuCard(CardName)
		ON DELETE NO ACTION
		ON UPDATE CASCADE
);

CREATE TABLE Customer(
	Phone NVARCHAR(8) NOT NULL PRIMARY KEY,
	CName NVARCHAR(25) NULL
);
CREATE TABLE OrderLine (
	OrderLineNo INT identity(1,1) NOT NULL PRIMARY KEY,
	MenuItemId  INT NOT NULL,
	CustomerNo  NVARCHAR(8) NOT NULL,

	CONSTRAINT ORDERLINE_MENUITEM_FK 
	FOREIGN KEY (MenuItemId) REFERENCES MenuItem(MenuItemNo)
		ON DELETE CASCADE
		ON UPDATE CASCADE,
		
	CONSTRAINT ORDERLINE_CUSTOMER_FK 
	FOREIGN KEY (CustomerNo) REFERENCES Customer(Phone)
		ON DELETE CASCADE
		ON UPDATE CASCADE	
);

CREATE TABLE Booking (
	BookingId	INT identity(1,1) NOT NULL PRIMARY KEY,
	DayAndTime	DATETIME NOT NULL,
	CustomerNo	NVARCHAR(8) NOT NULL,

	CONSTRAINT BOOKING_CUSTOMER_FK 
	FOREIGN KEY (CustomerNo) REFERENCES Customer(Phone)
		ON DELETE CASCADE
		ON UPDATE CASCADE

);

CREATE TABLE Reservation(
	BookingId	INT NOT NULL,
	TableNo		INT NOT NULL,

	CONSTRAINT RESERVATION_PK 
	PRIMARY KEY (BookingId, TableNo),
	
	CONSTRAINT RESERVATION_BOOKING_FK 
	FOREIGN KEY (BookingId) REFERENCES Booking(BookingId)
		ON DELETE CASCADE
		ON UPDATE CASCADE,

	CONSTRAINT RESERVATION_TABLE_FK 
	FOREIGN KEY (TableNo) REFERENCES RestaurantTable(TableNo)
		ON DELETE NO ACTION
		ON UPDATE CASCADE

);