path: root/db/schema/vendorDocu.ts

// 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(),
    pic: varchar("pic", { length: 50 }),
    contractId: integer("contract_id")
      .notNull()
      .references(() => contracts.id, { onDelete: "cascade" }),
    docNumber: varchar("doc_number", { length: 100 }).notNull(),
    // ✅ 벤더용 문서 번호 추가 (옵셔널)
    vendorDocNumber: varchar("vendor_doc_number", { length: 100 }),
    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 {
      uniqueContractDocStatus: uniqueIndex("unique_contract_doc_status").on(
        table.contractId,
        table.docNumber,
        table.status
      ),
      // ✅ 벤더 문서 번호가 있는 경우 유니크 제약 조건 (옵셔널이므로 별도 인덱스)
      uniqueContractVendorDoc: uniqueIndex("unique_contract_vendor_doc").on(
        table.contractId,
        table.vendorDocNumber
      ).where(sql`${table.vendorDocNumber} IS NOT NULL`),
    }
  }
)
// 확장된 issueStages 테이블
export const issueStages = pgTable(
  "issue_stages",
  {
    id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
    documentId: integer("document_id")
      .notNull()
      .references(() => documents.id, { onDelete: "cascade" }),
    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 {
      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"),
    uploaderId: integer("uploader_id"),
    uploaderName: varchar("uploader_name", { length: 100 }),
    
    // 확장된 상태 관리
    revisionStatus: varchar("revision_status", { length: 50 })
      .notNull()
      .default("SUBMITTED"), // SUBMITTED, UNDER_REVIEW, APPROVED, REJECTED, SUPERSEDED
    
    // 상세 날짜 추적
    submittedDate: date("submitted_date"),
    uploadedAt: date("uploaded_at"),
    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 {
      uniqueStageRev: uniqueIndex("unique_stage_rev").on(
        table.issueStageId,
        table.revision
      ),
    }
  }
)

// 기존 documentAttachments (변경 없음)
export const documentAttachments = pgTable(
  "document_attachments",
  {
    id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
    revisionId: integer("revision_id")
      .notNull()
      .references(() => revisions.id, { onDelete: "cascade" }),
    fileName: varchar("file_name", { length: 255 }).notNull(),
    filePath: varchar("file_path", { length: 1024 }).notNull(),
    fileType: varchar("file_type", { length: 50 }),
    fileSize: integer("file_size"),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
  }
)


export const enhancedDocumentsView = pgView("enhanced_documents_view", {
  // 기본 문서 정보
  documentId: integer("document_id").notNull(),
  docNumber: varchar("doc_number", { length: 100 }).notNull(),
  vendorDocNumber: varchar("vendor_doc_number", { length: 100 }), // ✅ 벤더 문서 번호 추가
  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(),
  
  // ✅ 프로젝트 및 벤더 정보 추가
  projectCode: varchar("project_code", { length: 50 }),
  vendorName: varchar("vendor_name", { length: 255 }),
  vendorCode: varchar("vendor_code", { length: 50 }),
  
  // 현재 스테이지 정보 (가장 우선순위가 높은 미완료 스테이지)
  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;
      uploadedAt: 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,
            'uploadedAt', r.uploaded_at,
            '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.vendor_doc_number, -- ✅ 벤더 문서 번호 추가
    d.title,
    d.pic,
    d.status,
    d.issued_date,
    d.contract_id,
    
    -- ✅ 프로젝트 및 벤더 정보 추가
    p.code as project_code,
    v.vendor_name as vendor_name,
    v.vendor_code as vendor_code,
    
    -- 현재 스테이지 정보
    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
  -- ✅ contracts, projects, vendors 테이블 JOIN 추가
  LEFT JOIN contracts c ON d.contract_id = c.id
  LEFT JOIN projects p ON c.project_id = p.id
  LEFT JOIN vendors v ON c.vendor_id = v.id
  
  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", {
  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"),
  latestStageName: varchar("latest_stage_name", { length: 100 }),
  latestStagePlanDate: date("latest_stage_plan_date"),
  latestStageActualDate: date("latest_stage_actual_date"),
  latestRevisionId: integer("latest_revision_id"),
  latestRevision: varchar("latest_revision", { length: 50 }),
  latestRevisionUploaderType: varchar("latest_revision_uploader_type", { length: 20 }),
  latestRevisionUploaderName: varchar("latest_revision_uploader_name", { length: 100 }),
  attachmentCount: integer("attachment_count"),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).notNull(),
}).as(sql`
    SELECT 
      d.id, 
      d.doc_number,
      d.title,
      d.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 r.id FROM revisions r JOIN issue_stages i ON r.issue_stage_id = i.id WHERE i.document_id = d.id ORDER BY r.created_at DESC LIMIT 1) AS latest_revision_id,
      (SELECT r.revision FROM revisions r JOIN issue_stages i ON r.issue_stage_id = i.id WHERE i.document_id = d.id ORDER BY r.created_at DESC LIMIT 1) AS latest_revision,
      (SELECT r.uploader_type FROM revisions r JOIN issue_stages i ON r.issue_stage_id = i.id WHERE i.document_id = d.id ORDER BY r.created_at DESC LIMIT 1) AS latest_revision_uploader_type,
      (SELECT r.uploader_name FROM revisions r JOIN issue_stages i ON r.issue_stage_id = i.id WHERE i.document_id = d.id ORDER BY r.created_at DESC LIMIT 1) AS latest_revision_uploader_name,
  
      (SELECT COUNT(*) FROM document_attachments a JOIN revisions r ON a.revision_id = r.id JOIN issue_stages i ON r.issue_stage_id = i.id WHERE i.document_id = d.id) AS attachment_count,
  
      d.created_at,
      d.updated_at
    FROM documents d
    JOIN contracts c ON d.contract_id = c.id
  `);

export const documentStagesView = pgView("document_stages_view", {
  documentId: integer("document_id").notNull(),
  docNumber: varchar("doc_number", { length: 100 }).notNull(),
  title: varchar("title", { length: 255 }).notNull(),
  status: varchar("status", { length: 50 }).notNull(),
  issuedDate: date("issued_date"),
  contractId: integer("contract_id").notNull(),
  stageCount: integer("stage_count").notNull(),
  stageList: jsonb("stage_list").$type<string[] | null>(),
  createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).notNull(),
}).as(sql`
  SELECT
    d.id AS document_id,
    d.doc_number,
    d.title,
    d.status,
    d.issued_date,
    d.contract_id,
    (SELECT COUNT(*) FROM issue_stages WHERE document_id = d.id) AS stage_count,
    COALESCE( 
      (SELECT json_agg(i.stage_name) FROM issue_stages i WHERE i.document_id = d.id), 
      '[]'
    ) AS stage_list,
    d.created_at,
    d.updated_at
  FROM documents d
`);

// 타입 추출
export type VendorDocumentsView = typeof vendorDocumentsView.$inferSelect
export type DocumentStagesView = typeof documentStagesView.$inferSelect
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