<?php
/**
 * API do Admin - Dashboard e Dados
 * Timezone: America/Sao_Paulo
 */

date_default_timezone_set('America/Sao_Paulo');

require_once __DIR__ . '/require_auth.php';

header('Content-Type: application/json');

try {
    // --- Database Connection ---
    $dbPath = __DIR__ . '/admin.db';
    $pdo = new PDO('sqlite:' . $dbPath);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // Configurações para evitar perda de dados
    $pdo->exec('PRAGMA journal_mode = WAL');
    $pdo->exec('PRAGMA busy_timeout = 10000');
    $pdo->exec('PRAGMA synchronous = NORMAL');
    $pdo->exec('PRAGMA cache_size = -64000');
    
    // Adicionar colunas se não existirem
    try { $pdo->exec("ALTER TABLE events ADD COLUMN placa TEXT"); } catch (Exception $e) {}
    try { $pdo->exec("ALTER TABLE events ADD COLUMN renavam TEXT"); } catch (Exception $e) {}
    try { $pdo->exec("ALTER TABLE events ADD COLUMN valor REAL"); } catch (Exception $e) {}
    try { $pdo->exec("ALTER TABLE events ADD COLUMN geo_city TEXT"); } catch (Exception $e) {}
    try { $pdo->exec("ALTER TABLE events ADD COLUMN geo_region TEXT"); } catch (Exception $e) {}
    try { $pdo->exec("ALTER TABLE events ADD COLUMN geo_country TEXT"); } catch (Exception $e) {}
    try { $pdo->exec("ALTER TABLE events ADD COLUMN geo_isp TEXT"); } catch (Exception $e) {}

    // --- Fetch Stats with Daily Change ---
    $stats = [];
    
    // Mapeamento de tipos de eventos (agrupa variações)
    $eventTypeGroups = [
        'visit' => ['visit', 'page_view', 'page_load'],
        'cnpj_consulta' => ['cnpj_consulta', 'consulta', 'cnpj_consultation_start'],
        'pix_gerado' => ['pix_gerado', 'pix_generation_success', 'pix_generated'],
        'pix_pago' => ['pix_pago', 'pix_paid']
    ];
    
    $today = date('Y-m-d');
    $yesterday = date('Y-m-d', strtotime('-1 day'));

    foreach ($eventTypeGroups as $groupName => $types) {
        // Criar placeholders para IN clause
        $placeholders = str_repeat('?,', count($types) - 1) . '?';
        
        // Total
        $stmtTotal = $pdo->prepare("SELECT COUNT(*) FROM events WHERE event_type IN ($placeholders)");
        $stmtTotal->execute($types);
        $total = $stmtTotal->fetchColumn();

        // Today's count
        $stmtToday = $pdo->prepare("SELECT COUNT(*) FROM events WHERE event_type IN ($placeholders) AND DATE(event_timestamp) = ?");
        $stmtToday->execute(array_merge($types, [$today]));
        $todayCount = $stmtToday->fetchColumn();

        // Yesterday's count
        $stmtYesterday = $pdo->prepare("SELECT COUNT(*) FROM events WHERE event_type IN ($placeholders) AND DATE(event_timestamp) = ?");
        $stmtYesterday->execute(array_merge($types, [$yesterday]));
        $yesterdayCount = $stmtYesterday->fetchColumn();

        // Calculate percentage change
        $change = 0;
        if ($yesterdayCount > 0) {
            $change = (($todayCount - $yesterdayCount) / $yesterdayCount) * 100;
        } elseif ($todayCount > 0) {
            $change = 100; // From 0 to >0 is a 100% increase for simplicity
        }

        $stats[$groupName] = [
            'total' => $total,
            'today' => $todayCount,
            'change' => round($change)
        ];
    }

    // --- Estatísticas de Valores PIX ---
    $pixValueStats = [];
    
    // Total de valores PIX gerados (usando json_extract para SQLite)
    $stmtPixValues = $pdo->prepare("
        SELECT 
            COUNT(*) as total_pix,
            SUM(CAST(json_extract(metadata, '$.valor') AS REAL)) as valor_total
        FROM events 
        WHERE event_type IN ('pix_gerado', 'pix_generation_success', 'pix_generated')
    ");
    $stmtPixValues->execute();
    $pixTotalData = $stmtPixValues->fetch(PDO::FETCH_ASSOC);
    
    // PIX gerados hoje
    $stmtPixValuesToday = $pdo->prepare("
        SELECT 
            COUNT(*) as total_pix,
            SUM(CAST(json_extract(metadata, '$.valor') AS REAL)) as valor_total
        FROM events 
        WHERE event_type IN ('pix_gerado', 'pix_generation_success', 'pix_generated')
        AND DATE(event_timestamp) = ?
    ");
    $stmtPixValuesToday->execute([$today]);
    $pixTodayData = $stmtPixValuesToday->fetch(PDO::FETCH_ASSOC);
    
    // Códigos PIX copiados
    $stmtPixCopied = $pdo->prepare("
        SELECT COUNT(*) FROM events 
        WHERE event_type = 'pix_codigo_copiado'
    ");
    $stmtPixCopied->execute();
    $pixCopiedTotal = $stmtPixCopied->fetchColumn();
    
    $stmtPixCopiedToday = $pdo->prepare("
        SELECT COUNT(*) FROM events 
        WHERE event_type = 'pix_codigo_copiado' AND DATE(event_timestamp) = ?
    ");
    $stmtPixCopiedToday->execute([$today]);
    $pixCopiedToday = $stmtPixCopiedToday->fetchColumn();
    
    $pixValueStats = [
        'valor_total_gerado' => round(floatval($pixTotalData['valor_total'] ?? 0), 2),
        'valor_hoje_gerado' => round(floatval($pixTodayData['valor_total'] ?? 0), 2),
        'total_codigos_copiados' => intval($pixCopiedTotal),
        'codigos_copiados_hoje' => intval($pixCopiedToday)
    ];

    // --- Fetch Recent Events with Placa, Renavam and Geolocation ---
    $recentEventsStmt = $pdo->query("
        SELECT 
            event_type, 
            event_timestamp, 
            metadata,
            placa,
            renavam,
            valor,
            ip_address,
            geo_city,
            geo_region,
            geo_country,
            geo_isp
        FROM events 
        ORDER BY event_timestamp DESC 
        LIMIT 100
    ");
    $recentEvents = $recentEventsStmt->fetchAll(PDO::FETCH_ASSOC);

    // --- Fetch UTM Sources ---
    $utmSourcesStmt = $pdo->query("
        SELECT json_extract(metadata, '$.utm.source') as utm_source, COUNT(*) as count
        FROM events
        WHERE json_extract(metadata, '$.utm.source') IS NOT NULL
        GROUP BY utm_source
        ORDER BY count DESC
        LIMIT 10
    ");
    $utmSources = $utmSourcesStmt->fetchAll(PDO::FETCH_ASSOC);

    // --- Fetch PIX Config History ---
    $pixHistory = [];
    try {
        $pixHistoryStmt = $pdo->query("
            SELECT 
                id,
                change_type,
                pix_key_old as old_pix_key,
                pix_key_new as new_pix_key,
                receiver_name_old as old_receiver_name,
                receiver_name_new as new_receiver_name,
                receiver_city_old as old_receiver_city,
                receiver_city_new as new_receiver_city,
                changed_by_ip,
                changed_at
            FROM pix_config_history 
            ORDER BY changed_at DESC 
            LIMIT 50
        ");
        $pixHistory = $pixHistoryStmt->fetchAll(PDO::FETCH_ASSOC);
    } catch (Exception $e) {
        // Tabela pode não existir ainda
    }

    // --- Combine Data ---
    $response = [
        'stats' => $stats,
        'pix_values' => $pixValueStats,
        'events' => $recentEvents,
        'utm_sources' => $utmSources,
        'pix_history' => $pixHistory,
        'server_time' => date('Y-m-d H:i:s'),
        'timezone' => 'America/Sao_Paulo'
    ];

    echo json_encode($response);

} catch (Exception $e) {
    http_response_code(500);
    echo json_encode(['status' => 'error', 'message' => 'Could not connect to database.', 'error' => $e->getMessage()]);
}
?>
