<?php
// includes/init-pages-visitor-system.php
// Script para inicializar o novo sistema de tracking de visitantes

require_once 'config.php';
require_once 'db.php';

class PagesVisitorSystemInit {
    private $db;
    
    public function __construct() {
        global $db;
        $this->db = $db;
    }
    
    /**
     * Verifica se as tabelas necessárias existem
     */
    public function checkRequiredTables() {
        $requiredTables = [
            'pages_visitor',
            'visitor_sessions', 
            'visitor_stats',
            'visitor_conversions'
        ];
        
        $existingTables = [];
        
        foreach ($requiredTables as $table) {
            try {
                $this->db->query("SHOW TABLES LIKE '{$table}'");
                $result = $this->db->single();
                if ($result) {
                    $existingTables[] = $table;
                }
            } catch (Exception $e) {
                error_log("Erro ao verificar tabela {$table}: " . $e->getMessage());
            }
        }
        
        return [
            'required' => $requiredTables,
            'existing' => $existingTables,
            'missing' => array_diff($requiredTables, $existingTables),
            'all_exist' => count($existingTables) === count($requiredTables)
        ];
    }
    
    /**
     * Migra páginas existentes da tabela pages para pages_visitor
     */
    public function migrateExistingPages() {
        try {
            $this->db->beginTransaction();
            
            // Verifica se há páginas na tabela pages que não estão em pages_visitor
            $this->db->query("
                SELECT p.* 
                FROM pages p 
                LEFT JOIN pages_visitor pv ON p.id = pv.page_id 
                WHERE p.published = 1 AND pv.id IS NULL
            ");
            
            $pagesToMigrate = $this->db->resultSet();
            $migratedCount = 0;
            
            foreach ($pagesToMigrate as $page) {
                // Gera URL da página
                $pageUrl = '/' . ltrim($page['slug'], '/');
                
                // Verifica se já existe uma entrada com esta URL
                $this->db->query("SELECT id FROM pages_visitor WHERE page_url = :page_url");
                $this->db->bind(':page_url', $pageUrl);
                $existing = $this->db->single();
                
                if (!$existing) {
                    // Cria nova entrada em pages_visitor
                    $this->db->query("
                        INSERT INTO pages_visitor (
                            page_id, page_url, page_title, page_type, page_category,
                            auto_created, first_detected, last_activity, is_active
                        ) VALUES (
                            :page_id, :page_url, :page_title, :page_type, :page_category,
                            0, :created_at, :updated_at, 1
                        )
                    ");
                    
                    $this->db->bind(':page_id', $page['id']);
                    $this->db->bind(':page_url', $pageUrl);
                    $this->db->bind(':page_title', $page['title']);
                    $this->db->bind(':page_type', 'database');
                    $this->db->bind(':page_category', $page['type'] ?? 'page');
                    $this->db->bind(':created_at', $page['created_at'] ?? date('Y-m-d H:i:s'));
                    $this->db->bind(':updated_at', $page['updated_at'] ?? date('Y-m-d H:i:s'));
                    
                    $this->db->execute();
                    $migratedCount++;
                }
            }
            
            $this->db->commit();
            
            return [
                'success' => true,
                'migrated_count' => $migratedCount,
                'total_found' => count($pagesToMigrate)
            ];
            
        } catch (Exception $e) {
            $this->db->rollback();
            error_log("Erro na migração de páginas: " . $e->getMessage());
            
            return [
                'success' => false,
                'error' => $e->getMessage(),
                'migrated_count' => 0
            ];
        }
    }
    
    /**
     * Limpa dados antigos e otimiza as tabelas
     */
    public function optimizeTables() {
        try {
            $results = [];
            
            // Remove sessões de visitantes com mais de 60 dias
            $this->db->query("DELETE FROM visitor_sessions WHERE created_at < DATE_SUB(NOW(), INTERVAL 60 DAY)");
            $this->db->execute();
            $results['old_sessions_deleted'] = $this->db->rowCount();
            
            // Remove estatísticas com mais de 90 dias
            $this->db->query("DELETE FROM visitor_stats WHERE stat_date < DATE_SUB(NOW(), INTERVAL 90 DAY)");
            $this->db->execute();
            $results['old_stats_deleted'] = $this->db->rowCount();
            
            // Remove conversões com mais de 90 dias
            $this->db->query("DELETE FROM visitor_conversions WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY)");
            $this->db->execute();
            $results['old_conversions_deleted'] = $this->db->rowCount();
            
            // Atualiza contadores de visitantes únicos
            $this->db->query("
                UPDATE pages_visitor pv
                SET total_unique_visitors = (
                    SELECT COUNT(DISTINCT visitor_hash)
                    FROM visitor_sessions vs
                    WHERE vs.pages_visitor_id = pv.id
                )
            ");
            $this->db->execute();
            $results['counters_updated'] = $this->db->rowCount();
            
            // Otimiza tabelas
            $tables = ['pages_visitor', 'visitor_sessions', 'visitor_stats', 'visitor_conversions'];
            foreach ($tables as $table) {
                try {
                    $this->db->query("OPTIMIZE TABLE {$table}");
                    $this->db->execute();
                } catch (Exception $e) {
                    error_log("Erro ao otimizar tabela {$table}: " . $e->getMessage());
                }
            }
            
            return [
                'success' => true,
                'results' => $results
            ];
            
        } catch (Exception $e) {
            error_log("Erro na otimização das tabelas: " . $e->getMessage());
            
            return [
                'success' => false,
                'error' => $e->getMessage()
            ];
        }
    }
    
    /**
     * Gera relatório do status do sistema
     */
    public function getSystemStatus() {
        try {
            $status = [];
            
            // Verifica tabelas
            $tableStatus = $this->checkRequiredTables();
            $status['tables'] = $tableStatus;
            
            // Conta registros nas tabelas principais
            if ($tableStatus['all_exist']) {
                // Pages visitor
                $this->db->query("SELECT COUNT(*) as count FROM pages_visitor WHERE is_active = 1");
                $result = $this->db->single();
                $status['active_pages'] = $result['count'] ?? 0;
                
                // Sessões de visitantes (últimos 30 dias)
                $this->db->query("SELECT COUNT(*) as count FROM visitor_sessions WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)");
                $result = $this->db->single();
                $status['recent_sessions'] = $result['count'] ?? 0;
                
                // Visitantes únicos (últimos 30 dias)
                $this->db->query("SELECT COUNT(DISTINCT visitor_hash) as count FROM visitor_sessions WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)");
                $result = $this->db->single();
                $status['unique_visitors_30d'] = $result['count'] ?? 0;
                
                // Conversões (últimos 30 dias)
                $this->db->query("SELECT COUNT(*) as count FROM visitor_conversions WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)");
                $result = $this->db->single();
                $status['conversions_30d'] = $result['count'] ?? 0;
                
                // Páginas mais visitadas (últimas 24h)
                $this->db->query("
                    SELECT pv.page_title, COUNT(DISTINCT vs.visitor_hash) as visitors
                    FROM pages_visitor pv
                    INNER JOIN visitor_sessions vs ON pv.id = vs.pages_visitor_id
                    WHERE vs.created_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
                    GROUP BY pv.id, pv.page_title
                    ORDER BY visitors DESC
                    LIMIT 5
                ");
                $status['top_pages_24h'] = $this->db->resultSet();
                
                // Top sources UTM
                $this->db->query("
                    SELECT utm_source, COUNT(DISTINCT visitor_hash) as visitors
                    FROM visitor_sessions
                    WHERE utm_source IS NOT NULL 
                    AND utm_source != ''
                    AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
                    GROUP BY utm_source
                    ORDER BY visitors DESC
                    LIMIT 5
                ");
                $status['top_utm_sources'] = $this->db->resultSet();
            }
            
            $status['system_healthy'] = $tableStatus['all_exist'];
            $status['last_check'] = date('Y-m-d H:i:s');
            
            return $status;
            
        } catch (Exception $e) {
            error_log("Erro ao gerar status do sistema: " . $e->getMessage());
            
            return [
                'system_healthy' => false,
                'error' => $e->getMessage(),
                'last_check' => date('Y-m-d H:i:s')
            ];
        }
    }
}

// Função de inicialização rápida para usar em outros scripts
function initPagesVisitorSystem() {
    $init = new PagesVisitorSystemInit();
    
    // Verifica se as tabelas existem
    $tableStatus = $init->checkRequiredTables();
    
    if (!$tableStatus['all_exist']) {
        error_log("Sistema pages_visitor não está completamente configurado. Tabelas faltando: " . implode(', ', $tableStatus['missing']));
        return false;
    }
    
    // Migra páginas existentes se necessário (executa apenas ocasionalmente)
    if (rand(1, 100) <= 5) { // 5% de chance
        $migration = $init->migrateExistingPages();
        if ($migration['migrated_count'] > 0) {
            error_log("Migradas {$migration['migrated_count']} páginas para o sistema pages_visitor");
        }
    }
    
    return true;
}

// Se for executado diretamente, mostra o status
if (basename($_SERVER['PHP_SELF']) === basename(__FILE__)) {
    header('Content-Type: application/json');
    
    $init = new PagesVisitorSystemInit();
    $status = $init->getSystemStatus();
    
    echo json_encode($status, JSON_PRETTY_PRINT);
}
?>