"use server";

import db from "@/db/db";
import { GetChangeOrderSchema } from "./validations";
import { unstable_cache } from "@/lib/unstable-cache";
import { filterColumns } from "@/lib/filter-columns";
import {
  asc,
  desc,
  ilike,
  and,
  or,
  count,
} from "drizzle-orm";

import {
  poaDetailView,
} from "@/db/schema/contract";

/**
 * POA 목록 조회
 */
export async function getChangeOrders(input: GetChangeOrderSchema) {
  return unstable_cache(
    async () => {
      try {
        const offset = (input.page - 1) * input.perPage;

        // 1. Build where clause
        let advancedWhere;
        try {
          advancedWhere = filterColumns({
            table: poaDetailView,
            filters: input.filters,
            joinOperator: input.joinOperator,
          });
        } catch (whereErr) {
          console.error("Error building advanced where:", whereErr);
          advancedWhere = undefined;
        }

        let globalWhere;
        if (input.search) {
          try {
            const s = `%${input.search}%`;
            globalWhere = or(
              ilike(poaDetailView.contractNo, s),
              ilike(poaDetailView.originalContractName, s),
              ilike(poaDetailView.projectCode, s),
              ilike(poaDetailView.projectName, s),
              ilike(poaDetailView.vendorName, s)
            );
          } catch (searchErr) {
            console.error("Error building search where:", searchErr);
            globalWhere = undefined;
          }
        }

        // 2. Combine where clauses
        let finalWhere;
        if (advancedWhere && globalWhere) {
          finalWhere = and(advancedWhere, globalWhere);
        } else {
          finalWhere = advancedWhere || globalWhere;
        }

        // 3. Build order by
        let orderBy;
        try {
          orderBy =
            input.sort.length > 0
              ? input.sort.map((item) =>
                  item.desc
                    ? desc(poaDetailView[item.id])
                    : asc(poaDetailView[item.id])
                )
              : [desc(poaDetailView.createdAt)];
        } catch (orderErr) {
          console.error("Error building order by:", orderErr);
          orderBy = [desc(poaDetailView.createdAt)];
        }

        // 4. Execute queries
        let data = [];
        let total = 0;

        try {
          const queryBuilder = db.select().from(poaDetailView);

          if (finalWhere) {
            queryBuilder.where(finalWhere);
          }

          queryBuilder.orderBy(...orderBy);
          queryBuilder.offset(offset).limit(input.perPage);

          data = await queryBuilder;

          const countBuilder = db
            .select({ count: count() })
            .from(poaDetailView);

          if (finalWhere) {
            countBuilder.where(finalWhere);
          }

          const countResult = await countBuilder;
          total = countResult[0]?.count || 0;
        } catch (queryErr) {
          console.error("Query execution failed:", queryErr);
          throw queryErr;
        }

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

        return { data, pageCount };
      } catch (err) {
        console.error("Error in getChangeOrders:", err);
        if (err instanceof Error) {
          console.error("Error message:", err.message);
          console.error("Error stack:", err.stack);
        }
        return { data: [], pageCount: 0 };
      }
    },
    [JSON.stringify(input)],
    {
      revalidate: 3600,
      tags: [`poa`],
    }
  )();
}