<?php
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);
    
    date_default_timezone_set('America/Sao_Paulo');
    
    // --- Get Action ---
    $action = $_GET['action'] ?? 'default';

    // --- Handle DELETE request: Clear all events data ---
    if ($_SERVER['REQUEST_METHOD'] === 'DELETE') {
        $pdo->exec("DELETE FROM events");
        $pdo->exec("DELETE FROM sqlite_sequence WHERE name='events'");
        
        echo json_encode([
            'status' => 'success',
            'message' => 'Todos os dados foram apagados com sucesso.'
        ]);
        exit;
    }
    
    // --- Action: Get Sessions ---
    if ($action === 'sessions') {
        $page = max(1, (int)($_GET['page'] ?? 1));
        $limit = min(20, (int)($_GET['limit'] ?? 20));
        $filter = $_GET['filter'] ?? '';
        $pixMode = $_GET['pix_mode'] ?? '';
        $offset = ($page - 1) * $limit;
        
        // Adicionar coluna session_id se não existir
        try {
            $pdo->exec("ALTER TABLE events ADD COLUMN session_id TEXT");
        } catch (Exception $e) {
            // Coluna já existe
        }
        
        // Atualizar eventos antigos que não têm session_id (agrupa por IP + hora)
        $pdo->exec("
            UPDATE events 
            SET session_id = 'legacy_' || substr(ip_address || strftime('%Y%m%d%H', event_timestamp), 1, 24)
            WHERE session_id IS NULL AND ip_address IS NOT NULL
        ");
        
        // Buscar sessões da coluna session_id OU do JSON (retrocompat)
        $sessionsQuery = "
            SELECT 
                COALESCE(session_id, json_extract(metadata, '\$.session_id'), 'unknown_' || id) as session_id,
                MIN(event_timestamp) as started_at,
                MAX(event_timestamp) as last_activity,
                COUNT(*) as event_count,
                GROUP_CONCAT(DISTINCT event_type) as event_types,
                MAX(ip_address) as ip_address,
                MAX(renavam) as renavam,
                MAX(placa) as placa,
                MAX(CASE WHEN event_type IN ('renavam_consulta', 'consulta_iniciada', 'page_view') THEN 
                    COALESCE(renavam, json_extract(metadata, '\$.renavam'))
                END) as renavam_consulta,
                MAX(CASE WHEN event_type IN ('renavam_consulta', 'consulta_iniciada') THEN 
                    COALESCE(placa, json_extract(metadata, '\$.placa'))
                END) as placa_consulta,
                MAX(CASE WHEN event_type IN ('pix_gerado', 'pagamento_gerado', 'pix_generation_success') THEN 1 ELSE 0 END) as has_pix,
                MAX(CASE WHEN event_type IN ('pix_pago', 'pix_confirmado') THEN 1 ELSE 0 END) as has_payment,
                MAX(CASE WHEN event_type IN ('pix_gerado', 'pagamento_gerado') THEN 
                    COALESCE(valor, json_extract(metadata, '\$.valor'), json_extract(metadata, '\$.amount'))
                END) as pix_amount,
                MAX(CASE WHEN event_type IN ('pix_gerado', 'pagamento_gerado', 'pix_generation_success') THEN 
                    COALESCE(json_extract(metadata, '\$.pix_mode'), 'manual')
                END) as pix_mode
            FROM events 
            GROUP BY COALESCE(session_id, json_extract(metadata, '\$.session_id'), 'unknown_' || id)
            ORDER BY last_activity DESC
        ";
        
        $allSessions = $pdo->query($sessionsQuery)->fetchAll(PDO::FETCH_ASSOC);
        
        // Filtrar se necessário
        if ($filter === 'with_payment') {
            $allSessions = array_filter($allSessions, fn($s) => $s['has_payment'] == 1);
        } elseif ($filter === 'with_renavam') {
            $allSessions = array_filter($allSessions, fn($s) => !empty($s['renavam']) || !empty($s['renavam_consulta']));
        } elseif ($filter === 'with_pix') {
            $allSessions = array_filter($allSessions, fn($s) => $s['has_pix'] == 1);
        }
        
        // Filtrar por modo PIX
        if ($pixMode === 'auto') {
            $allSessions = array_filter($allSessions, fn($s) => $s['has_pix'] == 1 && ($s['pix_mode'] === 'auto' || $s['pix_mode'] === null));
        } elseif ($pixMode === 'manual') {
            $allSessions = array_filter($allSessions, fn($s) => $s['has_pix'] == 1 && $s['pix_mode'] === 'manual');
        }
        
        $allSessions = array_values($allSessions); // Reindex
        $total = count($allSessions);
        $sessions = array_slice($allSessions, $offset, $limit);
        
        echo json_encode([
            'status' => 'success',
            'data' => $sessions,
            'pagination' => [
                'page' => $page,
                'limit' => $limit,
                'total' => $total,
                'totalPages' => ceil($total / $limit)
            ]
        ]);
        exit;
    }
    
    // --- Action: Get Session Details ---
    if ($action === 'session-details') {
        $sessionId = $_GET['session_id'] ?? '';
        
        if (empty($sessionId)) {
            throw new Exception('Session ID não informado');
        }
        
        // Busca por coluna session_id OU por JSON metadata
        $stmt = $pdo->prepare("
            SELECT event_type, event_timestamp, metadata, ip_address
            FROM events 
            WHERE session_id = ? 
               OR json_extract(metadata, '\$.session_id') = ?
               OR (session_id IS NULL AND ? LIKE 'legacy_%' AND ip_address || strftime('%Y%m%d%H', event_timestamp) LIKE substr(?, 8))
            ORDER BY event_timestamp ASC
        ");
        $stmt->execute([$sessionId, $sessionId, $sessionId, $sessionId]);
        $events = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        echo json_encode([
            'status' => 'success',
            'data' => $events
        ]);
        exit;
    }
    
    // --- Action: Get Events (para gráficos) ---
    if ($action === 'events') {
        $limit = min(1000, (int)($_GET['limit'] ?? 500));
        
        $stmt = $pdo->prepare("
            SELECT event_type, event_timestamp, utm_source, utm_medium, utm_campaign
            FROM events 
            ORDER BY event_timestamp DESC
            LIMIT ?
        ");
        $stmt->execute([$limit]);
        $events = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // Calcular estatísticas por tipo para gráficos
        $byType = [];
        $byHour = [];
        foreach ($events as $event) {
            $type = $event['event_type'];
            $byType[$type] = ($byType[$type] ?? 0) + 1;
            
            $hour = date('H', strtotime($event['event_timestamp']));
            if (!isset($byHour[$hour])) $byHour[$hour] = [];
            $byHour[$hour][$type] = ($byHour[$hour][$type] ?? 0) + 1;
        }
        
        echo json_encode([
            'status' => 'success',
            'events' => $events,
            'byType' => $byType,
            'byHour' => $byHour
        ]);
        exit;
    }

    // --- Default: Fetch Stats with Daily Change ---
    $stats = [];
    
    // Mapeamento de tipos de eventos (inclui aliases)
    $eventMappings = [
        'visit' => ['visit', 'page_view', 'page_load'],
        'cnpj_consulta' => ['cnpj_consulta', 'cnpj_consultation_start'],
        'pix_gerado' => ['pix_gerado', 'pagamento_gerado', 'pix_generation_success'],
        'pix_pago' => ['pix_pago', 'payment_confirmed']
    ];
    
    $today = date('Y-m-d');
    $yesterday = date('Y-m-d', strtotime('-1 day'));

    foreach ($eventMappings as $key => $types) {
        $placeholders = implode(',', array_fill(0, count($types), '?'));
        
        // Total
        $stmtTotal = $pdo->prepare("SELECT COUNT(*) FROM events WHERE event_type IN ($placeholders)");
        $stmtTotal->execute($types);
        $total = (int)$stmtTotal->fetchColumn();

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

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

        $change = 0;
        if ($yesterdayCount > 0) {
            $change = (($todayCount - $yesterdayCount) / $yesterdayCount) * 100;
        } elseif ($todayCount > 0) {
            $change = 100;
        }

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

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

    // --- Combine Data ---
    $response = [
        'stats' => $stats,
        'utm_sources' => $utmSources
    ];

    echo json_encode($response);

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