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

CREATE TABLE "Properties" (
"property_id" INT,
"property_type_code" VARCHAR(20),
"property_name" VARCHAR(80),
"property_address" VARCHAR(255),
"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 ("house", "house");
INSERT INTO "Ref_Property_Types" VALUES ("apartment", "apartment");
INSERT INTO "Ref_Property_Types" VALUES ("condo", "Condominium");
INSERT INTO "Ref_Property_Types" VALUES ("townhouse", "Townhouse");
INSERT INTO "Ref_Property_Types" VALUES ("studio", "Studio");
INSERT INTO "Properties" VALUES ("1", "house", "Sunnyvale Villa", "123 Sunny St, Sunnyvale, CA", "3");
INSERT INTO "Properties" VALUES ("2", "apartment", "Downtown Loft", "456 City Rd, Metropolis, NY", "2");
INSERT INTO "Properties" VALUES ("3", "condo", "Lakeside Condo", "789 Lakeview Dr, Lakeside, FL", "1");
INSERT INTO "Properties" VALUES ("4", "house", "Cozy Cottage", "321 Forest Ln, Woodsville, OR", "1");
INSERT INTO "Properties" VALUES ("5", "apartment", "Skyline Apartment", "654 High St, Metropolis, NY", "4");
INSERT INTO "Properties" VALUES ("6", "townhouse", "Maple Townhouse", "987 Maple Ave, Springfield, IL", "3");
INSERT INTO "Properties" VALUES ("7", "studio", "City Studio", "111 Center St, Metropolis, NY", "1");
INSERT INTO "Properties" VALUES ("8", "house", "Greenfield Estate", "222 Green Rd, Greenfield, TX", "5");
INSERT INTO "Properties" VALUES ("9", "apartment", "Riverside Apartment", "333 River Rd, Rivertown, WA", "1");
INSERT INTO "Properties" VALUES ("10", "house", "Hilltop Mansion", "444 Hill St, Hilltown, CO", "6");
INSERT INTO "Other_Available_Features" VALUES ("1", "Swimming Pool");
INSERT INTO "Other_Available_Features" VALUES ("2", "Fireplace");
INSERT INTO "Other_Available_Features" VALUES ("3", "Garage");
INSERT INTO "Other_Available_Features" VALUES ("4", "Garden");
INSERT INTO "Other_Available_Features" VALUES ("5", "Balcony");
INSERT INTO "Other_Property_Features" VALUES ("Swimming Pool");
INSERT INTO "Other_Property_Features" VALUES ("Fireplace");
INSERT INTO "Other_Property_Features" VALUES ("Garage");
INSERT INTO "Other_Property_Features" VALUES ("Garden");
INSERT INTO "Other_Property_Features" VALUES ("Balcony");