<?php
// archivo get_pieAnaEstDesMotComChartData.php
header('Content-Type: application/json');
include 'db_config.php';

// Función para eliminar acentos
function removeAccents($string) {
    return iconv('UTF-8', 'ASCII//TRANSLIT', $string);
}

// Definir la(s) tabla(s) a consultar
$tables = [
    ['name' => 'evaluacion_impacto']
];

// Modalidades específicas (en minúsculas y sin acentos)
$modalitiesSpecific = [
    "baja intensidad y equilibrio",
    "resistencia moderada",
    "aerobicos adaptados"  // Sin acento
];

// Arreglos para acumular los datos de las columnas
$dataP1 = [];
$dataP2 = [];
$dataP3 = [];
$dataP4 = [];
$dataP5 = [];
$dataP6 = [];
$dataP7 = [];
$dataP8 = [];
$dataP9 = [];
$dataP10 = [];

foreach ($tables as $table) {
    $tableName = $table['name'];
    // Se usa un único query que obtiene la suma de ambas columnas
    $sql = "SELECT LOWER(TRIM(modalidad_deporte)) AS modality, 
                   SUM(pregunta1) AS total_p1, 
                   SUM(pregunta2) AS total_p2,
                   SUM(pregunta3) AS total_p3, 
                   SUM(pregunta4) AS total_p4,
                   SUM(pregunta5) AS total_p5, 
                   SUM(pregunta6) AS total_p6,
                   SUM(pregunta7) AS total_p7, 
                   SUM(pregunta8) AS total_p8,
                   SUM(pregunta9) AS total_p9, 
                   SUM(pregunta10) AS total_p10
            FROM $tableName
            GROUP BY modality";
    $result = $conn->query($sql);
    while ($row = $result->fetch_assoc()){
        $mod = $row['modality'];
        $modNormalized = removeAccents($mod);
        // Si la modalidad no es una de las específicas, se agrupa en "ninguna"
        if (!in_array($modNormalized, $modalitiesSpecific)) {
            $modNormalized = "ninguna";
        }
        if (!isset($dataP1[$modNormalized])) {
            $dataP1[$modNormalized] = 0;
        }
        if (!isset($dataP2[$modNormalized])) {
            $dataP2[$modNormalized] = 0;
        }
        if (!isset($dataP3[$modNormalized])) {
            $dataP3[$modNormalized] = 0;
        }
        if (!isset($dataP4[$modNormalized])) {
            $dataP4[$modNormalized] = 0;
        }
        if (!isset($dataP5[$modNormalized])) {
            $dataP5[$modNormalized] = 0;
        }
        if (!isset($dataP6[$modNormalized])) {
            $dataP6[$modNormalized] = 0;
        }
        if (!isset($dataP7[$modNormalized])) {
            $dataP7[$modNormalized] = 0;
        }
        if (!isset($dataP8[$modNormalized])) {
            $dataP8[$modNormalized] = 0;
        }
        if (!isset($dataP9[$modNormalized])) {
            $dataP9[$modNormalized] = 0;
        }
        if (!isset($dataP10[$modNormalized])) {
            $dataP10[$modNormalized] = 0;
        }
        $dataP1[$modNormalized] += (int)$row['total_p1'];
        $dataP2[$modNormalized] += (int)$row['total_p2'];
        $dataP3[$modNormalized] += (int)$row['total_p3'];
        $dataP4[$modNormalized] += (int)$row['total_p4'];
        $dataP5[$modNormalized] += (int)$row['total_p5'];
        $dataP6[$modNormalized] += (int)$row['total_p6'];
        $dataP7[$modNormalized] += (int)$row['total_p7'];
        $dataP8[$modNormalized] += (int)$row['total_p8'];
        $dataP9[$modNormalized] += (int)$row['total_p9'];
        $dataP10[$modNormalized] += (int)$row['total_p10'];
    }
}
$conn->close();

// Preparar los arreglos de salida para cada set de datos
$p1Data = [];
foreach ($dataP1 as $modality => $total_p1) {
    $p1Data[] = [
        'modality' => $modality,
        'total'    => $total_p1
    ];
}

$p2Data = [];
foreach ($dataP2 as $modality => $total_p2) {
    $p2Data[] = [
        'modality' => $modality,
        'total'    => $total_p2
    ];
}
// Preparar los arreglos de salida para cada set de datos
$p3Data = [];
foreach ($dataP1 as $modality => $total_p3) {
    $p3Data[] = [
        'modality' => $modality,
        'total'    => $total_p3
    ];
}

$p4Data = [];
foreach ($dataP4 as $modality => $total_p4) {
    $p4Data[] = [
        'modality' => $modality,
        'total'    => $total_p4
    ];
}
// Preparar los arreglos de salida para cada set de datos
$p5Data = [];
foreach ($dataP5 as $modality => $total_p5) {
    $p5Data[] = [
        'modality' => $modality,
        'total'    => $total_p5
    ];
}

$p6Data = [];
foreach ($dataP6 as $modality => $total_p6) {
    $p6Data[] = [
        'modality' => $modality,
        'total'    => $total_p6
    ];
}
// Preparar los arreglos de salida para cada set de datos
$p7Data = [];
foreach ($dataP7 as $modality => $total_p7) {
    $p7Data[] = [
        'modality' => $modality,
        'total'    => $total_p7
    ];
}

$p8Data = [];
foreach ($dataP8 as $modality => $total_p8) {
    $p8Data[] = [
        'modality' => $modality,
        'total'    => $total_p8
    ];
}
// Preparar los arreglos de salida para cada set de datos
$p9Data = [];
foreach ($dataP9 as $modality => $total_p9) {
    $p9Data[] = [
        'modality' => $modality,
        'total'    => $total_p9
    ];
}

$p10Data = [];
foreach ($dataP10 as $modality => $total_p10) {
    $p10Data[] = [
        'modality' => $modality,
        'total'    => $total_p10
    ];
}

// Se envía como un objeto con dos propiedades: bienestar y motivacion
$response = [
    "Participación"  => $p1Data,
    "Atención" => $p2Data,
    "Desafios"  => $p3Data,
    "Aprender" => $p4Data,
    "Compañerismo"  => $p5Data,
    "Actitud" => $p6Data,
    "Resolutividad"  => $p7Data,
    "Organización" => $p8Data,
    "Resiliencia"  => $p9Data,
    "Responsabilidad" => $p210Data
];

echo json_encode($response);
?>