path: root/db/schema/vendorDocu.ts

// enhanced-schema.ts
import {
  pgTable, integer, varchar, timestamp, date, pgView, uniqueIndex, jsonb, boolean, serial,json,
  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 }),
    projectId: integer("project_id")
      // .notNull()
      .references(() => projects.id, { onDelete: "cascade" }),
    contractId: integer("contract_id")
      // .notNull()
      .references(() => contracts.id, { onDelete: "cascade" }),

    vendorId: integer("vendor_id")
      // .notNull()
      .references(() => vendors.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"),

    // ✅ DOLCE 연동을 위한 새로운 필드들
    drawingKind: varchar("drawing_kind", { length: 10 }), // B3, B4, B5
    drawingMoveGbn: varchar("drawing_move_gbn", { length: 50 }), // 도면입수, 도면제출, GTT Deliverable, SHI Input Information
    discipline: varchar("discipline", { length: 10 }), // DE, ME, etc.

    // ✅ 외부 시스템 연동 정보
    externalDocumentId: varchar("external_document_id", { length: 100 }), // DOLCE 시스템의 문서 ID
    externalSystemType: varchar("external_system_type", { length: 20 }), // DOLCE, SWP
    externalSyncedAt: timestamp("external_synced_at"), // 마지막 동기화 시간

    // ✅ B4 전용 필드들 (drawingKind가 B4일 때만 사용)
    cGbn: varchar("c_gbn", { length: 50 }), // CGbn
    dGbn: varchar("d_gbn", { length: 50 }), // DGbn  
    degreeGbn: varchar("degree_gbn", { length: 50 }), // DegreeGbn
    deptGbn: varchar("dept_gbn", { length: 50 }), // DeptGbn
    jGbn: varchar("j_gbn", { length: 50 }), // JGbn
    sGbn: varchar("s_gbn", { length: 50 }), // SGbn

    // ✅ DOLCE 응답의 추가 정보들
    shiDrawingNo: varchar("shi_drawing_no", { length: 100 }), // SHI 도면 번호
    manager: varchar("manager", { length: 100 }), // 담당자
    managerENM: varchar("manager_enm", { length: 100 }), // 담당자 영문명  
    managerNo: varchar("manager_no", { length: 50 }), // 담당자 번호
    registerGroup: integer("register_group"), // 등록 그룹
    registerGroupId: integer("register_group_id"), // 등록 그룹 ID

    // ✅ 생성자 정보 (DOLCE에서 가져온 정보)
    createUserNo: varchar("create_user_no", { length: 50 }), // 생성자 번호
    createUserId: varchar("create_user_id", { length: 100 }), // 생성자 ID (한글명)
    createUserENM: varchar("create_user_enm", { length: 100 }), // 생성자 영문명

    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`),

      // ✅ 외부 시스템 문서 ID 유니크 인덱스
      uniqueExternalDoc: uniqueIndex("unique_external_doc").on(
        table.contractId,
        table.externalDocumentId,
        table.externalSystemType
      ).where(sql`${table.externalDocumentId} IS NOT NULL`),


      uniqueProjectDocStatus: uniqueIndex("unique_project_doc_status").on(
        table.projectId,
        table.docNumber,
        table.status
      ),
      // uniqueProjectVendorDoc: uniqueIndex("unique_project_vendor_doc").on(
      //   table.projectId,
      //   table.vendorDocNumber
      // ).where(sql`${table.vendorDocNumber} IS NOT NULL`),

      // ✅ 외부 시스템 문서 ID 유니크 인덱스
      uniqueExternalDocProject: uniqueIndex("unique_external_doc_project").on(
        table.projectId,
        table.externalDocumentId,
        table.externalSystemType
      ).where(sql`${table.externalDocumentId} IS NOT NULL`),

      // ✅ drawingKind 인덱스 (자주 검색될 것 같음)
      drawingKindIndex: index("drawing_kind_idx").on(table.drawingKind),
    }
  }
)

// 확장된 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 }),

    usage: varchar("usage", { length: 100 }),
    usageType: varchar("usage_type", { length: 255 }),

    // 확장된 상태 관리
    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 }),
    externalUploadId: varchar("external_upload_id", { length: 255 }),

    comment: varchar("comment", { length: 500 }),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),

    registerId: varchar("register_id", { length: 50 }), // 상대 시스템에서 생성한 ID

  },
  (table) => {
    return {
      uniqueStageRevisionUsage: uniqueIndex("unique_stage_revision_usage").on(
        table.issueStageId,
        table.revision,
        table.usage,
        sql`COALESCE(${table.usageType}, '')`
      ),

    }
  }
)

// 기존 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"),

    // DOLCE 연동 관련 필드 추가
    uploadId: varchar("upload_id", { length: 36 }), // UUID 형태
    fileId: varchar("file_id", { length: 36 }),     // UUID 형태
    uploadedBy: varchar("uploaded_by", { length: 255 }), // userId 저장
    dolceFilePath: varchar("dolce_file_path", { length: 1024 }), // DOLCE에서 반환하는 파일 경로
    uploadedAt: timestamp("uploaded_at"), // DOLCE 업로드 성공 시간

    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(),
  drawingKind: varchar("drawing_kind", { length: 50 }),
  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(),

  // ✅ 프로젝트 및 벤더 정보 추가
  projectId: integer("project_id"),
  projectCode: varchar("project_code", { length: 50 }),
  vendorName: varchar("vendor_name", { length: 255 }),
  vendorCode: varchar("vendor_code", { length: 50 }),
  vendorId: varchar("vendor_id", { length: 50 }),

  // ✅ B4 전용 필드들 (drawingKind가 B4일 때만 사용)
  cGbn: varchar("c_gbn", { length: 50 }), // CGbn
  dGbn: varchar("d_gbn", { length: 50 }), // DGbn  
  degreeGbn: varchar("degree_gbn", { length: 50 }), // DegreeGbn
  deptGbn: varchar("dept_gbn", { length: 50 }), // DeptGbn
  jGbn: varchar("j_gbn", { length: 50 }), // JGbn
  sGbn: varchar("s_gbn", { length: 50 }), // SGbn

  // 현재 스테이지 정보 (가장 우선순위가 높은 미완료 스테이지)
  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;
      usage: 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,
            'usage', r.usage,
            '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.drawing_kind,
    d.vendor_doc_number, -- ✅ 벤더 문서 번호 추가
    d.title,
    d.pic,
    d.status,
    d.issued_date,
    d.contract_id,

    d.c_gbn,
    d.d_gbn,
    d.degree_gbn,
    d.dept_gbn,
    d.s_gbn,
    d.j_gbn,


    
    -- ✅ 프로젝트 및 벤더 정보 추가
    c.project_id as project_id,
    p.code as project_code,
    v.vendor_name as vendor_name,
    v.vendor_code as vendor_code,
    c.vendor_id as vendor_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
  -- ✅ 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(),
    vendorId: integer("vendor_id").notNull(),

    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.vendorId,
        table.targetSystem
      ),
    }
  }
)

// 변경 로그 테이블 (모든 변경사항 추적)
export const changeLogs = pgTable(
  "change_logs",
  {
    id: serial("id").primaryKey(),
    vendorId: integer("vendor_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 {
      vendorSyncedIdx: index("idx_change_logs_vendor_synced").on(
        table.vendorId,
        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(),
    vendorId: integer("vendor_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 {
      projectSystemIdx: index("idx_sync_batches_project_system").on(
        table.vendorId,
        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", {
  vendorId: integer("vendor_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.vendor_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.vendor_id = sc.vendor_id
      AND (cl.target_systems IS NULL OR cl.target_systems @> to_jsonb(ARRAY[sc.target_system]))
    GROUP BY cl.vendor_id, sc.target_system
  )
  SELECT 
    cs.vendor_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.vendor_id = cs.vendor_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


export const simplifiedDocumentsView = pgView("simplified_documents_view", {
  // 기본 문서 정보
  documentId: integer("document_id").notNull(),
  projectId: integer("project_id"),
  docNumber: varchar("doc_number", { length: 100 }).notNull(),
  drawingKind: varchar("drawing_kind", { length: 10 }),
  drawingMoveGbn: varchar("drawing_move_gbn", { length: 50 }),
  discipline: varchar("discipline", { length: 10 }),
  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"),
  vendorId: integer("vendor_id"),

  // 외부 시스템 연동 정보
  externalDocumentId: varchar("external_document_id", { length: 100 }),
  externalSystemType: varchar("external_system_type", { length: 20 }),
  externalSyncedAt: timestamp("external_synced_at"),

  // DOLCE 응답의 추가 정보들
  shiDrawingNo: varchar("shi_drawing_no", { length: 100 }),
  manager: varchar("manager", { length: 100 }),
  managerENM: varchar("manager_enm", { length: 100 }),
  managerNo: varchar("manager_no", { length: 50 }),
  registerGroup: integer("register_group"),
  registerGroupId: integer("register_group_id"),

  // 생성자 정보
  createUserNo: varchar("create_user_no", { length: 50 }),
  createUserId: varchar("create_user_id", { length: 100 }),
  createUserENM: varchar("create_user_enm", { length: 100 }),

  // 프로젝트 및 벤더 정보
  projectCode: varchar("project_code", { length: 50 }),
  vendorName: varchar("vendor_name", { length: 255 }),
  vendorCode: varchar("vendor_code", { length: 50 }),

  // B4 전용 필드들
  cGbn: varchar("c_gbn", { length: 50 }),
  dGbn: varchar("d_gbn", { length: 50 }),
  degreeGbn: varchar("degree_gbn", { length: 50 }),
  deptGbn: varchar("dept_gbn", { length: 50 }),
  jGbn: varchar("j_gbn", { length: 50 }),
  sGbn: varchar("s_gbn", { length: 50 }),

  // 첫 번째 스테이지 날짜 정보 (drawingKind에 따라 다름)
  firstStageId: integer("first_stage_id"),
  firstStageName: varchar("first_stage_name", { length: 100 }),
  firstStagePlanDate: date("first_stage_plan_date"),
  firstStageActualDate: date("first_stage_actual_date"),

  // 두 번째 스테이지 날짜 정보 (drawingKind에 따라 다름)
  secondStageId: integer("second_stage_id"),
  secondStageName: varchar("second_stage_name", { length: 100 }),
  secondStagePlanDate: date("second_stage_plan_date"),
  secondStageActualDate: date("second_stage_actual_date"),

  // 전체 스테이지 목록
  allStages: json("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;
      usage: string | null;
      usageType: 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;
        dolceFilePath: 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 
  -- 리비전별 첨부파일 집계
  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,
            'dolceFilePath', da.dolce_file_path,
            '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,
            'usage', r.usage,
            'usageType', r.usage_type,
            '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', COALESCE(ra.attachments, '[]'::json)
          ) 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', COALESCE(sr.revisions, '[]'::json)
        ) 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
  ),
  
  -- 첫 번째 스테이지 정보 (drawingKind에 따라 다른 조건)
  first_stage_info AS (
    SELECT 
      document_id,
      first_stage_id,
      first_stage_name,
      first_stage_plan_date,
      first_stage_actual_date
    FROM (
      SELECT 
        ist.document_id,
        ist.id as first_stage_id,
        ist.stage_name as first_stage_name,
        ist.plan_date as first_stage_plan_date,
        ist.actual_date as first_stage_actual_date,
        ROW_NUMBER() OVER (PARTITION BY ist.document_id ORDER BY ist.stage_order ASC) as rn
      FROM issue_stages ist
      JOIN documents d ON ist.document_id = d.id
      WHERE 
        (d.drawing_kind = 'B4' AND LOWER(ist.stage_name) LIKE '%pre%') OR
        (d.drawing_kind = 'B3' AND LOWER(ist.stage_name) LIKE '%approval%') OR
        (d.drawing_kind = 'B5' AND LOWER(ist.stage_name) LIKE '%first%')
    ) ranked
    WHERE rn = 1
  ),
  
  -- 두 번째 스테이지 정보 (drawingKind에 따라 다른 조건)
  second_stage_info AS (
    SELECT 
      document_id,
      second_stage_id,
      second_stage_name,
      second_stage_plan_date,
      second_stage_actual_date
    FROM (
      SELECT 
        ist.document_id,
        ist.id as second_stage_id,
        ist.stage_name as second_stage_name,
        ist.plan_date as second_stage_plan_date,
        ist.actual_date as second_stage_actual_date,
        ROW_NUMBER() OVER (PARTITION BY ist.document_id ORDER BY ist.stage_order ASC) as rn
      FROM issue_stages ist
      JOIN documents d ON ist.document_id = d.id
      WHERE 
        (d.drawing_kind = 'B4' AND LOWER(ist.stage_name) LIKE '%work%') OR
        (d.drawing_kind = 'B3' AND LOWER(ist.stage_name) LIKE '%work%') OR
        (d.drawing_kind = 'B5' AND LOWER(ist.stage_name) LIKE '%second%')
    ) ranked
    WHERE rn = 1
  ),
  
  -- 첨부파일 수 집계
  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.project_id,
    d.vendor_id,
    d.doc_number,
    d.drawing_kind,
    d.drawing_move_gbn,
    d.discipline,
    d.vendor_doc_number,
    d.title,
    d.pic,
    d.status,
    d.issued_date,
    d.contract_id,
    
    -- 외부 시스템 연동 정보
    d.external_document_id,
    d.external_system_type,
    d.external_synced_at,
    
    -- DOLCE 응답의 추가 정보들
    d.shi_drawing_no,
    d.manager,
    d.manager_enm,
    d.manager_no,
    d.register_group,
    d.register_group_id,
    
    -- 생성자 정보
    d.create_user_no,
    d.create_user_id,
    d.create_user_enm,
    
    -- 프로젝트 및 벤더 정보
    p.code as project_code,
    v.vendor_name,
    v.vendor_code,
    
    -- B4 전용 필드들
    d.c_gbn,
    d.d_gbn,
    d.degree_gbn,
    d.dept_gbn,
    d.s_gbn,
    d.j_gbn,
    
    -- 첫 번째 스테이지 정보
    fsi.first_stage_id,
    fsi.first_stage_name,
    fsi.first_stage_plan_date,
    fsi.first_stage_actual_date,
    
    -- 두 번째 스테이지 정보
    ssi.second_stage_id,
    ssi.second_stage_name,
    ssi.second_stage_plan_date,
    ssi.second_stage_actual_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
  -- projects, vendors 테이블 JOIN (projectId가 이제 documents에 직접 있음)
  LEFT JOIN projects p ON d.project_id = p.id AND p.type = 'ship'
  LEFT JOIN contracts c ON d.contract_id = c.id
  LEFT JOIN vendors v ON c.vendor_id = v.id
  
  -- 스테이지 정보 JOIN
  LEFT JOIN first_stage_info fsi ON d.id = fsi.document_id
  LEFT JOIN second_stage_info ssi ON d.id = ssi.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 type SimplifiedDocumentsView = typeof simplifiedDocumentsView.$inferSelect


export const documentStagesOnlyView = pgView("document_stages_only_view", {
  // 기본 문서 정보
  documentId: integer("document_id").notNull(),
  docNumber: varchar("doc_number", { length: 100 }).notNull(),
  drawingKind: varchar("drawing_kind", { length: 50 }),
  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 }),
  vendorId: varchar("vendor_id", { 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"),

  // 전체 스테이지 목록 (리비전 및 첨부파일 제외)
  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;
    description: string | null;
    notes: string | null;
    reminderDays: number | null;
  }>>(),

  // 메타 정보
  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
  ),
  -- 문서별 스테이지 집계 (리비전 제외)
  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,
          'description', ist.description,
          'notes', ist.notes,
          'reminderDays', ist.reminder_days
        ) ORDER BY ist.stage_order
      ) as all_stages
    FROM issue_stages ist
    GROUP BY ist.document_id
  )
  
  SELECT 
    d.id as document_id,
    d.doc_number,
    d.drawing_kind,
    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,
    c.vendor_id as vendor_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,
    
    -- 전체 스테이지 (리비전 제외)
    COALESCE(sa.all_stages, '[]'::json) as all_stages,
    
    -- 메타 정보
    d.created_at,
    d.updated_at
    
  FROM documents d
  -- 프로젝트 및 벤더 정보 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
  
  -- 스테이지 관련 정보 JOIN
  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 stage_aggregation sa ON d.id = sa.document_id
  
  ORDER BY d.created_at DESC
`);

// 타입 추출
export type DocumentStagesOnlyView = typeof documentStagesOnlyView.$inferSelect


export const stageDocuments = pgTable(
  "stage_documents",
  {
    id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
    
    // 필수 문서 정보
    docNumber: varchar("doc_number", { length: 100 }).notNull(),
    title: varchar("title", { length: 255 }).notNull(),
    status: varchar("status", { length: 50 }).notNull().default("ACTIVE"),
    
    // 선택적 문서 정보
    vendorDocNumber: varchar("vendor_doc_number", { length: 100 }),
    issuedDate: date("issued_date"),
    
    // 직접 참조로 JOIN 최소화
    projectId: integer("project_id")
      .notNull()
      .references(() => projects.id, { onDelete: "cascade" }),
    vendorId: integer("vendor_id").notNull(), // vendors 테이블의 vendor_id 직접 저장
    contractId: integer("contract_id").notNull(),
    // 메타 정보
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
  },
  (table) => {
    return {
      // 프로젝트 내에서 문서 번호 유니크
      uniqueProjectDoc: uniqueIndex("unique_project_doc").on(
        table.projectId,
        table.docNumber,
        table.status
      ),
      
      // 벤더 문서 번호 유니크 (있는 경우)
      uniqueProjectVendorDoc: uniqueIndex("unique_project_vendor_doc").on(
        table.projectId,
        table.vendorDocNumber
      ).where(sql`${table.vendorDocNumber} IS NOT NULL`),
      
      // 검색용 인덱스
      vendorIdIndex: index("stage_doc_vendor_id_idx").on(table.vendorId),
      statusIndex: index("stage_doc_status_idx").on(table.status),
    }
  }
)

// 🎯 간소화된 스테이지 전용 뷰
export const stageDocumentsView = pgView("stage_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(),
  status: varchar("status", { length: 50 }).notNull(),
  issuedDate: date("issued_date"),
  
  // 프로젝트 및 벤더 정보 (직접 참조로 간소화)
  projectId: integer("project_id").notNull(),
  contractId: integer("contract_id").notNull(),
  projectCode: varchar("project_code", { length: 50 }),
  vendorId: integer("vendor_id").notNull(),
  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"),

  // 전체 스테이지 목록 (리비전 및 첨부파일 제외)
  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;
    description: string | null;
    notes: string | null;
    reminderDays: number | null;
  }>>(),

  // 메타 정보
  createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).notNull(),
}).as(sql`
  WITH document_stats AS (
    SELECT 
      sd.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 stage_documents sd
    LEFT JOIN stage_issue_stages ist ON sd.id = ist.document_id
    GROUP BY sd.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 stage_issue_stages
    WHERE stage_status NOT IN ('COMPLETED', 'APPROVED')
    ORDER BY document_id, stage_order ASC, priority DESC
  ),
  -- 문서별 스테이지 집계 (리비전 제외)
  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,
          'description', ist.description,
          'notes', ist.notes,
          'reminderDays', ist.reminder_days
        ) ORDER BY ist.stage_order
      ) as all_stages
    FROM stage_issue_stages ist
    GROUP BY ist.document_id
  )
  
  SELECT 
    sd.id as document_id,
    sd.doc_number,
    sd.vendor_doc_number,
    sd.title,
    sd.status,
    sd.issued_date,
    
    -- 프로젝트 및 벤더 정보 (직접 참조로 간소화)
    sd.project_id,
    sd.contract_id,
    p.code as project_code,
    sd.vendor_id,
    v.vendor_name,
    v.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,
    
    -- 전체 스테이지 (리비전 제외)
    COALESCE(sa.all_stages, '[]'::json) as all_stages,
    
    -- 메타 정보
    sd.created_at,
    sd.updated_at
    
  FROM stage_documents sd
  -- 간소화된 JOIN (vendors는 vendor_id로 직접 조인)
  LEFT JOIN projects p ON sd.project_id = p.id
  LEFT JOIN vendors v ON sd.vendor_id = v.id
  
  -- 스테이지 관련 정보 JOIN
  LEFT JOIN document_stats ds ON sd.id = ds.document_id
  LEFT JOIN current_stage_info csi ON sd.id = csi.document_id
  LEFT JOIN stage_aggregation sa ON sd.id = sa.document_id
  
  ORDER BY sd.created_at DESC
`);


// 🎯 issue_stages 테이블도 stage_documents를 참조하도록 수정
export const stageIssueStages = pgTable(
  "stage_issue_stages", // 또는 기존 issue_stages 테이블을 수정
  {
    id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
    documentId: integer("document_id")
      .notNull()
      .references(() => stageDocuments.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_stage_document_stage").on(
        table.documentId,
        table.stageName
      ),
      documentStageOrder: uniqueIndex("stage_document_stage_order").on(
        table.documentId,
        table.stageOrder
      ),
    }
  }
);

// 타입 추출
export type StageDocument = typeof stageDocuments.$inferSelect
export type StageDocumentInsert = typeof stageDocuments.$inferInsert
export type StageDocumentsView = typeof stageDocumentsView.$inferSelect
export type StageIssueStage = typeof stageIssueStages.$inferSelect
export type StageIssueStageInsert = typeof stageIssueStages.$inferInsert