import { ResultSetHeader, RowDataPacket } from "mysql2";
import DB from "../config/database/db";
import log from "../config/log";
import laundryRequestsTypes from "../schemas/laundryRequests.schema";
import CONSTANTS from "../config/constants";

let laundryRequestsDB: any = {};

laundryRequestsDB.add = async ({
  clientId,
  tenantId,
  propId,
  weight,
  noOfClothes,
  laundryDate,
  recordedById,
  recordedByName,
}: laundryRequestsTypes) => {
  const query = `Insert into LaundryRequests (clientId, tenantId, propId, weight, noOfClothes, laundryDate, recordedById, recordedByName) values (?, ?, ?, ?, ?, ?, ?, ?)`;
  const data = [clientId, tenantId, propId, weight, noOfClothes, laundryDate, recordedById, recordedByName];

  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

laundryRequestsDB.getByTenantIdAndClientIdAndDate = async ({
  tenantId,
  clientId,
  startDate,
  endDate,
}: laundryRequestsTypes & { startDate: string; endDate: string }) => {
  const query = `SELECT id, clientId, propId, tenantId, ROUND(weight, 2) as weight, noOfClothes, laundryDate, recordedById, recordedByName, createdAt, updatedAt FROM LaundryRequests WHERE tenantId = ? AND clientId = ? AND DATE(laundryDate) >= ? AND DATE(laundryDate) <= ? order by id desc`;
  const data = [tenantId, clientId, startDate, endDate];

  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows;
  else return false;
};

laundryRequestsDB.getByTenantIdAndClientIdAndDateForTenant = async ({
  tenantId,
  clientId,
  // startDate,
  // endDate,
  pageNum,
  limit,
}: laundryRequestsTypes & { startDate: string; endDate: string; pageNum: number; limit: number }) => {
  const offset = (pageNum - 1) * limit;
  const query = `SELECT id, clientId, propId, tenantId, ROUND(weight, 2) as weight, noOfClothes, laundryDate, recordedById, recordedByName, createdAt, updatedAt FROM LaundryRequests WHERE tenantId = ? AND clientId = ? order by id desc limit ?,?`;
  const data = [tenantId, clientId, `${offset}`, `${limit}`];

  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows;
  else return false;
};

laundryRequestsDB.getByClientIdAndDate = async ({
  clientId,
  startDate,
  endDate,
  pageNum,
  limit,
  propFilter,
}: laundryRequestsTypes & { startDate: string; endDate: string; pageNum: number; limit: number; propFilter: any; }) => {
  let query = `SELECT LR.clientId, LR.id, LR.propId, LR.tenantId, ROUND(LR.weight, 2) as weight, LR.noOfClothes, LR.laundryDate, LR.recordedById, LR.recordedByName, LR.createdAt, LR.updatedAt, T.name as tenantName, T.mobile as tenantMobile, P.name as propName, P.streetAddress as address, (select value from Documents where tenantId = LR.tenantId and clientId = LR.clientId and type = ? and moveOut=0 limit 1) as profilePicture FROM LaundryRequests as LR join Tenants as T on LR.tenantId = T.id join Properties as P on P.id = LR.propId WHERE LR.clientId = ? AND DATE(LR.laundryDate) >= ? AND DATE(LR.laundryDate) <= ? `;
  
  const offset = (pageNum - 1) * limit;

  const data: any = [
    CONSTANTS.DOCUMENT_TYPES.SELFI,
    clientId,
    startDate,
    endDate,
  ];

  if (propFilter && Array.isArray(propFilter) && propFilter.length > 0) {
    query += ` and LR.propId IN (${propFilter.map(() => "?").join(",")})`;
    data.push(...propFilter);
  }

  query += ` order by id desc limit ?, ?`
  data.push(`${offset}`);
  data.push(`${limit}`);

  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows;
  else return [];
};

laundryRequestsDB.getByClientIdAndDateAndSearch = async ({
  clientId,
  startDate,
  endDate,
  pageNum,
  limit,
  searchVal,
}: laundryRequestsTypes & { startDate: string; endDate: string; pageNum: number; limit: number; searchVal: string; }) => {
  const query = `SELECT LR.clientId, LR.id, LR.propId, LR.tenantId, ROUND(LR.weight, 2) as weight, LR.noOfClothes, LR.laundryDate, LR.recordedById, LR.recordedByName, LR.createdAt, LR.updatedAt, T.name as tenantName, T.mobile as tenantMobile, P.name as propName, P.streetAddress as address, (select value from Documents where tenantId = LR.tenantId and clientId = LR.clientId and type = ? and moveOut=0 limit 1) as profilePicture FROM LaundryRequests as LR join Tenants as T on LR.tenantId = T.id join Properties as P on P.id = LR.propId WHERE LR.clientId = ? AND DATE(LR.laundryDate) >= ? AND DATE(LR.laundryDate) <= ? and (T.name LIKE ? OR T.mobile LIKE ?) order by id desc limit ?, ?`;

  const offset = (pageNum - 1) * limit;

  const data = [
    CONSTANTS.DOCUMENT_TYPES.SELFI,
    clientId,
    startDate,
    endDate,
    `%${searchVal}%`,
    `%${searchVal}%`,
    `${offset}`,
    `${limit}`,
  ];

  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows;
  else return [];
};

laundryRequestsDB.getWeightUsedByClientIdAndTenantIdAndDate = async ({
  clientId,
  tenantId,
  startDate,
  endDate,
}: laundryRequestsTypes & { startDate: string; endDate: string }) => {
  const query = `SELECT ROUND(IFNULL(sum(weight), 0), 2) as weight FROM LaundryRequests WHERE tenantId = ? AND clientId = ? AND DATE(laundryDate) >= ? AND DATE(laundryDate) <= ?`;
  const data = [
    tenantId,
    clientId,
    startDate,
    endDate,
  ];

  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows[0].weight;
  else return 0;
};

laundryRequestsDB.getWeightUsedByClientIdAndTenantId = async ({
  clientId,
  tenantId,
}: laundryRequestsTypes) => {
  const query = `SELECT ROUND(IFNULL(sum(weight), 0), 2) as weight FROM LaundryRequests WHERE tenantId = ? AND clientId = ?`;
  const data = [
    tenantId,
    clientId,
  ];

  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows[0].weight;
  else return 0;
};

laundryRequestsDB.getWeightUsedByClientIdAndDate = async ({
  clientId,
  startDate,
  endDate,
  propFilter,
}: laundryRequestsTypes & { startDate: string; endDate: string; propFilter: any; }) => {
  let query = `SELECT ROUND(IFNULL(sum(weight), 0), 2) as weight FROM LaundryRequests WHERE clientId = ? AND DATE(laundryDate) >= ? AND DATE(laundryDate) <= ?`;
  
  const data = [
    clientId,
    startDate,
    endDate,
  ];

  if (propFilter && Array.isArray(propFilter) && propFilter.length > 0) {
    query += ` and propId IN (${propFilter.map(() => "?").join(",")})`;
    data.push(...propFilter);
  }

  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows[0].weight;
  else return 0;
};

laundryRequestsDB.getWeightUsedByClientIdAndDateAndSearch = async ({
  clientId,
  startDate,
  endDate,
  searchVal,
}: laundryRequestsTypes & { startDate: string; endDate: string; searchVal: string; }) => {
  const query = `SELECT ROUND(IFNULL(sum(LR.weight), 0), 2) as weight FROM LaundryRequests as LR join Tenants as T on LR.tenantId = T.id WHERE LR.clientId = ? AND DATE(LR.laundryDate) >= ? AND DATE(LR.laundryDate) <= ? and (T.name LIKE ? OR T.mobile LIKE ?)`;
  const data = [
    clientId,
    startDate,
    endDate,
    `%${searchVal}%`,
    `%${searchVal}%`,
  ];

  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length > 0) return rows[0].weight;
  else return 0;
};

export default laundryRequestsDB;