path: root/db/migrations/0123_magenta_nightcrawler.sql

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
);