<?php
require_once("/opt/wevads/config/credentials.php");
header('Content-Type: application/json');
header('Access-Control-Allow-Origin: *');

// Configuration base de données
$db_config = [
    'host' => 'localhost',
    'dbname' => 'wevads',
    'user' => 'admin',
    'password' => WEVADS_DB_PASS
];

try {
    $pdo = new PDO(
        "pgsql:host={$db_config['host']};dbname={$db_config['dbname']}",
        $db_config['user'],
        $db_config['password'],
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_TIMEOUT => 10
        ]
    );
    
    $pdo->exec("SET search_path TO admin, public;");
    
    $action = $_GET['action'] ?? 'windows';
    
    switch ($action) {
        case 'windows':
            // Analyse par heure de la journée
            $query = "
                SELECT 
                    EXTRACT(HOUR FROM send_date) as hour_of_day,
                    isp,
                    COUNT(*) as send_count,
                    ROUND(AVG(CASE WHEN status = 'inbox' THEN 1 ELSE 0 END) * 100, 2) as inbox_rate,
                    ROUND(AVG(CASE WHEN status = 'spam' THEN 1 ELSE 0 END) * 100, 2) as spam_rate
                FROM admin.unified_send_log
                WHERE send_date >= NOW() - INTERVAL '7 days'
                GROUP BY EXTRACT(HOUR FROM send_date), isp
                ORDER BY hour_of_day, isp
            ";
            
            $stmt = $pdo->query($query);
            $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            // Formater pour affichage
            $formatted = [];
            foreach ($data as $row) {
                $hour = str_pad($row['hour_of_day'], 2, '0', STR_PAD_LEFT) . ':00';
                $formatted[] = [
                    'hour' => $hour,
                    'isp' => $row['isp'] ?? 'unknown',
                    'send_count' => (int)$row['send_count'],
                    'inbox_rate' => (float)$row['inbox_rate'],
                    'spam_rate' => (float)$row['spam_rate'],
                    'score' => round((float)$row['inbox_rate'] - (float)$row['spam_rate'], 2)
                ];
            }
            
            // Générer des recommandations
            $byHour = [];
            foreach ($formatted as $row) {
                $hour = $row['hour'];
                if (!isset($byHour[$hour])) {
                    $byHour[$hour] = ['total_score' => 0, 'count' => 0];
                }
                $byHour[$hour]['total_score'] += $row['score'];
                $byHour[$hour]['count']++;
            }
            
            $avgScores = [];
            foreach ($byHour as $hour => $stats) {
                $avgScores[$hour] = $stats['total_score'] / $stats['count'];
            }
            
            arsort($avgScores);
            $top3 = array_slice($avgScores, 0, 3, true);
            
            echo json_encode([
                'status' => 'success',
                'period' => '7 days',
                'data' => $formatted,
                'recommendations' => [
                    'best_hours' => array_keys($top3),
                    'scores' => $top3,
                    'summary' => 'Basé sur les données des 7 derniers jours'
                ]
            ]);
            break;
            
        case 'heatmap':
            // Matrice 24h × 7 jours
            $query = "
                SELECT 
                    EXTRACT(DOW FROM send_date) as day_of_week,
                    EXTRACT(HOUR FROM send_date) as hour_of_day,
                    ROUND(AVG(CASE WHEN status = 'inbox' THEN 1 ELSE 0 END) * 100, 2) as inbox_rate
                FROM admin.unified_send_log
                WHERE send_date >= NOW() - INTERVAL '30 days'
                GROUP BY EXTRACT(DOW FROM send_date), EXTRACT(HOUR FROM send_date)
                ORDER BY day_of_week, hour_of_day
            ";
            
            $stmt = $pdo->query($query);
            $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            // Créer la matrice 7x24
            $heatmap = [];
            $days = ['Dim', 'Lun', 'Mar', 'Mer', 'Jeu', 'Ven', 'Sam'];
            
            // Initialiser avec des zéros
            for ($day = 0; $day < 7; $day++) {
                $heatmap[$days[$day]] = [];
                for ($hour = 0; $hour < 24; $hour++) {
                    $heatmap[$days[$day]][str_pad($hour, 2, '0', STR_PAD_LEFT) . ':00'] = 0;
                }
            }
            
            // Remplir avec les données
            foreach ($data as $row) {
                $dayIndex = (int)$row['day_of_week'];
                $hour = str_pad($row['hour_of_day'], 2, '0', STR_PAD_LEFT) . ':00';
                if ($dayIndex >= 0 && $dayIndex < 7) {
                    $heatmap[$days[$dayIndex]][$hour] = (float)$row['inbox_rate'];
                }
            }
            
            // Trouver les meilleurs moments
            $bestTimes = [];
            foreach ($heatmap as $day => $hours) {
                arsort($hours);
                $bestHour = key($hours);
                $bestTimes[$day] = [
                    'hour' => $bestHour,
                    'inbox_rate' => $hours[$bestHour]
                ];
            }
            
            // Trier par taux d'inbox
            uasort($bestTimes, function($a, $b) {
                return $b['inbox_rate'] <=> $a['inbox_rate'];
            });
            
            echo json_encode([
                'status' => 'success',
                'period' => '30 days',
                'heatmap' => $heatmap,
                'best_times' => array_slice($bestTimes, 0, 3)
            ]);
            break;
            
        case 'recommend':
            $isp = $_GET['isp'] ?? 'all';
            
            $query = "
                SELECT 
                    EXTRACT(HOUR FROM send_date) as hour,
                    COUNT(*) as volume,
                    ROUND(AVG(CASE WHEN status = 'inbox' THEN 1 ELSE 0 END) * 100, 2) as inbox_rate
                FROM admin.unified_send_log
                WHERE send_date >= NOW() - INTERVAL '14 days'
                " . ($isp !== 'all' ? " AND isp = :isp" : "") . "
                GROUP BY EXTRACT(HOUR FROM send_date)
                ORDER BY inbox_rate DESC
                LIMIT 5
            ";
            
            $stmt = $pdo->prepare($query);
            if ($isp !== 'all') {
                $stmt->bindParam(':isp', $isp);
            }
            $stmt->execute();
            $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
            
            $recommendations = [];
            foreach ($data as $row) {
                $confidence = 'low';
                $volume = (int)$row['volume'];
                if ($volume >= 1000) $confidence = 'high';
                elseif ($volume >= 100) $confidence = 'medium';
                
                $recommendations[] = [
                    'hour' => str_pad($row['hour'], 2, '0', STR_PAD_LEFT) . ':00',
                    'inbox_rate' => (float)$row['inbox_rate'],
                    'volume' => $volume,
                    'confidence' => $confidence
                ];
            }
            
            echo json_encode([
                'status' => 'success',
                'isp' => $isp,
                'period' => '14 days',
                'recommendations' => $recommendations,
                'best_overall' => $recommendations[0] ?? null
            ]);
            break;
            
        default:
            echo json_encode([
                'status' => 'error',
                'message' => 'Action non reconnue',
                'available_actions' => ['windows', 'heatmap', 'recommend'],
                'usage' => [
                    'GET ?action=windows' => 'Analyse par heure',
                    'GET ?action=heatmap' => 'Matrice 24h×7j',
                    'GET ?action=recommend&isp=gmail' => 'Meilleures heures pour un ISP'
                ]
            ]);
    }
    
} catch (PDOException $e) {
    // Générer un heatmap d'exemple en cas d'erreur
    $sample = [];
    $days = ['Dim', 'Lun', 'Mar', 'Mer', 'Jeu', 'Ven', 'Sam'];
    
    foreach ($days as $day) {
        for ($hour = 0; $hour < 24; $hour++) {
            $time = str_pad($hour, 2, '0', STR_PAD_LEFT) . ':00';
            $sample[$day][$time] = rand(70, 95);
        }
    }
    
    echo json_encode([
        'status' => 'error',
        'message' => 'Erreur base de données: ' . $e->getMessage(),
        'fallback' => [
            'status' => 'fallback_mode',
            'heatmap_sample' => $sample
        ]
    ]);
}
?>