<?php
declare(strict_types=1);

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

$u = require_login();
require_module_write($u, "usuarios");

function iec_to_utf8(string $value): string {
  $value = preg_replace('/^\xEF\xBB\xBF/', '', $value) ?? $value;

  if (function_exists('mb_check_encoding') && mb_check_encoding($value, 'UTF-8')) {
    return $value;
  }

  $encodings = ['Windows-1252', 'ISO-8859-1', 'ISO-8859-15'];
  foreach ($encodings as $enc) {
    $converted = @iconv($enc, 'UTF-8//IGNORE', $value);
    if ($converted !== false && $converted !== '') {
      return $converted;
    }
  }

  $converted = @utf8_encode($value);
  return $converted !== false ? $converted : $value;
}

function iec_normalize_header(string $value): string {
  $value = iec_to_utf8(trim($value));
  if ($value === '') return '';

  $value = str_replace(["\xc2\xa0", "\u{00A0}"], ' ', $value);

  $converted = @iconv('UTF-8', 'ASCII//TRANSLIT//IGNORE', $value);
  if ($converted !== false && $converted !== '') {
    $value = $converted;
  }

  $value = strtolower($value);
  $value = preg_replace('/[^a-z0-9]+/u', '_', $value) ?? $value;
  $value = trim($value, '_');
  $value = preg_replace('/_+/', '_', $value) ?? $value;

  return $value;
}

function iec_header_score(string $normalized, string $field): int {
  if ($normalized === '') return 0;

  $tests = [
    'rut' => static function(string $h): int {
      if ($h === 'rut') return 100;
      return preg_match('/(^|_)rut($|_)/', $h) ? 80 : 0;
    },
    'dv' => static function(string $h): int {
      if ($h === 'dv') return 100;
      return preg_match('/(^|_)dv($|_)/', $h) ? 80 : 0;
    },
    'cantidad_condiciones_cronicas' => static function(string $h): int {
      if (str_contains($h, 'cantidad') && str_contains($h, 'condicion') && str_contains($h, 'cronic')) return 100;
      return 0;
    },
    'estratificacion_riesgo' => static function(string $h): int {
      $score = 0;
      if (str_contains($h, 'estratific')) $score += 50;
      if (str_contains($h, 'riesgo')) $score += 30;
      if (str_contains($h, 'actual')) $score += 20;
      return $score;
    },
    'fecha_actualizacion_estratificacion' => static function(string $h): int {
      $score = 0;
      if (str_contains($h, 'fecha')) $score += 35;
      if (str_contains($h, 'actualiz')) $score += 35;
      if (str_contains($h, 'estratific')) $score += 30;
      return $score;
    },
    'detalle_diagnosticos_cronicos' => static function(string $h): int {
      $score = 0;
      if (str_contains($h, 'detalle')) $score += 20;
      if (str_contains($h, 'diagnost')) $score += 40;
      if (str_contains($h, 'condicion') || str_contains($h, 'cronic')) $score += 20;
      if (str_contains($h, 'cronica') || str_contains($h, 'cronicos')) $score += 20;
      return $score;
    },
  ];

  return isset($tests[$field]) ? $tests[$field]($normalized) : 0;
}

function iec_find_header_map(array $row): array {
  $fields = [
    'rut',
    'dv',
    'cantidad_condiciones_cronicas',
    'estratificacion_riesgo',
    'fecha_actualizacion_estratificacion',
    'detalle_diagnosticos_cronicos',
  ];

  $map = [];
  $scores = [];

  foreach ($row as $colIndex => $rawHeader) {
    $normalized = iec_normalize_header((string)$rawHeader);
    if ($normalized === '') continue;

    foreach ($fields as $field) {
      $score = iec_header_score($normalized, $field);
      if ($score <= 0) continue;
      if (!isset($scores[$field]) || $score > $scores[$field]) {
        $scores[$field] = $score;
        $map[$field] = $colIndex;
      }
    }
  }

  return $map;
}

function iec_detect_header_row(array $rows): array {
  if (!$rows) {
    throw new RuntimeException('El archivo no contiene filas para procesar.');
  }

  $bestRowNumber = null;
  $bestMap = [];
  $bestScore = -1;
  $debug = [];

  foreach ($rows as $rowNumber => $row) {
    if ((int)$rowNumber > 30) break;
    if (!is_array($row) || !$row) continue;

    $map = iec_find_header_map($row);
    $score = 0;
    foreach (['rut', 'dv', 'estratificacion_riesgo', 'detalle_diagnosticos_cronicos'] as $required) {
      if (isset($map[$required])) $score += 100;
    }
    if (isset($map['cantidad_condiciones_cronicas'])) $score += 30;
    if (isset($map['fecha_actualizacion_estratificacion'])) $score += 20;

    $normalizedPreview = [];
    foreach (array_slice($row, 0, 12, true) as $cell) {
      $n = iec_normalize_header((string)$cell);
      if ($n !== '') $normalizedPreview[] = $n;
    }
    if ($normalizedPreview) {
      $debug[] = 'Fila ' . $rowNumber . ': ' . implode(' | ', $normalizedPreview);
    }

    if ($score > $bestScore) {
      $bestScore = $score;
      $bestRowNumber = (int)$rowNumber;
      $bestMap = $map;
    }
  }

  $requiredOk = isset($bestMap['rut'], $bestMap['dv'], $bestMap['estratificacion_riesgo'], $bestMap['detalle_diagnosticos_cronicos']);
  if (!$requiredOk || $bestRowNumber === null) {
    throw new RuntimeException(
      'No pude ubicar los encabezados requeridos. Necesito al menos RUT, DV, Estratificación de Riesgo Actual y Detalle de Condiciones Crónicas (Diagnósticos). Vista previa: ' .
      implode(' || ', array_slice($debug, 0, 6))
    );
  }

  return [$bestRowNumber, $bestMap];
}

function iec_detect_csv_delimiter(string $sample): string {
  $counts = [
    ';' => substr_count($sample, ';'),
    ',' => substr_count($sample, ','),
    "\t" => substr_count($sample, "\t"),
    '|' => substr_count($sample, '|'),
  ];
  arsort($counts);
  $delimiter = (string)array_key_first($counts);
  return $delimiter !== '' ? $delimiter : ',';
}

function iec_read_csv_rows(string $path): array {
  $handle = fopen($path, 'rb');
  if (!$handle) {
    throw new RuntimeException('No se pudo abrir el archivo CSV.');
  }

  $sample = '';
  for ($i = 0; $i < 5 && !feof($handle); $i++) {
    $sample .= (string)fgets($handle);
  }
  rewind($handle);

  $delimiter = iec_detect_csv_delimiter($sample);
  $rows = [];
  $line = 0;

  while (($data = fgetcsv($handle, 0, $delimiter)) !== false) {
    $line++;
    if ($data === [null]) continue;
    $row = [];
    foreach ($data as $idx => $value) {
      $row[$idx] = trim(iec_to_utf8((string)$value));
    }
    $rows[$line] = $row;
  }

  fclose($handle);
  return $rows;
}

function iec_xlsx_col_to_index(string $ref): int {
  $letters = preg_replace('/[^A-Z]/', '', strtoupper($ref)) ?? '';
  $index = 0;
  $len = strlen($letters);
  for ($i = 0; $i < $len; $i++) {
    $index = ($index * 26) + (ord($letters[$i]) - 64);
  }
  return max(0, $index - 1);
}

function iec_xlsx_text_nodes(DOMXPath $xpath, DOMNode $context, string $query): string {
  $parts = [];
  foreach ($xpath->query($query, $context) ?: [] as $node) {
    $parts[] = trim((string)$node->nodeValue);
  }
  return trim(implode('', array_filter($parts, static fn($v) => $v !== '')));
}

function iec_xlsx_cell_value(DOMXPath $xpath, DOMNode $cell, array $sharedStrings): string {
  $type = ($cell instanceof DOMElement) ? (string)$cell->getAttribute('t') : '';

  if ($type === 'inlineStr') {
    return iec_xlsx_text_nodes($xpath, $cell, './main:is//main:t');
  }

  $valueNode = $xpath->query('./main:v', $cell);
  $value = ($valueNode && $valueNode->length > 0) ? trim((string)$valueNode->item(0)->nodeValue) : '';

  if ($type === 's') {
    $idx = (int)$value;
    return isset($sharedStrings[$idx]) ? $sharedStrings[$idx] : '';
  }

  return $value;
}

function iec_read_xlsx_rows(string $path, int $sheetNumber = 1): array {
  if (!class_exists('ZipArchive')) {
    throw new RuntimeException('Tu PHP no tiene ZipArchive habilitado. Para XLSX habilita zip en php.ini o usa CSV.');
  }

  $zip = new ZipArchive();
  if ($zip->open($path) !== true) {
    throw new RuntimeException('No se pudo abrir el archivo Excel.');
  }

  $ns = 'http://schemas.openxmlformats.org/spreadsheetml/2006/main';
  $sharedStrings = [];
  $sharedXml = $zip->getFromName('xl/sharedStrings.xml');
  if ($sharedXml !== false) {
    $domShared = new DOMDocument();
    if (@$domShared->loadXML($sharedXml)) {
      $xpathShared = new DOMXPath($domShared);
      $xpathShared->registerNamespace('main', $ns);
      foreach ($xpathShared->query('//main:si') ?: [] as $si) {
        $sharedStrings[] = iec_to_utf8(iec_xlsx_text_nodes($xpathShared, $si, './/main:t'));
      }
    }
  }

  $sheetXml = $zip->getFromName('xl/worksheets/sheet' . $sheetNumber . '.xml');
  if ($sheetXml === false) {
    $zip->close();
    throw new RuntimeException('No encontré la hoja principal del Excel.');
  }

  $dom = new DOMDocument();
  if (!@$dom->loadXML($sheetXml)) {
    $zip->close();
    throw new RuntimeException('No se pudo leer el contenido interno del Excel.');
  }

  $xpath = new DOMXPath($dom);
  $xpath->registerNamespace('main', $ns);

  $rows = [];
  foreach ($xpath->query('//main:sheetData/main:row') ?: [] as $rowNode) {
    if (!$rowNode instanceof DOMElement) continue;

    $excelRow = (int)$rowNode->getAttribute('r');
    if ($excelRow <= 0) $excelRow = count($rows) + 1;

    $row = [];
    foreach ($xpath->query('./main:c', $rowNode) ?: [] as $cell) {
      if (!$cell instanceof DOMElement) continue;
      $ref = (string)$cell->getAttribute('r');
      $col = iec_xlsx_col_to_index($ref);
      $row[$col] = trim(iec_to_utf8(iec_xlsx_cell_value($xpath, $cell, $sharedStrings)));
    }
    if ($row) ksort($row);
    $rows[$excelRow] = $row;
  }

  $zip->close();
  ksort($rows);
  return $rows;
}

function iec_map_rows(array $rows): array {
  [$headerRowNumber, $headerMap] = iec_detect_header_row($rows);
  $out = [];

  foreach ($rows as $rowNumber => $rawRow) {
    if ((int)$rowNumber <= $headerRowNumber) continue;
    if (!is_array($rawRow) || !$rawRow) continue;

    $rutBase = isset($headerMap['rut'], $rawRow[$headerMap['rut']])
      ? preg_replace('/\D+/', '', (string)$rawRow[$headerMap['rut']])
      : '';
    $dv = isset($headerMap['dv'], $rawRow[$headerMap['dv']])
      ? strtoupper(trim((string)$rawRow[$headerMap['dv']]))
      : '';

    $rut = normalize_rut((string)$rutBase . (string)$dv);
    if ($rut === '') continue;

    $out[] = [
      'rut' => $rut,
      'cantidad_condiciones_cronicas' => isset($headerMap['cantidad_condiciones_cronicas'], $rawRow[$headerMap['cantidad_condiciones_cronicas']])
        ? trim((string)$rawRow[$headerMap['cantidad_condiciones_cronicas']]) : '',
      'estratificacion_riesgo' => isset($headerMap['estratificacion_riesgo'], $rawRow[$headerMap['estratificacion_riesgo']])
        ? strtoupper(trim((string)$rawRow[$headerMap['estratificacion_riesgo']])) : '',
      'fecha_actualizacion_estratificacion' => isset($headerMap['fecha_actualizacion_estratificacion'], $rawRow[$headerMap['fecha_actualizacion_estratificacion']])
        ? trim((string)$rawRow[$headerMap['fecha_actualizacion_estratificacion']]) : '',
      'detalle_diagnosticos_cronicos' => isset($headerMap['detalle_diagnosticos_cronicos'], $rawRow[$headerMap['detalle_diagnosticos_cronicos']])
        ? trim((string)$rawRow[$headerMap['detalle_diagnosticos_cronicos']]) : '',
    ];
  }

  return $out;
}

function iec_extract_uploaded_rows(array $file): array {
  $tmp = (string)($file['tmp_name'] ?? '');
  if ($tmp === '') {
    throw new RuntimeException('No se recibió correctamente el archivo.');
  }

  $ext = strtolower(pathinfo((string)($file['name'] ?? ''), PATHINFO_EXTENSION));
  if ($ext === 'csv' || $ext === 'txt') {
    return iec_map_rows(iec_read_csv_rows($tmp));
  }
  if ($ext === 'xlsx') {
    return iec_map_rows(iec_read_xlsx_rows($tmp, 1));
  }
  throw new RuntimeException('Formato no soportado. Sube un .csv o .xlsx');
}

if (!isset($_FILES['excel'])) {
  json_response(['ok' => false, 'message' => 'No llegó el archivo al servidor.'], 400);
}

$file = $_FILES['excel'];
if ((int)($file['error'] ?? UPLOAD_ERR_NO_FILE) !== UPLOAD_ERR_OK) {
  json_response(['ok' => false, 'message' => 'Error al subir el archivo. Código: ' . (int)$file['error']], 400);
}

$soloExistentes = true;
if (isset($_POST['solo_existentes'])) {
  $soloExistentes = filter_var($_POST['solo_existentes'], FILTER_VALIDATE_BOOLEAN, FILTER_NULL_ON_FAILURE);
  $soloExistentes = ($soloExistentes === null) ? true : $soloExistentes;
}

try {
  $rows = iec_extract_uploaded_rows($file);
} catch (Throwable $e) {
  json_response(['ok' => false, 'message' => $e->getMessage()], 400);
}

if (!$rows) {
  json_response(['ok' => false, 'message' => 'No encontré filas válidas para importar.'], 400);
}

try {
  $pdo->beginTransaction();

  $stExists = $pdo->prepare('SELECT 1 FROM usuarios WHERE rut = ? LIMIT 1');
  $stUpdate = $pdo->prepare("UPDATE usuarios
    SET estratificacion_riesgo = :estratificacion_riesgo,
        cantidad_condiciones_cronicas = :cantidad_condiciones_cronicas,
        fecha_actualizacion_estratificacion = :fecha_actualizacion_estratificacion,
        detalle_diagnosticos_cronicos = :detalle_diagnosticos_cronicos
    WHERE rut = :rut");

  $summary = [
    'recibidos' => count($rows),
    'procesados' => 0,
    'actualizados' => 0,
    'no_encontrados' => 0,
    'sin_rut' => 0,
    'omitidos' => 0,
    'ruts_no_encontrados' => [],
  ];

  foreach ($rows as $row) {
    if (!is_array($row)) {
      $summary['omitidos']++;
      continue;
    }

    $rut = normalize_rut((string)($row['rut'] ?? ''));
    if ($rut === '') {
      $summary['sin_rut']++;
      continue;
    }

    $summary['procesados']++;

    $stExists->execute([$rut]);
    $exists = (bool)$stExists->fetchColumn();

    if (!$exists && $soloExistentes) {
      $summary['no_encontrados']++;
      if (count($summary['ruts_no_encontrados']) < 50) {
        $summary['ruts_no_encontrados'][] = $rut;
      }
      continue;
    }

    if (!$exists) {
      $summary['omitidos']++;
      continue;
    }

    $detalleDiagnosticos = (($row['detalle_diagnosticos_cronicos'] ?? '') === '' ? null : trim((string)$row['detalle_diagnosticos_cronicos']));
    $cantidadCronicos = (($row['cantidad_condiciones_cronicas'] ?? '') === '' ? null : (int)$row['cantidad_condiciones_cronicas']);
    if ($cantidadCronicos === null && $detalleDiagnosticos !== null) {
      $cantidadCronicos = count(fp_parse_diagnosticos_cronicos($detalleDiagnosticos));
    }

    $estratificacionValor = strtoupper(trim((string)($row['estratificacion_riesgo'] ?? '')));
    if ($estratificacionValor === '') {
      $estratificacionValor = 'G0';
    }

    $payload = [
      ':rut' => $rut,
      ':estratificacion_riesgo' => $estratificacionValor,
      ':cantidad_condiciones_cronicas' => $cantidadCronicos,
      ':fecha_actualizacion_estratificacion' => fp_parse_excel_date_or_null($row['fecha_actualizacion_estratificacion'] ?? null),
      ':detalle_diagnosticos_cronicos' => $detalleDiagnosticos,
    ];

    $stUpdate->execute($payload);
    fp_sync_usuario_diagnosticos($pdo, $rut, $payload[':detalle_diagnosticos_cronicos']);
    $summary['actualizados']++;
  }

  $pdo->commit();
  fp_log_activity($pdo, $u, 'Personas', 'Importar estratificación', 'Se importó estratificación: ' . (int)$summary['actualizados'] . ' personas actualizadas.', 'importacion', 'estratificacion');
  json_response(['ok' => true, 'summary' => $summary]);
} catch (Throwable $e) {
  if ($pdo->inTransaction()) {
    $pdo->rollBack();
  }
  json_response(['ok' => false, 'message' => 'Error importando estratificación: ' . $e->getMessage()], 500);
}
