path: root/lib/docu-list-rule/combo-box-settings/service.ts

"use server"

import { revalidatePath } from "next/cache"
import db from "@/db/db"
import { codeGroups, comboBoxSettings } from "@/db/schema/docu-list-rule"
import { projects } from "@/db/schema/projects"
import { eq, sql } from "drizzle-orm"
import { unstable_noStore } from "next/cache"

// Control Type이 combobox인 Code Groups 목록 조회
export async function getComboBoxCodeGroups(input: {
  page: number
  perPage: number
  search?: string
  sort?: Array<{ id: string; desc: boolean }>
  filters?: Array<{ id: string; value: string }>
  joinOperator?: "and" | "or"
  flags?: string[]
  groupId?: string
  description?: string
  isActive?: string
  projectId?: string
}) {
  unstable_noStore()
  
  try {
    const { page, perPage, sort, search, filters, joinOperator } = input
    const offset = (page - 1) * perPage

    // Control Type이 combobox이고 plant 타입 프로젝트인 조건
    let whereConditions = sql`${codeGroups.controlType} = 'combobox' AND ${projects.type} = 'plant'`
    
    // 프로젝트 ID 필터링
    if (input.projectId) {
      whereConditions = sql`${whereConditions} AND ${codeGroups.projectId} = ${parseInt(input.projectId)}`
    }
    
    // 검색 조건
    if (search) {
      const searchTerm = `%${search}%`
      whereConditions = sql`${whereConditions} AND (
        ${codeGroups.groupId} ILIKE ${searchTerm} OR
        ${codeGroups.description} ILIKE ${searchTerm} OR
        ${codeGroups.codeFormat} ILIKE ${searchTerm} OR
        ${projects.code} ILIKE ${searchTerm}
      )`
    }

    // 고급 필터링
    if (filters && filters.length > 0) {
      const filterConditions = filters.map(filter => {
        const { id, value } = filter
        if (!value) return null
        
        switch (id) {
          case "groupId":
            return sql`${codeGroups.groupId} ILIKE ${`%${value}%`}`
          case "description":
            return sql`${codeGroups.description} ILIKE ${`%${value}%`}`
          case "codeFormat":
            return sql`${codeGroups.codeFormat} ILIKE ${`%${value}%`}`
          case "controlType":
            return sql`${codeGroups.controlType} = ${value}`
          case "isActive":
            return sql`${codeGroups.isActive} = ${value === "true"}`
          case "createdAt":
            return sql`${codeGroups.createdAt}::text ILIKE ${`%${value}%`}`
          default:
            return null
        }
      }).filter(Boolean)

      if (filterConditions.length > 0) {
        const operator = joinOperator === "or" ? sql` OR ` : sql` AND `
        const combinedFilters = filterConditions.reduce((acc, condition, index) => {
          if (index === 0) return condition
          return sql`${acc}${operator}${condition}`
        })
        
        whereConditions = sql`${whereConditions} AND (${combinedFilters})`
      }
    }

    // 정렬 (안전한 필드 체크 적용)
    let orderBy = sql`${codeGroups.groupId} ASC`
    if (sort && sort.length > 0) {
      const sortField = sort[0]
      // 안전성 체크: 필드가 실제 테이블에 존재하는지 확인
      if (sortField && sortField.id && typeof sortField.id === "string") {
        const direction = sortField.desc ? sql`DESC` : sql`ASC`
        
        // 프로젝트 코드 정렬 처리
        if (sortField.id === "projectCode") {
          orderBy = sql`${projects.code} ${direction}`
        } else if (sortField.id in codeGroups) {
          const col = codeGroups[sortField.id as keyof typeof codeGroups]
          orderBy = sql`${col} ${direction}`
        }
      }
    }

    // 데이터 조회 (프로젝트 정보 포함)
    const data = await db
      .select({
        id: codeGroups.id,
        groupId: codeGroups.groupId,
        description: codeGroups.description,
        codeFormat: codeGroups.codeFormat,
        expressions: codeGroups.expressions,
        controlType: codeGroups.controlType,
        isActive: codeGroups.isActive,
        createdAt: codeGroups.createdAt,
        updatedAt: codeGroups.updatedAt,
        projectId: codeGroups.projectId,
        projectCode: projects.code,
        projectName: projects.name,
      })
      .from(codeGroups)
      .leftJoin(projects, eq(codeGroups.projectId, projects.id))
      .where(whereConditions)
      .orderBy(orderBy)
      .limit(perPage)
      .offset(offset)

    // 총 개수 조회 (프로젝트 정보 포함)
    const totalCountResult = await db
      .select({ count: sql<number>`count(*)` })
      .from(codeGroups)
      .leftJoin(projects, eq(codeGroups.projectId, projects.id))
      .where(whereConditions)

    const totalCount = totalCountResult[0]?.count || 0

    return {
      data,
      totalCount,
      pageCount: Math.ceil(totalCount / perPage),
    }
  } catch (error) {
    console.error("Error fetching combo box code groups:", error)
    return {
      data: [],
      totalCount: 0,
      pageCount: 0,
    }
  }
}

// 특정 Code Group의 Combo Box 옵션 조회
export async function getComboBoxOptions(codeGroupId: number, input?: {
  page?: number
  perPage?: number
  search?: string
  sort?: Array<{ id: string; desc: boolean }>
  filters?: Array<{ id: string; value: string }>
  joinOperator?: "and" | "or"
}) {
  try {
    const { page = 1, perPage = 10, sort, search } = input || {}
    const offset = (page - 1) * perPage

    // 기본 조건: codeGroupId만으로 조회 (projectId는 codeGroups를 통해 간접 참조)
    let whereConditions = eq(comboBoxSettings.codeGroupId, codeGroupId)
    
    // 검색 조건
    if (search) {
      const searchTerm = `%${search}%`
      whereConditions = sql`${whereConditions} AND (
        ${comboBoxSettings.code} ILIKE ${searchTerm} OR
        ${comboBoxSettings.description} ILIKE ${searchTerm} OR
        ${comboBoxSettings.remark} ILIKE ${searchTerm} OR
        ${projects.code} ILIKE ${searchTerm}
      )`
    }

    // 정렬 (안전한 필드 체크 적용)
    let orderBy = sql`${comboBoxSettings.sdq} ASC`
    if (sort && sort.length > 0) {
      const sortField = sort[0]
      // 안전성 체크: 필드가 실제 테이블에 존재하는지 확인
      if (sortField && sortField.id && typeof sortField.id === "string") {
        const direction = sortField.desc ? sql`DESC` : sql`ASC`
        
        // 프로젝트 코드 정렬 처리
        if (sortField.id === "projectCode") {
          orderBy = sql`${projects.code} ${direction}`
        } else if (sortField.id in comboBoxSettings) {
          const col = comboBoxSettings[sortField.id as keyof typeof comboBoxSettings]
          orderBy = sql`${col} ${direction}`
        }
      }
    }

    // 데이터 조회 (프로젝트 정보 포함)
    const data = await db
      .select({
        id: comboBoxSettings.id,
        codeGroupId: comboBoxSettings.codeGroupId,
        code: comboBoxSettings.code,
        description: comboBoxSettings.description,
        remark: comboBoxSettings.remark,
        sdq: comboBoxSettings.sdq,
        createdAt: comboBoxSettings.createdAt,
        updatedAt: comboBoxSettings.updatedAt,
        projectCode: projects.code,
        projectName: projects.name,
      })
      .from(comboBoxSettings)
      .leftJoin(codeGroups, eq(comboBoxSettings.codeGroupId, codeGroups.id))
      .leftJoin(projects, eq(codeGroups.projectId, projects.id))
      .where(whereConditions)
      .orderBy(orderBy)
      .limit(perPage)
      .offset(offset)

    // 총 개수 조회 (프로젝트 정보 포함)
    const totalCountResult = await db
      .select({ count: sql<number>`count(*)` })
      .from(comboBoxSettings)
      .leftJoin(codeGroups, eq(comboBoxSettings.codeGroupId, codeGroups.id))
      .leftJoin(projects, eq(codeGroups.projectId, projects.id))
      .where(whereConditions)

    const totalCount = totalCountResult[0]?.count || 0

    return {
      success: true,
      data,
      totalCount,
      pageCount: Math.ceil(totalCount / perPage),
    }
  } catch (error) {
    console.error("Error fetching combo box options:", error)
    return {
      success: false,
      data: [],
      totalCount: 0,
      pageCount: 0,
      error: "Failed to fetch combo box options"
    }
  }
}

// Combo Box 옵션 생성
export async function createComboBoxOption(input: {
  codeGroupId: number
  code: string
  description: string
  remark?: string
}) {
  try {
    // 해당 Code Group의 정보 가져오기
    const codeGroup = await db
      .select({ 
        description: codeGroups.description
      })
      .from(codeGroups)
      .where(eq(codeGroups.id, input.codeGroupId))
      .limit(1)

    if (codeGroup.length === 0) {
      return {
        success: false,
        error: "Code Group not found"
      }
    }

    // 코드 중복 체크
    const existingOption = await db
      .select({ id: comboBoxSettings.id })
      .from(comboBoxSettings)
      .where(
        sql`${comboBoxSettings.codeGroupId} = ${input.codeGroupId} AND ${comboBoxSettings.code} = ${input.code}`
      )
      .limit(1)

    if (existingOption.length > 0) {
      return {
        success: false,
        error: "이미 존재하는 코드입니다."
      }
    }

    // 다음 순서 번호 계산
    const maxSdqResult = await db
      .select({ maxSdq: sql<number>`COALESCE(MAX(sdq), 0)` })
      .from(comboBoxSettings)
      .where(eq(comboBoxSettings.codeGroupId, input.codeGroupId))

    const nextSdq = (maxSdqResult[0]?.maxSdq || 0) + 1

    const [newOption] = await db
      .insert(comboBoxSettings)
      .values({
        codeGroupId: input.codeGroupId,
        code: input.code,
        description: input.description || "-",
        remark: input.remark,
        sdq: nextSdq,
      })
      .returning({ id: comboBoxSettings.id })



    revalidatePath("/evcp/docu-list-rule/combo-box-settings")
    
    return {
      success: true,
      data: newOption,
      message: "Combo Box option created successfully"
    }
  } catch (error) {
    console.error("Error creating combo box option:", error)
    return {
      success: false,
      error: "Failed to create combo box option"
    }
  }
}

// Combo Box 옵션 수정
export async function updateComboBoxOption(input: {
  id: number
  code: string
  description: string
  remark?: string
  sdq?: number
}) {
  try {
    // 현재 수정 중인 항목의 codeGroupId 가져오기
    const currentOption = await db
      .select({ codeGroupId: comboBoxSettings.codeGroupId })
      .from(comboBoxSettings)
      .where(eq(comboBoxSettings.id, input.id))
      .limit(1)

    if (currentOption.length === 0) {
      return {
        success: false,
        error: "Option not found"
      }
    }

    // 코드 중복 체크 (현재 수정 중인 항목 제외)
    const existingOption = await db
      .select({ id: comboBoxSettings.id })
      .from(comboBoxSettings)
      .where(
        sql`${comboBoxSettings.codeGroupId} = ${currentOption[0].codeGroupId} AND ${comboBoxSettings.code} = ${input.code} AND ${comboBoxSettings.id} != ${input.id}`
      )
      .limit(1)

    if (existingOption.length > 0) {
      return {
        success: false,
        error: "이미 존재하는 코드입니다."
      }
    }

    const [updatedOption] = await db
      .update(comboBoxSettings)
      .set({
        code: input.code,
        description: input.description,
        remark: input.remark,
        ...(input.sdq !== undefined && { sdq: input.sdq }),
        updatedAt: new Date(),
      })
      .where(eq(comboBoxSettings.id, input.id))
      .returning({ id: comboBoxSettings.id })

    revalidatePath("/evcp/docu-list-rule/combo-box-settings")
    
    return {
      success: true,
      data: updatedOption,
      message: "Combo Box option updated successfully"
    }
  } catch (error) {
    console.error("Error updating combo box option:", error)
    return {
      success: false,
      error: "Failed to update combo box option"
    }
  }
}

// Combo Box 옵션 삭제
export async function deleteComboBoxOption(id: number) {
  try {
    const [deletedOption] = await db
      .delete(comboBoxSettings)
      .where(eq(comboBoxSettings.id, id))
      .returning({ id: comboBoxSettings.id })

    if (!deletedOption) {
      return {
        success: false,
        error: "Option not found"
      }
    }

    revalidatePath("/evcp/docu-list-rule/combo-box-settings")
    
    return {
      success: true,
      message: "Combo Box option deleted successfully"
    }
  } catch (error) {
    console.error("Error deleting combo box option:", error)
    return {
      success: false,
      error: "Failed to delete combo box option"
    }
  }
}

// Code Group의 모든 Combo Box 옵션 삭제
export async function clearComboBoxOptions(codeGroupId: number) {
  try {
    const deletedOptions = await db
      .delete(comboBoxSettings)
      .where(eq(comboBoxSettings.codeGroupId, codeGroupId))
      .returning({ id: comboBoxSettings.id })

    revalidatePath("/evcp/docu-list-rule/combo-box-settings")
    
    return {
      success: true,
      data: deletedOptions,
      message: `Cleared ${deletedOptions.length} Combo Box options successfully`
    }
  } catch (error) {
    console.error("Error clearing combo box options:", error)
    return {
      success: false,
      error: "Failed to clear combo box options"
    }
  }
}

// Combo Box 옵션 순서 업데이트 (드래그 앤 드롭)
export async function updateComboBoxOptionOrder(codeGroupId: number, reorderedOptions: { id: number; sdq: number }[]) {
  try {
    console.log("Updating combo box option order:", { codeGroupId, reorderedOptions })
    
    // 유니크 제약조건 때문에 임시로 큰 값으로 업데이트 후 실제 값으로 업데이트
    await db.transaction(async (tx) => {
      // 1단계: 모든 옵션을 임시 큰 값으로 업데이트
      for (const option of reorderedOptions) {
        console.log("Step 1 - Setting temporary value for option:", option.id)
        await tx
          .update(comboBoxSettings)
          .set({
            sdq: option.sdq + 1000, // 임시로 큰 값 설정
            updatedAt: new Date(),
          })
          .where(eq(comboBoxSettings.id, option.id))
      }
      
      // 2단계: 실제 값으로 업데이트
      for (const option of reorderedOptions) {
        console.log("Step 2 - Setting final value for option:", option.id, "sdq:", option.sdq)
        const result = await tx
          .update(comboBoxSettings)
          .set({
            sdq: option.sdq,
            updatedAt: new Date(),
          })
          .where(eq(comboBoxSettings.id, option.id))
          .returning({ id: comboBoxSettings.id, sdq: comboBoxSettings.sdq })
        
        console.log("Update result:", result)
      }
    })

    revalidatePath("/evcp/docu-list-rule/combo-box-settings")
    
    return {
      success: true,
      message: "Combo Box options reordered successfully"
    }
  } catch (error) {
    console.error("Error updating combo box option order:", error)
    return {
      success: false,
      error: "Failed to update combo box option order"
    }
  }
}

// 기존 데이터에 기본 순서값 설정 (마이그레이션 후 한 번만 실행)
export async function initializeComboBoxOptionOrder() {
  try {
    // sdq가 null인 모든 옵션들을 찾아서 순서대로 업데이트
    const codeGroupsWithOptions = await db
      .select({
        codeGroupId: comboBoxSettings.codeGroupId,
        id: comboBoxSettings.id,
      })
      .from(comboBoxSettings)
      .orderBy(comboBoxSettings.codeGroupId, comboBoxSettings.createdAt)

    // codeGroupId별로 그룹화하여 순서 설정
    const groupedOptions = codeGroupsWithOptions.reduce((acc, option) => {
      if (!acc[option.codeGroupId]) {
        acc[option.codeGroupId] = []
      }
      acc[option.codeGroupId].push(option.id)
      return acc
    }, {} as Record<number, number[]>)

    // 각 그룹별로 순서 업데이트
    for (const [codeGroupId, optionIds] of Object.entries(groupedOptions)) {
      for (let i = 0; i < optionIds.length; i++) {
        await db
          .update(comboBoxSettings)
          .set({
            sdq: i + 1,
            updatedAt: new Date(),
          })
          .where(eq(comboBoxSettings.id, optionIds[i]))
      }
    }

    return {
      success: true,
      message: "Combo Box option order initialized successfully"
    }
  } catch (error) {
    console.error("Error initializing combo box option order:", error)
    return {
      success: false,
      error: "Failed to initialize combo box option order"
    }
  }
}