"use server"

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

// 선종 타입 정의
export interface ShipTypeItem {
  CD: string // 선종코드
  CDNM: string // 선종명
  displayText: string // 표시용 텍스트 (CD + " - " + CDNM)
}

// 선종 검색 옵션
export interface ShipTypeSearchOptions {
  searchTerm?: string
  limit?: number
}

/**
 * 선종 목록 조회
 * cmctbCdnm 테이블에서 CD_CLF = 'PSA330' AND DEL_YN = 'N' 조건으로 조회
 */
export async function getShipTypes(options: ShipTypeSearchOptions = {}): Promise<{
  success: boolean
  data: ShipTypeItem[]
  error?: string
}> {
  try {
    const { searchTerm, limit = 100 } = options

    // WHERE 조건 구성
    let whereClause = and(
      eq(cmctbCdnm.CD_CLF, 'PSA330'),
      eq(cmctbCdnm.DEL_YN, 'N')
    )

    // 검색어가 있는 경우 추가 조건
    if (searchTerm && searchTerm.trim()) {
      const term = `%${searchTerm.trim().toUpperCase()}%`
      whereClause = and(
        whereClause,
        or(
          ilike(cmctbCdnm.CD, term),
          ilike(cmctbCdnm.CDNM, term)
        )
      )
    }

    const result = await db
      .select({
        CD: cmctbCdnm.CD,
        CDNM: cmctbCdnm.CDNM
      })
      .from(cmctbCdnm)
      .where(whereClause)
      .orderBy(asc(cmctbCdnm.CD))
      .limit(limit > 0 ? limit : 100)

    // null 값 처리 및 displayText 추가
    const cleanedResult = result
      .filter(item => item.CD && item.CDNM)
      .map(item => ({
        CD: item.CD!,
        CDNM: item.CDNM!,
        displayText: `${item.CD} - ${item.CDNM}`
      }))

    return {
      success: true,
      data: cleanedResult
    }
  } catch (error) {
    console.error('Error fetching ship types:', error)
    return {
      success: false,
      data: [],
      error: '선종을 조회하는 중 오류가 발생했습니다.'
    }
  }
}

/**
 * 특정 선종 조회 (코드로)
 */
export async function getShipTypeByCode(code: string): Promise<ShipTypeItem | null> {
  if (!code.trim()) {
    return null
  }

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

    if (result.length > 0 && result[0].CD && result[0].CDNM) {
      return {
        CD: result[0].CD,
        CDNM: result[0].CDNM,
        displayText: `${result[0].CD} - ${result[0].CDNM}`
      }
    }

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