import { ConvertQueryResultToJSON } from "@/lib/actions";
import { NoteStatusMap } from "@/models/projects/notesMap";
import { TableService } from "@/services/table/service";
import { PoolConnection, RowDataPacket } from "mysql2/promise";

export default async function fetchAllProjects(
  con: PoolConnection,
  filters: Record<string, any>,
) {
  const tableService = new TableService(con);

  const orWhere = filters.search.fields.map((f) => `UPPER(D.${f}) LIKE ?`);
  const params: any[] = filters.search.fields.map(
    (f) => `%${filters.search.text}%`,
  );

  const count = await tableService.query({
    table: "DOMAINS D",
    select: ["COUNT(*) AS TOTAL"],
    orWhere,
    params: params,
  });

  params.unshift(NoteStatusMap.OPEN);

  const domains = await tableService.query({
    table: "DOMAINS D",
    withCTE: [
      {
        name: "NOTES_CTE",
        query: {
          table: "DOMAINNOTES DN",
          select: ["DOMAIN", "COUNT(ID) AS NOTES"],
          where: [`STATUS = ?`],
          groupBy: ["DN.DOMAIN"],
        },
      },
    ],
    select: ["D.*", "NCTE.NOTES"],
    joins: ["LEFT JOIN NOTES_CTE NCTE ON NCTE.DOMAIN = D.ID"],
    orWhere,
    orderBy: ["CASE WHEN NCTE.NOTES > 0 THEN 1 ELSE 2 END", "D.NAME"],
    limit: [
      filters.pagination.pageSize,
      filters.pagination.pageIndex * filters.pagination.pageSize,
    ],
    params,
  });

  if (!domains.operation) {
    return {
      operation: false,
      message: domains.message,
    };
  }

  return {
    operation: true,
    data: {
      list: ConvertQueryResultToJSON(domains.data),
      count: count.data[0].TOTAL,
    },
  };
}

export async function fetchProject(
  con: PoolConnection,
  name: string,
  dict: Dictionary,
) {
  const query = `
    WITH
      NOTES_CTE AS (
        SELECT 
          DOMAIN,
          JSON_ARRAYAGG(
            JSON_OBJECT(
              "id", DN.ID,
              "title", DN.TITLE,
              "note", DN.NOTE,
              "status", DN.STATUS,
              "updatedAt", DN.UPDATED_AT
            )
          ) AS NOTES
        FROM DOMAINNOTES DN
        WHERE STATUS = 1
        GROUP BY DN.DOMAIN
      )
    SELECT 
      D.*, 
      NCTE.NOTES 
    FROM DOMAINS D
    LEFT JOIN NOTES_CTE NCTE ON NCTE.DOMAIN = D.ID
    WHERE D.NAME = ?
    `;

  const params = [name];

  const [result] = await con.query<RowDataPacket[]>(query, params);

  if (result.length === 0) {
    return {
      operation: false,
      message: dict.alerts.fetch_project.error.not_found,
    };
  }

  const formated = result.map((row) => {
    const notes = row.NOTES ? JSON.parse(row.NOTES) : [];

    return { ...row, NOTES: notes };
  });

  const converted = ConvertQueryResultToJSON(formated)[0];

  return {
    operation: true,
    data: converted,
  };
}