initial commit

1 files changed, 285 insertions, 0 deletions

diff --git a/db/schema/vendorDocu.ts b/db/schema/vendorDocu.ts
new file mode 100644
index 00000000..2e6ba5a1
--- /dev/null
+++ b/db/schema/vendorDocu.ts
@@ -0,0 +1,285 @@
+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
+  
\ No newline at end of file