"use server"

import db from '@/db/db'
import { cmctbCdnm } from '@/db/schema/NONSAP/nonsap'
import { eq, or, ilike, and } from 'drizzle-orm'

// 국가코드 타입 정의
export interface NationCode {
  CD: string // 2글자 국가코드
  CD2: string // 3글자 국가코드
  CD3: string // 3글자 숫자 국가코드
  CDNM: string // 한국어 국가명
  GRP_DSC: string // 영문 국가명
}

// 국가코드 검색 옵션
export interface NationSearchOptions {
  searchTerm?: string
  limit?: number
}

/**
 * 국가코드 목록 조회
 * 내부 PostgreSQL DB의 cmctbCdnm 테이블에서 CD_CLF = 'LE0010' 조건으로 조회
 */
export async function getNationCodes(options: NationSearchOptions = {}): Promise<{
  success: boolean
  data: NationCode[]
  error?: string
}> {
  try {
    const { searchTerm, limit = 100 } = options

    // 검색어가 있는 경우와 없는 경우를 분리해서 처리
    let result;
    
    if (searchTerm && searchTerm.trim()) {
      const term = `%${searchTerm.trim().toUpperCase()}%`
      result = await db
        .select({
          CD: cmctbCdnm.CD,
          CD2: cmctbCdnm.CD2,
          CD3: cmctbCdnm.CD3,
          CDNM: cmctbCdnm.CDNM,
          GRP_DSC: cmctbCdnm.GRP_DSC
        })
        .from(cmctbCdnm)
        .where(
          and(
            eq(cmctbCdnm.CD_CLF, 'LE0010'),
            or(
              ilike(cmctbCdnm.CD, term),
              ilike(cmctbCdnm.CD2, term),
              ilike(cmctbCdnm.CD3, term),
              ilike(cmctbCdnm.CDNM, `%${searchTerm.trim()}%`),
              ilike(cmctbCdnm.GRP_DSC, term)
            )
          )
        )
        .orderBy(cmctbCdnm.CD)
        .limit(limit > 0 ? limit : 100)
    } else {
      result = await db
        .select({
          CD: cmctbCdnm.CD,
          CD2: cmctbCdnm.CD2,
          CD3: cmctbCdnm.CD3,
          CDNM: cmctbCdnm.CDNM,
          GRP_DSC: cmctbCdnm.GRP_DSC
        })
        .from(cmctbCdnm)
        .where(eq(cmctbCdnm.CD_CLF, 'LE0010'))
        .orderBy(cmctbCdnm.CD)
        .limit(limit > 0 ? limit : 100)
    }

    // null 값 처리
    const cleanedResult = result
      .filter(item => item.CD && item.CD2 && item.CD3 && item.CDNM && item.GRP_DSC)
      .map(item => ({
        CD: item.CD!,
        CD2: item.CD2!,
        CD3: item.CD3!,
        CDNM: item.CDNM!,
        GRP_DSC: item.GRP_DSC!
      }))

    return {
      success: true,
      data: cleanedResult
    }
  } catch (error) {
    console.error('Error fetching nation codes:', error)
    return {
      success: false,
      data: [],
      error: '국가코드를 조회하는 중 오류가 발생했습니다.'
    }
  }
}

/**
 * 특정 국가코드 조회 (2글자 코드 기준)
 * 내부 PostgreSQL DB의 cmctbCdnm 테이블에서 조회
 */
export async function getNationCodeByCode(code: string): Promise<NationCode | null> {
  if (!code.trim()) {
    return null
  }

  try {
    const result = await db
      .select({
        CD: cmctbCdnm.CD,
        CD2: cmctbCdnm.CD2,
        CD3: cmctbCdnm.CD3,
        CDNM: cmctbCdnm.CDNM,
        GRP_DSC: cmctbCdnm.GRP_DSC
      })
      .from(cmctbCdnm)
      .where(
        and(
          eq(cmctbCdnm.CD_CLF, 'LE0010'),
          eq(cmctbCdnm.CD, code.trim().toUpperCase())
        )
      )
      .limit(1)

    if (result.length > 0 && result[0].CD && result[0].CD2 && result[0].CD3 && result[0].CDNM && result[0].GRP_DSC) {
      return {
        CD: result[0].CD,
        CD2: result[0].CD2,
        CD3: result[0].CD3,
        CDNM: result[0].CDNM,
        GRP_DSC: result[0].GRP_DSC
      }
    }

    return null
  } catch (error) {
    console.error('Error fetching nation code by code:', error)
    return null
  }
}

/**
 * 여러 국가코드 조회 (2글자 코드 기준)
 */
export async function getNationCodesByCodes(codes: string[]): Promise<NationCode[]> {
  if (!codes || codes.length === 0) {
    return []
  }

  try {
    const cleanedCodes = codes
      .map(code => code.trim().toUpperCase())
      .filter(code => code.length > 0)

    if (cleanedCodes.length === 0) {
      return []
    }

    const result = await db
      .select({
        CD: cmctbCdnm.CD,
        CD2: cmctbCdnm.CD2,
        CD3: cmctbCdnm.CD3,
        CDNM: cmctbCdnm.CDNM,
        GRP_DSC: cmctbCdnm.GRP_DSC
      })
      .from(cmctbCdnm)
      .where(
        and(
          eq(cmctbCdnm.CD_CLF, 'LE0010'),
          or(...cleanedCodes.map(code => eq(cmctbCdnm.CD, code)))
        )
      )
      .orderBy(cmctbCdnm.CD)

    return result
      .filter(item => item.CD && item.CD2 && item.CD3 && item.CDNM && item.GRP_DSC)
      .map(item => ({
        CD: item.CD!,
        CD2: item.CD2!,
        CD3: item.CD3!,
        CDNM: item.CDNM!,
        GRP_DSC: item.GRP_DSC!
      }))
  } catch (error) {
    console.error('Error fetching nation codes by codes:', error)
    return []
  }
}