From e46b7ccad4db812dfc8455b20425469afad61788 Mon Sep 17 00:00:00 2001
From: dujinkim <dujin.kim@dtsolution.co.kr>
Date: Tue, 13 May 2025 03:07:39 +0000
Subject: (대표님) drizzle-kit migrations and snapshots
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

---
 db/migrations/0054_last_stingray.sql | 149 +++++++++++++++++++++++++++++++++++
 1 file changed, 149 insertions(+)
 create mode 100644 db/migrations/0054_last_stingray.sql

(limited to 'db/migrations/0054_last_stingray.sql')

diff --git a/db/migrations/0054_last_stingray.sql b/db/migrations/0054_last_stingray.sql
new file mode 100644
index 00000000..74d3bd1c
--- /dev/null
+++ b/db/migrations/0054_last_stingray.sql
@@ -0,0 +1,149 @@
+CREATE TABLE "procurement_attachments" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"attachment_type" varchar(50) NOT NULL,
+	"procurement_rfqs_id" integer,
+	"procurement_rfq_details_id" integer,
+	"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,
+	CONSTRAINT "attachment_type_check" CHECK ("procurement_attachments"."procurement_rfqs_id" IS NOT NULL OR "procurement_attachments"."procurement_rfq_details_id" IS NOT NULL)
+);
+--> statement-breakpoint
+CREATE TABLE "procurement_quotation_items" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"quotation_id" integer NOT NULL,
+	"pr_item_id" integer NOT NULL,
+	"material_code" varchar(50),
+	"material_description" varchar(255),
+	"quantity" numeric NOT NULL,
+	"uom" varchar(20),
+	"unit_price" numeric NOT NULL,
+	"total_price" numeric NOT NULL,
+	"currency" varchar(10) DEFAULT 'USD',
+	"vendor_material_code" varchar(50),
+	"vendor_material_description" varchar(255),
+	"delivery_date" date,
+	"lead_time_in_days" integer,
+	"tax_rate" numeric,
+	"tax_amount" numeric,
+	"discount_rate" numeric,
+	"discount_amount" numeric,
+	"remark" text,
+	"is_alternative" boolean DEFAULT false,
+	"is_recommended" boolean DEFAULT false,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "procurement_rfq_attachments" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"rfq_id" integer NOT NULL,
+	"comment_id" integer,
+	"quotation_id" integer,
+	"file_name" varchar(255) NOT NULL,
+	"file_size" integer NOT NULL,
+	"file_type" varchar(100),
+	"file_path" varchar(500) NOT NULL,
+	"is_vendor_upload" boolean DEFAULT false,
+	"uploaded_by" integer,
+	"vendor_id" integer,
+	"uploaded_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "procurement_rfq_comments" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"rfq_id" integer NOT NULL,
+	"vendor_id" integer,
+	"user_id" integer,
+	"content" text NOT NULL,
+	"is_vendor_comment" boolean DEFAULT false,
+	"parent_comment_id" integer,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "procurement_vendor_quotations" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"rfq_id" integer NOT NULL,
+	"vendor_id" integer NOT NULL,
+	"quotation_code" varchar(50),
+	"quotation_version" integer DEFAULT 1,
+	"total_items_count" integer DEFAULT 0,
+	"sub_total" numeric DEFAULT '0',
+	"tax_total" numeric DEFAULT '0',
+	"discount_total" numeric DEFAULT '0',
+	"total_price" numeric DEFAULT '0',
+	"currency" varchar(10) DEFAULT 'USD',
+	"valid_until" date,
+	"estimated_delivery_date" date,
+	"payment_terms_code" varchar(50),
+	"incoterms_code" varchar(20),
+	"incoterms_detail" varchar(255),
+	"status" varchar(30) DEFAULT 'Draft' NOT NULL,
+	"remark" text,
+	"rejection_reason" text,
+	"submitted_at" timestamp,
+	"accepted_at" timestamp,
+	"created_by" integer,
+	"updated_by" integer,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+DROP VIEW "public"."procurement_rfq_details_view";--> statement-breakpoint
+ALTER TABLE "procurement_attachments" ADD CONSTRAINT "procurement_attachments_procurement_rfqs_id_procurement_rfqs_id_fk" FOREIGN KEY ("procurement_rfqs_id") REFERENCES "public"."procurement_rfqs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "procurement_attachments" ADD CONSTRAINT "procurement_attachments_procurement_rfq_details_id_procurement_rfq_details_id_fk" FOREIGN KEY ("procurement_rfq_details_id") REFERENCES "public"."procurement_rfq_details"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "procurement_attachments" ADD CONSTRAINT "procurement_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 "procurement_quotation_items" ADD CONSTRAINT "procurement_quotation_items_quotation_id_procurement_vendor_quotations_id_fk" FOREIGN KEY ("quotation_id") REFERENCES "public"."procurement_vendor_quotations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "procurement_quotation_items" ADD CONSTRAINT "procurement_quotation_items_pr_item_id_pr_items_id_fk" FOREIGN KEY ("pr_item_id") REFERENCES "public"."pr_items"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "procurement_rfq_attachments" ADD CONSTRAINT "procurement_rfq_attachments_rfq_id_procurement_rfqs_id_fk" FOREIGN KEY ("rfq_id") REFERENCES "public"."procurement_rfqs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "procurement_rfq_attachments" ADD CONSTRAINT "procurement_rfq_attachments_comment_id_procurement_rfq_comments_id_fk" FOREIGN KEY ("comment_id") REFERENCES "public"."procurement_rfq_comments"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "procurement_rfq_attachments" ADD CONSTRAINT "procurement_rfq_attachments_quotation_id_procurement_vendor_quotations_id_fk" FOREIGN KEY ("quotation_id") REFERENCES "public"."procurement_vendor_quotations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "procurement_rfq_attachments" ADD CONSTRAINT "procurement_rfq_attachments_uploaded_by_users_id_fk" FOREIGN KEY ("uploaded_by") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "procurement_rfq_attachments" ADD CONSTRAINT "procurement_rfq_attachments_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "procurement_rfq_comments" ADD CONSTRAINT "procurement_rfq_comments_rfq_id_procurement_rfqs_id_fk" FOREIGN KEY ("rfq_id") REFERENCES "public"."procurement_rfqs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "procurement_rfq_comments" ADD CONSTRAINT "procurement_rfq_comments_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "procurement_rfq_comments" ADD CONSTRAINT "procurement_rfq_comments_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "procurement_rfq_comments" ADD CONSTRAINT "procurement_rfq_comments_parent_comment_id_procurement_rfq_comments_id_fk" FOREIGN KEY ("parent_comment_id") REFERENCES "public"."procurement_rfq_comments"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "procurement_vendor_quotations" ADD CONSTRAINT "procurement_vendor_quotations_rfq_id_procurement_rfqs_id_fk" FOREIGN KEY ("rfq_id") REFERENCES "public"."procurement_rfqs"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "procurement_vendor_quotations" ADD CONSTRAINT "procurement_vendor_quotations_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "procurement_vendor_quotations" ADD CONSTRAINT "procurement_vendor_quotations_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 "procurement_vendor_quotations" ADD CONSTRAINT "procurement_vendor_quotations_incoterms_code_incoterms_code_fk" FOREIGN KEY ("incoterms_code") REFERENCES "public"."incoterms"("code") ON DELETE set null ON UPDATE no action;--> statement-breakpoint
+CREATE VIEW "public"."procurement_rfq_details_view" AS (select "rfq_details"."id" as "detail_id", "rfqs"."id" as "rfq_id", "rfqs"."rfq_code" as "rfq_code", (
+                SELECT COUNT(*) 
+                FROM procurement_rfq_comments 
+                WHERE rfq_id = "rfqs"."id" AND vendor_id = "rfq_details"."vendors_id"
+            ) as "comment_count", (
+                SELECT created_at 
+                FROM procurement_rfq_comments 
+                WHERE rfq_id = "rfqs"."id" AND vendor_id = "rfq_details"."vendors_id"
+                ORDER BY created_at DESC LIMIT 1
+            ) as "last_comment_date", (
+                SELECT created_at 
+                FROM procurement_rfq_comments 
+                WHERE rfq_id = "rfqs"."id" AND vendor_id = "rfq_details"."vendors_id" AND is_vendor_comment = true
+                ORDER BY created_at DESC LIMIT 1
+            ) as "last_vendor_comment_date", (
+                SELECT COUNT(*) 
+                FROM procurement_rfq_attachments 
+                WHERE rfq_id = "rfqs"."id" AND vendor_id = "rfq_details"."vendors_id"
+            ) as "attachment_count", (
+                SELECT COUNT(*) > 0
+                FROM procurement_vendor_quotations
+                WHERE rfq_id = "rfqs"."id" AND vendor_id = "rfq_details"."vendors_id"
+            ) as "has_quotation", (
+                SELECT status
+                FROM procurement_vendor_quotations
+                WHERE rfq_id = "rfqs"."id" AND vendor_id = "rfq_details"."vendors_id"
+                ORDER BY created_at DESC LIMIT 1
+            ) as "quotation_status", (
+                SELECT total_price
+                FROM procurement_vendor_quotations
+                WHERE rfq_id = "rfqs"."id" AND vendor_id = "rfq_details"."vendors_id"
+                ORDER BY created_at DESC LIMIT 1
+            ) as "quotation_total_price" from "procurement_rfq_details" "rfq_details" left join "procurement_rfqs" "rfqs" on "rfq_details"."procurement_rfqs_id" = "rfqs"."id" left join "projects" on "rfqs"."project_id" = "projects"."id" left join "items" on "rfqs"."item_id" = "items"."id" left join "vendors" on "rfq_details"."vendors_id" = "vendors"."id" left join "payment_terms" on "rfq_details"."payment_terms_code" = "payment_terms"."code" left join "incoterms" on "rfq_details"."incoterms_code" = "incoterms"."code" left join "users" "updated_by_user" on "rfq_details"."updated_by" = "updated_by_user"."id");
\ No newline at end of file
-- 
cgit v1.2.3

