"use server";

import db from "@/db/db";
import { eq, and, sql } from "drizzle-orm";
import {
  swpDocuments,
  swpDocumentRevisions,
  swpDocumentFiles,
  type SwpDocumentInsert,
  type SwpDocumentRevisionInsert,
  type SwpDocumentFileInsert,
  swpSchema,
} from "@/db/schema/SWP/swp-documents";

// ============================================================================
// API 응답 타입 정의
// ============================================================================

export interface SwpDocumentApiResponse {
  DOC_NO: string;
  DOC_TITLE: string;
  DOC_GB: string;
  DOC_TYPE: string;
  OWN_DOC_NO: string;
  SHI_DOC_NO: string;
  PROJ_NO: string;
  PROJ_NM: string;
  PKG_NO: string;
  MAT_CD: string;
  MAT_NM: string;
  DISPLN: string;
  CTGRY: string;
  VNDR_CD: string;
  CPY_CD: string;
  CPY_NM: string;
  PIC_NM: string;
  PIC_DEPTCD: string;
  PIC_DEPTNM: string;
  LTST_REV_NO: string;
  LTST_REV_SEQ: string;
  LTST_ACTV_STAT: string;
  STAGE: string;
  SKL_CD: string;
  MOD_TYPE: string;
  ACT_TYPE_NM: string;
  USE_YN: string;
  CRTER: string;
  CRTE_DTM: string;
  CHGR: string;
  CHG_DTM: string;
  REV_DTM: string | null;
}

export interface SwpFileApiResponse {
  OWN_DOC_NO: string;
  REV_NO: string;
  STAGE: string;
  FILE_NM: string;
  FILE_SEQ: string;
  FILE_SZ: string;
  FLD_PATH: string;
  ACTV_NO: string | null;
  ACTV_SEQ: string;
  BOX_SEQ: string;
  OFDC_NO: string;
  PROJ_NO: string;
  PKG_NO: string;
  VNDR_CD: string;
  CPY_CD: string;
  STAT: string;
  STAT_NM: string;
  IDX: string;
  CRTER: string;
  CRTE_DTM: string;
  CHGR: string;
  CHG_DTM: string;
}

// ============================================================================
// 동기화 결과 타입
// ============================================================================

export interface SyncResult {
  success: boolean;
  projectNo: string;
  stats: {
    documents: {
      total: number;
      inserted: number;
      updated: number;
    };
    revisions: {
      total: number;
      inserted: number;
      updated: number;
    };
    files: {
      total: number;
      inserted: number;
      updated: number;
    };
  };
  errors: string[];
  duration: number;
}

// ============================================================================
// 동기화 메인 함수
// ============================================================================

export async function syncSwpProject(
  projectNo: string,
  documents: SwpDocumentApiResponse[],
  files: SwpFileApiResponse[]
): Promise<SyncResult> {
  const startTime = Date.now();
  const errors: string[] = [];
  const stats = {
    documents: { total: 0, inserted: 0, updated: 0 },
    revisions: { total: 0, inserted: 0, updated: 0 },
    files: { total: 0, inserted: 0, updated: 0 },
  };

  try {
    // 트랜잭션으로 일괄 처리
    await db.transaction(async (tx) => {
      // 1. 문서 동기화
      console.log(`[SYNC] 문서 동기화 시작: ${documents.length}개`);
      for (const doc of documents) {
        try {
          const result = await upsertDocument(tx, doc);
          stats.documents.total++;
          if (result.inserted) stats.documents.inserted++;
          if (result.updated) stats.documents.updated++;
        } catch (error) {
          errors.push(
            `문서 ${doc.DOC_NO} 동기화 실패: ${error instanceof Error ? error.message : String(error)}`
          );
        }
      }

      // 2. 리비전별로 파일 그룹핑
      const revisionMap = new Map<string, SwpFileApiResponse[]>();
      for (const file of files) {
        const key = `${file.OWN_DOC_NO}|${file.REV_NO}`;
        if (!revisionMap.has(key)) {
          revisionMap.set(key, []);
        }
        revisionMap.get(key)!.push(file);
      }

      // 3. 리비전 및 파일 동기화
      console.log(`[SYNC] 리비전 동기화 시작: ${revisionMap.size}개`);
      for (const [key, revFiles] of revisionMap) {
        const [docNo, revNo] = key.split("|");
        const firstFile = revFiles[0];

        try {
          // 리비전 생성/업데이트
          const revisionResult = await upsertRevision(tx, docNo, firstFile);
          stats.revisions.total++;
          if (revisionResult.inserted) stats.revisions.inserted++;
          if (revisionResult.updated) stats.revisions.updated++;

          const revisionId = revisionResult.id;

          // 파일들 생성/업데이트
          for (const file of revFiles) {
            try {
              const fileResult = await upsertFile(tx, revisionId, docNo, file);
              stats.files.total++;
              if (fileResult.inserted) stats.files.inserted++;
              if (fileResult.updated) stats.files.updated++;
            } catch (error) {
              errors.push(
                `파일 ${file.FILE_NM} 동기화 실패: ${error instanceof Error ? error.message : String(error)}`
              );
            }
          }
        } catch (error) {
          errors.push(
            `리비전 ${docNo}-${revNo} 동기화 실패: ${error instanceof Error ? error.message : String(error)}`
          );
        }
      }

      console.log(
        `[SYNC] 동기화 완료: 문서 ${stats.documents.total}, 리비전 ${stats.revisions.total}, 파일 ${stats.files.total}`
      );
    });

    return {
      success: errors.length === 0,
      projectNo,
      stats,
      errors,
      duration: Date.now() - startTime,
    };
  } catch (error) {
    return {
      success: false,
      projectNo,
      stats,
      errors: [
        ...errors,
        `트랜잭션 실패: ${error instanceof Error ? error.message : String(error)}`,
      ],
      duration: Date.now() - startTime,
    };
  }
}

// ============================================================================
// Upsert 헬퍼 함수들
// ============================================================================

async function upsertDocument(
  tx: any,
  doc: SwpDocumentApiResponse
): Promise<{ id: string; inserted: boolean; updated: boolean }> {
  const data: SwpDocumentInsert = {
    DOC_NO: doc.DOC_NO,
    DOC_TITLE: doc.DOC_TITLE,
    DOC_GB: doc.DOC_GB || null,
    DOC_TYPE: doc.DOC_TYPE || null,
    OWN_DOC_NO: doc.OWN_DOC_NO,
    SHI_DOC_NO: doc.SHI_DOC_NO,
    PROJ_NO: doc.PROJ_NO,
    PROJ_NM: doc.PROJ_NM,
    PKG_NO: doc.PKG_NO || null,
    MAT_CD: doc.MAT_CD || null,
    MAT_NM: doc.MAT_NM || null,
    DISPLN: doc.DISPLN || null,
    CTGRY: doc.CTGRY || null,
    VNDR_CD: doc.VNDR_CD || null,
    CPY_CD: doc.CPY_CD,
    CPY_NM: doc.CPY_NM,
    PIC_NM: doc.PIC_NM,
    PIC_DEPTCD: doc.PIC_DEPTCD || null,
    PIC_DEPTNM: doc.PIC_DEPTNM,
    LTST_REV_NO: doc.LTST_REV_NO || null,
    LTST_REV_SEQ: doc.LTST_REV_SEQ || null,
    LTST_ACTV_STAT: doc.LTST_ACTV_STAT || null,
    STAGE: doc.STAGE || null,
    SKL_CD: doc.SKL_CD,
    MOD_TYPE: doc.MOD_TYPE || null,
    ACT_TYPE_NM: doc.ACT_TYPE_NM || null,
    USE_YN: doc.USE_YN || null,
    CRTER: doc.CRTER,
    CRTE_DTM: doc.CRTE_DTM,
    CHGR: doc.CHGR,
    CHG_DTM: doc.CHG_DTM,
    REV_DTM: doc.REV_DTM || null,
    sync_status: "synced",
    last_synced_at: new Date(),
    updated_at: new Date(),
  };

  // 기존 문서 확인
  const existing = await tx
    .select()
    .from(swpDocuments)
    .where(eq(swpDocuments.DOC_NO, doc.DOC_NO))
    .limit(1);

  if (existing.length > 0) {
    // 업데이트
    await tx
      .update(swpDocuments)
      .set(data)
      .where(eq(swpDocuments.DOC_NO, doc.DOC_NO));
    return { id: doc.DOC_NO, inserted: false, updated: true };
  } else {
    // 삽입
    await tx.insert(swpDocuments).values(data);
    return { id: doc.DOC_NO, inserted: true, updated: false };
  }
}

async function upsertRevision(
  tx: any,
  docNo: string,
  file: SwpFileApiResponse
): Promise<{ id: number; inserted: boolean; updated: boolean }> {
  const data: Omit<SwpDocumentRevisionInsert, "id"> = {
    DOC_NO: docNo,
    REV_NO: file.REV_NO,
    STAGE: file.STAGE,
    ACTV_NO: file.ACTV_NO || null,
    ACTV_SEQ: file.ACTV_SEQ,
    BOX_SEQ: file.BOX_SEQ,
    OFDC_NO: file.OFDC_NO,
    PROJ_NO: file.PROJ_NO,
    PKG_NO: file.PKG_NO || null,
    VNDR_CD: file.VNDR_CD || null,
    CPY_CD: file.CPY_CD,
    sync_status: "synced",
    last_synced_at: new Date(),
    updated_at: new Date(),
  };

  // 기존 리비전 확인
  const existing = await tx
    .select()
    .from(swpDocumentRevisions)
    .where(
      and(
        eq(swpDocumentRevisions.DOC_NO, docNo),
        eq(swpDocumentRevisions.REV_NO, file.REV_NO)
      )
    )
    .limit(1);

  if (existing.length > 0) {
    // 업데이트
    await tx
      .update(swpDocumentRevisions)
      .set(data)
      .where(eq(swpDocumentRevisions.id, existing[0].id));
    return { id: existing[0].id, inserted: false, updated: true };
  } else {
    // 삽입
    const result = await tx
      .insert(swpDocumentRevisions)
      .values(data)
      .returning({ id: swpDocumentRevisions.id });
    return { id: result[0].id, inserted: true, updated: false };
  }
}

async function upsertFile(
  tx: any,
  revisionId: number,
  docNo: string,
  file: SwpFileApiResponse
): Promise<{ id: number; inserted: boolean; updated: boolean }> {
  const data: Omit<SwpDocumentFileInsert, "id"> = {
    revision_id: revisionId,
    DOC_NO: docNo,
    FILE_NM: file.FILE_NM,
    FILE_SEQ: file.FILE_SEQ,
    FILE_SZ: file.FILE_SZ,
    FLD_PATH: file.FLD_PATH,
    STAT: file.STAT,
    STAT_NM: file.STAT_NM,
    IDX: file.IDX,
    ACTV_NO: file.ACTV_NO || null,
    CRTER: file.CRTER,
    CRTE_DTM: file.CRTE_DTM,
    CHGR: file.CHGR,
    CHG_DTM: file.CHG_DTM,
    sync_status: "synced",
    last_synced_at: new Date(),
    updated_at: new Date(),
  };

  // 기존 파일 확인 (revision + fileSeq로 unique)
  const existing = await tx
    .select()
    .from(swpDocumentFiles)
    .where(
      and(
        eq(swpDocumentFiles.revision_id, revisionId),
        eq(swpDocumentFiles.FILE_SEQ, file.FILE_SEQ)
      )
    )
    .limit(1);

  if (existing.length > 0) {
    // 업데이트
    await tx
      .update(swpDocumentFiles)
      .set(data)
      .where(eq(swpDocumentFiles.id, existing[0].id));
    return { id: existing[0].id, inserted: false, updated: true };
  } else {
    // 삽입
    const result = await tx
      .insert(swpDocumentFiles)
      .values(data)
      .returning({ id: swpDocumentFiles.id });
    return { id: result[0].id, inserted: true, updated: false };
  }
}

// ============================================================================
// 조회 헬퍼 함수들
// ============================================================================

/**
 * 프로젝트의 문서 계층 구조 조회 (복잡한 JSON 집계는 SQL 직접 실행)
 */
export async function getProjectDocumentsHierarchy(projectNo: string) {
  return db.execute(sql`
    SELECT 
      d."DOC_NO",
      d."DOC_TITLE",
      d."PROJ_NO",
      d."PROJ_NM",
      d."PKG_NO",
      d."VNDR_CD",
      d."CPY_NM",
      d."MAT_NM",
      d."LTST_REV_NO",
      d."LTST_ACTV_STAT",
      d.sync_status,
      d.last_synced_at,
      
      COALESCE(
        json_agg(
          json_build_object(
            'id', r.id,
            'revNo', r."REV_NO",
            'stage', r."STAGE",
            'actvNo', r."ACTV_NO",
            'ofdcNo', r."OFDC_NO",
            'syncStatus', r.sync_status,
            'fileCount', (
              SELECT COUNT(*)::int
              FROM swp.swp_document_files f2
              WHERE f2.revision_id = r.id
            ),
            'files', (
              SELECT COALESCE(json_agg(
                json_build_object(
                  'id', f.id,
                  'fileNm', f."FILE_NM",
                  'fileSeq', f."FILE_SEQ",
                  'fileSz', f."FILE_SZ",
                  'fldPath', f."FLD_PATH",
                  'stat', f."STAT",
                  'statNm', f."STAT_NM",
                  'syncStatus', f.sync_status,
                  'createdAt', f.created_at
                )
                ORDER BY f."FILE_SEQ"
              ), '[]'::json)
              FROM swp.swp_document_files f
              WHERE f.revision_id = r.id
            )
          )
          ORDER BY r."REV_NO" DESC
        ) FILTER (WHERE r.id IS NOT NULL),
        '[]'::json
      ) as revisions,
      
      COUNT(DISTINCT r.id)::int as revision_count,
      COUNT(f.id)::int as total_file_count
      
    FROM swp.swp_documents d
    LEFT JOIN swp.swp_document_revisions r ON d."DOC_NO" = r."DOC_NO"
    LEFT JOIN swp.swp_document_files f ON r.id = f.revision_id
    WHERE d."PROJ_NO" = ${projectNo}
    GROUP BY 
      d."DOC_NO",
      d."DOC_TITLE",
      d."PROJ_NO",
      d."PROJ_NM",
      d."PKG_NO",
      d."VNDR_CD",
      d."CPY_NM",
      d."MAT_NM",
      d."LTST_REV_NO",
      d."LTST_ACTV_STAT",
      d.sync_status,
      d.last_synced_at
    ORDER BY d."DOC_NO"
  `);
}

/**
 * 특정 문서의 모든 리비전 조회
 */
export async function getDocumentRevisions(docNo: string) {
  return db
    .select()
    .from(swpDocumentRevisions)
    .where(eq(swpDocumentRevisions.DOC_NO, docNo))
    .orderBy(sql`${swpDocumentRevisions.REV_NO} DESC`);
}

/**
 * 특정 리비전의 모든 파일 조회
 */
export async function getRevisionFiles(revisionId: number) {
  return db
    .select()
    .from(swpDocumentFiles)
    .where(eq(swpDocumentFiles.revision_id, revisionId))
    .orderBy(swpDocumentFiles.FILE_SEQ);
}

/**
 * 프로젝트 동기화 상태 조회
 */
export async function getProjectSyncStatus(projectNo: string) {
  return db.execute(sql`
    SELECT 
      d."PROJ_NO",
      d."PROJ_NM",
      
      COUNT(DISTINCT d."DOC_NO")::int as total_documents,
      COUNT(DISTINCT r.id)::int as total_revisions,
      COUNT(f.id)::int as total_files,
      
      COUNT(DISTINCT d."DOC_NO") FILTER (WHERE d.sync_status = 'synced')::int as docs_synced,
      COUNT(DISTINCT d."DOC_NO") FILTER (WHERE d.sync_status = 'pending')::int as docs_pending,
      COUNT(DISTINCT d."DOC_NO") FILTER (WHERE d.sync_status = 'error')::int as docs_error,
      
      COUNT(DISTINCT r.id) FILTER (WHERE r.sync_status = 'synced')::int as revs_synced,
      COUNT(f.id) FILTER (WHERE f.sync_status = 'synced')::int as files_synced,
      
      MAX(d.last_synced_at) as last_sync_time
      
    FROM swp.swp_documents d
    LEFT JOIN swp.swp_document_revisions r ON d."DOC_NO" = r."DOC_NO"
    LEFT JOIN swp.swp_document_files f ON r.id = f.revision_id
    WHERE d."PROJ_NO" = ${projectNo}
    GROUP BY d."PROJ_NO", d."PROJ_NM"
  `);
}