-- RESAURANT DATABASE

DROP DATABASE IF EXISTS restaurant;
CREATE DATABASE restaurant;
USE restaurant;

DROP TABLE IF EXISTS CUSTOMER;
DROP TABLE IF EXISTS TABLES;
DROP TABLE IF EXISTS OWNER;
DROP TABLE IF EXISTS STORE;
DROP TABLE IF EXISTS REWARDS;
DROP TABLE IF EXISTS STAFF;
DROP TABLE IF EXISTS SYSTEM;
DROP TABLE IF EXISTS FLOORPLAN;
DROP TABLE IF EXISTS CONTAINS; 
DROP TABLE IF EXISTS RESERVES;

-- Start with an easy table OWNER
CREATE TABLE OWNER (
  OwnerID INT,
  FirstName VARCHAR(30),
  LastName VARCHAR(30),
  CONSTRAINT Own_ID PRIMARY KEY (OwnerID)
);

INSERT INTO OWNER (OwnerID, FirstName, LastName) VALUES (002, 'Joe', 'Shmo');
INSERT INTO OWNER (OwnerID, FirstName, LastName) VALUES (003, 'Jane', 'Doe');

-- Create FLOORPLAN Table
CREATE TABLE FLOORPLAN (
  FloorPlanID INT,
  NoMain INT,
  NoPatio INT,
  NoBar INT,
  CONSTRAINT PRIMARY KEY (FloorPlanID)
);

INSERT INTO FLOORPLAN (FloorPlanID, NoMain, NoPatio, NoBar) VALUES
(1, 4, 6, 8), 
(2, 10, 8, 6), 
(3, 10, 0, 8), 
(4, 6, 4, 10);

-- Create the STORE table
CREATE TABLE STORE (
  StoreID INT,
  Street VARCHAR(50),
  State VARCHAR(50),
  Zip VARCHAR(10),
  City VARCHAR(30),
  FloorPlanID INT,
  CONSTRAINT PRIMARY KEY (StoreID),
  CONSTRAINT FOREIGN KEY (FloorPlanID) REFERENCES FLOORPLAN (FloorPlanID)
);

-- Mock Data for STORE Table
INSERT INTO STORE (StoreID, Street, State, Zip, City, FloorPlanID) VALUES
(1, '71 S Hawthorne Rd', 'IL', '60559', 'Westmont', 2);

-- Create STAFF table
CREATE TABLE STAFF (
  StaffID INT,
  FirstName VARCHAR(30),
  LastName VARCHAR(30),
  TypeOfEmployee VARCHAR(30),
  HireDate DATE,
  StoreID INT,
  CONSTRAINT PRIMARY KEY (StaffID),
  CONSTRAINT FOREIGN KEY (StoreID) REFERENCES STORE (StoreID)
);

INSERT INTO STAFF (StaffID, FirstName, LastName, TypeOfEmployee, HireDate, StoreID) VALUES
(227, 'Jaqueline', 'Wilkerson', 'Part Time', '1992-06-05', 1),
(346, 'Julianne', 'Erickson', 'Full Time', '1992-04-08', 1),
(334, 'Sam', 'Ball', 'Part Time', '2009-03-01', 1), 
(310, 'Aliyah', 'Barnett', 'Full Time', '2009-03-17', 1),
(356, 'Adrian', 'Hoover', 'Part Time', '2000-04-21', 1),
(246, 'Lorenzo', 'Contreras', 'Full Time', '1992-09-04', 1),
(331, 'America', 'Jenkins', 'Full Time', '2017-12-25', 1),
(338, 'Georgia', 'Owen', 'Part Time', '2014-09-20', 1),
(370, 'Dulce', 'Glover', 'Part Time', '2010-03-12', 1),
(214, 'Ciara', 'Ray', 'Full Time', '2014-04-14', 1),
(311, 'Jaylah', 'Zeek', 'Full Time', '1993-06-25', 1),
(319, 'Andre', 'Wild', 'Full Time', '1993-12-01', 1);


CREATE TABLE TABLETYPE (
  TableType VARCHAR(30),
  TypeID VARCHAR(5),
  NumSeats INT,
  CONSTRAINT Table_Type_ID PRIMARY KEY (TypeID)
);

INSERT INTO TABLETYPE (TableType,TypeID, NumSeats) VALUES 
('hightop','H',4), 
('booth','BT',4), 
('round','R',6), 
('two-top’','TT', 2);


-- Create TABLES table

CREATE TABLE TABLES (
  TableID VARCHAR(5),
  TypeID VARCHAR(30),
  FloorPlanID INT,
  CONSTRAINT Table_ID PRIMARY KEY (TableID),
  CONSTRAINT FOREIGN KEY (FloorPlanID) REFERENCES FLOORPLAN (FloorPlanID),
  -- created contraint on typeid and updated the name to matach
  CONSTRAINT FOREIGN KEY (TypeID) REFERENCES TABLETYPE (TypeID)
  

);


INSERT INTO TABLES (TableID, TypeID, FloorPlanID) VALUES
('m1','BT',2),
('m2', 'BT',2),
('m3','BT',2),
('m4','BT',2),
('m5','BT',2),
('m6','BT',2),
('m7','R',2),
('m8','R',2),
('m9','R',2),
('m10','R',2),
('p1','TT',2),
('p2','TT',2),
('p3','TT',2),
('p4','TT',2),
('p5','TT',2),
('p6','TT',2),
('p7','TT',2),
('p8','TT',2),
('b1','TT',2),
('b2','TT',2),
('b3','H',2),
('b4','H',2),
('b5','H',2),
('b6','H',2);



-- Next create CUSTOMER tables
CREATE TABLE CUSTOMER (
  CustomerID INT,
  FirstName VARCHAR(30),
  LastName VARCHAR(30),
  Allergy VARCHAR(30),
  CONSTRAINT Cus_ID PRIMARY KEY (CustomerID)
);

INSERT INTO CUSTOMER (CustomerID, FirstName, LastName, Allergy)
VALUES
  (5732, 'Leland', 'Mckee', 'Yes'),
  (8901, 'Tiana', 'Norman', 'No'),
  (2468, 'Rigoberto', 'Horn', 'Yes'),
  (7154, 'Jaron', 'Coffey', 'Yes'),
  (3920, 'Ean', 'Kelly', 'No'),
  (6489, 'Grayson', 'Hale', 'No'),
  (1234, 'Mikayla', 'Choi', 'Yes'),
  (9876, 'Bailey', 'Graham', 'No'),
  (5678, 'Kamora', 'Benton', 'Yes'),
  (4301, 'Justin', 'Snyder', 'Yes'),
  (8192, 'Walter', 'Nichols', 'No'),
  (3546, 'Lilyana', 'Osborn', 'Yes'),
  (2067, 'Aimee', 'Bartlett', 'No'),
  (9810, 'Gavyn', 'Lester', 'Yes'),
  (6423, 'Riley', 'Atkinson', 'No'),
  (7598, 'Kianna', 'Mendoza', 'No'),
  (2345, 'Jaelynn', 'Robbins', 'Yes'),
  (8765, 'Carla', 'Fox', 'Yes'),
  (1098, 'Amiah', 'Griffin', 'No'),
  (5032, 'Dana', 'Peters', 'Yes'),
  (3146, 'Sarah', 'Valenzuela', 'No'),
  (6902, 'Yurem', 'Moran', 'No'),
  (4578, 'Koen', 'Young', 'No'),
  (8236, 'Riley', 'Hodges', 'Yes'),
  (1789, 'Blaine', 'Moss', 'No');

CREATE TABLE CUSTOMERPHONENUM (
  PhoneNum VARCHAR(15),
  CustomerID INT,
  CONSTRAINT Cust_Phone FOREIGN KEY (CustomerID) REFERENCES CUSTOMER (CustomerID)
 );
 
INSERT INTO CUSTOMERPHONENUM (PhoneNum, CustomerID)
VALUES
  ('(339) 315-0421', 5732),
  ('(861) 972-0819', 8901),
  ('(826) 652-3266', 2468),
  ('(883) 896-5778', 7154),
  ('(867) 465-8280', 3920),
  ('(754) 650-2052', 6489),
  ('(735) 514-3752', 1234),
  ('(911) 680-5320', 9876),
  ('(210) 215-6247', 5678),
  ('(926) 595-7601', 4301),
  ('(696) 359-9376', 8192),
  ('(989) 263-2410', 3546),
  ('(861) 486-3281', 2067),
  ('(396) 987-7934', 9810),
  ('(637) 536-5369', 6423),
  ('(936) 590-6026', 7598),
  ('(931) 627-4070', 2345),
  ('(533) 300-5635', 8765),
  ('(630) 328-5117', 1098),
  ('(774) 734-2927', 5032),
  ('(516) 920-5959', 3146),
  ('(843) 951-4267', 6902),
  ('(794) 825-9869', 4578),
  ('(512) 390-5998', 8236),
  ('(606) 745-5247', 1789);

 CREATE TABLE CUSTOMEROCCASION (
   Occasion VARCHAR(50),
   CustomerID INT,
   CONSTRAINT Cust_Occ FOREIGN KEY (CustomerID) REFERENCES CUSTOMER (CustomerID)
 );

 INSERT INTO CUSTOMEROCCASION (Occasion, CustomerID)
VALUES
  ('Meeting', 5732),
  ('None', 8901),
  ('Anniversary', 2468),
  ('Birthday', 7154),
  ('Holiday', 3920),
  ('None', 6489),
  ('Meeting', 1234),
  ('Anniversary', 9876),
  ('Holiday', 5678),
  ('Birthday', 4301),
  ('None', 8192),
  ('Holiday', 3546),
  ('Meeting', 2067),
  ('Anniversary', 9810),
  ('Birthday', 6423),
  ('None', 7598),
  ('Meeting', 2345),
  ('Holiday', 8765),
  ('Birthday', 1098),
  ('Anniversary', 5032),
  ('None', 3146),
  ('Birthday', 6902),
  ('Meeting', 4578),
  ('None', 8236),
  ('Holiday', 1789);

CREATE TABLE RESERVES (
  CustomerID INT,
  TableID VARCHAR(5),
  ReserveDate DATE,
  ReserveTime TIME,
  StaffID INT,
  CONSTRAINT Reserves_ID PRIMARY KEY (CustomerID, TableID),
  CONSTRAINT Cust_Reserves FOREIGN KEY (CustomerID) REFERENCES CUSTOMER (CustomerID),
  CONSTRAINT Table_Reserves FOREIGN KEY (TableID) REFERENCES TABLES (TableID),
  CONSTRAINT Staff_Reserves FOREIGN KEY (StaffID) REFERENCES STAFF (StaffID)
);

INSERT INTO RESERVES (CustomerID, TableID, ReserveDate, ReserveTime, StaffID) VALUES
(3920, 'p7', '2023-11-14', '18:30:00', 338),
(9810, 'm4', '2023-11-14', '18:00:00', 370),
(7598, 'b5', '2023-11-14', '16:45:00', 311),
(8236, 'p2', '2023-11-14', '16:00:00', 338),
(2345, 'm2', '2023-11-15', '17:30:00', 356),
(1789, 'm2', '2023-11-15', '14:00:00', 227),
(1098, 'b1', '2023-11-15', '12:30:00', 334),
(2067, 'm9', '2023-11-23', '19:00:00', 246),
(5732, 'p4', '2023-11-17', '16:45:00', 311),
(7154, 'b3', '2023-11-16', '18:00:00', 370),
(8192, 'm7', '2023-11-16', '17:30:00', 319),
(6423, 'b6', '2023-11-16', '14:45:00', 334),

-- added addtional resevrations from customers who have rewards


(2345, 'p2', '2023-11-19', '16:00:00', 338),
(8765,'m2', '2023-11-20', '17:30:00', 356),
(1098,'m2', '2023-11-20', '14:00:00', 227),
(5032,'b1', '2023-11-20', '12:30:00', 334),
(3146,'m9', '2023-11-18', '19:00:00', 246),
(6902,'p4', '2023-11-22', '16:45:00', 311),
(4578,'b3', '2023-11-21', '18:00:00', 370),
(8236,'m7', '2023-11-21', '17:30:00', 319),
(1789,'b6', '2023-11-21', '14:45:00', 334)
;



CREATE TABLE OWNS (
  OwnerID INT,
  StoreID INT,
  CONSTRAINT PRIMARY KEY (OwnerID, StoreID),
  CONSTRAINT FOREIGN KEY (OwnerID) REFERENCES OWNER (OwnerID),
  CONSTRAINT FOREIGN KEY (StoreID) REFERENCES STORE (StoreID)
);

INSERT INTO OWNS (OwnerID, StoreID) VALUES (002, 1);
DROP TABLE IF EXISTS REWARDSTYPE;
-- Create REWARDS Tables
CREATE TABLE REWARDTYPE (
  RewardID INT,
  TypeOfReward VARCHAR(30),
  CONSTRAINT PRIMARY KEY (RewardID)
);

INSERT INTO REWARDTYPE (RewardID, TypeOfReward) VALUES
(1, 'Birthday'),
(2, 'Free App.'),
(3, '% Off '),
(4, '$ Off');

CREATE TABLE REWARDS (
  RewardID INT,
  RewardsNumber INT,
  CustomerID INT,
  CONSTRAINT PRIMARY KEY (RewardsNumber),
  CONSTRAINT FOREIGN KEY (CustomerID) REFERENCES CUSTOMER (CustomerID),
  CONSTRAINT FOREIGN KEY (RewardID) REFERENCES REWARDTYPE (RewardID)
);

INSERT INTO REWARDS (RewardID, RewardsNumber, CustomerID) VALUES
(1, 1001, 5732),
(2, 1002, 8901),
(3, 1003, 2468),
(3, 1004, 7154),
(2, 1005, 3920),
(3, 1006, 6489),
(4, 1007, 1234),
(1, 1008, 9876),
(2, 1009, 5678),
(4, 1010, 4301),
(2, 1011, 8192),
(1, 1012, 3546),
(2, 1013, 2067),
(4, 1014, 9810),
(2, 1015, 6423),
(2, 1016, 7598),
(4, 1017, 2345),
(3, 1018, 8765),
(2, 1019, 1098),
(1, 1020, 5032),
(1, 1021, 3146),
(2, 1022, 6902),
(3, 1023, 4578),
(4, 1024, 8236),
(3, 1025, 1789),
-- added additional rewards claimed by the same customer
(1, 1026, 2345),
(4, 1027, 8765),
(1, 1028, 1098),
(2, 1029, 5032),
(2, 1030, 3146),
(3, 1031, 6902),
(4, 1032, 4578),
(1, 1033, 8236),
(1, 1034, 1789);

-- Create SYSTEM Table
CREATE TABLE `SYSTEM` (
  SystemID INT,
  CONSTRAINT PRIMARY KEY (SystemID)
);

-- Mock Data for SYSTEM Table
INSERT INTO `SYSTEM` (SystemID) VALUES
(1);


CREATE TABLE TRACKS (
  CustomerID INT,
  TableID VARCHAR(5),
  RewardID INT,
  CONSTRAINT FOREIGN KEY (CustomerID) REFERENCES CUSTOMER (CustomerID),
  CONSTRAINT FOREIGN KEY (TableID) REFERENCES TABLES (TableID),
  CONSTRAINT FOREIGN KEY (RewardID) REFERENCES REWARDTYPE (RewardID)
);

-- Mock Data for TRACKS Table
INSERT INTO TRACKS (CustomerID, TableID, RewardID) VALUES
(3920, 'p7', 2),
(9810, 'm4', 4),
(7598, 'b5', 2),
(8236, 'p2', 4),
(2345, 'm2', 4),
(1789, 'm2', 3),
(1098, 'b1', 2),
(2067, 'm9', 2),
(5732, 'p4', 1),
(7154, 'b3', 3),
(8192, 'm7', 2),
(6423, 'b6', 2);