path: root/db/schema/contract.ts

import {
    pgTable,
    text,
    varchar,
    timestamp,
    integer,
    numeric,
    date,
    boolean,
    unique,
    jsonb,
    uniqueIndex, pgView
} from "drizzle-orm/pg-core"
import { projects } from "./projects"
import { vendorContacts, vendors } from "./vendors"
import { eq, sql } from "drizzle-orm";

// ============ contracts (계약/PO 정보) ============
export const contracts = pgTable("contracts", {
    // 주 키
    id: integer("id").primaryKey().generatedAlwaysAsIdentity(),

    // 프로젝트와 벤더 참조
    projectId: integer("project_id")
        .notNull()
        .references(() => projects.id, { onDelete: "cascade" }),

    vendorId: integer("vendor_id")
        .notNull()
        .references(() => vendors.id, { onDelete: "cascade" }),

    // 계약/PO 번호(유니크)
    contractNo: varchar("contract_no", { length: 100 }).notNull().unique(),
    contractName: varchar("contract_name", { length: 255 }).notNull(),

    // 계약/PO 상태나 기간
    status: varchar("status", { length: 50 }).notNull().default("ACTIVE"),
    startDate: date("start_date"),      // 발주일(혹은 유효 시작일)
    endDate: date("end_date"),          // 계약 종료일/유효 기간 등

    // --- PO에 자주 쓰이는 필드 추가 ---
    paymentTerms: text("payment_terms"),        // 결제 조건(예: 30일 후 현금, 선금/잔금 등)
    deliveryTerms: text("delivery_terms"),      // 납품 조건(Incoterms 등)
    deliveryDate: date("delivery_date"),        // 납품 기한(납기 예정일)
    deliveryLocation: varchar("delivery_location", { length: 255 }), // 납품 장소

    // 가격/금액 관련
    currency: varchar("currency", { length: 10 }).default("KRW"),  // 통화 (KRW, USD 등)
    totalAmount: numeric("total_amount", { precision: 12, scale: 2 }), // 총 계약 금액(아이템 합산 등)
    discount: numeric("discount", { precision: 12, scale: 2 }), // 전체 할인
    tax: numeric("tax", { precision: 12, scale: 2 }),          // 전체 세금
    shippingFee: numeric("shipping_fee", { precision: 12, scale: 2 }), // 배송비
    netTotal: numeric("net_total", { precision: 12, scale: 2 }),  // (합계) - (할인) + (세금) + (배송비)

    // 부분 납품/부분 결제 가능 여부
    partialShippingAllowed: boolean("partial_shipping_allowed").default(false),
    partialPaymentAllowed: boolean("partial_payment_allowed").default(false),

    // 추가 메모/비고
    remarks: text("remarks"),

    // 버전 관리 (PO 재발행 등)
    version: integer("version").default(1),

    // 생성/수정 시각
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
})

// 타입 추론
export type Contract = typeof contracts.$inferSelect

// ============ contract_items (1:N 관계) ============
// 한 계약(contracts.id)에 여러 아이템을 연결
export const contractItems = pgTable("contract_items", {
    id: integer("id").primaryKey().generatedAlwaysAsIdentity(),

    contractId: integer("contract_id")
        .notNull()
        .references(() => contracts.id, { onDelete: "cascade" }),

    itemId: integer("item_id")
        .notNull()
    // .references(() => items.id, { onDelete: "cascade" })
    ,

    // --- 품목(아이템) 단위 정보 ---
    description: text("description"), // 품목 설명 (스펙, 모델명 등)
    quantity: integer("quantity").notNull().default(1),
    unitPrice: numeric("unit_price", { precision: 10, scale: 2 }),

    taxRate: numeric("tax_rate", { precision: 5, scale: 2 }),   // % (예: 10.00)
    taxAmount: numeric("tax_amount", { precision: 10, scale: 2 }), // 계산된 세금
    totalLineAmount: numeric("total_line_amount", { precision: 12, scale: 2 }), // (수량×단가±할인+세금) 등

    // 비고
    remark: text("remark"),

    // 생성/수정 시각
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
}, (t) => [
    // 같은 계약 + 같은 itemId에 대한 중복을 막음
    unique().on(t.contractId, t.itemId),
    uniqueIndex("contract_items_contract_item_idx").on(t.contractId, t.itemId),
])

export type ContractItem = typeof contractItems.$inferSelect

// ============ DocuSign 연동용 (전자서명 이력) ============

// Envelope(전자서명 요청) 테이블
export const contractEnvelopes = pgTable("contract_envelopes", {
    id: integer("id").primaryKey().generatedAlwaysAsIdentity(),

    // 연결된 계약
    contractId: integer("contract_id")
        .notNull()
        .references(() => contracts.id, { onDelete: "cascade" }),

    // DocuSign에서 발급되는 Envelope/Document 식별자
    envelopeId: varchar("envelope_id", { length: 200 }).notNull(),
    documentId: varchar("document_id", { length: 200 }),

    // Envelope 전체 상태 (예: sent, completed, voided ...)
    envelopeStatus: varchar("envelope_status", { length: 50 }),

    fileName: varchar("file_name", { length: 255 }).notNull(),
    filePath: varchar("file_path", { length: 1024 }).notNull(),
    // 생성/수정 시각
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

// 하나의 Envelope에 여러 서명자(사인 요청 대상)가 있을 수 있음
export const contractSigners = pgTable("contract_signers", {
    id: integer("id").primaryKey().generatedAlwaysAsIdentity(),

    // Envelope와 1:N 관계
    envelopeId: integer("envelope_id")
        .notNull()
        .references(() => contractEnvelopes.id, { onDelete: "cascade" }),

    // Reference to vendor_contacts table (optional - if signer is from vendor contacts)
    vendorContactId: integer("vendor_contact_id")
        .references(() => vendorContacts.id),

    // Is this signer from the requester (company) side or vendor side
    signerType: varchar("signer_type", {
        length: 20,
        enum: ["REQUESTER", "VENDOR"]
    }).notNull().default("VENDOR"),

    // 서명자 정보 (manual entry or populated from vendor contact)
    signerEmail: varchar("signer_email", { length: 255 }).notNull(),
    signerName: varchar("signer_name", { length: 100 }).notNull(),
    signerPosition: varchar("signer_position", { length: 100 }),

    // 서명자별 상태 (sent, delivered, signed, declined, etc.)
    signerStatus: varchar("signer_status", { length: 50 }).default("PENDING"),
    signedAt: timestamp("signed_at"),

    // 생성/수정 시각
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
});



export const contractsDetailView = pgView("contracts_detail_view").as((qb) => {
    return qb
      .select({
        // Contract primary information
        id: contracts.id,
        contractNo: contracts.contractNo,
        contractName: contracts.contractName,
        status: contracts.status,
        startDate: contracts.startDate,
        endDate: contracts.endDate,
  
        // Project information
        projectId: contracts.projectId,
        projectCode: projects.code,
        projectName: projects.name,
  
        // Vendor information
        vendorId: contracts.vendorId,
        vendorName: vendors.vendorName,
  
        // Payment and delivery details
        paymentTerms: contracts.paymentTerms,
        deliveryTerms: contracts.deliveryTerms,
        deliveryDate: contracts.deliveryDate,
        deliveryLocation: contracts.deliveryLocation,
  
        // Financial information
        currency: contracts.currency,
        totalAmount: contracts.totalAmount,
        discount: contracts.discount,
        tax: contracts.tax,
        shippingFee: contracts.shippingFee,
        netTotal: contracts.netTotal,
  
        // Additional settings
        partialShippingAllowed: contracts.partialShippingAllowed,
        partialPaymentAllowed: contracts.partialPaymentAllowed,
        remarks: contracts.remarks,
        version: contracts.version,
  
        // Timestamps
        createdAt: contracts.createdAt,
        updatedAt: contracts.updatedAt,
  
        // Electronic signature status
        hasSignature: sql<boolean>`EXISTS (
          SELECT 1 
          FROM ${contractEnvelopes} 
          WHERE ${contractEnvelopes.contractId} = ${contracts.id}
        )`.as("has_signature"),
        // hasItme: sql<boolean>`EXISTS (
        //     SELECT 1 
        //     FROM ${contractItems} 
        //     WHERE ${contractItems.contractId} = ${contracts.id}
        //   )`.as("has_signature"),
  
        // =========================
        // 1) contract_items -> JSON
        // =========================
        // 'items' (or "contractItems")라는 필드를 JSON 배열로 가져오기
        items: sql<string>`COALESCE((
          SELECT json_agg(
            json_build_object(
              'id', ci.id,
              'itemId', ci.item_id,
              'description', ci.description,
              'quantity', ci.quantity,
              'unitPrice', ci.unit_price,
              'taxRate', ci.tax_rate,
              'taxAmount', ci.tax_amount,
              'totalLineAmount', ci.total_line_amount,
              'remark', ci.remark,
              'createdAt', ci.created_at,
              'updatedAt', ci.updated_at
            )
          )
          FROM ${contractItems} AS ci
          WHERE ci.contract_id = ${contracts.id}
        ), '[]')`.as("items"),
  
        // =========================
        // 2) contract_envelopes -> JSON
        // =========================
        envelopes: sql<string>`COALESCE((
          SELECT json_agg(
            json_build_object(
              'id', ce.id,
              'envelopeId', ce.envelope_id,
              'documentId', ce.document_id,
              'envelopeStatus', ce.envelope_status,
              'fileName', ce.file_name,
              'filePath', ce.file_path,
              'createdAt', ce.created_at,
              'updatedAt', ce.updated_at,
              'signers', (
                SELECT json_agg(
                  json_build_object(
                    'id', cs.id,
                    'vendorContactId', cs.vendor_contact_id,
                    'signerType', cs.signer_type,
                    'signerEmail', cs.signer_email,
                    'signerName', cs.signer_name,
                    'signerPosition', cs.signer_position,
                    'signerStatus', cs.signer_status,
                    'signedAt', cs.signed_at
                  )
                )
                FROM ${contractSigners} AS cs
                WHERE cs.envelope_id = ce.id
              )
            )
          )
          FROM ${contractEnvelopes} AS ce
          WHERE ce.contract_id = ${contracts.id}
        ), '[]')`.as("envelopes"),
      })
      .from(contracts)
      .leftJoin(projects, eq(contracts.projectId, projects.id))
      .leftJoin(vendors, eq(contracts.vendorId, vendors.id))
  });

  export interface ContractItemParsed {
    id: number
    itemId: number
    description: string | null
    quantity: number
    unitPrice: number | null
    taxRate: number | null
    taxAmount: number | null
    totalLineAmount: number | null
    remark: string | null
    createdAt: string
    updatedAt: string
  }
  
  // 기존 Envelope + Signers
  export interface Envelope {
    id: number
    envelopeId: string
    documentId: string | null
    envelopeStatus: string | null
    fileName: string
    filePath: string
    createdAt: string
    updatedAt: string
    signers?: {
      id: number
      vendorContactId: number | null
      signerType: string
      signerEmail: string
      signerName: string
      signerPosition: string | null
      signerStatus: string
      signedAt: string | null
    }[]
  }
  
  // Drizzle가 만들어준 raw type
  export type ContractDetail = typeof contractsDetailView.$inferSelect;
  
  // 우리가 UI에서 쓰고 싶은 파싱된 타입
  export type ContractDetailParsed = Omit<ContractDetail, "envelopes" | "items"> & {
    envelopes: Envelope[]
    items: ContractItemParsed[]
  }