import { ResultSetHeader, RowDataPacket } from "mysql2";
import DB from "../config/database/db";
import rentAgreementRecordTypes from "../schemas/rentAgreementRecord.schema";

const rentAgreementRecordDB: any = {};

rentAgreementRecordDB.getById = async ({ id }: rentAgreementRecordTypes) => {
  const query = "select * from RentAgreementRecords where id = ?";
  const data = [id];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
};

rentAgreementRecordDB.getByTenantIdandClientId = async ({
  tenantId,
  clientId,
}: rentAgreementRecordTypes) => {
  const query =
    "select * from RentAgreementRecords where tenantId = ? and clientId = ? order by id desc";
  const data = [tenantId, clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

rentAgreementRecordDB.add = async ({
  clientId,
  tenantId,
  propId,
  roomId,
  bedId,
  floor,
  rent,
  prevRent,
  noticePeriod,
  prevNoticePeriod,
  lockInPeriod,
  prevLockInPeriod,
  agreementPeriod,
  prevAgreementPeriod,
  agreementStartDate,
  prevAgreementStartDate,
  security,
  prevSecurity,
  doneByName,
  agreementUrl=null,
}: rentAgreementRecordTypes) => {
  const query =
    "insert into RentAgreementRecords (clientId, tenantId, propId, roomId, bedId, floor, rent, prevRent, noticePeriod, prevNoticePeriod, lockInPeriod, prevLockInPeriod, agreementPeriod, prevAgreementPeriod, agreementStartDate, prevAgreementStartDate, security, prevSecurity, doneByName, agreementUrl) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  const data = [
    clientId,
    tenantId,
    propId,
    roomId,
    bedId,
    floor,
    rent,
    prevRent,
    noticePeriod,
    prevNoticePeriod,
    lockInPeriod,
    prevLockInPeriod,
    agreementPeriod,
    prevAgreementPeriod,
    agreementStartDate,
    prevAgreementStartDate,
    security,
    prevSecurity,
    doneByName,
    agreementUrl,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

rentAgreementRecordDB.getByClientIdAndCreatedAtRange = async ({
  clientId,
  startDate,
  endDate,
}: rentAgreementRecordTypes & {startDate: string; endDate: string;}) => {
  const query =
    "select RAR.*, T.name as tenantName, T.mobile as tenantMobile, P.name as propName from RentAgreementRecords as RAR join Tenants as T on RAR.tenantId = T.id join Properties as P on P.id = RAR.propId where RAR.clientId = ? and DATE(RAR.createdAt) BETWEEN ? and ? order by RAR.id desc";
  const data = [clientId, startDate, endDate];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

rentAgreementRecordDB.getByClientIdAndSearch = async ({
  clientId,
  searchVal,
  searchType,
}: rentAgreementRecordTypes & {searchVal: string; searchType: string;}) => {
  let data: any = [clientId];

  let filterCondition = "";

  if(Number(searchType) === 1) {
    filterCondition = ` and T.mobile like ?`;
    data.push(`${searchVal}%`);
  } else if(Number(searchType) === 2) {
    filterCondition = ` and T.name like ?`;
    data.push(`%${searchVal}%`);
  }

  let query =
    `select RAR.*, T.name as tenantName, T.mobile as tenantMobile, P.name as propName from RentAgreementRecords as RAR join Tenants as T on RAR.tenantId = T.id join Properties as P on P.id = RAR.propId where RAR.clientId = ? ${filterCondition} order by RAR.id desc`;
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows;
  else return false;
};

export default rentAgreementRecordDB;