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

const autopayDB: any = {};

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

autopayDB.getByClientId = async ({ clientId, pageNum, limit }: autopayMandatesTypes & { pageNum: number, limit: number }) => {
  let query =
    "Select AM.*, (AM.totalAmount - (AM.transactionsRemaining * AM.amount)) as paidAmount, (select paidDate from AutopayQueue where autopayId = AM.id and status = ? order by id desc limit 1) as lastPaidDate, ET.name as expenseTypeName, PB.holderName as beneficiaryName, PB.mobile as beneficiaryMobile, PB.userType as beneficiaryUserType from AutopayMandates as AM join ExpenseTypes as ET on ET.id = AM.expenseType join PayoutBeneficiaries as PB on PB.id = AM.beneficiaryId where AM.clientId = ? order by AM.id desc";
  let data: any = [CONSTANTS.AUTOPAY_QUEUE_STATUS.SUCCESS, clientId];

  if (Number(pageNum) && Number(pageNum) > 0) {
    const offset = (Number(pageNum) - 1) * Number(limit);
    query += " limit ?, ?";
    data.push(`${offset}`);
    data.push(`${limit}`);
  }

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

autopayDB.getTransactionByClientIdAndAutopayId = async ({ clientId, autopayId, pageNum, limit }: autopayQueueTypes & { pageNum: number, limit: number }) => {
  let query =
    "Select AQ.*, ET.name as expenseTypeName, PB.holderName as beneficiaryName, PB.mobile as beneficiaryMobile, PB.userType as beneficiaryUserType from AutopayQueue as AQ join AutopayMandates as AM on AQ.autopayId = AM.id join ExpenseTypes as ET on ET.id = AM.expenseType join PayoutBeneficiaries as PB on PB.id = AM.beneficiaryId where AQ.clientId = ? and AQ.autopayId = ? order by AQ.id desc";
  let data: any = [clientId, autopayId];

  if (Number(pageNum) && Number(pageNum) > 0) {
    const offset = (Number(pageNum) - 1) * Number(limit);
    query += " limit ?, ?";
    data.push(`${offset}`);
    data.push(`${limit}`);
  }

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

autopayDB.createAutopay = async ({
  clientId,
  beneficiaryId,
  type,
  expenseType,
  totalTransactions,
  transactionsRemaining,
  totalAmount,
  amount,
  status,
  frequencyType,
  frequencyValue,
  nextDueDate,
  addedBy,
  addedByUserType,
}: autopayMandatesTypes) => {
  const query =
    "Insert into AutopayMandates (clientId, beneficiaryId, type, expenseType, totalTransactions, transactionsRemaining, totalAmount, amount, status, frequencyType, frequencyValue, nextDueDate, addedBy, addedByUserType) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
  const data = [
    clientId,
    beneficiaryId,
    type,
    expenseType,
    totalTransactions,
    transactionsRemaining,
    totalAmount,
    amount,
    status,
    frequencyType,
    frequencyValue,
    nextDueDate,
    addedBy,
    addedByUserType,
  ];
  const [rows] = await DB.query<ResultSetHeader>(query, data);
  return rows.insertId;
};

autopayDB.updateTransactionStatus = async ({ 
  id, 
  status, 
  description, 
  paidDate
 }: autopayQueueTypes) => {
  const query = "Update AutopayQueue set status = ?, paidDate = ?, description = ? where id = ?";
  const data = [status, paidDate, description, id];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return rows.affectedRows;
};

autopayDB.updateAutopayStatus = async ({ 
  id, 
  status,
 }: autopayQueueTypes) => {
  const query = "Update AutopayMandates set status = ? where id = ?";
  const data = [status, id];
  const [rows] = await DB.execute<ResultSetHeader>(query, data);
  return rows.affectedRows;
};

autopayDB.getSummaryByClientId = async ({
  clientId,
  startDate,
}: autopayMandatesTypes & { startDate: string, endDate: string }) => {
  const query = `Select 
    COUNT(CASE WHEN AM.status = ? THEN 1 END) as totalActive,
    SUM(CASE WHEN AM.status = ? AND MONTH(AM.nextDueDate) = MONTH(?) THEN AM.amount ELSE 0 END) AS totalUpcomingAmount,
    SUM(CASE WHEN AM.status = ? AND MONTH(AM.nextDueDate) = MONTH(?) THEN 1 ELSE 0 END) AS totalUpcomingPayments,
    SUM(CASE WHEN AM.status = ? THEN 1 ELSE 0 END) AS totalCompleted,
    FROM AutopayMandates AS AM 
    WHERE AM.clientId = ?`
  const data = [
    CONSTANTS.AUTOPAY_STATUS.ACTIVE,
    CONSTANTS.AUTOPAY_STATUS.ACTIVE,
    startDate,
    CONSTANTS.AUTOPAY_STATUS.ACTIVE,
    startDate,
    CONSTANTS.AUTOPAY_STATUS.COMPLETED,
    clientId,
  ];
  const [rows] = await DB.execute<RowDataPacket[]>(query, data);
  if (rows?.length > 0) return rows[0];
  else return false;
}

export default autopayDB;