From 9ceed79cf32c896f8a998399bf1b296506b2cd4a Mon Sep 17 00:00:00 2001
From: dujinkim <dujin.kim@dtsolution.co.kr>
Date: Tue, 8 Apr 2025 03:08:19 +0000
Subject: 로그인 및 미들웨어 처리. 구조 변경
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

---
 db/migrations/0000_shiny_wiccan.sql | 940 ++++++++++++++++++++++++++++++++++++
 1 file changed, 940 insertions(+)
 create mode 100644 db/migrations/0000_shiny_wiccan.sql

(limited to 'db/migrations/0000_shiny_wiccan.sql')

diff --git a/db/migrations/0000_shiny_wiccan.sql b/db/migrations/0000_shiny_wiccan.sql
new file mode 100644
index 00000000..f269dc04
--- /dev/null
+++ b/db/migrations/0000_shiny_wiccan.sql
@@ -0,0 +1,940 @@
+CREATE TYPE "public"."user_domain" AS ENUM('evcp', 'partners');--> statement-breakpoint
+CREATE TABLE "companies" (
+	"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "companies_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
+	"name" varchar(255) NOT NULL,
+	"taxID" integer NOT NULL,
+	"created_at" timestamp with time zone DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "contract_envelopes" (
+	"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "contract_envelopes_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
+	"contract_id" integer NOT NULL,
+	"envelope_id" varchar(200) NOT NULL,
+	"document_id" varchar(200),
+	"envelope_status" varchar(50),
+	"file_name" varchar(255) NOT NULL,
+	"file_path" varchar(1024) NOT NULL,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "contract_items" (
+	"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "contract_items_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
+	"contract_id" integer NOT NULL,
+	"item_id" integer NOT NULL,
+	"description" text,
+	"quantity" integer DEFAULT 1 NOT NULL,
+	"unit_price" numeric(10, 2),
+	"tax_rate" numeric(5, 2),
+	"tax_amount" numeric(10, 2),
+	"total_line_amount" numeric(12, 2),
+	"remark" text,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL,
+	CONSTRAINT "contract_items_contract_id_item_id_unique" UNIQUE("contract_id","item_id")
+);
+--> statement-breakpoint
+CREATE TABLE "contract_signers" (
+	"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "contract_signers_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
+	"envelope_id" integer NOT NULL,
+	"vendor_contact_id" integer,
+	"signer_type" varchar(20) DEFAULT 'VENDOR' NOT NULL,
+	"signer_email" varchar(255) NOT NULL,
+	"signer_name" varchar(100) NOT NULL,
+	"signer_position" varchar(100),
+	"signer_status" varchar(50) DEFAULT 'PENDING',
+	"signed_at" timestamp,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "contracts" (
+	"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "contracts_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
+	"project_id" integer NOT NULL,
+	"vendor_id" integer NOT NULL,
+	"contract_no" varchar(100) NOT NULL,
+	"contract_name" varchar(255) NOT NULL,
+	"status" varchar(50) DEFAULT 'ACTIVE' NOT NULL,
+	"start_date" date,
+	"end_date" date,
+	"payment_terms" text,
+	"delivery_terms" text,
+	"delivery_date" date,
+	"delivery_location" varchar(255),
+	"currency" varchar(10) DEFAULT 'KRW',
+	"total_amount" numeric(12, 2),
+	"discount" numeric(12, 2),
+	"tax" numeric(12, 2),
+	"shipping_fee" numeric(12, 2),
+	"net_total" numeric(12, 2),
+	"partial_shipping_allowed" boolean DEFAULT false,
+	"partial_payment_allowed" boolean DEFAULT false,
+	"remarks" text,
+	"version" integer DEFAULT 1,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL,
+	CONSTRAINT "contracts_contract_no_unique" UNIQUE("contract_no")
+);
+--> statement-breakpoint
+CREATE TABLE "poa" (
+	"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "poa_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
+	"contract_no" varchar(100) NOT NULL,
+	"original_contract_no" varchar(100) NOT NULL,
+	"project_id" integer NOT NULL,
+	"vendor_id" integer NOT NULL,
+	"original_contract_name" varchar(255) NOT NULL,
+	"original_status" varchar(50) NOT NULL,
+	"delivery_terms" text,
+	"delivery_date" date,
+	"delivery_location" varchar(255),
+	"currency" varchar(10),
+	"total_amount" numeric(12, 2),
+	"discount" numeric(12, 2),
+	"tax" numeric(12, 2),
+	"shipping_fee" numeric(12, 2),
+	"net_total" numeric(12, 2),
+	"change_reason" text,
+	"approval_status" varchar(50) DEFAULT 'PENDING',
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "items" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"item_code" varchar(100),
+	"item_name" varchar(255) NOT NULL,
+	"description" text,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL,
+	CONSTRAINT "items_item_code_unique" UNIQUE("item_code")
+);
+--> statement-breakpoint
+CREATE TABLE "pq_criterias" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"code" varchar(50) NOT NULL,
+	"check_point" varchar(255) NOT NULL,
+	"description" text,
+	"remarks" text,
+	"group_name" varchar(255),
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "pq_criterias_extension" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"pq_criteria_id" integer NOT NULL,
+	"project_id" integer NOT NULL,
+	"contract_info" text,
+	"additional_requirement" text,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "vendor_criteria_attachments" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"vendor_criteria_answer_id" integer NOT NULL,
+	"file_name" varchar(255) NOT NULL,
+	"file_path" varchar(1024) NOT NULL,
+	"file_type" varchar(50),
+	"file_size" integer,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "vendor_pq_criteria_answers" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"vendor_id" integer NOT NULL,
+	"criteria_id" integer NOT NULL,
+	"project_id" integer,
+	"answer" text,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "vendor_pq_review_logs" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"vendor_pq_criteria_answer_id" integer NOT NULL,
+	"reviewer_comment" text NOT NULL,
+	"reviewer_name" text,
+	"created_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "vendor_project_pqs" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"vendor_id" integer NOT NULL,
+	"project_id" integer NOT NULL,
+	"status" varchar(20) DEFAULT 'REQUESTED' NOT NULL,
+	"submitted_at" timestamp,
+	"approved_at" timestamp,
+	"rejected_at" timestamp,
+	"reject_reason" text,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "projects" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"code" varchar(50) NOT NULL,
+	"name" text NOT NULL,
+	"type" varchar(20) DEFAULT 'ship' NOT NULL,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "cbe_evaluations" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"rfq_id" integer NOT NULL,
+	"vendor_id" integer NOT NULL,
+	"evaluated_by" integer,
+	"evaluated_at" timestamp DEFAULT now() NOT NULL,
+	"result" varchar(50),
+	"total_cost" numeric(18, 2),
+	"currency" varchar(10) DEFAULT 'USD',
+	"payment_terms" varchar(255),
+	"incoterms" varchar(50),
+	"delivery_schedule" text,
+	"notes" text,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "rfq_attachments" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"rfq_id" integer,
+	"vendor_id" integer,
+	"file_name" varchar(255) NOT NULL,
+	"file_path" varchar(1024) NOT NULL,
+	"evaluation_id" integer,
+	"cbe_id" integer,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"comment_id" integer
+);
+--> statement-breakpoint
+CREATE TABLE "rfq_comments" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"rfq_id" integer,
+	"vendor_id" integer,
+	"comment_text" text NOT NULL,
+	"commented_by" integer NOT NULL,
+	"evaluation_id" integer,
+	"cbe_id" integer,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "rfq_evaluations" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"rfq_id" integer NOT NULL,
+	"vendor_id" integer NOT NULL,
+	"eval_type" varchar(30),
+	"result" varchar(255),
+	"notes" text,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "rfq_items" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"rfq_id" integer NOT NULL,
+	"item_code" varchar(100) NOT NULL,
+	"description" text,
+	"quantity" numeric(12, 2) DEFAULT 1,
+	"uom" varchar(50),
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "rfqs" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"rfq_code" varchar(50),
+	"project_id" integer,
+	"description" varchar(255),
+	"due_date" date NOT NULL,
+	"status" varchar(30) DEFAULT 'DRAFT' NOT NULL,
+	"rfq_type" varchar(30) DEFAULT 'PURCHASE',
+	"parent_rfq_id" integer,
+	"created_by" integer NOT NULL,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL,
+	CONSTRAINT "rfqs_rfq_code_unique" UNIQUE("rfq_code")
+);
+--> statement-breakpoint
+CREATE TABLE "vendor_commercial_responses" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"response_id" integer NOT NULL,
+	"total_price" numeric(18, 2),
+	"currency" varchar(10) DEFAULT 'USD',
+	"payment_terms" varchar(255),
+	"incoterms" varchar(50),
+	"delivery_period" varchar(100),
+	"warranty_period" varchar(100),
+	"validity_period" varchar(100),
+	"price_breakdown" text,
+	"commercial_notes" text,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "vendor_response_attachments" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"response_id" integer,
+	"technical_response_id" integer,
+	"commercial_response_id" integer,
+	"file_name" varchar(255) NOT NULL,
+	"file_path" varchar(1024) NOT NULL,
+	"file_type" varchar(50),
+	"attachment_type" varchar(50),
+	"description" varchar(255),
+	"uploaded_at" timestamp DEFAULT now() NOT NULL,
+	"uploaded_by" varchar(255)
+);
+--> statement-breakpoint
+CREATE TABLE "vendor_responses" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"rfq_id" integer NOT NULL,
+	"vendor_id" integer NOT NULL,
+	"response_status" varchar(30) DEFAULT 'REVIEWING' NOT NULL,
+	"notes" text,
+	"responded_by" varchar(255),
+	"responded_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "vendor_technical_responses" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"response_id" integer NOT NULL,
+	"summary" text,
+	"notes" text,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "otps" (
+	"email" varchar(256) PRIMARY KEY NOT NULL,
+	"code" varchar(6) NOT NULL,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"otpToken" varchar(512) NOT NULL,
+	"otp_expires" timestamp NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "permissions" (
+	"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "permissions_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
+	"permission_key" text NOT NULL,
+	"description" text,
+	"created_at" timestamp DEFAULT now()
+);
+--> statement-breakpoint
+CREATE TABLE "role_permissions" (
+	"role_id" integer NOT NULL,
+	"permission_id" integer NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "roles" (
+	"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "roles_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
+	"name" text NOT NULL,
+	"domain" "user_domain" NOT NULL,
+	"company_id" integer,
+	"description" text DEFAULT '' NOT NULL,
+	"created_at" timestamp DEFAULT now()
+);
+--> statement-breakpoint
+CREATE TABLE "user_roles" (
+	"user_id" integer NOT NULL,
+	"role_id" integer NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "users" (
+	"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "users_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
+	"name" varchar(255) NOT NULL,
+	"email" varchar(255) NOT NULL,
+	"company_id" integer,
+	"domain" "user_domain" DEFAULT 'partners' NOT NULL,
+	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
+	"image_url" varchar(1024),
+	CONSTRAINT "users_email_unique" UNIQUE("email")
+);
+--> statement-breakpoint
+CREATE TABLE "form_entries" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"form_code" varchar(50) NOT NULL,
+	"data" jsonb NOT NULL,
+	"contract_item_id" integer NOT NULL,
+	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
+	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "form_metas" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"project_id" integer NOT NULL,
+	"form_code" varchar(50) NOT NULL,
+	"form_name" varchar(255) NOT NULL,
+	"columns" jsonb NOT NULL,
+	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
+	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
+	CONSTRAINT "form_code_project_unique" UNIQUE("project_id","form_code")
+);
+--> statement-breakpoint
+CREATE TABLE "forms" (
+	"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "forms_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
+	"contract_item_id" integer NOT NULL,
+	"form_code" varchar(100) NOT NULL,
+	"form_name" varchar(255) NOT NULL,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "tag_classes" (
+	"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "tag_classes_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
+	"project_id" integer NOT NULL,
+	"code" varchar(100) NOT NULL,
+	"label" text NOT NULL,
+	"tag_type_code" varchar(50) NOT NULL,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL,
+	CONSTRAINT "uniq_code_in_project" UNIQUE("project_id","code")
+);
+--> statement-breakpoint
+CREATE TABLE "tag_subfield_options" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"project_id" integer NOT NULL,
+	"attributes_id" varchar(50) NOT NULL,
+	"code" varchar(50) NOT NULL,
+	"label" text NOT NULL,
+	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
+	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
+	CONSTRAINT "uniq_attribute_project_code" UNIQUE("project_id","attributes_id","code")
+);
+--> statement-breakpoint
+CREATE TABLE "tag_subfields" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"project_id" integer NOT NULL,
+	"tag_type_code" varchar(50) NOT NULL,
+	"attributes_id" varchar(50) NOT NULL,
+	"attributes_description" text NOT NULL,
+	"expression" text,
+	"delimiter" varchar(10),
+	"sort_order" integer DEFAULT 0 NOT NULL,
+	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
+	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
+	CONSTRAINT "uniq_tag_type_attribute" UNIQUE("project_id","tag_type_code","attributes_id")
+);
+--> statement-breakpoint
+CREATE TABLE "tag_type_class_form_mappings" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"project_id" integer NOT NULL,
+	"tag_type_label" varchar(255) NOT NULL,
+	"class_label" varchar(255) NOT NULL,
+	"form_code" varchar(50) NOT NULL,
+	"form_name" varchar(255) NOT NULL,
+	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
+	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
+	CONSTRAINT "uniq_mapping_in_project" UNIQUE("project_id","tag_type_label","class_label","form_code")
+);
+--> statement-breakpoint
+CREATE TABLE "tag_types" (
+	"code" varchar(50) NOT NULL,
+	"project_id" integer NOT NULL,
+	"description" text NOT NULL,
+	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
+	"updated_at" timestamp with time zone DEFAULT now() NOT NULL,
+	CONSTRAINT "tag_types_code_project_id_pk" PRIMARY KEY("code","project_id")
+);
+--> statement-breakpoint
+CREATE TABLE "tags" (
+	"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "tags_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
+	"contract_item_id" integer NOT NULL,
+	"form_id" integer,
+	"tag_no" varchar(100) NOT NULL,
+	"tag_type" varchar(50) NOT NULL,
+	"class" varchar(100) NOT NULL,
+	"description" text,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "vendor_data_report_temps" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"contract_item_id" integer NOT NULL,
+	"form_id" integer NOT NULL,
+	"file_name" varchar(255) NOT NULL,
+	"file_path" varchar(1024) NOT NULL,
+	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
+	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "document_attachments" (
+	"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "document_attachments_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
+	"revision_id" integer NOT NULL,
+	"file_name" varchar(255) NOT NULL,
+	"file_path" varchar(1024) NOT NULL,
+	"file_type" varchar(50),
+	"file_size" integer,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "documents" (
+	"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "documents_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
+	"contract_id" integer NOT NULL,
+	"doc_number" varchar(100) NOT NULL,
+	"title" varchar(255) NOT NULL,
+	"status" varchar(50) DEFAULT 'ACTIVE' NOT NULL,
+	"issued_date" date,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "issue_stages" (
+	"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "issue_stages_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
+	"document_id" integer NOT NULL,
+	"stage_name" varchar(100) NOT NULL,
+	"plan_date" date,
+	"actual_date" date,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "revisions" (
+	"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "revisions_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
+	"issue_stage_id" integer NOT NULL,
+	"revision" varchar(50) NOT NULL,
+	"uploader_type" varchar(20) DEFAULT 'vendor' NOT NULL,
+	"uploader_id" integer,
+	"uploader_name" varchar(100),
+	"comment" varchar(500),
+	"status" varchar(50),
+	"approved_date" date,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "vendor_attachments" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"vendor_id" integer,
+	"file_name" varchar(255) NOT NULL,
+	"file_path" varchar(1024) NOT NULL,
+	"attachment_type" varchar(50) DEFAULT 'GENERAL',
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "vendor_candidates" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"company_name" varchar(255) NOT NULL,
+	"contact_email" varchar(255) NOT NULL,
+	"contact_phone" varchar(50),
+	"country" varchar(100),
+	"source" varchar(100),
+	"status" varchar(30) DEFAULT 'COLLECTED' NOT NULL,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "vendor_contacts" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"vendor_id" integer NOT NULL,
+	"contact_name" varchar(255) NOT NULL,
+	"contact_position" varchar(100),
+	"contact_email" varchar(255) NOT NULL,
+	"contact_phone" varchar(50),
+	"is_primary" boolean DEFAULT false NOT NULL,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "vendor_investigation_attachments" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"investigation_id" integer NOT NULL,
+	"file_name" varchar(255) NOT NULL,
+	"file_path" varchar(1024) NOT NULL,
+	"attachment_type" varchar(50) DEFAULT 'REPORT',
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "vendor_investigations" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"vendor_id" integer NOT NULL,
+	"investigation_status" varchar(50) DEFAULT 'PLANNED' NOT NULL,
+	"scheduled_start_at" timestamp,
+	"scheduled_end_at" timestamp,
+	"completed_at" timestamp,
+	"investigation_notes" text,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "vendor_possible_items" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"vendor_id" integer NOT NULL,
+	"item_code" varchar(100) NOT NULL,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "vendors" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"vendor_name" varchar(255) NOT NULL,
+	"vendor_code" varchar(100),
+	"tax_id" varchar(100) NOT NULL,
+	"address" text,
+	"country" varchar(100),
+	"phone" varchar(50),
+	"email" varchar(255),
+	"website" varchar(255),
+	"status" varchar(30) DEFAULT 'PENDING_REVIEW' NOT NULL,
+	"representative_name" varchar(255),
+	"representative_birth" varchar(20),
+	"representative_email" varchar(255),
+	"representative_phone" varchar(50),
+	"corporate_registration_number" varchar(100),
+	"credit_agency" varchar(50),
+	"credit_rating" varchar(50),
+	"cash_flow_rating" varchar(50),
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "tasks" (
+	"id" varchar(30) PRIMARY KEY NOT NULL,
+	"code" varchar(128) DEFAULT concat('TASK-', to_char(nextval('tasks_code_seq'), 'FM0000')) NOT NULL,
+	"title" varchar(128),
+	"status" varchar(30) DEFAULT 'todo' NOT NULL,
+	"label" varchar(30) DEFAULT 'bug' NOT NULL,
+	"priority" varchar(30) DEFAULT 'low' NOT NULL,
+	"archived" boolean DEFAULT false NOT NULL,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT current_timestamp,
+	CONSTRAINT "tasks_code_unique" UNIQUE("code")
+);
+--> statement-breakpoint
+ALTER TABLE "contract_envelopes" ADD CONSTRAINT "contract_envelopes_contract_id_contracts_id_fk" FOREIGN KEY ("contract_id") REFERENCES "public"."contracts"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "contract_items" ADD CONSTRAINT "contract_items_contract_id_contracts_id_fk" FOREIGN KEY ("contract_id") REFERENCES "public"."contracts"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "contract_signers" ADD CONSTRAINT "contract_signers_envelope_id_contract_envelopes_id_fk" FOREIGN KEY ("envelope_id") REFERENCES "public"."contract_envelopes"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "contract_signers" ADD CONSTRAINT "contract_signers_vendor_contact_id_vendor_contacts_id_fk" FOREIGN KEY ("vendor_contact_id") REFERENCES "public"."vendor_contacts"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "contracts" ADD CONSTRAINT "contracts_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "contracts" ADD CONSTRAINT "contracts_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "poa" ADD CONSTRAINT "poa_original_contract_no_contracts_contract_no_fk" FOREIGN KEY ("original_contract_no") REFERENCES "public"."contracts"("contract_no") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "poa" ADD CONSTRAINT "poa_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "poa" ADD CONSTRAINT "poa_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "pq_criterias_extension" ADD CONSTRAINT "pq_criterias_extension_pq_criteria_id_pq_criterias_id_fk" FOREIGN KEY ("pq_criteria_id") REFERENCES "public"."pq_criterias"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint
+ALTER TABLE "pq_criterias_extension" ADD CONSTRAINT "pq_criterias_extension_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint
+ALTER TABLE "vendor_criteria_attachments" ADD CONSTRAINT "vendor_criteria_attachments_vendor_criteria_answer_id_vendor_pq_criteria_answers_id_fk" FOREIGN KEY ("vendor_criteria_answer_id") REFERENCES "public"."vendor_pq_criteria_answers"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "vendor_pq_criteria_answers" ADD CONSTRAINT "vendor_pq_criteria_answers_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint
+ALTER TABLE "vendor_pq_criteria_answers" ADD CONSTRAINT "vendor_pq_criteria_answers_criteria_id_pq_criterias_id_fk" FOREIGN KEY ("criteria_id") REFERENCES "public"."pq_criterias"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint
+ALTER TABLE "vendor_pq_criteria_answers" ADD CONSTRAINT "vendor_pq_criteria_answers_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint
+ALTER TABLE "vendor_pq_review_logs" ADD CONSTRAINT "vendor_pq_review_logs_vendor_pq_criteria_answer_id_vendor_pq_criteria_answers_id_fk" FOREIGN KEY ("vendor_pq_criteria_answer_id") REFERENCES "public"."vendor_pq_criteria_answers"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "vendor_project_pqs" ADD CONSTRAINT "vendor_project_pqs_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint
+ALTER TABLE "vendor_project_pqs" ADD CONSTRAINT "vendor_project_pqs_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint
+ALTER TABLE "cbe_evaluations" ADD CONSTRAINT "cbe_evaluations_rfq_id_rfqs_id_fk" FOREIGN KEY ("rfq_id") REFERENCES "public"."rfqs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "cbe_evaluations" ADD CONSTRAINT "cbe_evaluations_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "cbe_evaluations" ADD CONSTRAINT "cbe_evaluations_evaluated_by_users_id_fk" FOREIGN KEY ("evaluated_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_attachments" ADD CONSTRAINT "rfq_attachments_rfq_id_rfqs_id_fk" FOREIGN KEY ("rfq_id") REFERENCES "public"."rfqs"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_attachments" ADD CONSTRAINT "rfq_attachments_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_attachments" ADD CONSTRAINT "rfq_attachments_evaluation_id_rfq_evaluations_id_fk" FOREIGN KEY ("evaluation_id") REFERENCES "public"."rfq_evaluations"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_attachments" ADD CONSTRAINT "rfq_attachments_cbe_id_cbe_evaluations_id_fk" FOREIGN KEY ("cbe_id") REFERENCES "public"."cbe_evaluations"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_attachments" ADD CONSTRAINT "rfq_attachments_comment_id_rfq_comments_id_fk" FOREIGN KEY ("comment_id") REFERENCES "public"."rfq_comments"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_comments" ADD CONSTRAINT "rfq_comments_rfq_id_rfqs_id_fk" FOREIGN KEY ("rfq_id") REFERENCES "public"."rfqs"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_comments" ADD CONSTRAINT "rfq_comments_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_comments" ADD CONSTRAINT "rfq_comments_commented_by_users_id_fk" FOREIGN KEY ("commented_by") REFERENCES "public"."users"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_comments" ADD CONSTRAINT "rfq_comments_evaluation_id_rfq_evaluations_id_fk" FOREIGN KEY ("evaluation_id") REFERENCES "public"."rfq_evaluations"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_comments" ADD CONSTRAINT "rfq_comments_cbe_id_cbe_evaluations_id_fk" FOREIGN KEY ("cbe_id") REFERENCES "public"."cbe_evaluations"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_evaluations" ADD CONSTRAINT "rfq_evaluations_rfq_id_rfqs_id_fk" FOREIGN KEY ("rfq_id") REFERENCES "public"."rfqs"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_evaluations" ADD CONSTRAINT "rfq_evaluations_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_items" ADD CONSTRAINT "rfq_items_rfq_id_rfqs_id_fk" FOREIGN KEY ("rfq_id") REFERENCES "public"."rfqs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfq_items" ADD CONSTRAINT "rfq_items_item_code_items_item_code_fk" FOREIGN KEY ("item_code") REFERENCES "public"."items"("item_code") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfqs" ADD CONSTRAINT "rfqs_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfqs" ADD CONSTRAINT "rfqs_created_by_users_id_fk" FOREIGN KEY ("created_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "rfqs" ADD CONSTRAINT "rfqs_parent_rfq_id_rfqs_id_fk" FOREIGN KEY ("parent_rfq_id") REFERENCES "public"."rfqs"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "vendor_commercial_responses" ADD CONSTRAINT "vendor_commercial_responses_response_id_vendor_responses_id_fk" FOREIGN KEY ("response_id") REFERENCES "public"."vendor_responses"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "vendor_response_attachments" ADD CONSTRAINT "vendor_response_attachments_response_id_vendor_responses_id_fk" FOREIGN KEY ("response_id") REFERENCES "public"."vendor_responses"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "vendor_response_attachments" ADD CONSTRAINT "vendor_response_attachments_technical_response_id_vendor_technical_responses_id_fk" FOREIGN KEY ("technical_response_id") REFERENCES "public"."vendor_technical_responses"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "vendor_response_attachments" ADD CONSTRAINT "vendor_response_attachments_commercial_response_id_vendor_commercial_responses_id_fk" FOREIGN KEY ("commercial_response_id") REFERENCES "public"."vendor_commercial_responses"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "vendor_responses" ADD CONSTRAINT "vendor_responses_rfq_id_rfqs_id_fk" FOREIGN KEY ("rfq_id") REFERENCES "public"."rfqs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "vendor_responses" ADD CONSTRAINT "vendor_responses_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "vendor_technical_responses" ADD CONSTRAINT "vendor_technical_responses_response_id_vendor_responses_id_fk" FOREIGN KEY ("response_id") REFERENCES "public"."vendor_responses"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "role_permissions" ADD CONSTRAINT "role_permissions_role_id_roles_id_fk" FOREIGN KEY ("role_id") REFERENCES "public"."roles"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "role_permissions" ADD CONSTRAINT "role_permissions_permission_id_permissions_id_fk" FOREIGN KEY ("permission_id") REFERENCES "public"."permissions"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "roles" ADD CONSTRAINT "roles_company_id_vendors_id_fk" FOREIGN KEY ("company_id") REFERENCES "public"."vendors"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "user_roles" ADD CONSTRAINT "user_roles_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "user_roles" ADD CONSTRAINT "user_roles_role_id_roles_id_fk" FOREIGN KEY ("role_id") REFERENCES "public"."roles"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "users" ADD CONSTRAINT "users_company_id_vendors_id_fk" FOREIGN KEY ("company_id") REFERENCES "public"."vendors"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "form_entries" ADD CONSTRAINT "form_entries_contract_item_id_contract_items_id_fk" FOREIGN KEY ("contract_item_id") REFERENCES "public"."contract_items"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "form_metas" ADD CONSTRAINT "form_metas_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "forms" ADD CONSTRAINT "forms_contract_item_id_contract_items_id_fk" FOREIGN KEY ("contract_item_id") REFERENCES "public"."contract_items"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "tag_classes" ADD CONSTRAINT "tag_classes_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "tag_classes" ADD CONSTRAINT "tag_classes_tag_type_code_project_id_tag_types_code_project_id_fk" FOREIGN KEY ("tag_type_code","project_id") REFERENCES "public"."tag_types"("code","project_id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "tag_subfield_options" ADD CONSTRAINT "tag_subfield_options_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "tag_subfield_options" ADD CONSTRAINT "tag_subfield_options_attributes_id_project_id_tag_subfields_attributes_id_project_id_fk" FOREIGN KEY ("attributes_id","project_id") REFERENCES "public"."tag_subfields"("attributes_id","project_id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "tag_subfields" ADD CONSTRAINT "tag_subfields_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "tag_subfields" ADD CONSTRAINT "tag_subfields_tag_type_code_project_id_tag_types_code_project_id_fk" FOREIGN KEY ("tag_type_code","project_id") REFERENCES "public"."tag_types"("code","project_id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "tag_type_class_form_mappings" ADD CONSTRAINT "tag_type_class_form_mappings_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "tag_types" ADD CONSTRAINT "tag_types_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "tags" ADD CONSTRAINT "tags_contract_item_id_contract_items_id_fk" FOREIGN KEY ("contract_item_id") REFERENCES "public"."contract_items"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "tags" ADD CONSTRAINT "tags_form_id_forms_id_fk" FOREIGN KEY ("form_id") REFERENCES "public"."forms"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "vendor_data_report_temps" ADD CONSTRAINT "vendor_data_report_temps_contract_item_id_contract_items_id_fk" FOREIGN KEY ("contract_item_id") REFERENCES "public"."contract_items"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "vendor_data_report_temps" ADD CONSTRAINT "vendor_data_report_temps_form_id_forms_id_fk" FOREIGN KEY ("form_id") REFERENCES "public"."forms"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "document_attachments" ADD CONSTRAINT "document_attachments_revision_id_revisions_id_fk" FOREIGN KEY ("revision_id") REFERENCES "public"."revisions"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "documents" ADD CONSTRAINT "documents_contract_id_contracts_id_fk" FOREIGN KEY ("contract_id") REFERENCES "public"."contracts"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "issue_stages" ADD CONSTRAINT "issue_stages_document_id_documents_id_fk" FOREIGN KEY ("document_id") REFERENCES "public"."documents"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "vendor_attachments" ADD CONSTRAINT "vendor_attachments_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "vendor_contacts" ADD CONSTRAINT "vendor_contacts_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "vendor_investigation_attachments" ADD CONSTRAINT "vendor_investigation_attachments_investigation_id_vendor_investigations_id_fk" FOREIGN KEY ("investigation_id") REFERENCES "public"."vendor_investigations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "vendor_investigations" ADD CONSTRAINT "vendor_investigations_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "vendor_possible_items" ADD CONSTRAINT "vendor_possible_items_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "vendor_possible_items" ADD CONSTRAINT "vendor_possible_items_item_code_items_item_code_fk" FOREIGN KEY ("item_code") REFERENCES "public"."items"("item_code") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+CREATE UNIQUE INDEX "contract_items_contract_item_idx" ON "contract_items" USING btree ("contract_id","item_id");--> statement-breakpoint
+CREATE UNIQUE INDEX "vendor_response_unique" ON "vendor_responses" USING btree ("rfq_id","vendor_id");--> statement-breakpoint
+CREATE UNIQUE INDEX "contract_item_form_code_unique" ON "forms" USING btree ("contract_item_id","form_code");--> statement-breakpoint
+CREATE UNIQUE INDEX "unique_contract_doc_status" ON "documents" USING btree ("contract_id","doc_number","status");--> statement-breakpoint
+CREATE UNIQUE INDEX "unique_document_stage" ON "issue_stages" USING btree ("document_id","stage_name");--> statement-breakpoint
+CREATE UNIQUE INDEX "unique_stage_rev" ON "revisions" USING btree ("issue_stage_id","revision");--> statement-breakpoint
+CREATE VIEW "public"."contracts_detail_view" AS (select "contracts"."id", "contracts"."contract_no", "contracts"."contract_name", "contracts"."status", "contracts"."start_date", "contracts"."end_date", "contracts"."project_id", "projects"."code", "projects"."name", "contracts"."vendor_id", "vendors"."vendor_name", "contracts"."payment_terms", "contracts"."delivery_terms", "contracts"."delivery_date", "contracts"."delivery_location", "contracts"."currency", "contracts"."total_amount", "contracts"."discount", "contracts"."tax", "contracts"."shipping_fee", "contracts"."net_total", "contracts"."partial_shipping_allowed", "contracts"."partial_payment_allowed", "contracts"."remarks", "contracts"."version", "contracts"."created_at", "contracts"."updated_at", EXISTS (
+          SELECT 1 
+          FROM "contract_envelopes" 
+          WHERE "contract_envelopes"."contract_id" = "contracts"."id"
+        ) as "has_signature", COALESCE((
+          SELECT json_agg(
+            json_build_object(
+              'id', ci.id,
+              'itemId', ci.item_id,
+              'description', ci.description,
+              'quantity', ci.quantity,
+              'unitPrice', ci.unit_price,
+              'taxRate', ci.tax_rate,
+              'taxAmount', ci.tax_amount,
+              'totalLineAmount', ci.total_line_amount,
+              'remark', ci.remark,
+              'createdAt', ci.created_at,
+              'updatedAt', ci.updated_at
+            )
+          )
+          FROM "contract_items" AS ci
+          WHERE ci.contract_id = "contracts"."id"
+        ), '[]') as "items", COALESCE((
+          SELECT json_agg(
+            json_build_object(
+              'id', ce.id,
+              'envelopeId', ce.envelope_id,
+              'documentId', ce.document_id,
+              'envelopeStatus', ce.envelope_status,
+              'fileName', ce.file_name,
+              'filePath', ce.file_path,
+              'createdAt', ce.created_at,
+              'updatedAt', ce.updated_at,
+              'signers', (
+                SELECT json_agg(
+                  json_build_object(
+                    'id', cs.id,
+                    'vendorContactId', cs.vendor_contact_id,
+                    'signerType', cs.signer_type,
+                    'signerEmail', cs.signer_email,
+                    'signerName', cs.signer_name,
+                    'signerPosition', cs.signer_position,
+                    'signerStatus', cs.signer_status,
+                    'signedAt', cs.signed_at
+                  )
+                )
+                FROM "contract_signers" AS cs
+                WHERE cs.envelope_id = ce.id
+              )
+            )
+          )
+          FROM "contract_envelopes" AS ce
+          WHERE ce.contract_id = "contracts"."id"
+        ), '[]') as "envelopes" from "contracts" left join "projects" on "contracts"."project_id" = "projects"."id" left join "vendors" on "contracts"."vendor_id" = "vendors"."id");--> statement-breakpoint
+CREATE VIEW "public"."poa_detail_view" AS (select "poa"."id", "poa"."contract_no", "contracts"."project_id", "contracts"."vendor_id", "poa"."change_reason", "poa"."approval_status", "contracts"."contract_name" as "original_contract_name", "contracts"."status" as "original_status", "contracts"."start_date" as "original_start_date", "contracts"."end_date" as "original_end_date", "poa"."delivery_terms", "poa"."delivery_date", "poa"."delivery_location", "poa"."currency", "poa"."total_amount", "poa"."discount", "poa"."tax", "poa"."shipping_fee", "poa"."net_total", "poa"."created_at", "poa"."updated_at", EXISTS (
+              SELECT 1 
+              FROM "contract_envelopes" 
+              WHERE "contract_envelopes"."contract_id" = "poa"."id"
+          ) as "has_signature" from "poa" left join "contracts" on "poa"."contract_no" = "contracts"."contract_no");--> statement-breakpoint
+CREATE VIEW "public"."cbe_view" AS (select "cbe_evaluations"."id" as "cbe_id", "cbe_evaluations"."rfq_id" as "rfq_id", "cbe_evaluations"."vendor_id" as "vendor_id", "cbe_evaluations"."total_cost" as "total_cost", "cbe_evaluations"."currency" as "currency", "cbe_evaluations"."payment_terms" as "payment_terms", "cbe_evaluations"."incoterms" as "incoterms", "cbe_evaluations"."result" as "result", "cbe_evaluations"."notes" as "notes", "cbe_evaluations"."evaluated_by" as "evaluated_by", "cbe_evaluations"."evaluated_at" as "evaluated_at", "rfqs"."rfq_code" as "rfq_code", "rfqs"."description" as "rfq_description", "vendors"."vendor_name" as "vendor_name", "vendors"."vendor_code" as "vendor_code", "projects"."id" as "project_id", "projects"."code" as "project_code", "projects"."name" as "project_name", "users"."name" as "evaluator_name", "users"."email" as "evaluator_email" from "cbe_evaluations" inner join "rfqs" on "cbe_evaluations"."rfq_id" = "rfqs"."id" inner join "vendors" on "cbe_evaluations"."vendor_id" = "vendors"."id" left join "projects" on "rfqs"."project_id" = "projects"."id" left join "users" on "cbe_evaluations"."evaluated_by" = "users"."id");--> statement-breakpoint
+CREATE VIEW "public"."rfqs_view" AS (select "rfqs"."id" as "rfq_id", "rfqs"."status" as "status", "rfqs"."created_at" as "created_at", "rfqs"."updated_at" as "updated_at", "rfqs"."created_by" as "created_by", "rfqs"."rfq_type" as "rfq_type", "rfqs"."rfq_code" as "rfq_code", "rfqs"."description" as "description", "rfqs"."due_date" as "due_date", "rfqs"."parent_rfq_id" as "parent_rfq_id", "projects"."id" as "project_id", "projects"."code" as "project_code", "projects"."name" as "project_name", "users"."email" as "user_email", "users"."name" as "user_name", (
+          SELECT COUNT(*) 
+          FROM "rfq_items" 
+          WHERE "rfq_items"."rfq_id" = "rfqs"."id"
+        ) as "item_count", (
+          SELECT COUNT(*) 
+          FROM "rfq_attachments" 
+          WHERE "rfq_attachments"."rfq_id" = "rfqs"."id"
+        ) as "attachment_count" from "rfqs" left join "projects" on "rfqs"."project_id" = "projects"."id" left join "users" on "rfqs"."created_by" = "users"."id");--> statement-breakpoint
+CREATE VIEW "public"."vendor_cbe_view" AS (select "vendors"."id" as "vendor_id", "vendors"."vendor_name" as "vendor_name", "vendors"."vendor_code" as "vendor_code", "vendors"."address" as "address", "vendors"."country" as "country", "vendors"."email" as "email", "vendors"."website" as "website", "vendors"."status" as "vendor_status", "vendor_responses"."id" as "vendor_response_id", "vendor_responses"."rfq_id" as "rfq_id", "vendor_responses"."response_status" as "rfq_vendor_status", "vendor_responses"."updated_at" as "rfq_vendor_updated", "rfqs"."rfq_code" as "rfq_code", "rfqs"."rfq_type" as "rfq_type", "rfqs"."description" as "description", "rfqs"."due_date" as "due_date", "projects"."id" as "project_id", "projects"."code" as "project_code", "projects"."name" as "project_name", "cbe_evaluations"."id" as "cbe_id", "cbe_evaluations"."result" as "cbe_result", "cbe_evaluations"."notes" as "cbe_note", "cbe_evaluations"."updated_at" as "cbe_updated", "cbe_evaluations"."total_cost" as "total_cost", "cbe_evaluations"."currency" as "currency", "cbe_evaluations"."payment_terms" as "payment_terms", "cbe_evaluations"."incoterms" as "incoterms", "cbe_evaluations"."delivery_schedule" as "delivery_schedule" from "vendors" left join "vendor_responses" on "vendor_responses"."vendor_id" = "vendors"."id" left join "rfqs" on "vendor_responses"."rfq_id" = "rfqs"."id" left join "projects" on "rfqs"."project_id" = "projects"."id" left join "cbe_evaluations" on ("cbe_evaluations"."vendor_id" = "vendors"."id" and "cbe_evaluations"."rfq_id" = "vendor_responses"."rfq_id"));--> statement-breakpoint
+CREATE VIEW "public"."vendor_responses_view" AS (select "vendor_responses"."id" as "response_id", "vendor_responses"."rfq_id" as "rfq_id", "vendor_responses"."vendor_id" as "vendor_id", "rfqs"."rfq_code" as "rfq_code", "rfqs"."description" as "rfq_description", "rfqs"."due_date" as "rfq_due_date", "rfqs"."status" as "rfq_status", "rfqs"."rfq_type" as "rfq_type", "rfqs"."created_at" as "rfq_created_at", "rfqs"."updated_at" as "rfq_updated_at", "rfqs"."created_by" as "rfq_created_by", "projects"."id" as "project_id", "projects"."code" as "project_code", "projects"."name" as "project_name", "vendors"."vendor_name" as "vendor_name", "vendors"."vendor_code" as "vendor_code", "vendor_responses"."response_status" as "response_status", "vendor_responses"."responded_at" as "responded_at", CASE WHEN "vendor_technical_responses"."id" IS NOT NULL THEN TRUE ELSE FALSE END as "has_technical_response", "vendor_technical_responses"."id" as "technical_response_id", CASE WHEN "vendor_commercial_responses"."id" IS NOT NULL THEN TRUE ELSE FALSE END as "has_commercial_response", "vendor_commercial_responses"."id" as "commercial_response_id", "vendor_commercial_responses"."total_price" as "total_price", "vendor_commercial_responses"."currency" as "currency", "rfq_evaluations"."id" as "tbe_id", "rfq_evaluations"."result" as "tbe_result", "cbe_evaluations"."id" as "cbe_id", "cbe_evaluations"."result" as "cbe_result", (
+          SELECT COUNT(*) 
+          FROM "vendor_response_attachments" 
+          WHERE "vendor_response_attachments"."response_id" = "vendor_responses"."id"
+        ) as "attachment_count" from "vendor_responses" inner join "rfqs" on "vendor_responses"."rfq_id" = "rfqs"."id" inner join "vendors" on "vendor_responses"."vendor_id" = "vendors"."id" left join "projects" on "rfqs"."project_id" = "projects"."id" left join "vendor_technical_responses" on "vendor_technical_responses"."response_id" = "vendor_responses"."id" left join "vendor_commercial_responses" on "vendor_commercial_responses"."response_id" = "vendor_responses"."id" left join "rfq_evaluations" on ("rfq_evaluations"."rfq_id" = "vendor_responses"."rfq_id" and "rfq_evaluations"."vendor_id" = "vendor_responses"."vendor_id" and "rfq_evaluations"."eval_type" = 'TBE') left join "cbe_evaluations" on ("cbe_evaluations"."rfq_id" = "vendor_responses"."rfq_id" and "cbe_evaluations"."vendor_id" = "vendor_responses"."vendor_id"));--> statement-breakpoint
+CREATE VIEW "public"."vendor_rfq_view" AS (select "vendors"."id" as "vendor_id", "vendors"."vendor_name" as "vendor_name", "vendors"."vendor_code" as "vendor_code", "vendors"."address" as "address", "vendors"."country" as "country", "vendors"."email" as "email", "vendors"."website" as "website", "vendors"."status" as "vendor_status", "vendor_responses"."rfq_id" as "rfq_id", "vendor_responses"."response_status" as "rfq_vendor_status", "vendor_responses"."updated_at" as "rfq_vendor_updated", "rfqs"."rfq_code" as "rfq_code", "rfqs"."description" as "description", "rfqs"."due_date" as "due_date", "projects"."id" as "project_id", "projects"."code" as "project_code", "projects"."name" as "project_name" from "vendors" left join "vendor_responses" on "vendor_responses"."vendor_id" = "vendors"."id" left join "rfqs" on "vendor_responses"."rfq_id" = "rfqs"."id" left join "projects" on "rfqs"."project_id" = "projects"."id");--> statement-breakpoint
+CREATE VIEW "public"."vendor_tbe_view" AS (select "vendors"."id" as "vendor_id", "vendors"."vendor_name" as "vendor_name", "vendors"."vendor_code" as "vendor_code", "vendors"."address" as "address", "vendors"."country" as "country", "vendors"."email" as "email", "vendors"."website" as "website", "vendors"."status" as "vendor_status", "vendor_responses"."id" as "vendor_response_id", "vendor_responses"."rfq_id" as "rfq_id", "vendor_responses"."response_status" as "rfq_vendor_status", "vendor_responses"."updated_at" as "rfq_vendor_updated", "rfqs"."rfq_code" as "rfq_code", "rfqs"."rfq_type" as "rfq_type", "rfqs"."description" as "description", "rfqs"."due_date" as "due_date", "projects"."id" as "project_id", "projects"."code" as "project_code", "projects"."name" as "project_name", "rfq_evaluations"."id" as "tbe_id", "rfq_evaluations"."result" as "tbe_result", "rfq_evaluations"."notes" as "tbe_note", "rfq_evaluations"."updated_at" as "tbe_updated" from "vendors" left join "vendor_responses" on "vendor_responses"."vendor_id" = "vendors"."id" left join "rfqs" on "vendor_responses"."rfq_id" = "rfqs"."id" left join "projects" on "rfqs"."project_id" = "projects"."id" left join "rfq_evaluations" on ("rfq_evaluations"."vendor_id" = "vendors"."id" and "rfq_evaluations"."eval_type" = 'TBE' and "rfq_evaluations"."rfq_id" = "vendor_responses"."rfq_id"));--> statement-breakpoint
+CREATE VIEW "public"."role_view" AS (select "roles"."id" as "id", "roles"."name" as "name", "roles"."description" as "description", "roles"."domain" as "domain", "roles"."created_at" as "created_at", "vendors"."id" as "company_id", "vendors"."vendor_name" as "company_name", COUNT("users"."id") as "user_count" from "roles" left join "user_roles" on "user_roles"."role_id" = "roles"."id" left join "users" on "users"."id" = "user_roles"."user_id" left join "vendors" on "roles"."company_id" = "vendors"."id" group by "roles"."id", "vendors"."id");--> statement-breakpoint
+CREATE VIEW "public"."user_view" AS (select "users"."id" as "user_id", "users"."name" as "user_name", "users"."email" as "user_email", "users"."domain" as "user_domain", "users"."image_url" as "user_image", "vendors"."id" as "company_id", "vendors"."vendor_name" as "company_name", 
+      array_agg("roles"."name")
+     as "roles", "users"."created_at" as "created_at" from "users" left join "vendors" on "users"."company_id" = "vendors"."id" left join "user_roles" on "users"."id" = "user_roles"."user_id" left join "roles" on "user_roles"."role_id" = "roles"."id" group by "users"."id", "vendors"."id");--> statement-breakpoint
+CREATE VIEW "public"."view_tag_subfields" AS (select "tag_subfields"."id", "tag_subfields"."project_id", "tag_subfields"."tag_type_code", "tag_types"."description", "tag_subfields"."attributes_id", "tag_subfields"."attributes_description", "tag_subfields"."expression", "tag_subfields"."delimiter", "tag_subfields"."sort_order", "tag_subfields"."created_at", "tag_subfields"."updated_at", "projects"."code", "projects"."name" from "tag_subfields" inner join "tag_types" on ("tag_subfields"."tag_type_code" = "tag_types"."code" and "tag_subfields"."project_id" = "tag_types"."project_id") inner join "projects" on "tag_subfields"."project_id" = "projects"."id");--> statement-breakpoint
+CREATE VIEW "public"."document_stages_view" AS (
+  SELECT
+    d.id AS document_id,
+    d.doc_number,
+    d.title,
+    d.status,
+    d.issued_date,
+    d.contract_id,
+
+    (
+      SELECT COUNT(*)
+      FROM issue_stages
+      WHERE document_id = d.id
+    ) AS stage_count,
+
+    COALESCE( 
+      (
+        SELECT json_agg(i.stage_name)
+        FROM issue_stages i
+        WHERE i.document_id = d.id
+      ), 
+      '[]'
+    ) AS stage_list,
+
+    d.created_at,
+    d.updated_at
+  FROM documents d
+);--> statement-breakpoint
+CREATE VIEW "public"."vendor_documents_view" AS (
+    SELECT 
+      d.id, 
+      d.doc_number,
+      d.title,
+      d.status,
+      d.issued_date,
+  
+      d.contract_id,
+  
+      (
+        SELECT id FROM issue_stages
+        WHERE document_id = d.id
+        ORDER BY created_at DESC LIMIT 1
+      ) AS latest_stage_id,
+      (
+        SELECT stage_name FROM issue_stages
+        WHERE document_id = d.id
+        ORDER BY created_at DESC LIMIT 1
+      ) AS latest_stage_name,
+      (
+        SELECT plan_date FROM issue_stages
+        WHERE document_id = d.id
+        ORDER BY created_at DESC LIMIT 1
+      ) AS latest_stage_plan_date,
+      (
+        SELECT actual_date FROM issue_stages
+        WHERE document_id = d.id
+        ORDER BY created_at DESC LIMIT 1
+      ) AS latest_stage_actual_date,
+  
+      (
+        SELECT r.id FROM revisions r
+        JOIN issue_stages i ON r.issue_stage_id = i.id
+        WHERE i.document_id = d.id
+        ORDER BY r.created_at DESC LIMIT 1
+      ) AS latest_revision_id,
+      (
+        SELECT r.revision FROM revisions r
+        JOIN issue_stages i ON r.issue_stage_id = i.id
+        WHERE i.document_id = d.id
+        ORDER BY r.created_at DESC LIMIT 1
+      ) AS latest_revision,
+      (
+        SELECT r.uploader_type FROM revisions r
+        JOIN issue_stages i ON r.issue_stage_id = i.id
+        WHERE i.document_id = d.id
+        ORDER BY r.created_at DESC LIMIT 1
+      ) AS latest_revision_uploader_type,
+      (
+        SELECT r.uploader_name FROM revisions r
+        JOIN issue_stages i ON r.issue_stage_id = i.id
+        WHERE i.document_id = d.id
+        ORDER BY r.created_at DESC LIMIT 1
+      ) AS latest_revision_uploader_name,
+  
+      (
+        SELECT COUNT(*) FROM document_attachments a
+        JOIN revisions r ON a.revision_id = r.id
+        JOIN issue_stages i ON r.issue_stage_id = i.id
+        WHERE i.document_id = d.id
+      ) AS attachment_count,
+  
+      d.created_at,
+      d.updated_at
+    FROM documents d
+    JOIN contracts c ON d.contract_id = c.id
+  );--> statement-breakpoint
+CREATE VIEW "public"."vendor_detail_view" AS (select "id", "vendor_name", "vendor_code", "tax_id", "address", "country", "phone", "email", "website", "status", "representative_name", "representative_birth", "representative_email", "representative_phone", "corporate_registration_number", "credit_agency", "credit_rating", "cash_flow_rating", "created_at", "updated_at", 
+        (SELECT COALESCE(
+          json_agg(
+            json_build_object(
+              'id', c.id,
+              'contactName', c.contact_name,
+              'contactPosition', c.contact_position,
+              'contactEmail', c.contact_email,
+              'contactPhone', c.contact_phone,
+              'isPrimary', c.is_primary
+            )
+          ),
+          '[]'::json
+        )
+        FROM vendor_contacts c
+        WHERE c.vendor_id = vendors.id)
+       as "contacts", 
+        (SELECT COALESCE(
+          json_agg(
+            json_build_object(
+              'id', a.id,
+              'fileName', a.file_name,
+              'filePath', a.file_path,
+              'attachmentType', a.attachment_type,
+              'createdAt', a.created_at
+            )
+            ORDER BY a.attachment_type, a.created_at DESC
+          ),
+          '[]'::json
+        )
+        FROM vendor_attachments a
+        WHERE a.vendor_id = vendors.id)
+       as "attachments", 
+        (SELECT COUNT(*)
+        FROM vendor_attachments a
+        WHERE a.vendor_id = vendors.id)
+       as "attachment_count", 
+        (SELECT COUNT(*) 
+        FROM vendor_contacts c
+        WHERE c.vendor_id = vendors.id)
+       as "contact_count" from "vendors");--> statement-breakpoint
+CREATE VIEW "public"."vendor_investigations_view" AS (select "vendor_investigations"."id", "vendor_investigations"."investigation_status", "vendor_investigations"."scheduled_start_at", "vendor_investigations"."scheduled_end_at", "vendor_investigations"."completed_at", "vendor_investigations"."investigation_notes", "vendor_investigations"."created_at", "vendor_investigations"."updated_at", "vendor_investigations"."vendor_id", "vendors"."vendor_name", "vendors"."vendor_code", "vendors"."tax_id", "vendors"."status", "vendors"."country", "vendors"."email", "vendors"."phone", "vendors"."website", (
+        SELECT COALESCE(
+          json_agg(json_build_object(
+            'contactName', c.contact_name,
+            'contactEmail', c.contact_email,
+            'contactPhone', c.contact_phone,
+            'contactPosition', c.contact_position,
+            'isPrimary', c.is_primary,
+            'contactCreatedAt', c.created_at,
+            'contactUpdatedAt', c.updated_at
+          )),
+          '[]'::json
+        )
+        FROM vendor_contacts c
+        WHERE c.vendor_id = "vendors"."id"
+      ) as "contacts", (
+        SELECT COALESCE(
+          json_agg(json_build_object(
+            'itemCode', pi.item_code,
+            'itemName', i.item_name,
+            'itemCreatedAt', pi.created_at,
+            'itemUpdatedAt', pi.updated_at
+          )),
+          '[]'::json
+        )
+        FROM vendor_possible_items pi
+        LEFT JOIN items i ON pi.item_code = i.item_code
+        WHERE pi.vendor_id = "vendors"."id"
+      ) as "possibleItems" from "vendor_investigations" left join "vendors" on "vendor_investigations"."vendor_id" = "vendors"."id");--> statement-breakpoint
+CREATE VIEW "public"."vendor_items_view" AS (select "vendor_possible_items"."id", "vendor_possible_items"."vendor_id", "items"."item_name", "items"."item_code", "items"."description", "vendor_possible_items"."created_at", "vendor_possible_items"."updated_at" from "vendor_possible_items" left join "items" on "vendor_possible_items"."item_code" = "items"."item_code");
\ No newline at end of file
-- 
cgit v1.2.3

