summaryrefslogtreecommitdiff
path: root/db/schema
diff options
context:
space:
mode:
Diffstat (limited to 'db/schema')
-rw-r--r--db/schema/pq.ts409
-rw-r--r--db/schema/techVendors.ts30
-rw-r--r--db/schema/vendorData.ts38
-rw-r--r--db/schema/vendorDocu.ts609
4 files changed, 885 insertions, 201 deletions
diff --git a/db/schema/pq.ts b/db/schema/pq.ts
index 9aac7b89..c5f8c732 100644
--- a/db/schema/pq.ts
+++ b/db/schema/pq.ts
@@ -1,91 +1,90 @@
import {
- pgTable, serial, integer, varchar, text,
- timestamp, uniqueIndex, pgView
+ pgTable, serial, integer, varchar, text,
+ timestamp, uniqueIndex, pgView
} from "drizzle-orm/pg-core";
import { vendorTypes, vendors } from "./vendors";
import { projects } from "./projects";
-import { sql } from "drizzle-orm";
+import { sql, eq } from "drizzle-orm";
+import { users } from "./users";
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"),
+ 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(),
+ // (선택) "GENERAL", "Quality Management System" 등 큰 분류
+ groupName: varchar("group_name", { length: 255 }),
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ updatedAt: timestamp("updated_at").defaultNow().notNull(),
});
export const pqCriteriasExtension = pgTable("pq_criterias_extension", {
- id: serial("id").primaryKey(),
-
- // pq_criterias와 연결 (1:1 or 1:N 중 필요에 맞춰 사용)
- pqCriteriaId: integer("pq_criteria_id")
- .notNull()
- .references(() => pqCriterias.id, {
- onDelete: "cascade",
- onUpdate: "cascade",
- }),
-
- // projects 테이블에 대한 FK
- projectId: integer("project_id")
- .notNull()
- .references(() => projects.id, {
- onDelete: "cascade",
- onUpdate: "cascade",
- }),
-
- // 프로젝트별 PQ 시 필요한 추가 정보
- contractInfo: text("contract_info"),
- additionalRequirement: text("additional_requirement"),
-
- createdAt: timestamp("created_at").defaultNow().notNull(),
- updatedAt: timestamp("updated_at").defaultNow().notNull(),
- });
+ id: serial("id").primaryKey(),
+
+ // pq_criterias와 연결 (1:1 or 1:N 중 필요에 맞춰 사용)
+ pqCriteriaId: integer("pq_criteria_id")
+ .notNull()
+ .references(() => pqCriterias.id, {
+ onDelete: "cascade",
+ onUpdate: "cascade",
+ }),
+ // projects 테이블에 대한 FK
+ projectId: integer("project_id")
+ .notNull()
+ .references(() => projects.id, {
+ onDelete: "cascade",
+ onUpdate: "cascade",
+ }),
+ // 프로젝트별 PQ 시 필요한 추가 정보
+ contractInfo: text("contract_info"),
+ additionalRequirement: text("additional_requirement"),
+
+ 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(),
+ 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"),
+ 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(),
+ 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(),
- })
+ 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
// 협력업체와 프로젝트 PQ 요청 연결 테이블
export const vendorProjectPQs = pgTable("vendor_project_pqs", {
id: serial("id").primaryKey(),
-
+
// vendors 테이블 FK
vendorId: integer("vendor_id")
.notNull()
@@ -93,7 +92,7 @@ export const vendorProjectPQs = pgTable("vendor_project_pqs", {
onDelete: "cascade",
onUpdate: "cascade",
}),
-
+
// projects 테이블 FK
projectId: integer("project_id")
.notNull()
@@ -101,47 +100,92 @@ export const vendorProjectPQs = pgTable("vendor_project_pqs", {
onDelete: "cascade",
onUpdate: "cascade",
}),
-
+
// 상태: REQUESTED(요청됨), IN_PROGRESS(진행중), SUBMITTED(제출됨), APPROVED(승인됨), REJECTED(거부됨)
status: varchar("status", { length: 20 }).notNull().default("REQUESTED"),
-
+
// 메타데이터
submittedAt: timestamp("submitted_at"),
approvedAt: timestamp("approved_at"),
rejectedAt: timestamp("rejected_at"),
rejectReason: text("reject_reason"),
-
+
createdAt: timestamp("created_at").defaultNow().notNull(),
updatedAt: timestamp("updated_at").defaultNow().notNull(),
});
+export const vendorPQSubmissions = pgTable("vendor_pq_submissions", {
+ id: serial("id").primaryKey(),
+
+ pqNumber: varchar("pq_number", { length: 50 }).notNull().unique(),
+
+ requesterId: integer("requester_id")
+ .references(() => users.id),
+
+ vendorId: integer("vendor_id")
+ .notNull()
+ .references(() => vendors.id, {
+ onDelete: "cascade",
+ onUpdate: "cascade",
+ }),
+
+ // null이면 일반 PQ, 값이 있으면 프로젝트 PQ
+ projectId: integer("project_id")
+ .references(() => projects.id, {
+ onDelete: "cascade",
+ onUpdate: "cascade",
+ }),
+
+ // PQ 유형 구분을 명시적으로
+ type: varchar("type", { length: 20 }).notNull(), // "GENERAL" or "PROJECT"
+
+ status: varchar("status", { length: 20 }).notNull().default("REQUESTED"),
+
+ submittedAt: timestamp("submitted_at"),
+ approvedAt: timestamp("approved_at"),
+ rejectedAt: timestamp("rejected_at"),
+ rejectReason: text("reject_reason"),
+
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ updatedAt: timestamp("updated_at").defaultNow().notNull(),
+}, (table) => {
+ return {
+ // 협력업체별로 일반 PQ는 하나만, 프로젝트 PQ는 프로젝트당 하나만
+ uniqueConstraint: uniqueIndex("unique_pq_submission").on(
+ table.vendorId,
+ table.projectId,
+ table.type
+ ),
+ };
+});
+
// 기존 vendorPqCriteriaAnswers 테이블에 projectId 필드 추가
export const vendorPqCriteriaAnswers = pgTable("vendor_pq_criteria_answers", {
id: serial("id").primaryKey(),
-
+
vendorId: integer("vendor_id")
.notNull()
.references(() => vendors.id, {
onDelete: "cascade",
onUpdate: "cascade",
}),
-
+
criteriaId: integer("criteria_id")
.notNull()
.references(() => pqCriterias.id, {
onDelete: "cascade",
onUpdate: "cascade",
}),
-
+
// 추가: 프로젝트 ID (null은 일반 PQ를 의미)
projectId: integer("project_id")
.references(() => projects.id, {
onDelete: "cascade",
onUpdate: "cascade",
}),
-
+
answer: text("answer"),
-
+
createdAt: timestamp("created_at").defaultNow().notNull(),
updatedAt: timestamp("updated_at").defaultNow().notNull(),
});
@@ -164,23 +208,232 @@ export const projectApprovedVendors = pgView("project_approved_vendors").as((qb)
project_name: projects.name,
project_type: projects.type,
// pq_status: vendorProjectPQs.status,
- submitted_at: vendorProjectPQs.submittedAt,
- approved_at: vendorProjectPQs.approvedAt
+ submitted_at: vendorPQSubmissions.submittedAt,
+ approved_at: vendorPQSubmissions.approvedAt
})
.from(vendors)
.innerJoin(
- vendorProjectPQs,
- sql`${vendorProjectPQs.vendorId} = ${vendors.id}`
+ vendorPQSubmissions,
+ sql`${vendorPQSubmissions.vendorId} = ${vendors.id}`
)
.innerJoin(
projects,
- sql`${vendorProjectPQs.projectId} = ${projects.id}`
+ sql`${vendorPQSubmissions.projectId} = ${projects.id}`
)
.leftJoin(
vendorTypes,
sql`${vendors.vendorTypeId} = ${vendorTypes.id}`
)
- .where(sql`${vendorProjectPQs.status} = 'APPROVED'`);
+ .where(sql`${vendorPQSubmissions.status} = 'APPROVED'`);
});
export type ProjectApprovedVendors = typeof projectApprovedVendors.$inferSelect
+
+
+
+
+export const vendorInvestigations = pgTable("vendor_investigations", {
+ id: serial("id").primaryKey(),
+
+ // 어떤 벤더에 대한 실사인지 참조
+ vendorId: integer("vendor_id").notNull().references(() => vendors.id),
+
+ // PQ 제출에 대한 참조
+ pqSubmissionId: integer("pq_submission_id")
+ .references(() => vendorPQSubmissions.id, {
+ onDelete: "set null",
+ onUpdate: "cascade",
+ }),
+
+ requesterId: integer("requester_id")
+ .references(() => users.id),
+
+ qmManagerId: integer("qm_manager_id")
+ .references(() => users.id),
+
+ // 실사 상태
+ investigationStatus: varchar("investigation_status", {
+ length: 50,
+ enum: [
+ "PLANNED", // 계획됨
+ "IN_PROGRESS", // 진행 중
+ "COMPLETED", // 완료됨
+ "CANCELED", // 취소됨
+ ],
+ })
+ .notNull()
+ .default("PLANNED"),
+
+ // 평가 유형
+ evaluationType: varchar("evaluation_type", {
+ length: 50,
+ enum: [
+ "SITE_AUDIT", // 실사의뢰평가
+ "QM_SELF_AUDIT", // QM자체평가
+ ],
+ }),
+
+ // 실사 주소
+ investigationAddress: text("investigation_address"),
+
+ // 실사 방법
+ investigationMethod: varchar("investigation_method", { length: 100 }),
+
+ // 실사 일정 시작일 / 종료일
+ scheduledStartAt: timestamp("scheduled_start_at"),
+ scheduledEndAt: timestamp("scheduled_end_at"),
+
+
+ // 실사 예정일
+ forecastedAt: timestamp("forecasted_at"),
+
+ // 실사 의뢰일
+ requestedAt: timestamp("requested_at"),
+
+ // 실사 확정일
+ confirmedAt: timestamp("confirmed_at"),
+
+ // 실제 실사 완료일
+ completedAt: timestamp("completed_at"),
+
+ // 실사 평가점수
+ evaluationScore: integer("evaluation_score"),
+
+ // 실사 평가 결과
+ evaluationResult: varchar("evaluation_result", {
+ length: 50,
+ enum: [
+ "APPROVED", // 승인
+ "SUPPLEMENT", // 보완
+ "REJECTED", // 불가
+ ],
+ }),
+
+ // 실사 내용이나 특이사항
+ investigationNotes: text("investigation_notes"),
+
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ updatedAt: timestamp("updated_at").defaultNow().notNull(),
+});
+
+// 타입 정의
+export type VendorInvestigation = typeof vendorInvestigations.$inferSelect;
+export const vendorInvestigationAttachments = pgTable(
+ "vendor_investigation_attachments",
+ {
+ id: serial("id").primaryKey(),
+
+ // 어떤 실사 (investigation)에 대한 첨부파일인지
+ investigationId: integer("investigation_id")
+ .notNull()
+ .references(() => vendorInvestigations.id, { onDelete: "cascade" }),
+
+ fileName: varchar("file_name", { length: 255 }).notNull(),
+ filePath: varchar("file_path", { length: 1024 }).notNull(),
+
+ // 권장: 사용자 경험과 기능성을 위해 추가
+ fileSize: integer("file_size"), // bytes (nullable로 기존 데이터 호환)
+ mimeType: varchar("mime_type", { length: 100 }), // nullable로 기존 데이터 호환
+
+ // 첨부파일 종류 (예: 보고서, 사진, 기타 등 구분)
+ attachmentType: varchar("attachment_type", { length: 50 }).default("REPORT"),
+
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ updatedAt: timestamp("updated_at").defaultNow().notNull(),
+ }
+);
+
+// 타입 정의
+export type VendorInvestigationAttachment = typeof vendorInvestigationAttachments.$inferSelect;
+export type NewVendorInvestigationAttachment = typeof vendorInvestigationAttachments.$inferInsert;
+
+// 파일 업로드 관련 유틸리티 타입
+export interface FileUploadResult {
+ id: number;
+ fileName: string;
+ originalFileName: string;
+ filePath: string;
+ fileSize: number;
+ mimeType: string;
+ fileType: string;
+ uploadedBy: number;
+ description?: string;
+}
+
+// 파일 다운로드용 타입
+export interface FileDownloadInfo {
+ fileName: string;
+ originalFileName: string;
+ filePath: string;
+ mimeType: string;
+ fileSize: number;
+}
+
+/**
+ * A view that joins vendor_investigations + vendors,
+ * and also embeds contacts & possibleItems as JSON arrays.
+ */
+
+export const vendorInvestigationsView = pgView(
+ "vendor_investigations_view"
+).as((qb) => {
+ return qb
+ .select({
+ // Investigation fields - use investigationId as the primary identifier
+ investigationId: vendorInvestigations.id,
+ vendorId: vendorInvestigations.vendorId,
+ pqSubmissionId: vendorInvestigations.pqSubmissionId,
+ requesterId: vendorInvestigations.requesterId,
+ qmManagerId: vendorInvestigations.qmManagerId,
+ investigationStatus: vendorInvestigations.investigationStatus,
+ evaluationType: vendorInvestigations.evaluationType,
+ investigationAddress: vendorInvestigations.investigationAddress,
+ investigationMethod: vendorInvestigations.investigationMethod,
+ scheduledStartAt: vendorInvestigations.scheduledStartAt,
+ scheduledEndAt: vendorInvestigations.scheduledEndAt,
+ forecastedAt: vendorInvestigations.forecastedAt,
+ requestedAt: vendorInvestigations.requestedAt,
+ confirmedAt: vendorInvestigations.confirmedAt,
+ completedAt: vendorInvestigations.completedAt,
+ evaluationScore: vendorInvestigations.evaluationScore,
+ evaluationResult: vendorInvestigations.evaluationResult,
+ investigationNotes: vendorInvestigations.investigationNotes,
+ createdAt: vendorInvestigations.createdAt,
+ updatedAt: vendorInvestigations.updatedAt,
+
+ // Essential vendor fields
+ vendorName: vendors.vendorName,
+ vendorCode: vendors.vendorCode,
+
+ // User names and emails instead of just IDs
+ requesterName: sql<string>`requester.name`.as("requesterName"),
+ requesterEmail: sql<string>`requester.email`.as("requesterEmail"),
+ qmManagerName: sql<string>`qm_manager.name`.as("qmManagerName"),
+ qmManagerEmail: sql<string>`qm_manager.email`.as("qmManagerEmail"),
+
+ // File attachment status
+ hasAttachments: sql<boolean>`(
+ CASE
+ WHEN EXISTS (
+ SELECT 1 FROM vendor_investigation_attachments via
+ WHERE via.investigation_id = ${vendorInvestigations.id}
+ )
+ THEN true
+ ELSE false
+ END
+ )`.as("hasAttachments"),
+ })
+ .from(vendorInvestigations)
+ .leftJoin(
+ vendors,
+ eq(vendorInvestigations.vendorId, vendors.id)
+ )
+ .leftJoin(
+ sql`users AS requester`,
+ eq(vendorInvestigations.requesterId, sql`requester.id`)
+ )
+ .leftJoin(
+ sql`users AS qm_manager`,
+ eq(vendorInvestigations.qmManagerId, sql`qm_manager.id`)
+ )
+}) \ No newline at end of file
diff --git a/db/schema/techVendors.ts b/db/schema/techVendors.ts
index 55060adf..dcf73611 100644
--- a/db/schema/techVendors.ts
+++ b/db/schema/techVendors.ts
@@ -11,29 +11,28 @@ export const techVendors = pgTable("tech_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"),
+ taxId: varchar("tax_id", { length: 100 }),
country: varchar("country", { length: 100 }),
+ countryEng: varchar("country_eng", { length: 100 }),
+ countryFab: varchar("country_fab", { length: 100 }),
+ agentName: varchar("agent_name", { length: 255 }),
+ agentPhone: varchar("agent_phone", { length: 50 }),
+ agentEmail: varchar("agent_email", { length: 255 }),
+ address: varchar("address", { length: 255 }),
phone: varchar("phone", { length: 50 }),
email: varchar("email", { length: 255 }),
website: varchar("website", { length: 255 }),
- // 벤더 타입 추가
- techVendorType: varchar("tech_vendor_type", {
- length: 20,
- enum: VENDOR_TYPES
- }).notNull(),
- // 상태 필드 추가
+ // 벤더 타입을 다중 선택 가능하도록 text 타입으로 변경 (JSON 배열 저장)
+ techVendorType: text("tech_vendor_type").notNull(),
+ // 상태 필드 - 더 많은 상태 추가
status: varchar("status", {
length: 30,
enum: [
- "PENDING_REVIEW",
- "IN_REVIEW",
- "REJECTED",
"ACTIVE",
"INACTIVE",
"BLACKLISTED"
]
- }).default("PENDING_REVIEW").notNull(),
+ }).default("ACTIVE").notNull(),
// 대표자 정보
representativeName: varchar("representative_name", { length: 255 }),
representativeEmail: varchar("representative_email", { length: 255 }),
@@ -108,6 +107,11 @@ export const techVendorDetailView = pgView("tech_vendor_detail_view").as((qb) =>
taxId: techVendors.taxId,
address: techVendors.address,
country: techVendors.country,
+ countryEng: techVendors.countryEng,
+ countryFab: techVendors.countryFab,
+ agentName: techVendors.agentName,
+ agentPhone: techVendors.agentPhone,
+ agentEmail: techVendors.agentEmail,
phone: techVendors.phone,
email: techVendors.email,
website: techVendors.website,
@@ -203,7 +207,7 @@ export const techVendorCandidates = pgTable("tech_vendor_candidates", {
contactEmail: varchar("contact_email", { length: 255 }),
contactPhone: varchar("contact_phone", { length: 50 }),
- taxId: varchar("tax_id", { length: 100 }).notNull(),
+ taxId: varchar("tax_id", { length: 100 }),
address: text("address"),
country: varchar("country", { length: 100 }),
diff --git a/db/schema/vendorData.ts b/db/schema/vendorData.ts
index 01608df4..20025dc0 100644
--- a/db/schema/vendorData.ts
+++ b/db/schema/vendorData.ts
@@ -292,4 +292,40 @@ export const vendorDataReportTemps = pgTable("vendor_data_report_temps", {
.notNull(),
});
-export type VendorDataReportTemps = typeof vendorDataReportTemps.$inferSelect; \ No newline at end of file
+export type VendorDataReportTemps = typeof vendorDataReportTemps.$inferSelect;
+
+
+export const formListsView = pgView("form_lists_view").as((qb) => {
+ return qb
+ .select({
+ // Primary identifiers
+ id: sql<number>`${tagTypeClassFormMappings.id}`.as("id"),
+ projectId: sql<number>`${tagTypeClassFormMappings.projectId}`.as("project_id"),
+
+ // Project information
+ projectCode: sql<string>`${projects.code}`.as("project_code"),
+ projectName: sql<string>`${projects.name}`.as("project_name"),
+
+ // Form information
+ tagTypeLabel: sql<string>`${tagTypeClassFormMappings.tagTypeLabel}`.as("tag_type_label"),
+ classLabel: sql<string>`${tagTypeClassFormMappings.classLabel}`.as("class_label"),
+ formCode: sql<string>`${tagTypeClassFormMappings.formCode}`.as("form_code"),
+ formName: sql<string>`${tagTypeClassFormMappings.formName}`.as("form_name"),
+
+ // Additional fields
+ ep: sql<string | null>`${tagTypeClassFormMappings.ep}`.as("ep"),
+ remark: sql<string | null>`${tagTypeClassFormMappings.remark}`.as("remark"),
+
+ // Timestamps
+ createdAt: sql<Date>`${tagTypeClassFormMappings.createdAt}`.as("created_at"),
+ updatedAt: sql<Date>`${tagTypeClassFormMappings.updatedAt}`.as("updated_at"),
+ })
+ .from(tagTypeClassFormMappings)
+ .innerJoin(
+ projects,
+ eq(tagTypeClassFormMappings.projectId, projects.id)
+ );
+ });
+
+
+ export type FormListsView = typeof formListsView.$inferSelect;
diff --git a/db/schema/vendorDocu.ts b/db/schema/vendorDocu.ts
index 8eb9cbc6..ad49f031 100644
--- a/db/schema/vendorDocu.ts
+++ b/db/schema/vendorDocu.ts
@@ -1,43 +1,31 @@
-import { pgTable, integer, varchar, timestamp, date, pgView, uniqueIndex, jsonb } from "drizzle-orm/pg-core"
+// enhanced-schema.ts
+import { pgTable, integer, varchar, timestamp, date, pgView, uniqueIndex, jsonb, boolean, serial,
+ index, text } from "drizzle-orm/pg-core"
import { eq, sql } from "drizzle-orm";
import { projects } from "./projects";
import { vendors } from "./vendors";
import { contracts } from "./contract";
-
+// 기존 documents 테이블 (변경 없음)
export const documents = pgTable(
"documents",
{
- // 주 키
id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
-
- // documentType: varchar("document_type", { length: 50 }).notNull(),
-
pic: varchar("pic", { length: 50 }),
-
- // 어느 계약(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,
@@ -47,60 +35,86 @@ export const documents = pgTable(
}
)
+// 확장된 issueStages 테이블
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"),
-
- // 생성/수정 시각
+
+ // 새로운 스케줄 관리 필드들
+ stageStatus: varchar("stage_status", { length: 50 })
+ .notNull()
+ .default("PLANNED"), // PLANNED, IN_PROGRESS, SUBMITTED, APPROVED, REJECTED, COMPLETED
+ stageOrder: integer("stage_order").default(0),
+ priority: varchar("priority", { length: 20 }).default("MEDIUM"), // HIGH, MEDIUM, LOW
+
+ // 담당자 정보
+ assigneeId: integer("assignee_id"),
+ assigneeName: varchar("assignee_name", { length: 100 }),
+
+ // 알림 및 추가 정보
+ reminderDays: integer("reminder_days").default(3),
+ description: varchar("description", { length: 500 }),
+ notes: varchar("notes", { length: 1000 }),
+
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
),
+ documentStageOrder: uniqueIndex("document_stage_order").on(
+ table.documentId,
+ table.stageOrder
+ ),
}
}
);
+// 확장된 revisions 테이블
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 }),
+
+ // 확장된 상태 관리
+ revisionStatus: varchar("revision_status", { length: 50 })
+ .notNull()
+ .default("SUBMITTED"), // SUBMITTED, UNDER_REVIEW, APPROVED, REJECTED, SUPERSEDED
+
+ // 상세 날짜 추적
+ submittedDate: date("submitted_date"),
+ reviewStartDate: date("review_start_date"),
approvedDate: date("approved_date"),
+ rejectedDate: date("rejected_date"),
+
+ // 검토자 정보
+ reviewerId: integer("reviewer_id"),
+ reviewerName: varchar("reviewer_name", { length: 100 }),
+ reviewComments: varchar("review_comments", { length: 1000 }),
+
+ comment: varchar("comment", { length: 500 }),
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
@@ -108,6 +122,8 @@ export const revisions = pgTable(
}
}
)
+
+// 기존 documentAttachments (변경 없음)
export const documentAttachments = pgTable(
"document_attachments",
{
@@ -124,29 +140,308 @@ export const documentAttachments = pgTable(
}
)
+
+export const enhancedDocumentsView = pgView("enhanced_documents_view", {
+ // 기본 문서 정보
+ documentId: integer("document_id").notNull(),
+ docNumber: varchar("doc_number", { length: 100 }).notNull(),
+ title: varchar("title", { length: 255 }).notNull(),
+ pic: varchar("pic", { length: 50 }),
+ status: varchar("status", { length: 50 }).notNull(),
+ issuedDate: date("issued_date"),
+ contractId: integer("contract_id").notNull(),
+
+ // 현재 스테이지 정보 (가장 우선순위가 높은 미완료 스테이지)
+ currentStageId: integer("current_stage_id"),
+ currentStageName: varchar("current_stage_name", { length: 100 }),
+ currentStageStatus: varchar("current_stage_status", { length: 50 }),
+ currentStageOrder: integer("current_stage_order"),
+ currentStagePlanDate: date("current_stage_plan_date"),
+ currentStageActualDate: date("current_stage_actual_date"),
+ currentStageAssigneeName: varchar("current_stage_assignee_name", { length: 100 }),
+ currentStagePriority: varchar("current_stage_priority", { length: 20 }),
+
+ // 계산 필드
+ daysUntilDue: integer("days_until_due"),
+ isOverdue: boolean("is_overdue"),
+ daysDifference: integer("days_difference"),
+
+ // 전체 진행률
+ totalStages: integer("total_stages"),
+ completedStages: integer("completed_stages"),
+ progressPercentage: integer("progress_percentage"),
+
+ // 최신 리비전 정보
+ latestRevisionId: integer("latest_revision_id"),
+ latestRevision: varchar("latest_revision", { length: 50 }),
+ latestRevisionStatus: varchar("latest_revision_status", { length: 50 }),
+ latestRevisionUploaderName: varchar("latest_revision_uploader_name", { length: 100 }),
+ latestSubmittedDate: date("latest_submitted_date"),
+
+ // 전체 스테이지 목록 (리비전 및 첨부파일 포함)
+ allStages: jsonb("all_stages").$type<Array<{
+ id: number;
+ stageName: string;
+ stageStatus: string;
+ stageOrder: number;
+ planDate: string | null;
+ actualDate: string | null;
+ assigneeName: string | null;
+ priority: string;
+ revisions: Array<{
+ id: number;
+ issueStageId: number;
+ revision: string;
+ uploaderType: string;
+ uploaderId: number | null;
+ uploaderName: string | null;
+ comment: string | null;
+ revisionStatus: string;
+ submittedDate: string | null;
+ approvedDate: string | null;
+ reviewStartDate: string | null;
+ rejectedDate: string | null;
+ reviewerId: number | null;
+ reviewerName: string | null;
+ reviewComments: string | null;
+ createdAt: Date;
+ updatedAt: Date;
+ attachments: Array<{
+ id: number;
+ revisionId: number;
+ fileName: string;
+ filePath: string;
+ fileSize: number | null;
+ fileType: string | null;
+ createdAt: Date;
+ updatedAt: Date;
+ }>;
+ }>;
+ }>>(),
+
+ // 메타 정보
+ attachmentCount: integer("attachment_count"),
+ createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
+ updatedAt: timestamp("updated_at", { withTimezone: true }).notNull(),
+}).as(sql`
+ WITH document_stats AS (
+ SELECT
+ d.id as document_id,
+ COUNT(ist.id) as total_stages,
+ COUNT(CASE WHEN ist.stage_status IN ('COMPLETED', 'APPROVED') THEN 1 END) as completed_stages,
+ CASE
+ WHEN COUNT(ist.id) > 0
+ THEN ROUND((COUNT(CASE WHEN ist.stage_status IN ('COMPLETED', 'APPROVED') THEN 1 END) * 100.0) / COUNT(ist.id))
+ ELSE 0
+ END as progress_percentage
+ FROM documents d
+ LEFT JOIN issue_stages ist ON d.id = ist.document_id
+ GROUP BY d.id
+ ),
+ current_stage_info AS (
+ SELECT DISTINCT ON (document_id)
+ document_id,
+ id as current_stage_id,
+ stage_name as current_stage_name,
+ stage_status as current_stage_status,
+ stage_order as current_stage_order,
+ plan_date as current_stage_plan_date,
+ actual_date as current_stage_actual_date,
+ assignee_name as current_stage_assignee_name,
+ priority as current_stage_priority,
+ CASE
+ WHEN actual_date IS NULL AND plan_date IS NOT NULL
+ THEN plan_date - CURRENT_DATE
+ ELSE NULL
+ END as days_until_due,
+ CASE
+ WHEN actual_date IS NULL AND plan_date < CURRENT_DATE
+ THEN true
+ WHEN actual_date IS NOT NULL AND actual_date > plan_date
+ THEN true
+ ELSE false
+ END as is_overdue,
+ CASE
+ WHEN actual_date IS NOT NULL AND plan_date IS NOT NULL
+ THEN actual_date - plan_date
+ ELSE NULL
+ END as days_difference
+ FROM issue_stages
+ WHERE stage_status NOT IN ('COMPLETED', 'APPROVED')
+ ORDER BY document_id, stage_order ASC, priority DESC
+ ),
+ latest_revision_info AS (
+ SELECT DISTINCT ON (ist.document_id)
+ ist.document_id,
+ r.id as latest_revision_id,
+ r.revision as latest_revision,
+ r.revision_status as latest_revision_status,
+ r.uploader_name as latest_revision_uploader_name,
+ r.submitted_date as latest_submitted_date
+ FROM revisions r
+ JOIN issue_stages ist ON r.issue_stage_id = ist.id
+ ORDER BY ist.document_id, r.created_at DESC
+ ),
+ -- 리비전별 첨부파일 집계
+ revision_attachments AS (
+ SELECT
+ r.id as revision_id,
+ COALESCE(
+ json_agg(
+ json_build_object(
+ 'id', da.id,
+ 'revisionId', da.revision_id,
+ 'fileName', da.file_name,
+ 'filePath', da.file_path,
+ 'fileSize', da.file_size,
+ 'fileType', da.file_type,
+ 'createdAt', da.created_at,
+ 'updatedAt', da.updated_at
+ ) ORDER BY da.created_at
+ ) FILTER (WHERE da.id IS NOT NULL),
+ '[]'::json
+ ) as attachments
+ FROM revisions r
+ LEFT JOIN document_attachments da ON r.id = da.revision_id
+ GROUP BY r.id
+ ),
+ -- 스테이지별 리비전 집계 (첨부파일 포함)
+ stage_revisions AS (
+ SELECT
+ ist.id as stage_id,
+ COALESCE(
+ json_agg(
+ json_build_object(
+ 'id', r.id,
+ 'issueStageId', r.issue_stage_id,
+ 'revision', r.revision,
+ 'uploaderType', r.uploader_type,
+ 'uploaderId', r.uploader_id,
+ 'uploaderName', r.uploader_name,
+ 'comment', r.comment,
+ 'revisionStatus', r.revision_status,
+ 'submittedDate', r.submitted_date,
+ 'approvedDate', r.approved_date,
+ 'reviewStartDate', r.review_start_date,
+ 'rejectedDate', r.rejected_date,
+ 'reviewerId', r.reviewer_id,
+ 'reviewerName', r.reviewer_name,
+ 'reviewComments', r.review_comments,
+ 'createdAt', r.created_at,
+ 'updatedAt', r.updated_at,
+ 'attachments', ra.attachments
+ ) ORDER BY r.created_at
+ ) FILTER (WHERE r.id IS NOT NULL),
+ '[]'::json
+ ) as revisions
+ FROM issue_stages ist
+ LEFT JOIN revisions r ON ist.id = r.issue_stage_id
+ LEFT JOIN revision_attachments ra ON r.id = ra.revision_id
+ GROUP BY ist.id
+ ),
+ -- 문서별 스테이지 집계 (리비전 포함)
+ stage_aggregation AS (
+ SELECT
+ ist.document_id,
+ json_agg(
+ json_build_object(
+ 'id', ist.id,
+ 'stageName', ist.stage_name,
+ 'stageStatus', ist.stage_status,
+ 'stageOrder', ist.stage_order,
+ 'planDate', ist.plan_date,
+ 'actualDate', ist.actual_date,
+ 'assigneeName', ist.assignee_name,
+ 'priority', ist.priority,
+ 'revisions', sr.revisions
+ ) ORDER BY ist.stage_order
+ ) as all_stages
+ FROM issue_stages ist
+ LEFT JOIN stage_revisions sr ON ist.id = sr.stage_id
+ GROUP BY ist.document_id
+ ),
+ attachment_counts AS (
+ SELECT
+ ist.document_id,
+ COUNT(da.id) as attachment_count
+ FROM issue_stages ist
+ LEFT JOIN revisions r ON ist.id = r.issue_stage_id
+ LEFT JOIN document_attachments da ON r.id = da.revision_id
+ GROUP BY ist.document_id
+ )
+
+ SELECT
+ d.id as document_id,
+ d.doc_number,
+ d.title,
+ d.pic,
+ d.status,
+ d.issued_date,
+ d.contract_id,
+
+ -- 현재 스테이지 정보
+ csi.current_stage_id,
+ csi.current_stage_name,
+ csi.current_stage_status,
+ csi.current_stage_order,
+ csi.current_stage_plan_date,
+ csi.current_stage_actual_date,
+ csi.current_stage_assignee_name,
+ csi.current_stage_priority,
+
+ -- 계산 필드
+ csi.days_until_due,
+ csi.is_overdue,
+ csi.days_difference,
+
+ -- 진행률 정보
+ ds.total_stages,
+ ds.completed_stages,
+ ds.progress_percentage,
+
+ -- 최신 리비전 정보
+ lri.latest_revision_id,
+ lri.latest_revision,
+ lri.latest_revision_status,
+ lri.latest_revision_uploader_name,
+ lri.latest_submitted_date,
+
+ -- 전체 스테이지 (리비전 및 첨부파일 포함)
+ COALESCE(sa.all_stages, '[]'::json) as all_stages,
+
+ -- 기타
+ COALESCE(ac.attachment_count, 0) as attachment_count,
+ d.created_at,
+ d.updated_at
+
+ FROM documents d
+ LEFT JOIN document_stats ds ON d.id = ds.document_id
+ LEFT JOIN current_stage_info csi ON d.id = csi.document_id
+ LEFT JOIN latest_revision_info lri ON d.id = lri.document_id
+ LEFT JOIN stage_aggregation sa ON d.id = sa.document_id
+ LEFT JOIN attachment_counts ac ON d.id = ac.document_id
+
+ ORDER BY d.created_at DESC
+`);
+
+// 기존 뷰들도 유지 (호환성을 위해)
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(),
pic: varchar("pic", { 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
+ latestStageId: integer("latest_stage_id"),
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`
@@ -157,61 +452,19 @@ export const vendorDocumentsView = pgView("vendor_documents_view", {
d.pic,
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 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 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,
+ (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
@@ -219,7 +472,6 @@ export const vendorDocumentsView = pgView("vendor_documents_view", {
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(),
@@ -228,11 +480,7 @@ export const documentStagesView = pgView("document_stages_view", {
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`
@@ -243,26 +491,169 @@ export const documentStagesView = pgView("document_stages_view", {
d.status,
d.issued_date,
d.contract_id,
-
- (
- SELECT COUNT(*)
- FROM issue_stages
- WHERE document_id = d.id
- ) AS stage_count,
-
+ (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
- ),
+ (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
+export type EnhancedDocumentsView = typeof enhancedDocumentsView.$inferSelect
+
+
+// 동기화 설정 테이블
+export const syncConfigs = pgTable(
+ "sync_configs",
+ {
+ id: serial("id").primaryKey(),
+ contractId: integer("contract_id")
+ .notNull()
+ .references(() => contracts.id, { onDelete: "cascade" }),
+ targetSystem: varchar("target_system", { length: 50 }).notNull(), // 'SHI', 'SAP' 등
+ syncEnabled: boolean("sync_enabled").default(true),
+ syncIntervalMinutes: integer("sync_interval_minutes").default(30), // 30분마다
+ lastSuccessfulSync: timestamp("last_successful_sync"),
+ lastSyncAttempt: timestamp("last_sync_attempt"),
+ endpointUrl: text("endpoint_url").notNull(),
+ authToken: text("auth_token"),
+ apiVersion: varchar("api_version", { length: 20 }).default("v1"),
+ maxBatchSize: integer("max_batch_size").default(100),
+ retryMaxAttempts: integer("retry_max_attempts").default(3),
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ updatedAt: timestamp("updated_at").defaultNow().notNull(),
+ },
+ (table) => {
+ return {
+ contractSystemIdx: index("idx_sync_configs_contract_system").on(
+ table.contractId,
+ table.targetSystem
+ ),
+ }
+ }
+)
+
+// 변경 로그 테이블 (모든 변경사항 추적)
+export const changeLogs = pgTable(
+ "change_logs",
+ {
+ id: serial("id").primaryKey(),
+ contractId: integer("contract_id").notNull(),
+ entityType: varchar("entity_type", { length: 50 }).notNull(), // 'document', 'revision', 'attachment'
+ entityId: integer("entity_id").notNull(),
+ action: varchar("action", { length: 20 }).notNull(), // 'CREATE', 'UPDATE', 'DELETE'
+ changedFields: jsonb("changed_fields").$type<Record<string, any>>(),
+ oldValues: jsonb("old_values").$type<Record<string, any>>(),
+ newValues: jsonb("new_values").$type<Record<string, any>>(),
+ userId: integer("user_id"),
+ userName: varchar("user_name", { length: 255 }),
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ isSynced: boolean("is_synced").default(false),
+ syncAttempts: integer("sync_attempts").default(0),
+ lastSyncError: text("last_sync_error"),
+ syncedAt: timestamp("synced_at"),
+ targetSystems: jsonb("target_systems").$type<string[]>().default(sql`'[]'::jsonb`), // 동기화할 시스템 목록
+ },
+ (table) => {
+ return {
+ contractSyncedIdx: index("idx_change_logs_contract_synced").on(
+ table.contractId,
+ table.isSynced
+ ),
+ createdAtIdx: index("idx_change_logs_created_at").on(table.createdAt),
+ entityIdx: index("idx_change_logs_entity").on(table.entityType, table.entityId),
+ syncAttemptsIdx: index("idx_change_logs_sync_attempts").on(table.syncAttempts),
+ }
+ }
+)
+
+// 동기화 배치 테이블 (배치 단위로 동기화 관리)
+export const syncBatches = pgTable(
+ "sync_batches",
+ {
+ id: serial("id").primaryKey(),
+ contractId: integer("contract_id").notNull(),
+ targetSystem: varchar("target_system", { length: 50 }).notNull(),
+ batchSize: integer("batch_size").notNull(),
+ status: varchar("status", { length: 20 }).notNull().default("PENDING"), // 'PENDING', 'PROCESSING', 'SUCCESS', 'FAILED', 'PARTIAL'
+ startedAt: timestamp("started_at"),
+ completedAt: timestamp("completed_at"),
+ errorMessage: text("error_message"),
+ retryCount: integer("retry_count").default(0),
+ changeLogIds: jsonb("change_log_ids").$type<number[]>().notNull(), // 포함된 change_log ID들
+ successCount: integer("success_count").default(0),
+ failureCount: integer("failure_count").default(0),
+ syncMetadata: jsonb("sync_metadata").$type<Record<string, any>>(), // 추가 메타데이터
+ createdAt: timestamp("created_at").defaultNow().notNull(),
+ updatedAt: timestamp("updated_at").defaultNow().notNull(),
+ },
+ (table) => {
+ return {
+ contractSystemIdx: index("idx_sync_batches_contract_system").on(
+ table.contractId,
+ table.targetSystem
+ ),
+ statusIdx: index("idx_sync_batches_status").on(table.status),
+ createdAtIdx: index("idx_sync_batches_created_at").on(table.createdAt),
+ }
+ }
+)
+
+// 동기화 상태 추적을 위한 뷰
+export const syncStatusView = pgView("sync_status_view", {
+ contractId: integer("contract_id").notNull(),
+ targetSystem: varchar("target_system", { length: 50 }).notNull(),
+ totalChanges: integer("total_changes").notNull(),
+ pendingChanges: integer("pending_changes").notNull(),
+ syncedChanges: integer("synced_changes").notNull(),
+ failedChanges: integer("failed_changes").notNull(),
+ lastSyncAt: timestamp("last_sync_at"),
+ nextSyncAt: timestamp("next_sync_at"),
+ syncEnabled: boolean("sync_enabled"),
+}).as(sql`
+ WITH change_stats AS (
+ SELECT
+ cl.contract_id,
+ sc.target_system,
+ COUNT(*) as total_changes,
+ COUNT(CASE WHEN cl.is_synced = false AND cl.sync_attempts < sc.retry_max_attempts THEN 1 END) as pending_changes,
+ COUNT(CASE WHEN cl.is_synced = true THEN 1 END) as synced_changes,
+ COUNT(CASE WHEN cl.sync_attempts >= sc.retry_max_attempts AND cl.is_synced = false THEN 1 END) as failed_changes,
+ MAX(cl.synced_at) as last_sync_at
+ FROM change_logs cl
+ CROSS JOIN sync_configs sc
+ WHERE cl.contract_id = sc.contract_id
+ AND (cl.target_systems IS NULL OR cl.target_systems @> to_jsonb(sc.target_system))
+ GROUP BY cl.contract_id, sc.target_system
+ )
+ SELECT
+ cs.contract_id,
+ cs.target_system,
+ COALESCE(cs.total_changes, 0) as total_changes,
+ COALESCE(cs.pending_changes, 0) as pending_changes,
+ COALESCE(cs.synced_changes, 0) as synced_changes,
+ COALESCE(cs.failed_changes, 0) as failed_changes,
+ cs.last_sync_at,
+ CASE
+ WHEN sc.sync_enabled = true AND sc.last_successful_sync IS NOT NULL
+ THEN sc.last_successful_sync + (sc.sync_interval_minutes || ' minutes')::interval
+ ELSE NULL
+ END as next_sync_at,
+ sc.sync_enabled
+ FROM sync_configs sc
+ LEFT JOIN change_stats cs ON sc.contract_id = cs.contract_id AND sc.target_system = cs.target_system
+`)
+
+// 타입 추출
+export type SyncConfig = typeof syncConfigs.$inferSelect
+export type SyncConfigInsert = typeof syncConfigs.$inferInsert
+export type ChangeLog = typeof changeLogs.$inferSelect
+export type ChangeLogInsert = typeof changeLogs.$inferInsert
+export type SyncBatch = typeof syncBatches.$inferSelect
+export type SyncBatchInsert = typeof syncBatches.$inferInsert
+export type SyncStatusView = typeof syncStatusView.$inferSelect
generated by cgit v1.2.3 (git 2.39.1) at 2025-12-07 13:18:52 +0000