<?php
error_reporting(0);
ini_set('display_errors', '0');
require_once __DIR__ . '/../config/auth.php';

if (!function_exists('is_admin')) {
    function is_admin(array $user): bool { return (int)$user['user_type_id'] === 1; }
}
if (!function_exists('allowed_clients')) {
    function allowed_clients(array $user): ?array {
        if (is_admin($user)) return null;
        $ids = array_values(array_filter(array_map('intval', explode(',', $user['clients_multi'] ?? ''))));
        return $ids ?: [-1];
    }
}
if (!function_exists('client_where')) {
    function client_where(array $user, string $col = 'clients_id'): array {
        $ids = allowed_clients($user);
        if ($ids === null) return ['1=1', []];
        $ph = implode(',', array_fill(0, count($ids), '?'));
        return ["$col IN ($ph)", $ids];
    }
}

$user = require_auth();

// Build client filter based on user's access
$clientIds = array_filter(array_map('intval', explode(',', $user['clients_multi'] ?? '')));

try {
$db = db();

// Helper: count with optional WHERE
function count_table(PDO $db, string $table, string $where = '1', array $params = []): int {
    $stmt = $db->prepare("SELECT COUNT(*) FROM `$table` WHERE $where");
    $stmt->execute($params);
    return (int)$stmt->fetchColumn();
}

[$cw, $cp] = client_where($user, 'record_id');
[$bcw, $bcp] = client_where($user, 'clients_id');
[$ecw, $ecp] = client_where($user, 'clients_id');

// Build scoped count helper
function count_scoped(PDO $db, string $table, string $where, array $params): int {
    $stmt = $db->prepare("SELECT COUNT(*) FROM `$table` WHERE $where");
    $stmt->execute($params);
    return (int)$stmt->fetchColumn();
}

$stats = [
    'clients'     => count_scoped($db, 'clients',          $cw,  $cp),
    'employees'   => count_scoped($db, 'client_employees', $ecw !== '1=1' ? $ecw : '1=1', $ecw !== '1=1' ? $ecp : []),
    'bookings'    => count_scoped($db, 'bookings',         $bcw !== '1=1' ? $bcw : '1=1', $bcw !== '1=1' ? $bcp : []),
    'assessments' => count_table($db, 'assessments'),
    'tests'       => count_table($db, 'tests'),
    'users'       => count_table($db, 'safesure_users'),
    'certificates'=> count_table($db, 'certificates'),
];

// Booking statuses
$statusStmt = $db->query('SELECT status, COUNT(*) as cnt FROM bookings GROUP BY status');
$booking_statuses = [];
while ($row = $statusStmt->fetch()) {
    $booking_statuses[$row['status']] = (int)$row['cnt'];
}

// Recent bookings (last 10)
[$rb_cw, $rb_cp] = client_where($user, 'b.clients_id');
$rb_stmt = $db->prepare(
    "SELECT b.record_id, b.date_booked, b.status, b.booking_number,
            c.clients_name, u.safesure_users_name as assessor
     FROM bookings b
     LEFT JOIN clients c ON c.record_id = b.clients_id
     LEFT JOIN safesure_users u ON u.record_id = b.safesure_users_id
     WHERE ($rb_cw)
     ORDER BY b.record_id DESC LIMIT 10"
);
$rb_stmt->execute($rb_cp);
$recent = $rb_stmt->fetchAll();

json_success([
    'stats'            => $stats,
    'booking_statuses' => $booking_statuses,
    'recent_bookings'  => $recent,
]);
} catch (Throwable $e) {
    json_error('DB error: ' . $e->getMessage() . ' in ' . basename($e->getFile()) . ':' . $e->getLine(), 500);
}