<?php

/**
 * Admin Dashboard API - Updated for Service Templates & Occurrences
 * Provides overview statistics and recent activity
 */
require_once 'config/database.php';
require_once 'utils/helpers.php';

try {
    if ($_SERVER['REQUEST_METHOD'] !== 'GET') {
        Helpers::sendResponse(false, 'Method not allowed', null, 405);
    }

    // Get and verify token
    $token = Helpers::getBearerToken();
    $userData = Helpers::verifyJWT($token);

    if (!$userData) {
        Helpers::sendResponse(false, 'Unauthorized access', null, 401);
    }

    $database = new Database();
    $db = $database->getConnection();

    // Check if user has admin role
    $query = "SELECT ur.role_name, ur.role_level, ur.can_generate_reports 
              FROM users u 
              JOIN user_roles ur ON u.role_id = ur.id 
              WHERE u.id = :user_id";
    $stmt = $db->prepare($query);
    $stmt->bindParam(':user_id', $userData['user_id']);
    $stmt->execute();
    $userRole = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$userRole || $userRole['role_level'] < 4) {
        Helpers::sendResponse(false, 'Unauthorized. Admin access required.', null, 403);
    }

    // ============================================
    // STATISTICS
    // ============================================

    // Get total active users
    $query = "SELECT COUNT(*) as total FROM users WHERE is_active = TRUE";
    $stmt = $db->prepare($query);
    $stmt->execute();
    $totalUsers = $stmt->fetch(PDO::FETCH_ASSOC)['total'];

    // Get users by role
    $query = "SELECT ur.role_name, COUNT(u.id) as count 
              FROM user_roles ur 
              LEFT JOIN users u ON ur.id = u.role_id AND u.is_active = TRUE
              GROUP BY ur.id, ur.role_name 
              ORDER BY ur.role_level";
    $stmt = $db->prepare($query);
    $stmt->execute();
    $usersByRole = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Get total active service templates
    $query = "SELECT COUNT(*) as total FROM service_templates WHERE is_active = TRUE";
    $stmt = $db->prepare($query);
    $stmt->execute();
    $totalServiceTemplates = $stmt->fetch(PDO::FETCH_ASSOC)['total'];

    // Get total active service occurrences (upcoming and current)
    $query = "SELECT COUNT(*) as total 
              FROM service_occurrences 
              WHERE is_active = TRUE 
              AND was_cancelled = FALSE
              AND occurrence_date >= CURDATE()";
    $stmt = $db->prepare($query);
    $stmt->execute();
    $totalServiceOccurrences = $stmt->fetch(PDO::FETCH_ASSOC)['total'];

    // Get total departments
    $query = "SELECT COUNT(*) as total FROM departments WHERE is_active = TRUE";
    $stmt = $db->prepare($query);
    $stmt->execute();
    $totalDepartments = $stmt->fetch(PDO::FETCH_ASSOC)['total'];

    // Get today's attendance count
    $query = "SELECT COUNT(DISTINCT user_id) as total 
              FROM attendance 
              WHERE DATE(clock_in_time) = CURDATE()";
    $stmt = $db->prepare($query);
    $stmt->execute();
    $todayAttendance = $stmt->fetch(PDO::FETCH_ASSOC)['total'];

    // Get this week's attendance count
    $query = "SELECT COUNT(DISTINCT user_id) as total 
              FROM attendance 
              WHERE YEARWEEK(clock_in_time, 1) = YEARWEEK(CURDATE(), 1)";
    $stmt = $db->prepare($query);
    $stmt->execute();
    $weekAttendance = $stmt->fetch(PDO::FETCH_ASSOC)['total'];

    // Get total attendance records
    $query = "SELECT COUNT(*) as total FROM attendance";
    $stmt = $db->prepare($query);
    $stmt->execute();
    $totalAttendances = $stmt->fetch(PDO::FETCH_ASSOC)['total'];

    // Get pending rewards count
    $query = "SELECT COUNT(*) as total 
              FROM users_service_rewards usr
              JOIN service_rewards sr ON usr.service_reward_id = sr.id
              WHERE usr.attendance_count >= sr.attendance_required 
              AND usr.is_redeemed = FALSE
              AND sr.is_active = TRUE";
    $stmt = $db->prepare($query);
    $stmt->execute();
    $pendingRewards = $stmt->fetch(PDO::FETCH_ASSOC)['total'];

    // Get redeemed rewards count
    $query = "SELECT COUNT(*) as total 
              FROM users_service_rewards 
              WHERE is_redeemed = TRUE";
    $stmt = $db->prepare($query);
    $stmt->execute();
    $redeemedRewards = $stmt->fetch(PDO::FETCH_ASSOC)['total'];

    // Get active tickets count
    $query = "SELECT COUNT(*) as total FROM tickets WHERE is_resolved = FALSE";
    $stmt = $db->prepare($query);
    $stmt->execute();
    $activeTickets = $stmt->fetch(PDO::FETCH_ASSOC)['total'];

    // Get resolved tickets count
    $query = "SELECT COUNT(*) as total FROM tickets WHERE is_resolved = TRUE";
    $stmt = $db->prepare($query);
    $stmt->execute();
    $resolvedTickets = $stmt->fetch(PDO::FETCH_ASSOC)['total'];

    // ============================================
    // RECENT ATTENDANCE
    // ============================================
    $query = "SELECT a.id, a.clock_in_time, a.clock_out_time, a.time_spent, a.is_valid,
                     u.id as user_id, u.first_name, u.last_name, u.email, u.avatar_url,
                     so.id as service_occurrence_id, 
                     st.name as service_name, 
                     st.location,
                     so.occurrence_date,
                     so.start_time,
                     so.end_time,
                     d.name as department_name,
                     ur.role_name
              FROM attendance a
              JOIN users u ON a.user_id = u.id
              JOIN service_occurrences so ON a.service_occurrence_id = so.id
              JOIN service_templates st ON so.service_template_id = st.id
              JOIN user_roles ur ON u.role_id = ur.id
              LEFT JOIN departments d ON u.department_id = d.id
              WHERE a.is_valid = TRUE
              ORDER BY a.clock_in_time DESC
              LIMIT 10";
    $stmt = $db->prepare($query);
    $stmt->execute();
    $recentAttendance = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Format start_time and end_time for frontend
    foreach ($recentAttendance as &$attendance) {
        // Extract time from datetime
        if ($attendance['start_time']) {
            $attendance['start_time'] = date('H:i:s', strtotime($attendance['start_time']));
        }
        if ($attendance['end_time']) {
            $attendance['end_time'] = date('H:i:s', strtotime($attendance['end_time']));
        }
    }

    // ============================================
    // ACTIVE SERVICES (Currently ongoing)
    // ============================================
    $now = date('Y-m-d H:i:s');
    $query = "SELECT so.id, st.name, st.location, so.occurrence_date, 
                     so.start_time, so.end_time,
                     stype.name as service_type,
                     COUNT(DISTINCT a.user_id) as attendance_count
              FROM service_occurrences so
              JOIN service_templates st ON so.service_template_id = st.id
              LEFT JOIN service_types stype ON st.service_type_id = stype.id
              LEFT JOIN attendance a ON so.id = a.service_occurrence_id
              WHERE so.is_active = TRUE
              AND so.was_cancelled = FALSE
              AND so.start_time <= :now
              AND so.end_time >= :now
              GROUP BY so.id
              ORDER BY so.occurrence_date DESC, so.start_time DESC
              LIMIT 5";
    $stmt = $db->prepare($query);
    $stmt->bindParam(':now', $now);
    $stmt->execute();
    $activeServices = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // ============================================
    // UPCOMING SERVICES (Next 7 days)
    // ============================================
    $today = date('Y-m-d');
    $nextWeek = date('Y-m-d', strtotime('+7 days'));
    $query = "SELECT so.id, st.name, st.location, so.occurrence_date, 
                     so.start_time, so.end_time,
                     stype.name as service_type
              FROM service_occurrences so
              JOIN service_templates st ON so.service_template_id = st.id
              LEFT JOIN service_types stype ON st.service_type_id = stype.id
              WHERE so.is_active = TRUE
              AND so.was_cancelled = FALSE
              AND so.occurrence_date > :today
              AND so.occurrence_date <= :next_week
              ORDER BY so.occurrence_date ASC, so.start_time ASC
              LIMIT 5";
    $stmt = $db->prepare($query);
    $stmt->bindParam(':today', $today);
    $stmt->bindParam(':next_week', $nextWeek);
    $stmt->execute();
    $upcomingServices = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // ============================================
    // RECENT TICKETS
    // ============================================
    $query = "SELECT t.id, t.ticket_type, t.severity, t.reason, t.issued_at, t.is_resolved,
                     CONCAT(u.first_name, ' ', u.last_name) as user_name,
                     u.email as user_email,
                     CONCAT(ib.first_name, ' ', ib.last_name) as issued_by_name,
                     d.name as department_name
              FROM tickets t
              JOIN users u ON t.user_id = u.id
              JOIN users ib ON t.issued_by = ib.id
              LEFT JOIN departments d ON u.department_id = d.id
              ORDER BY t.issued_at DESC
              LIMIT 10";
    $stmt = $db->prepare($query);
    $stmt->execute();
    $recentTickets = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // ============================================
    // ATTENDANCE TRENDS (Last 7 days)
    // ============================================
    $query = "SELECT DATE(clock_in_time) as date, 
                     COUNT(DISTINCT user_id) as count
              FROM attendance
              WHERE clock_in_time >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
              GROUP BY DATE(clock_in_time)
              ORDER BY date ASC";
    $stmt = $db->prepare($query);
    $stmt->execute();
    $attendanceTrends = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // ============================================
    // TOP DEPARTMENTS BY ATTENDANCE (This month)
    // ============================================
    $query = "SELECT d.name, COUNT(a.id) as attendance_count
              FROM departments d
              JOIN users u ON d.id = u.department_id
              JOIN attendance a ON u.id = a.user_id
              WHERE MONTH(a.clock_in_time) = MONTH(CURDATE())
              AND YEAR(a.clock_in_time) = YEAR(CURDATE())
              AND d.is_active = TRUE
              GROUP BY d.id, d.name
              ORDER BY attendance_count DESC
              LIMIT 5";
    $stmt = $db->prepare($query);
    $stmt->execute();
    $topDepartments = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // ============================================
    // TOP ATTENDEES (This month)
    // ============================================
    $query = "SELECT u.id, 
                     CONCAT(u.first_name, ' ', u.last_name) as name,
                     u.avatar_url,
                     d.name as department,
                     COUNT(a.id) as attendance_count,
                     SUM(a.time_spent) as total_minutes
              FROM users u
              JOIN attendance a ON u.id = a.user_id
              LEFT JOIN departments d ON u.department_id = d.id
              WHERE MONTH(a.clock_in_time) = MONTH(CURDATE())
              AND YEAR(a.clock_in_time) = YEAR(CURDATE())
              AND a.is_valid = TRUE
              GROUP BY u.id
              ORDER BY attendance_count DESC
              LIMIT 10";
    $stmt = $db->prepare($query);
    $stmt->execute();
    $topAttendees = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // ============================================
    // SEND RESPONSE
    // ============================================
    Helpers::sendResponse(true, 'Dashboard data retrieved successfully', [
        'stats' => [
            'total_users' => (int)$totalUsers,
            'total_services' => (int)$totalServiceOccurrences,
            'total_service_templates' => (int)$totalServiceTemplates,
            'total_attendances' => (int)$totalAttendances,
            'total_departments' => (int)$totalDepartments,
            'today_attendance' => (int)$todayAttendance,
            'week_attendance' => (int)$weekAttendance,
            'pending_rewards' => (int)$pendingRewards,
            'redeemed_rewards' => (int)$redeemedRewards,
            'active_tickets' => (int)$activeTickets,
            'resolved_tickets' => (int)$resolvedTickets
        ],
        'users_by_role' => $usersByRole,
        'recent_attendance' => $recentAttendance,
        'active_services' => $activeServices,
        'upcoming_services' => $upcomingServices,
        'recent_tickets' => $recentTickets,
        'attendance_trends' => $attendanceTrends,
        'top_departments' => $topDepartments,
        'top_attendees' => $topAttendees
    ]);
} catch (PDOException $e) {
    ErrorLogger::logDatabaseError($e, $userData['user_id'] ?? 'unknown', [
        'endpoint' => 'admin/dashboard',
        'user_id' => $userData['user_id'] ?? 'unknown'
    ]);
    Helpers::sendResponse(false, 'Database error occurred: ' . $e->getMessage(), null, 500);
} catch (Exception $e) {
    ErrorLogger::logError($e, [
        'endpoint' => 'admin/dashboard',
        'user_id' => $userData['user_id'] ?? 'unknown'
    ]);
    Helpers::sendResponse(false, $e->getMessage(), null, 500);
}
