#!/bin/bash
# ============================================================================
# LIMPIOO — Backup de PRODUCCIÓN (solo lectura, no destructivo)
# ============================================================================
# Genera dumps completos (estructura + datos) de `limpioo` y `limpioo_userdb_%`
# para llevarlos a otro servidor (p. ej. desarrollo) y probar la migración
# v1.0 → v2.0 con datos reales antes de aplicarla en producción.
#
# Este script NO modifica las bases de datos. Es seguro ejecutarlo en caliente
# (usa --single-transaction). NO requiere root del SO si MySQL no lo exige.
#
# Uso:
#   bash database/migrations/backup_production.sh [--output-dir=PATH] [--gzip]
#
# Salida (por defecto):
#   database/backups/prod_<HOSTNAME>_<TIMESTAMP>/
#     ├── manifest.txt
#     ├── full_limpioo.sql[.gz]
#     ├── full_limpioo_userdb_1.sql[.gz]
#     └── ...
#
# Para transferir a dev:
#   rsync -avz --progress database/backups/prod_*  user@dev:/var/www/html/limpioo.console/database/backups/
#
# En dev, restaurar y migrar:
#   1) bash database/migrations/restore_from_prod.sh --backup-dir=database/backups/prod_<...>
#   2) 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 ─────────────────────────────────────────────────────────────────────
OUTPUT_DIR=""
GZIP=0
for arg in "$@"; do
    case "$arg" in
        --output-dir=*) OUTPUT_DIR="${arg#*=}" ;;
        --gzip|-z)      GZIP=1 ;;
        -h|--help)      sed -n '2,30p' "$0"; exit 0 ;;
        *) fail "Argumento desconocido: $arg"; exit 2 ;;
    esac
done

SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
DB_ROOT="$(cd "$SCRIPT_DIR/.." && pwd)"
TIMESTAMP="$(date +%Y%m%d_%H%M%S)"
HOSTNAME_SHORT="$(hostname -s)"
[[ -z "$OUTPUT_DIR" ]] && OUTPUT_DIR="$DB_ROOT/backups/prod_${HOSTNAME_SHORT}_${TIMESTAMP}"

echo ""
echo -e "${CYAN}============================================================${NC}"
echo -e "${CYAN}   LIMPIOO — Backup de PRODUCCIÓN${NC}"
echo -e "${CYAN}   $(date)  —  $(hostname)${NC}"
echo -e "${CYAN}============================================================${NC}"

# ── Verificaciones ───────────────────────────────────────────────────────────
step "1/4" "Verificaciones previas"
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"

# ── Credenciales ─────────────────────────────────────────────────────────────
step "2/4" "Credenciales de MySQL"
read -p "   Usuario 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"

# ── Descubrir BDs ────────────────────────────────────────────────────────────
step "3/4" "Descubrir bases de datos"
mapfile -t 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 [[ ${#DBS[@]} -eq 0 ]]; then
    fail "No se encontró ninguna BD limpioo*"
    exit 1
fi
echo "   BDs detectadas:"
for d in "${DBS[@]}"; do echo "     • $d"; done

# Espacio en disco (usa el padre existente más cercano)
DISK_TARGET="$(dirname "$OUTPUT_DIR")"
while [[ ! -d "$DISK_TARGET" && "$DISK_TARGET" != "/" ]]; do
    DISK_TARGET="$(dirname "$DISK_TARGET")"
done
DISK_FREE_KB="$(df -P "$DISK_TARGET" 2>/dev/null | awk 'NR==2 {print $4}')"
DISK_FREE_KB="${DISK_FREE_KB:-0}"
DISK_FREE_MB=$(( DISK_FREE_KB / 1024 ))
if [[ $DISK_FREE_MB -lt 500 ]]; then
    warn "Solo ${DISK_FREE_MB} MB libres en destino. Recomendado: >500 MB."
else
    ok "Espacio en disco: ${DISK_FREE_MB} MB libres"
fi

# ── Dumps ────────────────────────────────────────────────────────────────────
step "4/4" "Generar backups en $OUTPUT_DIR"
mkdir -p "$OUTPUT_DIR"
MANIFEST="$OUTPUT_DIR/manifest.txt"
{
    echo "Backup de PRODUCCIÓN"
    echo "Servidor: $(hostname)"
    echo "Timestamp: $TIMESTAMP"
    echo "MySQL versión: $(mysql "${MYSQL_AUTH[@]}" -N -B -e 'SELECT VERSION()')"
    echo ""
    echo "Bases de datos:"
} > "$MANIFEST"

for db in "${DBS[@]}"; do
    echo "   → $db"
    OUT="$OUTPUT_DIR/full_${db}.sql"

    # Conteo de filas para manifest
    {
        echo ""
        echo "[$db]"
    } >> "$MANIFEST"
    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 "  $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'")

    if [[ $GZIP -eq 1 ]]; then
        mysqldump "${MYSQL_AUTH[@]}" \
            --single-transaction --routines --triggers --events \
            --hex-blob --default-character-set=utf8mb4 \
            --databases "$db" \
          | gzip -c > "${OUT}.gz"
        SIZE=$(du -h "${OUT}.gz" | cut -f1)
        ok "$db → ${OUT}.gz ($SIZE)"
    else
        mysqldump "${MYSQL_AUTH[@]}" \
            --single-transaction --routines --triggers --events \
            --hex-blob --default-character-set=utf8mb4 \
            --databases "$db" > "$OUT"
        [[ -s "$OUT" ]] || { fail "Dump vacío para $db"; exit 1; }
        SIZE=$(du -h "$OUT" | cut -f1)
        ok "$db → $OUT ($SIZE)"
    fi
done

# Checksum del paquete
( cd "$OUTPUT_DIR" && sha256sum full_*.sql* > SHA256SUMS )
ok "Checksums escritos en $OUTPUT_DIR/SHA256SUMS"

# ── Resumen ──────────────────────────────────────────────────────────────────
echo ""
echo -e "${CYAN}============================================================${NC}"
echo -e "${GREEN}   ✅ Backup de producción completado.${NC}"
echo ""
echo -e "   Directorio: ${CYAN}$OUTPUT_DIR${NC}"
echo -e "   Manifest:   ${CYAN}$MANIFEST${NC}"
echo ""
echo -e "   Para transferir a desarrollo:"
echo -e "     ${YELLOW}rsync -avz --progress $OUTPUT_DIR/ usuario@dev-host:/ruta/destino/${NC}"
echo ""
echo -e "   En dev, restaurar:"
echo -e "     ${YELLOW}sudo bash database/migrations/restore_from_prod.sh --backup-dir=$(basename "$OUTPUT_DIR")${NC}"
echo -e "${CYAN}============================================================${NC}"
echo ""