<?php
declare(strict_types=1);

// /php/reportes/summary.php
// Entrega KPIs y distribuciones en tiempo real desde BD, respetando filtros del frontend.
// Frontend NO se modifica.

require_once __DIR__ . '/../auth/guard.php';
require_once __DIR__ . '/../conexion.php';
require_once __DIR__ . '/../_helpers.php';

$u = require_login();
require_module_access($u, "reportes");

// ---- input: POST(JSON) o GET ----
$method = $_SERVER['REQUEST_METHOD'] ?? 'GET';
$in = [];
if ($method === 'POST') {
  $in = read_json_body();
}

$typesIn = $in['types'] ?? null;

// soporta nombres usados por tu JS: sz/sec/geo (y alias sectorizacion/sector)
$szRaw  = (string)($in['sz'] ?? ($_GET['sz'] ?? ($in['sectorizacion'] ?? ($_GET['sectorizacion'] ?? ''))));
$secRaw = (string)($in['sec'] ?? ($_GET['sec'] ?? ($in['sector'] ?? ($_GET['sector'] ?? ''))));
$geo    = trim((string)($in['geo'] ?? ($_GET['geo'] ?? ''))); // "", "1", "0"
$percapitado = trim((string)($in['percapitado'] ?? ($_GET['percapitado'] ?? ''))); // "", "1", "0"
$estratificacionRaw = trim((string)($in['estratificacion_riesgo'] ?? ($_GET['estratificacion_riesgo'] ?? '')));
$diagnosticoQ = trim((string)($in['diagnostico_q'] ?? ($_GET['diagnostico_q'] ?? '')));

// ---- helpers ----
$toBool = function($v, bool $default = true): bool {
  if ($v === null) return $default;
  if (is_bool($v)) return $v;
  $s = strtolower(trim((string)$v));
  if ($s === '') return $default;
  if (in_array($s, ['1','true','si','sí','yes','y'], true)) return true;
  if (in_array($s, ['0','false','no','n'], true)) return false;
  return $default;
};

$toIntOrNull = function($v): ?int {
  $v = trim((string)$v);
  if ($v === '') return null;
  if (!is_numeric($v)) return null;
  $i = (int)$v;
  return $i > 0 ? $i : null;
};

$sz  = $toIntOrNull($szRaw);
$sec = $toIntOrNull($secRaw);
$estratificacion = strtoupper($estratificacionRaw);

// Tipos incluidos (checkboxes)
$incP = true; $incF = true; $incV = true;
if (is_array($typesIn)) {
  $incP = $toBool($typesIn['p'] ?? null, true);
  $incF = $toBool($typesIn['f'] ?? null, true);
  $incV = $toBool($typesIn['v'] ?? null, true);
}

// Verifica PDO
if (!isset($pdo) || !($pdo instanceof PDO)) {
  json_response(['ok'=>false,'message'=>'No se detectó $pdo en conexion.php'], 500);
}
fp_ensure_percapita_columns($pdo);

function labelsDataFromRows(array $rows, string $labelKey, string $countKey): array {
  $labels = [];
  $data = [];
  foreach ($rows as $r) {
    $labels[] = (string)($r[$labelKey] ?? '');
    $data[]   = (int)($r[$countKey] ?? 0);
  }
  return ['labels'=>$labels, 'data'=>$data];
}

function emptySummary(): array {
  return [
    'pTotal'=>0,'fTotal'=>0,'vTotal'=>0,
    'inscYes'=>0,'inscNo'=>0,
    'percYes'=>0,'percNo'=>0,
    'migYes'=>0,'migNo'=>0,'migNd'=>0,
    'geoYes'=>0,'geoNo'=>0,
    'cronicosTotal'=>0,'g0'=>0,'g1'=>0,'g2'=>0,'g3'=>0,
    'genero'=>['labels'=>[],'data'=>[]],
    'bySz'=>['labels'=>[],'data'=>[]],
    'bySec'=>['labels'=>[],'data'=>[]],
    'byEstratificacion'=>['labels'=>[],'data'=>[]],
    'bySzSec'=>[],
    'famRisk'=>['total'=>0,'bajo'=>0,'medio'=>0,'alto'=>0,'sinDato'=>0]
  ];
}

try {
  // ===== total base (sin filtros) =====
  $baseP = (int)$pdo->query("SELECT COUNT(*) FROM usuarios")->fetchColumn();
  $baseF = (int)$pdo->query("SELECT COUNT(*) FROM familias")->fetchColumn();
  $baseV = (int)$pdo->query("SELECT COUNT(*) FROM vivienda")->fetchColumn();

  $summary = emptySummary();

  // ===== WHERE comunes =====
  $whereP = [];
  $paramsP = [];
  if ($sz !== null) { $whereP[] = "sectorizacion = :sz"; $paramsP[':sz'] = $sz; }
  if ($sec !== null){ $whereP[] = "sector = :sec";       $paramsP[':sec'] = $sec; }
  if ($estratificacion !== '' && in_array($estratificacion, ['G0','G1','G2','G3'], true)) {
    $whereP[] = "COALESCE(NULLIF(estratificacion_riesgo, ''), 'G0') = :estratificacion";
    $paramsP[':estratificacion'] = $estratificacion;
  }
  if ($percapitado === '1' || $percapitado === '0') {
    $whereP[] = "percapitado = :percapitado";
    $paramsP[':percapitado'] = (int)$percapitado;
  }
  if ($diagnosticoQ !== '') {
    $whereP[] = "(
      COALESCE(detalle_diagnosticos_cronicos, '') LIKE :diag
      OR EXISTS (
        SELECT 1
        FROM usuario_diagnosticos_cronicos ud
        WHERE ud.usuario_rut = usuarios.rut
          AND (
            COALESCE(ud.codigo_diagnostico, '') LIKE :diag
            OR COALESCE(ud.nombre_diagnostico, '') LIKE :diag
            OR COALESCE(ud.diagnostico_completo, '') LIKE :diag
          )
      )
    )";
    $paramsP[':diag'] = '%' . $diagnosticoQ . '%';
  }

  // geo solo afecta PERSONAS
  if ($geo === "1") {
    $whereP[] = "(latitud IS NOT NULL AND TRIM(latitud) <> '' AND longitud IS NOT NULL AND TRIM(longitud) <> '')";
  } elseif ($geo === "0") {
    $whereP[] = "((latitud IS NULL OR TRIM(latitud) = '') OR (longitud IS NULL OR TRIM(longitud) = ''))";
  }

  $wherePSql = $whereP ? (" WHERE " . implode(" AND ", $whereP)) : "";

  // ===== PERSONAS =====
  if ($incP) {
    // KPIs personas
    $sql = "
      SELECT
        COUNT(*) AS pTotal,
        SUM(CASE WHEN inscrito_cesfam = 1 THEN 1 ELSE 0 END) AS inscYes,
        SUM(CASE WHEN (inscrito_cesfam = 0 OR inscrito_cesfam IS NULL) THEN 1 ELSE 0 END) AS inscNo,
        SUM(CASE WHEN percapitado = 1 THEN 1 ELSE 0 END) AS percYes,
        SUM(CASE WHEN percapitado = 0 OR percapitado IS NULL THEN 1 ELSE 0 END) AS percNo,
        SUM(CASE WHEN migrante = 1 THEN 1 ELSE 0 END) AS migYes,
        SUM(CASE WHEN migrante = 0 THEN 1 ELSE 0 END) AS migNo,
        SUM(CASE WHEN migrante IS NULL THEN 1 ELSE 0 END) AS migNd,
        SUM(CASE WHEN (latitud IS NOT NULL AND TRIM(latitud) <> '' AND longitud IS NOT NULL AND TRIM(longitud) <> '') THEN 1 ELSE 0 END) AS geoYes,
        SUM(CASE WHEN ((latitud IS NULL OR TRIM(latitud) = '') OR (longitud IS NULL OR TRIM(longitud) = '')) THEN 1 ELSE 0 END) AS geoNo,
        SUM(CASE WHEN COALESCE(cantidad_condiciones_cronicas, (SELECT COUNT(*) FROM usuario_diagnosticos_cronicos udc WHERE udc.usuario_rut = usuarios.rut), CASE WHEN COALESCE(TRIM(detalle_diagnosticos_cronicos), '') = '' THEN 0 ELSE (LENGTH(detalle_diagnosticos_cronicos) - LENGTH(REPLACE(detalle_diagnosticos_cronicos, '|', '')) + 1) END, 0) > 0 THEN 1 ELSE 0 END) AS cronicosTotal,
        SUM(CASE WHEN COALESCE(NULLIF(estratificacion_riesgo, ''), 'G0') = 'G0' THEN 1 ELSE 0 END) AS g0,
        SUM(CASE WHEN COALESCE(NULLIF(estratificacion_riesgo, ''), 'G0') = 'G1' THEN 1 ELSE 0 END) AS g1,
        SUM(CASE WHEN COALESCE(NULLIF(estratificacion_riesgo, ''), 'G0') = 'G2' THEN 1 ELSE 0 END) AS g2,
        SUM(CASE WHEN COALESCE(NULLIF(estratificacion_riesgo, ''), 'G0') = 'G3' THEN 1 ELSE 0 END) AS g3
      FROM usuarios
      $wherePSql
    ";
    $st = $pdo->prepare($sql);
    foreach ($paramsP as $k=>$v) $st->bindValue($k, $v, is_int($v) ? PDO::PARAM_INT : PDO::PARAM_STR);
    $st->execute();
    $row = $st->fetch(PDO::FETCH_ASSOC) ?: [];

    $summary['pTotal']  = (int)($row['pTotal'] ?? 0);
    $summary['inscYes'] = (int)($row['inscYes'] ?? 0);
    $summary['inscNo']  = (int)($row['inscNo'] ?? 0);
    $summary['percYes'] = (int)($row['percYes'] ?? 0);
    $summary['percNo']  = (int)($row['percNo'] ?? 0);
    $summary['migYes']  = (int)($row['migYes'] ?? 0);
    $summary['migNo']   = (int)($row['migNo'] ?? 0);
    $summary['migNd']   = (int)($row['migNd'] ?? 0);
    $summary['geoYes']  = (int)($row['geoYes'] ?? 0);
    $summary['geoNo']   = (int)($row['geoNo'] ?? 0);
    $summary['cronicosTotal'] = (int)($row['cronicosTotal'] ?? 0);
    $summary['g0'] = (int)($row['g0'] ?? 0);
    $summary['g1'] = (int)($row['g1'] ?? 0);
    $summary['g2'] = (int)($row['g2'] ?? 0);
    $summary['g3'] = (int)($row['g3'] ?? 0);

    // Genero (normalizado como tu JS demo)
    $sqlG = "
      SELECT
        CASE
          WHEN genero IS NULL OR TRIM(genero) = '' THEN 'Sin dato'
          WHEN LOWER(TRIM(genero)) LIKE 'f%' THEN 'Femenino'
          WHEN LOWER(TRIM(genero)) LIKE 'm%' THEN 'Masculino'
          ELSE 'Otro'
        END AS genero_norm,
        COUNT(*) AS c
      FROM usuarios
      $wherePSql
      GROUP BY genero_norm
      ORDER BY c DESC
    ";
    $stG = $pdo->prepare($sqlG);
    foreach ($paramsP as $k=>$v) $stG->bindValue($k, $v, is_int($v) ? PDO::PARAM_INT : PDO::PARAM_STR);
    $stG->execute();
    $rowsG = $stG->fetchAll(PDO::FETCH_ASSOC) ?: [];
    $summary['genero'] = labelsDataFromRows($rowsG, 'genero_norm', 'c');

    // Personas por sectorizacion
    $sqlSz = "
      SELECT
        COALESCE(CAST(sectorizacion AS CHAR), 'Sin dato') AS lab,
        COUNT(*) AS c
      FROM usuarios
      $wherePSql
      GROUP BY lab
      ORDER BY c DESC
    ";
    $stSz = $pdo->prepare($sqlSz);
    foreach ($paramsP as $k=>$v) $stSz->bindValue($k, $v, is_int($v) ? PDO::PARAM_INT : PDO::PARAM_STR);
    $stSz->execute();
    $rowsSz = $stSz->fetchAll(PDO::FETCH_ASSOC) ?: [];
    $summary['bySz'] = labelsDataFromRows($rowsSz, 'lab', 'c');

    // Personas por sector
    $sqlSec = "
      SELECT
        COALESCE(CAST(sector AS CHAR), 'Sin dato') AS lab,
        COUNT(*) AS c
      FROM usuarios
      $wherePSql
      GROUP BY lab
      ORDER BY c DESC
    ";
    $stSec = $pdo->prepare($sqlSec);
    foreach ($paramsP as $k=>$v) $stSec->bindValue($k, $v, is_int($v) ? PDO::PARAM_INT : PDO::PARAM_STR);
    $stSec->execute();
    $rowsSec = $stSec->fetchAll(PDO::FETCH_ASSOC) ?: [];
    $summary['bySec'] = labelsDataFromRows($rowsSec, 'lab', 'c');

    // Personas por estratificación
    $sqlER = "
      SELECT
        CASE
          WHEN estratificacion_riesgo IS NULL OR TRIM(estratificacion_riesgo) = '' THEN 'G0'
          ELSE estratificacion_riesgo
        END AS lab,
        COUNT(*) AS c
      FROM usuarios
      $wherePSql
      GROUP BY lab
      ORDER BY FIELD(lab, 'G0', 'G1', 'G2', 'G3', 'Sin dato'), c DESC
    ";
    $stER = $pdo->prepare($sqlER);
    foreach ($paramsP as $k=>$v) {
      $stER->bindValue($k, $v, is_int($v) ? PDO::PARAM_INT : PDO::PARAM_STR);
    }
    $stER->execute();
    $rowsER = $stER->fetchAll(PDO::FETCH_ASSOC) ?: [];
    $summary['byEstratificacion'] = labelsDataFromRows($rowsER, 'lab', 'c');

    // Personas por sectorizacion+sector (para Top)
    $sqlSzSec = "
      SELECT
        COALESCE(CAST(sectorizacion AS CHAR), 'Sin dato') AS sz,
        COALESCE(CAST(sector AS CHAR), 'Sin dato') AS sec,
        COUNT(*) AS c
      FROM usuarios
      $wherePSql
      GROUP BY sz, sec
      ORDER BY c DESC
      LIMIT 250
    ";
    $stSS = $pdo->prepare($sqlSzSec);
    foreach ($paramsP as $k=>$v) $stSS->bindValue($k, $v, is_int($v) ? PDO::PARAM_INT : PDO::PARAM_STR);
    $stSS->execute();
    $rowsSS = $stSS->fetchAll(PDO::FETCH_ASSOC) ?: [];
    $outSS = [];
    foreach ($rowsSS as $r) {
      $outSS[] = [
        'sz' => (string)($r['sz'] ?? 'Sin dato'),
        'sec'=> (string)($r['sec'] ?? 'Sin dato'),
        'count' => (int)($r['c'] ?? 0),
      ];
    }
    $summary['bySzSec'] = $outSS;
  }

  // ===== FAMILIAS (solo conteo con filtros sz/sec) =====
  if ($incF) {
    $whereF = [];
    $paramsF = [];
    if ($sz !== null) { $whereF[] = "sectorizacion = :sz"; $paramsF[':sz'] = $sz; }
    if ($sec !== null){ $whereF[] = "sector = :sec";       $paramsF[':sec'] = $sec; }
    $whereFSql = $whereF ? (" WHERE " . implode(" AND ", $whereF)) : "";

    $stF = $pdo->prepare("SELECT COUNT(*) FROM familias $whereFSql");
    foreach ($paramsF as $k=>$v) $stF->bindValue($k, $v, PDO::PARAM_INT);
    $stF->execute();
    $summary['fTotal'] = (int)$stF->fetchColumn();
  

    // ===== Clasificación familiar (tabla clasificacion_familiar) =====
    // La app (familias.js) guarda: puntaje_riesgo y clasificacion_riesgo (Bajo/Medio/Alto)
    // en tabla clasificacion_familiar, con upsert (1 registro por familia).
    //
    // Por si existieran múltiples registros, tomamos el último por ultima_actualizacion.
    $sqlR = "
      SELECT
        COUNT(DISTINCT f.codigo_familia) AS total,
        SUM(CASE WHEN cf.clasificacion_riesgo IS NULL OR TRIM(cf.clasificacion_riesgo) = '' THEN 1 ELSE 0 END) AS sinDato,
        SUM(CASE WHEN LOWER(TRIM(cf.clasificacion_riesgo)) LIKE '%baj%' THEN 1 ELSE 0 END) AS bajo,
        SUM(CASE WHEN LOWER(TRIM(cf.clasificacion_riesgo)) LIKE '%med%' THEN 1 ELSE 0 END) AS medio,
        SUM(CASE WHEN LOWER(TRIM(cf.clasificacion_riesgo)) LIKE '%alt%' THEN 1 ELSE 0 END) AS alto
      FROM familias f
      LEFT JOIN (
        SELECT c1.codigo_familia, c1.clasificacion_riesgo
        FROM clasificacion_familiar c1
        INNER JOIN (
          SELECT codigo_familia, MAX(ultima_actualizacion) AS mx
          FROM clasificacion_familiar
          GROUP BY codigo_familia
        ) t ON t.codigo_familia = c1.codigo_familia AND t.mx = c1.ultima_actualizacion
      ) cf ON cf.codigo_familia = f.codigo_familia
      $whereFSql
    ";
    $stR = $pdo->prepare($sqlR);
    foreach ($paramsF as $k=>$v) $stR->bindValue($k, $v, PDO::PARAM_INT);
    $stR->execute();
    $r = $stR->fetch(PDO::FETCH_ASSOC) ?: [];

    $summary['famRisk'] = [
      'total'   => (int)($r['total'] ?? $summary['fTotal']),
      'bajo'    => (int)($r['bajo'] ?? 0),
      'medio'   => (int)($r['medio'] ?? 0),
      'alto'    => (int)($r['alto'] ?? 0),
      'sinDato' => (int)($r['sinDato'] ?? 0),
    ];

}

  // ===== VIVIENDAS (solo conteo con filtros sz/sec) =====
  if ($incV) {
    $whereV = [];
    $paramsV = [];
    if ($sz !== null) { $whereV[] = "sectorizacion = :sz"; $paramsV[':sz'] = $sz; }
    if ($sec !== null){ $whereV[] = "sector = :sec";       $paramsV[':sec'] = $sec; }
    $whereVSql = $whereV ? (" WHERE " . implode(" AND ", $whereV)) : "";

    $stV = $pdo->prepare("SELECT COUNT(*) FROM vivienda $whereVSql");
    foreach ($paramsV as $k=>$v) $stV->bindValue($k, $v, PDO::PARAM_INT);
    $stV->execute();
    $summary['vTotal'] = (int)$stV->fetchColumn();
  }

  json_response([
    'ok' => true,
    'meta' => [
      'total_base' => [
        'p' => $baseP,
        'f' => $baseF,
        'v' => $baseV,
        'all' => ($baseP + $baseF + $baseV),
      ],
    ],
    'summary' => $summary,
  ]);

} catch (Throwable $e) {
  error_log("REPORTES SUMMARY ERROR: " . $e->getMessage());
  json_response(['ok'=>false,'message'=>'Error generando reporte.'], 500);
}
