import { pgTable, integer, varchar, timestamp, date, pgView, uniqueIndex, jsonb } from "drizzle-orm/pg-core"
import { eq, sql } from "drizzle-orm";
import { projects } from "./projects";
import { vendors } from "./vendors";
import { contracts } from "./contract";


export const documents = pgTable(
  "documents",
  {
    // 주 키
    id: integer("id").primaryKey().generatedAlwaysAsIdentity(),

    // 어느 계약(Contract) 소속인지
    contractId: integer("contract_id")
      .notNull()
      .references(() => contracts.id, { onDelete: "cascade" }),

    // 예: 문서 번호(유니크 설정 가능)
    docNumber: varchar("doc_number", { length: 100 }).notNull(),
    // 예: 문서(도서) 제목
    title: varchar("title", { length: 255 }).notNull(),

    // 추가 예시: 발행일, 상태 등
    status: varchar("status", { length: 50 })
      .notNull()
      .default("ACTIVE"),
    issuedDate: date("issued_date"),

    // 생성/수정 시각
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
  },
  (table) => {
    return {
      // contractId + docNumber + status 복합 유니크
      uniqueContractDocStatus: uniqueIndex("unique_contract_doc_status").on(
        table.contractId,
        table.docNumber,
        table.status
      ),
    }
  }
)

export const issueStages = pgTable(
  "issue_stages",
  {
    // 주 키
    id: integer("id").primaryKey().generatedAlwaysAsIdentity(),

    // 어느 문서 소속인지
    documentId: integer("document_id")
      .notNull()
      .references(() => documents.id, { onDelete: "cascade" }),

    // 스테이지명 (예: "Issued for Review", "AFC" 등)
    stageName: varchar("stage_name", { length: 100 }).notNull(),

    // 계획일, 실제일
    planDate: date("plan_date"),
    actualDate: date("actual_date"),

    // 생성/수정 시각
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
  },
  (table) => {
    return {
      // document_id + stage_name 조합을 유니크 인덱스로 지정
      uniqueDocumentStage: uniqueIndex("unique_document_stage").on(
        table.documentId,
        table.stageName
      ),
    }
  }
);

export const revisions = pgTable(
  "revisions",
  {
    id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
    issueStageId: integer("issue_stage_id").notNull(),
    revision: varchar("revision", { length: 50 }).notNull(),
    // 새로운 필드: 업로더 타입 (업체 또는 고객사)
    uploaderType: varchar("uploader_type", { length: 20 }).notNull().default("vendor"),
    // 선택적: 업로더 ID 또는 이름
    uploaderId: integer("uploader_id"),
    uploaderName: varchar("uploader_name", { length: 100 }),
    // 선택적: 추가 메타데이터
    comment: varchar("comment", { length: 500 }),
    status: varchar("status", { length: 50 }),
    approvedDate: date("approved_date"),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
  },
  (table) => {
    return {
      // "issue_stage_id + revision" 조합을 유니크로 묶음 (유지)
      uniqueStageRev: uniqueIndex("unique_stage_rev").on(
        table.issueStageId,
        table.revision
      ),
    }
  }
)
export const documentAttachments = pgTable(
  "document_attachments",
  {
    id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
    revisionId: integer("revision_id")
      .notNull()
      .references(() => revisions.id, { onDelete: "cascade" }),
    fileName: varchar("file_name", { length: 255 }).notNull(),
    filePath: varchar("file_path", { length: 1024 }).notNull(),
    fileType: varchar("file_type", { length: 50 }),
    fileSize: integer("file_size"),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
  }
)

export const vendorDocumentsView = pgView("vendor_documents_view", {
  // Match the columns in your SELECT statement
  id: integer("id").notNull(),
  docNumber: varchar("doc_number", { length: 100 }).notNull(),
  title: varchar("title", { length: 255 }).notNull(),
  status: varchar("status", { length: 50 }).notNull(),
  issuedDate: date("issued_date"),

  contractId: integer("contract_id").notNull(),

  latestStageId: integer("latest_stage_id"), // possibly can be null
  latestStageName: varchar("latest_stage_name", { length: 100 }),
  latestStagePlanDate: date("latest_stage_plan_date"),
  latestStageActualDate: date("latest_stage_actual_date"),

  latestRevisionId: integer("latest_revision_id"),
  latestRevision: varchar("latest_revision", { length: 50 }),
  latestRevisionUploaderType: varchar("latest_revision_uploader_type", { length: 20 }),
  latestRevisionUploaderName: varchar("latest_revision_uploader_name", { length: 100 }),

  attachmentCount: integer("attachment_count"),

  createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).notNull(),
}).as(sql`
    SELECT 
      d.id, 
      d.doc_number,
      d.title,
      d.status,
      d.issued_date,
  
      d.contract_id,
  
      (
        SELECT id FROM issue_stages
        WHERE document_id = d.id
        ORDER BY created_at DESC LIMIT 1
      ) AS latest_stage_id,
      (
        SELECT stage_name FROM issue_stages
        WHERE document_id = d.id
        ORDER BY created_at DESC LIMIT 1
      ) AS latest_stage_name,
      (
        SELECT plan_date FROM issue_stages
        WHERE document_id = d.id
        ORDER BY created_at DESC LIMIT 1
      ) AS latest_stage_plan_date,
      (
        SELECT actual_date FROM issue_stages
        WHERE document_id = d.id
        ORDER BY created_at DESC LIMIT 1
      ) AS latest_stage_actual_date,
  
      (
        SELECT r.id FROM revisions r
        JOIN issue_stages i ON r.issue_stage_id = i.id
        WHERE i.document_id = d.id
        ORDER BY r.created_at DESC LIMIT 1
      ) AS latest_revision_id,
      (
        SELECT r.revision FROM revisions r
        JOIN issue_stages i ON r.issue_stage_id = i.id
        WHERE i.document_id = d.id
        ORDER BY r.created_at DESC LIMIT 1
      ) AS latest_revision,
      (
        SELECT r.uploader_type FROM revisions r
        JOIN issue_stages i ON r.issue_stage_id = i.id
        WHERE i.document_id = d.id
        ORDER BY r.created_at DESC LIMIT 1
      ) AS latest_revision_uploader_type,
      (
        SELECT r.uploader_name FROM revisions r
        JOIN issue_stages i ON r.issue_stage_id = i.id
        WHERE i.document_id = d.id
        ORDER BY r.created_at DESC LIMIT 1
      ) AS latest_revision_uploader_name,
  
      (
        SELECT COUNT(*) FROM document_attachments a
        JOIN revisions r ON a.revision_id = r.id
        JOIN issue_stages i ON r.issue_stage_id = i.id
        WHERE i.document_id = d.id
      ) AS attachment_count,
  
      d.created_at,
      d.updated_at
    FROM documents d
    JOIN contracts c ON d.contract_id = c.id
  `);

// 문서 + 스테이지 리스트 뷰
export const documentStagesView = pgView("document_stages_view", {
  documentId: integer("document_id").notNull(),
  docNumber: varchar("doc_number", { length: 100 }).notNull(),
  title: varchar("title", { length: 255 }).notNull(),
  status: varchar("status", { length: 50 }).notNull(),
  issuedDate: date("issued_date"),
  contractId: integer("contract_id").notNull(),
  stageCount: integer("stage_count").notNull(),

  // 문자열 배열을 받을 것이므로 jsonb + $type<string[]>()
  // 스테이지가 없으면 null이 올 수도 있다면 string[] | null
  stageList: jsonb("stage_list").$type<string[] | null>(),

  createdAt: timestamp("created_at", { withTimezone: true }).notNull(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).notNull(),
}).as(sql`
  SELECT
    d.id AS document_id,
    d.doc_number,
    d.title,
    d.status,
    d.issued_date,
    d.contract_id,

    (
      SELECT COUNT(*)
      FROM issue_stages
      WHERE document_id = d.id
    ) AS stage_count,

    COALESCE( 
      (
        SELECT json_agg(i.stage_name)
        FROM issue_stages i
        WHERE i.document_id = d.id
      ), 
      '[]'
    ) AS stage_list,

    d.created_at,
    d.updated_at
  FROM documents d
`);

export type VendorDocumentsView = typeof vendorDocumentsView.$inferSelect
export type DocumentStagesView = typeof documentStagesView.$inferSelect