2 files changed, 204 insertions, 0 deletions

diff --git a/db/migrations/0097_poa_initial_setup.sql b/db/migrations/0097_poa_initial_setup.sql
new file mode 100644
index 00000000..fae3f4d1
--- /dev/null
+++ b/db/migrations/0097_poa_initial_setup.sql
@@ -0,0 +1,95 @@
+-- Drop existing tables and views
+DROP VIEW IF EXISTS change_orders_detail_view;
+DROP TABLE IF EXISTS change_order_items CASCADE;
+DROP TABLE IF EXISTS change_orders CASCADE;
+DROP VIEW IF EXISTS poa_detail_view;
+DROP TABLE IF EXISTS poa CASCADE;
+
+-- Create POA table
+CREATE TABLE poa (
+    id SERIAL PRIMARY KEY,
+    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 NOT NULL DEFAULT NOW(),
+    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
+    CONSTRAINT poa_original_contract_no_contracts_contract_no_fk 
+        FOREIGN KEY (original_contract_no) 
+        REFERENCES contracts(contract_no) 
+        ON DELETE CASCADE,
+    CONSTRAINT poa_project_id_projects_id_fk 
+        FOREIGN KEY (project_id) 
+        REFERENCES projects(id) 
+        ON DELETE CASCADE,
+    CONSTRAINT poa_vendor_id_vendors_id_fk 
+        FOREIGN KEY (vendor_id) 
+        REFERENCES vendors(id) 
+        ON DELETE CASCADE
+);
+
+-- Create POA detail view
+CREATE VIEW poa_detail_view AS
+SELECT 
+    -- POA primary information
+    poa.id,
+    poa.contract_no,
+    poa.change_reason,
+    poa.approval_status,
+
+    -- Original PO information
+    poa.original_contract_no,
+    poa.original_contract_name,
+    poa.original_status,
+    c.start_date as original_start_date,
+    c.end_date as original_end_date,
+
+    -- Project information
+    poa.project_id,
+    p.code as project_code,
+    p.name as project_name,
+
+    -- Vendor information
+    poa.vendor_id,
+    v.vendor_name,
+
+    -- Changed delivery details
+    poa.delivery_terms,
+    poa.delivery_date,
+    poa.delivery_location,
+
+    -- Changed financial information
+    poa.currency,
+    poa.total_amount,
+    poa.discount,
+    poa.tax,
+    poa.shipping_fee,
+    poa.net_total,
+
+    -- Timestamps
+    poa.created_at,
+    poa.updated_at,
+    
+    -- Electronic signature status
+    EXISTS (
+        SELECT 1 
+        FROM contract_envelopes 
+        WHERE contract_envelopes.contract_id = poa.id
+    ) as has_signature
+FROM poa
+LEFT JOIN contracts c ON poa.original_contract_no = c.contract_no
+LEFT JOIN projects p ON poa.project_id = p.id
+LEFT JOIN vendors v ON poa.vendor_id = v.id; 
\ No newline at end of file
diff --git a/db/seeds_2/poaSeed.ts b/db/seeds_2/poaSeed.ts
new file mode 100644
index 00000000..d93cde14
--- /dev/null
+++ b/db/seeds_2/poaSeed.ts
@@ -0,0 +1,109 @@
+import { faker } from "@faker-js/faker"
+import db from "../db"
+import { contracts, poa } from "../schema/contract"
+import { sql } from "drizzle-orm"
+
+export async function seedPOA({ count = 10 } = {}) {
+    try {
+        console.log(`📝 Inserting POA ${count}`)
+        
+        // 기존 POA 데이터 삭제 및 ID 시퀀스 초기화
+        await db.delete(poa)
+        await db.execute(sql`ALTER SEQUENCE poa_id_seq RESTART WITH 1;`)
+        console.log("✅ 기존 POA 데이터 삭제 및 ID 초기화 완료")
+        
+        // 조선업 맥락에 맞는 예시 문구들
+        const deliveryTermsExamples = [
+            "FOB 부산항",
+            "CIF 상하이항",
+            "DAP 울산조선소",
+            "DDP 거제 옥포조선소",
+        ]
+        const deliveryLocations = [
+            "부산 영도조선소",
+            "울산 본사 도크 #3",
+            "거제 옥포조선소 해양공장",
+            "목포신항 부두",
+        ]
+        const changeReasonExamples = [
+            "납품 일정 조정 필요",
+            "자재 사양 변경",
+            "선박 설계 변경에 따른 수정",
+            "추가 부품 요청",
+            "납품 장소 변경",
+            "계약 조건 재협상"
+        ]
+        
+        // 1. 기존 계약(PO) 목록 가져오기
+        const existingContracts = await db.select().from(contracts)
+        console.log(`Found ${existingContracts.length} existing contracts`)
+        
+        if (existingContracts.length === 0) {
+            throw new Error("계약(PO) 데이터가 없습니다. 먼저 계약 데이터를 생성해주세요.")
+        }
+
+        // 2. POA 생성
+        for (let i = 0; i < count; i++) {
+            try {
+                // 랜덤으로 원본 계약 선택
+                const originalContract = faker.helpers.arrayElement(existingContracts)
+                console.log(`Selected original contract: ${originalContract.contractNo}`)
+
+                // POA 생성
+                const totalAmount = faker.number.float({ min: 5000000, max: 500000000 })
+                const discount = faker.helpers.maybe(() => faker.number.float({ min: 0, max: 500000 }), { probability: 0.3 })
+                const tax = faker.helpers.maybe(() => faker.number.float({ min: 0, max: 1000000 }), { probability: 0.8 })
+                const shippingFee = faker.helpers.maybe(() => faker.number.float({ min: 0, max: 300000 }), { probability: 0.5 })
+                const netTotal = totalAmount - (discount || 0) + (tax || 0) + (shippingFee || 0)
+
+                const poaData = {
+                    // Form code는 원본과 동일하게 유지
+                    contractNo: originalContract.contractNo,
+                    originalContractNo: originalContract.contractNo,
+                    projectId: originalContract.projectId,
+                    vendorId: originalContract.vendorId,
+                    originalContractName: originalContract.contractName,
+                    originalStatus: originalContract.status,
+
+                    // 변경 가능한 정보들
+                    deliveryTerms: faker.helpers.arrayElement(deliveryTermsExamples),
+                    deliveryDate: faker.helpers.maybe(() => faker.date.future().toISOString(), { probability: 0.7 }),
+                    deliveryLocation: faker.helpers.arrayElement(deliveryLocations),
+                    currency: "KRW",
+                    totalAmount: totalAmount.toString(),
+                    discount: discount?.toString(),
+                    tax: tax?.toString(),
+                    shippingFee: shippingFee?.toString(),
+                    netTotal: netTotal.toString(),
+                    changeReason: faker.helpers.arrayElement(changeReasonExamples),
+                    approvalStatus: faker.helpers.arrayElement(["PENDING", "APPROVED", "REJECTED"]),
+                    createdAt: new Date(),
+                    updatedAt: new Date(),
+                }
+                
+                console.log("POA data:", poaData)
+                
+                await db.insert(poa).values(poaData)
+                console.log(`Created POA for contract: ${originalContract.contractNo}`)
+            } catch (error) {
+                console.error(`Error creating POA ${i + 1}:`, error)
+                throw error
+            }
+        }
+
+        console.log(`✅ Successfully added ${count} new POAs`)
+    } catch (error) {
+        console.error("Error in seedPOA:", error)
+        throw error
+    }
+}
+
+// 실행
+if (require.main === module) {
+    seedPOA({ count: 5 })
+        .then(() => process.exit(0))
+        .catch((error) => {
+            console.error(error)
+            process.exit(1)
+        })
+} 
\ No newline at end of file