"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, count, and } from "drizzle-orm"
import { unstable_noStore } from "next/cache"

// Code Groups 목록 조회  
export async function getCodeGroups(input: any) {
  unstable_noStore()
  
  try {
    const { page, perPage, search, filters, joinOperator } = input
    const offset = (page - 1) * perPage

    // 검색 조건 (Document Class 제외)
    let whereConditions = sql`${codeGroups.groupId} != 'DOC_CLASS'`
    
    // 검색어 필터링
    if (search) {
      const searchTerm = `%${search}%`
      whereConditions = sql`${codeGroups.groupId} != 'DOC_CLASS' AND (
        ${codeGroups.groupId} ILIKE ${searchTerm} OR
        ${codeGroups.description} ILIKE ${searchTerm} OR
        ${codeGroups.codeFormat} ILIKE ${searchTerm} OR
        ${codeGroups.controlType} ILIKE ${searchTerm} OR
        ${projects.code} ILIKE ${searchTerm}
      )`
    }

    // 고급 필터링 (단순화)
    if (filters && filters.length > 0) {
      const filterConditions = filters.map(filter => {
        const { id, value } = filter
        if (!value || Array.isArray(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 (input.sort && input.sort.length > 0) {
      const sortField = input.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 code groups:", error)
    return {
      data: [],
      totalCount: 0,
      pageCount: 0,
    }
  }
}

// Code Group 생성
export async function createCodeGroup(input: {
  projectId: number // projectCode를 projectId로 변경
  description: string
  codeFormat?: string
  expressions?: string
  controlType: string
  isActive?: boolean
}) {
  try {
    // 해당 프로젝트의 마지막 Code Group의 groupId를 찾아서 다음 번호 생성 (DOC_CLASS 제외)
    const lastCodeGroup = await db
      .select({ groupId: codeGroups.groupId })
      .from(codeGroups)
      .where(and(
        eq(codeGroups.projectId, input.projectId), // projectId로 변경
        sql`${codeGroups.groupId} != 'DOC_CLASS'`
      ))
      .orderBy(sql`CAST(SUBSTRING(${codeGroups.groupId}, 6) AS INTEGER) DESC`)
      .limit(1)

    let nextNumber = 1
    if (lastCodeGroup.length > 0 && lastCodeGroup[0].groupId) {
      const lastNumber = parseInt(lastCodeGroup[0].groupId.replace('Code_', ''))
      if (!isNaN(lastNumber)) {
        nextNumber = lastNumber + 1
      }
    }

    const newGroupId = `Code_${nextNumber}`

    // 새 Code Group 생성
    const [newCodeGroup] = await db
      .insert(codeGroups)
      .values({
        projectId: input.projectId, // projectId로 변경
        groupId: newGroupId,
        description: input.description,
        codeFormat: input.codeFormat,
        expressions: input.expressions,
        controlType: input.controlType,
        isActive: input.isActive ?? true,
      })
      .returning({ id: codeGroups.id, groupId: codeGroups.groupId })

    revalidatePath("/evcp/docu-list-rule/code-groups")
    
    return {
      success: true,
      data: newCodeGroup,
      message: "Code Group created successfully"
    }
  } catch (error) {
    console.error("Error creating code group:", error)
    return {
      success: false,
      error: "Failed to create code group"
    }
  }
}

// Code Group 수정
export async function updateCodeGroup(input: {
  id: number
  description: string
  codeFormat?: string
  expressions?: string
  controlType: string
  isActive?: boolean
}) {
  try {
    const [updatedCodeGroup] = await db
      .update(codeGroups)
      .set({
        description: input.description,
        codeFormat: input.codeFormat,
        expressions: input.expressions,
        controlType: input.controlType,
        isActive: input.isActive,
        updatedAt: new Date(),
      })
      .where(eq(codeGroups.id, input.id))
      .returning({ id: codeGroups.id })

    revalidatePath("/evcp/docu-list-rule/code-groups")
    
    return {
      success: true,
      data: updatedCodeGroup,
      message: "Code Group updated successfully"
    }
  } catch (error) {
    console.error("Error updating code group:", error)
    return {
      success: false,
      error: "Failed to update code group"
    }
  }
}

// Code Group 삭제
export async function deleteCodeGroup(id: number) {
  try {
    // Code Group 정보 조회
    const codeGroup = await db
      .select({
        id: codeGroups.id,
        controlType: codeGroups.controlType,
        description: codeGroups.description
      })
      .from(codeGroups)
      .where(eq(codeGroups.id, id))
      .limit(1)

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

    // Control Type이 combobox인 경우 관련 Combo Box 옵션들도 삭제
    if (codeGroup[0].controlType === 'combobox') {
      // Combo Box 옵션들 삭제
      await db
        .delete(comboBoxSettings)
        .where(eq(comboBoxSettings.codeGroupId, id))
    }

    // Code Group 삭제
    await db
      .delete(codeGroups)
      .where(eq(codeGroups.id, id))

    revalidatePath("/evcp/docu-list-rule/code-groups")
    revalidatePath("/evcp/docu-list-rule/combo-box-settings")
    
    return {
      success: true,
      message: codeGroup[0].controlType === 'combobox' 
        ? `Code Group과 관련 Combo Box 옵션들이 삭제되었습니다.`
        : "Code Group이 삭제되었습니다."
    }
  } catch (error) {
    console.error("Error deleting code group:", error)
    return {
      success: false,
      error: "Failed to delete code group"
    }
  }
}

// Code Group 단일 조회
export async function getCodeGroupById(id: number) {
  try {
    const [codeGroup] = 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(eq(codeGroups.id, id))
      .limit(1)

    return codeGroup || null
  } catch (error) {
    console.error("Error fetching code group by id:", error)
    return null
  }
}