#!/bin/bash
# ============================================================================
# LIMPIOO — Migración v1.0 → v2.0
# ============================================================================
# Estrategia:
#   1) Descubre `limpioo` y `limpioo_userdb_%` existentes.
#   2) Hace DOS backups por BD:
#        - full_<DB>.sql   (estructura + datos)  → rollback manual
#        - data_<DB>.sql   (solo datos)          → reimport limpio
#   3) DROP DATABASE de cada BD descubierta.
#   4) Recrea master (limpioo) desde admin/v2.0/database.sql.
#   5) Recrea cada tenant desde application/v2.0/database.sql.
#   6) Reimporta datos desde data_<DB>.sql.
#   7) Resincroniza AUTO_INCREMENT por tabla.
#   8) Pre-popula limpioo.user_uuid_databases.
#   9) Reaplica GRANTs MySQL a los usuarios dedicados de cada tenant
#      (DROP DATABASE elimina los privilegios; las contraseñas siguen vigentes
#      en mysql.user, pero los GRANTs sobre la BD desaparecen).
#
# Backups: database/backups/v1_to_v2_<TIMESTAMP>/  (NO se borran automáticamente).
#
# Uso:
#   sudo bash database/migrations/upgrade_v1_to_v2.sh [--dry-run] [--yes] [--only-db=NAME] [--skip-prewarm] [--skip-grants] [--backfill-thing=SLUG]
#
# --backfill-thing=SLUG  Tras el reimport, asigna ese slug (ej: "Puente-1") a
#                        todas las filas legacy de qr_usage_log que quedaron
#                        con device_thing=NULL (columna nueva en v2.0).
#                        Solo seguro cuando el tenant tiene un único dispositivo.
#
# Rollback manual de una BD:
#   mysql -u root DBNAME < database/backups/v1_to_v2_<TS>/full_DBNAME.sql
# ============================================================================

set -euo pipefail

# ── Colores ──────────────────────────────────────────────────────────────────
RED='\033[0;31m'; GREEN='\033[0;32m'; YELLOW='\033[1;33m'
BLUE='\033[0;34m'; CYAN='\033[0;36m'; NC='\033[0m'

ok()   { echo -e "   ${GREEN}✅ $*${NC}"; }
warn() { echo -e "   ${YELLOW}⚠️  $*${NC}"; }
fail() { echo -e "   ${RED}❌ $*${NC}"; }
info() { echo -e "   ${CYAN}ℹ️  $*${NC}"; }
step() { echo -e "\n${BLUE}[$1] $2${NC}"; }

# ── Args ─────────────────────────────────────────────────────────────────────
DRY_RUN=0
ASSUME_YES=0
ONLY_DB=""
SKIP_PREWARM=0
SKIP_GRANTS=0
BACKFILL_THING=""

for arg in "$@"; do
    case "$arg" in
        --dry-run)             DRY_RUN=1 ;;
        --yes|-y)              ASSUME_YES=1 ;;
        --only-db=*)           ONLY_DB="${arg#*=}" ;;
        --skip-prewarm)        SKIP_PREWARM=1 ;;
        --skip-grants)         SKIP_GRANTS=1 ;;
        --backfill-thing=*)    BACKFILL_THING="${arg#*=}" ;;
        -h|--help)
            sed -n '2,35p' "$0"; exit 0 ;;
        *)
            fail "Argumento desconocido: $arg"; exit 2 ;;
    esac
done

# ── Rutas ────────────────────────────────────────────────────────────────────
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
DB_ROOT="$(cd "$SCRIPT_DIR/.." && pwd)"          # database/
ADMIN_SQL="$DB_ROOT/admin/v2.0/database.sql"
TENANT_SQL="$DB_ROOT/application/v2.0/database.sql"
TIMESTAMP="$(date +%Y%m%d_%H%M%S)"
BACKUP_DIR="$DB_ROOT/backups/v1_to_v2_${TIMESTAMP}"
LOG_FILE=""    # se define tras crear BACKUP_DIR

# ── Header ───────────────────────────────────────────────────────────────────
echo ""
echo -e "${CYAN}============================================================${NC}"
echo -e "${CYAN}   LIMPIOO — Migración v1.0 → v2.0${NC}"
echo -e "${CYAN}   $(date)${NC}"
if [[ $DRY_RUN -eq 1 ]]; then
    echo -e "${YELLOW}   MODO: DRY-RUN (no se ejecutará nada destructivo)${NC}"
fi
echo -e "${CYAN}============================================================${NC}"

# ── Verificaciones previas ───────────────────────────────────────────────────
step "1/10" "Verificaciones previas"

if [[ $EUID -ne 0 ]]; then
    fail "Ejecuta como root: sudo bash $0"
    exit 1
fi
ok "Ejecución como root"

[[ -f "$ADMIN_SQL"  ]] || { fail "No existe $ADMIN_SQL";  exit 1; }
[[ -f "$TENANT_SQL" ]] || { fail "No existe $TENANT_SQL"; exit 1; }
ok "Schemas v2.0 encontrados"

command -v mysql      &>/dev/null || { fail "mysql no instalado";     exit 1; }
command -v mysqldump  &>/dev/null || { fail "mysqldump no instalado"; exit 1; }
ok "Herramientas mysql/mysqldump disponibles"

if [[ $SKIP_GRANTS -eq 0 ]]; then
    command -v php &>/dev/null || { fail "php no instalado (requerido para descifrar passwords; usa --skip-grants para omitir)"; exit 1; }
    ok "php disponible (para descifrado AES-256-GCM)"
fi

# ── Credenciales root ────────────────────────────────────────────────────────
step "2/10" "Credenciales de MySQL root"

read -p "   Usuario root MySQL [root]: " MYSQL_ROOT_USER
MYSQL_ROOT_USER="${MYSQL_ROOT_USER:-root}"
read -sp "   Contraseña root MySQL (vacío si usa socket): " MYSQL_ROOT_PASS
echo ""

if [[ -z "$MYSQL_ROOT_PASS" ]]; then
    MYSQL_AUTH=(-u "$MYSQL_ROOT_USER")
else
    MYSQL_AUTH=(-u "$MYSQL_ROOT_USER" "-p$MYSQL_ROOT_PASS")
fi

if ! mysql "${MYSQL_AUTH[@]}" -e "SELECT 1" &>/dev/null; then
    fail "No se pudo conectar a MySQL"
    exit 1
fi
ok "Conexión MySQL verificada"

# ── Descubrir BDs ────────────────────────────────────────────────────────────
step "3/10" "Descubrir bases de datos a migrar"

mapfile -t ALL_DBS < <(
    mysql "${MYSQL_AUTH[@]}" -N -B -e \
        "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
         WHERE SCHEMA_NAME = 'limpioo'
            OR SCHEMA_NAME LIKE 'limpioo\\_userdb\\_%' ESCAPE '\\\\'
         ORDER BY SCHEMA_NAME = 'limpioo' DESC, SCHEMA_NAME"
)

if [[ -n "$ONLY_DB" ]]; then
    DBS=()
    for d in "${ALL_DBS[@]}"; do
        [[ "$d" == "$ONLY_DB" ]] && DBS+=("$d")
    done
    if [[ ${#DBS[@]} -eq 0 ]]; then
        fail "--only-db=$ONLY_DB no coincide con ninguna BD existente"
        exit 1
    fi
else
    DBS=("${ALL_DBS[@]}")
fi

if [[ ${#DBS[@]} -eq 0 ]]; then
    fail "No se encontró ninguna BD limpioo* — nada que migrar"
    exit 1
fi

echo "   Bases de datos detectadas:"
for d in "${DBS[@]}"; do echo "     • $d"; done

# ── Espacio en disco ─────────────────────────────────────────────────────────
DISK_FREE_KB=$(df -P "$DB_ROOT" | awk 'NR==2 {print $4}')
DISK_FREE_MB=$((DISK_FREE_KB / 1024))
if [[ $DISK_FREE_MB -lt 500 ]]; then
    warn "Solo ${DISK_FREE_MB} MB libres en $(df -P "$DB_ROOT" | awk 'NR==2 {print $6}'). Recomendado: >500 MB."
else
    ok "Espacio en disco: ${DISK_FREE_MB} MB libres"
fi

# ── Confirmación ─────────────────────────────────────────────────────────────
if [[ $DRY_RUN -eq 0 && $ASSUME_YES -eq 0 ]]; then
    echo ""
    warn "Se van a HACER BACKUP, BORRAR Y RECREAR las BDs anteriores."
    warn "Los backups quedan en: $BACKUP_DIR"
    read -p "   ¿Continuar? Escribe 'SI' para confirmar: " CONFIRM
    if [[ "$CONFIRM" != "SI" ]]; then
        fail "Cancelado por el usuario"
        exit 1
    fi
fi

# ── Crear directorio de backups ──────────────────────────────────────────────
step "4/10" "Crear directorio de backups"

if [[ $DRY_RUN -eq 0 ]]; then
    mkdir -p "$BACKUP_DIR"
    LOG_FILE="$BACKUP_DIR/upgrade.log"
    : > "$LOG_FILE"
    # Redirigir stdout/stderr también al log (manteniendo terminal)
    exec > >(tee -a "$LOG_FILE") 2>&1
    ok "Directorio: $BACKUP_DIR"
else
    info "[dry-run] mkdir -p $BACKUP_DIR"
fi

# ── Helper: ejecutar o simular ───────────────────────────────────────────────
run() {
    if [[ $DRY_RUN -eq 1 ]]; then
        echo -e "   ${YELLOW}[dry-run]${NC} $*"
    else
        eval "$@"
    fi
}

# ── Conteos previos ──────────────────────────────────────────────────────────
declare -A PRE_COUNT
count_rows() {
    local db="$1"
    local total=0
    local tables
    tables=$(mysql "${MYSQL_AUTH[@]}" -N -B -e \
        "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
         WHERE TABLE_SCHEMA='$db' AND TABLE_TYPE='BASE TABLE'")
    for t in $tables; do
        local c
        c=$(mysql "${MYSQL_AUTH[@]}" -N -B -e "SELECT COUNT(*) FROM \`$db\`.\`$t\`" 2>/dev/null || echo 0)
        total=$((total + c))
        echo "      $db.$t = $c"
    done
    echo "$total"
}

# ── Fase 5: Backups ──────────────────────────────────────────────────────────
step "5/10" "Backup completo + datos por BD"

MANIFEST="$BACKUP_DIR/manifest.txt"
if [[ $DRY_RUN -eq 0 ]]; then
    {
        echo "Migración v1.0 → v2.0"
        echo "Timestamp: $TIMESTAMP"
        echo "Servidor: $(hostname)"
        echo "Bases de datos:"
        for d in "${DBS[@]}"; do echo "  - $d"; done
        echo ""
        echo "=== Conteo de filas (PRE) ==="
    } > "$MANIFEST"
fi

for db in "${DBS[@]}"; do
    echo "   → $db"

    # Conteo previo
    if [[ $DRY_RUN -eq 0 ]]; then
        echo "" >> "$MANIFEST"
        echo "[$db]" >> "$MANIFEST"
        total=0
        while IFS= read -r t; do
            [[ -z "$t" ]] && continue
            c=$(mysql "${MYSQL_AUTH[@]}" -N -B -e "SELECT COUNT(*) FROM \`$db\`.\`$t\`" 2>/dev/null || echo 0)
            total=$((total + c))
            echo "  $t = $c" >> "$MANIFEST"
        done < <(mysql "${MYSQL_AUTH[@]}" -N -B -e \
            "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
             WHERE TABLE_SCHEMA='$db' AND TABLE_TYPE='BASE TABLE'")
        PRE_COUNT[$db]=$total
        echo "  TOTAL = $total" >> "$MANIFEST"
        info "Filas totales pre-migración: $total"
    fi

    FULL_FILE="$BACKUP_DIR/full_${db}.sql"
    DATA_FILE="$BACKUP_DIR/data_${db}.sql"

    run "mysqldump ${MYSQL_AUTH[*]} \
            --single-transaction --routines --triggers --events \
            --hex-blob --default-character-set=utf8mb4 \
            '$db' > '$FULL_FILE'"

    run "mysqldump ${MYSQL_AUTH[*]} \
            --no-create-info --skip-triggers --complete-insert \
            --single-transaction --hex-blob --default-character-set=utf8mb4 \
            '$db' > '$DATA_FILE'"

    if [[ $DRY_RUN -eq 0 ]]; then
        if [[ ! -s "$FULL_FILE" || ! -s "$DATA_FILE" ]]; then
            fail "Dump vacío para $db — abortando"
            exit 1
        fi
        ok "Backups: $(du -h "$FULL_FILE" | cut -f1) full + $(du -h "$DATA_FILE" | cut -f1) data"
    fi
done

# ── Fase 6: DROP DATABASE ────────────────────────────────────────────────────
step "6/10" "DROP DATABASE de las BDs descubiertas"

for db in "${DBS[@]}"; do
    echo "   → DROP $db"
    run "mysql ${MYSQL_AUTH[*]} -e \"DROP DATABASE IF EXISTS \\\`$db\\\`\""
done
ok "Bases de datos eliminadas"

# ── Fase 7: Recrear estructura desde v2.0 ────────────────────────────────────
step "7/10" "Recrear estructura desde schemas v2.0"

# 7a) Master
echo "   → limpioo (admin/v2.0/database.sql)"
run "mysql ${MYSQL_AUTH[*]} < '$ADMIN_SQL'"

# 7b) Cada tenant
for db in "${DBS[@]}"; do
    [[ "$db" == "limpioo" ]] && continue
    echo "   → $db (application/v2.0/database.sql)"
    run "mysql ${MYSQL_AUTH[*]} -e \"CREATE DATABASE \\\`$db\\\` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci\""
    run "mysql ${MYSQL_AUTH[*]} '$db' < '$TENANT_SQL'"
done
ok "Estructura recreada"

# ── Fase 8: Reimport de datos + resync AUTO_INCREMENT ────────────────────────
step "8/10" "Reimportar datos y resincronizar AUTO_INCREMENT"

POST_MISMATCH=0

for db in "${DBS[@]}"; do
    DATA_FILE="$BACKUP_DIR/data_${db}.sql"
    echo "   → $db"

    # Reimport con FK desactivadas
    if [[ $DRY_RUN -eq 0 ]]; then
        {
            echo "SET FOREIGN_KEY_CHECKS=0;"
            echo "SET UNIQUE_CHECKS=0;"
            cat "$DATA_FILE"
            echo "SET FOREIGN_KEY_CHECKS=1;"
            echo "SET UNIQUE_CHECKS=1;"
        } | mysql "${MYSQL_AUTH[@]}" "$db" 2> >(tee -a "$BACKUP_DIR/reimport_${db}.errors.log" >&2) || {
            fail "Errores durante reimport de $db (ver $BACKUP_DIR/reimport_${db}.errors.log)"
        }
    else
        info "[dry-run] mysql $db < $DATA_FILE  (con FK_CHECKS=0)"
    fi

    # Backfill de qr_usage_log.device_thing en datos legacy v1.
    # En v1 la tabla qr_usage_log no tenía esta columna, así que tras el
    # reimport todas las filas legacy quedan con device_thing=NULL. Si se
    # pasa --backfill-thing=Puente-1 (o similar), se asigna ese slug a las
    # filas legacy del tenant para que el dashboard pueda consolidarlas con
    # los pagos físicos (log.THING) bajo la misma etiqueta de dispositivo.
    if [[ "$db" != "limpioo" && -n "$BACKFILL_THING" ]]; then
        if [[ $DRY_RUN -eq 0 ]]; then
            # Verificar que la tabla y la columna existen (puede no aplicar a tenants vacíos)
            has_col=$(mysql "${MYSQL_AUTH[@]}" -N -B -e \
                "SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
                 WHERE TABLE_SCHEMA='$db' AND TABLE_NAME='qr_usage_log'
                   AND COLUMN_NAME='device_thing'" 2>/dev/null || echo "")
            if [[ -n "$has_col" ]]; then
                bf_rows=$(mysql "${MYSQL_AUTH[@]}" -N -B "$db" -e "
                    UPDATE qr_usage_log SET device_thing = '$BACKFILL_THING' WHERE device_thing IS NULL;
                    SELECT ROW_COUNT();" 2>/dev/null | tail -1)
                bf_rows="${bf_rows:-0}"
                ok "Backfill device_thing='$BACKFILL_THING': $bf_rows filas actualizadas"
            else
                info "Backfill omitido (qr_usage_log.device_thing no existe en $db)"
            fi
        else
            info "[dry-run] UPDATE qr_usage_log SET device_thing='$BACKFILL_THING' WHERE device_thing IS NULL"
        fi
    fi

    # Resync AUTO_INCREMENT
    if [[ $DRY_RUN -eq 0 ]]; then
        # Para cada tabla con auto_increment, ALTER al MAX(pk)+1
        while IFS=$'\t' read -r tbl col; do
            [[ -z "$tbl" ]] && continue
            maxv=$(mysql "${MYSQL_AUTH[@]}" -N -B -e \
                "SELECT IFNULL(MAX(\`$col\`),0)+1 FROM \`$db\`.\`$tbl\`" 2>/dev/null || echo 1)
            mysql "${MYSQL_AUTH[@]}" -e \
                "ALTER TABLE \`$db\`.\`$tbl\` AUTO_INCREMENT=$maxv" 2>/dev/null || true
        done < <(mysql "${MYSQL_AUTH[@]}" -N -B -e \
            "SELECT t.TABLE_NAME, c.COLUMN_NAME
             FROM INFORMATION_SCHEMA.TABLES t
             JOIN INFORMATION_SCHEMA.COLUMNS c
               ON c.TABLE_SCHEMA=t.TABLE_SCHEMA AND c.TABLE_NAME=t.TABLE_NAME
             WHERE t.TABLE_SCHEMA='$db'
               AND t.TABLE_TYPE='BASE TABLE'
               AND c.EXTRA LIKE '%auto_increment%'")
    else
        info "[dry-run] resincronizar AUTO_INCREMENT en cada tabla de $db"
    fi

    # Conteo posterior
    if [[ $DRY_RUN -eq 0 ]]; then
        echo "" >> "$MANIFEST"
        echo "[POST $db]" >> "$MANIFEST"
        post_total=0
        while IFS= read -r t; do
            [[ -z "$t" ]] && continue
            c=$(mysql "${MYSQL_AUTH[@]}" -N -B -e "SELECT COUNT(*) FROM \`$db\`.\`$t\`" 2>/dev/null || echo 0)
            post_total=$((post_total + c))
            echo "  $t = $c" >> "$MANIFEST"
        done < <(mysql "${MYSQL_AUTH[@]}" -N -B -e \
            "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
             WHERE TABLE_SCHEMA='$db' AND TABLE_TYPE='BASE TABLE'")
        echo "  TOTAL = $post_total" >> "$MANIFEST"

        pre_total="${PRE_COUNT[$db]:-0}"
        if [[ "$post_total" -lt "$pre_total" ]]; then
            warn "$db: pre=$pre_total → post=$post_total (¡diferencia!)"
            POST_MISMATCH=1
        else
            ok "$db: pre=$pre_total → post=$post_total"
        fi
    fi
done

# ── Fase 9: Prewarm de limpioo.user_uuid_databases ───────────────────────────
# Pre-popula la caché UUID→tenant con los users.uuid de cada tenant.
# Sin esto, las primeras consultas caen en el fallback de auto-descubrimiento
# (más lento) hasta que la caché se llena de forma natural.
step "9/10" "Pre-poblar caché UUID → tenant (limpioo.user_uuid_databases)"

if [[ $SKIP_PREWARM -eq 1 ]]; then
    info "Omitido por --skip-prewarm"
else
    # Solo tiene sentido si limpioo.user_uuid_databases existe (debe, por v2.0)
    if [[ $DRY_RUN -eq 0 ]]; then
        if ! mysql "${MYSQL_AUTH[@]}" -N -B -e \
            "SELECT 1 FROM INFORMATION_SCHEMA.TABLES
             WHERE TABLE_SCHEMA='limpioo' AND TABLE_NAME='user_uuid_databases'" \
            | grep -q 1; then
            warn "limpioo.user_uuid_databases no existe — se omite prewarm"
        else
            TOTAL_PREWARMED=0
            for db in "${DBS[@]}"; do
                # Solo tenants
                [[ "$db" == "limpioo" ]] && continue
                # ¿Existe la tabla users en este tenant?
                has_users=$(mysql "${MYSQL_AUTH[@]}" -N -B -e \
                    "SELECT 1 FROM INFORMATION_SCHEMA.TABLES
                     WHERE TABLE_SCHEMA='$db' AND TABLE_NAME='users'" 2>/dev/null || echo "")
                [[ -z "$has_users" ]] && { info "$db: sin tabla users, omitido"; continue; }

                inserted=$(mysql "${MYSQL_AUTH[@]}" -N -B -e "
                    INSERT INTO \`limpioo\`.\`user_uuid_databases\` (uuid, db_name)
                    SELECT u.uuid, '$db'
                    FROM \`$db\`.\`users\` u
                    WHERE u.uuid IS NOT NULL AND u.uuid <> ''
                    ON DUPLICATE KEY UPDATE last_used = CURRENT_TIMESTAMP;
                    SELECT ROW_COUNT();" 2>/dev/null | tail -1)
                inserted="${inserted:-0}"
                ok "$db: $inserted entradas insertadas/actualizadas en caché"
                TOTAL_PREWARMED=$((TOTAL_PREWARMED + inserted))
            done
            ok "Caché UUID→tenant pre-poblada: $TOTAL_PREWARMED entradas totales"
        fi
    else
        for db in "${DBS[@]}"; do
            [[ "$db" == "limpioo" ]] && continue
            info "[dry-run] INSERT INTO limpioo.user_uuid_databases SELECT uuid, '$db' FROM $db.users …"
        done
    fi
fi

# ── Fase 10: Reaplicar GRANTs MySQL ──────────────────────────────────────────
# Al hacer DROP DATABASE se eliminan los privilegios sobre esa BD (mysql.db /
# mysql.tables_priv). Los usuarios siguen existiendo en mysql.user con su
# contraseña, pero ya no tienen acceso. Este paso descifra la contraseña
# almacenada en limpioo.users_databases y reasigna los GRANTs.
step "10/10" "Reaplicar GRANTs MySQL desde limpioo.users_databases"

if [[ $SKIP_GRANTS -eq 1 ]]; then
    info "Omitido por --skip-grants"
elif [[ $DRY_RUN -eq 1 ]]; then
    info "[dry-run] leer limpioo.users_databases, descifrar passwords y aplicar:"
    info "[dry-run]   ALTER USER '<dbuser>'@'localhost' IDENTIFIED BY '<pass>';"
    info "[dry-run]   GRANT ALL PRIVILEGES ON \`<db_name>\`.* TO '<dbuser>'@'localhost';"
else
    # Localizar la clave de cifrado en el código (hardcodeada)
    SIGNIN_PHP="$(cd "$DB_ROOT/.." && pwd)/ajax/auth/signin/post.php"
    if [[ ! -f "$SIGNIN_PHP" ]]; then
        warn "No se encontró $SIGNIN_PHP — omitiendo regrant"
    else
        ENC_KEY_B64=$(grep -oP "encKeyBase64\s*=\s*'\K[^']+" "$SIGNIN_PHP" | head -1)
        if [[ -z "$ENC_KEY_B64" ]]; then
            warn "No se pudo extraer encKeyBase64 de signin/post.php — omitiendo regrant"
        else
            REGRANT_COUNT=0
            REGRANT_FAILS=0
            while IFS=$'\t' read -r dbname dbuser pw_b64 iv_b64 tag_b64; do
                [[ -z "$dbname" ]] && continue
                # Filtrar tenants que existen en esta migración
                in_dbs=0
                for d in "${DBS[@]}"; do [[ "$d" == "$dbname" ]] && in_dbs=1; done
                [[ $in_dbs -eq 0 ]] && continue

                # Descifrar con PHP (AES-256-GCM)
                # NOTA: db_password_enc ya viene en base64 (TEXT), por eso NO se
                # le aplica TO_BASE64 en la query y aquí solo se decodifica una vez.
                # iv y tag son varbinary, se transportan vía TO_BASE64.
                dbpass=$(ENC_KEY="$ENC_KEY_B64" PW="$pw_b64" IV="$iv_b64" TAG="$tag_b64" \
                    php -r '
                        $key = base64_decode(getenv("ENC_KEY"), true);
                        $pw  = base64_decode(getenv("PW"));
                        $iv  = base64_decode(getenv("IV"));
                        $tag = base64_decode(getenv("TAG"));
                        $plain = openssl_decrypt($pw, "aes-256-gcm", $key, OPENSSL_RAW_DATA, $iv, $tag);
                        if ($plain === false) { fwrite(STDERR, "decrypt-fail\n"); exit(1); }
                        echo $plain;
                    ' 2>/dev/null) || dbpass=""

                if [[ -z "$dbpass" ]]; then
                    warn "$dbuser@$dbname: no se pudo descifrar password — omitido"
                    REGRANT_FAILS=$((REGRANT_FAILS+1))
                    continue
                fi

                # Escapar comillas simples para SQL literal
                dbpass_sql="${dbpass//\'/\'\'}"

                if mysql "${MYSQL_AUTH[@]}" <<SQL 2>/dev/null
CREATE USER IF NOT EXISTS '${dbuser}'@'localhost' IDENTIFIED BY '${dbpass_sql}';
ALTER USER '${dbuser}'@'localhost' IDENTIFIED BY '${dbpass_sql}';
GRANT ALL PRIVILEGES ON \`${dbname}\`.* TO '${dbuser}'@'localhost';
FLUSH PRIVILEGES;
SQL
                then
                    ok "$dbuser → $dbname (GRANT aplicado)"
                    REGRANT_COUNT=$((REGRANT_COUNT+1))
                else
                    warn "$dbuser@$dbname: GRANT falló"
                    REGRANT_FAILS=$((REGRANT_FAILS+1))
                fi
            done < <(mysql "${MYSQL_AUTH[@]}" -N -B -e "
                SELECT db_name,
                       db_username,
                       db_password_enc,
                       TO_BASE64(iv),
                       TO_BASE64(tag)
                FROM limpioo.users_databases
                WHERE enabled = 1" 2>/dev/null)

            if [[ $REGRANT_FAILS -gt 0 ]]; then
                warn "Regrant: $REGRANT_COUNT OK, $REGRANT_FAILS fallos"
            else
                ok "Regrant completado: $REGRANT_COUNT usuarios reasignados"
            fi
        fi
    fi
fi

# ── Resumen final ────────────────────────────────────────────────────────────
echo ""
echo -e "${CYAN}============================================================${NC}"
if [[ $DRY_RUN -eq 1 ]]; then
    echo -e "${YELLOW}   DRY-RUN completado. No se ejecutó nada destructivo.${NC}"
else
    if [[ $POST_MISMATCH -eq 1 ]]; then
        echo -e "${YELLOW}   ⚠️  Migración terminada CON ADVERTENCIAS.${NC}"
        echo -e "${YELLOW}      Revisa $MANIFEST y los .errors.log${NC}"
    else
        echo -e "${GREEN}   ✅ Migración v1.0 → v2.0 completada con éxito.${NC}"
    fi
    echo ""
    echo -e "   Backups en: ${CYAN}$BACKUP_DIR${NC}"
    echo -e "   Manifest:   ${CYAN}$MANIFEST${NC}"
    echo -e "   Log:        ${CYAN}$LOG_FILE${NC}"
    echo ""
    echo -e "   Rollback manual de una BD:"
    echo -e "     ${YELLOW}mysql -u root DBNAME < $BACKUP_DIR/full_DBNAME.sql${NC}"
    echo ""
    echo -e "   Tras validar, puedes borrar los backups:"
    echo -e "     ${YELLOW}rm -rf $BACKUP_DIR${NC}"
fi
echo -e "${CYAN}============================================================${NC}"
echo ""