const db = require("../../../managers/dbConfig");
const logger = require("../../../handlers/logger");
const billingQueries = require("../billingsAndPayment/queries/billing.sql");
const validator = require("validator");

const recordSale = async (req, res) => {
  const connection = await db.getConnection();
  try {
    const {
      patient_id, // should be "customer" or a real patient ID
      session_id,
      rx_time,
      items,
      vat_rate = 0,
      discount_type = "percent",
      discount_value = 0,
      billed_by,
      bill_to,
    } = req.body;

    if (!patient_id || !session_id || !rx_time || !items || !billed_by) {
      throw "Missing required fields for sale.";
    }

    if (!Array.isArray(items) || items.length === 0) {
      throw "Sales Cart is empty.";
    }

    await connection.beginTransaction();

    // Dispense-Section: Validate & optionally dispense........
    for (const item of items) {
      if (
        item.item_type === "inventory" &&
        (!item.inventory_id || item.inventory_id === "N/A")
      ) {
        throw `Invalid inventory_id for item: ${item.item_name}`;
      }

      if (item.unit_price < 1 || item.amount_price < 1 || item.quantity < 1) {
        throw `Invalid quantity or pricing for: ${item.item_name}`;
      }

      // Dispense inventory if necessary
      if (item.item_type === "inventory") {
        const [inv] = await connection.execute(
          "SELECT unit_quantities FROM inventories WHERE id = ?",
          [item.inventory_id]
        );

        if (!inv.length) throw `Inventory not found for ${item.item_name}`;

        if (inv[0].unit_quantities < item.quantity)
          throw `Insufficient stock for ${item.item_name}`;

        // update inventory
        await connection.execute(
          `UPDATE inventories SET unit_quantities = unit_quantities - ? WHERE id = ?`,
          [item.quantity, item.inventory_id]
        );

        // log dispensing
        await connection.execute(
          `INSERT INTO inventory_dispensed (inventory_id, patient_id, quantity_before, quantity_dispensed, quantity_after, dispensed_by, branch_id) 
           VALUES (?, ?, ?, ?, ?, ?, ?)`,
          [
            item.inventory_id,
            patient_id,
            inv[0].unit_quantities,
            item.quantity,
            inv[0].unit_quantities - item.quantity,
            billed_by,
            req.user.branch_id,
          ]
        );
      }

      // Billing-Section: Save to bills table..........
      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,
        rx_time,
        billed_by,
      ]);
    }

    // Calculate totals
    const subtotal = items.reduce((sum, item) => sum + item.amount_price, 0);
    const vatAmount = (vat_rate / 100) * subtotal;
    const discountAmount =
      discount_type === "percent"
        ? (discount_value / 100) * subtotal
        : parseFloat(discount_value);
    const netPayable = subtotal + vatAmount - discountAmount;
    // delete previous summary if exists
    await connection.execute(billingQueries.deleteSummary, [
      patient_id,
      session_id,
      rx_time,
    ]);

    // Insert new billing summary
    await connection.execute(billingQueries.insertSummary, [
      patient_id,
      session_id,
      rx_time,
      subtotal,
      vat_rate,
      discount_type,
      discount_value,
      vatAmount,
      discountAmount,
      netPayable,
      "paid", // auto mark as paid
      netPayable, // total paid
      0, // balance remaining
    ]);

    //**  Payment-Section: Insert payment record..........   */
    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,
        netPayable,
        "Outpatient sales",
        "cash",
        "patient",
        billed_by,
        session_id,
        "N/A",
      ]
    );

    await connection.commit();
    res.status(200).json({
      message: "Sale recorded successfully!",
      total: netPayable,
    });
  } catch (error) {
    await connection.rollback();
    logger.error("Sales Error:", error);
    res.status(500).json({ error: error.message || error });
  } finally {
    connection.release();
  }
};

// this function search both inventories and service_catalog using search_type query parameter

const searchItemsByType = async (req, res) => {
  const { search_type, q } = req.query;
  const keyword = `%${q?.trim() || ""}%`;
  // console.log(q);

  if (
    !search_type ||
    !["inventory", "service"].includes(search_type.toLowerCase())
  ) {
    return res.status(400).json({
      error: "Invalid or missing 'search_type'. Use 'inventory' or 'service'.",
    });
  }

  const connection = await db.getConnection();
  try {
    let query, params;

    if (search_type === "inventory") {
      query = `
        SELECT id, item_name, sp_pu AS price, 'inventory' AS item_type
        FROM inventories
        WHERE item_name LIKE ? OR item_class LIKE ?
        LIMIT 30
      `;
      params = [keyword, keyword];
    } else if (search_type === "service") {
      query = `
        SELECT id, name AS item_name, price, 'service' AS item_type
        FROM services_catalog
        WHERE name LIKE ? OR description LIKE ?
        LIMIT 30
      `;
      params = [keyword, keyword];
    }

    const [results] = await connection.execute(query, params);
    res.json({ results });
  } catch (err) {
    logger.error("Search Error:", err);
    res.status(500).json({ error: "Search failed." });
  } finally {
    connection.release();
  }
};

module.exports = { searchItemsByType, recordSale };
