"use server"

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

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

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

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

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

// Document Class 생성
export async function createDocumentClassCodeGroup(input: {
  projectId: number // projectCode를 projectId로 변경
  value: string
  description?: string
}) {
  try {
    // Value는 1자리 대문자 알파벳 그대로 저장 (API DOC_CLASS 전송용)
    const formattedValue = input.value.trim().toUpperCase()

    const [newDocumentClass] = await db
      .insert(documentClasses)
      .values({
        projectId: input.projectId,
        value: formattedValue, // "A", "B", "C" 등 1자리
        description: input.description || "",
        codeGroupId: null,
        isActive: true,
      })
      .returning({ id: documentClasses.id })

    revalidatePath("/evcp/docu-list-rule/document-class")
    revalidatePath("/evcp/docu-list-rule/code-groups")
    
    return {
      success: true,
      data: newDocumentClass,
      message: "Document Class created successfully"
    }
  } catch (error) {
    console.error("Error creating document class:", error)
    return {
      success: false,
      error: "Failed to create document class"
    }
  }
}

// Document Class 수정
export async function updateDocumentClassCodeGroup(input: {
  id: number
  value: string
  description?: string
}) {
  try {
    // Value는 1자리 대문자 알파벳 그대로 저장 (API DOC_CLASS 전송용)
    const formattedValue = input.value.trim().toUpperCase()

    const [updatedDocumentClass] = await db
      .update(documentClasses)
      .set({
        value: formattedValue, // "A", "B", "C" 등 1자리
        description: input.description || "",
        updatedAt: new Date(),
      })
      .where(eq(documentClasses.id, input.id))
      .returning({ id: documentClasses.id })

    revalidatePath("/evcp/docu-list-rule/document-class")
    
    return {
      success: true,
      data: updatedDocumentClass,
      message: "Document Class updated successfully"
    }
  } catch (error) {
    console.error("Error updating document class:", error)
    return {
      success: false,
      error: "Failed to update document class"
    }
  }
}

// Document Class 삭제
// Document Class의 옵션 개수 조회
export async function getDocumentClassOptionsCount(documentClassId: number) {
  try {
    const result = await db
      .select({ count: sql<number>`count(*)` })
      .from(documentClassOptions)
      .where(eq(documentClassOptions.documentClassId, documentClassId))

    return {
      success: true,
      count: result[0]?.count || 0
    }
  } catch (error) {
    console.error("Error getting document class options count:", error)
    return {
      success: false,
      count: 0
    }
  }
}

export async function deleteDocumentClassCodeGroup(id: number) {
  try {
    // 먼저 해당 Document Class의 옵션들을 삭제
    await db
      .delete(documentClassOptions)
      .where(eq(documentClassOptions.documentClassId, id))

    // 삭제할 Document Class의 codeGroupId 확인
    const documentClassToDelete = await db
      .select({ codeGroupId: documentClasses.codeGroupId })
      .from(documentClasses)
      .where(eq(documentClasses.id, id))
      .limit(1)

    const [deletedDocumentClass] = await db
      .delete(documentClasses)
      .where(eq(documentClasses.id, id))
      .returning({ id: documentClasses.id })

    // 같은 codeGroupId를 가진 다른 Document Class가 있는지 확인
    if (documentClassToDelete.length > 0 && documentClassToDelete[0].codeGroupId) {
      const remainingClasses = await db
        .select({ id: documentClasses.id })
        .from(documentClasses)
        .where(eq(documentClasses.codeGroupId, documentClassToDelete[0].codeGroupId))
        .limit(1)

      // 더 이상 Document Class가 없으면 Code Group도 삭제
      if (remainingClasses.length === 0) {
        await db
          .delete(codeGroups)
          .where(eq(codeGroups.id, documentClassToDelete[0].codeGroupId))
      }
    }

    revalidatePath("/evcp/docu-list-rule/document-class")
    revalidatePath("/evcp/docu-list-rule/code-groups")
    
    return {
      success: true,
      data: deletedDocumentClass,
      message: "Document Class and its options deleted successfully"
    }
  } catch (error) {
    console.error("Error deleting document class:", error)
    return {
      success: false,
      error: "Failed to delete document class"
    }
  }
}

// Document Class 옵션 목록 조회
export async function getDocumentClassSubOptions(documentClassId: 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 = 1000, sort, search } = input || {}
    const offset = (page - 1) * perPage

    // 기본 조건: documentClassId
    let whereConditions = eq(documentClassOptions.documentClassId, documentClassId)
    
    // 검색 조건
    if (search) {
      const searchTerm = `%${search}%`
      whereConditions = sql`${whereConditions} AND (
        ${documentClassOptions.optionCode} ILIKE ${searchTerm} OR
        ${documentClassOptions.description} ILIKE ${searchTerm}
      )`
    }

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

    const data = await db
      .select({
        id: documentClassOptions.id,
        documentClassId: documentClassOptions.documentClassId,
        description: documentClassOptions.description,
        optionCode: documentClassOptions.optionCode,
        sdq: documentClassOptions.sdq,
        isActive: documentClassOptions.isActive,
        createdAt: documentClassOptions.createdAt,
        updatedAt: documentClassOptions.updatedAt,
      })
      .from(documentClassOptions)
      .where(whereConditions)
      .orderBy(orderBy)
      .limit(perPage)
      .offset(offset)

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

// Document Class 옵션 생성
export async function createDocumentClassOptionItem(input: {
  documentClassId: number
  optionCode: string
  description?: string
}) {
  try {
    // Document Class 정보 조회하여 Value 가져오기
    const documentClass = await db
      .select({ value: documentClasses.value })
      .from(documentClasses)
      .where(eq(documentClasses.id, input.documentClassId))
      .limit(1)

    if (!documentClass.length) {
      return {
        success: false,
        error: "Document Class not found"
      }
    }



    // 사용자가 입력한 코드를 그대로 사용
    const userOptionCode = input.optionCode.toUpperCase().trim()
    
    // 같은 Document Class 내에서 코드 중복 체크
    const existingOption = await db
      .select({ id: documentClassOptions.id })
      .from(documentClassOptions)
      .where(
        and(
          eq(documentClassOptions.documentClassId, input.documentClassId),
          eq(documentClassOptions.optionCode, userOptionCode)
        )
      )
      .limit(1)

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

    // 해당 Document Class의 최대 sdq 값 찾기
    const maxSdqResult = await db
      .select({ maxSdq: sql<number>`COALESCE(MAX(${documentClassOptions.sdq}), 0)` })
      .from(documentClassOptions)
      .where(eq(documentClassOptions.documentClassId, input.documentClassId))

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

    const [newOption] = await db
      .insert(documentClassOptions)
      .values({
        documentClassId: input.documentClassId,
        description: input.description || "",
        optionCode: userOptionCode,
        sdq: nextSdq,
        isActive: true,
      })
      .returning({ id: documentClassOptions.id })

    revalidatePath("/evcp/docu-list-rule/document-class")
    
    return {
      success: true,
      data: newOption,
      message: "Document Class option created successfully"
    }
  } catch (error) {
    console.error("Error creating document class option:", error)
    return {
      success: false,
      error: "Failed to create document class option"
    }
  }
}

// Document Class 옵션 수정
export async function updateDocumentClassOption(input: {
  id: number
  optionCode?: string
  sdq?: number
}) {
  try {
    // 기존 옵션 조회하여 documentClassId 가져오기
    const currentOption = await db
      .select({ documentClassId: documentClassOptions.documentClassId })
      .from(documentClassOptions)
      .where(eq(documentClassOptions.id, input.id))
      .limit(1)

    if (!currentOption.length) {
      return {
        success: false,
        error: "옵션을 찾을 수 없습니다."
      }
    }

    // optionCode가 제공된 경우에만 중복 체크 및 업데이트
    if (input.optionCode) {
      const userOptionCode = input.optionCode.toUpperCase().trim()
      
      // 같은 Document Class 내에서 코드 중복 체크 (자신 제외)
      const existingOption = await db
        .select({ id: documentClassOptions.id })
        .from(documentClassOptions)
        .where(
          and(
            eq(documentClassOptions.documentClassId, currentOption[0].documentClassId),
            eq(documentClassOptions.optionCode, userOptionCode)
          )
        )
        .limit(1)

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

    // 업데이트할 데이터 준비
    const updateData: any = {
      updatedAt: new Date(),
    }
    
    if (input.optionCode) {
      updateData.description = input.optionCode.toUpperCase().trim()
      updateData.optionCode = input.optionCode.toUpperCase().trim()
    }
    
    if (input.sdq !== undefined) {
      updateData.sdq = input.sdq
    }

    const [updatedOption] = await db
      .update(documentClassOptions)
      .set(updateData)
      .where(eq(documentClassOptions.id, input.id))
      .returning({ id: documentClassOptions.id })

    revalidatePath("/evcp/docu-list-rule/document-class")
    
    return {
      success: true,
      data: updatedOption,
      message: "Document Class option updated successfully"
    }
  } catch (error) {
    console.error("Error updating document class option:", error)
    return {
      success: false,
      error: "Failed to update document class option"
    }
  }
}

// Document Class 옵션 삭제
export async function deleteDocumentClassOption(id: number) {
  try {
    const [deletedOption] = await db
      .delete(documentClassOptions)
      .where(eq(documentClassOptions.id, id))
      .returning({ id: documentClassOptions.id })

    revalidatePath("/evcp/docu-list-rule/document-class")
    
    return {
      success: true,
      data: deletedOption,
      message: "Document Class option deleted successfully"
    }
  } catch (error) {
    console.error("Error deleting document class option:", error)
    return {
      success: false,
      error: "Failed to delete document class option"
    }
  }
}

// API 응답 타입
export interface ScheduleSetting {
  COL_NM: string;
  DC_OBX_USE_YN: string;
  PROJ_COL_NM: string;
  PROJ_COL_NM_EN: string;
  SCD_VIEW_MGNT: string;
  USE_YN1: string;
  USE_YN2: string;
}

// 프로젝트 일정 설정을 가져오는 함수
export async function getProjectKindScheduleSetting(projectCode: string): Promise<ScheduleSetting[]> {
  try {
    const response = await fetch(
      `http://60.100.99.217/DDP/Services/VNDRService.svc/GetProjectKindScheduleSetting?PROJ_NO=${projectCode}`,
      {
        method: 'GET',
        headers: {
          'Content-Type': 'application/json',
        },
      }
    )

    if (!response.ok) {
      throw new Error('Failed to fetch schedule settings')
    }

    const data = await response.json()
    return data.GetProjectKindScheduleSettingResult || []
  } catch (error) {
    console.error('Error fetching schedule settings:', error)
    return []
  }
}

/**
 * 프로젝트의 Document Class와 해당 Stage 옵션 매핑 조회
 * @param projectCode 프로젝트 코드 (예: "SN2190")
 * @returns Document Class별 허용 Stage 목록 맵
 */
export async function getProjectDocumentClassStages(projectCode: string): Promise<Record<string, string[]>> {
  try {
    // 1. 프로젝트 ID 조회
    const project = await db
      .select({ id: projects.id })
      .from(projects)
      .where(eq(projects.code, projectCode))
      .limit(1);

    if (!project.length) {
      console.warn(`[getProjectDocumentClassStages] 프로젝트를 찾을 수 없습니다: ${projectCode}`);
      return {};
    }

    const projectId = project[0].id;

    // 2. 프로젝트의 모든 Document Class와 옵션 조회
    const documentClassesWithOptions = await db
      .select({
        docClassValue: documentClasses.value,
        optionCode: documentClassOptions.optionCode,
      })
      .from(documentClasses)
      .leftJoin(
        documentClassOptions,
        eq(documentClasses.id, documentClassOptions.documentClassId)
      )
      .where(
        and(
          eq(documentClasses.projectId, projectId),
          eq(documentClasses.isActive, true),
          eq(documentClassOptions.isActive, true)
        )
      )
      .orderBy(documentClasses.value, documentClassOptions.sdq);

    // 3. Document Class별로 Stage 목록 그룹핑
    const stageMap: Record<string, string[]> = {};

    for (const row of documentClassesWithOptions) {
      if (!row.docClassValue || !row.optionCode) continue;

      if (!stageMap[row.docClassValue]) {
        stageMap[row.docClassValue] = [];
      }

      stageMap[row.docClassValue].push(row.optionCode);
    }

    console.log(`[getProjectDocumentClassStages] ${projectCode}: ${Object.keys(stageMap).length}개 Document Class, 총 ${Object.values(stageMap).flat().length}개 Stage 옵션`);

    return stageMap;
  } catch (error) {
    console.error('[getProjectDocumentClassStages] 오류:', error);
    return {};
  }
}