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

const transactionDB: any = {};

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

transactionDB.getByGId = async ({ gId }: transactionsTypes) => {
  const query = "Select * from Transactions where gId = ?";
  const data = [gId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

transactionDB.getByTenantId = async ({
  tenantId,
  clientId,
  pageNum,
  limit,
}: transactionsTypes & { pageNum: number; limit: number }) => {
  //const query =
  //`Select ANY_VALUE(T.id) as id, ANY_VALUE(T.gateway) as gateway,ANY_VALUE(paymentAccountNo) as paymentAccountNo, ANY_VALUE(T.docs) as docs, ANY_VALUE(T.remarks) as remarks, ANY_VALUE(T.title) as title, ANY_VALUE(if(T.splitTnxId is NULL, T.gId, T.splitTnxId)) as gId, ANY_VALUE(T.settleStatus) as settleStatus, ANY_VALUE(T.settledOn) as settledOn, ANY_VALUE(T.utrNo) as utrNo, ANY_VALUE(T.bankRefNum) as bankRefNum, SUM(T.amount) as amount, ANY_VALUE(T.collectionDate) as collectionDate, ANY_VALUE(T.receipt) as receipt, ANY_VALUE(T.name) as name, ANY_VALUE(T.type) as type, ANY_VALUE(T.transactionFor) as transactionFor, ANY_VALUE(T.status) as status, ANY_VALUE(T.recordedBy) as recordedBy, ANY_VALUE(T.dueDate) as dueDate, ANY_VALUE(T.mode) as mode, ANY_VALUE(T.collectionDate) as createdAt, ANY_VALUE(T.updatedAt) as updatedAt, ANY_VALUE(TE.name) as tenantName, ANY_VALUE(T.propName) as propName, ANY_VALUE(T.roomNum) as roomNum, ANY_VALUE(T.tenantId) as tenantId from Transactions as T INNER JOIN Tenants as TE ON TE.id=T.tenantId where T.tenantId = ? and T.clientId = ? group by T.gId order by ANY_VALUE(T.id) Desc LIMIT ?,?;`;
  const query =
    `Select T.id, T.gateway, paymentAccountNo, T.docs, T.remarks, T.title, if(T.splitTnxId is NULL, T.gId, T.splitTnxId) as gId, T.settleStatus, T.settledOn, T.utrNo, T.bankRefNum, T.amount, T.collectionDate, T.receipt, T.name, T.type, T.transactionFor, T.status, T.recordedBy, T.dueDate, T.mode, T.collectionDate as createdAt, T.updatedAt, TE.name as tenantName, T.propName, T.roomNum, T.tenantId from Transactions as T INNER JOIN Tenants as TE ON TE.id=T.tenantId where T.tenantId = ? and T.clientId = ? order by T.id Desc LIMIT ?,?;`;

  const offset = (pageNum - 1) * limit;
  const data = [tenantId, clientId, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

transactionDB.getByTenantIdAndClientId = async ({
  tenantId,
  clientId,
}: transactionsTypes) => {
  const query =
    "Select Te.name as tenantName, T.ledgerReferenceId, T.gateway, T.tenantId, T.title, T.docs, T.remarks, T.id, if(T.splitTnxId is NULL, T.gId, T.splitTnxId) as gId, T.amount, T.collectionDate, T.receipt, T.name, T.type, T.transactionFor, T.status, T.recordedBy, T.dueDate, T.mode, T.collectionDate as createdAt, T.updatedAt, T.propName, T.roomNum from Transactions as T INNER JOIN Tenants as Te ON Te.id=T.tenantId where T.tenantId = ? and T.clientId = ? order by T.collectionDate Desc;";
  const data = [tenantId, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

transactionDB.getSecurityByTenantIdAndClientId = async ({
  tenantId,
  clientId,
}: transactionsTypes) => {
  const query =
    "Select Te.name as tenantName, T.docs, T.gateway, T.title, T.recordedBy, T.remarks, T.id, T.gId, T.amount, T.collectionDate, T.receipt, T.name, T.type, T.transactionFor, T.status, T.dueDate, T.mode, T.collectionDate as createdAt, T.updatedAt, T.propName, T.roomNum from Transactions as T INNER JOIN Tenants as Te ON Te.id=T.tenantId where T.tenantId = ? and T.clientId = ? and transactionFor = ? order by T.id Desc;";
  const data = [tenantId, clientId, CONSTANTS.TRANSACTION_FOR.SECURITY];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

transactionDB.getSecurityAmount = async ({
  tenantId,
  clientId,
  roomId,
  propId,
  status,
  transactionFor,
}: transactionsTypes) => {
  const query =
    "Select id, amount from Transactions where clientId=? and tenantId=? and roomId=? and propId=? and status = ? and transactionFor = ?";
  const data = [clientId, tenantId, roomId, propId, status, transactionFor];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

transactionDB.getSecurityAdjustedTransactions = async ({
  clientId,
  tenantId,
  propId,
  roomId,
  type,
  status,
}: transactionsTypes) => {
  const query =
    "Select * from Transactions where clientId=? and tenantId=? and roomId=? and type=? and propId=? and status = ? ";
  const data = [clientId, tenantId, roomId, type, propId, status];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

transactionDB.getSearchResultByClientId = async ({
  clientId,
  pageNum,
  limit,
  searchVal,
  transactionForValue,
}: transactionsTypes & {
  pageNum: number;
  limit: number;
  searchVal: string;
  transactionForValue: string;
}) => {
  const offset = (pageNum - 1) * limit;

  const query =
    "Select T.id, T.docs, T.gateway, if(T.splitTnxId is NULL, T.gId, T.splitTnxId) as gId, T.title, T.remarks, T.amount, T.collectionDate, T.receipt, T.name, T.type, T.transactionFor, T.status, T.recordedBy, T.dueDate, T.mode, T.createdAt, T.updatedAt, TE.id as tenantId, TE.name as tenantName, T.propName, T.roomNum, T.holdAmount, T.discount, T.paymentAccountNo, T.paymentAccountName, T.settleStatus, T.utrNo, T.bankRefNum, T.settledOn from Transactions as T left join Tenants as TE on TE.id = T.tenantId where T.clientId = ? and (T.transactionFor = ? OR TE.name like ? OR T.roomNum like ?) and T.status = ? order by T.id desc LIMIT ?, ?";

  const data = [
    clientId,
    transactionForValue,
    `${searchVal}%`,
    `${searchVal}`,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    `${offset}`,
    `${limit}`,
  ];

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

transactionDB.getSearchResultForStaff = async ({
  clientId,
  pageNum,
  limit,
  searchVal,
  transactionForValue,
  propertiesIds,
}: transactionsTypes & {
  pageNum: number;
  limit: number;
  searchVal: string;
  transactionForValue: string;
  propertiesIds: string;
}) => {
  const offset = (pageNum - 1) * limit;

  const query =
    "Select T.id, T.docs, T.gateway, if(T.splitTnxId is NULL, T.gId, T.splitTnxId) as gId, T.title, T.remarks, T.amount, T.collectionDate, T.receipt, T.name, T.type, T.transactionFor, T.status, T.recordedBy, T.dueDate, T.mode, T.createdAt, T.updatedAt, TE.id as tenantId, TE.name as tenantName, T.propName, T.roomNum, T.holdAmount, T.discount, T.paymentAccountNo, T.paymentAccountName, T.settleStatus, T.utrNo, T.bankRefNum, T.settledOn from Transactions as T left join Tenants as TE on TE.id = T.tenantId where T.clientId = ? and (T.transactionFor = ? OR TE.name like ? OR T.roomNum like ?) and T.propId in (" +
    `${propertiesIds}` +
    ") and T.status = ? order by T.id desc LIMIT ?, ?";

  const data = [
    clientId,
    transactionForValue,
    `%${searchVal}%`,
    `${searchVal}`,
    //propertiesIds,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    `${offset}`,
    `${limit}`,
  ];

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

transactionDB.getSearchResultByTenantId = async ({
  tenantId,
  clientId,
  pageNum,
  limit,
  searchVal,
  transactionForValue,
}: transactionsTypes & {
  pageNum: number;
  limit: number;
  searchVal: string;
  transactionForValue: string;
}) => {
  const offset = (pageNum - 1) * limit;

  let query = "";
  let data = [];

  if (Number(transactionForValue)) {
    query =
      "Select T.id, T.docs, T.gateway, T.gId, T.amount, T.collectionDate, T.receipt, T.title, T.remarks, T.name, T.type, T.transactionFor, T.status, T.dueDate, T.recordedBy, T.mode, T.createdAt, T.updatedAt, TE.name as tenantName, T.propName, T.roomNum, T.tenantId from Transactions as T left join Tenants as TE on TE.id = T.tenantId where T.tenantId = ? and T.clientId = ? and T.transactionFor = ? and T.status = ? order by T.id desc LIMIT ?, ?";

    data = [
      tenantId,
      clientId,
      transactionForValue,
      CONSTANTS.TRANSACTION_STATUS.SUCCESS,
      `${offset}`,
      `${limit}`,
    ];
  } else {
    query =
      "Select * from (Select T.id, T.docs, T.gateway, T.gId, T.amount, T.collectionDate, T.title, T.remarks, T.receipt, T.name, T.type, T.transactionFor, T.status, T.recordedBy, T.dueDate, T.mode, T.createdAt, T.updatedAt, TE.name as tenantName, T.propName, T.roomNum, T.tenantId from Transactions as T left join Tenants as TE on TE.id = T.tenantId where T.tenantId = ? and T.clientId = ? and T.status = ?) as v where  (v.amount like ? or v.gId like ?) order by v.id desc LIMIT ?, ?";

    data = [
      tenantId,
      clientId,
      CONSTANTS.TRANSACTION_STATUS.SUCCESS,
      `%${searchVal}%`,
      `%${searchVal}%`,
      `${offset}`,
      `${limit}`,
    ];
  }

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

transactionDB.getByClientId = async ({
  clientId,
  pageNum,
  limit,
  month,
}: transactionsTypes & { pageNum: number; limit: number; month: string }) => {
  const query =
    "Select T.id, T.docs, T.gateway, if(T.splitTnxId is NULL, T.gId, T.splitTnxId) as gId, T.title, T.remarks, T.amount, T.collectionDate, T.receipt, T.name, T.type, T.transactionFor, T.status, T.recordedBy, T.dueDate, T.mode, T.collectionDate as createdAt, T.updatedAt, TE.name as tenantName, T.propName, T.roomNum from Transactions as T left join Tenants as TE on TE.id = T.tenantId where T.clientId = ? and MONTH(T.collectionDate) = MONTH(?) and YEAR(T.collectionDate) = YEAR(?) and T.status = ? order by T.collectionDate Desc LIMIT ?, ?";
  const offset = (pageNum - 1) * limit;
  const data = [
    clientId,
    month,
    month,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

transactionDB.getTotalByClientId = async ({
  clientId,
  month,
}: transactionsTypes & { month: string }) => {
  const query =
    "Select SUM(T.amount) as total from Transactions as T where T.clientId = ? and MONTH(T.collectionDate) = MONTH(?) and YEAR(T.collectionDate) = YEAR(?) and T.status = ? and T.type != ? ";
  const data = [
    clientId,
    month,
    month,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return Number(rows[0].total);
  else return 0;
};

transactionDB.getTotalByTenantId = async ({
  tenantId,
  clientId,
}: // year,
  transactionsTypes & { year: string }) => {
  // const query =
  //   "Select SUM(T.amount) as total from Transactions as T where T.tenantId = ? and T.clientId = ? and YEAR(T.collectionDate) = YEAR(?) and T.status = ? and T.type != ? ";
  const query =
    "Select SUM(T.amount) as total from Transactions as T where T.amount > 0 and T.tenantId = ? and T.clientId = ? and T.status = ? and T.type != ? and T.isFinanciallyApplicable = 1";
  const data = [
    tenantId,
    clientId,
    // year,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return Number(rows[0].total);
  else return 0;
};

transactionDB.getTotalByClientIdForStaff = async ({
  clientId,
  month,
  propertiesIds,
}: transactionsTypes & {
  month: string;
  propertiesIds: string;
}) => {
  const query =
    "Select SUM(amount) as total from Transactions where clientId = ? and MONTH(createdAt) = MONTH(?) and YEAR(createdAt) = YEAR(?) and propId in (" +
    `${propertiesIds}` +
    ") and status = ? and type != ?  order by id Desc";
  const data = [
    clientId,
    month,
    month,
    //propertiesIds,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return Number(rows[0].total);
  else return 0;
};

transactionDB.getForStaff = async ({
  clientId,
  pageNum,
  limit,
  propertiesIds,
  month,
}: transactionsTypes & {
  pageNum: number;
  limit: number;
  propertiesIds: string;
  month: string;
}) => {
  const query =
    "Select T.id, T.tenantId, T.gateway, T.docs, if(T.splitTnxId is NULL, T.gId, T.splitTnxId) as gId, T.title, T.amount, T.remarks, T.collectionDate, T.receipt, T.name, T.type, T.transactionFor, T.status, T.recordedBy, T.dueDate, T.mode, T.createdAt, T.updatedAt, TE.name as tenantName, T.propName, T.roomNum, T.paymentAccountNo from Transactions as T left join Tenants as TE on TE.id = T.tenantId where T.clientId = ? and T.propId in (" +
    `${propertiesIds}` +
    ") and  MONTH(T.collectionDate) = MONTH(?) and  YEAR(T.collectionDate) = YEAR(?) and T.status = ? order by T.id Desc LIMIT ?, ?";
  const offset = (pageNum - 1) * limit;
  const data = [
    clientId,
    //propertiesIds,
    month,
    month,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

transactionDB.getForStaffByDateRange = async ({
  clientId,
  pageNum,
  limit,
  startDate,
  endDate,
  propertiesIds,
}: transactionsTypes & {
  pageNum: number;
  limit: number;
  propertiesIds: string;
  startDate: string;
  endDate: string;
}) => {
  const query =
    "Select T.id, T.tenantId, T.gateway, T.clientId, T.docs, if(T.splitTnxId is NULL, T.gId, T.splitTnxId) as gId, T.title, T.amount, T.remarks, T.collectionDate, T.receipt, T.name, T.type, T.transactionFor, T.status, T.recordedBy, T.dueDate, T.mode, T.createdAt, T.updatedAt, TE.name as tenantName, TE.mobile as tenantMobile, (select value from Documents where tenantId = T.tenantId and clientId = T.clientId and type = 4 and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = T.tenantId and clientId = T.clientId order by id desc limit 1) as kycStatus, T.propName, T.roomNum from Transactions as T left join Tenants as TE on TE.id = T.tenantId where T.clientId = ? and T.propId in (" +
    `${propertiesIds}` +
    ") and  date(T.collectionDate) Between ? and ? and T.status = ? order by T.id Desc LIMIT ?, ?";
  const offset = (pageNum - 1) * limit;
  const data = [
    clientId,
    //propertiesIds,
    startDate,
    endDate,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

transactionDB.getMonthlyRentReceived = async ({
  propId,
  type,
  status,
}: transactionsTypes) => {
  const query =
    "Select SUM(amount) as received from Transactions where type=? and propId=? and status=? and MONTH(dueDate) = MONTH(NOW())";
  const data = [type, propId, status];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

transactionDB.getTotalIncomeStats = async ({
  clientId,
  status,
}: transactionsTypes) => {
  const query =
    "Select SUM(T.amount) as totalIncome, MONTH(T.collectionDate) as month, YEAR(T.collectionDate) as year from Transactions as T where T.clientId=? and T.status=? and T.type != ? Group By  month, year  Order by year desc, month desc";
  const data = [
    clientId,
    status,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getTodaysCollection = async ({ clientId }: transactionsTypes) => {
  const query =
    "Select SUM(T.amount) as totalIncome from Transactions as T where T.clientId=? and T.isFinanciallyApplicable = 1 and T.status=? and T.type != ? and T.amount > 0 and DATE(T.collectionDate) = DATE(now())";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].totalIncome;
  else return 0;
};

transactionDB.getTodaysCollectionForWeb = async ({ clientId, propIds, }: transactionsTypes & { propIds: any }) => {
  let query =
    "Select SUM(T.amount) as totalIncome from Transactions as T where T.clientId=? and T.isFinanciallyApplicable = 1 and T.status=? and T.type != ? and T.amount > 0 and DATE(T.collectionDate) = DATE(now())";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
  ];

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

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

transactionDB.getTodaysCollectionForProp = async ({
  propId,
}: transactionsTypes) => {
  const query =
    "Select SUM(T.amount) as totalIncome from Transactions as T where T.propId=? and T.status=? and T.type != ? and T.amount > 0 and DATE(T.collectionDate) = DATE(now())";
  const data = [
    propId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].totalIncome;
  else return 0;
};

transactionDB.getTodaysSecurityCollection = async ({
  clientId,
}: transactionsTypes) => {
  const query =
    "Select SUM(T.amount) as totalIncome from Transactions as T where T.clientId=? and T.status=? and T.type != ? and T.transactionFor = ? and DATE(T.collectionDate) = DATE(now())";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].totalIncome;
  else return 0;
};

transactionDB.getTodaysSecurityCollectionForProp = async ({
  propId,
}: transactionsTypes) => {
  const query =
    "Select SUM(T.amount) as totalIncome from Transactions as T where T.propId=? and T.status=? and T.type != ? and T.transactionFor = ? and DATE(T.collectionDate) = DATE(now())";
  const data = [
    propId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].totalIncome;
  else return 0;
};

transactionDB.getTodaysRentCollection = async ({
  clientId,
}: transactionsTypes) => {
  const query =
    "Select SUM(T.amount) as totalIncome from Transactions as T where T.clientId=? and T.status=? and T.type != ? and T.transactionFor = ? and DATE(T.collectionDate) = DATE(now())";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.TRANSACTION_FOR.RENT,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].totalIncome;
  else return 0;
};

transactionDB.getTodaysRentCollectionForStaff = async ({
  clientId,
  propertiesIds
}: transactionsTypes & { propertiesIds: string }) => {
  const query =
    `Select SUM(T.amount) as totalIncome from Transactions as T where T.clientId=? and T.status=? and T.type != ? and T.transactionFor = ? and DATE(T.collectionDate) = DATE(now()) and T.propId in (${propertiesIds})`;
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.TRANSACTION_FOR.RENT,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].totalIncome;
  else return 0;
};

transactionDB.getTodaysRentCollectionForProp = async ({
  propId,
}: transactionsTypes) => {
  const query =
    "Select SUM(T.amount) as totalIncome from Transactions as T where T.propId=? and T.status=? and T.type != ? and T.transactionFor = ? and DATE(T.collectionDate) = DATE(now())";
  const data = [
    propId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.TRANSACTION_FOR.RENT,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].totalIncome;
  else return 0;
};

//for handling edge cases like excess settlement, initial settlement
transactionDB.getTodaysSecurityCollection_New = async ({
  clientId,
}: transactionsTypes) => {
  const query =
    "Select SUM(T.amount) as totalIncome from Transactions as T join Ledgers as L on T.ledgerReferenceId = L.referenceId  where T.clientId=? and T.status=? and T.type != ? and L.type = ? and DATE(T.collectionDate) = DATE(now())";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.DUES_TYPES.SECURITY,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].totalIncome;
  else return 0;
};

transactionDB.getTodaysCollectionForStaff = async ({
  clientId,
  propertiesIds,
}: transactionsTypes & { propertiesIds: string }) => {
  const query =
    "Select SUM(T.amount) as totalIncome from Transactions as T where T.clientId=? and T.isFinanciallyApplicable = 1 and T.status=? and T.type != ? and T.amount > 0 and T.propId in (" +
    `${propertiesIds}` +
    ") and DATE(T.collectionDate) = DATE(now())";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    //propertiesIds,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].totalIncome;
  else return 0;
};

transactionDB.getTodaysCollectionForWebStaff = async ({
  clientId,
  propIds,
  propertiesIds,
}: transactionsTypes & { propertiesIds: string; propIds: any }) => {
  let query =
    "Select SUM(T.amount) as totalIncome from Transactions as T where T.clientId=? and T.isFinanciallyApplicable = 1 and T.status=? and T.type != ? and T.amount > 0 and T.propId in (" +
    `${propertiesIds}` +
    ") and DATE(T.collectionDate) = DATE(now())";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    //propertiesIds,
  ];

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

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

transactionDB.getTodaysSecurityCollectionForStaff = async ({
  clientId,
  propertiesIds,
}: transactionsTypes & { propertiesIds: string }) => {
  const query =
    "Select SUM(T.amount) as totalIncome from Transactions as T where T.clientId=? and T.isFinanciallyApplicable = 1 and T.status=? and T.type != ? and T.propId in (" +
    `${propertiesIds}` +
    ") and transactionFor = ? and DATE(T.collectionDate) = DATE(now())";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.DUES_TYPES.SECURITY,
    //propertiesIds,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].totalIncome;
  else return 0;
};

//for handling edge cases like excess settlement, initial settlement
transactionDB.getTodaysSecurityCollectionForStaff_New = async ({
  clientId,
  propertiesIds,
}: transactionsTypes & { propertiesIds: string }) => {
  const query =
    "Select SUM(T.amount) as totalIncome from Transactions as T join Ledgers as L on T.ledgerReferenceId = L.referenceId where T.clientId=? and T.status=? and T.type != ? and T.propId in (" +
    `${propertiesIds}` +
    ") and L.type = ? and DATE(T.collectionDate) = DATE(now())";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
    //propertiesIds,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].totalIncome;
  else return 0;
};

transactionDB.getTotalIncomeStatsForStaff = async ({
  clientId,
  status,
  propertiesIds,
}: transactionsTypes & { clientId: any; propertiesIds: string }) => {
  const query =
    "Select SUM(T.amount) as totalIncome, MONTH(T.collectionDate) as month, YEAR(T.collectionDate) as year from Transactions as T where T.clientId=? and T.isFinanciallyApplicable = 1 and T.status=? and T.propId in (" +
    `${propertiesIds}` +
    ") and T.type != ?  Group By month, year Order by year desc, month desc";
  const data = [
    clientId,
    status,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getTotalIncomeStatsByProp = async ({
  propId,
  status,
}: transactionsTypes) => {
  const query =
    "Select SUM(T.amount) as totalIncome, MONTH(T.collectionDate) as month, YEAR(T.collectionDate) as year from Transactions as T where T.propId=? and T.status=? and T.type != ? Group By month, year Order by year desc, month desc";
  const data = [propId, status, CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getCountByTenantId = async ({ tenantId }: transactionsTypes) => {
  const query =
    "Select (Select SUM(amount) from Transactions where tenantId =? and transactionFor = ? and status = ?) as rent, (Select SUM(amount) from Transactions where tenantId =? and transactionFor = ? and status = ?) as security, (Select SUM(amount) from Transactions where tenantId =? and transactionFor NOT IN (?,?) and status = ?) as others from Transactions where tenantId =?";
  const data = [
    tenantId,
    CONSTANTS.TRANSACTION_FOR.RENT,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    tenantId,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    tenantId,
    CONSTANTS.TRANSACTION_FOR.RENT,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    tenantId,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

transactionDB.addOnline = async ({
  gId,
  bankId,
  clientId,
  tenantId,
  roomId,
  propId,
  amount,
  name,
  type,
  transactionFor,
  status,
  dueDate,
  receipt,
  mode,
  collectionDate,
  propName,
  roomNum,
  ledgerReferenceId,
  recordedBy,
  charges,
  gstCharges,
  title = null,
  isFinanciallyApplicable = 1,
}: transactionsTypes) => {
  const query =
    "INSERT INTO Transactions (gId, bankId, clientId, tenantId, roomId, propId, amount, name, type, transactionFor, status, dueDate, receipt, mode, collectionDate, propName, roomNum, ledgerReferenceId, recordedBy, charges, gstCharges, title, isFinanciallyApplicable) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  const data = [
    gId,
    bankId,
    clientId,
    tenantId,
    roomId,
    propId,
    amount,
    name,
    type,
    transactionFor,
    status,
    dueDate,
    receipt,
    mode,
    collectionDate,
    propName,
    roomNum,
    ledgerReferenceId,
    recordedBy,
    charges,
    gstCharges,
    title,
    isFinanciallyApplicable,
  ];
  const [row] = await DB.query<ResultSetHeader>(query, data);
  return row.insertId;
};

transactionDB.add = async ({
  gId,
  clientId,
  tenantId,
  roomId,
  propId,
  amount,
  name,
  type,
  transactionFor,
  status,
  dueDate,
  mode,
  receipt,
  collectionDate,
  propName,
  roomNum,
  ledgerReferenceId,
  recordedBy,
  discount = 0,
  paymentAccountNo = null,
  paymentAccountName = null,
  title = null,
  bankRefNum = null,
  remarks = null,
  isFinanciallyApplicable = 1,
}: transactionsTypes) => {
  const query =
    "INSERT INTO Transactions (gId, clientId, tenantId, roomId, propId, amount, name, type, transactionFor, status, dueDate, mode, receipt, collectionDate, propName,roomNum, ledgerReferenceId, recordedBy, discount, paymentAccountNo, paymentAccountName, title, bankRefNum, remarks, isFinanciallyApplicable) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  const data = [
    gId,
    clientId,
    tenantId,
    roomId,
    propId,
    amount,
    name,
    type,
    transactionFor,
    status,
    dueDate,
    mode,
    receipt,
    collectionDate,
    propName,
    roomNum,
    ledgerReferenceId,
    recordedBy,
    discount,
    paymentAccountNo,
    paymentAccountName,
    title,
    bankRefNum,
    remarks,
    isFinanciallyApplicable,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

transactionDB.removeById = async ({ id }: transactionsTypes) => {
  const query = "DELETE FROM Transactions WHERE id = ? limit 1";
  const data = [id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

transactionDB.removeByLedgerReferenceId = async ({ ledgerReferenceId }: transactionsTypes) => {
  const query = "DELETE FROM Transactions WHERE ledgerReferenceId = ?";
  const data = [ledgerReferenceId];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

transactionDB.removeByTenantId = async ({ tenantId }: transactionsTypes) => {
  const query = "DELETE FROM Transactions WHERE tenantId = ?";
  const data = [tenantId];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

transactionDB.getMonthlyRentTransaction = async ({
  clientId,
  status,
}: transactionsTypes) => {
  const query =
    "select sum(amount) as amount, month(collectionDate) as month from Transactions where year(collectionDate)= year(curdate()) and clientId =? and status=? and type != ? group by month";
  const data = [
    clientId,
    status,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getMonthlyRentTransactionByDateRange = async ({
  clientId,
  startDate,
  endDate,
  status,
}: transactionsTypes & { startDate: string; endDate: string; }) => {
  const query =
    "select sum(amount) as amount, month(collectionDate) as month, year(collectionDate) as year from Transactions where DATE(collectionDate) BETWEEN ? and ? and clientId =? and status=? and type != ? and isFinanciallyApplicable = 1 group by year, month";
  const data = [
    startDate,
    endDate,
    clientId,
    status,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getMonthlyRentTransactionByDateRangeForWeb = async ({
  clientId,
  startDate,
  endDate,
  status,
  propIds,
}: transactionsTypes & { startDate: string; endDate: string; propIds: any; }) => {
  let query =
    "select sum(amount) as amount, month(collectionDate) as month, year(collectionDate) as year from Transactions where DATE(collectionDate) BETWEEN ? and ? and clientId =? and status=? and type != ? and isFinanciallyApplicable = 1 ";
  const data = [
    startDate,
    endDate,
    clientId,
    status,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
  ];

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

  query += ` group by year, month`

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

transactionDB.getMonthlyRentTransactionByDateRangeWithoutSecuirty = async ({
  clientId,
  startDate,
  endDate,
  status,
}: transactionsTypes & { startDate: string; endDate: string; }) => {
  const query =
    "select sum(amount) as amount, month(collectionDate) as month, year(collectionDate) as year from Transactions where DATE(collectionDate) BETWEEN ? and ? and clientId =? and status=? and type != ? and transactionFor != ? and amount > 0 group by year, month";
  const data = [
    startDate,
    endDate,
    clientId,
    status,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getMonthlyRentTransactionByDateRangeForProp = async ({
  propId,
  startDate,
  endDate,
  status,
}: transactionsTypes & { startDate: string; endDate: string; }) => {
  const query =
    "select sum(amount) as amount, month(collectionDate) as month, year(collectionDate) as year from Transactions where DATE(collectionDate) BETWEEN ? and ? and propId =? and status=? and type != ? and isFinanciallyApplicable = 1 group by year, month";
  const data = [
    startDate,
    endDate,
    propId,
    status,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getMonthlyRentTransactionForStaff = async ({
  clientId,
  status,
  propertiesIds
}: transactionsTypes & { propertiesIds: string }) => {
  const query =
    `select sum(amount) as amount, month(collectionDate) as month from Transactions where year(collectionDate)= year(curdate()) and clientId =? and status=? and type != ? and propId in (${propertiesIds}) group by month`;
  const data = [
    clientId,
    status,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getMonthlyRentTransactionForProp = async ({
  propId,
  status,
}: transactionsTypes) => {
  const query =
    "select sum(amount) as amount, month(collectionDate) as month from Transactions where year(collectionDate)= year(curdate()) and propId =? and status=? and type != ? group by month";
  const data = [propId, status, CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getYearRentTransaction = async ({
  clientId,
  status,
}: transactionsTypes) => {
  const query =
    "select sum(amount) as amount, year(collectionDate) as year from Transactions where clientId =? and status=? and type != ? group by year";
  const data = [
    clientId,
    status,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getYearRentTransactionForWeb = async ({
  clientId,
  status,
  propIds,
}: transactionsTypes & { propIds: any }) => {
  let query =
    "select sum(amount) as amount, year(collectionDate) as year from Transactions where clientId =? and status=? and type != ? ";
  const data = [
    clientId,
    status,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
  ];

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

  query += ` group by year`

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

transactionDB.getYearRentTransactionForStaff = async ({
  clientId,
  status,
  propertiesIds,
}: transactionsTypes & { propertiesIds: string; }
) => {
  const query =
    `select sum(amount) as amount, year(collectionDate) as year from Transactions where clientId =? and status=? and type != ? and propId in (${propertiesIds}) group by year`;
  const data = [
    clientId,
    status,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getYearRentTransactionForProp = async ({
  propId,
  status,
}: transactionsTypes) => {
  const query =
    "select sum(amount) as amount, year(collectionDate) as year from Transactions where propId =? and status=? and type != ? group by year";
  const data = [propId, status, CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getRentReceivedByYearMonth = async ({
  propId,
  type,
  status,
  year,
  month,
}: transactionsTypes & { year: any; month: any }) => {
  const query =
    "Select SUM(amount) as received from Transactions as T where type=? and propId=? and T.isFinanciallyApplicable = 1 and status=? and MONTH(T.collectionDate) = ? and YEAR(T.collectionDate) = ? ";
  const data = [type, propId, status, month, year];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

transactionDB.getTotalIncomeStatsForStaffByYearMonth = async ({
  clientId,
  status,
  propertiesIds,
  year,
  month,
}: transactionsTypes & { propertiesIds: string; year: any; month: any }) => {
  const query = `Select SUM(T.amount) as totalIncome, MONTH(T.collectionDate) as month from Transactions as T where T.clientId=? and T.isFinanciallyApplicable = 1 and T.status=? and T.propId in (${propertiesIds}) and T.type != ? and YEAR(T.collectionDate) = ? and MONTH(T.collectionDate) = ? Group By month Order by month`;
  const data = [
    clientId,
    status,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    year,
    month,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getTodaysCollectionForStaffByYearMonth = async ({
  clientId,
  propertiesIds,
  year,
  month,
}: transactionsTypes & { propertiesIds: string } & {
  year: any;
  month: any;
}) => {
  // const query = `Select SUM(T.amount) as totalIncome from Transactions as T where T.clientId=? and T.status=? and T.type != ? and T.propId in (${propertiesIds}) and MONTH(T.createdAt) = month(curdate()) and YEAR(T.createdAt) = year(curdate())`;
  const query = `Select SUM(T.amount) as totalIncome from Transactions as T where T.clientId=? and T.status=? and T.type != ? and T.propId in (${propertiesIds}) and DATE(T.createdAt) = DATE(now())`;
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].totalIncome;
  else return 0;
};

transactionDB.getTotalIncomeStatsByMonth = async ({
  clientId,
  status,
  year,
  month,
}: transactionsTypes & { year: any; month: any }) => {
  const query =
    "Select SUM(T.amount) as totalIncome, MONTH(T.collectionDate) as month from Transactions as T where T.clientId=? and T.isFinanciallyApplicable = 1 and T.status=? and T.type != ? and YEAR(T.collectionDate) = ? and MONTH(T.collectionDate)=? group by month";
  const data = [
    clientId,
    status,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    year,
    month,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getTotalIncomeStatsByMonthWithoutRefunds = async ({
  clientId,
  status,
  year,
  month,
}: transactionsTypes & { year: any; month: any }) => {
  const query =
    "Select SUM(T.amount) as totalIncome from Transactions as T where T.clientId=? and T.isFinanciallyApplicable = 1 and T.status=? and T.type != ? and YEAR(T.collectionDate) = ? and MONTH(T.collectionDate)=? and amount > 0";
  const data = [
    clientId,
    status,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    year,
    month,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getTotalIncomeByClientIdForWeb = async ({
  clientId,
  startDate,
  endDate,
  propIds,
}: transactionsTypes & { startDate: string; endDate: string; propIds: any }) => {
  let query =
    `Select SUM(T.amount) as totalIncome from Transactions as T where T.clientId=? and T.isFinanciallyApplicable = 1 and T.status=? and T.type != ? and DATE(T.collectionDate) BETWEEN ? and ? and amount > 0`;
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    startDate,
    endDate,
  ];

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

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

transactionDB.getTotalIncomeStatsByMonthStaff = async ({
  clientId,
  propertiesIds,
  status,
  year,
  month,
}: transactionsTypes & { year: any; month: any; propertiesIds: any }) => {
  const query = `Select SUM(T.amount) as totalIncome, MONTH(T.collectionDate) as month from Transactions as T where T.clientId=? and T.isFinanciallyApplicable = 1 and T.propId in (${propertiesIds}) and T.status=? and T.type != ? and YEAR(T.collectionDate) = ? and MONTH(T.collectionDate)=? group by month`;
  const data = [
    clientId,
    status,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    year,
    month,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getTotalIncomeByClientIdForWebStaff = async ({
  clientId,
  propIds,
  propertiesIds,
  status,
  endDate,
  startDate,
}: transactionsTypes & { startDate: string; endDate: string; propertiesIds: any; propIds: any; }) => {
  let query = `Select SUM(T.amount) as totalIncome, MONTH(T.collectionDate) as month from Transactions as T where T.clientId=? and T.isFinanciallyApplicable = 1 and T.propId in (${propertiesIds}) and T.status=? and T.type != ? and Date(T.collectionDate) BETWEEN ? and ? and amount > 0 `;
  const data = [
    clientId,
    status,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    startDate,
    endDate,
  ];

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

  query += ` group by month`

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

transactionDB.getTotalIncomeStatsByMonthStaffWithoutRefunds = async ({
  clientId,
  propertiesIds,
  status,
  year,
  month,
}: transactionsTypes & { year: any; month: any; propertiesIds: any }) => {
  const query = `Select SUM(T.amount) as totalIncome, MONTH(T.collectionDate) as month from Transactions as T where T.clientId=? and T.isFinanciallyApplicable = 1 and T.propId in (${propertiesIds}) and T.status=? and T.type != ? and YEAR(T.collectionDate) = ? and MONTH(T.collectionDate)=? and amount > 0 group by month`;
  const data = [
    clientId,
    status,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    year,
    month,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getTotalIncomeStatsByMonthProp = async ({
  clientId,
  propId,
  status,
  year,
  month,
}: transactionsTypes & { year: any; month: any }) => {
  const query = `Select SUM(T.amount) as totalIncome, MONTH(T.collectionDate) as month from Transactions as T where T.clientId=? and T.isFinanciallyApplicable = 1 and T.propId = ? and T.status=? and T.type != ? and YEAR(T.collectionDate) = ? and MONTH(T.collectionDate)=? group by month`;
  const data = [
    clientId,
    propId,
    status,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    year,
    month,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getTotalIncomeStatsByMonthPropWithoutRefunds = async ({
  clientId,
  propId,
  status,
  year,
  month,
}: transactionsTypes & { year: any; month: any }) => {
  const query = `Select SUM(T.amount) as totalIncome, MONTH(T.collectionDate) as month from Transactions as T where T.clientId=? and T.isFinanciallyApplicable = 1 and T.propId = ? and T.status=? and T.type != ? and YEAR(T.collectionDate) = ? and MONTH(T.collectionDate)=? and amount > 0 group by month`;
  const data = [
    clientId,
    propId,
    status,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    year,
    month,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getTotalSecurityStatsByMonth = async ({
  clientId,
  status,
  year,
  month,
}: transactionsTypes & { year: any; month: any }) => {
  const query =
    "Select SUM(T.amount) as totalIncome from Transactions as T where T.clientId=? and T.status=? and T.type != ? and transactionFor = ? and YEAR(T.collectionDate) = ? and MONTH(T.collectionDate)=?";
  const data = [
    clientId,
    status,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
    year,
    month,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getTotalSecurityStatsByMonthForStaff = async ({
  clientId,
  propertiesIds,
  status,
  year,
  month,
}: transactionsTypes & { year: any; month: any; propertiesIds: string }) => {
  const query =
    `Select SUM(T.amount) as totalIncome, MONTH(T.collectionDate) as month from Transactions as T where T.clientId=? and T.status=? and T.type != ? and transactionFor = ? and YEAR(T.collectionDate) = ? and MONTH(T.collectionDate)=? and T.propId in (${propertiesIds}) group by month`;
  const data = [
    clientId,
    status,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
    year,
    month,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getTotalIncomeStatsByPropCurrentMonth = async ({
  propId,
  status,
}: transactionsTypes) => {
  const startOfMonth = moment().startOf("month").format("YYYY-MM-DD HH:mm:ss");
  const endOfMonth = moment().endOf("month").format("YYYY-MM-DD HH:mm:ss");
  // const query =
  //   "Select any_value(SUM(T.amount)) as totalIncome, any_value(MONTH(T.collectionDate)) as month, any_value(YEAR(T.collectionDate)) as year from Transactions as T where T.propId=? and T.status=? and T.type != ? and T.amount > 0 and MONTH(T.collectionDate)=month(curdate()) and YEAR(T.collectionDate)=YEAR(curdate()) group by month";
  //const data = [propId, status, CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED];
  const query =
    "Select any_value(SUM(T.amount)) as totalIncome, any_value(MONTH(T.collectionDate)) as month, any_value(YEAR(T.collectionDate)) as year from Transactions as T where T.propId=? and T.status=? and T.type != ? and T.amount > 0 and T.isFinanciallyApplicable = 1 and T.collectionDate BETWEEN ? and ?";
  const data = [propId, status, CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED, startOfMonth, endOfMonth];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.updateReceipt = async ({ id, receipt }: transactionsTypes) => {
  const query = "UPDATE Transactions SET receipt = ? WHERE id = ?";
  const data = [receipt, id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

transactionDB.updateReceiptByGId = async ({ gId, receipt }: transactionsTypes) => {
  const query = "UPDATE Transactions SET receipt = ? WHERE gId = ?";
  const data = [receipt, gId];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

transactionDB.updateGatewayByGId = async ({ gId, gateway }: transactionsTypes) => {
  const query = "UPDATE Transactions SET gateway = ? WHERE gId = ?";
  const data = [gateway, gId];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

transactionDB.getDailyRentForClient = async ({
  clientId,
}: transactionsTypes & { year: any; month: any }) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(amount) as amount from Transactions where clientId = ? and status = ? and type = ? and YEAR(collectionDate) = YEAR(CURDATE()) and MONTH(collectionDate) = MONTH(CURDATE()) and amount > 0 group by date";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailyRentForClientYearMonth = async ({
  clientId,
  year,
  month,
}: transactionsTypes & { year: any; month: any }) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(amount) as amount from Transactions where clientId = ? and status = ? and type = ? and YEAR(collectionDate) = ? and MONTH(collectionDate) = ? and amount > 0 and isFinanciallyApplicable = 1 group by date";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    year,
    month,
    // CONSTANTS.TRANSACTION_FOR.SECURITY,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailyRentForClientYearMonthType = async ({
  clientId,
  year,
  month,
  transactionFor,
}: transactionsTypes & { year: any; month: any }) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(amount) as amount from Transactions where clientId = ? and status = ? and type = ? and transactionFor = ? and YEAR(collectionDate) = ? and MONTH(collectionDate) = ? group by date";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    transactionFor,
    year,
    month,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailyIncomeForClientYearMonthType = async ({
  clientId,
  year,
  month,
}: transactionsTypes & { year: any; month: any }) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(amount) as amount, transactionFor from Transactions where clientId = ? and status = ? and type = ? and transactionFor not in (?, ?) and YEAR(collectionDate) = ? and MONTH(collectionDate) = ? and amount > 0 group by date, transactionFor";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_FOR.RENT,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
    year,
    month,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailyRefundForClientYearMonth = async ({
  clientId,
  year,
  month,
}: transactionsTypes & { year: any; month: any }) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(amount) as amount from Transactions where clientId = ? and status = ? and type = ? and YEAR(collectionDate) = ? and MONTH(collectionDate) = ? and amount < 0 group by date";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    year,
    month,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailySecurityForClient = async ({
  clientId,
}: transactionsTypes & { year: any; month: any }) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(amount) as amount from Transactions where clientId = ? and status = ? and type = ? and transactionFor = ? and YEAR(collectionDate) = YEAR(CURDATE()) and MONTH(collectionDate) = MONTH(CURDATE()) group by date";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailySecurityForClientYearMonth = async ({
  clientId,
  year,
  month,
}: transactionsTypes & { year: any; month: any }) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(amount) as amount from Transactions where clientId = ? and status = ? and type = ? and transactionFor = ? and YEAR(collectionDate) = ? and MONTH(collectionDate) = ? group by date";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
    year,
    month,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailySecurityAdjustedForClientYearMonth = async ({
  clientId,
  year,
  month,
}: transactionsTypes & { year: any; month: any }) => {
  const query =
    "Select DATE_FORMAT(T.collectionDate, '%Y-%m-%d') AS date, SUM(ABS(L.amount)) as amount from Transactions as T join Ledgers as L on L.referenceId = T.ledgerReferenceId where L.amount < 0 and L.subType = ? and T.mode = ? and T.clientId = ? and T.status = ? and T.type = ? and YEAR(T.collectionDate) = ? and MONTH(T.collectionDate) = ? group by date";
  const data = [
    CONSTANTS.LEDGER_SUBTYPES.MARKED_FROM_SECURITY,
    CONSTANTS.TRANSACTION_MODES.ADJUSTED_FROM_SECURITY,
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    year,
    month,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailyOfflineTransactionsForClient = async ({
  clientId,
}: transactionsTypes & { year: any; month: any }) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(amount) as amount from Transactions where clientId = ? and status = ? and type = ? and mode = ? and YEAR(collectionDate) = YEAR(CURDATE()) and MONTH(collectionDate) = MONTH(CURDATE()) and amount > 0 group by date";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailyOfflineTransactionsForClientYearMonth = async ({
  clientId,
  year,
  month,
}: transactionsTypes & { year: any; month: any }) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(amount) as amount from Transactions where clientId = ? and status = ? and type = ? and mode = ? and YEAR(collectionDate) = ? and MONTH(collectionDate) = ? and amount > 0 group by date";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
    year,
    month,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailyOnlineTransactionsForClient = async ({
  clientId,
}: transactionsTypes & { year: any; month: any }) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(amount) as amount from Transactions where clientId = ? and status = ? and type = ? and mode != ? and YEAR(collectionDate) = YEAR(CURDATE()) and MONTH(collectionDate) = MONTH(CURDATE()) and amount > 0 group by date";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailyOnlineTransactionsForClientYearMonth = async ({
  clientId,
  year,
  month,
}: transactionsTypes & { year: any; month: any }) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(amount) as amount from Transactions where clientId = ? and status = ? and type = ? and mode not in (?, ?) and recordedBy not like ? and YEAR(collectionDate) = ? and MONTH(collectionDate) = ? and amount > 0 group by date";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
    CONSTANTS.TRANSACTION_MODES.ADJUSTED_FROM_SECURITY,
    "%Kipinn App%",
    year,
    month,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailyPayuTransactionsForClientYearMonth = async ({
  clientId,
  year,
  month,
}: transactionsTypes & { year: any; month: any }) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(amount) as amount from Transactions where clientId = ? and status = ? and type = ? and mode != ? and recordedBy like ? and YEAR(collectionDate) = ? and MONTH(collectionDate) = ? group by date";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
    "%Kipinn App%",
    year,
    month,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getMonthlyRentForClient = async ({
  clientId,
  status,
}: transactionsTypes) => {
  const query =
    "Select SUM(T.amount) as amount, MONTH(T.collectionDate) as month, YEAR(T.collectionDate) as year from Transactions as T where T.clientId=? and T.status=? and T.type != ? and T.collectionDate >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and T.amount > 0 and T.transactionFor != ? and T.isFinanciallyApplicable = 1 Group By month, year Order by year desc, month desc";
  const data = [
    clientId,
    status,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getMonthlyRefundForClient = async ({
  clientId,
  status,
}: transactionsTypes) => {
  const query =
    "Select SUM(T.amount) as amount, MONTH(T.collectionDate) as month, YEAR(T.collectionDate) as year from Transactions as T where T.clientId=? and T.status=? and T.type != ? and T.collectionDate >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and amount < 0 Group By month, year Order by year desc, month desc";
  const data = [
    clientId,
    status,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getMonthlyRentForClientAndType = async ({
  clientId,
  status,
  transactionFor,
}: transactionsTypes) => {
  const query =
    "Select SUM(T.amount) as amount, MONTH(T.collectionDate) as month, YEAR(T.collectionDate) as year from Transactions as T where T.clientId=? and T.status=? and T.type != ? and transactionFor = ? and T.amount > 0 and T.isFinanciallyApplicable = 1 and T.collectionDate >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) Group By month, year Order by year desc, month desc";
  const data = [
    clientId,
    status,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    transactionFor,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getMonthlyIncomeForClientAndType = async ({
  clientId,
  status,
}: transactionsTypes) => {
  const query =
    "Select SUM(T.amount) as amount, MONTH(T.collectionDate) as month, YEAR(T.collectionDate) as year, transactionFor from Transactions as T where T.clientId=? and T.status=? and T.type != ? and transactionFor not in (?, ?) and amount > 0 and T.collectionDate >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) Group By month, year, transactionFor Order by year desc, month desc";
  const data = [
    clientId,
    status,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.TRANSACTION_FOR.RENT,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getMonthlySecurityForClient = async ({
  clientId,
  status,
}: transactionsTypes) => {
  const query =
    "Select SUM(T.amount) as amount, MONTH(T.collectionDate) as month, YEAR(T.collectionDate) as year from Transactions as T where T.clientId=? and T.status=? and T.type = ? and transactionFor = ? and T.collectionDate >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) Group By month, year Order by year desc, month desc";
  const data = [
    clientId,
    status,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getMonthlySecurityAdjustedForClient = async ({
  clientId,
  status,
}: transactionsTypes) => {
  const query =
    "Select SUM(ABS(L.amount)) as amount, MONTH(T.collectionDate) as month, YEAR(T.collectionDate) as year from Transactions as T join Ledgers as L on L.referenceId = T.ledgerReferenceId where L.amount < 0 and L.subType = ? and T.mode = ? and T.clientId=? and T.status=? and T.type = ? and T.collectionDate >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) Group By month, year Order by year desc, month desc";
  const data = [
    CONSTANTS.LEDGER_SUBTYPES.MARKED_FROM_SECURITY,
    CONSTANTS.TRANSACTION_MODES.ADJUSTED_FROM_SECURITY,
    clientId,
    status,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

// Amount not transaction records itself
transactionDB.getMonthlyOfflineTransactionsForClient = async ({
  clientId,
}: transactionsTypes & { year: any; month: any }) => {
  const query =
    "Select SUM(amount) as amount, MONTH(collectionDate) as month, YEAR(collectionDate) as year from Transactions where clientId = ? and status = ? and type = ? and mode = ? and collectionDate >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and amount > 0 and isFinanciallyApplicable = 1 Group By month, year Order by year desc, month desc";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getMonthlyOnlineTransactionsForClient = async ({
  clientId,
}: transactionsTypes & { year: any; month: any }) => {
  const query =
    "Select SUM(amount) as amount, MONTH(collectionDate) as month, YEAR(collectionDate) as year from Transactions where clientId = ? and status = ? and type = ? and mode not in (?, ?) and recordedBy not like ? and collectionDate >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and amount > 0 and isFinanciallyApplicable = 1 Group By month, year Order by year desc, month desc";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
    CONSTANTS.TRANSACTION_MODES.ADJUSTED_FROM_SECURITY,
    "%Kipinn App%",
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getMonthlyPayuTransactionsForClient = async ({
  clientId,
}: transactionsTypes & { year: any; month: any }) => {
  const query =
    "Select SUM(amount) as amount, MONTH(collectionDate) as month, YEAR(collectionDate) as year from Transactions where clientId = ? and status = ? and type = ? and mode != ? and recordedBy like ? and collectionDate >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and isFinanciallyApplicable = 1 Group By month, year Order by year desc, month desc";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
    "%Kipinn App%",
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

// transaction records
transactionDB.curMonthSecurityForProp = async ({
  propId,
}: transactionsTypes) => {
  const query =
    "Select T.id, T.docs, T.gateway, T.gId, T.amount, T.collectionDate, T.title, T.tenantId, T.receipt, T.remarks, T.name, T.type, T.transactionFor, T.status, T.recordedBy, T.paymentAccountNo, T.paymentAccountName, T.dueDate, T.mode, T.collectionDate as createdAt, T.updatedAt, TE.name as tenantName, T.propName, T.roomNum from Transactions as T left join Tenants as TE on TE.id = T.tenantId where T.propId = ? and T.status = ? and T.type = ? and T.transactionFor = ? and Month(T.collectionDate) = Month(CURDATE()) and Year(T.collectionDate) = Year(CURDATE()) order by T.collectionDate desc, T.id desc";
  const data = [
    propId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.curMonthTransactionForProp = async ({
  propId,
}: transactionsTypes) => {
  const query =
    "Select T.id, T.docs, T.gateway, T.gId, T.amount, T.collectionDate, T.title, T.tenantId, T.receipt, T.remarks, T.name, T.type, T.transactionFor, T.status, T.recordedBy, T.paymentAccountNo, T.paymentAccountName, T.dueDate, T.mode, T.collectionDate as createdAt, T.updatedAt, TE.name as tenantName, T.propName, T.roomNum from Transactions as T left join Tenants as TE on TE.id = T.tenantId where T.propId = ? and T.status = ? and T.type != ? and Month(T.collectionDate) = Month(CURDATE()) and Year(T.collectionDate) = Year(CURDATE()) order by T.collectionDate desc, T.id desc";
  const data = [
    propId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.curMonthOfflineTransactionsForProp = async ({
  propId,
}: transactionsTypes & { year: any; month: any }) => {
  const query =
    "Select T.id, T.docs, T.gateway, T.gId, T.amount, T.tenantId, T.title, T.collectionDate, T.receipt, T.remarks, T.name, T.type, T.transactionFor, T.status, T.recordedBy, T.paymentAccountNo, T.paymentAccountName, T.dueDate, T.mode, T.collectionDate as createdAt, T.updatedAt, TE.name as tenantName, T.propName, T.roomNum from Transactions as T left join Tenants as TE on TE.id = T.tenantId where T.propId = ? and T.status = ? and T.type = ? and T.mode = ? and Month(T.collectionDate) = Month(CURDATE()) and Year(T.collectionDate) = Year(CURDATE()) order by T.collectionDate desc, T.id desc";
  const data = [
    propId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.curMonthOnlineTransactionsForProp = async ({
  propId,
}: transactionsTypes & { year: any; month: any }) => {
  const query =
    "Select T.id, T.docs, T.gateway, T.gId, T.amount, T.collectionDate, T.title, T.receipt, T.tenantId, T.remarks, T.name, T.type, T.transactionFor, T.status, T.recordedBy, T.paymentAccountNo, T.paymentAccountName, T.dueDate, T.mode, T.collectionDate as createdAt, T.updatedAt, TE.name as tenantName, T.propName, T.roomNum from Transactions as T left join Tenants as TE on TE.id = T.tenantId where T.propId = ? and T.status = ? and T.type = ? and T.mode != ? and Month(T.collectionDate) = Month(CURDATE()) and Year(T.collectionDate) = Year(CURDATE()) order by T.collectionDate desc, T.id desc";
  const data = [
    propId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

/***************** For Staff ****************/

transactionDB.getMonthlyRentForStaff = async ({
  clientId,
  status,
  propertiesIds,
}: transactionsTypes & { clientId: any; propertiesIds: string }) => {
  const query =
    "Select SUM(T.amount) as amount, MONTH(T.collectionDate) as month, YEAR(T.collectionDate) as year from Transactions as T where T.clientId=? and T.status=? and T.type != ? and T.propId in (" +
    `${propertiesIds}` +
    ") and T.collectionDate >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and T.amount > 0 and T.transactionFor != ? and T.isFinanciallyApplicable = 1 Group By month, year Order by year desc, month desc";
  const data = [
    clientId,
    status,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getMonthlyRefundForStaff = async ({
  clientId,
  status,
  propertiesIds,
}: transactionsTypes & { clientId: any; propertiesIds: string }) => {
  const query =
    "Select SUM(T.amount) as amount, MONTH(T.collectionDate) as month, YEAR(T.collectionDate) as year from Transactions as T where T.clientId=? and T.status=? and T.type != ? and T.propId in (" +
    `${propertiesIds}` +
    ") and T.collectionDate >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and amount < 0 Group By month, year Order by year desc, month desc";
  const data = [
    clientId,
    status,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getMonthlyRentForStaffAndType = async ({
  clientId,
  status,
  transactionFor,
  propertiesIds,
}: transactionsTypes & { clientId: any; propertiesIds: string }) => {
  const query =
    "Select SUM(T.amount) as amount, MONTH(T.collectionDate) as month, YEAR(T.collectionDate) as year from Transactions as T where T.clientId=? and T.status=? and T.type != ? and T.propId in (" +
    `${propertiesIds}` +
    ") and transactionFor = ? and amount > 0 and T.isFinanciallyApplicable = 1 and T.collectionDate >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) Group By month, year Order by year desc, month desc";
  const data = [
    clientId,
    status,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    transactionFor,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getMonthlyIncomeForStaffAndType = async ({
  clientId,
  status,
  propertiesIds,
}: transactionsTypes & { clientId: any; propertiesIds: string }) => {
  const query =
    "Select SUM(T.amount) as amount, MONTH(T.collectionDate) as month, YEAR(T.collectionDate) as year, T.transactionFor as transactionFor from Transactions as T where T.clientId=? and T.status=? and T.type != ? and amount > 0 and T.propId in (" +
    `${propertiesIds}` +
    ") and transactionFor not in (?, ?) and T.collectionDate >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) Group By month, year, T.transactionFor Order by year desc, month desc";
  const data = [
    clientId,
    status,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.TRANSACTION_FOR.RENT,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getMonthlySecurityForStaff = async ({
  clientId,
  status,
  propertiesIds,
}: transactionsTypes & { clientId: any; propertiesIds: string }) => {
  const query =
    "Select SUM(T.amount) as amount, MONTH(T.collectionDate) as month, YEAR(T.collectionDate) as year from Transactions as T where T.clientId=? and T.status=? and T.type = ? and T.propId in (" +
    `${propertiesIds}` +
    ") and transactionFor = ? and T.collectionDate >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) Group By month, year Order by year desc, month desc";
  const data = [
    clientId,
    status,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getMonthlySecurityAdjustedForStaff = async ({
  clientId,
  status,
  propertiesIds,
}: transactionsTypes & { clientId: any; propertiesIds: string }) => {
  const query =
    "Select SUM(ABS(L.amount)) as amount, MONTH(T.collectionDate) as month, YEAR(T.collectionDate) as year from Transactions as T join Ledgers as L on L.referenceId = T.ledgerReferenceId where L.amount < 0 and L.subType = ? and T.mode = ? and T.clientId=? and T.status=? and T.type = ? and T.propId in (" +
    `${propertiesIds}` +
    ") and T.collectionDate >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) Group By month, year Order by year desc, month desc";
  const data = [
    CONSTANTS.LEDGER_SUBTYPES.MARKED_FROM_SECURITY,
    CONSTANTS.TRANSACTION_MODES.ADJUSTED_FROM_SECURITY,
    clientId,
    status,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getMonthlyOfflineTransactionsForStaff = async ({
  clientId,
  propertiesIds,
}: transactionsTypes & { clientId: any; propertiesIds: string }) => {
  const query =
    "Select SUM(amount) as amount, MONTH(collectionDate) as month, YEAR(collectionDate) as year from Transactions where clientId = ? and status = ? and type = ? and mode = ? and propId in (" +
    `${propertiesIds}` +
    ") and collectionDate >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and amount > 0 and isFinanciallyApplicable = 1 Group By month, year Order by year desc, month desc";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getMonthlyOnlineTransactionsForStaff = async ({
  clientId,
  propertiesIds,
}: transactionsTypes & { clientId: any; propertiesIds: string }) => {
  const query =
    "Select SUM(amount) as amount, MONTH(collectionDate) as month, YEAR(collectionDate) as year from Transactions where clientId = ? and status = ? and type = ? and mode not in (?, ?) and recordedBy not like ? and propId in (" +
    `${propertiesIds}` +
    ") and collectionDate >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and amount > 0 and isFinanciallyApplicable = 1 Group By month, year Order by year desc, month desc";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
    CONSTANTS.TRANSACTION_MODES.ADJUSTED_FROM_SECURITY,
    "%Kipinn App%",
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getMonthlyPayuTransactionsForStaff = async ({
  clientId,
  propertiesIds,
}: transactionsTypes & { clientId: any; propertiesIds: string }) => {
  const query =
    "Select SUM(amount) as amount, MONTH(collectionDate) as month, YEAR(collectionDate) as year from Transactions where clientId = ? and status = ? and type = ? and mode != ? and recordedBy like ? and propId in (" +
    `${propertiesIds}` +
    ") and collectionDate >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and isFinanciallyApplicable = 1 Group By month, year Order by year desc, month desc";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
    "%Kipinn App%",
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailyRentForStaff = async ({
  clientId,
  propertiesIds,
}: transactionsTypes & { clientId: any; propertiesIds: string }) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(amount) as amount from Transactions where clientId = ? and status = ? and type = ? and propId in (" +
    `${propertiesIds}` +
    ") and YEAR(collectionDate) = YEAR(CURDATE()) and MONTH(collectionDate) = MONTH(CURDATE()) and amount > 0 group by date";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailyRentForStaffYearMonth = async ({
  clientId,
  propertiesIds,
  year,
  month,
}: transactionsTypes & {
  clientId: any;
  propertiesIds: string;
  year: any;
  month: any;
}) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(amount) as amount from Transactions where clientId = ? and status = ? and type = ? and propId in (" +
    `${propertiesIds}` +
    ") and YEAR(collectionDate) = ? and MONTH(collectionDate) = ? and amount > 0 and transactionFor != ? group by date";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    year,
    month,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailyRefundForStaffYearMonth = async ({
  clientId,
  propertiesIds,
  year,
  month,
}: transactionsTypes & {
  clientId: any;
  propertiesIds: string;
  year: any;
  month: any;
}) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(amount) as amount from Transactions where clientId = ? and status = ? and type = ? and propId in (" +
    `${propertiesIds}` +
    ") and YEAR(collectionDate) = ? and MONTH(collectionDate) = ? and amount < 0 group by date";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    year,
    month,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailyRentForStaffYearMonthType = async ({
  clientId,
  propertiesIds,
  year,
  month,
  transactionFor,
}: transactionsTypes & {
  clientId: any;
  propertiesIds: string;
  year: any;
  month: any;
}) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(amount) as amount from Transactions where clientId = ? and status = ? and type = ? and propId in (" +
    `${propertiesIds}` +
    ") and transactionFor = ? and YEAR(collectionDate) = ? and MONTH(collectionDate) = ? group by date";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    transactionFor,
    year,
    month,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailyIncomeForStaffYearMonthType = async ({
  clientId,
  propertiesIds,
  year,
  month,
}: transactionsTypes & {
  clientId: any;
  propertiesIds: string;
  year: any;
  month: any;
}) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(amount) as amount, transactionFor from Transactions where clientId = ? and status = ? and type = ? and propId in (" +
    `${propertiesIds}` +
    ") and transactionFor not in (?, ?) and amount > 0 and YEAR(collectionDate) = ? and MONTH(collectionDate) = ? group by date, transactionFor";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_FOR.RENT,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
    year,
    month,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailySecurityForStaff = async ({
  clientId,
  propertiesIds,
}: transactionsTypes & { clientId: any; propertiesIds: string }) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(amount) as amount from Transactions where clientId = ? and status = ? and type = ? and propId in (" +
    `${propertiesIds}` +
    ") and transactionFor = ? and YEAR(collectionDate) = YEAR(CURDATE()) and MONTH(collectionDate) = MONTH(CURDATE()) group by date";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailySecurityForStaffYearMonth = async ({
  clientId,
  propertiesIds,
  year,
  month,
}: transactionsTypes & {
  clientId: any;
  propertiesIds: string;
  year: any;
  month: any;
}) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(amount) as amount from Transactions where clientId = ? and status = ? and type = ? and propId in (" +
    `${propertiesIds}` +
    ") and transactionFor = ? and YEAR(collectionDate) = ? and MONTH(collectionDate) = ? group by date";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
    year,
    month,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailySecurityAdjustedForStaffYearMonth = async ({
  clientId,
  propertiesIds,
  year,
  month,
}: transactionsTypes & {
  clientId: any;
  propertiesIds: string;
  year: any;
  month: any;
}) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(ABS(L.amount)) as amount from Transactions as T join Ledgers as L on L.referenceId = T.ledgerReferenceId where L.amount < 0 and L.subType = ? and T.mode = ? and T.clientId = ? and T.status = ? and T.type = ? and T.propId in (" +
    `${propertiesIds}` +
    ") and YEAR(T.collectionDate) = ? and MONTH(T.collectionDate) = ? group by date";
  const data = [
    CONSTANTS.LEDGER_SUBTYPES.MARKED_FROM_SECURITY,
    CONSTANTS.TRANSACTION_MODES.ADJUSTED_FROM_SECURITY,
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    year,
    month,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailyOfflineTransactionsForStaff = async ({
  clientId,
  propertiesIds,
}: transactionsTypes & { clientId: any; propertiesIds: string }) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(amount) as amount from Transactions where clientId = ? and status = ? and type = ? and propId in (" +
    `${propertiesIds}` +
    ") and mode = ? and YEAR(collectionDate) = YEAR(CURDATE()) and MONTH(collectionDate) = MONTH(CURDATE()) and amount > 0 group by date";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailyOfflineTransactionsForStaffYearMonth = async ({
  clientId,
  propertiesIds,
  year,
  month,
}: transactionsTypes & {
  clientId: any;
  propertiesIds: string;
  year: any;
  month: any;
}) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(amount) as amount from Transactions where clientId = ? and status = ? and type = ? and propId in (" +
    `${propertiesIds}` +
    ") and mode = ? and YEAR(collectionDate) = ? and MONTH(collectionDate) = ? and amount > 0 group by date";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
    year,
    month,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailyOnlineTransactionsForStaff = async ({
  clientId,
  propertiesIds,
}: transactionsTypes & { clientId: any; propertiesIds: string }) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(amount) as amount from Transactions where clientId = ? and status = ? and type = ? and mode != ? and propId in (" +
    `${propertiesIds}` +
    ") and YEAR(collectionDate) = YEAR(CURDATE()) and MONTH(collectionDate) = MONTH(CURDATE()) and amount > 0 group by date";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailyOnlineTransactionsForStaffYearMonth = async ({
  clientId,
  propertiesIds,
  year,
  month,
}: transactionsTypes & {
  clientId: any;
  propertiesIds: string;
  year: any;
  month: any;
}) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(amount) as amount from Transactions where clientId = ? and status = ? and type = ? and mode not in (?, ?) and recordedBy not like ? and propId in (" +
    `${propertiesIds}` +
    ") and YEAR(collectionDate) = ? and MONTH(collectionDate) = ? and amount > 0 group by date";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
    CONSTANTS.TRANSACTION_MODES.ADJUSTED_FROM_SECURITY,
    "%Kipinn App%",
    year,
    month,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailyPayuTransactionsForStaffYearMonth = async ({
  clientId,
  propertiesIds,
  year,
  month,
}: transactionsTypes & {
  clientId: any;
  propertiesIds: string;
  year: any;
  month: any;
}) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(amount) as amount from Transactions where clientId = ? and status = ? and type = ? and mode != ? and recordedBy like ? and propId in (" +
    `${propertiesIds}` +
    ") and YEAR(collectionDate) = ? and MONTH(collectionDate) = ? group by date";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
    "%Kipinn App%",
    year,
    month,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

/***************** For Property ****************/

transactionDB.getMonthlyRentByPropId = async ({
  propId,
  status,
}: {
  propId: any;
  status: any;
}) => {
  const query =
    "Select SUM(T.amount) as amount, MONTH(T.collectionDate) as month, YEAR(T.collectionDate) as year from Transactions as T where T.propId=? and T.status=? and T.type != ? and T.collectionDate >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and T.amount > 0 and T.transactionFor != ? Group By month, year Order by year desc, month desc";
  const data = [propId, status, CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED, CONSTANTS.TRANSACTION_FOR.SECURITY];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getMonthlyRefundByPropId = async ({
  propId,
  status,
}: {
  propId: any;
  status: any;
}) => {
  const query =
    "Select SUM(T.amount) as amount, MONTH(T.collectionDate) as month, YEAR(T.collectionDate) as year from Transactions as T where T.propId=? and T.status=? and T.type != ? and T.collectionDate >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and amount < 0 Group By month, year Order by year desc, month desc";
  const data = [propId, status, CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getMonthlyRentByPropIdAndType = async ({
  propId,
  status,
  transactionFor,
}: {
  propId: any;
  status: any;
  transactionFor: any;
}) => {
  const query =
    "Select SUM(T.amount) as amount, MONTH(T.collectionDate) as month, YEAR(T.collectionDate) as year from Transactions as T where T.propId=? and T.status=? and T.type != ? and transactionFor = ? and T.collectionDate >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) Group By month, year Order by year desc, month desc";
  const data = [propId, status, CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED, transactionFor];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getMonthlyIncomeByPropIdAndType = async ({
  propId,
  status,
}: {
  propId: any;
  status: any;
  transactionFor: any;
}) => {
  const query =
    "Select SUM(T.amount) as amount, MONTH(T.collectionDate) as month, YEAR(T.collectionDate) as year, T.transactionFor as transactionFor from Transactions as T where T.propId=? and T.status=? and T.type != ? and amount > 0 and transactionFor not in (?, ?) and T.collectionDate >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) Group By month, year, T.transactionFor Order by year desc, month desc";
  const data = [
    propId,
    status,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.TRANSACTION_FOR.RENT,
    CONSTANTS.TRANSACTION_FOR.SECURITY
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getMonthlySecurityByPropId = async ({
  propId,
  status,
}: {
  propId: any;
  status: any;
}) => {
  const query =
    "Select SUM(T.amount) as amount, MONTH(T.collectionDate) as month, YEAR(T.collectionDate) as year from Transactions as T where T.propId=? and T.status=? and T.type = ? and T.transactionFor = ? and T.collectionDate >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) Group By month, year Order by year desc, month desc";
  const data = [
    propId,
    status,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getMonthlySecurityAdjustedByPropId = async ({
  propId,
  status,
}: {
  propId: any;
  status: any;
}) => {
  const query =
    "Select SUM(ABS(L.amount)) as amount, MONTH(T.collectionDate) as month, YEAR(T.collectionDate) as year from Transactions as T join Ledgers as L on L.referenceId = T.ledgerReferenceId where L.amount < 0 and L.subType = ? and T.mode = ? and T.propId=? and T.status=? and T.type = ? and T.collectionDate >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) Group By month, year Order by year desc, month desc";
  const data = [
    CONSTANTS.LEDGER_SUBTYPES.MARKED_FROM_SECURITY,
    CONSTANTS.TRANSACTION_MODES.ADJUSTED_FROM_SECURITY,
    propId,
    status,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getMonthlyOfflineTransactionsByPropId = async ({
  propId,
  status,
}: {
  propId: any;
  status: any;
}) => {
  const query =
    "Select SUM(amount) as amount, MONTH(collectionDate) as month, YEAR(collectionDate) as year from Transactions where propId = ? and status = ? and type = ? and mode = ? and collectionDate >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and amount > 0 Group By month, year Order by year desc, month desc";
  const data = [
    propId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getMonthlyOnlineTransactionsByPropId = async ({
  propId,
  status,
}: {
  propId: any;
  status: any;
}) => {
  const query =
    "Select SUM(amount) as amount, MONTH(collectionDate) as month, YEAR(collectionDate) as year from Transactions where propId = ? and status = ? and type = ? and mode not in (?, ?) and recordedBy not like ? and collectionDate >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) and amount > 0 Group By month, year Order by year desc, month desc";
  const data = [
    propId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
    CONSTANTS.TRANSACTION_MODES.ADJUSTED_FROM_SECURITY,
    "%Kipinn App%",
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getMonthlyPayuTransactionsByPropId = async ({
  propId,
  status,
}: {
  propId: any;
  status: any;
}) => {
  const query =
    "Select SUM(amount) as amount, MONTH(collectionDate) as month, YEAR(collectionDate) as year from Transactions where propId = ? and status = ? and type = ? and mode != ? and recordedBy like ? and collectionDate >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH) Group By month, year Order by year desc, month desc";
  const data = [
    propId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
    "%Kipinn App%",
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailyRentByPropId = async ({
  propId,
  status,
}: {
  propId: any;
  status: any;
}) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(amount) as amount from Transactions where propId = ? and status = ? and type = ? and YEAR(collectionDate) = YEAR(CURDATE()) and MONTH(collectionDate) = MONTH(CURDATE()) group by date";
  const data = [
    propId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailyRentByPropIdYearMonth = async ({
  propId,
  year,
  month,
}: {
  propId: any;
  year: any;
  month: any;
}) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(amount) as amount from Transactions where propId = ? and status = ? and type = ? and YEAR(collectionDate) = ? and MONTH(collectionDate) = ? and amount > 0 and transactionFor != ? group by date";
  const data = [
    propId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    year,
    month,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailyRefundByPropIdYearMonth = async ({
  propId,
  year,
  month,
}: {
  propId: any;
  year: any;
  month: any;
}) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(amount) as amount from Transactions where propId = ? and status = ? and type = ? and YEAR(collectionDate) = ? and MONTH(collectionDate) = ? and amount < 0 group by date";
  const data = [
    propId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    year,
    month,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailyRentByPropIdYearMonthType = async ({
  propId,
  year,
  month,
  transactionFor,
}: {
  propId: any;
  year: any;
  month: any;
  transactionFor: any;
}) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(amount) as amount from Transactions where propId = ? and status = ? and type = ? and transactionFor = ? and YEAR(collectionDate) = ? and MONTH(collectionDate) = ? group by date";
  const data = [
    propId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    transactionFor,
    year,
    month,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailyIncomeByPropIdYearMonthType = async ({
  propId,
  year,
  month,
}: {
  propId: any;
  year: any;
  month: any;
  transactionFor: any;
}) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(amount) as amount, transactionFor from Transactions where propId = ? and status = ? and type = ? and transactionFor not in (?, ?) and YEAR(collectionDate) = ? and amount > 0 and MONTH(collectionDate) = ? group by date, transactionFor";
  const data = [
    propId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_FOR.RENT,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
    year,
    month,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailySecurityByPropId = async ({
  propId,
  status,
}: {
  propId: any;
  status: any;
}) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(amount) as amount from Transactions where propId = ? and status = ? and type = ? and transactionFor = ? and YEAR(collectionDate) = YEAR(CURDATE()) and MONTH(collectionDate) = MONTH(CURDATE()) group by date";
  const data = [
    propId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailySecurityByPropIdYearMonth = async ({
  propId,
  year,
  month,
}: {
  propId: any;
  year: any;
  month: any;
}) => {
  const query =
    "Select DATE_FORMAT(T.collectionDate, '%Y-%m-%d') AS date, SUM(T.amount) as amount from Transactions as T where T.propId = ? and T.status = ? and T.type = ? and T.transactionFor = ? and YEAR(T.collectionDate) = ? and MONTH(T.collectionDate) = ? group by date";
  const data = [
    propId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
    year,
    month,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailySecurityAdjustedByPropIdYearMonth = async ({
  propId,
  year,
  month,
}: {
  propId: any;
  year: any;
  month: any;
}) => {
  const query =
    "Select DATE_FORMAT(T.collectionDate, '%Y-%m-%d') AS date, SUM(ABS(L.amount)) as amount from Transactions as T join Ledgers as L on L.referenceId = T.ledgerReferenceId where L.amount < 0 and L.subType = ? and T.mode = ? and T.propId = ? and T.status = ? and T.type = ? and YEAR(T.collectionDate) = ? and MONTH(T.collectionDate) = ? group by date";
  const data = [
    CONSTANTS.LEDGER_SUBTYPES.MARKED_FROM_SECURITY,
    CONSTANTS.TRANSACTION_MODES.ADJUSTED_FROM_SECURITY,
    propId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    year,
    month,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailyOfflineTransactionsByPropId = async ({
  propId,
  status,
}: {
  propId: any;
  status: any;
}) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(amount) as amount from Transactions where propId = ? and status = ? and type = ? and mode = ? and YEAR(collectionDate) = YEAR(CURDATE()) and MONTH(collectionDate) = MONTH(CURDATE()) group by date";
  const data = [
    propId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailyOfflineTransactionsByPropIdYearMonth = async ({
  propId,
  year,
  month,
}: {
  propId: any;
  year: any;
  month: any;
}) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(amount) as amount from Transactions where propId = ? and status = ? and type = ? and mode = ? and YEAR(collectionDate) = ? and MONTH(collectionDate) = ? and amount > 0 group by date";
  const data = [
    propId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
    year,
    month,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailyOnlineTransactionsByPropId = async ({
  propId,
  status,
}: {
  propId: any;
  status: any;
}) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(amount) as amount from Transactions where propId = ? and status = ? and type = ? and mode != ? and YEAR(collectionDate) = YEAR(CURDATE()) and MONTH(collectionDate) = MONTH(CURDATE()) group by date";
  const data = [
    propId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailyOnlineTransactionsByPropIdYearMonth = async ({
  propId,
  year,
  month,
}: {
  propId: any;
  year: any;
  month: any;
}) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(amount) as amount from Transactions where propId = ? and status = ? and type = ? and mode not in (?, ?) and recordedBy not like ? and YEAR(collectionDate) = ? and MONTH(collectionDate) = ? and amount > 0 group by date";
  const data = [
    propId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
    CONSTANTS.TRANSACTION_MODES.ADJUSTED_FROM_SECURITY,
    "%Kipinn App%",
    year,
    month,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getDailyPayuTransactionsByPropIdYearMonth = async ({
  propId,
  year,
  month,
}: {
  propId: any;
  year: any;
  month: any;
}) => {
  const query =
    "Select DATE_FORMAT(collectionDate, '%Y-%m-%d') AS date, SUM(amount) as amount from Transactions where propId = ? and status = ? and type = ? and mode != ? and recordedBy like ? and YEAR(collectionDate) = ? and MONTH(collectionDate) = ? group by date";
  const data = [
    propId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
    "%Kipinn App%",
    year,
    month,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getByPropIdandDateRange = async ({
  propId,
  startDate,
  endDate,
}: transactionsTypes & { startDate: string; endDate: string }) => {
  const query =
    "Select T.id, T.tenantId, T.docs, T.gateway, if(T.splitTnxId is NULL, T.gId, T.splitTnxId) as gId, T.title, T.remarks, T.amount, T.gstCharges, T.gstNo, T.invoiceNo, T.collectionDate, T.receipt, T.name, T.roomId, T.type, T.transactionFor, T.status, T.recordedBy, T.dueDate, T.mode, T.collectionDate as createdAt, T.updatedAt, TE.name as tenantName, TE.mobile as tenantMobile, T.propName, T.roomNum, T.tenantId, T.holdAmount, T.discount, T.paymentAccountNo, T.paymentAccountName, T.settleStatus, T.utrNo, T.bankRefNum, T.settledOn from Transactions as T left join Tenants as TE on TE.id = T.tenantId where T.propId = ? and date(T.collectionDate) BETWEEN ? and ? order by T.collectionDate desc, T.id desc";
  const data = [propId, startDate, endDate];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

transactionDB.getByPropIdsandDateRangeForReport = async ({
  propId,
  startDate,
  endDate,
}: { propId: any; startDate: string; endDate: string }) => {
  const query =
    `Select T.id, T.tenantId, T.gateway, T.docs, if(T.splitTnxId is NULL, T.gId, T.splitTnxId) as gId, T.gateway, T.title, T.remarks, T.amount, T.gstCharges, T.gstNo, T.invoiceNo, T.collectionDate, T.receipt, T.name, T.roomId, T.type, T.transactionFor, T.status, T.recordedBy, T.dueDate, T.mode, T.collectionDate as createdAt, T.updatedAt, TE.name as tenantName, TE.mobile as tenantMobile, T.propName, T.roomNum, T.tenantId, T.holdAmount, T.discount, (Select bankName from BankAccounts where accountNum = T.paymentAccountNo) as bankName, T.paymentAccountNo, T.paymentAccountName, T.settleStatus, T.utrNo, T.bankRefNum, T.settledOn, T.ledgerReferenceId from Transactions as T left join Tenants as TE on TE.id = T.tenantId where T.propId in (${propId.map(() => '?').join(',')}) and date(T.collectionDate) BETWEEN ? and ? order by T.collectionDate asc, T.id asc`;
  const data = [...propId, startDate, endDate];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

transactionDB.getByPropIdsandDateRange = async ({
  propId,
  startDate,
  endDate,
}: { propId: any; startDate: string; endDate: string }) => {
  const query =
    `Select T.id, T.tenantId, T.gateway, T.docs, if(T.splitTnxId is NULL, T.gId, T.splitTnxId) as gId, T.gateway, T.title, T.remarks, T.amount, T.gstCharges, T.gstNo, T.invoiceNo, T.collectionDate, T.receipt, T.name, T.roomId, T.type, T.transactionFor, T.status, T.recordedBy, T.dueDate, T.mode, T.collectionDate as createdAt, T.updatedAt, TE.name as tenantName, TE.mobile as tenantMobile, T.propName, T.roomNum, T.tenantId, T.holdAmount, T.discount, T.paymentAccountNo, T.paymentAccountName, T.settleStatus, T.utrNo, T.bankRefNum, T.settledOn, T.ledgerReferenceId from Transactions as T left join Tenants as TE on TE.id = T.tenantId where T.propId in (${propId.map(() => '?').join(',')}) and date(T.collectionDate) BETWEEN ? and ? order by T.collectionDate asc, T.id asc`;
  const data = [...propId, startDate, endDate];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

transactionDB.getByPropIdandDateRangeForApp = async ({
  propId,
  startDate,
  endDate,
  limit,
  pageNum,
}: transactionsTypes & {
  startDate: string;
  endDate: string;
  limit: number;
  pageNum: number;
}) => {
  const query =
    "Select T.id, T.docs, T.gateway, if(T.splitTnxId is NULL, T.gId, T.splitTnxId) as gId, T.title, T.amount, T.remarks, T.collectionDate, T.receipt, T.name, T.type, T.transactionFor, T.status, T.recordedBy, T.dueDate, T.mode, T.collectionDate as createdAt, T.updatedAt, TE.name as tenantName, T.propName, T.roomNum, T.tenantId from Transactions as T left join Tenants as TE on TE.id = T.tenantId where T.propId = ? and date(T.collectionDate) BETWEEN ? and ? order by T.collectionDate desc, T.id desc LIMIT ?,?";

  const offset = (pageNum - 1) * limit;
  const data = [propId, startDate, endDate, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

transactionDB.getByClientIdandDateRange = async ({
  clientId,
  startDate,
  endDate,
  limit,
  pageNum,
}: transactionsTypes & {
  startDate: string;
  endDate: string;
  limit: number;
  pageNum: number;
}) => {
  const query =
    "Select T.id, T.docs, T.gateway, if(T.splitTnxId is NULL, T.gId, T.splitTnxId) as gId, T.title, T.amount, T.remarks, T.collectionDate, T.receipt, T.name, T.type, T.transactionFor, T.status, T.recordedBy, T.dueDate, T.mode, T.collectionDate as createdAt, T.updatedAt, TE.name as tenantName, TE.mobile as tenantMobile, (select value from Documents where tenantId = T.tenantId and clientId = T.clientId and type = 4 and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = T.tenantId and clientId = T.clientId order by id desc limit 1) as kycStatus, T.propName, T.roomNum, T.tenantId from Transactions as T left join Tenants as TE on TE.id = T.tenantId where T.clientId = ? and T.status = ? and T.type != ? and date(T.collectionDate) BETWEEN ? and ? order by T.collectionDate desc, T.id desc LIMIT ?,?";
  const offset = (pageNum - 1) * limit;
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    startDate,
    endDate,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

transactionDB.getByClientIdandDateRangeForReport = async ({
  clientId,
  startDate,
  endDate,
}: transactionsTypes & {
  startDate: string;
  endDate: string;
}) => {
  const query =
    "Select T.id, T.docs, T.gateway, if(T.splitTnxId is NULL, T.gId, T.splitTnxId) as gId, T.gateway, T.title, T.amount, T.remarks, T.gstCharges, T.gstNo, T.invoiceNo, T.collectionDate, T.receipt, T.name, T.roomId, T.type, T.transactionFor, T.status, T.recordedBy, T.dueDate, T.mode, T.collectionDate as createdAt, T.updatedAt, TE.name as tenantName, TE.mobile as tenantMobile, T.propName, T.roomNum, T.tenantId, T.holdAmount, T.discount, (Select bankName from BankAccounts where accountNum = T.paymentAccountNo) as bankName, T.paymentAccountNo, T.paymentAccountName, T.settleStatus, T.utrNo, T.bankRefNum, T.settledOn, T.ledgerReferenceId from Transactions as T left join Tenants as TE on TE.id = T.tenantId left join Properties as P on T.propId = P.id where T.clientId = ? and T.status = ? and T.type != ? and P.status = ? and date(T.collectionDate) BETWEEN ? and ? order by T.collectionDate asc, T.id asc";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

transactionDB.getByClientIdandDateRangeForWeb = async ({
  clientId,
  startDate,
  endDate,
}: transactionsTypes & {
  startDate: string;
  endDate: string;
}) => {
  const query =
    "Select T.id, T.docs, T.gateway, if(T.splitTnxId is NULL, T.gId, T.splitTnxId) as gId, T.gateway, T.title, T.amount, T.remarks, T.gstCharges, T.gstNo, T.invoiceNo, T.collectionDate, T.receipt, T.name, T.roomId, T.type, T.transactionFor, T.status, T.recordedBy, T.dueDate, T.mode, T.collectionDate as createdAt, T.updatedAt, TE.name as tenantName, TE.mobile as tenantMobile, T.propName, T.roomNum, T.tenantId, T.holdAmount, T.discount, T.paymentAccountNo, T.paymentAccountName, T.settleStatus, T.utrNo, T.bankRefNum, T.settledOn, T.ledgerReferenceId from Transactions as T left join Tenants as TE on TE.id = T.tenantId left join Properties as P on T.propId = P.id where T.clientId = ? and T.status = ? and T.type != ? and P.status = ? and date(T.collectionDate) BETWEEN ? and ? order by T.collectionDate asc, T.id asc";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

transactionDB.getByClientIdandDateRangeForWebStaff = async ({
  clientId,
  startDate,
  endDate,
  propertiesIds,
}: transactionsTypes & {
  startDate: string;
  endDate: string;
  propertiesIds: string;
}) => {
  const query =
    `Select T.id, T.docs, T.gateway, if(T.splitTnxId is NULL, T.gId, T.splitTnxId) as gId, T.gateway, T.title, T.amount, T.remarks, T.gstCharges, T.gstNo, T.invoiceNo, T.collectionDate, T.receipt, T.name, T.roomId, T.type, T.transactionFor, T.status, T.recordedBy, T.dueDate, T.mode, T.collectionDate as createdAt, T.updatedAt, TE.name as tenantName, TE.mobile as tenantMobile, T.propName, T.roomNum, T.tenantId, T.holdAmount, T.discount, T.paymentAccountNo, T.paymentAccountName, T.settleStatus, T.utrNo, T.bankRefNum, T.settledOn, T.ledgerReferenceId from Transactions as T left join Tenants as TE on TE.id = T.tenantId left join Properties as P on T.propId = P.id where T.clientId = ? and T.status = ? and T.type != ? and P.status = ? and date(T.collectionDate) BETWEEN ? and ? and T.propId in (${propertiesIds}) order by T.collectionDate asc, T.id asc`;
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

transactionDB.getRentByPropIdandDateRange = async ({
  propId,
  startDate,
  endDate,
}: transactionsTypes & { startDate: string; endDate: string }) => {
  const query =
    "select sum(amount) as amount from Transactions where propId =? and type != ? and isFinanciallyApplicable = 1 and amount > 0 and DATE(collectionDate) BETWEEN ? and ? ";
  const data = [
    propId,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getRentByClientIdandDateRange = async ({
  clientId,
  startDate,
  endDate,
}: transactionsTypes & { startDate: string; endDate: string }) => {
  const query =
    "select sum(amount) as amount from Transactions where clientId =? and type != ? and isFinanciallyApplicable = 1 and amount > 0 and DATE(collectionDate) BETWEEN ? and ? ";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getRentByClientIdandDateRangeForStaff = async ({
  clientId,
  startDate,
  endDate,
  propertiesIds,
}: transactionsTypes & { startDate: string; endDate: string; propertiesIds: string }) => {
  const query =
    `select sum(amount) as amount from Transactions where clientId =? and type != ? and isFinanciallyApplicable = 1 and amount > 0 and propId in (${propertiesIds}) and DATE(collectionDate) BETWEEN ? and ? `;
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getRentByPropIdandDateRangeWithoutSecurity = async ({
  propId,
  startDate,
  endDate,
}: transactionsTypes & { startDate: string; endDate: string }) => {
  const query =
    "select sum(amount) as amount from Transactions where propId =? and type != ? and transactionFor != ? and amount > 0 and DATE(collectionDate) BETWEEN ? and ? ";
  const data = [
    propId,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getSecurityByPropIdandDateRange = async ({
  propId,
  startDate,
  endDate,
}: transactionsTypes & { startDate: string; endDate: string }) => {
  const query =
    "select sum(amount) as amount from Transactions where propId =? and type != ? and transactionFor = ? and DATE(collectionDate) BETWEEN ? and ? ";
  const data = [
    propId,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getTotalSecurityInHandByDateRangeAndPropId = async ({
  propId,
  startDate,
  endDate,
}: transactionsTypes & { startDate: string; endDate: string }) => {
  const query =
    `Select SUM(T.amount) as amount from Transactions as T where T.propId = ? and date(T.collectionDate) Between ? and ? and T.status = ? and T.type != ? and (T.transactionFor = ? or T.amount < 0)
    UNION
    Select SUM(L.amount) as amount from Ledgers as L join Transactions as T on T.ledgerReferenceId = L.referenceId where L.propId = ? and L.subType = ? and T.mode = ? and L.amount < 0 and Date(T.collectionDate) Between ? and ? 
    `;
  const data = [
    propId,
    startDate,
    endDate,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
    propId,
    CONSTANTS.LEDGER_SUBTYPES.MARKED_FROM_SECURITY,
    CONSTANTS.TRANSACTION_MODES.ADJUSTED_FROM_SECURITY,
    startDate,
    endDate,
  ];

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

  let totalSecurity = Number(rows[0]?.amount) || 0;
  let securityUsed = Number(rows[1]?.amount) || 0;
  let inHandSecurity = totalSecurity + securityUsed;

  if (rows?.length > 0) return inHandSecurity;
  else return 0;
};

transactionDB.getRentCollectionByPropIdandDateRange = async ({
  propId,
  startDate,
  endDate,
}: transactionsTypes & { startDate: string; endDate: string }) => {
  const query =
    "select sum(amount) as amount from Transactions where propId =? and type != ? and transactionFor = ? and DATE(collectionDate) BETWEEN ? and ? ";
  const data = [
    propId,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.TRANSACTION_FOR.RENT,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};
transactionDB.getFineCollectionByPropIdandDateRange = async ({
  propId,
  startDate,
  endDate,
}: transactionsTypes & { startDate: string; endDate: string }) => {
  const query =
    "select sum(amount) as amount from Transactions where propId =? and type != ? and transactionFor = ? and DATE(collectionDate) BETWEEN ? and ? ";
  const data = [
    propId,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.TRANSACTION_FOR.FINE,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};
transactionDB.getUtilitiesCollectionByPropIdandDateRange = async ({
  propId,
  startDate,
  endDate,
}: transactionsTypes & { startDate: string; endDate: string }) => {
  const query =
    "select sum(amount) as amount from Transactions where propId =? and type != ? and transactionFor in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) and DATE(collectionDate) BETWEEN ? and ? ";
  const data = [
    propId,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.DUES_TYPES.ELECTRICITY,
    CONSTANTS.DUES_TYPES.GAS,
    CONSTANTS.DUES_TYPES.KEY_CHARGES,
    CONSTANTS.DUES_TYPES.LAUNDRY_CHARGES,
    CONSTANTS.DUES_TYPES.MAID_CHARGES,
    CONSTANTS.DUES_TYPES.MAINTENANCE,
    CONSTANTS.DUES_TYPES.WIFI,
    CONSTANTS.DUES_TYPES.TECH_CHARGES,
    //?, ?, ?, ?, ?
    CONSTANTS.DUES_TYPES.AGREEMENT_CHARGE,
    CONSTANTS.DUES_TYPES.FOOD,
    CONSTANTS.DUES_TYPES.OTHER,
    CONSTANTS.DUES_TYPES.DAMAGE,
    CONSTANTS.DUES_TYPES.REGISTRATION,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getSecurityByClientIdandDateRange = async ({
  clientId,
  startDate,
  endDate,
}: transactionsTypes & { startDate: string; endDate: string }) => {
  const query =
    "select sum(amount) as amount from Transactions where clientId = ? and type != ? and transactionFor = ? and DATE(collectionDate) BETWEEN ? and ? ";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getRentCollectionByClientIdandDateRange = async ({
  clientId,
  startDate,
  endDate,
}: transactionsTypes & { startDate: string; endDate: string }) => {
  const query =
    "select sum(amount) as amount from Transactions where clientId = ? and type != ? and transactionFor = ? and DATE(collectionDate) BETWEEN ? and ? ";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.TRANSACTION_FOR.RENT,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};
transactionDB.getFineCollectionByClientIdandDateRange = async ({
  clientId,
  startDate,
  endDate,
}: transactionsTypes & { startDate: string; endDate: string }) => {
  const query =
    "select sum(amount) as amount from Transactions where clientId = ? and type != ? and transactionFor = ? and DATE(collectionDate) BETWEEN ? and ? ";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.TRANSACTION_FOR.FINE,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};
transactionDB.getUtilitiesCollectionByClientIdandDateRange = async ({
  clientId,
  startDate,
  endDate,
}: transactionsTypes & { startDate: string; endDate: string }) => {
  const query =
    "select sum(amount) as amount from Transactions where clientId = ? and type != ? and transactionFor in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) and DATE(collectionDate) BETWEEN ? and ? ";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.DUES_TYPES.ELECTRICITY,
    CONSTANTS.DUES_TYPES.GAS,
    CONSTANTS.DUES_TYPES.KEY_CHARGES,
    CONSTANTS.DUES_TYPES.LAUNDRY_CHARGES,
    CONSTANTS.DUES_TYPES.MAID_CHARGES,
    CONSTANTS.DUES_TYPES.MAINTENANCE,
    CONSTANTS.DUES_TYPES.WIFI,
    CONSTANTS.DUES_TYPES.TECH_CHARGES,
    //?, ?, ?, ?, ?
    CONSTANTS.DUES_TYPES.AGREEMENT_CHARGE,
    CONSTANTS.DUES_TYPES.FOOD,
    CONSTANTS.DUES_TYPES.OTHER,
    CONSTANTS.DUES_TYPES.DAMAGE,
    CONSTANTS.DUES_TYPES.REGISTRATION,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getSecurityByClientIdandDateRangeForStaff = async ({
  clientId,
  startDate,
  endDate,
  propertiesIds
}: transactionsTypes & { startDate: string; endDate: string; propertiesIds: string }) => {
  const query =
    `select sum(amount) as amount from Transactions where clientId = ? and propId in (${propertiesIds}) and type != ? and transactionFor = ? and DATE(collectionDate) BETWEEN ? and ? `;
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getRentCollectionByClientIdandDateRangeForStaff = async ({
  clientId,
  startDate,
  endDate,
  propertiesIds
}: transactionsTypes & { startDate: string; endDate: string; propertiesIds: string }) => {
  const query =
    `select sum(amount) as amount from Transactions where clientId = ? and propId in (${propertiesIds}) and type != ? and transactionFor = ? and DATE(collectionDate) BETWEEN ? and ? `;
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.TRANSACTION_FOR.RENT,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};
transactionDB.getFineCollectionByClientIdandDateRangeForStaff = async ({
  clientId,
  startDate,
  endDate,
  propertiesIds
}: transactionsTypes & { startDate: string; endDate: string; propertiesIds: string }) => {
  const query =
    `select sum(amount) as amount from Transactions where clientId = ? and propId in (${propertiesIds}) and type != ? and transactionFor = ? and DATE(collectionDate) BETWEEN ? and ? `;
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.TRANSACTION_FOR.FINE,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};
transactionDB.getUtilitiesCollectionByClientIdandDateRangeForStaff = async ({
  clientId,
  startDate,
  endDate,
  propertiesIds
}: transactionsTypes & { startDate: string; endDate: string; propertiesIds: string }) => {
  const query =
    `select sum(amount) as amount from Transactions where clientId = ? and propId in (${propertiesIds}) and type != ? and transactionFor in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) and DATE(collectionDate) BETWEEN ? and ? `;
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.DUES_TYPES.ELECTRICITY,
    CONSTANTS.DUES_TYPES.GAS,
    CONSTANTS.DUES_TYPES.KEY_CHARGES,
    CONSTANTS.DUES_TYPES.LAUNDRY_CHARGES,
    CONSTANTS.DUES_TYPES.MAID_CHARGES,
    CONSTANTS.DUES_TYPES.MAINTENANCE,
    CONSTANTS.DUES_TYPES.WIFI,
    CONSTANTS.DUES_TYPES.TECH_CHARGES,
    //?, ?, ?, ?, ?
    CONSTANTS.DUES_TYPES.AGREEMENT_CHARGE,
    CONSTANTS.DUES_TYPES.FOOD,
    CONSTANTS.DUES_TYPES.OTHER,
    CONSTANTS.DUES_TYPES.DAMAGE,
    CONSTANTS.DUES_TYPES.REGISTRATION,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

// transactionDB.getSecurityByPropIdandDateRange = async ({
//   propId,
//   startDate,
//   endDate,
// }: transactionsTypes & { startDate: string; endDate: string }) => {
//   const query =
//     "select sum(amount) as amount from Transactions where propId =? and transactionFor = ? and DATE(collectionDate) BETWEEN ? and ? ";
//   const data = [propId, CONSTANTS.DUES_TYPES.SECURITY, startDate, endDate];
//   const [rows] = await DB.execute<RowDataPacket[]>(query, data);
//   if (rows?.length > 0) return rows[0];
//   else return [];
// };

transactionDB.getOfflineTransByPropIdandDateRange = async ({
  propId,
  startDate,
  endDate,
}: transactionsTypes & { startDate: string; endDate: string }) => {
  const query =
    "select sum(amount) as amount from Transactions where propId =? and  type = ? and mode = ? and DATE(collectionDate) BETWEEN ? and ? ";
  const data = [
    propId,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getOnlineTransByPropIdandDateRange = async ({
  propId,
  startDate,
  endDate,
}: transactionsTypes & { startDate: string; endDate: string }) => {
  const query =
    "select sum(amount) as amount from Transactions where propId =? and  type = ? and mode != ? and amount > 0 and DATE(collectionDate) BETWEEN ? and ? ";
  const data = [
    propId,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getViaKipinnTransByPropIdandDateRange = async ({
  propId,
  startDate,
  endDate,
}: transactionsTypes & { startDate: string; endDate: string }) => {
  const query =
    "select sum(amount) as amount from Transactions where propId =? and  type = ? and mode != ? and DATE(collectionDate) BETWEEN ? and ? and recordedBy like ?";
  const data = [
    propId,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
    startDate,
    endDate,
    "%Kipinn App%",
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getViaKipinnTransChargesByPropIdandDateRange = async ({
  propId,
  startDate,
  endDate,
}: transactionsTypes & { startDate: string; endDate: string }) => {
  const query =
    "select sum(charges) as charges from Transactions where propId =? and gateway = ? and type = ? and mode != ? and DATE(collectionDate) BETWEEN ? and ? and recordedBy like ?";
  const data = [
    propId,
    CONSTANTS.PAYMENT_GATEWAY.EASEBUZZ,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
    startDate,
    endDate,
    "%Kipinn App%",
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getOfflineTransByClientIdandDateRange = async ({
  clientId,
  startDate,
  endDate,
}: transactionsTypes & { startDate: string; endDate: string }) => {
  const query =
    "select sum(amount) as amount from Transactions where clientId =? and  type = ? and mode = ? and DATE(collectionDate) BETWEEN ? and ? ";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getOnlineTransByClientIdandDateRange = async ({
  clientId,
  startDate,
  endDate,
}: transactionsTypes & { startDate: string; endDate: string }) => {
  const query =
    "select sum(amount) as amount from Transactions where clientId =? and  type = ? and mode != ? and amount > 0 and DATE(collectionDate) BETWEEN ? and ? ";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getViaKipinnTransByClientIdandDateRange = async ({
  clientId,
  startDate,
  endDate,
}: transactionsTypes & { startDate: string; endDate: string }) => {
  const query =
    "select sum(amount) as amount from Transactions where clientId =? and  type = ? and mode != ? and DATE(collectionDate) BETWEEN ? and ? and recordedBy like ?";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
    startDate,
    endDate,
    "%Kipinn App%",
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getViaKipinnTransChargesByClientIdandDateRange = async ({
  clientId,
  startDate,
  endDate,
}: transactionsTypes & { startDate: string; endDate: string }) => {
  const query =
    "select sum(charges) as charges from Transactions where clientId =? and gateway = ? and type = ? and mode != ? and DATE(collectionDate) BETWEEN ? and ? and recordedBy like ?";
  const data = [
    clientId,
    CONSTANTS.PAYMENT_GATEWAY.EASEBUZZ,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
    startDate,
    endDate,
    "%Kipinn App%",
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getOfflineTransByClientIdandDateRangeForStaff = async ({
  clientId,
  startDate,
  endDate,
  propertiesIds
}: transactionsTypes & { startDate: string; endDate: string; propertiesIds: string }) => {
  const query =
    `select sum(amount) as amount from Transactions where clientId =? and propId in (${propertiesIds}) and  type = ? and mode = ? and DATE(collectionDate) BETWEEN ? and ? `;
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getOnlineTransByClientIdandDateRangeForStaff = async ({
  clientId,
  startDate,
  endDate,
  propertiesIds
}: transactionsTypes & { startDate: string; endDate: string; propertiesIds: string }) => {
  const query =
    `select sum(amount) as amount from Transactions where clientId =? and propId in (${propertiesIds}) and  type = ? and mode != ? and amount > 0 and DATE(collectionDate) BETWEEN ? and ? `;
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getViaKipinnTransByClientIdandDateRangeForStaff = async ({
  clientId,
  startDate,
  endDate,
  propertiesIds
}: transactionsTypes & { startDate: string; endDate: string; propertiesIds: string }) => {
  const query =
    `select sum(amount) as amount from Transactions where clientId =? and propId in (${propertiesIds}) and  type = ? and mode != ? and DATE(collectionDate) BETWEEN ? and ? and recordedBy like ?`;
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
    startDate,
    endDate,
    "%Kipinn App%",
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getViaKipinnTransChargesByClientIdandDateRangeForStaff = async ({
  clientId,
  startDate,
  endDate,
  propertiesIds
}: transactionsTypes & { startDate: string; endDate: string; propertiesIds: string }) => {
  const query =
    `select sum(charges) as charges from Transactions where clientId =? and propId in (${propertiesIds}) and gateway = ? and type = ? and mode != ? and DATE(collectionDate) BETWEEN ? and ? and recordedBy like ?`;
  const data = [
    clientId,
    CONSTANTS.PAYMENT_GATEWAY.EASEBUZZ,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
    startDate,
    endDate,
    "%Kipinn App%",
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getTotalCollectionByTenantIdAndClientId = async ({
  tenantId,
  clientId,
}: transactionsTypes) => {
  const query =
    "select sum(amount) as amount from Transactions where tenantId = ? and clientId = ? and isFinanciallyApplicable = 1 and amount > 0";
  const data = [tenantId, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].amount;
  else return 0;
};

transactionDB.getByPropId = async ({
  propId,
  pageNum,
  limit,
  month,
}: transactionsTypes & { pageNum: number; limit: number; month: string }) => {
  const query =
    "Select T.id, T.docs, T.gateway, if(T.splitTnxId is NULL, T.gId, T.splitTnxId) as gId, T.title, T.amount, T.remarks, T.collectionDate, T.receipt, T.name, T.type, T.transactionFor, T.status, T.recordedBy, T.dueDate, T.mode, T.collectionDate as createdAt, T.updatedAt, TE.name as tenantName, T.propName, T.roomNum from Transactions as T left join Tenants as TE on TE.id = T.tenantId where T.propId = ? and MONTH(T.collectionDate) = MONTH(?) and YEAR(T.collectionDate) = YEAR(?) and T.status = ? order by T.collectionDate Desc LIMIT ?, ?";
  const offset = (pageNum - 1) * limit;
  const data = [
    propId,
    month,
    month,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

transactionDB.getTotalByPropId = async ({
  propId,
  month,
}: transactionsTypes & { month: string }) => {
  const query =
    "Select SUM(T.amount) as total from Transactions as T where T.propId = ? and MONTH(T.collectionDate) = MONTH(?) and YEAR(T.collectionDate) = YEAR(?) and T.status = ? and T.type != ? ";
  const data = [
    propId,
    month,
    month,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return Number(rows[0].total);
  else return 0;
};

transactionDB.getTotalSecurityByPropId = async ({
  propId,
  month,
}: transactionsTypes & { month: string }) => {
  const query =
    "Select SUM(T.amount) as total from Transactions as T where T.propId = ? and MONTH(T.collectionDate) = MONTH(?) and YEAR(T.collectionDate) = YEAR(?) and T.status = ? and T.type != ? and T.transactionFor = ? ";
  const data = [
    propId,
    month,
    month,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return Number(rows[0].total);
  else return 0;
};

transactionDB.updatePropIdAndRoomIdForPropSwitch = async ({
  tenantId,
  clientId,
  propId,
  roomId,
}: transactionsTypes) => {
  const query =
    "update Transactions set propId = ?, roomId = ? where tenantId = ? and clientId = ?";
  const data = [propId, roomId, tenantId, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

transactionDB.getRecentTransactions = async ({
  clientId,
  limit,
}: transactionsTypes & { pageNum: number; limit: number }) => {
  const query =
    "Select T.id, T.docs, T.gateway, T.gId, T.amount, T.collectionDate, T.receipt, T.title, T.name, T.remarks, T.type, T.transactionFor, T.status, T.recordedBy, T.dueDate, T.mode, T.collectionDate as createdAt, T.updatedAt, TE.name as tenantName, T.propName, T.roomNum from Transactions as T left join Tenants as TE on TE.id = T.tenantId where T.clientId = ? and T.type = ? order by T.collectionDate Desc LIMIT ?";
  const data = [clientId, CONSTANTS.TRANSACTION_TYPES.TENANT_PAID, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

transactionDB.getRecentTransactionsForStaff = async ({
  clientId,
  propertiesIds,
  limit,
}: transactionsTypes & { pageNum: number; limit: number; propertiesIds: string; }) => {
  const query =
    `Select T.id, T.docs, T.gateway, T.gId, T.amount, T.collectionDate, T.receipt, T.title, T.name, T.remarks, T.type, T.transactionFor, T.status, T.recordedBy, T.dueDate, T.mode, T.collectionDate as createdAt, T.updatedAt, TE.name as tenantName, T.propName, T.roomNum from Transactions as T left join Tenants as TE on TE.id = T.tenantId where T.clientId = ? and T.propId in (${propertiesIds}) and T.type = ? order by T.collectionDate Desc LIMIT ?`;
  const data = [clientId, CONSTANTS.TRANSACTION_TYPES.TENANT_PAID, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

transactionDB.getRecentTransactionsForProp = async ({
  propId,
  limit,
}: transactionsTypes & { pageNum: number; limit: number }) => {
  const query =
    "Select T.id, T.docs, T.gateway, T.gId, T.amount, T.collectionDate, T.receipt, T.title, T.name, T.remarks, T.type, T.transactionFor, T.status, T.recordedBy, T.dueDate, T.mode, T.collectionDate as createdAt, T.updatedAt, TE.name as tenantName, T.propName, T.roomNum from Transactions as T left join Tenants as TE on TE.id = T.tenantId where T.propId = ? and T.type = ? order by T.collectionDate Desc LIMIT ?";
  const data = [propId, CONSTANTS.TRANSACTION_TYPES.TENANT_PAID, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

// FY = Mar-Apr
transactionDB.totalCollectionForFY = async ({
  clientId,
  startDate,
  endDate,
}: transactionsTypes & { startDate: string; endDate: string }) => {
  const query =
    "Select SUM(T.amount) as amount, Month(collectionDate) as month, Year(collectionDate) as year from Transactions as T where T.clientId = ? and date(T.collectionDate) Between ? and ? and T.status = ? and T.type != ? and T.isFinanciallyApplicable = 1 group by month, year";
  const data = [
    clientId,
    startDate,
    endDate,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.totalCollectionForFYAndStaff = async ({
  clientId,
  propertiesIds,
  startDate,
  endDate,
}: transactionsTypes & { startDate: string; endDate: string; propertiesIds: string }) => {
  const query =
    `Select SUM(T.amount) as amount, Month(collectionDate) as month, Year(collectionDate) as year from Transactions as T where T.clientId = ? and T.propId in (${propertiesIds}) and date(T.collectionDate) Between ? and ? and T.status = ? and T.type != ? and T.isFinanciallyApplicable = 1 group by month, year`;
  const data = [
    clientId,
    startDate,
    endDate,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.totalCollectionForFYAndProp = async ({
  propId,
  startDate,
  endDate,
}: transactionsTypes & { startDate: string; endDate: string }) => {
  const query =
    "Select SUM(T.amount) as amount, Month(T.collectionDate) as month, Year(collectionDate) as year from Transactions as T where T.propId = ? and date(T.collectionDate) Between ? and ? and T.status = ? and T.type != ? and T.isFinanciallyApplicable = 1 group by month, year";
  const data = [
    propId,
    startDate,
    endDate,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getTotalByDateRange = async ({
  clientId,
  startDate,
  endDate,
}: transactionsTypes & { startDate: string; endDate: string }) => {
  const query =
    "Select SUM(T.amount) as amount from Transactions as T where T.clientId = ? and date(T.collectionDate) Between ? and ? and T.status = ? and T.type != ? and amount > 0";
  const data = [
    clientId,
    startDate,
    endDate,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].amount;
  else return 0;
};

transactionDB.getTotalByDateRangeWithoutSecurity = async ({
  clientId,
  startDate,
  endDate,
}: transactionsTypes & { startDate: string; endDate: string }) => {
  const query =
    "Select IFNULL(SUM(T.amount), 0) as amount from Transactions as T where T.clientId = ? and date(T.collectionDate) Between ? and ? and T.status = ? and T.type != ? and T.transactionFor != ? and T.amount > 0";
  const data = [
    clientId,
    startDate,
    endDate,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].amount;
  else return 0;
};

transactionDB.getTotalSecurityByDateRange = async ({
  clientId,
  startDate,
  endDate,
}: transactionsTypes & { startDate: string; endDate: string }) => {
  const query =
    "Select SUM(T.amount) as amount from Transactions as T where T.clientId = ? and date(T.collectionDate) Between ? and ? and T.status = ? and T.type != ? and (T.transactionFor = ? or amount < 0)";
  const data = [
    clientId,
    startDate,
    endDate,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].amount;
  else return 0;
};

transactionDB.getTotalSecurityInHandByDateRange = async ({
  clientId,
  startDate,
  endDate,
}: transactionsTypes & { startDate: string; endDate: string }) => {
  const query =
    `Select SUM(T.amount) as amount from Transactions as T where T.clientId = ? and date(T.collectionDate) Between ? and ? and T.status = ? and T.type != ? and (T.transactionFor = ? or T.amount < 0)
    UNION
    Select SUM(L.amount) as amount from Ledgers as L join Transactions as T on T.ledgerReferenceId = L.referenceId where L.clientId = ? and L.subType = ? and T.mode = ? and L.amount < 0 and Date(T.collectionDate) Between ? and ? 
    `;
  const data = [
    clientId,
    startDate,
    endDate,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
    clientId,
    CONSTANTS.LEDGER_SUBTYPES.MARKED_FROM_SECURITY,
    CONSTANTS.TRANSACTION_MODES.ADJUSTED_FROM_SECURITY,
    startDate,
    endDate,
  ];

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

  let totalSecurity = Number(rows[0]?.amount) || 0;
  let securityUsed = Number(rows[1]?.amount) || 0;
  let inHandSecurity = totalSecurity + securityUsed;

  if (rows?.length > 0) return inHandSecurity;
  else return 0;
};

transactionDB.getTotalByDateRangeAndPropId = async ({
  clientId,
  propId,
  startDate,
  endDate,
}: transactionsTypes & { startDate: string; endDate: string }) => {
  const query =
    "Select SUM(T.amount) as amount from Transactions as T where T.clientId = ? and T.propId=? and date(T.collectionDate) Between ? and ? and T.status = ? and T.type != ?";
  const data = [
    clientId,
    propId,
    startDate,
    endDate,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].amount;
  else return 0;
};

transactionDB.getTotalForStaffByDateRange = async ({
  clientId,
  startDate,
  endDate,
  propertiesIds,
}: transactionsTypes & {
  startDate: string;
  endDate: string;
  propertiesIds: string;
}) => {
  const query =
    "Select SUM(T.amount) as amount from Transactions as T where T.clientId = ? and T.propId in (" +
    `${propertiesIds}` +
    ") and date(T.collectionDate) Between ? and ? and T.status = ? and T.type != ?";
  const data = [
    clientId,
    startDate,
    endDate,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].amount;
  else return 0;
};

transactionDB.updateSettleDetails = async ({
  id,
  settleStatus,
  settleDescription,
  splitTnxId,
}: transactionsTypes) => {
  const query =
    "UPDATE Transactions SET settleStatus = ?, settleDescription = ?, splitTnxId=? where id = ?";
  const data = [settleStatus, settleDescription, splitTnxId, id];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

transactionDB.updateSettleDetailsByGId = async ({
  gId,
  settleStatus,
  settleDescription,
  splitTnxId,
}: transactionsTypes) => {
  const query =
    "UPDATE Transactions SET settleStatus = ?, settleDescription = ?, splitTnxId=? where gId = ?";
  const data = [settleStatus, settleDescription, splitTnxId, gId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

transactionDB.updateSplitId = async ({
  gId,
  splitTnxId,
}: transactionsTypes) => {
  const query =
    "UPDATE Transactions SET splitTnxId=? where gId = ?";
  const data = [splitTnxId, gId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

transactionDB.updateAccountDetailsByGId = async ({
  gId,
  bankId,
  paymentAccountNo,
  paymentAccountName,
}: transactionsTypes) => {
  try {
    const query =
      "UPDATE Transactions SET bankId = ?, paymentAccountNo = ?, paymentAccountName = ? where gId = ?";
    const data = [bankId, paymentAccountNo, paymentAccountName, gId];
    const [rows] = await DB.execute<RowDataPacket[]>(query, data);
    return true;
  } catch (error: any) {
    log.info(`[Transaction Model], [updateAccountDetailsByGId], Error: ${error?.message || error}, GId [${gId}], Bank Id [${bankId}], Payment Account No [${paymentAccountNo}], Payment Account Name [${paymentAccountName}]`)
    return false;
  }
};

transactionDB.getByTenantNameMobile = async ({
  clientId,
  searchVal,
}: transactionsTypes & { searchVal: string }) => {
  const query =
    "select T.id, T.docs, T.gateway, if(T.splitTnxId is NULL, T.gId, T.splitTnxId) as gId, T.gateway, T.title, T.remarks, T.orderId, T.gstCharges, T.gstNo, T.invoiceNo, T.bankId, T.clientId, T.tenantId, T.propId, T.roomId, T.amount, T.name, T.receipt, T.type, T.transactionFor, T.status, T.recordedBy, T.dueDate, T.mode, T.ledgerReferenceId, T.propName, T.roomNum, T.settleStatus, T.settleDescription, T.collectionDate, T.discount, (Select bankName from BankAccounts where accountNum = T.paymentAccountNo) as bankName, T.paymentAccountNo, T.paymentAccountName, T.charges, T.collectionDate as createdAt, T.updatedAt, TE.name as tenantName, TE.mobile as tenantMobile, T.holdAmount, T.utrNo, T.bankRefNum, T.settledOn from Transactions as T join Tenants as TE on T.tenantId = TE.id where T.clientId = ? and (TE.name like ? or TE.mobile like ? or T.recordedBy like ?) order by T.collectionDate asc";
  const data = [clientId, `%${searchVal}%`, `%${searchVal}%`, `%${searchVal}%`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getByClientIdAndPropIdAndSearch = async ({
  clientId,
  propId,
  searchVal,
  searchType,
}: transactionsTypes & { searchVal: string; searchType: string; }) => {
  const data: any = [clientId, propId,];
  let filterCondition = "";

  if (Number(searchType) === 1) {
    filterCondition = " and TE.mobile like ?";
    data.push(`${searchVal}%`);
  } else if (Number(searchType) === 2) {
    filterCondition = " and TE.name like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 3) {
    filterCondition = " and T.recordedBy like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 4) {
    filterCondition = " and P.name like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 5) {
    filterCondition = " and T.roomNum like ?";
    data.push(`${searchVal}%`);
  }
  const query =
    `select T.id, T.docs, T.gateway, if(T.splitTnxId is NULL, T.gId, T.splitTnxId) as gId, T.title, T.remarks, T.orderId, T.gstCharges, T.gstNo, T.invoiceNo, T.bankId, T.clientId, T.tenantId, T.propId, T.roomId, T.amount, T.name, T.receipt, T.type, T.transactionFor, T.status, T.recordedBy, T.dueDate, T.mode, T.ledgerReferenceId, T.propName, T.roomNum, T.settleStatus, T.settleDescription, T.collectionDate, T.discount, T.paymentAccountNo, T.paymentAccountName, T.charges, T.collectionDate as createdAt, T.updatedAt, TE.name as tenantName, TE.mobile as tenantMobile, T.holdAmount, T.utrNo, T.bankRefNum, T.settledOn from Transactions as T join Properties as P on P.id = T.propId join Tenants as TE on T.tenantId = TE.id where T.clientId = ? and T.propId = ? ${filterCondition} order by T.id desc`;
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getByClientIdAndTenantNameMobile = async ({
  clientId,
  searchVal,
  searchType,
}: transactionsTypes & { searchVal: string; searchType: number; }) => {

  const data: any = [clientId,];
  let filterCondition = "";
  if (Number(searchType) === 1) {
    filterCondition = " and TE.mobile like ?";
    data.push(`${searchVal}%`);
  } else if (Number(searchType) === 2) {
    filterCondition = " and TE.name like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 3) {
    filterCondition = " and T.recordedBy like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 4) {
    filterCondition = " and P.name like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 5) {
    filterCondition = " and T.roomNum like ?";
    data.push(`${searchVal}%`);
  }
  const query =
    `select T.id, T.docs, T.gateway, if(T.splitTnxId is NULL, T.gId, T.splitTnxId) as gId, T.title, T.remarks, T.orderId, T.gstCharges, T.gstNo, T.invoiceNo, T.bankId, T.clientId, T.tenantId, T.propId, T.roomId, T.amount, T.name, T.receipt, T.type, T.transactionFor, T.status, T.recordedBy, T.dueDate, T.mode, T.ledgerReferenceId, T.propName, T.roomNum, T.settleStatus, T.settleDescription, T.collectionDate, T.discount, T.paymentAccountNo, T.paymentAccountName, T.charges, T.collectionDate as createdAt, T.updatedAt, TE.name as tenantName, TE.mobile as tenantMobile, (select value from Documents where tenantId = T.tenantId and clientId = T.clientId and type = 4 and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = T.tenantId and clientId = T.clientId order by id desc limit 1) as kycStatus, T.holdAmount, T.utrNo, T.bankRefNum, T.settledOn from Transactions as T join Properties as P on P.id = T.propId join Tenants as TE on T.tenantId = TE.id where T.clientId = ? ${filterCondition} order by T.id desc`;
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getByClientIdAndTenantNameMobileForStaff = async ({
  clientId,
  searchVal,
  searchType,
  propertiesIds,
}: transactionsTypes & { searchVal: string; searchType: number; propertiesIds: string }) => {

  const data: any = [clientId,];
  let filterCondition = "";
  if (Number(searchType) === 1) {
    filterCondition = " and TE.mobile like ?";
    data.push(`${searchVal}%`);
  } else if (Number(searchType) === 2) {
    filterCondition = " and TE.name like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 3) {
    filterCondition = " and T.recordedBy like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 4) {
    filterCondition = " and P.name like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 5) {
    filterCondition = " and T.roomNum like ?";
    data.push(`${searchVal}%`);
  }

  const query =
    `select T.id, T.docs, T.gateway, if(T.splitTnxId is NULL, T.gId, T.splitTnxId) as gId, T.title, T.remarks, T.orderId, T.gstCharges, T.gstNo, T.invoiceNo, T.bankId, T.clientId, T.tenantId, T.propId, T.roomId, T.amount, T.name, T.receipt, T.type, T.transactionFor, T.status, T.recordedBy, T.dueDate, T.mode, T.ledgerReferenceId, T.propName, T.roomNum, T.settleStatus, T.settleDescription, T.collectionDate, T.discount, T.paymentAccountNo, T.paymentAccountName, T.charges, T.collectionDate as createdAt, T.updatedAt, TE.name as tenantName, TE.mobile as tenantMobile, (select value from Documents where tenantId = T.tenantId and clientId = T.clientId and type = 4 and moveOut=0 limit 1) as profilePicture, (select kycStatus from Occupancies where tenantId = T.tenantId and clientId = T.clientId order by id desc limit 1) as kycStatus, T.holdAmount, T.utrNo, T.bankRefNum, T.settledOn from Transactions as T join Properties as P on P.id = T.propId join Tenants as TE on T.tenantId = TE.id where T.clientId = ? and T.propId in (${propertiesIds}) ${filterCondition} order by T.id desc`;
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

transactionDB.getRentByTenantNameOrMobile = async ({
  clientId,
  propId,
  searchVal,
  searchType,
}: transactionsTypes & { searchVal: string; searchType: number }) => {
  const data: any = [
    clientId,
    propId,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
  ];

  let filterCondition = "";

  if (Number(searchType) === 1) {
    filterCondition = " and TE.mobile like ?";
    data.push(`${searchVal}%`);
  } else if (Number(searchType) === 2) {
    filterCondition = " and TE.name like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 3) {
    filterCondition = " and T.recordedBy like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 4) {
    filterCondition = " and P.name like ?";
    data.push(`%${searchVal}%`);
  }
  const query =
    `select sum(T.amount) as amount from Transactions as T join Properties as P on P.id = T.propId join Tenants as TE on T.tenantId = TE.id where T.clientId =? and T.propId = ? and T.type != ? and T.amount > 0 ${filterCondition}`;
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getSecurityByTenantNameOrMobile = async ({
  clientId,
  propId,
  searchVal,
  searchType,
}: transactionsTypes & { searchVal: string; searchType: string }) => {

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

  let filterCondition = "";
  if (Number(searchType) === 1) {
    filterCondition = " and TE.mobile like ?";
    data.push(`${searchVal}%`);
  } else if (Number(searchType) === 2) {
    filterCondition = " and TE.name like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 3) {
    filterCondition = " and T.recordedBy like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 4) {
    filterCondition = " and P.name like ?";
    data.push(`%${searchVal}%`);
  }
  const query =
    `select sum(T.amount) as amount from Transactions as T join Properties as P on P.id = T.propId join Tenants as TE on T.tenantId = TE.id where T.clientId =? and T.propId = ? and T.transactionFor = ? ${filterCondition}`;
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getRentCollectionByTenantNameOrMobile = async ({
  clientId,
  propId,
  searchVal,
  searchType,
}: transactionsTypes & { searchVal: string; searchType: number }) => {

  const data: any = [
    clientId,
    propId,
    CONSTANTS.DUES_TYPES.RENT,
  ];
  let filterCondition = "";
  if (Number(searchType) === 1) {
    filterCondition = " and TE.mobile like ?";
    data.push(`${searchVal}%`);
  } else if (Number(searchType) === 2) {
    filterCondition = " and TE.name like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 3) {
    filterCondition = " and T.recordedBy like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 4) {
    filterCondition = " and P.name like ?";
    data.push(`%${searchVal}%`);
  }
  const query =
    `select sum(T.amount) as amount from Transactions as T join Properties as P on P.id = T.propId join Tenants as TE on T.tenantId = TE.id where T.clientId =? and T.propId = ? and T.transactionFor = ? ${filterCondition}`;
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};
transactionDB.getFineCollectionByTenantNameOrMobile = async ({
  clientId,
  propId,
  searchVal,
  searchType,
}: transactionsTypes & { searchVal: string; searchType: number }) => {

  const data: any = [
    clientId,
    propId,
    CONSTANTS.DUES_TYPES.FINE,
  ];

  let filterCondition = "";
  if (Number(searchType) === 1) {
    filterCondition = " and TE.mobile like ?";
    data.push(`${searchVal}%`);
  } else if (Number(searchType) === 2) {
    filterCondition = " and TE.name like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 3) {
    filterCondition = " and T.recordedBy like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 4) {
    filterCondition = " and P.name like ?";
    data.push(`%${searchVal}%`);
  }

  const query =
    `select sum(T.amount) as amount from Transactions as T join Properties as P on P.id = T.propId join Tenants as TE on T.tenantId = TE.id where T.clientId =? and T.propId = ? and T.transactionFor = ? ${filterCondition}`;
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};
transactionDB.getUtilitiesCollectionByTenantNameOrMobile = async ({
  clientId,
  propId,
  searchVal,
  searchType,
}: transactionsTypes & { searchVal: string; searchType: number }) => {

  const data: any = [
    clientId,
    propId,
    CONSTANTS.DUES_TYPES.ELECTRICITY,
    CONSTANTS.DUES_TYPES.GAS,
    CONSTANTS.DUES_TYPES.KEY_CHARGES,
    CONSTANTS.DUES_TYPES.LAUNDRY_CHARGES,
    CONSTANTS.DUES_TYPES.MAID_CHARGES,
    CONSTANTS.DUES_TYPES.MAINTENANCE,
    CONSTANTS.DUES_TYPES.WIFI,
    CONSTANTS.DUES_TYPES.TECH_CHARGES,
    //?, ?, ?, ?, ?
    CONSTANTS.DUES_TYPES.AGREEMENT_CHARGE,
    CONSTANTS.DUES_TYPES.FOOD,
    CONSTANTS.DUES_TYPES.OTHER,
    CONSTANTS.DUES_TYPES.DAMAGE,
    CONSTANTS.DUES_TYPES.REGISTRATION,
  ];
  let filterCondition = "";
  if (Number(searchType) === 1) {
    filterCondition = " and TE.mobile like ?";
    data.push(`${searchVal}%`);
  } else if (Number(searchType) === 2) {
    filterCondition = " and TE.name like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 3) {
    filterCondition = " and T.recordedBy like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 4) {
    filterCondition = " and P.name like ?";
    data.push(`%${searchVal}%`);
  }

  const query =
    `select sum(T.amount) as amount from Transactions as T join Properties as P on P.id = T.propId join Tenants as TE on T.tenantId = TE.id where T.clientId =? and T.propId = ? and T.transactionFor in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ${filterCondition}`;
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getOfflineTransByTenantNameOrMobile = async ({
  clientId,
  propId,
  searchVal,
  searchType,
}: transactionsTypes & { searchVal: string; searchType: string; }) => {

  const data: any = [
    clientId,
    propId,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
  ];
  let filterCondition = "";
  if (Number(searchType) === 1) {
    filterCondition = " and TE.mobile like ?";
    data.push(`${searchVal}%`);
  } else if (Number(searchType) === 2) {
    filterCondition = " and TE.name like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 3) {
    filterCondition = " and T.recordedBy like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 4) {
    filterCondition = " and P.name like ?";
    data.push(`%${searchVal}%`);
  }
  const query =
    `select sum(T.amount) as amount from Transactions as T join Properties as P on P.id = T.propId join Tenants as TE on T.tenantId = TE.id where T.clientId =? and T.propId = ? and  T.type = ? and T.mode = ? ${filterCondition}`;
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getOnlineTransByTenantNameOrMobile = async ({
  clientId,
  propId,
  searchVal,
  searchType,
}: transactionsTypes & { searchVal: string; searchType: string; }) => {
  const data: any = [
    clientId,
    propId,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
  ];
  let filterCondition = "";
  if (Number(searchType) === 1) {
    filterCondition = " and TE.mobile like ?";
    data.push(`${searchVal}%`);
  } else if (Number(searchType) === 2) {
    filterCondition = " and TE.name like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 3) {
    filterCondition = " and T.recordedBy like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 4) {
    filterCondition = " and P.name like ?";
    data.push(`%${searchVal}%`);
  }
  const query =
    `select sum(T.amount) as amount from Transactions as T join Properties as P on P.id = T.propId join Tenants as TE on T.tenantId = TE.id where T.clientId =? and T.propId = ? and  T.type = ? and T.mode != ? and T.amount > 0 ${filterCondition}`;
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getTodaysCollectionForPropBySearch = async ({
  propId,
  searchVal,
  searchType,
}: transactionsTypes & { searchVal: string; searchType: string; }) => {

  const data: any = [
    propId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
  ];

  let filterCondition = "";
  if (Number(searchType) === 1) {
    filterCondition = " and TE.mobile like ?";
    data.push(`${searchVal}%`);
  } else if (Number(searchType) === 2) {
    filterCondition = " and TE.name like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 3) {
    filterCondition = " and T.recordedBy like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 4) {
    filterCondition = " and P.name like ?";
    data.push(`%${searchVal}%`);
  }
  const query =
    `Select SUM(T.amount) as totalIncome from Transactions as T join Properties as P on P.id = T.propId join Tenants as TE on TE.id = T.tenantId where T.propId=? and T.status=? and T.type != ? and T.amount > 0 and DATE(T.collectionDate) = DATE(now()) ${filterCondition}`;
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].totalIncome;
  else return 0;
};

transactionDB.getRentByClientIdAndTenantNameOrMobile = async ({
  clientId,
  searchVal,
  searchType,
}: transactionsTypes & { searchVal: string; searchType: number; }) => {
  const data: any = [
    clientId,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
  ];
  let filterCondition = "";
  if (Number(searchType) === 1) {
    filterCondition = " and TE.mobile like ?";
    data.push(`${searchVal}%`);
  } else if (Number(searchType) === 2) {
    filterCondition = " and TE.name like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 3) {
    filterCondition = " and T.recordedBy like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 4) {
    filterCondition = " and P.name like ?";
    data.push(`%${searchVal}%`);
  }
  const query =
    `select sum(T.amount) as amount from Transactions as T join Properties as P on P.id = T.propId join Tenants as TE on T.tenantId = TE.id where T.clientId =? and T.type != ? and T.amount > 0 ${filterCondition}`;
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getSecurityByClientIdAndTenantNameOrMobile = async ({
  clientId,
  searchVal,
  searchType,
}: transactionsTypes & { searchVal: string; searchType: number; }) => {
  const data: any = [
    clientId,
    CONSTANTS.DUES_TYPES.SECURITY,
  ];
  let filterCondition = "";
  if (Number(searchType) === 1) {
    filterCondition = " and TE.mobile like ?";
    data.push(`${searchVal}%`);
  } else if (Number(searchType) === 2) {
    filterCondition = " and TE.name like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 3) {
    filterCondition = " and T.recordedBy like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 4) {
    filterCondition = " and P.name like ?";
    data.push(`%${searchVal}%`);
  }
  const query =
    `select sum(T.amount) as amount from Transactions as T join Properties as P on P.id = T.propId join Tenants as TE on T.tenantId = TE.id where T.clientId =? and T.transactionFor = ? ${filterCondition}`;
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getRentCollectionByClientIdAndTenantNameOrMobile = async ({
  clientId,
  searchVal,
  searchType,
}: transactionsTypes & { searchVal: string; searchType: number }) => {
  const data: any = [
    clientId,
    CONSTANTS.DUES_TYPES.RENT,
  ];
  let filterCondition = "";
  if (Number(searchType) === 1) {
    filterCondition = " and TE.mobile like ?";
    data.push(`${searchVal}%`);
  } else if (Number(searchType) === 2) {
    filterCondition = " and TE.name like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 3) {
    filterCondition = " and T.recordedBy like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 4) {
    filterCondition = " and P.name like ?";
    data.push(`%${searchVal}%`);
  }
  const query =
    `select sum(T.amount) as amount from Transactions as T join Properties as P on P.id = T.propId join Tenants as TE on T.tenantId = TE.id where T.clientId =? and T.transactionFor = ? ${filterCondition}`;
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};
transactionDB.getFineCollectionByClientIdAndTenantNameOrMobile = async ({
  clientId,
  searchVal,
  searchType,
}: transactionsTypes & { searchVal: string; searchType: number }) => {
  const data: any = [
    clientId,
    CONSTANTS.DUES_TYPES.FINE,
  ];
  let filterCondition = "";
  if (Number(searchType) === 1) {
    filterCondition = " and TE.mobile like ?";
    data.push(`${searchVal}%`);
  } else if (Number(searchType) === 2) {
    filterCondition = " and TE.name like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 3) {
    filterCondition = " and T.recordedBy like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 4) {
    filterCondition = " and P.name like ?";
    data.push(`%${searchVal}%`);
  }
  const query =
    `select sum(T.amount) as amount from Transactions as T join Properties as P on P.id = T.propId join Tenants as TE on T.tenantId = TE.id where T.clientId =? and T.transactionFor = ? ${filterCondition}`;
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};
transactionDB.getUtilitiesCollectionByClientIdAndTenantNameOrMobile = async ({
  clientId,
  searchVal,
  searchType,
}: transactionsTypes & { searchVal: string; searchType: number; }) => {
  const data: any = [
    clientId,
    CONSTANTS.DUES_TYPES.ELECTRICITY,
    CONSTANTS.DUES_TYPES.GAS,
    CONSTANTS.DUES_TYPES.KEY_CHARGES,
    CONSTANTS.DUES_TYPES.LAUNDRY_CHARGES,
    CONSTANTS.DUES_TYPES.MAID_CHARGES,
    CONSTANTS.DUES_TYPES.MAINTENANCE,
    CONSTANTS.DUES_TYPES.WIFI,
    CONSTANTS.DUES_TYPES.TECH_CHARGES,
    //?, ?, ?, ?, ?
    CONSTANTS.DUES_TYPES.AGREEMENT_CHARGE,
    CONSTANTS.DUES_TYPES.FOOD,
    CONSTANTS.DUES_TYPES.OTHER,
    CONSTANTS.DUES_TYPES.DAMAGE,
    CONSTANTS.DUES_TYPES.REGISTRATION,
  ];
  let filterCondition = "";
  if (Number(searchType) === 1) {
    filterCondition = " and TE.mobile like ?";
    data.push(`${searchVal}%`);
  } else if (Number(searchType) === 2) {
    filterCondition = " and TE.name like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 3) {
    filterCondition = " and T.recordedBy like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 4) {
    filterCondition = " and P.name like ?";
    data.push(`%${searchVal}%`);
  }
  const query =
    `select sum(T.amount) as amount from Transactions as T join Properties as P on P.id = T.propId join Tenants as TE on T.tenantId = TE.id where T.clientId =? and T.transactionFor in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ${filterCondition}`;
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getOfflineTransByClientIdAndTenantNameOrMobile = async ({
  clientId,
  searchVal,
  searchType,
}: transactionsTypes & { searchVal: string; searchType: number; }) => {
  const data: any = [
    clientId,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
  ];
  let filterCondition = "";
  if (Number(searchType) === 1) {
    filterCondition = " and TE.mobile like ?";
    data.push(`${searchVal}%`);
  } else if (Number(searchType) === 2) {
    filterCondition = " and TE.name like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 3) {
    filterCondition = " and T.recordedBy like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 4) {
    filterCondition = " and P.name like ?";
    data.push(`%${searchVal}%`);
  }
  const query =
    `select sum(T.amount) as amount from Transactions as T join Properties as P on P.id = T.propId join Tenants as TE on T.tenantId = TE.id where T.clientId =? and T.type = ? and T.mode = ? ${filterCondition}`;
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getOnlineTransByClientIdAndTenantNameOrMobile = async ({
  clientId,
  searchVal,
  searchType,
}: transactionsTypes & { searchVal: string; searchType: number; }) => {
  const data: any = [
    clientId,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
  ];
  let filterCondition = "";
  if (Number(searchType) === 1) {
    filterCondition = " and TE.mobile like ?";
    data.push(`${searchVal}%`);
  } else if (Number(searchType) === 2) {
    filterCondition = " and TE.name like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 3) {
    filterCondition = " and T.recordedBy like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 4) {
    filterCondition = " and P.name like ?";
    data.push(`%${searchVal}%`);
  }
  const query =
    `select sum(T.amount) as amount from Transactions as T join Properties as P on P.id = T.propId join Tenants as TE on T.tenantId = TE.id where T.clientId =? and T.type = ? and T.mode != ? and T.amount > 0 ${filterCondition}`;
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getTodaysCollectionByClientIdAndSearch = async ({
  clientId,
  searchVal,
  searchType,
}: transactionsTypes & { searchVal: string; searchType: number }) => {
  const data: any = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
  ];
  let filterCondition = "";
  if (Number(searchType) === 1) {
    filterCondition = " and TE.mobile like ?";
    data.push(`${searchVal}%`);
  } else if (Number(searchType) === 2) {
    filterCondition = " and TE.name like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 3) {
    filterCondition = " and T.recordedBy like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 4) {
    filterCondition = " and P.name like ?";
    data.push(`%${searchVal}%`);
  }
  const query =
    `Select SUM(T.amount) as totalIncome from Transactions as T join Properties as P on P.id = T.propId join Tenants as TE on TE.id = T.tenantId where T.clientId=? and T.status=? and T.type != ? and T.amount > 0 and DATE(T.collectionDate) = DATE(now()) ${filterCondition}`;
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].totalIncome;
  else return 0;
};

transactionDB.getRentByClientIdAndTenantNameOrMobileForStaff = async ({
  clientId,
  searchVal,
  searchType,
  propertiesIds
}: transactionsTypes & { searchVal: string; propertiesIds: string; searchType: number }) => {
  const data: any = [
    clientId,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
  ];
  let filterCondition = "";
  if (Number(searchType) === 1) {
    filterCondition = " and TE.mobile like ?";
    data.push(`${searchVal}%`);
  } else if (Number(searchType) === 2) {
    filterCondition = " and TE.name like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 3) {
    filterCondition = " and T.recordedBy like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 4) {
    filterCondition = " and P.name like ?";
    data.push(`%${searchVal}%`);
  }
  const query =
    `select sum(T.amount) as amount from Transactions as T join Properties as P on P.id = T.propId join Tenants as TE on T.tenantId = TE.id where T.clientId =? and T.propId in (${propertiesIds}) and T.type != ? and T.amount > 0 ${filterCondition} `;
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getSecurityByClientIdAndTenantNameOrMobileForStaff = async ({
  clientId,
  searchVal,
  searchType,
  propertiesIds
}: transactionsTypes & { searchVal: string; propertiesIds: string; searchType: number }) => {
  const data: any = [
    clientId,
    CONSTANTS.DUES_TYPES.SECURITY,
  ];
  let filterCondition = "";
  if (Number(searchType) === 1) {
    filterCondition = " and TE.mobile like ?";
    data.push(`${searchVal}%`);
  } else if (Number(searchType) === 2) {
    filterCondition = " and TE.name like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 3) {
    filterCondition = " and T.recordedBy like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 4) {
    filterCondition = " and P.name like ?";
    data.push(`%${searchVal}%`);
  }
  const query =
    `select sum(T.amount) as amount from Transactions as T join Properties as P on P.id = T.propId join Tenants as TE on T.tenantId = TE.id where T.clientId =? and T.propId in (${propertiesIds}) and T.transactionFor = ? ${filterCondition} `;
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getRentCollectionByClientIdAndTenantNameOrMobileForStaff = async ({
  clientId,
  searchVal,
  searchType,
  propertiesIds
}: transactionsTypes & { searchVal: string; propertiesIds: string; searchType: number }) => {
  const data: any = [
    clientId,
    CONSTANTS.DUES_TYPES.RENT,
  ];
  let filterCondition = "";
  if (Number(searchType) === 1) {
    filterCondition = " and TE.mobile like ?";
    data.push(`${searchVal}%`);
  } else if (Number(searchType) === 2) {
    filterCondition = " and TE.name like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 3) {
    filterCondition = " and T.recordedBy like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 4) {
    filterCondition = " and P.name like ?";
    data.push(`%${searchVal}%`);
  }
  const query =
    `select sum(T.amount) as amount from Transactions as T join Properties as P on P.id = T.propId join Tenants as TE on T.tenantId = TE.id where T.clientId =? and T.propId in (${propertiesIds}) and T.transactionFor = ? ${filterCondition}`;
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};
transactionDB.getFineCollectionByClientIdAndTenantNameOrMobileForStaff = async ({
  clientId,
  searchVal,
  searchType,
  propertiesIds
}: transactionsTypes & { searchVal: string; propertiesIds: string; searchType: number }) => {
  const data: any = [
    clientId,
    CONSTANTS.DUES_TYPES.FINE,
  ];
  let filterCondition = "";
  if (Number(searchType) === 1) {
    filterCondition = " and TE.mobile like ?";
    data.push(`${searchVal}%`);
  } else if (Number(searchType) === 2) {
    filterCondition = " and TE.name like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 3) {
    filterCondition = " and T.recordedBy like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 4) {
    filterCondition = " and P.name like ?";
    data.push(`%${searchVal}%`);
  }
  const query =
    `select sum(T.amount) as amount from Transactions as T join Properties as P on P.id = T.propId join Tenants as TE on T.tenantId = TE.id where T.clientId =? and T.propId in (${propertiesIds}) and T.transactionFor = ? ${filterCondition}`;
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};
transactionDB.getUtilitiesCollectionByClientIdAndTenantNameOrMobileForStaff = async ({
  clientId,
  searchVal,
  searchType,
  propertiesIds
}: transactionsTypes & { searchVal: string; propertiesIds: string; searchType: number }) => {
  const data: any = [
    clientId,
    CONSTANTS.DUES_TYPES.ELECTRICITY,
    CONSTANTS.DUES_TYPES.GAS,
    CONSTANTS.DUES_TYPES.KEY_CHARGES,
    CONSTANTS.DUES_TYPES.LAUNDRY_CHARGES,
    CONSTANTS.DUES_TYPES.MAID_CHARGES,
    CONSTANTS.DUES_TYPES.MAINTENANCE,
    CONSTANTS.DUES_TYPES.WIFI,
    CONSTANTS.DUES_TYPES.TECH_CHARGES,
    //?, ?, ?, ?, ?
    CONSTANTS.DUES_TYPES.AGREEMENT_CHARGE,
    CONSTANTS.DUES_TYPES.FOOD,
    CONSTANTS.DUES_TYPES.OTHER,
    CONSTANTS.DUES_TYPES.DAMAGE,
    CONSTANTS.DUES_TYPES.REGISTRATION,
  ];
  let filterCondition = "";
  if (Number(searchType) === 1) {
    filterCondition = " and TE.mobile like ?";
    data.push(`${searchVal}%`);
  } else if (Number(searchType) === 2) {
    filterCondition = " and TE.name like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 3) {
    filterCondition = " and T.recordedBy like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 4) {
    filterCondition = " and P.name like ?";
    data.push(`%${searchVal}%`);
  }
  const query =
    `select sum(T.amount) as amount from Transactions as T join Properties as P on P.id = T.propId join Tenants as TE on T.tenantId = TE.id where T.clientId =? and T.propId in (${propertiesIds}) and T.transactionFor in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ${filterCondition} `;
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getOfflineTransByClientIdAndTenantNameOrMobileForStaff = async ({
  clientId,
  searchVal,
  searchType,
  propertiesIds,
}: transactionsTypes & { searchVal: string; propertiesIds: string; searchType: number; }) => {
  const data: any = [
    clientId,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
  ];
  let filterCondition = "";
  if (Number(searchType) === 1) {
    filterCondition = " and TE.mobile like ?";
    data.push(`${searchVal}%`);
  } else if (Number(searchType) === 2) {
    filterCondition = " and TE.name like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 3) {
    filterCondition = " and T.recordedBy like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 4) {
    filterCondition = " and P.name like ?";
    data.push(`%${searchVal}%`);
  }
  const query =
    `select sum(T.amount) as amount from Transactions as T join Properties as P on P.id = T.propId join Tenants as TE on T.tenantId = TE.id where T.clientId =? and T.propId in (${propertiesIds}) and T.type = ? and T.mode = ? ${filterCondition}`;
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getOnlineTransByClientIdAndTenantNameOrMobileForStaff = async ({
  clientId,
  searchVal,
  searchType,
  propertiesIds,
}: transactionsTypes & { searchVal: string; propertiesIds: string; searchType: number; }) => {
  const data: any = [
    clientId,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_MODES.OFFLINE,
  ];
  let filterCondition = "";
  if (Number(searchType) === 1) {
    filterCondition = " and TE.mobile like ?";
    data.push(`${searchVal}%`);
  } else if (Number(searchType) === 2) {
    filterCondition = " and TE.name like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 3) {
    filterCondition = " and T.recordedBy like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 4) {
    filterCondition = " and P.name like ?";
    data.push(`%${searchVal}%`);
  }
  const query =
    `select sum(T.amount) as amount from Transactions as T join Properties as P on P.id = T.propId join Tenants as TE on T.tenantId = TE.id where T.clientId =? and T.propId in (${propertiesIds}) and T.type = ? and T.mode != ? and T.amount > 0 ${filterCondition} `;
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return [];
};

transactionDB.getTodaysCollectionByClientIdAndSearchForStaff = async ({
  clientId,
  searchVal,
  searchType,
  propertiesIds,
}: transactionsTypes & { searchVal: string; searchType: number; propertiesIds: string }) => {
  const data: any = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
  ];
  let filterCondition = "";
  if (Number(searchType) === 1) {
    filterCondition = " and TE.mobile like ?";
    data.push(`${searchVal}%`);
  } else if (Number(searchType) === 2) {
    filterCondition = " and TE.name like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 3) {
    filterCondition = " and T.recordedBy like ?";
    data.push(`%${searchVal}%`);
  } else if (Number(searchType) === 4) {
    filterCondition = " and P.name like ?";
    data.push(`%${searchVal}%`);
  }
  const query =
    `Select SUM(T.amount) as totalIncome from Transactions as T join Properties as P on P.id = T.propId join Tenants as TE on TE.id = T.tenantId where T.clientId=? and T.propId in (${propertiesIds}) and T.status=? and T.type != ? and T.amount > 0 and DATE(T.collectionDate) = DATE(now()) ${filterCondition}`;
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].totalIncome;
  else return 0;
};

transactionDB.updateHoldAmount = async ({
  id,
  holdAmount,
}: transactionsTypes) => {
  const query = "UPDATE Transactions SET holdAmount = ? where id = ?";
  const data = [holdAmount, id];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

transactionDB.tenantCollectionStats = async ({
  clientId,
  tenantId,
}: transactionsTypes) => {
  const query =
    `Select (Select SUM(amount) as totalCollection from Transactions where clientId = ? and tenantId = ?) as totalCollection,
     (Select SUM(amount) as kipinnCollection from Transactions where clientId = ? and tenantId = ? and recordedBy = ?) as kipinnCollection
    `;
  const data = [
    clientId,
    tenantId,
    clientId,
    tenantId,
    `Kipinn App`
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) {
    const total = rows[0].totalCollection ?? 0;
    const kipinn = rows[0].kipinnCollection ?? 0;
    return {
      totalCollection: total,
      kipinnCollection: kipinn,
      offlineCollection: Number(total) - Number(kipinn),
    };
  }
  else return [];
};

transactionDB.updateReceiptWithInvoice = async ({ id, receipt, invoiceNo, gstNo }: transactionsTypes) => {
  const query = "UPDATE Transactions SET receipt = ?, invoiceNo=?, gstNo = ? WHERE id = ?";
  const data = [receipt, invoiceNo, gstNo, id];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

transactionDB.updateReceiptWithInvoiceByGId = async ({ gId, receipt, invoiceNo, gstNo }: transactionsTypes) => {
  const query = "UPDATE Transactions SET receipt = ?, invoiceNo=?, gstNo = ? WHERE gId = ?";
  const data = [receipt, invoiceNo, gstNo, gId];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

transactionDB.getByGstNo = async ({ gstNo }: transactionsTypes) => {
  const query = "Select * from Transactions where gstNo = ? order by id desc limit 1";
  const data = [gstNo];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

transactionDB.getIncomeByDateRangeAndPropForClient = async ({
  clientId,
  startDate,
  endDate,
}: transactionsTypes & { startDate: any; endDate: any }) => {
  const query = `Select sum(T.amount) as totalIncome, T.propId as propId, P.name as propName from Transactions as T join Properties as P on P.id = T.propId where T.clientId = ? and T.type = ? and T.status = ? and T.transactionFor != ? and amount > 0 and Date(T.collectionDate) Between ? and ? group by T.propId`;
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

transactionDB.getSecurityByDateRangeAndPropForClient = async ({
  clientId,
  startDate,
  endDate,
}: transactionsTypes & { startDate: any; endDate: any }) => {
  const query = `Select sum(T.amount) as totalIncome, T.propId as propId, P.name as propName from Transactions as T join Properties as P on P.id = T.propId where T.clientId = ? and T.type = ? and T.status = ? and (T.transactionFor = ? or amount < 0) and Date(T.collectionDate) Between ? and ? group by T.propId`;
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

transactionDB.getIncomeByMonthAndforProp = async ({
  clientId,
  propId,
  startDate,
  endDate,
}: transactionsTypes & { startDate: any; endDate: any }) => {
  const query = `Select sum(T.amount) as totalIncome, Month(T.collectionDate) as month, Year(T.collectionDate) as year, T.propId as propId, P.name as propName from Transactions as T join Properties as P on P.id = T.propId where T.clientId = ? and T.propId = ? and T.type = ? and T.status = ? and T.transactionFor != ? and  T.amount > 0 and Date(T.collectionDate) Between ? and ? group by Year(T.collectionDate), Month(T.collectionDate), T.propId, P.name order by Year(T.collectionDate), Month(T.collectionDate);`;
  const data = [
    clientId,
    propId,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

transactionDB.getSecurityByMonthAndforProp = async ({
  clientId,
  propId,
  startDate,
  endDate,
}: transactionsTypes & { startDate: any; endDate: any }) => {
  const query = `Select sum(T.amount) as totalIncome, Month(T.collectionDate) as month, Year(T.collectionDate) as year, T.propId as propId, P.name as propName from Transactions as T join Properties as P on P.id = T.propId where T.clientId = ? and propId = ? and T.type = ? and T.status = ? and (T.transactionFor = ? or T.amount < 0) and Date(T.collectionDate) Between ? and ? group by Year(T.collectionDate), Month(T.collectionDate), T.propId, P.name order by Year(T.collectionDate), Month(T.collectionDate);`;
  const data = [
    clientId,
    propId,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_FOR.SECURITY,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

transactionDB.getByClientIdandDateRangeAndFilter = async ({
  clientId,
  startDate,
  endDate,
  limit,
  pageNum,
  recordedBy,
  transactionFor,
  mode,
  propId,
  settlement = null,
  locations = null,
  accounts = null
}: transactionsTypes & {
  startDate: string;
  endDate: string;
  limit: number;
  pageNum: number;
  recordedBy: any;
  transactionFor: any;
  mode: any;
  propId: any;
  settlement: any;
  locations: any;
  accounts: any
}) => {
  let query =
    `Select ANY_VALUE(T.id) as id, ANY_VALUE(paymentAccountNo) as paymentAccountNo, ANY_VALUE(T.paymentAccountName) as paymentAccountName, ANY_VALUE(isFinanciallyApplicable) as isFinanciallyApplicable, ANY_VALUE(charges) as charges, ANY_VALUE(T.gateway) as gateway, ANY_VALUE(T.docs) as docs, ANY_VALUE(T.remarks) as remarks, ANY_VALUE(T.title) as title, ANY_VALUE(if(T.splitTnxId is NULL, T.gId, T.splitTnxId)) as gId, ANY_VALUE(T.settleStatus) as settleStatus, ANY_VALUE(T.settledOn) as settledOn, ANY_VALUE(T.utrNo) as utrNo, ANY_VALUE(T.bankRefNum) as bankRefNum, T.amount as amount, ANY_VALUE(T.collectionDate) as collectionDate, ANY_VALUE(T.receipt) as receipt, ANY_VALUE(T.name) as name, ANY_VALUE(T.type) as type, ANY_VALUE(T.transactionFor) as transactionFor, ANY_VALUE(T.status) as status, ANY_VALUE(T.recordedBy) as recordedBy, ANY_VALUE(T.dueDate) as dueDate, ANY_VALUE(T.mode) as mode, ANY_VALUE(T.collectionDate) as createdAt, ANY_VALUE(T.updatedAt) as updatedAt, ANY_VALUE(TE.name) as tenantName, ANY_VALUE(TE.mobile) as tenantMobile, ANY_VALUE(T.propName) as propName, ANY_VALUE(T.roomNum) as roomNum, ANY_VALUE(T.tenantId) as tenantId, ANY_VALUE((select value from Documents where tenantId = T.tenantId and clientId = T.clientId and type = 4 and moveOut=0 limit 1)) as profilePicture, ANY_VALUE((select kycStatus from Occupancies where tenantId = T.tenantId and clientId = T.clientId order by id desc limit 1)) as kycStatus from Transactions as T join Properties as P on P.id = T.propId left join Tenants as TE on TE.id = T.tenantId where T.clientId = ? and T.status = ? and T.type != ? and date(T.collectionDate) BETWEEN ? and ?`;

  const offset = (pageNum - 1) * limit;
  let data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    startDate,
    endDate,
  ];

  if (recordedBy && recordedBy.length > 0) {
    query += ` and T.recordedBy in (${recordedBy.map(() => '?').join(',')})`;
    data.push(...recordedBy);
  }
  if (propId && propId.length > 0) {
    query += ` and T.propId in (${propId.map(() => '?').join(',')})`;
    data.push(...propId);
  }
  if (transactionFor && transactionFor.length > 0) {
    query += ` and T.transactionFor in (${transactionFor.map(() => '?').join(',')})`;
    data.push(...transactionFor);
  }
  if (locations && locations.length > 0) {
    query += ` and P.locationId in (${locations.map(() => '?').join(',')})`;
    data.push(...locations);
  }
  if (mode && mode.length > 0) {
    if (mode.includes('0')) {
      query += ` and (T.mode in (${mode.map(() => '?').join(',')}) OR T.recordedBy like '%Kipinn App%')`;
    } else {
      query += ` and T.mode in (${mode.map(() => '?').join(',')})`;
    }
    data.push(...mode);
  }

  if (accounts && accounts.length > 0) {
    query += ` and T.paymentAccountNo in (${accounts.map(() => '?').join(',')})`;
    data.push(...accounts);
  }
  if (settlement && settlement.length > 0) {
    if (settlement.includes('0')) {
      settlement.push('1');
      settlement.push('2');
      if (!settlement.includes('3')) {
        settlement.push('3');
      }
    }
    query += ` and (T.settleStatus in (${settlement.map(() => '?').join(',')}) and  T.recordedBy like '%Kipinn App%')`;
    data.push(...settlement);
  }

  // query += ` group by T.gId order by Max(T.collectionDate) desc, Max(T.id) desc LIMIT ?,?`
  query += ` order by T.collectionDate desc, T.id desc LIMIT ?,?`
  data.push(`${offset}`);
  data.push(`${limit}`);
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

transactionDB.getTotalByClientIdandDateRangeAndFilter = async ({
  clientId,
  startDate,
  endDate,
  recordedBy,
  transactionFor,
  mode,
  propId,
  accounts = null,
  locations = null,
}: transactionsTypes & {
  startDate: string;
  endDate: string;
  recordedBy: any;
  transactionFor: any;
  mode: any,
  propId: any,
  accounts: any,
  locations: any,
}) => {
  let query =
    `Select Sum(T.amount) as total from Transactions as T join Properties as P on P.id = T.propId where T.clientId = ? and T.isFinanciallyApplicable = 1 and T.status = ? and T.type != ? and T.amount > 0 and date(T.collectionDate) BETWEEN ? and ?`;

  let data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    startDate,
    endDate,
  ];

  if (recordedBy && recordedBy.length > 0) {
    query += ` and T.recordedBy in (${recordedBy.map(() => '?').join(',')})`;
    data.push(...recordedBy);
  }
  if (propId && propId.length > 0) {
    query += ` and T.propId in (${propId.map(() => '?').join(',')})`;
    data.push(...propId);
  }
  if (transactionFor && transactionFor.length > 0) {
    query += ` and T.transactionFor in (${transactionFor.map(() => '?').join(',')})`;
    data.push(...transactionFor);
  }
  if (mode && mode.length > 0) {
    if (mode.includes('0')) {
      query += ` and (T.mode in (${mode.map(() => '?').join(',')}) OR T.recordedBy like '%Kipinn App%')`;
    } else {
      query += ` and T.mode in (${mode.map(() => '?').join(',')})`;
    }
    data.push(...mode);
  }

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

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

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

transactionDB.getByPropIdandDateRangeAndFilterForApp = async ({
  propId,
  startDate,
  endDate,
  limit,
  pageNum,
  recordedBy,
  transactionFor,
  mode,
  settlement = null,
  locations = null,
  accounts = null,
}: transactionsTypes & {
  startDate: string;
  endDate: string;
  limit: number;
  pageNum: number;
  recordedBy: any[];
  transactionFor: any[];
  mode: any[];
  settlement: any;
  locations: any;
  accounts: any;
}) => {
  let query =
    `Select ANY_VALUE(T.id) as id, ANY_VALUE(paymentAccountNo) as paymentAccountNo, ANY_VALUE(T.paymentAccountName) as paymentAccountName, ANY_VALUE(isFinanciallyApplicable) as isFinanciallyApplicable, ANY_VALUE(charges) as charges, ANY_VALUE(T.gateway) as gateway, ANY_VALUE(T.docs) as docs, ANY_VALUE(T.remarks) as remarks, ANY_VALUE(T.title) as title, ANY_VALUE(if(T.splitTnxId is NULL, T.gId, T.splitTnxId)) as gId, ANY_VALUE(T.settleStatus) as settleStatus, ANY_VALUE(T.settledOn) as settledOn, ANY_VALUE(T.utrNo) as utrNo, ANY_VALUE(T.bankRefNum) as bankRefNum, T.amount as amount, ANY_VALUE(T.collectionDate) as collectionDate, ANY_VALUE(T.receipt) as receipt, ANY_VALUE(T.name) as name, ANY_VALUE(T.type) as type, ANY_VALUE(T.transactionFor) as transactionFor, ANY_VALUE(T.status) as status, ANY_VALUE(T.recordedBy) as recordedBy, ANY_VALUE(T.dueDate) as dueDate, ANY_VALUE(T.mode) as mode, ANY_VALUE(T.collectionDate) as createdAt, ANY_VALUE(T.updatedAt) as updatedAt, ANY_VALUE(TE.name) as tenantName, ANY_VALUE(TE.mobile) as tenantMobile, ANY_VALUE(T.propName) as propName, ANY_VALUE(T.roomNum) as roomNum, ANY_VALUE(T.tenantId) as tenantId, ANY_VALUE((select value from Documents where tenantId = T.tenantId and clientId = T.clientId and type = 4 and moveOut=0 limit 1)) as profilePicture, ANY_VALUE((select kycStatus from Occupancies where tenantId = T.tenantId and clientId = T.clientId order by id desc limit 1)) as kycStatus from Transactions as T join Properties as P on P.id = T.propId left join Tenants as TE on TE.id = T.tenantId where T.propId = ? and date(T.collectionDate) BETWEEN ? and ?`;

  const offset = (pageNum - 1) * limit;
  let data = [
    propId,
    startDate,
    endDate,
  ];

  if (recordedBy && recordedBy.length > 0) {
    query += ` and T.recordedBy in (${recordedBy.map(() => '?').join(',')})`;
    data.push(...recordedBy);
  }
  if (transactionFor && transactionFor.length > 0) {
    query += ` and T.transactionFor in (${transactionFor.map(() => '?').join(',')})`;
    data.push(...transactionFor);
  }
  if (locations && locations.length > 0) {
    query += ` and P.locationId in (${locations.map(() => '?').join(',')})`;
    data.push(...locations);
  }
  if (mode && mode.length > 0) {
    if (mode.includes('0')) {
      query += ` and (T.mode in (${mode.map(() => '?').join(',')}) OR T.recordedBy like '%Kipinn App%')`;
    } else {
      query += ` and T.mode in (${mode.map(() => '?').join(',')})`;
    }
    data.push(...mode);
  }
  if (settlement && settlement.length > 0) {
    if (settlement.includes('0')) {
      settlement.push('1');
      settlement.push('2');
      if (!settlement.includes('3')) {
        settlement.push('3');
      }
    }
    query += ` and (T.settleStatus in (${settlement.map(() => '?').join(',')}) and  T.recordedBy like '%Kipinn App%')`;
    data.push(...settlement);
  }
  if (accounts && accounts.length > 0) {
    query += ` and T.paymentAccountNo in (${accounts.map(() => '?').join(',')})`;
    data.push(...accounts);
  }

  // query += ` group by T.gId order by Max(T.collectionDate) desc, Max(T.id) desc LIMIT ?,?`
  query += ` order by T.collectionDate desc, T.id desc LIMIT ?,?`
  data.push(`${offset}`);
  data.push(`${limit}`);
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

transactionDB.getTotalByPropIdandDateRangeAndFilterForApp = async ({
  propId,
  startDate,
  endDate,
  recordedBy,
  transactionFor,
  mode,
  accounts = null
}: transactionsTypes & {
  startDate: string;
  endDate: string;
  recordedBy: any[];
  transactionFor: any[];
  mode: any[],
  accounts: any
}) => {
  let query =
    `Select SUM(T.amount) as total from Transactions as T where T.propId = ? and T.isFinanciallyApplicable = 1 and T.amount > 0 and date(T.collectionDate) BETWEEN ? and ?`;

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

  if (recordedBy && recordedBy.length > 0) {
    query += ` and T.recordedBy in (${recordedBy.map(() => '?').join(',')})`;
    data.push(...recordedBy);
  }
  if (transactionFor && transactionFor.length > 0) {
    query += ` and T.transactionFor in (${transactionFor.map(() => '?').join(',')})`;
    data.push(...transactionFor);
  }
  if (mode && mode.length > 0) {
    if (mode.includes('0')) {
      query += ` and (T.mode in (${mode.map(() => '?').join(',')}) OR T.recordedBy like '%Kipinn App%')`;
    } else {
      query += ` and T.mode in (${mode.map(() => '?').join(',')})`;
    }
    data.push(...mode);
  }

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

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

transactionDB.getForStaffByDateRangeAndFilter = async ({
  clientId,
  pageNum,
  limit,
  startDate,
  endDate,
  propertiesIds,
  recordedBy,
  transactionFor,
  mode,
  propId,
  settlement = null,
  locations = null,
  accounts = null,
}: transactionsTypes & {
  pageNum: number;
  limit: number;
  propertiesIds: string;
  startDate: string;
  endDate: string;
  recordedBy: any;
  transactionFor: any;
  mode: any;
  propId: any;
  settlement: any;
  locations: any;
  accounts: any;
}) => {
  let query =
    `Select ANY_VALUE(T.id) as id, ANY_VALUE(paymentAccountNo) as paymentAccountNo, ANY_VALUE(T.paymentAccountName) as paymentAccountName, ANY_VALUE(isFinanciallyApplicable) as isFinanciallyApplicable, ANY_VALUE(charges) as charges, ANY_VALUE(T.gateway) as gateway, ANY_VALUE(T.docs) as docs, ANY_VALUE(T.remarks) as remarks, ANY_VALUE(T.title) as title, ANY_VALUE(if(T.splitTnxId is NULL, T.gId, T.splitTnxId)) as gId, ANY_VALUE(T.settleStatus) as settleStatus, ANY_VALUE(T.settledOn) as settledOn, ANY_VALUE(T.utrNo) as utrNo, ANY_VALUE(T.bankRefNum) as bankRefNum, T.amount as amount, ANY_VALUE(T.collectionDate) as collectionDate, ANY_VALUE(T.receipt) as receipt, ANY_VALUE(T.name) as name, ANY_VALUE(T.type) as type, ANY_VALUE(T.transactionFor) as transactionFor, ANY_VALUE(T.status) as status, ANY_VALUE(T.recordedBy) as recordedBy, ANY_VALUE(T.dueDate) as dueDate, ANY_VALUE(T.mode) as mode, ANY_VALUE(T.collectionDate) as createdAt, ANY_VALUE(T.updatedAt) as updatedAt, ANY_VALUE(TE.name) as tenantName, ANY_VALUE(TE.mobile) as tenantMobile, ANY_VALUE(T.propName) as propName, ANY_VALUE(T.roomNum) as roomNum, ANY_VALUE(T.tenantId) as tenantId, ANY_VALUE((select value from Documents where tenantId = T.tenantId and clientId = T.clientId and type = 4 and moveOut=0 limit 1)) as profilePicture, ANY_VALUE((select kycStatus from Occupancies where tenantId = T.tenantId and clientId = T.clientId order by id desc limit 1)) as kycStatus from Transactions as T join Properties as P on P.id = T.propId left join Tenants as TE on TE.id = T.tenantId where T.clientId = ? and T.propId in (${propertiesIds}) and  date(T.collectionDate) Between ? and ? and T.status = ?`;
  const offset = (pageNum - 1) * limit;
  let data = [
    clientId,
    startDate,
    endDate,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
  ];

  if (recordedBy && recordedBy.length > 0) {
    query += ` and T.recordedBy in (${recordedBy.map(() => '?').join(',')})`;
    data.push(...recordedBy);
  }
  if (propId && propId.length > 0) {
    query += ` and T.propId in (${propId.map(() => '?').join(',')})`;
    data.push(...propId);
  }
  if (transactionFor && transactionFor.length > 0) {
    query += ` and T.transactionFor in (${transactionFor.map(() => '?').join(',')})`;
    data.push(...transactionFor);
  }
  if (locations && locations.length > 0) {
    query += ` and P.locationId in (${locations.map(() => '?').join(',')})`;
    data.push(...locations);
  }
  if (mode && mode.length > 0) {
    if (mode.includes('0')) {
      query += ` and (T.mode in (${mode.map(() => '?').join(',')}) OR T.recordedBy like '%Kipinn App%')`;
    } else {
      query += ` and T.mode in (${mode.map(() => '?').join(',')})`;
    }
    data.push(...mode);
  }

  if (settlement && settlement.length > 0) {
    if (settlement.includes('0')) {
      settlement.push('1');
      settlement.push('2');
      if (!settlement.includes('3')) {
        settlement.push('3');
      }
    }
    query += ` and (T.settleStatus in (${settlement.map(() => '?').join(',')}) and  T.recordedBy like '%Kipinn App%')`;
    data.push(...settlement);
  }
  if (accounts && accounts.length > 0) {
    query += ` and T.paymentAccountNo in (${accounts.map(() => '?').join(',')})`;
    data.push(...accounts);
  }

  // query += ` group by T.gId order by Max(T.collectionDate) desc, Max(T.id) desc LIMIT ?,?`
  query += ` order by T.collectionDate desc, T.id desc LIMIT ?,?`
  data.push(`${offset}`);
  data.push(`${limit}`);

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

transactionDB.getTotalByClientIdandDateRangeAndFilterForStaff = async ({
  clientId,
  startDate,
  endDate,
  recordedBy,
  transactionFor,
  mode,
  propId,
  propertiesIds,
  accounts = null,
  locations = null,
}: transactionsTypes & {
  startDate: string;
  endDate: string;
  recordedBy: any;
  transactionFor: any;
  mode: any;
  propId: any;
  propertiesIds: string;
  accounts: any;
  locations: any;
}) => {
  let query =
    `Select Sum(T.amount) as total from Transactions as T join Properties as P on P.id = T.propId where T.clientId = ? and T.isFinanciallyApplicable = 1 and T.status = ? and T.type != ? and T.propId in (${propertiesIds}) and T.amount > 0 and date(T.collectionDate) BETWEEN ? and ?`;

  let data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    startDate,
    endDate,
  ];

  if (recordedBy && recordedBy.length > 0) {
    query += ` and T.recordedBy in (${recordedBy.map(() => '?').join(',')})`;
    data.push(...recordedBy);
  }
  if (propId && propId.length > 0) {
    query += ` and T.propId in (${propId.map(() => '?').join(',')})`;
    data.push(...propId);
  }
  if (transactionFor && transactionFor.length > 0) {
    query += ` and T.transactionFor in (${transactionFor.map(() => '?').join(',')})`;
    data.push(...transactionFor);
  }
  if (mode && mode.length > 0) {
    if (mode.includes('0')) {
      query += ` and (T.mode in (${mode.map(() => '?').join(',')}) OR T.recordedBy like '%Kipinn App%')`;
    } else {
      query += ` and T.mode in (${mode.map(() => '?').join(',')})`;
    }
    data.push(...mode);
  }
  if (accounts && accounts.length > 0) {
    query += ` and T.paymentAccountNo in (${accounts.map(() => '?').join(',')})`;
    data.push(...accounts);
  }

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

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

transactionDB.updateRoomByTenantIdAndRoomId = async ({
  tenantId,
  roomId,
  newRoomId,
}: transactionsTypes & { newRoomId: number }) => {
  const query = "UPDATE Transactions SET roomId = ? where tenantId = ? and roomId = ?";
  const data = [newRoomId, tenantId, roomId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

transactionDB.getLastRentPaidByClientIdAndTenantId = async ({
  clientId,
  tenantId,
}: transactionsTypes) => {
  const query = "Select * from Transactions where clientId = ? and tenantId = ? and transactionFor = ? and type = ? and status = ? order by collectionDate desc, dueDate desc, id desc limit 1";
  const data = [
    clientId,
    tenantId,
    CONSTANTS.TRANSACTION_FOR.RENT,
    CONSTANTS.TRANSACTION_TYPES.TENANT_PAID,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

transactionDB.updateDiscount = async ({
  id,
  discount,
}: transactionsTypes) => {
  const query = `Update Transactions set discount = ? where id = ?`;
  const data = [discount, id];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

transactionDB.updateUploadedDoc = async ({
  id,
  docs,
}: transactionsTypes) => {
  const query = `Update Transactions set docs = ? where id = ?`;
  const data = [docs, id];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

transactionDB.updateCharges = async ({
  id,
  charges,
}: transactionsTypes) => {
  const query = `Update Transactions set charges = ? where id = ?`
  const data = [charges, id];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

transactionDB.updateGstCharges = async ({
  id,
  gstCharges,
}: transactionsTypes) => {
  const query = `Update Transactions set gstCharges = ? where id = ?`
  const data = [gstCharges, id];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

transactionDB.getRefundedAmountByClientIdAndDateRange = async ({
  clientId,
  startDate,
  endDate,
}: transactionsTypes & { startDate: any; endDate: any }) => {
  const query = `Select sum(T.amount) as totalRefunded from Transactions as T where T.clientId = ? and amount < 0 and Date(T.collectionDate) Between ? and ?`;
  const data = [
    clientId,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].totalRefunded;
  else return false;
};

transactionDB.getRefundedAmountByClientIdAndDateRangeForStaff = async ({
  clientId,
  propertiesIds,
  startDate,
  endDate,
}: transactionsTypes & { startDate: any; endDate: any; propertiesIds: string }) => {
  const query = `Select sum(T.amount) as totalRefunded from Transactions as T where T.clientId = ? and T.propId in (${propertiesIds}) and amount < 0 and Date(T.collectionDate) Between ? and ?`;
  const data = [
    clientId,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].totalRefunded;
  else return false;
};

transactionDB.getRefundedAmountByClientIdForWeb = async ({
  clientId,
  propIds,
  startDate,
  endDate,
}: transactionsTypes & { startDate: any; endDate: any; propIds: any }) => {
  let query = `Select sum(T.amount) as totalRefunded from Transactions as T where T.clientId = ? and amount < 0 and Date(T.collectionDate) Between ? and ?`;
  const data = [
    clientId,
    startDate,
    endDate,
  ];

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

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

transactionDB.getRefundedAmountByClientIdForWebStaff = async ({
  clientId,
  propIds,
  propertiesIds,
  startDate,
  endDate,
}: transactionsTypes & { startDate: any; endDate: any; propertiesIds: string; propIds: any }) => {
  let query = `Select sum(T.amount) as totalRefunded from Transactions as T where T.clientId = ? and T.propId in (${propertiesIds}) and amount < 0 and Date(T.collectionDate) Between ? and ?`;
  const data = [
    clientId,
    startDate,
    endDate,
  ];

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

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

transactionDB.getRefundedAmountByPropIdAndDateRange = async ({
  clientId,
  propId,
  startDate,
  endDate,
}: transactionsTypes & { startDate: any; endDate: any }) => {
  const query = `Select sum(T.amount) as totalRefunded from Transactions as T where T.clientId = ? and T.propId = ? and amount < 0 and Date(T.collectionDate) Between ? and ?`;
  const data = [
    clientId,
    propId,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].totalRefunded;
  else return false;
};

transactionDB.getAllCollectionByPropId = async ({
  propId
}: transactionsTypes) => {
  const query = `Select sum(T.amount) as total from Transactions as T where T.propId = ? and amount > 0`;
  const data = [
    propId
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].total;
  else return 0;
};

transactionDB.getAllCollectionByPropIdForCurrentMonth = async ({ propId }: transactionsTypes) => {
  const query = `SELECT SUM(T.amount) AS total FROM Transactions AS T WHERE T.propId = ? AND T.amount > 0 AND T.collectionDate >= DATE_FORMAT(CURDATE(), '%Y-%m-01') AND T.collectionDate < DATE_FORMAT(CURDATE() + INTERVAL 1 MONTH, '%Y-%m-01')`;
  const data = [propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].total || 0;
  else return 0;
};

transactionDB.getRefundedRecordsByClientIdAndDateRange = async ({
  clientId,
  startDate,
  endDate,
}: transactionsTypes & { startDate: string; endDate: string }) => {
  const query = `Select T.*, TE.name as tenantName, TE.mobile as tenantMobile, (select value from Documents where clientId = T.clientId and tenantId = T.tenantId and type = ? order by id desc limit 1) as settlementSummary from Transactions as T join Tenants as TE on T.tenantId = TE.id where T.clientId = ? and amount < 0 and DATE(T.collectionDate) BETWEEN ? and ? order by T.collectionDate desc, T.id desc`;

  const data = [
    CONSTANTS.DOCUMENT_TYPES.SETTLEMENT_SUMMARY,
    clientId,
    startDate,
    endDate,
  ];

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

transactionDB.getRefundedRecordsByClientIdAndDateRangeForStaff = async ({
  clientId,
  startDate,
  endDate,
  propertiesIds,
}: transactionsTypes & { startDate: string; endDate: string; propertiesIds: any; }) => {
  const query = `Select T.*, TE.name as tenantName, TE.mobile as tenantMobile, (select value from Documents where clientId = T.clientId and tenantId = T.tenantId and type = ? order by id desc limit 1) as settlementSummary from Transactions as T join Tenants as TE on T.tenantId = TE.id where T.clientId = ? and T.propId in (${propertiesIds}) and amount < 0 and DATE(T.collectionDate) BETWEEN ? and ? order by T.collectionDate desc, T.id desc`;

  const data = [
    CONSTANTS.DOCUMENT_TYPES.SETTLEMENT_SUMMARY,
    clientId,
    startDate,
    endDate,
  ];

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

transactionDB.getRefundedAmountByClientIdAndTenantId = async ({
  clientId,
  tenantId,
  createdAt,
}: transactionsTypes) => {
  const query = `Select IFNULL(SUM(ABS(amount)), 0) as refundedAmount from Transactions where clientId = ? and tenantId = ? and DATE(createdAt) > ? and amount < 0 and transactionFor = ?`;
  const data = [
    clientId,
    tenantId,
    createdAt,
    CONSTANTS.TRANSACTION_FOR.SETTLEMENT,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].refundedAmount;
  else return false;
};

transactionDB.deleteByClientIdAndTenantId = async ({
  clientId,
  tenantId,
}: transactionsTypes) => {
  const query = `Delete from Transactions where clientId = ? and tenantId = ?`
  const data = [clientId, tenantId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

transactionDB.getTotalByClientIdAndFilters = async ({
  clientId,
  transactionFor,
  propIds,
  startDate,
  endDate,
}: transactionsTypes & { transactionFor: any; propIds: any; startDate: string; endDate: string }) => {
  let query =
    "SELECT SUM(T.amount) as totalCollection FROM Transactions as T WHERE T.clientId = ? AND T.status = ? AND T.type != ? AND DATE(T.collectionDate) BETWEEN ? AND ?";
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    startDate,
    endDate,
  ];

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

  if (transactionFor && transactionFor.length > 0) {
    query += ` AND T.transactionFor IN (${transactionFor.map(() => '?').join(',')})`;
    data.push(...transactionFor);
  }

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

transactionDB.getTotalByClientIdAndFiltersForStaff = async ({
  clientId,
  transactionFor,
  propIds,
  startDate,
  endDate,
  propertiesIds,
}: transactionsTypes & { transactionFor: any; propIds: any; startDate: string; endDate: string; propertiesIds: string; }) => {
  let query =
    `SELECT SUM(T.amount) as totalCollection FROM Transactions as T WHERE T.clientId = ? AND T.propId in (${propertiesIds}) AND T.status = ? AND T.type != ? AND DATE(T.collectionDate) BETWEEN ? AND ?`;
  const data = [
    clientId,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    startDate,
    endDate,
  ];

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

  if (transactionFor && transactionFor.length > 0) {
    query += ` AND T.transactionFor IN (${transactionFor.map(() => '?').join(',')})`;
    data.push(...transactionFor);
  }

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

transactionDB.updateRoomAndPropForSwitch = async ({
  clientId,
  tenantId,
  type,
  propId,
  roomId,
  newPropId,
  newRoomId,
}: transactionsTypes & { newPropId: number; newRoomId: number; }) => {
  const query = `Update Transactions set propId = ?, roomId = ? where clientId = ? and tenantId = ? and type = ? and propId = ? and roomId = ?`;
  const data = [
    newPropId,
    newRoomId,
    clientId,
    tenantId,
    type,
    propId,
    roomId,
  ];

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

transactionDB.updateTallyStatusByGId = async ({ gId, tallyStatus }: transactionsTypes) => {
  const query = "update Transactions set tallyStatus = ? where gId = ? limit 1";
  const data = [tallyStatus, gId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

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

transactionDB.updateTallyBillRef = async ({ id, tallyBillRef }: transactionsTypes) => {
  const query = "update Transactions set tallyBillRef = ? where id = ? limit 1";
  const data = [tallyBillRef, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

transactionDB.updateTallyDetailsByGId = async ({ gId, tallyStatus, tallyBillRef }: transactionsTypes) => {
  const query = "update Transactions set tallyStatus = ?, tallyBillRef = ? where gId = ? limit 1";
  const data = [tallyStatus, tallyBillRef, gId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

transactionDB.syncTransactionTally = async ({ clientId }: transactionsTypes) => {
  const query = "update Transactions 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;
};


transactionDB.getOnlineAccountTransactions = async ({
  clientId,
  startDate,
  endDate,
  paymentAccountNo,
  propIds
}: transactionsTypes & { startDate: string; endDate: string, propIds: any }) => {
  let query =
    "select sum(amount) as amount from Transactions where recordedBy= 'Kipinn App' and paymentAccountNo = ? and clientId =? and type != ? and amount > 0 and DATE(collectionDate) BETWEEN ? and ?";
  let data = [
    paymentAccountNo,
    clientId,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    startDate,
    endDate,
  ];
  if (propIds && propIds.length > 0) {
    query += ` and 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 [];
};

transactionDB.getLastPaidByClientIdAndTenantId = async ({ clientId, tenantId }: transactionsTypes) => {
  const query = "select amount, collectionDate from Transactions where transactionFor =? and clientId =? and tenantId =? order by id desc limit 1";
  const data = [CONSTANTS.DUES_TYPES.RENT, clientId, tenantId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

transactionDB.getByLedgerReferenceId = async ({ ledgerReferenceId }: transactionsTypes) => {
  const query = "select * from Transactions where ledgerReferenceId =?";
  const data = [ledgerReferenceId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

transactionDB.getPaidByCountByProp = async ({
  clientId,
}: transactionsTypes) => {
  const startOfMonth = moment().startOf("month").format("YYYY-MM-DD HH:mm:ss");
  const endOfMonth = moment().endOf("month").format("YYYY-MM-DD HH:mm:ss");
  const query =
    "Select T.propId, count(distinct T.tenantId) as count from Transactions as T where T.clientId = ? and T.status=? and T.type != ? and T.amount > 0 and T.isFinanciallyApplicable = 1 and T.collectionDate BETWEEN ? and ? group by propId";
  const data = [clientId, CONSTANTS.TRANSACTION_STATUS.SUCCESS, CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED, startOfMonth, endOfMonth];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

transactionDB.getSumForReportByClientId = async ({
  clientId,
  startDate,
  endDate,
}: transactionsTypes & {startDate: string; endDate: string;}) => {
  const query = `Select T.transactionFor, SUM(T.amount) as amount from Transactions as T where T.clientId = ? and DATE(T.collectionDate) BETWEEN ? and ? and T.amount > 0 and T.isFinanciallyApplicable = 1 and T.type != ? and T.status = ? group by T.transactionFor`;

  const data = [
    clientId,
    startDate,
    endDate,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
  ];

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

transactionDB.getSumForReportByPropIds = async ({
  clientId,
  propId,
  startDate,
  endDate,
}: transactionsTypes & {startDate: string; endDate: string; propId: any[]}) => {
  const query = `Select T.transactionFor, SUM(T.amount) as amount from Transactions as T where T.clientId = ? and T.propId in (${propId.map(() => '?').join(',')}) and DATE(T.paidDate) BETWEEN ? and ? and T.amount > 0 and T.isFinanciallyApplicable = 1 and T.type != ? and T.status = ? group by T.transactionFor`;

  const data = [
    clientId,
    ...propId,
    startDate,
    endDate,
    CONSTANTS.TRANSACTION_TYPES.SECURITY_ADJUSTED,
    CONSTANTS.TRANSACTION_STATUS.SUCCESS,
  ];

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

transactionDB.updateDueDate = async ({ id, dueDate }: transactionsTypes) => {
  const query = "update Transactions set dueDate = ? where id = ?";
  const data = [
    dueDate,
    id,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
}; 

transactionDB.updateUtrOnPayOut = async ({
  id,
  settleStatus,
  utrNo,
  settledOn,
  bankRefNum,
  gateway,
}: transactionsTypes) => {
  const query =
    "UPDATE Transactions SET settleStatus = ?, utrNo = ?, settledOn = ?, bankRefNum = ?, gateway = ? where id = ?";
  const data = [settleStatus, utrNo, settledOn, bankRefNum, gateway, id];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

transactionDB.getTransByAmountTypeClientIdTenantIdAndCreatedAt = async ({ amount, clientId, tenantId, transactionFor, createdAt }: transactionsTypes) => {
  const query = "Select * from Transactions where amount=? and clientId=? and tenantId = ? and transactionFor = ? and date(createdAt) = ?";
  const data = [amount, clientId, tenantId, transactionFor, createdAt];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

transactionDB.getNumberOfTenantPaidByPropertyIdAndDateRange = async ({ 
  propId, 
  startDate, 
  endDate 
}: transactionsTypes & { startDate: string; endDate: string; }) => {
  const query = `SELECT COUNT(DISTINCT tenantId) AS count FROM Transactions WHERE amount > 0 and propId = ? AND status = ? and DATE(collectionDate) BETWEEN ? AND ?`;
  const data = [
    propId, 
    CONSTANTS.TRANSACTION_STATUS.SUCCESS, 
    startDate, 
    endDate
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0].count;
  else return 0;
};

export default transactionDB;