import DB from "../config/database/db";
import mysql from "mysql2";
import { ResultSetHeader, RowDataPacket } from "mysql2";
import payoutTransactionsTypes from "../schemas/payoutTransactions.schema";
import log from "../config/log";
import payoutBeneficiariesTypes from "../schemas/payoutBeneficiaries.schema";

const payoutTransactionDB: any = {};

payoutTransactionDB.getById = async ({
  id,
}: payoutTransactionsTypes) => {
  const query =
    "Select * from PayoutTransactions where id = ?";

  const data = [id];

  const [rows] = await DB.execute<RowDataPacket[]>(
    query,
    data
  );

  if (rows?.length > 0) return rows[0];
  else return false;
};

payoutTransactionDB.getByTransId = async ({
  transId,
}: payoutTransactionsTypes) => {
  const query =
    "Select * from PayoutTransactions where transId = ?";

  const data = [transId];

  const [rows] = await DB.execute<RowDataPacket[]>(
    query,
    data
  );

  if (rows?.length > 0) return rows[0];
  else return false;
};

payoutTransactionDB.getByClientId = async ({
  clientId,
}: payoutTransactionsTypes) => {
  const query = `
    Select PT.holderName as name, PT.accountNum, PT.ifsc, PT.transId, PT.cfTransferId, PT.transferUtr, PT.amount, PT.mode as transferMethod, PT.status, PT.statusDescription, PT.remark, PT.updatedAt from PayoutTransactions as PT where PT.clientId = ? order by PT.id desc;
  `;

  const data = [clientId];

  const [rows] = await DB.execute<RowDataPacket[]>(
    query,
    data
  );

  if (rows?.length > 0) return rows;
  else return false;
};


payoutTransactionDB.getByBeneficiaryId = async ({
  clientId,
  payoutBeneficiaryId,
}: payoutTransactionsTypes) => {
  const query = `
    Select PT.holderName as name, PT.accountNum, PT.ifsc, PT.transId, PT.cfTransferId, PT.transferUtr, PT.amount, PT.mode as transferMethod, PT.status, PT.statusDescription, PT.remark, PT.updatedAt from PayoutTransactions as PT where PT.clientId = ? and PT.payoutBeneficiaryId=? order by PT.id desc;
  `;

  const data = [clientId, payoutBeneficiaryId];

  const [rows] = await DB.execute<RowDataPacket[]>(
    query,
    data
  );

  if (rows?.length > 0) return rows;
  else return false;
};

payoutTransactionDB.getByBeneUserIdAndUserType = async ({
  clientId,
  userId,
  userType
}: payoutBeneficiariesTypes) => {
  const query = `SELECT PT.holderName AS name, PT.accountNum, PT.ifsc, PT.transId, PT.cfTransferId, PT.transferUtr, PT.amount, PT.charges as charge, PT.tax, PT.currentBalance, PT.mode AS transferMethod, PT.status, PT.statusDescription, PT.remark, PT.receipt, PT.createdAt, PT.updatedAt FROM PayoutTransactions AS PT INNER JOIN PayoutBeneficiaries AS PB ON PB.id = PT.payoutBeneficiaryId  WHERE PT.clientId = ? AND PB.userId = ? AND PB.userType = ? order by PT.id desc`;

  const data = [clientId, userId, userType];

  const [rows] = await DB.execute<RowDataPacket[]>(
    query,
    data
  );

  if (rows?.length > 0) return rows;
  else return false;
};

payoutTransactionDB.getByAllForEachBeneficiary = async ({
  clientId
}: payoutTransactionsTypes) => {
  const query = `SELECT payoutBeneficiaryId, SUM(amount) AS totalAmount FROM PayoutTransactions WHERE clientId=? and status = 3 GROUP BY payoutBeneficiaryId`;

  const data = [clientId];

  const [rows] = await DB.execute<RowDataPacket[]>(
    query,
    data
  );

  if (rows?.length > 0) return rows;
  else return [];
};

payoutTransactionDB.getByExpenseId = async ({
  expenseId,
}: payoutTransactionsTypes) => {
  const query = `
    Select * 
    from PayoutTransactions
    where expenseId = ?
    order by id desc
  `;

  const data = [expenseId];

  const [rows] = await DB.execute<RowDataPacket[]>(
    query,
    data
  );

  if (rows?.length > 0) return rows;
  else return false;
};

payoutTransactionDB.add = async ({
  clientId,
  transId,
  cfTransferId,
  amount,
  vendorChargeDeducted,
  charges,
  mode,
  payoutBeneficiaryId,
  cfBeneficiaryId,
  expenseId,
  expenseType,
  status,
  statusDescription,
  holderName,
  accountNum,
  ifsc,
  remark,
  doneById,
  doneByType,
  beneficiaryMobile,
  currentBalance,
  autopayQueueId=null,
}: payoutTransactionsTypes) => {
  const query = `
    INSERT INTO PayoutTransactions (
      clientId,
      transId,
      cfTransferId,
      amount,
      vendorChargeDeducted,
      charges,
      mode,
      payoutBeneficiaryId,
      cfBeneficiaryId,
      expenseId,
      expenseType,
      status,
      statusDescription,
      holderName,
      accountNum,
      ifsc,
      remark,
      doneById,
      doneByType,
      beneficiaryMobile,
      currentBalance,
      autopayQueueId
    ) 
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `;

  const data = [
    clientId,
    transId,
    cfTransferId,
    amount,
    vendorChargeDeducted,
    charges,
    mode,
    payoutBeneficiaryId,
    cfBeneficiaryId,
    expenseId,
    expenseType,
    status,
    statusDescription,
    holderName,
    accountNum,
    ifsc,
    remark,
    doneById,
    doneByType,
    beneficiaryMobile,
    currentBalance,
    autopayQueueId,
  ];

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

  return rows.insertId;
};

payoutTransactionDB.updateTransaction = async ({
  id,
  status,
  statusDescription,
  transferUtr,
  charges,
  tax,
  expenseId
}: payoutTransactionsTypes) => {
  const query = `
    UPDATE PayoutTransactions
    SET 
      status = ?,
      statusDescription = ?,
      transferUtr = ?,
      expenseId = ?,
      charges = ?,  
      tax = ? 
    WHERE id = ?
  `;

  const data = [
    status,
    statusDescription,
    transferUtr,
    expenseId,
    charges,
    tax,
    id,
  ];
  //log.info(mysql.format(query, data));
  const [rows] = await DB.execute<ResultSetHeader>(
    query,
    data
  );

  if (rows.affectedRows > 0) return true;
  else return false;
};


payoutTransactionDB.updateReceipt = async ({id,receipt}: payoutTransactionsTypes) => {
  const query = `UPDATE PayoutTransactions SET receipt = ? WHERE id = ?`;

  const data = [receipt, id];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);

  if (rows.affectedRows > 0) return true;
  else return false;
};

payoutTransactionDB.updateIsWalletAdjusted = async ({id, isWalletAdjusted}: payoutTransactionsTypes) => {
  const query = `UPDATE PayoutTransactions SET isWalletAdjusted = ? WHERE id = ?`;

  const data = [isWalletAdjusted, id];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);

  if (rows.affectedRows > 0) return true;
  else return false;
};

payoutTransactionDB.getByBeneficiaryIdWithSearch = async ({
  clientId,
  beneficiaryId,
  searchVal,
  searchType,
  pageNum,
  limit,
}: payoutTransactionsTypes & { searchVal: string; searchType: number; beneficiaryId: number; pageNum:any, limit: number }) => {

  let searchQuery = "";
  const data: any[] = [clientId, beneficiaryId];

  // Search by UTR = 2
  if (Number(searchType) === 2 && searchVal) {
    searchQuery = ` AND PT.transferUtr LIKE ? `;
    data.push(`%${searchVal}%`);
  }

  // Search by Bank Account = 1
  if (Number(searchType) === 1 && searchVal) {
    searchQuery = ` AND PT.accountNum LIKE ? `;
    data.push(`%${searchVal}%`);
  }

  let query = `
    SELECT 
      PT.holderName AS name,
      PT.accountNum,
      PT.ifsc,
      PT.transId,
      PT.cfTransferId,
      PT.transferUtr,
      PT.amount,
      PT.charges as charge,
      PT.tax,
      PT.currentBalance,
      PT.mode AS transferMethod,
      PT.status,
      PT.statusDescription,
      PT.remark,
      PT.receipt,
      PT.createdAt,
      PT.updatedAt
    FROM PayoutTransactions AS PT
    WHERE 
      PT.clientId = ?
      AND PT.payoutBeneficiaryId = ?
      ${searchQuery}
  `;

  query += ` ORDER BY PT.id DESC`;

  if(pageNum) {
    const offset = (Number(pageNum) - 1) * limit;
    query += ` LIMIT ${limit} OFFSET ${offset}`;
  }
  

  const [rows] = await DB.execute<RowDataPacket[]>(
    query,
    data
  );

  if (rows?.length > 0) {
    return rows;
  } else {
    return false;
  }
};

payoutTransactionDB.getByBeneficiaryIdWithSearchForApp = async ({
  clientId,
  beneficiaryId,
  searchVal,
  pageNum,
  limit,
}: payoutTransactionsTypes & { searchVal: string; beneficiaryId: number; pageNum:any, limit: number }) => {

  let searchQuery = "";
  const data: any[] = [clientId, beneficiaryId];


  searchQuery = ` AND (PT.transferUtr LIKE ? OR PT.accountNum LIKE ?) `;
  data.push(`%${searchVal}%`);
  data.push(`%${searchVal}%`);

  let query = `
    SELECT 
      PT.holderName AS name,
      PT.accountNum,
      PT.ifsc,
      PT.transId,
      PT.cfTransferId,
      PT.transferUtr,
      PT.amount,
      PT.charges as charge,
      PT.tax,
      PT.currentBalance,
      PT.mode AS transferMethod,
      PT.status,
      PT.statusDescription,
      PT.remark,
      PT.receipt,
      PT.createdAt,
      PT.updatedAt
    FROM PayoutTransactions AS PT
    WHERE 
      PT.clientId = ?
      AND PT.payoutBeneficiaryId = ?
      ${searchQuery}
  `;

  query += ` ORDER BY PT.id DESC`;

  if(pageNum) {
    const offset = (Number(pageNum) - 1) * limit;
    query += ` LIMIT ${limit} OFFSET ${offset}`;
  }
  

  const [rows] = await DB.execute<RowDataPacket[]>(
    query,
    data
  );

  if (rows?.length > 0) {
    return rows;
  } else {
    return false;
  }
};

payoutTransactionDB.getByBeneficiaryIdWithDate = async ({
  clientId,
  beneficiaryId,
  startDate,
  endDate,
  pageNum,
  limit
}: payoutTransactionsTypes & { startDate: string; endDate: string; beneficiaryId: number; pageNum: any; limit: number}) => {

  let query = `
    SELECT 
      PT.holderName AS name,
      PT.accountNum,
      PT.ifsc,
      PT.transId,
      PT.cfTransferId,
      PT.transferUtr,
      PT.amount,
      PT.charges as charge,
      PT.tax,
      PT.currentBalance,
      PT.mode AS transferMethod,
      PT.status,
      PT.statusDescription,
      PT.remark,
      PT.receipt,
      PT.createdAt,
      PT.updatedAt
    FROM PayoutTransactions AS PT
    WHERE 
      PT.clientId = ?
      AND PT.payoutBeneficiaryId = ?
      AND DATE(PT.createdAt) BETWEEN ? AND ?
  `;
  query += ` ORDER BY PT.id DESC`;
  if(pageNum) {
    const offset = (Number(pageNum) - 1) * limit;
    query += ` LIMIT ${limit} OFFSET ${offset}`;
  }
  

  const data = [
    clientId,
    beneficiaryId,
    startDate,
    endDate,
  ];

  const [rows] = await DB.execute<RowDataPacket[]>(
    query,
    data
  );

  if (rows?.length > 0) {
    return rows;
  } else {
    return false;
  }
};

// payoutTransactionDB.getByClientIdWithSearch = async ({
//   clientId,
//   searchVal,
//   searchType,
//   pageNum,
//   limit,
// }: payoutTransactionsTypes & { searchVal: string; searchType: number; pageNum: any; limit: number}) => {

//   let searchQuery = "";
//   const data: any[] = [clientId];

//   // Search by UTR = 2
//   if (Number(searchType) === 2 && searchVal) {
//     searchQuery = ` AND PT.transferUtr LIKE ? `;
//     data.push(`%${searchVal}%`);
//   }

//   // Search by Bank Account = 1
//   if (Number(searchType) === 1 && searchVal) {
//     searchQuery = ` AND PT.accountNum LIKE ? `;
//     data.push(`%${searchVal}%`);
//   }

//   let query = `
//     SELECT 
//       PT.holderName AS name,
//       PT.accountNum,
//       PT.ifsc,
//       PT.transId,
//       PT.cfTransferId,
//       PT.transferUtr,
//       PT.amount,
//       PT.vendorChargeDeducted,
//       PT.charges as charge,
//       PT.tax,
//       PT.currentBalance,
//       PT.mode AS transferMethod,
//       PT.status,
//       PT.statusDescription,
//       PT.remark,
//       PT.receipt,
//       PT.createdAt,
//       PT.updatedAt
//     FROM PayoutTransactions AS PT
//     WHERE 
//       PT.clientId = ?
//       ${searchQuery}
//   `;
//     query += ` ORDER BY PT.id DESC`;
//     if(pageNum) {
//       const offset = (Number(pageNum) - 1) * limit;
//       query += ` LIMIT ${limit} OFFSET ${offset}`;
//     }
    
//   // log.info(`${query}`)


//   const [rows] = await DB.execute<RowDataPacket[]>(
//     query,
//     data
//   );

//   if (rows?.length > 0) {
//     return rows;
//   } else {
//     return false;
//   }
// };

payoutTransactionDB.getByClientIdWithSearch = async ({
  clientId,
  searchVal,
  searchType,
  pageNum,
  limit,
}: payoutTransactionsTypes & { searchVal: string; searchType: number; pageNum: any; limit: number}) => {


  let searchQuery = "";
  const data: any[] = [clientId];

  // Search by DoneByName = 3
  if (Number(searchType) === 3 && searchVal) {
    searchQuery = ` AND CASE WHEN PT.doneByType IS NULL OR PT.doneByType = 1 THEN C.name WHEN PT.doneByType = 3 THEN S.name ELSE NULL END LIKE ? `;
    data.push(`%${searchVal}%`);
  }
  // Search by UTR = 2
  if (Number(searchType) === 2 && searchVal) {
    searchQuery = ` AND PT.transferUtr LIKE ? `;
    data.push(`%${searchVal}%`);
  }

  // Search by Bank Account = 1
  if (Number(searchType) === 1 && searchVal) {
    searchQuery = ` AND PT.accountNum LIKE ? `;
    data.push(`%${searchVal}%`);
  }

  let query = `SELECT PT.holderName AS name, PT.accountNum, PT.ifsc, PT.transId, PT.cfTransferId, PT.transferUtr, PT.amount, PT.vendorChargeDeducted, PT.charges AS charge, PT.tax, PT.currentBalance, PT.mode AS transferMethod, PT.status, PT.statusDescription, PT.remark, PT.receipt, PT.createdAt, PT.updatedAt, PT.doneById, PT.doneByType, CASE WHEN PT.doneByType IS NULL OR PT.doneByType = 1 THEN C.name WHEN PT.doneByType = 3 THEN S.name ELSE NULL END AS doneByName FROM PayoutTransactions AS PT LEFT JOIN Clients AS C ON ((PT.doneByType IS NULL AND PT.clientId = C.id) OR (PT.doneByType = 1 AND PT.doneById = C.id)) LEFT JOIN Staffs AS S ON PT.doneByType = 3 AND PT.doneById = S.id WHERE PT.clientId = ? ${searchQuery}`;

  query += ` ORDER BY PT.id DESC`;

  if (pageNum) {
    const offset = (Number(pageNum) - 1) * limit;
    query += ` LIMIT ${limit} OFFSET ${offset}`;
  }

  const [rows] = await DB.execute<RowDataPacket[]>(
    query,
    data
  );

  if (rows?.length > 0) {
    return rows;
  } else {
    return false;
  }
};

payoutTransactionDB.getByClientIdWithSearchForStaff = async ({
  clientId,
  searchVal,
  searchType,
  doneById,
  pageNum,
  limit,
}: payoutTransactionsTypes & { searchVal: string; searchType: number; pageNum: any; limit: number}) => {

  
  let searchQuery = "";
  const data: any[] = [clientId, doneById];

  // Search by UTR = 2
  if (Number(searchType) === 2 && searchVal) {
    searchQuery = ` AND PT.transferUtr LIKE ? `;
    data.push(`%${searchVal}%`);
  }

  // Search by Bank Account = 1
  if (Number(searchType) === 1 && searchVal) {
    searchQuery = ` AND PT.accountNum LIKE ? `;
    data.push(`%${searchVal}%`);
  }

  let query = `SELECT PT.holderName AS name, PT.accountNum, PT.ifsc, PT.transId, PT.cfTransferId, PT.transferUtr, PT.amount, PT.vendorChargeDeducted, PT.charges AS charge, PT.tax, PT.currentBalance, PT.mode AS transferMethod, PT.status, PT.statusDescription, PT.remark, PT.receipt, PT.createdAt, PT.updatedAt, PT.doneById, PT.doneByType, CASE WHEN PT.doneByType IS NULL OR PT.doneByType = 1 THEN C.name WHEN PT.doneByType = 3 THEN S.name ELSE NULL END AS doneByName FROM PayoutTransactions AS PT LEFT JOIN Clients AS C ON ((PT.doneByType IS NULL AND PT.clientId = C.id) OR (PT.doneByType = 1 AND PT.doneById = C.id)) LEFT JOIN Staffs AS S ON PT.doneByType = 3 AND PT.doneById = S.id WHERE PT.clientId = ? and PT.doneByType=3 and PT.doneById = ? ${searchQuery}`;

  query += ` ORDER BY PT.id DESC`;

  if (pageNum) {
    const offset = (Number(pageNum) - 1) * limit;
    query += ` LIMIT ${limit} OFFSET ${offset}`;
  }

  const [rows] = await DB.execute<RowDataPacket[]>(
    query,
    data
  );

  if (rows?.length > 0) {
    return rows;
  } else {
    return false;
  }
};

// payoutTransactionDB.getByClientIdWithSearchForApp = async ({
//   clientId,
//   searchVal,
//   pageNum,
//   limit,
// }: payoutTransactionsTypes & { searchVal: string; pageNum: any; limit: number}) => {

//   let searchQuery = "";
//   const data: any[] = [clientId];

//   searchQuery = ` AND (PT.beneficiaryMobile LIKE ? OR PT.holderName LIKE ? OR PT.transferUtr LIKE ? OR PT.accountNum LIKE ?) `;
//   data.push(`%${searchVal}%`);
//   data.push(`%${searchVal}%`);
//   data.push(`%${searchVal}%`);
//   data.push(`%${searchVal}%`);

//   let query = `
//     SELECT 
//       PT.holderName AS name,
//       PT.accountNum,
//       PT.ifsc,
//       PT.transId,
//       PT.cfTransferId,
//       PT.transferUtr,
//       PT.amount,
//       PT.vendorChargeDeducted,
//       PT.charges as charge,
//       PT.tax,
//       PT.currentBalance,
//       PT.mode AS transferMethod,
//       PT.status,
//       PT.statusDescription,
//       PT.remark,
//       PT.receipt,
//       PT.createdAt,
//       PT.updatedAt
//     FROM PayoutTransactions AS PT
//     WHERE 
//       PT.clientId = ?
//       ${searchQuery}
//   `;
//     query += ` ORDER BY PT.id DESC`;
//     if(pageNum) {
//       const offset = (Number(pageNum) - 1) * limit;
//       query += ` LIMIT ${limit} OFFSET ${offset}`;
//     }


//   const [rows] = await DB.execute<RowDataPacket[]>(
//     query,
//     data
//   );

//   if (rows?.length > 0) {
//     return rows;
//   } else {
//     return false;
//   }
// };

payoutTransactionDB.getByClientIdWithSearchForApp = async ({
  clientId,
  searchVal,
  pageNum,
  limit,
}: payoutTransactionsTypes & {
  searchVal: string;
  pageNum: any;
  limit: number;
}) => {

  let searchQuery = "";
  const data: any[] = [clientId];

  if (searchVal) {
    searchQuery = ` AND (
      PT.beneficiaryMobile LIKE ?
      OR PT.holderName LIKE ?
      OR PT.transferUtr LIKE ?
      OR PT.accountNum LIKE ?
    )`;

    const search = `%${searchVal}%`;
    data.push(search, search, search, search);
  }

  let query = `
    SELECT
      PT.holderName AS name,
      PT.accountNum,
      PT.ifsc,
      PT.transId,
      PT.cfTransferId,
      PT.transferUtr,
      PT.amount,
      PT.vendorChargeDeducted,
      PT.charges AS charge,
      PT.tax,
      PT.currentBalance,
      PT.mode AS transferMethod,
      PT.status,
      PT.statusDescription,
      PT.remark,
      PT.receipt,
      PT.createdAt,
      PT.updatedAt,
      PT.doneById,
      PT.doneByType,
      CASE
        WHEN PT.doneByType IS NULL OR PT.doneByType = 1 THEN C.name
        WHEN PT.doneByType = 3 THEN S.name
        ELSE NULL
      END AS doneByName
    FROM PayoutTransactions AS PT
    LEFT JOIN Clients AS C
      ON (
        (PT.doneByType IS NULL AND PT.clientId = C.id)
        OR
        (PT.doneByType = 1 AND PT.doneById = C.id)
      )
    LEFT JOIN Staffs AS S
      ON PT.doneByType = 3
      AND PT.doneById = S.id
    WHERE PT.clientId = ?
    ${searchQuery}
    ORDER BY PT.id DESC
  `;

  if (pageNum) {
    const offset = (Number(pageNum) - 1) * limit;
    query += ` LIMIT ${limit} OFFSET ${offset}`;
  }

  const [rows] = await DB.execute<RowDataPacket[]>(
    query,
    data
  );

  return rows?.length > 0 ? rows : false;
};

// payoutTransactionDB.getByClientIdWithDate = async ({
//   clientId,
//   startDate,
//   endDate,
//   pageNum,
//   limit,
// }: payoutTransactionsTypes & { startDate: string; endDate: string; pageNum: any; limit: number}) => {

//   let query = `
//     SELECT 
//       PT.holderName AS name,
//       PT.accountNum,
//       PT.ifsc,
//       PT.transId,
//       PT.cfTransferId,
//       PT.transferUtr,
//       PT.amount,
//       PT.vendorChargeDeducted,
//       PT.charges as charge,
//       PT.tax,
//       PT.currentBalance,
//       PT.mode AS transferMethod,
//       PT.status,
//       PT.statusDescription,
//       PT.remark,
//       PT.receipt,
//       PT.createdAt,
//       PT.updatedAt
//     FROM PayoutTransactions AS PT
//     WHERE 
//       PT.clientId = ?
//       AND DATE(PT.createdAt) BETWEEN ? AND ?
//   `;
//   query += ` ORDER BY PT.id DESC`;
//   if(pageNum) {
//     const offset = (Number(pageNum) - 1) * limit;
//     query += ` LIMIT ${limit} OFFSET ${offset}`;
//   }
  

//   const data = [
//     clientId,
//     startDate,
//     endDate,
//   ];

//   const [rows] = await DB.execute<RowDataPacket[]>(
//     query,
//     data
//   );

//   if (rows?.length > 0) {
//     return rows;
//   } else {
//     return false;
//   }
// };

payoutTransactionDB.getByClientIdWithDate = async ({
  clientId,
  startDate,
  endDate,
  pageNum,
  limit,
}: payoutTransactionsTypes & { startDate: string; endDate: string; pageNum: any; limit: number}) => {

  //let query = `SELECT PT.holderName AS name, PT.accountNum, PT.ifsc, PT.transId, PT.cfTransferId, PT.transferUtr, PT.amount, PT.vendorChargeDeducted, PT.charges AS charge, PT.tax, PT.currentBalance, PT.mode AS transferMethod, PT.status, PT.statusDescription, PT.remark, PT.receipt, PT.createdAt, PT.updatedAt, PT.doneById, PT.doneByType, CASE WHEN PT.doneByType = 1 THEN C.name WHEN PT.doneByType = 3 THEN S.name ELSE NULL END AS doneByName FROM PayoutTransactions AS PT LEFT JOIN Clients AS C ON PT.doneByType = 1 AND PT.doneById = C.id LEFT JOIN Staffs AS S ON PT.doneByType = 3 AND PT.doneById = S.id WHERE PT.clientId = ? AND DATE(PT.createdAt) BETWEEN ? AND ?`;
  let query = `SELECT PT.holderName AS name, PT.accountNum, PT.ifsc, PT.transId, PT.cfTransferId, PT.transferUtr, PT.amount, PT.vendorChargeDeducted, PT.charges AS charge, PT.tax, PT.currentBalance, PT.mode AS transferMethod, PT.status, PT.statusDescription, PT.remark, PT.receipt, PT.createdAt, PT.updatedAt, PT.doneById, PT.doneByType, CASE WHEN PT.doneByType IS NULL OR PT.doneByType = 1 THEN C.name WHEN PT.doneByType = 3 THEN S.name ELSE NULL END AS doneByName FROM PayoutTransactions AS PT LEFT JOIN Clients AS C ON ((PT.doneByType IS NULL AND PT.clientId = C.id) OR (PT.doneByType = 1 AND PT.doneById = C.id)) LEFT JOIN Staffs AS S ON PT.doneByType = 3 AND PT.doneById = S.id WHERE PT.clientId = ? AND DATE(PT.createdAt) BETWEEN ? AND ?`;

  query += ` ORDER BY PT.id DESC`;

  if (pageNum) {
    const offset = (Number(pageNum) - 1) * limit;
    query += ` LIMIT ${limit} OFFSET ${offset}`;
  }

  const data = [
    clientId,
    startDate,
    endDate,
  ];

  const [rows] = await DB.execute<RowDataPacket[]>(
    query,
    data
  );

  if (rows?.length > 0) {
    return rows;
  } else {
    return false;
  }
};

payoutTransactionDB.getByClientIdWithDateForStaff = async ({
  clientId,
  startDate,
  endDate,
  doneById,
  pageNum,
  limit,
}: payoutTransactionsTypes & { startDate: string; endDate: string; pageNum: any; limit: number}) => {

  //let query = `SELECT PT.holderName AS name, PT.accountNum, PT.ifsc, PT.transId, PT.cfTransferId, PT.transferUtr, PT.amount, PT.vendorChargeDeducted, PT.charges AS charge, PT.tax, PT.currentBalance, PT.mode AS transferMethod, PT.status, PT.statusDescription, PT.remark, PT.receipt, PT.createdAt, PT.updatedAt, PT.doneById, PT.doneByType, CASE WHEN PT.doneByType = 1 THEN C.name WHEN PT.doneByType = 3 THEN S.name ELSE NULL END AS doneByName FROM PayoutTransactions AS PT LEFT JOIN Clients AS C ON PT.doneByType = 1 AND PT.doneById = C.id LEFT JOIN Staffs AS S ON PT.doneByType = 3 AND PT.doneById = S.id WHERE PT.clientId = ? AND DATE(PT.createdAt) BETWEEN ? AND ?`;
  let query = `SELECT PT.holderName AS name, PT.accountNum, PT.ifsc, PT.transId, PT.cfTransferId, PT.transferUtr, PT.amount, PT.vendorChargeDeducted, PT.charges AS charge, PT.tax, PT.currentBalance, PT.mode AS transferMethod, PT.status, PT.statusDescription, PT.remark, PT.receipt, PT.createdAt, PT.updatedAt, PT.doneById, PT.doneByType, CASE WHEN PT.doneByType IS NULL OR PT.doneByType = 1 THEN C.name WHEN PT.doneByType = 3 THEN S.name ELSE NULL END AS doneByName FROM PayoutTransactions AS PT LEFT JOIN Clients AS C ON ((PT.doneByType IS NULL AND PT.clientId = C.id) OR (PT.doneByType = 1 AND PT.doneById = C.id)) LEFT JOIN Staffs AS S ON PT.doneByType = 3 AND PT.doneById = S.id WHERE PT.clientId = ? AND PT.doneByType = 3 AND PT.doneById = ? AND DATE(PT.createdAt) BETWEEN ? AND ?`;

  query += ` ORDER BY PT.id DESC`;

  if (pageNum) {
    const offset = (Number(pageNum) - 1) * limit;
    query += ` LIMIT ${limit} OFFSET ${offset}`;
  }

  const data = [
    clientId,
    doneById,
    startDate,
    endDate,
  ];

  const [rows] = await DB.execute<RowDataPacket[]>(
    query,
    data
  );

  if (rows?.length > 0) {
    return rows;
  } else {
    return false;
  }
};

payoutTransactionDB.getTransactionsSummaryByClientIdWithDate = async ({
  clientId,
  startDate,
  endDate,
}: payoutTransactionsTypes & { startDate: string; endDate: string; }) => {

  const query = `
    SELECT
      COALESCE(SUM(amount), 0) AS totalAmount,

      COALESCE(SUM(
        CASE 
          WHEN status = 3 THEN amount
          ELSE 0
        END
      ), 0) AS successfulAmount,

      COALESCE(SUM(
        CASE 
          WHEN status = 1 THEN amount
          ELSE 0
        END
      ), 0) AS pendingAmount,

      COALESCE(SUM(
        CASE 
          WHEN status = 4 THEN amount
          ELSE 0
        END
      ), 0) AS failedAmount,

      COALESCE(SUM(
        CASE 
          WHEN status = 6 THEN amount
          ELSE 0
        END
      ), 0) AS rejectedAmount,

      COALESCE(SUM(
        CASE
          WHEN status = 3 THEN charges
          ELSE 0
        END
      ), 0) AS totalCharges,

      COALESCE(SUM(
        CASE
          WHEN status = 3 THEN tax
          ELSE 0
        END
      ), 0) AS totalTax,

      COALESCE(SUM(
        CASE
          WHEN status = 5 THEN amount
          ELSE 0
        END
      ), 0) AS reversedAmount

    FROM PayoutTransactions
    WHERE 
      clientId = ?
      AND DATE(createdAt) BETWEEN ? AND ?;
  `;

  const data = [
    clientId,
    startDate,
    endDate,
  ];

  const [rows] = await DB.execute<RowDataPacket[]>(
    query,
    data
  );

  const summary = rows?.[0] || {};

  return {
    totalAmount: Number(summary.totalAmount || 0),
    successfulAmount: Number(summary.successfulAmount || 0),
    pendingAmount: Number(summary.pendingAmount || 0),
    failedAmount: Number(summary.failedAmount || 0),
    totalCharges: Number(summary.totalCharges || 0),
    reversedAmount: Number(summary.reversedAmount || 0),
    totalTax: Number(summary.totalTax || 0),
    availableAmount:
      Number(summary.totalAmount || 0) -
      Number(summary.pendingAmount || 0),
  };
};

payoutTransactionDB.getTransactionsSummaryByClientIdWithDateForStaff = async ({
  clientId,
  doneById,
  startDate,
  endDate,
}: payoutTransactionsTypes & { startDate: string; endDate: string; }) => {

  const query = `
    SELECT
      COALESCE(SUM(amount), 0) AS totalAmount,

      COALESCE(SUM(
        CASE 
          WHEN status = 3 THEN amount
          ELSE 0
        END
      ), 0) AS successfulAmount,

      COALESCE(SUM(
        CASE 
          WHEN status = 1 THEN amount
          ELSE 0
        END
      ), 0) AS pendingAmount,

      COALESCE(SUM(
        CASE 
          WHEN status = 4 THEN amount
          ELSE 0
        END
      ), 0) AS failedAmount,

      COALESCE(SUM(
        CASE 
          WHEN status = 6 THEN amount
          ELSE 0
        END
      ), 0) AS rejectedAmount,

      COALESCE(SUM(
        CASE
          WHEN status = 3 THEN charges
          ELSE 0
        END
      ), 0) AS totalCharges,

      COALESCE(SUM(
        CASE
          WHEN status = 3 THEN tax
          ELSE 0
        END
      ), 0) AS totalTax,

      COALESCE(SUM(
        CASE
          WHEN status = 5 THEN amount
          ELSE 0
        END
      ), 0) AS reversedAmount

    FROM PayoutTransactions
    WHERE 
      clientId = ?
      AND doneByType = 3
      AND doneById = ?
      AND DATE(createdAt) BETWEEN ? AND ?;
  `;

  const data = [
    clientId,
    doneById,
    startDate,
    endDate,
  ];

  const [rows] = await DB.execute<RowDataPacket[]>(
    query,
    data
  );

  const summary = rows?.[0] || {};

  return {
    totalAmount: Number(summary.totalAmount || 0),
    successfulAmount: Number(summary.successfulAmount || 0),
    pendingAmount: Number(summary.pendingAmount || 0),
    failedAmount: Number(summary.failedAmount || 0),
    totalCharges: Number(summary.totalCharges || 0),
    reversedAmount: Number(summary.reversedAmount || 0),
    totalTax: Number(summary.totalTax || 0),
    availableAmount:
      Number(summary.totalAmount || 0) -
      Number(summary.pendingAmount || 0),
  };
};

payoutTransactionDB.addTopup = async ({
  clientId,
  amount,
  ledgerBalance,
  fundSource,
  utr,
}: {clientId: number, amount: number, ledgerBalance: number, fundSource: string, utr: string}) => {
  const query = `
    INSERT INTO PayoutRecharge (
      clientId,
      amount,
      ledgerBalance,
      fundSource,
      utr
    ) 
    VALUES (?, ?, ?, ?, ?)
  `;

  const data = [
    clientId,
    amount,
    ledgerBalance,
    fundSource,
    utr,
  ];

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

  return rows.insertId;
};

payoutTransactionDB.getPayoutRechargeWithDate = async ({
  clientId,
  startDate,
  endDate,
}: payoutTransactionsTypes & { startDate: string; endDate: string; }) => {

  const query = `
    SELECT 
      PR.amount,
      PR.utr,
      PR.fundSource,
      PR.ledgerBalance,
      PR.updatedAt
    FROM PayoutRecharge AS PR
    WHERE 
      PR.clientId = ?
      AND DATE(PR.createdAt) BETWEEN ? AND ?
    ORDER BY PR.id DESC;
  `;

  const data = [
    clientId,
    startDate,
    endDate,
  ];

  const [rows] = await DB.execute<RowDataPacket[]>(
    query,
    data
  );

  if (rows?.length > 0) {
    return rows;
  } else {
    return false;
  }
};

payoutTransactionDB.getByDoneByIdAndDoneByTypeAndDate = async ({
  clientId,
  doneById,
  doneByType,
  startDate,
  endDate,
}: payoutTransactionsTypes & { startDate: string; endDate: string; }) => {

  const query = ` SELECT SUM(amount) as amount FROM PayoutTransactions WHERE clientId = ? AND doneById = ? AND doneByType = ? AND DATE(createdAt) BETWEEN ? AND ?`;

  const data = [
    clientId,
    doneById,
    doneByType,
    startDate,
    endDate,
  ];

  const [rows] = await DB.execute<RowDataPacket[]>(
    query,
    data
  );

  if (rows.length > 0) return rows[0].amount;
  return 0;
};

export default payoutTransactionDB;