path: root/db/schema/vendorDocu.ts

import { pgTable, integer, varchar, timestamp, date ,pgView,uniqueIndex ,jsonb} from "drizzle-orm/pg-core"
import { eq , sql} from "drizzle-orm";
import { projects } from "./projects";
import { vendors } from "./vendors";
import { contracts } from "./contract";


export const documents = pgTable(
  "documents",
  {
    // 주 키
    id: integer("id").primaryKey().generatedAlwaysAsIdentity(),

    // 어느 계약(Contract) 소속인지
    contractId: integer("contract_id")
      .notNull()
      .references(() => contracts.id, { onDelete: "cascade" }),

    // 예: 문서 번호(유니크 설정 가능)
    docNumber: varchar("doc_number", { length: 100 }).notNull(),
    // 예: 문서(도서) 제목
    title: varchar("title", { length: 255 }).notNull(),

    // 추가 예시: 발행일, 상태 등
    status: varchar("status", { length: 50 })
      .notNull()
      .default("ACTIVE"),
    issuedDate: date("issued_date"),

    // 생성/수정 시각
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
  },
  (table) => {
    return {
      // contractId + docNumber + status 복합 유니크
      uniqueContractDocStatus: uniqueIndex("unique_contract_doc_status").on(
        table.contractId,
        table.docNumber,
        table.status
      ),
    }
  }
)

export const issueStages = pgTable(
  "issue_stages",
  {
    // 주 키
    id: integer("id").primaryKey().generatedAlwaysAsIdentity(),

    // 어느 문서 소속인지
    documentId: integer("document_id")
      .notNull()
      .references(() => documents.id, { onDelete: "cascade" }),

    // 스테이지명 (예: "Issued for Review", "AFC" 등)
    stageName: varchar("stage_name", { length: 100 }).notNull(),

    // 계획일, 실제일
    planDate: date("plan_date"),
    actualDate: date("actual_date"),

    // 생성/수정 시각
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
  },
  (table) => {
    return {
      // document_id + stage_name 조합을 유니크 인덱스로 지정
      uniqueDocumentStage: uniqueIndex("unique_document_stage").on(
        table.documentId,
        table.stageName
      ),
    }
  }
);

  export const revisions = pgTable(
    "revisions",
    {
      id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
      issueStageId: integer("issue_stage_id").notNull(),
      revision: varchar("revision", { length: 50 }).notNull(),
      // 새로운 필드: 업로더 타입 (업체 또는 고객사)
      uploaderType: varchar("uploader_type", { length: 20 }).notNull().default("vendor"),
      // 선택적: 업로더 ID 또는 이름
      uploaderId: integer("uploader_id"),
      uploaderName: varchar("uploader_name", { length: 100 }),
      // 선택적: 추가 메타데이터
      comment: varchar("comment", { length: 500 }),
      status: varchar("status", { length: 50 }),
      approvedDate: date("approved_date"),
      createdAt: timestamp("created_at").defaultNow().notNull(),
      updatedAt: timestamp("updated_at").defaultNow().notNull(),
    },
    (table) => {
      return {
        // "issue_stage_id + revision" 조합을 유니크로 묶음 (유지)
        uniqueStageRev: uniqueIndex("unique_stage_rev").on(
          table.issueStageId,
          table.revision
        ),
      }
    }
  )
  export const documentAttachments = pgTable(
    "document_attachments",
    {
      id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
      revisionId: integer("revision_id")
        .notNull()
        .references(() => revisions.id, { onDelete: "cascade" }),
      fileName: varchar("file_name", { length: 255 }).notNull(),
      filePath: varchar("file_path", { length: 1024 }).notNull(),
      fileType: varchar("file_type", { length: 50 }), 
      fileSize: integer("file_size"), 
      createdAt: timestamp("created_at").defaultNow().notNull(),
      updatedAt: timestamp("updated_at").defaultNow().notNull(),
    }
  )
  

  // export const vendorDocumentsView = pgTable("vendor_documents", {
  //   vendorId: integer("vendor_id").notNull(),
  //   id: integer("id").notNull(),
  //   docNumber: varchar("doc_number", { length: 100 }).notNull(),
  //   title: varchar("title", { length: 255 }).notNull(),
  
  //   // 새로 추가된 컬럼들 (contractId, contractNo, contractName, status)
  //   contractId: integer("contract_id").notNull(),
  //   contractNo: varchar("contract_no", { length: 100 }).notNull(),
  //   contractName: varchar("contract_name", { length: 255 }).notNull(),
  //   status: varchar("status", { length: 50 }).notNull(),
    
  //   createdAt: timestamp("created_at", { withTimezone: true }),
  //   updatedAt: timestamp("updated_at", { withTimezone: true }),
    
  // })


  export const vendorDocumentsView = pgView("vendor_documents_view", {
    // Match the columns in your SELECT statement
    id: integer("id").notNull(),
    docNumber: varchar("doc_number", { length: 100 }).notNull(),
    title: varchar("title", { length: 255 }).notNull(),
    status: varchar("status", { length: 50 }).notNull(),
    issuedDate: date("issued_date"),
  
    contractId: integer("contract_id").notNull(),
  
    latestStageId: integer("latest_stage_id"), // possibly can be null
    latestStageName: varchar("latest_stage_name", { length: 100 }),
    latestStagePlanDate: date("latest_stage_plan_date"),
    latestStageActualDate: date("latest_stage_actual_date"),
  
    latestRevisionId: integer("latest_revision_id"),
    latestRevision: varchar("latest_revision", { length: 50 }),
    latestRevisionUploaderType: varchar("latest_revision_uploader_type", { length: 20 }),
    latestRevisionUploaderName: varchar("latest_revision_uploader_name", { length: 100 }),
  
    attachmentCount: integer("attachment_count"),
  
    createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
    updatedAt: timestamp("updated_at", { withTimezone: true }).notNull(),
  }).as(sql`
    SELECT 
      d.id, 
      d.doc_number,
      d.title,
      d.status,
      d.issued_date,
  
      d.contract_id,
  
      (
        SELECT id FROM issue_stages
        WHERE document_id = d.id
        ORDER BY created_at DESC LIMIT 1
      ) AS latest_stage_id,
      (
        SELECT stage_name FROM issue_stages
        WHERE document_id = d.id
        ORDER BY created_at DESC LIMIT 1
      ) AS latest_stage_name,
      (
        SELECT plan_date FROM issue_stages
        WHERE document_id = d.id
        ORDER BY created_at DESC LIMIT 1
      ) AS latest_stage_plan_date,
      (
        SELECT actual_date FROM issue_stages
        WHERE document_id = d.id
        ORDER BY created_at DESC LIMIT 1
      ) AS latest_stage_actual_date,
  
      (
        SELECT r.id FROM revisions r
        JOIN issue_stages i ON r.issue_stage_id = i.id
        WHERE i.document_id = d.id
        ORDER BY r.created_at DESC LIMIT 1
      ) AS latest_revision_id,
      (
        SELECT r.revision FROM revisions r
        JOIN issue_stages i ON r.issue_stage_id = i.id
        WHERE i.document_id = d.id
        ORDER BY r.created_at DESC LIMIT 1
      ) AS latest_revision,
      (
        SELECT r.uploader_type FROM revisions r
        JOIN issue_stages i ON r.issue_stage_id = i.id
        WHERE i.document_id = d.id
        ORDER BY r.created_at DESC LIMIT 1
      ) AS latest_revision_uploader_type,
      (
        SELECT r.uploader_name FROM revisions r
        JOIN issue_stages i ON r.issue_stage_id = i.id
        WHERE i.document_id = d.id
        ORDER BY r.created_at DESC LIMIT 1
      ) AS latest_revision_uploader_name,
  
      (
        SELECT COUNT(*) FROM document_attachments a
        JOIN revisions r ON a.revision_id = r.id
        JOIN issue_stages i ON r.issue_stage_id = i.id
        WHERE i.document_id = d.id
      ) AS attachment_count,
  
      d.created_at,
      d.updated_at
    FROM documents d
    JOIN contracts c ON d.contract_id = c.id
  `);



// 문서 + 스테이지 리스트 뷰
export const documentStagesView = pgView("document_stages_view", {
  documentId: integer("document_id").notNull(),
  docNumber: varchar("doc_number", { length: 100 }).notNull(),
  title: varchar("title", { length: 255 }).notNull(),
  status: varchar("status", { length: 50 }).notNull(),
  issuedDate: date("issued_date"),
  contractId: integer("contract_id").notNull(),
  stageCount: integer("stage_count").notNull(),

  // 문자열 배열을 받을 것이므로 jsonb + $type<string[]>()
  // 스테이지가 없으면 null이 올 수도 있다면 string[] | null
  stageList: jsonb("stage_list").$type<string[] | null>(),

  createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).notNull(),
}).as(sql`
  SELECT
    d.id AS document_id,
    d.doc_number,
    d.title,
    d.status,
    d.issued_date,
    d.contract_id,

    (
      SELECT COUNT(*)
      FROM issue_stages
      WHERE document_id = d.id
    ) AS stage_count,

    COALESCE( 
      (
        SELECT json_agg(i.stage_name)
        FROM issue_stages i
        WHERE i.document_id = d.id
      ), 
      '[]'
    ) AS stage_list,

    d.created_at,
    d.updated_at
  FROM documents d
`);


  export type VendorDocumentsView = typeof vendorDocumentsView.$inferSelect
  export type DocumentStagesView = typeof documentStagesView.$inferSelect