"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,
  hideCode: boolean = false
): Promise<MaterialSearchResult> {
  try {
    const offset = (page - 1) * perPage;

    // 검색 조건
    let searchWhere: SQL<unknown> | undefined;
    if (query.trim()) {
      const searchPattern = `%${query.trim()}%`;
      if (hideCode) {
        // 코드 보호 모드: 설명만 검색
        searchWhere = sql`${MATERIAL_GROUP_MASTER.materialGroupDescription} ILIKE ${searchPattern}`;
      } else {
        // 일반 모드: 코드와 설명 모두 검색
        searchWhere = sql`(
          ${MATERIAL_GROUP_MASTER.materialGroupCode} ILIKE ${searchPattern} OR
          ${MATERIAL_GROUP_MASTER.materialGroupDescription} ILIKE ${searchPattern}
        )`;
      }
    }

    const { data, total } = await db.transaction(async (tx) => {
      let data;
      let totalCount;

      if (hideCode) {
        // 코드 보호 모드: 같은 이름의 자재 중복 제거 (DISTINCT ON)
        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.materialGroupDescription, MATERIAL_GROUP_MASTER.materialGroupCode)
          .$dynamic(); // Drizzle에서 DISTINCT ON을 위해 dynamic 쿼리 사용

        // 실제로는 DISTINCT ON을 위해 raw SQL 사용
        const distinctQuery = sql`
          SELECT DISTINCT ON (material_group_description)
            material_group_code, material_group_description, material_group_uom
          FROM ${MATERIAL_GROUP_MASTER}
          ${searchWhere ? sql`WHERE ${searchWhere}` : sql``}
          ORDER BY material_group_description, material_group_code
          LIMIT ${perPage} OFFSET ${offset}
        `;

        const distinctResult = await tx.execute(distinctQuery);
        data = distinctResult.rows as {
          material_group_code: string;
          material_group_description: string;
          material_group_uom: string | null;
        }[];

        // 총 개수도 DISTINCT로 계산
        const countQuery = sql`
          SELECT COUNT(DISTINCT material_group_description) as count
          FROM ${MATERIAL_GROUP_MASTER}
          ${searchWhere ? sql`WHERE ${searchWhere}` : sql``}
        `;

        const countResult = await tx.execute(countQuery);
        totalCount = Number(countResult.rows[0]?.count) || 0;
      } else {
        // 일반 모드: 기존 방식 유지
        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);

        totalCount = countResult[0]?.count || 0;
      }

      return {
        data: data.map((row: {
          material_group_code?: string;
          materialGroupCode?: string;
          material_group_description?: string;
          materialGroupDescription?: string;
          material_group_uom?: string | null;
          materialGroupUom?: string | null;
        }) => ({
          materialGroupCode: row.material_group_code || row.materialGroupCode,
          materialGroupDescription: row.material_group_description || row.materialGroupDescription,
          materialGroupUom: row.material_group_uom || row.materialGroupUom,
          displayText: hideCode
            ? `${(row.material_group_description || row.materialGroupDescription) || ''}` // 코드 보호 모드: 설명만
            : `${(row.material_group_code || row.materialGroupCode) || ''} - ${(row.material_group_description || row.materialGroupDescription) || ''}`, // 일반 모드: 코드 + 설명
        })),
        total: totalCount,
      };
    });

    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,
      },
    };
  }
}