CREATE TYPE "public"."rebaseline_reason" AS ENUM('none', 'pre_sap10', 'physical_state_changed', 'both');--> statement-breakpoint
ALTER TYPE "public"."file_type" ADD VALUE IF NOT EXISTS 'other';--> statement-breakpoint
CREATE TABLE "epc_renewable_heat_incentive" (
	"id" bigserial PRIMARY KEY NOT NULL,
	"epc_property_id" bigint NOT NULL,
	"space_heating_kwh" real NOT NULL,
	"water_heating_kwh" real NOT NULL,
	"impact_of_loft_insulation_kwh" real,
	"impact_of_cavity_insulation_kwh" real,
	"impact_of_solid_wall_insulation_kwh" real,
	CONSTRAINT "epc_renewable_heat_incentive_epc_property_id_unique" UNIQUE("epc_property_id")
);
--> statement-breakpoint
CREATE TABLE "property_baseline_performance" (
	"id" bigserial PRIMARY KEY NOT NULL,
	"property_id" bigint NOT NULL,
	"lodged_sap_score" integer NOT NULL,
	"lodged_epc_band" "epc" NOT NULL,
	"lodged_co2_emissions_t_per_yr" real NOT NULL,
	"lodged_primary_energy_intensity_kwh_per_m2_yr" integer NOT NULL,
	"effective_sap_score" integer NOT NULL,
	"effective_epc_band" "epc" NOT NULL,
	"effective_co2_emissions_t_per_yr" real NOT NULL,
	"effective_primary_energy_intensity_kwh_per_m2_yr" integer NOT NULL,
	"rebaseline_reason" "rebaseline_reason" NOT NULL,
	"space_heating_kwh" real NOT NULL,
	"water_heating_kwh" real NOT NULL,
	"fuel_rates_period" text,
	"heating_kwh" real,
	"heating_cost_gbp" real,
	"hot_water_kwh" real,
	"hot_water_cost_gbp" real,
	"lighting_kwh" real,
	"lighting_cost_gbp" real,
	"appliances_kwh" real,
	"appliances_cost_gbp" real,
	"cooking_kwh" real,
	"cooking_cost_gbp" real,
	"pumps_fans_kwh" real,
	"pumps_fans_cost_gbp" real,
	"cooling_kwh" real,
	"cooling_cost_gbp" real,
	"standing_charges_gbp" real,
	"seg_credit_gbp" real,
	"total_annual_bill_gbp" real,
	CONSTRAINT "property_baseline_performance_property_id_unique" UNIQUE("property_id")
);
--> statement-breakpoint
ALTER TABLE "epc_building_part" ALTER COLUMN "wall_construction"
  SET DATA TYPE jsonb
  USING (
    CASE
      WHEN "wall_construction" IS NULL THEN NULL
      WHEN "wall_construction" ~ '^-?[0-9]+$' THEN "wall_construction"::jsonb
      ELSE to_json("wall_construction")::jsonb
    END
  );--> statement-breakpoint
ALTER TABLE "epc_building_part" ALTER COLUMN "wall_insulation_type"
  SET DATA TYPE jsonb
  USING (
    CASE
      WHEN "wall_insulation_type" IS NULL THEN NULL
      WHEN "wall_insulation_type" ~ '^-?[0-9]+$' THEN "wall_insulation_type"::jsonb
      ELSE to_json("wall_insulation_type")::jsonb
    END
  );--> statement-breakpoint
ALTER TABLE "epc_building_part" ALTER COLUMN "party_wall_construction"
  SET DATA TYPE jsonb
  USING (
    CASE
      WHEN "party_wall_construction" IS NULL THEN NULL
      WHEN "party_wall_construction" ~ '^-?[0-9]+$' THEN "party_wall_construction"::jsonb
      ELSE to_json("party_wall_construction")::jsonb
    END
  );--> statement-breakpoint
ALTER TABLE "epc_building_part" ALTER COLUMN "party_wall_construction" DROP NOT NULL;--> statement-breakpoint
ALTER TABLE "epc_building_part" ALTER COLUMN "flat_roof_insulation_thickness"
  SET DATA TYPE jsonb
  USING (
    CASE
      WHEN "flat_roof_insulation_thickness" IS NULL THEN NULL
      WHEN "flat_roof_insulation_thickness" ~ '^-?[0-9]+$' THEN "flat_roof_insulation_thickness"::jsonb
      ELSE to_json("flat_roof_insulation_thickness")::jsonb
    END
  );--> statement-breakpoint
ALTER TABLE "epc_building_part" ALTER COLUMN "roof_insulation_location"
  SET DATA TYPE jsonb
  USING (
    CASE
      WHEN "roof_insulation_location" IS NULL THEN NULL
      WHEN "roof_insulation_location" ~ '^-?[0-9]+$' THEN "roof_insulation_location"::jsonb
      ELSE to_json("roof_insulation_location")::jsonb
    END
  );--> statement-breakpoint
ALTER TABLE "epc_building_part" ALTER COLUMN "roof_insulation_thickness"
  SET DATA TYPE jsonb
  USING (
    CASE
      WHEN "roof_insulation_thickness" IS NULL THEN NULL
      WHEN "roof_insulation_thickness" ~ '^-?[0-9]+$' THEN "roof_insulation_thickness"::jsonb
      ELSE to_json("roof_insulation_thickness")::jsonb
    END
  );--> statement-breakpoint
ALTER TABLE "epc_main_heating_detail" ALTER COLUMN "main_fuel_type"
  SET DATA TYPE jsonb
  USING (
    CASE
      WHEN "main_fuel_type" IS NULL THEN NULL
      WHEN "main_fuel_type" ~ '^-?[0-9]+$' THEN "main_fuel_type"::jsonb
      ELSE to_json("main_fuel_type")::jsonb
    END
  );--> statement-breakpoint
ALTER TABLE "epc_main_heating_detail" ALTER COLUMN "heat_emitter_type"
  SET DATA TYPE jsonb
  USING (
    CASE
      WHEN "heat_emitter_type" IS NULL THEN NULL
      WHEN "heat_emitter_type" ~ '^-?[0-9]+$' THEN "heat_emitter_type"::jsonb
      ELSE to_json("heat_emitter_type")::jsonb
    END
  );--> statement-breakpoint
ALTER TABLE "epc_main_heating_detail" ALTER COLUMN "emitter_temperature"
  SET DATA TYPE jsonb
  USING (
    CASE
      WHEN "emitter_temperature" IS NULL THEN NULL
      WHEN "emitter_temperature" ~ '^-?[0-9]+$' THEN "emitter_temperature"::jsonb
      ELSE to_json("emitter_temperature")::jsonb
    END
  );--> statement-breakpoint
ALTER TABLE "epc_main_heating_detail" ALTER COLUMN "main_heating_control"
  SET DATA TYPE jsonb
  USING (
    CASE
      WHEN "main_heating_control" IS NULL THEN NULL
      WHEN "main_heating_control" ~ '^-?[0-9]+$' THEN "main_heating_control"::jsonb
      ELSE to_json("main_heating_control")::jsonb
    END
  );--> statement-breakpoint
ALTER TABLE "epc_property" ALTER COLUMN "energy_pv_connection"
  SET DATA TYPE jsonb
  USING (
    CASE
      WHEN "energy_pv_connection" IS NULL THEN NULL
      WHEN "energy_pv_connection" ~ '^-?[0-9]+$' THEN "energy_pv_connection"::jsonb
      ELSE to_json("energy_pv_connection")::jsonb
    END
  );--> statement-breakpoint
ALTER TABLE "epc_property" ALTER COLUMN "heating_cylinder_size"
  SET DATA TYPE jsonb
  USING (
    CASE
      WHEN "heating_cylinder_size" IS NULL THEN NULL
      WHEN "heating_cylinder_size" ~ '^-?[0-9]+$' THEN "heating_cylinder_size"::jsonb
      ELSE to_json("heating_cylinder_size")::jsonb
    END
  );--> statement-breakpoint
ALTER TABLE "epc_property" ALTER COLUMN "heating_immersion_heating_type"
  SET DATA TYPE jsonb
  USING (
    CASE
      WHEN "heating_immersion_heating_type" IS NULL THEN NULL
      WHEN "heating_immersion_heating_type" ~ '^-?[0-9]+$' THEN "heating_immersion_heating_type"::jsonb
      ELSE to_json("heating_immersion_heating_type")::jsonb
    END
  );--> statement-breakpoint
ALTER TABLE "epc_property" ALTER COLUMN "heating_cylinder_insulation_type"
  SET DATA TYPE jsonb
  USING (
    CASE
      WHEN "heating_cylinder_insulation_type" IS NULL THEN NULL
      WHEN "heating_cylinder_insulation_type" ~ '^-?[0-9]+$' THEN "heating_cylinder_insulation_type"::jsonb
      ELSE to_json("heating_cylinder_insulation_type")::jsonb
    END
  );--> statement-breakpoint
ALTER TABLE "epc_property" ALTER COLUMN "heating_secondary_heating_type"
  SET DATA TYPE jsonb
  USING (
    CASE
      WHEN "heating_secondary_heating_type" IS NULL THEN NULL
      WHEN "heating_secondary_heating_type" ~ '^-?[0-9]+$' THEN "heating_secondary_heating_type"::jsonb
      ELSE to_json("heating_secondary_heating_type")::jsonb
    END
  );--> statement-breakpoint
ALTER TABLE "epc_property" ALTER COLUMN "heating_shower_outlet_type"
  SET DATA TYPE jsonb
  USING (
    CASE
      WHEN "heating_shower_outlet_type" IS NULL THEN NULL
      WHEN "heating_shower_outlet_type" ~ '^-?[0-9]+$' THEN "heating_shower_outlet_type"::jsonb
      ELSE to_json("heating_shower_outlet_type")::jsonb
    END
  );--> statement-breakpoint
ALTER TABLE "epc_window" ALTER COLUMN "glazing_gap"
  SET DATA TYPE jsonb
  USING (
    CASE
      WHEN "glazing_gap" IS NULL THEN NULL
      WHEN "glazing_gap" ~ '^-?[0-9]+$' THEN "glazing_gap"::jsonb
      ELSE to_json("glazing_gap")::jsonb
    END
  );--> statement-breakpoint
ALTER TABLE "epc_window" ALTER COLUMN "orientation"
  SET DATA TYPE jsonb
  USING (
    CASE
      WHEN "orientation" IS NULL THEN NULL
      WHEN "orientation" ~ '^-?[0-9]+$' THEN "orientation"::jsonb
      ELSE to_json("orientation")::jsonb
    END
  );--> statement-breakpoint
ALTER TABLE "epc_window" ALTER COLUMN "window_type"
  SET DATA TYPE jsonb
  USING (
    CASE
      WHEN "window_type" IS NULL THEN NULL
      WHEN "window_type" ~ '^-?[0-9]+$' THEN "window_type"::jsonb
      ELSE to_json("window_type")::jsonb
    END
  );--> statement-breakpoint
ALTER TABLE "epc_window" ALTER COLUMN "glazing_type"
  SET DATA TYPE jsonb
  USING (
    CASE
      WHEN "glazing_type" IS NULL THEN NULL
      WHEN "glazing_type" ~ '^-?[0-9]+$' THEN "glazing_type"::jsonb
      ELSE to_json("glazing_type")::jsonb
    END
  );--> statement-breakpoint
ALTER TABLE "epc_window" ALTER COLUMN "draught_proofed"
  SET DATA TYPE jsonb
  USING to_json("draught_proofed")::jsonb;--> statement-breakpoint
ALTER TABLE "epc_window" ALTER COLUMN "window_location"
  SET DATA TYPE jsonb
  USING (
    CASE
      WHEN "window_location" IS NULL THEN NULL
      WHEN "window_location" ~ '^-?[0-9]+$' THEN "window_location"::jsonb
      ELSE to_json("window_location")::jsonb
    END
  );--> statement-breakpoint
ALTER TABLE "epc_window" ALTER COLUMN "window_wall_type"
  SET DATA TYPE jsonb
  USING (
    CASE
      WHEN "window_wall_type" IS NULL THEN NULL
      WHEN "window_wall_type" ~ '^-?[0-9]+$' THEN "window_wall_type"::jsonb
      ELSE to_json("window_wall_type")::jsonb
    END
  );--> statement-breakpoint
ALTER TABLE "epc_window" ALTER COLUMN "permanent_shutters_present"
  SET DATA TYPE jsonb
  USING to_json("permanent_shutters_present")::jsonb;--> statement-breakpoint
ALTER TABLE "epc_window" ALTER COLUMN "transmission_data_source"
  SET DATA TYPE jsonb
  USING (
    CASE
      WHEN "transmission_data_source" IS NULL THEN NULL
      WHEN "transmission_data_source" ~ '^-?[0-9]+$' THEN "transmission_data_source"::jsonb
      ELSE to_json("transmission_data_source")::jsonb
    END
  );--> statement-breakpoint
ALTER TABLE "epc_building_part" ADD COLUMN "roof_construction_type" text;--> statement-breakpoint
ALTER TABLE "epc_building_part" ADD COLUMN "curtain_wall_age" text;--> statement-breakpoint
ALTER TABLE "epc_property" ADD COLUMN "mechanical_vent_duct_insulation_level" integer;--> statement-breakpoint
ALTER TABLE "epc_property" ADD COLUMN "addendum_stone_walls" boolean;--> statement-breakpoint
ALTER TABLE "epc_property" ADD COLUMN "addendum_system_build" boolean;--> statement-breakpoint
ALTER TABLE "epc_property" ADD COLUMN "addendum_numbers" jsonb;--> statement-breakpoint
ALTER TABLE "epc_property" ADD COLUMN "heating_number_baths" integer;--> statement-breakpoint
ALTER TABLE "epc_property" ADD COLUMN "heating_number_baths_wwhrs" integer;--> statement-breakpoint
ALTER TABLE "epc_property" ADD COLUMN "heating_electric_shower_count" integer;--> statement-breakpoint
ALTER TABLE "epc_property" ADD COLUMN "heating_mixer_shower_count" integer;--> statement-breakpoint
ALTER TABLE "epc_property" ADD COLUMN "ventilation_present" boolean DEFAULT false NOT NULL;--> statement-breakpoint
ALTER TABLE "epc_property" ADD COLUMN "ventilation_sheltered_sides" integer;--> statement-breakpoint
ALTER TABLE "epc_property" ADD COLUMN "ventilation_has_suspended_timber_floor" boolean;--> statement-breakpoint
ALTER TABLE "epc_property" ADD COLUMN "ventilation_suspended_timber_floor_sealed" boolean;--> statement-breakpoint
ALTER TABLE "epc_property" ADD COLUMN "ventilation_has_draught_lobby" boolean;--> statement-breakpoint
ALTER TABLE "epc_property" ADD COLUMN "ventilation_air_permeability_ap4_m3_h_m2" real;--> statement-breakpoint
ALTER TABLE "epc_property" ADD COLUMN "ventilation_mechanical_ventilation_kind" text;--> statement-breakpoint
ALTER TABLE "epc_renewable_heat_incentive" ADD CONSTRAINT "epc_renewable_heat_incentive_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 "property_baseline_performance" ADD CONSTRAINT "property_baseline_performance_property_id_property_id_fk" FOREIGN KEY ("property_id") REFERENCES "public"."property"("id") ON DELETE no action ON UPDATE no action;
