From 9ceed79cf32c896f8a998399bf1b296506b2cd4a Mon Sep 17 00:00:00 2001
From: dujinkim <dujin.kim@dtsolution.co.kr>
Date: Tue, 8 Apr 2025 03:08:19 +0000
Subject: 로그인 및 미들웨어 처리. 구조 변경
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

---
 db/migrations_backup/0096_real_inhumans.sql | 85 +++++++++++++++++++++++++++++
 1 file changed, 85 insertions(+)
 create mode 100644 db/migrations_backup/0096_real_inhumans.sql

(limited to 'db/migrations_backup/0096_real_inhumans.sql')

diff --git a/db/migrations_backup/0096_real_inhumans.sql b/db/migrations_backup/0096_real_inhumans.sql
new file mode 100644
index 00000000..d97dc83e
--- /dev/null
+++ b/db/migrations_backup/0096_real_inhumans.sql
@@ -0,0 +1,85 @@
+CREATE TABLE "poa" (
+	"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "poa_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
+	"contract_no" varchar(100) NOT NULL,
+	"original_contract_no" varchar(100) NOT NULL,
+	"project_id" integer NOT NULL,
+	"vendor_id" integer NOT NULL,
+	"original_contract_name" varchar(255) NOT NULL,
+	"original_status" varchar(50) NOT NULL,
+	"delivery_terms" text,
+	"delivery_date" date,
+	"delivery_location" varchar(255),
+	"currency" varchar(10),
+	"total_amount" numeric(12, 2),
+	"discount" numeric(12, 2),
+	"tax" numeric(12, 2),
+	"shipping_fee" numeric(12, 2),
+	"net_total" numeric(12, 2),
+	"change_reason" text,
+	"approval_status" varchar(50) DEFAULT 'PENDING',
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "vendor_investigation_attachments" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"investigation_id" integer NOT NULL,
+	"file_name" varchar(255) NOT NULL,
+	"file_path" varchar(1024) NOT NULL,
+	"attachment_type" varchar(50) DEFAULT 'REPORT',
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+CREATE TABLE "vendor_investigations" (
+	"id" serial PRIMARY KEY NOT NULL,
+	"vendor_id" integer NOT NULL,
+	"status" varchar(50) DEFAULT 'PLANNED' NOT NULL,
+	"scheduled_start_at" timestamp,
+	"scheduled_end_at" timestamp,
+	"completed_at" timestamp,
+	"investigation_notes" text,
+	"created_at" timestamp DEFAULT now() NOT NULL,
+	"updated_at" timestamp DEFAULT now() NOT NULL
+);
+--> statement-breakpoint
+ALTER TABLE "poa" ADD CONSTRAINT "poa_original_contract_no_contracts_contract_no_fk" FOREIGN KEY ("original_contract_no") REFERENCES "public"."contracts"("contract_no") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "poa" ADD CONSTRAINT "poa_project_id_projects_id_fk" FOREIGN KEY ("project_id") REFERENCES "public"."projects"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "poa" ADD CONSTRAINT "poa_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_investigation_attachments" ADD CONSTRAINT "vendor_investigation_attachments_investigation_id_vendor_investigations_id_fk" FOREIGN KEY ("investigation_id") REFERENCES "public"."vendor_investigations"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
+ALTER TABLE "vendor_investigations" ADD CONSTRAINT "vendor_investigations_vendor_id_vendors_id_fk" FOREIGN KEY ("vendor_id") REFERENCES "public"."vendors"("id") ON DELETE no action ON UPDATE no action;--> statement-breakpoint
+CREATE VIEW "public"."poa_detail_view" AS (select "poa"."id", "poa"."contract_no", "contracts"."project_id", "contracts"."vendor_id", "poa"."change_reason", "poa"."approval_status", "contracts"."contract_name" as "original_contract_name", "contracts"."status" as "original_status", "contracts"."start_date" as "original_start_date", "contracts"."end_date" as "original_end_date", "poa"."delivery_terms", "poa"."delivery_date", "poa"."delivery_location", "poa"."currency", "poa"."total_amount", "poa"."discount", "poa"."tax", "poa"."shipping_fee", "poa"."net_total", "poa"."created_at", "poa"."updated_at", EXISTS (
+              SELECT 1 
+              FROM "contract_envelopes" 
+              WHERE "contract_envelopes"."contract_id" = "poa"."id"
+          ) as "has_signature" from "poa" left join "contracts" on "poa"."contract_no" = "contracts"."contract_no");--> statement-breakpoint
+CREATE VIEW "public"."vendor_investigations_view" AS (select "vendor_investigations"."id", "vendor_investigations"."status", "vendor_investigations"."scheduled_start_at", "vendor_investigations"."scheduled_end_at", "vendor_investigations"."completed_at", "vendor_investigations"."investigation_notes", "vendor_investigations"."created_at", "vendor_investigations"."updated_at", "vendors"."id", "vendors"."vendor_name", "vendors"."vendor_code", "vendors"."tax_id", "vendors"."status", "vendors"."country", "vendors"."email", "vendors"."phone", "vendors"."website", "vendors"."created_at", "vendors"."updated_at", (
+        SELECT COALESCE(
+          json_agg(json_build_object(
+            'id', c.id,
+            'contactName', c.contact_name,
+            'contactEmail', c.contact_email,
+            'contactPhone', c.contact_phone,
+            'contactPosition', c.contact_position,
+            'isPrimary', c.is_primary,
+            'createdAt', c.created_at,
+            'updatedAt', c.updated_at
+          )),
+          '[]'::json
+        )
+        FROM vendor_contacts c
+        WHERE c.vendor_id = "vendors"."id"
+      ) as "contacts", (
+        SELECT COALESCE(
+          json_agg(json_build_object(
+            'id', pi.id,
+            'vendorId', pi.vendor_id,
+            'itemCode', pi.item_code,
+            'createdAt', pi.created_at,
+            'updatedAt', pi.updated_at
+          )),
+          '[]'::json
+        )
+        FROM vendor_possible_items pi
+        WHERE pi.vendor_id = "vendors"."id"
+      ) as "possibleItems" from "vendor_investigations" left join "vendors" on "vendor_investigations"."vendor_id" = "vendors"."id");
\ No newline at end of file
-- 
cgit v1.2.3

