'use server';

import db from '@/db/db';
import { revalidateI18nPaths } from '@/lib/revalidate';
import {
  and,
  asc,
  count,
  desc,
  eq,
  ilike,
  or,
} from 'drizzle-orm';

import { approvalTemplateCategories, approvalTemplates } from '@/db/schema/knox/approvals';

// ---------------------------------------------
// Types
// ---------------------------------------------

export type ApprovalTemplateCategory = typeof approvalTemplateCategories.$inferSelect;

// ---------------------------------------------
// Revalidation helpers
// ---------------------------------------------
async function revalidateApprovalTemplateCategoriesPaths() {
  await revalidateI18nPaths('/evcp/approval/template');
}

// ---------------------------------------------
// List & read helpers
// ---------------------------------------------

interface ListInput {
  page: number;
  perPage: number;
  search?: string;
  filters?: Record<string, unknown>[];
  joinOperator?: 'and' | 'or';
  sort?: Array<{ id: string; desc: boolean }>;
}

export async function getApprovalTemplateCategoryList(input: ListInput) {
  const offset = (input.page - 1) * input.perPage;

  /* ------------------------------------------------------------------
   * WHERE 절 구성
   * ----------------------------------------------------------------*/
  const advancedWhere = input.filters ? and(
    ...input.filters.map(filter => {
      // 간단한 필터링 로직 - 실제로는 filterColumns 유틸리티 사용 가능
      return eq(approvalTemplateCategories.isActive, true);
    })
  ) : undefined;

  // 전역 검색 (name, description)
  let globalWhere;
  if (input.search) {
    const s = `%${input.search}%`;
    globalWhere = or(
      ilike(approvalTemplateCategories.name, s),
      ilike(approvalTemplateCategories.description, s),
    );
  }

  const conditions = [];
  if (advancedWhere) conditions.push(advancedWhere);
  if (globalWhere) conditions.push(globalWhere);

  const where =
    conditions.length === 0
      ? undefined
      : conditions.length === 1
      ? conditions[0]
      : and(...conditions);

  /* ------------------------------------------------------------------
   * ORDER BY 절 구성
   * ----------------------------------------------------------------*/
  let orderBy;
  try {
    orderBy = input.sort && input.sort.length > 0
      ? input.sort
          .map((item) => {
            if (!item || !item.id || typeof item.id !== 'string') return null;
            if (!(item.id in approvalTemplateCategories)) return null;
            // eslint-disable-next-line @typescript-eslint/ban-ts-comment
            // @ts-ignore
            const col = approvalTemplateCategories[item.id];
            return item.desc ? desc(col) : asc(col);
          })
          .filter((v): v is Exclude<typeof v, null> => v !== null)
      : [asc(approvalTemplateCategories.sortOrder), asc(approvalTemplateCategories.name)];
  } catch {
    orderBy = [asc(approvalTemplateCategories.sortOrder), asc(approvalTemplateCategories.name)];
  }

  /* ------------------------------------------------------------------
   * 데이터 조회
   * ----------------------------------------------------------------*/
  const data = await db
    .select()
    .from(approvalTemplateCategories)
    .where(where)
    .orderBy(...orderBy)
    .limit(input.perPage)
    .offset(offset);

  const totalResult = await db
    .select({ count: count() })
    .from(approvalTemplateCategories)
    .where(where);

  const total = totalResult[0]?.count ?? 0;
  const pageCount = Math.ceil(total / input.perPage);

  return {
    data,
    pageCount,
  };
}

// ----------------------------------------------------
// Get single category
// ----------------------------------------------------
export async function getApprovalTemplateCategory(id: string): Promise<ApprovalTemplateCategory | null> {
  const [category] = await db
    .select()
    .from(approvalTemplateCategories)
    .where(eq(approvalTemplateCategories.id, id))
    .limit(1);

  return category || null;
}

// ----------------------------------------------------
// Create category
// ----------------------------------------------------
interface CreateInput {
  name: string;
  description?: string;
  sortOrder?: number;
  createdBy: number;
}

export async function createApprovalTemplateCategory(data: CreateInput): Promise<ApprovalTemplateCategory> {
  // 중복 이름 체크
  const existing = await db
    .select({ id: approvalTemplateCategories.id })
    .from(approvalTemplateCategories)
    .where(and(
      eq(approvalTemplateCategories.name, data.name),
      eq(approvalTemplateCategories.isActive, true)
    ))
    .limit(1);

  if (existing.length > 0) {
    throw new Error('이미 존재하는 카테고리 이름입니다.');
  }

  const [newCategory] = await db
    .insert(approvalTemplateCategories)
    .values({
      name: data.name,
      description: data.description,
      sortOrder: data.sortOrder ?? 0,
      createdBy: data.createdBy,
      updatedBy: data.createdBy,
    })
    .returning();

  await revalidateApprovalTemplateCategoriesPaths();
  return newCategory;
}

// ----------------------------------------------------
// Update category
// ----------------------------------------------------
interface UpdateInput {
  name?: string;
  description?: string;
  isActive?: boolean;
  sortOrder?: number;
  updatedBy: number;
}

export async function updateApprovalTemplateCategory(id: string, data: UpdateInput): Promise<ApprovalTemplateCategory> {
  const existing = await getApprovalTemplateCategory(id);
  if (!existing) throw new Error('카테고리를 찾을 수 없습니다.');

  // 이름 변경 시 중복 체크
  if (data.name && data.name !== existing.name) {
    const existingName = await db
      .select({ id: approvalTemplateCategories.id })
      .from(approvalTemplateCategories)
      .where(and(
        eq(approvalTemplateCategories.name, data.name),
        eq(approvalTemplateCategories.isActive, true)
      ))
      .limit(1);

    if (existingName.length > 0) {
      throw new Error('이미 존재하는 카테고리 이름입니다.');
    }
  }

  await db
    .update(approvalTemplateCategories)
    .set({
      name: data.name ?? existing.name,
      description: data.description === undefined ? existing.description : data.description,
      isActive: data.isActive ?? existing.isActive,
      sortOrder: data.sortOrder ?? existing.sortOrder,
      updatedAt: new Date(),
      updatedBy: data.updatedBy,
    })
    .where(eq(approvalTemplateCategories.id, id));

  const result = await getApprovalTemplateCategory(id);
  if (!result) throw new Error('업데이트된 카테고리를 조회할 수 없습니다.');
  await revalidateApprovalTemplateCategoriesPaths();
  return result;
}

// ----------------------------------------------------
// Delete category (soft delete - 비활성화)
// ----------------------------------------------------
export async function deleteApprovalTemplateCategory(id: string, updatedBy: number): Promise<{ success: boolean; error?: string }> {
  try {
    const existing = await getApprovalTemplateCategory(id);
    if (!existing) return { success: false, error: '카테고리를 찾을 수 없습니다.' };

    // 카테고리가 사용 중인지 확인 (approvalTemplates에서 참조 확인)
    const usageCount = await db
      .select({ count: count() })
      .from(approvalTemplates)
      .where(eq(approvalTemplates.category, existing.name));

    if (usageCount[0]?.count > 0) {
      return { success: false, error: '사용 중인 카테고리는 삭제할 수 없습니다.' };
    }

    await db
      .update(approvalTemplateCategories)
      .set({
        isActive: false,
        updatedAt: new Date(),
        updatedBy,
      })
      .where(eq(approvalTemplateCategories.id, id));

    await revalidateApprovalTemplateCategoriesPaths();
    return { success: true };
  } catch (error) {
    return {
      success: false,
      error: error instanceof Error ? error.message : '삭제에 실패했습니다.',
    };
  }
}

// ----------------------------------------------------
// Get all active categories (드롭다운용)
// ----------------------------------------------------
export async function getActiveApprovalTemplateCategories(): Promise<ApprovalTemplateCategory[]> {
  return await db
    .select()
    .from(approvalTemplateCategories)
    .where(eq(approvalTemplateCategories.isActive, true))
    .orderBy(asc(approvalTemplateCategories.sortOrder), asc(approvalTemplateCategories.name));
}