CREATE TABLE IF NOT EXISTS "plan" (
	"id" bigserial PRIMARY KEY NOT NULL,
	"created_at" timestamp DEFAULT now() NOT NULL,
	"is_default" boolean NOT NULL
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "plan_recommendations" (
	"id" bigserial PRIMARY KEY NOT NULL,
	"plan_id" bigint NOT NULL,
	"recommendation_id" bigint NOT NULL
);
--> statement-breakpoint
ALTER TABLE "recommendation" ADD COLUMN "property_id" bigint NOT NULL;--> statement-breakpoint
ALTER TABLE "recommendation" ADD COLUMN "co2_equivalent_savings" real;--> statement-breakpoint
ALTER TABLE "recommendation" ADD COLUMN "energy_savings" real;--> statement-breakpoint
ALTER TABLE "recommendation" ADD COLUMN "energy_cost_savings" real;--> statement-breakpoint
ALTER TABLE "recommendation" ADD COLUMN "property_valuation_increase" real;--> statement-breakpoint
ALTER TABLE "recommendation" ADD COLUMN "rental_yield_increase" real;--> statement-breakpoint
ALTER TABLE "recommendation" ADD COLUMN "total_work_hours" real;--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "recommendation" ADD CONSTRAINT "recommendation_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 "plan_recommendations" ADD CONSTRAINT "plan_recommendations_plan_id_plan_id_fk" FOREIGN KEY ("plan_id") REFERENCES "plan"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "plan_recommendations" ADD CONSTRAINT "plan_recommendations_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 $$;
