<?php
/**
 * Cost Comparison API
 */

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');

if ($action === 'get_all') {
    $stmt = $conn->prepare("
        SELECT cc.id, cc.item_name, cc.price, cc.quantity, cc.unit, cc.created_at,
               s.name AS supplier_name
        FROM cost_comparisons cc
        LEFT JOIN suppliers s ON cc.supplier_id = s.id
        WHERE cc.purchase_manager_id = ?
        ORDER BY cc.created_at DESC
    ");
    $stmt->bind_param('i', $pm_id);
    $stmt->execute();
    $result = $stmt->get_result();

    $comparisons = [];
    while ($row = $result->fetch_assoc()) {
        $comparisons[] = $row;
    }
    echo json_encode(['success' => true, 'comparisons' => $comparisons]);
    exit;
}

if ($action === 'get_item_groups') {
    $stmt = $conn->prepare("
        SELECT
            item_name,
            MIN(price) AS best_price,
            MAX(price) AS worst_price,
            COUNT(*) AS quote_count,
            MAX(created_at) AS last_updated
        FROM cost_comparisons
        WHERE purchase_manager_id = ?
        GROUP BY item_name
        ORDER BY last_updated DESC
    ");
    $stmt->bind_param('i', $pm_id);
    $stmt->execute();
    $result = $stmt->get_result();

    $groups = [];
    while ($row = $result->fetch_assoc()) {
        $groups[] = $row;
    }
    echo json_encode(['success' => true, 'groups' => $groups]);
    exit;
}

if ($action === 'get_item_comparisons') {
    $item_name = isset($_GET['item']) ? trim($_GET['item']) : '';
    if ($item_name === '') {
        echo json_encode(['success' => false, 'message' => 'Item name required']);
        exit;
    }

    $stmt = $conn->prepare("
        SELECT cc.id, cc.item_name, cc.price, cc.quantity, cc.unit, cc.created_at,
               s.name AS supplier_name, s.id AS supplier_id
        FROM cost_comparisons cc
        LEFT JOIN suppliers s ON cc.supplier_id = s.id
        WHERE cc.purchase_manager_id = ? AND cc.item_name = ?
        ORDER BY cc.price ASC
    ");
    $stmt->bind_param('is', $pm_id, $item_name);
    $stmt->execute();
    $result = $stmt->get_result();

    $comparisons = [];
    while ($row = $result->fetch_assoc()) {
        $comparisons[] = $row;
    }

    echo json_encode([
        'success' => true,
        'item' => $item_name,
        'comparisons' => $comparisons,
        'best_price' => count($comparisons) ? $comparisons[0]['price'] : null,
        'worst_price' => count($comparisons) ? $comparisons[count($comparisons) - 1]['price'] : null
    ]);
    exit;
}

if ($action === 'create') {
    $data = is_array($payload) ? $payload : [];

    $item_name = trim($data['item_name'] ?? '');
    $supplier_id = isset($data['supplier_id']) ? (int)$data['supplier_id'] : 0;
    $price = isset($data['price']) ? (float)$data['price'] : 0;
    $quantity = isset($data['quantity']) && $data['quantity'] !== '' ? (float)$data['quantity'] : 1;
    $unit = trim($data['unit'] ?? '');
    $ingredient_id = isset($data['ingredient_id']) && $data['ingredient_id'] !== '' ? (int)$data['ingredient_id'] : null;

    if ($item_name === '' || $supplier_id <= 0 || $price <= 0) {
        echo json_encode(['success' => false, 'message' => 'Item, supplier and price are required']);
        exit;
    }

    $stmt = $conn->prepare("
        INSERT INTO cost_comparisons (purchase_manager_id, ingredient_id, item_name, supplier_id, price, quantity, unit, created_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, NOW())
    ");
    $stmt->bind_param('iisidds', $pm_id, $ingredient_id, $item_name, $supplier_id, $price, $quantity, $unit);
    $ok = $stmt->execute();

    echo json_encode([
        'success' => $ok,
        'message' => $ok ? 'Comparison quote saved' : 'Failed to save comparison'
    ]);
    exit;
}

if ($action === 'get_supplier_products_pricing') {
    $table_check = $conn->query("SHOW TABLES LIKE 'supplier_products'");
    if (!$table_check || $table_check->num_rows === 0) {
        echo json_encode(['success' => true, 'suppliers' => []]);
        exit;
    }

    $columns_result = $conn->query("SHOW COLUMNS FROM supplier_products");
    if (!$columns_result) {
        echo json_encode(['success' => false, 'message' => 'Failed to read supplier_products schema']);
        exit;
    }

    $sp_columns = [];
    while ($col = $columns_result->fetch_assoc()) {
        $sp_columns[$col['Field']] = true;
    }

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

    $priceExpr = isset($sp_columns['unit_price']) ? "sp.unit_price" : "COALESCE(i.unit_cost, 0)";
    $unitExpr = isset($sp_columns['unit']) ? "sp.unit" : "COALESCE(i.unit, '')";
    $statusExpr = isset($sp_columns['availability'])
        ? "sp.availability"
        : (isset($sp_columns['status']) ? "CASE WHEN sp.status='active' THEN 'available' ELSE 'inactive' END" : "'available'");

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

    $sql = "
        SELECT
            s.id AS supplier_id,
            s.name AS supplier_name,
            COALESCE(i.name, $productNameExpr, 'Unknown Product') AS product_name,
            COALESCE($unitExpr, i.unit, '') AS unit,
            COALESCE($priceExpr, i.unit_cost, 0) AS price,
            $statusExpr AS product_status
        FROM suppliers s
        LEFT JOIN supplier_products sp
            ON s.id = sp.supplier_id
        LEFT JOIN ingredients i
            ON sp.ingredient_id = i.id
        WHERE s.purchase_manager_id = ?
          AND s.status = 'active'
          $productFilter
        ORDER BY s.name ASC, product_name ASC
    ";

    $stmt = $conn->prepare($sql);
    if (!$stmt) {
        echo json_encode(['success' => false, 'message' => 'Prepare failed: ' . $conn->error]);
        exit;
    }
    $stmt->bind_param('i', $pm_id);
    $stmt->execute();
    $result = $stmt->get_result();

    $grouped = [];
    while ($row = $result->fetch_assoc()) {
        $sid = (int)$row['supplier_id'];
        if (!isset($grouped[$sid])) {
            $grouped[$sid] = [
                'supplier_id' => $sid,
                'supplier_name' => $row['supplier_name'],
                'products' => []
            ];
        }
        if (!empty($row['product_name']) && $row['product_name'] !== 'Unknown Product') {
            $grouped[$sid]['products'][] = [
                'name' => $row['product_name'],
                'unit' => $row['unit'],
                'price' => (float)$row['price'],
                'status' => $row['product_status']
            ];
        }
    }

    echo json_encode([
        'success' => true,
        'suppliers' => array_values($grouped)
    ]);
    exit;
}

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