CREATE TABLE EMPLOYEE (
  EmployeeID int NOT NULL PRIMARY KEY,
  Name varchar(30) NOT NULL,
  Phone varchar(15) NOT NULL, -- var name changed
  Email varchar(30),
  Office INT NOT NULL,
  Floor INT NOT NULL
);

Create table MEETING (
  MeetingID INT NOT NULL,
  Room INT NOT NULL,
  Floor int NOT NULL,
  StartTime Int NOT NULL,
  Primary key (MeetingID)
);
  
create table ATTENDANTS (
  MeetingID int NOT NULL,
  EmployeeID int NOT NULL,
  PRIMARY KEY (MeetingID, EmployeeID) -- constraint why
);

create table NOTIFICATION (
  NotificationID int NOT NULL,
  EmployeeID int NOT NULL,
  NotificationDate int NOT NULL,
  NotificationType varchar(10) NOT NULL, 
  PRIMARY KEY (NotificationID, EmployeeID) 
);

create table SYMPTOM (
  RowID int NOT NULL,
  EmployeeID int NOT NULL,
  SymptomID int NOT NULL,
  DateReported INT NOT NULL,
  primary key (RowID)
);


Create table SCAN (
  ScanID int NOT NULL,
  EmployeeID int NOT NULL,
  ScanDate INT NOT NULL,
  ScanTime INT NOT NULL,
  Temperature INT NOT NULL,
  PRIMARY key (ScanID)
);

Create table TEST (
  TestID int NOT NULL,
  Location varchar(30) NOT NULL,
  EmployeeID int NOT NULL,
  TestDate int NOT NULL,
  TestTime int NOT NULL,
  TestResult varchar(20) NOT NULL,
  primary key (TestID)
);

create TABLE POSCASE (
  CaseID int NOT NULL,
  EmployeeID int NOT NULL,
  CaseDate INT NOT NULL,
  Resolution varchar(20),
  primary key (CaseID)
);

create table HEALTHSTATUS (
  RowID int NOT NULL,
  EmployeeID int NOT NULL,
  StatusDate int NOT NULL, 
  Status varchar(20) NOT NULL,
  Primary key (RowID)
);

-- adding data to tables
INSERT INTO EMPLOYEE VALUES 
(1, 'John', '555-555-5555', 'john@usc.edu', 1, 1),
(2, 'Jane', '555-555-5555', 'jane@usc.edu', 1, 1),
(3, 'Michael', '111-222-3333', 'michael@usc.edu', 1, 1),
(4, 'Emily', '444-555-6666', 'emily@usc.edu', 1, 1),
(5, 'David', '777-888-9999', 'david@usc.edu', 1, 2),
(6, 'Sophia', '555-123-4567', 'sophia@usc.edu', 2, 2),
(7, 'Oliver', '555-234-5678', 'oliver@usc.edu', 2, 2),
(8, 'Emma', '555-345-6789', 'emma@usc.edu', 2, 2),
(9, 'Liam', '555-456-7890', 'liam@usc.edu', 1, 3),
(10, 'Ava', '555-567-8901', 'ava@usc.edu', 1, 3),
(11, 'Noah', '555-678-9012', 'noah@usc.edu', 1, 3),
(12, 'Isabella', '555-789-0123', 'isabella@usc.edu', 2, 3),
(13, 'William', '555-890-1234', 'william@usc.edu', 1, 4),
(14, 'Sophia', '555-987-6543', 'sophia2@usc.edu', 2, 4),
(15, 'Benjamin', '555-876-5432', 'benjamin@usc.edu', 2, 4),
(16, 'Amelia', '555-765-4321', 'amelia@usc.edu', 1, 4),
(17, 'Mia', '555-654-3210', 'mia@usc.edu', 1, 1),
(18, 'Ethan', '555-543-2109', 'ethan@usc.edu', 1, 2),
(19, 'Elijah', '555-432-1098', 'elijah@usc.edu', 1, 1),
(20, 'Harper', '555-321-0987', 'harper@usc.edu', 1, 3),
(21, 'James', '555-210-9876', 'james@usc.edu', 1, 2),
(22, 'Evelyn', '555-109-8765', 'evelyn@usc.edu', 2, 2),
(23, 'Logan', '555-098-7654', 'logan@usc.edu', 1, 4),
(24, 'Abigail', '555-987-6543', 'abigail@usc.edu', 2, 3),
(25, 'Sophia', '555-555-5555', 'sophia@usc.edu', 1, 1);


INSERT INTO ATTENDANTS VALUES 
(1, 15),
(1, 8),
(1, 23),
(1, 5),
(2, 9),
(2, 12),
(2, 24),
(2, 2),
(3, 7),
(3, 13),
(3, 19),
(4, 1),
(4, 20),
(5, 14),
(5, 6),
(5, 25);

INSERT INTO MEETING VALUES 
(1, 1, 1, 4),
(2, 2, 1, 6),
(3, 1, 2, 9),
(4, 2, 3, 11),
(5, 1, 4, 14);

INSERT INTO SYMPTOM VALUES 
(1, 1, 1, 1),
(2, 1, 2, 2),
(3, 1, 1, 2),
(4, 10, 5, 3),
(5, 10, 2, 5),
(6, 10, 3, 2),
(7, 4, 2, 3);


INSERT INTO SCAN VALUES 
(1, 1, 1, 8, 98),
(2, 2, 1, 8, 98),
(3, 4, 1, 8, 98),
(4, 7, 2, 8, 98);

INSERT INTO TEST VALUES 
(1, 'company', 1, 1, 12, 'Negative'),
(2, 'company', 2, 2, 12, 'Negative'),
(3, 'clinic', 3, 1, 13, 'Positive'),
(4, 'clinic', 23, 1, 11, 'Negative');

INSERT INTO POSCASE VALUES (1, 3, 1, 'left');

INSERT INTO HEALTHSTATUS VALUES 
(1, 1, 1, 'well'),
(2, 3, 1, 'sick'),
(3, 2, 1, 'sick'),
(4, 3, 1, 'sick'),
(5, 8, 1, 'well'),
(6, 7, 1, 'well'),
(7, 1, 1, 'well'),
(8, 9, 1, 'sick'),
(9, 9, 1, 'sick'),
(10, 9, 1, 'sick');