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

# Uso:
#   ALLOW_DROP_DATABASE=1 ./mysqlsh_restore_hardened.sh <DUMP_DIR> <DATABASE> [EXCLUDE_TABLES] [SKIP_VIEWS_TRIGGERS]
# Ejemplos:
#   ALLOW_DROP_DATABASE=1 ./mysqlsh_restore_hardened.sh /backups/mbinv_20260602_010000 mbinv "" 1
#   INCLUDE_TABLES='"'"'mbinv.tabla1'"'"','"'"'origen.tabla1'"'"'' ALLOW_DROP_DATABASE=1 ./mysqlsh_restore_hardened.sh /backups/dump mbinv "" 1

DUMP_DIR="${1:-}"
DATABASE="${2:-}"
EXCLUDE_TABLES="${3:-}"
SKIP_VIEWS_TRIGGERS="${4:-0}"  # 1 = omitir vistas/triggers/.post.sql
INCLUDE_TABLES="${INCLUDE_TABLES:-}"

if [[ -z "$DUMP_DIR" || -z "$DATABASE" ]]; then
  echo "Uso: $0 <DUMP_DIR> <DATABASE> [EXCLUDE_TABLES] [SKIP_VIEWS_TRIGGERS]"
  exit 1
fi

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

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

ALLOW_DROP_DATABASE="${ALLOW_DROP_DATABASE:-0}"
RESTORE_RESUME="${RESTORE_RESUME:-0}"
START_FROM_TABLE="${START_FROM_TABLE:-}"

# Conexion (se puede sobreescribir por env o MYSQL_CNF)
DB_USER="${DB_USER:-manuel}"
DB_PASSWORD="${DB_PASSWORD:-}"
DB_HOST="${DB_HOST:-}"
DB_PORT="${DB_PORT:-3306}"
MYSQL_CNF="${MYSQL_CNF:-}"

if [[ -n "$MYSQL_CNF" ]]; then
  if [[ ! -f "$MYSQL_CNF" ]]; then
    echo "Error: MYSQL_CNF no existe: $MYSQL_CNF"
    exit 1
  fi
  CNF_DB_USER="$(awk -F= '/^[[:space:]]*user[[:space:]]*=/{gsub(/^[[:space:]]+|[[:space:]]+$/, "", $2); print $2; exit}' "$MYSQL_CNF" || true)"
  CNF_DB_PASSWORD="$(awk -F= '/^[[:space:]]*password[[:space:]]*=/{gsub(/^[[:space:]]+|[[:space:]]+$/, "", $2); print $2; exit}' "$MYSQL_CNF" || true)"
  CNF_DB_HOST="$(awk -F= '/^[[:space:]]*host[[:space:]]*=/{gsub(/^[[:space:]]+|[[:space:]]+$/, "", $2); print $2; exit}' "$MYSQL_CNF" || true)"
  CNF_DB_PORT="$(awk -F= '/^[[:space:]]*port[[:space:]]*=/{gsub(/^[[:space:]]+|[[:space:]]+$/, "", $2); print $2; exit}' "$MYSQL_CNF" || true)"

  [[ -z "$DB_USER" && -n "$CNF_DB_USER" ]] && DB_USER="$CNF_DB_USER"
  [[ -z "$DB_PASSWORD" && -n "$CNF_DB_PASSWORD" ]] && DB_PASSWORD="$CNF_DB_PASSWORD"
  [[ -z "$DB_HOST" && -n "$CNF_DB_HOST" ]] && DB_HOST="$CNF_DB_HOST"
  [[ -z "$DB_PORT" && -n "$CNF_DB_PORT" ]] && DB_PORT="$CNF_DB_PORT"
fi

DB_HOST="${DB_HOST:-10.0.1.5}"

if [[ -z "$DB_USER" || -z "$DB_HOST" || -z "$DB_PORT" ]]; then
  echo "Error: faltan DB_USER/DB_HOST/DB_PORT"
  exit 1
fi
if [[ -z "$DB_PASSWORD" ]]; then
  echo "Error: DB_PASSWORD vacío. Define DB_PASSWORD o MYSQL_CNF"
  exit 1
fi

if ! command -v mysql >/dev/null 2>&1; then
  echo "Error: no se encontró el cliente mysql"
  exit 1
fi
if ! command -v find >/dev/null 2>&1; then
  echo "Error: no se encontró find"
  exit 1
fi
if ! command -v awk >/dev/null 2>&1; then
  echo "Error: no se encontró awk"
  exit 1
fi
if ! command -v sed >/dev/null 2>&1; then
  echo "Error: no se encontró sed"
  exit 1
fi
if ! command -v tr >/dev/null 2>&1; then
  echo "Error: no se encontró tr"
  exit 1
fi
if ! command -v mktemp >/dev/null 2>&1; then
  echo "Error: no se encontró mktemp"
  exit 1
fi

TARGET_DB="$DATABASE"
export TZ="America/Guatemala"
RESTORE_START_TS="$(date +%s)"

if [[ ! "$TARGET_DB" =~ ^[A-Za-z0-9_]+$ ]]; then
  echo "Error: nombre de base de datos inválido: $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

DB_EXISTE="$(MYSQL_PWD="$DB_PASSWORD" mysql -h "$DB_HOST" -u "$DB_USER" -P "$DB_PORT" -Nse "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME='${TARGET_DB}' LIMIT 1;" 2>/dev/null || true)"
if [[ -n "$DB_EXISTE" && "$ALLOW_DROP_DATABASE" != "1" && "$RESTORE_RESUME" != "1" && -z "$START_FROM_TABLE" ]]; then
  echo "Error: la base '$TARGET_DB' ya existe en ${DB_HOST}:${DB_PORT}."
  echo "Para continuar con reemplazo seguro, ejecuta con ALLOW_DROP_DATABASE=1"
  exit 1
fi

normalize_list() {
  local raw="$1"
  printf '%s' "$raw" | tr -d " '\""
}

in_csv_list() {
  local needle="$1"
  local csv="$2"
  local item

  [[ -z "$csv" ]] && return 1

  IFS=',' read -r -a _items <<< "$csv"
  for item in "${_items[@]}"; do
    [[ -z "$item" ]] && continue
    if [[ "$item" == "$needle" ]]; then
      return 0
    fi
    if [[ "$item" == *.* ]]; then
      if [[ "${item##*.}" == "$needle" ]]; then
        return 0
      fi
    fi
  done

  return 1
}

INCLUDE_TABLES_NORM="$(normalize_list "$INCLUDE_TABLES")"
EXCLUDE_TABLES_NORM="$(normalize_list "$EXCLUDE_TABLES")"
START_FROM_TABLE_NORM="$(normalize_list "$START_FROM_TABLE")"

checkpoint_key="${DUMP_DIR}|${TARGET_DB}|${INCLUDE_TABLES_NORM}|${EXCLUDE_TABLES_NORM}|${SKIP_VIEWS_TRIGGERS}"
checkpoint_key_safe="$(printf '%s' "$checkpoint_key" | tr -c 'A-Za-z0-9' '_' | cut -c1-160)"
CHECKPOINT_FILE="${RESTORE_CHECKPOINT_FILE:-/tmp/restore_checkpoint_${checkpoint_key_safe}.txt}"

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

if [[ -n "$START_FROM_TABLE_NORM" ]] && [[ ! "$START_FROM_TABLE_NORM" =~ ^[A-Za-z0-9_]+$ ]]; then
  echo "Error: START_FROM_TABLE inválida: $START_FROM_TABLE_NORM"
  exit 1
fi

should_skip_table() {
  local table_name="$1"

  if [[ -n "$INCLUDE_TABLES_NORM" ]]; then
    if ! in_csv_list "$table_name" "$INCLUDE_TABLES_NORM"; then
      return 0
    fi
  fi

  if [[ -n "$EXCLUDE_TABLES_NORM" ]]; then
    if in_csv_list "$table_name" "$EXCLUDE_TABLES_NORM"; then
      return 0
    fi
  fi

  return 1
}

list_data_files() {
  find "$DUMP_DIR" -type f \( -name "*@*@*.tsv.zst" -o -name "*@*@*.tsv" -o -name "*@*@*.tsv.gz" \) 2>/dev/null | sort -V
}

SOURCE_SCHEMA=""
if [[ -f "$DUMP_DIR/@.json" ]]; then
  SOURCE_SCHEMA="$(sed -nE 's/.*"schemas"[[:space:]]*:[[:space:]]*\[[[:space:]]*"([^"]+)".*/\1/p' "$DUMP_DIR/@.json" | sed -n '1p' || true)"
fi

if [[ -z "$SOURCE_SCHEMA" ]]; then
  first_data_file="$(find "$DUMP_DIR" -type f \( -name '*@*@*.tsv.zst' -o -name '*@*@*.tsv' -o -name '*@*@*.tsv.gz' \) | sort -V | sed -n '1p' || true)"
  if [[ -n "$first_data_file" ]]; then
    SOURCE_SCHEMA="$(basename "$first_data_file" | awk -F'@' '{print $1}')"
  fi
fi

echo "================================================================="
echo " RESTORE OLD-SCHOOL (LOAD DATA LOCAL INFILE)"
echo "================================================================="
echo "Destino         : ${DB_USER}@${DB_HOST}:${DB_PORT}"
echo "Dump origen     : $DUMP_DIR"
echo "Base destino    : $TARGET_DB"
echo "Esquema origen  : ${SOURCE_SCHEMA:-desconocido}"
if [[ -n "$INCLUDE_TABLES_NORM" ]]; then
  echo "Include tables  : $INCLUDE_TABLES_NORM"
fi
if [[ -n "$EXCLUDE_TABLES_NORM" ]]; then
  echo "Exclude tables  : $EXCLUDE_TABLES_NORM"
fi
if [[ -n "$START_FROM_TABLE_NORM" ]]; then
  echo "Start from table: $START_FROM_TABLE_NORM"
fi
if [[ "$RESTORE_RESUME" == "1" ]]; then
  echo "Resume          : habilitado"
  echo "Checkpoint file : $CHECKPOINT_FILE"
fi
echo "-----------------------------------------------------------------"

if [[ "$RESTORE_RESUME" == "1" ]]; then
  if [[ ! -f "$CHECKPOINT_FILE" ]]; then
    : > "$CHECKPOINT_FILE"
  fi
  echo "-> Resume mode: conservando base existente y checkpoint actual"
  MYSQL_PWD="$DB_PASSWORD" mysql -h "$DB_HOST" -u "$DB_USER" -P "$DB_PORT" \
    -e "CREATE DATABASE IF NOT EXISTS \`$TARGET_DB\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
else
  : > "$CHECKPOINT_FILE"
  echo "-> Inicializando base de datos limpia..."
  if [[ "$ALLOW_DROP_DATABASE" == "1" ]]; 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\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
  else
    MYSQL_PWD="$DB_PASSWORD" mysql -h "$DB_HOST" -u "$DB_USER" -P "$DB_PORT" \
      -e "CREATE DATABASE IF NOT EXISTS \`$TARGET_DB\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
  fi
  echo "   [OK] Base de datos vacía creada."
fi

# FASE 1: Estructuras
echo "-> Fase 1: Creando estructuras..."

if [[ "$RESTORE_RESUME" == "1" && -s "$CHECKPOINT_FILE" ]]; then
  echo "   [SKIP] Fase 1 omitida por modo resume con checkpoint existente"
  sql_count=0
else

sql_count=0
while IFS= read -r sql_file; do
  [[ -e "$sql_file" ]] || continue
  filename="$(basename "$sql_file")"

  if [[ "$SKIP_VIEWS_TRIGGERS" == "1" ]]; then
    if [[ "$filename" == *".post.sql"* || "$filename" == *"trigger"* || "$filename" == *"view"* || "$filename" == *"@.sql"* ]]; then
      continue
    fi
  fi

  table_name=""
  if [[ "$filename" == *"@"* ]]; then
    table_name="$(echo "$filename" | awk -F'@' '{print $2}' | sed -E 's/\..*$//')"
  fi

  if [[ -n "$table_name" ]] && should_skip_table "$table_name"; then
    continue
  fi

  if [[ -n "$SOURCE_SCHEMA" && "$SOURCE_SCHEMA" != "$TARGET_DB" ]]; then
    sed_schema=(
      -e "s/\`$SOURCE_SCHEMA\`\./\`$TARGET_DB\`./g"
      -e "s/USE \`$SOURCE_SCHEMA\`/USE \`$TARGET_DB\`/g"
      -e "s/CREATE DATABASE IF NOT EXISTS \`$SOURCE_SCHEMA\`/CREATE DATABASE IF NOT EXISTS \`$TARGET_DB\`/g"
    )
  else
    sed_schema=()
  fi

  (
    echo "SET FOREIGN_KEY_CHECKS=0;"
    echo "SET UNIQUE_CHECKS=0;"
    tr -d '\r' < "$sql_file" | sed -E \
      -e 's/\/\*![0-9]{5} DEFINER=`[^`]+`@`[^`]+` \*\//\/* stripped DEFINER *\//g' \
      -e 's/DEFINER=`[^`]+`@`[^`]+`[ ]*//g' \
      -e 's/SQL SECURITY DEFINER/SQL SECURITY INVOKER/g' \
      "${sed_schema[@]}"
    echo ";SET FOREIGN_KEY_CHECKS=1;"
    echo "SET UNIQUE_CHECKS=1;"
  ) | MYSQL_PWD="$DB_PASSWORD" mysql -h "$DB_HOST" -u "$DB_USER" -P "$DB_PORT" --default-character-set=utf8mb4 "$TARGET_DB" 2>/dev/null || true

  sql_count=$((sql_count + 1))
done < <(find "$DUMP_DIR" -type f -name "*.sql" 2>/dev/null | sort)
fi

echo "   [OK] Estructuras procesadas: $sql_count"

# FASE 2: Datos
echo "-> Fase 2: Cargando datos..."

if ! command -v zstd >/dev/null 2>&1; then
  echo "Error: zstd no está instalado y es requerido para archivos .zst"
  exit 1
fi

tmp_infile="$(mktemp /tmp/restore_load_data_XXXXXX.tsv)"
tmp_tiempos_tabla="$(mktemp /tmp/restore_table_times_XXXXXX.tsv)"
cleanup() {
  rm -f "$tmp_infile"
  rm -f "$tmp_tiempos_tabla"
}
trap cleanup EXIT

loaded_chunks=0
total_chunks=0
failed_chunks=0
skipped_view_chunks=0
skipped_missing_chunks=0
skipped_checkpoint_chunks=0
skipped_start_from_chunks=0
declare -a failed_items=()

if [[ -n "$START_FROM_TABLE_NORM" ]]; then
  start_table_exists=0
  while IFS= read -r data_file; do
    filename_check="$(basename "$data_file")"
    table_check="$(echo "$filename_check" | awk -F'@' '{print $2}' | sed -E 's/@@.*$//')"
    if [[ -z "$table_check" ]]; then
      continue
    fi
    if should_skip_table "$table_check"; then
      continue
    fi
    if [[ "$table_check" == "$START_FROM_TABLE_NORM" ]]; then
      start_table_exists=1
      break
    fi
  done < <(list_data_files)

  if [[ "$start_table_exists" -ne 1 ]]; then
    echo "Error: START_FROM_TABLE '$START_FROM_TABLE_NORM' no existe en el dump (o fue excluida por filtros)."
    exit 1
  fi
fi

while IFS= read -r data_file; do
  filename="$(basename "$data_file")"
  table_name="$(echo "$filename" | awk -F'@' '{print $2}' | sed -E 's/@@.*$//')"

  if [[ -z "$table_name" ]]; then
    continue
  fi

  if should_skip_table "$table_name"; then
    continue
  fi

  if [[ -n "$START_FROM_TABLE_NORM" ]] && [[ "$table_name" < "$START_FROM_TABLE_NORM" ]]; then
    echo "   -> Saltando (start_from_table): $table_name :: $filename"
    skipped_start_from_chunks=$((skipped_start_from_chunks + 1))
    continue
  fi

  total_chunks=$((total_chunks + 1))

  table_type="$(MYSQL_PWD="$DB_PASSWORD" mysql -Nse "SELECT TABLE_TYPE FROM information_schema.TABLES WHERE TABLE_SCHEMA='${TARGET_DB}' AND TABLE_NAME='${table_name}' LIMIT 1;" -h "$DB_HOST" -u "$DB_USER" -P "$DB_PORT" </dev/null 2>/dev/null || true)"
  if [[ "$table_type" == "VIEW" ]]; then
    echo "   -> Omitiendo vista: $table_name :: $filename"
    skipped_view_chunks=$((skipped_view_chunks + 1))
    failed_chunks=$((failed_chunks + 1))
    failed_items+=("$filename || vista omitida")
    continue
  fi
  if [[ "$table_type" != "BASE TABLE" ]]; then
    echo "   -> Omitiendo objeto no cargable (tabla no existe): $table_name :: $filename"
    skipped_missing_chunks=$((skipped_missing_chunks + 1))
    failed_chunks=$((failed_chunks + 1))
    failed_items+=("$filename || tabla base no existe en destino")
    continue
  fi

  if [[ "$RESTORE_RESUME" == "1" ]] && grep -Fxq "$filename" "$CHECKPOINT_FILE"; then
    echo "   -> Saltando (checkpoint): $table_name :: $filename"
    skipped_checkpoint_chunks=$((skipped_checkpoint_chunks + 1))
    continue
  fi

  echo "   -> Inyectando: $table_name :: $filename"

  rm -f "$tmp_infile"
  decode_ok=1

  case "$data_file" in
    *.zst)
      if ! zstd -d -c "$data_file" > "$tmp_infile"; then
        decode_ok=0
      fi
      ;;
    *.gz)
      if ! gzip -dc "$data_file" > "$tmp_infile"; then
        decode_ok=0
      fi
      ;;
    *)
      if ! cp "$data_file" "$tmp_infile"; then
        decode_ok=0
      fi
      ;;
  esac

  if [[ "$decode_ok" -ne 1 ]]; then
    echo "   [ERROR] No se pudo preparar chunk: $filename"
    failed_chunks=$((failed_chunks + 1))
    failed_items+=("$filename || error al descomprimir/copiar chunk")
    rm -f "$tmp_infile"
    continue
  fi

  t0="$(date +%s)"

  if MYSQL_PWD="$DB_PASSWORD" mysql --local-infile=1 -h "$DB_HOST" -u "$DB_USER" -P "$DB_PORT" "$TARGET_DB" </dev/null \
    -e "SET foreign_key_checks=0; SET unique_checks=0; LOAD DATA LOCAL INFILE '$tmp_infile' INTO TABLE \`$table_name\` FIELDS TERMINATED BY '\\t' LINES TERMINATED BY '\\n'; SET unique_checks=1; SET foreign_key_checks=1;"; then
    t1="$(date +%s)"
    elapsed="$((t1 - t0))"
    printf '%s\t%s\n' "$table_name" "$elapsed" >> "$tmp_tiempos_tabla"

    rm -f "$tmp_infile"
    loaded_chunks=$((loaded_chunks + 1))
    if [[ "$RESTORE_RESUME" == "1" ]]; then
      printf '%s\n' "$filename" >> "$CHECKPOINT_FILE"
    fi
  else
    echo "   [ERROR] Falló la carga de: $table_name :: $filename"
    failed_chunks=$((failed_chunks + 1))
    failed_items+=("$filename || error al cargar en tabla $table_name")
    rm -f "$tmp_infile"
    continue
  fi
done < <(list_data_files)

echo "   [OK] Chunks cargados: $loaded_chunks"
echo "   [OK] Chunks no cargados: $failed_chunks"
echo "   [OK] Vistas omitidas: $skipped_view_chunks"
if [[ "$skipped_missing_chunks" -gt 0 ]]; then
  echo "   [OK] Objetos no encontrados en destino: $skipped_missing_chunks"
fi
if [[ "$skipped_checkpoint_chunks" -gt 0 ]]; then
  echo "   [OK] Chunks saltados por checkpoint: $skipped_checkpoint_chunks"
fi
if [[ "$skipped_start_from_chunks" -gt 0 ]]; then
  echo "   [OK] Chunks saltados por start_from_table: $skipped_start_from_chunks"
fi

if [[ "${#failed_items[@]}" -gt 0 ]]; then
  echo "-----------------------------------------------------------------"
  echo " DETALLE DE CHUNKS NO CARGADOS"
  echo "-----------------------------------------------------------------"
  for item in "${failed_items[@]}"; do
    echo " - $item"
  done
fi

if [[ -s "$tmp_tiempos_tabla" ]]; then
  echo "-----------------------------------------------------------------"
  echo " RESUMEN POR TABLA (segundos)"
  echo "-----------------------------------------------------------------"
  printf '%-50s | %12s\n' "Tabla" "Tiempo (s)"
  printf '%-50s-+-%12s\n' "--------------------------------------------------" "------------"
  awk -F'\t' '{sum[$1]+=$2} END {for (t in sum) printf "%s\t%d\n", t, sum[t]}' "$tmp_tiempos_tabla" \
    | sort \
    | while IFS=$'\t' read -r tabla tiempo; do
        printf '%-50s | %12s\n' "$tabla" "$tiempo"
      done
fi

RESTORE_END_TS="$(date +%s)"
RESTORE_TOTAL_SEC="$((RESTORE_END_TS - RESTORE_START_TS))"

echo "-----------------------------------------------------------------"
echo " TIEMPO TOTAL RESTORE: ${RESTORE_TOTAL_SEC}s"
echo " RESUMEN RESTORE: total=${total_chunks}, cargados=${loaded_chunks}, no_cargados=${failed_chunks}"
if [[ "$failed_chunks" -gt 0 ]]; then
  echo " PROCESO FINALIZADO CON ERRORES PARCIALES: base '$TARGET_DB' restaurada parcialmente"
  echo " CHECKPOINT: $CHECKPOINT_FILE"
  echo "================================================================="
  exit 2
fi

echo " PROCESO FINALIZADO: base '$TARGET_DB' restaurada"
echo " CHECKPOINT: $CHECKPOINT_FILE"
echo "================================================================="
exit 0