#!/usr/bin/env bash
set -euo pipefail

# Uso:
#   ./mysqlsh_restore_hardened.sh <DUMP_DIR> <DATABASE> [EXCLUDE_TABLES] [SKIP_VIEWS_TRIGGERS]
# Ejemplo:
#   ./mysqlsh_restore_hardened.sh /backups/mbinv_20260602_010000 mbinv "" 1
# Opcional:
#   MYSQL_CNF=/ruta/credenciales.cnf ./mysqlsh_restore_hardened.sh /backups/mbinv_20260602_010000 mbinv "" 1

DUMP_DIR="${1:-}"
DATABASE="${2:-}"
EXCLUDE_TABLES="${3:-}"
SKIP_VIEWS_TRIGGERS="${4:-0}"  # 1 = omitir vistas y triggers
LOAD_THREADS="${LOAD_THREADS:-8}"
CREATE_INVISIBLE_PKS="${CREATE_INVISIBLE_PKS:-1}"

if [[ -z "$DUMP_DIR" || -z "$DATABASE" ]]; then
  echo "Uso: $0 <DUMP_DIR> <DATABASE> [EXCLUDE_TABLES] [SKIP_VIEWS_TRIGGERS]"
  echo "Ej:  $0 /backups/mbinv_20260602_010000 mbinv \"\" 1"
  exit 1
fi

if [[ ! -d "$DUMP_DIR" ]]; then
  echo "Error: no existe el directorio de dump: $DUMP_DIR"
  exit 1
fi

# Configuracion de conexion (forzada por defecto, sobreescribible por env/.cnf)
DB_USER="${DB_USER:-manuel}"
DB_PASSWORD="${DB_PASSWORD:-Manuel$123}"
DB_HOST="${DB_HOST:-132.226.40.48}"
DB_PORT="${DB_PORT:-3310}"

# Archivo opcional de credenciales estilo ini:
# [client]
# user=...
# password=...
# host=...
# port=...
MYSQL_CNF="${MYSQL_CNF:-}"
MYSQLSH_BIN="${MYSQLSH_BIN:-mysqlsh}"

if [[ -n "$MYSQL_CNF" ]]; then
  if [[ ! -f "$MYSQL_CNF" ]]; then
    echo "Error: MYSQL_CNF no existe: $MYSQL_CNF"
    exit 1
  fi
  # Si existen en el archivo, sobreescriben defaults.
  DB_USER="$(awk -F= '/^[[:space:]]*user[[:space:]]*=/{gsub(/^[[:space:]]+|[[:space:]]+$/, "", $2); print $2; exit}' "$MYSQL_CNF" || true)"
  DB_PASSWORD="$(awk -F= '/^[[:space:]]*password[[:space:]]*=/{gsub(/^[[:space:]]+|[[:space:]]+$/, "", $2); print $2; exit}' "$MYSQL_CNF" || true)"
  DB_HOST="$(awk -F= '/^[[:space:]]*host[[:space:]]*=/{gsub(/^[[:space:]]+|[[:space:]]+$/, "", $2); print $2; exit}' "$MYSQL_CNF" || true)"
  DB_PORT="$(awk -F= '/^[[:space:]]*port[[:space:]]*=/{gsub(/^[[:space:]]+|[[:space:]]+$/, "", $2); print $2; exit}' "$MYSQL_CNF" || true)"
fi

if [[ -z "$DB_USER" || -z "$DB_PASSWORD" || -z "$DB_HOST" || -z "$DB_PORT" ]]; then
  echo "Error: faltan credenciales/host/puerto para conexion MySQL"
  exit 1
fi

if ! command -v "$MYSQLSH_BIN" >/dev/null 2>&1; then
  echo "Error: no se encontro MYSQLSH_BIN=$MYSQLSH_BIN"
  exit 1
fi

if [[ ! "$LOAD_THREADS" =~ ^[0-9]+$ ]] || [[ "$LOAD_THREADS" -lt 1 ]] || [[ "$LOAD_THREADS" -gt 64 ]]; then
  echo "Error: LOAD_THREADS debe ser un entero entre 1 y 64"
  exit 1
fi

if [[ "$CREATE_INVISIBLE_PKS" != "0" && "$CREATE_INVISIBLE_PKS" != "1" ]]; then
  echo "Error: CREATE_INVISIBLE_PKS debe ser 0 o 1"
  exit 1
fi

# Evitar herencia de variables de entorno que alteren conexion
unset MYSQL_PWD MYSQL_USER MYSQL_HOST MYSQL_TCP_PORT MYSQL_UNIX_PORT
unset MYSQLX_PWD MYSQLX_USER MYSQLX_HOST MYSQLX_TCP_PORT
unset MYSQLSH_USER MYSQLSH_PASSWORD MYSQLSH_URI

export TZ="America/Guatemala"
TARGET_DB="$DATABASE"
PROGRESS_FILE="/tmp/load-progress-${TARGET_DB}.json"

WORK_DIR="/tmp/load_dump_${TARGET_DB}"
rm -rf "$WORK_DIR"
mkdir -p "$WORK_DIR"

echo "Restore forzado hacia: ${DB_USER}@${DB_HOST}:${DB_PORT}"
echo "Dump origen: $DUMP_DIR"
echo "Base destino: $TARGET_DB"

# Guardas para evitar borrados accidentales.
if [[ ! "$TARGET_DB" =~ ^[A-Za-z0-9_]+$ ]]; then
  echo "Error: nombre de base de datos invalido: $TARGET_DB"
  exit 1
fi

case "$TARGET_DB" in
  mysql|sys|information_schema|performance_schema)
    echo "Error: base protegida, restore cancelado: $TARGET_DB"
    exit 1
    ;;
esac

ALLOW_DROP_DATABASE="${ALLOW_DROP_DATABASE:-0}"

TARGET_DB_EXISTS="$(MYSQL_PWD="$DB_PASSWORD" mysql -N -s -h "$DB_HOST" -u "$DB_USER" -P "$DB_PORT" -e "SELECT COUNT(*) FROM information_schema.schemata WHERE schema_name='${TARGET_DB}'" 2>/dev/null || echo 0)"
if [[ -z "$TARGET_DB_EXISTS" || ! "$TARGET_DB_EXISTS" =~ ^[0-9]+$ ]]; then
  TARGET_DB_EXISTS=0
fi

if [[ "$TARGET_DB_EXISTS" -gt 0 && "$ALLOW_DROP_DATABASE" != "1" ]]; then
  echo "Error: Operación detenida para evitar DROP accidental."
  echo "La base destino ya existe: $TARGET_DB"
  echo "Ejecuta agregando: ALLOW_DROP_DATABASE=1 al inicio."
  exit 1
fi

echo "Copiando dump a area temporal: $WORK_DIR"
cp -a "$DUMP_DIR"/. "$WORK_DIR"/

DATA_FILES_COUNT="$(find "$WORK_DIR" -type f \( -name '*.tsv' -o -name '*.tsv.zst' -o -name '*.zst' \) | wc -l | tr -d ' ')"
echo "Archivos de datos detectados en dump: $DATA_FILES_COUNT"
if [[ "$DATA_FILES_COUNT" == "0" ]]; then
  echo "Aviso: no se detectaron archivos de datos (.tsv/.zst). Revisar que el dump incluya data y no solo DDL."
fi

# Sanitizar DEFINER y usar INVOKER
find "$WORK_DIR" -type f -name "*.sql" -exec sed -i -E \
  -e 's/\/\*![0-9]{5} DEFINER=`[^`]+`@`[^`]+` \*\//\/* stripped DEFINER *\//g' \
  -e 's/DEFINER=`[^`]+`@`[^`]+`[ ]*//g' \
  -e 's/SQL SECURITY DEFINER/SQL SECURITY INVOKER/g' {} +

if [[ "$SKIP_VIEWS_TRIGGERS" == "1" ]]; then
  echo "Omitiendo vistas y triggers (SKIP_VIEWS_TRIGGERS=1)..."
  find "$WORK_DIR" -type f -name "*.sql" -exec sed -i -E \
    -e '/^-- begin trigger /,/^-- end trigger /d' \
    -e '/^-- begin view /,/^-- end view /d' {} +
fi

if [[ "$TARGET_DB_EXISTS" -gt 0 ]]; then
  MYSQL_PWD="$DB_PASSWORD" mysql \
    -h "$DB_HOST" -u "$DB_USER" -P "$DB_PORT" \
    -e "DROP DATABASE IF EXISTS \`$TARGET_DB\`; CREATE DATABASE \`$TARGET_DB\`;"
else
  MYSQL_PWD="$DB_PASSWORD" mysql \
    -h "$DB_HOST" -u "$DB_USER" -P "$DB_PORT" \
    -e "CREATE DATABASE IF NOT EXISTS \`$TARGET_DB\`;"
fi

CHECK_CMD="
var r=session.runSql(\"select user(), @@hostname, @@port\").fetchOne();
print('Endpoint solicitado: ${DB_HOST}:${DB_PORT}');
print('Conectado como: ' + r[0] + ' | Host DB reportado: ' + r[1] + ' | Puerto DB reportado: ' + r[2]);
if (String(r[0]).split('@')[0] !== '${DB_USER}') {
  throw new Error('Conexion inesperada: usuario autenticado no coincide con el destino forzado');
}
"

"$MYSQLSH_BIN" --js \
  --uri="${DB_USER}:${DB_PASSWORD}@${DB_HOST}:${DB_PORT}" \
  --execute "$CHECK_CMD"

if [[ -z "$EXCLUDE_TABLES" ]]; then
  LOAD_CMD="util.loadDump('$WORK_DIR', {
    'ignoreVersion': true,
    'resetProgress': true,
    'threads': $LOAD_THREADS,
    'createInvisiblePKs': $( [[ "$CREATE_INVISIBLE_PKS" == "1" ]] && echo true || echo false ),
    'loadDdl': true,
    'loadData': true,
    'schema': '$TARGET_DB',
    'progressFile': '$PROGRESS_FILE',
    'showProgress': true
  })"
else
  LOAD_CMD="util.loadDump('$WORK_DIR', {
    'ignoreVersion': true,
    'resetProgress': true,
    'threads': $LOAD_THREADS,
    'createInvisiblePKs': $( [[ "$CREATE_INVISIBLE_PKS" == "1" ]] && echo true || echo false ),
    'loadDdl': true,
    'loadData': true,
    'excludeTables': [$EXCLUDE_TABLES],
    'schema': '$TARGET_DB',
    'progressFile': '$PROGRESS_FILE',
    'showProgress': true
  })"
fi

echo "Ejecutando restore con LOAD_THREADS=$LOAD_THREADS y CREATE_INVISIBLE_PKS=$CREATE_INVISIBLE_PKS..."
printf '%s\n' "$LOAD_CMD" | "$MYSQLSH_BIN" "${DB_USER}:${DB_PASSWORD}@${DB_HOST}:${DB_PORT}" --verbose=0 --js

echo "Restore finalizado: $TARGET_DB"
rm -rf "$WORK_DIR"