"use server"

import { revalidatePath } from "next/cache"
import db from "@/db/db"
import { codeGroups, comboBoxSettings } from "@/db/schema/docu-list-rule"
import { eq, sql, count } 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
}) {
  unstable_noStore()
  
  try {
    const { page, perPage, sort, search, filters, joinOperator } = input
    const offset = (page - 1) * perPage

    // Control Type이 combobox인 조건
    let whereConditions = sql`${codeGroups.controlType} = 'combobox'`
    
    // 검색 조건
    if (search) {
      const searchTerm = `%${search}%`
      whereConditions = sql`${whereConditions} AND (
        ${codeGroups.groupId} ILIKE ${searchTerm} OR
        ${codeGroups.description} ILIKE ${searchTerm} OR
        ${codeGroups.codeFormat} 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.createdAt} DESC`
    if (sort && sort.length > 0) {
      const sortField = sort[0]
      // 안전성 체크: 필드가 실제 테이블에 존재하는지 확인
      if (sortField && sortField.id && typeof sortField.id === "string" && sortField.id in codeGroups) {
        const direction = sortField.desc ? sql`DESC` : sql`ASC`
        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,
      })
      .from(codeGroups)
      .where(whereConditions)
      .orderBy(orderBy)
      .limit(perPage)
      .offset(offset)

    // 총 개수 조회
    const [{ count: total }] = await db
      .select({ count: count() })
      .from(codeGroups)
      .where(whereConditions)

    const pageCount = Math.ceil(total / perPage)

    return {
      success: true,
      data,
      pageCount,
    }
  } catch (error) {
    console.error("Error fetching combo box code groups:", error)
    return {
      success: false,
      error: "Failed to fetch combo box code groups",
      data: [],
      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
    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}
      )`
    }

    // 정렬 (안전한 필드 체크 적용)
    let orderBy = sql`${comboBoxSettings.createdAt} DESC`
    if (sort && sort.length > 0) {
      const sortField = sort[0]
      // 안전성 체크: 필드가 실제 테이블에 존재하는지 확인
      if (sortField && sortField.id && typeof sortField.id === "string" && sortField.id in comboBoxSettings) {
        const direction = sortField.desc ? sql`DESC` : sql`ASC`
        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,
        createdAt: comboBoxSettings.createdAt,
        updatedAt: comboBoxSettings.updatedAt,
      })
      .from(comboBoxSettings)
      .where(whereConditions)
      .orderBy(orderBy)
      .limit(perPage)
      .offset(offset)

    // 총 개수 조회
    const [{ count: total }] = await db
      .select({ count: count() })
      .from(comboBoxSettings)
      .where(whereConditions)

    const pageCount = Math.ceil(total / perPage)

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

// 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 codeGroupDescription = codeGroup[0].description

    // 해당 Code Group의 마지막 옵션 번호 찾기
    const lastOption = await db
      .select({ code: comboBoxSettings.code })
      .from(comboBoxSettings)
      .where(eq(comboBoxSettings.codeGroupId, input.codeGroupId))
      .orderBy(sql`CAST(SUBSTRING(${comboBoxSettings.code} FROM ${codeGroupDescription.length + 2}) AS INTEGER) DESC`)
      .limit(1)

    let nextNumber = 1
    if (lastOption.length > 0 && lastOption[0].code) {
      const prefix = `${codeGroupDescription}_`
      if (lastOption[0].code.startsWith(prefix)) {
        const lastNumber = parseInt(lastOption[0].code.replace(prefix, ''))
        if (!isNaN(lastNumber)) {
          nextNumber = lastNumber + 1
        }
      }
    }

    const newCode = `${codeGroupDescription}_${nextNumber}`

    const [newOption] = await db
      .insert(comboBoxSettings)
      .values({
        codeGroupId: input.codeGroupId,
        code: newCode,
        description: input.description,
        remark: input.remark,
      })
      .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
}) {
  try {
    const [updatedOption] = await db
      .update(comboBoxSettings)
      .set({
        code: input.code,
        description: input.description,
        remark: input.remark,
        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"
    }
  }
}