'use server'

import { and, asc, desc, ilike, or, sql, eq } from 'drizzle-orm';
import db from '@/db/db';
import { filterColumns } from "@/lib/filter-columns";
import { materialSearchView } from "@/db/schema/items";

// 자재그룹 뷰의 컬럼 타입 정의
type MaterialGroupColumn = keyof typeof materialSearchView.$inferSelect;

export interface GetMaterialGroupsInput {
  page: number;
  perPage: number;
  search?: string;
  sort: Array<{
    id: MaterialGroupColumn;
    desc: boolean;
  }>;
  filters?: any[];
  joinOperator: 'and' | 'or';
}

/**
 * 자재그룹 목록을 조회합니다.
 * materialSearchView를 사용하여 MATKL(자재그룹코드)와 ATWTB(자재그룹 설명)의 고유한 조합을 조회
 */
export async function getMaterialGroups(input: GetMaterialGroupsInput) {
  const safePerPage = Math.min(input.perPage, 100);
  
  try {
    const offset = (input.page - 1) * safePerPage;
    
    // 고급 필터링
    const advancedWhere = filterColumns({
      table: materialSearchView,
      filters: (input.filters || []) as any,
      joinOperator: input.joinOperator,
    });

    // 전역 검색 - 주요 컬럼들에 대해 검색
    let globalWhere;
    if (input.search) {
      const s = `%${input.search}%`;
      globalWhere = or(
        ilike(materialSearchView.materialGroupCode, s),      // 자재그룹코드
        ilike(materialSearchView.materialGroupDesc, s),     // 자재그룹 설명
        ilike(materialSearchView.displayText, s),            // 표시 텍스트
      );
    }

    const finalWhere = and(advancedWhere, globalWhere);

    // 정렬 처리 - 타입 안전하게 처리
    const orderBy = input.sort.length > 0
      ? input.sort.map((item) => {
          const column = materialSearchView[item.id];
          return item.desc ? desc(column) : asc(column);
        })
      : [asc(materialSearchView.materialGroupCode)];

    // 데이터 조회
    const { data, total } = await db.transaction(async (tx) => {
      const data = await tx
        .select({
          materialGroupCode: materialSearchView.materialGroupCode,
          materialGroupDesc: materialSearchView.materialGroupDesc,
          displayText: materialSearchView.displayText,
        })
        .from(materialSearchView)
        .where(finalWhere)
        .orderBy(...orderBy)
        .offset(offset)
        .limit(safePerPage);

      const totalResult = await tx
        .select({
          count: sql<number>`count(*)`
        })
        .from(materialSearchView)
        .where(finalWhere);
      
      const total = Number(totalResult[0]?.count) || 0;
      return { data, total };
    });

    const pageCount = Math.ceil(total / safePerPage);
    return { data, pageCount };
  } catch (err) {
    console.error('Error in getMaterialGroups:', err);
    return { data: [], pageCount: 0 };
  }
}

/**
 * 무한 스크롤을 위한 자재그룹 조회 (페이지네이션 없음)
 */
export interface GetMaterialGroupsInfiniteInput extends Omit<GetMaterialGroupsInput, 'page' | 'perPage'> {
  limit?: number; // 무한 스크롤용 추가 옵션
}

export async function getMaterialGroupsInfinite(input: GetMaterialGroupsInfiniteInput) {
  try {
    // 고급 필터링
    const advancedWhere = filterColumns({
      table: materialSearchView,
      filters: (input.filters || []) as any,
      joinOperator: input.joinOperator || "and",
    });

    // 전역 검색
    let globalWhere;
    if (input.search) {
      const s = `%${input.search}%`;
      globalWhere = or(
        ilike(materialSearchView.materialGroupCode, s),
        ilike(materialSearchView.materialGroupDesc, s),
        ilike(materialSearchView.displayText, s),
      );
    }

    const finalWhere = and(advancedWhere, globalWhere);

    // 정렬 처리 - 타입 안전하게 처리
    const orderBy = input.sort.length > 0
      ? input.sort.map((item) => {
          const column = materialSearchView[item.id];
          return item.desc ? desc(column) : asc(column);
        })
      : [asc(materialSearchView.materialGroupCode)];

    // 전체 데이터 조회 (클라이언트에서 가상화 처리)
    const data = await db
      .select({
        materialGroupCode: materialSearchView.materialGroupCode,
        materialGroupDesc: materialSearchView.materialGroupDesc,
        displayText: materialSearchView.displayText,
      })
      .from(materialSearchView)
      .where(finalWhere)
      .orderBy(...orderBy);

    return { data };
  } catch (err) {
    console.error('Error in getMaterialGroupsInfinite:', err);
    return { data: [] };
  }
}

/**
 * 특정 자재그룹 상세 정보 조회
 */
export async function getMaterialGroupDetail(materialGroupCode: string) {
  try {
    const materialGroup = await db
      .select()
      .from(materialSearchView)
      .where(eq(materialSearchView.materialGroupCode, materialGroupCode))
      .limit(1);

    return materialGroup.length > 0 ? materialGroup[0] : null;
  } catch (err) {
    console.error('Error in getMaterialGroupDetail:', err);
    return null;
  }
}