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 ("HVAC", "Heating, Ventilation, and Air Conditioning");
INSERT INTO "Ref_Feature_Types" VALUES ("SAFETY", "Safety and Security Features");
INSERT INTO "Ref_Feature_Types" VALUES ("AMENITY", "Amenities and Extras");
INSERT INTO "Other_Available_Features" VALUES ("1", "HVAC", "AirCon", "AirCon");
INSERT INTO "Other_Available_Features" VALUES ("2", "HVAC", "Heater", "Central heating system");
INSERT INTO "Other_Available_Features" VALUES ("3", "SAFETY", "Alarm", "Burglar alarm system");
INSERT INTO "Other_Available_Features" VALUES ("4", "AMENITY", "Pool", "Outdoor swimming pool");
INSERT INTO "Other_Available_Features" VALUES ("5", "AMENITY", "Gym", "Fitness center access");
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");