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

const ledgerDB: any = {};

ledgerDB.add = async ({
  tenantId,
  roomId,
  propId,
  clientId,
  amount,
  balance,
  referenceId,
  transactionId,
  type,
  rentStartDate,
  rentEndDate,
  description,
  dueDate,
  discount = 0,
  title=null,
  subType=0,
}: ledgerTypes) => {
  const query =
    "Insert into Ledgers (tenantId, roomId, propId, clientId, amount, balance, referenceId, transactionId, type, rentStartDate, rentEndDate, description, dueDate, discount, title, subType) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  const data = [
    tenantId,
    roomId,
    propId,
    clientId,
    amount,
    balance,
    referenceId,
    transactionId,
    type,
    rentStartDate,
    rentEndDate,
    description,
    dueDate,
    discount,
    title,
    subType,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

ledgerDB.getByLedgerReferenceId = async ({
  referenceId,
}: ledgerTypes) => {
  const query = "Select * from Ledgers where referenceId = ? and Date(createdAt) = curdate();";
  const data = [referenceId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

ledgerDB.getDueByLedgerReferenceId = async ({
  referenceId,
}: ledgerTypes) => {
  const query = "Select * from Ledgers where referenceId = ?";
  const data = [referenceId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

ledgerDB.getPreviousBalance = async ({ tenantId, clientId }: ledgerTypes) => {
  const query =
    "Select balance from Ledgers where tenantId = ? and clientId = ? order by id desc limit 1;";
  const data = [tenantId, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].balance;
  else return 0;
};

ledgerDB.updateBalance = async ({
  tenantId,
  clientId,
  balance,
  type,
  referenceId,
}: ledgerTypes) => {
  const query =
    "Update Ledgers set balance = ? where tenantId = ? and clientId = ? and referenceId = ? and type = ?;";
  const data = [balance, tenantId, clientId, referenceId, type];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return rows.affectedRows;
};

ledgerDB.addBalance = async ({
  tenantId,
  clientId,
  balance,
  type,
  referenceId,
}: ledgerTypes) => {
  const query =
    "Update Ledgers set balance = balance + ? where tenantId = ? and clientId = ? and referenceId = ? and type = ?;";
  const data = [balance, tenantId, clientId, referenceId, type];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return rows.affectedRows;
};

ledgerDB.updateBalanceById = async ({
  id,
  balance,
}: ledgerTypes) => {
  const query =
    "Update Ledgers set balance = ? where id = ?;";
  const data = [balance, id];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return rows.affectedRows;
};

ledgerDB.updateAmount = async ({
  tenantId,
  clientId,
  amount,
  type,
  referenceId,
}: ledgerTypes) => {
  const query =
    "Update Ledgers set amount = ? where tenantId = ? and clientId = ? and type = ? and referenceId = ? and amount > 0;";
  const data = [amount, tenantId, clientId, type, referenceId];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return rows.affectedRows;
};

//add clientId filter to solve ledger available to different clients issue
ledgerDB.getByTenantId = async ({
  tenantId,
  clientId,
  pageNum,
  limit,
}: {
  tenantId: number;
  clientId: number;
  pageNum: number;
  limit: number;
}) => {
  const query =
    "select T.receipt, T.name, T.gId, T.status, T.mode, T.propName, T.roomNum, T.collectionDate, Te.name as tenantName, L.id, L.referenceId, L.amount, L.balance, L.title, L.discount, L.transactionId, L.type, L.subType, L.dueDate, L.rentStartDate, L.rentEndDate, L.createdAt,CASE WHEN L.balance < 0 THEN 'Excess Payment' WHEN L.type = 11 THEN 'Discount' WHEN L.balance > 0 THEN 'Pending Payment' WHEN L.balance = 0 THEN 'Completed' END AS entryType, L.description from Ledgers as L INNER JOIN Tenants as Te ON Te.id = L.tenantId LEFT JOIN Transactions as T ON T.id = L.transactionId where L.tenantId = ? and L.clientId = ? order by L.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;
};

ledgerDB.getByTenantIdAsc = async ({
  tenantId,
  clientId,
  pageNum,
  limit,
}: {
  tenantId: number;
  clientId: number;
  pageNum: number;
  limit: number;
}) => {
  const query =
    "select T.receipt, T.name, T.gId, T.status, T.mode, T.propName, T.roomNum, T.collectionDate, Te.name as tenantName, L.id, L.referenceId, L.amount, L.balance, L.title, L.discount, L.transactionId, L.type, L.subType, L.dueDate, L.rentStartDate, L.rentEndDate, L.createdAt,CASE WHEN L.balance < 0 THEN 'Excess Payment' WHEN L.type = 11 THEN 'Discount' WHEN L.balance > 0 THEN 'Pending Payment' WHEN L.balance = 0 THEN 'Completed' END AS entryType, L.description from Ledgers as L INNER JOIN Tenants as Te ON Te.id = L.tenantId LEFT JOIN Transactions as T ON T.id = L.transactionId where L.tenantId = ? and L.clientId = ? order by L.id 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;
};

ledgerDB.getTotalByTenantId = async ({
  tenantId,
  clientId,
}: {
  tenantId: number;
  clientId: number;
  year: string;
}) => {
  const query =
    "select SUM(amount) as totalCollection, (select if(balance>0, 0, balance) as advancePaid from Ledgers where id IN (select max(id) from Ledgers where tenantId = ? and clientId = ?)) as  advancePaid from Ledgers where description != 'Token amount paid' and tenantId = ? and  clientId = ?;";
  const data = [tenantId, clientId, tenantId, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return 0;
};

ledgerDB.getTotalByTenantIdForMovingOut = async ({
  tenantId,
  clientId,
}: {
  tenantId: number;
  clientId: number;
}) => {
  const query =
    "select SUM(amount) as totalCollection, (select if(balance>0, 0, balance) as advancePaid from Ledgers where id IN (select max(id) from Ledgers where tenantId = ? and clientId = ?)) as  advancePaid from Ledgers where description != 'Token amount paid' and tenantId = ? and  clientId = ? and type != 2;";
  const data = [tenantId, clientId, tenantId, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return 0;
};

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

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

  query =
    "select T.receipt, T.name, T.gId, T.status, T.mode, T.propName, T.roomNum, T.collectionDate, Te.name as tenantName, L.id, L.referenceId, L.amount, L.balance, L.title, L.discount, L.transactionId, L.type, L.subType, L.dueDate, L.rentStartDate, L.rentEndDate, L.createdAt,CASE WHEN L.balance < 0 THEN 'Excess Payment' WHEN L.type = 11 THEN 'Discount' WHEN L.balance > 0 THEN 'Pending Payment' WHEN L.balance = 0 THEN 'Completed' END AS entryType, L.description from Ledgers as L INNER JOIN Tenants as Te ON Te.id = L.tenantId LEFT JOIN Transactions as T ON T.id = L.transactionId where L.tenantId = ? and L.clientId=? and (L.description like ? OR T.gId like ? OR ABS(L.amount) like ?) order by L.id Desc LIMIT ?,?";

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

ledgerDB.remove = async ({ referenceId }: ledgerTypes) => {
  const query = "Delete from Ledgers where referenceId = ?";
  const data = [referenceId];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

ledgerDB.removeByReferenceIds = async ({ referenceIds }: { referenceIds: number[] }) => {
  const query = "Delete from Ledgers where referenceId IN (?)";
  const data = [referenceIds];
  await DB.query<ResultSetHeader[]>(query, data);
  return true;
};

ledgerDB.UpdateAmountAndBalanceByReferenceId = async ({ referenceId, amount, balance }: ledgerTypes) => {
  const query = "Update Ledgers set amount = ?, balance = ? where referenceId = ? and amount > 0 limit 1";
  const data = [amount, balance, referenceId];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

ledgerDB.UpdateBalanceByReferenceIdForPaidEntry = async ({ referenceId, balance }: ledgerTypes) => {
  const query = "Update Ledgers set balance = balance - ? where referenceId = ? and amount<0";
  const data = [balance, referenceId];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

ledgerDB.getSecurityTransactionAmount = async ({
  tenantId,
  type,
  roomId,
  propId,
}: ledgerTypes) => {
  const query =
    "Select Sum(amount) as amount from Ledgers where tenantId=? and roomId=? and propId=? and type = ? and amount < 0;";
  const data = [tenantId, roomId, propId, type];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

ledgerDB.getSecurityTransactionAmountX = async ({
  tenantId,
  type,
  roomId,
  propId,
}: ledgerTypes) => {
  const query =
    "Select Sum(amount) as amount from Ledgers where tenantId=? and roomId=? and propId=? and type = ? and amount < 0 and id > (select id from Ledgers where tenantId = ? and roomId = ? and propId = ? and type = ? and amount > 0 order by id desc limit 1);";
  const data = [tenantId, roomId, propId, type, tenantId, roomId, propId, type];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

ledgerDB.removeFutureDues = async ({
  tenantId,
  clientId,
  propId,
}: ledgerTypes) => {
  //nested this query this way because of MySQL limitation that we cannot delete from a table while simultaneously selecting from the same table in a subquery, below query is a work around of that.
  const query =
    "Delete from Ledgers where tenantId = ? and clientId = ? and propId = ? and dueDate > CURDATE() and referenceId NOT IN (Select refId from ( Select referenceId as refId from Ledgers where tenantId = ? and clientId = ? and propId = ? and dueDate > CURDATE() and amount < 0) AS temp)";
  const data = [tenantId, clientId, propId, tenantId, clientId, propId];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

ledgerDB.getEvictionAffectedRecords = async ({
  tenantId,
  propId,
  roomId,
}: ledgerTypes) => {
  const query =
    "Select * from Ledgers where tenantId = ? and propId = ? and roomId = ? and amount <= 0 and description like '%eviction%'";
  const data = [tenantId, propId, roomId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

ledgerDB.removeById = async ({ id }: ledgerTypes) => {
  const query = "delete from Ledgers where id = ?;";
  const data = [id];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return rows.affectedRows;
};

ledgerDB.getDailyRentForClient = async ({ clientId, month, year }: any) => {
  const query = `
    Select DATE_FORMAT(date, '%Y-%m-%d') AS date, amount from (Select DATE(createdAt) as date, ABS(SUM(amount)) as amount from Ledgers where clientId = ? and amount < 0 and type = 1 and MONTH(createdAt) = ? and YEAR(createdAt) = ? group by DATE(createdAt)) as groupedData`;
  const data = [clientId, month, year];
  const [rows] = await DB.execute(query, data);
  return rows;
};

// ledgerDB.getLastEntryOfTenant = async ({
//   tenantId,
//   clientId,
// }: {
//   tenantId: number;
//   clientId: number;
// }) => {
//   const query =
//     "select * from Ledgers where tenantId = ? and  clientId = ? order by id desc limit 1";
//   const data = [tenantId, clientId];
//   const [rows] = await DB.execute<RowDataPacket[]>(query, data);
//   if (rows?.length > 0) return rows[0];
//   else return 0;
// };

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

ledgerDB.getLastRent = async ({
  tenantId,
  clientId,
}: ledgerTypes & {shiftingDate: any}) => {
  const query = "select * from Ledgers where tenantId = ? and clientId = ? and type = ? and amount > 0 order by id desc limit 1;";
  const data = [tenantId, clientId, CONSTANTS.DUES_TYPES.RENT];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

ledgerDB.getPaidLastRent = async ({
  tenantId,
  clientId,
}: ledgerTypes & {shiftingDate: any}) => {
  const query = "select * from Ledgers where tenantId = ? and clientId = ? and type = ? and amount < 0 order by rentStartDate desc limit 1;";
  const data = [tenantId, clientId, CONSTANTS.DUES_TYPES.RENT];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

ledgerDB.updateLastRentDue = async ({
  tenantId,
  clientId,
  referenceId,
  amount,
  balance,
  rentStartDate,
  rentEndDate,
  roomId,
  propId,
}: ledgerTypes & { dueId: string }) => {
  const query = "update Ledgers set amount = ?, balance = ?, rentStartDate = ?, rentEndDate = ?, roomId = ?, propId = ? where tenantId = ? and clientId = ? and referenceId = ? and amount > 0";
  const data = [amount, balance, rentStartDate, rentEndDate, roomId, propId, tenantId, clientId, referenceId];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

ledgerDB.updateLastEntryBalance = async ({ tenantId, clientId, amount }: ledgerTypes) => {
  const query = "update Ledgers set balance = balance + ? where tenantId = ? and clientId = ? order by id desc limit 1;";
  const data = [amount, tenantId, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

ledgerDB.removeAllEntries = async ({ tenantId, clientId }: ledgerTypes) => {
  const query = "delete from Ledgers where tenantId = ? and clientId = ?";
  const data = [tenantId, clientId];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

ledgerDB.getLastEntry = async ({ tenantId, clientId }: ledgerTypes) => {
  const query = "select * from Ledgers where tenantId = ? and clientId = ? and description like '%Refund after eviction%' order by id desc limit 1";
  const data = [tenantId, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

ledgerDB.getLastEntryX = async ({ tenantId, clientId, createdAt }: ledgerTypes) => {
  const query = "select * from Ledgers where tenantId = ? and clientId = ? and DATE(createdAt) > ? and description like '%Refund after eviction%' order by id desc limit 1";
  const data = [tenantId, clientId, createdAt];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

ledgerDB.getLatestRefundEntry = async ({ tenantId, clientId, movedOutDate }: ledgerTypes & {movedOutDate: string;}) => {
  const query = "select * from Ledgers where tenantId = ? and clientId = ? and description like '%Refund after eviction%' and Date(createdAt) > Date(?) and L.id > (select id from Ledgers where clientId = ? and tenantId = ? and description like '%Paid back%') order by id desc limit 1";
  const data = [tenantId, clientId, movedOutDate, clientId, tenantId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

ledgerDB.removeByTenantIdForMoveOut = async ({ tenantId, clientId }: ledgerTypes) => {
  const query = "Delete from Ledgers where clientId = ? and tenantId = ? and referenceId in (Select M.ledgerReferenceId from MoveOutDues as M where M.clientId = ? and M.tenantId = ? and M.amount = M.balance)";
  const data = [clientId, tenantId, clientId, tenantId];
  const [rows] = await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

// ledgerDB.discardDuesForPartialByTenantIdForMoveOut = async ({ referenceId, tenantId }: ledgerTypes) => {
//   const query = "Update Ledgers set balance = 0 where id in (select latest.id from (select id from Ledgers where referenceId = ? and tenantId = ? order by id desc limit 1) as latest);";
//   const data = [referenceId, tenantId];
//   const [rows] = await DB.execute<ResultSetHeader[]>(query, data);
//   return true;
// };

ledgerDB.settleExcessPaymentAfterEviction = async ({
  tenantId,
  clientId,
  transactionId,
  description,
}: ledgerTypes) => {
  const query = "Update Ledgers set balance = 0, description = ?, transactionId = ?, subType = ? where tenantId = ? and clientId = ? and balance < 0 and description like '%refund%' and type = ? order by id desc limit 1;";
  const data = [
    description, 
    transactionId, 
    CONSTANTS.LEDGER_SUBTYPES.REFUND, 
    tenantId, 
    clientId, 
    CONSTANTS.DUES_TYPES.SECURITY
  ];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return rows.affectedRows;
};

ledgerDB.getByTransactionId = async ({
  transactionId,
}: { transactionId: number }) => {
  const query = "Select * from Ledgers where transactionId = ?;";
  const data = [transactionId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

ledgerDB.getByMultipleTransactionIds = async ({
  transactionId,
}: { transactionId: any }) => {
  const query = `Select * from Ledgers where transactionId in (${transactionId});`;
  const [rows] = await DB.execute<RowDataPacket[]>(query);
  if (rows?.length > 0) return rows;
  else return false;
};

ledgerDB.updateEntry = async ({
  clientId,
  tenantId,
  referenceId,
  amount,
  balance,
  rentStartDate,
  rentEndDate,
  dueDate,
  description,
}: ledgerTypes) => {
  const query = "update Ledgers set amount = ?, balance = ?, rentStartDate = ?, rentEndDate = ?, dueDate = ?, description = ? where clientId = ? and tenantId = ? and referenceId = ?;";
  const data = [amount, balance, rentStartDate, rentEndDate, dueDate, description, clientId, tenantId, referenceId];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);  
  return true
};

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

ledgerDB.updateDescription = async ({
  id,
  description,
}: ledgerTypes) => {
  const query =
    `Update Ledgers set description = ? where id = ?;`;
  const data = [
    description,
    id,
  ];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return true;
};

ledgerDB.updateRoomByTenantIdAndRoomId = async ({
  tenantId,
  roomId,
  newRoomId,
}: { tenantId: number; roomId: number; newRoomId: number }) => {
  const query =
    `Update Ledgers set roomId = ? where tenantId = ? and roomId = ?;`;
  const data = [
    newRoomId,
    tenantId,
    roomId,
  ];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return true;
};

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

ledgerDB.getPaidRecordByTenantIdAndTypeAndDate = async ({
  tenantId,
  clientId,
  type,
  createdAt,
}: ledgerTypes) => {
  const query =
    "Select * from Ledgers where tenantId = ? and clientId = ? and type = ? and createdAt >= ? and amount < 0 order by id desc;";
  const data = [tenantId, clientId, type, createdAt];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

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

ledgerDB.getTenantTotalDueAmount = async ({
  tenantId,
  clientId,
}: ledgerTypes) => {
  const query =
    "Select Sum(amount) as amount from Ledgers where tenantId=? and clientId=? and amount > 0;";
  const data = [tenantId, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};


ledgerDB.getTenantTotalCollections = async ({
  tenantId,
  clientId,
}: ledgerTypes) => {
  const query =
    "Select Sum(amount) as amount from Ledgers where tenantId=? and clientId=? and amount < 0;";
  const data = [tenantId, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

ledgerDB.getTenantSecurityDue = async ({
  tenantId,
  clientId,
}: ledgerTypes) => {
  const query =
    "select amount from Ledgers where tenantId = ? and clientId = ? and type = ? and amount > 0 order by id desc limit 1";
  const data = [tenantId, clientId, CONSTANTS.DUES_TYPES.SECURITY];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

ledgerDB.getLatestSecurityPaid = async ({
  tenantId,
  clientId,
}: ledgerTypes) => {
  const query =
    "Select Sum(amount) as amount from Ledgers where tenantId=? and clientId=? and type = ? and amount < 0 and id > (select id from Ledgers where tenantId = ? and clientId = ? and type = ? and amount > 0 order by id desc limit 1);";
  const data = [tenantId, clientId, CONSTANTS.DUES_TYPES.SECURITY, tenantId, clientId, CONSTANTS.DUES_TYPES.SECURITY];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};


ledgerDB.getTenantTotalDiscount = async ({
  tenantId,
  clientId,
}: ledgerTypes) => {
  const query =
    "select sum(discount) as amount from Ledgers where tenantId = ? and clientId = ? and type = ? and amount > 0 order by id desc limit 1";
  const data = [tenantId, clientId, CONSTANTS.DUES_TYPES.RENT];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

ledgerDB.getLastRefundEntry = async ({
  tenantId,
  clientId,
  moveInDate,
}: ledgerTypes & {moveInDate: any}) => {
  const query = `select * from Ledgers where tenantId = ? and clientId = ? and Date(createdAt) > ? and description like '%Paid back%' order by id desc limit 1`;

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

ledgerDB.getLastRefundForfeitEntry = async ({
  tenantId,
  clientId,
  moveInDate,
}: ledgerTypes & {moveInDate: any}) => {
  const query = `select * from Ledgers where tenantId = ? and clientId = ? and Date(createdAt) > ? and description like '%has been forfeited%' order by id desc limit 1`;

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

ledgerDB.isBookingPaid = async ({
  clientId,
  tenantId,
  moveInDate,
}: ledgerTypes & { moveInDate: string; }) => {
  const query = `Select * from Ledgers where clientId = ? and tenantId = ? and subType = ? and DATE(createdAt) > DATE(?)`
  const data = [
    clientId,
    tenantId,
    CONSTANTS.LEDGER_SUBTYPES.BOOKING_AMT,
    moveInDate,
  ];

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

ledgerDB.getSecurityAdjustedRecordsAfterEviction = async ({
  clientId,
  tenantId,
  moveOutDate,
}: ledgerTypes & {moveOutDate: string; }) => {
  const query = `select * from Ledgers where clientId = ? and tenantId = ? and ((Date(createdAt) >= ? and description like '%Adjustment from previous overpayment%') OR description like '%Adjusted from security deposit during eviction%' OR description like '%Settled while paying back%') order by id desc`;

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

ledgerDB.getMarkedFromSecurityDues = async ({
  clientId,
  tenantId,
  moveOutDate,
}: ledgerTypes & {moveOutDate: string; }) => {
  const query = `select * from Ledgers where clientId = ? and tenantId = ? and Date(createdAt) >= ? and subType = ? order by id desc`;

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

ledgerDB.getMarkedFromSecurityDuesForFnF = async ({
  clientId,
  tenantId,
  moveInDate,
}: ledgerTypes & {moveInDate: string; }) => {
  const query = `select * from Ledgers where clientId = ? and tenantId = ? and Date(createdAt) >= ? and subType = ? and description not like '%Adjusted from security deposit during eviction%' and description not like '%Settled while paying back%' order by id desc`;

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

ledgerDB.getSecurityUsedByMonthYearForProp = async ({
  propId,
  startDate,
  endDate,
}: ledgerTypes & {startDate: string; endDate: string; }) => {
  const query = `select SUM(IFNULL(L.amount, 0)) 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,
    CONSTANTS.LEDGER_SUBTYPES.MARKED_FROM_SECURITY,
    CONSTANTS.TRANSACTION_MODES.ADJUSTED_FROM_SECURITY,
    startDate,
    endDate,
  ];
  
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);

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

ledgerDB.getUnUsedSecurityByTenantIdAndClientId = async ({
  clientId,
  tenantId,
  createdAt,
}: ledgerTypes) => {
  const query = `Select SUM(CASE when L.type = ? then ABS(L.amount) else L.amount END) as amount from Ledgers as L where L.clientId = ? and L.tenantId = ? and L.createdAt >= ? and L.amount < 0 and (L.type = ? OR L.subType = ?);`

  const data = [
    CONSTANTS.DUES_TYPES.SECURITY,
    clientId,
    tenantId,
    createdAt,
    CONSTANTS.DUES_TYPES.SECURITY,
    CONSTANTS.LEDGER_SUBTYPES.MARKED_FROM_SECURITY,
  ];

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

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

ledgerDB.getUnUsedSecurityByTenantIdsAndClientId = async ({
  clientId,
  tenantIds,
}: ledgerTypes & {tenantIds: any}) => {

  if (!tenantIds || tenantIds.length === 0) return [];
  const query = `Select L.tenantId, SUM(CASE when L.type = ? then ABS(L.amount) else L.amount END) as amount from Ledgers as L join Occupancies as O on O.tenantId = L.tenantId and O.clientId = L.clientId where L.clientId = ? and L.tenantId in (${tenantIds.map(() => '?').join(',')}) and L.createdAt >= O.createdAt and L.amount < 0 and (L.type = ? OR L.subType = ?) group by L.tenantId;`

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

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

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

ledgerDB.getUsedSecurityByTenantIdAndClientIdForFnf = async ({
  clientId,
  tenantId,
  createdAt,
  moveOutDate,
}: ledgerTypes & {moveOutDate: string;}) => {
  const query = `Select * from Ledgers as L where L.clientId = ? and L.tenantId = ? and Date(L.createdAt) >= ? and Date(L.createdAt) <= ? and L.amount < 0 and L.subType = ? and description not like "%Adjusted from security deposit during eviction%"`

  const data = [
    clientId,
    tenantId,
    createdAt,
    moveOutDate,
    CONSTANTS.LEDGER_SUBTYPES.MARKED_FROM_SECURITY,
  ];

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

  if (rows && rows.length > 0) return rows;
  else return false;
};

ledgerDB.getUsedSecurityAmtByTenantIdAndClientId = async ({
  clientId,
  tenantId,
  createdAt,
}: ledgerTypes) => {
  const query = `Select SUM(ABS(amount)) as amount from Ledgers as L where L.clientId = ? and L.tenantId = ? and L.createdAt >= ? and L.amount < 0 and L.subType = ?`

  const data = [
    clientId,
    tenantId,
    createdAt,
    CONSTANTS.LEDGER_SUBTYPES.MARKED_FROM_SECURITY,
  ];

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

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

ledgerDB.getUsedSecurityAmtByMultipleTenantIdAndClientId = async ({
  clientId,
  tenantIds,
  createdAt,
}: ledgerTypes & {tenantIds: any}) => {
  const query = `Select SUM(ABS(amount)) as amount from Ledgers as L where L.clientId = ? and L.tenantId in (${tenantIds}) and L.createdAt >= ? and L.amount < 0 and L.subType = ? group by L.tenantId`

  const data = [
    clientId,
    createdAt,
    CONSTANTS.LEDGER_SUBTYPES.MARKED_FROM_SECURITY,
  ];

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

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

ledgerDB.getAdjustedSecurityAmtByTenantIdAndClientId = async ({
  clientId,
  tenantId,
}: ledgerTypes) => {
  const query = `Select SUM(ABS(amount)) as amount from Ledgers as L where L.clientId = ? and L.tenantId = ? and L.amount < 0 and L.subType = ?`

  const data = [
    clientId,
    tenantId,
    CONSTANTS.LEDGER_SUBTYPES.MARKED_FROM_SECURITY,
  ];

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

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

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

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

ledgerDB.getCurrentMonthRentEntries = async ({
  clientId,
}: ledgerTypes) => {
  const moment = require('moment');

  const startDate = moment().startOf('month').format('YYYY-MM-DD');
  const endDate = moment().endOf('month').format('YYYY-MM-DD');

  const query = `
    SELECT tenantId, amount, rentStartDate, rentEndDate, createdAt
    FROM Ledgers
    WHERE clientId = ?
      AND type = ?
      AND amount > 0
      AND rentStartDate BETWEEN ? AND ?
  `;

  const data = [
    clientId,
    CONSTANTS.DUES_TYPES.RENT,
    startDate,
    endDate,
  ];

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

  return rows || [];
};

ledgerDB.getAdvancePaidRent = async ({
  tenantId,
  clientId,
}: ledgerTypes) => {
  const query = `Select * from Ledgers where tenantId = ? and clientId = ? and type = ? and amount < 0 and DATE(rentStartDate) > CURDATE()`;
  const data = [
    tenantId,
    clientId,
    CONSTANTS.DUES_TYPES.RENT,
  ];

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

  if (rows && rows.length > 0) return rows;
  else return false;
};

ledgerDB.getCurMonthAdvanceRentPaid = async ({
  clientId,
}: ledgerTypes) => {
  const query = `Select SUM(T.amount) as totalAdvancePaid from Ledgers as L join Transactions as T on L.referenceId = T.ledgerReferenceId where L.clientId = ? and L.type = ? and L.amount < 0 and L.rentStartDate >= DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) - 1 DAY) and L.rentStartDate <= LAST_DAY(CURDATE()) and DATE(T.collectionDate) < DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) - 1 DAY)`;
  const data = [
    clientId,
    CONSTANTS.DUES_TYPES.RENT,
  ];

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

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

ledgerDB.getCurMonthAdvanceRentPaidForStaff = async ({
  clientId,
  propertiesIds,
}: ledgerTypes & { propertiesIds: any;}) => {
  const query = `Select SUM(T.amount) as totalAdvancePaid from Ledgers as L join Transactions as T on L.referenceId = T.ledgerReferenceId where L.clientId = ? and L.propId in (${propertiesIds}) and L.type = ? and L.amount < 0 and L.rentStartDate >= DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) - 1 DAY) and L.rentStartDate <= LAST_DAY(CURDATE()) and DATE(T.collectionDate) < DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE()) - 1 DAY)`;
  const data = [
    clientId,
    CONSTANTS.DUES_TYPES.RENT,
  ];

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

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

ledgerDB.updateDueForCancelEviction = async ({
  referenceId,
  rentStartDate,
  rentEndDate,
  amount,
  balance,
  description,
}: ledgerTypes) => {
  let query = `Update Ledgers SET amount = CASE WHEN amount > 0 THEN ? ELSE amount END, balance = CASE WHEN amount > 0 THEN ? WHEN amount < 0 THEN balance + ? ELSE ? END, rentStartDate = ?, rentEndDate = ?, description = ? where referenceId = ?`;
  let data = [
    amount,
    amount,
    balance,
    balance,
    rentStartDate,
    rentEndDate,
    description,
    referenceId,
  ];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return true;
};

ledgerDB.updateDueDateByReferenceId = async ({ referenceId, dueDate }: ledgerTypes) => {
  const query = "update Ledgers set dueDate = ? where referenceId = ?";
  const data = [
    dueDate,
    referenceId,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
}; 

ledgerDB.forfeitRefund = async ({
  id,
  amount,
  balance,
  description,
}: ledgerTypes) => {
  const query = `Update Ledgers SET amount = ?, balance = ?, description = ? where id = ?`;
  const data = [
    amount,
    balance,
    description,
    id,
  ];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return true;
};

ledgerDB.getGeneratedRentByPropIdAndDateRange = async ({ 
  propId, 
  startDate, 
  endDate 
}: ledgerTypes & { startDate: string; endDate: string; }) => {
  const query = `SELECT SUM(amount) AS totalRent FROM Ledgers WHERE propId = ? AND type = ? AND DATE(rentStartDate) BETWEEN ? AND ? and amount > 0`;
  const data = [
    propId,
    CONSTANTS.DUES_TYPES.RENT,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0].totalRent;
  else return 0;
};


export default ledgerDB;