<?php
/**
 * Supplier Products API
 * Manages which ingredients suppliers supply and at what prices
 */

session_start();
include 'config.php';

if (!isset($_SESSION['purchase_manager_id'])) {
    http_response_code(401);
    echo json_encode(['success' => false, 'message' => 'Unauthorized']);
    exit;
}

$pm_id = (int)$_SESSION['purchase_manager_id'];
$action = isset($_GET['action']) ? $_GET['action'] : '';
$rawInput = file_get_contents('php://input');
$payload = json_decode($rawInput, true);
if (!$action && is_array($payload)) {
    $action = $payload['action'] ?? '';
}

header('Content-Type: application/json');

// Get all products for a supplier
if ($action === 'get_supplier_products') {
    try {
        $supplier_id = isset($_GET['supplier_id']) ? (int)$_GET['supplier_id'] : 0;
        
        if ($supplier_id <= 0) {
            echo json_encode(['success' => false, 'message' => 'Supplier ID required']);
            exit;
        }
        
        // Verify supplier belongs to this PM and fetch supplier metadata
        $verify_stmt = $conn->prepare("SELECT id, name, lead_time_days FROM suppliers WHERE id = ? AND purchase_manager_id = ?");
        if (!$verify_stmt) {
            throw new Exception('Prepare failed: ' . $conn->error);
        }
        $verify_stmt->bind_param('ii', $supplier_id, $pm_id);
        $verify_stmt->execute();
        $supplier_result = $verify_stmt->get_result();
        if ($supplier_result->num_rows === 0) {
            echo json_encode(['success' => false, 'message' => 'Supplier not found']);
            exit;
        }
        $supplier_row = $supplier_result->fetch_assoc();
        $supplier_name = trim((string)($supplier_row['name'] ?? ''));
        $supplier_lead_time = (int)($supplier_row['lead_time_days'] ?? 0);

        // Check if supplier_products table exists
        $table_check = $conn->query("SHOW TABLES LIKE 'supplier_products'");
        $products = [];

        // 1) Explicit supplier_products entries
        if ($table_check && $table_check->num_rows > 0) {
            $columns_result = $conn->query("SHOW COLUMNS FROM supplier_products");
            if (!$columns_result) {
                throw new Exception('Failed to read supplier_products columns: ' . $conn->error);
            }
            $sp_columns = [];
            while ($col = $columns_result->fetch_assoc()) {
                $sp_columns[$col['Field']] = true;
            }

            $colOrDefault = static function ($colName, $defaultExpr, $alias = null) use (&$sp_columns) {
                $expr = isset($sp_columns[$colName]) ? "sp.$colName" : $defaultExpr;
                return $alias ? "$expr AS $alias" : $expr;
            };

            $availabilityExpr = isset($sp_columns['availability'])
                ? "sp.availability"
                : (isset($sp_columns['status']) ? "CASE WHEN sp.status='active' THEN 'available' ELSE 'discontinued' END" : "'available'");

            $supplierProductNameExpr = isset($sp_columns['supplier_product_name'])
                ? "sp.supplier_product_name"
                : (isset($sp_columns['product_name']) ? "sp.product_name" : "''");

            $stmt = $conn->prepare("
                SELECT 
                    " . $colOrDefault('id', 'NULL', 'id') . ",
                    " . $colOrDefault('supplier_id', (string)$supplier_id, 'supplier_id') . ",
                    " . $colOrDefault('ingredient_id', 'i.id', 'ingredient_id') . ",
                    " . $colOrDefault('supplier_sku', "''", 'supplier_sku') . ",
                    " . $supplierProductNameExpr . " AS supplier_product_name,
                    " . $colOrDefault('unit', "COALESCE(i.unit, '')", 'unit') . ",
                    " . $colOrDefault('unit_price', 'COALESCE(i.unit_cost, 0)', 'unit_price') . ",
                    " . (isset($sp_columns['minimum_order_qty']) ? 'sp.minimum_order_qty' : (isset($sp_columns['minimum_order_quantity']) ? 'sp.minimum_order_quantity' : '1')) . " AS minimum_order_qty,
                    " . $colOrDefault('lead_time_days', (string)$supplier_lead_time, 'lead_time_days') . ",
                    " . $availabilityExpr . " AS availability,
                    " . $colOrDefault('is_preferred', '0', 'is_preferred') . ",
                    " . $colOrDefault('notes', "''", 'notes') . ",
                    COALESCE(i.name, " . $supplierProductNameExpr . ") as ingredient_name,
                    i.current_stock,
                    i.minimum_stock,
                    " . $colOrDefault('created_at', 'NULL', 'created_at') . ",
                    0 AS is_read_only,
                    'supplier_products' AS source
                FROM supplier_products sp
                LEFT JOIN ingredients i ON sp.ingredient_id = i.id
                WHERE sp.supplier_id = ?
                ORDER BY COALESCE(i.name, " . $supplierProductNameExpr . ") ASC
            ");
            if (!$stmt) {
                throw new Exception('Prepare failed: ' . $conn->error);
            }
            $stmt->bind_param('i', $supplier_id);
            if (!$stmt->execute()) {
                throw new Exception('Execute failed: ' . $stmt->error);
            }

            $result = $stmt->get_result();
            while ($row = $result->fetch_assoc()) {
                $products[] = $row;
            }
        }

        // 2) Fallback: ingredients linked by supplier name text on ingredients table
        // This supports setups where ingredients.supplier is used but supplier_products is empty.
        if ($supplier_name !== '') {
            $fallback_sql = "
                SELECT
                    NULL AS id,
                    ? AS supplier_id,
                    i.id AS ingredient_id,
                    '' AS supplier_sku,
                    '' AS supplier_product_name,
                    i.unit AS unit,
                    COALESCE(i.unit_cost, 0) AS unit_price,
                    1 AS minimum_order_qty,
                    ? AS lead_time_days,
                    'available' AS availability,
                    0 AS is_preferred,
                    '' AS notes,
                    i.name AS ingredient_name,
                    i.current_stock,
                    i.minimum_stock,
                    NULL AS created_at,
                    1 AS is_read_only,
                    'ingredient_supplier_match' AS source
                FROM ingredients i
                WHERE LOWER(COALESCE(i.status, 'active')) = 'active'
                  AND TRIM(COALESCE(i.supplier, '')) <> ''
                  AND (
                        LOWER(TRIM(i.supplier)) = LOWER(TRIM(?))
                        OR FIND_IN_SET(
                            LOWER(TRIM(?)),
                            LOWER(
                                REPLACE(
                                    REPLACE(
                                        REPLACE(
                                            REPLACE(TRIM(i.supplier), ';', ','),
                                        '|', ','),
                                    '/', ','),
                                ', ', ',')
                            )
                        ) > 0
                        OR LOWER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(i.supplier), ' ', ''), '.', ''), ',', ''), '-', ''), '&', 'and'))
                           = LOWER(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(?), ' ', ''), '.', ''), ',', ''), '-', ''), '&', 'and'))
                  )
            ";

            if ($table_check && $table_check->num_rows > 0) {
                $fallback_sql .= "
                  AND NOT EXISTS (
                        SELECT 1
                        FROM supplier_products sp2
                        WHERE sp2.supplier_id = ?
                          AND sp2.ingredient_id = i.id
                  )
                ";
            }

            $fallback_sql .= " ORDER BY i.name ASC";

            $fallback_stmt = $conn->prepare($fallback_sql);
            if (!$fallback_stmt) {
                throw new Exception('Prepare failed: ' . $conn->error);
            }

            if ($table_check && $table_check->num_rows > 0) {
                $fallback_stmt->bind_param(
                    'iisssi',
                    $supplier_id,
                    $supplier_lead_time,
                    $supplier_name,
                    $supplier_name,
                    $supplier_name,
                    $supplier_id
                );
            } else {
                $fallback_stmt->bind_param(
                    'iisss',
                    $supplier_id,
                    $supplier_lead_time,
                    $supplier_name,
                    $supplier_name,
                    $supplier_name
                );
            }

            if (!$fallback_stmt->execute()) {
                throw new Exception('Execute failed: ' . $fallback_stmt->error);
            }

            $fallback_result = $fallback_stmt->get_result();
            while ($row = $fallback_result->fetch_assoc()) {
                $products[] = $row;
            }
        }

        usort($products, static function ($a, $b) {
            return strcasecmp((string)($a['ingredient_name'] ?? ''), (string)($b['ingredient_name'] ?? ''));
        });

        echo json_encode(['success' => true, 'products' => $products]);
        exit;
    } catch (Exception $e) {
        echo json_encode(['success' => false, 'message' => 'Error: ' . $e->getMessage()]);
        exit;
    }
}

// Get all available ingredients (for adding to supplier)
if ($action === 'get_available_ingredients') {
    try {
        $supplier_id = isset($_GET['supplier_id']) ? (int)$_GET['supplier_id'] : 0;
        
        if ($supplier_id <= 0) {
            echo json_encode(['success' => false, 'message' => 'Supplier ID required']);
            exit;
        }
        
        // Check if table exists
        $table_check = $conn->query("SHOW TABLES LIKE 'supplier_products'");
        
        $stmt = $conn->prepare("
            SELECT i.id, i.name, i.unit, i.current_stock, i.minimum_stock
            FROM ingredients i
            " . ($table_check && $table_check->num_rows > 0 ? "
            LEFT JOIN supplier_products sp ON i.id = sp.ingredient_id AND sp.supplier_id = ?
            WHERE sp.id IS NULL AND i.status = 'active'
            " : "
            WHERE i.status = 'active'
            ") . "
            ORDER BY i.name ASC
        ");
        
        if (!$stmt) {
            throw new Exception('Prepare failed: ' . $conn->error);
        }
        
        if ($table_check && $table_check->num_rows > 0) {
            $stmt->bind_param('i', $supplier_id);
        }
        
        if (!$stmt->execute()) {
            throw new Exception('Execute failed: ' . $stmt->error);
        }
        
        $result = $stmt->get_result();
        
        $ingredients = [];
        while ($row = $result->fetch_assoc()) {
            $ingredients[] = $row;
        }
        
        echo json_encode(['success' => true, 'ingredients' => $ingredients]);
        exit;
    } catch (Exception $e) {
        echo json_encode(['success' => false, 'message' => 'Error: ' . $e->getMessage()]);
        exit;
    }
}

// Add product to supplier
if ($action === 'add_product') {
    try {
        $data = is_array($payload) ? $payload : [];
        $supplier_id = isset($data['supplier_id']) ? (int)$data['supplier_id'] : 0;
        $ingredient_id = isset($data['ingredient_id']) ? (int)$data['ingredient_id'] : 0;
        $supplier_sku = trim($data['supplier_sku'] ?? '');
        $supplier_product_name = trim($data['supplier_product_name'] ?? '');
        $unit = trim($data['unit'] ?? '');
        $unit_price = isset($data['unit_price']) ? (float)$data['unit_price'] : 0;
        $minimum_order_qty = isset($data['minimum_order_qty']) ? (float)$data['minimum_order_qty'] : 1;
        $lead_time_days = isset($data['lead_time_days']) ? (int)$data['lead_time_days'] : 0;
        $availability = in_array(($data['availability'] ?? 'available'), ['available', 'discontinued', 'seasonal'], true) ? $data['availability'] : 'available';
        $is_preferred = isset($data['is_preferred']) ? (bool)$data['is_preferred'] : false;
        $notes = trim($data['notes'] ?? '');
        
        if ($supplier_id <= 0 || $unit_price <= 0) {
            echo json_encode(['success' => false, 'message' => 'Supplier and unit price are required']);
            exit;
        }
        
        // Verify supplier belongs to this PM
        $verify_stmt = $conn->prepare("SELECT id FROM suppliers WHERE id = ? AND purchase_manager_id = ?");
        if (!$verify_stmt) {
            throw new Exception('Prepare failed: ' . $conn->error);
        }
        $verify_stmt->bind_param('ii', $supplier_id, $pm_id);
        $verify_stmt->execute();
        if ($verify_stmt->get_result()->num_rows === 0) {
            echo json_encode(['success' => false, 'message' => 'Supplier not found']);
            exit;
        }
        
        // Get ingredient unit if not provided
        if ($unit === '') {
            $ing_stmt = $conn->prepare("SELECT unit FROM ingredients WHERE id = ?");
            if (!$ing_stmt) {
                throw new Exception('Prepare failed: ' . $conn->error);
            }
            $ing_stmt->bind_param('i', $ingredient_id);
            $ing_stmt->execute();
            $ing_result = $ing_stmt->get_result();
            if ($ing_result->num_rows > 0) {
                $ing_row = $ing_result->fetch_assoc();
                $unit = $ing_row['unit'];
            }
        }
        
        $columns_result = $conn->query("SHOW COLUMNS FROM supplier_products");
        if (!$columns_result) {
            throw new Exception('Failed to read supplier_products columns: ' . $conn->error);
        }
        $sp_columns = [];
        while ($col = $columns_result->fetch_assoc()) {
            $sp_columns[$col['Field']] = true;
        }

        // Use explicit name when provided; fallback to ingredient name.
        $product_name_value = $supplier_product_name;
        if ($product_name_value === '') {
            if ($ingredient_id > 0) {
                $name_stmt = $conn->prepare("SELECT name FROM ingredients WHERE id = ? LIMIT 1");
                if ($name_stmt) {
                    $name_stmt->bind_param('i', $ingredient_id);
                    if ($name_stmt->execute()) {
                        $name_result = $name_stmt->get_result();
                        if ($name_result && $name_result->num_rows > 0) {
                            $product_name_value = (string)($name_result->fetch_assoc()['name'] ?? '');
                        }
                    }
                    $name_stmt->close();
                }
            }
        }
        if ($ingredient_id <= 0 && $product_name_value === '') {
            echo json_encode(['success' => false, 'message' => 'Select an ingredient or enter a product name']);
            exit;
        }

        $insert_cols = ['supplier_id'];
        $types = 'i';
        $params = [$supplier_id];

        if ($ingredient_id > 0) {
            $insert_cols[] = 'ingredient_id';
            $types .= 'i';
            $params[] = $ingredient_id;
        }

        if (isset($sp_columns['supplier_sku'])) {
            $insert_cols[] = 'supplier_sku';
            $types .= 's';
            $params[] = $supplier_sku;
        }

        if (isset($sp_columns['supplier_product_name'])) {
            $insert_cols[] = 'supplier_product_name';
            $types .= 's';
            $params[] = $product_name_value;
        } elseif (isset($sp_columns['product_name'])) {
            $insert_cols[] = 'product_name';
            $types .= 's';
            $params[] = $product_name_value;
        }

        if (isset($sp_columns['unit'])) {
            $insert_cols[] = 'unit';
            $types .= 's';
            $params[] = $unit;
        }

        if (isset($sp_columns['unit_price'])) {
            $insert_cols[] = 'unit_price';
            $types .= 'd';
            $params[] = $unit_price;
        }

        if (isset($sp_columns['minimum_order_qty'])) {
            $insert_cols[] = 'minimum_order_qty';
            $types .= 'd';
            $params[] = $minimum_order_qty;
        } elseif (isset($sp_columns['minimum_order_quantity'])) {
            $insert_cols[] = 'minimum_order_quantity';
            $types .= 'd';
            $params[] = $minimum_order_qty;
        }

        if (isset($sp_columns['lead_time_days'])) {
            $insert_cols[] = 'lead_time_days';
            $types .= 'i';
            $params[] = $lead_time_days;
        }

        if (isset($sp_columns['availability'])) {
            $insert_cols[] = 'availability';
            $types .= 's';
            $params[] = $availability;
        } elseif (isset($sp_columns['status'])) {
            $insert_cols[] = 'status';
            $types .= 's';
            $params[] = ($availability === 'available' ? 'active' : 'inactive');
        }

        if (isset($sp_columns['is_preferred'])) {
            $insert_cols[] = 'is_preferred';
            $types .= 'i';
            $params[] = $is_preferred ? 1 : 0;
        }

        if (isset($sp_columns['notes'])) {
            $insert_cols[] = 'notes';
            $types .= 's';
            $params[] = $notes;
        }

        $placeholders = implode(', ', array_fill(0, count($insert_cols), '?'));
        $stmt = $conn->prepare("
            INSERT INTO supplier_products (" . implode(', ', $insert_cols) . ")
            VALUES ($placeholders)
        ");
        if (!$stmt) {
            throw new Exception('Prepare failed: ' . $conn->error);
        }

        $bindValues = [];
        $bindValues[] = &$types;
        foreach ($params as $idx => $value) {
            $bindValues[] = &$params[$idx];
        }
        if (!call_user_func_array([$stmt, 'bind_param'], $bindValues)) {
            throw new Exception('Bind failed: ' . $stmt->error);
        }
        
        $ok = $stmt->execute();
        if (!$ok) {
            throw new Exception('Execute failed: ' . $stmt->error);
        }
        
        echo json_encode([
            'success' => true,
            'message' => 'Product added to supplier',
            'product_id' => $stmt->insert_id
        ]);
        exit;
    } catch (Exception $e) {
        echo json_encode(['success' => false, 'message' => 'Error: ' . $e->getMessage()]);
        exit;
    }
}

// Update product pricing and details
if ($action === 'update_product') {
    try {
        $data = is_array($payload) ? $payload : [];
        $id = isset($data['id']) ? (int)$data['id'] : 0;
        $supplier_id = isset($data['supplier_id']) ? (int)$data['supplier_id'] : 0;
        $supplier_sku = trim($data['supplier_sku'] ?? '');
        $supplier_product_name = trim($data['supplier_product_name'] ?? '');
        $unit_price = isset($data['unit_price']) ? (float)$data['unit_price'] : 0;
        $minimum_order_qty = isset($data['minimum_order_qty']) ? (float)$data['minimum_order_qty'] : 1;
        $lead_time_days = isset($data['lead_time_days']) ? (int)$data['lead_time_days'] : 0;
        $availability = in_array(($data['availability'] ?? 'available'), ['available', 'discontinued', 'seasonal'], true) ? $data['availability'] : 'available';
        $is_preferred = isset($data['is_preferred']) ? (bool)$data['is_preferred'] : false;
        $notes = trim($data['notes'] ?? '');
        
        // Verify ownership
        $verify_stmt = $conn->prepare("
            SELECT sp.id FROM supplier_products sp
            JOIN suppliers s ON sp.supplier_id = s.id
            WHERE sp.id = ? AND s.purchase_manager_id = ?
        ");
        if (!$verify_stmt) {
            throw new Exception('Prepare failed: ' . $conn->error);
        }
        $verify_stmt->bind_param('ii', $id, $pm_id);
        $verify_stmt->execute();
        if ($verify_stmt->get_result()->num_rows === 0) {
            echo json_encode(['success' => false, 'message' => 'Product not found']);
            exit;
        }
        
        $columns_result = $conn->query("SHOW COLUMNS FROM supplier_products");
        if (!$columns_result) {
            throw new Exception('Failed to read supplier_products columns: ' . $conn->error);
        }
        $sp_columns = [];
        while ($col = $columns_result->fetch_assoc()) {
            $sp_columns[$col['Field']] = true;
        }

        $updates = [];
        $types = '';
        $params = [];

        if (isset($sp_columns['supplier_sku'])) {
            $updates[] = 'supplier_sku = ?';
            $types .= 's';
            $params[] = $supplier_sku;
        }

        if (isset($sp_columns['supplier_product_name'])) {
            $updates[] = 'supplier_product_name = ?';
            $types .= 's';
            $params[] = $supplier_product_name;
        } elseif (isset($sp_columns['product_name'])) {
            $updates[] = 'product_name = ?';
            $types .= 's';
            $params[] = $supplier_product_name;
        }

        if (isset($sp_columns['unit_price'])) {
            $updates[] = 'unit_price = ?';
            $types .= 'd';
            $params[] = $unit_price;
        }

        if (isset($sp_columns['minimum_order_qty'])) {
            $updates[] = 'minimum_order_qty = ?';
            $types .= 'd';
            $params[] = $minimum_order_qty;
        } elseif (isset($sp_columns['minimum_order_quantity'])) {
            $updates[] = 'minimum_order_quantity = ?';
            $types .= 'd';
            $params[] = $minimum_order_qty;
        }

        if (isset($sp_columns['lead_time_days'])) {
            $updates[] = 'lead_time_days = ?';
            $types .= 'i';
            $params[] = $lead_time_days;
        }

        if (isset($sp_columns['availability'])) {
            $updates[] = 'availability = ?';
            $types .= 's';
            $params[] = $availability;
        } elseif (isset($sp_columns['status'])) {
            $updates[] = 'status = ?';
            $types .= 's';
            $params[] = ($availability === 'available' ? 'active' : 'inactive');
        }

        if (isset($sp_columns['is_preferred'])) {
            $updates[] = 'is_preferred = ?';
            $types .= 'i';
            $params[] = $is_preferred ? 1 : 0;
        }

        if (isset($sp_columns['notes'])) {
            $updates[] = 'notes = ?';
            $types .= 's';
            $params[] = $notes;
        }

        if (empty($updates)) {
            echo json_encode(['success' => false, 'message' => 'No updatable columns found in supplier_products']);
            exit;
        }

        $stmt = $conn->prepare("
            UPDATE supplier_products
            SET " . implode(', ', $updates) . "
            WHERE id = ?
        ");
        if (!$stmt) {
            throw new Exception('Prepare failed: ' . $conn->error);
        }

        $types .= 'i';
        $params[] = $id;

        $bindValues = [];
        $bindValues[] = &$types;
        foreach ($params as $idx => $value) {
            $bindValues[] = &$params[$idx];
        }
        if (!call_user_func_array([$stmt, 'bind_param'], $bindValues)) {
            throw new Exception('Bind failed: ' . $stmt->error);
        }
        
        if (!$stmt->execute()) {
            throw new Exception('Execute failed: ' . $stmt->error);
        }
        
        echo json_encode([
            'success' => true,
            'message' => 'Product updated'
        ]);
        exit;
    } catch (Exception $e) {
        echo json_encode(['success' => false, 'message' => 'Error: ' . $e->getMessage()]);
        exit;
    }
}

// Remove product from supplier
if ($action === 'delete_product') {
    try {
        $data = is_array($payload) ? $payload : [];
        $id = isset($data['id']) ? (int)$data['id'] : 0;
        
        // Verify ownership
        $verify_stmt = $conn->prepare("
            SELECT sp.id FROM supplier_products sp
            JOIN suppliers s ON sp.supplier_id = s.id
            WHERE sp.id = ? AND s.purchase_manager_id = ?
        ");
        if (!$verify_stmt) {
            throw new Exception('Prepare failed: ' . $conn->error);
        }
        $verify_stmt->bind_param('ii', $id, $pm_id);
        $verify_stmt->execute();
        if ($verify_stmt->get_result()->num_rows === 0) {
            echo json_encode(['success' => false, 'message' => 'Product not found']);
            exit;
        }
        
        $stmt = $conn->prepare("DELETE FROM supplier_products WHERE id = ?");
        if (!$stmt) {
            throw new Exception('Prepare failed: ' . $conn->error);
        }
        $stmt->bind_param('i', $id);
        if (!$stmt->execute()) {
            throw new Exception('Execute failed: ' . $stmt->error);
        }
        
        echo json_encode([
            'success' => true,
            'message' => 'Product removed'
        ]);
        exit;
    } catch (Exception $e) {
        echo json_encode(['success' => false, 'message' => 'Error: ' . $e->getMessage()]);
        exit;
    }
}

// Get low stock ingredients that need purchasing
if ($action === 'get_low_stock_ingredients') {
    try {
        // Check if table exists
        $table_check = $conn->query("SHOW TABLES LIKE 'supplier_products'");
        
        if (!$table_check || $table_check->num_rows === 0) {
            echo json_encode(['success' => true, 'ingredients' => [], 'warning' => 'Product table not yet created']);
            exit;
        }
        
        $columns_result = $conn->query("SHOW COLUMNS FROM supplier_products");
        if (!$columns_result) {
            throw new Exception('Failed to read supplier_products columns: ' . $conn->error);
        }
        $sp_columns = [];
        while ($col = $columns_result->fetch_assoc()) {
            $sp_columns[$col['Field']] = true;
        }

        $spActiveFilter = isset($sp_columns['availability'])
            ? "sp.availability = 'available'"
            : (isset($sp_columns['status']) ? "sp.status = 'active'" : "1=1");

        $stmt = $conn->prepare("
            SELECT 
                i.id,
                i.name,
                i.unit,
                i.current_stock,
                i.minimum_stock,
                i.unit_cost,
                (i.minimum_stock - i.current_stock) as quantity_needed,
                COUNT(DISTINCT s.id) as supplier_count
            FROM ingredients i
            LEFT JOIN supplier_products sp ON i.id = sp.ingredient_id
                AND $spActiveFilter
            LEFT JOIN suppliers s ON sp.supplier_id = s.id
                AND s.status = 'active'
                AND s.purchase_manager_id = ?
            WHERE i.status = 'active'
                AND i.current_stock <= i.minimum_stock
            GROUP BY i.id, i.name, i.unit, i.current_stock, i.minimum_stock, i.unit_cost
            ORDER BY (i.minimum_stock - i.current_stock) DESC
        ");
        if (!$stmt) {
            throw new Exception('Prepare failed: ' . $conn->error);
        }
        $stmt->bind_param('i', $pm_id);
        if (!$stmt->execute()) {
            throw new Exception('Execute failed: ' . $stmt->error);
        }
        $result = $stmt->get_result();
        
        $ingredients = [];
        while ($row = $result->fetch_assoc()) {
            $ingredients[] = $row;
        }
        
        echo json_encode(['success' => true, 'ingredients' => $ingredients]);
        exit;
    } catch (Exception $e) {
        echo json_encode(['success' => false, 'message' => 'Error: ' . $e->getMessage()]);
        exit;
    }
}

// Get supplier products for a specific ingredient
if ($action === 'get_supplier_products_for_ingredient') {
    try {
        $ingredient_id = isset($_GET['ingredient_id']) ? (int)$_GET['ingredient_id'] : 0;
        
        if ($ingredient_id <= 0) {
            echo json_encode(['success' => false, 'message' => 'Ingredient ID required']);
            exit;
        }
        
        // Fetch ingredient details for fallback matching by ingredients.supplier text.
        $ingredient_stmt = $conn->prepare("
            SELECT id, name, unit, unit_cost, supplier
            FROM ingredients
            WHERE id = ? AND LOWER(COALESCE(status, 'active')) = 'active'
            LIMIT 1
        ");
        if (!$ingredient_stmt) {
            throw new Exception('Prepare failed: ' . $conn->error);
        }
        $ingredient_stmt->bind_param('i', $ingredient_id);
        if (!$ingredient_stmt->execute()) {
            throw new Exception('Execute failed: ' . $ingredient_stmt->error);
        }
        $ingredient_result = $ingredient_stmt->get_result();
        if ($ingredient_result->num_rows === 0) {
            echo json_encode(['success' => true, 'products' => []]);
            exit;
        }
        $ingredient = $ingredient_result->fetch_assoc();

        // 1) Explicit supplier_products rows (when table exists).
        $products = [];
        $seen_supplier_ids = [];

        $table_check = $conn->query("SHOW TABLES LIKE 'supplier_products'");
        if ($table_check && $table_check->num_rows > 0) {
            $columns_result = $conn->query("SHOW COLUMNS FROM supplier_products");
            if (!$columns_result) {
                throw new Exception('Failed to read supplier_products columns: ' . $conn->error);
            }
            $sp_columns = [];
            while ($col = $columns_result->fetch_assoc()) {
                $sp_columns[$col['Field']] = true;
            }

            $colOrDefault = static function ($colName, $defaultExpr, $alias = null) use (&$sp_columns) {
                $expr = isset($sp_columns[$colName]) ? "sp.$colName" : $defaultExpr;
                return $alias ? "$expr AS $alias" : $expr;
            };

            $availabilityExpr = isset($sp_columns['availability'])
                ? "sp.availability"
                : (isset($sp_columns['status']) ? "CASE WHEN sp.status='active' THEN 'available' ELSE 'discontinued' END" : "'available'");

            $availabilityFilter = isset($sp_columns['availability'])
                ? "sp.availability = 'available' AND "
                : (isset($sp_columns['status']) ? "sp.status = 'active' AND " : "");

            $stmt = $conn->prepare("
                SELECT 
                    " . $colOrDefault('id', 'NULL', 'id') . ",
                    " . $colOrDefault('supplier_id', 's.id', 'supplier_id') . ",
                    " . $colOrDefault('ingredient_id', (string)$ingredient_id, 'ingredient_id') . ",
                    " . $colOrDefault('supplier_sku', "''", 'supplier_sku') . ",
                    " . $colOrDefault('supplier_product_name', (isset($sp_columns['product_name']) ? 'sp.product_name' : "''"), 'supplier_product_name') . ",
                    " . $colOrDefault('unit', "COALESCE(i.unit, '')", 'unit') . ",
                    " . $colOrDefault('unit_price', 'COALESCE(i.unit_cost, 0)', 'unit_price') . ",
                    " . (isset($sp_columns['minimum_order_qty']) ? 'sp.minimum_order_qty' : (isset($sp_columns['minimum_order_quantity']) ? 'sp.minimum_order_quantity' : '1')) . " AS minimum_order_qty,
                    " . $colOrDefault('lead_time_days', 'COALESCE(s.lead_time_days, 0)', 'lead_time_days') . ",
                    " . $availabilityExpr . " AS availability,
                    " . $colOrDefault('is_preferred', '0', 'is_preferred') . ",
                    s.name as supplier_name,
                    s.rating as supplier_rating,
                    s.lead_time_days as supplier_lead_time,
                    0 as is_read_only,
                    'supplier_products' as source
                FROM supplier_products sp
                JOIN suppliers s ON sp.supplier_id = s.id
                LEFT JOIN ingredients i ON sp.ingredient_id = i.id
                WHERE sp.ingredient_id = ? AND " . $availabilityFilter . " s.status = 'active' AND s.purchase_manager_id = ?
                ORDER BY is_preferred DESC, unit_price ASC
            ");
            if (!$stmt) {
                throw new Exception('Prepare failed: ' . $conn->error);
            }
            $stmt->bind_param('ii', $ingredient_id, $pm_id);
            if (!$stmt->execute()) {
                throw new Exception('Execute failed: ' . $stmt->error);
            }
            $result = $stmt->get_result();
            while ($row = $result->fetch_assoc()) {
                $products[] = $row;
                $seen_supplier_ids[(int)$row['supplier_id']] = true;
            }
        }

        // 2) Fallback rows from ingredients.supplier text for active PM suppliers.
        $supplier_text = trim((string)($ingredient['supplier'] ?? ''));
        if ($supplier_text !== '') {
            $normalize = static function ($value) {
                $value = trim((string)$value);
                if ($value === '') return '';
                $value = strtolower($value);
                $value = str_replace('&', 'and', $value);
                $value = preg_replace('/[.\-,]/', ' ', $value);
                $value = preg_replace('/\s+/', ' ', $value);
                return trim($value);
            };

            $tokens_raw = preg_split('/[,\;|\/]+/', $supplier_text);
            $tokens = [];
            foreach ($tokens_raw as $token) {
                $n = $normalize($token);
                if ($n !== '') {
                    $tokens[$n] = true;
                }
            }

            if (!empty($tokens)) {
                $sup_stmt = $conn->prepare("
                    SELECT id, name, rating, lead_time_days
                    FROM suppliers
                    WHERE purchase_manager_id = ? AND status = 'active'
                ");
                if (!$sup_stmt) {
                    throw new Exception('Prepare failed: ' . $conn->error);
                }
                $sup_stmt->bind_param('i', $pm_id);
                if (!$sup_stmt->execute()) {
                    throw new Exception('Execute failed: ' . $sup_stmt->error);
                }
                $sup_result = $sup_stmt->get_result();

                while ($sup = $sup_result->fetch_assoc()) {
                    $sid = (int)$sup['id'];
                    if (isset($seen_supplier_ids[$sid])) {
                        continue;
                    }
                    $supplier_name_normalized = $normalize($sup['name'] ?? '');
                    if ($supplier_name_normalized === '' || !isset($tokens[$supplier_name_normalized])) {
                        continue;
                    }

                    $products[] = [
                        'id' => null,
                        'supplier_id' => $sid,
                        'ingredient_id' => (int)$ingredient['id'],
                        'supplier_sku' => '',
                        'supplier_product_name' => (string)$ingredient['name'],
                        'unit' => (string)($ingredient['unit'] ?? ''),
                        'unit_price' => (float)($ingredient['unit_cost'] ?? 0),
                        'minimum_order_qty' => 1,
                        'lead_time_days' => (int)($sup['lead_time_days'] ?? 0),
                        'availability' => 'available',
                        'is_preferred' => 0,
                        'supplier_name' => (string)$sup['name'],
                        'supplier_rating' => $sup['rating'],
                        'supplier_lead_time' => (int)($sup['lead_time_days'] ?? 0),
                        'is_read_only' => 1,
                        'source' => 'ingredient_supplier_match'
                    ];
                }
            }
        }

        usort($products, static function ($a, $b) {
            return (float)($a['unit_price'] ?? 0) <=> (float)($b['unit_price'] ?? 0);
        });

        echo json_encode(['success' => true, 'products' => $products]);
        exit;
    } catch (Exception $e) {
        echo json_encode(['success' => false, 'message' => 'Error: ' . $e->getMessage()]);
        exit;
    }
}

echo json_encode(['success' => false, 'message' => 'Invalid action']);
