"use server";

import db from "@/db/db";
import { swpDocuments, swpDocumentRevisions, swpDocumentFiles } from "@/db/schema/SWP/swp-documents";
import { eq, and, sql, like, desc, asc, type SQL } from "drizzle-orm";
import { fetchSwpProjectData } from "./api-client";
import { syncSwpProject } from "./sync-service";

// ============================================================================
// 타입 정의
// ============================================================================

export interface SwpTableFilters {
  projNo?: string;
  docNo?: string;
  docTitle?: string;
  pkgNo?: string;
  vndrCd?: string;
  stage?: string;
}

export interface SwpTableParams {
  page: number;
  pageSize: number;
  sortBy?: string;
  sortOrder?: "asc" | "desc";
  filters?: SwpTableFilters;
}

export interface SwpDocumentWithStats {
  DOC_NO: string;
  DOC_TITLE: string;
  PROJ_NO: string;
  PROJ_NM: string | null;
  PKG_NO: string | null;
  VNDR_CD: string | null;
  CPY_NM: string | null;
  LTST_REV_NO: string | null;
  STAGE: string | null;
  sync_status: "synced" | "pending" | "error";
  last_synced_at: Date;
  revision_count: number;
  file_count: number;
}

// ============================================================================
// 서버 액션: 문서 목록 조회 (페이지네이션 + 검색)
// ============================================================================

export async function fetchSwpDocuments(params: SwpTableParams) {
  const { page, pageSize, sortBy = "last_synced_at", sortOrder = "desc", filters } = params;
  const offset = (page - 1) * pageSize;

  try {
    // WHERE 조건 구성
    const conditions: SQL<unknown>[] = [];
    
    if (filters?.projNo) {
      conditions.push(like(swpDocuments.PROJ_NO, `%${filters.projNo}%`));
    }
    if (filters?.docNo) {
      conditions.push(like(swpDocuments.DOC_NO, `%${filters.docNo}%`));
    }
    if (filters?.docTitle) {
      conditions.push(like(swpDocuments.DOC_TITLE, `%${filters.docTitle}%`));
    }
    if (filters?.pkgNo) {
      conditions.push(like(swpDocuments.PKG_NO, `%${filters.pkgNo}%`));
    }
    if (filters?.vndrCd) {
      conditions.push(like(swpDocuments.VNDR_CD, `%${filters.vndrCd}%`));
    }
    if (filters?.stage) {
      conditions.push(eq(swpDocuments.STAGE, filters.stage));
    }

    const whereClause = conditions.length > 0 ? and(...conditions) : undefined;

    // 총 개수 조회
    const totalResult = await db
      .select({ count: sql<number>`count(*)::int` })
      .from(swpDocuments)
      .where(whereClause);
    
    const total = totalResult[0]?.count || 0;

    // 정렬 컬럼 결정
    const orderByColumn = 
      sortBy === "DOC_NO" ? swpDocuments.DOC_NO :
      sortBy === "DOC_TITLE" ? swpDocuments.DOC_TITLE :
      sortBy === "PROJ_NO" ? swpDocuments.PROJ_NO :
      sortBy === "PKG_NO" ? swpDocuments.PKG_NO :
      sortBy === "STAGE" ? swpDocuments.STAGE :
      swpDocuments.last_synced_at;

    // 데이터 조회 (Drizzle query builder 사용)
    const documents = await db
      .select({
        DOC_NO: swpDocuments.DOC_NO,
        DOC_TITLE: swpDocuments.DOC_TITLE,
        PROJ_NO: swpDocuments.PROJ_NO,
        PROJ_NM: swpDocuments.PROJ_NM,
        PKG_NO: swpDocuments.PKG_NO,
        VNDR_CD: swpDocuments.VNDR_CD,
        CPY_NM: swpDocuments.CPY_NM,
        LTST_REV_NO: swpDocuments.LTST_REV_NO,
        STAGE: swpDocuments.STAGE,
        sync_status: swpDocuments.sync_status,
        last_synced_at: swpDocuments.last_synced_at,
        revision_count: sql<number>`COUNT(DISTINCT ${swpDocumentRevisions.id})::int`,
        file_count: sql<number>`COUNT(${swpDocumentFiles.id})::int`,
      })
      .from(swpDocuments)
      .leftJoin(swpDocumentRevisions, eq(swpDocuments.DOC_NO, swpDocumentRevisions.DOC_NO))
      .leftJoin(swpDocumentFiles, eq(swpDocumentRevisions.id, swpDocumentFiles.revision_id))
      .where(whereClause)
      .groupBy(
        swpDocuments.DOC_NO,
        swpDocuments.DOC_TITLE,
        swpDocuments.PROJ_NO,
        swpDocuments.PROJ_NM,
        swpDocuments.PKG_NO,
        swpDocuments.VNDR_CD,
        swpDocuments.CPY_NM,
        swpDocuments.LTST_REV_NO,
        swpDocuments.STAGE,
        swpDocuments.sync_status,
        swpDocuments.last_synced_at
      )
      .orderBy(sortOrder === "desc" ? desc(orderByColumn) : asc(orderByColumn))
      .limit(pageSize)
      .offset(offset);

    return {
      data: documents,
      total,
      page,
      pageSize,
      totalPages: Math.ceil(total / pageSize),
    };
  } catch (error) {
    console.error("[fetchSwpDocuments] 오류:", error);
    throw new Error("문서 목록 조회 실패 [SWP API에서 실패가 발생했습니다. 담당자에게 문의하세요]");
  }
}

// ============================================================================
// 서버 액션: 문서의 리비전 목록 조회
// ============================================================================

export async function fetchDocumentRevisions(docNo: string) {
  try {
    const revisions = await db
      .select({
        id: swpDocumentRevisions.id,
        DOC_NO: swpDocumentRevisions.DOC_NO,
        REV_NO: swpDocumentRevisions.REV_NO,
        STAGE: swpDocumentRevisions.STAGE,
        ACTV_NO: swpDocumentRevisions.ACTV_NO,
        OFDC_NO: swpDocumentRevisions.OFDC_NO,
        sync_status: swpDocumentRevisions.sync_status,
        last_synced_at: swpDocumentRevisions.last_synced_at,
        file_count: sql<number>`(
          SELECT COUNT(*)::int 
          FROM swp.swp_document_files f 
          WHERE f.revision_id = ${swpDocumentRevisions.id}
        )`,
      })
      .from(swpDocumentRevisions)
      .where(eq(swpDocumentRevisions.DOC_NO, docNo))
      .orderBy(desc(swpDocumentRevisions.REV_NO));

    return revisions;
  } catch (error) {
    console.error("[fetchDocumentRevisions] 오류:", error);
    throw new Error("리비전 목록 조회 실패");
  }
}

// ============================================================================
// 서버 액션: 리비전의 파일 목록 조회
// ============================================================================

export async function fetchRevisionFiles(revisionId: number) {
  try {
    const files = await db
      .select({
        id: swpDocumentFiles.id,
        FILE_NM: swpDocumentFiles.FILE_NM,
        FILE_SEQ: swpDocumentFiles.FILE_SEQ,
        FILE_SZ: swpDocumentFiles.FILE_SZ,
        FLD_PATH: swpDocumentFiles.FLD_PATH,
        STAT: swpDocumentFiles.STAT,
        STAT_NM: swpDocumentFiles.STAT_NM,
        sync_status: swpDocumentFiles.sync_status,
        created_at: swpDocumentFiles.created_at,
      })
      .from(swpDocumentFiles)
      .where(eq(swpDocumentFiles.revision_id, revisionId))
      .orderBy(asc(swpDocumentFiles.FILE_SEQ));

    return files;
  } catch (error) {
    console.error("[fetchRevisionFiles] 오류:", error);
    throw new Error("파일 목록 조회 실패");
  }
}

// ============================================================================
// 서버 액션: 프로젝트 동기화
// ============================================================================

export async function syncSwpProjectAction(projectNo: string, docGb: "M" | "V" = "V") {
  try {
    console.log(`[syncSwpProjectAction] 시작: ${projectNo}`);
    
    // 1. API에서 데이터 조회
    const { documents, files } = await fetchSwpProjectData(projectNo, docGb);
    
    // 2. 동기화 실행
    const result = await syncSwpProject(projectNo, documents, files);
    
    console.log(`[syncSwpProjectAction] 완료:`, result.stats);
    
    return result;
  } catch (error) {
    console.error("[syncSwpProjectAction] 오류:", error);
    throw new Error(
      error instanceof Error ? error.message : "동기화 실패"
    );
  }
}

// ============================================================================
// 서버 액션: 프로젝트 목록 조회 (필터용)
// ============================================================================

export async function fetchProjectList() {
  try {
    const projects = await db
      .select({
        PROJ_NO: swpDocuments.PROJ_NO,
        PROJ_NM: swpDocuments.PROJ_NM,
        doc_count: sql<number>`COUNT(DISTINCT ${swpDocuments.DOC_NO})::int`,
      })
      .from(swpDocuments)
      .groupBy(swpDocuments.PROJ_NO, swpDocuments.PROJ_NM)
      .orderBy(desc(sql`COUNT(DISTINCT ${swpDocuments.DOC_NO})`));

    return projects;
  } catch (error) {
    console.error("[fetchProjectList] 오류:", error);
    return [];
  }
}

// ============================================================================
// 서버 액션: 통계 조회
// ============================================================================

export async function fetchSwpStats(projNo?: string) {
  try {
    const whereClause = projNo ? eq(swpDocuments.PROJ_NO, projNo) : undefined;

    const stats = await db
      .select({
        total_documents: sql<number>`COUNT(DISTINCT ${swpDocuments.DOC_NO})::int`,
        total_revisions: sql<number>`COUNT(DISTINCT ${swpDocumentRevisions.id})::int`,
        total_files: sql<number>`COUNT(${swpDocumentFiles.id})::int`,
        last_sync: sql<Date>`MAX(${swpDocuments.last_synced_at})`,
      })
      .from(swpDocuments)
      .leftJoin(swpDocumentRevisions, eq(swpDocuments.DOC_NO, swpDocumentRevisions.DOC_NO))
      .leftJoin(swpDocumentFiles, eq(swpDocumentRevisions.id, swpDocumentFiles.revision_id))
      .where(whereClause);

    return stats[0] || {
      total_documents: 0,
      total_revisions: 0,
      total_files: 0,
      last_sync: null,
    };
  } catch (error) {
    console.error("[fetchSwpStats] 오류:", error);
    return {
      total_documents: 0,
      total_revisions: 0,
      total_files: 0,
      last_sync: null,
    };
  }
}