From e0dfb55c5457aec489fc084c4567e791b4c65eb1 Mon Sep 17 00:00:00 2001
From: dujinkim <dujin.kim@dtsolution.co.kr>
Date: Wed, 26 Mar 2025 00:37:41 +0000
Subject: 3/25 까지의 대표님 작업사항
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

---
 db/migrations/0087_tidy_eddie_brock.sql | 59 +++++++++++++++++++++++++++++++++
 1 file changed, 59 insertions(+)
 create mode 100644 db/migrations/0087_tidy_eddie_brock.sql

(limited to 'db/migrations/0087_tidy_eddie_brock.sql')

diff --git a/db/migrations/0087_tidy_eddie_brock.sql b/db/migrations/0087_tidy_eddie_brock.sql
new file mode 100644
index 00000000..533d3a40
--- /dev/null
+++ b/db/migrations/0087_tidy_eddie_brock.sql
@@ -0,0 +1,59 @@
+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
+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
+CREATE UNIQUE INDEX "vendor_response_unique" ON "vendor_responses" USING btree ("rfq_id","vendor_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", "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" 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 "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"));
\ No newline at end of file
-- 
cgit v1.2.3

