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

const moveOutDB: any = {};

moveOutDB.getById = async ({ id }: moveOutTypes) => {
  const query = "Select * from MoveOut where id = ?";
  const data = [id];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};
moveOutDB.getForElectricity = async ({ roomId }: moveOutTypes) => {
  const query =
    "Select * from MoveOut where roomId = ? and (MONTH(moveOutDate) = MONTH(now()) or  MONTH(moveOutDate) = MONTH(DATE_ADD(now(), Interval -1 MONTH))) order by id";
  const data = [roomId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

moveOutDB.getAllByMultiDetails = async ({
  tenantId,
  clientId,
  propId,
  roomId,
  status,
}: moveOutTypes) => {
  const query =
    "Select * from MoveOut where tenantId = ? and clientId = ? and propId = ? and roomId = ? and status = ? order by id desc";
  const data = [tenantId, clientId, propId, roomId, status];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

// moveOutDB.getByTenantIdAndClientId = async ({
//   tenantId,
//   clientId,
// }: moveOutTypes) => {
//   const query =
//     "Select * from MoveOut where tenantId = ? and clientId = ? and propId = ? and roomId = ? and status = ? 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;
// };

moveOutDB.getByTenantIdAndClientId = async ({
  clientId,
  tenantId,
}: moveOutTypes) => {
  const query =
    "select M.id, M.notes, M.refundStatus, M.isGstEnabled, M.wasCancelled, 0 as bookingAmt, M.discountType, M.gId, M.discount, M.discountPeriod, M.discountStartDate, M.discountEndDate, M.isDiscountFromFirstMonth, M.stayType, M.kycStatus, M.moveOutReason, M.isOnlinePaymentEnabled, M.isPoliceVerified, M.isRentAgreementSigned, M.rentalBond, M.createdAt, M.clientId, M.flatId, M.tenantId, M.electricityReading, M.propId, M.roomId, M.bedId, M.floor, M.rent, M.rentalCycle, M.noticePeriod, M.lockInPeriod, M.security, M.agreementPeriod, M.agreementStartDate, M.moveInDate, M.moveOutDate, M.status, M.rentalType, M.tenantDues as tenantDues, M.bookedBy, CASE WHEN M.bookedBy IS NULL OR M.bookedBy = 0 THEN C.name ELSE S.name END as bookedByName, C.mobile as ownerMobile, P.name as propName, P.type as propType, P.address as propertyAddress, P.streetAddress as propertyStreetAddress, P.isIdVerificationEnabled, P.isPanVerificationEnabled, P.isRentAgreementEnabled, P.isPoliceVerificationEnabled, P.isOnlinePaymentEnabled, R.roomNum, (Select value from Documents where tenantId = M.tenantId and propId = P.id and type = ? and moveOut=1 order by id desc limit 1) as profilePicture from MoveOut as M left join Properties as P on P.id = M.propId left join Rooms as R on R.id = M.roomId left join Clients as C on C.id = M.clientId left join Staffs as S on S.id = M.bookedBy where M.clientId = ? and M.tenantId = ?";
  const data = [CONSTANTS.DOCUMENT_TYPES.SELFI, clientId, tenantId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

moveOutDB.getByTenantIdAndClientIdOrderByDesc = async ({
  clientId,
  tenantId,
}: moveOutTypes) => {
  const query =
    "select M.id, M.notes, M.refundStatus, M.isGstEnabled, M.wasCancelled, 0 as bookingAmt, M.discountType, M.gId, M.discount, M.discountPeriod, M.discountStartDate, M.discountEndDate, M.isDiscountFromFirstMonth, M.stayType, M.kycStatus, M.moveOutReason, M.isOnlinePaymentEnabled, M.isPoliceVerified, M.isRentAgreementSigned, M.rentalBond, M.createdAt, M.clientId, M.flatId, M.tenantId, M.electricityReading, M.propId, M.roomId, M.bedId, M.floor, M.rent, M.rentalCycle, M.noticePeriod, M.lockInPeriod, M.security, M.agreementPeriod, M.agreementStartDate, M.moveInDate, M.moveOutDate, M.status, M.rentalType, M.tenantDues as tenantDues, M.bookedBy, CASE WHEN M.bookedBy IS NULL OR M.bookedBy = 0 THEN C.name ELSE S.name END as bookedByName, C.mobile as ownerMobile, P.name as propName, P.type as propType, P.address as propertyAddress, P.streetAddress as propertyStreetAddress, P.isIdVerificationEnabled, P.isPanVerificationEnabled, P.isRentAgreementEnabled, P.isPoliceVerificationEnabled, P.isOnlinePaymentEnabled, R.roomNum, (Select value from Documents where tenantId = M.tenantId and propId = P.id and type = ? and moveOut=1 order by id desc limit 1) as profilePicture from MoveOut as M left join Properties as P on P.id = M.propId left join Rooms as R on R.id = M.roomId left join Clients as C on C.id = M.clientId left join Staffs as S on S.id = M.bookedBy where M.clientId = ? and M.tenantId = ? order by id desc"; // (2026-08-10)added order by id desc so that every time latest moveOut entry is picked, caused issue when 2 moveout entry are there and old move out entry was being picked
  const data = [CONSTANTS.DOCUMENT_TYPES.SELFI, clientId, tenantId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

moveOutDB.getByTenantIdAndClientIdAll = async ({
  clientId,
  tenantId,
}: moveOutTypes) => {
  const query =
    "select M.id, M.notes, M.refundStatus, M.isGstEnabled, M.wasCancelled, M.discountType, M.gId, M.discount, M.discountPeriod, M.discountStartDate, M.discountEndDate, M.isDiscountFromFirstMonth, M.stayType, M.kycStatus, M.moveOutReason, M.isOnlinePaymentEnabled, M.isPoliceVerified, M.isRentAgreementSigned, M.rentalBond, M.createdAt, M.clientId, M.flatId, M.tenantId, M.electricityReading, M.propId, M.roomId, M.bedId, M.floor, M.rent, M.rentalCycle, M.noticePeriod, M.lockInPeriod, M.security, M.agreementPeriod, M.agreementStartDate, M.moveInDate, M.moveOutDate, M.status, M.rentalType, M.tenantDues as tenantDues, C.mobile as ownerMobile, P.name as propName, P.type as propType, P.address as propertyAddress, P.streetAddress as propertyStreetAddress, P.isIdVerificationEnabled, P.isPanVerificationEnabled, P.isRentAgreementEnabled, P.isPoliceVerificationEnabled, P.isOnlinePaymentEnabled, R.roomNum from MoveOut as M left join Properties as P on P.id = M.propId left join Rooms as R on R.id = M.roomId left join Clients as C on C.id = M.clientId where M.clientId = ? and M.tenantId = ?";
  const data = [clientId, tenantId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

moveOutDB.getByTenantIdAndClientIdDesc = async ({
  clientId,
  tenantId,
}: moveOutTypes) => {
  const query =
    "select M.id, M.gId, M.refundStatus, M.isGstEnabled, M.notes, M.wasCancelled, M.discountType, M.discount, M.discountPeriod, M.discountStartDate, M.discountEndDate, M.isDiscountFromFirstMonth, M.stayType, M.kycStatus, M.moveOutReason, M.isOnlinePaymentEnabled, M.rentalBond, M.createdAt, M.clientId, M.flatId, M.tenantId, M.electricityReading, M.propId, M.roomId, M.bedId, M.floor, M.rent, M.rentalCycle, M.noticePeriod, M.lockInPeriod, M.security, M.agreementPeriod, M.agreementStartDate, M.moveInDate, M.moveOutDate, M.status, M.rentalType, M.tenantDues as tenantDues, C.mobile as ownerMobile, P.name as propName, P.type as propType, P.address as propertyAddress, P.streetAddress as propertyStreetAddress, P.isIdVerificationEnabled, P.isPanVerificationEnabled, P.isRentAgreementEnabled, P.isPoliceVerificationEnabled, P.isOnlinePaymentEnabled, R.roomNum from MoveOut as M left join Properties as P on P.id = M.propId left join Rooms as R on R.id = M.roomId left join Clients as C on C.id = M.clientId where M.clientId = ? and M.tenantId = ? order by M.id desc limit 1";
  const data = [clientId, tenantId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

moveOutDB.getByFullOccupancyTenantIdAndClientIdDesc = async ({
  clientId,
  tenantId,
}: moveOutTypes) => {
  const query =
    "select M.id, M.gId, M.refundStatus, M.isGstEnabled, M.notes, M.wasCancelled, M.discountType, M.discount, M.discountPeriod, M.discountStartDate, M.discountEndDate, M.isDiscountFromFirstMonth, M.stayType, M.kycStatus, M.moveOutReason, M.isOnlinePaymentEnabled, M.rentalBond, M.createdAt, M.clientId, M.flatId, M.tenantId, M.electricityReading, M.propId, M.roomId, M.bedId, M.floor, M.rent, M.rentalCycle, M.noticePeriod, M.lockInPeriod, M.security, M.agreementPeriod, M.agreementStartDate, M.moveInDate, M.moveOutDate, M.status, M.rentalType, M.tenantDues as tenantDues, C.mobile as ownerMobile, P.name as propName, P.type as propType, P.address as propertyAddress, P.streetAddress as propertyStreetAddress, P.isIdVerificationEnabled, P.isPanVerificationEnabled, P.isRentAgreementEnabled, P.isPoliceVerificationEnabled, P.isOnlinePaymentEnabled, R.roomNum from MoveOut as M left join Properties as P on P.id = M.propId left join Rooms as R on R.id = M.roomId left join Clients as C on C.id = M.clientId where M.clientId = ? and M.tenantId = ? and M.stayType != ? and M.rentalType != 0 order by M.id desc limit 1";
  const data = [clientId, tenantId, CONSTANTS.STAY_TYPE.SHORT];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

moveOutDB.add = async ({
  clientId,
  tenantId,
  propId,
  roomId,
  bedId,
  floor,
  reason,
  clientDues,
  tenantDues,
  rent,
  rentalCycle,
  rentalType,
  rentalMonths,
  noticePeriod,
  lockInPeriod,
  security,
  agreementPeriod,
  agreementStartDate,
  moveInDate,
  moveOutDate,
  flatId,
  notes,
  rentalBond,
  moveOutReason,
  isPoliceVerified,
  isRentAgreementSigned,
  stayType,
  kycStatus,
  discountType,
  discount,
  discountPeriod,
  discountStartDate,
  discountEndDate,
  isDiscountFromFirstMonth,
  wasCancelled,
  status,
  gId,
}: moveOutTypes) => {
  const query =
    "Insert into MoveOut (clientId, tenantId, propId, roomId, bedId, floor, reason, clientDues, tenantDues, rent, rentalCycle, rentalType, rentalMonths, noticePeriod, lockInPeriod, security, agreementPeriod, agreementStartDate, moveInDate, moveOutDate, flatId, notes, rentalBond, moveOutReason, isPoliceVerified, isRentAgreementSigned, stayType, KycStatus, discountType, discount, discountPeriod, discountStartDate, discountEndDate, isDiscountFromFirstMonth, wasCancelled, status, gId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  const data = [
    clientId,
    tenantId,
    propId,
    roomId,
    bedId,
    floor,
    reason,
    clientDues,
    tenantDues,
    rent,
    rentalCycle,
    rentalType,
    rentalMonths,
    noticePeriod,
    lockInPeriod,
    security,
    agreementPeriod,
    agreementStartDate,
    moveInDate,
    moveOutDate,
    flatId,
    notes,
    rentalBond,
    moveOutReason,
    isPoliceVerified,
    isRentAgreementSigned,
    stayType,
    kycStatus,
    discountType,
    discount,
    discountPeriod,
    discountStartDate,
    discountEndDate,
    isDiscountFromFirstMonth,
    wasCancelled,
    status,
    gId,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

moveOutDB.updateBookedBy = async ({ bookedBy, id }: moveOutTypes) => {
  const query = "Update MoveOut set bookedBy = ? where id = ?";
  const data = [bookedBy, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

moveOutDB.updateIsGstEnabled = async ({ isGstEnabled, id }: moveOutTypes) => {
  const query = "Update MoveOut set isGstEnabled = ? where id = ?";
  const data = [isGstEnabled, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

moveOutDB.remove = async ({
  clientId,
  tenantId,
  propId,
  roomId,
  bedId,
}: moveOutTypes) => {
  const query =
    "Delete from MoveOut where clientId = ? and tenantId = ? and propId = ? and roomId = ? and bedId = ?";
  const data = [clientId, tenantId, propId, roomId, bedId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

moveOutDB.removeById = async ({
  id,
}: moveOutTypes) => {
  const query =
    "Delete from MoveOut where id = ?";
  const data = [id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

moveOutDB.extendStay = async ({ id, moveOutDate }: moveOutTypes) => {
  const query = "Update MoveOut set moveOutDate=? where id =?";
  const data = [moveOutDate, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

moveOutDB.updateStatus = async ({ id, status }: moveOutTypes) => {
  const query = "Update MoveOut set status=? where id =?";
  const data = [status, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

moveOutDB.updateMoveOutDate = async ({ id, moveOutDate }: moveOutTypes) => {
  const query = "Update MoveOut set moveOutDate=? where id =?";
  const data = [moveOutDate, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

moveOutDB.updateElectricityReading = async ({
  id,
  electricityReading,
}: moveOutTypes) => {
  const query = "Update MoveOut set electricityReading=? where id =?";
  const data = [electricityReading, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

moveOutDB.updateTenantDue = async ({
  tenantId,
  tenantDues,
  propId,
  roomId,
}: moveOutTypes) => {
  const query =
    "Update MoveOut set tenantDues = tenantDues + ? where tenantId = ? and propId = ? and roomId = ?";
  const data = [tenantDues, tenantId, propId, roomId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

moveOutDB.setTenantDue = async ({
  tenantId,
  tenantDues,
  propId,
  roomId,
}: moveOutTypes) => {
  const query =
    "Update MoveOut set tenantDues = ? where tenantId = ? and propId = ? and roomId = ?";
  const data = [tenantDues, tenantId, propId, roomId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

moveOutDB.subtractTenantDue = async ({
  clientId,
  tenantId,
  tenantDues,
  propId,
  roomId,
}: moveOutTypes) => {
  const query =
    "Update MoveOut set tenantDues = tenantDues - ? where clientId = ? and tenantId = ? and propId = ? and roomId = ?";
  const data = [tenantDues, clientId, tenantId, propId, roomId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

moveOutDB.getAllByMultiDetailsFlat = async ({
  tenantId,
  clientId,
  propId,
  flatId,
  status,
}: moveOutTypes) => {
  const query =
    "Select * from MoveOut where tenantId = ? and clientId = ? and propId = ? and flatId = ? and status = ? order by id desc";
  const data = [tenantId, clientId, propId, flatId, status];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

moveOutDB.getMovedOutTenants = async ({ tenantId }: moveOutTypes) => {
  const query =
    "Select * from MoveOut where tenantId = ? and status = ? order by id desc limit 1;";

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

moveOutDB.getMovedOutTenantsWithClientId = async ({ tenantId, clientId }: moveOutTypes) => {
  const query =
    "Select * from MoveOut where tenantId = ? and clientId = ? and status = ? order by id desc limit 1;";

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

moveOutDB.getEvictedStats = async ({ clientId, propId }: moveOutTypes) => {
  const query =
    "select totalMovedOut, curMonthMovedOut, if(totalDefaultedDues is null, 0, totalDefaultedDues) as totalDefaultedDues, defaulters from (select (Select count(Distinct(tenantId)) as totalMovedOut from MoveOut where clientId = ? and propId = ? and status = ?) as totalMovedOut, (Select count(Distinct(tenantId)) as curMonthMovedOut from MoveOut where clientId = ? and propId = ? and status = ? and Month(moveOutDate) = Month(curdate()) and Year(moveOutDate) = Year(curdate())) as curMonthMovedOut, (Select sum(MD.balance) as totalDefaultedDues from MoveOut as M join MoveOutDues as MD on M.id = MD.moveOutId where M.clientId = ? and M.propId = ? and M.status = ? and M.id in (SELECT MAX(innerM.id) FROM MoveOut as innerM WHERE innerM.tenantId = M.tenantId GROUP BY innerM.tenantId)) as totalDefaultedDues, (Select count(*) from (select M.tenantId from MoveOut as M join MoveOutDues as MD on M.id = MD.moveOutId where M.clientId = ? and M.propId = ? and M.status = ? group by M.tenantId, M.clientId HAVING sum(MD.balance) > 0) as defaulterTenants) as defaulters) as V";
  const data = [
    clientId,
    propId,
    CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
    clientId,
    propId,
    CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
    clientId,
    propId,
    CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
    clientId,
    propId,
    CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

moveOutDB.getEvictedStatsByDateRange = async ({ clientId, propId, startDate, endDate, }: moveOutTypes & { startDate: string; endDate: string }) => {
  const query =
    "select totalMovedOut, curMonthMovedOut, if(totalDefaultedDues is null, 0, totalDefaultedDues) as totalDefaultedDues, defaulters, workingPending, cancelledBooking from (select (Select count(Distinct(tenantId)) as totalMovedOut from MoveOut where clientId = ? and DATE(moveOutDate) BETWEEN ? and ? and propId = ? and status = ?) as totalMovedOut, (Select count(Distinct(tenantId)) as curMonthMovedOut from MoveOut where clientId = ? and propId = ? and status = ? and Month(moveOutDate) = Month(curdate()) and Year(moveOutDate) = Year(curdate())) as curMonthMovedOut, (Select sum(MD.balance) as totalDefaultedDues from MoveOut as M join MoveOutDues as MD on M.id = MD.moveOutId where M.clientId = ? and DATE(M.moveOutDate) BETWEEN ? and ? and M.propId = ? and M.status = ? and M.id in (SELECT MAX(innerM.id) FROM MoveOut as innerM WHERE innerM.tenantId = M.tenantId GROUP BY innerM.tenantId)) as totalDefaultedDues, (Select count(*) from (select M.tenantId from MoveOut as M join MoveOutDues as MD on M.id = MD.moveOutId where M.clientId = ? and DATE(moveOutDate) BETWEEN ? and ? and M.propId = ? and M.status = ? group by M.tenantId, M.clientId HAVING sum(MD.balance) > 0) as defaulterTenants) as defaulters, (Select count(Distinct(tenantId)) from MoveOut where clientId = ? and propId = ? and CURDATE() > DATE_ADD(moveOutDate, INTERVAL 30 DAY) and status = ? and wasCancelled = 0 and refundStatus < ?) as workingPending, (Select count(*) as bookingCancelled from MoveOut as M where M.clientId = ? and M.propId = ? and DATE(M.moveOutDate) BETWEEN ? and ? and M.status = ? and M.wasCancelled=1 and M.id in (SELECT MAX(innerM.id) FROM MoveOut as innerM WHERE innerM.tenantId = M.tenantId and innerM.clientId=M.clientId and innerM.status = 2 and innerM.wasCancelled=1 GROUP BY innerM.tenantId)) as cancelledBooking) as V";
  const data = [
    clientId,
    startDate,
    endDate,
    propId,
    CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
    clientId,
    propId,
    CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
    clientId,
    startDate,
    endDate,
    propId,
    CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
    clientId,
    startDate,
    endDate,
    propId,
    CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
    clientId,
    propId,
    CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
    CONSTANTS.REFUND_STATUS.STATEMENT_GENERATED,
    clientId,
    propId,
    startDate,
    endDate,
    CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

moveOutDB.getEvictedStatsByClientId = async ({ clientId }: moveOutTypes) => {
  const query =
    "select totalMovedOut, curMonthMovedOut, if(totalDefaultedDues is null, 0, totalDefaultedDues) as totalDefaultedDues, defaulters from (select (Select count(Distinct(tenantId)) as totalMovedOut from MoveOut where clientId = ? and status = ?) as totalMovedOut, (Select count(Distinct(tenantId)) as curMonthMovedOut from MoveOut where clientId = ? and status = ? and Month(moveOutDate) = Month(curdate()) and Year(moveOutDate) = Year(curdate())) as curMonthMovedOut, (Select sum(MD.balance) as totalDefaultedDues from MoveOut as M join MoveOutDues as MD on M.id = MD.moveOutId where M.clientId = ? and M.status = ? and M.id in (SELECT MAX(innerM.id) FROM MoveOut as innerM WHERE innerM.tenantId = M.tenantId GROUP BY innerM.tenantId)) as totalDefaultedDues, (Select count(*) from (select M.tenantId from MoveOut as M join MoveOutDues as MD on M.id = MD.moveOutId where M.clientId = ? and M.status = ? group by M.tenantId, M.clientId HAVING sum(MD.balance) > 0) as defaulterTenants) as defaulters) as V";
  const data = [
    clientId,
    CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
    clientId,
    CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
    clientId,
    CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
    clientId,
    CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};


moveOutDB.getEvictedStatsByClientIdAndDateRange = async ({
  clientId,
  startDate,
  endDate
}: moveOutTypes & { startDate: string; endDate: string }) => {
  const query =
    `select totalMovedOut, curMonthMovedOut, if(totalDefaultedDues is null, 0, totalDefaultedDues) as totalDefaultedDues, defaulters, workingPending, cancelledBooking from (select 
    (Select count(Distinct(tenantId)) as totalMovedOut from MoveOut where clientId = ? AND DATE(moveOutDate) BETWEEN ? and ? and status = ?) as totalMovedOut, 
    (Select count(Distinct(tenantId)) as curMonthMovedOut from MoveOut where clientId = ? and status = ? and Month(moveOutDate) = Month(curdate()) and Year(moveOutDate) = Year(curdate())) as curMonthMovedOut, 
    (Select sum(MD.balance) as totalDefaultedDues from MoveOut as M join MoveOutDues as MD on M.id = MD.moveOutId where M.clientId = ? and DATE(M.moveOutDate) BETWEEN ? and ? and M.status = ? and M.id in (SELECT MAX(innerM.id) FROM MoveOut as innerM WHERE innerM.tenantId = M.tenantId GROUP BY innerM.tenantId)) as totalDefaultedDues, 
    (Select count(*) from (select M.tenantId from MoveOut as M join MoveOutDues as MD on M.id = MD.moveOutId where M.clientId = ? and DATE(M.moveOutDate) BETWEEN ? and ? and M.status = ? group by M.tenantId, M.clientId HAVING sum(MD.balance) > 0) as defaulterTenants) as defaulters, 
    (Select count(Distinct(tenantId)) from MoveOut where clientId = ? and CURDATE() > DATE_ADD(moveOutDate, INTERVAL 30 DAY) and wasCancelled = 0 and status = ? and refundStatus < ?) as workingPending,
    (Select count(*) as bookingCancelled from MoveOut as M where M.clientId = ? and DATE(M.moveOutDate) BETWEEN ? and ? and M.status = ? and M.wasCancelled=1 and M.id in (SELECT MAX(innerM.id) FROM MoveOut as innerM WHERE innerM.tenantId = M.tenantId and innerM.clientId=M.clientId and innerM.status = 2 and innerM.wasCancelled=1 GROUP BY innerM.tenantId)) as cancelledBooking) 
    as V`;
  const data = [
    clientId,
    startDate,
    endDate,
    CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
    clientId,
    CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
    clientId,
    startDate,
    endDate,
    CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
    clientId,
    startDate,
    endDate,
    CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
    clientId,
    CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
    CONSTANTS.REFUND_STATUS.STATEMENT_GENERATED,
    clientId,
    startDate,
    endDate,
    CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

moveOutDB.getEvictedStatsByClientIdForStaff = async ({ clientId, propertiesIds }: moveOutTypes & { propertiesIds: string }) => {
  const query =
    `select totalMovedOut, curMonthMovedOut, if(totalDefaultedDues is null, 0, totalDefaultedDues) as totalDefaultedDues, defaulters from (select (Select count(Distinct(tenantId)) as totalMovedOut from MoveOut where clientId = ? and propId in (${propertiesIds}) and status = ?) as totalMovedOut, (Select count(Distinct(tenantId)) as curMonthMovedOut from MoveOut where clientId = ? and propId in (${propertiesIds}) and status = ? and Month(moveOutDate) = Month(curdate()) and Year(moveOutDate) = Year(curdate())) as curMonthMovedOut, (Select sum(MD.balance) as totalDefaultedDues from MoveOut as M join MoveOutDues as MD on M.id = MD.moveOutId where M.clientId = ? and M.propId in (${propertiesIds}) and M.status = ? and M.id in (SELECT MAX(innerM.id) FROM MoveOut as innerM WHERE innerM.tenantId = M.tenantId GROUP BY innerM.tenantId)) as totalDefaultedDues, (Select count(*) from (select M.tenantId from MoveOut as M join MoveOutDues as MD on M.id = MD.moveOutId where M.clientId = ? and M.propId in (${propertiesIds}) and M.status = ? group by M.tenantId, M.clientId HAVING sum(MD.balance) > 0) as defaulterTenants) as defaulters) as V`;
  const data = [
    clientId,
    CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
    clientId,
    CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
    clientId,
    CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
    clientId,
    CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

moveOutDB.getEvictedStatsByClientIdForStaffAndDateRange = async ({ clientId, propertiesIds, startDate, endDate }: moveOutTypes & { propertiesIds: string; startDate: string; endDate: string; }) => {

  const query =
    `select totalMovedOut, cancelledBooking, workingPending, curMonthMovedOut, if(totalDefaultedDues is null, 0, totalDefaultedDues) as totalDefaultedDues, defaulters from (select (Select count(Distinct(tenantId)) as totalMovedOut from MoveOut where clientId = ? and DATE(moveOutDate) BETWEEN ? and ? and propId in (${propertiesIds}) and status = ?) as totalMovedOut, (Select count(Distinct(tenantId)) as curMonthMovedOut from MoveOut where clientId = ? and propId in (${propertiesIds}) and status = ? and Month(moveOutDate) = Month(curdate()) and Year(moveOutDate) = Year(curdate())) as curMonthMovedOut, (Select sum(MD.balance) as totalDefaultedDues from MoveOut as M join MoveOutDues as MD on M.id = MD.moveOutId where M.clientId = ? and DATE(M.moveOutDate) BETWEEN ? and ? and M.propId in (${propertiesIds}) and M.status = ? and M.id in (SELECT MAX(innerM.id) FROM MoveOut as innerM WHERE innerM.tenantId = M.tenantId GROUP BY innerM.tenantId)) as totalDefaultedDues, (Select count(*) from (select M.tenantId from MoveOut as M join MoveOutDues as MD on M.id = MD.moveOutId where M.clientId = ? and DATE(M.moveOutDate) BETWEEN ? and ? and M.propId in (${propertiesIds}) and M.status = ? group by M.tenantId, M.clientId HAVING sum(MD.balance) > 0) as defaulterTenants) as defaulters, (Select count(Distinct(tenantId)) from MoveOut where clientId = ? and propId in (${propertiesIds}) and CURDATE() > DATE_ADD(moveOutDate, INTERVAL 30 DAY) and wasCancelled = 0 and status = ? and refundStatus < ? and id IN (SELECT MAX(innerM.id) FROM MoveOut as innerM WHERE innerM.tenantId = tenantId and innerM.clientId=clientId GROUP BY innerM.tenantId)) as workingPending,
    (Select count(*) as bookingCancelled from MoveOut as M where M.clientId = ? and propId in (${propertiesIds}) and DATE(M.moveOutDate) BETWEEN ? and ? and M.status = ? and M.wasCancelled=1 and M.id in (SELECT MAX(innerM.id) FROM MoveOut as innerM WHERE innerM.tenantId = M.tenantId and innerM.clientId=M.clientId and innerM.status = 2 and innerM.wasCancelled=1 GROUP BY innerM.tenantId)) as cancelledBooking) as V`;
  const data = [
    clientId,
    startDate,
    endDate,
    CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
    clientId,
    CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
    clientId,
    startDate,
    endDate,
    CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
    clientId,
    startDate,
    endDate,
    CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
    clientId,
    CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
    CONSTANTS.REFUND_STATUS.STATEMENT_GENERATED,
    clientId,
    startDate,
    endDate,
    CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
  ];
  //log.info(mysql.format(query, data))
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

moveOutDB.getByBedId = async ({ bedId }: moveOutTypes) => {
  const query =
    "Select * from MoveOut where bedId = ? and status = ? order by id desc limit 1;";
  const data = [bedId, CONSTANTS.MOVE_OUT_STATUS.MOVEOUT];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

moveOutDB.getLastMoveOutsByBedIds = async ({ bedIds, clientId }: moveOutTypes & { bedIds: number[] }) => {
  const query = `SELECT bedId, MAX(moveOutDate) as moveOutDate FROM MoveOut WHERE bedId IN (${bedIds}) and clientId = ? GROUP BY bedId`;
  const data = [
    clientId,
  ]
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  return rows;
};


moveOutDB.removeByTenantId = async ({
  clientId,
  tenantId,
}: moveOutTypes) => {
  const query =
    "Delete from MoveOut where clientId = ? and tenantId = ? and status = ?";
  const data = [clientId, tenantId, CONSTANTS.MOVE_OUT_STATUS.INITIATED];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

moveOutDB.getLastMoveOutDateByBedId = async ({
  bedId
}: moveOutTypes) => {
  const query = "Select moveOutDate from MoveOut where bedId = ? order by id desc limit 1";
  const data = [bedId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

moveOutDB.switchRoomAndFlat = async ({
  clientId,
  tenantId,
  roomId,
  flatId,
  bedId,
  floor,
  rent,
}: moveOutTypes) => {
  const query =
    "Update MoveOut set roomId=?, bedId=?, floor=?, rent=?, flatId=? where clientId =? and tenantId =? order by id desc limit 1";
  const data = [roomId, bedId, floor, rent, flatId, clientId, tenantId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

moveOutDB.switchPropertyFlat = async ({
  clientId,
  tenantId,
  roomId,
  flatId,
  bedId,
  floor,
  propId,
  rent,
}: moveOutTypes) => {
  const query =
    "Update MoveOut set roomId = ?, bedId = ?, floor = ?, rent = ?, flatId = ?, propId = ? where clientId =? and tenantId =?";
  const data = [roomId, bedId, floor, rent, flatId, propId, clientId, tenantId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

moveOutDB.switchRoom = async ({
  clientId,
  tenantId,
  roomId,
  bedId,
  floor,
  rent,
}: moveOutTypes) => {
  const query =
    "Update MoveOut set roomId=?, bedId=?, floor=?, rent=? where clientId =? and tenantId =? order by id desc limit 1";
  const data = [roomId, bedId, floor, rent, clientId, tenantId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

moveOutDB.deleteFullOccupiedExtraEntry = async ({
  tenantId,
  clientId,
}: moveOutTypes) => {
  const query =
    "delete from MoveOut where tenantId = ? and clientId = ? order by id desc limit 1;";
  const data = [tenantId, clientId];
  const [rows] = await DB.execute<ResultSetHeader[]>(query, data);
  if (rows?.length > 0) return true;
  else return false;
};

moveOutDB.updateLastReminded = async ({ lastRemindedOn, tenantId, clientId }: moveOutTypes) => {
  const query = "Update MoveOut set lastRemindedOn = ? where tenantId = ? and clientId = ?";
  const data = [lastRemindedOn, tenantId, clientId];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

moveOutDB.remindedToday = async ({ clientId, tenantId }: moveOutTypes) => {
  const query =
    "select * from MoveOut where clientId = ? and tenantId = ? and Date(lastRemindedOn) = curdate();";
  const data = [clientId, tenantId];
  const [rows] = await DB.execute<ResultSetHeader[]>(query, data);
  if (rows.length > 0) return true;
  else return false;
};

// moveOutDB.getOccupancyReportDataByPropId = async ({
//   clientId,
//   propId,
//   startDate,
//   endDate,
// }: moveOutTypes & {startDate: string; endDate: string}) => {
//   const query =
//     "Select TE.name as tenantName, R.roomNum, if(R.floor IS NULL, (select name from Flats as F where F.id=R.flatId), R.floor) as floor, P.name as propName, M.moveInDate, M.moveOutDate, (select count(*) from Beds as B where B.roomId = R.id) as noOfBeds, M.rent, M.security, M.moveOutReason, (select SUM(balance) from MoveOutDues as MD where MD.tenantId = M.tenantId and MD.clientId = M.clientId) as totalDues, (select SUM(amount) from Transactions as T where T.tenantId = M.tenantId and T.clientId = M.clientId and T.amount > 0) as totalCollection from MoveOut as M join Properties as P on P.id = M.propId join Tenants as TE on M.tenantId = TE.id join Rooms as R on R.id = M.roomId where M.clientId = ? and M.propId = ? and DATE(M.moveOutDate) BETWEEN ? and ? and M.status = ? order by M.id desc";
//   const data = [
//     clientId,
//     propId,
//     startDate,
//     endDate,
//     CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
//   ];
//   const [rows] = await DB.execute<RowDataPacket[]>(query, data);
//   if (rows?.length > 0) return rows;
//   else return false;
// };

// moveOutDB.getOccupancyReportDataByClientId = async ({
//   clientId,
//   startDate,
//   endDate,
// }: moveOutTypes & {startDate: string; endDate: string}) => {
//   const query =
//     "Select TE.name as tenantName, R.roomNum, if(R.floor IS NULL, (select name from Flats as F where F.id=R.flatId), R.floor) as floor, P.name as propName, M.moveInDate, M.moveOutDate, (select count(*) from Beds as B where B.roomId = R.id) as noOfBeds, M.rent, M.security, M.moveOutReason, (select SUM(balance) from MoveOutDues as MD where MD.tenantId = M.tenantId and MD.clientId = M.clientId) as totalDues, (select SUM(amount) from Transactions as T where T.tenantId = M.tenantId and T.clientId = M.clientId and T.amount > 0) as totalCollection from MoveOut as M join Properties as P on P.id = M.propId join Tenants as TE on M.tenantId = TE.id join Rooms as R on R.id = M.roomId where M.clientId = ? and DATE(M.moveOutDate) BETWEEN ? and ? and M.status = ? order by M.id desc";
//   const data = [
//     clientId,
//     startDate,
//     endDate,
//     CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
//   ];
//   const [rows] = await DB.execute<RowDataPacket[]>(query, data);
//   if (rows?.length > 0) return rows;
//   else return false;
// };

moveOutDB.getByClientIdandDateRange = async ({
  clientId,
  startDate,
  endDate,
}: moveOutTypes & { startDate: string; endDate: string }) => {
  const query = "SELECT ANY_VALUE(MO.propId) AS propId, ANY_VALUE(T.gender) AS gender, ANY_VALUE(T.email) AS email, ANY_VALUE(T.dob) AS dob, ANY_VALUE(T.aadharNumber) AS aadhaarNumber, ANY_VALUE(T.bloodGroup) AS bloodGroup, ANY_VALUE(T.occupation) AS occupation, ANY_VALUE(MO.kycStatus) AS kycStatus, ANY_VALUE(COALESCE(NULLIF(TG.fatherName, ''), '')) AS fatherName, ANY_VALUE(COALESCE(NULLIF(TG.fatherMobile, ''), '')) AS fatherMobile, ANY_VALUE(COALESCE(NULLIF(TG.fatherOccupation, ''), '')) AS fatherOccupation, ANY_VALUE(COALESCE(NULLIF(TG.fatherAnnualIncome, ''), '')) AS fatherAnnualIncome, ANY_VALUE(COALESCE(NULLIF(TG.motherName, ''), '')) AS motherName, ANY_VALUE(COALESCE(NULLIF(TG.motherMobile, ''), '')) AS motherMobile, ANY_VALUE(COALESCE(NULLIF(TG.localGuardianName, ''), '')) AS localGuardianName, ANY_VALUE(COALESCE(NULLIF(TG.localGuardianMobile, ''), '')) AS localGuardianMobile, ANY_VALUE(COALESCE(NULLIF(TG.localGuardianRelation, ''), '')) AS localGuardianRelation, ANY_VALUE(T.institutionName) AS institutionName, ANY_VALUE(T.institutionEmail) AS institutionEmail, ANY_VALUE(T.title) AS title, ANY_VALUE(T.address) AS address, COUNT(MO.tenantId) AS occupiedBeds, ANY_VALUE(DATE_FORMAT(MO.moveInDate, '%Y-%m-%d')) AS moveInDate, ANY_VALUE(MO.rent) AS rent, ANY_VALUE(MO.security) AS security, ANY_VALUE(T.name) AS name, ANY_VALUE(T.mobile) AS mobile, ANY_VALUE(R.roomNum) AS roomNum, ANY_VALUE(R.extendedName) AS roomName, ANY_VALUE(T.id) AS id, ANY_VALUE(RO.name) AS roomOptionName, ANY_VALUE(MO.rentalCycle) AS rentalCycle, ANY_VALUE(MO.moveOutReason) AS moveOutReason, ANY_VALUE(MO.notes) AS notes, ANY_VALUE(MO.status) AS occupancyStatus, ANY_VALUE(MO.moveOutDate) AS moveOutDate, ANY_VALUE(MO.lockInPeriod) AS lockInPeriod, ANY_VALUE(MO.noticePeriod) AS noticePeriod FROM MoveOut AS MO JOIN Tenants AS T ON T.id = MO.tenantId LEFT JOIN TenantGuardians AS TG ON T.id = TG.tenantId JOIN Rooms AS R ON R.id = MO.roomId JOIN RoomOptions AS RO ON R.roomOptionId = RO.id LEFT JOIN Properties AS P ON MO.propId = P.id WHERE MO.status = ? AND MO.clientId = ? AND P.status = ? AND DATE(MO.moveInDate) <= ? AND DATE(MO.moveOutDate) >= ? GROUP BY MO.tenantId";
  const data = [
    CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
    clientId,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    endDate,
    startDate
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

moveOutDB.getByPropIdandDateRange = async ({
  propId,
  startDate,
  endDate,
}: { propId: any; startDate: string; endDate: string }) => {
  const query = `SELECT ANY_VALUE(MO.propId) AS propId, ANY_VALUE(T.gender) AS gender, ANY_VALUE(T.email) AS email, ANY_VALUE(T.dob) AS dob, ANY_VALUE(T.aadharNumber) AS aadhaarNumber, ANY_VALUE(T.bloodGroup) AS bloodGroup, ANY_VALUE(T.occupation) AS occupation, ANY_VALUE(MO.kycStatus) AS kycStatus, ANY_VALUE(COALESCE(NULLIF(TG.fatherName, ''), '')) AS fatherName, ANY_VALUE(COALESCE(NULLIF(TG.fatherMobile, ''), '')) AS fatherMobile, ANY_VALUE(COALESCE(NULLIF(TG.fatherOccupation, ''), '')) AS fatherOccupation, ANY_VALUE(COALESCE(NULLIF(TG.fatherAnnualIncome, ''), '')) AS fatherAnnualIncome, ANY_VALUE(COALESCE(NULLIF(TG.motherName, ''), '')) AS motherName, ANY_VALUE(COALESCE(NULLIF(TG.motherMobile, ''), '')) AS motherMobile, ANY_VALUE(COALESCE(NULLIF(TG.localGuardianName, ''), '')) AS localGuardianName, ANY_VALUE(COALESCE(NULLIF(TG.localGuardianMobile, ''), '')) AS localGuardianMobile, ANY_VALUE(COALESCE(NULLIF(TG.localGuardianRelation, ''), '')) AS localGuardianRelation, ANY_VALUE(T.institutionName) AS institutionName, ANY_VALUE(T.institutionEmail) AS institutionEmail, ANY_VALUE(T.title) AS title, ANY_VALUE(T.address) AS address, COUNT(MO.tenantId) AS occupiedBeds, ANY_VALUE(DATE_FORMAT(MO.moveInDate, '%Y-%m-%d')) AS moveInDate, ANY_VALUE(MO.rent) AS rent, ANY_VALUE(MO.security) AS security, ANY_VALUE(T.name) AS name, ANY_VALUE(T.mobile) AS mobile, ANY_VALUE(R.roomNum) AS roomNum, ANY_VALUE(R.extendedName) AS roomName, ANY_VALUE(T.id) AS id, ANY_VALUE(RO.name) AS roomOptionName, ANY_VALUE(MO.rentalCycle) AS rentalCycle, ANY_VALUE(MO.moveOutReason) AS moveOutReason, ANY_VALUE(MO.notes) AS notes, ANY_VALUE(MO.status) AS occupancyStatus, ANY_VALUE(MO.moveOutDate) AS moveOutDate, ANY_VALUE(MO.lockInPeriod) AS lockInPeriod, ANY_VALUE(MO.noticePeriod) AS noticePeriod FROM MoveOut AS MO JOIN Tenants AS T ON T.id = MO.tenantId LEFT JOIN TenantGuardians AS TG ON T.id = TG.tenantId JOIN Rooms AS R ON R.id = MO.roomId JOIN RoomOptions AS RO ON R.roomOptionId = RO.id WHERE MO.status = ? AND MO.propId in (${propId.map(() => '?').join(',')}) AND DATE(MO.moveInDate) <= ? AND DATE(MO.moveOutDate) >= ? GROUP BY MO.tenantId`;
  const data = [CONSTANTS.MOVE_OUT_STATUS.MOVEOUT, ...propId, endDate, startDate];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

moveOutDB.getByGIdX = async ({ gId }: moveOutTypes) => {
  const query = "Select M.gId, M.tenantId, M.clientId, M.propId, M.id, T.name as tenantName, T.mobile as tenantMobile from MoveOut as M join Tenants as T on M.tenantId = T.id where M.gId = ?";
  const data = [gId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

moveOutDB.getByTenantIdAndClientIdForWeb = async ({ tenantId, clientId }: moveOutTypes) => {
  const query =
    "select M.id, 0 as bookingAmt, M.refundStatus, M.isGstEnabled, M.clientId, M.tenantId, M.gId, M.propId, M.roomId, M.bedId, M.floor, M.flatId, M.rent, M.rentalCycle, M.noticePeriod, M.lockInPeriod, M.isOnlinePaymentEnabled as isOnlinePaymentEnabledTenant, M.security, M.agreementPeriod, M.agreementStartDate, M.moveInDate, M.moveOutDate, M.status, C.mobile as ownerMobile, P.name as propName, P.address as propertyAddress, P.streetAddress as propertyStreetAddress, P.isIdVerificationEnabled, P.isPanVerificationEnabled, P.isRentAgreementEnabled, P.isPoliceVerificationEnabled, P.isOnlinePaymentEnabled, R.roomNum from MoveOut as M left join Properties as P on P.id = M.propId left join Rooms as R on R.id = M.roomId left join Clients as C on C.id = M.clientId where M.tenantId = ? and M.clientId = ?";
  const data = [tenantId, clientId];

  //log.info(`Query [${query}], Data [${JSON.stringify(data)}]`);
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

moveOutDB.getByClientIdAndTenantId = async ({
  clientId,
  tenantId,
}: moveOutTypes) => {
  const query = "select * from MoveOut where clientId = ? and tenantId = ?";
  const data = [clientId, tenantId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

moveOutDB.getAllByTenantIdAndClientId = async ({
  clientId,
  tenantId,
}: moveOutTypes) => {
  const query =
    "select O.id, 0 as isFoodOpted, 0 as bookingAmt, null as verificationId, O.isGstEnabled, O.notes, O.kycStatus, O.discountType, O.gId, O.discount, O.discountPeriod, O.discountStartDate, O.discountEndDate, O.isDiscountFromFirstMonth, O.stayType, O.moveOutReason, O.isPoliceVerified, O.isRentAgreementSigned, O.isOnlinePaymentEnabled, O.rentalBond, O.createdAt, O.clientId, O.tenantId, null as isFineEnabled, null as fine, R.flatId, null as fineType, null as gracePeriod, O.electricityReading, O.propId, O.roomId, O.bedId, O.floor, O.rent, O.rentalCycle, O.noticePeriod, O.lockInPeriod, O.security, O.agreementPeriod, O.agreementStartDate, O.moveInDate, O.moveOutDate, O.status, O.rentalType, O.rentalMonths, C.mobile as ownerMobile, P.name as propName, P.type as propType, P.address as propertyAddress, P.streetAddress as propertyStreetAddress, P.isIdVerificationEnabled, P.isPanVerificationEnabled, P.isRentAgreementEnabled, P.isPoliceVerificationEnabled, P.isOnlinePaymentEnabled as propOnlineEnable, R.roomNum, RO.name as roomOptionName, RO.type as roomOptionType, RO.amenities from MoveOut as O left join Properties as P on P.id = O.propId left join Rooms as R on R.id = O.roomId left join RoomOptions as RO on RO.id = R.roomOptionId left join Clients as C on C.id = O.clientId where O.clientId = ? and O.tenantId = ? order by O.id desc";
  const data = [clientId, tenantId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

moveOutDB.updateRefundStatus = async ({
  id,
  refundStatus,
}: moveOutTypes) => {
  const query = `Update MoveOut set refundStatus = ? where id = ?`;
  const data = [refundStatus, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};


moveOutDB.updateRefundStatusToPending = async ({
  clientId,
  tenantId,
}: moveOutTypes) => {
  const query = `Update MoveOut set refundStatus = 0 where clientId = ? and tenantId = ? and refundStatus = ?`;
  const data = [clientId, tenantId, CONSTANTS.REFUND_STATUS.INITIATED];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};


moveOutDB.getByClientIdAndFilters = async ({
  clientId,
  propIds,
  flatIds,
  filterArr,
  filterVal,
  propertiesIds,
  pageNum,
  limit
}: moveOutTypes & { filterArr: any[]; propIds: string; flatIds: string; limit: number; pageNum: number; filterVal: any; propertiesIds: string; }) => {
  let query = `select M.id as occupancyId, M.moveInDate, M.moveOutDate, M.isPoliceVerified, M.isRentAgreementSigned, M.agreementStartDate, M.agreementPeriod, M.floor, M.rent, T.id as tenantId, T.name, T.mobile, T.gender, T.occupation, M.status, T.kycStatus as tenantKycStatus, M.kycStatus, T.device as platform, P.type as propertyType, P.name as propertyName, R.roomNum, R.flatId, (Select SUM(balance) as total from MoveOutDues where tenantId = M.tenantId and clientId = M.clientId) as totalDues, (Select MAX(dueDate) as dueDate from Dues where tenantId = M.tenantId and clientId = M.clientId) as dueDate, (Select value from Documents where tenantId = T.id and propId = P.id and type = ?  and moveOut=1 order by id desc limit 1) as profilePicture from MoveOut as M inner join Tenants as T on T.id = M.tenantId inner join Properties as P on P.id = M.propId inner join Rooms as R on R.id = M.roomId LEFT JOIN Flats as F on F.id = M.flatId WHERE M.clientId = ? AND M.status not in (?) AND M.id IN (SELECT MAX(innerM.id) FROM MoveOut as innerM WHERE innerM.tenantId = M.tenantId and innerM.clientId = M.clientId GROUP BY innerM.tenantId )`;
  const data: any = [
    CONSTANTS.DOCUMENT_TYPES.SELFI,
    clientId,
    CONSTANTS.MOVE_OUT_STATUS.INITIATED,
  ];

  if (filterArr && Array.isArray(filterArr)) {
    for (let filter of filterArr) {
      switch (filter) {
        case "ED":
          //Evicted Tenants With Dues
          query += ` and M.tenantId in (select tenantId from MoveOutDues where clientId = M.clientId)`;
          break;
        case "ECM":
          //Current Month Evicted
          query += ` and Month(M.moveOutDate) = Month(curdate()) and Year(M.moveOutDate) = Year(curdate())`;
          break;
        case "EPR":
          //Evicted Refund Pending
          query += ` and M.refundStatus in (?, ?)`;
          data.push(CONSTANTS.REFUND_STATUS.PENDING);
          data.push(CONSTANTS.REFUND_STATUS.STATEMENT_GENERATED);
          break;
        case "ERD":
          //Evicted Refund Done
          query += ` and M.refundStatus = ?`;
          data.push(CONSTANTS.REFUND_STATUS.PROCESSED);
          break;
        case "CB":
          //Cancelled Booking
          query += ` and M.wasCancelled = 1`;
          break;
        case "MO":
          //Normal Moved Out
          query += ` and M.wasCancelled = 0`;
          break;
        case "WP":
          //Working(FnF) Pending
          query += ` and CURDATE() > DATE_ADD(M.moveOutDate, INTERVAL 30 DAY) and M.refundStatus < ?`;
          data.push(CONSTANTS.REFUND_STATUS.STATEMENT_GENERATED);
          break;
        case "BY":
          //Booked By
          let filterValArr: any = [];
          if (filterVal && typeof filterVal === 'string') {
            filterValArr = filterVal.split(',').map(v => v.trim()).filter(Boolean);
          }
          query += ` and M.bookedBy in (${filterValArr.map(() => '?').join(',')})`;
          data.push(...filterValArr);
          break;
      }
    }
  }

  if (propIds && typeof propIds === 'string') {
    //Property Filter
    let propArr: any = [];
    if (propIds && typeof propIds === 'string') {
      propArr = propIds.split(',').map((v: any) => v.trim()).filter(Boolean);
    }
    query += ` and M.propId in (${propArr.map(() => '?').join(',')})`;
    data.push(...propArr);
  }

  if (flatIds && typeof flatIds === 'string') {
    //Flat Filter
    let flatArr: any = [];
    // if (flatIds && typeof flatIds === 'string') {
    //   flatArr = flatIds.split(',').map((v: any) => v.trim()).filter(Boolean);
    // }
    // query += ` and M.flatId in (${flatArr.map(() => '?').join(',')})`;
    // data.push(...flatArr);
    query += ` and M.flatId = ?`;
    data.push(flatIds);
  }

  if (propertiesIds) {
    query += ` and M.propId in (${propertiesIds})`
  }

  const offset: number = (pageNum - 1) * limit;
  if (filterArr && Array.isArray(filterArr) && filterArr.includes("RS")) {
    query += ` order by P.name, CASE WHEN M.flatId IS NOT NULL THEN CAST(F.name AS UNSIGNED) ELSE CAST(M.floor AS UNSIGNED) END, R.id, M.id limit ?, ?`;
  } else if (filterArr && Array.isArray(filterArr) && filterArr.includes("CS")) {
    query += ` order by M.createdAt desc, M.id desc limit ?, ?`;
  } else {
    query += ` order by M.id desc, M.createdAt desc limit ?, ?`;
  }
  data.push(`${offset}`);
  data.push(`${limit}`);
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

moveOutDB.getCountByClientIdAndFilters = async ({
  clientId,
  propIds,
  flatIds,
  filterArr,
  filterVal,
  propertiesIds,
}: moveOutTypes & { filterArr: any[]; propIds: string; flatIds: string; limit: number; pageNum: number; filterVal: any; propertiesIds: string; }) => {
  let query = `select Count(M.id) as count from MoveOut as M inner join Tenants as T on T.id = M.tenantId inner join Properties as P on P.id = M.propId inner join Rooms as R on R.id = M.roomId LEFT JOIN EqaroTenants as ET ON M.tenantId = ET.tenantId AND M.propId = ET.propId WHERE M.clientId = ? AND M.status not in (?) AND M.id IN (SELECT MAX(innerM.id) FROM MoveOut as innerM WHERE innerM.tenantId = M.tenantId and innerM.clientId = M.clientId GROUP BY innerM.tenantId )`;
  const data: any = [
    clientId,
    CONSTANTS.MOVE_OUT_STATUS.INITIATED,
  ];

  if (filterArr && Array.isArray(filterArr)) {
    for (let filter of filterArr) {
      switch (filter) {
        case "ED":
          //Evicted Tenants With Dues
          query += ` and M.tenantId in (select tenantId from MoveOutDues where clientId = M.clientId)`;
          break;
        case "ECM":
          //Current Month Evicted
          query += ` and Month(M.moveOutDate) = Month(curdate()) and Year(M.moveOutDate) = Year(curdate())`;
          break;
        case "EPR":
          //Evicted Refund Pending
          query += ` and M.refundStatus in (?, ?)`;
          data.push(CONSTANTS.REFUND_STATUS.PENDING);
          data.push(CONSTANTS.REFUND_STATUS.STATEMENT_GENERATED);
          break;
        case "ERD":
          //Evicted Refund Done
          query += ` and M.refundStatus = ?`;
          data.push(CONSTANTS.REFUND_STATUS.PROCESSED);
          break;
        case "CB":
          //Cancelled Booking
          query += ` and M.wasCancelled = 1`;
          break;
        case "MO":
          //Normal Moved Out
          query += ` and M.wasCancelled = 0`;
          break;
        case "WP":
          //Working(FnF) Pending
          query += ` and CURDATE() > DATE_ADD(M.moveOutDate, INTERVAL 30 DAY) and M.refundStatus < ?`;
          data.push(CONSTANTS.REFUND_STATUS.STATEMENT_GENERATED);
          break;
        case "BY":
          //Booked By
          let filterValArr: any = [];
          if (filterVal && typeof filterVal === 'string') {
            filterValArr = filterVal.split(',').map(v => v.trim()).filter(Boolean);
          }
          query += ` and M.bookedBy in (${filterValArr.map(() => '?').join(',')})`;
          data.push(...filterValArr);
          break;
      }
    }
  }

  if (propIds && typeof propIds === 'string') {
    //Property Filter
    let propArr: any = [];
    if (propIds && typeof propIds === 'string') {
      propArr = propIds.split(',').map((v: any) => v.trim()).filter(Boolean);
    }
    query += ` and M.propId in (${propArr.map(() => '?').join(',')})`;
    data.push(...propArr);
  }

  if (flatIds && typeof flatIds === 'string') {
    //Flat Filter
    let flatArr: any = [];
    // if (flatIds && typeof flatIds === 'string') {
    //   flatArr = flatIds.split(',').map((v: any) => v.trim()).filter(Boolean);
    // }
    // query += ` and M.flatId in (${flatArr.map(() => '?').join(',')})`;
    // data.push(...flatArr);
    query += ` and M.flatId = ?`;
    data.push(flatIds);
  }

  if (propertiesIds) {
    query += ` and M.propId in (${propertiesIds})`
  }

  query += ` order by M.moveOutDate desc`

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

moveOutDB.deleteByRoomIdBedIdAndTenantId = async ({
  tenantId,
  roomId,
  bedId,
  clientId,
}: moveOutTypes) => {
  const query =
    "delete from MoveOut where tenantId = ? and roomId = ? and bedId = ? and clientId=? and status=1 limit 1";
  const data = [tenantId, roomId, bedId, clientId];
  const [rows] = await DB.execute<ResultSetHeader[]>(query, data);
  if (rows?.length > 0) return true;
  else return false;
};

moveOutDB.getCurMonthBookingCountByProp = async ({clientId}: moveOutTypes) => {
  const query = `
  Select propId, count(distinct tenantId) as count from MoveOut where clientId = ? and Month(moveInDate) = MONTH(CURDATE()) and Year(moveInDate) = YEAR(CURDATE()) and status = ? group by propId`;
  const data = [
    clientId, 
    CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

moveOutDB.getByClientIdAndBookedByAndDate = async ({
  clientId,
  pageNum,
  limit,
  bookedBy,
  startDate,
  endDate,
}: moveOutTypes & { pageNum: number; limit: number; startDate: string; endDate: string; }) => {
  
  let query =
  `select O.id as occupancyId, 'movedOut' as occupancy, O.moveInDate, O.moveOutDate, O.isPoliceVerified, O.isRentAgreementSigned, O.agreementStartDate, O.agreementPeriod, O.floor, O.rent, T.id as tenantId, T.name, T.mobile, T.gender, T.occupation, O.status, T.kycStatus as tenantKycStatus, O.kycStatus, T.device as platform, P.type as propertyType, P.name as propertyName, R.roomNum, R.flatId, (Select SUM(balance) as total from Dues where tenantId = O.tenantId and clientId = O.clientId) as totalDues, (Select MAX(dueDate) as dueDate from Dues where tenantId = O.tenantId and clientId = O.clientId) as dueDate, (Select value from Documents where tenantId = T.id and propId = P.id and type = ?  and moveOut=0 order by id desc limit 1) as profilePicture from MoveOut as O inner join Tenants as T on T.id = O.tenantId inner join Properties as P on P.id = O.propId inner join Rooms as R on R.id = O.roomId WHERE O.clientId = ? AND O.status = ? AND O.id IN (SELECT MAX(innerO.id) FROM MoveOut as innerO WHERE innerO.tenantId = O.tenantId and innerO.clientId = O.clientId GROUP BY innerO.tenantId ) and O.bookedBy = ? and O.moveInDate BETWEEN ? and ? and Not Exists (select 1 from Occupancies as occ where occ.clientId = ? and O.tenantId = occ.tenantId and occ.bookedBy = ?) order by O.id desc limit ?, ? `;

  const data: any = [
    CONSTANTS.DOCUMENT_TYPES.SELFI,
    clientId,
    CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
    bookedBy,
    startDate,
    endDate,
    clientId,
    bookedBy,
  ];
  
  const offset: number = (pageNum - 1) * limit;
  data.push(`${offset}`)
  data.push(`${limit}`)

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

moveOutDB.getCountByClientIdAndBookedByAndDate = async ({
  clientId,
  bookedBy,
  startDate,
  endDate,
}: moveOutTypes & { pageNum: number; limit: number; startDate: string; endDate: string; }) => {
  
  let query =
  `select COUNT(*) as total from MoveOut as O inner join Tenants as T on T.id = O.tenantId inner join Properties as P on P.id = O.propId inner join Rooms as R on R.id = O.roomId WHERE O.clientId = ? AND O.status = ? AND O.id IN (SELECT MAX(innerO.id) FROM MoveOut as innerO WHERE innerO.tenantId = O.tenantId and innerO.clientId = O.clientId GROUP BY innerO.tenantId) and O.bookedBy = ? and O.moveInDate BETWEEN ? and ? and Not Exists (select 1 from Occupancies as occ where occ.clientId = ? and O.tenantId = occ.tenantId and occ.bookedBy = ?)`;

  const data: any = [
    clientId,
    CONSTANTS.MOVE_OUT_STATUS.MOVEOUT,
    bookedBy,
    startDate,
    endDate,
    clientId,
    bookedBy,
  ];

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

moveOutDB.getAllByTenantIdAndClientIdPropId = async ({
  clientId,
  tenantId,
  propId,
}: moveOutTypes) => {
  const query =
    "select O.id, 0 as isFoodOpted, 0 as bookingAmt, null as verificationId, O.isGstEnabled, O.notes, O.kycStatus, O.discountType, O.gId, O.discount, O.discountPeriod, O.discountStartDate, O.discountEndDate, O.isDiscountFromFirstMonth, O.stayType, O.moveOutReason, O.isPoliceVerified, O.isRentAgreementSigned, O.isOnlinePaymentEnabled, O.rentalBond, O.createdAt, O.clientId, O.tenantId, null as isFineEnabled, null as fine, R.flatId, null as fineType, null as gracePeriod, O.electricityReading, O.propId, O.roomId, O.bedId, O.floor, O.rent, O.rentalCycle, O.noticePeriod, O.lockInPeriod, O.security, O.agreementPeriod, O.agreementStartDate, O.moveInDate, O.moveOutDate, O.status, O.rentalType, O.rentalMonths, C.mobile as ownerMobile, P.name as propName, P.type as propType, P.address as propertyAddress, P.streetAddress as propertyStreetAddress, P.isIdVerificationEnabled, P.isPanVerificationEnabled, P.isRentAgreementEnabled, P.isPoliceVerificationEnabled, P.isOnlinePaymentEnabled as propOnlineEnable, R.roomNum, RO.name as roomOptionName, RO.type as roomOptionType, RO.amenities from MoveOut as O left join Properties as P on P.id = O.propId left join Rooms as R on R.id = O.roomId left join RoomOptions as RO on RO.id = R.roomOptionId left join Clients as C on C.id = O.clientId where O.clientId = ? and O.tenantId = ? and O.propId = ? order by O.id desc";
  const data = [clientId, tenantId, propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

export default moveOutDB;