#!/bin/bash
# ============================================================================
# LIMPIOO — Restaurar dumps de PRODUCCIÓN en este servidor (DEV)
# ============================================================================
# DESTRUCTIVO en local: hace DROP de las BDs `limpioo` y `limpioo_userdb_%`
# existentes en este servidor y las sustituye por las del paquete recibido
# de producción.
#
# El paquete debe haberse generado con `backup_production.sh` (contiene
# `full_*.sql` o `full_*.sql.gz` y opcionalmente `SHA256SUMS`).
#
# Uso:
#   sudo bash database/migrations/restore_from_prod.sh --backup-dir=PATH [--yes]
#
# Tras restaurar, ejecutar la migración:
#   sudo bash database/migrations/upgrade_v1_to_v2.sh
# ============================================================================

set -euo pipefail

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 ─────────────────────────────────────────────────────────────────────
BACKUP_DIR=""
ASSUME_YES=0
for arg in "$@"; do
    case "$arg" in
        --backup-dir=*) BACKUP_DIR="${arg#*=}" ;;
        --yes|-y)       ASSUME_YES=1 ;;
        -h|--help)      sed -n '2,20p' "$0"; exit 0 ;;
        *) fail "Argumento desconocido: $arg"; exit 2 ;;
    esac
done

if [[ -z "$BACKUP_DIR" ]]; then
    fail "Falta --backup-dir=PATH"
    exit 2
fi
if [[ ! -d "$BACKUP_DIR" ]]; then
    # Permitir nombre relativo dentro de database/backups/
    SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
    DB_ROOT="$(cd "$SCRIPT_DIR/.." && pwd)"
    if [[ -d "$DB_ROOT/backups/$BACKUP_DIR" ]]; then
        BACKUP_DIR="$DB_ROOT/backups/$BACKUP_DIR"
    else
        fail "Directorio no encontrado: $BACKUP_DIR"
        exit 1
    fi
fi
BACKUP_DIR="$(cd "$BACKUP_DIR" && pwd)"

echo ""
echo -e "${CYAN}============================================================${NC}"
echo -e "${CYAN}   LIMPIOO — Restaurar dumps de PROD en DEV${NC}"
echo -e "${CYAN}   $(date)${NC}"
echo -e "${CYAN}============================================================${NC}"
info "Paquete: $BACKUP_DIR"

# ── Verificaciones ───────────────────────────────────────────────────────────
step "1/5" "Verificaciones previas"
[[ $EUID -eq 0 ]] || { fail "Ejecuta como root"; exit 1; }
ok "Ejecución como root"

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

# Listar dumps
mapfile -t DUMPS < <(find "$BACKUP_DIR" -maxdepth 1 -type f \( -name 'full_*.sql' -o -name 'full_*.sql.gz' \) | sort)
if [[ ${#DUMPS[@]} -eq 0 ]]; then
    fail "No hay archivos full_*.sql[.gz] en $BACKUP_DIR"
    exit 1
fi
echo "   Dumps detectados:"
for f in "${DUMPS[@]}"; do echo "     • $(basename "$f") ($(du -h "$f" | cut -f1))"; done

# ── Verificar checksums (si existen) ─────────────────────────────────────────
step "2/5" "Verificar SHA256SUMS"
if [[ -f "$BACKUP_DIR/SHA256SUMS" ]]; then
    if ( cd "$BACKUP_DIR" && sha256sum -c SHA256SUMS --quiet ); then
        ok "Checksums verificados"
    else
        fail "Checksums inválidos — paquete corrupto"
        exit 1
    fi
else
    warn "SHA256SUMS no presente, omitiendo verificación"
fi

# ── Credenciales ─────────────────────────────────────────────────────────────
step "3/5" "Credenciales de MySQL root (LOCAL/DEV)"
read -p "   Usuario root MySQL [root]: " MYSQL_USER
MYSQL_USER="${MYSQL_USER:-root}"
read -sp "   Contraseña (vacío si usa socket): " MYSQL_PASS
echo ""
if [[ -z "$MYSQL_PASS" ]]; then
    MYSQL_AUTH=(-u "$MYSQL_USER")
else
    MYSQL_AUTH=(-u "$MYSQL_USER" "-p$MYSQL_PASS")
fi
mysql "${MYSQL_AUTH[@]}" -e "SELECT 1" &>/dev/null || { fail "Conexión MySQL fallida"; exit 1; }
ok "Conexión MySQL verificada"

# ── Calcular lista de BDs a restaurar ────────────────────────────────────────
DBS_TO_RESTORE=()
for f in "${DUMPS[@]}"; do
    name=$(basename "$f")
    name="${name#full_}"
    name="${name%.sql.gz}"
    name="${name%.sql}"
    DBS_TO_RESTORE+=("$name")
done

# Detectar BDs existentes
mapfile -t EXISTING_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 '\\\\'"
)

# ── Confirmación ─────────────────────────────────────────────────────────────
echo ""
warn "Se van a SUSTITUIR estas BDs locales por las de producción:"
for db in "${DBS_TO_RESTORE[@]}"; do
    in_local=""
    for e in "${EXISTING_DBS[@]:-}"; do [[ "$e" == "$db" ]] && in_local=" (existe localmente)"; done
    echo "     • $db$in_local"
done
if [[ ${#EXISTING_DBS[@]} -gt 0 ]]; then
    echo ""
    warn "Las siguientes BDs locales adicionales NO se tocan:"
    for e in "${EXISTING_DBS[@]}"; do
        in_pkg=""
        for d in "${DBS_TO_RESTORE[@]}"; do [[ "$d" == "$e" ]] && in_pkg="x"; done
        [[ -z "$in_pkg" ]] && echo "     • $e"
    done
fi

if [[ $ASSUME_YES -eq 0 ]]; then
    echo ""
    read -p "   ¿Continuar? Escribe 'SI' para confirmar: " CONFIRM
    [[ "$CONFIRM" == "SI" ]] || { fail "Cancelado por el usuario"; exit 1; }
fi

# ── Restaurar ────────────────────────────────────────────────────────────────
step "4/5" "DROP + restaurar cada BD"
for db in "${DBS_TO_RESTORE[@]}"; do
    echo "   → $db"
    mysql "${MYSQL_AUTH[@]}" -e "DROP DATABASE IF EXISTS \`$db\`"

    # Buscar el archivo correspondiente
    if   [[ -f "$BACKUP_DIR/full_${db}.sql.gz" ]]; then
        gunzip -c "$BACKUP_DIR/full_${db}.sql.gz" | mysql "${MYSQL_AUTH[@]}"
    elif [[ -f "$BACKUP_DIR/full_${db}.sql" ]]; then
        mysql "${MYSQL_AUTH[@]}" < "$BACKUP_DIR/full_${db}.sql"
    else
        fail "No encontrado dump para $db"; exit 1
    fi

    # Verificar que existe
    found=$(mysql "${MYSQL_AUTH[@]}" -N -B -e "SHOW DATABASES LIKE '$db'" || true)
    if [[ "$found" == "$db" ]]; then
        ok "$db restaurada"
    else
        fail "$db NO restaurada correctamente"
        exit 1
    fi
done

# ── Resumen ──────────────────────────────────────────────────────────────────
step "5/5" "Resumen"
echo "   Conteos por BD restaurada:"
for db in "${DBS_TO_RESTORE[@]}"; do
    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)
        echo "     $db.$t = $c"
    done < <(mysql "${MYSQL_AUTH[@]}" -N -B -e \
        "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
         WHERE TABLE_SCHEMA='$db' AND TABLE_TYPE='BASE TABLE'")
done

echo ""
echo -e "${CYAN}============================================================${NC}"
echo -e "${GREEN}   ✅ Restauración completada.${NC}"
echo ""
echo -e "   Próximo paso — probar la migración v1.0 → v2.0:"
echo -e "     ${YELLOW}sudo bash database/migrations/upgrade_v1_to_v2.sh --dry-run${NC}"
echo -e "     ${YELLOW}sudo bash database/migrations/upgrade_v1_to_v2.sh${NC}"
echo -e "${CYAN}============================================================${NC}"
echo ""