CREATE TYPE "public"."measure_type" AS ENUM('air_source_heat_pump', 'boiler_upgrade', 'high_heat_retention_storage_heaters', 'secondary_heating', 'roomstat_programmer_trvs', 'time_temperature_zone_control', 'cylinder_thermostat', 'cavity_wall_insulation', 'extension_cavity_wall_insulation', 'external_wall_insulation', 'internal_wall_insulation', 'loft_insulation', 'flat_roof_insulation', 'room_roof_insulation', 'solid_floor_insulation', 'suspended_floor_insulation', 'double_glazing', 'secondary_glazing', 'draught_proofing', 'mechanical_ventilation', 'low_energy_lighting', 'solar_pv', 'hot_water_tank_insulation', 'sealing_open_fireplace');--> statement-breakpoint
CREATE TABLE "installed_measure" (
	"id" bigserial PRIMARY KEY NOT NULL,
	"uprn" text NOT NULL,
	"measure_type" "measure_type" NOT NULL,
	"installed_at" timestamp DEFAULT now(),
	"sap_points" real,
	"carbon_savings" real,
	"kwh_savings" real,
	"bill_savings" real,
	"heat_demand_savings" real,
	"source" text,
	"is_active" boolean DEFAULT true NOT NULL
);
--> statement-breakpoint
CREATE INDEX "idx_installed_measure_uprn" ON "installed_measure" USING btree ("uprn");--> statement-breakpoint
CREATE INDEX "idx_installed_measure_uprn_active" ON "installed_measure" USING btree ("uprn") WHERE "installed_measure"."is_active" = true;--> statement-breakpoint
CREATE INDEX "idx_installed_measure_measure_type" ON "installed_measure" USING btree ("measure_type");--> statement-breakpoint
CREATE INDEX "idx_installed_measure_uprn_measure" ON "installed_measure" USING btree ("uprn","measure_type") WHERE "installed_measure"."is_active" = true;