const validator = require("validator");
const logger = require("../../../../handlers/logger");
const {
  formatCurrencyForObjects,
} = require("../../../../managers/customFormaters");
const db = require("../../../../managers/dbConfig");
const {
  transformDateTimeField,
} = require("../../../../managers/formatDateTime");
const billingQueries = require("../queries/billing.sql");
const { error } = require("winston");
const { determineType } = require("../../../../managers/regex");

// const saveBill = async (req, res) => {
//   const connection = await db.getConnection();
//   try {
//     const {
//       patient_id,
//       session_id,
//       rx_time,
//       vat_rate,
//       discount_type,
//       discount_value,
//       items,
//       bill_to,
//       billed_by,
//     } = req.body;

//     // console.log("Billing data:", req.body);

//     if (!billed_by) {
//       return res.status(400).json({ error: "Staff details is required" });
//     }
//     if (!bill_to || bill_to === "-----") {
//       return res
//         .status(400)
//         .json({ error: "Bill-To? Please select who is paying the bill." });
//     }
//     if (!items || items.length === 0) {
//       return res.status(400).json({ error: "ERROR: Empty Billing List!" });
//     }

//     if (!patient_id || !session_id || !rx_time) {
//       return res.status(400).json({
//         error:
//           "ERROR: One the following is missing-- patient_id, session_id, rx_time",
//       });
//     }

//     for (const item of items) {
//       if (
//         (item.inventory_id == "N/A" && item.item_type == "inventory") ||
//         (item.item_type == "inventory" && item.inventory_id == null)
//       ) {
//         return res.status(400).json({
//           error: `ERROR: ${item.item_name} does not exist in inventory register. Please remove it from billing items.`,
//         });
//       } else if (
//         item.unit_price < 1 ||
//         item.amount_price < 1 ||
//         item.quantity < 1
//       ) {
//         return res.status(400).json({
//           error: `ERROR: ${item.item_name} has invalid unit price, amount price or quantity. Please check the values.`,
//         });
//       }
//     }

//     await connection.beginTransaction();

//     // 1. Insert each billing item
//     for (const item of items) {
//       // 🚫 Prevent duplicate billing
//       const [existing] = await connection.execute(
//         `SELECT 1 FROM billing_summary WHERE patient_id = ? AND session_id = ? AND rx_time = ? LIMIT 1`,
//         [patient_id, session_id, rx_time]
//       );
//       // Check if any existing billing summary exists for the same patient and session

//       if (existing.length > 0) {
//         return res.status(400).json({
//           error: "Billing has already been created for this session.",
//         });
//       }

//       await connection.execute(billingQueries.insertBill, [
//         patient_id,
//         session_id,
//         item.inventory_id ?? null,
//         item.item_name,
//         item.quantity,
//         item.unit_price,
//         item.amount_price,
//         item.item_type || "inventory",
//         rx_time,
//         billed_by,
//       ]);
//     }

//     // 2. Calculate subtotal
//     const [totalRow] = await connection.execute(
//       billingQueries.getTotalByBillingTime,
//       [patient_id, session_id, rx_time]
//     );
//     const subtotal = totalRow[0]?.total || 0;

//     // 3. Calculate VAT and Discount
//     const vatAmount = (vat_rate / 100) * subtotal;

//     // ✅ Updated discount calculation
//     let discountAmount = 0;
//     if (discount_type === "percent") {
//       discountAmount = (discount_value / 100) * subtotal;
//     } else if (discount_type === "fixed") {
//       discountAmount = parseFloat(discount_value);
//     }

//     const netPayable = subtotal + vatAmount - discountAmount;

//     // 4. Delete old summary if exists
//     await connection.execute(billingQueries.deleteSummary, [
//       patient_id,
//       session_id,
//       rx_time,
//     ]);

//     // 5. Insert fresh billing summary
//     await connection.execute(billingQueries.insertSummary, [
//       patient_id,
//       session_id,
//       rx_time,
//       subtotal,
//       vat_rate,
//       discount_type,
//       discount_value,
//       vatAmount,
//       discountAmount,
//       netPayable,
//       "pending",
//       0, // total_paid
//       netPayable, // balance_remaining
//     ]);

//     // update wallets according to bill_to, including payment and bill summary if patient's wallet charged

//     if (bill_to === "patient") {
//       // Get patient outstanding_balance -- wallet
//       const [balanceRes] = await connection.execute(
//         "SELECT outstanding_balance FROM patients WHERE patient_id = ?",
//         [patient_id]
//       );
//       const currentBalance = Number(balanceRes[0]?.outstanding_balance || 0);
//       const newBalance = currentBalance - netPayable;

//       // Update outstanding_balance -- wallet
//       await connection.execute(
//         "UPDATE patients SET outstanding_balance = ? WHERE patient_id = ?",
//         [newBalance, patient_id]
//       );

//       // Update payment and billing summary records if wallet is positive

//       if (currentBalance > 0.0) {
//         let walletCharged;

//         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 updateBillingSummarySql = `UPDATE billing_summary SET status = ?, total_paid = ?, balance_remaining = ?
//        WHERE patient_id = ? AND session_id = ? AND rx_time = ?`;

//         //
//         if (currentBalance < netPayable) {
//           walletCharged = currentBalance;

//           await connection.execute(insertPaymentSql, [
//             patient_id,
//             rx_time,
//             walletCharged,
//             "treatment",
//             "Wallet",
//             "patient",
//             billed_by,
//             session_id,
//             "N/A",
//           ]);

//           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, session_id]
//           );

//           const totalPaid = Number(payments[0].total_paid || 0);

//           const remaining = netPayable - totalPaid;

//           await connection.execute(updateBillingSummarySql, [
//             "partially_paid",
//             totalPaid,
//             remaining,
//             patient_id,
//             session_id,
//             rx_time,
//           ]);
//         } else if (currentBalance >= netPayable) {
//           walletCharged = netPayable;

//           await connection.execute(insertPaymentSql, [
//             patient_id,
//             rx_time,
//             walletCharged,
//             "treatment",
//             "Wallet",
//             "patient",
//             billed_by,
//             session_id,
//             "N/A",
//           ]);

//           await connection.execute(updateBillingSummarySql, [
//             "paid",
//             netPayable,
//             0.0,
//             patient_id,
//             session_id,
//             rx_time,
//           ]);
//         }
//       }
//     } else if (bill_to === "hmo") {
//       // Add to HMO wallet and HMO bills
//       const [hmoRes] = await connection.execute(
//         "SELECT hmo, hmo_wallet FROM patients WHERE patient_id = ?",
//         [patient_id]
//       );

//       const hmoCode = hmoRes[0]?.hmo;
//       if (!hmoCode) throw "Patient is not under any HMO";

//       const newWallet = Number(hmoRes[0]?.hmo_wallet || 0) + netPayable;

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

//       await connection.execute(
//         "INSERT INTO hmo_bills (code_name, patient_id, amount) VALUES (?, ?, ?)",
//         [hmoCode, patient_id, netPayable]
//       );
//     } else if (bill_to === "f.o.c") {
//       await connection.execute(
//         "INSERT INTO foc (patient_id, amount, awarded_by) VALUES (?, ?, ?)",
//         [patient_id, netPayable, billed_by]
//       );
//     }

//     await connection.commit();

//     res.json({
//       message: "Bill saved successfully",
//       net_payable: netPayable,
//       subtotal,
//       vatAmount,
//       discountAmount,
//     });
//   } catch (error) {
//     if (connection) await connection.rollback();
//     logger.error("Save Bill Error:", error);
//     res.status(500).json({ error: "Internal Server Error" });
//   } finally {
//     if (connection) connection.release();
//   }
// };

const saveBill = async (req, res, next) => {
  const connection = await db.getConnection();
  try {
    const {
      patient_id,
      session_id,
      rx_time,
      vat_rate,
      discount_type,
      discount_value,
      items,
      bill_to,
      billed_by,
    } = req.body;

    if (!billed_by)
      return res.status(400).json({ error: "Staff details is required" });
    if (!bill_to || bill_to === "-----select-----")
      return res
        .status(400)
        .json({ error: "Bill-To? Please select who is paying the bill." });
    if (!items || items.length === 0)
      return res.status(400).json({ error: "ERROR: Empty Billing List!" });
    if (!patient_id || !session_id || !rx_time)
      return res
        .status(400)
        .json({ error: "Missing patient_id, session_id, or rx_time" });

    for (const item of items) {
      if (
        (item.inventory_id === "N/A" && item.item_type === "inventory") ||
        (item.item_type === "inventory" && item.inventory_id == null)
      ) {
        return res.status(400).json({
          error: `ERROR: ${item.item_name} does not exist in inventory record.\n\nIt's likely prescribed for patient to buy outside this facility.\n\nPlease remove it from the billing list or contact the prescribing doctor.`,
        });
      } else if (
        item.unit_price < 1 ||
        item.amount_price < 1 ||
        item.quantity < 1
      ) {
        return res
          .status(400)
          .json({ error: `ERROR: ${item.item_name} has invalid values.` });
      }
    }

    await connection.beginTransaction();

    for (const item of items) {
      const [existing] = await connection.execute(
        `SELECT 1 FROM billing_summary WHERE patient_id = ? AND session_id = ? AND rx_time = ? LIMIT 1`,
        [patient_id, session_id, rx_time]
      );
      if (existing.length > 0)
        return res
          .status(400)
          .json({ error: "Billing already exists for this session." });

      await connection.execute(billingQueries.insertBill, [
        patient_id,
        session_id,
        item.inventory_id ?? null,
        item.item_name,
        item.quantity,
        item.unit_price,
        item.amount_price,
        item.item_type || "inventory",
        rx_time,
        billed_by,
      ]);
    }

    const [totalRow] = await connection.execute(
      billingQueries.getTotalByBillingTime,
      [patient_id, session_id, rx_time]
    );
    const subtotal = totalRow[0]?.total || 0;
    const vatAmount = (vat_rate / 100) * subtotal;

    let discountAmount = 0;
    if (discount_type === "percent") {
      discountAmount = (discount_value / 100) * subtotal;
    } else if (discount_type === "fixed") {
      discountAmount = parseFloat(discount_value);
    }

    const netPayable = subtotal + vatAmount - discountAmount;

    await connection.execute(billingQueries.deleteSummary, [
      patient_id,
      session_id,
      rx_time,
    ]);

    await connection.execute(billingQueries.insertSummary, [
      patient_id,
      session_id,
      rx_time,
      subtotal,
      vat_rate,
      discount_type,
      discount_value,
      vatAmount,
      discountAmount,
      netPayable,
      "pending",
      0,
      netPayable,
    ]);

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

    if (bill_to === "patient") {
      const [balanceRes] = await connection.execute(
        "SELECT outstanding_balance FROM patients WHERE patient_id = ?",
        [patient_id]
      );
      const currentBalance = Number(balanceRes[0]?.outstanding_balance || 0);
      const newBalance = currentBalance - netPayable;

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

      await connection.execute(
        `INSERT INTO patient_wallet_txn (patient_id, transaction_type, amount, balance_before, balance_after, purpose, tx_ref, payment_channel, updated_by)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          patient_id,
          "debit",
          netPayable,
          currentBalance,
          newBalance,
          "itemized billing",
          session_id,
          "wallet",
          billed_by,
        ]
      );

      let walletCharged = 0;
      if (currentBalance > 0.0) {
        walletCharged = Math.min(currentBalance, netPayable);

        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,
            walletCharged,
            "treatment",
            "Wallet",
            "patient",
            billed_by,
            session_id,
            "N/A",
          ]
        );
      }

      const totalPaid = walletCharged;
      const remaining = netPayable - totalPaid;
      const status =
        totalPaid >= netPayable
          ? "paid"
          : totalPaid > 0
          ? "partially_paid"
          : "pending";

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

      await connection.execute(updateTxBillSql, [
        netPayable,
        totalPaid,
        bill_to,
        newBalance,
        patient_id,
        session_id,
        rx_time,
      ]);
    } else if (bill_to === "family/group") {
      const [grpInfo] = await connection.execute(
        "SELECT family_or_group FROM patients WHERE patient_id = ?",
        [patient_id]
      );
      const groupName = grpInfo[0]?.family_or_group;

      if (
        !groupName ||
        groupName.toLowerCase() === "single/personal" ||
        groupName.toLowerCase() == null
      )
        throw new Error(
          "Billing Error: Patient is not assigned to any valid group/family."
        );

      const [groupDetails] = await connection.execute(
        "SELECT id, wallet FROM families WHERE name = ?",
        [groupName]
      );

      if (groupDetails.length < 1) {
        return res.status(400).json({
          error: `Billing Error: Group/family "${groupName}" not found`,
        });
      }

      const family = groupDetails[0];
      const walletBefore = Number(family.wallet);
      const walletAfter = walletBefore - netPayable;

      await connection.execute("UPDATE families SET wallet = ? WHERE id = ?", [
        walletAfter,
        family.id,
      ]);

      await connection.execute(
        `INSERT INTO family_wallet_tx (family_id, transaction_type, amount, balance_before, balance_after, purpose, tx_ref, payment_channel, updated_by)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [
          family.id,
          "debit",
          netPayable,
          walletBefore,
          walletAfter,
          "itemized billing",
          session_id,
          "wallet",
          billed_by,
        ]
      );

      let walletCharged = 0;
      if (walletBefore > 0.0) {
        walletCharged = Math.min(walletBefore, netPayable);

        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,
            walletCharged,
            "treatment",
            "Wallet",
            "family/group",
            billed_by,
            session_id,
            "N/A",
          ]
        );
      }

      const remaining = netPayable - walletCharged;
      const status =
        walletCharged >= netPayable
          ? "paid"
          : walletCharged > 0
          ? "partially_paid"
          : "pending";

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

      await connection.execute(updateTxBillSql, [
        netPayable,
        walletCharged,
        bill_to,
        walletAfter,
        patient_id,
        session_id,
        rx_time,
      ]);
    } else if (bill_to === "hmo") {
      const [hmoRes] = await connection.execute(
        "SELECT hmo, hmo_wallet FROM patients WHERE patient_id = ?",
        [patient_id]
      );
      const hmoCode = hmoRes[0]?.hmo;
      if (!hmoCode || hmoCode === null || hmoCode.toLowerCase() === "nil")
        throw new Error(
          `Billing Error! Patient ${patient_id} does NOT subscribe to any HMO`
        );

      const newWallet = Number(hmoRes[0]?.hmo_wallet || 0) + netPayable;
      await connection.execute(
        "UPDATE patients SET hmo_wallet = ? WHERE patient_id = ?",
        [newWallet, patient_id]
      );

      await connection.execute(
        "INSERT INTO hmo_bills (code_name, patient_id, amount) VALUES (?, ?, ?)",
        [hmoCode, patient_id, netPayable]
      );
    } else if (bill_to === "f.o.c") {
      await connection.execute(
        "INSERT INTO foc (patient_id, amount, awarded_by) VALUES (?, ?, ?)",
        [patient_id, netPayable, billed_by]
      );
    }

    await connection.commit();

    res.json({
      message: "Bill saved successfully",
      net_payable: netPayable,
      subtotal,
      vatAmount,
      discountAmount,
    });
  } catch (error) {
    if (connection) await connection.rollback();
    logger.error("Save Bill Error:", error);
    return next(error);
  } finally {
    if (connection) connection.release();
  }
};

//**       update RX bill  *****/

const updateRxBill = async (req, res) => {
  let {
    patient_id,
    session_id,
    date_time,
    bill,
    bill_to,
    bill_dscr,
    updated_by,
  } = req.body;

  if (!patient_id) throw "patient ID is required";
  if (!bill) throw "bill amount is required";
  if (!bill_to) throw "please select who's paying the bill";
  if (!session_id) throw "session_id is required";
  if (!date_time) throw "date and time are required";
  if (!validator.isNumeric("0")) throw "Invalid paid amount";
  if (!validator.isNumeric(bill.toString()))
    throw `${bill} is NOT a valid amount`;

  const checkRxSql = `SELECT patient_id, bill, paid, bill_to,to_balance FROM tx WHERE patient_id=? AND session_id=? AND date_time=?`;
  const updateTxBillSql = `UPDATE tx SET bill=?, paid=?, bill_to=?, to_balance=? WHERE patient_id=? AND session_id=? AND date_time=?`;
  const updateWalletSql = `UPDATE patients SET outstanding_balance=? WHERE patient_id=?`;
  const updateHMOWalletSql = `UPDATE patients SET hmo_wallet=? WHERE patient_id=?`;
  const insertToHMOBill = `INSERT INTO hmo_bills(code_name, patient_id, amount) VALUES(?,?,?)`;
  const insertToFOC = `INSERT INTO foc(patient_id, amount, awarded_by) VALUES(?,?,?)`;
  const selectWalletBalanceSql = `SELECT outstanding_balance, hmo, family_or_group, hmo_wallet FROM patients WHERE patient_id=?`;
  const insertBillingHxSql = `INSERT INTO billing_hx (pt_id, rx_time, previous_bill, new_bill, difference, updated_by, date_time, previous_bill_to, new_bill_to) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`;
  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 insertFamilyWalletTx = `
    INSERT INTO family_wallet_tx (
      family_id, transaction_type, amount, balance_before, balance_after,
      purpose, tx_ref, payment_channel, updated_by
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`;

  const connection = await db.getConnection();

  await connection.beginTransaction();

  // 1. Confirm the Rx exists
  const [rxExist] = await connection.execute(checkRxSql, [
    patient_id,
    session_id,
    date_time,
  ]);
  if (rxExist.length < 1)
    throw `Rx not found for session ${session_id} at ${date_time}`;

  // 2. Get patient wallet balances
  const [prev_balance] = await connection.execute(selectWalletBalanceSql, [
    patient_id,
  ]);
  let prev_bal = Number(prev_balance[0].outstanding_balance);
  let hmo_bal = Number(prev_balance[0].hmo_wallet);
  const hmo = prev_balance[0].hmo;
  const patientGroup = prev_balance[0].family_or_group;

  // 3. Prepare billing context
  const prev_bill = Number(rxExist[0].bill);
  const prev_payment = Number(rxExist[0].paid);
  const prev_bill_to = rxExist[0].bill_to;
  const rx_time = date_time;
  let paid = 0;
  let toBalance = prev_bal;
  let walletCharged = 0;

  // 4. BILLING LOGIC
  switch (bill_to.toLowerCase()) {
    case "patient": {
      let oldWallet = prev_bal;
      toBalance = oldWallet - bill;
      await connection.execute(updateWalletSql, [toBalance, patient_id]);
      walletCharged = oldWallet > 0.0 ? Math.min(oldWallet, bill) : 0;
      paid = walletCharged;
      break;
    }

    case "hmo":
      if (!hmo || hmo.toLowerCase() === "nil")
        return res.status(400).json({
          error: `Billing Error! Patient ${patient_id} does NOT subscribe to any HMO`,
        });
      hmo_bal += bill;
      await connection.execute(updateHMOWalletSql, [hmo_bal, patient_id]);
      await connection.execute(insertToHMOBill, [hmo, patient_id, bill]);
      break;

    case "family/group": {
      if (
        patientGroup === "Single/Personal" ||
        !patientGroup ||
        patientGroup.toLowerCase() === null
      )
        throw "Billing Error:Patient  NOT registered under any group or family.\n\nPlease select HMO or Patient as payer";

      const [groupDetails] = await connection.execute(
        `SELECT id, wallet FROM families WHERE name = ?`,
        [patientGroup]
      );
      if (!groupDetails.length)
        throw `Family/group "${patientGroup}" not found`;

      const family = groupDetails[0];
      const oldWallet = Number(family.wallet);
      const newWallet = oldWallet - bill;

      await connection.execute(
        `UPDATE families SET wallet = ? WHERE name = ?`,
        [newWallet, patientGroup]
      );

      await connection.execute(insertFamilyWalletTx, [
        family.id,
        "debit",
        bill,
        oldWallet,
        newWallet,
        bill_dscr,
        session_id,
        "wallet",
        updated_by,
      ]);

      walletCharged = oldWallet > 0.0 ? Math.min(oldWallet, bill) : 0;
      paid = walletCharged;
      break;
    }

    case "f.o.c":
      await connection.execute(insertToFOC, [patient_id, bill, updated_by]);
      break;
  }

  // 5. Insert billing history
  await connection.execute(insertBillingHxSql, [
    patient_id,
    rx_time,
    prev_bill,
    bill,
    bill - prev_bill,
    updated_by,
    date_time,
    prev_bill_to,
    bill_to,
  ]);

  // 6. Update tx table
  await connection.execute(updateTxBillSql, [
    bill,
    paid,
    bill_to,
    toBalance,
    patient_id,
    session_id,
    date_time,
  ]);

  // 7. Insert payment if wallet was actually used
  if (walletCharged > 0) {
    const payerType =
      bill_to.toLowerCase() === "family/group" ? "family/group" : "patient";

    await connection.execute(insertPaymentSql, [
      patient_id,
      rx_time,
      walletCharged,
      bill_dscr || "Rx Billing",
      "Wallet",
      payerType,
      updated_by,
      session_id,
      "N/A",
    ]);
  }

  // 8. Calculate total paid and sync billing_summary
  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, date_time, session_id]
  );
  const totalPaid = Number(payments[0].total_paid || 0);
  const remaining = bill - totalPaid;
  let summaryStatus = "pending";
  if (totalPaid === bill) summaryStatus = "paid";
  else if (totalPaid > 0) summaryStatus = "partially_paid";

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

  if (summaryExists.length > 0) {
    await connection.execute(
      `UPDATE billing_summary
         SET total_bill=?, vat_rate=?, discount_type=?, discount_rate=?,
             vat_amount=?, discount_amount=?, net_payable=?, total_paid=?, balance_remaining=?, status=?
         WHERE patient_id=? AND session_id=? AND rx_time=?`,
      [
        bill,
        0,
        "none",
        0,
        0,
        0,
        bill,
        totalPaid,
        remaining,
        summaryStatus,
        patient_id,
        session_id,
        date_time,
      ]
    );
  } else {
    await connection.execute(
      `INSERT INTO billing_summary (
          patient_id, session_id, rx_time,
          total_bill, vat_rate, discount_type, discount_rate,
          vat_amount, discount_amount, net_payable,total_paid, balance_remaining, status
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        patient_id,
        session_id,
        date_time,
        bill,
        0,
        "none",
        0,
        0,
        0,
        bill,
        totalPaid,
        remaining,
        summaryStatus,
      ]
    );
  }

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

  res.status(200).json({
    status: "success",
    message: "Billing updated successfully",
  });
  await connection.rollback();
  connection.release();
  // } catch (error) {

  //   logger.error("Database error in updateRxBill:", error);
  //   res.status(500).json({
  //     status: "error",
  //     error: "An error occurred while updating the billing.",
  //   });
  // }
};

// const updateRxBill = async (req, res) => {
//   const {
//     patient_id,
//     session_id,
//     date_time,
//     bill,
//     bill_to,
//     bill_dscr,
//     updated_by,
//   } = req.body;
//   console.log("Update Rx Bill Data:", req.body);
//   let paid = 0;
//   if (!patient_id) throw "patient ID is required";
//   //if (!paid) throw "amount paid is required";
//   if (!bill) throw "bill amount is required";
//   if (!bill_to) throw "please select who's paying the bill";
//   if (!session_id) throw "session_id is required";
//   if (!date_time) throw "date and time are required";
//   if (!validator.isNumeric(paid.toString()))
//     throw `${paid} is NOT a valid amount`;
//   if (!validator.isNumeric(bill.toString()))
//     throw `${bill} is NOT a valid amount`;

//   const checkRxSql = `SELECT patient_id, bill, paid, bill_to,to_balance FROM tx WHERE patient_id=? AND session_id=? AND date_time=?`;
//   const updateTxBillSql =
//     "UPDATE tx SET bill=?, paid=?, bill_to=?, to_balance=? WHERE patient_id=? AND session_id=? AND date_time=?";
//   const updateWalletSql = `UPDATE patients SET outstanding_balance=? WHERE patient_id=?`;
//   const updateHMOWalletSql = `UPDATE patients SET hmo_wallet=? WHERE patient_id=?`;
//   const insertToHMOBill = `INSERT INTO hmo_bills(code_name, patient_id, amount) VALUES(?,?,?)`;
//   const insertToFOC = `INSERT INTO foc(patient_id, amount, awarded_by) VALUES(?,?,?)`;
//   const selectWalletBalanceSql = `SELECT outstanding_balance, hmo, family_or_group, hmo_wallet FROM patients WHERE patient_id=?`;
//   const insertBillingHxSql = `INSERT INTO billing_hx (pt_id, rx_time, previous_bill, new_bill, difference, updated_by, date_time, previous_bill_to, new_bill_to) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`;

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

//     const [rxExist] = await connection.execute(checkRxSql, [
//       patient_id,
//       session_id,
//       date_time,
//     ]);
//     if (rxExist.length < 1)
//       throw `Incorrect Session ID OR date_time: Rx with session-ID ${session_id} at ${date_time} NOT found`;

//     if (bill > 0) {
//       const prev_bill = Number(rxExist[0].bill);
//       const prev_payment = Number(rxExist[0].paid);
//       const prev_bill_to = rxExist[0].bill_to;

//       const [prev_balance] = await connection.execute(selectWalletBalanceSql, [
//         patient_id,
//       ]);

//       let prev_bal = Number(prev_balance[0].outstanding_balance);
//       let toBalance = prev_bal;

//       switch (bill_to) {
//         case "patient":
//           toBalance = prev_bal + (paid - bill);
//           await connection.execute(updateWalletSql, [toBalance, patient_id]);
//           break;

//         case "hmo":
//           if (
//             prev_balance[0].hmo.toLowerCase() == "nil" ||
//             prev_balance[0].hmo == null
//           )
//             throw `Billing Error! this patient ${patient_id} does NOT subscribe to any HMO`;
//           prev_bal = Number(prev_balance[0].hmo_wallet);
//           const hmo_wallet = prev_bal + bill;
//           await connection.execute(updateHMOWalletSql, [
//             hmo_wallet,
//             patient_id,
//           ]);
//           await connection.execute(insertToHMOBill, [
//             prev_balance[0].hmo,
//             patient_id,
//             bill,
//           ]);
//           break;
//         case "family/group":
//           const insertFamilyWalletTx = `
//             INSERT INTO family_wallet_tx (
//               family_id, transaction_type, amount, balance_before, balance_after,
//               purpose, tx_ref, payment_channel, updated_by
//             ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`;

//           const patientGroup = prev_balance[0].family_or_group;

//           if (patientGroup === "Single/Personal") {
//             throw "Billing Error: Patient belongs to Single/Personal case note\n\nPlease select HMO or Patient as the payer (i.e., bill-to)";
//           }

//           const selectFamilyOrGrp =
//             "SELECT id, wallet FROM families WHERE name = ?";
//           const updateFamilyOrGrpWallet =
//             "UPDATE families SET wallet = ? WHERE name = ?";

//           const [groupDetails] = await connection.execute(selectFamilyOrGrp, [
//             patientGroup,
//           ]);

//           if (!groupDetails.length) {
//             throw `Billing Error: Group/family "${patientGroup}" not found`;
//           }

//           const family = groupDetails[0];
//           const groupWalletBalance = Number(family.wallet);
//           const toFamilyBalance = groupWalletBalance - bill; // ✅ Allow negative balance

//           // Update group wallet
//           await connection.execute(updateFamilyOrGrpWallet, [
//             toFamilyBalance,
//             patientGroup,
//           ]);

//           // Insert wallet transaction
//           await connection.execute(insertFamilyWalletTx, [
//             family.id,
//             "debit",
//             bill,
//             groupWalletBalance,
//             toFamilyBalance,
//             bill_dscr,
//             sessID,
//             "wallet",
//             signed_by,
//           ]);

//           // 🔁 Insert payment and update billing summary (same as 'patient' logic)
//           if (groupWalletBalance > 0.0) {
//             let walletCharged;

//             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 updateBillingSummarySql = `UPDATE billing_summary SET status = ?, total_paid = ?, balance_remaining = ?
//       WHERE patient_id = ? AND session_id = ? AND rx_time = ?`;

//             if (groupWalletBalance < bill) {
//               walletCharged = groupWalletBalance;

//               await connection.execute(insertPaymentSql, [
//                 pt_id,
//                 rx_time,
//                 walletCharged,
//                 bill_dscr,
//                 "Wallet",
//                 "family/group",
//                 signed_by,
//                 sessID,
//                 "N/A",
//               ]);

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

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

//               await connection.execute(updateBillingSummarySql, [
//                 "partially_paid",
//                 totalPaid,
//                 remaining,
//                 pt_id,
//                 sessID,
//                 rx_time,
//               ]);
//             } else {
//               walletCharged = bill;

//               await connection.execute(insertPaymentSql, [
//                 pt_id,
//                 rx_time,
//                 walletCharged,
//                 bill_dscr,
//                 "Wallet",
//                 "family/group",
//                 signed_by,
//                 sessID,
//                 "N/A",
//               ]);

//               await connection.execute(updateBillingSummarySql, [
//                 "paid",
//                 bill,
//                 0.0,
//                 pt_id,
//                 sessID,
//                 rx_time,
//               ]);
//             }
//           }
//           break;

//         case "f.o.c":
//           await connection.execute(insertToFOC, [patient_id, bill, updated_by]);
//           break;
//       }

//       // Insert into billing_hx
//       await connection.execute(insertBillingHxSql, [
//         patient_id,
//         date_time,
//         prev_bill,
//         bill,
//         bill - prev_bill,
//         updated_by,
//         date_time,
//         prev_bill_to,
//         bill_to,
//       ]);

//       await connection.execute(updateTxBillSql, [
//         bill,
//         paid,
//         bill_to,
//         toBalance,
//         patient_id,
//         session_id,
//         date_time,
//       ]);
//     }

//     // Sync into billing_summary
//     const checkSummary = `
//       SELECT 1 FROM billing_summary
//       WHERE patient_id = ? AND session_id = ? AND rx_time = ? LIMIT 1
//       `;
//     const insertSummary = `
//       INSERT INTO billing_summary (
//         patient_id, session_id, rx_time,
//         total_bill, vat_rate, discount_type, discount_rate,
//         vat_amount, discount_amount, net_payable, status
//       ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
//       `;
//     const updateSummary = `
//       UPDATE billing_summary
//       SET total_bill=?, vat_rate=?, discount_type=?, discount_rate=?,
//           vat_amount=?, discount_amount=?, net_payable=?, status=?
//       WHERE patient_id=? AND session_id=? AND rx_time=?
//       `;

//     const vat_rate = 0;
//     const discount_type = "none";
//     const discount_value = 0;
//     const vat_amount = 0;
//     const discount_amount = 0;
//     const net_payable = bill;
//     const status = "pending";

//     const [summaryExists] = await connection.execute(checkSummary, [
//       patient_id,
//       session_id,
//       date_time,
//     ]);

//     if (summaryExists.length > 0) {
//       await connection.execute(updateSummary, [
//         bill,
//         vat_rate,
//         discount_type,
//         discount_value,
//         vat_amount,
//         discount_amount,
//         net_payable,
//         status,
//         patient_id,
//         session_id,
//         date_time,
//       ]);
//     } else {
//       await connection.execute(insertSummary, [
//         patient_id,
//         session_id,
//         date_time,
//         bill,
//         vat_rate,
//         discount_type,
//         discount_value,
//         vat_amount,
//         discount_amount,
//         net_payable,
//         status,
//       ]);
//     }

//     //console.log("give me task");

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

//     res.status(200).json({
//       status: "success",
//       message: "Billing updated successfully",
//     });
//   } catch (error) {
//     await connection.rollback();
//     connection.release();
//     logger.error("Database error in updateRxBill:", error);
//     res.status(500).json({
//       status: "error",
//       message: "An error occurred while updating the billing.",
//     });
//   }
// };

const getBillingSummaries = async (req, res) => {
  let { limit, offset, status, start_date, end_date, id, q } = req.pagination;

  const sessID = determineType(id);
  if (sessID == "Session ID") {
    // Handle session ID specific logic
    q = id;
    id = null; // Reset id to null if using session ID
  }

  let baseQuery = billingQueries.getBillingSummariesPaginated;
  const filters = [];
  const values = [];

  if (status) {
    baseQuery = baseQuery.replace("{{statusFilter}}", "AND status = ?");
    values.push(status);
  } else {
    baseQuery = baseQuery.replace("{{statusFilter}}", "");
  }

  if (start_date && end_date) {
    baseQuery = baseQuery.replace(
      "{{dateFilter}}",
      "AND rx_time BETWEEN ? AND ?"
    );
    values.push(start_date, end_date);
  } else {
    baseQuery = baseQuery.replace("{{dateFilter}}", "");
  }

  if (id) {
    baseQuery = baseQuery.replace("{{patientFilter}}", "AND patient_id = ?");
    values.push(id);
  } else {
    baseQuery = baseQuery.replace("{{patientFilter}}", "");
  }
  if (q) {
    baseQuery = baseQuery.replace("{{sessionFilter}}", "AND session_id = ?");
    values.push(q);
  } else {
    baseQuery = baseQuery.replace("{{sessionFilter}}", "");
  }

  values.push(limit, offset);

  try {
    let [rows] = await db.execute(baseQuery, values);

    newRows = transformDateTimeField(rows);
    newRows = formatCurrencyForObjects(newRows);

    res.status(200).json({ status: "success", data: newRows });
  } catch (error) {
    logger.error("Billing summary fetch failed:", error);
    res
      .status(500)
      .json({ status: "failed", error: "Failed to fetch billing summaries" });
  }
};
module.exports = { saveBill, updateRxBill, getBillingSummaries };
