<?php
ini_set('memory_limit', '512M');
set_time_limit(0);
/**
 * notas_moji_a_entidades_con_backup.php (PHP 8.2 robusto)
 */

ini_set('display_errors', '1');
ini_set('display_startup_errors', '1');
ini_set('html_errors', '0');
ini_set('log_errors', '1');
ini_set('error_log', __DIR__ . '/notas_moji_fix_error.log');
error_reporting(E_ALL);
set_time_limit(0);
header('Content-Type: text/html; charset=UTF-8');

register_shutdown_function(function () {
    $e = error_get_last();
    if ($e && in_array($e['type'], [E_ERROR, E_PARSE, E_CORE_ERROR, E_COMPILE_ERROR], true)) {
        echo "<pre>FATAL: {$e['message']}\nArchivo: {$e['file']}\nLínea: {$e['line']}\n</pre>";
    }
});

echo "<pre>";

if (!extension_loaded('mysqli')) {
    echo "ERROR: extension mysqli NO está cargada.\n";
    echo "Revisá el selector de PHP / módulos del hosting.\n</pre>";
    exit;
}

require_once __DIR__ . '/concha.php'; // $host,$usuario,$contra,$basedatos
if (!isset($host, $usuario, $contra, $basedatos) || $host === '' || $usuario === '' || $basedatos === '') {
    echo "ERROR: concha.php debe definir \$host, \$usuario, \$contra, \$basedatos.\n</pre>";
    exit;
}

mysqli_report(MYSQLI_REPORT_OFF);

$con = @new mysqli((string)$host, (string)$usuario, (string)$contra, (string)$basedatos);
if ($con->connect_error) {
    echo "ERROR MySQL connect: " . $con->connect_error . "\n</pre>";
    exit;
}
if (!$con->set_charset('utf8mb4')) {
    echo "WARN: No pude setear utf8mb4: " . $con->error . "\n";
}

/* ---------------- CONFIG ---------------- */
$limit   = 500;
$TRUNCAR = false; // true = trunca; false = saltea y loguea

$BACKUP_NOTAS = __DIR__ . '/nota.back.sql';
$BACKUP_MENU  = __DIR__ . '/menu.back.sql';

function sql_quote(mysqli $con, $v) {
    if ($v === null) return "NULL";
    if (is_int($v) || is_float($v)) return (string)$v;
    return "'" . $con->real_escape_string((string)$v) . "'";
}

function pick_backup_path($desired) {
    if (@is_writable(dirname($desired))) return $desired;

    $tmp = rtrim((string)sys_get_temp_dir(), '/\\');
    $base = basename($desired);
    $alt = $tmp . DIRECTORY_SEPARATOR . $base;

    return $alt;
}

function backup_table_to_sql(mysqli $con, $table, $filePath) {
    $filePath = pick_backup_path($filePath);

    $fp = @fopen($filePath, 'wb');
    if (!$fp) return [false, "No pude crear backup: $filePath (permisos)"];

    @flock($fp, LOCK_EX);

    fwrite($fp,
        "-- Backup tabla `$table`\n" .
        "-- Fecha: " . date('Y-m-d H:i:s') . "\n" .
        "SET NAMES utf8mb4;\n" .
        "SET time_zone = '+00:00';\n\n"
    );

    $resCreate = $con->query("SHOW CREATE TABLE `$table`");
    if (!$resCreate) { fclose($fp); return [false, "Error SHOW CREATE TABLE `$table`: ".$con->error]; }
    $rowCreate = $resCreate->fetch_assoc();
    $resCreate->free();

    $createSql = $rowCreate['Create Table'] ?? '';
    if ($createSql === '') { fclose($fp); return [false, "No pude obtener CREATE TABLE de `$table`"]; }

    fwrite($fp, "DROP TABLE IF EXISTS `$table`;\n");
    fwrite($fp, $createSql . ";\n\n");

    $resCols = $con->query("SHOW COLUMNS FROM `$table`");
    if (!$resCols) { fclose($fp); return [false, "Error SHOW COLUMNS `$table`: ".$con->error]; }

    $cols = [];
    while ($c = $resCols->fetch_assoc()) $cols[] = $c['Field'];
    $resCols->free();

    if (!$cols) { fclose($fp); return [false, "No pude listar columnas de `$table`"]; }
    $colList = '`' . implode('`,`', $cols) . '`';

    $resData = $con->query("SELECT * FROM `$table`");
    if (!$resData) { fclose($fp); return [false, "Error SELECT `$table`: ".$con->error]; }

    $count = 0;
    $batchSize = 300;
    $values = [];

    while ($r = $resData->fetch_assoc()) {
        $count++;
        $rowVals = [];
        foreach ($cols as $col) $rowVals[] = sql_quote($con, $r[$col] ?? null);
        $values[] = "(" . implode(",", $rowVals) . ")";

        if (count($values) >= $batchSize) {
            fwrite($fp, "INSERT INTO `$table` ($colList) VALUES\n" . implode(",\n", $values) . ";\n");
            $values = [];
        }
    }
    $resData->free();

    if ($values) {
        fwrite($fp, "INSERT INTO `$table` ($colList) VALUES\n" . implode(",\n", $values) . ";\n");
    }

    fwrite($fp, "\n-- Fin backup. Filas: $count\n");
    @flock($fp, LOCK_UN);
    fclose($fp);

    return [true, "Backup OK: $filePath (tabla: $table, filas: $count)"];
}

function html_entity_decode_loop($s, $maxLoops = 3) {
    $s = (string)$s;
    for ($i=0; $i<$maxLoops; $i++) {
        $dec = html_entity_decode($s, ENT_QUOTES | ENT_HTML5, 'UTF-8');
        if ($dec === $s) break;
        $s = $dec;
    }
    return $s;
}

function needs_fix_1x($s) {
    $s = (string)$s;
    return (strpos($s,'Ã')!==false) || (strpos($s,'Â')!==false) || (strpos($s,'â€')!==false);
}
function needs_fix_2x($s) {
    $s = (string)$s;
    return (strpos($s,'ÃƒÂ')!==false) || (strpos($s,'Ã¢Â€Â')!==false) || (strpos($s,'Ã‚Â')!==false) || (strpos($s,'Ãƒ')!==false);
}

function moji_1x_a_utf8($txt) {
    if ($txt === null) return $txt;
    $txt = (string)$txt;

    $txt = str_replace(['Ã','Ã‰','Ã','Ã“','Ãš','Ã‘','Ãœ'], ['Á','É','Í','Ó','Ú','Ñ','Ü'], $txt);
    $txt = str_replace(['Ã¡','Ã©','Ã­','Ã³','Ãº','Ã±','Ã¼'], ['á','é','í','ó','ú','ñ','ü'], $txt);

    $txt = str_replace(['Â¿','Â¡'], ['¿','¡'], $txt);

    $txt = str_replace(['â€œ','â€','â€˜','â€™','â€“','â€”','â€¦'], ['“','”','‘','’','–','—','…'], $txt);

    $txt = str_replace(['Â°','Â«','Â»','Â©','Â®','Âº','Âª'], ['°','«','»','©','®','º','ª'], $txt);

    $txt = str_replace("Â ", "\xC2\xA0", $txt);
    return $txt;
}

function moji_2x_a_utf8($txt) {
    if ($txt === null) return $txt;
    $txt = (string)$txt;

    $txt = str_replace(['ÃƒÂ','ÃƒÂ‰','ÃƒÂ','ÃƒÂ“','ÃƒÂš','ÃƒÂ‘','ÃƒÂœ'], ['Á','É','Í','Ó','Ú','Ñ','Ü'], $txt);
    $txt = str_replace(['ÃƒÂ¡','ÃƒÂ©','ÃƒÂ­','ÃƒÂ³','ÃƒÂº','ÃƒÂ±','ÃƒÂ¼'], ['á','é','í','ó','ú','ñ','ü'], $txt);

    $txt = str_replace(['Ã‚Â¿','Ã‚Â¡'], ['¿','¡'], $txt);
    $txt = str_replace(['Ã¢Â€Âœ','Ã¢Â€Â','Ã¢Â€Â˜','Ã¢Â€Â™','Ã¢Â€Â“','Ã¢Â€Â—','Ã¢Â€Â”','Ã¢Â€Â¦'], ['“','”','‘','’','–','—','—','…'], $txt);
    $txt = str_replace(['Ã‚Â°','Ã‚Â«','Ã‚Â»','Ã‚Â©','Ã‚Â®','Ã‚Âº','Ã‚Âª'], ['°','«','»','©','®','º','ª'], $txt);
    $txt = str_replace("Ã‚Â ", "\xC2\xA0", $txt);

    // parcial sin Â
    $txt = str_replace(['Ãƒ','Ãƒ‰','Ãƒ','Ãƒ“','Ãƒš','Ãƒ‘','Ãƒœ'], ['Á','É','Í','Ó','Ú','Ñ','Ü'], $txt);
    $txt = str_replace(['Ãƒ¡','Ãƒ©','Ãƒ­','Ãƒ³','Ãƒº','Ãƒ±','Ãƒ¼'], ['á','é','í','ó','ú','ñ','ü'], $txt);
    $txt = str_replace(['Ã¢€œ','Ã¢€','Ã¢€˜','Ã¢€™','Ã¢€“','Ã¢€”','Ã¢€¦'], ['“','”','‘','’','–','—','…'], $txt);

    return $txt;
}

function protect_allowed_tags($s, &$map) {
    $s = (string)$s;
    $map = [];
    $i = 0;

    $s = preg_replace_callback('~<\s*br\s*/?\s*>~i', function($m) use (&$map,&$i){
        $k="__HTMLTAG{$i}__"; $map[$k]=$m[0]; $i++; return $k;
    }, $s);

    $s = preg_replace_callback('~<\s*/\s*p\s*>~i', function($m) use (&$map,&$i){
        $k="__HTMLTAG{$i}__"; $map[$k]=$m[0]; $i++; return $k;
    }, $s);

    $s = preg_replace_callback('~<\s*p\s*>~i', function($m) use (&$map,&$i){
        $k="__HTMLTAG{$i}__"; $map[$k]=$m[0]; $i++; return $k;
    }, $s);

    return $s;
}
function restore_allowed_tags($s, $map) {
    if (!$map) return (string)$s;
    return str_replace(array_keys($map), array_values($map), (string)$s);
}

function a_entidades($s) {
    if ($s === null) return $s;
    $s = (string)$s;

    $s = html_entity_decode_loop($s, 3);

    $map = [];
    $s = protect_allowed_tags($s, $map);

    $s = str_replace(['&','<','>','"',"'" ], ['&amp;','&lt;','&gt;','&quot;','&#039;'], $s);

    $s = restore_allowed_tags($s, $map);

    $buscar = [
        'á','é','í','ó','ú','Á','É','Í','Ó','Ú',
        'ñ','Ñ','ü','Ü',
        '¿','¡',
        '“','”','‘','’',
        '–','—','…',
        '°','«','»','©','®','º','ª',
        "\xC2\xA0"
    ];
    $reempl = [
        '&aacute;','&eacute;','&iacute;','&oacute;','&uacute;',
        '&Aacute;','&Eacute;','&Iacute;','&Oacute;','&Uacute;',
        '&ntilde;','&Ntilde;','&uuml;','&Uuml;',
        '&iquest;','&iexcl;',
        '&ldquo;','&rdquo;','&lsquo;','&rsquo;',
        '&ndash;','&mdash;','&hellip;',
        '&deg;','&laquo;','&raquo;','&copy;','&reg;','&ordm;','&ordf;',
        '&nbsp;'
    ];
    return str_replace($buscar, $reempl, $s);
}

function nonbmp_a_numeric_entities($s) {
    if ($s === null) return $s;
    $s = (string)$s;

    return preg_replace_callback('/[\x{10000}-\x{10FFFF}]/u', function($m){
        $ch = $m[0];
        $ucs4 = null;

        if (function_exists('mb_convert_encoding')) {
            $ucs4 = mb_convert_encoding($ch, 'UCS-4BE', 'UTF-8');
        } elseif (function_exists('iconv')) {
            $ucs4 = @iconv('UTF-8', 'UCS-4BE', $ch);
        }
        if ($ucs4 === false || $ucs4 === null) return '&#xFFFD;';

        $u = unpack('N', $ucs4);
        $cp = $u[1];
        return '&#x' . strtoupper(dechex($cp)) . ';';
    }, $s);
}

function get_varchar_limit(mysqli $con, $table, $col) {
    $table = $con->real_escape_string((string)$table);
    $col   = $con->real_escape_string((string)$col);

    $sql = "SELECT COLUMN_TYPE, DATA_TYPE
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = '$table'
              AND COLUMN_NAME = '$col'
            LIMIT 1";
    $res = $con->query($sql);
    if (!$res) return null;
    $row = $res->fetch_assoc();
    $res->free();
    if (!$row) return null;

    $dataType = strtolower((string)($row['DATA_TYPE'] ?? ''));
    $colType  = strtolower((string)($row['COLUMN_TYPE'] ?? ''));

    if ($dataType === 'varchar' || $dataType === 'char') {
        if (preg_match('/\((\d+)\)/', $colType, $m)) return (int)$m[1];
    }
    return null;
}

function truncate_to($s, $max) {
    if ($s === null || $max === null) return $s;
    $s = (string)$s;
    if (strlen($s) <= $max) return $s;
    return substr($s, 0, $max);
}

function normalizar_total_a_entidades($s) {
    if ($s === null) return $s;
    $s = (string)$s;

    $s = html_entity_decode_loop($s, 3);

    if (needs_fix_2x($s)) $s = (string)moji_2x_a_utf8($s);
    if (needs_fix_1x($s)) $s = (string)moji_1x_a_utf8($s);

    $s = (string)a_entidades($s);
    $s = (string)nonbmp_a_numeric_entities($s);

    return $s;
}

/* ---------------- EJECUCIÓN ---------------- */

// BACKUP NOTAS
list($ok1, $msg1) = backup_table_to_sql($con, 'notas', $BACKUP_NOTAS);
echo $msg1 . "\n";
if (!$ok1) { echo "CORTO: no se aplican cambios.\n</pre>"; exit; }

echo "Continuando con la normalización de NOTAS...\n\n";

$limTitulo = get_varchar_limit($con, 'notas', 'titulo');
$limBajada = get_varchar_limit($con, 'notas', 'bajada');

echo "Límite titulo: " . ($limTitulo === null ? "SIN LIMITE" : $limTitulo) . "\n";
echo "Límite bajada: " . ($limBajada === null ? "SIN LIMITE" : $limBajada) . "\n";
echo "Modo: " . ($TRUNCAR ? "TRUNCAR" : "SALTEAR si excede") . "\n\n";

$upd = $con->prepare("UPDATE notas SET titulo = ?, bajada = ? WHERE ID = ? LIMIT 1");
if (!$upd) { echo "ERROR prepare UPDATE notas: ".$con->error."\n</pre>"; exit; }

$total=0; $updated=0; $skipped=0; $errUpd=0;
$offset=0;

while (true) {
    $q = "SELECT ID, titulo, bajada FROM notas ORDER BY ID ASC LIMIT " . (int)$limit . " OFFSET " . (int)$offset;
    $res = $con->query($q);
    if (!$res) { echo "ERROR SELECT notas: ".$con->error."\n</pre>"; exit; }
    if ($res->num_rows === 0) { $res->free(); break; }

    while ($row = $res->fetch_assoc()) {
        $total++;
        $id = (int)$row['ID'];

        $titulo = $row['titulo'];
        $bajada = $row['bajada'];

        $newTitulo = normalizar_total_a_entidades($titulo);
        $newBajada = normalizar_total_a_entidades($bajada);

        if ($newTitulo === $titulo && $newBajada === $bajada) continue;

        $excedeTitulo = ($limTitulo !== null && strlen((string)$newTitulo) > $limTitulo);
        $excedeBajada = ($limBajada !== null && strlen((string)$newBajada) > $limBajada);

        if ($excedeTitulo || $excedeBajada) {
            if ($TRUNCAR) {
                if ($excedeTitulo) $newTitulo = truncate_to($newTitulo, $limTitulo);
                if ($excedeBajada) $newBajada = truncate_to($newBajada, $limBajada);
            } else {
                $skipped++;
                echo "SKIP NOTAS ID=$id (excede: " . ($excedeTitulo ? "titulo " : "") . ($excedeBajada ? "bajada " : "") . ")\n";
                continue;
            }
        }

        $upd->bind_param("ssi", $newTitulo, $newBajada, $id);
        if (!$upd->execute()) {
            $errUpd++;
            echo "ERROR update NOTAS ID=$id: ".$upd->error."\n";
            continue;
        }
        $updated++;
        echo "OK NOTAS ID=$id\n";
    }

    $res->free();
    $offset += $limit;
}

$upd->close();

echo "\n---- RESUMEN NOTAS ----\n";
echo "Leídas      : $total\n";
echo "Actualizadas: $updated\n";
echo "Salteadas   : $skipped\n";
echo "Errores upd : $errUpd\n";
echo "Backup      : " . pick_backup_path($BACKUP_NOTAS) . "\n\n";

// BACKUP MENU
list($ok2, $msg2) = backup_table_to_sql($con, 'menu', $BACKUP_MENU);
echo $msg2 . "\n";
if (!$ok2) { echo "CORTO: no se aplican cambios en MENU.\n</pre>"; exit; }

echo "Continuando con la normalización de MENU...\n\n";

$limNombre = get_varchar_limit($con, 'menu', 'nombre');
echo "Límite nombre: " . ($limNombre === null ? "SIN LIMITE" : $limNombre) . "\n";
echo "Modo: " . ($TRUNCAR ? "TRUNCAR" : "SALTEAR si excede") . "\n\n";

$updm = $con->prepare("UPDATE menu SET nombre = ? WHERE ID = ? LIMIT 1");
if (!$updm) { echo "ERROR prepare UPDATE menu: ".$con->error."\n</pre>"; exit; }

$totalM=0; $updatedM=0; $skippedM=0; $errUpdM=0;
$offset=0;

while (true) {
    $q = "SELECT ID, nombre FROM menu ORDER BY ID ASC LIMIT " . (int)$limit . " OFFSET " . (int)$offset;
    $resm = $con->query($q);
    if (!$resm) { echo "ERROR SELECT menu: ".$con->error."\n</pre>"; exit; }
    if ($resm->num_rows === 0) { $resm->free(); break; }

    while ($row = $resm->fetch_assoc()) {
        $totalM++;
        $id = (int)$row['ID'];

        $nombre = $row['nombre'];
        $newNombre = normalizar_total_a_entidades($nombre);

        if ($newNombre === $nombre) continue;

        $excede = ($limNombre !== null && strlen((string)$newNombre) > $limNombre);
        if ($excede) {
            if ($TRUNCAR) {
                $newNombre = truncate_to($newNombre, $limNombre);
            } else {
                $skippedM++;
                echo "SKIP MENU ID=$id (excede: nombre)\n";
                continue;
            }
        }

        $updm->bind_param("si", $newNombre, $id);
        if (!$updm->execute()) {
            $errUpdM++;
            echo "ERROR update MENU ID=$id: ".$updm->error."\n";
            continue;
        }
        $updatedM++;
        echo "OK MENU ID=$id\n";
    }

    $resm->free();
    $offset += $limit;
}

$updm->close();

echo "\n---- RESUMEN MENU ----\n";
echo "Leídas      : $totalM\n";
echo "Actualizadas: $updatedM\n";
echo "Salteadas   : $skippedM\n";
echo "Errores upd : $errUpdM\n";
echo "Backup      : " . pick_backup_path($BACKUP_MENU) . "\n";

echo "\nListo.\n</pre>";