const { pool } = require("../../config/db");

/* ============================================================
   STOCK CONSUMPTION CONTROLLER
   Manages inventory stock levels, consumption tracking, and stock updates
============================================================ */

/* ✅ UPDATE STOCK AFTER CONSUMPTION */
const updateStockAfterConsumption = async (req, res) => {
  const client = await pool.connect();
  try {
    const { itemId } = req.params;
    const { quantity, consumption_id, shift_id, remarks } = req.body;
    const org_id = req.session.user?.orgId;
    const userId = req.session.user?.userId;

    if (!org_id) {
      return res.status(401).json({ success: false, message: "Unauthorized" });
    }

    if (!quantity || quantity <= 0) {
      return res.status(400).json({ success: false, message: "Valid quantity is required" });
    }

    await client.query('BEGIN');

    // Get current item stock
    const itemResult = await client.query(
      `SELECT ci.*, cc.name as category_name 
       FROM consumption_items ci
       JOIN consumption_categories cc ON cc.id = ci.category_id
       WHERE ci.id = $1 AND ci.org_id = $2 AND ci.is_deleted = false`,
      [itemId, org_id]
    );

    if (itemResult.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Item not found" });
    }

    const item = itemResult.rows[0];
    const currentStock = parseFloat(item.available_stock) || 0;
    const consumedQuantity = parseFloat(quantity);
    
    // Check if enough stock is available
    if (consumedQuantity > currentStock) {
      return res.status(400).json({ 
        success: false, 
        message: `Insufficient stock. Available: ${currentStock} ${item.uom}, Required: ${consumedQuantity} ${item.uom}` 
      });
    }

    const newStock = currentStock - consumedQuantity;

    // Update stock in consumption_items table
    await client.query(
      `UPDATE consumption_items 
       SET available_stock = $1, 
           last_stock_update = NOW(),
           updated_by = $2,
           updated_at = NOW()
       WHERE id = $3 AND org_id = $4`,
      [newStock, userId, itemId, org_id]
    );

    // Record stock transaction
    await client.query(
      `INSERT INTO stock_transactions 
       (item_id, transaction_type, quantity, previous_stock, new_stock, 
        reference_id, reference_type, remarks, created_by, org_id)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)`,
      [itemId, 'CONSUMPTION', -consumedQuantity, currentStock, newStock, 
       consumption_id, 'DAILY_CONSUMPTION', remarks || `Consumed ${consumedQuantity} ${item.uom}`, userId, org_id]
    );

    // Check for low stock alert
    const minimumStock = parseFloat(item.minimum_stock) || 0;
    if (newStock <= minimumStock && minimumStock > 0) {
      // Check if alert already exists
      const existingAlert = await client.query(
        `SELECT id FROM stock_alerts 
         WHERE item_id = $1 AND is_resolved = false AND org_id = $2`,
        [itemId, org_id]
      );

      if (existingAlert.rows.length === 0) {
        await client.query(
          `INSERT INTO stock_alerts (item_id, alert_type, alert_message, org_id)
           VALUES ($1, $2, $3, $4)`,
          [itemId, 'LOW_STOCK', `Item ${item.name} is low on stock. Current stock: ${newStock} ${item.uom}. Minimum required: ${minimumStock} ${item.uom}`, org_id]
        );
      }
    }

    await client.query('COMMIT');

    res.json({
      success: true,
      message: "Stock updated successfully",
      data: {
        item_id: itemId,
        item_name: item.name,
        previous_stock: currentStock,
        consumed_quantity: consumedQuantity,
        new_stock: newStock,
        uom: item.uom
      }
    });

  } catch (err) {
    await client.query('ROLLBACK');
    console.error("UPDATE STOCK AFTER CONSUMPTION ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  } finally {
    client.release();
  }
};

/* ✅ ADD STOCK (PURCHASE / RESTOCK) */
const addStock = async (req, res) => {
  const client = await pool.connect();
  try {
    const { itemId } = req.params;
    const { quantity, reference_id, reference_type, remarks, unit_price, vendor_name } = req.body;
    const org_id = req.session.user?.orgId;
    const userId = req.session.user?.userId;

    if (!org_id) {
      return res.status(401).json({ success: false, message: "Unauthorized" });
    }

    if (!quantity || quantity <= 0) {
      return res.status(400).json({ success: false, message: "Valid quantity is required" });
    }

    await client.query('BEGIN');

    // Get current item
    const itemResult = await client.query(
      `SELECT * FROM consumption_items WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [itemId, org_id]
    );

    if (itemResult.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Item not found" });
    }

    const item = itemResult.rows[0];
    const currentStock = parseFloat(item.available_stock) || 0;
    const addedQuantity = parseFloat(quantity);
    const newStock = currentStock + addedQuantity;

    // Update stock
    await client.query(
      `UPDATE consumption_items 
       SET available_stock = $1, 
           last_stock_update = NOW(),
           updated_by = $2,
           updated_at = NOW()
       WHERE id = $3 AND org_id = $4`,
      [newStock, userId, itemId, org_id]
    );

    // Record stock transaction
    await client.query(
      `INSERT INTO stock_transactions 
       (item_id, transaction_type, quantity, previous_stock, new_stock, 
        reference_id, reference_type, remarks, created_by, org_id)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)`,
      [itemId, 'PURCHASE', addedQuantity, currentStock, newStock, 
       reference_id, reference_type, remarks || `Added ${addedQuantity} ${item.uom}`, userId, org_id]
    );

    // Resolve low stock alerts if any
    if (currentStock <= (item.minimum_stock || 0) && newStock > (item.minimum_stock || 0)) {
      await client.query(
        `UPDATE stock_alerts 
         SET is_resolved = true, resolved_at = NOW(), resolved_by = $1
         WHERE item_id = $2 AND is_resolved = false AND org_id = $3`,
        [userId, itemId, org_id]
      );
    }

    await client.query('COMMIT');

    res.json({
      success: true,
      message: "Stock added successfully",
      data: {
        item_id: itemId,
        item_name: item.name,
        previous_stock: currentStock,
        added_quantity: addedQuantity,
        new_stock: newStock,
        uom: item.uom
      }
    });

  } catch (err) {
    await client.query('ROLLBACK');
    console.error("ADD STOCK ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  } finally {
    client.release();
  }
};

/* ✅ GET STOCK TRANSACTIONS FOR AN ITEM */
const getStockTransactions = async (req, res) => {
  try {
    const { itemId } = req.params;
    const org_id = req.session.user?.orgId;
    const { page = 1, limit = 20, transaction_type } = req.query;
    const offset = (parseInt(page) - 1) * parseInt(limit);

    let query = `
      SELECT st.*, 
             CONCAT(u.first_name, ' ', u.last_name) as user_name,
             ci.name as item_name,
             ci.uom
      FROM stock_transactions st
      LEFT JOIN users u ON u.id = st.created_by
      JOIN consumption_items ci ON ci.id = st.item_id
      WHERE st.item_id = $1 AND st.org_id = $2
    `;
    
    const params = [itemId, org_id];
    let paramIndex = 3;

    if (transaction_type) {
      query += ` AND st.transaction_type = $${paramIndex}`;
      params.push(transaction_type);
      paramIndex++;
    }

    query += ` ORDER BY st.created_at DESC LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`;
    params.push(parseInt(limit), offset);

    const result = await pool.query(query, params);

    const countQuery = `
      SELECT COUNT(*) FROM stock_transactions 
      WHERE item_id = $1 AND org_id = $2
    `;
    const countParams = [itemId, org_id];
    
    if (transaction_type) {
      countQuery += ` AND transaction_type = $3`;
      countParams.push(transaction_type);
    }

    const countResult = await pool.query(countQuery, countParams);

    res.json({
      success: true,
      data: result.rows,
      pagination: {
        total: parseInt(countResult.rows[0].count),
        page: parseInt(page),
        limit: parseInt(limit),
        totalPages: Math.ceil(parseInt(countResult.rows[0].count) / parseInt(limit))
      }
    });

  } catch (err) {
    console.error("GET STOCK TRANSACTIONS ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ✅ GET STOCK SUMMARY FOR ALL ITEMS */
const getStockSummary = async (req, res) => {
  try {
    const org_id = req.session.user?.orgId;
    const { category_id } = req.query;

    let query = `
      SELECT 
        ci.id,
        ci.name,
        ci.uom,
        ci.available_stock,
        ci.minimum_stock,
        cc.name as category_name,
        CASE 
          WHEN ci.available_stock <= 0 THEN 'OUT_OF_STOCK'
          WHEN ci.available_stock <= ci.minimum_stock THEN 'LOW_STOCK'
          ELSE 'IN_STOCK'
        END as stock_status,
        (ci.available_stock / NULLIF(ci.minimum_stock, 1) * 100) as stock_percentage
      FROM consumption_items ci
      JOIN consumption_categories cc ON cc.id = ci.category_id
      WHERE ci.org_id = $1 AND ci.is_deleted = false
    `;

    const params = [org_id];
    let paramIndex = 2;

    if (category_id) {
      query += ` AND ci.category_id = $${paramIndex}`;
      params.push(category_id);
      paramIndex++;
    }

    query += ` ORDER BY ci.available_stock ASC, ci.name ASC`;

    const result = await pool.query(query, params);

    // Calculate totals
    const totals = {
      total_items: result.rows.length,
      total_stock_value: 0,
      low_stock_count: result.rows.filter(i => i.stock_status === 'LOW_STOCK').length,
      out_of_stock_count: result.rows.filter(i => i.stock_status === 'OUT_OF_STOCK').length
    };

    res.json({
      success: true,
      data: result.rows,
      summary: totals
    });

  } catch (err) {
    console.error("GET STOCK SUMMARY ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ✅ UPDATE MINIMUM STOCK LEVEL */
const updateMinimumStock = async (req, res) => {
  try {
    const { itemId } = req.params;
    const { minimum_stock } = req.body;
    const org_id = req.session.user?.orgId;
    const userId = req.session.user?.userId;

    if (!org_id) {
      return res.status(401).json({ success: false, message: "Unauthorized" });
    }

    if (minimum_stock === undefined || minimum_stock < 0) {
      return res.status(400).json({ success: false, message: "Valid minimum stock value is required" });
    }

    const result = await pool.query(
      `UPDATE consumption_items 
       SET minimum_stock = $1, 
           updated_by = $2,
           updated_at = NOW()
       WHERE id = $3 AND org_id = $4
       RETURNING *`,
      [minimum_stock, userId, itemId, org_id]
    );

    if (result.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Item not found" });
    }

    res.json({
      success: true,
      message: "Minimum stock level updated successfully",
      data: result.rows[0]
    });

  } catch (err) {
    console.error("UPDATE MINIMUM STOCK ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ✅ GET LOW STOCK ITEMS */
const getLowStockItems = async (req, res) => {
  try {
    const org_id = req.session.user?.orgId;

    const result = await pool.query(
      `SELECT 
         ci.id,
         ci.name,
         ci.uom,
         ci.available_stock,
         ci.minimum_stock,
         cc.name as category_name,
         (ci.minimum_stock - ci.available_stock) as shortage_quantity
       FROM consumption_items ci
       JOIN consumption_categories cc ON cc.id = ci.category_id
       WHERE ci.org_id = $1 
         AND ci.is_deleted = false
         AND ci.available_stock <= ci.minimum_stock
         AND ci.minimum_stock > 0
       ORDER BY (ci.available_stock / NULLIF(ci.minimum_stock, 1)) ASC`,
      [org_id]
    );

    res.json({
      success: true,
      data: result.rows,
      count: result.rows.length
    });

  } catch (err) {
    console.error("GET LOW STOCK ITEMS ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ✅ GET STOCK ALERTS */
const getStockAlerts = async (req, res) => {
  try {
    const org_id = req.session.user?.orgId;
    const { resolved = false } = req.query;

    const result = await pool.query(
      `SELECT 
         sa.*,
         ci.name as item_name,
         ci.uom,
         ci.available_stock,
         ci.minimum_stock,
         CONCAT(u.first_name, ' ', u.last_name) as resolved_by_name
       FROM stock_alerts sa
       JOIN consumption_items ci ON ci.id = sa.item_id
       LEFT JOIN users u ON u.id = sa.resolved_by
       WHERE sa.org_id = $1 AND sa.is_resolved = $2
       ORDER BY sa.created_at DESC`,
      [org_id, resolved === 'true']
    );

    res.json({
      success: true,
      data: result.rows
    });

  } catch (err) {
    console.error("GET STOCK ALERTS ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ✅ RESOLVE STOCK ALERT */
const resolveStockAlert = async (req, res) => {
  try {
    const { alertId } = req.params;
    const userId = req.session.user?.userId;

    const result = await pool.query(
      `UPDATE stock_alerts 
       SET is_resolved = true, 
           resolved_at = NOW(), 
           resolved_by = $1
       WHERE id = $2 AND is_resolved = false
       RETURNING *`,
      [userId, alertId]
    );

    if (result.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Alert not found or already resolved" });
    }

    res.json({
      success: true,
      message: "Alert resolved successfully",
      data: result.rows[0]
    });

  } catch (err) {
    console.error("RESOLVE STOCK ALERT ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

/* ✅ BULK UPDATE STOCK (For multiple items) */
const bulkUpdateStock = async (req, res) => {
  const client = await pool.connect();
  try {
    const { updates } = req.body;
    const org_id = req.session.user?.orgId;
    const userId = req.session.user?.userId;

    if (!org_id) {
      return res.status(401).json({ success: false, message: "Unauthorized" });
    }

    if (!updates || !Array.isArray(updates) || updates.length === 0) {
      return res.status(400).json({ success: false, message: "Updates array is required" });
    }

    await client.query('BEGIN');

    const results = [];
    for (const update of updates) {
      const { item_id, quantity, transaction_type, remarks } = update;
      
      const itemResult = await client.query(
        `SELECT * FROM consumption_items WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
        [item_id, org_id]
      );

      if (itemResult.rows.length === 0) {
        throw new Error(`Item ${item_id} not found`);
      }

      const item = itemResult.rows[0];
      const currentStock = parseFloat(item.available_stock) || 0;
      const changeQuantity = parseFloat(quantity);
      const newStock = currentStock + changeQuantity;

      if (newStock < 0) {
        throw new Error(`Insufficient stock for ${item.name}`);
      }

      await client.query(
        `UPDATE consumption_items 
         SET available_stock = $1, 
             last_stock_update = NOW(),
             updated_by = $2,
             updated_at = NOW()
         WHERE id = $3 AND org_id = $4`,
        [newStock, userId, item_id, org_id]
      );

      await client.query(
        `INSERT INTO stock_transactions 
         (item_id, transaction_type, quantity, previous_stock, new_stock, remarks, created_by, org_id)
         VALUES ($1, $2, $3, $4, $5, $6, $7, $8)`,
        [item_id, transaction_type, changeQuantity, currentStock, newStock, remarks, userId, org_id]
      );

      results.push({
        item_id,
        item_name: item.name,
        previous_stock: currentStock,
        new_stock: newStock,
        change: changeQuantity
      });
    }

    await client.query('COMMIT');

    res.json({
      success: true,
      message: `Updated ${results.length} item(s) successfully`,
      data: results
    });

  } catch (err) {
    await client.query('ROLLBACK');
    console.error("BULK UPDATE STOCK ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  } finally {
    client.release();
  }
};

/* ✅ GET STOCK MOVEMENT REPORT */
const getStockMovementReport = async (req, res) => {
  try {
    const org_id = req.session.user?.orgId;
    const { from_date, to_date, item_id } = req.query;

    let query = `
      SELECT 
        st.*,
        ci.name as item_name,
        ci.uom,
        CONCAT(u.first_name, ' ', u.last_name) as user_name
      FROM stock_transactions st
      JOIN consumption_items ci ON ci.id = st.item_id
      LEFT JOIN users u ON u.id = st.created_by
      WHERE st.org_id = $1
    `;

    const params = [org_id];
    let paramIndex = 2;

    if (from_date && to_date) {
      query += ` AND st.created_at::date BETWEEN $${paramIndex} AND $${paramIndex + 1}`;
      params.push(from_date, to_date);
      paramIndex += 2;
    }

    if (item_id) {
      query += ` AND st.item_id = $${paramIndex}`;
      params.push(item_id);
      paramIndex++;
    }

    query += ` ORDER BY st.created_at DESC`;

    const result = await pool.query(query, params);

    res.json({
      success: true,
      data: result.rows,
      summary: {
        total_transactions: result.rows.length,
        total_in: result.rows.filter(t => t.transaction_type === 'PURCHASE').reduce((sum, t) => sum + parseFloat(t.quantity), 0),
        total_out: Math.abs(result.rows.filter(t => t.transaction_type === 'CONSUMPTION').reduce((sum, t) => sum + parseFloat(t.quantity), 0))
      }
    });

  } catch (err) {
    console.error("GET STOCK MOVEMENT REPORT ERROR:", err);
    res.status(500).json({ success: false, message: err.message });
  }
};

module.exports = {
  updateStockAfterConsumption,
  addStock,
  getStockTransactions,
  getStockSummary,
  updateMinimumStock,
  getLowStockItems,
  getStockAlerts,
  resolveStockAlert,
  bulkUpdateStock,
  getStockMovementReport
};