CREATE TABLE "b_rfq_attachment_revisions" ( "id" serial PRIMARY KEY NOT NULL, "attachment_id" integer NOT NULL, "revision_no" varchar(10) NOT NULL, "file_name" varchar(255) NOT NULL, "original_file_name" varchar(255) NOT NULL, "file_path" varchar(512) NOT NULL, "file_size" integer, "file_type" varchar(100), "is_latest" boolean DEFAULT true NOT NULL, "revision_comment" text, "created_by" integer NOT NULL, "created_at" timestamp DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "b_rfqs" ( "id" serial PRIMARY KEY NOT NULL, "rfq_code" varchar(50), "project_id" integer, "description" varchar(255), "remark" text, "due_date" date NOT NULL, "status" varchar(30) DEFAULT 'DRAFT' NOT NULL, "pic_code" varchar(50), "pic_name" varchar(50), "eng_pic_name" varchar(50), "project_company" varchar(255), "project_flag" varchar(255), "project_site" varchar(255), "package_no" varchar(50), "package_name" varchar(255), "created_by" integer NOT NULL, "updated_by" integer NOT NULL, "created_at" timestamp DEFAULT now() NOT NULL, "updated_at" timestamp DEFAULT now() NOT NULL, CONSTRAINT "b_rfqs_rfq_code_unique" UNIQUE("rfq_code") ); --> statement-breakpoint CREATE TABLE "b_rfq_attachments" ( "id" serial PRIMARY KEY NOT NULL, "attachment_type" varchar(50) NOT NULL, "serial_no" varchar(50) NOT NULL, "rfq_id" integer NOT NULL, "file_name" varchar(255) NOT NULL, "original_file_name" varchar(255) NOT NULL, "file_path" varchar(512) NOT NULL, "file_size" integer, "file_type" varchar(100), "description" varchar(500), "created_by" integer NOT NULL, "created_at" timestamp DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "final_rfq" ( "id" serial PRIMARY KEY NOT NULL, "rfq_id" integer NOT NULL, "final_rfq_status" varchar(30) DEFAULT 'DRAFT' NOT NULL, "vendor_id" integer NOT NULL, "due_date" date NOT NULL, "valid_date" date, "incoterms_code" varchar(20), "gtc" varchar(255), "gtc_valid_date" varchar(255), "classification" varchar(255), "sparepart" varchar(255), "short_list" boolean DEFAULT false NOT NULL, "return_yn" boolean DEFAULT false NOT NULL, "cp_request_yn" boolean DEFAULT false NOT NULL, "prject_gtc_yn" boolean DEFAULT true NOT NULL, "return_revision" integer DEFAULT 0 NOT NULL, "currency" varchar(10) DEFAULT 'KRW', "payment_terms_code" varchar(50), "tax_code" varchar(255) DEFAULT 'VV', "delivery_date" date NOT NULL, "place_of_shipping" varchar(255), "place_of_destination" varchar(255), "firsttime_yn" boolean DEFAULT true NOT NULL, "material_price_related_yn" boolean DEFAULT false, "remark" text, "vendor_remark" text, "created_at" timestamp DEFAULT now() NOT NULL, "updated_at" timestamp DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "initial_rfq" ( "id" serial PRIMARY KEY NOT NULL, "rfq_id" integer NOT NULL, "initial_rfq_status" varchar(30) DEFAULT 'DRAFT' NOT NULL, "vendor_id" integer NOT NULL, "due_date" date NOT NULL, "valid_date" date, "incoterms_code" varchar(20), "gtc" varchar(255), "gtc_valid_date" varchar(255), "classification" varchar(255), "sparepart" varchar(255), "short_list" boolean DEFAULT false NOT NULL, "return_yn" boolean DEFAULT false NOT NULL, "cp_request_yn" boolean DEFAULT false NOT NULL, "prject_gtc_yn" boolean DEFAULT false NOT NULL, "return_revision" integer DEFAULT 0 NOT NULL, "created_at" timestamp DEFAULT now() NOT NULL, "updated_at" timestamp DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "vendor_attachment_responses" ( "id" serial PRIMARY KEY NOT NULL, "attachment_id" integer NOT NULL, "vendor_id" integer NOT NULL, "rfq_type" varchar(20) NOT NULL, "rfq_record_id" integer NOT NULL, "response_status" varchar(30) DEFAULT 'NOT_RESPONDED' NOT NULL, "current_revision" varchar(10) DEFAULT 'Rev.0', "responded_revision" varchar(10), "response_comment" text, "vendor_comment" text, "requested_at" timestamp NOT NULL, "responded_at" timestamp, "created_at" timestamp DEFAULT now() NOT NULL, "updated_at" timestamp DEFAULT now() NOT NULL ); --> statement-breakpoint CREATE TABLE "vendor_response_history" ( "id" serial PRIMARY KEY NOT NULL, "vendor_response_id" integer NOT NULL, "action" varchar(50) NOT NULL, "previous_status" varchar(30), "new_status" varchar(30), "comment" text, "action_by" integer, "action_at" timestamp DEFAULT now() NOT NULL ); --> statement-breakpoint ALTER TABLE "b_rfq_attachment_revisions" ADD CONSTRAINT "b_rfq_attachment_revisions_attachment_id_b_rfq_attachments_id_fk" FOREIGN KEY ("attachment_id") REFERENCES "public"."b_rfq_attachments"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint ALTER TABLE "b_rfq_attachment_revisions" ADD CONSTRAINT "b_rfq_attachment_revisions_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 "b_rfqs" ADD CONSTRAINT "b_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 "b_rfqs" ADD CONSTRAINT "b_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 "b_rfqs" ADD CONSTRAINT "b_rfqs_updated_by_users_id_fk" FOREIGN KEY ("updated_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint ALTER TABLE "b_rfq_attachments" ADD CONSTRAINT "b_rfq_attachments_rfq_id_b_rfqs_id_fk" FOREIGN KEY ("rfq_id") REFERENCES "public"."b_rfqs"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint ALTER TABLE "b_rfq_attachments" ADD CONSTRAINT "b_rfq_attachments_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 "final_rfq" ADD CONSTRAINT "final_rfq_rfq_id_b_rfqs_id_fk" FOREIGN KEY ("rfq_id") REFERENCES "public"."b_rfqs"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint ALTER TABLE "final_rfq" ADD CONSTRAINT "final_rfq_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 "final_rfq" ADD CONSTRAINT "final_rfq_incoterms_code_incoterms_code_fk" FOREIGN KEY ("incoterms_code") REFERENCES "public"."incoterms"("code") ON DELETE set null ON UPDATE no action;--> statement-breakpoint ALTER TABLE "final_rfq" ADD CONSTRAINT "final_rfq_payment_terms_code_payment_terms_code_fk" FOREIGN KEY ("payment_terms_code") REFERENCES "public"."payment_terms"("code") ON DELETE set null ON UPDATE no action;--> statement-breakpoint ALTER TABLE "initial_rfq" ADD CONSTRAINT "initial_rfq_rfq_id_b_rfqs_id_fk" FOREIGN KEY ("rfq_id") REFERENCES "public"."b_rfqs"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint ALTER TABLE "initial_rfq" ADD CONSTRAINT "initial_rfq_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 "initial_rfq" ADD CONSTRAINT "initial_rfq_incoterms_code_incoterms_code_fk" FOREIGN KEY ("incoterms_code") REFERENCES "public"."incoterms"("code") ON DELETE set null ON UPDATE no action;--> statement-breakpoint ALTER TABLE "vendor_attachment_responses" ADD CONSTRAINT "vendor_attachment_responses_attachment_id_b_rfq_attachments_id_fk" FOREIGN KEY ("attachment_id") REFERENCES "public"."b_rfq_attachments"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint ALTER TABLE "vendor_attachment_responses" ADD CONSTRAINT "vendor_attachment_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_response_history" ADD CONSTRAINT "vendor_response_history_vendor_response_id_vendor_attachment_responses_id_fk" FOREIGN KEY ("vendor_response_id") REFERENCES "public"."vendor_attachment_responses"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint ALTER TABLE "vendor_response_history" ADD CONSTRAINT "vendor_response_history_action_by_users_id_fk" FOREIGN KEY ("action_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint CREATE UNIQUE INDEX "latest_revision_idx" ON "b_rfq_attachment_revisions" USING btree ("attachment_id","is_latest");--> statement-breakpoint CREATE UNIQUE INDEX "vendor_response_idx" ON "vendor_attachment_responses" USING btree ("attachment_id","vendor_id","rfq_type");--> statement-breakpoint CREATE VIEW "public"."b_rfqs_master" AS ( SELECT br.id as rfq_id, br.rfq_code, br.description, br.status, br.due_date, br.pic_code, br.pic_name, br.eng_pic_name, br.package_no, br.package_name, br.project_id, p.code as project_code, p.name as project_name, p.type as project_type, br.project_company, br.project_flag, br.project_site, COALESCE(att_count.total_attachments, 0) as total_attachments, br.created_at, br.updated_at FROM b_rfqs br LEFT JOIN projects p ON br.project_id = p.id LEFT JOIN ( SELECT rfq_id, COUNT(*) as total_attachments FROM b_rfq_attachments GROUP BY rfq_id ) att_count ON br.id = att_count.rfq_id );--> statement-breakpoint CREATE VIEW "public"."final_rfq_detail" AS ( SELECT br.id as rfq_id, br.rfq_code, br.status as rfq_status, fr.id as final_rfq_id, fr.final_rfq_status, fr.vendor_id, v.vendor_code, v.vendor_name, v.country as vendor_country, v.business_size as vendor_business_size, fr.due_date, fr.valid_date, fr.delivery_date, fr.incoterms_code, inc.description as incoterms_description, fr.payment_terms_code, pt.description as payment_terms_description, fr.currency, fr.tax_code, fr.place_of_shipping, fr.place_of_destination, fr.short_list, fr.return_yn, fr.cp_request_yn, fr.prject_gtc_yn, fr.firsttime_yn, fr.material_price_related_yn, fr.return_revision, fr.gtc, fr.gtc_valid_date, fr.classification, fr.sparepart, fr.remark, fr.vendor_remark, fr.created_at, fr.updated_at FROM b_rfqs br JOIN final_rfq fr ON br.id = fr.rfq_id LEFT JOIN vendors v ON fr.vendor_id = v.id LEFT JOIN incoterms inc ON fr.incoterms_code = inc.code LEFT JOIN payment_terms pt ON fr.payment_terms_code = pt.code );--> statement-breakpoint CREATE VIEW "public"."initial_rfq_detail" AS ( SELECT br.id as rfq_id, br.rfq_code, br.status as rfq_status, ir.id as initial_rfq_id, ir.initial_rfq_status, ir.vendor_id, v.vendor_code, v.vendor_name, v.country as vendor_country, v.business_size as vendor_business_size, ir.due_date, ir.valid_date, ir.incoterms_code, inc.description as incoterms_description, ir.incoterms_detail, ir.short_list, ir.return_yn, ir.cp_request_yn, ir.prject_gtc_yn, ir.return_revision, ir.gtc, ir.gtc_valid_date, ir.classification, ir.sparepart, ir.created_at, ir.updated_at FROM b_rfqs br JOIN initial_rfq ir ON br.id = ir.rfq_id LEFT JOIN vendors v ON ir.vendor_id = v.id LEFT JOIN incoterms inc ON ir.incoterms_code = inc.code );--> statement-breakpoint CREATE VIEW "public"."rfq_dashboard" AS ( SELECT br.id as rfq_id, br.rfq_code, br.description, br.status, br.due_date, p.code as project_code, p.name as project_name, br.package_no, br.package_name, br.pic_name, COALESCE(att_count.total_attachments, 0) as total_attachments, COALESCE(init_summary.vendor_count, 0) as initial_vendor_count, COALESCE(final_summary.vendor_count, 0) as final_vendor_count, COALESCE(init_summary.avg_response_rate, 0) as initial_response_rate, COALESCE(final_summary.avg_response_rate, 0) as final_response_rate, CASE WHEN br.status = 'DRAFT' THEN 0 WHEN br.status = 'Doc. Received' THEN 10 WHEN br.status = 'PIC Assigned' THEN 20 WHEN br.status = 'Doc. Confirmed' THEN 30 WHEN br.status = 'Init. RFQ Sent' THEN 40 WHEN br.status = 'Init. RFQ Answered' THEN 50 WHEN br.status = 'TBE started' THEN 60 WHEN br.status = 'TBE finished' THEN 70 WHEN br.status = 'Final RFQ Sent' THEN 80 WHEN br.status = 'Quotation Received' THEN 90 WHEN br.status = 'Vendor Selected' THEN 100 ELSE 0 END as overall_progress, (br.due_date - CURRENT_DATE) as days_to_deadline, br.created_at FROM b_rfqs br LEFT JOIN projects p ON br.project_id = p.id LEFT JOIN ( SELECT rfq_id, COUNT(*) as total_attachments FROM b_rfq_attachments GROUP BY rfq_id ) att_count ON br.id = att_count.rfq_id LEFT JOIN ( SELECT rfq_id, COUNT(DISTINCT vendor_id) as vendor_count, AVG(response_rate) as avg_response_rate FROM vendor_response_summary WHERE rfq_type = 'INITIAL' GROUP BY rfq_id ) init_summary ON br.id = init_summary.rfq_id LEFT JOIN ( SELECT rfq_id, COUNT(DISTINCT vendor_id) as vendor_count, AVG(response_rate) as avg_response_rate FROM vendor_response_summary WHERE rfq_type = 'FINAL' GROUP BY rfq_id ) final_summary ON br.id = final_summary.rfq_id );--> statement-breakpoint CREATE VIEW "public"."vendor_response_summary" AS ( SELECT br.id as rfq_id, br.rfq_code, br.status as rfq_status, v.id as vendor_id, v.vendor_code, v.vendor_name, v.country as vendor_country, v.business_size as vendor_business_size, var.rfq_type, COUNT(var.id) as total_attachments, COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) as responded_count, COUNT(CASE WHEN var.response_status = 'NOT_RESPONDED' THEN 1 END) as pending_count, COUNT(CASE WHEN var.response_status = 'WAIVED' THEN 1 END) as waived_count, COUNT(CASE WHEN var.response_status = 'REVISION_REQUESTED' THEN 1 END) as revision_requested_count, ROUND( (COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) * 100.0 / NULLIF(COUNT(CASE WHEN var.response_status != 'WAIVED' THEN 1 END), 0)), 2 ) as response_rate, ROUND( ((COUNT(CASE WHEN var.response_status = 'RESPONDED' THEN 1 END) + COUNT(CASE WHEN var.response_status = 'WAIVED' THEN 1 END)) * 100.0 / COUNT(var.id)), 2 ) as completion_rate FROM b_rfqs br JOIN b_rfq_attachments bra ON br.id = bra.rfq_id JOIN vendor_attachment_responses var ON bra.id = var.attachment_id JOIN vendors v ON var.vendor_id = v.id GROUP BY br.id, br.rfq_code, br.status, v.id, v.vendor_code, v.vendor_name, v.country, v.business_size, var.rfq_type );