CREATE TYPE "public"."review_status" AS ENUM('draft', 'pending', 'reviewing', 'approved', 'rejected', 'revised');--> statement-breakpoint
CREATE TABLE "gtc_clauses" (
	"id" serial PRIMARY KEY NOT NULL,
	"document_id" integer NOT NULL,
	"parent_id" integer,
	"item_number" varchar(50) NOT NULL,
	"category" varchar(100),
	"subtitle" varchar(500) NOT NULL,
	"content" text,
	"sort_order" numeric(10, 2) DEFAULT '0' NOT NULL,
	"depth" integer DEFAULT 0 NOT NULL,
	"full_path" varchar(200),
	"number_variable_name" varchar(100),
	"subtitle_variable_name" varchar(100),
	"content_variable_name" varchar(100),
	"is_active" boolean DEFAULT true NOT NULL,
	"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
);
--> statement-breakpoint
CREATE TABLE "gtc_negotiation_history" (
	"id" serial PRIMARY KEY NOT NULL,
	"vendor_clause_id" integer NOT NULL,
	"action" varchar(50) NOT NULL,
	"previous_status" "review_status",
	"new_status" "review_status",
	"changed_fields" jsonb,
	"comment" text,
	"attachments" jsonb,
	"actor_type" varchar(20) NOT NULL,
	"actor_id" integer,
	"actor_name" varchar(100),
	"actor_email" varchar(255),
	"created_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "gtc_templates" (
	"id" serial PRIMARY KEY NOT NULL,
	"name" varchar(255) NOT NULL,
	"description" text,
	"version" varchar(50) DEFAULT '1.0' NOT NULL,
	"original_file_name" varchar(255) NOT NULL,
	"file_path" varchar(500) NOT NULL,
	"file_size" integer,
	"variable_metadata" jsonb,
	"is_active" boolean DEFAULT true NOT NULL,
	"is_default" boolean DEFAULT false NOT NULL,
	"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
);
--> statement-breakpoint
CREATE TABLE "gtc_vendor_clauses" (
	"id" serial PRIMARY KEY NOT NULL,
	"vendor_document_id" integer NOT NULL,
	"base_clause_id" integer NOT NULL,
	"parent_id" integer,
	"modified_item_number" varchar(50),
	"modified_category" varchar(100),
	"modified_subtitle" varchar(500),
	"modified_content" text,
	"sort_order" numeric(10, 2) DEFAULT '0' NOT NULL,
	"depth" integer DEFAULT 0 NOT NULL,
	"full_path" varchar(200),
	"is_number_modified" boolean DEFAULT false NOT NULL,
	"is_category_modified" boolean DEFAULT false NOT NULL,
	"is_subtitle_modified" boolean DEFAULT false NOT NULL,
	"is_content_modified" boolean DEFAULT false NOT NULL,
	"review_status" "review_status" DEFAULT 'draft' NOT NULL,
	"negotiation_note" text,
	"is_active" boolean DEFAULT true NOT NULL,
	"is_excluded" boolean DEFAULT false NOT NULL,
	"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
);
--> statement-breakpoint
CREATE TABLE "gtc_vendor_documents" (
	"id" serial PRIMARY KEY NOT NULL,
	"base_document_id" integer NOT NULL,
	"vendor_id" integer NOT NULL,
	"name" varchar(255) NOT NULL,
	"description" text,
	"version" varchar(50) DEFAULT '1.0' NOT NULL,
	"review_status" "review_status" DEFAULT 'draft' NOT NULL,
	"negotiation_start_date" timestamp with time zone,
	"negotiation_end_date" timestamp with time zone,
	"approval_date" timestamp with time zone,
	"final_file_name" varchar(255),
	"final_file_path" varchar(500),
	"final_file_size" integer,
	"is_active" boolean DEFAULT true NOT NULL,
	"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
);
--> statement-breakpoint
ALTER TABLE "gtc_clauses" ADD CONSTRAINT "gtc_clauses_document_id_gtc_documents_id_fk" FOREIGN KEY ("document_id") REFERENCES "public"."gtc_documents"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "gtc_clauses" ADD CONSTRAINT "gtc_clauses_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_clauses" ADD CONSTRAINT "gtc_clauses_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
ALTER TABLE "gtc_negotiation_history" ADD CONSTRAINT "gtc_negotiation_history_vendor_clause_id_gtc_vendor_clauses_id_fk" FOREIGN KEY ("vendor_clause_id") REFERENCES "public"."gtc_vendor_clauses"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "gtc_negotiation_history" ADD CONSTRAINT "gtc_negotiation_history_actor_id_users_id_fk" FOREIGN KEY ("actor_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "gtc_templates" ADD CONSTRAINT "gtc_templates_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_templates" ADD CONSTRAINT "gtc_templates_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
ALTER TABLE "gtc_vendor_clauses" ADD CONSTRAINT "gtc_vendor_clauses_vendor_document_id_gtc_vendor_documents_id_fk" FOREIGN KEY ("vendor_document_id") REFERENCES "public"."gtc_vendor_documents"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "gtc_vendor_clauses" ADD CONSTRAINT "gtc_vendor_clauses_base_clause_id_gtc_clauses_id_fk" FOREIGN KEY ("base_clause_id") REFERENCES "public"."gtc_clauses"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "gtc_vendor_clauses" ADD CONSTRAINT "gtc_vendor_clauses_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_vendor_clauses" ADD CONSTRAINT "gtc_vendor_clauses_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
ALTER TABLE "gtc_vendor_documents" ADD CONSTRAINT "gtc_vendor_documents_base_document_id_gtc_documents_id_fk" FOREIGN KEY ("base_document_id") REFERENCES "public"."gtc_documents"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "gtc_vendor_documents" ADD CONSTRAINT "gtc_vendor_documents_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "gtc_vendor_documents" ADD CONSTRAINT "gtc_vendor_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_vendor_documents" ADD CONSTRAINT "gtc_vendor_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_clauses_document_item_number_idx" ON "gtc_clauses" USING btree ("document_id","parent_id","item_number");--> statement-breakpoint
CREATE INDEX "gtc_clauses_document_idx" ON "gtc_clauses" USING btree ("document_id");--> statement-breakpoint
CREATE INDEX "gtc_clauses_parent_idx" ON "gtc_clauses" USING btree ("parent_id");--> statement-breakpoint
CREATE INDEX "gtc_clauses_full_path_idx" ON "gtc_clauses" USING btree ("full_path");--> statement-breakpoint
CREATE INDEX "gtc_negotiation_history_vendor_clause_idx" ON "gtc_negotiation_history" USING btree ("vendor_clause_id");--> statement-breakpoint
CREATE INDEX "gtc_negotiation_history_action_idx" ON "gtc_negotiation_history" USING btree ("action");--> statement-breakpoint
CREATE INDEX "gtc_negotiation_history_created_at_idx" ON "gtc_negotiation_history" USING btree ("created_at");--> statement-breakpoint
CREATE INDEX "gtc_templates_name_idx" ON "gtc_templates" USING btree ("name");--> statement-breakpoint
CREATE INDEX "gtc_templates_is_default_idx" ON "gtc_templates" USING btree ("is_default");--> statement-breakpoint
CREATE UNIQUE INDEX "gtc_vendor_clauses_vendor_base_idx" ON "gtc_vendor_clauses" USING btree ("vendor_document_id","base_clause_id");--> statement-breakpoint
CREATE INDEX "gtc_vendor_clauses_vendor_document_idx" ON "gtc_vendor_clauses" USING btree ("vendor_document_id");--> statement-breakpoint
CREATE INDEX "gtc_vendor_clauses_base_clause_idx" ON "gtc_vendor_clauses" USING btree ("base_clause_id");--> statement-breakpoint
CREATE INDEX "gtc_vendor_clauses_parent_idx" ON "gtc_vendor_clauses" USING btree ("parent_id");--> statement-breakpoint
CREATE INDEX "gtc_vendor_clauses_review_status_idx" ON "gtc_vendor_clauses" USING btree ("review_status");--> statement-breakpoint
CREATE UNIQUE INDEX "gtc_vendor_documents_base_vendor_idx" ON "gtc_vendor_documents" USING btree ("base_document_id","vendor_id");--> statement-breakpoint
CREATE INDEX "gtc_vendor_documents_vendor_idx" ON "gtc_vendor_documents" USING btree ("vendor_id");--> statement-breakpoint
CREATE INDEX "gtc_vendor_documents_base_document_idx" ON "gtc_vendor_documents" USING btree ("base_document_id");--> statement-breakpoint
CREATE INDEX "gtc_vendor_documents_review_status_idx" ON "gtc_vendor_documents" USING btree ("review_status");--> statement-breakpoint
CREATE VIEW "public"."gtc_clauses_tree_view" AS (select "gtc_clauses"."id", "gtc_clauses"."document_id", "gtc_clauses"."parent_id", "gtc_clauses"."item_number", "gtc_clauses"."category", "gtc_clauses"."subtitle", "gtc_clauses"."content", "gtc_clauses"."sort_order", "gtc_clauses"."depth", "gtc_clauses"."full_path", "gtc_clauses"."number_variable_name", "gtc_clauses"."subtitle_variable_name", "gtc_clauses"."content_variable_name", "gtc_clauses"."is_active", "gtc_clauses"."created_at", "gtc_clauses"."created_by_id", "gtc_clauses"."updated_at", "gtc_clauses"."updated_by_id", "gtc_clauses"."edit_reason", "gtc_documents"."type", "gtc_documents"."file_name", "gtc_documents"."revision", "gtc_documents"."project_id", created_by_user.name as "created_by_name", created_by_user.email as "created_by_email", updated_by_user.name as "updated_by_name", updated_by_user.email as "updated_by_email", parent_clause.item_number as "parent_item_number", parent_clause.subtitle as "parent_subtitle", 
        (
          SELECT count(*)
          FROM gtc_clauses children
          WHERE children.parent_id = "gtc_clauses"."id"
          AND children.is_active = true
        )
       as "children_count", 
        (
          SELECT count(*)
          FROM gtc_clauses siblings
          WHERE siblings.parent_id = "gtc_clauses"."parent_id"
          AND siblings.is_active = true
        )
       as "siblings_count", 
        "gtc_clauses"."number_variable_name" IS NOT NULL AND
        "gtc_clauses"."subtitle_variable_name" IS NOT NULL AND
        "gtc_clauses"."content_variable_name" IS NOT NULL
       as "has_all_variable_names", 
        COALESCE(
          "gtc_clauses"."number_variable_name",
          'CLAUSE_' || REPLACE(COALESCE("gtc_clauses"."full_path", "gtc_clauses"."item_number"), '.', '_') || '_NUMBER'
        )
       as "auto_number_variable", 
        COALESCE(
          "gtc_clauses"."subtitle_variable_name",
          'CLAUSE_' || REPLACE(COALESCE("gtc_clauses"."full_path", "gtc_clauses"."item_number"), '.', '_') || '_SUBTITLE'
        )
       as "auto_subtitle_variable", 
        COALESCE(
          "gtc_clauses"."content_variable_name",
          'CLAUSE_' || REPLACE(COALESCE("gtc_clauses"."full_path", "gtc_clauses"."item_number"), '.', '_') || '_CONTENT'
        )
       as "auto_content_variable", 
        "gtc_clauses"."created_by_id" != "gtc_clauses"."updated_by_id" OR 
        "gtc_clauses"."created_at" != "gtc_clauses"."updated_at"
       as "has_edit_history" from "gtc_clauses" left join "gtc_documents" on "gtc_clauses"."document_id" = "gtc_documents"."id" left join users created_by_user on "gtc_clauses"."created_by_id" = created_by_user.id left join users updated_by_user on "gtc_clauses"."updated_by_id" = updated_by_user.id left join gtc_clauses parent_clause on "gtc_clauses"."parent_id" = parent_clause.id);--> statement-breakpoint
CREATE VIEW "public"."gtc_vendor_clauses_view" AS (select "gtc_vendor_clauses"."id", "gtc_vendor_clauses"."vendor_document_id", "gtc_vendor_clauses"."base_clause_id", "gtc_vendor_clauses"."parent_id", 
        COALESCE("gtc_vendor_clauses"."modified_item_number", "gtc_clauses"."item_number")
       as "effective_item_number", 
        COALESCE("gtc_vendor_clauses"."modified_category", "gtc_clauses"."category")
       as "effective_category", 
        COALESCE("gtc_vendor_clauses"."modified_subtitle", "gtc_clauses"."subtitle")
       as "effective_subtitle", 
        COALESCE("gtc_vendor_clauses"."modified_content", "gtc_clauses"."content")
       as "effective_content", "gtc_vendor_clauses"."is_number_modified", "gtc_vendor_clauses"."is_category_modified", "gtc_vendor_clauses"."is_subtitle_modified", "gtc_vendor_clauses"."is_content_modified", "gtc_clauses"."item_number", "gtc_clauses"."category", "gtc_clauses"."subtitle", "gtc_clauses"."content", "gtc_vendor_documents"."vendor_id", "vendors"."vendor_code", "vendors"."vendor_name", "gtc_vendor_documents"."base_document_id", "gtc_documents"."type", "gtc_documents"."file_name", "gtc_vendor_clauses"."review_status", "gtc_vendor_clauses"."negotiation_note", "gtc_vendor_clauses"."is_excluded", "gtc_vendor_clauses"."sort_order", "gtc_vendor_clauses"."depth", "gtc_vendor_clauses"."full_path", 
        "gtc_vendor_clauses"."is_number_modified" OR 
        "gtc_vendor_clauses"."is_category_modified" OR 
        "gtc_vendor_clauses"."is_subtitle_modified" OR 
        "gtc_vendor_clauses"."is_content_modified"
       as "has_modifications", "gtc_clauses"."number_variable_name", "gtc_clauses"."subtitle_variable_name", "gtc_clauses"."content_variable_name", 
        COALESCE(
          "gtc_clauses"."number_variable_name",
          'VENDOR_' || "vendors"."vendor_code" || '_CLAUSE_' || 
          REPLACE(COALESCE("gtc_vendor_clauses"."full_path", 
                  COALESCE("gtc_vendor_clauses"."modified_item_number", "gtc_clauses"."item_number")), '.', '_') || '_NUMBER'
        )
       as "auto_number_variable", 
        COALESCE(
          "gtc_clauses"."subtitle_variable_name",
          'VENDOR_' || "vendors"."vendor_code" || '_CLAUSE_' || 
          REPLACE(COALESCE("gtc_vendor_clauses"."full_path", 
                  COALESCE("gtc_vendor_clauses"."modified_item_number", "gtc_clauses"."item_number")), '.', '_') || '_SUBTITLE'
        )
       as "auto_subtitle_variable", 
        COALESCE(
          "gtc_clauses"."content_variable_name",
          'VENDOR_' || "vendors"."vendor_code" || '_CLAUSE_' || 
          REPLACE(COALESCE("gtc_vendor_clauses"."full_path", 
                  COALESCE("gtc_vendor_clauses"."modified_item_number", "gtc_clauses"."item_number")), '.', '_') || '_CONTENT'
        )
       as "auto_content_variable", "gtc_vendor_clauses"."created_at", "gtc_vendor_clauses"."updated_at" from "gtc_vendor_clauses" left join "gtc_clauses" on "gtc_vendor_clauses"."base_clause_id" = "gtc_clauses"."id" left join "gtc_vendor_documents" on "gtc_vendor_clauses"."vendor_document_id" = "gtc_vendor_documents"."id" left join "vendors" on "gtc_vendor_documents"."vendor_id" = "vendors"."id" left join "gtc_documents" on "gtc_vendor_documents"."base_document_id" = "gtc_documents"."id");