CREATE TYPE "public"."portfolio_capability" AS ENUM('approver', 'contractor');--> statement-breakpoint
ALTER TYPE "public"."measure_type" ADD VALUE 'damp_mould';--> statement-breakpoint
ALTER TYPE "public"."measure_type" ADD VALUE 'door_undercut';--> statement-breakpoint
ALTER TYPE "public"."measure_type" ADD VALUE 'extractor_fan';--> statement-breakpoint
ALTER TYPE "public"."measure_type" ADD VALUE 'loft_board';--> statement-breakpoint
ALTER TYPE "public"."measure_type" ADD VALUE 'trickle_vent';--> statement-breakpoint
ALTER TYPE "public"."file_source" ADD VALUE 'contractor';--> statement-breakpoint
ALTER TYPE "public"."file_type" ADD VALUE 'pre_photo';--> statement-breakpoint
ALTER TYPE "public"."file_type" ADD VALUE 'mid_photo';--> statement-breakpoint
ALTER TYPE "public"."file_type" ADD VALUE 'post_photo';--> statement-breakpoint
ALTER TYPE "public"."file_type" ADD VALUE 'pre_installation_building_inspection';--> statement-breakpoint
ALTER TYPE "public"."file_type" ADD VALUE 'claim_of_compliance';--> statement-breakpoint
ALTER TYPE "public"."file_type" ADD VALUE 'handover_pack';--> statement-breakpoint
ALTER TYPE "public"."file_type" ADD VALUE 'insurance_guarantee';--> statement-breakpoint
ALTER TYPE "public"."file_type" ADD VALUE 'installer_qualifications';--> statement-breakpoint
ALTER TYPE "public"."file_type" ADD VALUE 'mcs_compliance_certificate';--> statement-breakpoint
ALTER TYPE "public"."file_type" ADD VALUE 'minor_works_electrical_certificate';--> statement-breakpoint
ALTER TYPE "public"."file_type" ADD VALUE 'point_of_work_risk_assessment';--> statement-breakpoint
ALTER TYPE "public"."file_type" ADD VALUE 'installer_feedback';--> statement-breakpoint
ALTER TYPE "public"."file_type" ADD VALUE 'workmanship_warranty';--> statement-breakpoint
ALTER TYPE "public"."file_type" ADD VALUE 'g98_notification';--> statement-breakpoint
ALTER TYPE "public"."file_type" ADD VALUE 'certificate_of_conformity';--> statement-breakpoint
ALTER TYPE "public"."file_type" ADD VALUE 'ventilation_assessment_checklist';--> statement-breakpoint
ALTER TYPE "public"."file_type" ADD VALUE 'contractor_other';--> statement-breakpoint
CREATE TABLE "deal_approvals" (
	"id" bigserial PRIMARY KEY NOT NULL,
	"hubspot_deal_id" text NOT NULL,
	"approved_by" bigint NOT NULL,
	"approved_at" timestamp with time zone DEFAULT now() NOT NULL,
	"notes" text,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "deal_approved_measures" (
	"id" bigserial PRIMARY KEY NOT NULL,
	"deal_approval_id" bigint NOT NULL,
	"measure_name" text NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "portfolio_capabilities" (
	"id" bigserial PRIMARY KEY NOT NULL,
	"user_id" bigint NOT NULL,
	"portfolio_id" bigint NOT NULL,
	"capability" "portfolio_capability" NOT NULL,
	"created_at" timestamp (6) with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp (6) with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "portfolio_capabilities_user_id_portfolio_id_capability_unique" UNIQUE("user_id","portfolio_id","capability")
);
--> statement-breakpoint
ALTER TABLE "uploaded_files" ADD COLUMN "measure_name" text;--> statement-breakpoint
ALTER TABLE "uploaded_files" ADD COLUMN "uploaded_by" bigint;--> statement-breakpoint
ALTER TABLE "deal_approvals" ADD CONSTRAINT "deal_approvals_approved_by_user_id_fk" FOREIGN KEY ("approved_by") REFERENCES "public"."user"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "deal_approved_measures" ADD CONSTRAINT "deal_approved_measures_deal_approval_id_deal_approvals_id_fk" FOREIGN KEY ("deal_approval_id") REFERENCES "public"."deal_approvals"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "portfolio_capabilities" ADD CONSTRAINT "portfolio_capabilities_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."user"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "portfolio_capabilities" ADD CONSTRAINT "portfolio_capabilities_portfolio_id_portfolio_id_fk" FOREIGN KEY ("portfolio_id") REFERENCES "public"."portfolio"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "idx_deal_approvals_deal_id" ON "deal_approvals" USING btree ("hubspot_deal_id");--> statement-breakpoint
CREATE INDEX "idx_deal_approved_measures_approval_id" ON "deal_approved_measures" USING btree ("deal_approval_id");--> statement-breakpoint
ALTER TABLE "uploaded_files" ADD CONSTRAINT "uploaded_files_uploaded_by_user_id_fk" FOREIGN KEY ("uploaded_by") REFERENCES "public"."user"("id") ON DELETE no action ON UPDATE no action;