DO $$ BEGIN
 CREATE TYPE "goal" AS ENUM('Valuation Improvement', 'Increasing EPC', 'Reducing CO2 emissions', 'Energy Savings', 'None');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
DO $$ BEGIN
 CREATE TYPE "role" AS ENUM('creator', 'admin', 'read', 'write');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
DO $$ BEGIN
 CREATE TYPE "status" AS ENUM('scoping', 'assessment', 'tendering', 'project underway', 'completion; status: on track', 'completion; status: delayed', 'completion; status: at risk', 'completion; status: completed', 'needs review');
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "portfolio" (
	"id" serial PRIMARY KEY NOT NULL,
	"name" text NOT NULL,
	"budget" real,
	"status" "status" NOT NULL,
	"goal" "goal" NOT NULL,
	"cost" real,
	"number_of_properties" integer,
	"co2_equivalent_savings" real,
	"energy_savings" real,
	"energy_cost_savings" real,
	"property_valuation_increase" real,
	"rental_yield_increase" real,
	"total_work_hours" real,
	"created_at" timestamp (6) with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp (6) with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE IF NOT EXISTS "portfolioUsers" (
	"id" serial PRIMARY KEY NOT NULL,
	"user_id" integer NOT NULL,
	"portfolio_id" integer NOT NULL,
	"role" "role" NOT NULL,
	"created_at" timestamp (6) with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp (6) with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "portfolioUsers" ADD CONSTRAINT "portfolioUsers_user_id_user_id_fk" FOREIGN KEY ("user_id") REFERENCES "user"("id") ON DELETE no action ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;
--> statement-breakpoint
DO $$ BEGIN
 ALTER TABLE "portfolioUsers" ADD CONSTRAINT "portfolioUsers_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 $$;
