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_Available_Features" (
"feature_id" INT,
"feature_name" VARCHAR(80),
PRIMARY KEY ("feature_id")
);

CREATE TABLE "Properties" (
"property_id" INT,
"property_type_code" VARCHAR(20),
"date_on_market" DATETIME,
"property_name" VARCHAR(80),
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_Available_Features" VALUES ("1", "Swimming Pool");
INSERT INTO "Other_Available_Features" VALUES ("2", "Gym");
INSERT INTO "Other_Available_Features" VALUES ("3", "Garden");
INSERT INTO "Other_Available_Features" VALUES ("4", "Balcony");
INSERT INTO "Other_Available_Features" VALUES ("5", "Parking");
INSERT INTO "Other_Available_Features" VALUES ("6", "Security System");
INSERT INTO "Other_Available_Features" VALUES ("7", "Pet Friendly");
INSERT INTO "Other_Available_Features" VALUES ("8", "Furnished");
INSERT INTO "Other_Available_Features" VALUES ("9", "Air Conditioning");
INSERT INTO "Other_Available_Features" VALUES ("10", "Dishwasher");
INSERT INTO "Ref_Property_Types" VALUES ("APT", "Apartment");
INSERT INTO "Ref_Property_Types" VALUES ("HOUSE", "Single Family House");
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 ("COMM", "Commercial Property");
INSERT INTO "Ref_Property_Types" VALUES ("DUPLEX", "Duplex");
INSERT INTO "Ref_Property_Types" VALUES ("STUDIO", "Studio Apartment");
INSERT INTO "Ref_Property_Types" VALUES ("LOFT", "Loft Apartment");
INSERT INTO "Ref_Property_Types" VALUES ("VILLA", "Villa");
INSERT INTO "Properties" VALUES ("1", "APT", "2023-01-15 10:00:00", "Sunny Apartment");
INSERT INTO "Properties" VALUES ("2", "HOUSE", "2023-01-20 11:30:00", "Family Home with Garden");
INSERT INTO "Properties" VALUES ("3", "CONDO", "2023-02-01 09:00:00", "Downtown Condo");
INSERT INTO "Properties" VALUES ("4", "TOWNHOUSE", "2023-02-05 14:00:00", "Modern Townhouse");
INSERT INTO "Properties" VALUES ("5", "LAND", "2023-02-10 16:00:00", "Large Plot for Development");
INSERT INTO "Properties" VALUES ("6", "COMM", "2023-02-12 08:00:00", "Office Space");
INSERT INTO "Properties" VALUES ("7", "APT", "2023-03-01 10:00:00", "Cozy Studio");
INSERT INTO "Properties" VALUES ("8", "HOUSE", "2023-03-05 12:00:00", "House with Pool");
INSERT INTO "Properties" VALUES ("9", "DUPLEX", "2023-03-10 15:00:00", "Investment Duplex");
INSERT INTO "Properties" VALUES ("10", "CONDO", "2023-03-15 11:00:00", "Luxury Condo");
INSERT INTO "Properties" VALUES ("11", "STUDIO", "2023-04-01 09:00:00", "Compact Studio");
INSERT INTO "Properties" VALUES ("12", "LOFT", "2023-04-05 13:00:00", "Spacious Loft");
INSERT INTO "Properties" VALUES ("13", "VILLA", "2023-04-10 10:00:00", "Seaside Villa");
INSERT INTO "Properties" VALUES ("14", "HOUSE", "2023-04-15 14:00:00", "Rural Retreat");
INSERT INTO "Properties" VALUES ("15", "APT", "2023-04-20 16:00:00", "City View Apartment");
INSERT INTO "Properties" VALUES ("16", "COMM", "2023-05-01 09:00:00", "Retail Storefront");
INSERT INTO "Properties" VALUES ("17", "LAND", "2023-05-05 11:00:00", "Agricultural Land");
INSERT INTO "Properties" VALUES ("18", "TOWNHOUSE", "2023-05-10 13:00:00", "End Unit Townhouse");
INSERT INTO "Properties" VALUES ("19", "CONDO", "2023-05-15 15:00:00", "Penthouse Condo");
INSERT INTO "Properties" VALUES ("20", "HOUSE", "2023-05-20 10:00:00", "Historic Home");