path: root/lib/docu-list-rule/number-types/service.ts

"use server"

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

// Number Types 목록 조회
export async function getNumberTypes(input: {
  page: number
  perPage: number
  search?: string
  sort?: Array<{ id: string; desc: boolean }>
  filters?: Array<{ id: string; value: string }>
  joinOperator?: "and" | "or"
  flags?: string[]
  numberTypeId?: string
  description?: string
  isActive?: string
  projectId?: string
}) {
  unstable_noStore()
  
  try {
    const { page, perPage, sort, search, filters, joinOperator } = input
    const offset = (page - 1) * perPage

    // 기본 조건 (plant 타입 프로젝트만)
    let whereConditions = sql`${projects.type} = 'plant'`
    
    // 프로젝트 ID 필터링
    if (input.projectId) {
      whereConditions = sql`${whereConditions} AND ${documentNumberTypes.projectId} = ${parseInt(input.projectId)}`
    }
    
    // 검색 조건
    if (search) {
      const searchTerm = `%${search}%`
      whereConditions = sql`${whereConditions} AND (
        ${documentNumberTypes.name} ILIKE ${searchTerm} OR
        ${documentNumberTypes.description} 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 "name":
            return sql`${documentNumberTypes.name} ILIKE ${`%${value}%`}`
          case "description":
            return sql`${documentNumberTypes.description} ILIKE ${`%${value}%`}`
          case "isActive":
            return sql`${documentNumberTypes.isActive} = ${value === "true"}`
          case "createdAt":
            return sql`${documentNumberTypes.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`${documentNumberTypes.name} 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 documentNumberTypes) {
          const col = documentNumberTypes[sortField.id as keyof typeof documentNumberTypes]
          orderBy = sql`${col} ${direction}`
        }
      }
    }

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

    // 총 개수 조회 (프로젝트 정보 포함)
    const totalCountResult = await db
      .select({ count: sql<number>`count(*)` })
      .from(documentNumberTypes)
      .leftJoin(projects, eq(documentNumberTypes.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 number types:", error)
    return {
      data: [],
      totalCount: 0,
      pageCount: 0,
    }
  }
}

// Number Type 생성
export async function createNumberType(input: {
  projectId: number
  name: string
  description?: string
  isActive?: boolean
}) {
  try {
    // 해당 프로젝트에서 중복 이름 체크
    const existing = await db
      .select({ id: documentNumberTypes.id })
      .from(documentNumberTypes)
      .where(and(
        eq(documentNumberTypes.projectId, input.projectId),
        eq(documentNumberTypes.name, input.name)
      ))
      .limit(1)

    if (existing.length > 0) {
      return {
        success: false,
        error: "Number Type with this name already exists in this project"
      }
    }

    const [newNumberType] = await db
      .insert(documentNumberTypes)
      .values({
        projectId: input.projectId,
        name: input.name,
        description: input.description,
        isActive: input.isActive ?? true,
      })
      .returning({ id: documentNumberTypes.id })

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

// Number Type 수정
export async function updateNumberType(input: {
  id: number
  name: string
  description?: string
  isActive?: boolean
}) {
  try {
    // 다른 Number Type에서 같은 이름 사용하는지 체크
    const existing = await db
      .select({ id: documentNumberTypes.id })
      .from(documentNumberTypes)
      .where(sql`${documentNumberTypes.name} = ${input.name} AND ${documentNumberTypes.id} != ${input.id}`)
      .limit(1)

    if (existing.length > 0) {
      return {
        success: false,
        error: "Number Type with this name already exists"
      }
    }

    const [updatedNumberType] = await db
      .update(documentNumberTypes)
      .set({
        name: input.name,
        description: input.description,
        isActive: input.isActive,
        updatedAt: new Date(),
      })
      .where(eq(documentNumberTypes.id, input.id))
      .returning({ id: documentNumberTypes.id })

    if (!updatedNumberType) {
      return {
        success: false,
        error: "Number Type not found"
      }
    }

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

// Number Type 삭제
export async function deleteNumberType(id: number) {
  try {
    // Number Type 정보 조회
    const numberType = await db
      .select({
        id: documentNumberTypes.id,
        name: documentNumberTypes.name,
        description: documentNumberTypes.description
      })
      .from(documentNumberTypes)
      .where(eq(documentNumberTypes.id, id))
      .limit(1)

    if (numberType.length === 0) {
      return {
        success: false,
        error: "Number Type not found"
      }
    }

    // 관련된 config가 있는지 확인 (Code Group 정보 포함)
    const relatedConfigs = await db
      .select({
        id: documentNumberTypeConfigs.id,
        codeGroupDescription: codeGroups.description
      })
      .from(documentNumberTypeConfigs)
      .leftJoin(codeGroups, eq(documentNumberTypeConfigs.codeGroupId, codeGroups.id))
      .where(eq(documentNumberTypeConfigs.documentNumberTypeId, id))

    // 설정 정보를 반환 (삭제는 허용하되 경고용)
    const configInfo = relatedConfigs.length > 0 ? {
      hasConfigs: true,
      codeGroupNames: relatedConfigs.map(config => 
        config.codeGroupDescription || "Unknown Code Group"
      )
    } : {
      hasConfigs: false,
      codeGroupNames: []
    }

    // Number Type 삭제 (CASCADE로 관련 config들도 자동 삭제됨)
    await db
      .delete(documentNumberTypes)
      .where(eq(documentNumberTypes.id, id))

    revalidatePath("/evcp/docu-list-rule/number-types")
    
    return {
      success: true,
      message: configInfo.hasConfigs 
        ? `Number Type "${numberType[0].name}"이(가) 설정과 함께 삭제되었습니다.`
        : "Number Type deleted successfully",
      configInfo
    }
  } catch (error) {
    console.error("Error deleting number type:", error)
    return {
      success: false,
      error: "Failed to delete number type"
    }
  }
}

// Number Type 설정 정보 확인 (삭제하지 않고 정보만 반환)
export async function checkNumberTypeConfigs(id: number) {
  try {
    // Number Type 정보 조회
    const numberType = await db
      .select({
        id: documentNumberTypes.id,
        name: documentNumberTypes.name,
        description: documentNumberTypes.description
      })
      .from(documentNumberTypes)
      .where(eq(documentNumberTypes.id, id))
      .limit(1)

    if (numberType.length === 0) {
      return {
        success: false,
        error: "Number Type not found"
      }
    }

    // 관련된 config가 있는지 확인 (Code Group 정보 포함)
    const relatedConfigs = await db
      .select({
        id: documentNumberTypeConfigs.id,
        codeGroupDescription: codeGroups.description
      })
      .from(documentNumberTypeConfigs)
      .leftJoin(codeGroups, eq(documentNumberTypeConfigs.codeGroupId, codeGroups.id))
      .where(eq(documentNumberTypeConfigs.documentNumberTypeId, id))

    const configInfo = relatedConfigs.length > 0 ? {
      hasConfigs: true,
      codeGroupNames: relatedConfigs.map(config => 
        config.codeGroupDescription || "Unknown Code Group"
      )
    } : {
      hasConfigs: false,
      codeGroupNames: []
    }

    return {
      success: true,
      data: {
        numberType: numberType[0],
        configInfo
      }
    }
  } catch (error) {
    console.error("Error checking number type configs:", error)
    return {
      success: false,
      error: "Failed to check number type configs"
    }
  }
}

// Number Types 목록 조회 (설정 정보 포함)
export async function getNumberTypesWithConfigs(input: {
  page: number
  perPage: number
  search?: string
  sort?: Array<{ id: string; desc: boolean }>
  filters?: Array<{ id: string; value: string }>
  joinOperator?: "and" | "or"
  flags?: string[]
  numberTypeId?: string
  description?: string
  isActive?: string
  projectId?: string
}) {
  unstable_noStore()
  
  try {
    const { page, perPage, sort, search, filters, joinOperator } = input
    const offset = (page - 1) * perPage

    // 기본 조건 (plant 타입 프로젝트만)
    let whereConditions = sql`${projects.type} = 'plant'`
    
    // 프로젝트 ID 필터링
    if (input.projectId) {
      whereConditions = sql`${whereConditions} AND ${documentNumberTypes.projectId} = ${parseInt(input.projectId)}`
    }
    
    // 검색 조건
    if (search) {
      const searchTerm = `%${search}%`
      whereConditions = sql`${whereConditions} AND (
        ${documentNumberTypes.name} ILIKE ${searchTerm} OR
        ${documentNumberTypes.description} 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 "name":
            return sql`${documentNumberTypes.name} ILIKE ${`%${value}%`}`
          case "description":
            return sql`${documentNumberTypes.description} ILIKE ${`%${value}%`}`
          case "isActive":
            return sql`${documentNumberTypes.isActive} = ${value === "true"}`
          case "createdAt":
            return sql`${documentNumberTypes.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`${documentNumberTypes.name} 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 documentNumberTypes) {
          const col = documentNumberTypes[sortField.id as keyof typeof documentNumberTypes]
          orderBy = sql`${col} ${direction}`
        }
      }
    }

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

    // 각 Number Type에 대한 설정 정보 조회
    const dataWithConfigs = await Promise.all(
      data.map(async (numberType) => {
        const relatedConfigs = await db
          .select({
            id: documentNumberTypeConfigs.id,
            codeGroupDescription: codeGroups.description
          })
          .from(documentNumberTypeConfigs)
          .leftJoin(codeGroups, eq(documentNumberTypeConfigs.codeGroupId, codeGroups.id))
          .where(eq(documentNumberTypeConfigs.documentNumberTypeId, numberType.id))

        const configInfo = relatedConfigs.length > 0 ? {
          hasConfigs: true,
          codeGroupNames: relatedConfigs.map(config => 
            config.codeGroupDescription || "Unknown Code Group"
          )
        } : {
          hasConfigs: false,
          codeGroupNames: []
        }

        return {
          ...numberType,
          configInfo
        }
      })
    )

    // 전체 개수 조회
    const totalCount = await db
      .select({ count: sql<number>`count(*)` })
      .from(documentNumberTypes)
      .leftJoin(projects, eq(documentNumberTypes.projectId, projects.id))
      .where(whereConditions)

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

export async function checkNumberTypesExist(projectId: number, names: string[]): Promise<boolean> {
  try {
    const existing = await db
      .select({ name: documentNumberTypes.name })
      .from(documentNumberTypes)
      .where(and(
        eq(documentNumberTypes.projectId, projectId),
        inArray(documentNumberTypes.name, names)
      ))

    // 모든 name이 존재하는지 체크
    return existing.length === names.length
  } catch (error) {
    console.error("Error checking number types:", error)
    return false
  }
}