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"),
FOREIGN KEY ("Employee_ID") REFERENCES "employee"("Employee_ID")
);

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 Smith", "30", "New York");
INSERT INTO "employee" VALUES ("2", "Bob Johnson", "25", "Los Angeles");
INSERT INTO "employee" VALUES ("3", "Charlie Brown", "35", "Chicago");
INSERT INTO "employee" VALUES ("4", "Diana Prince", "28", "New York");
INSERT INTO "employee" VALUES ("5", "Eve Adams", "40", "Los Angeles");
INSERT INTO "employee" VALUES ("6", "Frank White", "22", "Chicago");
INSERT INTO "employee" VALUES ("7", "Grace Lee", "33", "New York");
INSERT INTO "employee" VALUES ("8", "Henry King", "29", "Los Angeles");
INSERT INTO "employee" VALUES ("9", "Ivy Green", "31", "Chicago");
INSERT INTO "employee" VALUES ("10", "Jack Black", "27", "New York");
INSERT INTO "shop" VALUES ("101", "Tech Emporium", "Downtown", "Manhattan", "500", "Mr. Big");
INSERT INTO "shop" VALUES ("102", "Gadget Hub", "Westside", "Hollywood", "300", "Ms. Smart");
INSERT INTO "shop" VALUES ("103", "Electronic World", "Northside", "Lincoln Park", "700", "Dr. Genius");
INSERT INTO "shop" VALUES ("104", "Future Tech", "Midtown", "Midtown East", "450", "Mrs. Bright");
INSERT INTO "shop" VALUES ("105", "Digital Dreams", "South Bay", "Santa Monica", "600", "Mr. Vision");
INSERT INTO "evaluation" VALUES ("1", "2022", "1500.0");
INSERT INTO "evaluation" VALUES ("1", "2023", "2000.0");
INSERT INTO "evaluation" VALUES ("2", "2023", "1200.0");
INSERT INTO "evaluation" VALUES ("3", "2022", "1800.0");
INSERT INTO "evaluation" VALUES ("4", "2023", "1000.0");
INSERT INTO "evaluation" VALUES ("5", "2022", "2500.0");
INSERT INTO "evaluation" VALUES ("6", "2023", "900.0");
INSERT INTO "evaluation" VALUES ("7", "2022", "1700.0");
INSERT INTO "evaluation" VALUES ("8", "2023", "1300.0");
INSERT INTO "evaluation" VALUES ("9", "2022", "1600.0");
INSERT INTO "evaluation" VALUES ("10", "2023", "1100.0");
INSERT INTO "hiring" VALUES ("101", "1", "2021-01-15", "1");
INSERT INTO "hiring" VALUES ("102", "2", "2022-03-01", "1");
INSERT INTO "hiring" VALUES ("103", "3", "2020-06-20", "1");
INSERT INTO "hiring" VALUES ("101", "4", "2023-02-10", "0");
INSERT INTO "hiring" VALUES ("102", "5", "2019-09-01", "1");
INSERT INTO "hiring" VALUES ("103", "6", "2023-07-01", "0");
INSERT INTO "hiring" VALUES ("104", "7", "2021-11-01", "1");
INSERT INTO "hiring" VALUES ("105", "8", "2022-05-15", "1");
INSERT INTO "hiring" VALUES ("104", "9", "2020-03-01", "0");
INSERT INTO "hiring" VALUES ("105", "10", "2023-01-01", "1");