<?php
require __DIR__ . '/bootstrap.php';

use App\Core\Auth;
use App\Core\Database;
use App\Core\Response;

Auth::requireRole(['super_admin','event_manager','walk_leader']);
$pdo = Database::connection();

function pyrk_report_count(PDO $pdo, string $sql, mixed $default = 0): mixed
{
    try {
        $value = $pdo->query($sql)->fetchColumn();
        return $value === false ? $default : $value;
    } catch (Throwable) {
        return $default;
    }
}

function pyrk_report_rows(PDO $pdo, string $sql): array
{
    try {
        return $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable) {
        return [];
    }
}

/*
 * Active dashboard walks are current/upcoming records that still belong to the
 * operational schedule. Historical, completed and archived walks are excluded.
 */
$activeWalks = pyrk_report_rows($pdo, '
    SELECT
        w.id,
        w.title,
        w.walk_date,
        w.start_time,
        w.expected_finish_time,
        w.status,
        w.access_type,
        w.waiver_required,
        COALESCE(w.capacity, 0) AS capacity,
        r.route_name,
        r.total_km,
        (SELECT COUNT(*)
           FROM walk_registrations wr
          WHERE wr.walk_id = w.id
            AND wr.registration_status IN ("registered", "approved")) AS registered_count,
        (SELECT COUNT(*)
           FROM walk_registrations wr
          WHERE wr.walk_id = w.id
            AND wr.registration_status = "waitlisted") AS waitlisted_count,
        (SELECT COUNT(*)
           FROM walk_registrations wr
          WHERE wr.walk_id = w.id
            AND wr.registration_status = "cancelled") AS cancelled_count,
        (SELECT COUNT(DISTINCT a.user_id)
           FROM attendance a
          WHERE a.walk_id = w.id
            AND (a.check_in_time IS NOT NULL OR a.status IN ("checked_in", "started", "finished", "partially_completed", "manual_verified"))) AS checked_in_count,
        (SELECT COUNT(DISTINCT a.user_id)
           FROM attendance a
          WHERE a.walk_id = w.id
            AND (a.finish_time IS NOT NULL OR a.status IN ("finished", "partially_completed", "manual_verified"))) AS finished_count,
        (SELECT COUNT(DISTINCT ws.user_id)
           FROM waiver_signatures ws
          WHERE ws.walk_id = w.id) AS waiver_signed_count
    FROM walks w
    JOIN routes r ON r.id = w.route_id
    WHERE w.walk_date >= CURDATE()
      AND LOWER(TRIM(COALESCE(w.status, ""))) IN ("open", "draft", "closed")
    ORDER BY w.walk_date ASC, w.start_time ASC, w.id ASC
');

$activeSummary = [
    'walks' => count($activeWalks),
    'registrations' => 0,
    'capacity' => 0,
    'available_places' => 0,
    'waivers_signed' => 0,
    'checked_in' => 0,
    'finished' => 0,
    'waitlisted' => 0,
];

foreach ($activeWalks as &$walk) {
    $registered = max(0, (int)($walk['registered_count'] ?? 0));
    $capacity = max(0, (int)($walk['capacity'] ?? 0));
    $available = $capacity > 0 ? max(0, $capacity - $registered) : 0;

    $walk['available_places'] = $available;
    $walk['occupancy_percent'] = $capacity > 0
        ? min(100, round(($registered / $capacity) * 100, 1))
        : 0;

    $activeSummary['registrations'] += $registered;
    $activeSummary['capacity'] += $capacity;
    $activeSummary['available_places'] += $available;
    $activeSummary['waivers_signed'] += max(0, (int)($walk['waiver_signed_count'] ?? 0));
    $activeSummary['checked_in'] += max(0, (int)($walk['checked_in_count'] ?? 0));
    $activeSummary['finished'] += max(0, (int)($walk['finished_count'] ?? 0));
    $activeSummary['waitlisted'] += max(0, (int)($walk['waitlisted_count'] ?? 0));
}
unset($walk);

$stats = [
    'members' => (int)pyrk_report_count($pdo, 'SELECT COUNT(*) FROM users u JOIN roles r ON r.id = u.role_id WHERE r.name = "member"'),
    'active_routes' => (int)pyrk_report_count($pdo, 'SELECT COUNT(*) FROM routes WHERE status = "active"'),
    'upcoming_walks' => (int)$activeSummary['walks'],
    // Deliberately scoped to active walks; this is no longer an all-time cumulative number.
    'registrations' => (int)$activeSummary['registrations'],
    'finished_attendance' => (int)pyrk_report_count($pdo, 'SELECT COUNT(*) FROM attendance WHERE status IN ("finished","manual_verified","partially_completed")'),
    'club_kilometers' => (float)pyrk_report_count($pdo, 'SELECT COALESCE(SUM(total_kilometers),0) FROM member_profiles', 0),
    'pending_photos' => (int)pyrk_report_count($pdo, 'SELECT COUNT(*) FROM walk_photos WHERE status = "pending"'),
    'votes' => (int)pyrk_report_count($pdo, 'SELECT COUNT(*) FROM photo_votes'),
    'today_checkins' => (int)pyrk_report_count($pdo, 'SELECT COUNT(*) FROM attendance WHERE DATE(check_in_time) = CURDATE()'),
];

$topWalkers = pyrk_report_rows($pdo, 'SELECT u.id AS user_id, u.full_name, u.profile_photo_path, COALESCE(mp.total_walks,0) AS total_walks, COALESCE(mp.total_kilometers,0) AS total_kilometers, COALESCE(mp.loyalty_points,0) AS loyalty_points FROM member_profiles mp JOIN users u ON u.id = mp.user_id ORDER BY mp.total_kilometers DESC, mp.total_walks DESC LIMIT 8');

$pendingPhotos = pyrk_report_rows($pdo, 'SELECT wp.id, wp.title, wp.uploaded_at, u.full_name, w.title AS walk_title FROM walk_photos wp JOIN users u ON u.id = wp.user_id JOIN walks w ON w.id = wp.walk_id WHERE wp.status = "pending" ORDER BY wp.uploaded_at DESC LIMIT 6');

$recentMembers = pyrk_report_rows($pdo, 'SELECT u.id, u.full_name, u.email, u.mobile, u.profile_photo_path, u.created_at, COALESCE(mp.total_kilometers,0) AS total_kilometers FROM users u JOIN roles r ON r.id = u.role_id LEFT JOIN member_profiles mp ON mp.user_id = u.id WHERE r.name = "member" ORDER BY u.created_at DESC LIMIT 6');

Response::ok([
    'stats' => $stats,
    'active_walk_summary' => $activeSummary,
    'active_walks' => $activeWalks,
    // Kept for backward compatibility with any cached dashboard script.
    'upcoming_walks' => $activeWalks,
    'top_walkers' => $topWalkers,
    'pending_photos' => $pendingPhotos,
    'recent_members' => $recentMembers,
]);
