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 "Other_Property_Features" (
"property_feature_description" VARCHAR(80)
);

CREATE TABLE "Properties" (
"property_id" INT UNIQUE,
"property_type_code" VARCHAR(20),
"date_on_market" DATETIME,
"property_name" VARCHAR(80),
"room_count" INT,
"agreed_selling_price" DECIMAL(19, 4),
PRIMARY KEY ("property_id"),
FOREIGN KEY ("property_type_code") REFERENCES "Ref_Property_Types"("property_type_code")
);

CREATE TABLE "Ref_Property_Types" (
"property_type_code" VARCHAR(20) UNIQUE,
"property_type_description" VARCHAR(80),
PRIMARY KEY ("property_type_code")
);





INSERT INTO "Other_Property_Features" VALUES ("Garden");
INSERT INTO "Other_Property_Features" VALUES ("Balcony");
INSERT INTO "Other_Property_Features" VALUES ("Parking");
INSERT INTO "Other_Property_Features" VALUES ("Swimming Pool");
INSERT INTO "Other_Property_Features" VALUES ("Gym");
INSERT INTO "Other_Property_Features" VALUES ("Fireplace");
INSERT INTO "Other_Property_Features" VALUES ("Waterfront");
INSERT INTO "Other_Property_Features" VALUES ("City View");
INSERT INTO "Other_Property_Features" VALUES ("Mountain View");
INSERT INTO "Other_Property_Features" VALUES ("Pet Friendly");
INSERT INTO "Ref_Property_Types" VALUES ("house", "Single-family house");
INSERT INTO "Ref_Property_Types" VALUES ("APT", "Apartment unit");
INSERT INTO "Ref_Property_Types" VALUES ("CONDO", "Condominium");
INSERT INTO "Ref_Property_Types" VALUES ("TOWNHOUSE", "Townhouse");
INSERT INTO "Ref_Property_Types" VALUES ("LAND", "Vacant land");
INSERT INTO "Ref_Property_Types" VALUES ("COMMERCIAL", "Commercial property");
INSERT INTO "Ref_Property_Types" VALUES ("STUDIO", "Studio Apartment");
INSERT INTO "Properties" VALUES ("1", "house", "2023-01-15 10:00:00", "Cozy Family Home", "4", "350000.0000");
INSERT INTO "Properties" VALUES ("2", "APT", "2023-02-01 11:30:00", "Downtown Loft", "2", "280000.0000");
INSERT INTO "Properties" VALUES ("3", "CONDO", "2023-03-10 09:00:00", "Luxury Condo", "3", "500000.0000");
INSERT INTO "Properties" VALUES ("4", "house", "2023-04-05 14:00:00", "Suburban Retreat", "5", "420000.0000");
INSERT INTO "Properties" VALUES ("5", "APT", "2023-05-20 16:00:00", "Studio Apartment", "1", "180000.0000");
INSERT INTO "Properties" VALUES ("6", "TOWNHOUSE", "2023-06-01 10:00:00", "Modern Townhouse", "3", "380000.0000");
INSERT INTO "Properties" VALUES ("7", "house", "2023-07-12 13:00:00", "Countryside Villa", "6", "750000.0000");
INSERT INTO "Properties" VALUES ("8", "APT", "2023-08-25 09:00:00", "Spacious Apartment", "4", "320000.0000");
INSERT INTO "Properties" VALUES ("9", "STUDIO", "2023-09-01 10:00:00", "Compact Studio", "1", "150000.0000");
INSERT INTO "Properties" VALUES ("10", "house", "2023-10-10 11:00:00", "Small Cottage", "1", "200000.0000");
INSERT INTO "Properties" VALUES ("11", "APT", "2023-11-05 12:00:00", "Penthouse Suite", "5", "900000.0000");
INSERT INTO "Properties" VALUES ("12", "house", "2023-12-01 14:00:00", "Grand Estate", "8", "1200000.0000");
INSERT INTO "Properties" VALUES ("13", "APT", "2024-01-01 09:00:00", "Urban Flat", "3", "295000.0000");
INSERT INTO "Properties" VALUES ("14", "CONDO", "2024-02-15 10:00:00", "Riverside Condo", "2", "400000.0000");
INSERT INTO "Properties" VALUES ("15", "house", "2024-03-20 11:00:00", "Beach House", "4", "600000.0000");