PRAGMA foreign_keys = ON;

PRAGMA writable_schema = 1;
DELETE FROM sqlite_master WHERE type IN ("table", "index", "trigger");
PRAGMA writable_schema = 0;
VACUUM;

CREATE TABLE "employee" (
"Employee_ID" INT UNIQUE,
"Name" TEXT,
"Age" INT,
"City" TEXT,
PRIMARY KEY ("Employee_ID")
);

CREATE TABLE "evaluation" (
"Employee_ID" TEXT,
"Year_awarded" TEXT,
"Bonus" FLOAT,
PRIMARY KEY ("Employee_ID", "Year_awarded")
);

CREATE TABLE "hiring" (
"Shop_ID" INT,
"Employee_ID" INT,
"Start_from" TEXT,
"Is_full_time" BOOLEAN,
PRIMARY KEY ("Employee_ID"),
FOREIGN KEY ("Employee_ID") REFERENCES "employee"("Employee_ID"),
FOREIGN KEY ("Shop_ID") REFERENCES "shop"("Shop_ID")
);

CREATE TABLE "shop" (
"Shop_ID" INT UNIQUE,
"Name" TEXT,
"Location" TEXT,
"District" TEXT,
"Number_products" INT,
"Manager_name" TEXT,
PRIMARY KEY ("Shop_ID")
);





INSERT INTO "employee" VALUES ("1", "Alice Johnson", "28", "New York");
INSERT INTO "employee" VALUES ("2", "Bob Smith", "35", "Los Angeles");
INSERT INTO "employee" VALUES ("3", "Charlie Lee", "22", "Chicago");
INSERT INTO "employee" VALUES ("4", "Diana Prince", "40", "Houston");
INSERT INTO "employee" VALUES ("5", "Ethan Hunt", "30", "Phoenix");
INSERT INTO "employee" VALUES ("6", "Fiona Gallagher", "27", "Philadelphia");
INSERT INTO "employee" VALUES ("7", "George Martin", "50", "San Antonio");
INSERT INTO "employee" VALUES ("8", "Hannah Baker", "24", "San Diego");
INSERT INTO "evaluation" VALUES ("1", "2022", "1500.00");
INSERT INTO "evaluation" VALUES ("1", "2023", "1800.00");
INSERT INTO "evaluation" VALUES ("2", "2023", "1200.00");
INSERT INTO "evaluation" VALUES ("3", "2021", "800.00");
INSERT INTO "evaluation" VALUES ("4", "2022", "2000.00");
INSERT INTO "evaluation" VALUES ("5", "2023", "0.00");
INSERT INTO "evaluation" VALUES ("6", "2022", "1100.00");
INSERT INTO "evaluation" VALUES ("7", "2023", "2500.00");
INSERT INTO "evaluation" VALUES ("8", "2021", "900.00");
INSERT INTO "shop" VALUES ("101", "Tech World", "Downtown", "Central", "500", "Michael Scott");
INSERT INTO "shop" VALUES ("102", "Fashion Hub", "Uptown", "North", "300", "Pam Beesly");
INSERT INTO "shop" VALUES ("103", "Grocery Mart", "Midtown", "Central", "1000", "Jim Halpert");
INSERT INTO "shop" VALUES ("104", "Book Nook", "Eastside", "East", "200", "Dwight Schrute");
INSERT INTO "shop" VALUES ("105", "Sports Arena", "Westside", "West", "400", "Angela Martin");
INSERT INTO "hiring" VALUES ("101", "1", "2021-06-15", "1");
INSERT INTO "hiring" VALUES ("102", "2", "2020-03-01", "0");
INSERT INTO "hiring" VALUES ("103", "3", "2022-01-10", "1");
INSERT INTO "hiring" VALUES ("104", "4", "2019-11-20", "1");
INSERT INTO "hiring" VALUES ("105", "5", "2023-02-05", "0");
INSERT INTO "hiring" VALUES ("101", "6", "2021-08-30", "1");
INSERT INTO "hiring" VALUES ("102", "7", "2018-07-12", "1");
INSERT INTO "hiring" VALUES ("103", "8", "2020-09-25", "0");