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

class Inventory {
  // Fetch all inventory items for a specific branch
  static async getAll(branch_id, limit, offset) {
    if (!branch_id) throw "Branch ID is required";

    const [rows] = await db.execute(
      "SELECT * FROM inventories WHERE branch_id = ? ORDER BY date_time DESC LIMIT ? OFFSET ?",
      [branch_id, limit, offset]
    );

    return rows;
  }

  // Fetch a single inventory item by ID
  static async getById(id, branch_id) {
    if (!validator.isNumeric(String(id))) throw `${id} is not a valid stock ID`;
    if (!validator.isNumeric(String(branch_id)))
      throw `${branch_id} is not a valid branch ID`;
    const [rows] = await db.execute(
      "SELECT * FROM inventories WHERE id = ? AND branch_id = ?",
      [id, branch_id]
    );
    return rows[0];
  }

  // Fetch inventory item by filter
  static async getByFilter({
    id,
    item_name,
    item_class,
    branch_id,
    limit,
    offset,
  }) {
    let query = "SELECT * FROM inventories WHERE branch_id = ?";
    const params = [branch_id];

    if (id) {
      query += " AND (id = ? OR item_name LIKE ? OR item_class LIKE ?)";
      params.push(id, `%${id}%`, `%${id}%`);
    }

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

    const [rows] = await db.execute(query, params);
    return rows;
  }

  // Add new inventory item
  static async create(
    item_name,
    quantity,
    item_type,
    item_class,
    low_stock_threshold,
    branch_id,
    updated_by,
    cp_pu,
    sp_pu
  ) {
    return db.execute(
      "INSERT INTO inventories (item_name, unit_quantities, last_restock_quantity, item_type, item_class, low_stock_threshold, branch_id, updated_by, cp_pu, sp_pu) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
      [
        item_name,
        quantity,
        quantity,
        item_type,
        item_class,
        low_stock_threshold,
        branch_id,
        updated_by,
        cp_pu,
        sp_pu,
      ]
    );
  }

  // Update stock details
  static async updateStockDetails(
    id,
    item_name,
    cp_pu,
    sp_pu,
    item_type,
    item_class,
    low_stock_threshold,
    updated_by,
    branch_id
  ) {
    let query = "UPDATE inventories SET updated_by = ?";
    let params = [updated_by];

    if (item_name != "") {
      query += ", item_name = ?";
      params.push(item_name);
    }

    if (item_type != null && item_type != "----") {
      query += ", item_type = ?";
      params.push(item_type);
    }

    if (item_class != null && item_class != "----") {
      query += ", item_class = ?";
      params.push(item_class);
    }

    if (low_stock_threshold != "") {
      if (!validator.isNumeric(String(low_stock_threshold)))
        throw `${low_stock_threshold} is not a valid stock Number`;
      query += ", low_stock_threshold = ?";
      params.push(low_stock_threshold);
    }

    if (cp_pu != "") {
      if (!validator.isNumeric(String(cp_pu)))
        throw `prices can only be whole digits or decimal-- you sent ${cp_pu}`;
      query += ", cp_pu = ?";
      params.push(cp_pu);
    }

    if (sp_pu != "") {
      if (!validator.isNumeric(String(sp_pu)))
        throw `prices can only be whole digits or decimal-- you sent ${sp_pu} `;
      query += ", sp_pu = ?";
      params.push(sp_pu);
    }

    query += " WHERE id = ? AND branch_id = ?";
    params.push(id, branch_id);

    return db.execute(query, params);
  }

  // Increase stock (restocking)
  static async restock(id, quantity, cp_pu, updated_by, branch_id) {
    const connection = await db.getConnection();
    await connection.beginTransaction();

    try {
      const [previousData] = await connection.execute(
        "SELECT * FROM inventories WHERE id = ? AND branch_id = ?",
        [id, branch_id]
      );

      if (!previousData[0])
        throw "Item NOT Found: Incorrect ID or may have been deleted!";

      await connection.execute(
        "UPDATE inventories SET unit_quantities = unit_quantities + ?, last_restock_quantity = ?, cp_pu = ?, updated_by = ? WHERE id = ? AND branch_id = ?",
        [quantity, quantity, cp_pu, updated_by, id, branch_id]
      );

      await connection.commit();
      connection.release();
    } catch (error) {
      await connection.rollback();
      connection.release();
      throw error;
    }
  }

  // Dispense stock (reduces quantity)
  static async dispenseStock(
    id,
    quantity,
    patient_id,
    dispensed_by,
    branch_id
  ) {
    const connection = await db.getConnection();
    await connection.beginTransaction();

    try {
      const [previousData] = await connection.execute(
        "SELECT * FROM inventories WHERE id = ? AND branch_id = ?",
        [id, branch_id]
      );

      if (!previousData[0]) throw "Item not found.";
      if (previousData[0].unit_quantities < quantity)
        throw "Insufficient stock";

      const [verify_ptId] = await connection.execute(
        "SELECT patient_id FROM patients WHERE patient_id=?",
        [patient_id]
      );

      if (verify_ptId.length < 1)
        throw `Patient with ID-- ${patient_id} Not found! Incorrect patient ID or the patient may have been deleted.`;

      await connection.execute(
        "UPDATE inventories SET unit_quantities = unit_quantities - ?, updated_by = ? WHERE id = ? AND branch_id = ?",
        [quantity, dispensed_by, id, branch_id]
      );

      await connection.execute(
        "INSERT INTO inventory_dispensed (inventory_id, patient_id, quantity_dispensed, dispensed_by, branch_id) VALUES (?, ?, ?, ?, ?)",
        [id, patient_id, quantity, dispensed_by, branch_id]
      );

      await connection.commit();
      connection.release();
    } catch (error) {
      await connection.rollback();
      connection.release();
      throw error;
    }
  }

  // Dispense Multiple Items at Once
  static async dispenseMultipleItems(
    items,
    patient_id,
    dispensed_by,
    branch_id
  ) {
    const connection = await db.getConnection();
    await connection.beginTransaction();

    try {
      for (const item of items) {
        const { inventory_id, quantity, name } = item;
        if (!quantity || quantity <= 0) throw "Invalid quantity for " + name;
        if (!validator.isNumeric(String(quantity)))
          throw `${quantity} is NOT a valid and plain number`;
        patient_id = patient_id ?? "Not for patient";

        const [inventory] = await connection.execute(
          "SELECT unit_quantities FROM inventories WHERE id = ? AND branch_id = ?",
          [inventory_id, branch_id]
        );

        if (!inventory.length)
          throw `NOT-IN-STOCK: ${name} with ID ${inventory_id} not found`;
        if (inventory[0].unit_quantities < quantity)
          throw `Insufficient stock for item ID ${inventory_id}`;

        await connection.execute(
          "UPDATE inventories SET unit_quantities = unit_quantities - ?, updated_by = ? WHERE id = ? AND branch_id = ?",
          [quantity, dispensed_by, inventory_id, branch_id]
        );

        const quantity_before = inventory[0].unit_quantities;
        const quantity_after = quantity_before - quantity;

        await connection.execute(
          "INSERT INTO inventory_dispensed (inventory_id, patient_id,quantity_before, quantity_dispensed,quantity_after, dispensed_by, branch_id) VALUES (?, ?, ?, ?, ?,?,?)",
          [
            inventory_id,
            patient_id,
            quantity_before,
            quantity,
            quantity_after,
            dispensed_by,
            branch_id,
          ]
        );
      }

      await connection.commit();
      connection.release();
    } catch (error) {
      await connection.rollback();
      connection.release();
      throw error;
    }
  }

  // Dispense History Query with Filters
  static async getDispenseHistory(branch_id, id, limit, offset) {
    let query = `
    SELECT d.*, i.item_name 
    FROM inventory_dispensed d 
    JOIN inventories i ON d.inventory_id = i.id 
    WHERE d.branch_id = ?
  `;

    const params = [branch_id];

    if (id) {
      query += " AND (d.inventory_id = ? OR d.patient_id = ?)";
      params.push(id, id);
    }

    query += " ORDER BY d.date_time DESC LIMIT ? OFFSET ?";
    params.push(limit, offset);

    return db.execute(query, params);
  }

  // Fetch low-stock items (based on custom or default thresholds)
  static async getLowStockItems(branch_id) {
    return db.execute(
      `SELECT i.*, COALESCE(i.low_stock_threshold, t.default_threshold) AS stock_threshold
      FROM inventories i
      LEFT JOIN inventory_thresholds t ON i.item_type = t.item_type
      WHERE i.branch_id = ? AND i.unit_quantities <= COALESCE(i.low_stock_threshold, t.default_threshold)`,
      [branch_id]
    );
  }
}

module.exports = Inventory;
