CREATE TABLE "change_entry" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"project_id" uuid NOT NULL,
	"titel" text NOT NULL,
	"auswirkung" text,
	"entscheidung" text DEFAULT 'offen' NOT NULL,
	"created_by" uuid,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "phase_gate" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"project_id" uuid NOT NULL,
	"phase" text NOT NULL,
	"ergebnis_erreicht" boolean NOT NULL,
	"notiz" text,
	"reviewed_by" uuid,
	"reviewed_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "project_status" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"project_id" uuid NOT NULL,
	"ampel" text NOT NULL,
	"trend" text,
	"text" text,
	"extern_sichtbar" boolean DEFAULT false NOT NULL,
	"created_by" uuid,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "project" ADD COLUMN "ziel" text;--> statement-breakpoint
ALTER TABLE "project" ADD COLUMN "nicht_ziele" text;--> statement-breakpoint
ALTER TABLE "project" ADD COLUMN "liefergegenstaende" text;--> statement-breakpoint
ALTER TABLE "project" ADD COLUMN "zieldreieck_flex" text;--> statement-breakpoint
ALTER TABLE "project" ADD COLUMN "phase" text DEFAULT 'definition' NOT NULL;--> statement-breakpoint
ALTER TABLE "project" ADD COLUMN "abschlussvermerk" text;--> statement-breakpoint
ALTER TABLE "risk" ADD COLUMN "strategie" text;--> statement-breakpoint
ALTER TABLE "risk" ADD COLUMN "owner_user_id" uuid;--> statement-breakpoint
ALTER TABLE "change_entry" ADD CONSTRAINT "change_entry_project_id_project_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."project"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "change_entry" ADD CONSTRAINT "change_entry_created_by_user_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."user"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "phase_gate" ADD CONSTRAINT "phase_gate_project_id_project_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."project"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "phase_gate" ADD CONSTRAINT "phase_gate_reviewed_by_user_id_fk" FOREIGN KEY ("reviewed_by") REFERENCES "public"."user"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "project_status" ADD CONSTRAINT "project_status_project_id_project_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."project"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "project_status" ADD CONSTRAINT "project_status_created_by_user_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."user"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "idx_change_entry_project" ON "change_entry" USING btree ("project_id");--> statement-breakpoint
CREATE INDEX "idx_phase_gate_project" ON "phase_gate" USING btree ("project_id");--> statement-breakpoint
CREATE INDEX "idx_project_status_project" ON "project_status" USING btree ("project_id");--> statement-breakpoint
ALTER TABLE "risk" ADD CONSTRAINT "risk_owner_user_id_user_id_fk" FOREIGN KEY ("owner_user_id") REFERENCES "public"."user"("id") ON DELETE set null ON UPDATE no action;