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 "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 "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,
	"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 "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 "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
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,
	"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
);
--> 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 "form_templates" (
	"id" serial PRIMARY KEY NOT NULL,
	"form_id" integer,
	"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 "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),
	"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
);
--> statement-breakpoint
CREATE TABLE "tag_subfield_options" (
	"id" serial PRIMARY KEY 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
);
--> statement-breakpoint
CREATE TABLE "tag_subfields" (
	"id" serial PRIMARY KEY 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("tag_type_code","attributes_id")
);
--> statement-breakpoint
CREATE TABLE "tag_type_class_form_mappings" (
	"id" serial PRIMARY KEY 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
);
--> statement-breakpoint
CREATE TABLE "tag_types" (
	"code" varchar(50) PRIMARY KEY 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
);
--> 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 "view_tag_subfields" (
	"id" integer PRIMARY KEY NOT NULL,
	"tag_type_code" varchar(50) NOT NULL,
	"tag_type_description" text,
	"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,
	"updated_at" timestamp with time zone
);
--> 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_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_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
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 "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 no action;--> 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 no action;--> 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 "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 "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 "form_templates" ADD CONSTRAINT "form_templates_form_id_forms_id_fk" FOREIGN KEY ("form_id") REFERENCES "public"."forms"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "tag_classes" ADD CONSTRAINT "tag_classes_tag_type_code_tag_types_code_fk" FOREIGN KEY ("tag_type_code") REFERENCES "public"."tag_types"("code") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "tag_subfield_options" ADD CONSTRAINT "tag_subfield_options_attributes_id_tag_subfields_attributes_id_fk" FOREIGN KEY ("attributes_id") REFERENCES "public"."tag_subfields"("attributes_id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "tag_subfields" ADD CONSTRAINT "tag_subfields_tag_type_code_tag_types_code_fk" FOREIGN KEY ("tag_type_code") REFERENCES "public"."tag_types"("code") 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_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", EXISTS (
            SELECT 1 
            FROM "contract_items" 
            WHERE "contract_items"."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"."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"."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_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");