/* eslint-disable @typescript-eslint/no-explicit-any */

/* IMPORT */
import {
  asc,
  count,
  desc,
  eq,
} from 'drizzle-orm';
import { PgTransaction } from 'drizzle-orm/pg-core';
import {
  regEvalCriteria,
  regEvalCriteriaDetails,
  regEvalCriteriaView,
  type NewRegEvalCriteria,
  type NewRegEvalCriteriaDetails,
} from '@/db/schema';

// ----------------------------------------------------------------------------------------------------

/* SELECT VIEW TRANSACTION */
async function selectRegEvalCriteria(
  tx: PgTransaction<any, any, any>,
  params: {
    where?: any;
    orderBy?: (ReturnType<typeof asc> | ReturnType<typeof desc>)[];
    offset?: number;
    limit?: number;
  },
) {
  const {
    where,
    orderBy,
    offset = 0,
    limit = 10,
  } = params;
  const result = await tx
    .select()
    .from(regEvalCriteriaView)
    .where(where)
    .orderBy(...(orderBy ?? []))
    .offset(offset)
    .limit(limit);

  return result;
}

/* SELECT COUNT TRANSACTION */
async function countRegEvalCriteria(
  tx: PgTransaction<any, any, any>,
  where?: any,
) {
  const result = await tx
    .select({ count: count() })
    .from(regEvalCriteriaView)
    .where(where);
  
  return result[0]?.count ?? 0;
}

/* SELECT JOIN TRANSACTION */
async function selectRegEvalCriteriaWithDetails(
  tx: PgTransaction<any, any, any>,
  id: number,
) {
  const criteria = await tx
    .select()
    .from(regEvalCriteria)
    .where(eq(regEvalCriteria.id, id));

  if (!criteria[0]) {
    return null;
  }

  const details = await tx
    .select()
    .from(regEvalCriteriaDetails)
    .where(eq(regEvalCriteriaDetails.criteriaId, id))
    .orderBy(regEvalCriteriaDetails.orderIndex);

  return {
    ...criteria[0],
    criteriaDetails: details,
  };
}

// ----------------------------------------------------------------------------------------------------

/* INSERT CRITERIA TRANSACTION */
async function insertRegEvalCriteria(
  tx: PgTransaction<any, any, any>,
  data: NewRegEvalCriteria,
) {
  const [insertRes] = await tx
    .insert(regEvalCriteria)
    .values(data)
    .returning();

  return insertRes;
}

/* INSERT CRITERIA DETAILS TRANSACTION */
async function insertRegEvalCriteriaDetails(
  tx: PgTransaction<any, any, any>,
  data: NewRegEvalCriteriaDetails,
) {
  const [insertRes] = await tx
    .insert(regEvalCriteriaDetails)
    .values(data)
    .returning();

  return insertRes;
}

// ----------------------------------------------------------------------------------------------------

/* UPDATE CRITERIA TRANSACTION */
async function updateRegEvalCriteria(
  tx: PgTransaction<any, any, any>,
  criteriaId: number,
  data: Partial<NewRegEvalCriteria>,
) {
  const [updateRes] = await tx
    .update(regEvalCriteria)
    .set(data)
    .where(eq(regEvalCriteria.id, criteriaId))
    .returning();

  return updateRes;
}

/* UPDATE CRITERIA DETAILS TRANSACTION */
async function updateRegEvalCriteriaDetails(
  tx: PgTransaction<any, any, any>,
  detailId: number,
  data: Partial<NewRegEvalCriteriaDetails>,
) {
  const [updateRes] = await tx
    .update(regEvalCriteriaDetails)
    .set(data)
    .where(eq(regEvalCriteriaDetails.id, detailId))
    .returning();

  return updateRes;
}

// ----------------------------------------------------------------------------------------------------

/* DELETE CRITERIA TRANSACTION */
async function deleteRegEvalCriteria(
  tx: PgTransaction<any, any, any>,
  criteriaId: number,
) {
  const [deleteRes] = await tx
    .delete(regEvalCriteria)
    .where(eq(regEvalCriteria.id, criteriaId))
    .returning();

  return deleteRes;
}

/* DELETE CRITERIA TRANSACTION */
async function deleteRegEvalCriteriaDetails(
  tx: PgTransaction<any, any, any>,
  datailId: number,
) {
  const [deleteRes] = await tx
    .delete(regEvalCriteriaDetails)
    .where(eq(regEvalCriteriaDetails.id, datailId))
    .returning();

  return deleteRes;
}

/* DELETE ALL TRANSACTION */
async function deleteAllRegEvalCriteria(
  tx: PgTransaction<any, any, any>,
) {
  const [deleteRes] = await tx.delete(regEvalCriteria);

  return deleteRes;
}

// ----------------------------------------------------------------------------------------------------

/* EXPORT */
export {
  countRegEvalCriteria,
  deleteAllRegEvalCriteria,
  deleteRegEvalCriteria,
  deleteRegEvalCriteriaDetails,
  insertRegEvalCriteria,
  insertRegEvalCriteriaDetails,
  selectRegEvalCriteria,
  selectRegEvalCriteriaWithDetails,
  updateRegEvalCriteria,
  updateRegEvalCriteriaDetails,
};