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 UNIQUE,
"feature_type_code" VARCHAR(20),
"feature_name" VARCHAR(80),
PRIMARY KEY ("feature_id"),
FOREIGN KEY ("feature_type_code") REFERENCES "Ref_Feature_Types"("feature_type_code")
);

CREATE TABLE "Other_Property_Features" (
"feature_id" INT,
"property_feature_description" VARCHAR(80),
FOREIGN KEY ("feature_id") REFERENCES "Other_Available_Features"("feature_id")
);

CREATE TABLE "Properties" (
"property_id" INT UNIQUE,
PRIMARY KEY ("property_id")
);

CREATE TABLE "Ref_Feature_Types" (
"feature_type_code" VARCHAR(20) UNIQUE,
"feature_type_name" VARCHAR(80),
PRIMARY KEY ("feature_type_code")
);





INSERT INTO "Ref_Feature_Types" VALUES ("INT", "Interior Feature");
INSERT INTO "Ref_Feature_Types" VALUES ("EXT", "Exterior Feature");
INSERT INTO "Ref_Feature_Types" VALUES ("UTIL", "Utility Feature");
INSERT INTO "Ref_Feature_Types" VALUES ("SEC", "Security Feature");
INSERT INTO "Ref_Feature_Types" VALUES ("ACC", "Accessibility Feature");
INSERT INTO "Ref_Feature_Types" VALUES ("KIT", "Kitchen Feature");
INSERT INTO "Ref_Feature_Types" VALUES ("BATH", "Bathroom Feature");
INSERT INTO "Other_Available_Features" VALUES ("1", "INT", "AirCon");
INSERT INTO "Other_Available_Features" VALUES ("2", "EXT", "Swimming Pool");
INSERT INTO "Other_Available_Features" VALUES ("3", "UTIL", "Solar Panels");
INSERT INTO "Other_Available_Features" VALUES ("4", "SEC", "Alarm System");
INSERT INTO "Other_Available_Features" VALUES ("5", "INT", "Fireplace");
INSERT INTO "Other_Available_Features" VALUES ("6", "EXT", "Garden");
INSERT INTO "Other_Available_Features" VALUES ("7", "INT", "Hardwood Floors");
INSERT INTO "Other_Available_Features" VALUES ("8", "KIT", "Dishwasher");
INSERT INTO "Other_Available_Features" VALUES ("9", "BATH", "Bathtub");
INSERT INTO "Other_Available_Features" VALUES ("10", "INT", "Central Heating");
INSERT INTO "Other_Available_Features" VALUES ("11", "INT", "Smart Home System");
INSERT INTO "Other_Available_Features" VALUES ("12", "EXT", "Balcony");
INSERT INTO "Other_Available_Features" VALUES ("13", "UTIL", "Water Heater");
INSERT INTO "Other_Available_Features" VALUES ("14", "SEC", "Video Surveillance");
INSERT INTO "Other_Available_Features" VALUES ("15", "ACC", "Wheelchair Accessible");
INSERT INTO "Other_Property_Features" VALUES ("1", "Central Air Conditioning");
INSERT INTO "Other_Property_Features" VALUES ("1", "Window Unit Air Conditioner");
INSERT INTO "Other_Property_Features" VALUES ("2", "In-ground pool");
INSERT INTO "Other_Property_Features" VALUES ("3", "Owned solar panels");
INSERT INTO "Other_Property_Features" VALUES ("4", "Monitored alarm system");
INSERT INTO "Other_Property_Features" VALUES ("5", "Wood burning fireplace");
INSERT INTO "Other_Property_Features" VALUES ("6", "Large backyard garden");
INSERT INTO "Other_Property_Features" VALUES ("7", "Original hardwood floors");
INSERT INTO "Other_Property_Features" VALUES ("8", "Stainless steel dishwasher");
INSERT INTO "Other_Property_Features" VALUES ("9", "Clawfoot bathtub");
INSERT INTO "Other_Property_Features" VALUES ("10", "Gas central heating");
INSERT INTO "Other_Property_Features" VALUES ("11", "Integrated smart home system");
INSERT INTO "Other_Property_Features" VALUES ("12", "Private balcony with city view");
INSERT INTO "Other_Property_Features" VALUES ("13", "Tankless water heater");
INSERT INTO "Other_Property_Features" VALUES ("14", "24/7 video surveillance");
INSERT INTO "Other_Property_Features" VALUES ("15", "Ramp access");
INSERT INTO "Properties" VALUES ("101");
INSERT INTO "Properties" VALUES ("102");
INSERT INTO "Properties" VALUES ("103");
INSERT INTO "Properties" VALUES ("104");
INSERT INTO "Properties" VALUES ("105");