"use server";

import { getServerSession } from "next-auth";
import { authOptions } from "@/app/api/auth/[...nextauth]/route";
import db from "@/db/db";
import { vendors } from "@/db/schema/vendors";
import { contracts } from "@/db/schema/contract";
import { projects } from "@/db/schema/projects";
import { swpDocumentFiles, swpDocumentRevisions } from "@/db/schema/SWP/swp-documents";
import { eq, and, sql } from "drizzle-orm";
import { fetchSwpDocuments, type SwpTableParams } from "./actions";

// ============================================================================
// 벤더 세션 정보 조회
// ============================================================================

interface VendorSessionInfo {
  vendorId: number;
  vendorCode: string;
  vendorName: string;
  companyId: number;
}

export async function getVendorSessionInfo(): Promise<VendorSessionInfo | null> {
  const session = await getServerSession(authOptions);
  
  if (!session?.user?.companyId) {
    return null;
  }

  const companyId = typeof session.user.companyId === 'string' 
    ? parseInt(session.user.companyId, 10)
    : session.user.companyId as number;

  // vendors 테이블에서 companyId로 벤더 정보 조회
  const vendor = await db
    .select({
      id: vendors.id,
      vendorCode: vendors.vendorCode,
      vendorName: vendors.vendorName,
    })
    .from(vendors)
    .where(eq(vendors.id, companyId))
    .limit(1);

  if (!vendor[0] || !vendor[0].vendorCode) {
    return null;
  }

  return {
    vendorId: vendor[0].id,
    vendorCode: vendor[0].vendorCode,
    vendorName: vendor[0].vendorName,
    companyId,
  };
}

// ============================================================================
// 벤더의 프로젝트 목록 조회
// ============================================================================

export async function fetchVendorProjects() {
  try {
    const vendorInfo = await getVendorSessionInfo();
    
    if (!vendorInfo) {
      throw new Error("벤더 정보를 찾을 수 없습니다.");
    }

    // contracts 테이블에서 해당 벤더의 계약들의 프로젝트 조회
    const vendorProjects = await db
      .selectDistinct({
        PROJ_NO: projects.code,
        PROJ_NM: projects.name,
        contract_count: sql<number>`COUNT(DISTINCT ${contracts.id})::int`,
      })
      .from(contracts)
      .innerJoin(projects, eq(contracts.projectId, projects.id))
      .where(eq(contracts.vendorId, vendorInfo.vendorId))
      .groupBy(projects.id, projects.code, projects.name)
      .orderBy(sql`COUNT(DISTINCT ${contracts.id}) DESC`);

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

// ============================================================================
// 벤더 필터링된 문서 목록 조회
// ============================================================================

export async function fetchVendorDocuments(params: SwpTableParams) {
  try {
    const vendorInfo = await getVendorSessionInfo();
    
    if (!vendorInfo) {
      throw new Error("벤더 정보를 찾을 수 없습니다.");
    }

    // 벤더 코드를 필터에 자동 추가
    const vendorParams: SwpTableParams = {
      ...params,
      filters: {
        ...params.filters,
        vndrCd: vendorInfo.vendorCode, // 벤더 코드 필터 강제 적용
      },
    };

    // 기존 fetchSwpDocuments 재사용
    return await fetchSwpDocuments(vendorParams);
  } catch (error) {
    console.error("[fetchVendorDocuments] 오류:", error);
    throw new Error("문서 목록 조회 실패");
  }
}

// ============================================================================
// 파일 업로드
// ============================================================================

export interface FileUploadParams {
  revisionId: number;
  file: {
    FILE_NM: string;
    FILE_SEQ: string;
    FILE_SZ: string;
    FLD_PATH: string;
    STAT?: string;
    STAT_NM?: string;
  };
}

export async function uploadFileToRevision(params: FileUploadParams) {
  try {
    const vendorInfo = await getVendorSessionInfo();
    
    if (!vendorInfo) {
      throw new Error("벤더 정보를 찾을 수 없습니다.");
    }

    const { revisionId, file } = params;

    // 1. 해당 리비전이 벤더에게 제공된 문서인지 확인
    const revisionCheck = await db
      .select({
        DOC_NO: swpDocumentRevisions.DOC_NO,
        VNDR_CD: sql<string>`(
          SELECT d."VNDR_CD" 
          FROM swp.swp_documents d 
          WHERE d."DOC_NO" = ${swpDocumentRevisions.DOC_NO}
        )`,
      })
      .from(swpDocumentRevisions)
      .where(eq(swpDocumentRevisions.id, revisionId))
      .limit(1);

    if (!revisionCheck[0]) {
      throw new Error("리비전을 찾을 수 없습니다.");
    }

    // 벤더 코드가 일치하는지 확인
    if (revisionCheck[0].VNDR_CD !== vendorInfo.vendorCode) {
      throw new Error("이 문서에 대한 권한이 없습니다.");
    }

    // 2. 파일 정보 저장 (upsert)
    const existingFile = await db
      .select({ id: swpDocumentFiles.id })
      .from(swpDocumentFiles)
      .where(
        and(
          eq(swpDocumentFiles.revision_id, revisionId),
          eq(swpDocumentFiles.FILE_SEQ, file.FILE_SEQ)
        )
      )
      .limit(1);

    if (existingFile[0]) {
      // 업데이트
      await db.execute(sql`
        UPDATE swp.swp_document_files
        SET 
          "FILE_NM" = ${file.FILE_NM},
          "FILE_SZ" = ${file.FILE_SZ},
          "FLD_PATH" = ${file.FLD_PATH},
          "STAT" = ${file.STAT || null},
          "STAT_NM" = ${file.STAT_NM || null},
          sync_status = 'synced',
          updated_at = NOW()
        WHERE id = ${existingFile[0].id}
      `);

      return { success: true, fileId: existingFile[0].id, action: "updated" };
    } else {
      // 삽입
      const result = await db.execute<{ id: number }>(sql`
        INSERT INTO swp.swp_document_files 
          (revision_id, "FILE_NM", "FILE_SEQ", "FILE_SZ", "FLD_PATH", "STAT", "STAT_NM", sync_status)
        VALUES 
          (${revisionId}, ${file.FILE_NM}, ${file.FILE_SEQ}, ${file.FILE_SZ}, ${file.FLD_PATH}, ${file.STAT || null}, ${file.STAT_NM || null}, 'synced')
        RETURNING id
      `);

      return { success: true, fileId: result.rows[0].id, action: "created" };
    }
  } catch (error) {
    console.error("[uploadFileToRevision] 오류:", error);
    throw new Error(
      error instanceof Error ? error.message : "파일 업로드 실패"
    );
  }
}

// ============================================================================
// 벤더 통계 조회
// ============================================================================

export async function fetchVendorSwpStats(projNo?: string) {
  try {
    const vendorInfo = await getVendorSessionInfo();
    
    if (!vendorInfo) {
      throw new Error("벤더 정보를 찾을 수 없습니다.");
    }

    const whereConditions = [
      sql`d."VNDR_CD" = ${vendorInfo.vendorCode}`,
    ];

    if (projNo) {
      whereConditions.push(sql`d."PROJ_NO" = ${projNo}`);
    }

    const stats = await db.execute<{
      total_documents: number;
      total_revisions: number;
      total_files: number;
      uploaded_files: number;
      last_sync: Date | null;
    }>(sql`
      SELECT 
        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(CASE WHEN f."FLD_PATH" IS NOT NULL AND f."FLD_PATH" != '' THEN 1 END)::int as uploaded_files,
        MAX(d.last_synced_at) as last_sync
      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 ${sql.join(whereConditions, sql` AND `)}
    `);

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