"use server"

import { revalidatePath } from "next/cache"
import db from "@/db/db"
import { unstable_noStore } from "next/cache"
import { documentNumberTypeConfigs, codeGroups, documentNumberTypes } from "@/db/schema/docu-list-rule"
import { projects } from "@/db/schema/projects"
import { asc, eq, sql, and } from "drizzle-orm"
import { GetNumberTypeConfigsSchema } from "./validation"

// 특정 Number Type의 Configs 조회
export async function getNumberTypeConfigs(input: GetNumberTypeConfigsSchema) {
  unstable_noStore()
  
  try {
    const { numberTypeId, page = 1, perPage = 10, search, filters, joinOperator } = input
    const offset = (page - 1) * perPage

    // numberTypeId 유효성 검사
    if (!numberTypeId || numberTypeId <= 0) {
      return {
        data: [],
        totalCount: 0,
        pageCount: 0,
      }
    }

    // 기본 조건: 특정 Number Type
    let whereConditions = eq(documentNumberTypeConfigs.documentNumberTypeId, numberTypeId)
    
    // 프로젝트 ID 필터링 (Number Type이 해당 프로젝트에 속하는지 확인)
    if (input.projectId) {
      whereConditions = sql`${whereConditions} AND ${documentNumberTypes.projectId} = ${parseInt(input.projectId)}`
    }

    // 검색 조건 추가
    if (search && search.trim() !== "") {
      const searchTerm = `%${search}%`
      whereConditions = sql`${whereConditions} AND (
        ${codeGroups.description} ILIKE ${searchTerm} OR
        ${documentNumberTypeConfigs.description} ILIKE ${searchTerm} OR
        ${documentNumberTypeConfigs.remark} ILIKE ${searchTerm} OR
        ${projects.code} ILIKE ${searchTerm}
      )`
    }

    // 고급 필터링 (Code Groups 스타일)
    if (filters && filters.length > 0) {
      const filterConditions = filters.map(filter => {
        const { id, value } = filter
        if (!value || Array.isArray(value)) return null
        
        switch (id) {
          case "codeGroupName":
            return sql`${codeGroups.description} ILIKE ${`%${value}%`}`
          case "description":
            return sql`${documentNumberTypeConfigs.description} ILIKE ${`%${value}%`}`
          case "remark":
            return sql`${documentNumberTypeConfigs.remark} ILIKE ${`%${value}%`}`
          case "sdq":
            return sql`${documentNumberTypeConfigs.sdq} = ${parseInt(value)}`
          case "isActive":
            return sql`${documentNumberTypeConfigs.isActive} = ${value === "true"}`
          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 = and(whereConditions, sql`(${combinedFilters})`) || whereConditions
      }
    }

    // 정렬 (Code Groups 스타일)
    let orderBy = sql`${documentNumberTypeConfigs.sdq} 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`
        
        // 정렬 필드에 따른 컬럼 선택
        let col: typeof documentNumberTypeConfigs.sdq | typeof documentNumberTypeConfigs.description | typeof documentNumberTypeConfigs.remark | typeof codeGroups.description | typeof documentNumberTypeConfigs.createdAt | typeof documentNumberTypeConfigs.updatedAt
        switch (sortField.id) {
          case "sdq":
            col = documentNumberTypeConfigs.sdq
            break
          case "description":
            col = documentNumberTypeConfigs.description
            break
          case "remark":
            col = documentNumberTypeConfigs.remark
            break
          case "codeGroupName":
            col = codeGroups.description
            break
          case "createdAt":
            col = documentNumberTypeConfigs.createdAt
            break
          case "updatedAt":
            col = documentNumberTypeConfigs.updatedAt
            break
          default:
            col = documentNumberTypeConfigs.sdq
        }
        orderBy = sql`${col} ${direction}`
      }
    }

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

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

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

    return {
      data,
      totalCount,
      pageCount: Math.ceil(totalCount / perPage),
    }
  } catch (error) {
    console.error("Error fetching number type configs:", error)
    return {
      data: [],
      totalCount: 0,
      pageCount: 0,
    }
  }
}

export async function createNumberTypeConfig(input: {
  documentNumberTypeId: number
  codeGroupId: number | null
  sdq: number
  description?: string
  delimiter?: string
  remark?: string
}) {
  try {
    // 중복 체크: 같은 documentNumberTypeId와 sdq 조합이 이미 존재하는지 확인
    const existingConfig = await db
      .select({ id: documentNumberTypeConfigs.id })
      .from(documentNumberTypeConfigs)
      .where(
        sql`${documentNumberTypeConfigs.documentNumberTypeId} = ${input.documentNumberTypeId} AND ${documentNumberTypeConfigs.sdq} = ${input.sdq}`
      )
      .limit(1)

    if (existingConfig.length > 0) {
      return {
        success: false,
        error: "이미 존재하는 순서 번호입니다."
      }
    }

    const [result] = await db
      .insert(documentNumberTypeConfigs)
      .values({
        documentNumberTypeId: input.documentNumberTypeId,
        codeGroupId: input.codeGroupId,
        sdq: input.sdq,
        description: input.description,
        delimiter: input.delimiter,
        remark: input.remark,
      })
      .returning({ id: documentNumberTypeConfigs.id })

    revalidatePath("/evcp/docu-list-rule/number-type-configs")
    
    return {
      success: true,
      data: result,
      message: "Number Type Config created successfully"
    }
  } catch (error) {
    console.error("Error creating number type config:", error)
    return {
      success: false,
      error: "Failed to create number type config"
    }
  }
}

export async function updateNumberTypeConfig(input: {
  id: number
  codeGroupId: number | null
  sdq: number
  description?: string
  delimiter?: string
  remark?: string
}) {
  try {
    // 현재 수정 중인 항목의 documentNumberTypeId 가져오기
    const currentConfig = await db
      .select({ documentNumberTypeId: documentNumberTypeConfigs.documentNumberTypeId })
      .from(documentNumberTypeConfigs)
      .where(eq(documentNumberTypeConfigs.id, input.id))
      .limit(1)

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

    // 중복 체크: 같은 documentNumberTypeId와 sdq 조합이 이미 존재하는지 확인 (현재 수정 중인 항목 제외)
    const existingConfig = await db
      .select({ id: documentNumberTypeConfigs.id })
      .from(documentNumberTypeConfigs)
      .where(
        sql`${documentNumberTypeConfigs.documentNumberTypeId} = ${currentConfig[0].documentNumberTypeId} AND ${documentNumberTypeConfigs.sdq} = ${input.sdq} AND ${documentNumberTypeConfigs.id} != ${input.id}`
      )
      .limit(1)

    if (existingConfig.length > 0) {
      return {
        success: false,
        error: "이미 존재하는 순서 번호입니다."
      }
    }

    const [result] = await db
      .update(documentNumberTypeConfigs)
      .set({
        codeGroupId: input.codeGroupId,
        sdq: input.sdq,
        description: input.description,
        delimiter: input.delimiter,
        remark: input.remark,
        updatedAt: new Date(),
      })
      .where(eq(documentNumberTypeConfigs.id, input.id))
      .returning({ id: documentNumberTypeConfigs.id })

    revalidatePath("/evcp/docu-list-rule/number-type-configs")
    
    return {
      success: true,
      data: result,
      message: "Number Type Config updated successfully"
    }
  } catch (error) {
    console.error("Error updating number type config:", error)
    return {
      success: false,
      error: "Failed to update number type config"
    }
  }
}
// Number Type Config 순서 변경 (간단한 방식)
export async function updateNumberTypeConfigOrder(input: {
  id: number
  sdq: number
}) {
  try {
    // 현재 수정 중인 항목의 documentNumberTypeId 가져오기
    const currentConfig = await db
      .select({ 
        documentNumberTypeId: documentNumberTypeConfigs.documentNumberTypeId,
        currentSdq: documentNumberTypeConfigs.sdq
      })
      .from(documentNumberTypeConfigs)
      .where(eq(documentNumberTypeConfigs.id, input.id))
      .limit(1)

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

    const { documentNumberTypeId, currentSdq } = currentConfig[0]

    // 임시 값으로 먼저 업데이트 (중복 방지)
    const tempSdq = -999999 // 충분히 작은 임시 값
    await db
      .update(documentNumberTypeConfigs)
      .set({
        sdq: tempSdq,
        updatedAt: new Date(),
      })
      .where(eq(documentNumberTypeConfigs.id, input.id))

    // 다른 항목들의 순서 조정
    if (input.sdq > currentSdq) {
      // 순서가 증가한 경우: 현재 순서와 새 순서 사이의 항목들을 1씩 감소
      await db
        .update(documentNumberTypeConfigs)
        .set({
          sdq: sql`${documentNumberTypeConfigs.sdq} - 1`,
          updatedAt: new Date(),
        })
        .where(
          sql`${documentNumberTypeConfigs.documentNumberTypeId} = ${documentNumberTypeId} AND ${documentNumberTypeConfigs.sdq} > ${currentSdq} AND ${documentNumberTypeConfigs.sdq} <= ${input.sdq} AND ${documentNumberTypeConfigs.id} != ${input.id}`
        )
    } else if (input.sdq < currentSdq) {
      // 순서가 감소한 경우: 새 순서와 현재 순서 사이의 항목들을 1씩 증가
      await db
        .update(documentNumberTypeConfigs)
        .set({
          sdq: sql`${documentNumberTypeConfigs.sdq} + 1`,
          updatedAt: new Date(),
        })
        .where(
          sql`${documentNumberTypeConfigs.documentNumberTypeId} = ${documentNumberTypeId} AND ${documentNumberTypeConfigs.sdq} >= ${input.sdq} AND ${documentNumberTypeConfigs.sdq} < ${currentSdq} AND ${documentNumberTypeConfigs.id} != ${input.id}`
        )
    }

    // 최종 순서로 업데이트
    const [result] = await db
      .update(documentNumberTypeConfigs)
      .set({
        sdq: input.sdq,
        updatedAt: new Date(),
      })
      .where(eq(documentNumberTypeConfigs.id, input.id))
      .returning({ id: documentNumberTypeConfigs.id })

    revalidatePath("/evcp/docu-list-rule/number-type-configs")
    
    return {
      success: true,
      data: result,
      message: "Number Type Config order updated successfully"
    }
  } catch (error) {
    console.error("Error updating number type config order:", error)
    return {
      success: false,
      error: "Failed to update number type config order"
    }
  }
}

// Number Type Config 삭제
export async function deleteNumberTypeConfig(id: number) {
  try {
    // 삭제할 Config 정보 조회
    const [configToDelete] = await db
      .select({
        documentNumberTypeId: documentNumberTypeConfigs.documentNumberTypeId,
        sdq: documentNumberTypeConfigs.sdq,
      })
      .from(documentNumberTypeConfigs)
      .where(eq(documentNumberTypeConfigs.id, id))

    if (!configToDelete) {
      throw new Error("Config not found")
    }

    // Config 삭제
    await db
      .delete(documentNumberTypeConfigs)
      .where(eq(documentNumberTypeConfigs.id, id))

    // 순서 재정렬 (삭제된 순서보다 큰 모든 항목의 순서를 1씩 감소)
    await db
      .update(documentNumberTypeConfigs)
      .set({
        sdq: sql`${documentNumberTypeConfigs.sdq} - 1`,
        updatedAt: new Date(),
      })
      .where(
        sql`${documentNumberTypeConfigs.documentNumberTypeId} = ${configToDelete.documentNumberTypeId} AND ${documentNumberTypeConfigs.sdq} > ${configToDelete.sdq}`
      )

    revalidatePath("/evcp/docu-list-rule/number-type-configs")
    
    return {
      success: true,
      data: configToDelete,
      message: "Number Type Config deleted successfully"
    }
  } catch (error) {
    console.error("Error deleting number type config:", error)
    
    if (error instanceof Error && error.message === "Config not found") {
      return {
        success: false,
        error: "Config not found"
      }
    }
    
    return {
      success: false,
      error: "Failed to delete number type config"
    }
  }
}

// 활성화된 Code Groups 조회 (Config 생성/수정 시 사용)
export async function getActiveCodeGroups(projectId?: number) {
  unstable_noStore()
  
  try {
    let whereConditions = eq(codeGroups.isActive, true)
    
    // 프로젝트별 필터링 추가
    if (projectId) {
      whereConditions = and(whereConditions, eq(codeGroups.projectId, projectId)) || whereConditions
    }

    const codeGroupsData = await db
      .select({
        id: codeGroups.id,
        groupId: codeGroups.groupId,
        description: codeGroups.description,
        controlType: codeGroups.controlType,
        isActive: codeGroups.isActive,
        projectId: codeGroups.projectId,
      })
      .from(codeGroups)
      .where(whereConditions)
      .orderBy(asc(codeGroups.description))

    return {
      success: true,
      data: codeGroupsData,
    }
  } catch (error) {
    console.error("Error fetching active code groups:", error)
    return {
      success: false,
      error: "Failed to fetch active code groups",
      data: [],
    }
  }
}