const validator = require("validator");
const db = require("../../../../managers/dbConfig");
const logger = require("../../../../handlers/logger");

const queries = require("../queries/payment.sql");
const { error } = require("winston");
const {
  transformDateTimeField,
} = require("../../../../managers/formatDateTime");

// ** This function processes a payment of bills accumulated over a period of time
// OR different session for a patient, updating their outstanding balance and inserting a payment record into the database.
// It also handles errors and transactions to ensure data integrity.

// const payAccumulatedBill = async (req, res) => {
//   let {
//     patient_id,
//     rx_time,
//     rx_session,
//     amount_paid,
//     description,
//     payment_means,
//     staff,
//   } = req.body;

//   // Validate input fields
//   if (!patient_id) throw "patient ID is required";
//   if (!amount_paid) throw "amount is required";
//   if (!validator.isNumeric(amount_paid.toString()))
//     throw `${amount_paid} is not a valid amount`;
//   if (!staff) throw "staff name is required";

//   // Use default values if not provided
//   rx_time = rx_time || "N/A";
//   rx_session = rx_session || "N/A";

//   // SQL statements
//   const checkIDSql = `SELECT patient_id, outstanding_balance FROM patients WHERE patient_id=?`;
//   const updatePtWalletSql = `UPDATE patients SET outstanding_balance=? WHERE patient_id=?`;
//   const insertPaymentSql = `
//     INSERT INTO payments (
//       patient_id, rx_time, amount_paid, paid_for, payment_method, confirmed_by, rx_session
//     ) VALUES (?, ?, ?, ?, ?, ?, ?)`;

//   const fetchUnpaidBills = `
//     SELECT * FROM billing_summary
//     WHERE patient_id = ? AND status IN ('pending', 'partially_paid')
//     ORDER BY rx_time ASC`;

//   const updateBillSummary = `
//     UPDATE billing_summary
//     SET total_paid = ?, balance_remaining = ?, status = ?
//     WHERE id = ?`;

//   const updateTxBillSql = `UPDATE tx SET paid = ?, to_balance = ? WHERE patient_id = ? AND session_id = ? AND date_time = ?`;

//   const insertWalletTxn = `
//     INSERT INTO patient_wallet_txn (
//       patient_id, transaction_type, amount, balance_before, balance_after,
//       purpose, tx_ref, payment_channel, updated_by
//     ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`;

//   const getTxBillTo = `SELECT bill_to FROM tx WHERE patient_id = ? AND session_id = ? AND date_time = ? LIMIT 1`;

//   const connection = await db.getConnection();
//   try {
//     await connection.beginTransaction();

//     // 1. Validate patient ID and fetch previous wallet balance
//     const [validID] = await connection.execute(checkIDSql, [patient_id]);
//     if (validID.length < 1)
//       throw `Incorrect patient ID: Patient with ID ${patient_id} NOT found`;

//     const prev_bal = Number(validID[0].outstanding_balance);
//     const newWalletBalance = prev_bal + Number(amount_paid);

//     // 2. Log wallet credit transaction in patient_wallet_txn
//     console.log("got here");
//     await connection.execute(insertWalletTxn, [
//       patient_id,
//       "credit",
//       amount_paid,
//       prev_bal,
//       newWalletBalance,
//       description || "Wallet funding",
//       rx_session,
//       payment_means || "N/A",
//       staff,
//     ]);

//     let walletRunningBalance = newWalletBalance;

//     // 3. Fetch unpaid and partially paid bills
//     const [unpaidBills] = await connection.execute(fetchUnpaidBills, [
//       patient_id,
//     ]);

//     let remainingPayment = Number(amount_paid);

//     for (const bill of unpaidBills) {
//       // Skip bills not billed to patient
//       const [txBillToRow] = await connection.execute(getTxBillTo, [
//         patient_id,
//         bill.session_id,
//         bill.rx_time,
//       ]);
//       const billTo = txBillToRow[0]?.bill_to?.toLowerCase();
//       if (billTo !== "patient") continue;

//       const billId = bill.id;
//       const netPayable = Number(bill.net_payable);
//       const alreadyPaid = Number(bill.total_paid || 0);
//       const remaining = netPayable - alreadyPaid;

//       if (remaining <= 0) continue;

//       // 4. Determine how much of the payment to apply to this bill
//       const toPay = Math.min(remaining, remainingPayment);
//       const newPaid = alreadyPaid + toPay;
//       const newRemaining = netPayable - newPaid;
//       const newStatus =
//         Math.abs(newPaid - netPayable) < 0.01 ? "paid" : "partially_paid";

//       // 5. Update the billing_summary with new payment info
//       await connection.execute(updateBillSummary, [
//         newPaid,
//         newRemaining,
//         newStatus,
//         billId,
//       ]);

//       // 6. Insert into payments table to record the actual payment
//       await connection.execute(insertPaymentSql, [
//         patient_id,
//         bill.rx_time,
//         toPay,
//         "treatment",
//         "wallet",
//         staff,
//         bill.session_id,
//       ]);

//       // 7. Update tx table to reflect new paid amount and remaining balance
//       const walletBefore = walletRunningBalance;
//       const walletAfter = walletBefore - toPay;
//       walletRunningBalance = walletAfter;

//       await connection.execute(updateTxBillSql, [
//         newPaid,
//         walletAfter,
//         patient_id,
//         bill.session_id,
//         bill.rx_time,
//       ]);

//       // 8. Log the wallet deduction per bill as a debit
//       await connection.execute(insertWalletTxn, [
//         patient_id,
//         "debit",
//         toPay,
//         walletBefore,
//         walletAfter,
//         `Bill payment for session ${bill.session_id}`,
//         bill.session_id,
//         "wallet",
//         staff,
//       ]);

//       // 9. Reduce remaining payment left
//       remainingPayment -= toPay;
//       if (remainingPayment <= 0) break;
//     }

//     // 10. Update patient wallet after loop to reflect latest balance
//     await connection.execute(updatePtWalletSql, [
//       walletRunningBalance,
//       patient_id,
//     ]);

//     // 11. Commit transaction
//     await connection.commit();
//     connection.release();

//     res.status(200).json({
//       status: "success",
//       message: "Wallet payment and billing updates completed.",
//     });
//   } catch (error) {
//     await connection.rollback();
//     connection.release();
//     logger.error("Wallet + billing payment error:", error);
//     res.status(400).json({ status: "failed", error });
//   }
// };

const payAccumulatedBill = async (req, res) => {
  let {
    patient_id,
    rx_time,
    rx_session,
    amount_paid,
    description,
    payment_means,
    staff,
  } = req.body;

  if (!patient_id) throw "patient ID is required";
  if (!amount_paid) throw "amount is required";
  if (!validator.isNumeric(amount_paid.toString()))
    throw `${amount_paid} is not a valid amount`;
  if (!staff) throw "staff name is required";

  rx_time = rx_time || "N/A";
  rx_session = rx_session || "N/A";

  const checkIDSql = `SELECT outstanding_balance FROM patients WHERE patient_id = ?`;
  const updateWalletSql = `UPDATE patients SET outstanding_balance = ? WHERE patient_id = ?`;

  const insertWalletTxn = `
    INSERT INTO patient_wallet_txn (
      patient_id, transaction_type, amount, balance_before, balance_after,
      purpose, tx_ref, payment_channel, updated_by
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
  `;

  const fetchUnpaidBills = `
    SELECT * FROM billing_summary
    WHERE patient_id = ? AND status IN ('pending', 'partially_paid')
    ORDER BY rx_time ASC
  `;

  const getTxBillTo = `SELECT bill_to FROM tx WHERE patient_id = ? AND session_id = ? AND date_time = ? LIMIT 1`;

  const updateBillSummary = `
    UPDATE billing_summary
    SET total_paid = ?, balance_remaining = ?, status = ?
    WHERE id = ?
  `;

  const insertPayment = `
    INSERT INTO payments (
      patient_id, rx_time, amount_paid, paid_for, payment_method,
      payer_type, confirmed_by, rx_session, bank_name, wallet_txn_ref
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `;

  const updateTxSql = `
    UPDATE tx SET paid = ?, to_balance = ?, bill_to = ?
    WHERE patient_id = ? AND session_id = ? AND date_time = ?
  `;

  const connection = await db.getConnection();
  try {
    await connection.beginTransaction();

    const [row] = await connection.execute(checkIDSql, [patient_id]);
    if (row.length === 0) throw `Patient ID '${patient_id}' not found`;

    const prevBalance = Number(row[0].outstanding_balance);
    const deposit = Number(amount_paid);
    const newBalance = prevBalance + deposit;

    // Step 1: Generate wallet_txn_ref
    const walletTxnRef = `deposit-${Date.now()}`;

    // Step 2: Log credit in wallet history
    await connection.execute(insertWalletTxn, [
      patient_id,
      "credit",
      deposit,
      prevBalance,
      newBalance,
      description || "Wallet funding",
      walletTxnRef,
      payment_means || "N/A",
      staff,
    ]);

    // Step 3: Update patient wallet
    await connection.execute(updateWalletSql, [newBalance, patient_id]);

    let remainingPayment = deposit;

    // Step 4: Fetch unpaid bills
    const [unpaidBills] = await connection.execute(fetchUnpaidBills, [
      patient_id,
    ]);

    for (const bill of unpaidBills) {
      // Only settle bills billed to patient
      const [txRow] = await connection.execute(getTxBillTo, [
        patient_id,
        bill.session_id,
        bill.rx_time,
      ]);
      const billTo = txRow[0]?.bill_to?.toLowerCase();
      if (billTo !== "patient") continue;

      const net = Number(bill.net_payable);
      const paid = Number(bill.total_paid || 0);
      const balance = net - paid;
      if (balance <= 0 || remainingPayment <= 0) continue;

      const toPay = Math.min(remainingPayment, balance);
      const newPaid = paid + toPay;
      const newRemaining = net - newPaid;
      const status = Math.abs(newRemaining) < 0.01 ? "paid" : "partially_paid";

      // Step 5: Update bill summary
      await connection.execute(updateBillSummary, [
        newPaid,
        newRemaining,
        status,
        bill.id,
      ]);

      // Step 6: Insert into payments
      await connection.execute(insertPayment, [
        patient_id,
        bill.rx_time,
        toPay,
        "treatment",
        "wallet",
        "patient",
        staff,
        bill.session_id,
        "N/A",
        walletTxnRef,
      ]);

      // Step 7: Update tx
      await connection.execute(updateTxSql, [
        newPaid,
        newRemaining,
        "patient",
        patient_id,
        bill.session_id,
        bill.rx_time,
      ]);

      remainingPayment -= toPay;
      if (remainingPayment <= 0) break;
    }

    await connection.commit();
    connection.release();

    res.status(200).json({
      status: "success",
      message: "Patient wallet funded and bills settled.",
      ref: walletTxnRef,
    });
  } catch (error) {
    await connection.rollback();
    connection.release();
    logger.error("Patient wallet settlement error:", error);
    res.status(400).json({ status: "failed", error });
  }
};

// const fundFamilyWalletAndSettleBills = async (req, res) => {
//   const { family_name, amount_paid, payment_means, staff, tx_ref } = req.body;

//   if (!family_name) throw "Family name is required";
//   if (!payment_means) throw "Payment means is required";
//   if (!amount_paid) throw "Amount is required";
//   if (!validator.isNumeric(amount_paid.toString()))
//     throw "Invalid amount\n\n" + amount_paid + "\n\nAmount can ONLY be NUMBERS";
//   if (!staff) throw "Staff name is required";

//   const getFamilyInfo = `SELECT id, wallet FROM families WHERE name = ?`;
//   const updateFamilyWallet = `UPDATE families SET wallet = ? WHERE name = ?`;
//   const insertFamilyTxn = `
//     INSERT INTO family_wallet_tx (
//       family_id, transaction_type, amount, balance_before, balance_after,
//       purpose, tx_ref, payment_channel, updated_by
//     ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`;

//   const getPatientsInFamily = `SELECT patient_id FROM patients WHERE family_or_group = ?`;
//   const fetchUnpaidBills = `
//     SELECT * FROM billing_summary
//     WHERE patient_id = ? AND status IN ('pending', 'partially_paid')
//     ORDER BY rx_time ASC`;
//   const updateBillSummary = `
//     UPDATE billing_summary
//     SET total_paid = ?, balance_remaining = ?, status = ?
//     WHERE id = ?`;
//   const insertPaymentSql = `
//     INSERT INTO payments (
//       patient_id, rx_time, amount_paid, paid_for, payment_method, payer_type,
//       confirmed_by, rx_session, bank_name
//     ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`;
//   const updateTxBillSql = `
//     UPDATE tx SET paid = ?, to_balance = ?, bill_to = ?
//     WHERE patient_id = ? AND session_id = ? AND date_time = ?`;
//   const getTxBillTo = `SELECT bill_to FROM tx WHERE patient_id = ? AND session_id = ? AND date_time = ? LIMIT 1`;

//   const connection = await db.getConnection();
//   try {
//     await connection.beginTransaction();

//     const [familyRows] = await connection.execute(getFamilyInfo, [family_name]);
//     if (familyRows.length === 0) throw `Family '${family_name}' not found.`;

//     const family = familyRows[0];
//     const prevBalance = Number(family.wallet);
//     const newBalance = prevBalance + Number(amount_paid);

//     // 1. Update family wallet
//     await connection.execute(updateFamilyWallet, [newBalance, family_name]);

//     // 2. Log the credit
//     await connection.execute(insertFamilyTxn, [
//       family.id,
//       "credit",
//       amount_paid,
//       prevBalance,
//       newBalance,
//       "Wallet funding",
//       tx_ref || null,
//       payment_means,
//       staff,
//     ]);

//     // 3. Get patients in this family
//     const [patients] = await connection.execute(getPatientsInFamily, [
//       family_name,
//     ]);

//     let remainingPayment = Number(amount_paid);
//     let walletRunningBalance = newBalance;

//     for (const p of patients) {
//       const [unpaidBills] = await connection.execute(fetchUnpaidBills, [
//         p.patient_id,
//       ]);
//       for (const bill of unpaidBills) {
//         const netPayable = Number(bill.net_payable);
//         const alreadyPaid = Number(bill.total_paid || 0);
//         const remaining = netPayable - alreadyPaid;

//         if (remaining <= 0 || remainingPayment <= 0) continue;

//         // Check if the bill_to is family/group before proceeding
//         const [txBillToRow] = await connection.execute(getTxBillTo, [
//           p.patient_id,
//           bill.session_id,
//           bill.rx_time,
//         ]);
//         const billTo = txBillToRow[0]?.bill_to?.toLowerCase();
//         if (billTo !== "family/group" && billTo !== family_name.toLowerCase())
//           continue;

//         const toPay = Math.min(remaining, remainingPayment);
//         const newPaid = alreadyPaid + toPay;
//         const newRemaining = netPayable - newPaid;
//         const newStatus =
//           Math.abs(newRemaining) < 0.01 ? "paid" : "partially_paid";

//         // Update billing summary
//         await connection.execute(updateBillSummary, [
//           newPaid,
//           newRemaining,
//           newStatus,
//           bill.id,
//         ]);

//         // Log payment
//         await connection.execute(insertPaymentSql, [
//           p.patient_id,
//           bill.rx_time,
//           toPay,
//           "treatment",
//           "wallet",
//           "family/group",
//           staff,
//           bill.session_id,
//           "N/A",
//         ]);

//         // Update tx
//         const walletBefore = walletRunningBalance;
//         const walletAfter = walletBefore - toPay;
//         walletRunningBalance = walletAfter;

//         await connection.execute(updateTxBillSql, [
//           newPaid,
//           walletAfter,
//           family_name,
//           p.patient_id,
//           bill.session_id,
//           bill.rx_time,
//         ]);

//         // Log family wallet debit
//         await connection.execute(insertFamilyTxn, [
//           family.id,
//           "debit",
//           toPay,
//           walletBefore,
//           walletAfter,
//           `Bill payment for ${p.patient_id}`,
//           bill.session_id,
//           "wallet",
//           staff,
//         ]);

//         remainingPayment -= toPay;
//         if (remainingPayment <= 0) break;
//       }
//       if (remainingPayment <= 0) break;
//     }

//     // Final update of wallet balance
//     await connection.execute(updateFamilyWallet, [
//       walletRunningBalance,
//       family_name,
//     ]);

//     await connection.commit();
//     connection.release();

//     res.status(200).json({
//       status: "success",
//       message: "Family wallet funded and bills settled successfully.",
//     });
//   } catch (error) {
//     await connection.rollback();
//     connection.release();
//     logger.error("Family wallet payment error:", error);
//     res.status(400).json({ status: "failed", error });
//   }
// };

// const payAccumulatedBill = async (req, res) => {
//   let {
//     patient_id,
//     rx_time,
//     rx_session,
//     amount_paid,
//     description,
//     payment_means,
//     //TODO: add payer type
//     staff,
//   } = req.body;

//   if (!patient_id) throw "patient ID is required";
//   if (!amount_paid) throw "amount is required";
//   if (!validator.isNumeric(amount_paid.toString()))
//     throw `${amount_paid} is not a valid amount`;
//   if (!staff) throw "staff name is required";
//   if (rx_time == undefined || "") {
//     rx_time = "N/A";
//   }
//   if (rx_session == undefined || "") {
//     rx_session = "N/A";
//   }

//   const checkIDSql = `SELECT patient_id, outstanding_balance FROM patients WHERE patient_id=?`;
//   const updatePtWalletSql =
//     "UPDATE patients SET outstanding_balance=? WHERE patient_id=?";
//   const insertPaymentSql = `INSERT INTO payments (patient_id, rx_time, amount_paid, paid_for, payment_method, confirmed_by, rx_session) VALUES (?, ?, ?, ?, ?, ?, ?)`;

//   const connection = await db.getConnection();
//   try {
//     await connection.beginTransaction();

//     const [validID] = await connection.execute(checkIDSql, [patient_id]);
//     if (validID.length < 1)
//       throw `Incorrect patient ID: Patient with ID ${patient_id} NOT found`;

//     const prev_bal = Number(validID[0].outstanding_balance);
//     const current_bal = prev_bal + Number(amount_paid);

//     await connection.execute(updatePtWalletSql, [current_bal, patient_id]);

//     // Insert payment record
//     await connection.execute(insertPaymentSql, [
//       patient_id,
//       rx_time || "N/A",
//       amount_paid,
//       description || "N/A",
//       payment_means || "N/A",
//       staff,
//       rx_session || "N/A",
//     ]);

//     await connection.commit();
//     connection.release();

//     res.status(200).json({
//       status: "success",
//       message: "Payment processed successfully",
//     });
//   } catch (error) {
//     await connection.rollback();
//     connection.release();
//     logger.error("Database error in payBill:", error);
//     res.status(400).json({
//       status: "error",
//       error,
//     });
//   }
// };

// ** This function processes a payment for a specific bill and session,
// updating the patient's outstanding balance and inserting a payment record into the database.
// It also handles errors and transactions to ensure data integrity.

const fundFamilyWalletAndSettleBills = async (req, res) => {
  const { family_name, amount_paid, payment_means, staff, tx_ref } = req.body;

  if (!family_name) throw "Family name is required";
  if (!payment_means) throw "Payment means is required";
  if (!amount_paid) throw "Amount is required";
  if (!validator.isNumeric(amount_paid.toString()))
    throw "Invalid amount\n\n" + amount_paid + "\n\nAmount can ONLY be NUMBERS";
  if (!staff) throw "Staff name is required";

  const getFamilyInfo = `SELECT id, wallet FROM families WHERE name = ?`;
  const updateFamilyWallet = `UPDATE families SET wallet = ? WHERE name = ?`;
  const insertFamilyTxn = `
    INSERT INTO family_wallet_tx (
      family_id, transaction_type, amount, balance_before, balance_after,
      purpose, tx_ref, payment_channel, updated_by
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
  `;

  const getPatientsInFamily = `SELECT patient_id FROM patients WHERE family_or_group = ?`;
  const fetchUnpaidBills = `
    SELECT * FROM billing_summary
    WHERE patient_id = ? AND status IN ('pending', 'partially_paid')
    ORDER BY rx_time ASC
  `;
  const updateBillSummary = `
    UPDATE billing_summary
    SET total_paid = ?, balance_remaining = ?, status = ?
    WHERE id = ?
  `;
  const insertPaymentSql = `
    INSERT INTO payments (
      patient_id, rx_time, amount_paid, paid_for, payment_method, payer_type,
      confirmed_by, rx_session, bank_name, wallet_txn_ref
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `;
  const updateTxBillSql = `
    UPDATE tx SET paid = ?, to_balance = ?, bill_to = ?
    WHERE patient_id = ? AND session_id = ? AND date_time = ?
  `;
  const getTxBillTo = `
    SELECT bill_to FROM tx WHERE patient_id = ? AND session_id = ? AND date_time = ? LIMIT 1
  `;

  const connection = await db.getConnection();
  try {
    await connection.beginTransaction();

    const [familyRows] = await connection.execute(getFamilyInfo, [family_name]);
    if (familyRows.length === 0) throw `Family '${family_name}' not found.`;

    const family = familyRows[0];
    const prevBalance = Number(family.wallet);
    const deposit = Number(amount_paid);
    const newBalance = prevBalance + deposit;

    // Generate reference ID
    const walletTxnRef = `deposit-${Date.now()}`;

    // 1. Update wallet balance
    await connection.execute(updateFamilyWallet, [newBalance, family_name]);

    // 2. Log the credit into family_wallet_tx
    await connection.execute(insertFamilyTxn, [
      family.id,
      "credit",
      deposit,
      prevBalance,
      newBalance,
      "Wallet funding",
      walletTxnRef,
      payment_means,
      staff,
    ]);

    // 3. Get patients in the family
    const [patients] = await connection.execute(getPatientsInFamily, [
      family_name,
    ]);

    //Note: Any payment made in the following loop is from deposit not wallet as this loop can only be reached if wallet is negative.
    // Please understand that, negative wallet means unpaid bills, and we can't pay bills from negative wallet.
    // So, any deposit to negative wallet is actually a deposit to pay bills, If the deposit is more than the negative wallet,
    // then the remaining balance is added to the wallet.

    let remainingPayment = deposit;

    for (const p of patients) {
      const [unpaidBills] = await connection.execute(fetchUnpaidBills, [
        p.patient_id,
      ]);

      for (const bill of unpaidBills) {
        const netPayable = Number(bill.net_payable);
        const alreadyPaid = Number(bill.total_paid || 0);
        const remaining = netPayable - alreadyPaid;

        if (remaining <= 0 || remainingPayment <= 0) continue; // If the bill is already fully paid, skip it

        // Check bill_to before applying payment
        const [txBillToRow] = await connection.execute(getTxBillTo, [
          p.patient_id,
          bill.session_id,
          bill.rx_time,
        ]);
        const billTo = txBillToRow[0]?.bill_to?.toLowerCase();
        if (billTo !== "family/group" && billTo !== family_name.toLowerCase())
          continue;

        const toPay = Math.min(remaining, remainingPayment);
        const newPaid = alreadyPaid + toPay;
        const newRemaining = netPayable - newPaid;
        const newStatus =
          Math.abs(newRemaining) < 0.01 ? "paid" : "partially_paid";

        // Update billing summary
        await connection.execute(updateBillSummary, [
          newPaid,
          newRemaining,
          newStatus,
          bill.id,
        ]);

        // Log the bill payment--- payment table actually stores the payment of each bill
        // for each patient(either through deposit or wallet), so we can tag it with wallet_txn_ref to identify wallet that made the payment
        // This is important for tracking payments made via family deposit/ wallet
        await connection.execute(insertPaymentSql, [
          p.patient_id,
          bill.rx_time,
          toPay,
          "treatment",
          "wallet",
          "family/group",
          staff,
          bill.session_id,
          "N/A",
          walletTxnRef, // tag it!
        ]);

        // Update tx record
        await connection.execute(updateTxBillSql, [
          newPaid,
          newRemaining,
          family_name,
          p.patient_id,
          bill.session_id,
          bill.rx_time,
        ]);

        // Log the debit for this payment from wallet-- this is not needed, as we already logged the credit above and the payment is actually from deposit

        // const walletBefore = remainingPayment === deposit
        //   ? newBalance
        //   : newBalance - (deposit - remainingPayment);
        // const walletAfter = walletBefore - toPay;

        // await connection.execute(insertFamilyTxn, [
        //   family.id,
        //   "debit",
        //   toPay,
        //   walletBefore,
        //   walletAfter,
        //   `Bill payment for ${p.patient_id}`,
        //   bill.session_id,
        //   "wallet",
        //   staff,
        // ]);

        remainingPayment -= toPay;
        if (remainingPayment <= 0) break;
      }

      if (remainingPayment <= 0) break;
    }

    await connection.commit();
    connection.release();

    res.status(200).json({
      status: "success",
      message: "Family wallet funded and bills settled successfully.",
    });
  } catch (error) {
    await connection.rollback();
    connection.release();
    logger.error("Family wallet payment error:", error);
    res.status(400).json({ status: "failed", error });
  }
};
const processPayment = async (req, res) => {
  res.status(501).json({
    status: "failed",
    error:
      "This medium is currently suspended for maintenance.\n\nPlease, pay through the patient's or family/group wallet.",
  });
  return;
  const {
    patient_id,
    rx_time,
    rx_session,
    bank_name = "N/A",
    amount_paid,
    paid_for,
    payment_method,
    confirmed_by,
    payer_type, // e.g., "patient", "hmo", "foc"
  } = req.body;

  if (!validator.isNumeric(amount_paid.toString()))
    return res
      .status(400)
      .json({ error: `${amount_paid} is not a valid amount` });
  if (amount_paid <= 0)
    return res
      .status(400)
      .json({ error: "Amount paid must be greater than zero." });

  if (!patient_id || !rx_time || !rx_session || !amount_paid || !confirmed_by) {
    return res.status(400).json({ error: "Missing required payment fields." });
  }

  const connection = await db.getConnection();
  try {
    await connection.beginTransaction();

    const [billingSummary] = await connection.execute(
      `SELECT net_payable, status FROM billing_summary
       WHERE patient_id = ? AND session_id = ? AND rx_time = ? LIMIT 1`,
      [patient_id, rx_session, rx_time]
    );

    if (billingSummary.length === 0) {
      return res.status(404).json({ error: "Billing summary not found." });
    }

    const { net_payable, status } = billingSummary[0];

    if (status === "paid") {
      return res
        .status(400)
        .json({ error: "This bill has already been marked as paid." });
    }

    if (payment_method === "Wallet") {
      const [walletRes] = await connection.execute(
        "SELECT outstanding_balance FROM patients WHERE patient_id = ?",
        [patient_id]
      );

      if (walletRes.length === 0) {
        return res.status(400).json({
          error: `Patient with ID ${patient_id} not found.`,
        });
      }
      if (walletRes[0].outstanding_balance === null) {
        return res.status(400).json({
          error: `Patient with ID ${patient_id} has no wallet balance.`,
        });
      }
      const currentWallet = Number(walletRes[0]?.outstanding_balance || 0);

      if (currentWallet < amount_paid)
        return res.status(400).json({
          error: `Insufficient wallet balance. Available: ₦${currentWallet}`,
        });

      const newWallet = currentWallet - amount_paid;

      await connection.execute(
        "UPDATE patients SET outstanding_balance = ? WHERE patient_id = ?",
        [newWallet, patient_id]
      );
    }

    await connection.execute(
      `INSERT INTO payments (
         patient_id, rx_time, amount_paid, paid_for, payment_method,
         payer_type, confirmed_by, rx_session, bank_name
       ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        patient_id,
        rx_time,
        amount_paid,
        paid_for || "N/A",
        payment_method || "N/A",
        payer_type || "patient",
        confirmed_by,
        rx_session,
        bank_name,
      ]
    );

    const [payments] = await connection.execute(
      `SELECT SUM(amount_paid) AS total_paid FROM payments
       WHERE patient_id = ? AND rx_time = ? AND rx_session = ?`,
      [patient_id, rx_time, rx_session]
    );

    const totalPaid = Number(payments[0].total_paid || 0);
    const payable = Number(net_payable);
    const remaining = payable - totalPaid;

    const isFullyPaid = Math.abs(totalPaid - payable) < 0.01;
    const newStatus = isFullyPaid ? "paid" : "partially_paid";

    await connection.execute(
      `UPDATE billing_summary SET status = ?, total_paid = ?, balance_remaining = ?
       WHERE patient_id = ? AND session_id = ? AND rx_time = ?`,
      [newStatus, totalPaid, remaining, patient_id, rx_session, rx_time]
    );

    //   if(payer_type.toLowerCase() === "patient"){
    //     const checkIDSql = `SELECT patient_id, outstanding_balance FROM patients WHERE patient_id=?`;
    // const updatePtWalletSql =
    //   "UPDATE patients SET outstanding_balance=? WHERE patient_id=?";
    //   }

    await connection.commit();
    res.status(200).json({
      message: "Payment processed successfully",
      total_paid: totalPaid,
      status: newStatus,
    });
  } catch (error) {
    await connection.rollback();
    logger.error("Payment processing error:", error);
    res.status(500).json({ error: "Internal Server Error" });
  } finally {
    if (connection) connection.release();
  }
};

// ** This function retrieves all payments made by a patient,
// including pagination and filtering options.
// It also handles errors and transactions to ensure data integrity.

const getAllPayments = async (req, res) => {
  const {
    id,
    limit,
    offset,
    start_date,
    end_date,
    q,
    payment_method,
    payer_type,
  } = req.pagination;

  try {
    let baseQuery = `SELECT * FROM payments WHERE 1=1`;
    let params = [];

    // Dynamic filters
    if (q) {
      baseQuery += ` AND (
      patient_id LIKE ? OR 
        bill_reference LIKE ? OR
        payment_method LIKE ? OR
        bank_name LIKE ? OR
        rx_session LIKE ?
      )`;
      const searchTerm = `%${q}%`;
      params.push(searchTerm, searchTerm, searchTerm, searchTerm, searchTerm);
    }

    if (start_date && end_date) {
      baseQuery += ` AND DATE(date_time) BETWEEN ? AND ?`;
      params.push(start_date, end_date);
    }

    if (payment_method) {
      baseQuery += ` AND payment_method LIKE ?`;
      params.push(`%${payment_method}%`);
    }

    if (payer_type) {
      baseQuery += ` AND payer_type LIKE ?`;
      params.push(`%${payer_type}%`);
    }

    baseQuery += ` ORDER BY date_time DESC LIMIT ? OFFSET ?`;
    params.push(limit, offset);

    const [rows] = await db.query(baseQuery, params);

    // Count total for pagination
    let countQuery = `SELECT COUNT(*) AS total FROM payments WHERE 1=1`;
    const countParams = [];
    // rx_session
    if (q) {
      countQuery += ` AND (
        patient_id LIKE ? OR 
        bill_reference LIKE ? OR
        payment_method LIKE ? OR
        bank_name LIKE ? OR
        rx_session LIKE ?
      )`;
      const searchTerm = `%${q}%`;
      countParams.push(
        searchTerm,
        searchTerm,
        searchTerm,
        searchTerm,
        searchTerm
      );
    }

    if (start_date && end_date) {
      countQuery += ` AND DATE(date_time) BETWEEN ? AND ?`;
      countParams.push(start_date, end_date);
    }

    if (payment_method) {
      countQuery += ` AND payment_method = ?`;
      countParams.push(payment_method);
    }

    if (payer_type) {
      countQuery += ` AND payer_type = ?`;
      countParams.push(payer_type);
    }

    const [totalResult] = await db.query(countQuery, countParams);
    if (totalResult.length === 0) {
      return res.status(404).json({ error: "No payments found." });
    }
    let cleanRows = transformDateTimeField(rows);

    res.status(200).json({
      status: "success",
      message: "Payments fetched successfully",
      page: req.pagination.page,
      limit,
      total: totalResult[0].total,
      data: cleanRows,
    });
  } catch (error) {
    logger.error("Get Payments Error:", error);
    res.status(500).json({ error: "Server error while fetching payments." });
  }
};

// ** This function searches for payments based on a query string.
// It retrieves payments that match the query in either the patient ID, bill reference, bank name or the payment method.
// It also handles errors and transactions to ensure data integrity.

const searchPayments = async (req, res) => {
  const { q } = req.query;

  if (!q || q.trim() === "") {
    return res.status(400).json({ error: "Query cannot be empty." });
  }

  const connection = await db.getConnection();
  try {
    const searchTerm = `%${q}%`;
    const [results] = await connection.execute(queries.searchPayments, [
      searchTerm,
      searchTerm,
    ]);
    res.json({ results });
  } catch (error) {
    logger.error("Search Payments Error:", error);
    res.status(500).json({ error: "Search failed." });
  } finally {
    connection.release();
  }
};

// ** This function deletes a payment record from the database.
// It also handles errors and transactions to ensure data integrity.
// It is important to note that this function should be used with caution,
// as deleting payment records can have significant implications for financial reporting and auditing.

const deletePayment = async (req, res) => {
  const { id } = req.params;
  const connection = await db.getConnection();
  try {
    await connection.execute(queries.deletePayment, [id]);
    res.json({ message: "Payment deleted." });
  } catch (error) {
    logger.error("Delete Payment Error:", error);
    res.status(500).json({ error: "Unable to delete payment." });
  } finally {
    connection.release();
  }
};

module.exports = {
  processPayment,
  payAccumulatedBill,
  fundFamilyWalletAndSettleBills,
  getAllPayments,
  searchPayments,
  deletePayment,
};

// ** This function processes a payment for a specific bill,
// updating the patient's outstanding balance and inserting a payment record into the database.
// It also handles errors and transactions to ensure data integrity.

// const createPayment = async (req, res) => {
//   const {
//     patient_id,
//     rx_time,
//     amount_paid,
//     paid_for,
//     bill_reference,
//     payment_method,
//     payer_type = "patient",
//     confirmed_by,
//     rx_session,
//     bank_name = null,
//   } = req.body;

//   if (!patient_id || !amount_paid || !confirmed_by)
//     return res.status(400).json({ error: "Missing required fields" });

//   const connection = await db.getConnection();
//   try {
//     await connection.execute(queries.insertPayment, [
//       patient_id,
//       rx_time,
//       amount_paid,
//       paid_for,
//       bill_reference,
//       payment_method,
//       payer_type,
//       confirmed_by,
//       rx_session,
//       bank_name,
//     ]);
//     res.status(201).json({ message: "Payment recorded successfully." });
//   } catch (error) {
//     logger.error("Create Payment Error:", error);
//     res.status(500).json({ error: "Failed to record payment." });
//   } finally {
//     connection.release();
//   }
// };
