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

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

if (!function_exists('safe_read_json')) {
  function safe_read_json(): array {
    $raw = file_get_contents('php://input');
    if ($raw === false || trim($raw) === '') return [];
    $j = json_decode($raw, true);
    return is_array($j) ? $j : [];
  }
}

function s($v): string { return trim((string)($v ?? '')); }
function i_or_null($v): ?int {
  if ($v === '' || $v === null) return null;
  if (!is_numeric($v)) return null;
  return (int)$v;
}
function f_or_null($v): ?float {
  if ($v === '' || $v === null) return null;
  if (!is_numeric($v)) return null;
  return (float)$v;
}

function normalize_diag_label(string $raw): string {
  $label = trim($raw);
  if ($label === '') return '';
  $label = preg_replace('/^\d{6,8}\s*/u', '', $label) ?? $label;
  if (preg_match('/^\[([^\]]+)\]\s*-\s*(.+)$/u', $label, $m) === 1) {
    $label = trim((string)$m[2]);
  } elseif (preg_match('/^[A-Z][0-9][0-9A-Z.]*\s*-?\s*(.+)$/iu', $label, $m) === 1) {
    $label = trim((string)$m[1]);
  }
  $label = preg_replace('/\s+/u', ' ', $label) ?? $label;
  return trim($label);
}

function split_diag_parts(string $raw): array {
  $raw = preg_replace('/[\r\n]+/u', '|', $raw);
  $raw = preg_replace('/[;,]+/u', '|', $raw);
  $raw = preg_replace('/\s*\|\s*/u', '|', $raw);
  $parts = array_map(static fn($x) => trim((string)$x), explode('|', (string)$raw));
  return array_values(array_filter($parts, static fn($x) => $x !== ''));
}

try {
  fp_ensure_percapita_columns($pdo);

  $method = strtoupper($_SERVER['REQUEST_METHOD'] ?? 'GET');
  $src = ($method === 'POST') ? safe_read_json() : $_GET;
  $mode = s($src['mode'] ?? '');

  if ($mode === 'diagnostics') {
    $seen = [];
    $diagnosticos = [];

    try {
      $sql = "SELECT COALESCE(nombre_diagnostico, diagnostico_completo, codigo_diagnostico, '') AS diagnostico
              FROM usuario_diagnosticos_cronicos
              WHERE COALESCE(TRIM(nombre_diagnostico), TRIM(diagnostico_completo), TRIM(codigo_diagnostico), '') <> ''";
      $rows = $pdo->query($sql)->fetchAll(PDO::FETCH_COLUMN);
      foreach ($rows as $row) {
        $label = normalize_diag_label((string)$row);
        if ($label === '') continue;
        $key = mb_strtolower($label, 'UTF-8');
        if (isset($seen[$key])) continue;
        $seen[$key] = true;
        $diagnosticos[] = $label;
      }
    } catch (Throwable $e) {
    }

    try {
      $sql = "SELECT COALESCE(detalle_diagnosticos_cronicos, '') AS detalle
              FROM usuarios
              WHERE COALESCE(TRIM(detalle_diagnosticos_cronicos), '') <> ''";
      $rows = $pdo->query($sql)->fetchAll(PDO::FETCH_COLUMN);
      foreach ($rows as $row) {
        foreach (split_diag_parts((string)$row) as $part) {
          $label = normalize_diag_label($part);
          if ($label === '') continue;
          $key = mb_strtolower($label, 'UTF-8');
          if (isset($seen[$key])) continue;
          $seen[$key] = true;
          $diagnosticos[] = $label;
        }
      }
    } catch (Throwable $e) {
    }

    natcasesort($diagnosticos);
    json_response([
      'ok' => true,
      'diagnosticos' => array_values($diagnosticos)
    ]);
    exit;
  }

  if ($mode === 'filters') {
    $sz = s($src['sectorizacion'] ?? '');
    $szId = ($sz !== '' && ctype_digit($sz)) ? (int)$sz : null;

    $sectorizaciones = [];
    try {
      $sectorizaciones = $pdo
        ->query("SELECT id, nombre_sectorizacion, color_sector FROM sectorizacion ORDER BY nombre_sectorizacion ASC")
        ->fetchAll(PDO::FETCH_ASSOC);
    } catch (Throwable $e) {
      $sectorizaciones = [];
    }

    $relCol = null;
    foreach (['sectorizacion_id', 'sectorizacion', 'id_sectorizacion'] as $candidate) {
      try {
        $st = $pdo->prepare("SHOW COLUMNS FROM `sectores` LIKE :c");
        $st->execute([':c' => $candidate]);
        if ($st->fetch(PDO::FETCH_ASSOC)) { $relCol = $candidate; break; }
      } catch (Throwable $e) {
      }
    }

    $sectores = [];
    try {
      if ($relCol) {
        if ($szId !== null) {
          $st = $pdo->prepare("SELECT id, nombre_sector, `$relCol` AS sectorizacion_id
                               FROM sectores
                               WHERE `$relCol` = :sz
                               ORDER BY nombre_sector ASC");
          $st->execute([':sz' => $szId]);
          $sectores = $st->fetchAll(PDO::FETCH_ASSOC);
        } else {
          $sectores = $pdo
            ->query("SELECT id, nombre_sector, `$relCol` AS sectorizacion_id FROM sectores ORDER BY nombre_sector ASC")
            ->fetchAll(PDO::FETCH_ASSOC);
        }
      } else {
        $sectores = $pdo
          ->query("SELECT id, nombre_sector FROM sectores ORDER BY nombre_sector ASC")
          ->fetchAll(PDO::FETCH_ASSOC);
      }
    } catch (Throwable $e) {
      $sectores = [];
    }

    json_response([
      'ok' => true,
      'sectorizaciones' => $sectorizaciones,
      'sectores' => $sectores
    ]);
    exit;
  }

  $q  = s($src['q'] ?? '');
  $inscrito = s($src['inscrito_cesfam'] ?? '');
  $migrante = s($src['migrante'] ?? '');
  $percapitado = s($src['percapitado'] ?? '');
  $genero = s($src['genero'] ?? '');
  $edadMin = i_or_null($src['edad_min'] ?? null);
  $edadMax = i_or_null($src['edad_max'] ?? null);
  $sectorizacion = s($src['sectorizacion'] ?? '');
  $sector = s($src['sector'] ?? '');
  $estratificacion = strtoupper(s($src['estratificacion_riesgo'] ?? ''));
  $diagnosticoQ = s($src['diagnostico_q'] ?? '');
  $diagnosticos = array_values(array_filter(array_map('s', is_array($src['diagnosticos'] ?? null) ? $src['diagnosticos'] : []), static fn($v) => $v !== ''));

  $latMin = f_or_null($src['lat_min'] ?? null);
  $latMax = f_or_null($src['lat_max'] ?? null);
  $lngMin = f_or_null($src['lng_min'] ?? null);
  $lngMax = f_or_null($src['lng_max'] ?? null);

  $limit  = i_or_null($src['limit'] ?? null) ?? 5000;
  $offset = i_or_null($src['offset'] ?? null) ?? 0;

  if ($limit < 1) $limit = 1;
  if ($limit > 20000) $limit = 20000;
  if ($offset < 0) $offset = 0;

  $baseWhere = [];
  $params = [];

  if ($q !== '') {
    $baseWhere[] = "(
      u.rut LIKE :q
      OR COALESCE(u.nombres,'') LIKE :q
      OR COALESCE(u.apellidos,'') LIKE :q
      OR COALESCE(u.direccion,'') LIKE :q
    )";
    $params[':q'] = '%' . $q . '%';
  }

  if ($inscrito !== '' && ($inscrito === '0' || $inscrito === '1')) {
    $baseWhere[] = 'u.inscrito_cesfam = :inscrito';
    $params[':inscrito'] = (int)$inscrito;
  }

  if ($migrante !== '' && ($migrante === '0' || $migrante === '1')) {
    $baseWhere[] = 'u.migrante = :migrante';
    $params[':migrante'] = (int)$migrante;
  }

  if ($percapitado !== '' && ($percapitado === '0' || $percapitado === '1')) {
    $baseWhere[] = 'u.percapitado = :percapitado';
    $params[':percapitado'] = (int)$percapitado;
  }

  $g = mb_strtolower(trim($genero), 'UTF-8');
  if (in_array($g, ['f','m','o'], true)) {
    if ($g === 'f') $baseWhere[] = "(u.genero IS NOT NULL AND TRIM(u.genero) <> '' AND LOWER(TRIM(u.genero)) LIKE 'f%')";
    if ($g === 'm') $baseWhere[] = "(u.genero IS NOT NULL AND TRIM(u.genero) <> '' AND LOWER(TRIM(u.genero)) LIKE 'm%')";
    if ($g === 'o') $baseWhere[] = "(u.genero IS NOT NULL AND TRIM(u.genero) <> '' AND LOWER(TRIM(u.genero)) NOT LIKE 'f%' AND LOWER(TRIM(u.genero)) NOT LIKE 'm%')";
  }

  if ($edadMin !== null) {
    $baseWhere[] = "COALESCE(NULLIF(u.edad, 0), CASE WHEN NULLIF(u.fecha_nacimiento,'0000-00-00') IS NOT NULL AND u.fecha_nacimiento >= '1900-01-01' AND u.fecha_nacimiento <= CURDATE() THEN TIMESTAMPDIFF(YEAR, u.fecha_nacimiento, CURDATE()) END, 0) >= :edadMin";
    $params[':edadMin'] = $edadMin;
  }
  if ($edadMax !== null) {
    $baseWhere[] = "COALESCE(NULLIF(u.edad, 0), CASE WHEN NULLIF(u.fecha_nacimiento,'0000-00-00') IS NOT NULL AND u.fecha_nacimiento >= '1900-01-01' AND u.fecha_nacimiento <= CURDATE() THEN TIMESTAMPDIFF(YEAR, u.fecha_nacimiento, CURDATE()) END, 0) <= :edadMax";
    $params[':edadMax'] = $edadMax;
  }

  if ($sectorizacion !== '' && ctype_digit($sectorizacion)) {
    $baseWhere[] = 'u.sectorizacion = :sectorizacion';
    $params[':sectorizacion'] = (int)$sectorizacion;
  }

  if ($sector !== '' && ctype_digit($sector)) {
    $baseWhere[] = 'u.sector = :sector';
    $params[':sector'] = (int)$sector;
  }

  if ($estratificacion !== '' && in_array($estratificacion, ['G0','G1','G2','G3'], true)) {
    $baseWhere[] = "COALESCE(NULLIF(u.estratificacion_riesgo, ''), 'G0') = :estratificacion";
    $params[':estratificacion'] = $estratificacion;
  }

  if ($diagnosticos) {
    $diagClauses = [];
    foreach ($diagnosticos as $idx => $diag) {
      $paramLike = ':diagLike' . $idx;
      $paramEq = ':diagEq' . $idx;
      $params[$paramLike] = '%' . $diag . '%';
      $params[$paramEq] = $diag;
      $diagClauses[] = "(
        COALESCE(u.detalle_diagnosticos_cronicos, '') LIKE $paramLike
        OR EXISTS (
          SELECT 1
          FROM usuario_diagnosticos_cronicos ud
          WHERE ud.usuario_rut = u.rut
            AND (
              COALESCE(ud.codigo_diagnostico, '') = $paramEq
              OR COALESCE(ud.nombre_diagnostico, '') = $paramEq
              OR COALESCE(ud.diagnostico_completo, '') = $paramEq
              OR COALESCE(ud.codigo_diagnostico, '') LIKE $paramLike
              OR COALESCE(ud.nombre_diagnostico, '') LIKE $paramLike
              OR COALESCE(ud.diagnostico_completo, '') LIKE $paramLike
            )
        )
      )";
    }
    $baseWhere[] = '(' . implode(' OR ', $diagClauses) . ')';
  } elseif ($diagnosticoQ !== '') {
    $baseWhere[] = "(
      COALESCE(u.detalle_diagnosticos_cronicos, '') LIKE :diag
      OR EXISTS (
        SELECT 1
        FROM usuario_diagnosticos_cronicos ud
        WHERE ud.usuario_rut = u.rut
          AND (
            COALESCE(ud.codigo_diagnostico, '') LIKE :diag
            OR COALESCE(ud.nombre_diagnostico, '') LIKE :diag
            OR COALESCE(ud.diagnostico_completo, '') LIKE :diag
          )
      )
    )";
    $params[':diag'] = '%' . $diagnosticoQ . '%';
  }

  if ($mode === 'sector_summary') {
    $where = $baseWhere;
    $whereSql = $where ? (' WHERE ' . implode(' AND ', $where)) : '';

    $sql = "
      SELECT
        COALESCE(u.sector, 0) AS sector_id,
        COALESCE(s.nombre_sector, 'Sin sector') AS sector_nombre,
        COALESCE(u.sectorizacion, s.sectorizacion, 0) AS sectorizacion_id,
        COALESCE(z.nombre_sectorizacion, 'Sin sectorización') AS sectorizacion_nombre,
        COALESCE(z.color_sector, '#94a3b8') AS sectorizacion_color,

        COUNT(*) AS total_personas,
        SUM(CASE WHEN u.inscrito_cesfam = 1 THEN 1 ELSE 0 END) AS inscritos,
        SUM(CASE WHEN u.migrante = 1 THEN 1 ELSE 0 END) AS migrantes,
        SUM(CASE WHEN COALESCE(NULLIF(u.estratificacion_riesgo, ''), 'G0') = 'G0' THEN 1 ELSE 0 END) AS g0,
        SUM(CASE WHEN COALESCE(NULLIF(u.estratificacion_riesgo, ''), 'G0') = 'G1' THEN 1 ELSE 0 END) AS g1,
        SUM(CASE WHEN COALESCE(NULLIF(u.estratificacion_riesgo, ''), 'G0') = 'G2' THEN 1 ELSE 0 END) AS g2,
        SUM(CASE WHEN COALESCE(NULLIF(u.estratificacion_riesgo, ''), 'G0') = 'G3' THEN 1 ELSE 0 END) AS g3,
        SUM(CASE WHEN COALESCE(u.cantidad_condiciones_cronicas, diag.diag_count, 0) > 0 THEN 1 ELSE 0 END) AS con_cronicos,
        SUM(COALESCE(u.cantidad_condiciones_cronicas, diag.diag_count, 0)) AS total_condiciones_cronicas,

        AVG(CASE
          WHEN u.latitud IS NOT NULL AND u.longitud IS NOT NULL
           AND TRIM(CAST(u.latitud AS CHAR)) NOT IN ('', '0', '0.0', '0.000000')
           AND TRIM(CAST(u.longitud AS CHAR)) NOT IN ('', '0', '0.0', '0.000000')
          THEN CAST(u.latitud AS DECIMAL(10,6))
        END) AS avg_lat,
        AVG(CASE
          WHEN u.latitud IS NOT NULL AND u.longitud IS NOT NULL
           AND TRIM(CAST(u.latitud AS CHAR)) NOT IN ('', '0', '0.0', '0.000000')
           AND TRIM(CAST(u.longitud AS CHAR)) NOT IN ('', '0', '0.0', '0.000000')
          THEN CAST(u.longitud AS DECIMAL(10,6))
        END) AS avg_lng,
        SUM(CASE
          WHEN u.latitud IS NOT NULL AND u.longitud IS NOT NULL
           AND TRIM(CAST(u.latitud AS CHAR)) NOT IN ('', '0', '0.0', '0.000000')
           AND TRIM(CAST(u.longitud AS CHAR)) NOT IN ('', '0', '0.0', '0.000000')
          THEN 1 ELSE 0 END
        ) AS with_coords
      FROM usuarios u
      LEFT JOIN sectores s ON s.id = u.sector
      LEFT JOIN sectorizacion z ON z.id = COALESCE(u.sectorizacion, s.sectorizacion)
      LEFT JOIN (
        SELECT usuario_rut, COUNT(*) AS diag_count
        FROM usuario_diagnosticos_cronicos
        GROUP BY usuario_rut
      ) diag ON diag.usuario_rut = u.rut
      $whereSql
      GROUP BY sector_id, sector_nombre, sectorizacion_id, sectorizacion_nombre, sectorizacion_color
      ORDER BY total_personas DESC, sector_nombre ASC
    ";

    $stmt = $pdo->prepare($sql);
    foreach ($params as $k => $v) $stmt->bindValue($k, $v);
    $stmt->execute();
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $sqlCount = 'SELECT COUNT(*) FROM usuarios u' . $whereSql;
    $stC = $pdo->prepare($sqlCount);
    foreach ($params as $k => $v) $stC->bindValue($k, $v);
    $stC->execute();
    $total = (int)$stC->fetchColumn();

    $withReference = 0;
    foreach ($rows as $r) {
      if ($r['avg_lat'] !== null && $r['avg_lng'] !== null) $withReference++;
    }

    json_response([
      'ok' => true,
      'summary' => $rows,
      'meta' => [
        'total' => $total,
        'sectors' => count($rows),
        'with_reference_points' => $withReference
      ]
    ]);
    exit;
  }

  $where = $baseWhere;
  $where[] = "(
    u.latitud IS NOT NULL AND u.longitud IS NOT NULL
    AND TRIM(CAST(u.latitud AS CHAR)) NOT IN ('', '0', '0.0', '0.000000')
    AND TRIM(CAST(u.longitud AS CHAR)) NOT IN ('', '0', '0.0', '0.000000')
  )";

  if ($latMin !== null && $latMax !== null) {
    $where[] = 'CAST(u.latitud AS DECIMAL(10,6)) BETWEEN :latMin AND :latMax';
    $params[':latMin'] = $latMin;
    $params[':latMax'] = $latMax;
  }
  if ($lngMin !== null && $lngMax !== null) {
    $where[] = 'CAST(u.longitud AS DECIMAL(10,6)) BETWEEN :lngMin AND :lngMax';
    $params[':lngMin'] = $lngMin;
    $params[':lngMax'] = $lngMax;
  }

  $whereSql = $where ? (' WHERE ' . implode(' AND ', $where)) : '';

  $sqlCount = 'SELECT COUNT(*) FROM usuarios u' . $whereSql;
  $stC = $pdo->prepare($sqlCount);
  foreach ($params as $k => $v) $stC->bindValue($k, $v);
  $stC->execute();
  $total = (int)$stC->fetchColumn();

  $sql = "
    SELECT
      u.rut,
      u.nombres,
      u.apellidos,
      u.direccion,
      u.genero,
      CASE
        WHEN (u.edad IS NULL OR u.edad = 0)
          AND NULLIF(u.fecha_nacimiento,'0000-00-00') IS NOT NULL
          AND u.fecha_nacimiento >= '1900-01-01'
          AND u.fecha_nacimiento <= CURDATE()
        THEN TIMESTAMPDIFF(YEAR, u.fecha_nacimiento, CURDATE())
        ELSE u.edad
      END AS edad,
      u.celular,
      u.telefonos,
      u.inscrito_cesfam,
      u.percapitado,
      u.fecha_actualizacion_percapita,
      u.migrante,
      COALESCE(NULLIF(u.estratificacion_riesgo, ''), 'G0') AS estratificacion_riesgo,
      COALESCE(
        u.cantidad_condiciones_cronicas,
        diag.diag_count,
        CASE
          WHEN COALESCE(TRIM(u.detalle_diagnosticos_cronicos), '') = '' THEN 0
          ELSE (LENGTH(u.detalle_diagnosticos_cronicos) - LENGTH(REPLACE(u.detalle_diagnosticos_cronicos, '|', '')) + 1)
        END,
        0
      ) AS cantidad_condiciones_cronicas,
      u.detalle_diagnosticos_cronicos,
      u.sectorizacion AS sectorizacion,
      z.nombre_sectorizacion AS sectorizacion_nombre,
      z.color_sector AS sectorizacion_color,
      u.sector AS sector,
      s.nombre_sector AS sector_nombre,
      u.latitud,
      u.longitud
    FROM usuarios u
    LEFT JOIN sectorizacion z ON z.id = u.sectorizacion
    LEFT JOIN sectores s ON s.id = u.sector
    LEFT JOIN (
      SELECT usuario_rut, COUNT(*) AS diag_count
      FROM usuario_diagnosticos_cronicos
      GROUP BY usuario_rut
    ) diag ON diag.usuario_rut = u.rut
    $whereSql
    ORDER BY apellidos ASC, nombres ASC
    LIMIT $limit OFFSET $offset
  ";

  $stmt = $pdo->prepare($sql);
  foreach ($params as $k => $v) $stmt->bindValue($k, $v);
  $stmt->execute();
  $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

  json_response([
    'ok' => true,
    'data' => $rows,
    'meta' => ['limit' => $limit, 'offset' => $offset, 'total' => $total]
  ]);

} catch (Throwable $e) {
  error_log('GEO LIST ERROR: ' . $e->getMessage());
  json_response(['ok' => false, 'message' => 'Error listando geolocalización.'], 500);
}
