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

### === CONFIGURACIÓN ===
DB_DATABASE="unicornflower"
DB_USERNAME="souleater"
DB_PASSWORD='Enfsedlhf90234pfv(&'
DB_HOST="127.0.0.1"
DB_PORT="3306"

BK_FILE="${PWD}/fidbaq_backup.sql"     # o fidbaq_backup.sql.gz
BACKUP_DIR="${PWD}/backups"
CHARSET="utf8mb4"
COLLATE="utf8mb4_unicode_ci"

FORCE="${FORCE:-0}"                    # export FORCE=1 para saltar confirmación
### =====================

need() { command -v "$1" >/dev/null 2>&1; }
has_opt() { "$1" --help 2>&1 | grep -Fq -- "$2"; }

if ! need mysql || ! need mysqldump; then
  echo ">> Instalando cliente MariaDB/MySQL..."
  sudo apt-get update -y && sudo apt-get install -y mariadb-client
fi

mkdir -p "$BACKUP_DIR"

TS=$(date +%Y%m%d_%H%M%S)
DUMP_FILE="${BACKUP_DIR}/${DB_DATABASE}_${TS}.sql.gz"

if [[ "$FORCE" != "1" ]]; then
  read -rp "¡ATENCIÓN! Se DROPEARÁ y RECREARÁ la BD '${DB_DATABASE}'. ¿Continuar? [y/N] " ans
  [[ "${ans:-N}" =~ ^[Yy]$ ]] || { echo "Abortado."; exit 1; }
fi

# Credenciales temporales
CRED_FILE="$(mktemp)"
chmod 600 "$CRED_FILE"
cat > "$CRED_FILE" <<EOF
[client]
user=${DB_USERNAME}
password=${DB_PASSWORD}
host=${DB_HOST}
port=${DB_PORT}
protocol=tcp
default-character-set=${CHARSET}
EOF

cleanup() {
  rm -f "$CRED_FILE" /tmp/_fidbaq_backup.sql /tmp/_fidbaq_backup_sanitized.sql \
        /tmp/_final_import.sql /tmp/_header.sql /tmp/_footer.sql /tmp/_dump_err.log
}
trap cleanup EXIT

# Detecta event_scheduler
EVENT_SCHED="OFF"
if mysql --defaults-extra-file="$CRED_FILE" -N -e "SHOW VARIABLES LIKE 'event_scheduler';" 2>/dev/null | grep -iq 'ON$'; then
  EVENT_SCHED="ON"
fi

# Opciones base para dump
DUMP_OPTS_BASE=(--single-transaction --triggers)
# events
if has_opt mysqldump "--events" && [[ "$EVENT_SCHED" == "ON" ]]; then
  DUMP_OPTS_BASE+=(--events)
else
  DUMP_OPTS_BASE+=(--skip-events)
fi
# column-statistics (cliente 8 contra server antiguo)
if has_opt mysqldump "--column-statistics"; then
  DUMP_OPTS_BASE+=(--column-statistics=0)
fi
# set-gtid-purged si existe
if has_opt mysqldump "--set-gtid-purged"; then
  DUMP_OPTS_BASE+=(--set-gtid-purged=OFF)
fi

echo ">> Backup de '${DB_DATABASE}' → ${DUMP_FILE}"
# 1er intento: con routines
DUMP_OPTS_WITH_ROUT=( "${DUMP_OPTS_BASE[@]}" --routines )
set +e
mysqldump --defaults-extra-file="$CRED_FILE" "${DUMP_OPTS_WITH_ROUT[@]}" "$DB_DATABASE" \
  2> /tmp/_dump_err.log | gzip -c > "$DUMP_FILE"
RC=$?
set -e

if [[ $RC -ne 0 ]] || ! test -s "$DUMP_FILE"; then
  if grep -qi -- 'mysql\.proc' /tmp/_dump_err.log; then
    echo ">> Detectado problema con mysql.proc. Reintentando backup SIN --routines..."
    DUMP_OPTS_NO_ROUT=( "${DUMP_OPTS_BASE[@]}" --skip-routines )
    : > "$DUMP_FILE"
    mysqldump --defaults-extra-file="$CRED_FILE" "${DUMP_OPTS_NO_ROUT[@]}" "$DB_DATABASE" \
      | gzip -c > "$DUMP_FILE"
    test -s "$DUMP_FILE" || { echo "ERROR: backup vacío tras reintento"; exit 1; }
  else
    echo "ERROR en mysqldump:"; tail -n +1 /tmp/_dump_err.log; exit 1
  fi
fi
echo "OK. ($(ls -lh "$DUMP_FILE" | awk '{print $5}'))"

# Fuente del dump de entrada
if [[ -f "$BK_FILE" ]]; then
  SRC="$BK_FILE"
elif [[ -f "${BK_FILE}.gz" ]]; then
  SRC="${BK_FILE}.gz"
else
  echo "ERROR: No encuentro ${BK_FILE} (.sql o .sql.gz) en $(pwd)"; exit 1
fi

echo ">> Preparando dump de entrada: $SRC"
if [[ "$SRC" == *.gz ]]; then
  gunzip -c "$SRC" > /tmp/_fidbaq_backup.sql
else
  cp -f "$SRC" /tmp/_fidbaq_backup.sql
fi

# Sanitiza: quita CREATE DATABASE / USE; normaliza DEFINER
sed -E '/^[[:space:]]*CREATE[[:space:]]+DATABASE\b/Id; /^[[:space:]]*USE[[:space:]]+`?[^`]+`?;/Id' /tmp/_fidbaq_backup.sql \
| sed -E 's/DEFINER[ ]*=[ ]*`[^`]+`@`[^`]+`/DEFINER=CURRENT_USER/g; s/DEFINER[ ]*=[ ]*[^ ]+@[^ ]+/DEFINER=CURRENT_USER/g' \
> /tmp/_fidbaq_backup_sanitized.sql

# Preambulo / Cierre para import segura
cat > /tmp/_header.sql <<'SQL'
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='';
SET NAMES utf8mb4;
START TRANSACTION;
SQL

cat > /tmp/_footer.sql <<'SQL'
COMMIT;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SQL

cat /tmp/_header.sql /tmp/_fidbaq_backup_sanitized.sql /tmp/_footer.sql > /tmp/_final_import.sql

echo ">> Dropear y recrear la BD '${DB_DATABASE}'..."
mysql --defaults-extra-file="$CRED_FILE" -e "DROP DATABASE IF EXISTS \`$DB_DATABASE\`; CREATE DATABASE \`$DB_DATABASE\` CHARACTER SET ${CHARSET} COLLATE ${COLLATE};"
echo "OK."

echo ">> Importando en '${DB_DATABASE}'..."
mysql --defaults-extra-file="$CRED_FILE" --init-command="SET SESSION sql_mode='';" "$DB_DATABASE" < /tmp/_final_import.sql
echo "Importación completada."

echo ">> Verificación (número de tablas):"
mysql --defaults-extra-file="$CRED_FILE" -N -e "SELECT COUNT(*) AS tablas FROM information_schema.tables WHERE table_schema='${DB_DATABASE}';"

echo ">> Backup de seguridad: ${DUMP_FILE}"
echo "Listo."