import { Repository } from "typeorm";
import { IBasicCompany } from "../interfaces";
import { query } from "express";

export async function getLocalsCompanyInformationTable(
  repository: Repository<any>,
  { company, status, visible, lazyEvent }: IBasicCompany,
) {
  try {
    const queryBuilder = repository
      .createQueryBuilder("locals_company_information_for_the_table")
      .skip(lazyEvent.first)
      .take(lazyEvent.rows);

    // DOC: Filtro global
    if (lazyEvent.filters["global"] && lazyEvent.filters["global"].value) {
      const globalValue = `%${lazyEvent.filters[
        "global"
      ].value.toLowerCase()}%`;
      queryBuilder.andWhere(
        "(LOWER(locals_company_information_for_the_table.code) LIKE :globalValue OR LOWER(locals_company_information_for_the_table.name) LIKE :globalValue OR LOWER(locals_company_information_for_the_table.address) LIKE :globalValue OR LOWER(locals_company_information_for_the_table.partner_name) LIKE :globalValue OR LOWER(locals_company_information_for_the_table.partner_surname) LIKE :globalValue OR LOWER(locals_company_information_for_the_table.partner_full_name) LIKE :globalValue OR locals_company_information_for_the_table.partner_full_name LIKE :globalValue)",
        { globalValue },
      );
    }

    // DOC: Filtro por estado FILTRO POR DEFECTO.
    if (status !== null && status >= 0) {
      queryBuilder.andWhere(
        "locals_company_information_for_the_table.status = :status",
        {
          status,
        },
      );
    }

    // DOC: Filtro por company FILTRO POR DEFECTO.
    if (company !== null && company >= 0) {
      queryBuilder.andWhere(
        "locals_company_information_for_the_table.company = :company",
        {
          company,
        },
      );
    }

    // DOC: Filtro por visible FILTRO POR DEFECTO.
    if (visible !== null) {
      queryBuilder.andWhere(
        "locals_company_information_for_the_table.visible = :visible",
        {
          visible,
        },
      );
    }

    // DOC: Acá vienen los otros filtros, por las columnas de la tabla.
    const filters = lazyEvent.filters;
    Object.keys(filters).forEach((key) => {
      let value = filters[key].value; // DOC: Obtenemos el valor del filtro.

      if (
        (Array.isArray(value) && value.length > 0) ||
        (!Array.isArray(value) && value && key !== "global")
      ) {
        // DOC: Verificamos si el filtro tiene valor, si no es un filtro global y no es un array vacío.
        const matchMode = filters[key].matchMode; // DOC: Obtenemos el modo de coincidencia del filtro.
        if (!Array.isArray(value) && value) {
          // DOC: Si el valor no es un array y tiene valor, lo convertimos a minúsculas.
          value = filters[key].value.toLowerCase();
        }

        const accessKey =
          key.split(".").length > 1
            ? `${key}`
            : `locals_company_information_for_the_table.${key}`; // DOC: Verificamos si el filtro es un campo anidado o no.

        switch (
          matchMode // DOC: Verificamos el modo de coincidencia del filtro.
        ) {
          case "custom":
            // 📌 Filtro por created / updated (rango de fechas en UTC)
            if (key === "created" || key === "updated") {
              if (Array.isArray(value) && value.length === 2) {
                const [from, to] = value;

                if (from !== null && to === null) {
                  queryBuilder.andWhere(`${accessKey} >= :from`, {
                    from: new Date(from),
                  });
                } else if (from === null && to !== null) {
                  queryBuilder.andWhere(`${accessKey} <= :to`, {
                    to: new Date(to),
                  });
                } else if (from !== null && to !== null) {
                  queryBuilder.andWhere(`${accessKey} BETWEEN :from AND :to`, {
                    from: new Date(from),
                    to: new Date(to),
                  });
                }
              }
            }

            // 📌 Filtro por created_by (array de ids)
            if (key === "created_by") {
              if (Array.isArray(value) && value.length > 0) {
                queryBuilder.andWhere(`${accessKey} IN (:...${key})`, {
                  [key]: value,
                });
              }
            }

            // 📌 Filtro por updated_by (array de ids)
            if (key === "updated_by") {
              if (Array.isArray(value) && value.length > 0) {
                queryBuilder.andWhere(`${accessKey} IN (:...${key})`, {
                  [key]: value,
                });
              }
            }

            // 📌 Filtro por total_requests / average_rating (rango numérico)
            if (key === "total_requests" || key === "average_rating") {
              if (Array.isArray(value) && value.length === 2) {
                const [from, to] = value;

                if (from !== null && to === null) {
                  queryBuilder.andWhere(`${accessKey} >= :from`, { from });
                } else if (from === null && to !== null) {
                  queryBuilder.andWhere(`${accessKey} <= :to`, { to });
                } else if (from !== null && to !== null) {
                  queryBuilder.andWhere(`${accessKey} BETWEEN :from AND :to`, {
                    from,
                    to,
                  });
                }
              }
            }

            break;
          case "contains": // DOC: Si el modo de coincidencia es "contains".
            queryBuilder.andWhere(`LOWER(${accessKey}) LIKE :${key}`, {
              [key]: `%${value}%`,
            });
            break;
          case "equals": // DOC: Si el modo de coincidencia es "equals".
            queryBuilder.andWhere(`${accessKey} = :${key}`, { [key]: value });
            break;
          case "notEquals": // DOC: Si el modo de coincidencia es "notEquals".
            queryBuilder.andWhere(`${accessKey} != :${key}`, { [key]: value });
            break;
          case "startsWith": // DOC: Si el modo de coincidencia es "startsWith".
            queryBuilder.andWhere(`LOWER(${accessKey}) LIKE :${key}`, {
              [key]: `${value}%`,
            });
            break;
          case "endsWith": // DOC: Si el modo de coincidencia es "endsWith".
            queryBuilder.andWhere(`LOWER(${accessKey}) LIKE :${key}`, {
              [key]: `%${value}`,
            });
            break;
          case "in": // DOC: Si el modo de coincidencia es "in".
            if (Array.isArray(value)) {
              queryBuilder.andWhere(`${accessKey} IN (:...${key})`, {
                [key]: value,
              });
            }
            break;
          case "notIn": // DOC: Si el modo de coincidencia es "notIn
            if (Array.isArray(value)) {
              queryBuilder.andWhere(`${accessKey} NOT IN (:...${key})`, {
                [key]: value,
              });
            }
            break;
          case "lt": // DOC: Si el modo de coincidencia es "lt" (less than).
            queryBuilder.andWhere(`${accessKey} < :${key}`, { [key]: value });
            break;
          case "lte": // DOC: Si el modo de coincidencia es "lte" (less than or equal).
            queryBuilder.andWhere(`${accessKey} <= :${key}`, { [key]: value });
            break;
          case "gt": // DOC: Si el modo de coincidencia es "gt" (greater than).
            queryBuilder.andWhere(`${accessKey} > :${key}`, { [key]: value });
            break;
          case "gte": // DOC: Si el modo de coincidencia es "gte" (greater than or equal).
            queryBuilder.andWhere(`${accessKey} >= :${key}`, { [key]: value });
            break;
          case "between": // DOC: Si el modo de coincidencia es "between".
            if (Array.isArray(value) && value.length === 2) {
              const [from, to] = value;
              queryBuilder.andWhere(`${accessKey} BETWEEN :from AND :to`, {
                from,
                to,
              });
            }
            break;
          case "dateIs": {
            // DOC: Si el modo de coincidencia es "dateIs". Hay que tener en cuenta la zona horaria (timezone)
            const date = new Date(value);
            const start = new Date(date);
            start.setUTCHours(0, 0, 0, 0);

            const end = new Date(date);
            end.setUTCHours(23, 59, 59, 999);

            queryBuilder.andWhere(`${accessKey} BETWEEN :start AND :end`, {
              start,
              end,
            });
            break;
          }
          case "dateIsNot": {
            // DOC: Si el modo de coincidencia es "dateIsNot". Hay que tener en cuenta la zona horaria (timezone)
            const date = new Date(value);
            const start = new Date(date);
            start.setUTCHours(0, 0, 0, 0);

            const end = new Date(date);
            end.setUTCHours(23, 59, 59, 999);

            queryBuilder.andWhere(`${accessKey} NOT BETWEEN :start AND :end`, {
              start,
              end,
            });
            break;
          }
          case "dateBefore": // DOC: Si el modo de coincidencia es "dateBefore". Hay que tener en cuenta la zona horaria (timezone)
            queryBuilder.andWhere(`${accessKey} < :${key}`, {
              [key]: new Date(value),
            });
            break;
          case "dateAfter": // DOC: Si el modo de coincidencia es "dateAfter". Hay que tener en cuenta la zona horaria (timezone)
            queryBuilder.andWhere(`${accessKey} > :${key}`, {
              [key]: new Date(value),
            });
            break;
          default: // DOC: Si el modo de coincidencia no es ninguno de los anteriores.
            break;
        }
      }
    });

    // DOC: Ordenamiento de los resultados.
    if (lazyEvent.sortField) {
      const sortOrder = lazyEvent.sortOrder === 1 ? "ASC" : "DESC";
      queryBuilder.addOrderBy(
        `locals_company_information_for_the_table.${lazyEvent.sortField}`,
        sortOrder,
      );
    }

    // DOC: Ejecutamos la consulta y obtenemos los resultados.
    const [data, totalRecords] = await queryBuilder.getManyAndCount();

    // DOC: Si quieres ver la consulta generada, puedes descomentar las siguientes líneas:
    // console.log("Query:", queryBuilder.getQuery());
    // console.log("Query:", queryBuilder.getQueryAndParameters());

    return {
      data,
      totalRecords,
    };
  } catch (error) {
    return {
      data: [],
      totalRecords: 0,
    };
  }
}