"use server"

import db from "@/db/db"
import { projects, biddingProjects } from "@/db/schema"
import { eq, ilike, or } from "drizzle-orm"

// 통합 프로젝트 타입 정의
export interface UnifiedProject {
  id: string // projects의 경우 id를 string으로, biddingProjects의 경우 pspid
  code: string
  name: string
  type: string
  source: 'projects' | 'biddingProjects' // 어느 테이블에서 왔는지 구분
  originalData?: any // 원본 데이터 (필요시 사용)
}

// 프로젝트 검색 옵션
export interface ProjectSearchOptions {
  searchFrom: 'both' | 'projects' | 'biddingProjects'
  searchTerm?: string
  limit?: number
}

// 프로젝트 정보 조회 결과 타입
export interface ProjectInfo {
  projectCode: string
  projectName: string | null
  shipType?: string | null
  projectMsrm?: string | null
  projectHtDivision?: string | null
  source: 'projects' | 'biddingProjects'
}

/**
 * 통합 프로젝트 검색 - projects와 biddingProjects 테이블에서 검색
 */
export async function searchUnifiedProjects(options: ProjectSearchOptions): Promise<{
  success: boolean
  data: UnifiedProject[]
  error?: string
}> {
  try {
    const { searchFrom, searchTerm, limit = 100 } = options
    const results: UnifiedProject[] = []

    // projects 테이블에서 검색
    if (searchFrom === 'both' || searchFrom === 'projects') {
      try {
        let projectsQuery = db.select({
          id: projects.id,
          code: projects.code,
          name: projects.name,
          type: projects.type
        }).from(projects)

        if (searchTerm && searchTerm.trim()) {
          const term = `%${searchTerm.trim()}%`
          projectsQuery = projectsQuery.where(
            or(
              ilike(projects.code, term),
              ilike(projects.name, term)
            )
          )
        }

        const projectsResults = await projectsQuery.limit(limit)

        for (const project of projectsResults) {
          results.push({
            id: project.id.toString(),
            code: project.code,
            name: project.name,
            type: project.type || 'project',
            source: 'projects',
            originalData: project
          })
        }
      } catch (error) {
        console.error('Error searching projects table:', error)
        // projects 테이블 검색 실패해도 계속 진행
      }
    }

    // biddingProjects 테이블에서 검색
    if (searchFrom === 'both' || searchFrom === 'biddingProjects') {
      try {
        let biddingQuery = db.select({
          pspid: biddingProjects.pspid,
          projNm: biddingProjects.projNm,
          pjtType: biddingProjects.pjtType,
          ptypeNm: biddingProjects.ptypeNm
        }).from(biddingProjects)

        if (searchTerm && searchTerm.trim()) {
          const term = `%${searchTerm.trim()}%`
          biddingQuery = biddingQuery.where(
            or(
              ilike(biddingProjects.pspid, term),
              ilike(biddingProjects.projNm, term)
            )
          )
        }

        const biddingResults = await biddingQuery.limit(limit)

        for (const biddingProject of biddingResults) {
          results.push({
            id: biddingProject.pspid,
            code: biddingProject.pspid,
            name: biddingProject.projNm || '',
            type: biddingProject.pjtType || 'bidding',
            source: 'biddingProjects',
            originalData: biddingProject
          })
        }
      } catch (error) {
        console.error('Error searching biddingProjects table:', error)
        // biddingProjects 테이블 검색 실패해도 계속 진행
      }
    }

    return {
      success: true,
      data: results
    }
  } catch (error) {
    console.error('Error in searchUnifiedProjects:', error)
    return {
      success: false,
      data: [],
      error: '프로젝트 검색 중 오류가 발생했습니다.'
    }
  }
}

/**
 * 프로젝트 코드로 상세 정보 조회
 */
export async function getProjectInfoByCode(projectCode: string, searchFrom: 'both' | 'projects' | 'biddingProjects' = 'both'): Promise<ProjectInfo | null> {
  if (!projectCode.trim()) {
    return null
  }

  // projects 테이블에서 먼저 검색
  if (searchFrom === 'both' || searchFrom === 'projects') {
    try {
      const projectInfo = await db.select().from(projects).where(eq(projects.code, projectCode)).limit(1)
      
      if (projectInfo && projectInfo.length > 0) {
        return {
          projectCode: projectInfo[0].code,
          projectName: projectInfo[0].name,
          shipType: projectInfo[0].SKND || undefined,
          projectHtDivision: projectInfo[0].type || undefined,
          source: 'projects'
        }
      }
    } catch (error) {
      console.error('Error searching in projects table:', error)
    }
  }

  // biddingProjects 테이블에서 검색
  if (searchFrom === 'both' || searchFrom === 'biddingProjects') {
    try {
      const projectInfo = await db.select().from(biddingProjects).where(eq(biddingProjects.pspid, projectCode)).limit(1)
      
      if (projectInfo && projectInfo.length > 0) {
        let projectHtDivision = null
        if (projectInfo[0].pjtType === 'SHIP') {
          projectHtDivision = 'H'
        } else if (projectInfo[0].pjtType === 'HULL') {
          projectHtDivision = 'H'
        } else if (projectInfo[0].pjtType === 'TOP') {
          projectHtDivision = 'T'
        }

        return {
          projectCode: projectInfo[0].pspid,
          projectName: projectInfo[0].projNm,
          projectMsrm: projectInfo[0].ptypeNm,
          projectHtDivision,
          source: 'biddingProjects'
        }
      }
    } catch (error) {
      console.error('Error searching in biddingProjects table:', error)
    }
  }

  return null
}