CREATE TYPE "public"."energy_element_type" AS ENUM('roof', 'wall', 'floor', 'main_heating', 'window', 'lighting', 'hot_water', 'secondary_heating', 'main_heating_controls');--> statement-breakpoint
CREATE TABLE "epc_building_part" (
	"id" bigserial PRIMARY KEY NOT NULL,
	"epc_property_id" bigint NOT NULL,
	"identifier" text NOT NULL,
	"construction_age_band" text NOT NULL,
	"wall_construction" text NOT NULL,
	"wall_insulation_type" text NOT NULL,
	"wall_thickness_measured" boolean NOT NULL,
	"party_wall_construction" text NOT NULL,
	"building_part_number" integer,
	"wall_dry_lined" boolean,
	"wall_thickness_mm" integer,
	"wall_insulation_thickness" text,
	"floor_heat_loss" integer,
	"floor_insulation_thickness" text,
	"flat_roof_insulation_thickness" text,
	"floor_type" text,
	"floor_construction_type" text,
	"floor_insulation_type_str" text,
	"floor_u_value_known" boolean,
	"roof_construction" integer,
	"roof_insulation_location" text,
	"roof_insulation_thickness" text,
	"room_in_roof_floor_area" real,
	"room_in_roof_construction_age_band" text,
	"alt_wall_1_area" real,
	"alt_wall_1_dry_lined" text,
	"alt_wall_1_construction" integer,
	"alt_wall_1_insulation_type" integer,
	"alt_wall_1_thickness_measured" text,
	"alt_wall_1_insulation_thickness" text,
	"alt_wall_2_area" real,
	"alt_wall_2_dry_lined" text,
	"alt_wall_2_construction" integer,
	"alt_wall_2_insulation_type" integer,
	"alt_wall_2_thickness_measured" text,
	"alt_wall_2_insulation_thickness" text
);
--> statement-breakpoint
CREATE TABLE "epc_energy_element" (
	"id" bigserial PRIMARY KEY NOT NULL,
	"epc_property_id" bigint NOT NULL,
	"element_type" "energy_element_type" NOT NULL,
	"description" text NOT NULL,
	"energy_efficiency_rating" integer NOT NULL,
	"environmental_efficiency_rating" integer NOT NULL
);
--> statement-breakpoint
CREATE TABLE "epc_flat_details" (
	"id" bigserial PRIMARY KEY NOT NULL,
	"epc_property_id" bigint NOT NULL,
	"level" integer NOT NULL,
	"top_storey" text NOT NULL,
	"flat_location" integer NOT NULL,
	"heat_loss_corridor" integer NOT NULL,
	"storey_count" integer,
	"unheated_corridor_length_m" integer,
	CONSTRAINT "epc_flat_details_epc_property_id_unique" UNIQUE("epc_property_id")
);
--> statement-breakpoint
CREATE TABLE "epc_floor_dimension" (
	"id" bigserial PRIMARY KEY NOT NULL,
	"epc_building_part_id" bigint NOT NULL,
	"floor" integer,
	"room_height_m" real NOT NULL,
	"total_floor_area_m2" real NOT NULL,
	"party_wall_length_m" real NOT NULL,
	"heat_loss_perimeter_m" real NOT NULL,
	"floor_insulation" integer,
	"floor_construction" integer
);
--> statement-breakpoint
CREATE TABLE "epc_main_heating_detail" (
	"id" bigserial PRIMARY KEY NOT NULL,
	"epc_property_id" bigint NOT NULL,
	"has_fghrs" boolean NOT NULL,
	"main_fuel_type" text NOT NULL,
	"heat_emitter_type" text NOT NULL,
	"emitter_temperature" text NOT NULL,
	"main_heating_control" text NOT NULL,
	"fan_flue_present" boolean,
	"boiler_flue_type" integer,
	"boiler_ignition_type" integer,
	"central_heating_pump_age" integer,
	"central_heating_pump_age_str" text,
	"main_heating_index_number" integer,
	"sap_main_heating_code" integer,
	"main_heating_number" integer,
	"main_heating_category" integer,
	"main_heating_fraction" integer,
	"main_heating_data_source" integer,
	"condensing" boolean,
	"weather_compensator" boolean
);
--> statement-breakpoint
CREATE TABLE "epc_property" (
	"id" bigserial PRIMARY KEY NOT NULL,
	"property_id" bigint NOT NULL,
	"portfolio_id" bigint NOT NULL,
	"uprn" bigint,
	"uprn_source" text,
	"report_reference" text,
	"report_type" text,
	"assessment_type" text,
	"sap_version" real,
	"schema_type" text,
	"schema_versions_original" text,
	"status" text,
	"calculation_software_version" text,
	"address_line_1" text,
	"address_line_2" text,
	"post_town" text,
	"postcode" text,
	"region_code" text,
	"country_code" text,
	"language_code" text,
	"dwelling_type" text NOT NULL,
	"property_type" text,
	"built_form" text,
	"tenure" text NOT NULL,
	"transaction_type" text NOT NULL,
	"inspection_date" timestamp NOT NULL,
	"completion_date" timestamp,
	"registration_date" timestamp,
	"total_floor_area_m2" real NOT NULL,
	"measurement_type" integer,
	"solar_water_heating" boolean NOT NULL,
	"has_hot_water_cylinder" boolean NOT NULL,
	"has_fixed_air_conditioning" boolean NOT NULL,
	"has_conservatory" boolean,
	"has_heated_separate_conservatory" boolean,
	"conservatory_type" integer,
	"door_count" integer NOT NULL,
	"wet_rooms_count" integer NOT NULL,
	"extensions_count" integer NOT NULL,
	"heated_rooms_count" integer NOT NULL,
	"open_chimneys_count" integer NOT NULL,
	"habitable_rooms_count" integer NOT NULL,
	"insulated_door_count" integer NOT NULL,
	"cfl_fixed_lighting_bulbs_count" integer NOT NULL,
	"led_fixed_lighting_bulbs_count" integer NOT NULL,
	"incandescent_fixed_lighting_bulbs_count" integer NOT NULL,
	"blocked_chimneys_count" integer,
	"draughtproofed_door_count" integer,
	"energy_rating_average" integer,
	"low_energy_fixed_lighting_bulbs_count" integer,
	"fixed_lighting_outlets_count" integer,
	"low_energy_fixed_lighting_outlets_count" integer,
	"number_of_storeys" integer,
	"any_unheated_rooms" boolean,
	"hydro" boolean,
	"photovoltaic_array" boolean,
	"waste_water_heat_recovery" text,
	"pressure_test" integer,
	"pressure_test_certificate_number" integer,
	"percent_draughtproofed" integer,
	"insulated_door_u_value" real,
	"multiple_glazed_proportion" integer,
	"windows_transmission_u_value" real,
	"windows_transmission_data_source" integer,
	"windows_transmission_solar_transmittance" real,
	"energy_mains_gas" boolean NOT NULL,
	"energy_meter_type" text NOT NULL,
	"energy_pv_battery_count" integer NOT NULL,
	"energy_wind_turbines_count" integer NOT NULL,
	"energy_gas_smart_meter_present" boolean NOT NULL,
	"energy_is_dwelling_export_capable" boolean NOT NULL,
	"energy_wind_turbines_terrain_type" text NOT NULL,
	"energy_electricity_smart_meter_present" boolean NOT NULL,
	"energy_pv_connection" text,
	"energy_pv_percent_roof_area" integer,
	"energy_pv_battery_capacity" real,
	"energy_wind_turbine_hub_height" real,
	"energy_wind_turbine_rotor_diameter" real,
	"heating_cylinder_size" text,
	"heating_water_heating_code" integer,
	"heating_water_heating_fuel" integer,
	"heating_immersion_heating_type" text,
	"heating_cylinder_insulation_type" text,
	"heating_cylinder_thermostat" text,
	"heating_secondary_fuel_type" integer,
	"heating_secondary_heating_type" text,
	"heating_cylinder_insulation_thickness_mm" integer,
	"heating_wwhrs_index_number_1" integer,
	"heating_wwhrs_index_number_2" integer,
	"heating_shower_outlet_type" text,
	"heating_shower_wwhrs" integer,
	"ventilation_type" text,
	"ventilation_draught_lobby" boolean,
	"ventilation_pressure_test" text,
	"ventilation_open_flues_count" integer,
	"ventilation_closed_flues_count" integer,
	"ventilation_boiler_flues_count" integer,
	"ventilation_other_flues_count" integer,
	"ventilation_extract_fans_count" integer,
	"ventilation_passive_vents_count" integer,
	"ventilation_flueless_gas_fires_count" integer,
	"ventilation_in_pcdf_database" boolean,
	"mechanical_ventilation" integer,
	"mechanical_vent_duct_type" integer,
	"mechanical_vent_duct_placement" integer,
	"mechanical_vent_duct_insulation" integer,
	"mechanical_ventilation_index_number" integer,
	"mechanical_vent_measured_installation" text
);
--> statement-breakpoint
CREATE TABLE "epc_property_energy_performance" (
	"id" bigserial PRIMARY KEY NOT NULL,
	"epc_property_id" bigint NOT NULL,
	"energy_rating_current" integer,
	"energy_consumption_current" integer,
	"environmental_impact_current" integer,
	"heating_cost_current" real,
	"lighting_cost_current" real,
	"hot_water_cost_current" real,
	"co2_emissions_current" real,
	"co2_emissions_current_per_floor_area" integer,
	"current_energy_efficiency_band" text,
	"energy_rating_potential" real,
	"energy_consumption_potential" integer,
	"environmental_impact_potential" integer,
	"heating_cost_potential" real,
	"lighting_cost_potential" real,
	"hot_water_cost_potential" real,
	"co2_emissions_potential" real,
	"potential_energy_efficiency_band" text,
	CONSTRAINT "epc_property_energy_performance_epc_property_id_unique" UNIQUE("epc_property_id")
);
--> statement-breakpoint
CREATE TABLE "epc_window" (
	"id" bigserial PRIMARY KEY NOT NULL,
	"epc_property_id" bigint NOT NULL,
	"pvc_frame" text NOT NULL,
	"glazing_gap" text NOT NULL,
	"orientation" text NOT NULL,
	"window_type" text NOT NULL,
	"glazing_type" text NOT NULL,
	"window_width" real NOT NULL,
	"window_height" real NOT NULL,
	"draught_proofed" boolean NOT NULL,
	"window_location" text NOT NULL,
	"window_wall_type" text NOT NULL,
	"permanent_shutters_present" boolean NOT NULL,
	"frame_factor" real,
	"permanent_shutters_insulated" text,
	"transmission_u_value" real,
	"transmission_data_source" integer,
	"transmission_solar_transmittance" real
);
--> statement-breakpoint
ALTER TABLE "epc_building_part" ADD CONSTRAINT "epc_building_part_epc_property_id_epc_property_id_fk" FOREIGN KEY ("epc_property_id") REFERENCES "public"."epc_property"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "epc_energy_element" ADD CONSTRAINT "epc_energy_element_epc_property_id_epc_property_id_fk" FOREIGN KEY ("epc_property_id") REFERENCES "public"."epc_property"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "epc_flat_details" ADD CONSTRAINT "epc_flat_details_epc_property_id_epc_property_id_fk" FOREIGN KEY ("epc_property_id") REFERENCES "public"."epc_property"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "epc_floor_dimension" ADD CONSTRAINT "epc_floor_dimension_epc_building_part_id_epc_building_part_id_fk" FOREIGN KEY ("epc_building_part_id") REFERENCES "public"."epc_building_part"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "epc_main_heating_detail" ADD CONSTRAINT "epc_main_heating_detail_epc_property_id_epc_property_id_fk" FOREIGN KEY ("epc_property_id") REFERENCES "public"."epc_property"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "epc_property" ADD CONSTRAINT "epc_property_property_id_property_id_fk" FOREIGN KEY ("property_id") REFERENCES "public"."property"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "epc_property" ADD CONSTRAINT "epc_property_portfolio_id_portfolio_id_fk" FOREIGN KEY ("portfolio_id") REFERENCES "public"."portfolio"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "epc_property_energy_performance" ADD CONSTRAINT "epc_property_energy_performance_epc_property_id_epc_property_id_fk" FOREIGN KEY ("epc_property_id") REFERENCES "public"."epc_property"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "epc_window" ADD CONSTRAINT "epc_window_epc_property_id_epc_property_id_fk" FOREIGN KEY ("epc_property_id") REFERENCES "public"."epc_property"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
CREATE UNIQUE INDEX "uq_epc_property_property_portfolio" ON "epc_property" USING btree ("property_id","portfolio_id");