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

const autopayQueueDB: any = {};

autopayQueueDB.getAutopayById = async ({id}: autopayMandatesTypes) => {
  const query = `select * from AutopayMandates where id = ?`;
  const data = [id];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0];
  return false;
};

autopayQueueDB.getTodayPendingAutopays = async () => {
  const query = `select * from AutopayMandates where status = ? and transactionsRemaining > 0 and DATE(nextDueDate) = DATE(CURDATE())`;
  const data = [CONSTANTS.AUTOPAY_STATUS.ACTIVE];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  return false;
};

autopayQueueDB.getPendingTransactions = async () => {
  const query = `select * from AutopayQueue where status in (?, ?)`;
  const data = [
    CONSTANTS.AUTOPAY_QUEUE_STATUS.PENDING,
    CONSTANTS.AUTOPAY_QUEUE_STATUS.FAILED,
  ];
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows;
  return false;
};

autopayQueueDB.getWalletByClientIdAndStaffId = async ({clientId, staffId}: {clientId: number; staffId: number | null;}) => {
  let query = `select * from Wallet where clientId = ?`;
  const data = [clientId];
  if(staffId){
    query += ` and staffId = ?`;
    data.push(staffId);
  } else {
    query += ` and staffId is NULL`;
  }
  const [rows] = await DB.query<RowDataPacket[]>(query, data);
  if (rows && rows.length > 0) return rows[0];
  return false;
};

autopayQueueDB.addTransactionInQueue = async ({
  clientId,
  beneficiaryId,
  amount,
  status,
  description,
  dueDate,
  autopayId,
}: {
  clientId: number;
  beneficiaryId: number;
  amount: number;
  status: number;
  description: string;
  dueDate: string;
  autopayId: number;
}) => {
  const query = `INSERT INTO AutopayQueue (clientId, beneficiaryId, amount, status, description, dueDate, autopayId) VALUES (?, ?, ?, ?, ?, ?, ?)`;

  const data = [
    clientId,
    beneficiaryId,
    amount,
    status,
    description,
    dueDate,
    autopayId,
  ];

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

autopayQueueDB.updateNextDueDate = async ({
  id,
  nextDueDate,
}: {
  id: number;
  nextDueDate: string;
}) => {
  const query = `UPDATE AutopayMandates SET nextDueDate = ? WHERE id = ?`;

  const data = [nextDueDate, id];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  if (rows?.affectedRows > 0) return true;
  else return false;
};

autopayQueueDB.updateQueueRecordRemainingTransactions = async ({
  id,
  transactionsRemaining,
}: {
  id: number;
  transactionsRemaining: number;
}) => {
  const query = `UPDATE AutopayMandates SET transactionsRemaining = ? WHERE id = ?`;

  const data = [transactionsRemaining, id];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  if (rows?.affectedRows > 0) return true;
  else return false;
};

autopayQueueDB.updateAutopayMandateStatus = async ({
  id,
  status,
}: {
  id: number;
  status: number;
}) => {
  const query = `UPDATE AutopayMandates SET status = ? WHERE id = ?`;

  const data = [status, id];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  if (rows?.affectedRows > 0) return true;
  else return false;
};

autopayQueueDB.updateQueueRecordStatus = async ({
  id,
  status,
  description,
}: {
  id: number;
  status: number;
  description?: string;
}) => {
  const query = `UPDATE AutopayQueue SET status = ?, description = ? WHERE id = ?`;

  const data = [status, description, id];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  if (rows?.affectedRows > 0) return true;
  else return false;
};

export default autopayQueueDB;