summaryrefslogtreecommitdiff
path: root/db/schema
diff options
context:
space:
mode:
Diffstat (limited to 'db/schema')
-rw-r--r--db/schema/companies.ts13
-rw-r--r--db/schema/contract.ts227
-rw-r--r--db/schema/items.ts12
-rw-r--r--db/schema/pq.ts67
-rw-r--r--db/schema/projects.ts13
-rw-r--r--db/schema/rfq.ts743
-rw-r--r--db/schema/tasks.ts46
-rw-r--r--db/schema/users.ts147
-rw-r--r--db/schema/vendorData.ts205
-rw-r--r--db/schema/vendorDocu.ts285
-rw-r--r--db/schema/vendors.ts118
11 files changed, 1876 insertions, 0 deletions
diff --git a/db/schema/companies.ts b/db/schema/companies.ts
new file mode 100644
index 00000000..60f8a0ce
--- /dev/null
+++ b/db/schema/companies.ts
@@ -0,0 +1,13 @@
+import { pgTable, varchar, integer, timestamp } from "drizzle-orm/pg-core";
+
+export const companies = pgTable("companies", {
+ id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
+ name: varchar("name", { length: 255 }).notNull(),
+ taxID: integer("taxID").notNull(),
+ // 회사가 여러 군데일 경우를 대비하여 unique는 걸지 않을 수도 있음
+ createdAt: timestamp("created_at", { withTimezone: true })
+ .defaultNow()
+ .notNull(),
+});
+
+export type Company = typeof companies.$inferSelect
diff --git a/db/schema/contract.ts b/db/schema/contract.ts
new file mode 100644
index 00000000..3c29f0d0
--- /dev/null
+++ b/db/schema/contract.ts
@@ -0,0 +1,227 @@
+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 - ADDED .as('has_signature') here
+ hasSignature: sql<boolean>`EXISTS (
+ SELECT 1
+ FROM ${contractEnvelopes}
+ WHERE ${contractEnvelopes.contractId} = ${contracts.id}
+ )`.as('has_signature'),
+ })
+ .from(contracts)
+ .leftJoin(projects, eq(contracts.projectId, projects.id))
+ .leftJoin(vendors, eq(contracts.vendorId, vendors.id))
+});
+
+// Type inference for the view
+export type ContractDetail = typeof contractsDetailView.$inferSelect; \ No newline at end of file
diff --git a/db/schema/items.ts b/db/schema/items.ts
new file mode 100644
index 00000000..1d4b0ded
--- /dev/null
+++ b/db/schema/items.ts
@@ -0,0 +1,12 @@
+import { pgTable, varchar, text, timestamp ,serial} from "drizzle-orm/pg-core"
+
+export const items = pgTable("items", {
+ id: serial("id").primaryKey(),
+ itemCode: varchar("item_code", { length: 100 }).unique(),
+ itemName: varchar("item_name", { length: 255 }).notNull(),
+ description: text("description"),
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ updatedAt: timestamp("updated_at").defaultNow().notNull(),
+});
+
+export type Item = typeof items.$inferSelect
diff --git a/db/schema/pq.ts b/db/schema/pq.ts
new file mode 100644
index 00000000..59ec8f07
--- /dev/null
+++ b/db/schema/pq.ts
@@ -0,0 +1,67 @@
+import {
+ pgTable, serial, integer, varchar, text,
+ timestamp, uniqueIndex
+} from "drizzle-orm/pg-core";
+import { vendors } from "./vendors";
+
+export const pqCriterias = pgTable("pq_criterias", {
+ id: serial("id").primaryKey(),
+ code: varchar("code", { length: 50 }).notNull(), // 예: "1-1"
+ checkPoint: varchar("check_point", { length: 255 }).notNull(),
+ description: text("description"),
+ remarks: text("remarks"),
+
+ // (선택) "GENERAL", "Quality Management System" 등 큰 분류
+ groupName: varchar("group_name", { length: 255 }),
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ updatedAt: timestamp("updated_at").defaultNow().notNull(),
+});
+
+export const vendorPqCriteriaAnswers = pgTable("vendor_pq_criteria_answers", {
+ id: serial("id").primaryKey(),
+ vendorId: integer("vendor_id")
+ .notNull()
+ .references(() => vendors.id, { onDelete: "cascade" }),
+
+ criteriaId: integer("criteria_id")
+ .notNull()
+ .references(() => pqCriterias.id, { onDelete: "cascade" }),
+
+ answer: text("answer"),
+
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ updatedAt: timestamp("updated_at").defaultNow().notNull(),
+});
+
+export const vendorCriteriaAttachments = pgTable("vendor_criteria_attachments", {
+ id: serial("id").primaryKey(),
+ vendorCriteriaAnswerId: integer("vendor_criteria_answer_id")
+ .references(() => vendorPqCriteriaAnswers.id, { onDelete: "cascade" })
+ .notNull(),
+
+ 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 vendorPqReviewLogs = pgTable("vendor_pq_review_logs", {
+ id: serial("id").primaryKey(),
+
+ // Each log references a single vendorPqCriteriaAnswers row
+ vendorPqCriteriaAnswerId: integer("vendor_pq_criteria_answer_id")
+ .references(() => vendorPqCriteriaAnswers.id, { onDelete: "cascade" })
+ .notNull(),
+
+ // The reviewer's comment text
+ reviewerComment: text("reviewer_comment").notNull(),
+
+ // Optionally store the reviewer name or user ID, if you have it
+ reviewerName: text("reviewer_name"),
+
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ })
+export type PqCriterias = typeof pqCriterias.$inferSelect
diff --git a/db/schema/projects.ts b/db/schema/projects.ts
new file mode 100644
index 00000000..9e253d77
--- /dev/null
+++ b/db/schema/projects.ts
@@ -0,0 +1,13 @@
+import { pgTable, varchar, text, timestamp ,serial} from "drizzle-orm/pg-core"
+
+export const projects = pgTable("projects", {
+ id: serial("id").primaryKey(),
+ code: varchar("code", { length: 50 }).notNull(),
+ name: text("name").notNull(),
+ type: varchar("type", { length: 20 }).default("ship").notNull(),
+
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ updatedAt: timestamp("updated_at").defaultNow().notNull(),
+ })
+
+ export type Project = typeof projects.$inferSelect \ No newline at end of file
diff --git a/db/schema/rfq.ts b/db/schema/rfq.ts
new file mode 100644
index 00000000..98c4245c
--- /dev/null
+++ b/db/schema/rfq.ts
@@ -0,0 +1,743 @@
+import { pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, uniqueIndex, foreignKey } from "drizzle-orm/pg-core";
+import { vendors } from "./vendors";
+import { users } from "./users";
+import { items } from "./items";
+import { eq, sql, and } from "drizzle-orm";
+import { projects } from "./projects";
+
+export const rfqs = pgTable(
+ "rfqs",
+ {
+ id: serial("id").primaryKey(),
+
+ // RFQ 고유 코드
+ rfqCode: varchar("rfq_code", { length: 50 }).unique(), // ex) "RFQ-2025-001"
+
+ // 프로젝트 참조
+ projectId: integer("project_id")
+ .references(() => projects.id, { onDelete: "set null" }),
+
+ description: varchar("description", { length: 255 }),
+
+ dueDate: date("due_date", { mode: "date" })
+ .$type<Date>()
+ .notNull(),
+
+ status: varchar("status", { length: 30 })
+ .$type<"DRAFT" | "PUBLISHED" | "EVALUATION" | "AWARDED">()
+ .default("DRAFT")
+ .notNull(),
+
+ // Budgetary / Purchase 여부
+ rfqType: varchar("rfq_type", { length: 30 }).default("PURCHASE"),
+
+ // 자기 자신 테이블 FK(부모 RFQ)
+ parentRfqId: integer("parent_rfq_id"),
+
+ // 생성자
+ createdBy: integer("created_by")
+ .notNull()
+ .references(() => users.id, { onDelete: "set null" }),
+
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ updatedAt: timestamp("updated_at").defaultNow().notNull(),
+ },
+ // <==== 여기서 자기참조 FK를 정의 (수정됨)
+ (table) => {
+ return {
+ // parentRfqId -> rfqs.id
+ parentFk: foreignKey({
+ columns: [table.parentRfqId],
+ foreignColumns: [table.id],
+ }).onDelete("set null"), // 메서드 체이닝 방식으로 변경
+ };
+ }
+);
+
+export const rfqItems = pgTable("rfq_items", {
+ id: serial("id").primaryKey(),
+ rfqId: integer("rfq_id")
+ .notNull()
+ .references(() => rfqs.id, { onDelete: "cascade" }),
+ itemCode: varchar("item_code", { length: 100 })
+ .notNull()
+ .references(() => items.itemCode, { onDelete: "cascade" }),
+ description: text("description"),
+ quantity: numeric("quantity", { precision: 12, scale: 2 })
+ .$type<number>()
+ .default(1),
+ uom: varchar("uom", { length: 50 }), // 단위
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ updatedAt: timestamp("updated_at").defaultNow().notNull(),
+});
+
+
+export const rfqAttachments = pgTable("rfq_attachments", {
+ id: serial("id").primaryKey(),
+ rfqId: integer("rfq_id").references(() => rfqs.id),
+ vendorId: integer("vendor_id").references(() => vendors.id),
+ fileName: varchar("file_name", { length: 255 }).notNull(),
+ filePath: varchar("file_path", { length: 1024 }).notNull(),
+ evaluationId: integer("evaluation_id")
+ .references(() => rfqEvaluations.id)
+ .$type<number | null>(),
+ cbeId: integer("cbe_id")
+ .references(() => cbeEvaluations.id)
+ .$type<number | null>(),
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ commentId: integer("comment_id")
+ .references(() => rfqComments.id)
+ .$type<number | null>(),
+});
+
+export const rfqComments = pgTable("rfq_comments", {
+ id: serial("id").primaryKey(),
+ rfqId: integer("rfq_id").references(() => rfqs.id),
+ vendorId: integer("vendor_id").references(() => vendors.id),
+ commentText: text("comment_text").notNull(),
+ commentedBy: integer("commented_by")
+ .references(() => users.id) // users 테이블의 id 필드 참조 추가
+ .notNull(),
+ // 아래처럼, 평가(TBE/CBE)에 속한 코멘트인지 여부를 구분할 필드
+ evaluationId: integer("evaluation_id")
+ .references(() => rfqEvaluations.id)
+ .$type<number | null>(),
+ cbeId: integer("cbe_id")
+ .references(() => cbeEvaluations.id)
+ .$type<number | null>(),
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ updatedAt: timestamp("updated_at").defaultNow().notNull(),
+});
+
+export const rfqEvaluations = pgTable("rfq_evaluations", {
+ id: serial("id").primaryKey(),
+ rfqId: integer("rfq_id")
+ .notNull()
+ .references(() => rfqs.id),
+ vendorId: integer("vendor_id")
+ .notNull()
+ .references(() => vendors.id),
+ evalType: varchar("eval_type", { length: 30 })
+ .$type<"TBE" | "CBE">(),
+ // TBE or CBE (기술 / 상업)
+ result: varchar("result", { length: 255 }), // 예: "PASS", "FAIL", "ACCEPTABLE" 등
+ notes: text("notes"),
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ updatedAt: timestamp("updated_at").defaultNow().notNull(),
+});
+
+
+// General vendor response to RFQ invitation
+export const vendorResponses = pgTable(
+ "vendor_responses",
+ {
+ id: serial("id").primaryKey(),
+
+ rfqId: integer("rfq_id")
+ .notNull()
+ .references(() => rfqs.id, { onDelete: "cascade" }),
+
+ vendorId: integer("vendor_id")
+ .notNull()
+ .references(() => vendors.id, { onDelete: "cascade" }),
+
+ // Vendor's overall response status
+ responseStatus: varchar("response_status", { length: 30 })
+ .$type<"INVITED" | "ACCEPTED" | "DECLINED" | "REVIEWING" | "RESPONDED">()
+ .default("REVIEWING")
+ .notNull(),
+
+ // General notes from vendor
+ notes: text("notes"),
+
+ // Metadata
+ respondedBy: varchar("responded_by", { length: 255 }), // Could be vendor contact name
+ respondedAt: timestamp("responded_at").defaultNow().notNull(),
+ updatedAt: timestamp("updated_at").defaultNow().notNull(),
+ },
+ (table) => ({
+ // Each vendor can only have one response per RFQ
+ uniqueConstraint: uniqueIndex("vendor_response_unique").on(
+ table.rfqId,
+ table.vendorId
+ ),
+ })
+);
+
+// Technical response details from vendor
+export const vendorTechnicalResponses = pgTable(
+ "vendor_technical_responses",
+ {
+ id: serial("id").primaryKey(),
+
+ // Link to main response
+ responseId: integer("response_id")
+ .notNull()
+ .references(() => vendorResponses.id, { onDelete: "cascade" }),
+
+ // 간소화된 기술 응답 필드
+ // 기술 내용은 주로 첨부 파일로 받으므로 최소한의 필드만 유지
+ summary: text("summary"), // 간단한 기술 응답 요약
+ notes: text("notes"), // 추가 노트나 코멘트
+
+ // Metadata
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ updatedAt: timestamp("updated_at").defaultNow().notNull(),
+ }
+);
+
+// Commercial response details from vendor
+export const vendorCommercialResponses = pgTable(
+ "vendor_commercial_responses",
+ {
+ id: serial("id").primaryKey(),
+
+ // Link to main response
+ responseId: integer("response_id")
+ .notNull()
+ .references(() => vendorResponses.id, { onDelete: "cascade" }),
+
+ // Commercial response fields
+ totalPrice: numeric("total_price", { precision: 18, scale: 2 }).$type<number>(),
+ currency: varchar("currency", { length: 10 }).default("USD"),
+
+ // Commercial terms
+ paymentTerms: varchar("payment_terms", { length: 255 }),
+ incoterms: varchar("incoterms", { length: 50 }),
+ deliveryPeriod: varchar("delivery_period", { length: 100 }),
+ warrantyPeriod: varchar("warranty_period", { length: 100 }),
+
+ // Validity of offer
+ validityPeriod: varchar("validity_period", { length: 100 }),
+
+ // Price breakdown or other commercial notes
+ priceBreakdown: text("price_breakdown"),
+ commercialNotes: text("commercial_notes"),
+
+ // Metadata
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ updatedAt: timestamp("updated_at").defaultNow().notNull(),
+ }
+);
+
+
+// Attachment table for vendor responses
+export const vendorResponseAttachments = pgTable(
+ "vendor_response_attachments",
+ {
+ id: serial("id").primaryKey(),
+
+ // Can be linked to any type of response
+ responseId: integer("response_id")
+ .references(() => vendorResponses.id, { onDelete: "cascade" }),
+ technicalResponseId: integer("technical_response_id")
+ .references(() => vendorTechnicalResponses.id, { onDelete: "cascade" }),
+ commercialResponseId: integer("commercial_response_id")
+ .references(() => vendorCommercialResponses.id, { onDelete: "cascade" }),
+
+ // File details
+ fileName: varchar("file_name", { length: 255 }).notNull(),
+ filePath: varchar("file_path", { length: 1024 }).notNull(),
+ fileType: varchar("file_type", { length: 50 }),
+
+ // 첨부 파일 분류
+ attachmentType: varchar("attachment_type", { length: 50 })
+ .$type<"TECHNICAL_SPEC" | "QUALITY_CERT" | "COMPLIANCE_DOC" | "COMMERCIAL" | "OTHER">(),
+ description: varchar("description", { length: 255 }),
+
+ // Metadata
+ uploadedAt: timestamp("uploaded_at").defaultNow().notNull(),
+ uploadedBy: varchar("uploaded_by", { length: 255 }),
+ }
+);
+
+
+export const cbeEvaluations = pgTable("cbe_evaluations", {
+ id: serial("id").primaryKey(),
+
+ // 어떤 RFQ에 대한 CBE인지
+ rfqId: integer("rfq_id")
+ .notNull()
+ .references(() => rfqs.id, { onDelete: "cascade" }),
+
+ // 어떤 벤더의 상업평가(CBE)인지
+ vendorId: integer("vendor_id")
+ .notNull()
+ .references(() => vendors.id, { onDelete: "cascade" }),
+
+ // 평가자(작성자) 정보가 필요하다면 (예: 상업팀 담당자)
+ evaluatedBy: integer("evaluated_by")
+ .references(() => users.id, { onDelete: "set null" }),
+
+ // 평가 일시
+ evaluatedAt: timestamp("evaluated_at").defaultNow().notNull(),
+
+ // 상업평가 결과(예: ACCEPT, REJECT, PENDING 등)
+ result: varchar("result", { length: 50 }),
+
+ // 총 금액(견적 기준일 수도 있고, 평가 과정에서 조정이 있을 수도 있음)
+ totalCost: numeric("total_cost", { precision: 18, scale: 2 }).$type<number>(),
+
+ // 통화
+ currency: varchar("currency", { length: 10 }).default("USD"),
+
+ // 결제 조건, 인도 조건, 기타 상업 조건
+ paymentTerms: varchar("payment_terms", { length: 255 }),
+ incoterms: varchar("incoterms", { length: 50 }),
+ deliverySchedule: text("delivery_schedule"), // 정해진 형식이 있다면 varchar, 아니면 text
+
+ // 기타 비고
+ notes: text("notes"),
+
+ // 기본 관리 필드
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ updatedAt: timestamp("updated_at").defaultNow().notNull(),
+});
+
+
+
+export const cbeView = pgView("cbe_view").as((qb) => {
+ return qb
+ .select({
+ cbeId: sql<number>`${cbeEvaluations.id}`.as("cbe_id"),
+ rfqId: sql<number>`${cbeEvaluations.rfqId}`.as("rfq_id"),
+ vendorId: sql<number>`${cbeEvaluations.vendorId}`.as("vendor_id"),
+ totalCost: sql<number | null>`${cbeEvaluations.totalCost}`.as("total_cost"),
+ currency: sql<string | null>`${cbeEvaluations.currency}`.as("currency"),
+ paymentTerms: sql<string | null>`${cbeEvaluations.paymentTerms}`.as("payment_terms"),
+ incoterms: sql<string | null>`${cbeEvaluations.incoterms}`.as("incoterms"),
+ result: sql<string | null>`${cbeEvaluations.result}`.as("result"),
+ notes: sql<string | null>`${cbeEvaluations.notes}`.as("notes"),
+ evaluatedBy: sql<number | null>`${cbeEvaluations.evaluatedBy}`.as("evaluated_by"),
+ evaluatedAt: sql<Date>`${cbeEvaluations.evaluatedAt}`.as("evaluated_at"),
+
+ rfqCode: sql<string>`${rfqs.rfqCode}`.as("rfq_code"),
+ rfqDescription: sql<string | null>`${rfqs.description}`.as("rfq_description"),
+ vendorName: sql<string>`${vendors.vendorName}`.as("vendor_name"),
+ vendorCode: sql<string | null>`${vendors.vendorCode}`.as("vendor_code"),
+
+ projectId: sql<number | null>`${projects.id}`.as("project_id"),
+ projectCode: sql<string | null>`${projects.code}`.as("project_code"),
+ projectName: sql<string | null>`${projects.name}`.as("project_name"),
+
+ evaluatorName: sql<string | null>`${users.name}`.as("evaluator_name"),
+ evaluatorEmail: sql<string | null>`${users.email}`.as("evaluator_email"),
+
+ })
+ .from(cbeEvaluations)
+ .innerJoin(rfqs, eq(cbeEvaluations.rfqId, rfqs.id))
+ .innerJoin(vendors, eq(cbeEvaluations.vendorId, vendors.id))
+ .leftJoin(projects, eq(rfqs.projectId, projects.id))
+ .leftJoin(users, eq(cbeEvaluations.evaluatedBy, users.id));
+});
+
+
+//view
+
+export const rfqsView = pgView("rfqs_view").as((qb) => {
+ return qb
+ .select({
+ // rfqs 주요 필드
+ id: sql<number>`${rfqs.id}`.as("rfq_id"),
+ status: sql<string>`${rfqs.status}`.as("status"),
+ createdAt: sql<Date>`${rfqs.createdAt}`.as("created_at"),
+ updatedAt: sql<Date>`${rfqs.updatedAt}`.as("updated_at"),
+ createdBy: sql<number | null>`${rfqs.createdBy}`.as("created_by"),
+ rfqType: sql<string | null>`${rfqs.rfqType}`.as("rfq_type"),
+
+ // rfqs 추가 필드
+ rfqCode: sql<string>`${rfqs.rfqCode}`.as("rfq_code"),
+ description: sql<string | null>`${rfqs.description}`.as("description"),
+ dueDate: sql<Date | null>`${rfqs.dueDate}`.as("due_date"),
+ parentRfqId: sql<number | null>`${rfqs.parentRfqId}`.as("parent_rfq_id"),
+
+ // 프로젝트 테이블 필드
+ projectId: sql<number | null>`${projects.id}`.as("project_id"),
+ projectCode: sql<string | null>`${projects.code}`.as("project_code"),
+ projectName: sql<string | null>`${projects.name}`.as("project_name"),
+
+ // users 테이블 관련 필드
+ userEmail: sql<string | null>`${users.email}`.as("user_email"),
+ userName: sql<string | null>`${users.name}`.as("user_name"),
+
+ // rfq_items 및 rfq_attachments 카운트
+ itemCount: sql<number>`(
+ SELECT COUNT(*)
+ FROM "rfq_items"
+ WHERE "rfq_items"."rfq_id" = ${rfqs.id}
+ )`.as("item_count"),
+
+ attachmentCount: sql<number>`(
+ SELECT COUNT(*)
+ FROM "rfq_attachments"
+ WHERE "rfq_attachments"."rfq_id" = ${rfqs.id}
+ )`.as("attachment_count"),
+
+
+ })
+ .from(rfqs)
+ .leftJoin(projects, eq(rfqs.projectId, projects.id))
+ .leftJoin(users, eq(rfqs.createdBy, users.id))
+})
+
+// vendorRfqView 업데이트 벤더 기준
+export const vendorRfqView = pgView("vendor_rfq_view").as((qb) => {
+ return qb
+ .select({
+ // vendors 테이블
+ vendorId: sql<number>`${vendors.id}`.as("vendor_id"),
+ vendorName: sql<string>`${vendors.vendorName}`.as("vendor_name"),
+ vendorCode: sql<string>`${vendors.vendorCode}`.as("vendor_code"),
+ address: sql<string | null>`${vendors.address}`.as("address"),
+ country: sql<string | null>`${vendors.country}`.as("country"),
+ email: sql<string | null>`${vendors.email}`.as("email"),
+ website: sql<string | null>`${vendors.website}`.as("website"),
+ vendorStatus: sql<string>`${vendors.status}`.as("vendor_status"),
+
+ // rfqVendors 테이블
+ rfqId: sql<number | null>`${vendorResponses.rfqId}`.as("rfq_id"),
+ rfqVendorStatus: sql<string | null>`${vendorResponses.responseStatus}`.as("rfq_vendor_status"),
+ rfqVendorUpdated: sql<Date | null>`${vendorResponses.updatedAt}`.as("rfq_vendor_updated"),
+
+ // rfqs 테이블
+ rfqCode: sql<string | null>`${rfqs.rfqCode}`.as("rfq_code"),
+ description: sql<string | null>`${rfqs.description}`.as("description"),
+ dueDate: sql<Date | null>`${rfqs.dueDate}`.as("due_date"),
+
+ // 프로젝트 테이블 (업데이트됨)
+ projectId: sql<number | null>`${projects.id}`.as("project_id"),
+ projectCode: sql<string | null>`${projects.code}`.as("project_code"),
+ projectName: sql<string | null>`${projects.name}`.as("project_name"),
+ })
+ .from(vendors)
+ .leftJoin(vendorResponses, eq(vendorResponses.vendorId, vendors.id))
+ .leftJoin(rfqs, eq(vendorResponses.rfqId, rfqs.id))
+ .leftJoin(projects, eq(rfqs.projectId, projects.id)) // 프로젝트 테이블 조인 추가
+})
+
+
+
+// vendorTbeView 업데이트
+export const vendorTbeView = pgView("vendor_tbe_view").as((qb) => {
+ return qb
+ .select({
+ // vendors 기본정보
+ vendorId: sql<number>`${vendors.id}`.as("vendor_id"),
+ vendorName: sql<string>`${vendors.vendorName}`.as("vendor_name"),
+ vendorCode: sql<string | null>`${vendors.vendorCode}`.as("vendor_code"),
+ address: sql<string | null>`${vendors.address}`.as("address"),
+ country: sql<string | null>`${vendors.country}`.as("country"),
+ email: sql<string | null>`${vendors.email}`.as("email"),
+ website: sql<string | null>`${vendors.website}`.as("website"),
+ vendorStatus: sql<string>`${vendors.status}`.as("vendor_status"),
+
+ // rfq_vendors
+ vendorResponseId: sql<number | null>`${vendorResponses.id}`.as("vendor_response_id"),
+ rfqId: sql<number | null>`${vendorResponses.rfqId}`.as("rfq_id"),
+ rfqVendorStatus: sql<string | null>`${vendorResponses.responseStatus}`.as("rfq_vendor_status"),
+ rfqVendorUpdated: sql<Date | null>`${vendorResponses.updatedAt}`.as("rfq_vendor_updated"),
+
+ // rfqs
+ rfqCode: sql<string | null>`${rfqs.rfqCode}`.as("rfq_code"),
+ rfqType: sql<string | null>`${rfqs.rfqType}`.as("rfq_type"),
+ description: sql<string | null>`${rfqs.description}`.as("description"),
+ dueDate: sql<Date | null>`${rfqs.dueDate}`.as("due_date"),
+
+ // 프로젝트 테이블 필드 (업데이트됨)
+ projectId: sql<number | null>`${projects.id}`.as("project_id"),
+ projectCode: sql<string | null>`${projects.code}`.as("project_code"),
+ projectName: sql<string | null>`${projects.name}`.as("project_name"),
+
+ // rfq_evaluations (TBE 전용)
+ tbeId: sql<number | null>`${rfqEvaluations.id}`.as("tbe_id"),
+ tbeResult: sql<string | null>`${rfqEvaluations.result}`.as("tbe_result"),
+ tbeNote: sql<string | null>`${rfqEvaluations.notes}`.as("tbe_note"),
+ tbeUpdated: sql<Date | null>`${rfqEvaluations.updatedAt}`.as("tbe_updated"),
+ })
+ .from(vendors)
+ .leftJoin(
+ vendorResponses,
+ eq(vendorResponses.vendorId, vendors.id)
+ )
+ // 1) rfqVendors ↔ rfqs
+ .leftJoin(
+ rfqs,
+ eq(vendorResponses.rfqId, rfqs.id)
+ )
+ // 2) 프로젝트 테이블 조인 추가
+ .leftJoin(
+ projects,
+ eq(rfqs.projectId, projects.id)
+ )
+ // 3) rfqEvaluations
+ .leftJoin(
+ rfqEvaluations,
+ and(
+ eq(rfqEvaluations.vendorId, vendors.id),
+ eq(rfqEvaluations.evalType, "TBE"),
+ eq(rfqEvaluations.rfqId, vendorResponses.rfqId)
+ )
+ )
+})
+
+export const vendorCbeView = pgView("vendor_cbe_view").as((qb) => {
+ return qb
+ .select({
+ // [1] Vendor 기본정보
+ vendorId: sql<number>`${vendors.id}`.as("vendor_id"),
+ vendorName: sql<string>`${vendors.vendorName}`.as("vendor_name"),
+ vendorCode: sql<string | null>`${vendors.vendorCode}`.as("vendor_code"),
+ address: sql<string | null>`${vendors.address}`.as("address"),
+ country: sql<string | null>`${vendors.country}`.as("country"),
+ email: sql<string | null>`${vendors.email}`.as("email"),
+ website: sql<string | null>`${vendors.website}`.as("website"),
+ vendorStatus: sql<string>`${vendors.status}`.as("vendor_status"),
+
+ // [2] rfq_vendors (vendorResponses)
+ vendorResponseId: sql<number | null>`${vendorResponses.id}`.as("vendor_response_id"),
+ rfqId: sql<number | null>`${vendorResponses.rfqId}`.as("rfq_id"),
+ rfqVendorStatus: sql<string | null>`${vendorResponses.responseStatus}`.as("rfq_vendor_status"),
+ rfqVendorUpdated: sql<Date | null>`${vendorResponses.updatedAt}`.as("rfq_vendor_updated"),
+
+ // [3] rfqs
+ rfqCode: sql<string | null>`${rfqs.rfqCode}`.as("rfq_code"),
+ rfqType: sql<string | null>`${rfqs.rfqType}`.as("rfq_type"),
+ description: sql<string | null>`${rfqs.description}`.as("description"),
+ dueDate: sql<Date | null>`${rfqs.dueDate}`.as("due_date"),
+
+ // [4] 프로젝트 정보
+ projectId: sql<number | null>`${projects.id}`.as("project_id"),
+ projectCode: sql<string | null>`${projects.code}`.as("project_code"),
+ projectName: sql<string | null>`${projects.name}`.as("project_name"),
+
+ // [5] CBE 평가(cbeEvaluations)
+ cbeId: sql<number | null>`${cbeEvaluations.id}`.as("cbe_id"),
+ cbeResult: sql<string | null>`${cbeEvaluations.result}`.as("cbe_result"),
+ cbeNote: sql<string | null>`${cbeEvaluations.notes}`.as("cbe_note"),
+ cbeUpdated: sql<Date | null>`${cbeEvaluations.updatedAt}`.as("cbe_updated"),
+
+ // 상업평가용 추가 필드들
+ totalCost: sql<number | null>`${cbeEvaluations.totalCost}`.as("total_cost"),
+ currency: sql<string | null>`${cbeEvaluations.currency}`.as("currency"),
+ paymentTerms: sql<string | null>`${cbeEvaluations.paymentTerms}`.as("payment_terms"),
+ incoterms: sql<string | null>`${cbeEvaluations.incoterms}`.as("incoterms"),
+ deliverySchedule: sql<string | null>`${cbeEvaluations.deliverySchedule}`.as("delivery_schedule"),
+ })
+ .from(vendors)
+ .leftJoin(
+ vendorResponses,
+ eq(vendorResponses.vendorId, vendors.id)
+ )
+ .leftJoin(
+ rfqs,
+ eq(vendorResponses.rfqId, rfqs.id)
+ )
+ .leftJoin(
+ projects,
+ eq(rfqs.projectId, projects.id)
+ )
+ .leftJoin(
+ cbeEvaluations,
+ and(
+ eq(cbeEvaluations.vendorId, vendors.id),
+ eq(cbeEvaluations.rfqId, vendorResponses.rfqId)
+ )
+ );
+ });
+
+// Types for TypeScript
+export type VendorResponse = typeof vendorResponses.$inferSelect;
+export type VendorTechnicalResponse = typeof vendorTechnicalResponses.$inferSelect;
+export type VendorCommercialResponse = typeof vendorCommercialResponses.$inferSelect;
+export type VendorResponseAttachment = typeof vendorResponseAttachments.$inferSelect;
+
+
+// View to combine vendor response data with projects
+export const vendorResponsesView = pgView("vendor_responses_view").as((qb) => {
+ return qb
+ .select({
+ // Response identification
+ responseId: sql<number>`${vendorResponses.id}`.as("response_id"),
+ rfqId: sql<number>`${vendorResponses.rfqId}`.as("rfq_id"),
+ vendorId: sql<number>`${vendorResponses.vendorId}`.as("vendor_id"),
+
+ // RFQ information
+ rfqCode: sql<string>`${rfqs.rfqCode}`.as("rfq_code"),
+ rfqDescription: sql<string | null>`${rfqs.description}`.as("rfq_description"),
+ rfqDueDate: sql<Date | null>`${rfqs.dueDate}`.as("rfq_due_date"),
+ rfqStatus: sql<string>`${rfqs.status}`.as("rfq_status"),
+ rfqType: sql<string | null>`${rfqs.rfqType}`.as("rfq_type"),
+ rfqCreatedAt: sql<Date>`${rfqs.createdAt}`.as("rfq_created_at"),
+ rfqUpdatedAt: sql<Date>`${rfqs.updatedAt}`.as("rfq_updated_at"),
+ rfqCreatedBy: sql<number | null>`${rfqs.createdBy}`.as("rfq_created_by"),
+
+ // Project information
+ projectId: sql<number | null>`${projects.id}`.as("project_id"),
+ projectCode: sql<string | null>`${projects.code}`.as("project_code"),
+ projectName: sql<string | null>`${projects.name}`.as("project_name"),
+
+ // Vendor information
+ vendorName: sql<string>`${vendors.vendorName}`.as("vendor_name"),
+ vendorCode: sql<string | null>`${vendors.vendorCode}`.as("vendor_code"),
+
+ // Response status
+ responseStatus: sql<"INVITED" | "ACCEPTED" | "DECLINED" | "REVIEWING" | "RESPONDED">`${vendorResponses.responseStatus}`
+ .as("response_status"),
+ respondedAt: sql<Date>`${vendorResponses.respondedAt}`.as("responded_at"),
+
+ // Technical response indicators
+ hasTechnicalResponse: sql<boolean>`CASE WHEN ${vendorTechnicalResponses.id} IS NOT NULL THEN TRUE ELSE FALSE END`.as("has_technical_response"),
+ technicalResponseId: sql<number | null>`${vendorTechnicalResponses.id}`.as("technical_response_id"),
+
+ // Commercial response indicators
+ hasCommercialResponse: sql<boolean>`CASE WHEN ${vendorCommercialResponses.id} IS NOT NULL THEN TRUE ELSE FALSE END`.as("has_commercial_response"),
+ commercialResponseId: sql<number | null>`${vendorCommercialResponses.id}`.as("commercial_response_id"),
+ totalPrice: sql<number | null>`${vendorCommercialResponses.totalPrice}`.as("total_price"),
+ currency: sql<string | null>`${vendorCommercialResponses.currency}`.as("currency"),
+
+ // Evaluation links
+ tbeId: sql<number | null>`${rfqEvaluations.id}`.as("tbe_id"),
+ tbeResult: sql<string | null>`${rfqEvaluations.result}`.as("tbe_result"),
+ cbeId: sql<number | null>`${cbeEvaluations.id}`.as("cbe_id"),
+ cbeResult: sql<string | null>`${cbeEvaluations.result}`.as("cbe_result"),
+
+ // Attachments count
+ attachmentCount: sql<number>`(
+ SELECT COUNT(*)
+ FROM "vendor_response_attachments"
+ WHERE "vendor_response_attachments"."response_id" = ${vendorResponses.id}
+ )`.as("attachment_count"),
+ })
+ .from(vendorResponses)
+ .innerJoin(rfqs, eq(vendorResponses.rfqId, rfqs.id))
+ .innerJoin(vendors, eq(vendorResponses.vendorId, vendors.id))
+ .leftJoin(projects, eq(rfqs.projectId, projects.id)) // Added project join
+ .leftJoin(
+ vendorTechnicalResponses,
+ eq(vendorTechnicalResponses.responseId, vendorResponses.id)
+ )
+ .leftJoin(
+ vendorCommercialResponses,
+ eq(vendorCommercialResponses.responseId, vendorResponses.id)
+ )
+ .leftJoin(
+ rfqEvaluations,
+ and(
+ eq(rfqEvaluations.rfqId, vendorResponses.rfqId),
+ eq(rfqEvaluations.vendorId, vendorResponses.vendorId),
+ eq(rfqEvaluations.evalType, "TBE")
+ )
+ )
+ .leftJoin(
+ cbeEvaluations,
+ and(
+ eq(cbeEvaluations.rfqId, vendorResponses.rfqId),
+ eq(cbeEvaluations.vendorId, vendorResponses.vendorId)
+ )
+ );
+});
+
+
+export type VendorResponsesView = typeof vendorResponsesView.$inferSelect
+
+
+export type Rfq = typeof rfqs.$inferSelect
+export type RfqItem = typeof rfqItems.$inferSelect
+export type RfqAttach = typeof rfqAttachments.$inferSelect
+export type RfqComment = typeof rfqComments.$inferSelect
+export type RfqEvaluation = typeof rfqEvaluations.$inferSelect
+
+export type VendorRfqViewBase = typeof vendorRfqView.$inferSelect
+
+// 2) 우리가 코드에서 필요한 속성까지 합친 확장 타입
+export interface VendorRfqViewWithComments extends VendorRfqViewBase {
+ comments?: {
+ id: number
+ commentText: string
+ vendorId?: number
+ evaluationId?: number
+ createdAt: Date
+ commentedBy?: number
+ }[]
+ // 필요하다면 quote, attachments 등 추가 필드도...
+}
+
+export type VendorTbeView = typeof vendorTbeView.$inferSelect
+export type VendorCbeView = typeof vendorCbeView.$inferSelect
+
+export interface RfqWithItemCount {
+ // 기본 RFQ 식별자
+ rfqId: number;
+ // id: number; // rfqId와 동일 (편의상 중복)
+
+ // RFQ 기본 정보
+ rfqCode: string;
+ description: string | null;
+ status: string; // 필요시 "DRAFT" | "PUBLISHED" | "EVALUATION" | "AWARDED" 로 제한 가능
+
+ // 날짜 정보
+ dueDate: Date | null;
+ createdAt: Date;
+ updatedAt: Date;
+
+ // 프로젝트 관련 정보
+ projectCode: string | null;
+ projectName: string | null;
+
+ // 생성자 정보
+ createdBy: number | null;
+ createdByEmail: string | null;
+
+ // 사용자 정보
+ userId?: number | null;
+ userEmail?: string | null;
+ userName?: string | null;
+
+ // 집계 정보 (서브쿼리로 계산됨)
+ itemCount: number;
+ attachCount: number;
+
+ // 추가 필드 (필요시 데이터 변환 함수에서 채울 수 있음)
+ rfqType?: string | null;
+ projectId?: number | null;
+ parentRfqId?: number | null;
+
+ // 다른 필요한 필드들
+ vendorId?: number | null;
+ rfqVendorId?: number | null;
+ rfqVendorStatus?: string | null;
+ rfqVendorUpdated?: Date | null;
+}
+
+export interface RfqWithItems extends Rfq {
+ lines: RfqItem[];
+}
+
+export interface RfqWithAll extends VendorResponsesView {
+
+ // 아래처럼 배열/객체로
+ items: Array<{
+ id: number
+ itemCode: string
+ itemName: string
+ quantity?: number
+ uom?: string
+ description?: string | null
+ }>
+ attachments: Array<{
+ id: number
+ fileName: string
+ filePath: string
+ vendorId?: number | null
+ evaluationId?: number | null
+ }>
+ comments: Array<{
+ id: number
+ commentText: string
+ vendorId?: number | null
+ evaluationId?: number | null
+ createdAt: Date
+ }>
+
+}
+export type RfqsView = typeof rfqsView.$inferSelect
diff --git a/db/schema/tasks.ts b/db/schema/tasks.ts
new file mode 100644
index 00000000..fc4000bb
--- /dev/null
+++ b/db/schema/tasks.ts
@@ -0,0 +1,46 @@
+import { integer, pgTable, boolean, timestamp, varchar, primaryKey } from "drizzle-orm/pg-core";
+import { sql } from "drizzle-orm"
+
+import { generateId } from "@/lib/id"
+
+export const tasks = pgTable("tasks", {
+ id: varchar("id", { length: 30 })
+ .$defaultFn(() => generateId())
+ .primaryKey(),
+ code: varchar("code", { length: 128 })
+ .notNull()
+ .unique()
+ .default(
+ sql`concat('TASK-', to_char(nextval('tasks_code_seq'), 'FM0000'))`
+ )
+ ,
+ title: varchar("title", { length: 128 }),
+ status: varchar("status", {
+ length: 30,
+ enum: ["todo", "in-progress", "done", "canceled"],
+ })
+ .notNull()
+ .default("todo"),
+ label: varchar("label", {
+ length: 30,
+ enum: ["bug", "feature", "enhancement", "documentation"],
+ })
+ .notNull()
+ .default("bug"),
+ priority: varchar("priority", {
+ length: 30,
+ enum: ["low", "medium", "high"],
+ })
+ .notNull()
+ .default("low"),
+ archived: boolean("archived").notNull().default(false),
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ updatedAt: timestamp("updated_at")
+ .default(sql`current_timestamp`)
+ .$onUpdate(() => new Date()),
+})
+
+export type Task = typeof tasks.$inferSelect
+export type NewTask = Omit<Task, "code"> & {
+ code?: string
+} \ No newline at end of file
diff --git a/db/schema/users.ts b/db/schema/users.ts
new file mode 100644
index 00000000..843ee2f3
--- /dev/null
+++ b/db/schema/users.ts
@@ -0,0 +1,147 @@
+import { integer, pgTable, varchar,timestamp,pgEnum ,pgView, text, primaryKey} from "drizzle-orm/pg-core";
+import { eq , sql} from "drizzle-orm";
+import { vendors } from "./vendors";
+
+export const userDomainEnum = pgEnum("user_domain", ["evcp", "partners"]);
+
+
+export const users = pgTable("users", {
+ id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
+ name: varchar("name", { length: 255 }).notNull(),
+ email: varchar("email", { length: 255 }).notNull().unique(),
+ companyId: integer("company_id")
+ .references(() => vendors.id, { onDelete: "set null" }),
+ domain: userDomainEnum("domain").notNull().default("partners"),
+ createdAt: timestamp("created_at", { withTimezone: true })
+ .defaultNow()
+ .notNull(),
+ imageUrl: varchar("image_url", { length: 1024 }),
+
+});
+
+export const otps = pgTable('otps', {
+ email: varchar('email', { length: 256 }).notNull().primaryKey(),
+ code: varchar('code', { length: 6 }).notNull(),
+ createdAt: timestamp('created_at').defaultNow().notNull(),
+ otpToken: varchar({ length: 512 }).notNull(), // 이제 null 불가능
+ otpExpires: timestamp('otp_expires').notNull(), // null 불가능
+});
+
+export const permissions = pgTable("permissions", {
+ id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
+ permissionKey: text("permission_key").notNull(),
+ description: text("description"),
+ createdAt: timestamp("created_at").default(sql`now()`),
+});
+
+export const roles = pgTable("roles", {
+ id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
+ name: text("name").notNull(), // admin, manager
+ domain: userDomainEnum("domain").notNull(),
+ companyId: integer("company_id")
+ .references(() => vendors.id, { onDelete: "cascade" }),
+ description: text("description").default("").notNull(),
+ createdAt: timestamp("created_at").default(sql`now()`),
+});
+
+export const rolePermissions = pgTable("role_permissions", {
+ roleId: integer("role_id")
+ .references(() => roles.id, { onDelete: "cascade" })
+ .notNull(),
+ permissionId: integer("permission_id")
+ .references(() => permissions.id, { onDelete: "cascade" })
+ .notNull(),
+}, (table) => {
+ return [{
+ pk: primaryKey({ columns: [table.roleId, table.permissionId] }),
+ pkWithCustomName: primaryKey({ name: 'rolePermissions_pk', columns: [table.roleId, table.permissionId] }),
+ }];
+});
+
+export const userRoles = pgTable("user_roles", {
+ userId: integer("user_id")
+ .references(() => users.id, { onDelete: "cascade" })
+ .notNull(),
+ roleId: integer("role_id")
+ .references(() => roles.id, { onDelete: "cascade" })
+ .notNull(),
+}, (table) => {
+ return [{
+ pk: primaryKey({ columns: [table.userId, table.roleId] }),
+ pkWithCustomName: primaryKey({ name: 'userRoles_pk', columns: [table.userId, table.roleId] }),
+ }];
+});
+
+export type User = typeof users.$inferSelect
+export type UserRole = typeof userRoles.$inferSelect
+export type Role = typeof roles.$inferSelect
+export type UserDomainType = (typeof userDomainEnum.enumValues)[number];
+
+
+export const userView = pgView("user_view").as((qb) => {
+ return qb
+ .select({
+ // 1) userId: number
+ user_id: sql<number>`${users.id}`.as("user_id"),
+
+ // 2) userName: string
+ user_name: sql<string>`${users.name}`.as("user_name"),
+
+ // 3) userEmail: string
+ user_email: sql<string>`${users.email}`.as("user_email"),
+
+ user_domain: sql<string>`${users.domain}`.as("user_domain"),
+
+ user_image: sql<string>`${users.imageUrl}`.as("user_image"),
+
+ // 4) companyId: number | null
+ company_id: sql<number | null>`${vendors.id}`.as("company_id"),
+
+ // 5) companyName: string | null
+ company_name: sql<string | null>`${vendors.vendorName}`.as("company_name"),
+
+ // 6) roles: string[]
+ // Drizzle가 이 배열을 제대로 추론하려면 sql<string[]> 붙여야 함
+ roles: sql<string[]>`
+ array_agg(${roles.name})
+ `.as("roles"),
+ // 7) createdAt: Date
+ created_at: sql<Date>`${users.createdAt}`.as("created_at"),
+ })
+ .from(users)
+ .leftJoin(vendors, eq(users.companyId, vendors.id))
+ .leftJoin(userRoles, eq(users.id, userRoles.userId))
+ .leftJoin(roles, eq(userRoles.roleId, roles.id))
+ // array_agg를 쓰려면 GROUP BY 필요
+ .groupBy(
+ users.id,
+ vendors.id
+ // user_roles 테이블은 array_agg로 묶이므로 groupBy 필요 X
+ );
+});
+
+export const roleView = pgView("role_view").as((qb) => {
+ return qb
+ .select({
+ id: sql<number >`${roles.id}`.as("id"),
+ name: sql<string>`${roles.name}`.as("name"),
+ description: sql<string>`${roles.description}`.as("description"),
+ domain: sql<string>`${roles.domain}`.as("domain"),
+ created_at: sql<Date>`${roles.createdAt}`.as("created_at"),
+ // company
+ company_id: sql<number | null>`${vendors.id}`.as("company_id"),
+ company_name: sql<string | null>`${vendors.vendorName}`.as("company_name"),
+
+ // userCount
+ user_count: sql<number>`COUNT(${users.id})`.as("user_count"),
+ })
+ .from(roles)
+ .leftJoin(userRoles, eq(userRoles.roleId, roles.id))
+ .leftJoin(users, eq(users.id, userRoles.userId))
+ .leftJoin(vendors, eq(roles.companyId, vendors.id))
+ .groupBy(roles.id, vendors.id) // user_count(집계) 위해 groupBy
+})
+
+export type UserView = typeof userView.$inferSelect;
+export type RoleView = typeof roleView.$inferSelect;
+export type RolePermission = typeof rolePermissions.$inferSelect; \ No newline at end of file
diff --git a/db/schema/vendorData.ts b/db/schema/vendorData.ts
new file mode 100644
index 00000000..f7baa883
--- /dev/null
+++ b/db/schema/vendorData.ts
@@ -0,0 +1,205 @@
+import {
+ pgTable,
+ text,
+ varchar,
+ timestamp,
+ integer, numeric, date, unique, serial, jsonb, uniqueIndex
+} from "drizzle-orm/pg-core"
+import { contractItems } from "./contract"
+
+export const forms = pgTable("forms", {
+ id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
+ contractItemId: integer("contract_item_id")
+ .notNull()
+ .references(() => contractItems.id, { onDelete: "cascade" }),
+ formCode: varchar("form_code", { length: 100 }).notNull(),
+ formName: varchar("form_name", { length: 255 }).notNull(),
+ // tagType: varchar("tag_type", { length: 50 }).notNull(),
+ // class: varchar("class", { length: 100 }).notNull(),
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ updatedAt: timestamp("updated_at").defaultNow().notNull(),
+}, (table) => {
+ return {
+ // contractItemId와 formCode의 조합을 유니크하게 설정
+ contractItemFormCodeUnique: uniqueIndex("contract_item_form_code_unique").on(
+ table.contractItemId,
+ table.formCode
+ ),
+ }
+})
+
+export const rfqAttachments = pgTable("form_templates", {
+ id: serial("id").primaryKey(),
+ formId: integer("form_id").references(() => forms.id),
+ fileName: varchar("file_name", { length: 255 }).notNull(),
+ filePath: varchar("file_path", { length: 1024 }).notNull(),
+
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ udpatedAt: timestamp("updated_at").defaultNow().notNull(),
+
+});
+
+
+export const formMetas = pgTable("form_metas", {
+ id: serial("id").primaryKey(),
+ formCode: varchar("form_code", { length: 50 }).notNull(),
+ formName: varchar("form_name", { length: 255 }).notNull(),
+ columns: jsonb("columns").notNull(),
+ createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
+ updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
+})
+
+export const formEntries = pgTable("form_entries", {
+ id: serial("id").primaryKey(),
+ formCode: varchar("form_code", { length: 50 }).notNull(),
+ data: jsonb("data").notNull(),
+ contractItemId: integer("contract_item_id")
+ .notNull()
+ .references(() => contractItems.id, { onDelete: "cascade" }),
+ createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
+ updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
+})
+
+
+// ============ tags (각 계약 아이템에 대한 Tag) ============
+// "어느 계약의 어느 아이템에 대한 태그"임을 나타내려면 contract_items를 참조
+export const tags = pgTable("tags", {
+ id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
+
+ // 이 Tag가 속한 "계약 내 아이템" (즉 contract_items.id)
+ contractItemId: integer("contract_item_id")
+ .notNull()
+ .references(() => contractItems.id, { onDelete: "cascade" }),
+
+ formId: integer("form_id")
+ .references(() => forms.id, { onDelete: "set null" }),
+
+ tagNo: varchar("tag_no", { length: 100 }).notNull(),
+ tagType: varchar("tag_type", { length: 50 }).notNull(),
+ class: varchar("class", { length: 100 }).notNull(),
+ description: text("description"),
+
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ updatedAt: timestamp("updated_at").defaultNow().notNull(),
+})
+
+export type Tag = typeof tags.$inferSelect
+export type Form = typeof forms.$inferSelect
+export type NewTag = typeof tags.$inferInsert
+
+export const tagTypes = pgTable("tag_types", {
+ code: varchar("code", { length: 50 }).primaryKey(),
+ description: text("description").notNull(),
+
+ createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
+ updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
+})
+
+export const tagSubfields = pgTable("tag_subfields", {
+ id: serial("id").primaryKey(),
+
+ // 외래키: tagTypeCode -> tagTypes.code
+ tagTypeCode: varchar("tag_type_code", { length: 50 })
+ .notNull()
+ .references(() => tagTypes.code, { onDelete: "cascade" }),
+
+ /**
+ * 나머지 필드
+ */
+ // tagTypeDescription: -> 이제 불필요. tagTypes.description로 join
+ attributesId: varchar("attributes_id", { length: 50 }).notNull(),
+ attributesDescription: text("attributes_description").notNull(),
+
+ expression: text("expression"),
+ delimiter: varchar("delimiter", { length: 10 }),
+
+ sortOrder: integer("sort_order").default(0).notNull(),
+
+ createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
+ updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
+}, (table) => {
+ return {
+ uniqTagTypeAttribute: unique("uniq_tag_type_attribute").on(
+ table.tagTypeCode,
+ table.attributesId
+ ),
+ };
+});
+
+export const tagSubfieldOptions = pgTable("tag_subfield_options", {
+ id: serial("id").primaryKey(),
+
+ // 어떤 subfield에 속하는 옵션인지
+ attributesId: varchar("attributes_id", { length: 50 })
+ .notNull()
+ .references(() => tagSubfields.attributesId, { onDelete: "cascade" }),
+
+ /**
+ * 실제 코드 (예: "PM", "AA", "VB", "VAR", "01", "02" ...)
+ */
+ code: varchar("code", { length: 50 }).notNull(),
+
+ /**
+ * 사용자에게 보여줄 레이블 (예: "Pump", "Pneumatic Motor", "Ball Valve", ...)
+ */
+ label: text("label").notNull(),
+
+ /**
+ * 생성/수정 시각 (선택)
+ */
+ createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
+ updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
+})
+
+export const tagClasses = pgTable("tag_classes", {
+ id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
+
+ // 기존 code/label
+ code: varchar("code", { length: 100 }).notNull(),
+ label: text("label").notNull(),
+
+ // 새 필드: tagTypeCode -> references tagTypes.code
+ tagTypeCode: varchar("tag_type_code", { length: 50 })
+ .notNull()
+ .references(() => tagTypes.code, { onDelete: "cascade" }),
+
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ updatedAt: timestamp("updated_at").defaultNow().notNull(),
+})
+
+export const tagTypeClassFormMappings = pgTable("tag_type_class_form_mappings", {
+ id: serial("id").primaryKey(),
+
+ tagTypeLabel: varchar("tag_type_label", { length: 255 }).notNull(),
+ classLabel: varchar("class_label", { length: 255 }).notNull(),
+
+ formCode: varchar("form_code", { length: 50 }).notNull(),
+ formName: varchar("form_name", { length: 255 }).notNull(),
+
+ createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
+ updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
+})
+
+export type TagTypeClassFormMappings = typeof tagTypeClassFormMappings.$inferSelect
+export type TagSubfields = typeof tagSubfields.$inferSelect
+export type TagSubfieldOption = typeof tagSubfieldOptions.$inferSelect
+export type TagClasses = typeof tagClasses.$inferSelect
+
+
+export const viewTagSubfields = pgTable("view_tag_subfields", {
+ id: integer("id").primaryKey(),
+
+ tagTypeCode: varchar("tag_type_code", { length: 50 }).notNull(),
+ tagTypeDescription: text("tag_type_description"),
+ attributesId: varchar("attributes_id", { length: 50 }).notNull(),
+ attributesDescription: text("attributes_description").notNull(),
+
+ expression: text("expression"),
+ delimiter: varchar("delimiter", { length: 10 }),
+ sortOrder: integer("sort_order").default(0).notNull(),
+
+ createdAt: timestamp("created_at", { withTimezone: true }),
+ updatedAt: timestamp("updated_at", { withTimezone: true }),
+})
+
+export type ViewTagSubfields = typeof viewTagSubfields.$inferSelect
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
diff --git a/db/schema/vendors.ts b/db/schema/vendors.ts
new file mode 100644
index 00000000..b2005537
--- /dev/null
+++ b/db/schema/vendors.ts
@@ -0,0 +1,118 @@
+// db/schema/vendors.ts
+import { pgTable, serial, varchar, text, timestamp, boolean, integer ,pgView} from "drizzle-orm/pg-core";
+import { items } from "./items";
+import { eq} from "drizzle-orm";
+
+export const vendors = pgTable("vendors", {
+ id: serial("id").primaryKey(),
+ vendorName: varchar("vendor_name", { length: 255 }).notNull(),
+ vendorCode: varchar("vendor_code", { length: 100 }),
+ taxId: varchar("tax_id", { length: 100 }).notNull(),
+ address: text("address"),
+ country: varchar("country", { length: 100 }),
+ phone: varchar("phone", { length: 50 }),
+ email: varchar("email", { length: 255 }),
+ website: varchar("website", { length: 255 }),
+ status: varchar("status", {
+ length: 30,
+ enum: [
+ "PENDING_REVIEW", // 가입 신청 중 (초기 신청)
+ "IN_REVIEW", // 심사 중
+ "REJECTED", // 심사 거부됨
+ "IN_PQ", // PQ 진행 중
+ "PQ_SUBMITTED", // PQ 제출
+ "PQ_FAILED", // PQ 실패
+ "APPROVED", // PQ 통과, 승인됨
+ "ACTIVE", // 활성 상태 (실제 거래 중)
+ "INACTIVE", // 비활성 상태 (일시적)
+ "BLACKLISTED", // 거래 금지 상태
+ ]
+ })
+ .notNull()
+ .default("PENDING_REVIEW"),
+
+ representativeName: varchar("representative_name", { length: 255 }),
+ representativeBirth: varchar("representative_birth", { length: 20 }),
+ representativeEmail: varchar("representative_email", { length: 255 }),
+ representativePhone: varchar("representative_phone", { length: 50 }),
+ corporateRegistrationNumber: varchar("corporate_registration_number", {
+ length: 100,
+ }),
+
+ creditAgency: varchar("credit_agency", { length: 50 }),
+ creditRating: varchar("credit_rating", { length: 50 }),
+ cashFlowRating: varchar("cash_flow_rating", { length: 50 }),
+
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ updatedAt: timestamp("updated_at").defaultNow().notNull(),
+});
+
+export const vendorContacts = pgTable("vendor_contacts", {
+ id: serial("id").primaryKey(),
+ vendorId: integer("vendor_id").notNull().references(() => vendors.id),
+ contactName: varchar("contact_name", { length: 255 }).notNull(),
+ contactPosition: varchar("contact_position", { length: 100 }),
+ contactEmail: varchar("contact_email", { length: 255 }).notNull(),
+ contactPhone: varchar("contact_phone", { length: 50 }),
+ isPrimary: boolean("is_primary").default(false).notNull(),
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ updatedAt: timestamp("updated_at").defaultNow().notNull(),
+});
+
+
+export const vendorPossibleItems = pgTable("vendor_possible_items", {
+ id: serial("id").primaryKey(),
+ vendorId: integer("vendor_id").notNull().references(() => vendors.id),
+ // itemId: integer("item_id"), // 별도 item 테이블 연동시
+ itemCode: varchar("item_code", { length: 100 })
+ .notNull()
+ .references(() => items.itemCode, { onDelete: "cascade" }),
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ updatedAt: timestamp("updated_at").defaultNow().notNull(),
+});
+
+export const vendorItemsView = pgView("vendor_items_view").as((qb) => {
+ return qb
+ .select({
+ // vendorPossibleItems의 "id" -> "vendorItemId"
+ vendorItemId: vendorPossibleItems.id,
+ vendorId: vendorPossibleItems.vendorId,
+
+ // items의 "id" -> "itemId"
+ // itemId: items.id,
+ itemName: items.itemName,
+ itemCode: items.itemCode,
+ description: items.description,
+
+ createdAt: vendorPossibleItems.createdAt,
+ updatedAt: vendorPossibleItems.updatedAt,
+ })
+ .from(vendorPossibleItems)
+ .leftJoin(items, eq(vendorPossibleItems.itemCode, items.itemCode))
+})
+
+export const vendorAttachments = pgTable("vendor_attachments", {
+ id: serial("id").primaryKey(),
+ vendorId: integer("vendor_id").references(() => vendors.id),
+ fileName: varchar("file_name", { length: 255 }).notNull(),
+ filePath: varchar("file_path", { length: 1024 }).notNull(),
+ attachmentType: varchar("attachment_type", {
+ length: 50,
+ }).default("GENERAL"),
+
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ updatedAt: timestamp("updated_at").defaultNow().notNull(),
+});
+
+
+export type Vendor = typeof vendors.$inferSelect
+export type VendorContact = typeof vendorContacts.$inferSelect
+export type VendorItem = typeof vendorPossibleItems.$inferSelect
+export type VendorAttach = typeof vendorAttachments.$inferSelect
+
+export type VendorWithAttachments = Vendor & {
+ hasAttachments?: boolean;
+ attachmentsList?: VendorAttach[];
+}
+
+export type VendorItemsView = typeof vendorItemsView.$inferSelect \ No newline at end of file
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-07 05:52:49 +0000