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

const propertyLeaseDB: any = {};

propertyLeaseDB.create = async ({
  clientId,
  propId,
  landlordId,
  startDate,
  endDate,
  notice,
  rent,
  security,
  rentDate,
  incrementType,
  incrementValue,
  incrementMonth,
  lockInPeriod,
  rentCollectionType,
}: propertyLeaseTypes) => {
  const query = `Insert into PropertyLease (clientId, propId, landlordId, startDate, endDate, notice, rent, security, rentDate, incrementType, incrementValue, incrementMonth, lockInPeriod, rentCollectionType) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;
  const data = [
    clientId,
    propId,
    landlordId,
    startDate,
    endDate,
    notice,
    rent,
    security,
    rentDate,
    incrementType,
    incrementValue,
    incrementMonth,
    lockInPeriod,
    rentCollectionType
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

propertyLeaseDB.createX = async ({
  clientId,
  propId,
  flatId=null,
  landlordId,
  startDate,
  endDate,
  notice,
  rent,
  security,
  rentDate,
  incrementType,
  incrementValue,
  incrementMonth,
  lockInPeriod,
  rentCollectionType,
  propType,
  ownership,
}: propertyLeaseTypes) => {
  const query = `Insert into PropertyLease (clientId, propId, flatId, landlordId, startDate, endDate, notice, rent, security, rentDate, incrementType, incrementValue, incrementMonth, lockInPeriod, rentCollectionType, propType, ownership) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;
  const data = [
    clientId,
    propId,
    flatId,
    landlordId,
    startDate,
    endDate,
    notice,
    rent,
    security,
    rentDate,
    incrementType,
    incrementValue,
    incrementMonth,
    lockInPeriod,
    rentCollectionType,
    propType,
    ownership,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

propertyLeaseDB.edit = async ({
  id,
  clientId,
  propId,
  flatId,
  landlordId,
  startDate,
  endDate,
  notice,
  rent,
  security,
  rentDate,
  incrementType,
  incrementValue,
  incrementMonth,
  lockInPeriod,
  rentCollectionType,
  propType,
  ownership,
}: propertyLeaseTypes) => {
  const query = `update PropertyLease set clientId = ?, propId = ?, flatId = ?, landlordId = ?, startDate = ?, endDate = ?, notice = ?, rent =?, security = ?, rentDate = ?, incrementType = ?, incrementValue = ?, incrementMonth = ?, lockInPeriod = ?, rentCollectionType = ?, propType = ?, ownership = ? where id = ?`;
  const data = [
    clientId,
    propId,
    flatId,
    landlordId,
    startDate,
    endDate,
    notice,
    rent,
    security,
    rentDate,
    incrementType,
    incrementValue,
    incrementMonth,
    lockInPeriod,
    rentCollectionType,
    propType,
    ownership,
    id,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

propertyLeaseDB.delete = async ({
  id,
}: propertyLeaseTypes) => {
  const query = `Delete from PropertyLease where id = ? limit 1`;
  const data = [
    id,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return true;
};

propertyLeaseDB.getByClientIdAndPropId = async ({ clientId, propId }: propertyLeaseTypes) => {
  const query = `Select PL.id, PL.propType as propertyType, PL.flatId, PL.ownership as ownershipType, PL.lockInPeriod, PL.rentCollectionType, PL.incrementMonth, PL.incrementType, PL.incrementValue, PL.clientId, PL.propId, P.name as propertyName, P.streetAddress as propertyAddress, PL.landlordId, PL.startDate, PL.endDate, PL.notice, PL.rent, PL.security, PL.createdAt, PL.rentDate, L.id as landlordId, L.name as landlordName, L.mobile as landlordMobile, L.email as landlordEmail, L.alternateMobile as landlordAltMobile, F.name as flatName from PropertyLease as PL join Landlords as L on PL.landlordId = L.id  INNER JOIN Properties as P on PL.propId = P.id left join Flats as F on PL.flatId = F.id where PL.clientId = ? and PL.propId = ?`;
  const data = [clientId, propId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0];
  else return false;
};

propertyLeaseDB.getByClientIdAndPropIds = async ({ clientId, propIds }: propertyLeaseTypes & {propIds: any[]}) => {
  const query = `Select PL.id, PL.propType as propertyType, PL.flatId, PL.ownership as ownershipType, PL.lockInPeriod, PL.rentCollectionType, PL.incrementMonth, PL.incrementType, PL.incrementValue, PL.clientId, PL.propId, P.name as propertyName, P.streetAddress as propertyAddress, PL.landlordId, PL.startDate, PL.endDate, PL.notice, PL.rent, PL.security, PL.createdAt, PL.rentDate, L.id as landlordId, L.name as landlordName, L.mobile as landlordMobile, L.email as landlordEmail, L.alternateMobile as landlordAltMobile, F.name as flatName from PropertyLease as PL join Landlords as L on PL.landlordId = L.id  INNER JOIN Properties as P on PL.propId = P.id left join Flats as F on PL.flatId = F.id where PL.clientId = ? and PL.propId in (${propIds.map(() => "?").join(",")})`;
  const data = [clientId, ...propIds];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  else return false;
};

propertyLeaseDB.getByClientIdAndPropIdAll = async ({ clientId, propId }: propertyLeaseTypes) => {
  const query = `Select PL.id, PL.propType as propertyType, PL.flatId, PL.ownership as ownershipType, PL.lockInPeriod, PL.rentCollectionType, PL.incrementMonth, PL.incrementType, PL.incrementValue, PL.clientId, PL.propId, P.name as propertyName, P.streetAddress as propertyAddress, PL.landlordId, PL.startDate, PL.endDate, PL.notice, PL.rent, PL.security, PL.createdAt, PL.rentDate, L.id as landlordId, L.name as landlordName, L.mobile as landlordMobile, L.email as landlordEmail, L.alternateMobile as landlordAltMobile, F.name as flatName from PropertyLease as PL join Landlords as L on PL.landlordId = L.id  INNER JOIN Properties as P on PL.propId = P.id left join Flats as F on PL.flatId = F.id where PL.clientId = ? and PL.propId = ?`;
  const data = [clientId, propId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  else return false;
};

propertyLeaseDB.getByClientIdAndPropIdAndFlatId = async ({ clientId, propId, flatId }: propertyLeaseTypes) => {
  const query = `Select PL.id, PL.propType as propertyType, PL.ownership as ownershipType, PL.lockInPeriod, PL.rentCollectionType, PL.incrementMonth, PL.incrementType, PL.incrementValue, PL.clientId, PL.propId, P.name as propertyName, P.streetAddress as propertyAddress, PL.landlordId, PL.startDate, PL.endDate, PL.notice, PL.rent, PL.security, PL.createdAt, PL.rentDate, L.id as landlordId, L.name as landlordName, L.mobile as landlordMobile, L.email as landlordEmail, L.alternateMobile as landlordAltMobile, F.name as flatName, F.id as flatId from PropertyLease as PL join Landlords as L on PL.landlordId = L.id  INNER JOIN Properties as P on PL.propId = P.id INNER JOIN Flats as F on F.id = PL.flatId where PL.clientId = ? and PL.propId = ? and PL.flatId = ?`;
  const data = [clientId, propId, flatId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0];
  else return false;
};

propertyLeaseDB.getByClientId = async ({ clientId }: propertyLeaseTypes) => {
  const query = `Select PL.id, PL.flatId, PL.propType as propertyType, PL.ownership as ownershipType, PL.lockInPeriod, PL.rentCollectionType, PL.incrementMonth, PL.incrementType, PL.incrementValue, PL.clientId, PL.propId, P.name as propertyName, P.streetAddress as propertyAddress, PL.landlordId, PL.startDate, PL.endDate, PL.notice, PL.rent, PL.security, PL.createdAt, PL.rentDate, L.id as landlordId, L.name as landlordName, L.mobile as landlordMobile, L.email as landlordEmail, L.alternateMobile as landlordAltMobile, F.name as flatName, (select paymentDate from LandlordTransactions where clientId=PL.clientId and propId=PL.propId order by id desc limit 1) as lastPaidOn,  (select IFNULL(sum(amount), 0) from LandlordTransactions where clientId=PL.clientId and propId=PL.propId) as totalPayments from PropertyLease as PL join Landlords as L on PL.landlordId = L.id INNER JOIN Properties as P on PL.propId = P.id left join Flats F on F.id = PL.flatId where PL.clientId = ? and P.status = ? order by PL.id desc`;
  const data = [clientId, CONSTANTS.PROPERTY_STATUS.ACTIVE];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  else return false;
};

propertyLeaseDB.getByClientIdForStaff = async ({ clientId, staffId }: propertyLeaseTypes & {staffId: number}) => {
  const query = `Select PL.id, PL.flatId, PL.propType, PL.ownership, PL.lockInPeriod, PL.rentCollectionType, PL.incrementMonth, PL.incrementType, PL.incrementValue, PL.clientId, PL.propId, P.name as propertyName, P.streetAddress as propertyAddress, PL.landlordId, PL.startDate, PL.endDate, PL.notice, PL.rent, PL.security, PL.createdAt, PL.rentDate, L.id as landlordId, L.name as landlordName, L.mobile as landlordMobile, F.name as flatName, (select paymentDate from LandlordTransactions where clientId=PL.clientId and propId=PL.propId order by id desc limit 1) as lastPaidOn,  (select IFNULL(sum(amount), 0) from LandlordTransactions where clientId=PL.clientId and propId=PL.propId) as totalPayments from PropertyLease as PL join Landlords as L on PL.landlordId = L.id INNER JOIN Properties as P on PL.propId = P.id join PropertyStaff as PS on P.id = PS.propId left join Flats F on F.id = PL.flatId where PL.clientId = ? and PS.staffId = ? and P.status = ? order by PL.id desc`;
  const data = [clientId, staffId, CONSTANTS.PROPERTY_STATUS.ACTIVE];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  else return false;
};

propertyLeaseDB.getByClientIdAndSearchVal = async ({
  clientId,
  searchVal,
}: propertyLeaseTypes & { searchVal: string }) => {
  const query = "Select PL.id, PL.flatId, PL.propType, PL.ownership, PL.lockInPeriod, PL.rentCollectionType, PL.incrementMonth, PL.incrementType, PL.incrementValue, PL.clientId, PL.propId, P.name as propertyName, P.streetAddress as propertyAddress, PL.landlordId, PL.startDate, PL.endDate, PL.notice, PL.rent, PL.security, PL.createdAt, PL.rentDate, L.id as landlordId, L.name as landlordName, L.mobile as landlordMobile, F.name as flatName, (select paymentDate from LandlordTransactions where clientId=PL.clientId and propId=PL.propId order by id desc limit 1) as lastPaidOn,  (select IFNULL(sum(amount), 0) from LandlordTransactions where clientId=PL.clientId and propId=PL.propId) as totalPayments from PropertyLease as PL join Landlords as L on PL.landlordId = L.id INNER JOIN Properties as P on PL.propId = P.id left join Flats F on F.id = PL.flatId where PL.clientId = ? and P.status = ? and (L.name like ? or L.mobile like ? or P.name like ?)";
  const data = [
    clientId,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
    `%${searchVal}%`,
    `%${searchVal}%`,
    `%${searchVal}%`,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

propertyLeaseDB.getExpiringAgreements = async ({ clientId }: propertyLeaseTypes) => {
  const query = `Select PL.id, PL.flatId, PL.propType, PL.ownership, PL.lockInPeriod, PL.rentCollectionType, PL.incrementMonth, PL.incrementType, PL.incrementValue, PL.clientId, PL.propId, P.name as propertyName, P.streetAddress as propertyAddress, PL.landlordId, PL.startDate, PL.endDate, PL.notice, PL.rent, PL.security, PL.createdAt, PL.rentDate, L.id as landlordId, L.name as landlordName, L.mobile as landlordMobile, F.name as flatName from PropertyLease as PL join Landlords as L on PL.landlordId = L.id INNER JOIN Properties as P on PL.propId = P.id left join Flats F on F.id = PL.flatId where PL.clientId = ? and PL.endDate <= LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 1 MONTH))`;
  const data = [clientId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  else return false;
};

propertyLeaseDB.getExpiringAgreementsForStaff = async ({ clientId, staffId }: propertyLeaseTypes & {staffId: number}) => {
  const query = `Select PL.id, PL.flatId, PL.propType, PL.ownership, PL.lockInPeriod, PL.rentCollectionType, PL.incrementMonth, PL.incrementType, PL.incrementValue, PL.clientId, PL.propId, P.name as propertyName, P.streetAddress as propertyAddress, PL.landlordId, PL.startDate, PL.endDate, PL.notice, PL.rent, PL.security, PL.createdAt, PL.rentDate, L.id as landlordId, L.name as landlordName, L.mobile as landlordMobile, F.name as flatName from PropertyLease as PL join Landlords as L on PL.landlordId = L.id INNER JOIN Properties as P on PL.propId = P.id join PropertyStaff as PS on P.id = PS.propId left join Flats F on F.id = PL.flatId where PL.clientId = ? and PS.staffId = ? and PL.endDate <= LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 1 MONTH))`;
  const data = [clientId, staffId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  else return false;
};

propertyLeaseDB.getExpiringLockInPeriod = async ({ clientId }: propertyLeaseTypes) => {
  const query = `Select PL.id, PL.flatId, PL.propType, PL.ownership, PL.lockInPeriod, PL.rentCollectionType, PL.incrementMonth, PL.incrementType, PL.incrementValue, PL.clientId, PL.propId, P.name as propertyName, P.streetAddress as propertyAddress, PL.landlordId, PL.startDate, PL.endDate, PL.notice, PL.rent, PL.security, PL.createdAt, PL.rentDate, L.id as landlordId, L.name as landlordName, L.mobile as landlordMobile, F.name as flatName from PropertyLease as PL join Landlords as L on PL.landlordId = L.id INNER JOIN Properties as P on PL.propId = P.id left join Flats F on F.id = PL.flatId where PL.clientId = ? and DATE_ADD(PL.startDate, INTERVAL PL.lockInPeriod MONTH) <= LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 1 MONTH)) and PL.endDate> CURDATE()`;
  const data = [clientId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  else return false;
};

propertyLeaseDB.getExpiringLockInPeriodForStaff = async ({ clientId, staffId }: propertyLeaseTypes & {staffId: number}) => {
  const query = `Select PL.id, PL.flatId, PL.propType, PL.ownership, PL.lockInPeriod, PL.rentCollectionType, PL.incrementMonth, PL.incrementType, PL.incrementValue, PL.clientId, PL.propId, P.name as propertyName, P.streetAddress as propertyAddress, PL.landlordId, PL.startDate, PL.endDate, PL.notice, PL.rent, PL.security, PL.createdAt, PL.rentDate, L.id as landlordId, L.name as landlordName, L.mobile as landlordMobile, F.name as flatName from PropertyLease as PL join Landlords as L on PL.landlordId = L.id INNER JOIN Properties as P on PL.propId = P.id join PropertyStaff as PS on P.id = PS.propId left join Flats F on F.id = PL.flatId where PL.clientId = ? and PS.staffId = ? and DATE_ADD(PL.startDate, INTERVAL PL.lockInPeriod MONTH) <= LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 1 MONTH)) and PL.endDate> CURDATE()`;
  const data = [clientId, staffId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  else return false;
};

propertyLeaseDB.getCurrentMonthExpiringAgreementsCount = async ({ clientId }: propertyLeaseTypes) => {
  const query = `Select count(*) as count from PropertyLease as PL join Landlords as L on PL.landlordId = L.id where PL.clientId = ? and year(PL.endDate)= year(CURDATE()) and month(PL.endDate) = month(CURDATE())`;
  const data = [clientId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0];
  else return false;
};
propertyLeaseDB.getNextMonthExpiringAgreementsCount = async ({ clientId }: propertyLeaseTypes) => {
  const query = `Select count(*) as count from PropertyLease as PL join Landlords as L on PL.landlordId = L.id where PL.clientId = ? and year(PL.endDate)= year(DATE_ADD(CURDATE(), INTERVAL 1 MONTH)) and month(PL.endDate) = month(LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 1 MONTH)))`;
  const data = [clientId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0];
  else return false;
};
propertyLeaseDB.getExpiredAgreementsCount = async ({ clientId }: propertyLeaseTypes) => {
  const query = `Select count(*) as count from PropertyLease as PL join Landlords as L on PL.landlordId = L.id where PL.clientId = ? and PL.endDate < CURDATE()`;
  const data = [clientId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0];
  else return false;
};

propertyLeaseDB.getCurrentMonthExpiringAgreementsCountForStaff = async ({ clientId, staffId }: propertyLeaseTypes & {staffId: number}) => {
  const query = `Select count(*) as count from PropertyLease as PL join Landlords as L on PL.landlordId = L.id join PropertyStaff as PS on PL.propId = PS.propId where PL.clientId = ? and PS.staffId = ? and year(PL.endDate)= year(CURDATE()) and month(PL.endDate) = month(CURDATE())`;
  const data = [clientId, staffId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0];
  else return false;
};
propertyLeaseDB.getNextMonthExpiringAgreementsCountForStaff = async ({ clientId, staffId }: propertyLeaseTypes & {staffId: number}) => {
  const query = `Select count(*) as count from PropertyLease as PL join Landlords as L on PL.landlordId = L.id join PropertyStaff as PS on PL.propId = PS.propId where PL.clientId = ? and PS.staffId = ? and year(PL.endDate)= year(DATE_ADD(CURDATE(), INTERVAL 1 MONTH)) and month(PL.endDate) = month(LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 1 MONTH)))`;
  const data = [clientId, staffId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0];
  else return false;
};
propertyLeaseDB.getExpiredAgreementsCountForStaff = async ({ clientId, staffId }: propertyLeaseTypes & {staffId: number}) => {
  const query = `Select count(*) as count from PropertyLease as PL join Landlords as L on PL.landlordId = L.id join PropertyStaff as PS on PL.propId = PS.propId where PL.clientId = ? and PS.staffId = ? and PL.endDate < CURDATE()`;
  const data = [clientId, staffId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0];
  else return false;
};

propertyLeaseDB.getCurrentMonthExpiringLockInPeriodCount = async ({ clientId }: propertyLeaseTypes) => {
  const query = `Select count(*) as count from PropertyLease as PL join Landlords as L on PL.landlordId = L.id where PL.clientId = ? and MONTH(DATE_ADD(PL.startDate, INTERVAL PL.lockInPeriod MONTH)) = MONTH(CURDATE()) and YEAR(DATE_ADD(PL.startDate, INTERVAL PL.lockInPeriod MONTH)) = YEAR(CURDATE()) and PL.endDate> CURDATE()`;
  const data = [clientId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0];
  else return false;
};
propertyLeaseDB.getNextMonthExpiringLockInPeriodCount = async ({ clientId }: propertyLeaseTypes) => {
  const query = `Select count(*) as count from PropertyLease as PL join Landlords as L on PL.landlordId = L.id where PL.clientId = ? and MONTH(DATE_ADD(PL.startDate, INTERVAL PL.lockInPeriod MONTH)) = MONTH(DATE_ADD(CURDATE(), INTERVAL 1 MONTH)) and YEAR(DATE_ADD(PL.startDate, INTERVAL PL.lockInPeriod MONTH)) = YEAR(DATE_ADD(CURDATE(), INTERVAL 1 MONTH)) and PL.endDate> CURDATE()`;
  const data = [clientId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0];
  else return false;
};
propertyLeaseDB.getExpiredLockInPeriodCount = async ({ clientId }: propertyLeaseTypes) => {
  const query = `Select count(*) as count from PropertyLease as PL join Landlords as L on PL.landlordId = L.id where PL.clientId = ? and DATE(DATE_ADD(PL.startDate, INTERVAL PL.lockInPeriod MONTH)) <= CURDATE() and PL.endDate> CURDATE()`;
  const data = [clientId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0];
  else return false;
};

propertyLeaseDB.getCurrentMonthExpiringLockInPeriodCountForStaff = async ({ clientId, staffId }: propertyLeaseTypes & {staffId: number}) => {
  const query = `Select count(*) as count from PropertyLease as PL join Landlords as L on PL.landlordId = L.id join PropertyStaff as PS on PL.propId = PS.propId where PL.clientId = ? and PS.staffId = ? and MONTH(DATE_ADD(PL.startDate, INTERVAL PL.lockInPeriod MONTH)) = MONTH(CURDATE()) and YEAR(DATE_ADD(PL.startDate, INTERVAL PL.lockInPeriod MONTH)) = YEAR(CURDATE()) and PL.endDate> CURDATE()`;
  const data = [clientId, staffId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0];
  else return false;
};
propertyLeaseDB.getNextMonthExpiringLockInPeriodCountForStaff = async ({ clientId, staffId }: propertyLeaseTypes & {staffId: number}) => {
  const query = `Select count(*) as count from PropertyLease as PL join Landlords as L on PL.landlordId = L.id join PropertyStaff as PS on PL.propId = PS.propId where PL.clientId = ? and PS.staffId = ? and MONTH(DATE_ADD(PL.startDate, INTERVAL PL.lockInPeriod MONTH)) = MONTH(DATE_ADD(CURDATE(), INTERVAL 1 MONTH)) and YEAR(DATE_ADD(PL.startDate, INTERVAL PL.lockInPeriod MONTH)) = YEAR(DATE_ADD(CURDATE(), INTERVAL 1 MONTH)) and PL.endDate> CURDATE()`;
  const data = [clientId, staffId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0];
  else return false;
};
propertyLeaseDB.getExpiredLockInPeriodCountForStaff = async ({ clientId, staffId }: propertyLeaseTypes & {staffId: number}) => {
  const query = `Select count(*) as count from PropertyLease as PL join Landlords as L on PL.landlordId = L.id join PropertyStaff as PS on PL.propId = PS.propId where PL.clientId = ? and PS.staffId = ? and DATE(DATE_ADD(PL.startDate, INTERVAL PL.lockInPeriod MONTH)) <= CURDATE() and PL.endDate> CURDATE()`;
  const data = [clientId, staffId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0];
  else return false;
};

propertyLeaseDB.getByClientIdAndLandlordId = async ({
  clientId,
  landlordId,
}: propertyLeaseTypes) => {
  const query = 
    `Select PL.id, PL.lockInPeriod, PL.flatId, F.name as flatName, PL.propType, PL.ownership, PL.rentCollectionType, PL.incrementMonth, PL.incrementType, PL.incrementValue, PL.clientId, PL.propId, P.name as propertyName, P.streetAddress as propertyAddress, P.type, PL.landlordId, PL.startDate, PL.endDate, PL.notice, PL.rent, PL.security, PL.createdAt, PL.rentDate, L.id as landlordId, L.name as landlordName, L.mobile as landlordMobile, (select paymentDate from LandlordTransactions where clientId=PL.clientId and propId=PL.propId order by id desc limit 1) as lastPaidOn,  (select IFNULL(sum(amount), 0) from LandlordTransactions where clientId=PL.clientId and propId=PL.propId) as totalPayments from PropertyLease as PL join Landlords as L on PL.landlordId = L.id INNER JOIN Properties as P on PL.propId = P.id left join Flats as F on PL.flatId = F.id where PL.clientId = ? and PL.landlordId = ?`

  const data = [
    clientId,
    landlordId,
  ];

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

propertyLeaseDB.getPropertiesByClientIdAndLandlordId = async ({
  clientId,
  landlordId,
}: propertyLeaseTypes) => {
  const query = `Select PL.propId, P.name as propertyName, COALESCE(JSON_ARRAYAGG(IF(F.id IS NOT NULL, JSON_OBJECT('id', F.id, 'name', F.name), NULL)), JSON_ARRAY()) as flats from PropertyLease as PL INNER JOIN Properties as P on PL.propId = P.id left join Flats as F on F.id = PL.flatId where PL.clientId = ? and PL.landlordId = ? group by PL.propId, P.name`;

  const data = [
    clientId,
    landlordId,
  ];

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

propertyLeaseDB.isPropertyLeaseCompleted = async ({
  clientId,
  propId,
}: propertyLeaseTypes) => {
  const query = `select P.* from Properties as P left join PropertyLease as PL on PL.propId = P.id left join (select landlordId, clientId, propId, Count(Distinct type) AS docCount from LandlordDocuments where type in (?, ?, ?, ?) group by landlordId, clientId, propId) as LD on LD.landlordId = PL.landlordId and LD.clientId = PL.clientId and LD.propId = PL.propId where P.clientId = ? and P.status = ? and (LD.docCount < 4 OR LD.docCount IS NULL) order by P.id desc`;

  const data = [
    CONSTANTS.DOCUMENT_TYPES.AADHAAR,
    CONSTANTS.DOCUMENT_TYPES.PAN,
    CONSTANTS.DOCUMENT_TYPES.POLICE_VERIFICATION,
    CONSTANTS.DOCUMENT_TYPES.RENT_AGREEMENT,
    clientId,
    CONSTANTS.PROPERTY_STATUS.ACTIVE,
  ];

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

propertyLeaseDB.getTotalMonthlyRentByLandlordId = async ({ landlordId }: propertyLeaseTypes) => {
  const query = `Select IFNULL(SUM(PL.rent), 0) as totalMonthlyRent from PropertyLease as PL join Properties as P on PL.propId = P.id where PL.landlordId = ? and P.status = ?`;
  const data = [landlordId, CONSTANTS.PROPERTY_STATUS.ACTIVE];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return Number(rows[0].totalMonthlyRent);
  else return 0;
};

propertyLeaseDB.getTotalMonthlyRentByClientId = async ({ clientId }: propertyLeaseTypes) => {
  const query = `Select IFNULL(SUM(PL.rent), 0) as totalMonthlyRent from PropertyLease as PL join Properties as P on PL.propId = P.id where PL.clientId = ? and P.status = ?`;
  const data = [clientId, CONSTANTS.PROPERTY_STATUS.ACTIVE];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return Number(rows[0].totalMonthlyRent);
  else return 0;
};

propertyLeaseDB.getTotalMonthlyRentByClientIdForStaff = async ({ 
  clientId, 
  propertiesIds 
}: propertyLeaseTypes & {propertiesIds: string}) => {
  const query = `Select IFNULL(SUM(PL.rent), 0) as totalMonthlyRent from PropertyLease as PL join Properties as P on PL.propId = P.id where PL.clientId = ? and PL.propId in (${propertiesIds}) and P.status = ?`;
  const data = [clientId, CONSTANTS.PROPERTY_STATUS.ACTIVE];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return Number(rows[0].totalMonthlyRent);
  else return 0;
};

propertyLeaseDB.getTotalMonthlyRentByClientIdForWeb = async ({
  clientId, 
  propIds 
}: propertyLeaseTypes & { propIds: any; }) => {
  let query = `Select IFNULL(SUM(PL.rent), 0) as totalMonthlyRent from PropertyLease as PL join Properties as P on PL.propId = P.id where PL.clientId = ? and P.status = ?`;
  const data = [clientId, CONSTANTS.PROPERTY_STATUS.ACTIVE];

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

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

propertyLeaseDB.getTotalMonthlyRentByClientIdForWebStaff = async ({ 
  clientId,
  propIds, 
  propertiesIds 
}: propertyLeaseTypes & {propertiesIds: string; propIds: any}) => {
  let query = `Select IFNULL(SUM(PL.rent), 0) as totalMonthlyRent from PropertyLease as PL join Properties as P on PL.propId = P.id where PL.clientId = ? and PL.propId in (${propertiesIds}) and P.status = ?`;
  const data = [clientId, CONSTANTS.PROPERTY_STATUS.ACTIVE];

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

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

propertyLeaseDB.getTotalMonthlyRentByPropId = async ({ propId }: propertyLeaseTypes) => {
  const query = `Select IFNULL(SUM(PL.rent), 0) as totalMonthlyRent from PropertyLease as PL join Properties as P on PL.propId = P.id where PL.propId = ? and P.status = ?`;
  const data = [propId, CONSTANTS.PROPERTY_STATUS.ACTIVE];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return Number(rows[0].totalMonthlyRent);
  else return 0;
};

propertyLeaseDB.updateLandlordId = async ({
  landlordId,
  newLandlordId,
}: propertyLeaseTypes & {newLandlordId: number;}) => {
  const query = `Update PropertyLease set landlordId = ? where landlordId = ?`;
  const data = [
    newLandlordId,
    landlordId,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

propertyLeaseDB.getCountByLandlordId = async ({ landlordId }: propertyLeaseTypes) => {
  const query = `Select count(*) as count from PropertyLease where landlordId = ?`;
  const data = [landlordId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0].count;
  else return false;
};

propertyLeaseDB.getByLandlordId = async ({ landlordId }: propertyLeaseTypes) => {
  const query = `Select PL.id, PL.flatId, PL.propType, PL.ownership, PL.lockInPeriod, PL.rentCollectionType, PL.incrementMonth, PL.incrementType, PL.incrementValue, PL.clientId, PL.propId, P.name as propertyName, P.streetAddress as propertyAddress, PL.landlordId, PL.startDate, PL.endDate, PL.notice, PL.rent, PL.security, PL.createdAt, PL.rentDate, C.name as clientName, C.mobile as clientMobile, (select paymentDate from LandlordTransactions where clientId=PL.clientId and propId=PL.propId order by id desc limit 1) as lastPaidOn, (select IFNULL(sum(amount), 0) from LandlordTransactions where clientId=PL.clientId and propId=PL.propId) as totalPayments from PropertyLease as PL join Clients as C on PL.clientId = C.id INNER JOIN Properties as P on PL.propId = P.id where PL.landlordId = ?`;
  const data = [landlordId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  else return false;
};

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

  let filterCondition = "";
  if (Number(searchType) === 1) {
    filterCondition = ` AND C.mobile like ?`;
    data.push(`${searchValue}%`)
  } else if (Number(searchType) === 2) {
    filterCondition = ` AND C.name like ?`;
    data.push(`%${searchValue}%`);
  } else if (Number(searchType) === 3) {
    filterCondition = ` AND P.name like ?`;
    data.push(`%${searchValue}%`);
  }

  const query = `Select PL.id, PL.flatId, PL.propType, PL.ownership, PL.lockInPeriod, PL.rentCollectionType, PL.incrementMonth, PL.incrementType, PL.incrementValue, PL.clientId, PL.propId, P.name as propertyName, P.streetAddress as propertyAddress, PL.landlordId, PL.startDate, PL.endDate, PL.notice, PL.rent, PL.security, PL.createdAt, PL.rentDate, C.name as clientName, C.mobile as clientMobile, (select paymentDate from LandlordTransactions where clientId=PL.clientId and propId=PL.propId order by id desc limit 1) as lastPaidOn, (select IFNULL(sum(amount), 0) from LandlordTransactions where clientId=PL.clientId and propId=PL.propId) as totalPayments from PropertyLease as PL join Clients as C on PL.clientId = C.id INNER JOIN Properties as P on PL.propId = P.id where PL.landlordId = ? ${filterCondition}`;
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  else return false;
};

propertyLeaseDB.getExpiringAgreementsByLandlordId = async ({ landlordId }: propertyLeaseTypes) => {
  const query = `Select PL.id, PL.flatId, PL.propType, PL.ownership, PL.lockInPeriod, PL.rentCollectionType, PL.incrementMonth, PL.incrementType, PL.incrementValue, PL.clientId, PL.propId, P.name as propertyName, P.streetAddress as propertyAddress, PL.landlordId, PL.startDate, PL.endDate, PL.notice, PL.rent, PL.security, PL.createdAt, PL.rentDate, C.name as ClientName, C.mobile as clientMobile from PropertyLease as PL join Clients as C on PL.clientId = C.id INNER JOIN Properties as P on PL.propId = P.id where PL.landlordId = ? and PL.endDate <= LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 1 MONTH))`;
  const data = [landlordId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  else return false;
};

propertyLeaseDB.getExpiringLockInPeriodForLandlordId = async ({ landlordId }: propertyLeaseTypes) => {
  const query = `Select PL.id, PL.flatId, PL.propType, PL.ownership, PL.lockInPeriod, PL.rentCollectionType, PL.incrementMonth, PL.incrementType, PL.incrementValue, PL.clientId, PL.propId, P.name as propertyName, P.streetAddress as propertyAddress, PL.landlordId, PL.startDate, PL.endDate, PL.notice, PL.rent, PL.security, PL.createdAt, PL.rentDate, C.name as clientName, C.mobile as clientMobile from PropertyLease as PL join Clients as C on PL.clientId = C.id INNER JOIN Properties as P on PL.propId = P.id where PL.landlordId = ? and DATE_ADD(PL.startDate, INTERVAL PL.lockInPeriod MONTH) <= LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 1 MONTH)) and PL.endDate> CURDATE()`;
  const data = [landlordId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  else return false;
};

propertyLeaseDB.getCurrentMonthExpiringAgreementsCountForLandlord = async ({ landlordId }: propertyLeaseTypes) => {
  const query = `Select count(*) as count from PropertyLease as PL where PL.landlordId = ? and year(PL.endDate)= year(CURDATE()) and month(PL.endDate) = month(CURDATE())`;
  const data = [landlordId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0].count;
  else return false;
};
propertyLeaseDB.getNextMonthExpiringAgreementsCountForLandlord = async ({ landlordId }: propertyLeaseTypes) => {
  const query = `Select count(*) as count from PropertyLease as PL where PL.landlordId = ? and year(PL.endDate)= year(DATE_ADD(CURDATE(), INTERVAL 1 MONTH)) and month(PL.endDate) = month(LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 1 MONTH)))`;
  const data = [landlordId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0].count;
  else return false;
};
propertyLeaseDB.getExpiredAgreementsCountForLandlord = async ({ landlordId }: propertyLeaseTypes) => {
  const query = `Select count(*) as count from PropertyLease as PL where PL.landlordId = ? and PL.endDate < CURDATE()`;
  const data = [landlordId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0].count;
  else return false;
};

propertyLeaseDB.getCurrentMonthExpiringLockInPeriodCountForlandlord = async ({ landlordId }: propertyLeaseTypes) => {
  const query = `Select count(*) as count from PropertyLease as PL where PL.landlordId = ? and MONTH(DATE_ADD(PL.startDate, INTERVAL PL.lockInPeriod MONTH)) = MONTH(CURDATE()) and YEAR(DATE_ADD(PL.startDate, INTERVAL PL.lockInPeriod MONTH)) = YEAR(CURDATE()) and PL.endDate> CURDATE()`;
  const data = [landlordId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0].count;
  else return false;
};
propertyLeaseDB.getNextMonthExpiringLockInPeriodCountForlandlord = async ({ landlordId }: propertyLeaseTypes) => {
  const query = `Select count(*) as count from PropertyLease as PL where PL.landlordId = ? and MONTH(DATE_ADD(PL.startDate, INTERVAL PL.lockInPeriod MONTH)) = MONTH(DATE_ADD(CURDATE(), INTERVAL 1 MONTH)) and YEAR(DATE_ADD(PL.startDate, INTERVAL PL.lockInPeriod MONTH)) = YEAR(DATE_ADD(CURDATE(), INTERVAL 1 MONTH)) and PL.endDate> CURDATE()`;
  const data = [landlordId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0].count;
  else return false;
};
propertyLeaseDB.getExpiredLockInPeriodCountForlandlord = async ({ landlordId }: propertyLeaseTypes) => {
  const query = `Select count(*) as count from PropertyLease as PL where PL.landlordId = ? and DATE(DATE_ADD(PL.startDate, INTERVAL PL.lockInPeriod MONTH)) <= CURDATE() and PL.endDate> CURDATE()`;
  const data = [landlordId];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0].count;
  else return false;
};

export default propertyLeaseDB;