"use server";

import { sql, SQL } from "drizzle-orm";
import db from "@/db/db";
import { MATERIAL_GROUP_MASTER } from "@/db/schema/MDG/mdg";

export interface MaterialSearchItem {
  materialGroupCode: string;
  materialGroupDescription: string;
  materialGroupUom?: string;
  displayText: string; // 애플리케이션 레벨에서 계산된 필드
}

export interface MaterialSearchResult {
  success: boolean;
  data: MaterialSearchItem[];
  pagination: {
    page: number;
    perPage: number;
    total: number;
    pageCount: number;
    hasNextPage: boolean;
    hasPrevPage: boolean;
  };
}

/**
 * 자재 검색 함수 - material_search_view에서 검색
 */
export async function searchMaterialsForSelector(
  query: string,
  page: number = 1,
  perPage: number = 10
): Promise<MaterialSearchResult> {
  try {
    const offset = (page - 1) * perPage;

    // 검색 조건
    let searchWhere: SQL<unknown> | undefined;
    if (query.trim()) {
      const searchPattern = `%${query.trim()}%`;
      searchWhere = sql`(
        ${MATERIAL_GROUP_MASTER.materialGroupCode} ILIKE ${searchPattern} OR 
        ${MATERIAL_GROUP_MASTER.materialGroupDescription} ILIKE ${searchPattern}
      )`;
    }

    const { data, total } = await db.transaction(async (tx) => {
      // 데이터 조회
      const data = await tx
        .select({
          materialGroupCode: MATERIAL_GROUP_MASTER.materialGroupCode,
          materialGroupDescription: MATERIAL_GROUP_MASTER.materialGroupDescription,
          materialGroupUom: MATERIAL_GROUP_MASTER.materialGroupUom,
        })
        .from(MATERIAL_GROUP_MASTER)
        .where(searchWhere)
        .orderBy(MATERIAL_GROUP_MASTER.materialGroupCode, MATERIAL_GROUP_MASTER.materialGroupDescription)
        .limit(perPage)
        .offset(offset);

      // 총 개수 조회
      const countResult = await tx
        .select({ count: sql<number>`count(*)` })
        .from(MATERIAL_GROUP_MASTER)
        .where(searchWhere);

      const total = countResult[0]?.count || 0;

      return {
        data: data.map((row) => ({
          materialGroupCode: row.materialGroupCode,
          materialGroupDescription: row.materialGroupDescription,
          materialGroupUom: row.materialGroupUom,
          displayText: `${row.materialGroupCode || ''} - ${row.materialGroupDescription || ''}`, // 애플리케이션 레벨에서 생성
        })),
        total,
      };
    });

    const pageCount = Math.ceil(total / perPage);

    return {
      success: true,
      data,
      pagination: {
        page,
        perPage,
        total,
        pageCount,
        hasNextPage: page < pageCount,
        hasPrevPage: page > 1,
      },
    };
  } catch (error) {
    console.error("자재 검색 오류:", error);
    return {
      success: false,
      data: [],
      pagination: {
        page: 1,
        perPage: 10,
        total: 0,
        pageCount: 0,
        hasNextPage: false,
        hasPrevPage: false,
      },
    };
  }
}