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 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();
  }
};

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

const updateRxBill = async (req, res) => {
  const {
    patient_id,
    session_id,
    date_time,
    bill,
    bill_to,
    bill_dscr,
    updated_by,
  } = 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, 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 == "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 "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,
  subtotal, vat_rate, discount_type, discount_value,
  vat_amount, discount_amount, net_payable, status
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
`;
    const updateSummary = `
UPDATE billing_summary
SET subtotal=?, vat_rate=?, discount_type=?, discount_value=?,
    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) => {
  const { limit, offset, status, start_date, end_date, patient_id } =
    req.pagination;

  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 (patient_id) {
    baseQuery = baseQuery.replace("{{patientFilter}}", "AND patient_id = ?");
    values.push(patient_id);
  } else {
    baseQuery = baseQuery.replace("{{patientFilter}}", "");
  }

  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 };
