CREATE TABLE "property_removal_requests" (
	"id" bigserial PRIMARY KEY NOT NULL,
	"hubspot_deal_id" text NOT NULL,
	"portfolio_id" bigint NOT NULL,
	"reason" text NOT NULL,
	"status" text DEFAULT 'pending' NOT NULL,
	"requested_by" bigint NOT NULL,
	"requested_at" timestamp with time zone DEFAULT now() NOT NULL,
	"reviewed_by" bigint,
	"reviewed_at" timestamp with time zone
);
--> statement-breakpoint
ALTER TABLE "installed_measure" ALTER COLUMN "measure_type" SET DATA TYPE text;--> statement-breakpoint
DROP TYPE "public"."measure_type";--> statement-breakpoint
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
ALTER TABLE "installed_measure" ALTER COLUMN "measure_type" SET DATA TYPE "public"."measure_type" USING "measure_type"::"public"."measure_type";--> statement-breakpoint
ALTER TABLE "uploaded_files" ALTER COLUMN "file_type" SET DATA TYPE text;--> statement-breakpoint
DROP TYPE "public"."file_type";--> statement-breakpoint
CREATE TYPE "public"."file_type" AS ENUM('photo_pack', 'site_note', 'rd_sap_site_note', 'pas_2023_ventilation', 'pas_2023_condition', 'pas_significance', 'par_photo_pack', 'pas_2023_property', 'pas_2023_occupancy', 'ecmk_site_note', 'ecmk_rd_sap_site_note', 'ecmk_survey_xml', 'pre_photo', 'mid_photo', 'post_photo', 'loft_hatch_photo', 'dmev_photos', 'door_undercut_photos', 'trickle_vent_photos', 'pre_installation_building_inspection', 'point_of_work_risk_assessment', 'claim_of_compliance', 'mcs_compliance_certificate', 'certificate_of_conformity', 'minor_works_electrical_certificate', 'trustmark_licence_numbers', 'operative_competency', 'ventilation_assessment_checklist', 'anemometer_readings', 'commissioning_records', 'part_f_ventilation_document', 'handover_pack', 'insurance_guarantee', 'workmanship_warranty', 'g98_notification', 'installer_qualifications', 'installer_feedback', 'contractor_other');--> statement-breakpoint
ALTER TABLE "uploaded_files" ALTER COLUMN "file_type" SET DATA TYPE "public"."file_type" USING "file_type"::"public"."file_type";--> statement-breakpoint
ALTER TABLE "property_removal_requests" ADD CONSTRAINT "property_removal_requests_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 "property_removal_requests" ADD CONSTRAINT "property_removal_requests_requested_by_user_id_fk" FOREIGN KEY ("requested_by") REFERENCES "public"."user"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "property_removal_requests" ADD CONSTRAINT "property_removal_requests_reviewed_by_user_id_fk" FOREIGN KEY ("reviewed_by") REFERENCES "public"."user"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "idx_removal_requests_deal_id" ON "property_removal_requests" USING btree ("hubspot_deal_id");--> statement-breakpoint
CREATE INDEX "idx_removal_requests_portfolio_id" ON "property_removal_requests" USING btree ("portfolio_id");