-- Lookup table for Dwellings' Occupancy Status 
CREATE TABLE dwellings_occupancy_status ( id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, status VARCHAR(50) NOT NULL );

-- Lookup table for Water Distribution Points' Operational Status 
CREATE TABLE water_distribution_operational_status ( id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, status VARCHAR(50) NOT NULL );

-- Lookup table for Educational Facilities' Facility Type 
CREATE TABLE educational_facilities_facility_type ( id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, type VARCHAR(50) NOT NULL ); 

-- Add foreign key constraint to Dwellings table
ALTER TABLE dwellings
ADD COLUMN occupancy_status_id INTEGER,
ADD CONSTRAINT fk_dwellings_occupancy_status
  FOREIGN KEY (occupancy_status_id)
  REFERENCES dwellings_occupancy_status (id);

-- Add foreign key constraint to Water Distribution Points table
ALTER TABLE water_distribution_points
ADD COLUMN operational_status_id INTEGER,
ADD CONSTRAINT fk_water_distribution_operational_status
  FOREIGN KEY (operational_status_id)
  REFERENCES water_distribution_operational_status (id);

-- Add foreign key constraint to Educational Facilities table
ALTER TABLE educational_facilities
ADD COLUMN facility_type_id INTEGER,
ADD CONSTRAINT fk_educational_facilities_facility_type
  FOREIGN KEY (facility_type_id)
  REFERENCES educational_facilities_facility_type (id);

-- Populate Dwellings' Occupancy Status lookup table 
INSERT INTO dwellings_occupancy_status (status) VALUES ('Occupied'), ('Vacant'), ('Under Construction');

-- Populate Water Distribution Points' Operational Status lookup table 
INSERT INTO water_distribution_operational_status (status) VALUES ('Active'), ('Temporarily Closed');

-- Populate Educational Facilities' Facility Type lookup table 
INSERT INTO educational_facilities_facility_type (type) VALUES ('School'), ('Vocational Training Center');