import moment from "moment";
import CONSTANTS from "../../config/constants";
import propertyDB from "../../models/property.model";
import transactionDB from "../../models/transaction.model";
import duesDB from "../../models/dues.model";
import propertiesTypes from "../../schemas/property.schema";
import createIncomeStats from "./createIncomeStats";
import clientsTypes from "../../schemas/client.schema";
import staffsTypes from "../../schemas/staff.schema";
import occupancyDB from "../../models/occupancy.model";
import { getFinancialYearDates, getLast12Months, getLast31Days, getMonthDays } from "./getYearsArray";
import log from "../../config/log";
import expenseDB from "../../models/expense.model";
import expense from "../../controllers/expense.controller";

// export const clientPendingRentDetails = async (client: clientsTypes) => {
//   const clientId = client.id;
//   const properties = await propertyDB.getPropIdsByClientId({
//     clientId,
//     status: CONSTANTS.PROPERTY_STATUS.ACTIVE,
//   });

//   let stats = [
//     {
//       totalIncome: 0,
//       totalDues: 0,
//       dueTenantCount: 0,
//       month: moment().format("M"),
//       year: moment().format("YYYY"),
//     },
//   ];

//   let dueTenantCount = 0;

//   if (properties) {

//     const transactionStats = await transactionDB.getTotalIncomeStats({
//       clientId,
//       status: CONSTANTS.TRANSACTION_STATUS.SUCCESS,
//     });

//     const duesStats = await duesDB.getTotalDuesStats({
//       clientId,
//     });

//     const isOtherThanPending = properties.find(
//       (prop: propertiesTypes) =>
//         prop.status !== CONSTANTS.PROPERTY_STATUS.PENDING
//     );

//     stats = await createIncomeStats(stats, transactionStats, duesStats);

//     for (let stat of stats) {
//       const tenantCount = await duesDB.getTenantCountYearMonth({ clientId, month: stat.month, year: stat.year });
//       dueTenantCount = tenantCount.dueTenantCount;
//       stat.dueTenantCount = dueTenantCount;
//     }
//   }

//   const data = {
//     stats,
//     dueTenantCount: Number(dueTenantCount) || 0,
//     clientStatus: client.status,
//   };
//   return data;
// };

// export const staffPendingRentDetails = async (staff: staffsTypes) => {
//   const staffId = staff.id;
//   const clientId = staff.clientId;
//   const staffLinkedProps = await propertyDB.getPropsByStaffId({
//     staffId,
//   });

//   let propertyNames: string[] = [];

//   let stats = [
//     {
//       totalIncome: 0,
//       totalDues: 0,
//       dueTenantCount: 0,
//       month: moment().format("M"),
//       year: moment().format("YYYY"),
//     },
//   ];

//   let dueTenantCount = 0;

//   if (staffLinkedProps) {

//     const propertiesIds = staffLinkedProps
//       .map((prop: propertiesTypes) => prop.id)
//       .join(",");

//     propertyNames = staffLinkedProps.map((prop: propertiesTypes) => prop.name);

//     const transactionStats = await transactionDB.getTotalIncomeStatsForStaff({
//       clientId,
//       status: CONSTANTS.TRANSACTION_STATUS.SUCCESS,
//       propertiesIds
//     });

//     const duesStats = await duesDB.getTotalDuesStatsForStaff({
//       clientId,
//       propertiesIds
//     });

//     stats = await createIncomeStats(stats, transactionStats, duesStats);

//     for (let stat of stats) {
//       const tenantCount = await duesDB.getTenantCountForStaffYearMonth({ clientId, propertiesIds, month: stat.month, year: stat.year });
//       dueTenantCount = tenantCount.dueTenantCount;
//       stat.dueTenantCount = dueTenantCount;
//     }
//   }

//   const data = {
//     stats,
//     dueTenantCount: Number(dueTenantCount) || 0,
//     clientStatus: staff.status,
//   };
//   return data;
// };

export const clientMonthlyPendingRent = async (
  client: clientsTypes
) => {

  const last12Months = getLast12Months();

  let rentCollectionData = await transactionDB.getMonthlyRentForClient({
    clientId: client.id,
    status: CONSTANTS.TRANSACTION_STATUS.SUCCESS
  });

  let onlyRentData = await transactionDB.getMonthlyRentForClientAndType({
    clientId: client.id,
    status: CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    transactionFor: CONSTANTS.TRANSACTION_FOR.RENT,
  });

  let otherData = await transactionDB.getMonthlyIncomeForClientAndType({
    clientId: client.id,
    status: CONSTANTS.TRANSACTION_STATUS.SUCCESS,
  });

  const otherDataMap: {
    [year: string]: {
      [month: string]: {
        [transactionFor: number]: number;
      };
    };
  } = {};

  for (const row of otherData) {
    const { year, month, transactionFor, amount } = row;

    if (!otherDataMap[year]) {
      otherDataMap[year] = {};
    }

    if (!otherDataMap[year][month]) {
      otherDataMap[year][month] = {};
    }

    otherDataMap[year][month][transactionFor] = amount;
  }


  let expenseData = await expenseDB.getMonthlyExpenseForClient({
    clientId: client.id,
  });

  let otherExpenseData = await expenseDB.getMonthlyExpenseForClientByCategory({
    clientId: client.id,
  });

  const otherExpenseDataMap: {
    [year: string]: {
      [month: string]: {
        [category: string]: number;
      };
    };
  } = {};

  for (const row of otherExpenseData) {
    const { year, month, category, amount } = row;

    if (!otherExpenseDataMap[year]) {
      otherExpenseDataMap[year] = {};
    }

    if (!otherExpenseDataMap[year][month]) {
      otherExpenseDataMap[year][month] = {};
    }

    otherExpenseDataMap[year][month][category] = amount;
  }

  let buildingRentExpenseData = await expenseDB.getMonthlyExpenseForClientAndCategory({
    clientId: client.id,
    category: CONSTANTS.EXPENSE_CATEGORIES.RENT_UTILITIES,
  });

  let foodExpenseData = await expenseDB.getMonthlyExpenseForClientAndCategory({
    clientId: client.id,
    category: CONSTANTS.EXPENSE_CATEGORIES.FOOD_SUPPLIES,
  });

  let staffSalaryExpenseData = await expenseDB.getMonthlyExpenseForClientAndCategory({
    clientId: client.id,
    category: CONSTANTS.EXPENSE_CATEGORIES.STAFF_WAGES,
  });

  let monthlySecurityAdjustedData = await transactionDB.getMonthlySecurityAdjustedForClient({
    clientId: client.id,
    status: CONSTANTS.TRANSACTION_STATUS.SUCCESS
  });

  let monthlySecurityData = await transactionDB.getMonthlySecurityForClient({
    clientId: client.id,
    status: CONSTANTS.TRANSACTION_STATUS.SUCCESS
  });

  let monthlyOfflineData = await transactionDB.getMonthlyOfflineTransactionsForClient({
    clientId: client.id,
  });

  let monthlyOnlineData = await transactionDB.getMonthlyOnlineTransactionsForClient({
    clientId: client.id,
  });

  let monthlyPayuData = await transactionDB.getMonthlyPayuTransactionsForClient({
    clientId: client.id,
  });

  let monthlyRefundData = await transactionDB.getMonthlyRefundForClient({
    clientId: client.id,
    status: CONSTANTS.TRANSACTION_STATUS.SUCCESS
  });

  const duesStats = await duesDB.getTotalDuesStats({
    clientId: client.id,
  });

  let monthlyDueTenantCount = [];

  for (let item of last12Months) {
    const tenantCount = await duesDB.getTenantCountYearMonth({ clientId: client.id, month: item.month, year: item.year });
    monthlyDueTenantCount.push({ dueTenantCount: tenantCount.dueTenantCount, month: item.month, year: item.year });
  }

  const stats = last12Months.map((item) => {
    const rentCollection = rentCollectionData.find(
      (d: any) => d.month == item.month && d.year == item.year
    )?.amount || 0;
    const onylRentCollection = onlyRentData.find(
      (d: any) => d.month == item.month && d.year == item.year
    )?.amount || 0;
    const expenseCollection = expenseData.find(
      (d: any) => d.month == item.month && d.year == item.year
    )?.amount || 0;
    const buildingRentExpenseCollection = buildingRentExpenseData.find(
      (d: any) => d.month == item.month && d.year == item.year
    )?.amount || 0;
    const foodExpenseCollection = foodExpenseData.find(
      (d: any) => d.month == item.month && d.year == item.year
    )?.amount || 0;
    const staffSalaryExpenseCollection = staffSalaryExpenseData.find(
      (d: any) => d.month == item.month && d.year == item.year
    )?.amount || 0;
    const totalDue = duesStats.find(
      (d: any) => d.month == item.month && d.year == item.year
    )?.totalDues || 0;
    const securityCollection = monthlySecurityData.find(
      (d: any) => d.month == item.month && d.year == item.year
    )?.amount || 0;
    const securityAdjustedCollection = monthlySecurityAdjustedData.find(
      (d: any) => d.month == item.month && d.year == item.year
    )?.amount || 0;
    const offlineCollection = monthlyOfflineData.find(
      (d: any) => d.month == item.month && d.year == item.year
    )?.amount || 0;
    const onlineCollection = monthlyOnlineData.find(
      (d: any) => d.month == item.month && d.year == item.year
    )?.amount || 0;
    const payuCollection = monthlyPayuData.find(
      (d: any) => d.month == item.month && d.year == item.year
    )?.amount || 0;
    const refund = Math.abs(monthlyRefundData.find(
      (d: any) => d.month == item.month && d.year == item.year
    )?.amount || 0);

    const dueTenantCount = monthlyDueTenantCount.find(
      (d: any) => d.month == item.month && d.year == item.year
    )?.dueTenantCount || 0;

    const yearKey = String(item.year);
    const monthKey = String(Number(item.month));

    const otherOnDate = otherDataMap[yearKey]?.[monthKey] || {};
    const otherExpenseOnDate = otherExpenseDataMap[yearKey]?.[monthKey] || {};

    return {
      //last12Months,
      dueTenantCount: dueTenantCount,
      month: item.month,
      year: item.year,
      totalIncome: Number(rentCollection),
      totalRent: Number(onylRentCollection),
      // otherIncome: Number(rentCollection) - (Number(onylRentCollection) + Number(securityCollection)),
      otherIncome: Number(rentCollection) - (Number(onylRentCollection)),
      totalExpense: Number(expenseCollection),
      totalBuildingRentExpense: Number(buildingRentExpenseCollection),
      totalFoodExpense: Number(foodExpenseCollection),
      totalStaffSalaryExpense: Number(staffSalaryExpenseCollection),
      otherExpense: Number(expenseCollection) - (Number(buildingRentExpenseCollection) + Number(foodExpenseCollection) + Number(staffSalaryExpenseCollection)),
      // netProfitLoss: Number(rentCollection) - Number(expenseCollection) - Number(refund),
      netProfitLoss: Number(rentCollection) - Number(expenseCollection),
      totalDues: Number(totalDue),
      totalSecurity: Number(securityCollection),
      totalSecurityAdjusted: Number(securityAdjustedCollection),
      totalOffline: Number(offlineCollection),
      totalOnline: Number(onlineCollection),
      totalPayu: Number(payuCollection),
      totalRefund: Number(refund),
      otherOnDate,
      otherExpenseOnDate,
    };
  });

  return { data: stats };
};

export const staffMonthlyPendingRent = async (staff: staffsTypes) => {
  const staffId = staff.id;
  const clientId = staff.clientId;
  const staffLinkedProps = await propertyDB.getPropsByStaffId({
    staffId,
  });

  //let propertyNames: string[] = [];

  if (staffLinkedProps) {

    const last12Months = getLast12Months();

    const propertiesIds = staffLinkedProps
      .map((prop: propertiesTypes) => prop.id)
      .join(",");

    //propertyNames = staffLinkedProps.map((prop: propertiesTypes) => prop.name);

    let rentCollectionData = await transactionDB.getMonthlyRentForStaff({
      clientId: clientId,
      status: CONSTANTS.TRANSACTION_STATUS.SUCCESS,
      propertiesIds
    });

    let refundData = await transactionDB.getMonthlyRefundForStaff({
      clientId: clientId,
      status: CONSTANTS.TRANSACTION_STATUS.SUCCESS,
      propertiesIds
    });

    let onlyRentData = await transactionDB.getMonthlyRentForStaffAndType({
      clientId: clientId,
      status: CONSTANTS.TRANSACTION_STATUS.SUCCESS,
      transactionFor: CONSTANTS.TRANSACTION_FOR.RENT,
      propertiesIds: propertiesIds,
    });

    let otherData = await transactionDB.getMonthlyIncomeForStaffAndType({
      clientId: clientId,
      status: CONSTANTS.TRANSACTION_STATUS.SUCCESS,
      propertiesIds: propertiesIds,
    });

    const otherDataMap: {
      [year: string]: {
        [month: string]: {
          [transactionFor: number]: number;
        };
      };
    } = {};

    for (const row of otherData) {
      const { year, month, transactionFor, amount } = row;

      if (!otherDataMap[year]) {
        otherDataMap[year] = {};
      }

      if (!otherDataMap[year][month]) {
        otherDataMap[year][month] = {};
      }

      otherDataMap[year][month][transactionFor] = amount;
    }

    let expenseData = await expenseDB.getMonthlyExpenseForStaff({
      clientId: clientId,
      propertiesIds
    });

    let otherExpenseData = await expenseDB.getMonthlyExpenseForClientByCategory({
      clientId: clientId,
      propertiesIds,
    });

    const otherExpenseDataMap: {
      [year: string]: {
        [month: string]: {
          [category: string]: number;
        };
      };
    } = {};

    for (const row of otherExpenseData) {
      const { year, month, category, amount } = row;

      if (!otherExpenseDataMap[year]) {
        otherExpenseDataMap[year] = {};
      }

      if (!otherExpenseDataMap[year][month]) {
        otherExpenseDataMap[year][month] = {};
      }

      otherExpenseDataMap[year][month][category] = amount;
    }

    let buildingRentExpenseData = await expenseDB.getMonthlyExpenseForClientAndCategoryForStaff({
      clientId: clientId,
      category: CONSTANTS.EXPENSE_CATEGORIES.RENT_UTILITIES,
      propertiesIds
    });

    let foodExpenseData = await expenseDB.getMonthlyExpenseForClientAndCategoryForStaff({
      clientId: clientId,
      category: CONSTANTS.EXPENSE_CATEGORIES.FOOD_SUPPLIES,
      propertiesIds
    });

    let staffSalaryExpenseData = await expenseDB.getMonthlyExpenseForClientAndCategoryForStaff({
      clientId: clientId,
      category: CONSTANTS.EXPENSE_CATEGORIES.STAFF_WAGES,
      propertiesIds
    });

    let monthlySecurityData = await transactionDB.getMonthlySecurityForStaff({
      clientId: clientId,
      status: CONSTANTS.TRANSACTION_STATUS.SUCCESS,
      propertiesIds
    });

    let monthlySecurityAdjustedData = await transactionDB.getMonthlySecurityAdjustedForStaff({
      clientId: clientId,
      status: CONSTANTS.TRANSACTION_STATUS.SUCCESS,
      propertiesIds
    });

    let monthlyOfflineData = await transactionDB.getMonthlyOfflineTransactionsForStaff({
      clientId: clientId,
      propertiesIds
    });

    let monthlyOnlineData = await transactionDB.getMonthlyOnlineTransactionsForStaff({
      clientId: clientId,
      propertiesIds
    });

    let monthlyPayuData = await transactionDB.getMonthlyPayuTransactionsForStaff({
      clientId: clientId,
      propertiesIds
    });

    const duesStats = await duesDB.getTotalDuesStatsForStaff({
      clientId,
      propertiesIds
    });

    let monthlyDueTenantCount = [];

    for (let item of last12Months) {
      const tenantCount = await duesDB.getTenantCountForStaffYearMonth({ clientId, propertiesIds, month: item.month, year: item.year });
      monthlyDueTenantCount.push({ dueTenantCount: tenantCount.dueTenantCount, month: item.month, year: item.year });
    }

    const stats = last12Months.map((item) => {
      const rentCollection = rentCollectionData.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.amount || 0;
      const onylRentCollection = onlyRentData.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.amount || 0;
      const expenseCollection = expenseData.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.amount || 0;
      const buildingRentExpenseCollection = buildingRentExpenseData.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.amount || 0;
      const foodExpenseCollection = foodExpenseData.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.amount || 0;
      const staffSalaryExpenseCollection = staffSalaryExpenseData.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.amount || 0;
      const totalDue = duesStats.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.totalDues || 0;
      const securityCollection = monthlySecurityData.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.amount || 0;
      const securityAdjustedCollection = monthlySecurityAdjustedData.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.amount || 0;
      const offlineCollection = monthlyOfflineData.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.amount || 0;
      const onlineCollection = monthlyOnlineData.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.amount || 0;
      const payuCollection = monthlyPayuData.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.amount || 0;
      const refund = Math.abs(refundData.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.amount || 0);

      const dueTenantCount = monthlyDueTenantCount.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.dueTenantCount || 0;

      const yearKey = String(item.year);
      const monthKey = String(Number(item.month));
      const otherOnDate = otherDataMap[yearKey]?.[monthKey] || {};
      const otherExpenseOnDate = otherExpenseDataMap[yearKey]?.[monthKey] || {};

      return {
        //last12Months,
        dueTenantCount: dueTenantCount,
        month: item.month,
        year: item.year,
        totalIncome: Number(rentCollection),
        totalRent: Number(onylRentCollection),
        // otherIncome: Number(rentCollection) - (Number(onylRentCollection) + Number(securityCollection)),
        otherIncome: Number(rentCollection) - (Number(onylRentCollection)),
        totalExpense: Number(expenseCollection),
        totalBuildingRentExpense: Number(buildingRentExpenseCollection),
        totalFoodExpense: Number(foodExpenseCollection),
        totalStaffSalaryExpense: Number(staffSalaryExpenseCollection),
        otherExpense: Number(expenseCollection) - (Number(buildingRentExpenseCollection) + Number(foodExpenseCollection) + Number(staffSalaryExpenseCollection)),
        // netProfitLoss: Number(rentCollection) - Number(expenseCollection) - Number(refund),
        netProfitLoss: Number(rentCollection) - Number(expenseCollection),
        totalDues: Number(totalDue),
        totalSecurity: Number(securityCollection),
        totalSecurityAdjusted: Number(securityAdjustedCollection),
        totalOffline: Number(offlineCollection),
        totalOnline: Number(onlineCollection),
        totalPayu: Number(payuCollection),
        totalRefund: Number(refund),
        otherOnDate,
        otherExpenseOnDate,
      };
    });
    return { data: stats };
  }
};

export const clientDailyPendingRent = async (
  client: clientsTypes
) => {

  let month = moment().month() + 1;
  let year = moment().year();

  const dates = getLast31Days();

  let rentCollectionData = await transactionDB.getDailyRentForClient({
    clientId: client.id,
  });

  let dailySecurityData = await transactionDB.getDailySecurityForClient({
    clientId: client.id,
  });

  let dailyOfflineData = await transactionDB.getDailyOfflineTransactionsForClient({
    clientId: client.id,
  });

  let dailyOnlineData = await transactionDB.getDailyOnlineTransactionsForClient({
    clientId: client.id,
  });

  let totalPendingDues = await duesDB.getDailyTotalDuesStats({
    clientId: client.id,
  });

  const stats = dates.map((item: any) => {
    const rentCollection = rentCollectionData.find(
      (d: any) => d.date == item.date
    )?.amount || 0;
    const totalDues = totalPendingDues.find(
      (d: any) => d.date == item.date
    )?.totalDues || 0;
    const securityCollection = dailySecurityData.find(
      (d: any) => d.date == item.date
    )?.amount || 0;
    const offlineCollection = dailyOfflineData.find(
      (d: any) => d.date == item.date
    )?.amount || 0;
    const onlineCollection = dailyOnlineData.find(
      (d: any) => d.date == item.date
    )?.amount || 0;
    return {
      date: item.date,
      totalIncome: Number(rentCollection),
      totalDues: Number(totalDues),
      totalSecurity: Number(securityCollection),
      totalOffline: Number(offlineCollection),
      totalOnline: Number(onlineCollection),
    };
  });

  return { data: stats };
};

export const clientDailyPendingRentForMonth = async (
  client: clientsTypes,
  month: any,
  year: any,
) => {

  // let month = moment().month() + 1;
  // let year = moment().year();

  const dates = getMonthDays(month, year);

  let rentCollectionData = await transactionDB.getDailyRentForClientYearMonth({
    clientId: client.id,
    year: year,
    month: month,
  });

  let onlyRentData = await transactionDB.getDailyRentForClientYearMonthType({
    clientId: client.id,
    year: year,
    month: month,
    transactionFor: CONSTANTS.TRANSACTION_FOR.RENT,
  });

  let otherData = await transactionDB.getDailyIncomeForClientYearMonthType({
    clientId: client.id,
    year: year,
    month: month,
  });

  const otherDataMap: Record<
    string,
    Record<number, number>
  > = {};

  otherData.forEach(({ date, amount, transactionFor }: any) => {
    if (!otherDataMap[date]) otherDataMap[date] = {};
    if (!otherDataMap[date][transactionFor]) otherDataMap[date][transactionFor] = 0;
    otherDataMap[date][transactionFor] += Number(amount);
  });

  let expenseData = await expenseDB.getDailyExpenseForClientYearMonth({
    clientId: client.id,
    year: year,
    month: month,
  });

  let otherExpenseData = await expenseDB.getDailyExpenseForClientYearMonthByCategory({
    clientId: client.id,
    year: year,
    month: month,
  });

  const otherExpenseDataMap: Record<
    string,
    Record<any, number>
  > = {};

  otherExpenseData.forEach(({ date, amount, category }: any) => {
    if (!otherExpenseDataMap[date]) otherExpenseDataMap[date] = {};
    if (!otherExpenseDataMap[date][category]) otherExpenseDataMap[date][category] = 0;
    otherExpenseDataMap[date][category] += Number(amount);
  });


  let dailySecurityAdjustedData = await transactionDB.getDailySecurityAdjustedForClientYearMonth({
    clientId: client.id,
    year: year,
    month: month,
  });

  let dailySecurityData = await transactionDB.getDailySecurityForClientYearMonth({
    clientId: client.id,
    year: year,
    month: month,
  });

  let dailyOfflineData = await transactionDB.getDailyOfflineTransactionsForClientYearMonth({
    clientId: client.id,
    year: year,
    month: month,
  });

  let dailyOnlineData = await transactionDB.getDailyOnlineTransactionsForClientYearMonth({
    clientId: client.id,
    year: year,
    month: month,
  });

  let dailyPayuData = await transactionDB.getDailyPayuTransactionsForClientYearMonth({
    clientId: client.id,
    year: year,
    month: month,
  });

  let totalPendingDues = await duesDB.getDailyTotalDuesStats({
    clientId: client.id,
  });

  let refundData = await transactionDB.getDailyRefundForClientYearMonth({
    clientId: client.id,
    year: year,
    month: month,
  });

  const stats = dates.map((item: any) => {
  const rentCollection = rentCollectionData.find((d: any) => d.date === item.date)?.amount || 0;
  const onylRentCollection = onlyRentData.find((d: any) => d.date === item.date)?.amount || 0;
  const expenseCollection = expenseData.find((d: any) => d.date === item.date)?.amount || 0;
  // const buildingRentExpenseCollection = buildingRentExpenseData.find((d: any) => d.date === item.date)?.amount || 0;
  // const foodExpenseCollection = foodExpenseData.find((d: any) => d.date === item.date)?.amount || 0;
  // const staffSalaryExpenseCollection = staffSalaryExpenseData.find((d: any) => d.date === item.date)?.amount || 0;
  const totalDues = totalPendingDues.find((d: any) => d.date === item.date)?.totalDues || 0;
  const securityCollection = dailySecurityData.find((d: any) => d.date === item.date)?.amount || 0;
  const securityAdjustedCollection = dailySecurityAdjustedData.find((d: any) => d.date === item.date)?.amount || 0;
  const offlineCollection = dailyOfflineData.find((d: any) => d.date === item.date)?.amount || 0;
  const onlineCollection = dailyOnlineData.find((d: any) => d.date === item.date)?.amount || 0;
  const payuCollection = dailyPayuData.find((d: any) => d.date === item.date)?.amount || 0;
  const refund = Math.abs(refundData.find((d: any) => d.date === item.date)?.amount || 0);

  const otherOnDate = otherDataMap[item.date] || {};
  const otherExpenseOnDate = otherExpenseDataMap[item.date] || {};
  return {
    date: item.date,
    totalIncome: Number(rentCollection),
    totalRent: Number(onylRentCollection),
    otherIncome: Number(rentCollection) - Number(onylRentCollection),
    totalExpense: Number(expenseCollection),
    // totalBuildingRentExpense: Number(buildingRentExpenseCollection),
    // totalFoodExpense: Number(foodExpenseCollection),
    // totalStaffSalaryExpense: Number(staffSalaryExpenseCollection),
    // otherExpense: Number(expenseCollection) - (Number(buildingRentExpenseCollection) + Number(foodExpenseCollection) + Number(staffSalaryExpenseCollection)),
    netProfitLoss: Number(rentCollection) - Number(expenseCollection),
    totalDues: Number(totalDues),
    totalSecurity: Number(securityCollection),
    totalSecurityAdjusted: Number(securityAdjustedCollection),
    totalOffline: Number(offlineCollection),
    totalOnline: Number(onlineCollection),
    totalPayu: Number(payuCollection),
    totalRefund: Number(refund),
    otherOnDate,
    otherExpenseOnDate,
  };
  }).filter((entry) => {
    const isFuture = moment(entry.date).isAfter(moment(), 'day');
    const allZero = Object.entries(entry).every(([key, val]) => {
      if (key === 'date') return true;
      if (typeof val === 'object') return Object.keys(val).length <= 0;
      return val === 0;
    });
    return !(isFuture && allZero);
  }).reverse();

  return { data: stats };
};

export const staffDailyPendingRent = async (
  staff: staffsTypes
) => {

  let month = moment().month() + 1;
  let year = moment().year();

  const dates = getLast31Days();

  const staffId = staff.id;
  const clientId = staff.clientId;
  const staffLinkedProps = await propertyDB.getPropsByStaffId({
    staffId,
  });

  //let propertyNames: string[] = [];

  if (staffLinkedProps) {

    const propertiesIds = staffLinkedProps
      .map((prop: propertiesTypes) => prop.id)
      .join(",");

    let rentCollectionData = await transactionDB.getDailyRentForStaff({
      clientId: clientId,
      propertiesIds
    });

    let dailySecurityData = await transactionDB.getDailySecurityForStaff({
      clientId: clientId,
      propertiesIds
    });

    let dailyOfflineData = await transactionDB.getDailyOfflineTransactionsForStaff({
      clientId: clientId,
      propertiesIds
    });

    let dailyOnlineData = await transactionDB.getDailyOnlineTransactionsForStaff({
      clientId: clientId,
      propertiesIds
    });
    //Sukhbir - Pending 27th Jan 2025
    let totalPendingDues = await duesDB.getDailyTotalDuesStatsForStaff({
      clientId: clientId,
      propertiesIds
    });

    const stats = dates.map((item: any) => {
      const rentCollection = rentCollectionData.find(
        (d: any) => d.date == item.date
      )?.amount || 0;
      const totalDues = totalPendingDues.find(
        (d: any) => d.date == item.date
      )?.totalDues || 0;
      const securityCollection = dailySecurityData.find(
        (d: any) => d.date == item.date
      )?.amount || 0;
      const offlineCollection = dailyOfflineData.find(
        (d: any) => d.date == item.date
      )?.amount || 0;
      const onlineCollection = dailyOnlineData.find(
        (d: any) => d.date == item.date
      )?.amount || 0;
      return {
        date: item.date,
        totalIncome: Number(rentCollection),
        totalDues: Number(totalDues),
        totalSecurity: Number(securityCollection),
        totalOffline: Number(offlineCollection),
        totalOnline: Number(onlineCollection),
      };
    });

    return { data: stats };
  }
};

export const staffDailyPendingRentForMonth = async (
  staff: staffsTypes,
  year: any,
  month: any,
) => {

  // let month = moment().month() + 1;
  // let year = moment().year();

  const dates = getMonthDays(month, year);

  const staffId = staff.id;
  const clientId = staff.clientId;
  const staffLinkedProps = await propertyDB.getPropsByStaffId({
    staffId,
  });

  //let propertyNames: string[] = [];

  if (staffLinkedProps) {

    const propertiesIds = staffLinkedProps
      .map((prop: propertiesTypes) => prop.id)
      .join(",");

    let rentCollectionData = await transactionDB.getDailyRentForStaffYearMonth({
      clientId: clientId,
      propertiesIds,
      year,
      month,
    });

    let refundData = await transactionDB.getDailyRefundForStaffYearMonth({
      clientId: clientId,
      propertiesIds,
      year,
      month,
    });

    let onlyRentData = await transactionDB.getDailyRentForStaffYearMonthType({
      clientId: clientId,
      propertiesIds: propertiesIds,
      year: year,
      month: month,
      transactionFor: CONSTANTS.TRANSACTION_FOR.RENT,
    });

    let otherData = await transactionDB.getDailyIncomeForStaffYearMonthType({
      clientId: clientId,
      propertiesIds: propertiesIds,
      year: year,
      month: month,
    });

    const otherDataMap: Record<
      string,
      Record<number, number>
    > = {};

    otherData.forEach(({ date, amount, transactionFor }: any) => {
      if (!otherDataMap[date]) otherDataMap[date] = {};
      if (!otherDataMap[date][transactionFor]) otherDataMap[date][transactionFor] = 0;
      otherDataMap[date][transactionFor] += Number(amount);
    });

    let expenseData = await expenseDB.getDailyExpenseForStaffYearMonth({
      clientId: clientId,
      propertiesIds,
      year,
      month,
    });

    let otherExpenseData = await expenseDB.getDailyExpenseForStaffYearMonthByCategory({
      clientId: clientId,
      propertiesIds,
      year,
      month,
    });

    const otherExpenseDataMap: Record<
      string,
      Record<any, number>
    > = {};

    otherExpenseData.forEach(({ date, amount, category }: any) => {
      if (!otherExpenseDataMap[date]) otherExpenseDataMap[date] = {};
      if (!otherExpenseDataMap[date][category]) otherExpenseDataMap[date][category] = 0;
      otherExpenseDataMap[date][category] += Number(amount);
    });

    let dailySecurityData = await transactionDB.getDailySecurityForStaffYearMonth({
      clientId: clientId,
      propertiesIds,
      year,
      month,
    });

    let dailySecurityAdjustedData = await transactionDB.getDailySecurityAdjustedForStaffYearMonth({
      clientId: clientId,
      propertiesIds,
      year,
      month,
    });

    let dailyOfflineData = await transactionDB.getDailyOfflineTransactionsForStaffYearMonth({
      clientId: clientId,
      propertiesIds,
      year,
      month,
    });

    let dailyOnlineData = await transactionDB.getDailyOnlineTransactionsForStaffYearMonth({
      clientId: clientId,
      propertiesIds,
      year,
      month,
    });

    let dailyPayuData = await transactionDB.getDailyPayuTransactionsForStaffYearMonth({
      clientId: clientId,
      propertiesIds,
      year,
      month,
    });
    //Sukhbir - Pending 27th Jan 2025
    let totalPendingDues = await duesDB.getDailyTotalDuesStatsForStaff({
      clientId: clientId,
      propertiesIds
    });

    const stats = dates.map((item: any) => {
      const rentCollection = rentCollectionData.find(
        (d: any) => d.date == item.date
      )?.amount || 0;
      const onylRentCollection = onlyRentData.find(
        (d: any) => d.date == item.date
      )?.amount || 0;
      const expenseCollection = expenseData.find(
        (d: any) => d.date == item.date
      )?.amount || 0;
      const totalDues = totalPendingDues.find(
        (d: any) => d.date == item.date
      )?.totalDues || 0;
      const securityCollection = dailySecurityData.find(
        (d: any) => d.date == item.date
      )?.amount || 0;
      const securityAdjustedCollection = dailySecurityAdjustedData.find(
        (d: any) => d.date == item.date
      )?.amount || 0;
      const offlineCollection = dailyOfflineData.find(
        (d: any) => d.date == item.date
      )?.amount || 0;
      const onlineCollection = dailyOnlineData.find(
        (d: any) => d.date == item.date
      )?.amount || 0;
      const payuCollection = dailyPayuData.find(
        (d: any) => d.date == item.date
      )?.amount || 0;
      const refund = Math.abs(refundData.find(
        (d: any) => d.date == item.date
      )?.amount || 0);
      
      const otherOnDate = otherDataMap[item.date] || {};
      const otherExpenseOnDate = otherExpenseDataMap[item.date] || {};
      return {
        date: item.date,
        totalIncome: Number(rentCollection),
        totalRent: Number(onylRentCollection),
        // otherIncome: Number(rentCollection) - (Number(onylRentCollection) + Number(securityCollection)),
        otherIncome: Number(rentCollection) - (Number(onylRentCollection)),
        totalExpense: Number(expenseCollection),
        // netProfitLoss: Number(rentCollection) - Number(expenseCollection) - Number(refund), 
        netProfitLoss: Number(rentCollection) - Number(expenseCollection), 
        totalDues: Number(totalDues),
        totalSecurity: Number(securityCollection),
        totalSecurityAdjusted: Number(securityAdjustedCollection),
        totalOffline: Number(offlineCollection),
        totalOnline: Number(onlineCollection),
        totalPayu: Number(payuCollection),
        totalRefund: Number(refund),
        otherOnDate,
        otherExpenseOnDate,
      };
    }).filter((entry) => {
      const isFuture = moment(entry.date).isAfter(moment(), 'day');
      const allZero = Object.entries(entry).every(([key, val]) => {
        if (key === 'date') return true;
        if (typeof val === 'object') return Object.keys(val).length <= 0;
        return val === 0;
      });
      return !(isFuture && allZero);
    }).reverse();

    return { data: stats };
  }
};

export const propertyMonthlyPendingRent = async ({propId}: {propId: number}) => {

    const last12Months = getLast12Months();


    let rentCollectionData = await transactionDB.getMonthlyRentByPropId({
      propId: propId,
      status: CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    });

    let refundData = await transactionDB.getMonthlyRefundByPropId({
      propId: propId,
      status: CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    });

    let onlyRentData = await transactionDB.getMonthlyRentByPropIdAndType({
      propId: propId,
      status: CONSTANTS.TRANSACTION_STATUS.SUCCESS,
      transactionFor: CONSTANTS.TRANSACTION_FOR.RENT,
    });

    let otherData = await transactionDB.getMonthlyIncomeByPropIdAndType({
      propId: propId,
      status: CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    });

    const otherDataMap: {
      [year: string]: {
        [month: string]: {
          [transactionFor: number]: number;
        };
      };
    } = {};

    for (const row of otherData) {
      const { year, month, transactionFor, amount } = row;

      if (!otherDataMap[year]) {
        otherDataMap[year] = {};
      }

      if (!otherDataMap[year][month]) {
        otherDataMap[year][month] = {};
      }

      otherDataMap[year][month][transactionFor] = amount;
    }

    let expenseData = await expenseDB.getMonthlyExpenseByPropId({
      propId: propId,
      status: CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    });

    let otherExpenseData = await expenseDB.getMonthlyExpenseByPropIdCategory({
      propId: propId,
    });

    const otherExpenseDataMap: {
      [year: string]: {
        [month: string]: {
          [category: string]: number;
        };
      };
    } = {};

    for (const row of otherExpenseData) {
      const { year, month, category, amount } = row;

      if (!otherExpenseDataMap[year]) {
        otherExpenseDataMap[year] = {};
      }

      if (!otherExpenseDataMap[year][month]) {
        otherExpenseDataMap[year][month] = {};
      }

      otherExpenseDataMap[year][month][category] = amount;
    }

    let buildingRentExpenseData = await expenseDB.getMonthlyExpenseByPropIdAndCategory({
      propId: propId,
      category: CONSTANTS.EXPENSE_CATEGORIES.RENT_UTILITIES,
    });

    let foodExpenseData = await expenseDB.getMonthlyExpenseByPropIdAndCategory({
      propId: propId,
      category: CONSTANTS.EXPENSE_CATEGORIES.FOOD_SUPPLIES,
    });

    let staffSalaryExpenseData = await expenseDB.getMonthlyExpenseByPropIdAndCategory({
      propId: propId,
      category: CONSTANTS.EXPENSE_CATEGORIES.STAFF_WAGES,
    });

    let monthlySecurityData = await transactionDB.getMonthlySecurityByPropId({
      propId: propId,
      status: CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    });

    let monthlySecurityAdjustedData = await transactionDB.getMonthlySecurityAdjustedByPropId({
      propId: propId,
      status: CONSTANTS.TRANSACTION_STATUS.SUCCESS,
    });

    let monthlyOfflineData = await transactionDB.getMonthlyOfflineTransactionsByPropId({
      propId: propId,
    });

    let monthlyOnlineData = await transactionDB.getMonthlyOnlineTransactionsByPropId({
      propId: propId,
    });

    let monthlyPayuData = await transactionDB.getMonthlyPayuTransactionsByPropId({
      propId: propId,
    });

    const duesStats = await duesDB.getTotalDuesStatsByPropId({
      propId,
    });

    let monthlyDueTenantCount = [];

    for (let item of last12Months) {
      const tenantCount = await duesDB.getTenantCountByPropIdYearMonth({ propId, month: item.month, year: item.year });
      monthlyDueTenantCount.push({ dueTenantCount: tenantCount.dueTenantCount, month: item.month, year: item.year });
    }

    const stats = last12Months.map((item) => {
      const rentCollection = rentCollectionData.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.amount || 0;
      const onylRentCollection = onlyRentData.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.amount || 0;
      const expenseCollection = expenseData.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.amount || 0;
      const buildingRentExpenseCollection = buildingRentExpenseData.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.amount || 0;
      const foodExpenseCollection = foodExpenseData.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.amount || 0;
      const staffSalaryExpenseCollection = staffSalaryExpenseData.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.amount || 0;
      const totalDue = duesStats.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.totalDues || 0;
      const securityCollection = monthlySecurityData.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.amount || 0;
      const securityAdjustedCollection = monthlySecurityAdjustedData.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.amount || 0;
      const offlineCollection = monthlyOfflineData.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.amount || 0;
      const onlineCollection = monthlyOnlineData.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.amount || 0;
      const payuCollection = monthlyPayuData.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.amount || 0;
      const refund = Math.abs(refundData.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.amount || 0);

      const dueTenantCount = monthlyDueTenantCount.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.dueTenantCount || 0;

      const yearKey = String(item.year);
      const monthKey = String(Number(item.month));

      const otherOnDate = otherDataMap[yearKey]?.[monthKey] || {};
      const otherExpenseOnDate = otherExpenseDataMap[yearKey]?.[monthKey] || {};

      return {
        dueTenantCount: dueTenantCount,
        month: item.month,
        year: item.year,
        totalIncome: Number(rentCollection),
        totalRent: Number(onylRentCollection),
        // otherIncome: Number(rentCollection) - (Number(onylRentCollection) + Number(securityCollection)),
        otherIncome: Number(rentCollection) - (Number(onylRentCollection)),
        totalExpense: Number(expenseCollection),
        totalBuildingRentExpense: Number(buildingRentExpenseCollection),
        totalFoodExpense: Number(foodExpenseCollection),
        totalStaffSalaryExpense: Number(staffSalaryExpenseCollection),
        otherExpense: Number(expenseCollection) - (Number(buildingRentExpenseCollection) + Number(foodExpenseCollection) + Number(staffSalaryExpenseCollection)),
        // netProfitLoss: Number(rentCollection) - Number(expenseCollection) - Number(refund),
        netProfitLoss: Number(rentCollection) - Number(expenseCollection),
        totalDues: Number(totalDue),
        totalSecurity: Number(securityCollection),
        totalSecurityAdjusted: Number(securityAdjustedCollection),
        totalOffline: Number(offlineCollection),
        totalOnline: Number(onlineCollection),
        totalPayu: Number(payuCollection),
        totalRefund: Number(refund),
        otherOnDate,
        otherExpenseOnDate,
      };
    });
    return { data: stats };
};

export const propertyDailyPendingRent = async ({propId}: {propId: number}) => {

  const dates = getLast31Days();

  let rentCollectionData = await transactionDB.getDailyRentByPropId({
    propId: propId,
    status: CONSTANTS.TRANSACTION_STATUS.SUCCESS,
  });

  let securityData = await transactionDB.getDailySecurityByPropId({
    propId: propId,
    status: CONSTANTS.TRANSACTION_STATUS.SUCCESS,
  });

  let offlineData = await transactionDB.getDailyOfflineTransactionsByPropId({
    propId: propId,
  });

  let onlineData = await transactionDB.getDailyOnlineTransactionsByPropId({
    propId: propId,
  });

  const duesStats = await duesDB.getDailyTotalDuesStatsByPropId({
    propId,
  });


  const stats = dates.map((item: any) => {
    const rentCollection = rentCollectionData.find(
      (d: any) => d.date == item.date
    )?.amount || 0;
    const totalDue = duesStats.find(
      (d: any) => d.date == item.date
    )?.totalDues || 0;
    const securityCollection = securityData.find(
      (d: any) => d.date == item.date
    )?.amount || 0;
    const offlineCollection = offlineData.find(
      (d: any) => d.date == item.date
    )?.amount || 0;
    const onlineCollection = onlineData.find(
      (d: any) => d.date == item.date
    )?.amount || 0;
    return {
      date: item.date,
      totalIncome: Number(rentCollection),
      totalDues: Number(totalDue),
      totalSecurity: Number(securityCollection),
      totalOffline: Number(offlineCollection),
      totalOnline: Number(onlineCollection),
    };
  });

  return { data: stats };
};

export const propertyDailyPendingRentForMonth = async ({propId, year, month}: {propId: number; year: any; month: any}) => {

  const dates = getMonthDays(month, year);

  let rentCollectionData = await transactionDB.getDailyRentByPropIdYearMonth({
    propId: propId,
    year,
    month,
  });

  let refundData = await transactionDB.getDailyRefundByPropIdYearMonth({
    propId: propId,
    year,
    month,
  });

  let onlyRentData = await transactionDB.getDailyRentByPropIdYearMonthType({
    propId: propId,
    year,
    month,
    transactionFor: CONSTANTS.TRANSACTION_FOR.RENT,
  });

  let otherData = await transactionDB.getDailyIncomeByPropIdYearMonthType({
    propId: propId,
    year: year,
    month: month,
  });

  const otherDataMap: Record<
    string,
    Record<number, number>
  > = {};

  otherData.forEach(({ date, amount, transactionFor }: any) => {
    if (!otherDataMap[date]) otherDataMap[date] = {};
    if (!otherDataMap[date][transactionFor]) otherDataMap[date][transactionFor] = 0;
    otherDataMap[date][transactionFor] += Number(amount);
  });

  let expenseData = await expenseDB.getDailyExpenseByPropIdYearMonth({
    propId: propId,
    year,
    month,
  });

  let otherExpenseData = await expenseDB.getDailyExpenseByPropIdYearMonthCategory({
    propId: propId,
    year: year,
    month: month,
  });

  const otherExpenseDataMap: Record<
    string,
    Record<any, number>
  > = {};

  otherExpenseData.forEach(({ date, amount, category }: any) => {
    if (!otherExpenseDataMap[date]) otherExpenseDataMap[date] = {};
    if (!otherExpenseDataMap[date][category]) otherExpenseDataMap[date][category] = 0;
    otherExpenseDataMap[date][category] += Number(amount);
  });

  let securityData = await transactionDB.getDailySecurityByPropIdYearMonth({
    propId: propId,
    year,
    month,
  });

  let securityAdjustedData = await transactionDB.getDailySecurityAdjustedByPropIdYearMonth({
    propId: propId,
    year,
    month,
  });

  let offlineData = await transactionDB.getDailyOfflineTransactionsByPropIdYearMonth({
    propId: propId,
    year,
    month,
  });

  let onlineData = await transactionDB.getDailyOnlineTransactionsByPropIdYearMonth({
    propId: propId,
    year,
    month,
  });

  let payuData = await transactionDB.getDailyPayuTransactionsByPropIdYearMonth({
    propId: propId,
    year,
    month,
  });

  const duesStats = await duesDB.getDailyTotalDuesStatsByPropId({
    propId,
  });


  const stats = dates.map((item: any) => {
    const rentCollection = rentCollectionData.find((d: any) => d.date === item.date)?.amount || 0;
    const onlyRentCollection = onlyRentData.find((d: any) => d.date === item.date)?.amount || 0;
    const expenseCollection = expenseData.find((d: any) => d.date === item.date)?.amount || 0;
    const totalDue = duesStats.find((d: any) => d.date === item.date)?.totalDues || 0;
    const securityCollection = securityData.find((d: any) => d.date === item.date)?.amount || 0;
    const securityAdjustedCollection = securityAdjustedData.find((d: any) => d.date === item.date)?.amount || 0;
    const offlineCollection = offlineData.find((d: any) => d.date === item.date)?.amount || 0;
    const onlineCollection = onlineData.find((d: any) => d.date === item.date)?.amount || 0;
    const payuCollection = payuData.find((d: any) => d.date === item.date)?.amount || 0;
    const refund = Math.abs(refundData.find((d: any) => d.date === item.date)?.amount || 0);

    const otherOnDate = otherDataMap[item.date] || {};
    const otherExpenseOnDate = otherExpenseDataMap[item.date] || {};

    return {
      date: item.date,
      totalIncome: Number(rentCollection),
      totalRent: Number(onlyRentCollection),
      otherIncome: Number(rentCollection) - Number(onlyRentCollection),
      totalExpense: Number(expenseCollection),
      netProfitLoss: Number(rentCollection) - Number(expenseCollection),
      totalDues: Number(totalDue),
      totalSecurity: Number(securityCollection),
      totalSecurityAdjusted: Number(securityAdjustedCollection),
      totalOffline: Number(offlineCollection),
      totalOnline: Number(onlineCollection),
      totalPayu: Number(payuCollection),
      totalRefund: Number(refund),
      otherOnDate,
      otherExpenseOnDate,
    };
  }).filter((entry) => {
    const isFuture = moment(entry.date).isAfter(moment(), 'day');
    const allZero = Object.entries(entry).every(([key, val]) => {
      if (key === 'date') return true;
      if (typeof val === 'object') return Object.keys(val).length <= 0;
      return val === 0;
    });
    return !(isFuture && allZero);
  }).reverse();
  
  return { data: stats };
};

export const propertyCurrentFYPendingRent = async ({propId}: {propId: number}) => {

  const {startDate, endDate} = getFinancialYearDates();

  const { amount: totalYearlyRent } = await transactionDB.getRentByPropIdandDateRange({
    propId: propId,
    startDate,
    endDate,
  });

  const { amount: totalYearlySecurity } = await transactionDB.getSecurityByPropIdandDateRange({
    propId: propId,
    startDate,
    endDate,
  });

  return { totalYearlyRent, totalYearlySecurity };
};

export const propertiesPendingRentForCurMonth = async ({propertiesIds}: {propertiesIds: any}) => {

    const last12Months = getLast12Months();
    const data = [];

    for (let propId of propertiesIds) {
      let rentCollectionData = await transactionDB.getMonthlyRentByPropId({
        propId: propId,
        status: CONSTANTS.TRANSACTION_STATUS.SUCCESS,
      });
  
      let onlyRentData = await transactionDB.getMonthlyRentByPropIdAndType({
        propId: propId,
        status: CONSTANTS.TRANSACTION_STATUS.SUCCESS,
        transactionFor: CONSTANTS.TRANSACTION_FOR.RENT,
      });
  
      let expenseData = await expenseDB.getMonthlyExpenseByPropId({
        propId: propId,
        status: CONSTANTS.TRANSACTION_STATUS.SUCCESS,
      });
  
      let buildingRentExpenseData = await expenseDB.getMonthlyExpenseByPropIdAndCategory({
        propId: propId,
        category: CONSTANTS.EXPENSE_CATEGORIES.RENT_UTILITIES,
      });
  
      let foodExpenseData = await expenseDB.getMonthlyExpenseByPropIdAndCategory({
        propId: propId,
        category: CONSTANTS.EXPENSE_CATEGORIES.FOOD_SUPPLIES,
      });
  
      let staffSalaryExpenseData = await expenseDB.getMonthlyExpenseByPropIdAndCategory({
        propId: propId,
        category: CONSTANTS.EXPENSE_CATEGORIES.STAFF_WAGES,
      });
  
      let monthlySecurityData = await transactionDB.getMonthlySecurityByPropId({
        propId: propId,
        status: CONSTANTS.TRANSACTION_STATUS.SUCCESS,
      });
  
      let monthlyOfflineData = await transactionDB.getMonthlyOfflineTransactionsByPropId({
        propId: propId,
      });
  
      let monthlyOnlineData = await transactionDB.getMonthlyOnlineTransactionsByPropId({
        propId: propId,
      });
  
      let monthlyPayuData = await transactionDB.getMonthlyPayuTransactionsByPropId({
        propId: propId,
      });
  
      const duesStats = await duesDB.getTotalDuesStatsByPropId({
        propId: propId,
      });
  
      let monthlyDueTenantCount = [];
  
      for (let item of last12Months) {
        const tenantCount = await duesDB.getTenantCountByPropIdYearMonth({ propId: propId, month: item.month, year: item.year });
        monthlyDueTenantCount.push({ dueTenantCount: tenantCount.dueTenantCount, month: item.month, year: item.year });
      }
  
      const currentMonth = moment().month() + 1;
      const currentYear = moment().year();

      const item = { month: currentMonth, year: currentYear };

      const rentCollection = rentCollectionData.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.amount || 0;

      const onylRentCollection = onlyRentData.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.amount || 0;

      const expenseCollection = expenseData.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.amount || 0;

      const buildingRentExpenseCollection = buildingRentExpenseData.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.amount || 0;

      const foodExpenseCollection = foodExpenseData.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.amount || 0;

      const staffSalaryExpenseCollection = staffSalaryExpenseData.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.amount || 0;

      const totalDue = duesStats.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.totalDues || 0;

      const securityCollection = monthlySecurityData.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.amount || 0;

      const offlineCollection = monthlyOfflineData.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.amount || 0;

      const onlineCollection = monthlyOnlineData.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.amount || 0;

      const payuCollection = monthlyPayuData.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.amount || 0;

      const dueTenantCount = monthlyDueTenantCount.find(
        (d: any) => d.month == item.month && d.year == item.year
      )?.dueTenantCount || 0;

      const property = await propertyDB.getById({
        id: propId,
      });

      const stats = {
        propId: property.id,
        propName: property.name,
        dueTenantCount: dueTenantCount,
        month: item.month,
        year: item.year,
        totalIncome: Number(rentCollection),
        totalRent: Number(onylRentCollection),
        // otherIncome: Number(rentCollection) - (Number(onylRentCollection) + Number(securityCollection)),
        otherIncome: Number(rentCollection) - (Number(onylRentCollection)),
        totalExpense: Number(expenseCollection),
        totalBuildingRentExpense: Number(buildingRentExpenseCollection),
        totalFoodExpense: Number(foodExpenseCollection),
        totalStaffSalaryExpense: Number(staffSalaryExpenseCollection),
        otherExpense: Number(expenseCollection) - (
          Number(buildingRentExpenseCollection) +
          Number(foodExpenseCollection) +
          Number(staffSalaryExpenseCollection)
        ),
        netProfitLoss: Number(rentCollection) - Number(expenseCollection),
        totalDues: Number(totalDue),
        totalSecurity: Number(securityCollection),
        totalOffline: Number(offlineCollection),
        totalOnline: Number(onlineCollection),
        totalPayu: Number(payuCollection),
      };
      data.push(stats);
    }
    return data;
};