import mysql, { ResultSetHeader, RowDataPacket } from "mysql2";
import DB from "../config/database/db";
import duesTypes from "../schemas/dues.schema";
import CONSTANTS from "../config/constants";
import log from "../config/log";
import moment from "moment";

const duesDB: any = {};

duesDB.getById = async ({ id }: duesTypes) => {
  const query = "Select * from Dues where id = ?";
  const data = [id];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getByIdAndClientId = async ({ id, clientId }: duesTypes) => {
  const query = "Select * from Dues where id = ? and clientId = ?";
  const data = [id, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getByIdAndTenantId = async ({ id, tenantId, propId }: duesTypes) => {
  const query =
    "Select * from Dues where id = ? and tenantId =? and propId = ?";
  const data = [id, tenantId, propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getByOccupancyId = async ({ occupancyId }: duesTypes) => {
  const query = "Select * from Dues where occupancyId = ?";
  const data = [occupancyId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getTempByIdAndTenantId = async ({ id, tenantId, propId }: duesTypes) => {
  const query =
    "Select * from TemporaryDues where id = ? and tenantId =? and propId = ?";
  const data = [id, tenantId, propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getTotalDuesByOccupancyId = async ({ occupancyId }: duesTypes) => {
  const query = "Select SUM(balance) as total from Dues where occupancyId = ?";
  const data = [occupancyId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getTotalDuesStats = async ({ clientId }: duesTypes) => {
  const query =
    "Select SUM(D.balance) as totalDues, any_value(MONTH(D.dueDate)) as month, YEAR(D.dueDate) as year from Dues as D where D.clientId=? Group By month, year Order by year desc, month desc";
  const data = [clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

duesDB.getDailyTotalDuesStats = async ({ clientId }: duesTypes) => {
  const query =
    "Select SUM(D.balance) as totalDues, any_value(MONTH(D.dueDate)) as month, YEAR(D.dueDate) as year, DAY(D.dueDate) as day from Dues as D where D.clientId=? Group By day, month, year Order by year desc, month desc, day desc";
  const data = [clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (Array.isArray(rows)) {
    return rows.map((row: any) => ({
      date: `${String(row.year)}-${String(row.month).padStart(2, "0")}-${String(
        row.day
      ).padStart(2, "0")}`,
      totalDues: row.totalDues,
    }));
  } else return [];
};

duesDB.getTotalDuesStatsForStaff = async ({
  clientId,
  propertiesIds,
}: duesTypes & { propertiesIds: string }) => {
  const query =
    "Select SUM(D.balance) as totalDues, MONTH(D.dueDate) as month, YEAR(D.dueDate) as year from Dues as D where D.clientId=? and D.propId in (" +
    `${propertiesIds}` +
    ") Group By month, year Order by year desc, month desc";
  //const data = [clientId, propertiesIds];
  const data = [clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

duesDB.getDailyTotalDuesStatsForStaff = async ({
  clientId,
  propertiesIds,
}: duesTypes & { propertiesIds: string }) => {
  const query =
    "Select SUM(D.balance) as totalDues, MONTH(D.dueDate) as month, YEAR(D.dueDate) as year, DAY(D.dueDate) as day from Dues as D where D.clientId=? and D.propId in (" +
    `${propertiesIds}` +
    ") Group By day, month, year Order by year desc, month desc, day desc";
  //const data = [clientId, propertiesIds];
  const data = [clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (Array.isArray(rows)) {
    return rows.map((row: any) => ({
      date: `${String(row.year)}-${String(row.month).padStart(2, "0")}-${String(
        row.day
      ).padStart(2, "0")}`,
      totalDues: row.totalDues,
    }));
  } else return [];
};

// Sukhbir - Delete it after testing.. QUERY was wrong
// duesDB.getTotalDuesStatsByPropId = async ({ propId }: duesTypes) => {
//   const query =
//     "Select any_value(SUM(D.amount)) as totalDues, any_value(MONTH(D.createdAt)) as month, any_value(YEAR(D.createdAt)) as year from Dues as D where D.propId=? and MONTH(D.createdAt) = month(CURDATE()) and YEAR(D.createdAt)=YEAR(CURDATE()) group by month";
//   const data = [propId];
//   const [rows] = await DB.execute<RowDataPacket[]>(query, data);
//   if (rows?.length > 0) return rows;
//   else return [];
// };

duesDB.getTotalDuesStatsByPropId = async ({ propId }: duesTypes) => {
  const query =
    "Select any_value(SUM(D.balance)) as totalDues, any_value(MONTH(D.dueDate)) as month, any_value(YEAR(D.dueDate)) as year from Dues as D where D.propId=? Group By month, year Order by year desc, month desc";
  const data = [propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

duesDB.getDailyTotalDuesStatsByPropId = async ({ propId }: duesTypes) => {
  const query =
    "Select any_value(SUM(D.balance)) as totalDues, any_value(MONTH(D.dueDate)) as month, any_value(YEAR(D.dueDate)) as year, DAY(D.dueDate) as day from Dues as D where D.propId=? Group By day, month, year Order by year desc, month desc, day desc";
  const data = [propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (Array.isArray(rows)) {
    return rows.map((row: any) => ({
      date: `${String(row.year)}-${String(row.month).padStart(2, "0")}-${String(
        row.day
      ).padStart(2, "0")}`,
      totalDues: row.totalDues,
    }));
  } else return [];
};

duesDB.getTenantCount = async ({ clientId }: duesTypes) => {
  const query =
    "Select COUNT(DISTINCT tenantId) as dueTenantCount from Dues where clientId = ?";
  const data = [clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getTenantCountYearMonth = async ({
  clientId,
  month,
  year,
}: duesTypes & { year: any; month: any }) => {
  const query =
    "Select COUNT(DISTINCT tenantId) as dueTenantCount from Dues where clientId = ? and MONTH(dueDate) = ? and YEAR(dueDate) = ?";
  const data = [clientId, month, year];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getTenantCountYearMonthAndType = async ({
  clientId,
  month,
  year,
  type,
}: duesTypes & { year: any; month: any; type: any }) => {
  const query =
    `Select COUNT(DISTINCT tenantId) as dueTenantCount from Dues where clientId = ? and MONTH(dueDate) = ? and YEAR(dueDate) = ? and type in (${type})`;
  const data = [clientId, month, year];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getTenantCountForStaff = async ({
  clientId,
  propertiesIds,
}: duesTypes & {
  propertiesIds: string;
}) => {
  const query =
    "Select COUNT(DISTINCT tenantId) as dueTenantCount from Dues where clientId =? and propId in (" +
    `${propertiesIds}` +
    ")";
  //const data = [clientId, propertiesIds];
  const data = [clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getTenantCountForStaffYearMonth = async ({
  clientId,
  propertiesIds,
  month,
  year,
}: duesTypes & {
  propertiesIds: string;
  year: any;
  month: any;
}) => {
  const query =
    "Select COUNT(DISTINCT tenantId) as dueTenantCount from Dues where clientId =? and propId in (" +
    `${propertiesIds}` +
    ") and MONTH(dueDate) = ? and YEAR(dueDate) = ?";
  //const data = [clientId, propertiesIds];
  const data = [clientId, month, year];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getTenantCountForStaffYearMonthAndType = async ({
  clientId,
  propertiesIds,
  month,
  year,
  type,
}: duesTypes & {
  propertiesIds: string;
  year: any;
  month: any;
  type: any;
}) => {
  const query =
    `Select COUNT(DISTINCT tenantId) as dueTenantCount from Dues where clientId =? and propId in (${propertiesIds}) and MONTH(dueDate) = ? and YEAR(dueDate) = ? and type in (${type})`;
  //const data = [clientId, propertiesIds];
  const data = [clientId, month, year];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getTenantCountByPropId = async ({ propId, dueDate }: duesTypes) => {
  // const query =
  //   "Select COUNT(tenantId) as dueTenantCount from Dues where propId = ? and MONTH(dueDate) = ? and YEAR(dueDate) = ?";
  // const data = [propId, dueDate, dueDate];
  const query =
    "Select COUNT(tenantId) as dueTenantCount from Dues where propId = ?";
  const data = [propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getTenantCountByLocation = async ({ locationId }: duesTypes & {locationId: number;}) => {
  const query =
    "Select COUNT(D.tenantId) as dueTenantCount from Dues as D join Properties as P on P.id = D.propId where P.locationId = ?";
  const data = [locationId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getDistinctTenantCountByPropId = async ({ propId }: duesTypes) => {
  const query =
    "Select COUNT(DISTINCT tenantId) as dueTenantCount from Dues where propId = ?";
  const data = [propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getDistinctTenantCountByClientId = async ({ clientId }: duesTypes) => {
  const query =
    "Select COUNT(DISTINCT tenantId) as dueTenantCount from Dues where clientId = ?";
  const data = [clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};


duesDB.getTenantCountByPropIdYearMonth = async ({
  propId,
  month,
  year,
}: duesTypes & { year: any; month: any }) => {
  const query =
    "Select COUNT(DISTINCT tenantId) as dueTenantCount from Dues where propId = ? and MONTH(dueDate) = ? and YEAR(dueDate) = ?";
  const data = [propId, month, year];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getTenantCountByPropIdYearMonthAndType = async ({
  propId,
  month,
  year,
  type,
}: duesTypes & { year: any; month: any; type: any }) => {
  const query =
    `Select COUNT(DISTINCT tenantId) as dueTenantCount from Dues where propId = ? and MONTH(dueDate) = ? and YEAR(dueDate) = ? and type in (${type})`;
  const data = [propId, month, year];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getTenantCountByTenantId = async ({ tenantId }: duesTypes) => {
  const query =
    "Select COUNT(DISTINCT tenantId) as dueTenantCount from Dues where tenantId = ?";
  const data = [tenantId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getFromTemp = async ({
  tenantId,
  occupancyId,
  clientId,
  dueDate,
  type,
}: duesTypes) => {
  const query =
    "Select * from TemporaryDues where tenantId = ? and occupancyId = ? and clientId = ? and dueDate = ? and type = ?";
  const data = [tenantId, occupancyId, clientId, dueDate, type];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getParticularDue = async ({
  tenantId,
  occupancyId,
  clientId,
  dueDate,
  type,
}: duesTypes) => {
  const query =
    "Select * from Dues where tenantId = ? and occupancyId = ? and clientId = ? and dueDate = ? and type = ?";
  const data = [tenantId, occupancyId, clientId, dueDate, type];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getByClientIdandPage = async ({
  clientId,
  pageNum,
  limit,
  dueDate,
}: duesTypes & { pageNum: number; limit: number }) => {
  const query =
    "Select D.id, D.remarks, D.title, D.remarks, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? and MONTH(D.dueDate) = MONTH(?) and YEAR(D.dueDate) = YEAR(?) order by D.dueDate desc, D.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  const data = [CONSTANTS.DOCUMENT_TYPES.SELFI, clientId, dueDate, dueDate, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getByClientIdandPageandOrderByAmount = async ({
  clientId,
  pageNum,
  limit,
  dueDate,
  ordBy,
}: duesTypes & { pageNum: number; limit: number; ordBy: string }) => {
  const sortDirection = ordBy.toUpperCase() === 'ASC' ? 'ASC' : 'DESC';
  const query =
    `Select D.id, D.remarks, D.title, D.remarks, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId inner join (SELECT tenantId, SUM(amount) AS totalDue FROM Dues WHERE clientId = ? AND Month(dueDate) = MONTH(?) AND YEAR(dueDate) = YEAR(?) GROUP BY tenantId) AS TD ON TD.tenantId = D.tenantId where D.clientId = ? and MONTH(D.dueDate) = MONTH(?) and YEAR(D.dueDate) = YEAR(?) order by TD.totalDue ${sortDirection}, D.dueDate, D.id desc limit ?, ?`;
  const offset: number = (pageNum - 1) * limit;
  const data = [clientId, dueDate, dueDate, clientId, dueDate, dueDate, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getByClientIdAndTypeAndPage = async ({
  clientId,
  pageNum,
  limit,
  dueDate,
  type,
}: duesTypes & { pageNum: number; limit: number; type: any }) => {
  const query =
    `Select D.id, D.remarks, D.title, D.remarks, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? and MONTH(D.dueDate) = MONTH(?) and YEAR(D.dueDate) = YEAR(?) and D.type in (${type}) order by D.dueDate desc, D.id desc limit ?, ?`;
  const offset: number = (pageNum - 1) * limit;
  const data = [clientId, dueDate, dueDate, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getByClientIdAndTypeAndPageAndOrderByAmount = async ({
  clientId,
  pageNum,
  limit,
  dueDate,
  type,
  ordBy,
}: duesTypes & { pageNum: number; limit: number; type: any; ordBy: string }) => {
  const sortDirection = ordBy.toUpperCase() === 'ASC' ? 'ASC' : 'DESC';
  const query =
    `Select D.id, D.remarks, D.title, D.remarks, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId inner join (SELECT tenantId, SUM(amount) AS totalDue FROM Dues WHERE clientId = ? AND Month(dueDate) = MONTH(?) AND YEAR(dueDate) = YEAR(?) and type in (${type}) GROUP BY tenantId) AS TD ON TD.tenantId = D.tenantId where D.clientId = ? and MONTH(D.dueDate) = MONTH(?) and YEAR(D.dueDate) = YEAR(?) and D.type in (${type}) order by TD.totalDue ${sortDirection}, D.dueDate, D.id desc limit ?, ?`;
  const offset: number = (pageNum - 1) * limit;
  const data = [clientId, dueDate, dueDate, clientId, dueDate, dueDate, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getForStaffByPage = async ({
  propertiesIds,
  pageNum,
  limit,
  dueDate,
}: {
  pageNum: number;
  limit: number;
  propertiesIds: string;
  dueDate: any;
}) => {
  const query =
    "Select D.id, D.title, D.remarks, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.propId in (" +
    `${propertiesIds}` +
    ") and MONTH(D.dueDate) = MONTH(?) and YEAR(D.dueDate) = YEAR(?) order by D.dueDate, D.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  //const data = [propertiesIds, `${offset}`, `${limit}`];
  const data = [CONSTANTS.DOCUMENT_TYPES.SELFI, dueDate, dueDate, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getForStaffByPageAndOrderByAmount = async ({
  propertiesIds,
  pageNum,
  limit,
  dueDate,
  ordBy,
}: {
  pageNum: number;
  limit: number;
  propertiesIds: string;
  dueDate: any;
  ordBy: string,
}) => {
  const sortDirection = ordBy.toUpperCase() === "ASC" ? "ASC" : "DESC";
  const query =
    `Select D.id, D.title, D.remarks, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId inner join (SELECT tenantId, SUM(amount) AS totalDue FROM Dues WHERE propId in (${propertiesIds}) and Month(dueDate) = MONTH(?) AND YEAR(dueDate) = YEAR(?) GROUP BY tenantId) AS TD ON TD.tenantId = D.tenantId where D.propId in (${propertiesIds}) and MONTH(D.dueDate) = MONTH(?) and YEAR(D.dueDate) = YEAR(?) order by TD.totalDue ${sortDirection}, D.dueDate, D.id desc limit ?, ?`;
  const offset: number = (pageNum - 1) * limit;
  //const data = [propertiesIds, `${offset}`, `${limit}`];
  const data = [dueDate, dueDate, dueDate, dueDate, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getForStaffByPageAndType = async ({
  clientId,
  propertiesIds,
  pageNum,
  limit,
  dueDate,
  type,
}: {
  clientId: number;
  pageNum: number;
  limit: number;
  propertiesIds: string;
  dueDate: any;
  type: any;
}) => {
  const query =
    `Select D.id, D.title, D.remarks, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? and D.propId in (${propertiesIds}) and MONTH(D.dueDate) = MONTH(?) and YEAR(D.dueDate) = YEAR(?) and D.type in (${type}) order by D.dueDate, D.id desc limit ?, ?`;
  const offset: number = (pageNum - 1) * limit;
  //const data = [propertiesIds, `${offset}`, `${limit}`];
  const data = [clientId, dueDate, dueDate, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getForStaffByPageAndTypeAndOrderByAmount = async ({
  clientId,
  propertiesIds,
  pageNum,
  limit,
  dueDate,
  type,
  ordBy
}: {
  clientId: number;
  pageNum: number;
  limit: number;
  propertiesIds: string;
  dueDate: any;
  type: any;
  ordBy: string;
}) => {
  const sortDirection = ordBy.toUpperCase() === "ASC" ? "ASC" : "DESC";
  const query =
    `Select D.id, D.title, D.remarks, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId inner join (SELECT tenantId, SUM(amount) AS totalDue FROM Dues WHERE clientId = ? AND Month(dueDate) = MONTH(?) AND YEAR(dueDate) = YEAR(?) and propId in (${propertiesIds}) and type in (${type}) GROUP BY tenantId) AS TD ON TD.tenantId = D.tenantId where D.clientId = ? and D.propId in (${propertiesIds}) and MONTH(D.dueDate) = MONTH(?) and YEAR(D.dueDate) = YEAR(?) and D.type in (${type}) order by TD.totalDue ${sortDirection}, D.dueDate, D.id desc limit ?, ?`;
  const offset: number = (pageNum - 1) * limit;
  //const data = [propertiesIds, `${offset}`, `${limit}`];
  const data = [clientId, dueDate, dueDate, clientId, dueDate, dueDate, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getByPropIdandPage = async ({
  propId,
  pageNum,
  limit,
  dueDate,
}: duesTypes & { pageNum: number; limit: number }) => {
  const query =
    "Select D.id, D.remarks, D.title, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.propId = ? and MONTH(D.dueDate) = MONTH(?) and YEAR(D.dueDate) = YEAR(?) order by D.dueDate desc, D.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  const data = [CONSTANTS.DOCUMENT_TYPES.SELFI, propId, dueDate, dueDate, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getByPropIdandPageAndOrderByAmount = async ({
  propId,
  pageNum,
  limit,
  dueDate,
  ordBy,
}: duesTypes & { pageNum: number; limit: number; ordBy: string; }) => {
  const sortDirection = ordBy.toUpperCase() === 'ASC' ? 'ASC' : 'DESC';
  const query =
    `Select D.id, D.remarks, D.title, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId inner join (SELECT tenantId, SUM(amount) AS totalDue FROM Dues WHERE propId = ? AND Month(dueDate) = MONTH(?) AND YEAR(dueDate) = YEAR(?) GROUP BY tenantId) AS TD ON TD.tenantId = D.tenantId where D.propId = ? and MONTH(D.dueDate) = MONTH(?) and YEAR(D.dueDate) = YEAR(?) order by TD.totalDue ${sortDirection}, D.dueDate, D.id desc limit ?, ?`;
  const offset: number = (pageNum - 1) * limit;
  const data = [propId, dueDate, dueDate, propId, dueDate, dueDate, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getByPropIdandPageAndType = async ({
  propId,
  pageNum,
  limit,
  dueDate,
  type,
}: duesTypes & { pageNum: number; limit: number; type: any }) => {
  const query =
    `Select D.id, D.remarks, D.title, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.propId = ? and MONTH(D.dueDate) = MONTH(?) and YEAR(D.dueDate) = YEAR(?) and D.type in (${type}) order by D.dueDate desc, D.id desc limit ?, ?`;
  const offset: number = (pageNum - 1) * limit;
  const data = [propId, dueDate, dueDate, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getByPropIdandPageAndTypeAndOrderByAmount = async ({
  propId,
  pageNum,
  limit,
  dueDate,
  type,
  ordBy
}: duesTypes & { pageNum: number; limit: number; type: any; ordBy: string }) => {
  const sortDirection = ordBy.toUpperCase() === 'ASC' ? 'ASC' : 'DESC';
  const query =
    `Select D.id, D.remarks, D.title, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId inner join (SELECT tenantId, SUM(amount) AS totalDue FROM Dues WHERE propId = ? AND Month(dueDate) = MONTH(?) AND YEAR(dueDate) = YEAR(?) and type in (${type}) GROUP BY tenantId) AS TD ON TD.tenantId = D.tenantId where D.propId = ? and MONTH(D.dueDate) = MONTH(?) and YEAR(D.dueDate) = YEAR(?) and D.type in (${type}) order by TD.totalDue ${sortDirection}, D.dueDate, D.id desc limit ?, ?`;
  const offset: number = (pageNum - 1) * limit;
  const data = [propId, dueDate, dueDate, propId, dueDate, dueDate, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getByTenantIdandPage = async ({
  tenantId,
  pageNum,
  limit,
}: duesTypes & { pageNum: number; limit: number }) => {
  const query =
    "Select D.id, D.remarks, D.title, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.occupancyId, D.propId, D.roomId, D.clientId, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.tenantId where D.tenantId = ? order by D.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  const data = [tenantId, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getByTenantId = async ({ tenantId }: duesTypes) => {
  const query =
    "Select  D.id, D.title, D.discount, D.balance, D.remarks, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.occupancyId, D.tenantId, D.propId, D.roomId, D.clientId, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, R.roomNum, P.name as propertyName from Dues as D left join Tenants as T on T.id = D.tenantId left join Rooms as R on R.id = D.roomId left join Properties as P on P.id = D.propId where D.tenantId = ? order by D.dueDate desc";
  const data = [tenantId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getByTenantIdAndClientId = async ({ tenantId, clientId }: duesTypes) => {
  const query =
    "Select  D.id, D.title, D.discount, D.tallyBillRef, D.tallyStatus, D.tallyGuid, D.balance, D.remarks, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.occupancyId, D.tenantId, D.propId, D.roomId, D.clientId, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, R.roomNum, P.name as propertyName from Dues as D left join Tenants as T on T.id = D.tenantId left join Rooms as R on R.id = D.roomId left join Properties as P on P.id = D.propId where D.tenantId = ? and D.clientId = ? order by D.dueDate desc";
  const data = [tenantId, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getByTenantIdAndClientIdAndType = async ({ tenantId, clientId, type }: duesTypes) => {
  const query =
    "Select  D.id, D.title, D.discount, D.balance, D.remarks, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.occupancyId, D.tenantId, D.propId, D.roomId, D.clientId, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, R.roomNum, P.name as propertyName from Dues as D left join Tenants as T on T.id = D.tenantId left join Rooms as R on R.id = D.roomId left join Properties as P on P.id = D.propId where D.tenantId = ? and D.clientId = ? and D.type = ? order by D.dueDate desc limit 1";
  const data = [tenantId, clientId, type];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getTotalDuesByClientId = async ({ clientId, dueDate }: duesTypes) => {
  // const query =
  //   "Select SUM(balance)as totalDues from Dues where clientId = ? and MONTH(createdAt) = MONTH(?) and YEAR(createdAt) = YEAR(?)";
  const query = "Select SUM(balance)as totalDues from Dues where clientId = ?";
  //const data = [clientId, dueDate, dueDate];
  const data = [clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getTotalDuesForStaff = async ({
  clientId,
  propertiesIds,
}: duesTypes & {
  propertiesIds: string;
}) => {
  // const query =
  //   "Select SUM(balance)as totalDues from Dues where clientId=? and propId in (" +
  //   `${propertiesIds}` +
  //   ") and MONTH(createdAt) = MONTH(?) and YEAR(createdAt) = YEAR(?)";
  const query =
    "Select SUM(balance)as totalDues from Dues where clientId=? and propId in (" +
    `${propertiesIds}` +
    ")";
  //const data = [clientId, dueDate, dueDate];
  const data = [clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getTotalDuesByPropId = async ({ propId }: duesTypes) => {
  // const query =
  //   "Select SUM(balance)as totalDues from Dues where propId = ? and DATE_FORMAT(dueDate, '%Y-%m') <= DATE_FORMAT(?, '%Y-%m')";
  // const data = [propId, dueDate];
  const query = "Select SUM(balance) as totalDues from Dues where propId = ?";
  const data = [propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getTotalDuesByLocation = async ({ locationId }: {locationId: number}) => {
  const query = "Select SUM(D.balance) as totalDues from Dues as D join Properties as P on D.propId = P.id where P.locationId = ?";
  const data = [locationId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getTotalDuesByPropIdTenantNameMobile = async ({
  propId,
  searchVal,
}: duesTypes & { searchVal: string }) => {
  const query =
    "Select SUM(D.balance)as totalDues from Dues as D join Tenants as T on D.tenantId = T.id where D.propId = ? and T.name like ? or T.mobile like ?";
  const data = [propId, `%${searchVal}%`, `%${searchVal}%`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getTotalDuesByPropIdDateRange = async ({
  propId,
  startDate,
  endDate,
}: duesTypes & { startDate: string; endDate: string }) => {
  const query =
    "Select SUM(balance) as totalDues from Dues where propId = ? and DATE(dueDate) BETWEEN ? and ?";
  const data = [propId, startDate, endDate];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getTotalNumberOfDuesByPropIdDateRange = async ({
  propId,
  startDate,
  endDate,
}: duesTypes & { startDate: string; endDate: string }) => {
  const query =
    "Select count(id) as totalDues from Dues where propId = ? and DATE(dueDate) BETWEEN ? and ?";
  const data = [propId, startDate, endDate];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].totalDues;
  else return 0;
};

duesDB.getTotalDuesByTenantId = async ({ tenantId, propId }: duesTypes) => {
  const query =
    "Select SUM(balance) as totalDues from Dues where tenantId = ? and propId = ?";
  const data = [tenantId, propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getTotalRentDuesByTenantId = async ({ tenantId, propId}: duesTypes) => {
  const query =
    "Select SUM(balance) as totalDues from Dues where tenantId = ? and propId = ? and type = ?";
  const data = [tenantId, propId, CONSTANTS.DUES_TYPES.RENT];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getDuesSummaryByTenantId = async ({ tenantId, clientId, propId }: duesTypes) => {
  const query = `
    SELECT 
      SUM(balance) AS totalDues,
      SUM(CASE WHEN type = ? THEN balance ELSE 0 END) AS rentDues
    FROM Dues 
    WHERE tenantId = ? AND clientId = ? and propId= ?
  `;

  const data = [CONSTANTS.DUES_TYPES.RENT, tenantId, clientId, propId];

  const [rows] = await DB.execute<RowDataPacket[]>(query, data);

  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getTotalDuesByPropIdGroupByTenant = async ({ propId }: duesTypes) => {
  const query =
    "Select SUM(balance) as totalDues, tenantId from Dues where propId = ? group by tenantId";
  const data = [propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getTotalDuesByPropIdGroupByFlat = async ({ propId }: duesTypes) => {
  const query =
    "Select SUM(D.balance) as totalDues, R.flatId as flatId from Dues as D join Rooms as R on D.roomId = R.id join Flats as F on R.flatId = F.id where D.propId = ? group by F.id";
  const data = [propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getTotalDuesByTenantIdForEviction = async ({
  tenantId,
  propId,
}: duesTypes) => {
  const query =
    "Select SUM(balance) as totalDues from Dues where tenantId = ? and propId = ? and type != 2";
  const data = [tenantId, propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getTotalDuesByDate = async ({
  tenantId,
}: duesTypes & { date: string }) => {
  const query =
    "Select SUM(balance) as totalDues from Dues where tenantId = ? and type != 2";
  const data = [tenantId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return 0;
};
duesDB.getTotalTenantDuesByClientId = async ({
  tenantId,
  clientId
}: duesTypes & { date: string }) => {
  const query =
    "Select SUM(balance) as totalDues from Dues where tenantId = ? and clientId = ? and type != 2";
  const data = [tenantId, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return 0;
};

duesDB.getTotalTempDuesByDate = async ({
  tenantId,
}: duesTypes & { date: string }) => {
  const query =
    "Select SUM(amount) as totalTempDues from TemporaryDues where tenantId = ?";
  const data = [tenantId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return 0;
};

duesDB.getTotalTempDuesByDateWithClient = async ({
  tenantId,
  clientId
}: duesTypes & { date: string }) => {
  const query =
    "Select SUM(amount) as totalTempDues from TemporaryDues where tenantId = ? and clientId = ?";
  const data = [tenantId, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return 0;
};

duesDB.getDuesByDate = async ({
  tenantId,
}: duesTypes & { date: string }) => {
  const query =
    "Select * from Dues where tenantId = ? and type != 2 order by id desc ";
  const data = [tenantId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getTenantDuesByClient = async ({
  tenantId,
  clientId,
}: duesTypes & { date: string }) => {
  const query =
    "Select * from Dues where tenantId = ? and clientId = ? and type != 2 order by id desc ";
  const data = [tenantId, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getTempDuesByDate = async ({
  tenantId,
  propId,
}: duesTypes & { date: string }) => {
  const query =
    "Select * from TemporaryDues where tenantId = ? and propId = ? order by id desc ";
  const data = [tenantId, propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

duesDB.getDueBySameDueDate = async ({
  tenantId,
  date,
  type,
}: duesTypes & { date: string }) => {
  const query =
    "Select * from Dues where tenantId = ? and type = ? and DATE(dueDate) = ?  order by id desc ";
  const data = [tenantId, type, date];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.updateAmount = async ({ amount, id }: duesTypes) => {
  const query = "Update Dues set amount = ? where id = ?";
  const data = [amount, id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

duesDB.updateTempAmount = async ({ amount, balance, id }: duesTypes) => {
  const query = "Update TemporaryDues set amount = ?, balance = ? where id = ?";
  const data = [amount, balance, id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

duesDB.removeDue = async ({ id }: duesTypes) => {
  const query = "Delete from Dues where id = ?";
  const data = [id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

duesDB.removeDuesByIds = async ({ ids }: { ids: number[] }) => {
  const query = "Delete from Dues where id IN (?)";
  const data = [ids];
  await DB.query<ResultSetHeader[]>(query, data);
  return true;
};

duesDB.removeFutureDues = async ({ tenantId, clientId, propId }: duesTypes) => {
  const query =
    "Delete from Dues where tenantId=? and clientId=? and propId=? and date(dueDate) > CURDATE() and amount = balance";
  const data = [tenantId, clientId, propId];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

duesDB.removeTempDue = async ({ id }: duesTypes) => {
  const query = "Delete from TemporaryDues where id = ?";
  const data = [id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

duesDB.add = async ({
  tenantId,
  amount,
  occupancyId,
  roomId,
  propId,
  clientId,
  dueDate,
  type,
  balance,
  ledgerReferenceId,
}: duesTypes) => {
  const query =
    "Insert into Dues (tenantId, amount, occupancyId, roomId, propId, clientId, dueDate, type, balance , ledgerReferenceId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  const data = [
    tenantId,
    amount,
    occupancyId,
    roomId,
    propId,
    clientId,
    dueDate,
    type,
    balance,
    ledgerReferenceId,
  ];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return rows.insertId;
};

duesDB.addWithStartEndDate = async ({
  tenantId,
  amount,
  occupancyId,
  roomId,
  propId,
  clientId,
  rentStartDate,
  rentEndDate,
  dueDate,
  type,
  balance,
  ledgerReferenceId,
}: duesTypes) => {
  const query =
    "Insert into Dues (tenantId, amount, occupancyId, roomId, propId, clientId, dueDate, type,rentStartDate, rentEndDate, balance , ledgerReferenceId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  const data = [
    tenantId,
    amount,
    occupancyId,
    roomId,
    propId,
    clientId,
    dueDate,
    type,
    rentStartDate,
    rentEndDate,
    balance,
    ledgerReferenceId,
  ];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return rows.insertId;
};

// new add dues
duesDB.addX = async ({
  tenantId,
  amount,
  occupancyId,
  roomId,
  propId,
  clientId,
  dueDate,
  type,
  balance,
  ledgerReferenceId,
  description,
  title,
}: duesTypes) => {
  const query =
    "Insert into Dues (tenantId, amount, occupancyId, roomId, propId, clientId, dueDate, type, balance , ledgerReferenceId, description, title) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    
  const data = [
    tenantId,
    amount,
    occupancyId,
    roomId,
    propId,
    clientId,
    dueDate,
    type,
    balance,
    ledgerReferenceId,
    description,
    title,
  ];

  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return rows.insertId;
};

duesDB.addWithStartEndDateX = async ({
  tenantId,
  amount,
  occupancyId,
  roomId,
  propId,
  clientId,
  rentStartDate,
  rentEndDate,
  dueDate,
  type,
  balance,
  ledgerReferenceId,
  description,
  title,
  discount = 0,
}: duesTypes) => {
  const query =
    "Insert into Dues (tenantId, amount, occupancyId, roomId, propId, clientId, dueDate, type,rentStartDate, rentEndDate, balance , ledgerReferenceId, description, title, discount) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  const data = [
    tenantId,
    amount,
    occupancyId,
    roomId,
    propId,
    clientId,
    dueDate,
    type,
    rentStartDate,
    rentEndDate,
    balance,
    ledgerReferenceId,
    description,
    title,
    discount,
  ];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return rows.insertId;
};

duesDB.addToTemp = async ({
  tenantId,
  amount,
  balance,
  occupancyId,
  roomId,
  propId,
  clientId,
  dueDate,
  type,
  rentStartDate,
  rentEndDate,
}: duesTypes) => {
  const query =
    "Insert into TemporaryDues (tenantId, amount, balance, occupancyId, roomId, propId, clientId, dueDate, type, rentStartDate, rentEndDate) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  const data = [
    tenantId,
    amount,
    balance,
    occupancyId,
    roomId,
    propId,
    clientId,
    dueDate,
    type,
    rentStartDate,
    rentEndDate,
  ];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

duesDB.addToTempX = async ({
  tenantId,
  amount,
  balance,
  occupancyId,
  roomId,
  propId,
  clientId,
  dueDate,
  type,
  rentStartDate,
  rentEndDate,
  title,
  description,
}: duesTypes) => {
  const query =
    "Insert into TemporaryDues (tenantId, amount, balance, occupancyId, roomId, propId, clientId, dueDate, type, rentStartDate, rentEndDate, title, description) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  const data = [
    tenantId,
    amount,
    balance,
    occupancyId,
    roomId,
    propId,
    clientId,
    dueDate,
    type,
    rentStartDate,
    rentEndDate,
    title,
    description,
  ];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

duesDB.removeTempDues = async ({
  tenantId,
  clientId,
  occupancyId,
}: duesTypes) => {
  const query =
    "Delete from TemporaryDues where tenantId = ? and clientId = ? and occupancyId = ?";
  const data = [tenantId, clientId, occupancyId];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

duesDB.removeByOccupancyId = async ({ occupancyId }: duesTypes) => {
  const query = "Delete from Dues where occupancyId = ?";
  const data = [occupancyId];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

duesDB.removeByTenantId = async ({ tenantId }: duesTypes) => {
  const query = "Delete from Dues where tenantId = ?";
  const data = [tenantId];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

duesDB.removeByClientIdAndTenantId = async ({ clientId, tenantId }: duesTypes) => {
  const query = "Delete from Dues where clientId = ? and tenantId = ?";
  const data = [ clientId, tenantId];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

duesDB.removeTempByTenantId = async ({ tenantId }: duesTypes) => {
  const query = "Delete from TemporaryDues where tenantId = ?";
  const data = [tenantId];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

duesDB.removeTempByClientIdAndTenantId = async ({ tenantId, clientId }: duesTypes) => {
  const query = "Delete from TemporaryDues where tenantId = ? and clientId = ?";
  const data = [tenantId, clientId];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

duesDB.getTotalDuesForTenant = async ({
  tenantId,
  clientId,
}: duesTypes & { date: string }) => {
  // const query =
  //   "Select SUM(balance) as totalDues, DATE(dueDate) as dueDate from Dues where tenantId = ? group by DATE(dueDate) limit 1";
  const query =
    "Select any_value(SUM(balance)) as totalDues, any_value(DATE(dueDate)) as dueDate from Dues where tenantId = ? and clientId=?";
  const data = [tenantId, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return 0;
};

duesDB.getTotalDuesByPropIdYearMonth = async ({
  propId,
  year,
  month,
}: duesTypes & { year: any; month: any }) => {
  // const query =
  //   "Select SUM(balance)as duesForMonth from Dues as D where propId = ? and MONTH(D.dueDate) = ? and YEAR(D.dueDate) = ?";
  // const data = [propId, month, year];
  const date = `${year}-${month}-01`;

  const startOfMonth = moment(date, "YYYY-M-DD")
    .startOf("month")
    .format("YYYY-MM-DD HH:mm:ss");

  const endOfMonth = moment(date, "YYYY-M-DD")
    .endOf("month")
    .format("YYYY-MM-DD HH:mm:ss");
  const query =
    "Select SUM(balance)as duesForMonth from Dues as D where propId = ? and D.dueDate between ? and ?";
  const data = [propId, startOfMonth, endOfMonth];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getTotalDuesByLocationYearMonth = async ({
  locationId,
  year,
  month,
}: duesTypes & { locationId: number; year: any; month: any }) => {
  const date = `${year}-${month}-01`;

  const startOfMonth = moment(date, "YYYY-M-DD")
    .startOf("month")
    .format("YYYY-MM-DD HH:mm:ss");

  const endOfMonth = moment(date, "YYYY-M-DD")
    .endOf("month")
    .format("YYYY-MM-DD HH:mm:ss");
  const query =
    "Select SUM(D.balance)as duesForMonth from Dues as D join Properties as P on P.id = D.propId where P.locationId = ? and D.dueDate between ? and ?";
  const data = [locationId, startOfMonth, endOfMonth];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getTotalDuesByYearMonthForStaff = async ({
  clientId,
  propertiesIds,
  year,
  month,
}: duesTypes & { year: any; month: any; propertiesIds: string; }) => {
  const query =
    `Select SUM(balance)as duesForMonth from Dues as D where clientId = ? and propId in (${propertiesIds}) and MONTH(D.dueDate) = ? and YEAR(D.dueDate) = ?`;
  const data = [clientId, month, year];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getTotalDuesByPropIdYearMonthAndType = async ({
  propId,
  year,
  month,
  type,
}: duesTypes & { year: any; month: any; type: any }) => {
  const query =
    `Select SUM(balance)as duesForMonth from Dues as D where propId = ? and MONTH(D.dueDate) = ? and YEAR(D.dueDate) = ? and D.type in (${type})`;
  const data = [propId, month, year];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getTotalDuesByPropIdTenantNameMobileYearMonth = async ({
  propId,
  year,
  month,
  searchVal,
}: duesTypes & { year: any; month: any; searchVal: string }) => {
  const query =
    "Select SUM(D.balance)as duesForMonth from Dues as D join Tenants as T on D.tenantId = T.id where D.propId = ? and MONTH(D.dueDate) = ? and YEAR(D.dueDate) = ? and T.name like ? or T.mobile like ?";
  const data = [propId, month, year, `%${searchVal}%`, `%${searchVal}%`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getDuesByMonthForStaff = async ({
  clientId,
  propertiesIds,
  year,
  month,
}: duesTypes & { propertiesIds: string; year: any; month: any }) => {
  const query = `Select SUM(D.balance) as totalDues, MONTH(D.dueDate) as month from Dues as D where D.clientId=? and D.propId in (${propertiesIds}) and YEAR(D.dueDate) = ? and MONTH(D.dueDate)= ? Group By month Order by month`;
  const data = [clientId, year, month];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

duesDB.getDuesByMonth = async ({
  clientId,
  year,
  month,
}: duesTypes & { year: any; month: any }) => {
  const query =
    "Select SUM(D.balance) as duesForMonth from Dues as D join Properties as P on D.propId = P.id where D.clientId=? and YEAR(D.dueDate) = ? and MONTH(D.dueDate)= ? and P.status = ?";
  const data = [clientId, year, month, CONSTANTS.PROPERTY_STATUS.ACTIVE];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

duesDB.getDuesByClientIdForWebForStaff = async ({
  clientId,
  propIds,
  propertiesIds,
  startDate,
  endDate,
}: duesTypes & { propertiesIds: string; startDate: string; endDate: string; propIds: any; }) => {
  let query = `Select SUM(D.balance) as totalDues from Dues as D where D.clientId = ? and D.propId in (${propertiesIds}) and DATE(D.dueDate) BETWEEN ? and ?`;
  const data = [clientId, startDate, endDate];

  if (propIds && propIds.length > 0) {
    query += ` and D.propId in (${propIds.map(() => '?').join(',')})`;
    data.push(...propIds);
  }

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

duesDB.getDuesByClientIdForWeb = async ({
  clientId,
  propIds,
  startDate,
  endDate,
}: duesTypes & { startDate: string; endDate: string; propIds: any; }) => {
  let query =
    `Select SUM(D.balance) as duesForMonth from Dues as D join Properties as P on D.propId = P.id where D.clientId = ? and DATE(D.dueDate) BETWEEN ? and ? and P.status = ?`;
  const data = [clientId, startDate, endDate, CONSTANTS.PROPERTY_STATUS.ACTIVE];

  if (propIds && propIds.length > 0) {
    query += ` and D.propId in (${propIds.map(() => '?').join(',')})`;
    data.push(...propIds);
  }

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

duesDB.getDuesByMonthAndTypeForStaff = async ({
  clientId,
  propertiesIds,
  year,
  month,
  type,
}: duesTypes & { propertiesIds: string; year: any; month: any; type: any }) => {
  const query = `Select SUM(D.balance) as totalDues, MONTH(D.dueDate) as month from Dues as D where D.clientId=? and D.propId in (${propertiesIds}) and YEAR(D.dueDate) = ? and MONTH(D.dueDate)= ? and D.type in (${type}) Group By month Order by month`;
  const data = [clientId, year, month];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

duesDB.getDuesByMonthAndType = async ({
  clientId,
  year,
  month,
  type,
}: duesTypes & { year: any; month: any; type: any }) => {
  const query =
    `Select SUM(D.balance) as duesForMonth, MONTH(D.dueDate) as month from Dues as D where D.clientId=? and YEAR(D.dueDate) = ? and MONTH(D.dueDate)= ? and D.type in (${type}) Group By month Order by month`;
  const data = [clientId, year, month];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

duesDB.updateBalance = async ({ id, balance }: duesTypes) => {
  const query = "Update Dues set balance = ? where id = ?";
  const data = [balance, id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

duesDB.addBalance = async ({ id, balance }: duesTypes) => {
  const query = "Update Dues set balance = balance+? where id = ?";
  const data = [balance, id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

duesDB.getByTenantIdAndLedgerReferenceId = async ({
  tenantId,
  ledgerReferenceId,
}: duesTypes) => {
  const query =
    "Select  D.id, D.remarks, D.ledgerReferenceId, D.tallyStatus, D.tallyBillRef, D.discount, D.rentStartDate, D.rentEndDate, D.occupancyId, D.tenantId, D.propId, D.roomId, D.clientId, D.amount,D.balance, D.dueDate, D.type, D.title, D.createdAt, D.updatedAt from Dues as D where D.tenantId = ? and D.ledgerReferenceId = ? order by D.type ";
  const data = [tenantId, ledgerReferenceId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getByTenantIdAndPropId = async ({ tenantId, propId }: duesTypes) => {
  const query =
    "Select  D.id, D.remarks, D.title, D.tallyStatus, D.tallyBillRef, D.ledgerReferenceId, D.discount, D.rentStartDate, D.rentEndDate, D.occupancyId, D.tenantId, D.propId, D.roomId, D.clientId, D.amount,D.balance, D.dueDate, D.type, D.createdAt, D.updatedAt from Dues as D where D.tenantId = ? and D.propId = ? order by D.type ";
  const data = [tenantId, propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getByLedgerReferenceId = async ({ ledgerReferenceId, tenantId, clientId }: duesTypes) => {
  const query =
    "Select * from Dues where ledgerReferenceId = ? and tenantId = ? and clientId = ?";
  const data = [ledgerReferenceId, tenantId, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

// duesDB.getTotalBalanceByLedgerReferenceId = async ({
//   ledgerReferenceId,
//   clientId,
//   tenantId,
// }: duesTypes) => {
//   const query =
//     "Select  SUM(D.balance) as totalBalance from Dues as D where D.ledgerReferenceId = ? and D.clientId = ? and D.tenantId = ?";
//   const data = [ledgerReferenceId, clientId, tenantId];
//   const [rows] = await DB.execute<RowDataPacket[]>(query, data);
//   if (rows?.length > 0) return rows[0];
//   else return false;
// };

duesDB.getByIdForTransaction = async ({ id }: duesTypes) => {
  const query =
    "Select  D.id, D.remarks, D.tallyBillRef, D.tallyStatus, D.description, D.ledgerReferenceId, D.discount, D.rentStartDate, D.rentEndDate, D.occupancyId, D.tenantId, D.propId, D.roomId, D.clientId, D.amount, D.balance, D.dueDate, D.type, D.title, D.createdAt, D.updatedAt from Dues as D where D.id = ?";
  const data = [id];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getAllDues = async ({ ids }: any) => {
  const query = `Select * from Dues where id in (${ids}) order by type`;
  const [rows] = await DB.execute<RowDataPacket[]>(query);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getTenantAllDues = async ({ tenantIds, clientId }: any) => {
  const query = `Select * from Dues where clientId=? and tenantId in (${tenantIds}) order by type`;
  const data = [clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getByTenantIdAndPropIdAndRoomId = async ({
  tenantId,
  propId,
  roomId,
}: duesTypes) => {
  const query =
    "Select * from Dues where tenantId = ? and propId = ? and roomId = ? and type != 2";
  const data = [tenantId, propId, roomId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

duesDB.getTotalDuesByType = async ({
  clientId,
  type,
  year,
  month,
}: duesTypes & { year: any; month: any }) => {
  const query =
    "Select SUM(D.balance) as rentDuesByType, MONTH(D.dueDate) as month from Dues as D where D.clientId=? and type = ? and YEAR(D.dueDate) = ? and MONTH(D.dueDate)= ? Group By month Order by month";
  const data = [clientId, type, year, month];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

duesDB.getTotalDuesByTypeAndDateRange = async ({
  clientId,
  type,
  startDate,
  endDate,
}: duesTypes & { startDate: any; endDate: any }) => {
  const query =
    "Select SUM(D.balance) as rentDuesByType from Dues as D where D.clientId=? and type = ? and DATE(D.dueDate) between ? and ?";
  const data = [clientId, type, startDate, endDate];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

duesDB.getTotalUtilityDuesForMonth = async ({
  clientId,
  startDate,
  endDate,
}: duesTypes & { startDate: any; endDate: any }) => {
  const query =
    "Select SUM(D.balance) as utilityDues from Dues as D where D.clientId=? and type in (?, ?, ?, ?, ?) and DATE(D.dueDate) between ? and ?";
  const data = [
    clientId,
    CONSTANTS.DUES_TYPES.WIFI,
    CONSTANTS.DUES_TYPES.MAID_CHARGES,
    CONSTANTS.DUES_TYPES.ELECTRICITY,
    CONSTANTS.DUES_TYPES.LAUNDRY_CHARGES,
    CONSTANTS.DUES_TYPES.GAS,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

duesDB.getTotalOtherDuesForMonth = async ({
  clientId,
  startDate,
  endDate,
}: duesTypes & { startDate: any; endDate: any }) => {
  const query =
    "Select SUM(D.balance) as otherDues from Dues as D where D.clientId=? and type not in (?, ?, ?, ?, ?, ?) and DATE(D.dueDate) between ? and ? ";
  const data = [
    clientId,
    CONSTANTS.DUES_TYPES.WIFI,
    CONSTANTS.DUES_TYPES.MAID_CHARGES,
    CONSTANTS.DUES_TYPES.ELECTRICITY,
    CONSTANTS.DUES_TYPES.LAUNDRY_CHARGES,
    CONSTANTS.DUES_TYPES.GAS,
    CONSTANTS.DUES_TYPES.RENT,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

duesDB.getTotalFineDuesForMonth = async ({
  clientId,
  startDate,
  endDate,
}: duesTypes & { startDate: any; endDate: any }) => {
  const query =
    "Select SUM(D.balance) as fineDues from Dues as D where D.clientId=? and D.type = ? and DATE(D.dueDate) between ? and ?";
  const data = [
    clientId,
    CONSTANTS.DUES_TYPES.FINE,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

duesDB.getTotalDuesByTypeByPropId = async ({
  propId,
  type,
  year,
  month,
}: duesTypes & { year: any; month: any }) => {
  const query =
    "Select SUM(D.balance) as rentDuesByType, MONTH(D.dueDate) as month from Dues as D where D.propId = ? and D.type = ? and YEAR(D.dueDate) = ? and MONTH(D.dueDate)= ? Group By month Order by month";
  const data = [propId, type, year, month];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

duesDB.getTotalDuesByTypeByPropIdAndDateRange = async ({
  propId,
  type,
  startDate,
  endDate,
}: duesTypes & { startDate: any; endDate: any }) => {
  const query =
    "Select SUM(D.balance) as rentDuesByType from Dues as D where D.propId = ? and D.type = ? and DATE(D.dueDate) between ? and ?";
  const data = [propId, type, startDate,endDate];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

duesDB.getTotalUtilityDuesByPropId = async ({
  propId,
  startDate,
  endDate,
}: duesTypes & { startDate: any; endDate: any }) => {
  const query =
    "Select SUM(D.balance) as utilityDues from Dues as D where D.propId = ? and D.type in (?,?,?,?,?) and DATE(D.dueDate) between ? and ?";
  const data = [
    propId, 
    CONSTANTS.DUES_TYPES.WIFI,
    CONSTANTS.DUES_TYPES.MAID_CHARGES,
    CONSTANTS.DUES_TYPES.ELECTRICITY,
    CONSTANTS.DUES_TYPES.LAUNDRY_CHARGES,
    CONSTANTS.DUES_TYPES.GAS,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

duesDB.getTotalOtherDuesByPropId = async ({
  propId,
  startDate,
  endDate,
}: duesTypes & { startDate: any; endDate: any }) => {
  const query =
    "Select SUM(D.balance) as otherDues from Dues as D where D.propId = ? and D.type not in (?,?,?,?,?,?) and DATE(D.dueDate) between ? and ?";
  const data = [
    propId, 
    CONSTANTS.DUES_TYPES.WIFI,
    CONSTANTS.DUES_TYPES.MAID_CHARGES,
    CONSTANTS.DUES_TYPES.ELECTRICITY,
    CONSTANTS.DUES_TYPES.LAUNDRY_CHARGES,
    CONSTANTS.DUES_TYPES.GAS,
    CONSTANTS.DUES_TYPES.RENT,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

duesDB.getTotalFineDuesByPropId = async ({
  propId,
  startDate,
  endDate,
}: duesTypes & { startDate: any; endDate: any }) => {
  const query =
    "Select SUM(D.balance) as fineDues from Dues as D where D.propId = ? and D.type = ? and DATE(D.dueDate) between ? and ?";
  const data = [
    propId, 
    CONSTANTS.DUES_TYPES.FINE,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

duesDB.getTotalDuesByTypeByLocationAndDateRange = async ({
  locationId,
  type,
  startDate,
  endDate,
}: duesTypes & { locationId: number; startDate: any; endDate: any }) => {
  const query =
    "Select SUM(D.balance) as rentDuesByType from Dues as D join Properties as P on P.id = D.propId where P.locationId = ? and D.type = ? and DATE(D.dueDate) between ? and ?";
  const data = [locationId, type, startDate,endDate];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

duesDB.getTotalUtilityDuesByLocation = async ({
  locationId,
  startDate,
  endDate,
}: duesTypes & { locationId: number; startDate: any; endDate: any }) => {
  const query =
    "Select SUM(D.balance) as utilityDues from Dues as D join Properties as P on P.id = D.propId where P.locationId = ? and D.type in (?,?,?,?,?) and DATE(D.dueDate) between ? and ?";
  const data = [
    locationId, 
    CONSTANTS.DUES_TYPES.WIFI,
    CONSTANTS.DUES_TYPES.MAID_CHARGES,
    CONSTANTS.DUES_TYPES.ELECTRICITY,
    CONSTANTS.DUES_TYPES.LAUNDRY_CHARGES,
    CONSTANTS.DUES_TYPES.GAS,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

duesDB.getTotalOtherDuesByLocation = async ({
  locationId,
  startDate,
  endDate,
}: duesTypes & { locationId: number; startDate: any; endDate: any }) => {
  const query =
    "Select SUM(D.balance) as otherDues from Dues as D join Properties as P on P.id = D.propId where P.locationId = ? and D.type not in (?,?,?,?,?,?) and DATE(D.dueDate) between ? and ?";
  const data = [
    locationId, 
    CONSTANTS.DUES_TYPES.WIFI,
    CONSTANTS.DUES_TYPES.MAID_CHARGES,
    CONSTANTS.DUES_TYPES.ELECTRICITY,
    CONSTANTS.DUES_TYPES.LAUNDRY_CHARGES,
    CONSTANTS.DUES_TYPES.GAS,
    CONSTANTS.DUES_TYPES.RENT,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

duesDB.getTotalFineDuesByLocation = async ({
  locationId,
  startDate,
  endDate,
}: duesTypes & { locationId: number; startDate: any; endDate: any }) => {
  const query =
    "Select SUM(D.balance) as fineDues from Dues as D join Properties as P on P.id = D.propId where P.locationId = ? and D.type = ? and DATE(D.dueDate) between ? and ?";
  const data = [
    locationId, 
    CONSTANTS.DUES_TYPES.FINE,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

duesDB.getTotalDuesByTypeByPropIdTenantNameMobile = async ({
  propId,
  type,
  year,
  month,
  searchVal,
}: duesTypes & { year: any; month: any; searchVal: string }) => {
  const query =
    "Select SUM(D.balance) as rentDuesByType, MONTH(D.dueDate) as month from Dues as D join Tenants as T on D.tenantId = T.id where D.propId = ? and D.type = ? and YEAR(D.dueDate) = ? and MONTH(D.dueDate)= ? and (T.name like ? or T.mobile like ?) Group By month Order by month";
  const data = [propId, type, year, month, `%${searchVal}%`, `%${searchVal}%`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

duesDB.getTotalDuesByTypeByPropIdDateRange = async ({
  propId,
  type,
  startDate,
  endDate,
}: duesTypes & { startDate: string; endDate: string }) => {
  const query =
    "Select SUM(D.balance) as rentDuesByType from Dues as D where D.propId = ? and D.type = ? and DATE(D.dueDate) BETWEEN ? and ? ";
  const data = [propId, type, startDate, endDate];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

duesDB.getTotalOverduesDuesByPropId = async ({
  propId,
  year,
  month,
}: duesTypes & { year: any; month: any }) => {
  let customData = month + "_" + year;
  const query =
    "Select SUM(balance)as overDues from Dues as D where propId = ? and DATE(D.dueDate)<=curdate() and concat(MONTH(D.dueDate), '_', YEAR(D.dueDate)) != ?";
  const data = [propId, customData];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getTotalOverduesDuesByLocation = async ({
  locationId,
  year,
  month,
}: duesTypes & { locationId: number; year: any; month: any }) => {
  let customData = month + "_" + year;
  const query =
    "Select SUM(D.balance) as overDues from Dues as D join Properties as P on P.id = D.propId where P.locationId = ? and DATE(D.dueDate)<=curdate() and concat(MONTH(D.dueDate), '_', YEAR(D.dueDate)) != ?";
  const data = [locationId, customData];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getTotalOverduesDuesByPropIdTenantNameMobile = async ({
  propId,
  year,
  month,
  searchVal,
}: duesTypes & { year: any; month: any; searchVal: string }) => {
  let customData = month + "_" + year;
  const query =
    "Select SUM(D.balance)as overDues from Dues as D join Tenants as T on D.tenantId = T.id where D.propId = ? and DATE(D.dueDate)<=curdate() and concat(MONTH(D.dueDate), '_', YEAR(D.dueDate)) != ? and T.name like ? or T.mobile like ?";
  const data = [propId, customData, `%${searchVal}%`, `%${searchVal}%`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getTotalOverduesByClientId = async ({
  clientId,
  year,
  month,
}: duesTypes & { month: any; year: any }) => {
  let customData = month + "_" + year;
  const query =
    "Select SUM(balance)as overDues from Dues where clientId = ? and DATE(createdAt)<=curdate() and concat(MONTH(dueDate), '_', YEAR(dueDate)) != ?";
  const data = [clientId, customData];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getTotalOverduesByClientIdForStaff = async ({
  clientId,
  propertiesIds,
  year,
  month,
}: duesTypes & { month: any; year: any; propertiesIds: string; }) => {
  let customData = month + "_" + year;
  const query =
    `Select SUM(balance)as overDues from Dues where clientId = ? and propId in (${propertiesIds}) and DATE(createdAt)<=curdate() and concat(MONTH(dueDate), '_', YEAR(dueDate)) != ?`;
  const data = [clientId, customData];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getTotalOverduesDuesForStaff = async ({
  clientId,
  propertiesIds,
  month,
}: duesTypes & { propertiesIds: string; year: any; month: any }) => {
  const query = `Select SUM(balance)as totalDues from Dues as D where D.clientId=? and D.propId in (${propertiesIds}) and MONTH(D.dueDate) < ?`;
  const data = [clientId, month];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getTotalDuesByTypeForStaff = async ({
  clientId,
  propertiesIds,
  type,
  year,
  month,
}: duesTypes & { propertiesIds: string; year: any; month: any }) => {
  const query = `Select SUM(D.balance) as totalDues, MONTH(D.dueDate) as month from Dues as D where D.clientId=? and D.propId in (${propertiesIds}) and D.type = ? and YEAR(D.dueDate) = ? and MONTH(D.dueDate)= ? Group By month Order by month`;
  const data = [clientId, type, year, month];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

duesDB.getTotalDuesByTypeAndDateRangeForStaff = async ({
  clientId,
  propertiesIds,
  type,
  startDate,
  endDate,
}: duesTypes & { propertiesIds: string; startDate: any; endDate: any }) => {
  const query = `Select SUM(D.balance) as totalDues from Dues as D where D.clientId=? and D.propId in (${propertiesIds}) and D.type = ? and DATE(D.dueDate) between ? and ?`;
  const data = [clientId, type, startDate, endDate];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

duesDB.getTotalUtilityDuesForStaff = async ({
  clientId,
  propertiesIds,
  startDate,
  endDate,
}: duesTypes & { propertiesIds: string; startDate: any; endDate: any }) => {
  const query = `Select SUM(D.balance) as utilityDues from Dues as D where D.clientId=? and D.propId in (${propertiesIds}) and D.type in (?,?,?,?,?) and DATE(D.dueDate) between ? and ?`;
  const data = [
    clientId, 
    CONSTANTS.DUES_TYPES.WIFI,
    CONSTANTS.DUES_TYPES.MAID_CHARGES,
    CONSTANTS.DUES_TYPES.ELECTRICITY,
    CONSTANTS.DUES_TYPES.LAUNDRY_CHARGES,
    CONSTANTS.DUES_TYPES.GAS,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

duesDB.getTotalOtherDuesForStaff = async ({
  clientId,
  propertiesIds,
  startDate,
  endDate,
}: duesTypes & { propertiesIds: string; startDate: any; endDate: any }) => {
  const query = `Select SUM(D.balance) as otherDues from Dues as D where D.clientId=? and D.propId in (${propertiesIds}) and D.type not in (?,?,?,?,?,?) and DATE(D.dueDate) between ? and ?`;
  const data = [
    clientId, 
    CONSTANTS.DUES_TYPES.WIFI,
    CONSTANTS.DUES_TYPES.MAID_CHARGES,
    CONSTANTS.DUES_TYPES.ELECTRICITY,
    CONSTANTS.DUES_TYPES.LAUNDRY_CHARGES,
    CONSTANTS.DUES_TYPES.GAS,
    CONSTANTS.DUES_TYPES.RENT,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

duesDB.getTotalFineDuesForStaff = async ({
  clientId,
  propertiesIds,
  startDate,
  endDate,
}: duesTypes & { propertiesIds: string; startDate: any; endDate: any }) => {
  const query = `Select SUM(D.balance) as fineDues from Dues as D where D.clientId=? and D.propId in (${propertiesIds}) and D.type = ? and DATE(D.dueDate) between ? and ?`;
  const data = [
    clientId, 
    CONSTANTS.DUES_TYPES.FINE,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

duesDB.getOverdueByClientIdandPage = async ({
  clientId,
  pageNum,
  limit,
  dueDate,
  year,
  month,
}: duesTypes & {
  pageNum: number;
  limit: number;
  year: number;
  month: number;
}) => {
  let customData = month + "_" + year;
  const query =
    "Select D.id, D.remarks, D.title, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? and DATE(D.dueDate)<=curdate() and concat(MONTH(D.dueDate), '_', YEAR(D.dueDate)) != ? order by D.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  const data = [CONSTANTS.DOCUMENT_TYPES.SELFI, clientId, customData, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getDueByTypeByClientIdandPage = async ({
  clientId,
  pageNum,
  limit,
  type,
  dueDate,
}: duesTypes & {
  pageNum: number;
  limit: number;
  year: number;
  month: number;
}) => {
  const query =
    "Select D.id, D.remarks, D.title, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? and MONTH(D.dueDate) = MONTH(?) and YEAR(D.dueDate) = YEAR(?) and D.type = ? order by D.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  const data = [clientId, dueDate, dueDate, type, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getDueByTypeByClientIdandPageAndDateRange = async ({
  clientId,
  pageNum,
  limit,
  type,
  startDate,
  endDate,
}: duesTypes & {
  pageNum: number;
  limit: number;
  startDate: string;
  endDate: string;
}) => {
  const query =
    "Select D.id, D.remarks, D.title, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? and DATE(D.dueDate) between ? and ? and D.type = ? order by D.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  const data = [CONSTANTS.DOCUMENT_TYPES.SELFI, clientId, startDate, endDate, type, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getUtilityDueByClientIdandPage = async ({
  clientId,
  pageNum,
  limit,
  startDate,
  endDate,
}: duesTypes & {
  pageNum: number;
  limit: number;
  startDate: string;
  endDate: string;
}) => {
  const query =
    "Select D.id, D.remarks, D.title, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? and DATE(D.dueDate) between ? and ? and D.type in (?,?,?,?,?) order by D.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  const data = [
    CONSTANTS.DOCUMENT_TYPES.SELFI,
    clientId, 
    startDate,
    endDate,
    CONSTANTS.DUES_TYPES.WIFI,
    CONSTANTS.DUES_TYPES.MAID_CHARGES,
    CONSTANTS.DUES_TYPES.ELECTRICITY,
    CONSTANTS.DUES_TYPES.LAUNDRY_CHARGES,
    CONSTANTS.DUES_TYPES.GAS,
    `${offset}`, 
    `${limit}`
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getOtherDueByClientIdandPage = async ({
  clientId,
  pageNum,
  limit,
  startDate,
  endDate,
}: duesTypes & {
  pageNum: number;
  limit: number;
  startDate: string;
  endDate: string;
}) => {
  const query =
    "Select D.id, D.remarks, D.title, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? and DATE(D.dueDate) between ? and ? and D.type not in (?,?,?,?,?,?) order by D.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  const data = [
    CONSTANTS.DOCUMENT_TYPES.SELFI,
    clientId, 
    startDate, 
    endDate, 
    CONSTANTS.DUES_TYPES.WIFI,
    CONSTANTS.DUES_TYPES.MAID_CHARGES,
    CONSTANTS.DUES_TYPES.ELECTRICITY,
    CONSTANTS.DUES_TYPES.LAUNDRY_CHARGES,
    CONSTANTS.DUES_TYPES.GAS,
    CONSTANTS.DUES_TYPES.RENT,
    `${offset}`, 
    `${limit}`
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getFineDueByClientIdandPage = async ({
  clientId,
  pageNum,
  limit,
  startDate,
  endDate,
}: duesTypes & {
  pageNum: number;
  limit: number;
  startDate: string;
  endDate: string;
}) => {
  const query =
    "Select D.id, D.remarks, D.title, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? and DATE(D.dueDate) between ? and ? and D.type = ? order by D.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  const data = [
    CONSTANTS.DOCUMENT_TYPES.SELFI,
    clientId, 
    startDate, 
    endDate, 
    CONSTANTS.DUES_TYPES.FINE,
    `${offset}`, 
    `${limit}`
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getOverdueByPropIdandPage = async ({
  propId,
  pageNum,
  limit,
  year,
  month,
}: duesTypes & {
  pageNum: number;
  limit: number;
  year: number;
  month: number;
}) => {
  let customData = month + "_" + year;
  const query =
    "Select D.id, D.remarks, D.title, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.propId = ? and DATE(D.dueDate)<=curdate() and concat(MONTH(D.dueDate), '_', YEAR(D.dueDate)) != ? order by D.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  const data = [CONSTANTS.DOCUMENT_TYPES.SELFI, customData, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getOverdueByPropIdandPageAndFilterAndOrderByAmount = async ({
  propId,
  pageNum,
  limit,
  year,
  month,
  type,
  ordBy,
}: duesTypes & {
  pageNum: number;
  limit: number;
  year: number;
  month: number;
  ordBy: string;
}) => {
  let customData = month + "_" + year;
  const sortDirection = ordBy.toUpperCase() === 'ASC' ? 'ASC' : 'DESC';
  const query =
    `Select D.id, D.remarks, D.title, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId inner join (SELECT tenantId, SUM(amount) AS totalDue FROM Dues WHERE propId = ? and DATE(dueDate)<=curdate() and concat(MONTH(dueDate), '_', YEAR(dueDate)) != ? and type in (${type}) GROUP BY tenantId) AS TD ON TD.tenantId = D.tenantId where D.propId = ? and DATE(D.dueDate)<=curdate() and concat(MONTH(D.dueDate), '_', YEAR(D.dueDate)) != ? order by TD.totalDue ${sortDirection}, D.id desc limit ?, ?`;
  const offset: number = (pageNum - 1) * limit;
  const data = [propId, customData, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getDueByTypeByPropIdandPage = async ({
  propId,
  pageNum,
  limit,
  type,
  dueDate,
}: duesTypes & {
  pageNum: number;
  limit: number;
  year: number;
  month: number;
}) => {
  const query =
    "Select D.id, D.remarks, D.title, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.propId = ? and MONTH(D.dueDate) = MONTH(?) and YEAR(D.dueDate) = YEAR(?) and D.type = ? order by D.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  const data = [propId, dueDate, dueDate, type, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getDueByTypeByPropIdandPageAndDateRange = async ({
  propId,
  pageNum,
  limit,
  type,
  startDate,
  endDate,
}: duesTypes & {
  pageNum: number;
  limit: number;
  year: number;
  startDate: string;
  endDate: string;
}) => {
  const query =
    "Select D.id, D.remarks, D.title, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.propId = ? and DATE(D.dueDate) between ? and ? and D.type = ? order by D.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  const data = [CONSTANTS.DOCUMENT_TYPES.SELFI, propId, startDate, endDate, type, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getDueByTypeByLocationIdandPageAndDateRange = async ({
  locationId,
  pageNum,
  limit,
  type,
  startDate,
  endDate,
}: duesTypes & {
  locationId: any;
  pageNum: number;
  limit: number;
  year: number;
  startDate: string;
  endDate: string;
}) => {
  const query =
    "Select D.id, D.remarks, D.title, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where P.locationId = ? and DATE(D.dueDate) between ? and ? and D.type = ? order by D.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  const data = [CONSTANTS.DOCUMENT_TYPES.SELFI, locationId, startDate, endDate, type, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getUtilityDueByPropIdandPage = async ({
  propId,
  pageNum,
  limit,
  startDate,
  endDate,
}: duesTypes & {
  pageNum: number;
  limit: number;
  year: number;
  startDate: string;
  endDate: string;
}) => {
  const query =
    "Select D.id, D.remarks, D.title, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.propId = ? and DATE(D.dueDate) between ? and ? and D.type in (?,?,?,?,?) order by D.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  const data = [
    CONSTANTS.DOCUMENT_TYPES.SELFI,
    propId, 
    startDate,
    endDate,
    CONSTANTS.DUES_TYPES.WIFI,
    CONSTANTS.DUES_TYPES.MAID_CHARGES,
    CONSTANTS.DUES_TYPES.ELECTRICITY,
    CONSTANTS.DUES_TYPES.LAUNDRY_CHARGES,
    CONSTANTS.DUES_TYPES.GAS, 
    `${offset}`, 
    `${limit}`
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getUtilityDueByLocationIdandPage = async ({
  locationId,
  pageNum,
  limit,
  startDate,
  endDate,
}: duesTypes & {
  locationId: any;
  pageNum: number;
  limit: number;
  year: number;
  startDate: string;
  endDate: string;
}) => {
  const query =
    "Select D.id, D.remarks, D.title, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where P.locationId = ? and DATE(D.dueDate) between ? and ? and D.type in (?,?,?,?,?) order by D.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  const data = [
    CONSTANTS.DOCUMENT_TYPES.SELFI,
    locationId, 
    startDate,
    endDate,
    CONSTANTS.DUES_TYPES.WIFI,
    CONSTANTS.DUES_TYPES.MAID_CHARGES,
    CONSTANTS.DUES_TYPES.ELECTRICITY,
    CONSTANTS.DUES_TYPES.LAUNDRY_CHARGES,
    CONSTANTS.DUES_TYPES.GAS, 
    `${offset}`, 
    `${limit}`
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getOtherDueByPropIdandPage = async ({
  propId,
  pageNum,
  limit,
  startDate,
  endDate,
}: duesTypes & {
  pageNum: number;
  limit: number;
  year: number;
  startDate: string;
  endDate: StaticRange;
}) => {
  const query =
    "Select D.id, D.remarks, D.title, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.propId = ? and DATE(D.dueDate) between ? and ? and D.type not in (?,?,?,?,?, ?) order by D.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  const data = [
    CONSTANTS.DOCUMENT_TYPES.SELFI,
    propId, 
    startDate,
    endDate,
    CONSTANTS.DUES_TYPES.WIFI,
    CONSTANTS.DUES_TYPES.MAID_CHARGES,
    CONSTANTS.DUES_TYPES.ELECTRICITY,
    CONSTANTS.DUES_TYPES.LAUNDRY_CHARGES,
    CONSTANTS.DUES_TYPES.GAS, 
    CONSTANTS.DUES_TYPES.RENT,
    `${offset}`, 
    `${limit}`
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getOtherDueByLocationIdandPage = async ({
  locationId,
  pageNum,
  limit,
  startDate,
  endDate,
}: duesTypes & {
  locationId: any
  pageNum: number;
  limit: number;
  year: number;
  startDate: string;
  endDate: StaticRange;
}) => {
  const query =
    "Select D.id, D.remarks, D.title, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where P.locationId = ? and DATE(D.dueDate) between ? and ? and D.type not in (?,?,?,?,?, ?) order by D.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  const data = [
    CONSTANTS.DOCUMENT_TYPES.SELFI,
    locationId, 
    startDate,
    endDate,
    CONSTANTS.DUES_TYPES.WIFI,
    CONSTANTS.DUES_TYPES.MAID_CHARGES,
    CONSTANTS.DUES_TYPES.ELECTRICITY,
    CONSTANTS.DUES_TYPES.LAUNDRY_CHARGES,
    CONSTANTS.DUES_TYPES.GAS, 
    CONSTANTS.DUES_TYPES.RENT,
    `${offset}`, 
    `${limit}`
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getFineDueByPropIdandPage = async ({
  propId,
  pageNum,
  limit,
  startDate,
  endDate,
}: duesTypes & {
  pageNum: number;
  limit: number;
  year: number;
  startDate: string;
  endDate: StaticRange;
}) => {
  const query =
    "Select D.id, D.remarks, D.title, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.propId = ? and DATE(D.dueDate) between ? and ? and D.type = ? order by D.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  const data = [
    CONSTANTS.DOCUMENT_TYPES.SELFI,
    propId, 
    startDate,
    endDate,
    CONSTANTS.DUES_TYPES.FINE,
    `${offset}`, 
    `${limit}`
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getFineDueByLocationIdandPage = async ({
  locationId,
  pageNum,
  limit,
  startDate,
  endDate,
}: duesTypes & {
  locationId: any
  pageNum: number;
  limit: number;
  year: number;
  startDate: string;
  endDate: StaticRange;
}) => {
  const query =
    "Select D.id, D.remarks, D.title, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where P.locationId = ? and DATE(D.dueDate) between ? and ? and D.type = ? order by D.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  const data = [
    CONSTANTS.DOCUMENT_TYPES.SELFI,
    locationId, 
    startDate,
    endDate,
    CONSTANTS.DUES_TYPES.FINE,
    `${offset}`, 
    `${limit}`
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getAllByPropIdandPage = async ({
  propId,
  pageNum,
  limit,
  dueDate,
}: duesTypes & { pageNum: number; limit: number }) => {
  const query =
    "Select D.id, D.remarks, D.title, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.propId = ? and DATE_FORMAT(D.dueDate, '%Y-%m') <= DATE_FORMAT(?, '%Y-%m') order by D.dueDate desc, D.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  const data = [propId, dueDate, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getAllByClientIdandPage = async ({
  clientId,
  pageNum,
  limit,
  dueDate,
}: duesTypes & { pageNum: number; limit: number }) => {
  const query =
    "Select D.id, D.remarks, D.title, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? and DATE_FORMAT(D.dueDate, '%Y-%m') <= DATE_FORMAT(?, '%Y-%m') order by D.dueDate desc, D.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  const data = [clientId, dueDate, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getAllForStaffByPage = async ({
  propertiesIds,
  pageNum,
  limit,
}: {
  pageNum: number;
  limit: number;
  propertiesIds: string;
}) => {
  const query =
    "Select D.id, D.remarks, D.title, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.propId in (" +
    `${propertiesIds}` +
    ") order by D.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  //const data = [propertiesIds, `${offset}`, `${limit}`];
  const data = [`${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getOverduesForStaffByPage = async ({
  propertiesIds,
  pageNum,
  limit,
  year,
  month,
}: {
  pageNum: number;
  limit: number;
  propertiesIds: string;
  month: number;
  year: number;
}) => {
  let customData = month + "_" + year;
  const query =
    "Select D.id, D.remarks, D.title, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.propId in (" +
    `${propertiesIds}` +
    ") and DATE(D.dueDate)<=curdate() and concat(MONTH(D.dueDate), '_', YEAR(D.dueDate)) != ?  order by D.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  //const data = [propertiesIds, `${offset}`, `${limit}`];
  const data = [CONSTANTS.DOCUMENT_TYPES.SELFI, customData, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getDueByTypeForStaffByPage = async ({
  propertiesIds,
  pageNum,
  limit,
  type,
  dueDate,
}: duesTypes & {
  pageNum: number;
  limit: number;
  propertiesIds: string;
}) => {
  const query =
    "Select D.id, D.remarks, D.title, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.propId in (" +
    `${propertiesIds}` +
    ") and MONTH(D.dueDate) = MONTH(?) and YEAR(D.dueDate) = YEAR(?) and D.type = ? order by D.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  //const data = [propertiesIds, `${offset}`, `${limit}`];
  const data = [CONSTANTS.DOCUMENT_TYPES.SELFI, dueDate, dueDate, type, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getDueByTypeAndDateRangeForStaffByPage = async ({
  propertiesIds,
  pageNum,
  limit,
  type,
  startDate,
  endDate,
}: duesTypes & {
  pageNum: number;
  limit: number;
  propertiesIds: string;
  startDate:string;
  endDate: string;
}) => {
  const query =
    "Select D.id, D.remarks, D.title, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.propId in (" +
    `${propertiesIds}` +
    ") and DATE(D.dueDate) between ? and ? and D.type = ? order by D.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  //const data = [propertiesIds, `${offset}`, `${limit}`];
  const data = [startDate, endDate, type, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getUtilityDueForStaffByPage = async ({
  propertiesIds,
  pageNum,
  limit,
  startDate,
  endDate,
}: duesTypes & {
  pageNum: number;
  limit: number;
  propertiesIds: string;
  startDate: string;
  endDate: string;
}) => {
  const query =
    "Select D.id, D.remarks, D.title, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.propId in (" +
    `${propertiesIds}` +
    ") and DATE(D.dueDate) between ? and ? and D.type in (?,?,?,?,?) order by D.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  //const data = [propertiesIds, `${offset}`, `${limit}`];
  const data = [
    CONSTANTS.DOCUMENT_TYPES.SELFI,
    startDate,
    endDate,
    CONSTANTS.DUES_TYPES.WIFI,
    CONSTANTS.DUES_TYPES.MAID_CHARGES,
    CONSTANTS.DUES_TYPES.ELECTRICITY,
    CONSTANTS.DUES_TYPES.LAUNDRY_CHARGES,
    CONSTANTS.DUES_TYPES.GAS,
    `${offset}`, 
    `${limit}`
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getOtherDueForStaffByPage = async ({
  propertiesIds,
  pageNum,
  limit,
  startDate,
  endDate,
}: duesTypes & {
  pageNum: number;
  limit: number;
  propertiesIds: string;
  startDate: string;
  endDate: string;
}) => {
  const query =
    "Select D.id, D.remarks, D.title, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.propId in (" +
    `${propertiesIds}` +
    ") and DATE(D.dueDate) between ? and ? and D.type not in (?,?,?,?,?,?) order by D.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  //const data = [propertiesIds, `${offset}`, `${limit}`];
  const data = [
    CONSTANTS.DOCUMENT_TYPES.SELFI,
    startDate,
    endDate,
    CONSTANTS.DUES_TYPES.WIFI,
    CONSTANTS.DUES_TYPES.MAID_CHARGES,
    CONSTANTS.DUES_TYPES.ELECTRICITY,
    CONSTANTS.DUES_TYPES.LAUNDRY_CHARGES,
    CONSTANTS.DUES_TYPES.GAS,
    CONSTANTS.DUES_TYPES.RENT,
    `${offset}`, 
    `${limit}`
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getFineDueForStaffByPage = async ({
  propertiesIds,
  pageNum,
  limit,
  startDate,
  endDate,
}: duesTypes & {
  pageNum: number;
  limit: number;
  propertiesIds: string;
  startDate: string;
  endDate: string;
}) => {
  const query =
    "Select D.id, D.remarks, D.title, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.propId in (" +
    `${propertiesIds}` +
    ") and DATE(D.dueDate) between ? and ? and D.type = ? order by D.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  //const data = [propertiesIds, `${offset}`, `${limit}`];
  const data = [
    CONSTANTS.DOCUMENT_TYPES.SELFI,
    startDate,
    endDate,
    CONSTANTS.DUES_TYPES.FINE,
    `${offset}`, 
    `${limit}`
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

// duesDB.getTotalDuesByClientIdYearMonth = async ({
//   clientId,
//   year,
//   month,
// }: duesTypes & { year: any; month: any }) => {
//   const query =
//     "Select SUM(balance)as totalDues from Dues as D where clientId = ? and MONTH(D.createdAt) = ? and YEAR(D.createdAt) = ?";
//   const query1 =
//     "Select SUM(D.balance) as totalDues, MONTH(D.createdAt) as month from Dues as D where D.clientId=? and YEAR(D.createdAt) = ? and MONTH(D.createdAt)= ? Group By month Order by month";
//   const data = [clientId, month, year];
//   const [rows] = await DB.execute<RowDataPacket[]>(query, data);
//   if (rows?.length > 0) return rows[0];
//   else return false;
// };

duesDB.updateOccupancyByTenant = async ({
  tenantId,
  clientId,
  occupancyId,
  propId,
  roomId,
}: duesTypes) => {
  const query =
    "update Dues set occupancyId = ?, propId = ?, roomId = ? where tenantId = ? and clientId = ?";
  const data = [occupancyId, propId, roomId, tenantId, clientId];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

duesDB.updateOccupancyForSwitchProp = async ({
  tenantId,
  clientId,
  occupancyId,
  propId,
  roomId,
}: duesTypes) => {
  const query =
    "update Dues set occupancyId = ?, propId = ?, roomId = ? where tenantId = ? and clientId = ?";
  const data = [occupancyId, propId, roomId, tenantId, clientId];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

duesDB.getLastRentDue = async ({
  tenantId,
  clientId,
  shiftingDate,
}: duesTypes & { shiftingDate: any }) => {
  const query =
    "Select * from Dues where tenantId = ? and clientId = ? and type = ? and (Month(rentStartDate) = Month(?) or Month(rentEndDate) = Month(?)) and (YEAR(rentStartDate) = YEAR(?) or Year(rentEndDate) = Year(?)) order by id desc limit 1";
  const data = [
    tenantId,
    clientId,
    CONSTANTS.DUES_TYPES.RENT,
    shiftingDate,
    shiftingDate,
    shiftingDate,
    shiftingDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.updateLastRentDue = async ({
  tenantId,
  clientId,
  ledgerReferenceId,
  amount,
  balance,
  rentStartDate,
  rentEndDate,
}: duesTypes & { dueId: string }) => {
  const query =
    "update Dues set amount = ?, balance = ?, rentStartDate = ?, rentEndDate = ? where tenantId = ? and clientId = ? and ledgerReferenceId = ?";
  const data = [
    amount,
    balance,
    rentStartDate,
    rentEndDate,
    tenantId,
    clientId,
    ledgerReferenceId,
  ];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

duesDB.removeAllDues = async ({ tenantId, clientId }: duesTypes) => {
  const query = "delete from Dues where tenantId = ? and clientId = ?";
  const data = [tenantId, clientId];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

duesDB.getByPropId = async ({
  propId,
  startDate,
  endDate,
}: duesTypes & { startDate: string; endDate: string }) => {
  const query =
    "Select D.id, D.title, D.balance, D.remarks, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.propId = ? order by D.id desc";
  const data = [CONSTANTS.DOCUMENT_TYPES.SELFI, propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getByClientId = async ({
  clientId,
}: duesTypes & { startDate: string; endDate: string }) => {
  const query =
    "Select D.id, D.title, D.balance, D.remarks, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? order by D.id desc";
  const data = [CONSTANTS.DOCUMENT_TYPES.SELFI, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getByClientIdForStaff = async ({
  clientId,
  propertiesIds,
}: duesTypes & { startDate: string; endDate: string; propertiesIds: string }) => {
  const query =
    `Select D.id, D.title, D.balance, D.remarks, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? and D.propId in (${propertiesIds}) order by D.id desc`;
  const data = [CONSTANTS.DOCUMENT_TYPES.SELFI, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getByClientIdAndLocation = async ({
  clientId,
  locationId,
  startDate,
  endDate,
}: duesTypes & { locationId: number; startDate: string; endDate: string; }) => {
  const query =
    "Select D.id, D.title, D.balance, D.remarks, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? and P.locationId = ? and DATE(D.dueDate) BETWEEN ? and ? order by D.id desc";
  const data = [
    CONSTANTS.DOCUMENT_TYPES.SELFI, 
    clientId, 
    locationId, 
    startDate, 
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getByPropIdandDateRange = async ({
  propId,
  startDate,
  endDate,
}: duesTypes & { startDate: string; endDate: string }) => {
  const query =
    "Select D.id, D.title, D.propId, D.roomId, D.remarks, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.description, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.propId = ? and DATE(D.dueDate) BETWEEN ? and ? order by D.id desc";
  const data = [CONSTANTS.DOCUMENT_TYPES.SELFI, propId, startDate, endDate];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getByPropIdsandDateRange = async ({
  propId,
  startDate,
  endDate,
}: { propId: any; startDate: string; endDate: string }) => {
  const query =
    `Select D.id, D.title, D.propId, D.roomId, D.remarks, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.description, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.propId in (${propId.map(() => '?').join(',')}) and DATE(D.dueDate) BETWEEN ? and ? order by D.id desc`;
  const data = [CONSTANTS.DOCUMENT_TYPES.SELFI, ...propId, startDate, endDate];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getCurMonthRentDefaulterCountByClientId = async ({
  clientId,
}: duesTypes) => {
  const query =
    "Select COUNT(Distinct(D.id)) as count from Dues as D where D.clientId = ? and D.type = ? and Month(D.dueDate) = Month(NOW()) and YEAR(D.dueDate) = YEAR(NOW()) and Date(D.dueDate) < Date(NOW())";
  const data = [clientId, CONSTANTS.DUES_TYPES.RENT];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getCurMonthRentDefaulterCountByClientIdForStaff = async ({
  clientId,
  propertiesIds,
}: duesTypes & {propertiesIds: string}) => {
  const query =
    `Select COUNT(Distinct(D.id)) as count from Dues as D where D.clientId = ? and D.type = ? and Month(D.dueDate) = Month(NOW()) and YEAR(D.dueDate) = YEAR(NOW()) and Date(D.dueDate) < Date(NOW()) and D.propId in (${propertiesIds})`;
  const data = [clientId, CONSTANTS.DUES_TYPES.RENT];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getCurMonthRentDefaulterCountByPropId = async ({
  propId,
}: duesTypes) => {
  const query =
    "Select COUNT(Distinct(D.id)) as count from Dues as D where D.propId = ? and D.type = ? and Month(D.dueDate) = Month(NOW()) and YEAR(D.dueDate) = YEAR(NOW()) and Date(D.dueDate) < Date(NOW())";
  const data = [propId, CONSTANTS.DUES_TYPES.RENT];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getOldestRentDue = async ({ tenantId, clientId }: duesTypes) => {
  const query =
    "Select * from Dues as D where D.tenantId = ? and D.clientId = ? and D.type = ? order by D.id limit 1";
  const data = [tenantId, clientId, CONSTANTS.DUES_TYPES.RENT];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.getByTenantNameMobile = async ({
  propId,
  searchVal,
}: duesTypes & { searchVal: string }) => {
  const query =
    "Select D.id, D.remarks, D.title, D.propId, D.roomId, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, DATE_FORMAT(D.dueDate, '%Y-%m-%d') as dueDate, D.description, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.propId = ? and (T.name like ? or T.mobile like ?) order by D.id desc";
  const data = [propId, `%${searchVal}%`, `%${searchVal}%`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getByPropIdAndSearch = async ({
  propId,
  searchVal,
  searchType,
}: duesTypes & { searchVal: string; searchType: string; }) => {
  const data: any = [propId,];
  let filterCondition = "";
  if (Number(searchType) === 1) {
    filterCondition = " and T.mobile like ?";
    data.push(`${searchVal}%`);
  } else if (Number(searchType) === 2) {
    filterCondition = " and T.name like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 3) {
    filterCondition = " and P.name like ?";
    data.push(`%${searchVal}%`);
  }
  const query =
    `Select D.id, D.remarks, D.title, D.propId, D.roomId, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, DATE_FORMAT(D.dueDate, '%Y-%m-%d') as dueDate, D.description, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.propId = ? ${filterCondition} order by D.id desc`;
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getByTenantNameMobileForClient = async ({
  clientId,
  searchVal,
  searchType,
}: duesTypes & { searchVal: string; searchType: string; }) => {
  const data: any = [clientId,];
  let filterCondition = "";
  if (Number(searchType) === 1) {
    filterCondition = " and T.mobile like ?";
    data.push(`${searchVal}%`);
  } else if (Number(searchType) === 2) {
    filterCondition = " and T.name like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 3) {
    filterCondition = " and P.name like ?";
    data.push(`%${searchVal}%`);
  }
  const query =
    `Select D.id, D.remarks, D.title, D.propId, D.roomId, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, DATE_FORMAT(D.dueDate, '%Y-%m-%d') as dueDate, D.description, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? ${filterCondition} order by D.id desc`;
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getByClientIdandDateRange = async ({
  clientId,
  startDate,
  endDate,
}: duesTypes & { startDate: string; endDate: string }) => {
  const query =
    "Select D.id, D.remarks, D.title, D.propId, D.roomId, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.description, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? and P.status = ? and DATE(D.dueDate) BETWEEN ? and ? order by D.id desc";
  const data = [CONSTANTS.DOCUMENT_TYPES.SELFI, clientId, CONSTANTS.PROPERTY_STATUS.ACTIVE, startDate, endDate];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getPendingByClientId = async ({
  clientId
}: duesTypes) => {
  const query =
    "Select D.id, D.remarks, D.title, D.propId, D.roomId, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.description, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? and P.status = ? order by D.id desc";
  const data = [CONSTANTS.DOCUMENT_TYPES.SELFI, clientId, CONSTANTS.PROPERTY_STATUS.ACTIVE];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getByClientIdandDateRangeAndSortBy = async ({
  clientId,
  startDate,
  endDate,
  sortBy,
}: duesTypes & { startDate: string; endDate: string; sortBy: string; }) => {
  let sortDirection = (sortBy === "MA" || sortBy === "DDMA") ? "DESC" : "ASC";
  let query =
    "Select D.id, D.remarks, D.title, D.propId, D.roomId, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.description, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId inner join (SELECT tenantId, SUM(amount) AS totalDue FROM Dues WHERE clientId = ? AND Date(dueDate) BETWEEN ? and ? GROUP BY tenantId) AS TD ON TD.tenantId = D.tenantId where D.clientId = ? and P.status = ? and DATE(D.dueDate) BETWEEN ? and ? ";
  const data = [CONSTANTS.DOCUMENT_TYPES.SELFI, clientId, startDate, endDate, clientId, CONSTANTS.PROPERTY_STATUS.ACTIVE, startDate, endDate];

  if (sortBy && sortBy === 'MA') {
    query += ` order by TD.totalDue ${sortDirection}, D.id desc`;
  } else if (sortBy && sortBy === 'MI') {
    query += ` order by TD.totalDue ${sortDirection}, D.id desc`;
  } else if (sortBy && sortBy === 'DDMA') {
    query += ` order by D.dueDate desc, D.id desc`;
  } else if (sortBy && sortBy === 'DDMI') {
    query += ` order by D.dueDate asc, D.id desc`;
  } else {
    // Default
    query += ` order by D.id desc`;
  }

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

duesDB.getByClientIdAndPropIdandDateRangeAndSortBy = async ({
  clientId,
  propId,
  startDate,
  endDate,
  sortBy,
}: duesTypes & { startDate: string; endDate: string; sortBy: string; }) => {
  let sortDirection = (sortBy === "MA" || sortBy === "DDMA") ? "DESC" : "ASC";
  let query =
    "Select D.id, D.remarks, D.title, D.propId, D.roomId, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.description, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId inner join (SELECT tenantId, SUM(amount) AS totalDue FROM Dues WHERE clientId = ? and propId = ? AND Date(dueDate) BETWEEN ? and ? GROUP BY tenantId) AS TD ON TD.tenantId = D.tenantId where D.clientId = ? and D.propId = ? and P.status = ? and DATE(D.dueDate) BETWEEN ? and ? ";
  const data = [CONSTANTS.DOCUMENT_TYPES.SELFI, clientId, propId, startDate, endDate, clientId, propId, CONSTANTS.PROPERTY_STATUS.ACTIVE, startDate, endDate];

  if (sortBy && sortBy === 'MA') {
    query += ` order by TD.totalDue ${sortDirection}, D.id desc`;
  } else if (sortBy && sortBy === 'MI') {
    query += ` order by TD.totalDue ${sortDirection}, D.id desc`;
  } else if (sortBy && sortBy === 'DDMA') {
    query += ` order by D.dueDate desc, D.id desc`;
  } else if (sortBy && sortBy === 'DDMI') {
    query += ` order by D.dueDate asc, D.id desc`;
  } else {
    // Default
    query += ` order by D.id desc`;
  }

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

duesDB.getByClientIdAndLocationIdandDateRangeAndSortBy = async ({
  clientId,
  locationId,
  startDate,
  endDate,
  sortBy,
}: duesTypes & { locationId: any; startDate: string; endDate: string; sortBy: string; }) => {
  let sortDirection = (sortBy === "MA" || sortBy === "DDMA") ? "DESC" : "ASC";
  let query =
    "Select D.id, D.remarks, D.title, D.propId, D.roomId, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.description, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId inner join (SELECT D.tenantId, SUM(D.amount) AS totalDue FROM Dues as D join Properties as P WHERE D.clientId = ? and P.locationId = ? AND Date(dueDate) BETWEEN ? and ? GROUP BY tenantId) AS TD ON TD.tenantId = D.tenantId where D.clientId = ? and P.locationId = ? and P.status = ? and DATE(D.dueDate) BETWEEN ? and ? ";
  const data = [CONSTANTS.DOCUMENT_TYPES.SELFI, clientId, locationId, startDate, endDate, clientId, locationId, CONSTANTS.PROPERTY_STATUS.ACTIVE, startDate, endDate];

  if (sortBy && sortBy === 'MA') {
    query += ` order by TD.totalDue ${sortDirection}, D.id desc`;
  } else if (sortBy && sortBy === 'MI') {
    query += ` order by TD.totalDue ${sortDirection}, D.id desc`;
  } else if (sortBy && sortBy === 'DDMA') {
    query += ` order by D.dueDate desc, D.id desc`;
  } else if (sortBy && sortBy === 'DDMI') {
    query += ` order by D.dueDate asc, D.id desc`;
  } else {
    // Default
    query += ` order by D.id desc`;
  }

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

duesDB.getByClientIdandDateRangeAndSortByForStaff = async ({
  clientId,
  propertiesIds,
  startDate,
  endDate,
  sortBy,
}: duesTypes & { startDate: string; endDate: string; sortBy: string; propertiesIds: string; }) => {
  let sortDirection = (sortBy === "MA" || sortBy === "DDMA") ? "DESC" : "ASC";
  let query =
    `Select D.id, D.remarks, D.title, D.propId, D.roomId, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.description, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId inner join (SELECT tenantId, SUM(amount) AS totalDue FROM Dues WHERE clientId = ? and propId in (${propertiesIds}) AND Date(dueDate) BETWEEN ? and ? GROUP BY tenantId) AS TD ON TD.tenantId = D.tenantId where D.clientId = ? and D.propId in (${propertiesIds}) and P.status = ? and DATE(D.dueDate) BETWEEN ? and ? `;
  const data = [CONSTANTS.DOCUMENT_TYPES.SELFI, clientId, startDate, endDate, clientId, CONSTANTS.PROPERTY_STATUS.ACTIVE, startDate, endDate];

  if (sortBy && sortBy === 'MA') {
    query += ` order by TD.totalDue ${sortDirection}, D.id desc`;
  } else if (sortBy && sortBy === 'MI') {
    query += ` order by TD.totalDue ${sortDirection}, D.id desc`;
  } else if (sortBy && sortBy === 'DDMA') {
    query += ` order by D.dueDate desc, D.id desc`;
  } else if (sortBy && sortBy === 'DDMI') {
    query += ` order by D.dueDate asc, D.id desc`;
  } else {
    // Default
    query += ` order by D.id desc`;
  }

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

duesDB.getByClientIdandDateRangeForStaff = async ({
  clientId,
  propertiesIds,
  startDate,
  endDate,
}: duesTypes & { startDate: string; endDate: string; propertiesIds: string }) => {
  const query =
    `Select D.id, D.remarks, D.title, D.propId, D.roomId, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.description, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? and D.propId in (${propertiesIds}) and P.status = ? and DATE(D.dueDate) BETWEEN ? and ? order by D.id desc`;
  const data = [CONSTANTS.DOCUMENT_TYPES.SELFI, clientId, CONSTANTS.PROPERTY_STATUS.ACTIVE, startDate, endDate];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getByTypeAndTenantId = async ({
  tenantId,
  type,
}: duesTypes) => {
  const query =
    "Select * from Dues where tenantId = ? and type = ? order by id desc";
  const data = [tenantId, type];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

duesDB.getDueTotalByFlatId = async ({
  clientId,
  flatId
}: duesTypes & {flatId : any}) => {
  const query = 
    "Select SUM(D.balance) as total from Dues as D where D.clientId = ? and D.tenantId in (select O.tenantId from Occupancies as O where O.flatId = ? group by O.tenantId);";
  const data = [clientId, flatId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

duesDB.updateEntry = async ({
  id,
  amount,
  balance,
  rentStartDate,
  rentEndDate,
  dueDate,
  description,
}: duesTypes) => {
  const query = "update Dues set amount = ?, balance = ?, rentStartDate = ?, rentEndDate = ?, dueDate = ?, description = ? where id = ?;";
  const data = [amount, balance, rentStartDate, rentEndDate, dueDate, description, id];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);  
  return true
};

duesDB.deleteFutureCycleChangeRent = async ({
  clientId,
  tenantId,
  rentStartDate,
  rentEndDate,
}: duesTypes) => {
  const query = "delete from Dues where tenantId = ? and clientId = ? and type = ? and description like ? and DATE(rentStartDate) BETWEEN ? and ?";
  const data = [
    tenantId, 
    clientId, 
    CONSTANTS.DUES_TYPES.RENT, 
    `%Rent added by Kipinn while changing rental cycle%`,
    rentStartDate,
    rentEndDate,
  ];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);  
  return true
};

duesDB.updateRemarks = async ({
  tenantId,
  clientId,
  remarks
}: duesTypes) => {
  const query =
    `Update Dues set remarks = ? where tenantId = ? and clientId = ?;`;
  const data = [
    remarks,
    tenantId,
    clientId,
  ];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);  
  return true;
};

duesDB.getByClientIdAndFiltersAndDateRange = async ({
  clientId,
  pageNum,
  limit,
  startDate,
  endDate,
  type,
  propIds,
  sortBy,
  locationIds=null,
}: duesTypes & { 
  pageNum: number; 
  limit: number; 
  startDate: string; 
  endDate: string; 
  type: any;
  propIds: any;
  sortBy: string;
  sortDirection: string;
  locationIds: any;
}) => {
  let sortDirection = (sortBy === "MA" || sortBy === "DDMA") ? "DESC" : "ASC";
  let query =
    `Select D.id, D.remarks, D.title, D.remarks, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId`;

  const offset: number = (pageNum - 1) * limit;
  // let data = [clientId, startDate, endDate];
  let data = [];

  if (sortBy && (sortBy === "MA" || sortBy === "MI")) {
    query += ` inner join (SELECT tenantId, SUM(amount) AS totalDue FROM Dues WHERE clientId = ? AND Date(dueDate) BETWEEN ? and ?`
    data.push(clientId, startDate, endDate);
    
    if (type && type.length > 0) {
      query += ` and type in (${type.map(() => '?').join(',')})`;
      data.push(...type);
    }

    if (propIds && propIds.length > 0) {
      query += ` and propId in (${propIds.map(() => '?').join(',')})`;
      data.push(...propIds);
    }

    query += ` GROUP BY tenantId) AS TD ON TD.tenantId = D.tenantId`
  }

  query += ` where D.clientId = ? and Date(dueDate) BETWEEN ? and ?`;
  data.push(clientId, startDate, endDate);

  if (type && type.length > 0) {
    query += ` and D.type in (${type.map(() => '?').join(',')})`;
    data.push(...type);
  }
  
  if (locationIds && locationIds.length > 0) {
    query += ` and P.locationId in (${locationIds.map(() => '?').join(',')})`;
    data.push(...locationIds);
  }

  if (propIds && propIds.length > 0) {
    query += ` and D.propId in (${propIds.map(() => '?').join(',')})`;
    data.push(...propIds);
  }

  if (sortBy && sortBy === 'MA') {
    query += ` order by TD.totalDue ${sortDirection}, D.id desc`;
  } else if (sortBy && sortBy === 'MI') {
    query += ` order by TD.totalDue ${sortDirection}, D.id desc`;
  } else if (sortBy && sortBy === 'DDMA') {
    query += ` order by D.dueDate desc, D.id desc`;
  } else if (sortBy && sortBy === 'DDMI') {
    query += ` order by D.dueDate asc, D.id desc`;
  } else {
    // Default
    query += ` order by D.dueDate desc, D.id desc`;
  }

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

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

duesDB.getTotalDuesCountByClientIdAndFiltersAndDateRange = async ({
  clientId,
  startDate,
  endDate,
  type,
  propIds,
  locationIds=null,
}: duesTypes & { 
  startDate: string; 
  endDate: string; 
  type: any;
  propIds: any;
  locationIds: any;
}) => {
  let query =
    `Select COUNT(D.id) as totalDues from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId`;
  // let data = [clientId, startDate, endDate];
  let data = [];

  query += ` where D.clientId = ? and Date(dueDate) BETWEEN ? and ?`;
  data.push(clientId, startDate, endDate);

  if (type && type.length > 0) {
    query += ` and D.type in (${type.map(() => '?').join(',')})`;
    data.push(...type);
  }
  
  if (locationIds && locationIds.length > 0) {
    query += ` and P.locationId in (${locationIds.map(() => '?').join(',')})`;
    data.push(...locationIds);
  }

  if (propIds && propIds.length > 0) {
    query += ` and D.propId in (${propIds.map(() => '?').join(',')})`;
    data.push(...propIds);
  }

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

duesDB.getByClientIdAndFilters = async ({
  clientId,
  pageNum,
  limit,
  type,
  propIds,
  sortBy,
  startDate,
  endDate,
  locationIds = null,
}: duesTypes & { 
  pageNum: number; 
  limit: number; 
  type: any;
  propIds: any;
  locationIds: any;
  sortBy: string;
  sortDirection: string;
  startDate: string; 
  endDate: string; 
}) => {
  let sortDirection = (sortBy === "MA" || sortBy === "DDMA") ? "DESC" : "ASC";
  //let query =
    //`Select D.id, D.remarks, D.title, D.remarks, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId`;
  let query =
    `Select T.id as tenantId, ANY_VALUE(D.remarks) as remarks, SUM(D.balance) as balance, sum(D.amount) as amount, ANY_VALUE(T.name) as tenantName, ANY_VALUE(T.mobile) as tenantMobile, ANY_VALUE(R.roomNum) as roomNum, ANY_VALUE(R.flatId) as flatId, ANY_VALUE(R.floor) as floor, ANY_VALUE(P.name) as propertyName, ANY_VALUE(P.type) as propertyType from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId`;

  const offset: number = (pageNum - 1) * limit;
  // let data = [clientId, startDate, endDate];
  let data = [];

  if (sortBy && (sortBy === "MA" || sortBy === "MI")) {
    query += ` inner join (SELECT tenantId, SUM(amount) AS totalDue FROM Dues WHERE clientId = ? and Date(dueDate) BETWEEN ? and ?`
    data.push(clientId, startDate, endDate);
    
    if (type && type.length > 0) {
      query += ` and type in (${type.map(() => '?').join(',')})`;
      data.push(...type);
    }

    if (propIds && propIds.length > 0) {
      query += ` and propId in (${propIds.map(() => '?').join(',')})`;
      data.push(...propIds);
    }

    query += ` GROUP BY tenantId) AS TD ON TD.tenantId = D.tenantId`
  }

  query += ` where D.clientId = ? and Date(dueDate) BETWEEN ? and ?`;
  data.push(clientId, startDate, endDate);

  if (type && type.length > 0) {
    query += ` and D.type in (${type.map(() => '?').join(',')})`;
    data.push(...type);
  }

  if (propIds && propIds.length > 0) {
    query += ` and D.propId in (${propIds.map(() => '?').join(',')})`;
    data.push(...propIds);
  }

  if (locationIds && locationIds.length > 0) {
    query += ` and P.locationId in (${locationIds.map(() => '?').join(',')})`;
    data.push(...locationIds);
  }

  if (sortBy && sortBy === 'MA') {
    query += ` group by T.id order by TD.totalDue ${sortDirection}`;
  } else if (sortBy && sortBy === 'MI') {
    query += ` group by T.id order by TD.totalDue ${sortDirection}`;
  } 
  // else if (sortBy && sortBy === 'DDMA') {
  //   query += ` group by T.id order by MIN(D.dueDate) desc`;
  // } else if (sortBy && sortBy === 'DDMI') {
  //   query += ` group by T.id order by MAX(D.dueDate) desc`;
  // } 
  else {
    // Default
    query += ` group by T.id order by MAX(D.id) desc`;
  }

  query += ` limit ?, ?`
  data.push(`${offset}`);
  data.push(`${limit}`);
  // log.info(
  //           `${mysql.format(query, data)}`
  //         );

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

//Not being use for now, maybe in future. Don't remove -- 2025-12-26
duesDB.getByClientIdAndFiltersAndDateRangeNew = async ({
  clientId,
  pageNum,
  limit,
  startDate,
  endDate,
  type,
  propIds,
  sortBy,
}: duesTypes & { 
  pageNum: number; 
  limit: number; 
  startDate: string; 
  endDate: string; 
  type: any;
  propIds: any;
  sortBy: string;
  sortDirection: string;
}) => {
  let sortDirection = (sortBy === "MA" || sortBy === "DDMA") ? "DESC" : "ASC";
  let query =
    `Select D.tenantId from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId`;

  const offset: number = (pageNum - 1) * limit;
  // let data = [clientId, startDate, endDate];
  let data = [];

  if (sortBy && (sortBy === "MA" || sortBy === "MI")) {
    query += ` inner join (SELECT tenantId, SUM(amount) AS totalDue FROM Dues WHERE clientId = ? AND Date(dueDate) BETWEEN ? and ?`
    data.push(clientId, startDate, endDate);
    
    if (type && type.length > 0) {
      query += ` and type in (${type.map(() => '?').join(',')})`;
      data.push(...type);
    }

    if (propIds && propIds.length > 0) {
      query += ` and propId in (${propIds.map(() => '?').join(',')})`;
      data.push(...propIds);
    }

    query += ` GROUP BY tenantId) AS TD ON TD.tenantId = D.tenantId`
  }

  query += ` where D.clientId = ? and Date(dueDate) BETWEEN ? and ?`;
  data.push(clientId, startDate, endDate);

  if (type && type.length > 0) {
    query += ` and D.type in (${type.map(() => '?').join(',')})`;
    data.push(...type);
  }

  if (propIds && propIds.length > 0) {
    query += ` and D.propId in (${propIds.map(() => '?').join(',')})`;
    data.push(...propIds);
  }

  if (sortBy && sortBy === 'MA') {
    query += ` order by TD.totalDue ${sortDirection}, D.id desc`;
  } else if (sortBy && sortBy === 'MI') {
    query += ` order by TD.totalDue ${sortDirection}, D.id desc`;
  } else if (sortBy && sortBy === 'DDMA') {
    query += ` order by D.dueDate desc, D.id desc`;
  } else if (sortBy && sortBy === 'DDMI') {
    query += ` order by D.dueDate asc, D.id desc`;
  } else {
    // Default
    query += ` order by D.dueDate desc, D.id desc`;
  }

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

  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) {
    const tenantIds = rows.map((r: any) => r.tenantId);
    let dueQuery = `Select D.id, D.remarks, D.title, D.remarks, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? and D.tenantId in (${tenantIds.map(() => '?').join(',')})`

    const dueData = [clientId, ...tenantIds];

    const [duesRows] = await DB.execute<RowDataPacket[]>(dueQuery, dueData);
    if (rows?.length > 0) return duesRows;
    else return false;
  } 
  else return false;
};

duesDB.getByClientIdAndPropIdAndFiltersAndDateRange = async ({
  clientId,
  propId,
  pageNum,
  limit,
  startDate,
  endDate,
  type,
  sortBy,
}: duesTypes & { 
  pageNum: number; 
  limit: number; 
  startDate: string; 
  endDate: string; 
  type: any;
  sortBy: string;
}) => {
  let sortDirection = (sortBy === "MA" || sortBy === "DDMA") ? "DESC" : "ASC";
  let query =
    `Select D.id, D.remarks, D.title, D.remarks, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId`;

  const offset: number = (pageNum - 1) * limit;
  // let data = [clientId, startDate, endDate];
  let data = [];

  if (sortBy && (sortBy === "MA" || sortBy === "MI")) {
    query += ` inner join (SELECT tenantId, SUM(amount) AS totalDue FROM Dues WHERE clientId = ? and propId = ? AND Date(dueDate) BETWEEN ? and ?`
    data.push(clientId, propId, startDate, endDate);
    
    if (type && type.length > 0) {
      query += ` and type in (${type.map(() => '?').join(',')})`;
      data.push(...type);
    }

    query += ` GROUP BY tenantId) AS TD ON TD.tenantId = D.tenantId`
  }

  query += ` where D.clientId = ? and D.propId = ? and Date(D.dueDate) BETWEEN ? and ?`;
  data.push(clientId, propId, startDate, endDate);

  if (type && type.length > 0) {
    query += ` and D.type in (${type.map(() => '?').join(',')})`;
    data.push(...type);
  }

  if (sortBy && sortBy === 'MA') {
    query += ` order by TD.totalDue ${sortDirection}, D.id desc`;
  } else if (sortBy && sortBy === 'MI') {
    query += ` order by TD.totalDue ${sortDirection}, D.id desc`;
  } else if (sortBy && sortBy === 'DDMA') {
    query += ` order by D.dueDate desc, D.id desc`;
  } else if (sortBy && sortBy === 'DDMI') {
    query += ` order by D.dueDate asc, D.id desc`;
  } else {
    // Default
    query += ` order by D.dueDate desc, D.id desc`;
  }

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

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

duesDB.getTotalDuesCountByClientIdAndPropIdAndFiltersAndDateRange = async ({
  clientId,
  propId,
  startDate,
  endDate,
  type,
}: duesTypes & { 
  startDate: string; 
  endDate: string; 
  type: any;
}) => {
  let query =
    `Select COUNT(D.id) as totalDues from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId`;
  // let data = [clientId, startDate, endDate];
  let data = [];

  query += ` where D.clientId = ? and D.propId = ? and Date(D.dueDate) BETWEEN ? and ?`;
  data.push(clientId, propId, startDate, endDate);

  if (type && type.length > 0) {
    query += ` and D.type in (${type.map(() => '?').join(',')})`;
    data.push(...type);
  }

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

duesDB.getByClientIdAndFiltersAndDateRangeForStaff = async ({
  clientId,
  propertiesIds,
  pageNum,
  limit,
  startDate,
  endDate,
  type,
  propIds,
  sortBy,
  locationIds=null,
}: duesTypes & { 
  propertiesIds: string;
  pageNum: number; 
  limit: number; 
  startDate: string; 
  endDate: string; 
  type: any;
  propIds: any;
  sortBy: string;
  sortDirection: string;
  locationIds: any;
}) => {
  let sortDirection = (sortBy === "MA" || sortBy === "DDMA") ? "DESC" : "ASC";
  let query =
    `Select D.id, D.remarks, D.title, D.remarks, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId`;

  const offset: number = (pageNum - 1) * limit;
  // let data = [clientId, startDate, endDate];
  let data = [];

  if (sortBy && (sortBy === "MA" || sortBy === "MI")) {
    query += ` inner join (SELECT tenantId, SUM(amount) AS totalDue FROM Dues WHERE clientId = ? and propId in (${propertiesIds}) AND Date(dueDate) BETWEEN ? and ?`
    data.push(clientId, startDate, endDate);
    
    if (type && type.length > 0) {
      query += ` and type in (${type.map(() => '?').join(',')})`;
      data.push(...type);
    }

    if (propIds && propIds.length > 0) {
      query += ` and propId in (${propIds.map(() => '?').join(',')})`;
      data.push(...propIds);
    }

    query += ` GROUP BY tenantId) AS TD ON TD.tenantId = D.tenantId`
  }

  query += ` where D.clientId = ? and D.propId in (${propertiesIds}) and Date(dueDate) BETWEEN ? and ?`;
  data.push(clientId, startDate, endDate);

  if (type && type.length > 0) {
    query += ` and D.type in (${type.map(() => '?').join(',')})`;
    data.push(...type);
  }
  
  if (locationIds && locationIds.length > 0) {
    query += ` and P.locationId in (${locationIds.map(() => '?').join(',')})`;
    data.push(...locationIds);
  }

  if (propIds && propIds.length > 0) {
    query += ` and D.propId in (${propIds.map(() => '?').join(',')})`;
    data.push(...propIds);
  }

  if (sortBy && sortBy === 'MA') {
    query += ` order by TD.totalDue ${sortDirection}, D.id desc`;
  } else if (sortBy && sortBy === 'MI') {
    query += ` order by TD.totalDue ${sortDirection}, D.id desc`;
  } else if (sortBy && sortBy === 'DDMA') {
    query += ` order by D.dueDate desc, D.id desc`;
  } else if (sortBy && sortBy === 'DDMI') {
    query += ` order by D.dueDate asc, D.id desc`;
  } else {
    // Default
    query += ` order by D.dueDate desc, D.id desc`;
  }

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

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

duesDB.getTotalDuesCountByClientIdAndFiltersAndDateRangeForStaff = async ({
  clientId,
  propertiesIds,
  startDate,
  endDate,
  type,
  propIds,
  locationIds=null,
}: duesTypes & { 
  propertiesIds: string;
  pageNum: number; 
  limit: number; 
  startDate: string; 
  endDate: string; 
  type: any;
  propIds: any;
  sortBy: string;
  sortDirection: string;
  locationIds: any;
}) => {
  let query =
    `Select COUNT(D.id) as totalDues from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId`;

  // let data = [clientId, startDate, endDate];
  let data = [];

  query += ` where D.clientId = ? and D.propId in (${propertiesIds}) and Date(dueDate) BETWEEN ? and ?`;
  data.push(clientId, startDate, endDate);

  if (type && type.length > 0) {
    query += ` and D.type in (${type.map(() => '?').join(',')})`;
    data.push(...type);
  }
  
  if (locationIds && locationIds.length > 0) {
    query += ` and P.locationId in (${locationIds.map(() => '?').join(',')})`;
    data.push(...locationIds);
  }

  if (propIds && propIds.length > 0) {
    query += ` and D.propId in (${propIds.map(() => '?').join(',')})`;
    data.push(...propIds);
  }

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


duesDB.getByClientIdAndFiltersForStaff = async ({
  clientId,
  propertiesIds,
  pageNum,
  limit,
  type,
  propIds,
  sortBy,
  startDate,
  endDate,
  locationIds = null,
}: duesTypes & { 
  startDate: string;
  endDate: string;
  propertiesIds: string;
  pageNum: number; 
  limit: number; 
  type: any;
  propIds: any;
  sortBy: string;
  sortDirection: string;
  locationIds: any;
}) => {
  let sortDirection = (sortBy === "MA" || sortBy === "DDMA") ? "DESC" : "ASC";
  let query =
    `Select T.id as tenantId, ANY_VALUE(D.remarks) as remarks, SUM(D.balance) as balance, sum(D.amount) as amount, ANY_VALUE(T.name) as tenantName, ANY_VALUE(T.mobile) as tenantMobile, ANY_VALUE(R.roomNum) as roomNum, ANY_VALUE(R.flatId) as flatId, ANY_VALUE(R.floor) as floor, ANY_VALUE(P.name) as propertyName, ANY_VALUE(P.type) as propertyType from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId`;

  const offset: number = (pageNum - 1) * limit;
  // let data = [clientId, startDate, endDate];
  let data = [];

  if (sortBy && (sortBy === "MA" || sortBy === "MI")) {
    query += ` inner join (SELECT tenantId, SUM(amount) AS totalDue FROM Dues WHERE clientId = ? and propId in (${propertiesIds}) AND Date(dueDate) BETWEEN ? and ?`
    data.push(clientId, startDate, endDate);
    
    if (type && type.length > 0) {
      query += ` and type in (${type.map(() => '?').join(',')})`;
      data.push(...type);
    }

    if (propIds && propIds.length > 0) {
      query += ` and propId in (${propIds.map(() => '?').join(',')})`;
      data.push(...propIds);
    }

    query += ` GROUP BY tenantId) AS TD ON TD.tenantId = D.tenantId`
  }

  query += ` where D.clientId = ? and D.propId in (${propertiesIds}) AND Date(dueDate) BETWEEN ? and ?`;
  data.push(clientId, startDate, endDate);

  if (type && type.length > 0) {
    query += ` and D.type in (${type.map(() => '?').join(',')})`;
    data.push(...type);
  }

  if (propIds && propIds.length > 0) {
    query += ` and D.propId in (${propIds.map(() => '?').join(',')})`;
    data.push(...propIds);
  }

  if (locationIds && locationIds.length > 0) {
    query += ` and P.locationId in (${locationIds.map(() => '?').join(',')})`;
    data.push(...locationIds);
  }

  if (sortBy && sortBy === 'MA') {
    query += ` group by T.id order by TD.totalDue ${sortDirection},  T.id DESC`;
  } else if (sortBy && sortBy === 'MI') {
    query += ` group by T.id order by TD.totalDue ${sortDirection},  T.id ASC`;
  } 
  // else if (sortBy && sortBy === 'DDMA') {
  //   query += `group by T.id order by MAX(D.dueDate) desc, MIN(D.id) desc`;
  // } else if (sortBy && sortBy === 'DDMI') {
  //   query += ` group by T.id order by MAX(D.dueDate) asc, MAX(D.id) desc`;
  // } 
  else {
    // Default
    query += ` group by T.id order by MAX(D.id) desc`;
  }

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

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

duesDB.getTotalByClientIdAndFiltersAndDateRange = async ({
  clientId,
  startDate,
  endDate,
  type,
  propIds,
  locationIds=null,
}: duesTypes & { 
  startDate: string; 
  endDate: string; 
  type: any;
  propIds: any;
  locationIds: any;
}) => {

  let query =
    `Select SUM(D.balance) as totalDues from Dues as D join Properties as P on P.id = D.propId where D.clientId = ? and Date(D.dueDate) BETWEEN ? and ?`;

  let data = [clientId, startDate, endDate];

  if (Array.isArray(type) && type.length > 0) {
    query += ` and D.type in (${type.map(() => '?').join(',')})`;
    data.push(...type);
  }

  if (Array.isArray(propIds) && propIds.length > 0) {
    query += ` and D.propId in (${propIds.map(() => '?').join(',')})`;
    data.push(...propIds);
  }
  
  if (locationIds && locationIds.length > 0) {
    query += ` and P.locationId in (${locationIds.map(() => '?').join(',')})`;
    data.push(...locationIds);
  }

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

duesDB.getTotalByClientIdAndFilters = async ({
  clientId,
  type,
  propIds,
  locationIds
}: duesTypes & { 
  type: any;
  propIds: any;
  locationIds: any
}) => {

  let query =
    `Select SUM(D.balance) as totalDues from Dues as D join Properties as P on P.id = D.propId where D.clientId = ?`;

  let data = [clientId];

  if (Array.isArray(type) && type.length > 0) {
    query += ` and D.type in (${type.map(() => '?').join(',')})`;
    data.push(...type);
  }

  if (Array.isArray(propIds) && propIds.length > 0) {
    query += ` and D.propId in (${propIds.map(() => '?').join(',')})`;
    data.push(...propIds);
  }

  if (locationIds && locationIds.length > 0) {
    query += ` and P.locationId in (${locationIds.map(() => '?').join(',')})`;
    data.push(...locationIds);
  }

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

duesDB.getTotalByClientIdAndFiltersAndDateRangeForStaff = async ({
  clientId,
  startDate,
  endDate,
  type,
  propIds,
  propertiesIds,
  locationIds=null,
}: duesTypes & { 
  startDate: string; 
  endDate: string; 
  type: any;
  propIds: any;
  propertiesIds: string;
  locationIds: any;
}) => {
  let query =
    `Select SUM(D.balance) as totalDues from Dues as D join Properties as P on P.id = D.propId where D.clientId = ? and D.propId in (${propertiesIds}) and Date(D.dueDate) BETWEEN ? and ?`;

  let data = [clientId, startDate, endDate];

  if (type && type.length > 0) {
    query += ` and D.type in (${type.map(() => '?').join(',')})`;
    data.push(...type);
  }

  if (propIds && propIds.length > 0) {
    query += ` and D.propId in (${propIds.map(() => '?').join(',')})`;
    data.push(...propIds);
  }

  if (locationIds && locationIds.length > 0) {
    query += ` and P.locationId in (${locationIds.map(() => '?').join(',')})`;
    data.push(...locationIds);
  }

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


duesDB.getTotalByClientIdAndFiltersForStaff = async ({
  clientId,
  type,
  propIds,
  propertiesIds,
  startDate,
  endDate,
  locationIds=null,
}: duesTypes & { 
  type: any;
  startDate: string,
  endDate: string,
  propIds: any;
  propertiesIds: string;
  locationIds: any;
}) => {
  let query =
    `Select SUM(D.balance) as totalDues from Dues as D join Properties as P on P.id = D.propId where D.clientId = ? and D.propId in (${propertiesIds}) AND Date(D.dueDate) BETWEEN ? and ?`;

  let data = [clientId, startDate, endDate];

  if (type && type.length > 0) {
    query += ` and D.type in (${type.map(() => '?').join(',')})`;
    data.push(...type);
  }

  if (propIds && propIds.length > 0) {
    query += ` and D.propId in (${propIds.map(() => '?').join(',')})`;
    data.push(...propIds);
  }

  if (locationIds && locationIds.length > 0) {
    query += ` and P.locationId in (${locationIds.map(() => '?').join(',')})`;
    data.push(...locationIds);
  }

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

duesDB.getLifetimeTotalByClientIdAndFiltersForStaff = async ({
  clientId,
  type,
  propIds,
  propertiesIds,
  locationIds=null,
}: duesTypes & { 
  type: any;
  startDate: string,
  endDate: string,
  propIds: any;
  propertiesIds: string;
  locationIds: any;
}) => {
  let query =
    `Select SUM(D.balance) as totalDues from Dues as D join Properties as P on P.id = D.propId where D.clientId = ? and D.propId in (${propertiesIds}) `;

  let data = [clientId];

  if (type && type.length > 0) {
    query += ` and D.type in (${type.map(() => '?').join(',')})`;
    data.push(...type);
  }

  if (propIds && propIds.length > 0) {
    query += ` and D.propId in (${propIds.map(() => '?').join(',')})`;
    data.push(...propIds);
  }

  if (locationIds && locationIds.length > 0) {
    query += ` and P.locationId in (${locationIds.map(() => '?').join(',')})`;
    data.push(...locationIds);
  }

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

duesDB.getTotalByClientIdAndPropIdAndFiltersAndDateRange = async ({
  clientId,
  startDate,
  endDate,
  type,
  propId,
}: duesTypes & { 
  startDate: string; 
  endDate: string; 
  type: any;
}) => {
  let query =
    `Select SUM(D.balance) as totalDues from Dues as D where D.clientId = ? and D.propId = ? and Date(D.dueDate) BETWEEN ? and ?`;

  let data = [clientId, propId, startDate, endDate];

  if (type && type.length > 0) {
    query += ` and D.type in (${type.map(() => '?').join(',')})`;
    data.push(...type);
  }

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

duesDB.getTenantCountByClientIdAndFiltersAndDateRange = async ({
  clientId,
  startDate,
  endDate,
  type,
  propIds,
  locationIds=null,
}: duesTypes & { 
  startDate: string; 
  endDate: string; 
  type: any;
  propIds: any;
  locationIds: any;
}) => {
  let query =
    `Select Count(Distinct(D.tenantId)) as tenantCount from Dues as D join Properties as P on P.id = D.propId where D.clientId = ? and Date(D.dueDate) BETWEEN ? and ?`;

  let data = [clientId, startDate, endDate];

  if (type && type.length > 0) {
    query += ` and D.type in (${type.map(() => '?').join(',')})`;
    data.push(...type);
  }

  if (propIds && propIds.length > 0) {
    query += ` and D.propId in (${propIds.map(() => '?').join(',')})`;
    data.push(...propIds);
  }
  
  if (locationIds && locationIds.length > 0) {
    query += ` and P.locationId in (${locationIds.map(() => '?').join(',')})`;
    data.push(...locationIds);
  }

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

duesDB.getTenantCountAndFiltersForClient = async ({
  clientId,
  type,
  propIds,
}: duesTypes & { 
  type: any;
  propIds: any;
}) => {
  let query =
    `Select Count(Distinct(D.tenantId)) as tenantCount from Dues as D where D.clientId = ?`;

  let data = [clientId];

  if (type && type.length > 0) {
    query += ` and D.type in (${type.map(() => '?').join(',')})`;
    data.push(...type);
  }

  if (propIds && propIds.length > 0) {
    query += ` and D.propId in (${propIds.map(() => '?').join(',')})`;
    data.push(...propIds);
  }

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

duesDB.getTenantCountByClientIdAndFiltersAndDateRangeForStaff = async ({
  clientId,
  startDate,
  endDate,
  type,
  propIds,
  propertiesIds,
  locationIds=null,
}: duesTypes & { 
  startDate: string; 
  endDate: string; 
  type: any;
  propIds: any;
  propertiesIds: string;
  locationIds: any;
}) => {
  let query =
    `Select Count(Distinct(D.tenantId)) as tenantCount from Dues as D join Properties as P on P.id = D.propId where D.clientId = ? and D.propId in (${propertiesIds}) and Date(D.dueDate) BETWEEN ? and ?`;

  let data = [clientId, startDate, endDate];

  if (type && type.length > 0) {
    query += ` and D.type in (${type.map(() => '?').join(',')})`;
    data.push(...type);
  }

  if (propIds && propIds.length > 0) {
    query += ` and D.propId in (${propIds.map(() => '?').join(',')})`;
    data.push(...propIds);
  }

  if (locationIds && locationIds.length > 0) {
    query += ` and P.locationId in (${locationIds.map(() => '?').join(',')})`;
    data.push(...locationIds);
  }

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


duesDB.getTenantCountByClientIdAndFiltersForStaff = async ({
  clientId,
  type,
  propIds,
  propertiesIds,
}: duesTypes & { 
  type: any;
  propIds: any;
  propertiesIds: string;
}) => {
  let query =
    `Select Count(Distinct(D.tenantId)) as tenantCount from Dues as D where D.clientId = ? and D.propId in (${propertiesIds})`;

  let data = [clientId];

  if (type && type.length > 0) {
    query += ` and D.type in (${type.map(() => '?').join(',')})`;
    data.push(...type);
  }

  if (propIds && propIds.length > 0) {
    query += ` and D.propId in (${propIds.map(() => '?').join(',')})`;
    data.push(...propIds);
  }

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

duesDB.getTenantCountByClientIdAndPropIdAndFiltersAndDateRange = async ({
  clientId,
  startDate,
  endDate,
  type,
  propId,
}: duesTypes & { 
  startDate: string; 
  endDate: string; 
  type: any;
}) => {
  let query =
    `Select Count(Distinct(D.tenantId)) as tenantCount from Dues as D where D.clientId = ? and D.propId = ? and Date(D.dueDate) BETWEEN ? and ?`;

  let data = [clientId, propId, startDate, endDate];

  if (type && type.length > 0) {
    query += ` and D.type in (${type.map(() => '?').join(',')})`;
    data.push(...type);
  }

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

duesDB.updateRoomByTenantIdAndRoomId = async ({
  tenantId,
  roomId,
  newRoomId,
}: duesTypes & {newRoomId: number}) => {
  const query =
    "update Dues set roomId = ? where tenantId = ? and roomId = ?;";
  const data = [newRoomId, tenantId, roomId];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

duesDB.getByClientIdAndTenantNameMobile = async ({
  clientId,
  searchVal,
  pageNum,
  limit,
}: duesTypes & { 
  pageNum: number; 
  limit: number; 
  searchVal: string; 
}) => {
  let query =
    `Select D.id, D.remarks, D.title, D.remarks, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? and (T.name like ? OR T.mobile like ?) order by D.id desc limit ?, ?`;

  const offset: number = (pageNum - 1) * limit;
  let data = [
    clientId,
    `%${searchVal}%`,
    `%${searchVal}%`,
    `${offset}`,
    `${limit}`,
  ];

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

duesDB.getTotalDuesByClientIdAndTenantNameMobile = async ({
  clientId,
  searchVal,
}: duesTypes & { 
  searchVal: string; 
}) => {
  let query =
    `Select COUNT(D.id) as totalDues from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? and (T.name like ? OR T.mobile like ?)`;

  let data = [
    clientId,
    `%${searchVal}%`,
    `%${searchVal}%`,
  ];

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

duesDB.getByClientIdAndTenantNameMobileForGroupedDues = async ({
  clientId,
  searchVal,
  pageNum,
  limit,
}: duesTypes & { 
  pageNum: number; 
  limit: number; 
  searchVal: string; 
}) => {
  // let query =
  //   `Select D.id, D.remarks, D.title, D.remarks, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? and (T.name like ? OR T.mobile like ?) order by D.id desc limit ?, ?`;
  let query =
    `Select ANY_VALUE(D.remarks) as remarks, SUM(D.balance) as balance, sum(D.amount) as amount, ANY_VALUE(T.name) as tenantName, ANY_VALUE(T.mobile) as tenantMobile, ANY_VALUE(T.id) as tenantId, ANY_VALUE(R.roomNum) as roomNum, ANY_VALUE(R.flatId) as flatId, ANY_VALUE(R.floor) as floor, ANY_VALUE(P.name) as propertyName, ANY_VALUE(P.type) as propertyType from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? and (T.name like ? OR T.mobile like ?) group by T.id limit ?, ?`;

  const offset: number = (pageNum - 1) * limit;
  let data = [
    clientId,
    `%${searchVal}%`,
    `%${searchVal}%`,
    `${offset}`,
    `${limit}`,
  ];

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

duesDB.getTotalByClientIdAndTenantNameMobile = async ({
  clientId,
  searchVal,
}: duesTypes & { 
  searchVal: string; 
}) => {
  let query =
    `Select SUM(D.balance) as totalDues from Dues as D inner join Tenants as T on T.id = D.tenantId where D.clientId = ? and (T.name like ? OR T.mobile like ?)`;

  let data = [
    clientId,
    `%${searchVal}%`,
    `%${searchVal}%`,
  ];

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

duesDB.getTotalTenantsByClientIdAndTenantNameMobile = async ({
  clientId,
  searchVal,
}: duesTypes & { 
  searchVal: string; 
}) => {
  let query =
    `Select Count(Distinct(D.tenantId)) as tenantCount from Dues as D inner join Tenants as T on T.id = D.tenantId where D.clientId = ? and (T.name like ? OR T.mobile like ?)`;

  let data = [
    clientId,
    `%${searchVal}%`,
    `%${searchVal}%`,
  ];

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

duesDB.getByPropIdAndTenantNameMobile = async ({
  clientId,
  propId,
  searchVal,
  pageNum,
  limit,
}: duesTypes & { 
  pageNum: number; 
  limit: number; 
  searchVal: string; 
}) => {
  let query =
    `Select D.id, D.remarks, D.title, D.remarks, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? and D.propId = ? and (T.name like ? OR T.mobile like ?) order by D.id desc limit ?, ?`;

  const offset: number = (pageNum - 1) * limit;
  let data = [
    clientId,
    propId,
    `%${searchVal}%`,
    `%${searchVal}%`,
    `${offset}`,
    `${limit}`,
  ];

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


duesDB.getByPropIdAndTenantNameMobileForGroupedDues = async ({
  clientId,
  propId,
  searchVal,
  pageNum,
  limit,
}: duesTypes & { 
  pageNum: number; 
  limit: number; 
  searchVal: string; 
}) => {
  // let query =
  //   `Select D.id, D.remarks, D.title, D.remarks, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? and (T.name like ? OR T.mobile like ?) order by D.id desc limit ?, ?`;
  let query =
    `Select ANY_VALUE(D.remarks) as remarks, SUM(D.balance) as balance, sum(D.amount) as amount, ANY_VALUE(T.name) as tenantName, ANY_VALUE(T.mobile) as tenantMobile, ANY_VALUE(T.id) as tenantId, ANY_VALUE(R.roomNum) as roomNum, ANY_VALUE(R.flatId) as flatId, ANY_VALUE(R.floor) as floor, ANY_VALUE(P.name) as propertyName, ANY_VALUE(P.type) as propertyType from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? and D.propId = ? and (T.name like ? OR T.mobile like ?) group by T.id limit ?, ?`;

  const offset: number = (pageNum - 1) * limit;
  let data = [
    clientId,
    propId,
    `%${searchVal}%`,
    `%${searchVal}%`,
    `${offset}`,
    `${limit}`,
  ];

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

duesDB.getTotalByPropIdAndTenantNameMobile = async ({
  clientId,
  propId,
  searchVal,
}: duesTypes & { 
  searchVal: string; 
}) => {
  let query =
    `Select SUM(D.balance) as totalDues from Dues as D inner join Tenants as T on T.id = D.tenantId where D.clientId = ? and propId = ? and (T.name like ? OR T.mobile like ?)`;

  let data = [
    clientId,
    propId,
    `%${searchVal}%`,
    `%${searchVal}%`,
  ];

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

duesDB.getTotalTenantsByPropIdAndTenantNameMobile = async ({
  clientId,
  propId,
  searchVal,
}: duesTypes & { 
  searchVal: string; 
}) => {
  let query =
    `Select Count(Distinct(D.tenantId)) as tenantCount from Dues as D inner join Tenants as T on T.id = D.tenantId where D.clientId = ? and propId = ? and (T.name like ? OR T.mobile like ?)`;

  let data = [
    clientId,
    propId,
    `%${searchVal}%`,
    `%${searchVal}%`,
  ];

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

duesDB.getTotalDuesByPropIdAndTenantNameMobile = async ({
  clientId,
  propId,
  searchVal,
}: duesTypes & { 
  searchVal: string; 
}) => {
  let query =
    `Select Count(D.id) as totalDues from Dues as D inner join Tenants as T on T.id = D.tenantId where D.clientId = ? and propId = ? and (T.name like ? OR T.mobile like ?)`;

  let data = [
    clientId,
    propId,
    `%${searchVal}%`,
    `%${searchVal}%`,
  ];

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

duesDB.getByClientIdAndTenantNameMobileForStaff = async ({
  clientId,
  searchVal,
  pageNum,
  limit,
  propertiesIds,
}: duesTypes & { 
  pageNum: number; 
  limit: number; 
  searchVal: string; 
  propertiesIds: string;
}) => {
  let query =
    `Select D.id, D.remarks, D.title, D.remarks, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? and D.propId in (${propertiesIds}) and (T.name like ? OR T.mobile like ?) order by D.id desc limit ?, ?`;

  const offset: number = (pageNum - 1) * limit;
  let data = [
    clientId,
    `%${searchVal}%`,
    `%${searchVal}%`,
    `${offset}`,
    `${limit}`,
  ];

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

duesDB.getTotalDuesCountByClientIdAndTenantNameMobileForStaff = async ({
  clientId,
  searchVal,
  propertiesIds,
}: duesTypes & { 
  searchVal: string; 
  propertiesIds: string;
}) => {
  let query =
    `Select COUNT(D.id) as totalDues from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? and D.propId in (${propertiesIds}) and (T.name like ? OR T.mobile like ?)`;

  let data = [
    clientId,
    `%${searchVal}%`,
    `%${searchVal}%`,
  ];

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


duesDB.getByClientIdAndTenantNameMobileForStaffGroupBy = async ({
  clientId,
  searchVal,
  pageNum,
  limit,
  propertiesIds,
}: duesTypes & { 
  pageNum: number; 
  limit: number; 
  searchVal: string; 
  propertiesIds: string;
}) => {
  let query =
    `Select ANY_VALUE(D.remarks) as remarks, SUM(D.balance) as balance, sum(D.amount) as amount, ANY_VALUE(T.name) as tenantName, ANY_VALUE(T.mobile) as tenantMobile, ANY_VALUE(T.id) as tenantId, ANY_VALUE(R.roomNum) as roomNum, ANY_VALUE(R.flatId) as flatId, ANY_VALUE(R.floor) as floor, ANY_VALUE(P.name) as propertyName, ANY_VALUE(P.type) as propertyType from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? and D.propId in (${propertiesIds}) and (T.name like ? OR T.mobile like ?) group by T.id limit ?, ?`;

  const offset: number = (pageNum - 1) * limit;
  let data = [
    clientId,
    `%${searchVal}%`,
    `%${searchVal}%`,
    `${offset}`,
    `${limit}`,
  ];

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

duesDB.getTotalByClientIdAndTenantNameMobileForStaff = async ({
  clientId,
  searchVal,
  propertiesIds
}: duesTypes & { 
  searchVal: string; 
  propertiesIds: string;
}) => {
  let query =
    `Select SUM(D.balance) as totalDues from Dues as D inner join Tenants as T on T.id = D.tenantId where D.clientId = ? and D.propId in (${propertiesIds}) and (T.name like ? OR T.mobile like ?)`;

  let data = [
    clientId,
    `%${searchVal}%`,
    `%${searchVal}%`,
  ];

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

duesDB.getTotalTenantsByClientIdAndTenantNameMobileForStaff = async ({
  clientId,
  searchVal,
  propertiesIds,
}: duesTypes & { 
  searchVal: string; 
  propertiesIds: string;
}) => {
  let query =
    `Select Count(Distinct(D.tenantId)) as tenantCount from Dues as D inner join Tenants as T on T.id = D.tenantId where D.clientId = ? and D.propId in (${propertiesIds}) and (T.name like ? OR T.mobile like ?)`;

  let data = [
    clientId,
    `%${searchVal}%`,
    `%${searchVal}%`,
  ];

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

duesDB.getZeroAmountDueByTenantIdAndClientId = async ({
  clientId,
  tenantId,
}: duesTypes) => {
  const query = 
   `Select * from Dues where clientId = ? and tenantId = ? and amount = 0`;
  const data = [
    clientId,
    tenantId,
  ];

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

duesDB.getSecurityShortFallAmtForClient = async ({
  clientId,
}: duesTypes) => {
  const query = 
  `select SUM(COALESCE(DT.totalTenantDues, 0) - COALESCE(LT.securityPaidAmt, 0)) AS totalDifference from (Select D.tenantId, Sum(D.balance) as totalTenantDues from Dues as D join Occupancies as O on O.tenantId = D.tenantId and O.clientId = D.clientId where D.clientId = ? group by D.tenantId) as DT left join (select L.tenantId, Sum(ABS(L.amount)) as securityPaidAmt from Ledgers as L join Occupancies as O on L.tenantId = O.tenantId and L.clientId = O.clientId where L.clientId = ? and L.amount < 0 and L.type = ? and Date(L.createdAt) >= Date(O.createdAt) group by L.tenantId) as LT on DT.tenantId = LT.tenantId where COALESCE(DT.totalTenantDues, 0) > COALESCE(LT.securityPaidAmt, 0);`;

  const data = [
    clientId,
    clientId,
    CONSTANTS.DUES_TYPES.SECURITY,
  ];

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

duesDB.getSecurityShortFallDuesForClient = async ({
  clientId,
}: duesTypes) => {
  const query = 
  `select D.*, T.name as tenantName, T.mobile as tenantMobile, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType from (Select D.tenantId, Sum(D.balance) as totalTenantDues from Dues as D join Occupancies as O on O.tenantId = D.tenantId and O.clientId = D.clientId where D.clientId = ? group by D.tenantId) as DT left join (select L.tenantId, Sum(ABS(L.amount)) as securityPaidAmt from Ledgers as L join Occupancies as O on L.tenantId = O.tenantId and L.clientId = O.clientId where L.clientId = ? and L.amount < 0 and L.type = ? and Date(L.createdAt) >= Date(O.createdAt) group by L.tenantId) as LT on DT.tenantId = LT.tenantId left join Dues as D on DT.tenantId = D.tenantId join Tenants as T on DT.tenantId = T.id join Properties as P on D.propId = P.id join Rooms as R on D.roomId = R.id where COALESCE(DT.totalTenantDues, 0) > COALESCE(LT.securityPaidAmt, 0);`;

  const data = [
    clientId,
    clientId,
    CONSTANTS.DUES_TYPES.SECURITY,
  ];

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

duesDB.getSecurityShortFallDuesForStaff = async ({
  clientId,
  propertiesIds,
}: duesTypes & {propertiesIds: string}) => {
  const query = 
  `select D.*, T.name as tenantName, T.mobile as tenantMobile, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType from (Select D.tenantId, Sum(D.balance) as totalTenantDues from Dues as D join Occupancies as O on O.tenantId = D.tenantId and O.clientId = D.clientId where D.clientId = ? and D.propId in (${propertiesIds}) group by D.tenantId) as DT left join (select L.tenantId, Sum(ABS(L.amount)) as securityPaidAmt from Ledgers as L join Occupancies as O on L.tenantId = O.tenantId and L.clientId = O.clientId where L.clientId = ? and L.propId in (${propertiesIds}) and L.amount < 0 and L.type = ? and Date(L.createdAt) >= Date(O.createdAt) group by L.tenantId) as LT on DT.tenantId = LT.tenantId left join Dues as D on DT.tenantId = D.tenantId join Tenants as T on DT.tenantId = T.id join Properties as P on D.propId = P.id join Rooms as R on D.roomId = R.id where COALESCE(DT.totalTenantDues, 0) > COALESCE(LT.securityPaidAmt, 0);`;

  const data = [
    clientId,
    clientId,
    CONSTANTS.DUES_TYPES.SECURITY,
  ];

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

duesDB.getSecurityShortFallAmtForStaff = async ({
  clientId,
  propertiesIds,
}: duesTypes & {propertiesIds: string}) => {
  const query = 
  `select SUM(COALESCE(DT.totalTenantDues, 0) - COALESCE(LT.securityPaidAmt, 0)) AS totalDifference from (Select D.tenantId, Sum(D.balance) as totalTenantDues from Dues as D join Occupancies as O on O.tenantId = D.tenantId and O.clientId = D.clientId where D.clientId = ? and D.propId in (${propertiesIds}) group by D.tenantId) as DT left join (select L.tenantId, Sum(ABS(L.amount)) as securityPaidAmt from Ledgers as L join Occupancies as O on L.tenantId = O.tenantId and L.clientId = O.clientId where L.clientId = ? and L.propId in (${propertiesIds}) and L.amount < 0 and L.type = ? and Date(L.createdAt) >= Date(O.createdAt) group by L.tenantId) as LT on DT.tenantId = LT.tenantId where COALESCE(DT.totalTenantDues, 0) > COALESCE(LT.securityPaidAmt, 0);`;

  const data = [
    clientId,
    clientId,
    CONSTANTS.DUES_TYPES.SECURITY,
  ];

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

duesDB.getSecurityShortFallDuesForProp = async ({
  clientId,
  propId,
}: duesTypes) => {
  const query = 
  `select D.*, T.name as tenantName, T.mobile as tenantMobile, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType from (Select D.tenantId, Sum(D.balance) as totalTenantDues from Dues as D join Occupancies as O on O.tenantId = D.tenantId and O.clientId = D.clientId where D.clientId = ? and D.propId = ? group by D.tenantId) as DT left join (select L.tenantId, Sum(ABS(L.amount)) as securityPaidAmt from Ledgers as L join Occupancies as O on L.tenantId = O.tenantId and L.clientId = O.clientId where L.clientId = ? and L.propId = ? and L.amount < 0 and L.type = ? and Date(L.createdAt) >= Date(O.createdAt) group by L.tenantId) as LT on DT.tenantId = LT.tenantId left join Dues as D on DT.tenantId = D.tenantId join Tenants as T on DT.tenantId = T.id join Properties as P on D.propId = P.id join Rooms as R on D.roomId = R.id where COALESCE(DT.totalTenantDues, 0) > COALESCE(LT.securityPaidAmt, 0);`;

  const data = [
    clientId,
    propId,
    clientId,
    propId,
    CONSTANTS.DUES_TYPES.SECURITY,
  ];

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

duesDB.getSecurityShortFallDuesForLocation = async ({
  clientId,
  locationId,
}: duesTypes & {locationId: any}) => {
  const query = 
  `select D.*, T.name as tenantName, T.mobile as tenantMobile, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType from (Select D.tenantId, Sum(D.balance) as totalTenantDues from Dues as D join Occupancies as O on O.tenantId = D.tenantId and O.clientId = D.clientId join Properties as P on P.id = D.propId where D.clientId = ? and P.locationId = ? group by D.tenantId) as DT left join (select L.tenantId, Sum(ABS(L.amount)) as securityPaidAmt from Ledgers as L join Occupancies as O on L.tenantId = O.tenantId and L.clientId = O.clientId join Properties as P on P.id = L.propId where L.clientId = ? and P.locationId = ? and L.amount < 0 and L.type = ? and Date(L.createdAt) >= Date(O.createdAt) group by L.tenantId) as LT on DT.tenantId = LT.tenantId left join Dues as D on DT.tenantId = D.tenantId join Tenants as T on DT.tenantId = T.id join Properties as P on D.propId = P.id join Rooms as R on D.roomId = R.id where COALESCE(DT.totalTenantDues, 0) > COALESCE(LT.securityPaidAmt, 0);`;

  const data = [
    clientId,
    locationId,
    clientId,
    locationId,
    CONSTANTS.DUES_TYPES.SECURITY,
  ];

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

duesDB.getSecurityShortFallAmtForProp = async ({
  clientId,
  propId,
}: duesTypes) => {
  const query = 
  `select SUM(COALESCE(DT.totalTenantDues, 0) - COALESCE(LT.securityPaidAmt, 0)) AS totalDifference from (Select D.tenantId, Sum(D.balance) as totalTenantDues from Dues as D join Occupancies as O on O.tenantId = D.tenantId and O.clientId = D.clientId where D.clientId = ? and D.propId = ? group by D.tenantId) as DT left join (select L.tenantId, Sum(ABS(L.amount)) as securityPaidAmt from Ledgers as L join Occupancies as O on L.tenantId = O.tenantId and L.clientId = O.clientId where L.clientId = ? and L.propId = ? and L.amount < 0 and L.type = ? and Date(L.createdAt) >= Date(O.createdAt) group by L.tenantId) as LT on DT.tenantId = LT.tenantId where COALESCE(DT.totalTenantDues, 0) > COALESCE(LT.securityPaidAmt, 0);`;

  const data = [
    clientId,
    propId,
    clientId,
    propId,
    CONSTANTS.DUES_TYPES.SECURITY,
  ];

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

duesDB.getSecurityShortFallAmtForLocation = async ({
  clientId,
  locationId,
}: duesTypes & {locationId: number}) => {
  const query = 
  `select SUM(COALESCE(DT.totalTenantDues, 0) - COALESCE(LT.securityPaidAmt, 0)) AS totalDifference from (Select D.tenantId, Sum(D.balance) as totalTenantDues from Dues as D join Properties as P on P.id = D.propId join Occupancies as O on O.tenantId = D.tenantId and O.clientId = D.clientId where D.clientId = ? and P.locationId = ? group by D.tenantId) as DT left join (select L.tenantId, Sum(ABS(L.amount)) as securityPaidAmt from Ledgers as L join Properties as P on P.id = L.propId join Occupancies as O on L.tenantId = O.tenantId and L.clientId = O.clientId where L.clientId = ? and P.locationId = ? and L.amount < 0 and L.type = ? and Date(L.createdAt) >= Date(O.createdAt) group by L.tenantId) as LT on DT.tenantId = LT.tenantId where COALESCE(DT.totalTenantDues, 0) > COALESCE(LT.securityPaidAmt, 0);`;

  const data = [
    clientId,
    locationId,
    clientId,
    locationId,
    CONSTANTS.DUES_TYPES.SECURITY,
  ];

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

duesDB.getAllDuesByPropId = async ({ propId }: duesTypes) => {
  const query =
    "Select sum(balance) as total from Dues where propId = ?";
  const data = [propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0]['total'];
  else return 0;
};

duesDB.toggleHide = async ({
  id,
  hideFromTenant,
}: duesTypes) => {
  const query = `Update Dues set hideFromTenant = ? where id = ?`;
  const data = [
    hideFromTenant,
    id,
  ];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

duesDB.getByClientIdAndOverDueDuration = async ({
   clientId,
   overDueDuration  
  }: duesTypes & {overDueDuration: number;}) => {
  const query =
    "Select D.id, D.remarks, D.title, D.propId, D.roomId, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.description, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? and P.status = ? and DATE(CURDATE()) > DATE_ADD(DATE(D.dueDate), INTERVAL ? DAY) order by D.id desc";
  const data = [
    CONSTANTS.DOCUMENT_TYPES.SELFI,
    clientId,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    overDueDuration,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return 0;
};

duesDB.getByClientIdAndOverDueDurationForStaff = async ({
   clientId,
   propertiesIds,
   overDueDuration  
  }: duesTypes & {overDueDuration: number; propertiesIds: string;}) => {
  const query =
    `Select D.id, D.remarks, D.title, D.propId, D.roomId, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.description, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? and P.id in (${propertiesIds}) and P.status = ? and DATE(CURDATE()) > DATE_ADD(DATE(D.dueDate), INTERVAL ? DAY) order by D.id desc`;
  const data = [
    CONSTANTS.DOCUMENT_TYPES.SELFI,
    clientId,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    overDueDuration,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return 0;
};

duesDB.getByClientIdAndPropIdAndOverDueDuration = async ({
   clientId,
   propId,
   overDueDuration  
  }: duesTypes & {overDueDuration: number;}) => {
  const query =
    "Select D.id, D.remarks, D.title, D.propId, D.roomId, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.description, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? and P.id = ? and P.status = ? and DATE(CURDATE()) > DATE_ADD(Date(D.dueDate), INTERVAL ? DAY) order by D.id desc";
  const data = [
    CONSTANTS.DOCUMENT_TYPES.SELFI,
    clientId,
    propId,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    overDueDuration,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return 0;
};

duesDB.getByClientIdAndLocationIdAndOverDueDuration = async ({
   clientId,
   locationId,
   overDueDuration  
  }: duesTypes & { locationId: any; overDueDuration: number;}) => {
  const query =
    "Select D.id, D.remarks, D.title, D.propId, D.roomId, D.balance, D.ledgerReferenceId, D.rentStartDate, D.rentEndDate, D.amount, D.dueDate, D.description, D.type, D.createdAt, D.updatedAt, T.name as tenantName, T.mobile as tenantMobile, T.id as tenantId, R.roomNum, R.flatId, R.floor, P.name as propertyName, P.type as propertyType, (select value from Documents where tenantId = D.tenantId and clientId = D.clientId and type = ? and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = D.tenantId and clientId = D.clientId order by id desc limit 1) as kycStatus from Dues as D inner join Tenants as T on T.id = D.tenantId inner join Rooms as R on R.id = D.roomId inner join Properties as P on P.id = D.propId where D.clientId = ? and P.locationId = ? and P.status = ? and DATE(CURDATE()) > DATE_ADD(Date(D.dueDate), INTERVAL ? DAY) order by D.id desc";
  const data = [
    CONSTANTS.DOCUMENT_TYPES.SELFI,
    clientId,
    locationId,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    overDueDuration,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return 0;
};

duesDB.getPendingDuesTenantIdAndClientId = async ({
  clientId,
  tenantId,
}: duesTypes) => {
  const query = 
   `Select sum(balance) as total from Dues where clientId = ? and tenantId = ?`;
  const data = [
    clientId,
    tenantId,
  ];

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


duesDB.getDuesForClientMultipleTenants = async ({
  clientId,
  tenantIds,
  type,
  startDate,
  endDate,
}: any) => {
  let query = 
   `Select id, tenantId, amount, balance, type, rentStartDate, rentEndDate, title, description, remarks from Dues where clientId = ? and tenantId in (${tenantIds.map(() => '?').join(',')})`;
   let data = [
    clientId,
    ...tenantIds,
  ];
  if(startDate) {
    query += " and Date(dueDate) BETWEEN ? and ?";
    data.push(startDate, endDate);
  }
  

  if (type && type.length > 0) {
    query += ` and type in (${type.map(() => '?').join(',')})`;
    data.push(...type);
  }


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

duesDB.updateTallyStatus = async ({ id, tallyStatus }: duesTypes) => {
  const query = "update Dues set tallyStatus = ? where id = ? limit 1";
  const data = [tallyStatus, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

duesDB.syncDuesTally = async ({ clientId }: duesTypes) => {
  const query = "update Dues set tallyStatus = ? where clientId = ? and tallyStatus = ?";
  const data = [
    CONSTANTS.TALLY_STATUS.RETRY,
    clientId,
    CONSTANTS.TALLY_STATUS.FAILED,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

duesDB.updateOccupancyIdByClientIdAndTenantId = async ({ clientId, tenantId, occupancyId }: duesTypes) => {
  const query = "update Dues set occupancyId = ? where clientId = ? and tenantId = ?";
  const data = [occupancyId, clientId, tenantId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

duesDB.updateDueForCancelEviction = async ({
  id,
  rentStartDate,
  rentEndDate,
  amount,
  balance,
}: duesTypes) => {
  const query = "update Dues set rentStartDate = ? , rentEndDate = ? , amount = ? , balance = ? where id = ? limit 1";
  const data = [rentStartDate, rentEndDate, amount, balance, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};


export default duesDB;