import { ResultSetHeader, RowDataPacket } from "mysql2";
import DB from "../config/database/db";
import landlordTransactionTypes from "../schemas/landlordTransactions.schema";
import log from "../config/log";

let landlordTransactionDB: any = {};

landlordTransactionDB.add = async ({
  clientId,
  gId,
  landlordId,
  propId,
  amount,
  expenseId,
  gateway,
  mode,
  paymentDate,
  description,
  status,
  type,
  remarks,
  bankRefNum
}: landlordTransactionTypes) => {
  const query = `INSERT INTO LandlordTransactions (clientId, gId, landlordId, propId, amount, expenseId, gateway, mode, paymentDate, description, status, type, remarks, bankRefNum) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;
  const data = [
    clientId,
    gId,
    landlordId,
    propId,
    amount,
    expenseId,
    gateway,
    mode,
    paymentDate,
    description,
    status,
    type,
    remarks,
    bankRefNum
  ];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

landlordTransactionDB.getLandlordTransactionsByProperty = async ({clientId, propId} : landlordTransactionTypes) => {
  const query = `SELECT P.name as propertyName, L.name as landlordName, L.mobile as landlordMobile, LT.gId, LT.amount, LT.clientId, LT.mode, LT.paymentDate, LT.description, LT.bankRefNum, LT.remarks, LT.type FROM LandlordTransactions as LT INNER JOIN Landlords as L On L.id=LT.landlordId INNER JOIN Properties as P ON LT.propId=P.id WHERE LT.clientId = ? AND LT.propId = ? ORDER BY LT.id DESC`;
  const data = [clientId, propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length === 0) {
    return [];
  }
  return rows;
};

landlordTransactionDB.getLandlordTransactionsByPropertyForFilter = async ({clientId, propId,  searchType, searchVal} : landlordTransactionTypes & {searchType:number, searchVal: string}) => {
  
  let data : any = [clientId, propId];
  let filterCondition = "";
  if(1 === Number(searchType)){
    filterCondition = 'AND LT.bankRefNum LIKE ?';
    data.push(`%${searchVal}%`);
  } else if(2 === Number(searchType)) {
    filterCondition = 'AND LT.amount = ?';
    data.push(searchVal);
  } else if(3 === Number(searchType)) {
    filterCondition = 'AND L.name LIKE ?';
    data.push(`%${searchVal}%`);
  } else if(4 === Number(searchType)) {
    filterCondition = 'AND L.mobile LIKE ?';
    data.push(`%${searchVal}%`);
  } else if(5 === Number(searchType)) {
    filterCondition = 'AND P.name LIKE ?';
    data.push(`%${searchVal}%`);
  }
  let query = `SELECT P.name as propertyName, L.name as landlordName, L.mobile as landlordMobile, LT.gId, LT.amount, LT.clientId, LT.mode, LT.paymentDate, LT.description, LT.bankRefNum, LT.remarks, LT.type FROM LandlordTransactions as LT INNER JOIN Landlords as L On L.id=LT.landlordId INNER JOIN Properties as P ON LT.propId=P.id WHERE LT.clientId = ? AND LT.propId = ? ${filterCondition} ORDER BY LT.id DESC`;

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

landlordTransactionDB.getLandlordTransactionsByClient = async ({clientId} : landlordTransactionTypes) => {
  const query = `SELECT P.name as propertyName, L.name as landlordName, L.mobile as landlordMobile, LT.gId, LT.amount, LT.clientId, LT.mode, LT.paymentDate, LT.description, LT.bankRefNum, LT.remarks, LT.type FROM LandlordTransactions as LT INNER JOIN Landlords as L On L.id=LT.landlordId INNER JOIN Properties as P ON LT.propId=P.id WHERE LT.clientId = ? ORDER BY LT.id DESC`;
  const data = [clientId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length === 0) {
    return [];
  }
  return rows;
};

landlordTransactionDB.getLandlordTransactionsByLandlordId = async ({clientId, landlordId} : landlordTransactionTypes) => {
  const query = `SELECT P.name as propertyName, L.name as landlordName, L.mobile as landlordMobile, LT.gId, LT.amount, LT.clientId, LT.mode, LT.paymentDate, LT.description, LT.bankRefNum, LT.remarks, LT.type FROM LandlordTransactions as LT INNER JOIN Landlords as L On L.id=LT.landlordId INNER JOIN Properties as P ON LT.propId=P.id WHERE LT.clientId = ? and LT.landlordId = ? ORDER BY LT.id DESC`;
  const data = [clientId, landlordId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length === 0) {
    return [];
  }
  return rows;
};

landlordTransactionDB.getLandlordTransactionsByClientForFilter = async ({clientId,  searchType, searchVal} : landlordTransactionTypes & {searchType:number, searchVal: string}) => {
  
  let data : any = [clientId];
  let filterCondition = "";
  if(1 === Number(searchType)){
    filterCondition = 'AND LT.bankRefNum LIKE ?';
    data.push(`%${searchVal}%`);
  } else if(2 === Number(searchType)) {
    filterCondition = 'AND LT.amount = ?';
    data.push(searchVal);
  } else if(3 === Number(searchType)) {
    filterCondition = 'AND L.name LIKE ?';
    data.push(`%${searchVal}%`);
  } else if(4 === Number(searchType)) {
    filterCondition = 'AND L.mobile LIKE ?';
    data.push(`%${searchVal}%`);
  } else if(5 === Number(searchType)) {
    filterCondition = 'AND P.name LIKE ?';
    data.push(`%${searchVal}%`);
  }
  let query = `SELECT P.name as propertyName, L.name as landlordName, L.mobile as landlordMobile, LT.gId, LT.amount, LT.clientId, LT.mode, LT.paymentDate, LT.description, LT.bankRefNum, LT.remarks, LT.type FROM LandlordTransactions as LT INNER JOIN Landlords as L On L.id=LT.landlordId INNER JOIN Properties as P ON LT.propId=P.id WHERE LT.clientId = ? ${filterCondition} ORDER BY LT.id DESC`;

  // log.info(
  //   `Query [${query}], Data [${JSON.stringify(data)}]`
  // );

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

landlordTransactionDB.getLandlordTransactionsByLandlordIdForFilter = async ({clientId, landlordId, searchType, searchVal} : landlordTransactionTypes & {searchType:number, searchVal: string}) => {
  
  let data : any = [clientId, landlordId];
  let filterCondition = "";
  if(1 === Number(searchType)){
    filterCondition = 'AND LT.bankRefNum LIKE ?';
    data.push(`%${searchVal}%`);
  } else if(2 === Number(searchType)) {
    filterCondition = 'AND LT.amount = ?';
    data.push(searchVal);
  } else if(3 === Number(searchType)) {
    filterCondition = 'AND L.name LIKE ?';
    data.push(`%${searchVal}%`);
  } else if(4 === Number(searchType)) {
    filterCondition = 'AND L.mobile LIKE ?';
    data.push(`%${searchVal}%`);
  } else if(5 === Number(searchType)) {
    filterCondition = 'AND P.name LIKE ?';
    data.push(`%${searchVal}%`);
  }
  let query = `SELECT P.name as propertyName, L.name as landlordName, L.mobile as landlordMobile, LT.gId, LT.amount, LT.clientId, LT.mode, LT.paymentDate, LT.description, LT.bankRefNum, LT.remarks, LT.type FROM LandlordTransactions as LT INNER JOIN Landlords as L On L.id=LT.landlordId INNER JOIN Properties as P ON LT.propId=P.id WHERE LT.clientId = ? and LT.landlordId = ? ${filterCondition} ORDER BY LT.id DESC`;

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

landlordTransactionDB.getLandlordTransactionsByClientAndStaffForFilter = async ({clientId, propertiesIds, searchType, searchVal} : landlordTransactionTypes & {searchType:number, searchVal: string; propertiesIds: string}) => {
  
  let data : any = [clientId];
  let filterCondition = "";
  if(1 === Number(searchType)){
    filterCondition = 'AND LT.bankRefNum LIKE ?';
    data.push(`%${searchVal}%`);
  } else if(2 === Number(searchType)) {
    filterCondition = 'AND LT.amount = ?';
    data.push(searchVal);
  } else if(3 === Number(searchType)) {
    filterCondition = 'AND L.name LIKE ?';
    data.push(`%${searchVal}%`);
  } else if(4 === Number(searchType)) {
    filterCondition = 'AND L.mobile LIKE ?';
    data.push(`%${searchVal}%`);
  } else if(5 === Number(searchType)) {
    filterCondition = 'AND P.name LIKE ?';
    data.push(`%${searchVal}%`);
  }
  let query = `SELECT P.name as propertyName, L.name as landlordName, L.mobile as landlordMobile, LT.gId, LT.amount, LT.clientId, LT.mode, LT.paymentDate, LT.description, LT.bankRefNum, LT.remarks, LT.type FROM LandlordTransactions as LT INNER JOIN Landlords as L On L.id=LT.landlordId INNER JOIN Properties as P ON LT.propId=P.id WHERE LT.clientId = ? and LT.propId in (${propertiesIds}) ${filterCondition} ORDER BY LT.id DESC`;

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

landlordTransactionDB.getLandlordTransactionsByClientIdAndDateRange = async ({
  clientId,
  startDate,
  endDate
} : landlordTransactionTypes & {startDate: string; endDate: string}) => {
  const query = `SELECT P.name as propertyName, L.name as landlordName, L.mobile as landlordMobile, LT.gId, LT.amount, LT.clientId, LT.mode, LT.paymentDate, LT.description, LT.bankRefNum, LT.remarks, LT.type FROM LandlordTransactions as LT INNER JOIN Landlords as L On L.id=LT.landlordId INNER JOIN Properties as P ON LT.propId=P.id WHERE LT.clientId = ? and DATE(LT.paymentDate) BETWEEN ? and ? ORDER BY LT.id DESC`;
  const data = [
    clientId,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows && rows.length>0) return rows;
  return false;
};

landlordTransactionDB.getLandlordTransactionsByClientIdAndDateRangeForStaff = async ({
  clientId,
  startDate,
  endDate,
  propertiesIds,
} : landlordTransactionTypes & {startDate: string; endDate: string; propertiesIds: string;}) => {
  const query = `SELECT P.name as propertyName, L.name as landlordName, L.mobile as landlordMobile, LT.gId, LT.amount, LT.clientId, LT.mode, LT.paymentDate, LT.description, LT.bankRefNum, LT.remarks, LT.type FROM LandlordTransactions as LT INNER JOIN Landlords as L On L.id=LT.landlordId INNER JOIN Properties as P ON LT.propId=P.id WHERE LT.clientId = ? and propId in (${propertiesIds}) and DATE(LT.paymentDate) BETWEEN ? and ? ORDER BY LT.id DESC`;
  const data = [
    clientId,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows && rows.length>0) return rows;
  return false;
};

landlordTransactionDB.getLandlordTransactionsByPropIdAndDateRange = async ({
  propId,
  startDate,
  endDate
} : landlordTransactionTypes & {startDate: string; endDate: string}) => {
  const query = `SELECT P.name as propertyName, L.name as landlordName, L.mobile as landlordMobile, LT.gId, LT.amount, LT.clientId, LT.mode, LT.paymentDate, LT.description, LT.bankRefNum, LT.remarks, LT.type FROM LandlordTransactions as LT INNER JOIN Landlords as L On L.id=LT.landlordId INNER JOIN Properties as P ON LT.propId=P.id WHERE LT.propId = ? and DATE(LT.paymentDate) BETWEEN ? and ? ORDER BY LT.id DESC`;
  const data = [
    propId,
    startDate,
    endDate,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows && rows.length>0) return rows;
  return false;
};

landlordTransactionDB.getSummaryByClientIdAndDateRange = async ({
  clientId,
  startDate,
  endDate
}: landlordTransactionTypes & {startDate: string; endDate: string}) => {
  const query = 
    `Select
    (Select IFNULL(SUM(amount), 0) from LandlordTransactions where clientId = ? and DATE(paymentDate) BETWEEN ? and ?) as total,
    (Select IFNULL(SUM(amount), 0) from LandlordTransactions where clientId = ? and type = ? and DATE(paymentDate) BETWEEN ? and ?) as totalRent,
    (Select IFNULL(SUM(amount), 0) from LandlordTransactions where clientId = ? and type = ? and DATE(paymentDate) BETWEEN ? and ?) as totalSecurity`;

  const data = [
    clientId,
    startDate,
    endDate,
    clientId,
    1, //Rent Type
    startDate,
    endDate,
    clientId,
    43, //Security Type
    startDate,
    endDate,
  ];

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

landlordTransactionDB.getSummaryByClientIdAndDateRangeForStaff = async ({
  clientId,
  startDate,
  endDate,
  propertiesIds,
}: landlordTransactionTypes & {startDate: string; endDate: string; propertiesIds: string}) => {
  const query = 
    `Select
    (Select IFNULL(SUM(amount), 0) from LandlordTransactions where clientId = ? and propId in (${propertiesIds}) and DATE(paymentDate) BETWEEN ? and ?) as total,
    (Select IFNULL(SUM(amount), 0) from LandlordTransactions where clientId = ? and propId in (${propertiesIds}) and type = ? and DATE(paymentDate) BETWEEN ? and ?) as totalRent,
    (Select IFNULL(SUM(amount), 0) from LandlordTransactions where clientId = ? and propId in (${propertiesIds}) and type = ? and DATE(paymentDate) BETWEEN ? and ?) as totalSecurity`;

  const data = [
    clientId,
    startDate,
    endDate,
    clientId,
    1, //Rent Type
    startDate,
    endDate,
    clientId,
    43, //Security Type
    startDate,
    endDate,
  ];

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

landlordTransactionDB.getSummaryByPropIdAndDateRange = async ({
  propId,
  startDate,
  endDate
}: landlordTransactionTypes & {startDate: string; endDate: string}) => {
  const query = 
    `Select
    (Select IFNULL(SUM(amount), 0) from LandlordTransactions where propId = ? and DATE(paymentDate) BETWEEN ? and ?) as total,
    (Select IFNULL(SUM(amount), 0) from LandlordTransactions where propId = ? and type = ? and DATE(paymentDate) BETWEEN ? and ?) as totalRent,
    (Select IFNULL(SUM(amount), 0) from LandlordTransactions where propId = ? and type = ? and DATE(paymentDate) BETWEEN ? and ?) as totalSecurity`;

  const data = [
    propId,
    startDate,
    endDate,
    propId,
    1, //Rent Type
    startDate,
    endDate,
    propId,
    43, //Security Type
    startDate,
    endDate,
  ];

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

export default landlordTransactionDB;