


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

const getStockDashboard = async (req, res) => {
  try {
    const orgId = req.session?.user?.orgId;

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

    const query = `
      SELECT 
        COALESCE(s.material_name, s.material_type, 'Uncategorized') as product_name,
        SUM(s.net_quantity) AS total_quantity,
        COUNT(*) as entry_count,
        s.quantity_unit,
        s.warehouse_id,
        w.warehouse_name,
        -- Convert warehouse capacity to KG if UOM is tons
        CASE 
          WHEN uom.symbol ILIKE 'ton' OR uom.symbol = 't' OR uom.name ILIKE '%ton%'
            THEN w.capacity * 1000
          ELSE w.capacity
        END AS warehouse_capacity_kg,
        CASE 
          WHEN uom.symbol ILIKE 'ton' OR uom.symbol = 't' OR uom.name ILIKE '%ton%'
            THEN w.ground_stock * 1000
          ELSE w.ground_stock
        END AS ground_stock_kg,
        CASE 
          WHEN uom.symbol ILIKE 'ton' OR uom.symbol = 't' OR uom.name ILIKE '%ton%'
            THEN w.alert_stock * 1000
          ELSE w.alert_stock
        END AS alert_stock_kg,
        uom.symbol as warehouse_uom_symbol
      FROM stocks s
      LEFT JOIN warehouses w ON w.id = s.warehouse_id AND w.is_deleted = false
      LEFT JOIN unit_of_measurements uom ON w.uom_id = uom.id
      WHERE s.is_deleted = false
        AND s.org_id = $1
        AND s.net_quantity > 0
        AND COALESCE(s.material_name, s.material_type, '') != ''
      GROUP BY 
        COALESCE(s.material_name, s.material_type, 'Uncategorized'), 
        s.quantity_unit, 
        s.warehouse_id,
        w.warehouse_name,
        w.capacity,
        w.ground_stock,
        w.alert_stock,
        uom.symbol,
        uom.name
      ORDER BY total_quantity DESC
    `;

    const result = await pool.query(query, [orgId]);

    if (result.rows.length === 0) {
      return res.status(200).json({
        success: true,
        data: [],
        message: "No stock data available",
      });
    }

    const stats = result.rows.map((row) => {
      const totalQty = parseFloat(row.total_quantity) || 0;
      const capacity = parseFloat(row.warehouse_capacity_kg) || 0;
      const groundStock = parseFloat(row.ground_stock_kg) || 0;
      const alertStock = parseFloat(row.alert_stock_kg) || 0;

      const capacityPercentage =
        capacity > 0 ? Math.min(100, (totalQty / capacity) * 100) : 0;

      // FIX: Consistent status logic — alert/ground stock are MINIMUM thresholds
      // stock MUST stay ABOVE these levels; falling below = bad
      const status = (() => {
        if (alertStock > 0 && totalQty <= alertStock) return "critical";
        if (groundStock > 0 && totalQty <= groundStock) return "warning";
        if (capacity > 0 && totalQty >= capacity * 0.9) return "full";
        return "normal";
      })();

      return {
        productName: row.product_name,
        quantityKg: totalQty,
        quantityTon: (totalQty / 1000).toFixed(2),
        entryCount: parseInt(row.entry_count) || 0,
        unit: row.quantity_unit || "Tons",
        warehouseId: row.warehouse_id,
        warehouseName: row.warehouse_name || "No Warehouse Assigned",
        warehouseCapacity: capacity,
        groundStock: groundStock,
        alertStock: alertStock,
        capacityPercentage: capacityPercentage,
        status,
      };
    });

    return res.status(200).json({
      success: true,
      data: stats,
    });
  } catch (error) {
    console.error("Stock Dashboard Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to fetch stock dashboard",
      error: error.message,
    });
  }
};

// Get Stock Ledger by Product - Shows all stock entries for a specific material
const getStockLedgerByProduct = async (req, res) => {
  try {
    const { materialType, page = 1, limit = 10 } = req.query;
    const orgId = req.session?.user?.orgId;

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

    if (!materialType) {
      return res.status(400).json({
        success: false,
        message: "materialType is required",
      });
    }

    const offset = (parseInt(page) - 1) * parseInt(limit);

    const query = `
      SELECT 
        s.id,
        s.vehicle_number,
        s.po_number,
        s.supplier_name,
        s.material_name as product_name,
        s.material_type,
        s.net_quantity,
        s.gross_weight,
        s.tare_weight,
        s.received_date as date,
        s.received_by,
        s.qc_by,
        s.quantity_unit,
        s.reference_no,
        s.created_at,
        CONCAT(u.first_name, ' ', u.last_name) as received_by_name,
        CONCAT(qc.first_name, ' ', qc.last_name) as qc_by_name
      FROM stocks s
      LEFT JOIN users u ON u.id = s.received_by
      LEFT JOIN users qc ON qc.id = s.qc_by
      WHERE s.is_deleted = false 
        AND s.org_id = $1
        AND s.net_quantity > 0
        AND (s.material_name ILIKE $2 OR s.material_type ILIKE $2)
      ORDER BY s.received_date DESC
      LIMIT $3 OFFSET $4
    `;

    const values = [orgId, `%${materialType}%`, parseInt(limit), offset];
    const result = await pool.query(query, values);

    const countQuery = `
      SELECT COUNT(*) as total 
      FROM stocks s
      WHERE s.is_deleted = false 
        AND s.org_id = $1
        AND s.net_quantity > 0
        AND (s.material_name ILIKE $2 OR s.material_type ILIKE $2)
    `;
    const countResult = await pool.query(countQuery, [
      orgId,
      `%${materialType}%`,
    ]);

    const total = parseInt(countResult.rows[0].total);

    const formattedData = result.rows.map((row) => ({
      id: row.id,
      productName: row.product_name || row.material_type || "Unknown",
      vehicleNumber: row.vehicle_number || "N/A",
      vendor: row.supplier_name || "Unknown",
      date: row.date || row.created_at,
      netQuantity: parseFloat(row.net_quantity) || 0,
      grossWeight: parseFloat(row.gross_weight) || 0,
      tareWeight: parseFloat(row.tare_weight) || 0,
      receivedBy: row.received_by_name || "Unknown",
      qcBy: row.qc_by_name || "Unknown",
      poNumber: row.po_number || "N/A",
      referenceNo: row.reference_no,
      quantityUnit: row.quantity_unit || "Tons",
    }));

    res.json({
      success: true,
      data: formattedData,
      pagination: {
        currentPage: parseInt(page),
        totalPages: Math.ceil(total / parseInt(limit)),
        totalEntries: total,
        itemsPerPage: parseInt(limit),
      },
    });
  } catch (error) {
    console.error("Stock Ledger Error:", error);
    res.status(500).json({
      success: false,
      message: "Failed to fetch stock ledger",
      error: error.message,
    });
  }
};

// Get all unique material names from stocks (for dropdown/filters)
const getStockMaterials = async (req, res) => {
  try {
    const orgId = req.session?.user?.orgId;

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

    const query = `
      SELECT DISTINCT 
        COALESCE(material_name, material_type, 'Uncategorized') as material_name,
        COUNT(*) as stock_count,
        SUM(net_quantity) as total_quantity
      FROM stocks
      WHERE is_deleted = false 
        AND org_id = $1
        AND net_quantity > 0
      GROUP BY COALESCE(material_name, material_type, 'Uncategorized')
      ORDER BY material_name
    `;

    const result = await pool.query(query, [orgId]);

    res.json({
      success: true,
      data: result.rows,
    });
  } catch (error) {
    console.error("Get Stock Materials Error:", error);
    res.status(500).json({
      success: false,
      message: "Failed to fetch stock materials",
    });
  }
};

// Get stock summary by PO number
const getStockByPONumber = async (req, res) => {
  try {
    const { po_number } = req.params;
    const orgId = req.session?.user?.orgId;

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

    const query = `
      SELECT 
        s.id,
        s.vehicle_number,
        s.material_name,
        s.net_quantity,
        s.received_date,
        s.supplier_name,
        s.quantity_unit,
        s.po_number
      FROM stocks s
      WHERE s.is_deleted = false 
        AND s.org_id = $1
        AND s.po_number = $2
        AND s.net_quantity > 0
      ORDER BY s.received_date DESC
    `;

    const result = await pool.query(query, [orgId, po_number]);

    res.json({
      success: true,
      data: result.rows,
      summary: {
        total_quantity: result.rows.reduce(
          (sum, row) => sum + parseFloat(row.net_quantity),
          0
        ),
        unique_materials: [
          ...new Set(result.rows.map((r) => r.material_name)),
        ].length,
      },
    });
  } catch (error) {
    console.error("Get Stock by PO Error:", error);
    res.status(500).json({
      success: false,
      message: "Failed to fetch stock by PO number",
    });
  }
};

const getStockByWarehouse = async (req, res) => {
  try {
    const orgId = req.session?.user?.orgId;
    const { warehouseId } = req.params;

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

    if (!warehouseId) {
      return res
        .status(400)
        .json({ success: false, message: "warehouseId is required" });
    }

    const query = `
      SELECT 
        COALESCE(s.material_name, s.material_type, 'Uncategorized') as product_name,
        SUM(s.net_quantity) AS total_quantity,
        COUNT(*) as entry_count,
        s.quantity_unit,
        s.warehouse_id,
        w.warehouse_name,
        CASE 
          WHEN uom.symbol ILIKE 'ton' OR uom.symbol = 't' OR uom.name ILIKE '%ton%' 
            THEN w.capacity * 1000
          ELSE w.capacity
        END as warehouse_capacity_kg,
        CASE 
          WHEN uom.symbol ILIKE 'ton' OR uom.symbol = 't' OR uom.name ILIKE '%ton%' 
            THEN w.ground_stock * 1000
          ELSE w.ground_stock
        END as ground_stock_kg,
        CASE 
          WHEN uom.symbol ILIKE 'ton' OR uom.symbol = 't' OR uom.name ILIKE '%ton%' 
            THEN w.alert_stock * 1000
          ELSE w.alert_stock
        END as alert_stock_kg,
        uom.symbol as warehouse_uom_symbol
      FROM stocks s
      LEFT JOIN warehouses w ON w.id = s.warehouse_id AND w.is_deleted = false
      LEFT JOIN unit_of_measurements uom ON w.uom_id = uom.id
      WHERE s.is_deleted = false
        AND s.org_id = $1
        AND s.net_quantity > 0
        AND s.warehouse_id = $2
      GROUP BY 
        COALESCE(s.material_name, s.material_type, 'Uncategorized'), 
        s.quantity_unit, 
        s.warehouse_id,
        w.warehouse_name,
        w.capacity,
        w.ground_stock,
        w.alert_stock,
        uom.symbol,
        uom.name
      ORDER BY total_quantity DESC
    `;

    const result = await pool.query(query, [orgId, warehouseId]);

    const stats = result.rows.map((row) => {
      const totalQtyKg = parseFloat(row.total_quantity) || 0;
      const capacityKg = parseFloat(row.warehouse_capacity_kg) || 0;
      const groundStockKg = parseFloat(row.ground_stock_kg) || 0;
      const alertStockKg = parseFloat(row.alert_stock_kg) || 0;

      const status = (() => {
        if (alertStockKg > 0 && totalQtyKg <= alertStockKg) return "critical";
        if (groundStockKg > 0 && totalQtyKg <= groundStockKg) return "warning";
        if (capacityKg > 0 && totalQtyKg >= capacityKg * 0.9) return "full";
        return "normal";
      })();

      return {
        productName: row.product_name,
        quantityKg: totalQtyKg,
        quantityTon: (totalQtyKg / 1000).toFixed(2),
        entryCount: parseInt(row.entry_count) || 0,
        unit: row.quantity_unit || "Tons",
        warehouseId: row.warehouse_id,
        warehouseName: row.warehouse_name || "Unknown Warehouse",
        warehouseCapacity: capacityKg,
        groundStock: groundStockKg,
        alertStock: alertStockKg,
        capacityPercentage:
          capacityKg > 0
            ? Math.min(100, (totalQtyKg / capacityKg) * 100)
            : 0,
        status,
      };
    });

    const firstRow = result.rows[0];

    res.json({
      success: true,
      data: stats,
      warehouseInfo: firstRow
        ? {
            id: firstRow.warehouse_id,
            name: firstRow.warehouse_name,
            capacity: parseFloat(firstRow.warehouse_capacity_kg) || 0,
            groundStock: parseFloat(firstRow.ground_stock_kg) || 0,
            alertStock: parseFloat(firstRow.alert_stock_kg) || 0,
          }
        : null,
    });
  } catch (error) {
    console.error("Get Stock by Warehouse Error:", error);
    res.status(500).json({
      success: false,
      message: "Failed to fetch stock by warehouse",
      error: error.message,
    });
  }
};

// Get all warehouses with stock summary
const getWarehouseStockSummary = async (req, res) => {
  try {
    const orgId = req.session?.user?.orgId;

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

    const query = `
      SELECT 
        w.id as warehouse_id,
        w.warehouse_name,
        w.capacity as total_capacity,
        w.ground_stock,
        w.alert_stock,
        COALESCE(SUM(s.net_quantity), 0) as current_stock,
        COUNT(DISTINCT COALESCE(s.material_name, s.material_type)) as material_count
      FROM warehouses w
      LEFT JOIN stocks s ON s.warehouse_id = w.id 
        AND s.is_deleted = false 
        AND s.org_id = $1
        AND s.net_quantity > 0
      WHERE w.is_deleted = false AND w.org_id = $1
      GROUP BY w.id, w.warehouse_name, w.capacity, w.ground_stock, w.alert_stock
      ORDER BY w.warehouse_name
    `;

    const result = await pool.query(query, [orgId]);

    const warehouses = result.rows.map((row) => {
      const stock = parseFloat(row.current_stock) || 0;
      const capacity = parseFloat(row.total_capacity) || 0;
      const ground = parseInt(row.ground_stock) || 0;
      const alert = parseInt(row.alert_stock) || 0;

      const status = (() => {
        if (alert > 0 && stock <= alert) return "critical";
        if (ground > 0 && stock <= ground) return "warning";
        if (capacity > 0 && stock >= capacity * 0.9) return "full";
        return "normal";
      })();

      return {
        id: row.warehouse_id,
        name: row.warehouse_name,
        totalCapacity: capacity,
        currentStock: stock,
        groundStock: ground,
        alertStock: alert,
        materialCount: parseInt(row.material_count) || 0,
        capacityPercentage:
          capacity > 0 ? Math.min(100, (stock / capacity) * 100) : 0,
        status,
      };
    });

    res.json({
      success: true,
      data: warehouses,
    });
  } catch (error) {
    console.error("Get Warehouse Stock Summary Error:", error);
    res.status(500).json({
      success: false,
      message: "Failed to fetch warehouse stock summary",
      error: error.message,
    });
  }
};



const getStoreStocks = async (req, res) => {
  try {
    const orgId = req.session?.user?.orgId;
    if (!orgId) return res.status(401).json({ success: false, message: "Unauthorized" });

    const query = `
      SELECT
        COALESCE(s.material_name, s.material_type, 'Uncategorized') AS material,
        COUNT(*)                          AS item_count,
        SUM(s.net_quantity)               AS total_qty,
        s.quantity_unit                   AS unit,
        MIN(s.received_date)              AS first_received,
        MAX(s.received_date)              AS last_received,
        w.warehouse_name
      FROM stocks s
      LEFT JOIN warehouses w ON w.id = s.warehouse_id AND w.is_deleted = false
      WHERE s.is_deleted = false
        AND s.org_id = $1
        AND s.net_quantity > 0
        AND w.warehouse_name ILIKE 'store'
      GROUP BY
        COALESCE(s.material_name, s.material_type, 'Uncategorized'),
        s.quantity_unit,
        w.warehouse_name
      ORDER BY material ASC
    `;

    const result = await pool.query(query, [orgId]);
    return res.status(200).json({ success: true, data: result.rows });
  } catch (error) {
    console.error("Get Store Stocks Error:", error);
    return res.status(500).json({ success: false, message: "Failed to fetch store stocks", error: error.message });
  }
};

// ─── GET /inventory/store-ledger?material=xxx ─────────────────────────────
// Returns individual stock entries for a material in store (for vendor panel)
const getStoreLedger = async (req, res) => {
  try {
    const { material } = req.query;
    const orgId = req.session?.user?.orgId;
    if (!orgId) return res.status(401).json({ success: false, message: "Unauthorized" });
    if (!material) return res.status(400).json({ success: false, message: "material is required" });

    const query = `
      SELECT
        s.id,
        s.vehicle_number,
        s.po_number,
        s.supplier_name,
        COALESCE(s.material_name, s.material_type) AS material_name,
        s.net_quantity,
        s.gross_weight,
        s.tare_weight,
        s.quantity_unit,
        s.received_date AS date,
        s.reference_no,
        s.created_at
      FROM stocks s
      LEFT JOIN warehouses w ON w.id = s.warehouse_id AND w.is_deleted = false
      WHERE s.is_deleted = false
        AND s.org_id = $1
        AND s.net_quantity > 0
        AND w.warehouse_name ILIKE 'store'
        AND (s.material_name ILIKE $2 OR s.material_type ILIKE $2)
      ORDER BY s.received_date ASC
    `;

    const result = await pool.query(query, [orgId, material]);

    const formattedData = result.rows.map(row => ({
      id: row.id,
      vehicleNumber: row.vehicle_number || "N/A",
      poNumber: row.po_number || "N/A",
      vendor: row.supplier_name || "Unknown",
      materialName: row.material_name || "Unknown",
      netQuantity: parseFloat(row.net_quantity) || 0,
      grossWeight: parseFloat(row.gross_weight) || 0,
      tareWeight: parseFloat(row.tare_weight) || 0,
      quantityUnit: row.quantity_unit || "",
      date: row.date || row.created_at,
      referenceNo: row.reference_no,
    }));

    return res.status(200).json({ success: true, data: formattedData });
  } catch (error) {
    console.error("Get Store Ledger Error:", error);
    return res.status(500).json({ success: false, message: "Failed to fetch store ledger", error: error.message });
  }
};

const getStoreIssues = async (req, res) => {
  try {
    const { material } = req.query;
    const orgId = req.session?.user?.orgId;
    if (!orgId) return res.status(401).json({ success: false, message: "Unauthorized" });
    if (!material) return res.status(400).json({ success: false, message: "material is required" });

    const query = `
      SELECT
        ii.id,
        ii.pr_number,
        ii.issue_date,
        ii.status,
        ii.notes,
        ii.assigned_to,
        ii.assigned_to_name,
        CONCAT(ib.first_name, ' ', ib.last_name) AS issued_by_name,
        CONCAT(at.first_name, ' ', at.last_name) AS assigned_to_full_name,
        iit.item_name,
        iit.issued_quantity,
        iit.uom
      FROM inventory_issues ii
      LEFT JOIN inventory_issue_items iit ON iit.inventory_issue_id = ii.id
      LEFT JOIN users ib ON ib.id = ii.issued_by
      LEFT JOIN users at ON at.id = ii.assigned_to
      WHERE (iit.item_name ILIKE $1)
      ORDER BY ii.issue_date DESC
    `;

    const result = await pool.query(query, [`%${material}%`]);

    const formattedData = result.rows.map(row => ({
      id: row.id,
      prNumber: row.pr_number,
      issueDate: row.issue_date,
      status: row.status,
      notes: row.notes,
      assignedTo: row.assigned_to_name || row.assigned_to_full_name || "—",
      issuedBy: row.issued_by_name || "—",
      itemName: row.item_name,
      issuedQuantity: parseFloat(row.issued_quantity) || 0,
      unit: row.uom || "",  // Changed from row.unit to row.uom
    }));

    return res.status(200).json({ success: true, data: formattedData });
  } catch (error) {
    console.error("Get Store Issues Error:", error);
    return res.status(500).json({ success: false, message: "Failed to fetch store issues", error: error.message });
  }
};


module.exports = {
  getStockDashboard,
  getStockLedgerByProduct,
  getStockMaterials,
  getStockByPONumber,
  getStockByWarehouse,
  getWarehouseStockSummary,
  getStoreStocks,
  getStoreLedger,
  getStoreIssues,
};