<?php
require_once 'database_config.php';

class EmailQueueAdmin {
    private $pdo;
    
    public function __construct() {
        $this->pdo = DatabaseConfig::getConnection();
    }
    
    public function showStats() {
        echo "\n=== ESTADÍSTICAS DE LA COLA DE EMAILS ===\n";
        
        // Estadísticas generales
        $sql = "SELECT 
                    status,
                    COUNT(*) as count
                FROM email_queue 
                GROUP BY status";
        
        $stmt = $this->pdo->query($sql);
        $generalStats = $stmt->fetchAll();
        
        echo "\nEstado general:\n";
        echo "---------------\n";
        foreach ($generalStats as $stat) {
            echo sprintf("%-12s: %s\n", ucfirst($stat['status']), $stat['count']);
        }
        
        // Estadísticas por categoría
        $sql = "SELECT 
                    race_category,
                    status,
                    COUNT(*) as count
                FROM email_queue 
                GROUP BY race_category, status
                ORDER BY race_category, status";
        
        $stmt = $this->pdo->query($sql);
        $categoryStats = $stmt->fetchAll();
        
        echo "\nPor categoría:\n";
        echo "-------------\n";
        $currentCategory = '';
        foreach ($categoryStats as $stat) {
            if ($currentCategory !== $stat['race_category']) {
                $currentCategory = $stat['race_category'];
                echo "\n{$currentCategory}:\n";
            }
            echo sprintf("  %-10s: %s\n", ucfirst($stat['status']), $stat['count']);
        }
        
        // Emails fallidos con errores
        $sql = "SELECT 
                    runner_name,
                    email,
                    race_category,
                    attempts,
                    error_message
                FROM email_queue 
                WHERE status = 'failed' 
                ORDER BY attempts DESC, last_attempt_at DESC
                LIMIT 10";
        
        $stmt = $this->pdo->query($sql);
        $failedEmails = $stmt->fetchAll();
        
        if (!empty($failedEmails)) {
            echo "\nÚltimos emails fallidos:\n";
            echo "------------------------\n";
            foreach ($failedEmails as $failed) {
                echo sprintf("%-30s %-30s %s (intentos: %d)\n", 
                    substr($failed['runner_name'], 0, 30),
                    substr($failed['email'], 0, 30),
                    $failed['race_category'],
                    $failed['attempts']
                );
                if ($failed['error_message']) {
                    echo "  Error: " . substr($failed['error_message'], 0, 80) . "\n";
                }
            }
        }
        
        // Log de procesos recientes
        $sql = "SELECT * FROM email_batch_log ORDER BY batch_date DESC LIMIT 5";
        $stmt = $this->pdo->query($sql);
        $recentLogs = $stmt->fetchAll();
        
        if (!empty($recentLogs)) {
            echo "\nProcesos recientes:\n";
            echo "-------------------\n";
            foreach ($recentLogs as $log) {
                echo sprintf("%s | Enviados: %d | Fallidos: %d | Tiempo: %ss\n",
                    $log['batch_date'],
                    $log['total_sent'],
                    $log['total_failed'],
                    $log['execution_time']
                );
                if ($log['notes']) {
                    echo "  Notas: {$log['notes']}\n";
                }
            }
        }
    }
    
    public function resetFailedEmails() {
        $sql = "UPDATE email_queue 
                SET status = 'pending', attempts = 0, error_message = NULL, last_attempt_at = NULL 
                WHERE status = 'failed'";
        
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute();
        
        $resetCount = $stmt->rowCount();
        echo "Se han reiniciado $resetCount emails fallidos a estado 'pending'.\n";
        
        return $resetCount;
    }
    
    public function clearQueue($status = null) {
        if ($status) {
            $sql = "DELETE FROM email_queue WHERE status = ?";
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute([$status]);
            $deletedCount = $stmt->rowCount();
            echo "Se han eliminado $deletedCount emails con estado '$status'.\n";
        } else {
            $sql = "DELETE FROM email_queue";
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute();
            $deletedCount = $stmt->rowCount();
            echo "Se han eliminado $deletedCount emails de la cola.\n";
        }
        
        return $deletedCount;
    }
    
    public function addTestEmail($email, $name = 'Usuario de Prueba') {
        $sql = "INSERT INTO email_queue 
                (runner_id, runner_name, email, race_category, bib_number, position, finish_time, gun_time) 
                VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
        
        $stmt = $this->pdo->prepare($sql);
        $result = $stmt->execute([
            'test_' . uniqid(),
            $name,
            $email,
            'Prueba',
            999,
            1,
            '00:30:00',
            '00:30:00'
        ]);
        
        if ($result) {
            echo "Email de prueba añadido para: $email\n";
        } else {
            echo "Error al añadir email de prueba.\n";
        }
        
        return $result;
    }
    
    public function exportFailedEmails($filename = null) {
        if (!$filename) {
            $filename = 'failed_emails_' . date('Y-m-d_H-i-s') . '.csv';
        }
        
        $sql = "SELECT 
                    runner_name,
                    email,
                    race_category,
                    bib_number,
                    position,
                    attempts,
                    error_message,
                    last_attempt_at
                FROM email_queue 
                WHERE status = 'failed'
                ORDER BY race_category, position";
        
        $stmt = $this->pdo->query($sql);
        $failedEmails = $stmt->fetchAll();
        
        if (empty($failedEmails)) {
            echo "No hay emails fallidos para exportar.\n";
            return false;
        }
        
        $fp = fopen($filename, 'w');
        
        // Encabezados
        fputcsv($fp, [
            'Nombre',
            'Email',
            'Categoría',
            'Número',
            'Posición',
            'Intentos',
            'Error',
            'Último Intento'
        ]);
        
        // Datos
        foreach ($failedEmails as $email) {
            fputcsv($fp, [
                $email['runner_name'],
                $email['email'],
                $email['race_category'],
                $email['bib_number'],
                $email['position'],
                $email['attempts'],
                $email['error_message'],
                $email['last_attempt_at']
            ]);
        }
        
        fclose($fp);
        
        echo "Exportados " . count($failedEmails) . " emails fallidos a: $filename\n";
        return true;
    }
}

// Función de ayuda
function showHelp() {
    echo "\nUso: php email_queue_admin.php [comando] [opciones]\n\n";
    echo "Comandos disponibles:\n";
    echo "  stats              - Mostrar estadísticas de la cola\n";
    echo "  reset-failed       - Reiniciar emails fallidos a estado 'pending'\n";
    echo "  clear [status]     - Eliminar emails (todos o por estado)\n";
    echo "  test-email [email] - Añadir email de prueba\n";
    echo "  export-failed      - Exportar emails fallidos a CSV\n";
    echo "  help               - Mostrar esta ayuda\n\n";
    echo "Ejemplos:\n";
    echo "  php email_queue_admin.php stats\n";
    echo "  php email_queue_admin.php clear failed\n";
    echo "  php email_queue_admin.php test-email test@example.com\n";
}

// Ejecución del script
if (basename(__FILE__) == basename($_SERVER['SCRIPT_FILENAME'])) {
    try {
        $admin = new EmailQueueAdmin();
        $command = $argv[1] ?? 'help';
        
        switch ($command) {
            case 'stats':
                $admin->showStats();
                break;
                
            case 'reset-failed':
                $admin->resetFailedEmails();
                break;
                
            case 'clear':
                $status = $argv[2] ?? null;
                if ($status && !in_array($status, ['pending', 'sent', 'failed'])) {
                    echo "Estado inválido. Use: pending, sent o failed\n";
                    exit(1);
                }
                $admin->clearQueue($status);
                break;
                
            case 'test-email':
                $email = $argv[2] ?? null;
                if (!$email) {
                    echo "Debe proporcionar un email: php email_queue_admin.php test-email email@example.com\n";
                    exit(1);
                }
                $name = $argv[3] ?? 'Usuario de Prueba';
                $admin->addTestEmail($email, $name);
                break;
                
            case 'export-failed':
                $filename = $argv[2] ?? null;
                $admin->exportFailedEmails($filename);
                break;
                
            case 'help':
            default:
                showHelp();
                break;
        }
        
    } catch (Exception $e) {
        echo "Error: " . $e->getMessage() . "\n";
        exit(1);
    }
}
?>
