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

const recurringExpenseDB: any = {};

recurringExpenseDB.create = async ({
  type,
  amount,
  clientId,
  propId,
  flatId,
  paidDate,
  paidByUserType,
  paidBy,
  paidTo,
  paidToUserType,
  description,
  paymentMethod,
  noOfMonths,
  dueDate,
  expenseCycle,
  referenceId,
}: recurringExpensesType) => {
  const query = `
    INSERT INTO RecurringExpenses 
    (type, amount, clientId, propId, flatId, paidDate, paidByUserType, paidBy, paidTo, paidToUserType, description, paymentMethod, noOfMonths, dueDate, expenseCycle, referenceId) 
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;

  const data = [
    type,
    amount,
    clientId,
    propId,
    flatId,
    paidDate,
    paidByUserType,
    paidBy,
    paidTo,
    paidToUserType,
    description,
    paymentMethod,
    noOfMonths,
    dueDate,
    expenseCycle,
    referenceId,
  ];

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

//With RecurrenceDate and ExpencePayment Cycle
recurringExpenseDB.createNew = async ({
  type,
  amount,
  clientId,
  propId,
  flatId,
  paidDate,
  paidByUserType,
  paidBy,
  paidTo,
  paidToUserType,
  description,
  paymentMethod,
  noOfMonths,
  dueDate,
  expenseCycle,
  referenceId,
  recurrenceDate,
  expenceCycleType,
  assetId = null,
  expenseNature = CONSTANTS.EXPENSE_NATURE.OPERATING,
  expenseTitle=null,
}: recurringExpensesType) => {
  const query = `
    INSERT INTO RecurringExpenses 
    (type, amount, clientId, propId, flatId, paidDate, paidByUserType, paidBy, paidTo, paidToUserType, description, paymentMethod, noOfMonths, dueDate, expenseCycle, referenceId, expenceCycleType, recurrenceDate, assetId, expenseNature, expenseTitle) 
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;

  const data = [
    type,
    amount,
    clientId,
    propId,
    flatId,
    paidDate,
    paidByUserType,
    paidBy,
    paidTo,
    paidToUserType,
    description,
    paymentMethod,
    noOfMonths,
    dueDate,
    expenseCycle,
    referenceId,
    expenceCycleType,
    recurrenceDate,
    assetId,
    expenseNature,
    expenseTitle,
  ];

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

recurringExpenseDB.update = async ({
  amount,
  paidByUserType,
  paidToUserType,
  paidBy,
  paidTo,
  description,
  id,
  noOfMonths,
  dueDate,
  expenseCycle,
  expenseTitle=null,
}: recurringExpensesType) => {
  const query =
    `Update RecurringExpenses set dueDate = ?, amount = ?, paidByUserType = ?,  paidBy = ?, paidTo = ?, description = ?, paidToUserType = ?, noOfMonths=?, expenseCycle=?, expenseTitle = ? where id = ?`;
  const data = [
    dueDate,
    amount,
    paidByUserType,
    paidBy,
    paidTo,
    description,
    paidToUserType,
    noOfMonths,
    expenseCycle,
    expenseTitle,
    id,
  ];
  await DB.execute<ResultSetHeader[]>(query, data);
  return id;
};

recurringExpenseDB.updateExpenseNature = async ({
  expenseNature,
  id
}: recurringExpensesType) => {
  const query =
    `Update RecurringExpenses set expenseNature = ? where id = ? limit 1`;
  const data = [
    expenseNature,
    id,
  ];
  await DB.execute<ResultSetHeader[]>(query, data);
  return id;
};

recurringExpenseDB.updateNew = async ({
  amount,
  paidByUserType,
  paidToUserType,
  paidBy,
  paidTo,
  description,
  id,
  noOfMonths,
  dueDate,
  expenseCycle,
  expenceCycleType,
  recurrenceDate,
  expenseTitle=null,
}: recurringExpensesType) => {
  const query =
    `Update RecurringExpenses set dueDate = ?, amount = ?, paidByUserType = ?,  paidBy = ?, paidTo = ?, description = ?, paidToUserType = ?, noOfMonths=?, expenseCycle=?, expenceCycleType= ?, recurrenceDate = ?, expenseTitle = ? where id = ?`;
  const data = [
    dueDate,
    amount,
    paidByUserType,
    paidBy,
    paidTo,
    description,
    paidToUserType,
    noOfMonths,
    expenseCycle,
    expenceCycleType,
    recurrenceDate,
    expenseTitle,
    id,
  ];
  await DB.execute<ResultSetHeader[]>(query, data);
  return id;
};

recurringExpenseDB.delete = async ({ id, clientId }: recurringExpensesType) => {
  const query = `DELETE FROM RecurringExpenses WHERE id = ? and clientId = ? limit 1`;
  const data = [id, clientId];

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

recurringExpenseDB.deleteByPropIdAndType = async ({ clientId, propId, type }: recurringExpensesType) => {
  const query = `DELETE FROM RecurringExpenses WHERE clientId = ? and propId = ? and type = ?`;
  const data = [clientId, propId, type];

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

recurringExpenseDB.updateStatus = async ({
  id,
  status,
}: recurringExpensesType) => {
  const query = `UPDATE RecurringExpenses SET status = ? WHERE id = ?`;
  const data = [status, id];

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

recurringExpenseDB.getByClientIdAndPageNum = async ({
  clientId,
  typeFilters=null,
  pageNum,
  limit,
}: recurringExpensesType & {pageNum: any; limit: any; typeFilters: any}) => {
  let query = 
    `Select RE.id, RE.expenseTitle, RE.clientId, RE.isAutopay, RE.autopayId, RE.assetId, RE.expenseCycle, RE.expenceCycleType as paymentCycle, RE.propId, P.name as propName, RE.flatId, F.name as flatName, RE.amount, RE.type, RE.paidDate, RE.paidByUserType, RE.paidToUserType, RE.paidBy, RE.paidTo, RE.description, RE.paymentMethod, RE.noOfMonths, RE.dueDate, RE.status, RE.createdAt, RE.updatedAt, CASE WHEN RE.paidToUserType = ? THEN (select name from Vendors where id = RE.paidTo) WHEN RE.paidToUserType = ? THEN (select name from Staffs where id = RE.paidTo) WHEN RE.paidToUserType = ? THEN (select name from Landlords where id = RE.paidTo) END as paidToName, if(RE.paidByUserType = ?, (select name from Clients where id = RE.paidBy), (select name from Staffs where id = RE.paidBy)) as paidByName, ET.name, EC.id as expenseCategoryId from RecurringExpenses as RE left join ExpenseTypes as ET on ET.id = RE.type left join ExpenseCategories as EC on ET.categoryId = EC.id left join Properties as P on P.id = RE.propId left join Flats as F on F.id = RE.flatId where RE.clientId = ? `;
  const offset: number = (pageNum - 1) * limit;
  const data: any = [
    CONSTANTS.USER_TYPE.VENDOR,
    CONSTANTS.USER_TYPE.STAFF,
    CONSTANTS.USER_TYPE.LANDLORD,
    CONSTANTS.USER_TYPE.CLIENT,
    clientId,
  ];

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

  query += ` order by RE.dueDate asc limit ?, ?`;
  data.push(`${offset}`, `${limit}`);

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

recurringExpenseDB.getByClientIdAndPageNumAndCategory = async ({
  clientId,
  categoryFilter=null,
  pageNum,
  limit,
}: recurringExpensesType & {pageNum: any; limit: any; categoryFilter: any}) => {
  let query = 
    `Select RE.id, RE.clientId, RE.expenseTitle, RE.expenseNature, RE.expenseCycle, RE.expenceCycleType as paymentCycle, RE.propId, P.name as propName, RE.flatId, F.name as flatName, RE.amount, RE.type, RE.paidDate, RE.paidByUserType, RE.paidToUserType, RE.paidBy, RE.paidTo, RE.description, RE.paymentMethod, RE.noOfMonths, RE.dueDate, RE.status, RE.createdAt, RE.updatedAt, CASE WHEN RE.paidToUserType = ? THEN (select name from Vendors where id = RE.paidTo) WHEN RE.paidToUserType = ? THEN (select name from Staffs where id = RE.paidTo) WHEN RE.paidToUserType = ? THEN (select name from Landlords where id = RE.paidTo) END as paidToName, if(RE.paidByUserType = ?, (select name from Clients where id = RE.paidBy), (select name from Staffs where id = RE.paidBy)) as paidByName, ET.name, EC.id as expenseCategoryId from RecurringExpenses as RE left join ExpenseTypes as ET on ET.id = RE.type left join ExpenseCategories as EC on ET.categoryId = EC.id left join Properties as P on P.id = RE.propId left join Flats as F on F.id = RE.flatId where RE.clientId = ? `;
  const offset: number = (pageNum - 1) * limit;
  const data: any = [
    CONSTANTS.USER_TYPE.VENDOR,
    CONSTANTS.USER_TYPE.STAFF,
    CONSTANTS.USER_TYPE.LANDLORD,
    CONSTANTS.USER_TYPE.CLIENT,
    clientId,
  ];

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

  query += ` order by RE.dueDate asc limit ?, ?`;
  data.push(`${offset}`, `${limit}`);

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

recurringExpenseDB.getByClientIdAndFiltersAndPageNum = async ({
  clientId,
  pageNum,
  limit,
  typeFilter,
}: recurringExpensesType & {pageNum: any; limit: any; typeFilter: any;}) => {
  let query = 
    `Select RE.id, RE.clientId, RE.isAutopay, RE.autopayId, RE.expenseTitle, RE.expenseNature, RE.assetId, RE.expenseCycle, RE.propId, P.name as propName, RE.flatId, F.name as flatName, RE.amount, RE.type, RE.paidDate, RE.paidByUserType, RE.paidToUserType, RE.paidBy, RE.paidTo, RE.description, RE.paymentMethod, RE.noOfMonths, RE.dueDate, RE.status, RE.createdAt, RE.updatedAt, CASE WHEN RE.paidToUserType = ? THEN (select name from Vendors where id = RE.paidTo) WHEN RE.paidToUserType = ? THEN (select name from Staffs where id = RE.paidTo) WHEN RE.paidToUserType = ? THEN (select name from Landlords where id = RE.paidTo) END as paidToName, if(RE.paidByUserType = ?, (select name from Clients where id = RE.paidBy), (select name from Staffs where id = RE.paidBy)) as paidByName, ET.name, EC.id as expenseCategoryId from RecurringExpenses as RE left join ExpenseTypes as ET on ET.id = RE.type left join ExpenseCategories as EC on ET.categoryId = EC.id left join Properties as P on P.id = RE.propId left join Flats as F on F.id = RE.flatId where RE.clientId = ? `;
  const offset: number = (pageNum - 1) * limit;
  const data: any = [
    CONSTANTS.USER_TYPE.VENDOR,
    CONSTANTS.USER_TYPE.STAFF,
    CONSTANTS.USER_TYPE.LANDLORD,
    CONSTANTS.USER_TYPE.CLIENT,
    clientId,
  ];

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

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

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


recurringExpenseDB.getByClientIdAndREFilter = async ({
  clientId,
  searchVal,
  searchType
}: recurringExpensesType & {searchVal: any; searchType: number;}) => {

  let query =""; 
  let searchFilter = "";
  if (searchType == 1){
    searchFilter = "paidToName";
  } else if (searchType == 2){
    searchFilter = "paidToMobile";
  }

  if(searchType){
    query = `select * from (Select RE.id, RE.expenseTitle, RE.isAutopay, RE.autopayId, RE.expenseNature, RE.assetId, RE.clientId, RE.expenseCycle, RE.propId, P.name as propName, RE.flatId, F.name as flatName, RE.amount, RE.type, RE.paidDate, RE.paidByUserType, RE.paidToUserType, RE.paidBy, RE.paidTo, RE.description, RE.paymentMethod, RE.noOfMonths, RE.dueDate, RE.status, RE.createdAt, RE.updatedAt, CASE WHEN RE.paidToUserType = ? THEN (select name from Vendors where id = RE.paidTo) WHEN RE.paidToUserType = ? THEN (select name from Staffs where id = RE.paidTo) WHEN RE.paidToUserType = ? THEN (select name from Landlords where id = RE.paidTo) END as paidToName, CASE WHEN RE.paidToUserType = ? THEN (select mobile from Vendors where id = RE.paidTo) WHEN RE.paidToUserType = ? THEN (select mobile from Staffs where id = RE.paidTo) WHEN RE.paidToUserType = ? THEN (select mobile from Landlords where id = RE.paidTo) END as paidToMobile, if(RE.paidByUserType = ?, (select name from Clients where id = RE.paidBy), (select name from Staffs where id = RE.paidBy)) as paidByName, ET.name, EC.id as expenseCategoryId from RecurringExpenses as RE left join ExpenseTypes as ET on ET.id = RE.type left join ExpenseCategories as EC on ET.categoryId = EC.id left join Properties as P on P.id = RE.propId left join Flats as F on F.id = RE.flatId where RE.clientId = ? ) as v where ${searchFilter} like ? order by v.dueDate desc `;
  } else {
    query = `select * from (Select RE.id, RE.expenseTitle, RE.isAutopay, RE.autopayId, RE.expenseNature, RE.clientId, RE.expenseCycle, RE.propId, P.name as propName, RE.flatId, F.name as flatName, RE.amount, RE.type, RE.paidDate, RE.paidByUserType, RE.paidToUserType, RE.paidBy, RE.paidTo, RE.description, RE.paymentMethod, RE.noOfMonths, RE.dueDate, RE.status, RE.createdAt, RE.updatedAt, CASE WHEN RE.paidToUserType = ? THEN (select name from Vendors where id = RE.paidTo) WHEN RE.paidToUserType = ? THEN (select name from Staffs where id = RE.paidTo) WHEN RE.paidToUserType = ? THEN (select name from Landlords where id = RE.paidTo) END as paidToName, CASE WHEN RE.paidToUserType = ? THEN (select mobile from Vendors where id = RE.paidTo) WHEN RE.paidToUserType = ? THEN (select mobile from Staffs where id = RE.paidTo) WHEN RE.paidToUserType = ? THEN (select mobile from Landlords where id = RE.paidTo) END as paidToMobile, if(RE.paidByUserType = ?, (select name from Clients where id = RE.paidBy), (select name from Staffs where id = RE.paidBy)) as paidByName, ET.name, EC.id as expenseCategoryId from RecurringExpenses as RE left join ExpenseTypes as ET on ET.id = RE.type left join ExpenseCategories as EC on ET.categoryId = EC.id left join Properties as P on P.id = RE.propId left join Flats as F on F.id = RE.flatId where RE.clientId = ? ) as v where propName like ? order by v.dueDate desc `;
  }
  const data = [
    CONSTANTS.USER_TYPE.VENDOR,
    CONSTANTS.USER_TYPE.STAFF,
    CONSTANTS.USER_TYPE.LANDLORD,
    CONSTANTS.USER_TYPE.VENDOR,
    CONSTANTS.USER_TYPE.STAFF,
    CONSTANTS.USER_TYPE.LANDLORD,
    CONSTANTS.USER_TYPE.CLIENT,
    clientId,
    `%${searchVal}%`
  ];
  //log.info(`${query}, Data ${JSON.stringify(data)}`);
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  return false;
};



recurringExpenseDB.getByClientIdAndPropIdAndPageNum = async ({
  clientId,
  propId,
  pageNum,
  limit,
}: recurringExpensesType & {pageNum: any; limit: any}) => {
  const query = 
    `Select RE.id, RE.clientId, RE.isAutopay, RE.autopayId, RE.expenseTitle, RE.expenseNature, RE.assetId, RE.expenseCycle, RE.propId, P.name as propName, RE.flatId, F.name as flatName, RE.amount, RE.type, RE.paidDate, RE.paidByUserType, RE.paidToUserType, RE.paidBy, RE.paidTo, RE.description, RE.paymentMethod, RE.noOfMonths, RE.dueDate, RE.status, RE.createdAt, RE.updatedAt, CASE WHEN RE.paidToUserType = ? THEN (select name from Vendors where id = RE.paidTo) WHEN RE.paidToUserType = ? THEN (select name from Staffs where id = RE.paidTo) WHEN RE.paidToUserType = ? THEN (select name from Landlords where id = RE.paidTo) END as paidToName, if(RE.paidByUserType = ?, (select name from Clients where id = RE.paidBy), (select name from Staffs where id = RE.paidBy)) as paidByName, ET.name, EC.id as expenseCategoryId from RecurringExpenses as RE left join ExpenseTypes as ET on ET.id = RE.type left join ExpenseCategories as EC on ET.categoryId = EC.id left join Properties as P on P.id = RE.propId left join Flats as F on F.id = RE.flatId where RE.clientId = ? and P.id = ? order by RE.dueDate desc limit ?, ?`;
  const offset: number = (pageNum - 1) * limit;
  const data = [
    CONSTANTS.USER_TYPE.VENDOR,
    CONSTANTS.USER_TYPE.STAFF,
    CONSTANTS.USER_TYPE.LANDLORD,
    CONSTANTS.USER_TYPE.CLIENT,
    clientId,
    propId,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  return false;
};

recurringExpenseDB.getByClientIdAndPropIdAndFiltersAndPageNum = async ({
  clientId,
  propId,
  pageNum,
  limit,
  typeFilter,
}: recurringExpensesType & {pageNum: any; limit: any; typeFilter: any}) => {
  let query = 
    `Select RE.id, RE.clientId, RE.isAutopay, RE.autopayId, RE.expenseTitle, RE.expenseNature, RE.assetId, RE.expenseCycle, RE.propId, P.name as propName, RE.flatId, F.name as flatName, RE.amount, RE.type, RE.paidDate, RE.paidByUserType, RE.paidToUserType, RE.paidBy, RE.paidTo, RE.description, RE.paymentMethod, RE.noOfMonths, RE.dueDate, RE.status, RE.createdAt, RE.updatedAt, CASE WHEN RE.paidToUserType = ? THEN (select name from Vendors where id = RE.paidTo) WHEN RE.paidToUserType = ? THEN (select name from Staffs where id = RE.paidTo) WHEN RE.paidToUserType = ? THEN (select name from Landlords where id = RE.paidTo) END as paidToName, if(RE.paidByUserType = ?, (select name from Clients where id = RE.paidBy), (select name from Staffs where id = RE.paidBy)) as paidByName, ET.name, EC.id as expenseCategoryId from RecurringExpenses as RE left join ExpenseTypes as ET on ET.id = RE.type left join ExpenseCategories as EC on ET.categoryId = EC.id left join Properties as P on P.id = RE.propId left join Flats as F on F.id = RE.flatId where RE.clientId = ? and P.id = ? `;
  const offset: number = (pageNum - 1) * limit;
  const data: any = [
    CONSTANTS.USER_TYPE.VENDOR,
    CONSTANTS.USER_TYPE.STAFF,
    CONSTANTS.USER_TYPE.LANDLORD,
    CONSTANTS.USER_TYPE.CLIENT,
    clientId,
    propId,
  ];

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

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

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

recurringExpenseDB.getCountByClientIdAndPageNum = async ({
  clientId,
  pageNum,
  limit,
}: recurringExpensesType & {pageNum: any; limit: any}) => {
  const query = 
    `Select count(*) as total from RecurringExpenses as RE left join ExpenseTypes as ET on ET.id = RE.type left join ExpenseCategories as EC on ET.categoryId = EC.id left join Properties as P on P.id = RE.propId where RE.clientId = ? order by RE.dueDate desc`;
  const data = [
    clientId
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0].total;
  return 0;
};

recurringExpenseDB.getCountByClientIdAndPropId = async ({
  clientId,
  propId,
}: recurringExpensesType & {pageNum: any; limit: any}) => {
  const query = 
    `Select count(*) as total from RecurringExpenses as RE left join ExpenseTypes as ET on ET.id = RE.type left join ExpenseCategories as EC on ET.categoryId = EC.id left join Properties as P on P.id = RE.propId where RE.clientId = ? and RE.propId = ? order by RE.dueDate desc`;
  const data = [
    clientId,
    propId,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0].total;
  return 0;
};

recurringExpenseDB.getByClientIdAndPageNumForStaff = async ({
  clientId,
  pageNum,
  limit,
  propertiesIds,
  typeFilters=null,
}: recurringExpensesType & {pageNum: any; limit: any; propertiesIds: any; typeFilters: any}) => {
  let query = 
    `Select RE.id, RE.clientId, RE.expenseTitle, RE.expenseNature, RE.expenseCycle, RE.propId, RE.propId, P.name as propName, RE.flatId, F.name as flatName, RE.amount, RE.type, RE.paidDate, RE.paidByUserType, RE.paidToUserType, RE.paidBy, RE.paidTo, RE.description, RE.paymentMethod, RE.noOfMonths, RE.dueDate, RE.status, RE.createdAt, RE.updatedAt, CASE WHEN RE.paidToUserType = ? THEN (select name from Vendors where id = RE.paidTo) WHEN RE.paidToUserType = ? THEN (select name from Staffs where id = RE.paidTo) WHEN RE.paidToUserType = ? THEN (select name from Landlords where id = RE.paidTo) END as paidToName, if(RE.paidByUserType = ?, (select name from Clients where id = RE.paidBy), (select name from Staffs where id = RE.paidBy)) as paidByName, ET.name, EC.id as expenseCategoryId from RecurringExpenses as RE left join ExpenseTypes as ET on ET.id = RE.type left join ExpenseCategories as EC on ET.categoryId = EC.id left join Properties as P on P.id = RE.propId left join Flats as F on F.id = RE.flatId where RE.clientId = ? and RE.propId in (${propertiesIds}) `;
  const offset: number = (pageNum - 1) * limit;
  const data: any = [
    CONSTANTS.USER_TYPE.VENDOR,
    CONSTANTS.USER_TYPE.STAFF,
    CONSTANTS.USER_TYPE.LANDLORD,
    CONSTANTS.USER_TYPE.CLIENT,  
    clientId,
  ];

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

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

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

recurringExpenseDB.getByClientIdAndPageNumAndCategoryForStaff = async ({
  clientId,
  pageNum,
  limit,
  propertiesIds,
  categoryFilter=null,
}: recurringExpensesType & {pageNum: any; limit: any; propertiesIds: any; categoryFilter: any}) => {
  let query = 
    `Select RE.id, RE.clientId, RE.expenseTitle, RE.expenseNature, RE.expenseCycle, RE.propId, RE.propId, P.name as propName, RE.flatId, F.name as flatName, RE.amount, RE.type, RE.paidDate, RE.paidByUserType, RE.paidToUserType, RE.paidBy, RE.paidTo, RE.description, RE.paymentMethod, RE.noOfMonths, RE.dueDate, RE.status, RE.createdAt, RE.updatedAt, CASE WHEN RE.paidToUserType = ? THEN (select name from Vendors where id = RE.paidTo) WHEN RE.paidToUserType = ? THEN (select name from Staffs where id = RE.paidTo) WHEN RE.paidToUserType = ? THEN (select name from Landlords where id = RE.paidTo) END as paidToName, if(RE.paidByUserType = ?, (select name from Clients where id = RE.paidBy), (select name from Staffs where id = RE.paidBy)) as paidByName, ET.name, EC.id as expenseCategoryId from RecurringExpenses as RE left join ExpenseTypes as ET on ET.id = RE.type left join ExpenseCategories as EC on ET.categoryId = EC.id left join Properties as P on P.id = RE.propId left join Flats as F on F.id = RE.flatId where RE.clientId = ? and RE.propId in (${propertiesIds}) `;
  const offset: number = (pageNum - 1) * limit;
  const data: any = [
    CONSTANTS.USER_TYPE.VENDOR,
    CONSTANTS.USER_TYPE.STAFF,
    CONSTANTS.USER_TYPE.LANDLORD,
    CONSTANTS.USER_TYPE.CLIENT,  
    clientId,
  ];

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

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

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


recurringExpenseDB.getCountByClientIdAndPageNumForStaff = async ({
  clientId,
  propertiesIds
}: recurringExpensesType & {propertiesIds: any}) => {
  const query = 
    `Select count(*) as total from RecurringExpenses as RE left join ExpenseTypes as ET on ET.id = RE.type left join ExpenseCategories as EC on ET.categoryId = EC.id left join Properties as P on P.id = RE.propId where RE.clientId = ? and RE.propId in (${propertiesIds})`;
  const data = [
    clientId
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0].total;
  return 0;
};

recurringExpenseDB.isUnPaidExistByClientId = async ({
  clientId,
}: recurringExpensesType) => {
  const query = 
    `Select * from RecurringExpenses where clientId = ?`;
  const data = [
    clientId
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return true;
  else return false;  
};

recurringExpenseDB.isUnPaidExistByClientIdForStaff = async ({
  clientId,
  propertiesIds
}: recurringExpensesType & {propertiesIds: any}) => {
  const query = 
    `Select * from RecurringExpenses where clientId = ? and propId in (${propertiesIds})`;
  const data = [
    clientId
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return true;
  else return false;  
};

recurringExpenseDB.getById = async ({
  id,
  clientId,
}: recurringExpensesType & {propertiesIds: any}) => {
  const query = 
    `Select * from RecurringExpenses where id = ? and clientId = ?`;
  const data = [
    id,
    clientId,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;  
};

recurringExpenseDB.getByPropIdAndType = async ({
  propId,
  type,
}: recurringExpensesType) => {
  const query = 
    `Select * from RecurringExpenses where propId = ? and type = ?`;
  const data = [
    propId,
    type
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;  
};

recurringExpenseDB.updateDueDateById = async ({
  id,
  dueDate,
  expenseCycle,
}: recurringExpensesType) => {
  const query = `UPDATE RecurringExpenses SET dueDate = ?, expenseCycle = ? WHERE id = ?`;
  const data = [dueDate, expenseCycle, id];

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

recurringExpenseDB.deleteSalaryExpense = async ({
  staffId,
  clientId,
}: {staffId: number; clientId: number}) => {
  const query = 
    `Delete from RecurringExpenses where type = 14 and paidTo = ? and clientId = ? limit 1`;
  const data = [
    staffId,
    clientId
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

recurringExpenseDB.updateSalaryExpense = async ({
  staffId,
  clientId,
  amount,
}: {staffId: number; clientId: number; amount: number}) => {
  const query = 
    `Update RecurringExpenses set amount = ? where type = 14 and paidTo = ? and clientId = ? limit 1`;
  const data = [
    amount,
    staffId,
    clientId
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

recurringExpenseDB.getStaffSalaryExpense = async ({
  staffId,
  clientId,
}: {staffId: number; clientId: number; amount: number}) => {
  const query = 
    `Select * from RecurringExpenses where type = 14 and paidTo = ? and clientId = ? limit 1`;
  const data = [
    staffId,
    clientId
  ];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

recurringExpenseDB.updateReferenceId = async ({
  id,
  referenceId,
}: recurringExpensesType) => {
  const query = `update RecurringExpenses set referenceId = ? where id = ?`;
  const data = [
    referenceId,
    id,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

recurringExpenseDB.updateIsAutopay = async ({
  id,
  isAutopay,
  autopayId,
}: recurringExpensesType) => {
  const query = `update RecurringExpenses set isAutopay = ?, autopayId = ? where id = ?`;
  const data = [
    isAutopay,
    autopayId,
    id,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

recurringExpenseDB.expenseToBeAddedByPropId = async ({
  clientId,
  propId,
}: recurringExpensesType) => {
  const query = 
    `Select IFNULL(Sum(amount), 0) as expenseToBeAdded from RecurringExpenses where clientId = ? and propId = ? and Month(dueDate) = Month(CURDATE()) and Year(dueDate) = Year(CURDATE())`;
  const data = [clientId, propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0].expenseToBeAdded;
  else return false;  
}

export default recurringExpenseDB;