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

let landlordAccountDB: any = {};

landlordAccountDB.add = async ({
  clientId,
  landlordId,
  propId,
  gateway,
  bankName,
  accountNum,
  ifsc,
  holderName,
  mId,
  key
}: landlordAccountTypes) => {
  const query = "INSERT INTO LandlordAccounts (clientId, landlordId, propId, gateway, bankName, accountNum, ifsc, holderName, mId, `key`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  const data = [
    clientId,
    landlordId,
    propId,
    gateway,
    bankName,
    accountNum,
    ifsc,
    holderName,
    mId,
    key
  ];
  // const formattedQuery = DB.format
  //   ? DB.format(query, data) // if you're using mysql2
  //   : query.replace(/\?/g, () => JSON.stringify(data.shift()));

  // console.log('Executing SQL:', formattedQuery);

  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};
landlordAccountDB.update = async ({
  id,
  clientId,
  landlordId,
  propId,
  gateway,
  bankName,
  accountNum,
  ifsc,
  holderName,
  mId,
  key
}: landlordAccountTypes) => {
  const query = "UPDATE LandlordAccounts SET clientId = ?, landlordId = ?, propId = ?, gateway = ?, bankName = ?, accountNum = ?, ifsc = ?, holderName = ?, mId = ?, `key` = ? WHERE id = ?";
  const data = [
    clientId,
    landlordId,
    propId,
    gateway,
    bankName,
    accountNum,
    ifsc,
    holderName,
    mId,
    key,
    id
  ];
  await DB.execute<ResultSetHeader[]>(query, data);
  return true;
};

landlordAccountDB.isEntryExists = async ({clientId, landlordId, propId}: landlordAccountTypes) => {
  const query = `SELECT * FROM LandlordAccounts WHERE clientId = ? AND landlordId = ? AND propId = ?`;
  const data = [clientId, landlordId, propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length === 0) {
    return false;
  }
  return rows[0];
};

landlordAccountDB.isMidExists = async ({clientId, landlordId, propId}: landlordAccountTypes) => {
  const query = `SELECT * FROM LandlordAccounts WHERE clientId = ? AND landlordId = ? AND propId = ? and mId is not null and mId != ''`;
  const data = [clientId, landlordId, propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length === 0) {
    return false;
  }
  return rows[0];
};

landlordAccountDB.getLandLordAccounts = async ({clientId, landlordId, propId}: landlordAccountTypes) => {
  const query = `SELECT * FROM LandlordAccounts WHERE clientId = ? AND landlordId = ? AND propId = ? ORDER BY id DESC`;
  const data = [clientId, landlordId, propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length === 0) {
    return [];
  }
  return rows;
};

landlordAccountDB.getAccountDetailByClientAndPropId = async ({clientId, landlordId, propId, gateway}: landlordAccountTypes) => {
  const query = `SELECT * FROM LandlordAccounts WHERE clientId = ? AND landlordId = ? AND propId = ? and gateway=? ORDER BY id DESC`;
  const data = [clientId, landlordId, propId, gateway];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length === 0) {
    return false;
  }
  return rows[0];
};

landlordAccountDB.getAccountDetailForPropId = async ({clientId, landlordId, propId} : landlordAccountTypes) => {
  const query = `SELECT * FROM LandlordAccounts WHERE clientId = ? AND landlordId = ? AND propId = ? ORDER BY id DESC`;
  const data = [clientId, landlordId, propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length === 0) {
    return false;
  }
  return rows[0];
};

landlordAccountDB.getAccountDetailByClientId = async ({clientId, propId} : landlordAccountTypes) => {
  const query = `SELECT * FROM LandlordAccounts WHERE clientId = ? AND propId = ? ORDER BY id DESC`;
  const data = [clientId, propId];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows.length === 0) {
    return false;
  }
  return rows[0];
};

export default landlordAccountDB;