import mysql, { ResultSetHeader, RowDataPacket } from "mysql2";
import DB from "../config/database/db";
import propertyTypes from "../schemas/property.schema";
import locationTypes from "../schemas/location.schema";
import propertyStaffTypes from "../schemas/propertyStaff.schema";
import CONSTANTS from "../config/constants";
import log from "../config/log";

const propertyDB: any = {};

propertyDB.getCount = async ({ clientId }: propertyTypes) => {
  const query = "Select Count(id) as count from Properties where clientId = ? and status != ?";
  const data = [clientId, CONSTANTS.PROPERTY_STATUS.DELETED];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  return rows[0];
};

propertyDB.getById = async ({ id }: propertyTypes) => {
  const query =
    "Select P.id, P.wifiPassword, P.isVisibleOnWebsite, P.isFoodEnabled, P.tallyStatus, P.locationId, P.source, P.gId, P.isGroundIncluded, P.fine, P.fineType, P.isPoliceVerificationEnabled, P.isIdVerificationEnabled, P.isPanVerificationEnabled, P.isRentAgreementEnabled, P.isOnlinePaymentEnabled, P.isPartialPaymentEnabled, P.isBondAvailable, P.name, P.clientId, P.ownerName, P.ownerMobile, P.ownerEmail, P.streetAddress as address, P.type, P.rentalCycle, P.gracePeriod, P.tenantPreference, P.security, P.agreementPeriod, P.noticePeriod, P.floorCount, P.lockInPeriod, P.status, P.bankId, P.businessName, P.payuKey, P.payuMid, P.createdAt, P.updatedAt, P.longitude, P.latitude, P.isGstEnabled, P.businessName, P.gstNo, P.invoiceNoPrefix, P.invoiceNo, P.gstCharges, P.pincode, P.isAadhaarInputEnabled, P.paymentGateway, P.isStampPaperRequired from Properties as P where P.id = ?";
  const data = [id];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

propertyDB.getByBankId = async ({ bankId }: propertyTypes) => {
  const query =
    "Select P.id, P.locationId, P.gId, P.isGroundIncluded, P.isPoliceVerificationEnabled, P.isIdVerificationEnabled, P.isPanVerificationEnabled, P.isRentAgreementEnabled, P.isOnlinePaymentEnabled, P.name, P.clientId, P.ownerName, P.ownerMobile, P.address, P.streetAddress, P.type, P.rentalCycle, P.gracePeriod, P.fine, P.tenantPreference, P.security, P.agreementPeriod, P.noticePeriod, P.floorCount, P.lockInPeriod, P.status, P.bankId,  P.createdAt, P.updatedAt from Properties as P where P.bankId = ?";
  const data = [bankId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

propertyDB.getPropsByStaffId = async ({ staffId }: propertyStaffTypes) => {
  const query =
    "Select P.id, P.locationId, P.gId, P.isGroundIncluded, P.isPoliceVerificationEnabled, P.isIdVerificationEnabled, P.isPanVerificationEnabled, P.isRentAgreementEnabled, P.isOnlinePaymentEnabled, P.name, P.clientId, P.ownerName, P.ownerMobile, P.address, P.streetAddress, P.type, P.rentalCycle, P.gracePeriod, P.fine, P.tenantPreference, P.security, P.agreementPeriod, P.noticePeriod, P.floorCount, P.lockInPeriod, P.status, P.bankId,  P.createdAt, P.updatedAt from Properties as P inner join PropertyStaff as PS on PS.propId = P.id where PS.staffId = ? order by P.name asc";
  const data = [staffId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

propertyDB.getAllInfoById = async ({ id }: propertyTypes) => {
  const query =
    "Select P.id, P.locationId, P.gId, P.isGroundIncluded, P.name, P.clientId, P.ownerName, P.ownerMobile, P.address, P.streetAddress, P.type, P.rentalCycle, P.gracePeriod, P.fine, P.tenantPreference, P.security, P.agreementPeriod, P.noticePeriod, P.floorCount, P.lockInPeriod, P.status, P.bankId, P.isPoliceVerificationEnabled, P.isIdVerificationEnabled, P.isPanVerificationEnabled, P.isOnlinePaymentEnabled, P.isBondAvailable, P.createdAt, P.updatedAt from Properties as P where P.id = ?";
  const data = [id];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

propertyDB.getBankLinkedProps = async ({ bankId, clientId }: propertyTypes) => {
  //const query = "Select * from (Select id, name, status, IF(bankId=?, 1, 0) as isLinked from Properties where clientId=? and (bankId = ? or ISNULL(bankId)) order by id desc) as v where v.status = ? or (v.status = ? and v.isLinked = ?)";
  const query = "Select * from (Select id, name, status, IF(bankId=?, 1, 0) as isLinked from Properties where clientId=? order by id desc) as v where v.status = ? or (v.status = ? and v.isLinked = ?)";
  const data = [
    bankId,
    clientId,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    CONSTANTS.PROPERTY_STATUS.INACTIVE,
    "1",
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

propertyDB.getRentAgreementTemplate = async ({
  clientId,
  propId,
}: propertyTypes & { propId: number }) => {
  const query =
    "Select * from RentAgreements where clientId = ? and propId = ? order by id desc limit 1";
  const data = [clientId, propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

propertyDB.getByNameAndAdress = async ({
  name,
  address,
  streetAddress,
  clientId,
}: propertyTypes) => {
  const query =
    "Select P.id, P.locationId, P.gId, P.name, P.clientId, P.ownerName, P.isGroundIncluded, P.ownerMobile, P.address, P.streetAddress, P.type, P.rentalCycle, P.gracePeriod, P.fine, P.tenantPreference, P.security, P.agreementPeriod, P.noticePeriod, P.floorCount, P.lockInPeriod, P.status, P.bankId,  P.createdAt, P.updatedAt from Properties as P where P.name = ? and P.address = ? and P.streetAddress = ? and P.clientId = ?";
  const data = [name, address, streetAddress, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

propertyDB.getByGId = async ({ gId }: propertyTypes) => {
  const query =
    "Select P.id, P.locationId, P.longitude, P.latitude, P.gId, P.name, P.clientId, P.ownerName, P.isGroundIncluded, P.ownerMobile, P.address, P.streetAddress, P.type, P.rentalCycle, P.gracePeriod, P.fine, P.tenantPreference, P.security, P.agreementPeriod, P.noticePeriod, P.floorCount, P.lockInPeriod, P.status, P.bankId,  P.createdAt, P.updatedAt from Properties as P where P.gId = ?";
  const data = [gId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

propertyDB.getPropIdsByClientId = async ({
  clientId,
  status,
}: propertyTypes) => {
  const query =
    "Select id, name, status, address from Properties where clientId = ? and status = ? order by id desc";
  const data = [clientId, status];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

propertyDB.getStampEnabledByClientId = async ({
  clientId,
}: propertyTypes) => {
  const query =
    "Select id, gId, name from Properties where clientId = ? and status = ? and isStampPaperRequired = 1 order by id desc";
  const data = [clientId, CONSTANTS.PROPERTY_STATUS.ACTIVE];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

propertyDB.getPropIdsByClientIdForStaff = async ({
  clientId,
  staffId,
  status,
}: propertyTypes & { staffId: number }) => {
  const query =
    "Select P.id, P.name, P.status, P.address from Properties as P join PropertyStaff as PS on P.id = PS.propId where P.clientId = ? and P.status = ? and PS.staffId = ? order by id desc";
  const data = [clientId, status, staffId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

propertyDB.getActivePropIdsByClientId = async ({
  clientId,
  status,
}: propertyTypes) => {
  const query =
    "Select id, name, status from Properties where clientId = ? and status = ? order by id desc";
  const data = [clientId, status];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

propertyDB.getSearchResultsByClientId = async ({
  clientId,
  pageNum,
  limit,
  searchVal,
}: propertyTypes & { pageNum: number; limit: number; searchVal: string }) => {
  const query =
    "Select P.id, P.locationId, P.gId, P.name, P.clientId, P.isGroundIncluded, P.ownerName, P.ownerMobile, P.streetAddress as address, P.streetAddress, P.longitude, P.latitude, P.type, P.rentalCycle, P.gracePeriod, P.fine, P.tenantPreference, P.security, P.agreementPeriod, P.floorCount, P.noticePeriod, P.lockInPeriod, P.status, P.bankId,  P.createdAt, P.updatedAt from Properties as P where P.clientId = ? and P.status != ? and P.name like ? order by P.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  const data = [
    clientId,
    CONSTANTS.PROPERTY_STATUS.DELETED,
    `%${searchVal}%`,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

propertyDB.getByClientId = async ({
  clientId,
  pageNum,
  limit,
  locationId,
}: propertyTypes & { pageNum: number; limit: number }) => {
  let query =
    "Select P.id, P.locationId, P.gId, P.name, P.clientId, P.isGroundIncluded, P.ownerName, P.ownerMobile, P.streetAddress as address, P.streetAddress, P.longitude, P.latitude, P.type, P.rentalCycle, P.gracePeriod, P.fine, P.tenantPreference, P.security, P.agreementPeriod, P.floorCount, P.noticePeriod, P.lockInPeriod, P.status, P.bankId,  P.createdAt, P.updatedAt from Properties as P where P.clientId = ? and P.status != ? ";
  const offset: number = (pageNum - 1) * limit;
  const data: any = [
    clientId,
    CONSTANTS.PROPERTY_STATUS.DELETED,
  ];

  if (Number(locationId) && Number(locationId) > 0) {
    query += ` and P.locationId = ?`;
    data.push(locationId);
  }

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

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

//Different order by
propertyDB.getByClientIdForWeb = async ({
  clientId,
  pageNum,
  limit,
}: propertyTypes & { pageNum: number; limit: number }) => {
  const query =
    "Select P.id, P.locationId, P.gId, P.name, P.clientId, P.isGroundIncluded, P.ownerName, P.ownerMobile, P.streetAddress as address, P.streetAddress, P.longitude, P.latitude, P.type, P.rentalCycle, P.gracePeriod, P.fine, P.tenantPreference, P.security, P.agreementPeriod, P.floorCount, P.noticePeriod, P.lockInPeriod, P.status, P.bankId,  P.createdAt, P.updatedAt from Properties as P where P.clientId = ? and P.status != ? order by P.id desc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  const data = [
    clientId,
    CONSTANTS.PROPERTY_STATUS.DELETED,
    `${offset}`,
    `${limit}`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

propertyDB.getAllByClientId = async ({ clientId }: propertyTypes) => {
  const query =
    "Select P.id, P.locationId, P.gId, P.name, P.isPartialPaymentEnabled, P.clientId, P.isGroundIncluded, P.ownerName, P.ownerMobile, P.streetAddress as address, P.streetAddress, P.type, P.rentalCycle, P.gracePeriod, P.fine, P.tenantPreference, P.security, P.agreementPeriod, P.floorCount, P.noticePeriod, P.lockInPeriod, P.status, P.bankId,  P.createdAt, P.updatedAt from Properties as P where P.clientId = ? and P.status != ? order by P.id desc";
  const data = [clientId, CONSTANTS.PROPERTY_STATUS.DELETED];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

propertyDB.getAllActiveByClientId = async ({ clientId }: propertyTypes) => {
  const query =
    "Select P.id, P.locationId, P.isPartialPaymentEnabled, P.gId, P.name, P.clientId, P.isGroundIncluded, P.ownerName, P.ownerMobile, P.streetAddress as address, P.streetAddress, P.type, P.rentalCycle, P.gracePeriod, P.fine, P.tenantPreference, P.security, P.agreementPeriod, P.floorCount, P.noticePeriod, P.lockInPeriod, P.status, P.bankId,  P.createdAt, P.updatedAt from Properties as P where P.clientId = ? and P.status = ? order by P.id desc";
  const data = [clientId, CONSTANTS.PROPERTY_STATUS.ACTIVE];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

propertyDB.getAllActiveByClientIdForStaff = async ({ clientId, staffId }: propertyTypes & { staffId: number }) => {
  const query =
    "Select P.id, P.locationId, P.isPartialPaymentEnabled, P.gId, P.name, P.clientId, P.isGroundIncluded, P.ownerName, P.ownerMobile, P.streetAddress as address, P.streetAddress, P.type, P.rentalCycle, P.gracePeriod, P.fine, P.tenantPreference, P.security, P.agreementPeriod, P.floorCount, P.noticePeriod, P.lockInPeriod, P.status, P.bankId,  P.createdAt, P.updatedAt from Properties as P join PropertyStaff as PS on PS.propId = P.id where P.clientId = ? and P.status = ? and PS.staffId = ? order by P.id desc";
  const data = [clientId, CONSTANTS.PROPERTY_STATUS.ACTIVE, staffId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

propertyDB.getAllActiveWithVacanyByClientId = async ({ clientId }: propertyTypes) => {
  const query =
    "Select P.id, P.locationId, P.gId, P.name, P.clientId, P.isGroundIncluded, P.ownerName, P.ownerMobile, P.streetAddress as address, P.streetAddress, P.type, P.rentalCycle, P.gracePeriod, P.fine, P.tenantPreference, P.security, P.agreementPeriod, P.floorCount, P.noticePeriod, P.lockInPeriod, P.status, P.bankId,  P.createdAt, P.updatedAt from Properties as P where P.clientId = ? and P.status = ? and P.id in (select R.propId from Rooms as R join Properties as innerP on R.propId = innerP.id join Beds as B on B.roomId = R.id where innerP.clientId = ? and R.status not in (?, ?)) order by P.id desc";
  const data = [
    clientId,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    clientId,
    CONSTANTS.ROOM_STATUS.INACTIVE,
    CONSTANTS.ROOM_STATUS.OCCUPIED
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

propertyDB.getAllActiveWithVacanyByClientIdForStaff = async ({
  clientId,
  staffId,
}: propertyTypes & { staffId: number }) => {
  const query =
    "Select P.id, P.locationId, P.gId, P.name, P.clientId, P.isGroundIncluded, P.ownerName, P.ownerMobile, P.streetAddress as address, P.streetAddress, P.type, P.rentalCycle, P.gracePeriod, P.fine, P.tenantPreference, P.security, P.agreementPeriod, P.floorCount, P.noticePeriod, P.lockInPeriod, P.status, P.bankId,  P.createdAt, P.updatedAt from Properties as P join PropertyStaff as PS on PS.propId = P.id where P.clientId = ? and P.status = ? and PS.staffId = ? and P.id in (select R.propId from Rooms as R join Properties as innerP on R.propId = innerP.id join Beds as B on B.roomId = R.id where innerP.clientId = ? and R.status not in (?, ?)) order by P.id desc";
  const data = [
    clientId,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    staffId,
    clientId,
    CONSTANTS.ROOM_STATUS.INACTIVE,
    CONSTANTS.ROOM_STATUS.OCCUPIED
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

propertyDB.getByClientIdAndStatus = async ({
  clientId,
  pageNum,
  limit,
  status,
  locationId,
}: propertyTypes & { pageNum: number; limit: number }) => {
  let query =
    "Select P.id, P.locationId, P.gId, P.name, P.isGroundIncluded, P.clientId, P.ownerName, P.ownerMobile, P.streetAddress as address, P.streetAddress, P.longitude, P.latitude, P.type, P.rentalCycle, P.gracePeriod, P.fine, P.tenantPreference, P.security, P.agreementPeriod, P.floorCount, P.noticePeriod, P.lockInPeriod, P.status, P.bankId,  P.createdAt, P.updatedAt from Properties as P where P.clientId = ? and P.status = ? ";
  //const query = "Select P.id, P.gId, P.name, P.isGroundIncluded, P.clientId, P.ownerName, P.ownerMobile, concat(P.streetAddress, ', ',  P.address) as address, P.streetAddress, P.type, P.rentalCycle, P.gracePeriod, P.fine, P.tenantPreference, P.security, P.agreementPeriod, P.floorCount, P.noticePeriod, P.lockInPeriod, P.status, P.bankId,  P.createdAt, P.updatedAt from Properties as P where P.clientId = ? and P.status = ? order by P.name asc";
  const offset: number = (pageNum - 1) * limit;
  //const data = [clientId, status];
  const data: any = [clientId, status,];

  if (Number(locationId) && Number(locationId) > 0) {
    query += ` and P.locationId = ?`;
    data.push(locationId);
  }

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

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

propertyDB.getByClientIdIncompleteProps = async ({
  clientId,
  pageNum,
  limit,
  locationId,
}: propertyTypes & { pageNum: number; limit: number }) => {
  let query =
    "Select P.id, P.locationId, P.gId, P.name, P.isGroundIncluded, P.clientId, P.ownerName, P.ownerMobile, P.streetAddress as address, P.streetAddress, P.longitude, P.latitude, P.type, P.rentalCycle, P.gracePeriod, P.fine, P.tenantPreference, P.security, P.agreementPeriod, P.floorCount, P.noticePeriod, P.lockInPeriod, P.status, P.bankId,  P.createdAt, P.updatedAt from Properties as P where P.clientId = ? and P.status in (?, ?) ";
  //const query = "Select P.id, P.gId, P.name, P.isGroundIncluded, P.clientId, P.ownerName, P.ownerMobile, concat(P.streetAddress, ', ',  P.address) as address, P.streetAddress, P.type, P.rentalCycle, P.gracePeriod, P.fine, P.tenantPreference, P.security, P.agreementPeriod, P.floorCount, P.noticePeriod, P.lockInPeriod, P.status, P.bankId,  P.createdAt, P.updatedAt from Properties as P where P.clientId = ? and P.status = ? order by P.name asc";
  const offset: number = (pageNum - 1) * limit;
  //const data = [clientId, status];
  const data: any = [clientId, CONSTANTS.PROPERTY_STATUS.PENDING, CONSTANTS.PROPERTY_STATUS.COMPLETED,];

  if (Number(locationId) && Number(locationId) > 0) {
    query += ` and P.locationId = ?`;
    data.push(locationId);
  }

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

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

propertyDB.getByClientIdIncompletePropsForStaff = async ({
  clientId,
  staffId,
  pageNum,
  limit,
  locationId,
}: propertyTypes & { pageNum: number; limit: number; staffId: number }) => {
  let query =
    `Select P.id, P.locationId, P.gId, P.name, P.isGroundIncluded, P.clientId, P.ownerName, P.ownerMobile, P.streetAddress as address, P.streetAddress, P.longitude, P.latitude, P.type, P.rentalCycle, P.gracePeriod, P.fine, P.tenantPreference, P.security, P.agreementPeriod, P.floorCount, P.noticePeriod, P.lockInPeriod, P.status, P.bankId,  P.createdAt, P.updatedAt from Properties as P join PropertyStaff as PS on P.id = PS.propId where P.clientId = ? and PS.staffId = ? and P.status in (?, ?) `;
  //const query = "Select P.id, P.gId, P.name, P.isGroundIncluded, P.clientId, P.ownerName, P.ownerMobile, concat(P.streetAddress, ', ',  P.address) as address, P.streetAddress, P.type, P.rentalCycle, P.gracePeriod, P.fine, P.tenantPreference, P.security, P.agreementPeriod, P.floorCount, P.noticePeriod, P.lockInPeriod, P.status, P.bankId,  P.createdAt, P.updatedAt from Properties as P where P.clientId = ? and P.status = ? order by P.name asc";
  const offset: number = (pageNum - 1) * limit;
  //const data = [clientId, status];
  const data: any = [clientId, staffId, CONSTANTS.PROPERTY_STATUS.PENDING, CONSTANTS.PROPERTY_STATUS.COMPLETED,];

  if (Number(locationId) && Number(locationId) > 0) {
    query += ` and P.locationId = ?`;
    data.push(locationId);
  }

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

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

propertyDB.getByClientAndStatusAndBank = async ({
  clientId,
  pageNum,
  limit,
  status,
}: propertyTypes & { pageNum: number; limit: number }) => {
  const query =
    "Select P.id, P.locationId, P.gId, P.name, P.isGroundIncluded, P.clientId, P.ownerName, P.ownerMobile, P.streetAddress as address, P.streetAddress, P.type, P.rentalCycle, P.gracePeriod, P.fine, P.tenantPreference, P.security, P.agreementPeriod, P.floorCount, P.noticePeriod, P.lockInPeriod, P.status, P.bankId,  P.createdAt, P.updatedAt from Properties as P where P.clientId = ? and P.status = ? and P.bankId IS NULL order by P.name asc limit ?, ?";
  const offset: number = (pageNum - 1) * limit;
  const data = [clientId, status, `${offset}`, `${limit}`];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

propertyDB.addBasicDetails = async ({
  gId,
  clientId,
  name,
  ownerName,
  ownerMobile,
  address,
  streetAddress,
  type,
  floorCount,
  isGroundIncluded,
  pincode,
  parentId = null,
}: propertyTypes) => {
  ownerMobile = ownerMobile.toString().substring(ownerMobile.length - 10);

  const query =
    "Insert into Properties (gId,clientId,name,ownerName,ownerMobile,address,streetAddress,type,floorCount,isGroundIncluded,pincode, parentId) values (?,?,?,?,?,?,?,?,?,?,?,?)";
  const data = [
    gId,
    clientId,
    name,
    ownerName,
    ownerMobile,
    address,
    streetAddress,
    type,
    floorCount,
    isGroundIncluded,
    pincode,
    parentId,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

propertyDB.addTenantAgreementDetails = async ({
  tenantPreference,
  security,
  agreementPeriod,
  noticePeriod,
  lockInPeriod,
  id,
}: propertyTypes) => {
  const query =
    "Update Properties set tenantPreference=?, security=?, agreementPeriod=?, noticePeriod=?, lockInPeriod=? where id=?";
  const data = [
    tenantPreference,
    security,
    agreementPeriod,
    noticePeriod,
    lockInPeriod,
    id,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

propertyDB.addRentDetails = async ({
  rentalCycle,
  gracePeriod,
  fine,
  fineType,
  id,
  status,
}: propertyTypes) => {
  const query =
    "Update Properties set rentalCycle=?, gracePeriod=?, fine=?, fineType=?, status=? where id=?";
  const data = [rentalCycle, gracePeriod, fine, fineType, status, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

propertyDB.updateBankId = async ({ id, bankId }: propertyTypes) => {
  const query = "Update Properties set bankId=? where id=?";
  const data = [bankId, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

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

propertyDB.getListWithClientId = async ({ clientId }: propertyTypes) => {
  const query =
    "SELECT P.id, P.name, P.type, P.gId, P.tenantPreference, P.locationId FROM Properties AS P WHERE P.clientId = ? and status =? order by P.name asc";
  const data = [clientId, CONSTANTS.PROPERTY_STATUS.ACTIVE];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

propertyDB.updateRentingRules = async ({
  id,
  name,
  tenantPreference,
  rentalCycle,
  noticePeriod,
  lockInPeriod,
  agreementPeriod,
  security,
  locationId,
}: propertyTypes) => {
  const query =
    "Update Properties set name = ?, tenantPreference = ?, rentalCycle = ?, noticePeriod = ?, lockInPeriod = ?, agreementPeriod = ?, security = ?, locationId = ? where id = ?";
  const data = [
    name,
    tenantPreference,
    rentalCycle,
    noticePeriod,
    lockInPeriod,
    agreementPeriod,
    security,
    locationId,
    id,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

propertyDB.updateRentingRulesForAll = async ({
  clientId,
  rentalCycle,
  noticePeriod,
  lockInPeriod,
  agreementPeriod,
  security,
  locationId,
}: propertyTypes) => {
  const query =
    "Update Properties set rentalCycle = ?, noticePeriod = ?, lockInPeriod = ?, agreementPeriod = ?, security = ?, locationId = ? where clientId = ?";
  const data = [
    rentalCycle,
    noticePeriod,
    lockInPeriod,
    agreementPeriod,
    security,
    locationId,
    clientId,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

propertyDB.updateManagementDetails = async ({
  id,
  ownerName,
  ownerMobile,
  ownerEmail,
  businessName,
  gstNo,
}: propertyTypes) => {
  const query =
    "Update Properties set ownerName=?, ownerMobile=?, ownerEmail = ?, businessName = ?, gstNo = ? where id=?";
  const data = [ownerName, ownerMobile, ownerEmail, businessName, gstNo, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

propertyDB.getSummaryByClientId = async ({ clientId }: propertyTypes) => {
  const query = `select
    (select count(id) from Properties where clientId = ? and status != ?) as total,
    (select count(id) from Properties where clientId = ? and status = ?) as active,
    (select count(id) from Properties where clientId = ? and status = ?) as inactive,
    (select count(id) from Properties where clientId = ? and status = ?) as completed,
    (select count(id) from Properties where clientId = ? and (status = ? || status = ?)) as pending
    from Properties limit 1;
    `;
  const data = [
    clientId,
    CONSTANTS.PROPERTY_STATUS.DELETED,
    clientId,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    clientId,
    CONSTANTS.PROPERTY_STATUS.INACTIVE,
    clientId,
    CONSTANTS.PROPERTY_STATUS.COMPLETED,
    clientId,
    CONSTANTS.PROPERTY_STATUS.PENDING,
    CONSTANTS.PROPERTY_STATUS.COMPLETED,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

propertyDB.getSummaryByStaffId = async ({ staffId }: propertyStaffTypes) => {
  const query = `select
    (select count(P.id) from Properties as P join PropertyStaff as PS on PS.propId = P.id where PS.staffId = ?) as total,
    (select count(P.id) from Properties as P join PropertyStaff as PS on PS.propId = P.id where PS.staffId = ? and P.status = ?) as active,
    (select count(P.id) from Properties as P join PropertyStaff as PS on PS.propId = P.id where PS.staffId = ? and P.status = ?) as inactive,
    (select count(P.id) from Properties as P join PropertyStaff as PS on PS.propId = P.id where PS.staffId = ? and P.status = ?) as completed,
    (select count(P.id) from Properties as P join PropertyStaff as PS on PS.propId = P.id where PS.staffId = ? and (P.status = ? || P.status = ?)) as pending
    `;
  const data = [
    staffId,
    staffId,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    staffId,
    CONSTANTS.PROPERTY_STATUS.INACTIVE,
    staffId,
    CONSTANTS.PROPERTY_STATUS.COMPLETED,
    staffId,
    CONSTANTS.PROPERTY_STATUS.PENDING,
    CONSTANTS.PROPERTY_STATUS.COMPLETED,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

propertyDB.getSummaryByClientIdX = async ({ clientId, locationId, }: propertyTypes) => {
  let query = `select 
    SUM(CASE WHEN status = ? THEN 0 ELSE 1 END) as total,
    SUM(CASE WHEN status = ? THEN 1 ELSE 0 END) as active,
    SUM(CASE WHEN status = ? THEN 1 ELSE 0 END) as inactive,
    SUM(CASE WHEN status = ? THEN 1 ELSE 0 END) as completed,
    SUM(CASE WHEN status in (?, ?) THEN 1 ELSE 0 END) as pending
    from Properties where clientId = ?
    `;
  const data = [
    CONSTANTS.PROPERTY_STATUS.DELETED,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    CONSTANTS.PROPERTY_STATUS.INACTIVE,
    CONSTANTS.PROPERTY_STATUS.COMPLETED,
    CONSTANTS.PROPERTY_STATUS.PENDING,
    CONSTANTS.PROPERTY_STATUS.COMPLETED,
    clientId,
  ];

  if (Number(locationId) && Number(locationId) > 0) {
    query += ` and locationId = ?`;
    data.push(locationId);
  }

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

propertyDB.getSummaryByStaffIdX = async ({ clientId, staffId, locationId }: propertyStaffTypes & { locationId: number; }) => {
  let query = `select 
    SUM(CASE WHEN P.status = ? THEN 0 ELSE 1 END) as total,
    SUM(CASE WHEN P.status = ? THEN 1 ELSE 0 END) as active,
    SUM(CASE WHEN P.status = ? THEN 1 ELSE 0 END) as inactive,
    SUM(CASE WHEN P.status = ? THEN 1 ELSE 0 END) as completed,
    SUM(CASE WHEN P.status in (?, ?) THEN 1 ELSE 0 END) as pending
    from Properties as P join PropertyStaff as PS on PS.propId = P.id where P.clientId = ? and PS.staffId = ?
    `;
  const data = [
    CONSTANTS.PROPERTY_STATUS.DELETED,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    CONSTANTS.PROPERTY_STATUS.INACTIVE,
    CONSTANTS.PROPERTY_STATUS.COMPLETED,
    CONSTANTS.PROPERTY_STATUS.PENDING,
    CONSTANTS.PROPERTY_STATUS.COMPLETED,
    clientId,
    staffId,
  ];

  if (Number(locationId) && Number(locationId) > 0) {
    query += ` and P.locationId = ?`;
    data.push(locationId);
  }

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

propertyDB.getFlatSummaryByClientId = async ({ clientId, locationId, }: propertyTypes) => {
  let query = `select 
    SUM(CASE WHEN 1=1 THEN 1 ELSE 0 END) as totalFlatCount,
    SUM(CASE WHEN F.status = ? and P.status = ? THEN 1 ELSE 0 END) as activeFlatCount,
    SUM(CASE WHEN (F.status = ? OR P.status != ?) THEN 1 ELSE 0 END) as inactiveFlatCount
    from Flats as F join Properties as P on P.id = F.propId where P.clientId = ? and P.status != ?
    `;
  const data = [
    CONSTANTS.FLAT_STATUS.ACTIVE,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    CONSTANTS.FLAT_STATUS.INACTIVE,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    clientId,
    CONSTANTS.PROPERTY_STATUS.DELETED,
  ];

  if (Number(locationId) && Number(locationId) > 0) {
    query += ` and P.locationId = ?`;
    data.push(locationId);
  }

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

propertyDB.getFlatSummaryByStaffId = async ({ clientId, staffId, locationId }: propertyStaffTypes & { locationId: number; }) => {
  let query = `select 
    SUM(CASE WHEN 1=1 THEN 1 ELSE 0 END) as totalFlatCount,
    SUM(CASE WHEN F.status = ? and P.status = ? THEN 1 ELSE 0 END) as activeFlatCount,
    SUM(CASE WHEN (F.status = ? OR P.status != ?) THEN 1 ELSE 0 END) as inactiveFlatCount
    from Flats as F join Properties as P on P.id = F.propId join PropertyStaff as PS on PS.propId = P.id where P.clientId = ? and PS.staffId = ? and P.status != ?
    `;
  const data = [
    CONSTANTS.FLAT_STATUS.ACTIVE,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    CONSTANTS.FLAT_STATUS.INACTIVE,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    clientId,
    staffId,
    CONSTANTS.PROPERTY_STATUS.DELETED,
  ];

  if (Number(locationId) && Number(locationId) > 0) {
    query += ` and P.locationId = ?`;
    data.push(locationId);
  }

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

propertyDB.editName = async ({ id, name }: propertyTypes) => {
  const query = "Update Properties set name = ? where id = ?";
  const data = [name, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

propertyDB.updatePayuMidAnDKeyDetails = async ({
  id,
  payuMid,
  payuKey,
}: propertyTypes) => {
  const query = "Update Properties set payuMid = ?, payuKey = ? where id = ?";
  const data = [payuMid, payuKey, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

propertyDB.updateLongitudeLatitude = async ({
  id,
  longitude,
  latitude,
}: propertyTypes) => {
  const query =
    "Update Properties set longitude = ?, latitude = ? where id = ?";
  const data = [longitude, latitude, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

propertyDB.updateTenantOnboardingSetting = async ({
  propIds,
  isEnabled,
  setting,
}: propertyTypes & { isEnabled: number; setting: string; propIds: any }) => {
  const query = `Update Properties set ${setting} = ? where id in (${propIds.map(() => '?').join(',')})`;
  const data = [isEnabled, ...propIds];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

propertyDB.updateTenantOnboardingSettingForClient = async ({
  clientId,
  isEnabled,
  setting,
}: propertyTypes & { isEnabled: number; setting: string }) => {
  const query = `Update Properties set ${setting} = ? where clientId = ?`;
  const data = [isEnabled, clientId];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

propertyDB.getTenantOnboardingSetting = async ({ propIds }: { propIds: any }) => {
  const query = `select P.isIdVerificationEnabled, P.isPoliceVerificationEnabled, P.isRentAgreementEnabled, P.isOnlinePaymentEnabled, P.isBondAvailable, RA.path from Properties as P left join RentAgreements as RA on P.id = RA.propId where P.id in (${propIds.map(() => '?').join(',')})`;
  const data = [...propIds];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

propertyDB.getTenantOnboardingSettingForClient = async ({ clientId }: propertyTypes) => {
  const query = `select P.isIdVerificationEnabled, P.isPoliceVerificationEnabled, P.isRentAgreementEnabled, P.isOnlinePaymentEnabled, P.isBondAvailable, RA.path from Properties as P left join RentAgreements as RA on P.id = RA.propId where P.clientId = ?`;
  const data = [clientId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

propertyDB.getPropertiesNameByClientId = async ({
  clientId,
}: propertyTypes) => {
  const query = `select P.id, P.name from Properties as P where P.clientId = ?`;
  const data = [clientId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

propertyDB.isGstEnabledForClient = async ({
  clientId,
}: propertyTypes) => {
  const query = `select * from Properties where clientId = ? and isGstEnabled = 1`;
  const data = [clientId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return 1;
  else return 0;
};

propertyDB.isGstEnabledForPropIds = async ({
  propId,
}: any) => {
  const query = `select * from Properties where id in (${propId.map(() => '?').join(',')}) and isGstEnabled = 1`;
  const data = [...propId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return 1;
  else return 0;
};

propertyDB.updateFineDetails = async ({
  id,
  fine,
  fineType,
  gracePeriod,
}: propertyTypes) => {
  const query = `update Properties set fine = ?, fineType = ?, gracePeriod = ? where id = ?`;
  const data = [
    fine,
    fineType,
    gracePeriod,
    id,
  ];

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

propertyDB.getByClientIdWithVerifiedLandlord = async ({
  clientId,
  pageNum,
  limit,
  locationId,
}: propertyTypes & { pageNum: any; limit: any }) => {
  //let query = `select P.* from Properties as P join PropertyLease as PL on PL.propId = P.id join (select landlordId, clientId, propId, flatId from LandlordDocuments where type in (?, ?, ?, ?) group by landlordId, clientId, propId, flatId having Count(Distinct type) = 4) as LD on LD.landlordId = PL.landlordId and LD.clientId = PL.clientId and LD.propId = PL.propId and (PL.flatId IS NULL OR LD.flatId = PL.flatId) where P.clientId = ? and P.status != ? `;
  let query = `select P.id, P.locationId, P.gId, P.name, P.clientId, P.isGroundIncluded, P.ownerName, P.ownerMobile, P.streetAddress as address, P.streetAddress, P.longitude, P.latitude, P.type, P.rentalCycle, P.gracePeriod, P.fine, P.tenantPreference, P.security, P.agreementPeriod, P.floorCount, P.noticePeriod, P.lockInPeriod, P.status, P.bankId,  P.createdAt, P.updatedAt from Properties as P join PropertyLease as PL on PL.propId = P.id join (select landlordId, clientId, propId, flatId from LandlordDocuments where type in (?) group by landlordId, clientId, propId, flatId having Count(Distinct type) = 1) as LD on LD.landlordId = PL.landlordId and LD.clientId = PL.clientId and LD.propId = PL.propId and (PL.flatId IS NULL OR LD.flatId = PL.flatId) where P.clientId = ? and P.status != ? `;

  const offset: number = (pageNum - 1) * limit;
  const data: any = [
    // CONSTANTS.DOCUMENT_TYPES.AADHAAR,
    // CONSTANTS.DOCUMENT_TYPES.PAN,
    // CONSTANTS.DOCUMENT_TYPES.POLICE_VERIFICATION,
    CONSTANTS.DOCUMENT_TYPES.RENT_AGREEMENT,
    clientId,
    CONSTANTS.PROPERTY_STATUS.DELETED,
  ];

  if (Number(locationId) && Number(locationId) > 0) {
    query += ` and P.locationId = ?`;
    data.push(locationId);
  }

  query += ` group by P.id order by P.id desc limit ?, ?`;
  data.push(offset);
  data.push(limit);

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

propertyDB.getByClientIdWithNotVerifiedLandlord = async ({
  clientId,
  pageNum,
  limit,
  locationId,
}: propertyTypes & { pageNum: any; limit: any }) => {
  //let query = `select P.* from Properties as P left join PropertyLease as PL on PL.propId = P.id left join (select landlordId, clientId, propId, flatId, Count(Distinct type) AS docCount from LandlordDocuments where type in (?, ?, ?, ?) group by landlordId, clientId, propId, flatId) as LD on LD.landlordId = PL.landlordId and LD.clientId = PL.clientId and LD.propId = PL.propId and (PL.flatId IS NULL OR LD.flatId = PL.flatId) where P.clientId = ? and P.status != ? and (LD.docCount < 4 OR LD.docCount IS NULL) `;
  let query = `select P.id, P.locationId, P.gId, P.name, P.clientId, P.isGroundIncluded, P.ownerName, P.ownerMobile, P.streetAddress as address, P.streetAddress, P.longitude, P.latitude, P.type, P.rentalCycle, P.gracePeriod, P.fine, P.tenantPreference, P.security, P.agreementPeriod, P.floorCount, P.noticePeriod, P.lockInPeriod, P.status, P.bankId,  P.createdAt, P.updatedAt from Properties as P left join PropertyLease as PL on PL.propId = P.id left join (select landlordId, clientId, propId, flatId, Count(Distinct type) AS docCount from LandlordDocuments where type in (?) group by landlordId, clientId, propId, flatId) as LD on LD.landlordId = PL.landlordId and LD.clientId = PL.clientId and LD.propId = PL.propId and (PL.flatId IS NULL OR LD.flatId = PL.flatId) where P.clientId = ? and P.status != ? and (LD.docCount < 1 OR LD.docCount IS NULL) `;

  const offset: number = (pageNum - 1) * limit;
  const data: any = [
    // CONSTANTS.DOCUMENT_TYPES.AADHAAR,
    // CONSTANTS.DOCUMENT_TYPES.PAN,
    // CONSTANTS.DOCUMENT_TYPES.POLICE_VERIFICATION,
    CONSTANTS.DOCUMENT_TYPES.RENT_AGREEMENT,
    clientId,
    CONSTANTS.PROPERTY_STATUS.DELETED,
  ];

  if (Number(locationId) && Number(locationId) > 0) {
    query += ` and P.locationId = ?`;
    data.push(locationId);
  }

  query += ` group by P.id order by P.id desc limit ?, ?`;
  data.push(offset);
  data.push(limit);

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

propertyDB.getByCLientIdWithBasicLandlord = async ({
  clientId,
  pageNum,
  limit,
  locationId,
}: propertyTypes & { pageNum: any; limit: any }) => {
  let query = `select P.id, P.locationId, P.gId, P.name, P.clientId, P.isGroundIncluded, P.ownerName, P.ownerMobile, P.streetAddress as address, P.streetAddress, P.longitude, P.latitude, P.type, P.rentalCycle, P.gracePeriod, P.fine, P.tenantPreference, P.security, P.agreementPeriod, P.floorCount, P.noticePeriod, P.lockInPeriod, P.status, P.bankId,  P.createdAt, P.updatedAt from Properties as P join PropertyLease as PL on PL.propId = P.id left join (select landlordId, clientId, propId, flatId, Count(Distinct type) AS docCount from LandlordDocuments where type in (?) group by landlordId, clientId, propId, flatId) as LD on LD.landlordId = PL.landlordId and LD.clientId = PL.clientId and LD.propId = PL.propId and (PL.flatId IS NULL OR LD.flatId = PL.flatId) where P.clientId = ? and P.status != ? and (LD.docCount < 1 OR LD.docCount IS NULL) `;

  const offset: number = (pageNum - 1) * limit;
  const data: any = [
    // CONSTANTS.DOCUMENT_TYPES.AADHAAR,
    // CONSTANTS.DOCUMENT_TYPES.PAN,
    // CONSTANTS.DOCUMENT_TYPES.POLICE_VERIFICATION,
    CONSTANTS.DOCUMENT_TYPES.RENT_AGREEMENT,
    clientId,
    CONSTANTS.PROPERTY_STATUS.DELETED,
  ];

  if (Number(locationId) && Number(locationId) > 0) {
    query += ` and P.locationId = ?`;
    data.push(locationId);
  }

  query += ` group by P.id order by P.id desc limit ?, ?`;
  data.push(offset);
  data.push(limit);

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

propertyDB.getFullyOccupiedByClientId = async ({
  clientId,
  pageNum,
  limit,
  locationId,
}: propertyTypes & { pageNum: any; limit: any }) => {
  let query = `select P.id, P.locationId, P.gId, P.name, P.clientId, P.isGroundIncluded, P.ownerName, P.ownerMobile, P.streetAddress as address, P.streetAddress, P.longitude, P.latitude, P.type, P.rentalCycle, P.gracePeriod, P.fine, P.tenantPreference, P.security, P.agreementPeriod, P.floorCount, P.noticePeriod, P.lockInPeriod, P.status, P.bankId,  P.createdAt, P.updatedAt, (select Count(id) from Rooms as R where status = ? and propId = P.id) as occupiedRooms, (select Count(Distinct R.id) from Rooms as R join Beds as B on R.id = B.roomId where R.status != ? and R.propId = P.id) as totalActiveRooms from Properties as P where P.clientId = ? and P.status = ? `;

  const offset: number = (pageNum - 1) * limit;
  const data: any = [
    CONSTANTS.ROOM_STATUS.OCCUPIED,
    CONSTANTS.ROOM_STATUS.INACTIVE,
    clientId,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
  ];

  if (Number(locationId) && Number(locationId) > 0) {
    query += ` and P.locationId = ?`;
    data.push(locationId);
  }

  query += ` having occupiedRooms = totalActiveRooms order by P.id desc limit ?, ?`;
  data.push(offset);
  data.push(limit);

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

propertyDB.getNotFullyOccupiedByClientId = async ({
  clientId,
  pageNum,
  limit,
  locationId,
}: propertyTypes & { pageNum: any; limit: any }) => {
  let query = `select P.id, P.locationId, P.gId, P.name, P.clientId, P.isGroundIncluded, P.ownerName, P.ownerMobile, P.streetAddress as address, P.streetAddress, P.longitude, P.latitude, P.type, P.rentalCycle, P.gracePeriod, P.fine, P.tenantPreference, P.security, P.agreementPeriod, P.floorCount, P.noticePeriod, P.lockInPeriod, P.status, P.bankId,  P.createdAt, P.updatedAt, (select Count(id) from Rooms where status = ? and propId = P.id) as occupiedRooms, (select Count(Distinct R.id) from Rooms as R join Beds as B on R.id = B.roomId where R.status != ? and R.propId = P.id) as totalActiveRooms from Properties as P where P.clientId = ? and P.status = ? `;

  const offset: number = (pageNum - 1) * limit;
  const data: any = [
    CONSTANTS.ROOM_STATUS.OCCUPIED,
    CONSTANTS.ROOM_STATUS.INACTIVE,
    clientId,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
  ];

  if (Number(locationId) && Number(locationId) > 0) {
    query += ` and P.locationId = ?`;
    data.push(locationId);
  }

  query += ` having occupiedRooms != totalActiveRooms order by P.id desc limit ?, ?`;
  data.push(offset);
  data.push(limit);

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

propertyDB.getFullyOccupiedByClientIdForStaff = async ({
  clientId,
  staffId,
  pageNum,
  limit,
  locationId,
}: propertyTypes & { pageNum: any; limit: any; staffId: number }) => {
  //let query = `select P.*, (select Count(id) from Rooms as R where status = ? and propId = P.id) as occupiedRooms, (select Count(Distinct R.id) from Rooms as R join Beds as B on R.id = B.roomId where R.status != ? and R.propId = P.id) as totalActiveRooms from Properties as P join PropertyStaff as PS on P.id = PS.propId where P.clientId = ? and PS.staffId = ? and P.status = ? `;
  let query = `select P.id, P.locationId, P.gId, P.name, P.clientId, P.isGroundIncluded, P.ownerName, P.ownerMobile, P.streetAddress as address, P.streetAddress, P.longitude, P.latitude, P.type, P.rentalCycle, P.gracePeriod, P.fine, P.tenantPreference, P.security, P.agreementPeriod, P.floorCount, P.noticePeriod, P.lockInPeriod, P.status, P.bankId,  P.createdAt, P.updatedAt, (select Count(id) from Rooms as R where status = ? and propId = P.id) as occupiedRooms, (select Count(Distinct R.id) from Rooms as R join Beds as B on R.id = B.roomId where R.status != ? and R.propId = P.id) as totalActiveRooms from Properties as P join PropertyStaff as PS on P.id = PS.propId where P.clientId = ? and PS.staffId = ? and P.status = ? `;

  const offset: number = (pageNum - 1) * limit;
  const data: any = [
    CONSTANTS.ROOM_STATUS.OCCUPIED,
    CONSTANTS.ROOM_STATUS.INACTIVE,
    clientId,
    staffId,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
  ];

  if (Number(locationId) && Number(locationId) > 0) {
    query += ` and P.locationId = ?`;
    data.push(locationId);
  }

  query += ` having occupiedRooms = totalActiveRooms order by P.id desc limit ?, ?`;
  data.push(offset);
  data.push(limit);

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

propertyDB.getNotFullyOccupiedByClientIdForStaff = async ({
  clientId,
  staffId,
  pageNum,
  limit,
  locationId,
}: propertyTypes & { pageNum: any; limit: any; staffId: any; }) => {
  //let query = `select P.*, (select Count(id) from Rooms where status = ? and propId = P.id) as occupiedRooms, (select Count(Distinct R.id) from Rooms as R join Beds as B on R.id = B.roomId where R.status != ? and R.propId = P.id) as totalActiveRooms from Properties as P join PropertyStaff as PS on P.id = PS.propId where P.clientId = ? and PS.staffId = ? and P.status = ? `;
  let query = `select P.id, P.locationId, P.gId, P.name, P.clientId, P.isGroundIncluded, P.ownerName, P.ownerMobile, P.streetAddress as address, P.streetAddress, P.longitude, P.latitude, P.type, P.rentalCycle, P.gracePeriod, P.fine, P.tenantPreference, P.security, P.agreementPeriod, P.floorCount, P.noticePeriod, P.lockInPeriod, P.status, P.bankId,  P.createdAt, P.updatedAt, (select Count(id) from Rooms where status = ? and propId = P.id) as occupiedRooms, (select Count(Distinct R.id) from Rooms as R join Beds as B on R.id = B.roomId where R.status != ? and R.propId = P.id) as totalActiveRooms from Properties as P join PropertyStaff as PS on P.id = PS.propId where P.clientId = ? and PS.staffId = ? and P.status = ? `;

  const offset: number = (pageNum - 1) * limit;
  const data: any = [
    CONSTANTS.ROOM_STATUS.OCCUPIED,
    CONSTANTS.ROOM_STATUS.INACTIVE,
    clientId,
    staffId,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
  ];

  if (Number(locationId) && Number(locationId) > 0) {
    query += ` and P.locationId = ?`;
    data.push(locationId);
  }

  query += ` having occupiedRooms != totalActiveRooms order by P.id desc limit ?, ?`;
  data.push(offset);
  data.push(limit);

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

propertyDB.getByClientIdWithVerifiedLandlordForStaff = async ({
  clientId,
  staffId,
  pageNum,
  limit,
  locationId,
}: propertyTypes & { pageNum: any; limit: any; staffId: number }) => {
  let query = `select P.* from Properties as P join PropertyLease as PL on PL.propId = P.id join PropertyStaff as PS on PL.propId = PS.propId join (select landlordId, clientId, propId, flatId from LandlordDocuments where type in (?) group by landlordId, clientId, propId, flatId having Count(Distinct type) = 1) as LD on LD.landlordId = PL.landlordId and LD.clientId = PL.clientId and LD.propId = PL.propId and (PL.flatId IS NULL OR LD.flatId = PL.flatId) where P.clientId = ? and PS.staffId = ? and P.status != ? `;

  const offset: number = (pageNum - 1) * limit;
  const data: any = [
    // CONSTANTS.DOCUMENT_TYPES.AADHAAR,
    // CONSTANTS.DOCUMENT_TYPES.PAN,
    // CONSTANTS.DOCUMENT_TYPES.POLICE_VERIFICATION,
    CONSTANTS.DOCUMENT_TYPES.RENT_AGREEMENT,
    clientId,
    staffId,
    CONSTANTS.PROPERTY_STATUS.DELETED,
  ];

  if (Number(locationId) && Number(locationId) > 0) {
    query += ` and P.locationId = ?`;
    data.push(locationId);
  }

  query += ` group by P.id order by P.id desc limit ?, ?`;
  data.push(offset);
  data.push(limit);

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

propertyDB.getByClientIdWithNotVerifiedLandlordForStaff = async ({
  clientId,
  staffId,
  pageNum,
  limit,
  locationId,
}: propertyTypes & { pageNum: any; limit: any; staffId: number }) => {
  let query = `select P.* from Properties as P left join PropertyLease as PL on PL.propId = P.id join PropertyStaff as PS on PL.propId = PS.propId left join (select landlordId, clientId, propId, flatId, Count(Distinct type) AS docCount from LandlordDocuments where type in (?) group by landlordId, clientId, propId, flatId) as LD on LD.landlordId = PL.landlordId and LD.clientId = PL.clientId and LD.propId = PL.propId and (PL.flatId IS NULL OR LD.flatId = PL.flatId) where P.clientId = ? and PS.staffId = ? and P.status != ? and (LD.docCount < 1 OR LD.docCount IS NULL) `;

  const offset: number = (pageNum - 1) * limit;
  const data: any = [
    // CONSTANTS.DOCUMENT_TYPES.AADHAAR,
    // CONSTANTS.DOCUMENT_TYPES.PAN,
    // CONSTANTS.DOCUMENT_TYPES.POLICE_VERIFICATION,
    CONSTANTS.DOCUMENT_TYPES.RENT_AGREEMENT,
    clientId,
    staffId,
    CONSTANTS.PROPERTY_STATUS.DELETED,
  ];

  if (Number(locationId) && Number(locationId) > 0) {
    query += ` and P.locationId = ?`;
    data.push(locationId);
  }

  query += ` group by P.id order by P.id desc limit ?, ?`;
  data.push(offset);
  data.push(limit);

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

propertyDB.getByCLientIdWithBasicLandlordForStaff = async ({
  clientId,
  staffId,
  pageNum,
  limit,
  locationId,
}: propertyTypes & { pageNum: any; limit: any; staffId: number }) => {
  let query = `select P.* from Properties as P join PropertyLease as PL on PL.propId = P.id join PropertyStaff as PS on PL.propId = PS.propId left join (select landlordId, clientId, propId, flatId, Count(Distinct type) AS docCount from LandlordDocuments where type in (?) group by landlordId, clientId, propId, flatId) as LD on LD.landlordId = PL.landlordId and LD.clientId = PL.clientId and LD.propId = PL.propId and (PL.flatId IS NULL OR LD.flatId = PL.flatId) where P.clientId = ? and PS.staffId = ? and P.status != ? and (LD.docCount < 1 OR LD.docCount IS NULL) `;

  const offset: number = (pageNum - 1) * limit;
  const data: any = [
    // CONSTANTS.DOCUMENT_TYPES.AADHAAR,
    // CONSTANTS.DOCUMENT_TYPES.PAN,
    // CONSTANTS.DOCUMENT_TYPES.POLICE_VERIFICATION,
    CONSTANTS.DOCUMENT_TYPES.RENT_AGREEMENT,
    clientId,
    staffId,
    CONSTANTS.PROPERTY_STATUS.DELETED,
  ];

  if (Number(locationId) && Number(locationId) > 0) {
    query += ` and P.locationId = ?`;
    data.push(locationId);
  }

  query += ` group by P.id order by P.id desc limit ?, ?`;
  data.push(offset);
  data.push(limit);

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

propertyDB.getPersonalAndPartnerProperties = async ({
  clientId,
}: propertyTypes) => {
  const query =
    `(SELECT C.id as clientId, C.parentId, IFNULL(C.businessName, C.name) as clientName, (select COUNT(id) from Properties where clientId = C.id and status = ?) AS propertyCount, (select Count(*) from Beds as B join Rooms as R on B.roomId = R.id join Properties as P on R.propId = P.id where P.clientId = C.id and P.status = ? and R.status != ?) as totalBeds, (select Count(*) from Beds as B join Rooms as R on B.roomId = R.id join Properties as P on R.propId = P.id where P.clientId = C.id and P.status = ? and R.status != ? and B.status != ?) as totalOccupiedBeds, (select Count(DISTINCT tenantId) from Occupancies where clientId = ? and status not in (?, ?)) as tenantCount FROM Clients as C WHERE C.id = ?  GROUP BY C.id, C.parentId, C.name)

    UNION ALL

    (SELECT C.id as clientId, C.parentId, IFNULL(C.businessName, C.name) as clientName, (select COUNT(id) from Properties where clientId = C.id and status = ?) AS propertyCount, (select Count(*) from Beds as B join Rooms as R on B.roomId = R.id join Properties as P on R.propId = P.id where P.status = ? and R.status != ? and P.id in (select id from Properties where parentId = ? and clientId = C.id)) as totalBeds, (select Count(*) from Beds as B join Rooms as R on B.roomId = R.id join Properties as P on R.propId = P.id where P.status = ? and R.status != ? and B.status != ? and P.id in (select id from Properties where parentId = ? and clientId = C.id)) as totalOccupiedBeds, (select Count(DISTINCT tenantId) from Occupancies where status not in (?, ?) and propId in (select id from Properties where parentId = ? and clientId = C.id)) as tenantCount FROM Clients as C WHERE C.parentId = ? GROUP BY C.id, C.parentId, C.name);`;

  const data = [
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    CONSTANTS.ROOM_STATUS.INACTIVE,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    CONSTANTS.ROOM_STATUS.INACTIVE,
    CONSTANTS.BED_STATUS.VACANT,
    clientId,
    CONSTANTS.OCCUPANCY_STATUS.PENDING,
    CONSTANTS.OCCUPANCY_STATUS.REQUESTED,
    clientId,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    CONSTANTS.ROOM_STATUS.INACTIVE,
    clientId,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    CONSTANTS.ROOM_STATUS.INACTIVE,
    CONSTANTS.BED_STATUS.VACANT,
    clientId,
    CONSTANTS.OCCUPANCY_STATUS.PENDING,
    CONSTANTS.OCCUPANCY_STATUS.REQUESTED,
    clientId,
    clientId,
  ];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

propertyDB.getAllByClientIdAndPropId = async ({ clientId, propIds }: propertyTypes & { propIds: any }) => {
  const query =
    `Select P.id, P.locationId, P.gId, P.name, P.clientId, P.isGroundIncluded, P.ownerName, P.ownerMobile, P.streetAddress as address, P.streetAddress, P.type, P.rentalCycle, P.gracePeriod, P.fine, P.tenantPreference, P.security, P.agreementPeriod, P.floorCount, P.noticePeriod, P.lockInPeriod, P.status, P.bankId,  P.createdAt, P.updatedAt from Properties as P where P.clientId = ? and P.id in (${propIds.map(() => '?').join(',')}) and P.status != ? order by P.id desc`;
  const data = [clientId, ...propIds, CONSTANTS.PROPERTY_STATUS.DELETED];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

propertyDB.updateIsOnlinePaymentEnabled = async ({
  id,
  isOnlinePaymentEnabled,
}: propertyTypes) => {
  const query = "Update Properties set isOnlinePaymentEnabled = ? where id = ?";
  const data = [isOnlinePaymentEnabled, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

propertyDB.updateIsFoodEnabled = async ({
  id,
  isFoodEnabled,
}: propertyTypes) => {
  const query = "Update Properties set isFoodEnabled = ? where id = ?";
  const data = [isFoodEnabled, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

propertyDB.updateIsIdVerificationEnabled = async ({
  id,
  isIdVerificationEnabled,
}: propertyTypes) => {
  const query = "Update Properties set isIdVerificationEnabled = ? where id = ?";
  const data = [isIdVerificationEnabled, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

propertyDB.updateIsRentAgreementEnabled = async ({
  id,
  isRentAgreementEnabled,
}: propertyTypes) => {
  const query = "Update Properties set isRentAgreementEnabled = ? where id = ?";
  const data = [isRentAgreementEnabled, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

propertyDB.updateIsPoliceVerificationEnabled = async ({
  id,
  isPoliceVerificationEnabled,
}: propertyTypes) => {
  const query = "Update Properties set isPoliceVerificationEnabled = ? where id = ?";
  const data = [isPoliceVerificationEnabled, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

propertyDB.updateIsPartialPaymentEnabled = async ({
  id,
  isPartialPaymentEnabled,
}: propertyTypes) => {
  const query = "Update Properties set isPartialPaymentEnabled = ? where id = ?";
  const data = [isPartialPaymentEnabled, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

propertyDB.getByLandlordIdAndFilters = async ({
  landlordId,
  clientFilter,
}: { landlordId: number; clientFilter: any }) => {
  let query = `select P.id, P.locationId, P.gId, P.name, P.clientId, P.isGroundIncluded, P.ownerName, P.ownerMobile, P.streetAddress as address, P.streetAddress, P.type, P.floorCount, P.noticePeriod, P.lockInPeriod, P.status, P.bankId,  P.createdAt, P.updatedAt, PL.startDate, PL.endDate, PL.rentDate,  PL.rent as leaseRent, PL.security as leaseSecurity, PL.notice as leaseNotice, PL.lockInPeriod as leaselockInPeriod, PL.incrementType, PL.incrementValue, PL.incrementMonth, PL.rentCollectionType, PL.ownership from Properties as P join PropertyLease as PL on P.id = PL.propId where PL.landlordId = ?`;

  const data = [landlordId];

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

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

propertyDB.getByLandlordIdAndClientId = async ({
  landlordId,
  clientId,
}: { landlordId: number; clientId: number }) => {
  let query = `select P.id, P.locationId, P.gId, P.name, P.clientId, P.isGroundIncluded, P.ownerName, P.ownerMobile, P.streetAddress as address, P.streetAddress, P.type, P.rentalCycle, P.gracePeriod, P.fine, P.tenantPreference, P.security, P.agreementPeriod, P.floorCount, P.noticePeriod, P.lockInPeriod, P.status, P.bankId,  P.createdAt, P.updatedAt, PL.startDate, PL.endDate, PL.rentDate,  PL.rent as leaseRent, PL.security as leaseSecurity from Properties as P join PropertyLease as PL on P.id = PL.propId where PL.landlordId = ? and PL.clientId = ?`;

  const data = [landlordId, clientId];

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

propertyDB.getByLandlordIdAndSearch = async ({
  landlordId,
  searchValue,
  searchType,
}: { landlordId: number; searchValue: string; searchType: string; }) => {
  const data: any = [landlordId];

  let filterCondition = "";

  if (Number(searchType) === 1) {
    filterCondition = " and P.name LIKE ? ";
    data.push(`%${searchValue}%`);
  }

  let query = `select P.id, P.locationId, P.gId, P.name, P.clientId, P.isGroundIncluded, P.ownerName, P.ownerMobile, P.streetAddress as address, P.streetAddress, P.type, P.floorCount, P.status, P.bankId, P.createdAt, P.updatedAt, PL.startDate, PL.endDate, PL.rentDate, PL.rent as leaseRent, PL.security as leaseSecurity, PL.notice as leaseNotice, PL.lockInPeriod as leaselockInPeriod, PL.incrementType, PL.incrementValue, PL.incrementMonth, PL.rentCollectionType, PL.ownership from Properties as P join PropertyLease as PL on P.id = PL.propId where PL.landlordId = ? ${filterCondition}`;

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


propertyDB.getPropIdsByClientIdAndBankId = async ({
  clientId,
  bankId,
}: propertyTypes) => {
  const query =
    "Select id, name from Properties where clientId = ? and status = ? and bankId=? order by id desc";
  const data = [clientId, CONSTANTS.PROPERTY_STATUS.ACTIVE, bankId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

propertyDB.updateLocation = async ({
  id,
  locationId,
}: propertyTypes) => {
  const query = "Update Properties set locationId = ? where id = ?";
  const data = [locationId, id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

propertyDB.getByLocationId = async ({
  clientId,
  locationId,
}: propertyTypes) => {
  const query =
    "Select * from Properties where clientId = ? and locationId = ? and status = ? order by id desc";
  const data = [clientId, locationId, CONSTANTS.PROPERTY_STATUS.ACTIVE];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

propertyDB.getAllByLocationId = async ({
  clientId,
  locationId,
}: propertyTypes) => {
  const query =
    "Select * from Properties where clientId = ? and locationId = ? and status != ? order by id desc";
  const data = [clientId, locationId, CONSTANTS.PROPERTY_STATUS.DELETED];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

propertyDB.concatAddress = async ({
  id,
}: propertyTypes) => {
  const query = "update Properties set streetAddress = CONCAT_WS(', ', streetAddress, address) where id = ?";
  const data = [id];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

propertyDB.updateDetails = async ({
  id,
  name,
  locationId,
  tenantPreference,
  security,
  type,
  source,
  rentalCycle,
  agreementPeriod,
  noticePeriod,
  lockInPeriod,
  streetAddress,
  pincode,
}: propertyTypes) => {
  const query = "update Properties set name = ?, locationId = ?, tenantPreference = ?, security = ?, type = ?, source = ?, rentalCycle = ?, agreementPeriod = ?, noticePeriod = ?, lockInPeriod = ?, streetAddress = ?, pincode = ? where id = ?";
  const data = [
    name,
    locationId,
    tenantPreference,
    security,
    type,
    source,
    rentalCycle,
    agreementPeriod,
    noticePeriod,
    lockInPeriod,
    streetAddress,
    pincode,
    id,
  ];

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

propertyDB.getByPropId = async ({
  propId,
}: { propId: any }) => {
  const query =
    `select id, gId, name, ownerName, ownerMobile from Properties where id in (${propId.map(() => '?').join(',')})`;
  const data = [...propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

propertyDB.getPropDataByClientId = async ({
  clientId,
}: propertyTypes) => {
  const query =
    `select id, gId, name, ownerName, ownerMobile from Properties where clientId=? and status= ? `;
  const data = [clientId, CONSTANTS.PROPERTY_STATUS.ACTIVE];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return [];
};

propertyDB.getPaymentEnabledOrNotForClient = async ({
  clientId,
  pageNum,
  limit,
  isOnlinePaymentEnabled
}: propertyTypes & { pageNum: number; limit: number }) => {
  let query =
    "Select P.id, P.locationId, P.gId, P.name, P.isGroundIncluded, P.clientId, P.ownerName, P.ownerMobile, P.streetAddress as address, P.streetAddress, P.longitude, P.latitude, P.type, P.rentalCycle, P.gracePeriod, P.fine, P.tenantPreference, P.security, P.agreementPeriod, P.floorCount, P.noticePeriod, P.lockInPeriod, P.status, P.bankId,  P.createdAt, P.updatedAt from Properties as P where P.clientId = ? and P.status = ? and P.isOnlinePaymentEnabled = ?";
  const offset: number = (pageNum - 1) * limit;
  //const data = [clientId, status];
  const data: any = [clientId, CONSTANTS.PROPERTY_STATUS.ACTIVE, isOnlinePaymentEnabled];

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

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

propertyDB.getPaymentEnabledOrNotForStaff = async ({
  clientId,
  staffId,
  pageNum,
  limit,
  isOnlinePaymentEnabled
}: propertyTypes & { pageNum: number; limit: number; staffId: number }) => {
  let query =
    `Select P.id, P.locationId, P.gId, P.name, P.isGroundIncluded, P.clientId, P.ownerName, P.ownerMobile, P.streetAddress as address, P.streetAddress, P.longitude, P.latitude, P.type, P.rentalCycle, P.gracePeriod, P.fine, P.tenantPreference, P.security, P.agreementPeriod, P.floorCount, P.noticePeriod, P.lockInPeriod, P.status, P.bankId,  P.createdAt, P.updatedAt from Properties as P join PropertyStaff as PS on P.id = PS.propId where P.clientId = ? and PS.staffId = ? and P.status = ? `;
  const offset: number = (pageNum - 1) * limit;
  const data: any = [clientId, staffId, CONSTANTS.PROPERTY_STATUS.ACTIVE];

  if (Number(isOnlinePaymentEnabled) && Number(isOnlinePaymentEnabled) > 0) {
    query += ` and P.isOnlinePaymentEnabled = ?`;
    data.push(isOnlinePaymentEnabled);
  }

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

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

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

propertyDB.getLastPropertyImage = async () => {
  const query =
    "Select * from PropertyListingPhotos order by id desc limit 1";
  const [rows] = await DB.execute<RowDataPacket[]>(query);
  if (rows?.length > 0) return rows[0];
  else return false;
};

propertyDB.addListingImage = async ({
  propId,
  type,
  imageUrl,
  clientId,
}: propertyTypes & { propId: number; type: string; imageUrl: string; clientId: number }) => {
  const query =
    "INSERT INTO PropertyListingPhotos (clientId, propId, imageUrl, type) VALUES (?, ?, ?, ?)";
  const data = [clientId, propId, imageUrl, type];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

propertyDB.syncPropertyIncomeSecurityLedgerTally = async ({
  clientId,
}: { clientId: number }) => {
  const query = `Update Properties set tallyStatus = ? where clientId = ? and tallyStatus = ? and status = ?`;
  const data = [
    CONSTANTS.TALLY_STATUS.RETRY,
    clientId,
    CONSTANTS.TALLY_STATUS.FAILED,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
  ];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  if (rows?.affectedRows > 0) return true;
  else return false;
};

propertyDB.updatePropertyTallyStatus = async ({
  id,
  tallyStatus,
}: { id: number; tallyStatus: number }) => {
  const query = `Update Properties set tallyStatus = ? where id = ?`;
  const data = [tallyStatus, id];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  if (rows?.affectedRows > 0) return true;
  else return false;
};

propertyDB.toggleWebsiteVisibility = async ({
  id,
  isVisibleOnWebsite,
}: propertyTypes) => {
  const query = `Update Properties set isVisibleOnWebsite = ? where id = ?`;
  const data = [isVisibleOnWebsite, id];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return true;
};

propertyDB.getLeasePendingFlats = async ({ clientId, propId }: { clientId: number; propId: number }) => {
  const query = `Select F.id, F.name from Flats as F left join PropertyLease as PL on F.id = PL.flatId where F.clientId = ? and F.propId = ? and PL.flatId is null`;
  const data = [clientId, propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

propertyDB.getByClientIdAndPaymentGateway = async ({ clientId, paymentGateway }: propertyTypes) => {
  const query = `Select * from Properties where clientId = ? and paymentGateway = ?`;
  const data = [clientId, paymentGateway];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

propertyDB.updateWifiPassword = async ({
  id,
  wifiPassword,
}: propertyTypes) => {
  const query = `Update Properties set wifiPassword = ? where id = ?`;
  const data = [wifiPassword, id];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return true;
};

propertyDB.addRentAgreement = async ({
  clientId,
  propId,
  path,
  signaturePath
}: any) => {
  const query = `INSERT INTO RentAgreements (clientId, propId, path, signaturePath) VALUES (?, ?, ?, ?)`;
  const data = [clientId, propId, path, signaturePath];
  //log.info(mysql.format(query, data))
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

propertyDB.enableRentAgreement = async ({
  id,
}: propertyTypes) => {
  const query = `Update Properties set isRentAgreementEnabled = ? where id = ?`;
  const data = [1, id];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return true;
};

propertyDB.getLastRentAgreementForActiveProp = async ({
  clientId,
}: propertyTypes) => {
  const query =
    "Select RA.* from RentAgreements as RA INNER JOIN Properties as P ON P.id= RA.propId where RA.clientId = ? and P.status= ? and signaturePath is not null and path is not null order by RA.id desc limit 1";
  const data = [clientId, CONSTANTS.PROPERTY_STATUS.ACTIVE];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

export default propertyDB;