<?php
require_once dirname(__DIR__, 2) . '/includes/db.php';
ini_set('display_errors', 0);
ob_start(); // Prevent HTML errors from breaking JSON

$action = $_GET['action'] ?? '';

try {
    if ($action === 'get_lookups') {
        // Fetch Stores
        $stores = [];
        $s_res = $conn->query("SELECT id, store_name as name FROM stores WHERE is_active = 1 ORDER BY store_name ASC");
        if ($s_res) { while ($s = $s_res->fetch_assoc()) { $stores[] = $s; } }

        // Fetch Products
        $products = [];
        $p_res = $conn->query("
            SELECT p.id, p.generic_name as name, MAX(pv.purchase_price) as est_price 
            FROM products p 
            LEFT JOIN product_variants pv ON p.id = pv.product_id 
            WHERE p.is_active = 1 
            GROUP BY p.id ORDER BY p.generic_name ASC
        ");
        if ($p_res) { while ($p = $p_res->fetch_assoc()) { $products[] = $p; } }

        // Fetch Priorities from DB ENUM
        $priorities = [];
        $enum_res = $conn->query("SHOW COLUMNS FROM purchase_requisitions LIKE 'priority'");
        if ($enum_res && $row = $enum_res->fetch_assoc()) {
            preg_match_all("/'([^']+)'/", $row['Type'], $matches);
            $priorities = $matches[1] ?? [];
        }

        ob_clean();
        header('Content-Type: application/json');
        echo json_encode(['status' => 'success', 'stores' => $stores, 'products' => $products, 'priorities' => $priorities]);
        exit;
    }

    if ($action === 'get_list') {
        $start = !empty($_GET['start_date']) ? $conn->real_escape_string($_GET['start_date']) : date('Y-m-01');
        $end   = !empty($_GET['end_date']) ? $conn->real_escape_string($_GET['end_date']) : date('Y-m-t');
        $status = $conn->real_escape_string($_GET['status'] ?? '');

        $where = ["pr.request_date >= '$start'", "pr.request_date <= '$end'"];
        if (!empty($status)) $where[] = "pr.status = '$status'";
        $where_clause = implode(" AND ", $where);

        $sql = "
            SELECT 
                pr.id, pr.requisition_no, pr.request_date, pr.priority, pr.status, pr.estimated_total_value,
                s.store_name,
                CONCAT(st.first_name, ' ', st.last_name) as requested_by_name,
                (SELECT COUNT(id) FROM purchase_requisition_items WHERE requisition_id = pr.id) as total_items
            FROM purchase_requisitions pr
            INNER JOIN stores s ON pr.store_id = s.id
            LEFT JOIN staff st ON pr.requested_by = st.staff_id
            WHERE $where_clause ORDER BY pr.id DESC
        ";

        $result = $conn->query($sql);
        if (!$result) throw new Exception("DB Error: " . $conn->error);

        $data = [];
        $kpis = ['total' => 0, 'pending' => 0, 'approved' => 0, 'value' => 0];

        while ($row = $result->fetch_assoc()) {
            $data[] = $row;
            $kpis['total']++;
            $kpis['value'] += (float)$row['estimated_total_value'];
            if ($row['status'] === 'Pending Approval') $kpis['pending']++;
            if ($row['status'] === 'Approved') $kpis['approved']++;
        }

        // Fetch Statuses from DB ENUM
        $statuses = [];
        $s_enum = $conn->query("SHOW COLUMNS FROM purchase_requisitions LIKE 'status'");
        if ($s_enum && $row = $s_enum->fetch_assoc()) {
            preg_match_all("/'([^']+)'/", $row['Type'], $matches);
            $statuses = $matches[1] ?? [];
        }

        ob_clean();
        header('Content-Type: application/json');
        echo json_encode(['status' => 'success', 'data' => $data, 'kpis' => $kpis, 'statuses' => $statuses]);
        exit;
    }

} catch (Exception $e) {
    ob_clean();
    header('Content-Type: application/json');
    echo json_encode(['status' => 'error', 'message' => $e->getMessage()]);
}
?>