(대표님 / 최겸) 스키마 변경사항 및 마이그레이션 커밋

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