<?php
/**
 * ============================================
 * REPORTS.PHP - Admin Reports API
 * ============================================
 * Save this as a separate file: reports.php
 */

// Uncomment when using as standalone file:

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

    // Verify admin access
    $query = "SELECT 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['can_generate_reports']) {
        Helpers::sendResponse(false, 'Unauthorized. Report generation access required.', null, 403);
    }

    $reportType = isset($_GET['type']) ? $_GET['type'] : 'general';
    $userId = isset($_GET['user_id']) ? intval($_GET['user_id']) : null;
    $serviceId = isset($_GET['service_id']) ? intval($_GET['service_id']) : null;
    $departmentId = isset($_GET['department_id']) ? intval($_GET['department_id']) : null;
    $startDate = isset($_GET['start_date']) ? $_GET['start_date'] : date('Y-m-d', strtotime('-30 days'));
    $endDate = isset($_GET['end_date']) ? $_GET['end_date'] : date('Y-m-d');

    // INDIVIDUAL USER REPORT
    if ($reportType === 'user' && $userId) {
        // Get user details
        $query = "SELECT u.id, u.email, u.first_name, u.last_name, u.phone_number,
                         u.avatar_url, u.created_at,
                         d.name as department_name,
                         ur.role_name,
                         COUNT(DISTINCT a.id) as total_attendance,
                         SUM(a.time_spent) as total_minutes,
                         COUNT(DISTINCT t.id) as total_tickets,
                         SUM(CASE WHEN t.is_resolved = FALSE THEN 1 ELSE 0 END) as open_tickets,
                         COUNT(DISTINCT usr.id) as rewards_earned,
                         COUNT(DISTINCT c.id) as certificates_earned
                  FROM users u
                  LEFT JOIN departments d ON u.department_id = d.id
                  JOIN user_roles ur ON u.role_id = ur.id
                  LEFT JOIN attendance a ON u.id = a.user_id 
                       AND DATE(a.clock_in_time) BETWEEN :start_date AND :end_date
                  LEFT JOIN tickets t ON u.id = t.user_id
                  LEFT JOIN users_service_rewards usr ON u.id = usr.user_id AND usr.is_redeemed = TRUE
                  LEFT JOIN certificates c ON u.id = c.user_id
                  WHERE u.id = :user_id
                  GROUP BY u.id";

        $stmt = $db->prepare($query);
        $stmt->bindParam(':user_id', $userId);
        $stmt->bindParam(':start_date', $startDate);
        $stmt->bindParam(':end_date', $endDate);
        $stmt->execute();
        $userReport = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$userReport) {
            Helpers::sendResponse(false, 'User not found', null, 404);
        }

        // Get attendance details
        $query = "SELECT a.id, a.clock_in_time, a.clock_out_time, a.time_spent,
                         s.name as service_name, s.location
                  FROM attendance a
                  JOIN services s ON a.service_id = s.id
                  WHERE a.user_id = :user_id 
                  AND DATE(a.clock_in_time) BETWEEN :start_date AND :end_date
                  ORDER BY a.clock_in_time DESC";

        $stmt = $db->prepare($query);
        $stmt->bindParam(':user_id', $userId);
        $stmt->bindParam(':start_date', $startDate);
        $stmt->bindParam(':end_date', $endDate);
        $stmt->execute();
        $attendanceDetails = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Get tickets
        $query = "SELECT t.id, t.ticket_number, t.ticket_type, t.reason, t.severity,
                         t.issued_at, t.is_resolved, t.resolved_at,
                         CONCAT(ib.first_name, ' ', ib.last_name) as issued_by_name
                  FROM tickets t
                  JOIN users ib ON t.issued_by = ib.id
                  WHERE t.user_id = :user_id
                  ORDER BY t.issued_at DESC";

        $stmt = $db->prepare($query);
        $stmt->bindParam(':user_id', $userId);
        $stmt->execute();
        $tickets = $stmt->fetchAll(PDO::FETCH_ASSOC);

        Helpers::sendResponse(true, 'User report generated successfully', [
            'user' => $userReport,
            'attendance_details' => $attendanceDetails,
            'tickets' => $tickets,
            'period' => ['start' => $startDate, 'end' => $endDate]
        ]);
    }

    // SERVICE REPORT
    elseif ($reportType === 'service' && $serviceId) {
        // Get service details
        $query = "SELECT s.id, s.name, s.description, s.location, s.start_time, s.end_time,
                         st.name as service_type,
                         COUNT(DISTINCT a.user_id) as total_attendees,
                         COUNT(DISTINCT a.id) as total_attendance_records,
                         AVG(a.time_spent) as avg_time_spent,
                         COUNT(DISTINCT sr.id) as rewards_count
                  FROM services s
                  LEFT JOIN service_types st ON s.service_type_id = st.id
                  LEFT JOIN attendance a ON s.id = a.service_id
                  LEFT JOIN service_rewards sr ON s.id = sr.service_id
                  WHERE s.id = :service_id
                  GROUP BY s.id";

        $stmt = $db->prepare($query);
        $stmt->bindParam(':service_id', $serviceId);
        $stmt->execute();
        $serviceReport = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$serviceReport) {
            Helpers::sendResponse(false, 'Service not found', null, 404);
        }

        // Get top attendees
        $query = "SELECT u.id, CONCAT(u.first_name, ' ', u.last_name) as name,
                         u.email, 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 a.service_id = :service_id
                  GROUP BY u.id
                  ORDER BY attendance_count DESC
                  LIMIT 10";

        $stmt = $db->prepare($query);
        $stmt->bindParam(':service_id', $serviceId);
        $stmt->execute();
        $topAttendees = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Get attendance by department
        $query = "SELECT d.name as department, COUNT(DISTINCT a.user_id) as attendee_count
                  FROM attendance a
                  JOIN users u ON a.user_id = u.id
                  LEFT JOIN departments d ON u.department_id = d.id
                  WHERE a.service_id = :service_id
                  GROUP BY d.id
                  ORDER BY attendee_count DESC";

        $stmt = $db->prepare($query);
        $stmt->bindParam(':service_id', $serviceId);
        $stmt->execute();
        $attendanceByDepartment = $stmt->fetchAll(PDO::FETCH_ASSOC);

        Helpers::sendResponse(true, 'Service report generated successfully', [
            'service' => $serviceReport,
            'top_attendees' => $topAttendees,
            'attendance_by_department' => $attendanceByDepartment
        ]);
    }

    // DEPARTMENT REPORT
    elseif ($reportType === 'department' && $departmentId) {
        // Get department details
        $query = "SELECT d.id, d.name, d.description, d.is_active, d.created_at,
                         COUNT(DISTINCT u.id) as total_members,
                         COUNT(DISTINCT CASE WHEN u.is_hod = TRUE THEN u.id END) as hod_count,
                         COUNT(DISTINCT a.id) as total_attendance,
                         COUNT(DISTINCT t.id) as total_tickets,
                         SUM(CASE WHEN t.is_resolved = FALSE THEN 1 ELSE 0 END) as open_tickets
                  FROM departments d
                  LEFT JOIN users u ON d.id = u.department_id AND u.is_active = TRUE
                  LEFT JOIN attendance a ON u.id = a.user_id 
                       AND DATE(a.clock_in_time) BETWEEN :start_date AND :end_date
                  LEFT JOIN tickets t ON u.id = t.user_id
                  WHERE d.id = :department_id
                  GROUP BY d.id";

        $stmt = $db->prepare($query);
        $stmt->bindParam(':department_id', $departmentId);
        $stmt->bindParam(':start_date', $startDate);
        $stmt->bindParam(':end_date', $endDate);
        $stmt->execute();
        $deptReport = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$deptReport) {
            Helpers::sendResponse(false, 'Department not found', null, 404);
        }

        // Get top performers
        $query = "SELECT u.id, CONCAT(u.first_name, ' ', u.last_name) as name,
                         u.email, u.avatar_url,
                         COUNT(a.id) as attendance_count,
                         SUM(a.time_spent) as total_minutes
                  FROM users u
                  LEFT JOIN attendance a ON u.id = a.user_id 
                       AND DATE(a.clock_in_time) BETWEEN :start_date AND :end_date
                  WHERE u.department_id = :department_id AND u.is_active = TRUE
                  GROUP BY u.id
                  ORDER BY attendance_count DESC
                  LIMIT 10";

        $stmt = $db->prepare($query);
        $stmt->bindParam(':department_id', $departmentId);
        $stmt->bindParam(':start_date', $startDate);
        $stmt->bindParam(':end_date', $endDate);
        $stmt->execute();
        $topPerformers = $stmt->fetchAll(PDO::FETCH_ASSOC);

        Helpers::sendResponse(true, 'Department report generated successfully', [
            'department' => $deptReport,
            'top_performers' => $topPerformers,
            'period' => ['start' => $startDate, 'end' => $endDate]
        ]);
    }

    // GENERAL REPORT
    else {
        // Overall statistics
        $query = "SELECT 
                      COUNT(DISTINCT u.id) as total_users,
                      COUNT(DISTINCT CASE WHEN u.is_active = TRUE THEN u.id END) as active_users,
                      COUNT(DISTINCT s.id) as total_services,
                      COUNT(DISTINCT d.id) as total_departments,
                      COUNT(DISTINCT a.id) as total_attendance,
                      COUNT(DISTINCT t.id) as total_tickets,
                      SUM(CASE WHEN t.is_resolved = FALSE THEN 1 ELSE 0 END) as open_tickets,
                      COUNT(DISTINCT r.id) as total_rewards,
                      COUNT(DISTINCT c.id) as total_certificates
                  FROM users u
                  LEFT JOIN services s ON s.is_active = TRUE
                  LEFT JOIN departments d ON d.is_active = TRUE
                  LEFT JOIN attendance a ON DATE(a.clock_in_time) BETWEEN :start_date AND :end_date
                  LEFT JOIN tickets t ON 1=1
                  LEFT JOIN rewards r ON r.is_active = TRUE
                  LEFT JOIN certificates c ON 1=1";

        $stmt = $db->prepare($query);
        $stmt->bindParam(':start_date', $startDate);
        $stmt->bindParam(':end_date', $endDate);
        $stmt->execute();
        $generalStats = $stmt->fetch(PDO::FETCH_ASSOC);

        // Attendance by service
        $query = "SELECT s.name, s.start_time, COUNT(DISTINCT a.user_id) as attendee_count
                  FROM services s
                  LEFT JOIN attendance a ON s.id = a.service_id 
                       AND DATE(a.clock_in_time) BETWEEN :start_date AND :end_date
                  WHERE s.is_active = TRUE
                  GROUP BY s.id
                  ORDER BY attendee_count DESC
                  LIMIT 10";

        $stmt = $db->prepare($query);
        $stmt->bindParam(':start_date', $startDate);
        $stmt->bindParam(':end_date', $endDate);
        $stmt->execute();
        $attendanceByService = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Attendance by department
        $query = "SELECT d.name, COUNT(DISTINCT a.user_id) as attendee_count
                  FROM departments d
                  LEFT JOIN users u ON d.id = u.department_id
                  LEFT JOIN attendance a ON u.id = a.user_id 
                       AND DATE(a.clock_in_time) BETWEEN :start_date AND :end_date
                  WHERE d.is_active = TRUE
                  GROUP BY d.id
                  ORDER BY attendee_count DESC";

        $stmt = $db->prepare($query);
        $stmt->bindParam(':start_date', $startDate);
        $stmt->bindParam(':end_date', $endDate);
        $stmt->execute();
        $attendanceByDepartment = $stmt->fetchAll(PDO::FETCH_ASSOC);

        // Top attendees
        $query = "SELECT u.id, CONCAT(u.first_name, ' ', u.last_name) as name,
                         u.email, u.avatar_url,
                         d.name as department,
                         COUNT(a.id) as attendance_count,
                         SUM(a.time_spent) as total_minutes
                  FROM users u
                  LEFT JOIN departments d ON u.department_id = d.id
                  LEFT JOIN attendance a ON u.id = a.user_id 
                       AND DATE(a.clock_in_time) BETWEEN :start_date AND :end_date
                  WHERE u.is_active = TRUE
                  GROUP BY u.id
                  HAVING attendance_count > 0
                  ORDER BY attendance_count DESC
                  LIMIT 10";

        $stmt = $db->prepare($query);
        $stmt->bindParam(':start_date', $startDate);
        $stmt->bindParam(':end_date', $endDate);
        $stmt->execute();
        $topAttendees = $stmt->fetchAll(PDO::FETCH_ASSOC);

        Helpers::sendResponse(true, 'General report generated successfully', [
            'statistics' => $generalStats,
            'attendance_by_service' => $attendanceByService,
            'attendance_by_department' => $attendanceByDepartment,
            'top_attendees' => $topAttendees,
            'period' => ['start' => $startDate, 'end' => $endDate]
        ]);
    }
} catch (PDOException $e) {
    ErrorLogger::logDatabaseError($e, $userData['user_id'] ?? 'unknown', [
        'endpoint' => 'admin/reports',
        'user_id' => $userData['user_id'] ?? 'unknown'
    ]);
    Helpers::sendResponse(false, 'Database error in reports management: ' . $e->getMessage(), null, 500);
} catch (Exception $e) {
    ErrorLogger::logError($e, [
        'endpoint' => 'admin/reports',
        'user_id' => $userData['user_id'] ?? 'unknown'
    ]);
    Helpers::sendResponse(false, 'Error in reports management: ' . $e->getMessage(), null, 500);
}
