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_type_code" VARCHAR(20),
PRIMARY KEY ("feature_id")
);

CREATE TABLE "Properties" (
"property_id" INT,
"property_type_code" VARCHAR(20),
"property_name" VARCHAR(80),
"room_count" INT,
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 "Ref_Property_Types" VALUES ("APT", "Apartment");
INSERT INTO "Ref_Property_Types" VALUES ("HSE", "House");
INSERT INTO "Ref_Property_Types" VALUES ("CON", "Condominium");
INSERT INTO "Ref_Property_Types" VALUES ("TWH", "Townhouse");
INSERT INTO "Ref_Property_Types" VALUES ("VIL", "Villa");
INSERT INTO "Properties" VALUES ("101", "APT", "Sunset View", "3");
INSERT INTO "Properties" VALUES ("102", "HSE", "Maple Residency", "5");
INSERT INTO "Properties" VALUES ("103", "CON", "Ocean Breeze", "2");
INSERT INTO "Properties" VALUES ("104", "TWH", "Greenwood Townhomes", "4");
INSERT INTO "Properties" VALUES ("105", "VIL", "Lakeside Villa", "6");
INSERT INTO "Properties" VALUES ("106", "APT", "City Center Loft", "1");
INSERT INTO "Properties" VALUES ("107", "HSE", "Hilltop Mansion", "8");
INSERT INTO "Properties" VALUES ("108", "CON", "Downtown Condo", "3");
INSERT INTO "Properties" VALUES ("109", "TWH", "Parkside Townhouse", "3");
INSERT INTO "Properties" VALUES ("110", "VIL", "Sunrise Villa", "5");
INSERT INTO "Other_Available_Features" VALUES ("1", "APT");
INSERT INTO "Other_Available_Features" VALUES ("2", "HSE");
INSERT INTO "Other_Available_Features" VALUES ("3", "CON");
INSERT INTO "Other_Available_Features" VALUES ("4", "TWH");
INSERT INTO "Other_Available_Features" VALUES ("5", "VIL");
INSERT INTO "Other_Available_Features" VALUES ("6", "APT");
INSERT INTO "Other_Available_Features" VALUES ("7", "HSE");
INSERT INTO "Other_Available_Features" VALUES ("8", "CON");