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

const queries = require("../queries/payment.sql");

// ** 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;

  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 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 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 newWalletBalance = prev_bal + Number(amount_paid);
    await connection.execute(updatePtWalletSql, [newWalletBalance, patient_id]);

    // Log wallet deposit as a payment
    await connection.execute(insertPaymentSql, [
      patient_id,
      rx_time,
      amount_paid,
      description || "Accumulated bill payment",
      payment_means || "N/A",
      staff,
      rx_session,
    ]);

    // Apply this payment to oldest unpaid bills
    const [unpaidBills] = await connection.execute(fetchUnpaidBills, [
      patient_id,
    ]);

    let remainingPayment = Number(amount_paid);

    for (const bill of unpaidBills) {
      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;

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

      await connection.execute(updateBillSummary, [
        newPaid,
        newRemaining,
        newStatus,
        billId,
      ]);

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

    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,
//     //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 processPayment = async (req, res) => {
  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 ?
      )`;
      const searchTerm = `%${q}%`;
      params.push(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 = [];

    if (q) {
      countQuery += ` AND (
        patient_id LIKE ? OR
        bill_reference LIKE ? OR
        payment_method LIKE ? OR
        bank_name LIKE ?
      )`;
      const searchTerm = `%${q}%`;
      countParams.push(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." });
    }

    res.status(200).json({
      status: "success",
      message: "Payments fetched successfully",
      page: req.pagination.page,
      limit,
      total: totalResult[0].total,
      data: rows,
    });
  } 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,
  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();
//   }
// };
