CREATE TYPE "public"."gtc_type" AS ENUM('standard', 'project');--> statement-breakpoint
CREATE TABLE "gtc_documents" (
	"id" serial PRIMARY KEY NOT NULL,
	"type" "gtc_type" NOT NULL,
	"project_id" integer,
	"revision" integer DEFAULT 0 NOT NULL,
	"file_name" varchar(255),
	"file_path" varchar(500),
	"file_size" integer,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"created_by_id" integer NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_by_id" integer,
	"edit_reason" text,
	"is_active" boolean DEFAULT true NOT NULL
);
--> statement-breakpoint
ALTER TABLE "gtc_documents" ADD CONSTRAINT "gtc_documents_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "gtc_documents" ADD CONSTRAINT "gtc_documents_created_by_id_users_id_fk" FOREIGN KEY ("created_by_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "gtc_documents" ADD CONSTRAINT "gtc_documents_updated_by_id_users_id_fk" FOREIGN KEY ("updated_by_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
CREATE UNIQUE INDEX "gtc_project_revision_idx" ON "gtc_documents" USING btree ("project_id","revision","type");--> statement-breakpoint
CREATE INDEX "gtc_type_idx" ON "gtc_documents" USING btree ("type");--> statement-breakpoint
CREATE INDEX "gtc_project_idx" ON "gtc_documents" USING btree ("project_id");--> statement-breakpoint
CREATE INDEX "gtc_created_at_idx" ON "gtc_documents" USING btree ("created_at");--> statement-breakpoint
CREATE INDEX "gtc_updated_at_idx" ON "gtc_documents" USING btree ("updated_at");