<?php
/**
 * API pour le Dashboard (Version Distante)
 * À installer sur le serveur cPanel dans le dossier api/
 */

header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
header("Access-Control-Allow-Methods: GET, POST, OPTIONS");
header("Access-Control-Max-Age: 3600");
header("Access-Control-Allow-Headers: Content-Type, Access-Control-Allow-Headers, Authorization, X-Requested-With");

if ($_SERVER['REQUEST_METHOD'] == 'OPTIONS') {
    http_response_code(200);
    exit;
}

require_once __DIR__ . '/../config/database.php';

$action = $_GET['action'] ?? '';
$date = $_GET['date'] ?? date('Y-m-d');

try {
    $pdo = Database::getInstance('transubd_PointageBus');
    // Note: Database::getInstance returns my Database wrapper, which supports query(), prepare(), etc.
    // However, some cases might expect a real PDO object. 
    // In our case, the switch/case uses $pdo->query() and $pdo->prepare(), which my wrapper supports!

    switch ($action) {
        case 'get_sites':
            try {
                // Requête inter-base de données (suppose que l'utilisateur a accès aux deux BDs)
                // Sinon, il faudrait une connexion séparée
                $stmt = $pdo->query("SELECT SiteID, Nom_Site, Nom_SiteAR, Localisation_Site FROM transubd_GGR.Sites WHERE SiteID != 'SG' ORDER BY SiteID");
                $sites = $stmt->fetchAll();
                echo json_encode(['success' => true, 'data' => $sites]);
            } catch (Exception $e) {
                // Fallback si la requête échoue (ex: pas de droits sur l'autre BD)
                // On renvoie une erreur pour que le client puisse gérer ou logger
                throw new Exception("Impossible de récupérer les sites: " . $e->getMessage());
            }
            break;

        case 'summary':
            $today = date('Y-m-d');
            $yesterday = date('Y-m-d', strtotime('-1 day'));
            $lastWeek = date('Y-m-d', strtotime('-7 days'));

            // Today's statistics
            $stmt = $pdo->prepare("
                SELECT 
                    COUNT(*) as total_checkins, 
                    COUNT(DISTINCT agent_id) as total_agents,
                    MIN(checkin_time) as first_checkin,
                    MAX(checkin_time) as last_checkin
                FROM checkins 
                WHERE checkin_date = ? AND is_valid = 1
            ");
            $stmt->execute([$today]);
            $todaySummary = $stmt->fetch();

            // Yesterday's statistics (for change percentage)
            $stmt = $pdo->prepare("SELECT COUNT(*) as total_checkins FROM checkins WHERE checkin_date = ? AND is_valid = 1");
            $stmt->execute([$yesterday]);
            $yesterdayCheckins = $stmt->fetch()['total_checkins'];

            // Weekly statistics
            $stmt = $pdo->prepare("
                SELECT checkin_date, COUNT(*) as count 
                FROM checkins 
                WHERE checkin_date BETWEEN ? AND ? AND is_valid = 1
                GROUP BY checkin_date
                ORDER BY checkin_date ASC
            ");
            $stmt->execute([$lastWeek, $today]);
            $weeklyStats = $stmt->fetchAll();

            // General counts
            $totalBuses = $pdo->query("SELECT COUNT(*) FROM buses WHERE actif = 1")->fetchColumn();
            $totalAgents = $pdo->query("SELECT COUNT(*) FROM agents WHERE is_active = 1")->fetchColumn();
            $totalOperators = $pdo->query("SELECT COUNT(*) FROM operators WHERE is_active = 1")->fetchColumn();
            $totalLines = $pdo->query("SELECT COUNT(*) FROM lignes WHERE is_active = 1")->fetchColumn();

            // Calculate percentages
            $checkinPercentage = $totalBuses > 0 ? round(($todaySummary['total_checkins'] / $totalBuses) * 100, 1) : 0;
            $checkinChange = 0;
            if ($yesterdayCheckins > 0) {
                $checkinChange = round((($todaySummary['total_checkins'] - $yesterdayCheckins) / $yesterdayCheckins) * 100, 1);
            }

            $response = [
                'success' => true,
                'data' => [
                    'today' => array_merge($todaySummary, [
                        'checkin_percentage' => $checkinPercentage,
                        'checkin_change_percentage' => $checkinChange
                    ]),
                    'totals' => [
                        'buses' => $totalBuses,
                        'agents' => $totalAgents,
                        'operators' => $totalOperators,
                        'lines' => $totalLines
                    ],
                    'weekly' => $weeklyStats
                ]
            ];
            echo json_encode($response);
            break;

        case 'daily_report':
            if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)) {
                throw new Exception('Invalid date format. Use YYYY-MM-DD');
            }

            // Daily Summary
            $stmt = $pdo->prepare("
                SELECT 
                    COUNT(*) as total_checkins, 
                    COUNT(DISTINCT agent_id) as total_agents,
                    MIN(checkin_time) as first_checkin,
                    MAX(checkin_time) as last_checkin
                FROM checkins 
                WHERE checkin_date = ? AND is_valid = 1
            ");
            $stmt->execute([$date]);
            $dailySummary = $stmt->fetch();

            // Hourly Stats
            $stmt = $pdo->prepare("
                SELECT HOUR(checkin_time) as hour, COUNT(*) as count
                FROM checkins
                WHERE checkin_date = ? AND is_valid = 1
                GROUP BY HOUR(checkin_time)
                ORDER BY hour ASC
            ");
            $stmt->execute([$date]);
            $hourlyStats = $stmt->fetchAll();

            // Checked In Buses
            $stmt = $pdo->prepare("
                SELECT DISTINCT b.*, o.name as operator_name, l.name as line_name, c.checkin_time
                FROM checkins c
                JOIN buses b ON c.bus_id = b.id
                LEFT JOIN operators o ON b.id_operateur = o.id
                LEFT JOIN lignes l ON b.id_ligne = l.ligne_code
                WHERE c.checkin_date = ? AND c.is_valid = 1
                ORDER BY c.checkin_time DESC
            ");
            $stmt->execute([$date]);
            $checkedInBuses = $stmt->fetchAll();
            $checkedInBusIds = array_column($checkedInBuses, 'id');

            // Absent Buses
            // Efficiently find buses not in the checkedIn list
            $sqlAbsent = "
                SELECT b.*, o.name as operator_name, l.name as line_name
                FROM buses b
                LEFT JOIN operators o ON b.id_operateur = o.id
                LEFT JOIN lignes l ON b.id_ligne = l.ligne_code
                WHERE b.actif = 1
            ";
            if (!empty($checkedInBusIds)) {
                $placeholders = implode(',', array_fill(0, count($checkedInBusIds), '?'));
                $sqlAbsent .= " AND b.id NOT IN ($placeholders)";
                $stmt = $pdo->prepare($sqlAbsent);
                $stmt->execute($checkedInBusIds);
            } else {
                $stmt = $pdo->query($sqlAbsent);
            }
            $absentBuses = $stmt->fetchAll();

            echo json_encode([
                'success' => true,
                'data' => [
                    'date' => $date,
                    'summary' => $dailySummary,
                    'hourly_breakdown' => $hourlyStats,
                    'checked_in_buses' => $checkedInBuses,
                    'absent_buses' => $absentBuses,
                    'checked_in_count' => count($checkedInBuses),
                    'absent_count' => count($absentBuses),
                    'total_expected' => count($checkedInBuses) + count($absentBuses)
                ]
            ]);
            break;

        case 'stats':
            // Validation de la date
            if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)) {
                throw new Exception('Invalid date format. Use YYYY-MM-DD');
            }

            // Total bus actifs
            $stmt = $pdo->prepare("SELECT COUNT(*) as count FROM buses WHERE actif = 1");
            $stmt->execute();
            $totalBuses = (int)$stmt->fetch()['count'];

            // Bus scannés
            $stmt = $pdo->prepare("SELECT COUNT(DISTINCT bus_id) as count FROM checkins WHERE DATE(checkin_datetime) = ? AND is_valid = 1");
            $stmt->execute([$date]);
            $scannedBuses = (int)$stmt->fetch()['count'];

            echo json_encode([
                'success' => true,
                'data' => [
                    'date' => $date,
                    'total_buses' => $totalBuses,
                    'scanned_buses' => $scannedBuses,
                    'absent_buses' => max(0, $totalBuses - $scannedBuses)
                ]
            ]);
            break;

        case 'active_buses':
            $stmt = $pdo->prepare("
                SELECT b.matricule, o.name as operator_name
                FROM buses b
                LEFT JOIN operators o ON b.id_operateur = o.id
                WHERE b.actif = 1
                ORDER BY b.matricule ASC
            ");
            $stmt->execute();
            $buses = $stmt->fetchAll();

            echo json_encode([
                'success' => true,
                'data' => $buses
            ]);
            break;

        case 'scanned_buses':
            if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)) {
                throw new Exception('Invalid date format. Use YYYY-MM-DD');
            }

            $stmt = $pdo->prepare("
                SELECT
                    b.matricule,
                    o.name as operator_name,
                    c.checkin_datetime,
                    a.full_name as scanned_by
                FROM checkins c
                LEFT JOIN buses b ON c.bus_id = b.id
                LEFT JOIN operators o ON b.id_operateur = o.id
                LEFT JOIN agents a ON c.agent_id = a.id
                WHERE DATE(c.checkin_datetime) = ?
                AND c.is_valid = 1
                ORDER BY c.checkin_datetime DESC
            ");
            $stmt->execute([$date]);
            $scannedBuses = $stmt->fetchAll();

            echo json_encode([
                'success' => true,
                'data' => $scannedBuses
            ]);
            break;

        case 'absent_buses':
            if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)) {
                throw new Exception('Invalid date format. Use YYYY-MM-DD');
            }

            $stmt = $pdo->prepare("
                SELECT
                    b.matricule,
                    o.name as operator_name
                FROM buses b
                LEFT JOIN operators o ON b.id_operateur = o.id
                WHERE b.actif = 1
                AND b.id NOT IN (
                    SELECT DISTINCT bus_id
                    FROM checkins
                    WHERE DATE(checkin_datetime) = ?
                    AND is_valid = 1
                )
                ORDER BY b.matricule ASC
            ");
            $stmt->execute([$date]);
            $absentBuses = $stmt->fetchAll();

            echo json_encode([
                'success' => true,
                'data' => $absentBuses
            ]);
            break;

        case 'get_exercises':
            $currentYear = date('Y');
            $stmt = $pdo->prepare("SELECT * FROM exercices WHERE annee <= ? ORDER BY annee DESC");
            $stmt->execute([$currentYear]);
            $exercises = $stmt->fetchAll();
            echo json_encode(['success' => true, 'data' => $exercises]);
            break;

        case 'get_exercise_by_year':
            $year = $_GET['year'] ?? '';
            if (empty($year)) {
                throw new Exception('Year is required');
            }
            $stmt = $pdo->prepare("SELECT * FROM exercices WHERE annee = ?");
            $stmt->execute([$year]);
            $exercise = $stmt->fetch();
            echo json_encode(['success' => true, 'data' => $exercise]);
            break;

        case 'operator_stats':
            $dateFrom = $_GET['date_from'] ?? date('Y-m-d', strtotime('-30 days'));
            $dateTo = $_GET['date_to'] ?? date('Y-m-d');

            // Get all operators
            $stmt = $pdo->prepare("SELECT * FROM operators WHERE is_active = 1");
            $stmt->execute();
            $operators = $stmt->fetchAll();

            $operatorStats = [];
            foreach ($operators as $operator) {
                // Count buses for this operator
                $stmtBus = $pdo->prepare("SELECT COUNT(*) FROM buses WHERE id_operateur = ? AND actif = 1");
                $stmtBus->execute([$operator['id']]);
                $totalBuses = $stmtBus->fetchColumn();

                // Get checkins stats
                $stmtCheckins = $pdo->prepare("
                    SELECT 
                        COUNT(*) as total_checkins,
                        COUNT(DISTINCT checkin_date) as active_days
                    FROM checkins 
                    WHERE operator_id = ? 
                    AND checkin_date BETWEEN ? AND ? 
                    AND is_valid = 1
                ");
                $stmtCheckins->execute([$operator['id'], $dateFrom, $dateTo]);
                $stats = $stmtCheckins->fetch();

                $totalCheckins = $stats['total_checkins'];
                $uniqueDays = $stats['active_days'];
                $expectedCheckins = $totalBuses * $uniqueDays;

                $operatorStats[] = [
                    'operator' => $operator,
                    'total_buses' => $totalBuses,
                    'total_checkins' => $totalCheckins,
                    'expected_checkins' => $expectedCheckins,
                    'compliance_percentage' => $expectedCheckins > 0 ? round(($totalCheckins / $expectedCheckins) * 100, 1) : 0,
                    'active_days' => $uniqueDays
                ];
            }

            // Sort by compliance percentage descending
            usort($operatorStats, function($a, $b) {
                return $b['compliance_percentage'] <=> $a['compliance_percentage'];
            });

            echo json_encode(['success' => true, 'data' => $operatorStats]);
            break;

        case 'line_stats':
            $dateFrom = $_GET['date_from'] ?? date('Y-m-d', strtotime('-30 days'));
            $dateTo = $_GET['date_to'] ?? date('Y-m-d');

            // Get all lines
            $stmt = $pdo->prepare("SELECT * FROM lignes WHERE is_active = 1");
            $stmt->execute();
            $lines = $stmt->fetchAll();

            $lineStats = [];
            foreach ($lines as $line) {
                // Count buses for this line
                $stmtBus = $pdo->prepare("SELECT COUNT(*) FROM buses WHERE id_ligne = ? AND actif = 1");
                $stmtBus->execute([$line['ligne_code']]); // Assuming id_ligne maps to ligne_code
                $totalBuses = $stmtBus->fetchColumn(); // Use actual count or line['buses_count'] if preferred, but actual count is safer

                // Get checkins stats
                $stmtCheckins = $pdo->prepare("
                    SELECT 
                        COUNT(*) as total_checkins,
                        COUNT(DISTINCT checkin_date) as active_days
                    FROM checkins 
                    WHERE line_id = ? 
                    AND checkin_date BETWEEN ? AND ? 
                    AND is_valid = 1
                ");
                $stmtCheckins->execute([$line['ligne_code'], $dateFrom, $dateTo]);
                $stats = $stmtCheckins->fetch();

                $totalCheckins = $stats['total_checkins'];
                $uniqueDays = $stats['active_days'];
                $expectedCheckins = $totalBuses * $uniqueDays;

                $lineStats[] = [
                    'line' => $line,
                    'total_buses' => $totalBuses,
                    'total_checkins' => $totalCheckins,
                    'expected_checkins' => $expectedCheckins,
                    'compliance_percentage' => $expectedCheckins > 0 ? round(($totalCheckins / $expectedCheckins) * 100, 1) : 0,
                    'active_days' => $uniqueDays
                ];
            }

            // Sort by compliance percentage descending
            usort($lineStats, function($a, $b) {
                return $b['compliance_percentage'] <=> $a['compliance_percentage'];
            });

            echo json_encode(['success' => true, 'data' => $lineStats]);
            break;

        case 'out_of_zone_alerts':
            $date = $_GET['date'] ?? date('Y-m-d');
            $limit = (int)($_GET['limit'] ?? 50);
            
            // Station coordinates (Boumerdes)
            $stationLat = 36.7667;
            $stationLng = 3.4167;
            $maxDistance = 500; // meters

            // Get checkins with coordinates
            $stmt = $pdo->prepare("
                SELECT c.*, b.matricule, o.name as operator_name
                FROM checkins c
                LEFT JOIN buses b ON c.bus_id = b.id
                LEFT JOIN operators o ON b.id_operateur = o.id
                WHERE c.checkin_date = ? 
                AND c.is_valid = 1 
                AND c.latitude IS NOT NULL 
                AND c.longitude IS NOT NULL
                LIMIT ?
            ");
            // PDO limit requires integer binding or direct injection if safe (casted above)
            $stmt->bindValue(1, $date);
            $stmt->bindValue(2, $limit, PDO::PARAM_INT);
            $stmt->execute();
            $checkins = $stmt->fetchAll();

            $outOfZoneCheckins = [];
            foreach ($checkins as $checkin) {
                $lat1 = $stationLat;
                $lng1 = $stationLng;
                $lat2 = (float)$checkin['latitude'];
                $lng2 = (float)$checkin['longitude'];

                // Haversine formula
                $earthRadius = 6371000;
                $latDelta = deg2rad($lat2 - $lat1);
                $lngDelta = deg2rad($lng2 - $lng1);
                $a = sin($latDelta / 2) * sin($latDelta / 2) +
                     cos(deg2rad($lat1)) * cos(deg2rad($lat2)) *
                     sin($lngDelta / 2) * sin($lngDelta / 2);
                $c = 2 * atan2(sqrt($a), sqrt(1 - $a));
                $distance = $earthRadius * $c;

                if ($distance > $maxDistance) {
                    $checkin['distance_from_station'] = round($distance, 2);
                    $checkin['station_coordinates'] = ['lat' => $stationLat, 'lng' => $stationLng];
                    $outOfZoneCheckins[] = $checkin;
                }
            }

            echo json_encode([
                'success' => true,
                'data' => [
                    'date' => $date,
                    'station_location' => [
                        'latitude' => $stationLat,
                        'longitude' => $stationLng,
                        'name' => 'Boumerdes Bus Station'
                    ],
                    'max_allowed_distance' => $maxDistance,
                    'total_out_of_zone' => count($outOfZoneCheckins),
                    'alerts' => $outOfZoneCheckins
                ]
            ]);
            break;

        case 'export_data':
            $type = $_GET['type'] ?? 'checkins';
            $dateFrom = $_GET['date_from'] ?? date('Y-m-d', strtotime('-30 days'));
            $dateTo = $_GET['date_to'] ?? date('Y-m-d');

            $data = [];
            switch ($type) {
                case 'checkins':
                    $stmt = $pdo->prepare("SELECT * FROM checkins WHERE checkin_date BETWEEN ? AND ? ORDER BY checkin_date DESC");
                    $stmt->execute([$dateFrom, $dateTo]);
                    $data = $stmt->fetchAll();
                    break;
                case 'buses':
                    $stmt = $pdo->query("SELECT * FROM buses");
                    $data = $stmt->fetchAll();
                    break;
                case 'agents':
                    $stmt = $pdo->query("SELECT * FROM agents");
                    $data = $stmt->fetchAll();
                    break;
                default:
                    throw new Exception('Invalid export type');
            }

            echo json_encode([
                'success' => true,
                'data' => $data,
                'meta' => [
                    'type' => $type,
                    'count' => count($data),
                    'generated_at' => date('Y-m-d H:i:s')
                ]
            ]);
            break;

        default:
            echo json_encode(['success' => false, 'message' => 'Action inconnue']);
            break;
    }

} catch (Exception $e) {
    http_response_code(500);
    echo json_encode([
        'success' => false,
        'message' => 'Erreur: ' . $e->getMessage()
    ]);
}
?>