<?php
    ob_start();
    // Incluir sistema de control de acceso
    require '../../backend/php/check_access.php';
    
    // Verificar permisos
    if(!hasModulePermission('ajustes') && !hasSubmenuPermission('ajustes_base_datos')){
        header('location: ../acceso_denegado.php');
        exit;
    }

    // Incluir archivo de conexión
    require '../../backend/bd/Conexion.php';

    // Definir los campos disponibles para reportes con sus nombres legibles
    $available_fields = [
        'sex' => 'Sexo',
        'modalidad' => 'Modalidad',
        'tsudadera' => 'Talla Sudadera',
        'tchaqueta' => 'Talla Chaqueta',
        'tzapatos' => 'Talla Zapatos',
        'ecivil' => 'Estado Civil',
        'zona' => 'Zona',
        'sisben' => 'Sisben',
        'pensionado' => 'Pensionado',
        'vivecon' => 'Vive Con',

        'poblaciond' => 'Población Discapacidad',
        'educacion' => 'Nivel Educativo',
        'leerut' => 'Lectura',
        'tecnologia' => 'Uso de Tecnología',
        'grup' => 'Grupo Sanguíneo'
    ];

    // Obtener las modalidades disponibles
    $modalidades_query = "SELECT DISTINCT modalidad FROM patients WHERE modalidad IS NOT NULL AND modalidad <> '' ORDER BY modalidad";
    $modalidades_stmt = $connect->prepare($modalidades_query);
    $modalidades_stmt->execute();
    $modalidades = $modalidades_stmt->fetchAll(PDO::FETCH_COLUMN);
    
    // Campo seleccionado (por defecto 'sex' si no se especifica)
    $selected_field = isset($_GET['field']) ? $_GET['field'] : 'sex';
    
    // Verificar que el campo seleccionado sea válido
    if (!array_key_exists($selected_field, $available_fields)) {
        $selected_field = 'sex'; // Campo por defecto si no es válido
    }

    // Procesar la exportación del reporte si se solicita
    if(isset($_GET['export_report'])) {
        // Consulta para obtener datos del campo seleccionado
        $query = "SELECT 
                    `$selected_field` as campo, 
                    COUNT(*) as cantidad 
                 FROM patients 
                 WHERE `$selected_field` IS NOT NULL AND `$selected_field` <> ''
                 GROUP BY `$selected_field` 
                 ORDER BY cantidad DESC";

        $stmt = $connect->prepare($query);
        $stmt->execute();

        $labels = [];
        $values = [];
        $total = 0;
        $data_table = [];

        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            if (empty($row['campo'])) {
                $row['campo'] = 'No especificado';
            }
            
            $labels[] = $row['campo'];
            $values[] = $row['cantidad'];
            $total += $row['cantidad'];
            $data_table[] = $row;
        }

        // Obtener fecha actual
        $fecha_actual = date('d/m/Y');
        $hora_actual = date('H:i:s');

        // Función para generar colores aleatorios pero consistentes
        function generateColors($count) {
            $colors = [];
            for ($i = 0; $i < $count; $i++) {
                // Genera un color basado en la posición para que sea consistente
                $hue = fmod($i * 137.5, 360); // Espaciamiento áureo en el círculo de colores
                $saturation = 70;
                $lightness = 60;
                
                // Convierte HSL a RGB y luego a formato hexadecimal
                $colors[] = "hsla($hue, $saturation%, $lightness%, 0.7)";
            }
            return $colors;
        }

        $backgroundColors = generateColors(count($labels));

        // Crear contenido HTML para imprimir
        $html_content = '<!DOCTYPE html>
        <html lang="es">
        <head>
            <meta charset="UTF-8">
            <title>Reporte de Usuarios por ' . $available_fields[$selected_field] . '</title>
            <script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/3.9.1/chart.min.js"></script>
            <style>
                body {
                    font-family: Arial, sans-serif;
                    margin: 0;
                    padding: 20px;
                }
                .report-header {
                    text-align: center;
                    margin-bottom: 30px;
                }
                .report-title {
                    font-size: 24px;
                    margin-bottom: 10px;
                }
                .report-subtitle {
                    font-size: 16px;
                    color: #666;
                }
                .report-info {
                    display: flex;
                    justify-content: space-between;
                    margin-bottom: 20px;
                }
                .report-date {
                    font-size: 14px;
                }
                .report-container {
                    display: flex;
                    flex-wrap: wrap;
                    justify-content: space-between;
                }
                .chart-container {
                    width: 48%;
                    margin-bottom: 30px;
                }
                table {
                    width: 100%;
                    border-collapse: collapse;
                    margin-bottom: 30px;
                }
                th, td {
                    padding: 12px 15px;
                    text-align: left;
                    border-bottom: 1px solid #ddd;
                }
                th {
                    background-color: #f2f2f2;
                    font-weight: bold;
                }
                .total-row {
                    font-weight: bold;
                    background-color: #f9f9f9;
                }
                @media print {
                    .no-print {
                        display: none;
                    }
                }
                .action-buttons {
                    display: flex;
                    justify-content: space-between;
                    margin-bottom: 20px;
                }
                .button {
                    padding: 10px 20px;
                    background-color: #4CAF50;
                    color: white;
                    border: none;
                    border-radius: 5px;
                    cursor: pointer;
                    font-size: 16px;
                    text-decoration: none;
                    display: inline-block;
                }
                .button-print {
                    background-color: #2196F3;
                }
                .button-back {
                    background-color: #555;
                }
                .form-select {
                    padding: 8px;
                    border-radius: 4px;
                    border: 1px solid #ddd;
                    min-width: 200px;
                }
            </style>
        </head>
        <body>
            <div class="action-buttons no-print">
                <a href="base.php" class="button button-back">Volver</a>
                <button onclick="window.print()" class="button button-print">Imprimir Reporte</button>
            </div>
            
            <div class="report-header">
                <div class="report-title">Programa Persona Mayor</div>
                <div class="report-subtitle">Reporte de Distribución de Usuarios por ' . $available_fields[$selected_field] . '</div>
            </div>
            
            <div class="report-info">
                <div class="report-date">Fecha: ' . $fecha_actual . '</div>
                <div class="report-date">Hora: ' . $hora_actual . '</div>
            </div>
            
            <table>
                <thead>
                    <tr>
                        <th>' . $available_fields[$selected_field] . '</th>
                        <th>Cantidad</th>
                        <th>Porcentaje</th>
                    </tr>
                </thead>
                <tbody>';
                
                // Agregar filas a la tabla
                foreach ($data_table as $index => $row) {
                    $porcentaje = ($total > 0) ? round(($row['cantidad'] / $total) * 100, 2) : 0;
                    
                    $html_content .= '
                    <tr>
                        <td>' . htmlspecialchars($row['campo']) . '</td>
                        <td>' . $row['cantidad'] . '</td>
                        <td>' . $porcentaje . '%</td>
                    </tr>';
                }
                
                $html_content .= '
                    <tr class="total-row">
                        <td>Total</td>
                        <td>' . $total . '</td>
                        <td>100%</td>
                    </tr>
                </tbody>
            </table>
            
            <div class="report-container">
                <div class="chart-container">
                    <canvas id="pieChart"></canvas>
                </div>
                <div class="chart-container">
                    <canvas id="barChart"></canvas>
                </div>
            </div>
            
            <script>
                // Datos para los gráficos
                const labels = ' . json_encode($labels) . ';
                const data = ' . json_encode($values) . ';
                
                // Colores para los gráficos
                const backgroundColors = ' . json_encode($backgroundColors) . ';
                const borderColors = backgroundColors.map(color => color.replace("0.7", "1"));
                
                // Gráfico circular
                const ctxPie = document.getElementById("pieChart").getContext("2d");
                const pieChart = new Chart(ctxPie, {
                    type: "pie",
                    data: {
                        labels: labels,
                        datasets: [{
                            data: data,
                            backgroundColor: backgroundColors,
                            borderColor: borderColors,
                            borderWidth: 1
                        }]
                    },
                    options: {
                        responsive: true,
                        plugins: {
                            legend: {
                                position: "right",
                            },
                            title: {
                                display: true,
                                text: "Distribución por ' . $available_fields[$selected_field] . '"
                            }
                        }
                    }
                });
                
                // Gráfico de barras
                const ctxBar = document.getElementById("barChart").getContext("2d");
                const barChart = new Chart(ctxBar, {
                    type: "bar",
                    data: {
                        labels: labels,
                        datasets: [{
                            label: "Cantidad de Usuarios",
                            data: data,
                            backgroundColor: backgroundColors,
                            borderColor: borderColors,
                            borderWidth: 1
                        }]
                    },
                    options: {
                        responsive: true,
                        scales: {
                            y: {
                                beginAtZero: true,
                                ticks: {
                                    stepSize: 1
                                }
                            }
                        },
                        plugins: {
                            title: {
                                display: true,
                                text: "Cantidad de Usuarios por ' . $available_fields[$selected_field] . '"
                            },
                            legend: {
                                display: false
                            }
                        }
                    }
                });
            </script>
        </body>
        </html>';

        // Mostrar el HTML
        echo $html_content;
        exit;
    }
?>
<!DOCTYPE html>
<html lang="es">
<head>
    <meta charset="UTF-8">
    <link href='https://unpkg.com/boxicons@2.0.9/css/boxicons.min.css' rel='stylesheet'>
    <link rel="stylesheet" href="../../backend/css/admin.css?v=7.0">
    <link rel="icon" type="image/png" sizes="96x96" href="../../backend/img/ico.svg">

    <title>Programa Persona Mayor | Reportes y Base de Datos</title>
    <style>
        .page-header {
            margin-bottom: 30px;
        }
        .page-header h1 {
            font-size: 28px;
            font-weight: 700;
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            -webkit-background-clip: text;
            -webkit-text-fill-color: transparent;
            background-clip: text;
            margin-bottom: 8px;
        }
        .page-header p {
            color: #6c757d;
            font-size: 15px;
        }
        
        .reports-grid {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(300px, 1fr));
            gap: 24px;
            margin-bottom: 40px;
        }
        
        .report-card {
            background: white;
            border-radius: 16px;
            padding: 28px;
            box-shadow: 0 4px 20px rgba(0, 0, 0, .08);
            transition: all 0.3s ease;
            border: 1px solid #f0f0f0;
            position: relative;
            overflow: hidden;
        }
        
        .report-card::before {
            content: '';
            position: absolute;
            top: 0;
            left: 0;
            width: 100%;
            height: 4px;
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
        }
        
        .report-card:hover {
            transform: translateY(-5px);
            box-shadow: 0 8px 30px rgba(102, 126, 234, .2);
        }
        
        .report-card-icon {
            width: 60px;
            height: 60px;
            border-radius: 14px;
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            display: flex;
            align-items: center;
            justify-content: center;
            margin-bottom: 20px;
            box-shadow: 0 8px 20px rgba(102, 126, 234, .3);
        }
        
        .report-card-icon i {
            font-size: 28px;
            color: white;
        }
        
        .report-card h3 {
            font-size: 20px;
            font-weight: 700;
            color: #2d3436;
            margin-bottom: 12px;
        }
        
        .report-card p {
            color: #6c757d;
            font-size: 14px;
            line-height: 1.6;
            margin-bottom: 20px;
        }
        
        .form-group {
            margin-bottom: 20px;
        }
        
        .form-group label {
            display: block;
            margin-bottom: 10px;
            font-weight: 600;
            color: #2d3436;
            font-size: 14px;
        }
        
        .form-control {
            width: 100%;
            padding: 14px 16px;
            border: 2px solid #e8e8e8;
            border-radius: 12px;
            font-size: 15px;
            transition: all 0.3s ease;
            background: #f8f9fc;
            color: #2d3436;
            box-sizing: border-box;
        }
        
        .form-control:focus {
            outline: none;
            border-color: #667eea;
            background: white;
            box-shadow: 0 0 0 4px rgba(102, 126, 234, 0.15);
        }
        
        .btn-generate {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            color: white;
            border: none;
            padding: 14px 28px;
            font-size: 15px;
            font-weight: 700;
            border-radius: 12px;
            cursor: pointer;
            transition: all 0.3s ease;
            box-shadow: 0 6px 20px rgba(102, 126, 234, 0.4);
            display: inline-flex;
            align-items: center;
            gap: 10px;
            text-transform: uppercase;
            letter-spacing: 0.5px;
        }
        
        .btn-generate:hover {
            transform: translateY(-2px);
            box-shadow: 0 10px 30px rgba(102, 126, 234, 0.5);
        }
        
        .btn-generate i {
            font-size: 18px;
        }
        
        .database-section {
            margin-top: 50px;
        }
        
        .section-title {
            font-size: 22px;
            font-weight: 700;
            color: #2d3436;
            margin-bottom: 24px;
            display: flex;
            align-items: center;
            gap: 12px;
        }
        
        .section-title i {
            font-size: 26px;
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            -webkit-background-clip: text;
            -webkit-text-fill-color: transparent;
            background-clip: text;
        }
        
        .database-grid {
            display: grid;
            grid-template-columns: repeat(auto-fill, minmax(200px, 1fr));
            gap: 20px;
        }
        
        .database-card {
            background: white;
            border-radius: 16px;
            padding: 24px;
            text-align: center;
            box-shadow: 0 4px 20px rgba(0, 0, 0, .08);
            transition: all 0.3s ease;
            border: 1px solid #f0f0f0;
        }
        
        .database-card:hover {
            transform: translateY(-5px);
            box-shadow: 0 8px 30px rgba(102, 126, 234, .2);
        }
        
        .database-card img {
            width: 80px;
            height: 80px;
            margin-bottom: 16px;
            filter: drop-shadow(0 4px 12px rgba(102, 126, 234, .2));
        }
        
        .btn-download {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            color: white;
            padding: 10px 20px;
            border-radius: 10px;
            text-decoration: none;
            display: inline-flex;
            align-items: center;
            gap: 8px;
            font-weight: 600;
            font-size: 14px;
            transition: all 0.3s ease;
            box-shadow: 0 4px 12px rgba(102, 126, 234, .3);
        }
        
        .btn-download:hover {
            transform: translateY(-2px);
            box-shadow: 0 6px 20px rgba(102, 126, 234, .4);
        }
        
        .btn-download i {
            font-size: 16px;
        }
        
        .empty-state {
            text-align: center;
            padding: 60px 20px;
            background: white;
            border-radius: 16px;
            box-shadow: 0 4px 20px rgba(0, 0, 0, .08);
        }
        
        .empty-state i {
            font-size: 64px;
            color: #e8e8e8;
            margin-bottom: 20px;
        }
        
        .empty-state p {
            color: #6c757d;
            font-size: 16px;
        }
        
        .report-viewer {
            display: none;
            background: white;
            border-radius: 16px;
            padding: 30px;
            margin-top: 30px;
            box-shadow: 0 4px 20px rgba(0, 0, 0, .08);
            border: 1px solid #f0f0f0;
        }
        
        .report-viewer.active {
            display: block;
        }
        
        .report-header-section {
            text-align: center;
            margin-bottom: 30px;
            padding-bottom: 20px;
            border-bottom: 2px solid #f0f0f0;
        }
        
        .report-header-section h2 {
            font-size: 24px;
            font-weight: 700;
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            -webkit-background-clip: text;
            -webkit-text-fill-color: transparent;
            background-clip: text;
            margin-bottom: 10px;
        }
        
        .report-actions {
            display: flex;
            justify-content: space-between;
            align-items: center;
            margin-bottom: 20px;
        }
        
        .btn-close-report {
            background: #6c757d;
            color: white;
            padding: 10px 20px;
            border-radius: 10px;
            border: none;
            cursor: pointer;
            font-weight: 600;
            transition: all 0.3s ease;
        }
        
        .btn-close-report:hover {
            background: #5a6268;
        }
        
        .btn-print-report {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            color: white;
            padding: 10px 20px;
            border-radius: 10px;
            border: none;
            cursor: pointer;
            font-weight: 600;
            transition: all 0.3s ease;
            display: inline-flex;
            align-items: center;
            gap: 8px;
        }
        
        .btn-print-report:hover {
            transform: translateY(-2px);
            box-shadow: 0 6px 20px rgba(102, 126, 234, .4);
        }
        
        .charts-container {
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(400px, 1fr));
            gap: 30px;
            margin: 30px 0;
        }
        
        .chart-wrapper {
            background: #f8f9fc;
            padding: 20px;
            border-radius: 12px;
        }
        
        .data-table {
            width: 100%;
            border-collapse: collapse;
            margin-top: 20px;
        }
        
        .data-table th,
        .data-table td {
            padding: 12px 15px;
            text-align: left;
            border-bottom: 1px solid #e8e8e8;
        }
        
        .data-table th {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            color: white;
            font-weight: 600;
        }
        
        .data-table tr:hover {
            background: #f8f9fc;
        }
        
        .total-row {
            font-weight: 700;
            background: #f0f0f0;
        }
        
        /* Estilos para tabla de análisis cruzado */
        #crossTableContainer .data-table {
            font-size: 13px;
        }
        
        #crossTableContainer .data-table th:first-child {
            background: linear-gradient(135deg, #764ba2 0%, #667eea 100%);
            position: sticky;
            left: 0;
            z-index: 2;
        }
        
        #crossTableContainer .data-table td:first-child {
            background: #f8f9fc;
            position: sticky;
            left: 0;
            z-index: 1;
            font-weight: 600;
        }
        
        #crossTableContainer .data-table td {
            text-align: center;
            min-width: 80px;
        }
        
        #crossTableContainer .data-table th {
            text-align: center;
            white-space: nowrap;
        }
        
        #crossTableContainer .data-table tbody tr:hover {
            background: #fff9e6;
        }
        
        /* Resaltar celdas con valores */
        #crossTableContainer .data-table td[data-value]:not([data-value="0"]) {
            background: linear-gradient(135deg, rgba(102, 126, 234, 0.1), rgba(118, 75, 162, 0.1));
            font-weight: 600;
        }
        
        /* Estilos para impresión */
        @media print {
            /* Ocultar todo excepto el reporte */
            #sidebar,
            nav,
            .breadcrumbs,
            .page-header,
            .reports-grid,
            .database-section,
            .report-actions {
                display: none !important;
            }
            
            .report-viewer {
                display: block !important;
                box-shadow: none;
                border: none;
                padding: 20px;
            }
            
            #crossTableContainer {
                page-break-inside: avoid;
            }
            
            .report-header-section h2 {
                color: #667eea !important;
                -webkit-print-color-adjust: exact;
                print-color-adjust: exact;
            }
            
            .data-table th {
                background: #667eea !important;
                color: white !important;
                -webkit-print-color-adjust: exact;
                print-color-adjust: exact;
            }
            
            .chart-wrapper {
                page-break-inside: avoid;
            }
            
            body {
                margin: 0;
                padding: 0;
            }
            
            #content {
                margin: 0 !important;
                padding: 0 !important;
            }
            
            #crossTableContainer .data-table th:first-child,
            #crossTableContainer .data-table td:first-child {
                position: relative;
            }
        }
        
        /* Animación para celdas al cargar */
        @keyframes fadeIn {
            from {
                opacity: 0;
                transform: scale(0.95);
            }
            to {
                opacity: 1;
                transform: scale(1);
            }
        }
        
        .report-viewer.active {
            animation: fadeIn 0.4s ease;
        }
    </style>
</head>
<body>
        <!-- SIDEBAR -->
   <!-- SIDEBAR DINÁMICO -->
    <?php include '../../backend/php/sidebar_menu.php'; ?>
 <!-- SIDEBAR -->

    <!-- NAVBAR -->
    <section id="content">
        <!-- NAVBAR -->
        <nav>
            <i class='bx bx-menu toggle-sidebar' ></i>
            <form action="#">
                <div class="form-group">
                    <input type="text" placeholder="Search...">
                    <i class='bx bx-search icon' ></i>
                </div>
            </form>
            
           
            <span class="divider"></span>
            <div class="profile">
                <img src="https://images.unsplash.com/photo-1517841905240-472988babdf9?ixid=MnwxMjA3fDB8MHxzZWFyY2h8NHx8cGVvcGxlfGVufDB8fDB8fA%3D%3D&ixlib=rb-1.2.1&auto=format&fit=crop&w=500&q=60" alt="">
                <ul class="profile-link">
                    <li><a href="../profile/mostrar.php"><i class='bx bxs-user-circle icon' ></i> Profile</a></li>
                    
                    <li>
                     <a href="../salir.php"><i class='bx bxs-log-out-circle' ></i> Logout</a>
                    </li>
                   
                </ul>
            </div>
        </nav>
        <!-- NAVBAR -->

        <!-- MAIN -->
        <main>
            <h1 class="title">Bienvenido <?php echo '<strong>'.$_SESSION['username'].'</strong>'; ?></h1>
            <ul class="breadcrumbs">
                <li><a href="../admin/escritorio.php">Home</a></li>
                <li class="divider">></li>
                <li><a href="#" class="active">Reportes y Base de Datos</a></li>
            </ul>
            
            <div class="page-header">
                <h1><i class='bx bxs-bar-chart-alt-2'></i> Reportes por Modalidad</h1>
                <p>Análisis de distribución de usuarios por modalidad y gestión de copias de seguridad</p>
            </div>
            
            <div class="reports-grid">
                <div class="report-card">
                    <div class="report-card-icon">
                        <i class='bx bxs-report'></i>
                    </div>
                    <h3>Generador de Reportes</h3>
                    <p>Análisis de distribución por modalidad. Selecciona una modalidad específica o cruza con otro campo</p>
                    <div class="form-group">
                        <label for="modalidad"><i class='bx bx-building'></i> Modalidad:</label>
                        <select name="modalidad" id="modalidad" class="form-control">
                            <option value="all">Todas las Modalidades</option>
                            <?php foreach($modalidades as $mod): ?>
                                <option value="<?php echo htmlspecialchars($mod); ?>"><?php echo htmlspecialchars($mod); ?></option>
                            <?php endforeach; ?>
                        </select>
                    </div>
                    <div class="form-group">
                        <label for="field2"><i class='bx bx-filter-alt'></i> Campo para Cruzar (opcional):</label>
                        <select name="field2" id="field2" class="form-control">
                            <option value="none">-- Sin cruce --</option>
                            <?php foreach($available_fields as $field_key => $field_name): ?>
                                <option value="<?php echo $field_key; ?>"><?php echo $field_name; ?></option>
                            <?php endforeach; ?>
                        </select>
                        <small style="display: block; margin-top: 8px; color: #6c757d; font-size: 12px;">
                            💡 <strong>Tip:</strong> Selecciona un campo para ver cómo se distribuye por cada modalidad (ej: Modalidad vs Sexo)
                        </small>
                    </div>
                    <button type="button" class="btn-generate" onclick="generateReport()">
                        <i class='bx bxs-bar-chart-alt-2'></i>
                        Reporte de Modalidad
                    </button>
                </div>
                
                <div class="report-card">
                    <div class="report-card-icon">
                        <i class='bx bxs-pie-chart-alt-2'></i>
                    </div>
                    <h3>Análisis por Modalidad</h3>
                    <p>Visualiza la distribución de usuarios en cada modalidad y cruza con otros datos demográficos</p>
                    <ul style="color: #6c757d; font-size: 14px; line-height: 2; text-align: left; margin-top: 20px;">
                        <li>🏛️ Todas las modalidades</li>
                        <li>📊 Gráficos interactivos</li>
                        <li>🔄 Análisis cruzado de datos</li>
                        <li>📈 Totales y porcentajes</li>
                        <li>📄 Exportación a PDF</li>
                    </ul>
                </div>
                
                <div class="report-card">
                    <div class="report-card-icon">
                        <i class='bx bxs-data'></i>
                    </div>
                    <h3>Campos para Cruzar</h3>
                    <p>Combina Modalidad con cualquiera de estos campos para obtener insights detallados</p>
                    <div style="display: grid; grid-template-columns: 1fr 1fr; gap: 8px; margin-top: 20px; font-size: 13px; color: #6c757d;">
                        <div>✓ Sexo</div>
                        <div>✓ Estado Civil</div>
                        <div>✓ Zona</div>
                        <div>✓ Educación</div>
                        <div>✓ Tecnología</div>
                        <div>✓ Grupo Sanguíneo</div>
                        <div>✓ Sisben</div>
                        <div>✓ Pensionado</div>
                        <div>✓ Vive Con</div>
                        <div>✓ Población D.</div>
                    </div>
                    <div style="margin-top: 15px; padding: 10px; background: #f0f7ff; border-radius: 8px; font-size: 12px; color: #4a5568;">
                        <strong>🔍 Ejemplos de análisis:</strong><br>
                        • Modalidad vs Sexo<br>
                        • Modalidad vs Zona<br>
                        • Modalidad vs Educación
                    </div>
                </div>
            </div>
            
            <!-- Visor de Reportes -->
            <div class="report-viewer" id="reportViewer">
                <div class="report-actions">
                    <button class="btn-close-report" onclick="closeReport()">
                        <i class='bx bx-x'></i> Cerrar Reporte
                    </button>
                    <button class="btn-print-report" onclick="downloadReportPDF()" style="background: linear-gradient(135deg, #f093fb 0%, #f5576c 100%);">
                        <i class='bx bxs-file-pdf'></i> Descargar PDF
                    </button>
                </div>
                
                <div class="report-header-section">
                    <h2>Programa Persona Mayor</h2>
                    <h3 id="reportTitle">Reporte de Distribución</h3>
                    <p id="reportDate"></p>
                </div>
                
                <div class="charts-container" id="chartsContainer">
                    <div class="chart-wrapper">
                        <canvas id="pieChart"></canvas>
                    </div>
                    <div class="chart-wrapper">
                        <canvas id="barChart"></canvas>
                    </div>
                </div>
                
                <!-- Tabla de análisis cruzado -->
                <div id="crossTableContainer" style="display: none; margin-top: 30px;">
                    <h3 style="text-align: center; margin-bottom: 20px; color: #2d3436;">Análisis Cruzado</h3>
                    <div style="overflow-x: auto;">
                        <table class="data-table" id="crossTable">
                        </table>
                    </div>
                </div>
                
                <table class="data-table" id="reportTable">
                    <thead>
                        <tr>
                            <th id="tableHeader">Campo</th>
                            <th>Cantidad</th>
                            <th>Porcentaje</th>
                        </tr>
                    </thead>
                    <tbody id="tableBody">
                    </tbody>
                </table>
            </div>
            
            <div class="database-section">
                <h2 class="section-title">
                    <i class='bx bxs-database'></i>
                    Copias de Seguridad de Base de Datos
                </h2>
                
                <?php 
                $sentencia = $connect->prepare("SELECT * FROM base;");
                $sentencia->execute();

                $data = array();
                if($sentencia){
                    while($r = $sentencia->fetchObject()){
                        $data[] = $r;
                    }
                }
                ?>
                
                <?php if(count($data)>0): ?>
                    <div class="database-grid">
                        <?php foreach($data as $e): ?> 
                            <div class="database-card">
                                <img src="../../backend/img/sql.png" alt="Database Backup" />
                                <a href="../../backend/img/subidas/<?php echo $e->base; ?>" class="btn-download" download>
                                    <i class='bx bxs-download'></i>
                                    Descargar
                                </a>
                            </div>
                        <?php endforeach; ?>
                    </div>
                <?php else: ?>
                    <div class="empty-state">
                        <i class='bx bxs-folder-open'></i>
                        <p>No hay copias de seguridad disponibles</p>
                    </div>
                <?php endif; ?>
            </div>
        </main>
        <!-- MAIN -->
    </section>
    
    <!-- NAVBAR -->
    <script src="../../backend/js/jquery.min.js"></script>
    <script src="../../backend/js/script.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/sweetalert/2.1.2/sweetalert.min.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jspdf/2.5.1/jspdf.umd.min.js"></script>
    <?php include_once '../../backend/php/upd_settin.php' ?>
    
    <script>
        let pieChart, barChart;
        
        // Actualizar botón según modo
        document.addEventListener('DOMContentLoaded', function() {
            const field2Select = document.getElementById('field2');
            const generateBtn = document.querySelector('.btn-generate');
            const originalBtnText = '<i class=\'bx bxs-bar-chart-alt-2\'></i> Reporte de Modalidad';
            const crossBtnText = '<i class=\'bx bx-scatter-chart\'></i> Análisis Cruzado';
            
            field2Select.addEventListener('change', function() {
                if (this.value !== 'none') {
                    generateBtn.innerHTML = crossBtnText;
                    generateBtn.style.background = 'linear-gradient(135deg, #f093fb 0%, #f5576c 100%)';
                } else {
                    generateBtn.innerHTML = originalBtnText;
                    generateBtn.style.background = 'linear-gradient(135deg, #667eea 0%, #764ba2 100%)';
                }
            });
        });
        
        function generateReport() {
            const modalidad = document.getElementById('modalidad').value;
            const field2 = document.getElementById('field2').value;
            const modalidadName = document.getElementById('modalidad').options[document.getElementById('modalidad').selectedIndex].text;
            const field2Name = field2 !== 'none' ? document.getElementById('field2').options[document.getElementById('field2').selectedIndex].text : null;
            
            // Mostrar indicador de carga
            const generateBtn = document.querySelector('.btn-generate');
            const originalBtnContent = generateBtn.innerHTML;
            generateBtn.innerHTML = '<i class="bx bx-loader-alt bx-spin"></i> Generando...';
            generateBtn.disabled = true;
            
            // Construir URL con parámetros
            let url = 'get_report_data.php?modalidad=' + encodeURIComponent(modalidad);
            if (field2 && field2 !== 'none') {
                url += '&field2=' + field2;
            }
            
            // Hacer petición AJAX para obtener datos
            fetch(url)
                .then(response => response.json())
                .then(data => {
                    // Restaurar botón
                    generateBtn.innerHTML = originalBtnContent;
                    generateBtn.disabled = false;
                    
                    if (data.error) {
                        swal({
                            title: "Error",
                            text: data.error,
                            icon: "error",
                            button: "Aceptar"
                        });
                        return;
                    }
                    
                    // Mostrar visor de reportes
                    document.getElementById('reportViewer').classList.add('active');
                    
                    // Scroll al reporte
                    document.getElementById('reportViewer').scrollIntoView({ behavior: 'smooth' });
                    
                    // Actualizar fecha
                    const now = new Date();
                    document.getElementById('reportDate').textContent = 'Generado el ' + now.toLocaleDateString() + ' a las ' + now.toLocaleTimeString();
                    
                    // Procesar según tipo de reporte
                    if (data.type === 'cross') {
                        // Reporte cruzado
                        generateCrossReport(data, modalidadName, field2Name);
                    } else {
                        // Reporte simple
                        generateSimpleReport(data, modalidadName);
                    }
                })
                .catch(error => {
                    // Restaurar botón en caso de error
                    generateBtn.innerHTML = originalBtnContent;
                    generateBtn.disabled = false;
                    
                    console.error('Error:', error);
                    swal({
                        title: "Error",
                        text: "No se pudo generar el reporte. Por favor, intenta nuevamente.",
                        icon: "error",
                        button: "Aceptar"
                    });
                });
        }
        
        function generateSimpleReport(data, modalidadName) {
            // Actualizar título
            const title = modalidadName === 'Todas las Modalidades' 
                ? 'Distribución de Usuarios por Modalidad'
                : 'Reporte de Modalidad: ' + modalidadName;
            document.getElementById('reportTitle').textContent = title;
            
            // Mostrar gráficos, ocultar tabla cruzada
            document.getElementById('chartsContainer').style.display = 'grid';
            document.getElementById('crossTableContainer').style.display = 'none';
            document.getElementById('reportTable').style.display = 'table';
            
            // Actualizar tabla
            document.getElementById('tableHeader').textContent = 'Modalidad';
            const tbody = document.getElementById('tableBody');
            tbody.innerHTML = '';
            
            let total = 0;
            data.data.forEach(row => {
                total += parseInt(row.cantidad);
            });
            
            data.data.forEach(row => {
                const percentage = ((row.cantidad / total) * 100).toFixed(2);
                const tr = document.createElement('tr');
                tr.innerHTML = `
                    <td>${row.campo}</td>
                    <td>${row.cantidad}</td>
                    <td>${percentage}%</td>
                `;
                tbody.appendChild(tr);
            });
            
            // Fila total
            const totalRow = document.createElement('tr');
            totalRow.className = 'total-row';
            totalRow.innerHTML = `
                <td>Total</td>
                <td>${total}</td>
                <td>100%</td>
            `;
            tbody.appendChild(totalRow);
            
            // Generar gráficos
            const chartTitle = modalidadName === 'Todas las Modalidades' ? 'Modalidad' : modalidadName;
            generateCharts(data.labels, data.values, chartTitle);
        }
        
        function generateCrossReport(data, field1Name, field2Name) {
            // Actualizar título
            document.getElementById('reportTitle').textContent = 'Análisis Cruzado: ' + field1Name + ' vs ' + field2Name;
            
            // Ocultar tabla simple y gráficos de torta
            document.getElementById('reportTable').style.display = 'none';
            document.getElementById('chartsContainer').style.display = 'none';
            
            // Mostrar tabla cruzada
            document.getElementById('crossTableContainer').style.display = 'block';
            
            // Generar tabla cruzada
            const crossTable = document.getElementById('crossTable');
            crossTable.innerHTML = '';
            
            // Crear encabezado
            const thead = document.createElement('thead');
            const headerRow = document.createElement('tr');
            headerRow.innerHTML = '<th>' + field1Name + ' \\ ' + field2Name + '</th>';
            data.field2Values.forEach(val2 => {
                headerRow.innerHTML += '<th>' + val2 + '</th>';
            });
            headerRow.innerHTML += '<th>Total</th>';
            thead.appendChild(headerRow);
            crossTable.appendChild(thead);
            
            // Crear cuerpo
            const tbody = document.createElement('tbody');
            let columnTotals = new Array(data.field2Values.length).fill(0);
            let grandTotal = 0;
            
            data.matrix.forEach((row, index) => {
                const tr = document.createElement('tr');
                tr.innerHTML = '<td><strong>' + row.label + '</strong></td>';
                
                let rowTotal = 0;
                data.field2Values.forEach((val2, colIndex) => {
                    const value = row[val2] || 0;
                    rowTotal += value;
                    columnTotals[colIndex] += value;
                    grandTotal += value;
                    tr.innerHTML += '<td data-value="' + value + '">' + value + '</td>';
                });
                
                tr.innerHTML += '<td style="background: #e8e8e8;"><strong>' + rowTotal + '</strong></td>';
                tbody.appendChild(tr);
            });
            
            // Fila de totales
            const totalRow = document.createElement('tr');
            totalRow.className = 'total-row';
            totalRow.innerHTML = '<td><strong>Total</strong></td>';
            columnTotals.forEach(total => {
                totalRow.innerHTML += '<td><strong>' + total + '</strong></td>';
            });
            totalRow.innerHTML += '<td><strong>' + grandTotal + '</strong></td>';
            tbody.appendChild(totalRow);
            
            crossTable.appendChild(tbody);
            
            // Generar gráfico de barras agrupadas
            generateGroupedBarChart(data, field1Name, field2Name);
        }
        
        function generateCharts(labels, values, fieldName) {
            // Destruir gráficos anteriores si existen
            if (pieChart) pieChart.destroy();
            if (barChart) barChart.destroy();
            
            // Generar colores
            const colors = generateColors(labels.length);
            
            // Gráfico circular
            const ctxPie = document.getElementById('pieChart').getContext('2d');
            pieChart = new Chart(ctxPie, {
                type: 'pie',
                data: {
                    labels: labels,
                    datasets: [{
                        data: values,
                        backgroundColor: colors,
                        borderWidth: 2,
                        borderColor: '#fff'
                    }]
                },
                options: {
                    responsive: true,
                    plugins: {
                        legend: {
                            position: 'right'
                        },
                        title: {
                            display: true,
                            text: 'Distribución por ' + fieldName,
                            font: {
                                size: 16,
                                weight: 'bold'
                            }
                        }
                    }
                }
            });
            
            // Gráfico de barras
            const ctxBar = document.getElementById('barChart').getContext('2d');
            barChart = new Chart(ctxBar, {
                type: 'bar',
                data: {
                    labels: labels,
                    datasets: [{
                        label: 'Cantidad de Usuarios',
                        data: values,
                        backgroundColor: colors,
                        borderWidth: 0
                    }]
                },
                options: {
                    responsive: true,
                    scales: {
                        y: {
                            beginAtZero: true,
                            ticks: {
                                stepSize: 1
                            }
                        }
                    },
                    plugins: {
                        legend: {
                            display: false
                        },
                        title: {
                            display: true,
                            text: 'Cantidad de Usuarios por ' + fieldName,
                            font: {
                                size: 16,
                                weight: 'bold'
                            }
                        }
                    }
                }
            });
        }
        
        function generateGroupedBarChart(data, field1Name, field2Name) {
            // Destruir gráficos anteriores si existen
            if (pieChart) pieChart.destroy();
            if (barChart) barChart.destroy();
            
            // Preparar datos para gráfico de barras agrupadas
            const datasets = [];
            const colors = generateColors(data.field2Values.length);
            
            data.field2Values.forEach((val2, index) => {
                const dataPoints = [];
                data.matrix.forEach(row => {
                    dataPoints.push(row[val2] || 0);
                });
                
                datasets.push({
                    label: val2,
                    data: dataPoints,
                    backgroundColor: colors[index],
                    borderWidth: 0
                });
            });
            
            // Crear canvas para gráfico cruzado
            const chartsContainer = document.getElementById('chartsContainer');
            chartsContainer.style.display = 'block';
            chartsContainer.innerHTML = '<div class="chart-wrapper" style="grid-column: 1 / -1;"><canvas id="crossBarChart"></canvas></div>';
            
            const ctxCross = document.getElementById('crossBarChart').getContext('2d');
            barChart = new Chart(ctxCross, {
                type: 'bar',
                data: {
                    labels: data.field1Values,
                    datasets: datasets
                },
                options: {
                    responsive: true,
                    scales: {
                        y: {
                            beginAtZero: true,
                            ticks: {
                                stepSize: 1
                            }
                        }
                    },
                    plugins: {
                        legend: {
                            display: true,
                            position: 'top'
                        },
                        title: {
                            display: true,
                            text: field1Name + ' por ' + field2Name,
                            font: {
                                size: 18,
                                weight: 'bold'
                            }
                        }
                    }
                }
            });
        }
        
        function generateColors(count) {
            const colors = [];
            for (let i = 0; i < count; i++) {
                const hue = (i * 137.5) % 360;
                colors.push(`hsla(${hue}, 70%, 60%, 0.8)`);
            }
            return colors;
        }
        
        function closeReport() {
            document.getElementById('reportViewer').classList.remove('active');
            window.scrollTo({ top: 0, behavior: 'smooth' });
        }
        
        function downloadReportPDF() {
            // Mostrar mensaje de carga
            const loadingMsg = document.createElement('div');
            loadingMsg.style.cssText = 'position: fixed; top: 50%; left: 50%; transform: translate(-50%, -50%); background: white; padding: 30px; border-radius: 10px; box-shadow: 0 4px 20px rgba(0,0,0,0.2); z-index: 10000; text-align: center;';
            loadingMsg.innerHTML = '<i class="bx bx-loader-alt bx-spin" style="font-size: 48px; color: #667eea;"></i><br><p style="margin-top: 15px; font-weight: 600;">Generando PDF con gráficos...</p>';
            document.body.appendChild(loadingMsg);
            
            setTimeout(() => {
                generatePDFWithCharts().then(() => {
                    document.body.removeChild(loadingMsg);
                }).catch(err => {
                    console.error(err);
                    document.body.removeChild(loadingMsg);
                    alert('Error al generar el PDF');
                });
            }, 100);
        }
        
        async function generatePDFWithCharts() {
            const { jsPDF } = window.jspdf;
            const doc = new jsPDF('p', 'mm', 'a4');
            
            const reportTitle = document.getElementById('reportTitle').textContent;
            const reportDate = document.getElementById('reportDate').textContent;
            
            // Detectar si es reporte cruzado o simple
            const crossTableVisible = document.getElementById('crossTableContainer').style.display !== 'none';
            
            // Encabezado
            doc.setFillColor(102, 126, 234);
            doc.rect(0, 0, 210, 35, 'F');
            
            doc.setTextColor(255, 255, 255);
            doc.setFontSize(20);
            doc.setFont('helvetica', 'bold');
            doc.text('Programa Persona Mayor', 105, 15, { align: 'center' });
            
            doc.setFontSize(14);
            doc.setFont('helvetica', 'normal');
            doc.text(reportTitle, 105, 23, { align: 'center' });
            
            doc.setFontSize(10);
            doc.text(reportDate, 105, 30, { align: 'center' });
            
            let currentY = 45;
            
            if (crossTableVisible) {
                // ==================== REPORTE CRUZADO ====================
                // Capturar gráfico de barras agrupadas
                try {
                    const crossBarChart = document.getElementById('crossBarChart');
                    if (crossBarChart) {
                        const chartImg = crossBarChart.toDataURL('image/png', 1.0);
                        
                        doc.setFontSize(12);
                        doc.setTextColor(0, 0, 0);
                        doc.setFont('helvetica', 'bold');
                        doc.text('Gráfico de Análisis Cruzado', 105, currentY, { align: 'center' });
                        currentY += 7;
                        
                        // Agregar gráfico centrado y más grande
                        doc.addImage(chartImg, 'PNG', 20, currentY, 170, 90);
                        currentY += 100;
                    }
                } catch (error) {
                    console.error('Error al capturar gráfico cruzado:', error);
                }
                
                // Obtener datos de la tabla cruzada
                const crossTable = document.getElementById('crossTable');
                const headers = [];
                const headerCells = crossTable.querySelectorAll('thead th');
                headerCells.forEach(th => headers.push(th.textContent));
                
                const tableData = [];
                const rows = crossTable.querySelectorAll('tbody tr');
                rows.forEach(row => {
                    const cells = row.querySelectorAll('td');
                    const rowData = [];
                    cells.forEach(cell => rowData.push(cell.textContent));
                    tableData.push(rowData);
                });
                
                // Crear tabla cruzada
                if (typeof doc.autoTable === 'function') {
                    doc.autoTable({
                        startY: currentY,
                        head: [headers],
                        body: tableData,
                        theme: 'grid',
                        headStyles: {
                            fillColor: [118, 75, 162],
                            textColor: [255, 255, 255],
                            fontStyle: 'bold',
                            fontSize: 9,
                            halign: 'center'
                        },
                        bodyStyles: {
                            fontSize: 8,
                            halign: 'center'
                        },
                        columnStyles: {
                            0: { fontStyle: 'bold', fillColor: [248, 249, 252], halign: 'left' }
                        },
                        margin: { left: 10, right: 10 }
                    });
                }
                
            } else {
                // ==================== REPORTE SIMPLE ====================
                const fieldName = document.getElementById('tableHeader').textContent;
                
                // Capturar gráficos
                try {
                    const pieChartCanvas = document.getElementById('pieChart');
                    const pieChartImg = pieChartCanvas.toDataURL('image/png', 1.0);
                    
                    doc.setFontSize(12);
                    doc.setTextColor(0, 0, 0);
                    doc.setFont('helvetica', 'bold');
                    doc.text('Gráfico de Distribución', 105, currentY, { align: 'center' });
                    currentY += 7;
                    
                    // Agregar gráfico circular
                    doc.addImage(pieChartImg, 'PNG', 15, currentY, 85, 85);
                    
                    // Capturar y agregar gráfico de barras
                    const barChartCanvas = document.getElementById('barChart');
                    const barChartImg = barChartCanvas.toDataURL('image/png', 1.0);
                    doc.addImage(barChartImg, 'PNG', 110, currentY, 85, 85);
                    
                    currentY += 95;
                } catch (error) {
                    console.error('Error al capturar gráficos:', error);
                }
                
                // Obtener datos de la tabla simple
                const tbody = document.getElementById('tableBody');
                const rows = tbody.querySelectorAll('tr:not(.total-row)');
                const tableData = [];
                
                rows.forEach(row => {
                    const cells = row.querySelectorAll('td');
                    tableData.push([
                        cells[0].textContent,
                        cells[1].textContent,
                        cells[2].textContent
                    ]);
                });
                
                // Obtener total
                const totalRow = tbody.querySelector('.total-row');
                const totalCells = totalRow.querySelectorAll('td');
                
                // Crear tabla con autoTable
                if (typeof doc.autoTable === 'function') {
                    doc.autoTable({
                        startY: currentY,
                        head: [[fieldName, 'Cantidad', 'Porcentaje']],
                        body: tableData,
                        foot: [[
                            totalCells[0].textContent,
                            totalCells[1].textContent,
                            totalCells[2].textContent
                        ]],
                        theme: 'striped',
                        headStyles: {
                            fillColor: [102, 126, 234],
                            textColor: [255, 255, 255],
                            fontStyle: 'bold',
                            fontSize: 10
                        },
                        bodyStyles: {
                            fontSize: 9
                        },
                        footStyles: {
                            fillColor: [240, 240, 240],
                            textColor: [0, 0, 0],
                            fontStyle: 'bold',
                            fontSize: 10
                        },
                        margin: { left: 15, right: 15 }
                    });
                }
            }
            
            // Descargar
            const fileName = reportTitle.replace(/\s+/g, '_') + '.pdf';
            doc.save(fileName);
        }
    </script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jspdf-autotable/3.5.31/jspdf.plugin.autotable.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/html2canvas/1.4.1/html2canvas.min.js"></script>
</body>
</html>