<?php
/**
 * Debug script for frequency capping analysis
 * v2 - Handles missing table creation
 */
require_once '../config.php';

header('Content-Type: text/html; charset=utf-8');

echo "<!DOCTYPE html><html><head><title>Debug: Frequency Capping</title>";
echo "<style>body{font-family:monospace;max-width:900px;margin:20px auto;padding:20px;background:#1a1a2e;color:#eee;}";
echo "table{width:100%;border-collapse:collapse;margin:10px 0;font-size:11px;}";
echo "th,td{padding:6px;border:1px solid #333;text-align:left;}th{background:#16213e;}";
echo ".section{background:#0f3460;padding:15px;border-radius:8px;margin:15px 0;}";
echo ".error{color:#ff6b6b;font-weight:bold;}.success{color:#4ecdc4;}.warn{color:#ffe66d;}";
echo "button{background:#e94560;color:white;padding:12px 24px;border:none;border-radius:4px;cursor:pointer;font-weight:bold;font-size:14px;}";
echo "button:hover{background:#ff2e63;}";
echo "h2{color:#e94560;margin-top:0;}";
echo ".alert{background:#3a0e18;border:1px solid #e94560;padding:15px;border-radius:6px;margin:10px 0;}</style></head><body>";

echo "<h1>🔍 Debug: Frequency Capping</h1>";

// Handle Actions
if (isset($_POST['create_table'])) {
    try {
        $pdo->exec("
            CREATE TABLE IF NOT EXISTS server_ads_stats (
                id INT AUTO_INCREMENT PRIMARY KEY,
                ad_id INT NOT NULL,
                ip_address VARCHAR(45) NOT NULL,
                action_type ENUM('view', 'click') NOT NULL DEFAULT 'view',
                viewed_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                UNIQUE KEY unique_view (ad_id, ip_address, action_type),
                KEY idx_ad_id (ad_id),
                KEY idx_viewed_at (viewed_at),
                FOREIGN KEY (ad_id) REFERENCES server_ads(id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ");
        echo "<div class='section success'>✅ Table <code>server_ads_stats</code> créée avec succès !</div>";
    } catch (PDOException $e) {
        echo "<div class='section error'>❌ Erreur création: " . htmlspecialchars($e->getMessage()) . "</div>";
    }
}

if (isset($_POST['fix_constraint'])) {
    try {
         // Delete duplicates first
        $pdo->exec("DELETE t1 FROM server_ads_stats t1 INNER JOIN server_ads_stats t2 WHERE t1.id < t2.id AND t1.ad_id = t2.ad_id AND t1.ip_address = t2.ip_address AND t1.action_type = t2.action_type");
        // Add Unique Key
        $pdo->exec("ALTER TABLE server_ads_stats ADD UNIQUE KEY unique_view (ad_id, ip_address, action_type)");
        echo "<div class='section success'>✅ Contrainte UNIQUE ajoutée !</div>";
    } catch (PDOException $e) {
        echo "<div class='section error'>❌ Erreur correction: " . htmlspecialchars($e->getMessage()) . "</div>";
    }
}

// 1. Check server_ads_stats table structure
echo "<div class='section'><h2>1. Structure de server_ads_stats</h2>";
try {
    $desc = $pdo->query("DESCRIBE server_ads_stats")->fetchAll();
    echo "<table><tr><th>Colonne</th><th>Type</th><th>Null</th><th>Key</th><th>Default</th></tr>";
    foreach ($desc as $col) {
        $keyHighlight = $col['Key'] ? "class='success'" : "";
        echo "<tr><td>{$col['Field']}</td><td>{$col['Type']}</td><td>{$col['Null']}</td><td $keyHighlight>{$col['Key']}</td><td>{$col['Default']}</td></tr>";
    }
    echo "</table>";
    
    // Check indexes
    echo "<h3>Index/Contraintes:</h3>";
    $indexes = $pdo->query("SHOW INDEX FROM server_ads_stats")->fetchAll();
    echo "<table><tr><th>Key_name</th><th>Column_name</th><th>Non_unique</th></tr>";
    foreach ($indexes as $idx) {
        $unique = $idx['Non_unique'] == 0 ? "<span class='success'>UNIQUE</span>" : "Non-unique";
        echo "<tr><td>{$idx['Key_name']}</td><td>{$idx['Column_name']}</td><td>{$unique}</td></tr>";
    }
    echo "</table>";
    
    $tableExists = true;
} catch (PDOException $e) {
    $tableExists = false;
    echo "<div class='alert'>";
    echo "<p class='error'>🚨 TABLE MANQUANTE: " . htmlspecialchars($e->getMessage()) . "</p>";
    echo "<p>La table nécessaire au stockage des vues n'existe pas. C'est pour cela que le frequency capping ne fonctionne pas.</p>";
    echo "<form method='post'><button type='submit' name='create_table'>🛠️ CRÉER LA TABLE MAINTENANT</button></form>";
    echo "</div>";
}
echo "</div>";

if ($tableExists) {
    // 2. Current data
    echo "<div class='section'><h2>2. Données (20 dernières vues)</h2>";
    $stats = $pdo->query("SELECT * FROM server_ads_stats ORDER BY viewed_at DESC LIMIT 20")->fetchAll();
    if (count($stats) > 0) {
        echo "<table><tr><th>ID</th><th>Ad ID</th><th>IP</th><th>Type</th><th>Viewed At</th></tr>";
        foreach ($stats as $s) {
            echo "<tr><td>{$s['id']}</td><td>{$s['ad_id']}</td><td>{$s['ip_address']}</td><td>{$s['action_type']}</td><td>{$s['viewed_at']}</td></tr>";
        }
        echo "</table>";
    } else {
        echo "<p class='warn'>Aucune donnée - Attendez qu'une pub soit affichée.</p>";
    }
    echo "</div>";
    
    // 3. Test Query
    echo "<div class='section'><h2>3. Test Simulation</h2>";
    $testAdId = $pdo->query("SELECT id FROM server_ads LIMIT 1")->fetchColumn();
    $clientIP = $_SERVER['REMOTE_ADDR'] ?? '127.0.0.1';
    
    if ($testAdId) {
        echo "<p>Test sur Ad ID: <strong>{$testAdId}</strong> | Votre IP: <strong>{$clientIP}</strong></p>";
        
        $sql = "SELECT COUNT(*) FROM server_ads_stats WHERE ad_id = ? AND ip_address = ? AND action_type = 'view' AND viewed_at > DATE_SUB(NOW(), INTERVAL 1 HOUR)";
        $checkStmt = $pdo->prepare($sql);
        
        if (!$checkStmt->execute([$testAdId, $clientIP])) {
            $err = $checkStmt->errorInfo();
            echo "<p class='error'>❌ Erreur SQL: " . htmlspecialchars($err[2]) . "</p>";
        } else {
            $result = $checkStmt->fetch();
            if ($result === false) {
                echo "<p class='error'>❌ Erreur Fetch</p>";
            } else {
                echo "<p>Vues < 1h: <strong class='" . ($result[0] > 0 ? 'success' : 'warn') . "'>{$result[0]}</strong></p>";
                
                if ($result[0] > 0) {
                    echo "<p class='success'>✅ Le Capping fonctionne: Pub bloquée pour cet IP.</p>";
                } else {
                     echo "<p class='warn'>⚠️ Pub visible (Pas de vue récente enregistrée ou délai écoulé).</p>";
                }
            }
        }
    }
    echo "</div>";
    
    // 4. Constraint Check
    $hasUnique = false;
    foreach ($indexes as $idx) {
        if ($idx['Key_name'] == 'unique_view') $hasUnique = true;
    }
    
    if (!$hasUnique) {
        echo "<div class='alert'>";
        echo "<p class='error'>🚨 MANQUE CONTRAINTE UNIQUE</p>";
        echo "<form method='post'><button type='submit' name='fix_constraint'>🔧 AJOUTER CONTRAINTE UNIQUE</button></form>";
        echo "</div>";
    }
}

echo "<div style='text-align:center;margin-top:30px;'><a href='../admin/advertising.php' style='color:#4ecdc4;'>← Retour Admin</a></div>";
echo "</body></html>";
