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

CREATE TABLE "Other_Property_Features" (
"property_id" INT
);

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 ("ELEC", "Electrical Features");
INSERT INTO "Ref_Feature_Types" VALUES ("HVAC", "Heating, Ventilation and Air Conditioning");
INSERT INTO "Ref_Feature_Types" VALUES ("SEC", "Security Features");
INSERT INTO "Ref_Feature_Types" VALUES ("PLMB", "Plumbing Features");
INSERT INTO "Ref_Feature_Types" VALUES ("EXT", "Exterior Features");
INSERT INTO "Other_Available_Features" VALUES ("1", "HVAC", "AirCon", "AirCon");
INSERT INTO "Other_Available_Features" VALUES ("2", "HVAC", "AirCon", "Air Conditioning System");
INSERT INTO "Other_Available_Features" VALUES ("3", "ELEC", "Generator", "Backup Generator");
INSERT INTO "Other_Available_Features" VALUES ("4", "SEC", "Alarm", "Security Alarm System");
INSERT INTO "Other_Available_Features" VALUES ("5", "PLMB", "WaterHeater", "Tankless Water Heater");
INSERT INTO "Other_Available_Features" VALUES ("6", "EXT", "Deck", "Wooden Deck");
INSERT INTO "Other_Available_Features" VALUES ("7", "HVAC", "AirCon", "Central AirCon");
INSERT INTO "Other_Available_Features" VALUES ("8", "ELEC", "SolarPanels", "Solar Panels");
INSERT INTO "Other_Available_Features" VALUES ("9", "SEC", "CCTV", "Closed Circuit TV");
INSERT INTO "Other_Available_Features" VALUES ("10", "HVAC", "HeatPump", "Heat Pump System");
INSERT INTO "Other_Property_Features" VALUES ("101");
INSERT INTO "Other_Property_Features" VALUES ("102");
INSERT INTO "Other_Property_Features" VALUES ("103");
INSERT INTO "Other_Property_Features" VALUES ("104");
INSERT INTO "Other_Property_Features" VALUES ("105");