DO $$ BEGIN
 CREATE TYPE "cost_unit" AS ENUM('gbp_sq_meter');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
DO $$ BEGIN
 CREATE TYPE "depth_unit" AS ENUM('mm');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
DO $$ BEGIN
 CREATE TYPE "type" AS ENUM('suspended_floor_insulation', 'solid_floor_insulation', 'external_wall_insulation', 'internal_wall_insulation');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
DO $$ BEGIN
 CREATE TYPE "r_value_unit" AS ENUM('square_meter_kelvin_per_watt');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
DO $$ BEGIN
 CREATE TYPE "thermal_conductivity_unit" AS ENUM('watt_per_meter_kelvin');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "material" (
	"id" bigserial PRIMARY KEY NOT NULL,
	"type" "type" NOT NULL,
	"description" text NOT NULL,
	"json" json,
	"depth_unit" "depth_unit",
	"cost" real,
	"cost_unit" "cost_unit",
	"r_value_per_mm" real,
	"r_value_unit" "r_value_unit",
	"thermal_conductivity" real,
	"thermal_conductivity_unit" "thermal_conductivity_unit",
	"link" text,
	"created_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "recommendation" (
	"id" bigserial PRIMARY KEY NOT NULL,
	"type" text NOT NULL,
	"description" text NOT NULL,
	"estimated_cost" real,
	"default" boolean NOT NULL,
	"new_u_value" real,
	"sap_points" real,
	"heat_demand" real,
	"created_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "recommendation_materials" (
	"id" bigserial PRIMARY KEY NOT NULL,
	"recommendation_id" bigint NOT NULL,
	"material_id" bigint NOT NULL,
	"created_at" timestamp DEFAULT now() NOT NULL
);
--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "recommendation_materials" ADD CONSTRAINT "recommendation_materials_recommendation_id_recommendation_id_fk" FOREIGN KEY ("recommendation_id") REFERENCES "recommendation"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "recommendation_materials" ADD CONSTRAINT "recommendation_materials_material_id_material_id_fk" FOREIGN KEY ("material_id") REFERENCES "material"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
