CREATE TABLE IF NOT EXISTS "property" (
	"id" serial PRIMARY KEY NOT NULL,
	"portfolio_id" integer NOT NULL,
	"creation_status" "creation_status" NOT NULL,
	"uprn" integer,
	"building_reference_number" integer,
	"status" "status",
	"address" text,
	"postcode" text,
	"has_pre_condition_report" boolean,
	"has_recommendations" boolean,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"updated_at" timestamp DEFAULT now() NOT NULL,
	"property_type" text,
	"built_form" text,
	"local_authority" text,
	"constituency" text,
	"number_of_rooms" integer,
	"year_built" text,
	"tenure" text,
	"current_epc_rating" "epc",
	"current_sap_points" real
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "property_details_epc" (
	"id" serial PRIMARY KEY NOT NULL,
	"property_id" integer NOT NULL,
	"portfolio_id" integer NOT NULL,
	"full_address" text,
	"total_floor_area" real,
	"walls" text,
	"walls_rating" smallint,
	"roof" text,
	"roof_rating" smallint,
	"floor" text,
	"floor_rating" smallint,
	"windows" text,
	"windows_rating" smallint,
	"heating" text,
	"heating_rating" smallint,
	"heating_contols" text,
	"heating_contols_rating" smallint,
	"hot_water" text,
	"hot_water_rating" smallint,
	"lighting" text,
	"lighting_rating" smallint,
	"mainfuel" text,
	"ventilation" text,
	"solar_pv" real,
	"solar_hot_water" boolean,
	"wind_turbine" smallint,
	"floor_height" real,
	"number_heated_rooms" integer,
	"heat_loss_corridor" boolean,
	"unheated_corridor_length" real,
	"number_of_open_fireplaces" integer,
	"number_of_extensions" integer,
	"number_of_storeys" integer,
	"mains_gas" boolean,
	"energy_tariff" text,
	"primary_energy_consumption" real,
	"co2_emissions" real
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "property_details_meter" (
	"id" serial PRIMARY KEY NOT NULL,
	"uprn" integer NOT NULL,
	"energy_supplier" text,
	"gas_supplier" text,
	"meter_reading_total" real,
	"meter_reading_electricity" real,
	"meter_reading_gas" real
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "property_targets" (
	"id" serial PRIMARY KEY NOT NULL,
	"property_id" integer NOT NULL,
	"portfolio_id" integer NOT NULL,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"epc" "epc",
	"heat_demand" text
);
--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "property" ADD CONSTRAINT "property_portfolio_id_portfolio_id_fk" FOREIGN KEY ("portfolio_id") REFERENCES "portfolio"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "property_details_epc" ADD CONSTRAINT "property_details_epc_property_id_property_id_fk" FOREIGN KEY ("property_id") REFERENCES "property"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "property_details_epc" ADD CONSTRAINT "property_details_epc_portfolio_id_portfolio_id_fk" FOREIGN KEY ("portfolio_id") REFERENCES "portfolio"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "property_targets" ADD CONSTRAINT "property_targets_property_id_property_id_fk" FOREIGN KEY ("property_id") REFERENCES "property"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "property_targets" ADD CONSTRAINT "property_targets_portfolio_id_portfolio_id_fk" FOREIGN KEY ("portfolio_id") REFERENCES "portfolio"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
