diff options
Diffstat (limited to 'db/migrations/0123_magenta_nightcrawler.sql')
| -rw-r--r-- | db/migrations/0123_magenta_nightcrawler.sql | 351 |
1 files changed, 351 insertions, 0 deletions
diff --git a/db/migrations/0123_magenta_nightcrawler.sql b/db/migrations/0123_magenta_nightcrawler.sql new file mode 100644 index 00000000..e18ec150 --- /dev/null +++ b/db/migrations/0123_magenta_nightcrawler.sql @@ -0,0 +1,351 @@ +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 +);
\ No newline at end of file |