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 Features");
INSERT INTO "Ref_Feature_Types" VALUES ("EXT", "Exterior Features");
INSERT INTO "Ref_Feature_Types" VALUES ("KIT", "Kitchen Features");
INSERT INTO "Ref_Feature_Types" VALUES ("BATH", "Bathroom Features");
INSERT INTO "Ref_Feature_Types" VALUES ("UTIL", "Utility Features");
INSERT INTO "Ref_Feature_Types" VALUES ("SEC", "Security Features");
INSERT INTO "Ref_Feature_Types" VALUES ("ACC", "Accessibility Features");
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", "KIT", "Dishwasher");
INSERT INTO "Other_Available_Features" VALUES ("4", "BATH", "Jacuzzi");
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", "UTIL", "Washer/Dryer Hookup");
INSERT INTO "Other_Available_Features" VALUES ("8", "SEC", "Alarm System");
INSERT INTO "Other_Available_Features" VALUES ("9", "INT", "Hardwood Floors");
INSERT INTO "Other_Available_Features" VALUES ("10", "ACC", "Wheelchair Accessible");
INSERT INTO "Other_Available_Features" VALUES ("11", "INT", "Central Heating");
INSERT INTO "Other_Available_Features" VALUES ("12", "EXT", "Balcony");
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");
INSERT INTO "Other_Property_Features" VALUES ("1", "Central air conditioning system");
INSERT INTO "Other_Property_Features" VALUES ("2", "Large outdoor swimming pool");
INSERT INTO "Other_Property_Features" VALUES ("3", "Stainless steel dishwasher");
INSERT INTO "Other_Property_Features" VALUES ("5", "Wood burning fireplace in living room");
INSERT INTO "Other_Property_Features" VALUES ("6", "Manicured garden with patio");
INSERT INTO "Other_Property_Features" VALUES ("1", "Window unit air conditioner");
INSERT INTO "Other_Property_Features" VALUES ("9", "Original hardwood floors throughout");
INSERT INTO "Other_Property_Features" VALUES ("11", "Gas central heating");