db_import_cli.sh

#!/usr/bin/env bash
# ==============================================================================
# Database Import Studio — CLI Mode
# Cross-platform: Linux + Windows (Git Bash / WSL)
# No external tools required beyond bash and mysql/mariadb.
# ==============================================================================
set -euo pipefail

# ── ANSI colors (disabled if not a terminal) ───────────────────────────────────
if [[ -t 1 ]]; then
  BOLD="\e[1m"; DIM="\e[2m"; RESET="\e[0m"
  RED="\e[91m"; GREEN="\e[92m"; YELLOW="\e[93m"
  BLUE="\e[94m"; CYAN="\e[96m"; WHITE="\e[97m"
else
  BOLD=""; DIM=""; RESET=""; RED=""; GREEN=""; YELLOW=""; BLUE=""; CYAN=""; WHITE=""
fi

banner() {
  printf "\n${BOLD}${BLUE}"
  printf "  ╔══════════════════════════════════════════════════╗\n"
  printf "  ║      Database Import Studio  — CLI Mode          ║\n"
  printf "  ║   MySQL / MariaDB cross-platform importer        ║\n"
  printf "  ╚══════════════════════════════════════════════════╝\n"
  printf "${RESET}\n"
}

info()    { printf "  ${CYAN}ℹ${RESET}  $*\n"; }
ok()      { printf "  ${GREEN}✓${RESET}  $*\n"; }
warn()    { printf "  ${YELLOW}⚠${RESET}  $*\n"; }
error()   { printf "  ${RED}✕${RESET}  $*\n" >&2; }
section() { printf "\n${BOLD}${WHITE}  ─── $* ───${RESET}\n"; }

prompt_val() {
  # prompt_val "Label" "default" [secret]
  local label="$1" default="$2" secret="${3:-}"
  local val
  if [[ -n "$secret" ]]; then
    printf "  ${DIM}%-22s${RESET}" "$label:"
    read -rsp "" val; echo ""
  else
    printf "  ${DIM}%-22s${RESET}[${default}] " "$label:"
    read -r val
  fi
  echo "${val:-$default}"
}

# ── Detect mysql / mariadb executable ─────────────────────────────────────────
find_mysql() {
  local candidates=(
    "/usr/bin/mysql" "/usr/local/bin/mysql"
    "/usr/bin/mariadb" "/usr/local/bin/mariadb"
    "mysql" "mariadb"
  )
  # Windows Git Bash common paths
  if [[ "$OSTYPE" == "msys" || "$OSTYPE" == "cygwin" ]]; then
    candidates+=(
      "/c/wamp64/bin/mariadb/mariadb11.5.2/bin/mysql.exe"
      "/c/wamp64/bin/mariadb/mariadb11.5.2/bin/mariadb.exe"
      "/c/xampp/mysql/bin/mysql.exe"
      "/c/mysql/bin/mysql.exe"
    )
  fi
  for c in "${candidates[@]}"; do
    if command -v "$c" &>/dev/null || [[ -x "$c" ]]; then
      echo "$c"; return 0
    fi
  done
  echo ""
}

# ── Log setup ─────────────────────────────────────────────────────────────────
if [[ "$OSTYPE" == "msys" || "$OSTYPE" == "cygwin" ]]; then
  LOG_DIR="${APPDATA:-$HOME}/DatabaseImportStudio/logs"
else
  LOG_DIR="$HOME/.local/share/DatabaseImportStudio/logs"
fi
mkdir -p "$LOG_DIR"
LOG_FILE="$LOG_DIR/import-$(date +%Y%m%d-%H%M%S).log"

log() {
  local line="[$(date +%H:%M:%S)] $*"
  echo "$line" >> "$LOG_FILE"
  echo "$line"
}

# ── Parse CLI args (all optional — prompts fill missing values) ────────────────
MYSQL_EXE=""
SQL_FILE=""
DATABASE=""
USER="root"
PASSWORD=""
HOST="127.0.0.1"
PORT="3306"
CREATE_DB="true"
RECREATE_DB="false"
DISABLE_FK="true"
FORCE="true"
SPEED="true"
YES="false"

while [[ $# -gt 0 ]]; do
  case "$1" in
    -h|--host)       HOST="$2";       shift 2;;
    -P|--port)       PORT="$2";       shift 2;;
    -u|--user)       USER="$2";       shift 2;;
    -p|--password)   PASSWORD="$2";   shift 2;;
    -d|--database)   DATABASE="$2";   shift 2;;
    -f|--file)       SQL_FILE="$2";   shift 2;;
    --mysql-bin|--mysql-exe) MYSQL_EXE="$2"; shift 2;;
    --no-create-db)  CREATE_DB="false";   shift;;
    --recreate-db)   RECREATE_DB="true";  shift;;
    --no-fk)         DISABLE_FK="false";  shift;;
    --no-force)      FORCE="false";       shift;;
    --no-speed)      SPEED="false";       shift;;
    -y|--yes)        YES="true";          shift;;
    --help)
      echo "Usage: $0 [OPTIONS]"
      echo ""
      echo "Options:"
      echo "  -h, --host         MySQL host          (default: 127.0.0.1)"
      echo "  -P, --port         MySQL port          (default: 3306)"
      echo "  -u, --user         MySQL username      (default: root)"
      echo "  -p, --password     MySQL password"
      echo "  -d, --database     Target database name"
      echo "  -f, --file         Path to SQL dump file"
      echo "  --mysql-exe        Path to mysql/mariadb executable"
      echo "  --recreate-db      Drop & recreate DB before import"
      echo "  --no-create-db     Don't auto-create database"
      echo "  --no-fk            Don't disable FK checks"
      echo "  --no-force         Don't use --force (stop on errors)"
      echo "  --no-speed         Don't apply large-dump optimizations"
      echo "  -y, --yes          Skip confirmation prompts"
      echo ""
      exit 0
      ;;
    *) warn "Unknown option: $1"; shift;;
  esac
done

# ── Start ──────────────────────────────────────────────────────────────────────
banner

# ── Interactive prompts for missing values ─────────────────────────────────────
section "Connection"

if [[ -z "$MYSQL_EXE" ]]; then
  AUTO=$(find_mysql)
  if [[ -n "$AUTO" ]]; then
    info "Detected: $AUTO"
  fi
  MYSQL_EXE=$(prompt_val "MySQL/MariaDB exe" "${AUTO:-mysql}")
fi

MYSQL_EXE="${MYSQL_EXE//\\//}"  # normalize backslashes on Git Bash

if ! command -v "$MYSQL_EXE" &>/dev/null && [[ ! -x "$MYSQL_EXE" ]]; then
  error "Executable not found: $MYSQL_EXE"
  exit 1
fi

[[ -z "$DATABASE" ]] && DATABASE=$(prompt_val "Database name" "")
[[ -z "$DATABASE" ]] && { error "Database name is required."; exit 1; }
[[ -z "$SQL_FILE" ]] && SQL_FILE=$(prompt_val "SQL dump file" "")

SQL_FILE="${SQL_FILE//\\//}"
[[ -z "$SQL_FILE" ]]   && { error "SQL dump file is required."; exit 1; }
[[ ! -f "$SQL_FILE" ]] && { error "File not found: $SQL_FILE"; exit 1; }

HOST=$(prompt_val "Host" "$HOST")
PORT=$(prompt_val "Port" "$PORT")
USER=$(prompt_val "Username" "$USER")
[[ -z "$PASSWORD" ]] && PASSWORD=$(prompt_val "Password" "" secret)

section "Options"
printf "  ${DIM}[Y/n]${RESET} to accept default\n\n"

yn_prompt() {
  local label="$1" default="$2"
  local icon; [[ "$default" == "true" ]] && icon="[Y/n]" || icon="[y/N]"
  printf "  ${DIM}%-40s${RESET}${icon} " "$label:"
  local ans; read -r ans
  case "${ans,,}" in
    y|yes) echo "true";;
    n|no)  echo "false";;
    *)     echo "$default";;
  esac
}

if [[ "$YES" != "true" ]]; then
  CREATE_DB=$(yn_prompt   "Create database if missing"      "$CREATE_DB")
  RECREATE_DB=$(yn_prompt "Drop & recreate DB (clean import)" "$RECREATE_DB")
  DISABLE_FK=$(yn_prompt  "Disable FK / unique checks"      "$DISABLE_FK")
  FORCE=$(yn_prompt       "Continue on SQL errors (--force)" "$FORCE")
  SPEED=$(yn_prompt       "Large-dump optimizations"        "$SPEED")
fi

# ── Confirm clean import ───────────────────────────────────────────────────────
if [[ "$RECREATE_DB" == "true" && "$YES" != "true" ]]; then
  printf "\n  ${RED}${BOLD}WARNING:${RESET}${RED} Database '${DATABASE}' will be DROPPED and recreated.${RESET}\n"
  printf "  All existing data will be deleted. Continue? [y/N] "
  read -r ans
  [[ "${ans,,}" != "y" && "${ans,,}" != "yes" ]] && { info "Aborted."; exit 0; }
fi

# ── Build base mysql args ──────────────────────────────────────────────────────
BASE_ARGS=(
  "--host=$HOST"
  "--port=$PORT"
  "--user=$USER"
  "--default-character-set=utf8mb4"
  "--connect-timeout=15"
)
[[ -n "$PASSWORD" ]] && BASE_ARGS+=("--password=$PASSWORD")

# ── Log file start ─────────────────────────────────────────────────────────────
section "Import"
printf "\n"
info "Log file: $LOG_FILE"
log "=== Database Import Studio ==="
log "Host:     $HOST:$PORT"
log "Database: $DATABASE"
log "SQL file: $SQL_FILE ($(du -h "$SQL_FILE" 2>/dev/null | cut -f1 || echo '?') )"
log "User:     $USER"

# ── Test connection ────────────────────────────────────────────────────────────
info "Testing MySQL connection…"
if ! "$MYSQL_EXE" "${BASE_ARGS[@]}" --execute="SELECT 1;" &>/dev/null; then
  error "Cannot connect to MySQL at $HOST:$PORT as '$USER'."
  error "Check your credentials and that MySQL is running."
  exit 1
fi
ok "Connection OK"
log "Connection successful."

# ── Create / recreate database ─────────────────────────────────────────────────
if [[ "$RECREATE_DB" == "true" ]]; then
  info "Dropping and recreating database '${DATABASE}'…"
  log "Dropping and recreating database '${DATABASE}'."
  "$MYSQL_EXE" "${BASE_ARGS[@]}" \
    --execute="DROP DATABASE IF EXISTS \`${DATABASE}\`; CREATE DATABASE \`${DATABASE}\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;" \
    2>>"$LOG_FILE" || { error "Failed to recreate database."; exit 1; }
  ok "Database recreated."
elif [[ "$CREATE_DB" == "true" ]]; then
  info "Creating database '${DATABASE}' if missing…"
  log "Creating database '${DATABASE}' if missing."
  "$MYSQL_EXE" "${BASE_ARGS[@]}" \
    --execute="CREATE DATABASE IF NOT EXISTS \`${DATABASE}\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;" \
    2>>"$LOG_FILE" || { error "Failed to create database."; exit 1; }
  ok "Database ready."
fi

# ── Count tables in dump ────────────────────────────────────────────────────────
EXPECTED_TABLES=$(grep -ci 'CREATE TABLE' "$SQL_FILE" 2>/dev/null || echo "?")
info "Detected ~${EXPECTED_TABLES} CREATE TABLE statements."
log "Expected tables: ${EXPECTED_TABLES}"

# ── Build import args ──────────────────────────────────────────────────────────
IMPORT_ARGS=("${BASE_ARGS[@]}")
[[ "$FORCE" == "true" ]]     && IMPORT_ARGS+=("--force")
IMPORT_ARGS+=("--show-warnings")
if [[ "$SPEED" == "true" ]]; then
  IMPORT_ARGS+=("--max_allowed_packet=1073741824")
  IMPORT_ARGS+=("--net_buffer_length=1048576")
fi
IMPORT_ARGS+=("--database=$DATABASE")

# ── File size for progress ─────────────────────────────────────────────────────
FILE_SIZE=$(stat -c%s "$SQL_FILE" 2>/dev/null || stat -f%z "$SQL_FILE" 2>/dev/null || wc -c < "$SQL_FILE")

# ── Progress bar ───────────────────────────────────────────────────────────────
draw_progress() {
  # draw_progress percent bytes_sent file_size elapsed
  local pct="$1" sent="$2" total="$3" elapsed="$4"
  local bar_w=40
  local filled=$(( pct * bar_w / 100 ))
  local empty=$(( bar_w - filled ))
  local bar=""
  for ((i=0;i<filled;i++)); do bar="${bar}█"; done
  for ((i=0;i<empty;i++));  do bar="${bar}░"; done
  local sent_mb total_mb
  sent_mb=$(awk "BEGIN{printf \"%.1f\", $sent/1048576}")
  total_mb=$(awk "BEGIN{printf \"%.1f\", $total/1048576}")
  printf "\r  ${CYAN}[${bar}]${RESET} ${BOLD}%3d%%${RESET}  ${sent_mb}/${total_mb} MB  ⏱ %s" \
    "$pct" "$elapsed"
}

# ── Start import ───────────────────────────────────────────────────────────────
START_TS=$(date +%s)
info "Streaming SQL file to MySQL…"
log "Import started."

{
  if [[ "$DISABLE_FK" == "true" ]]; then
    printf "SET SESSION FOREIGN_KEY_CHECKS=0; SET SESSION UNIQUE_CHECKS=0; SET SESSION AUTOCOMMIT=0;\n"
    log "FK and unique checks disabled."
  fi

  # Stream file in chunks and report progress
  SENT=0
  LAST_PCT=-1
  BAD_MODE="NO_AUTO_CREATE_USER"
  BLANK_MODE="                   "
  while IFS= read -r -d '' -n $((512*1024)) chunk 2>/dev/null || [[ -n "$chunk" ]]; do
    # Replace deprecated NO_AUTO_CREATE_USER with spaces for MySQL 8.0 compatibility
    if [[ "$chunk" == *"$BAD_MODE"* ]]; then
      chunk="${chunk//$BAD_MODE/$BLANK_MODE}"
    fi
    printf '%s' "$chunk"
    SENT=$(( SENT + ${#chunk} ))
    PCT=$(( FILE_SIZE > 0 ? SENT * 100 / FILE_SIZE : 0 ))
    [[ $PCT -gt 99 ]] && PCT=99
    if [[ $PCT -ne $LAST_PCT ]]; then
      ELAPSED=$(( $(date +%s) - START_TS ))
      ELAPSED_FMT=$(printf "%02d:%02d" $((ELAPSED/60)) $((ELAPSED%60)))
      draw_progress "$PCT" "$SENT" "$FILE_SIZE" "$ELAPSED_FMT" >&2
      LAST_PCT=$PCT
    fi
  done < "$SQL_FILE"

  if [[ "$DISABLE_FK" == "true" ]]; then
    printf "\nCOMMIT; SET SESSION FOREIGN_KEY_CHECKS=1; SET SESSION UNIQUE_CHECKS=1;\n"
  fi

} | "$MYSQL_EXE" "${IMPORT_ARGS[@]}" 2>>"$LOG_FILE"

EXIT_CODE=$?

TOTAL_ELAPSED=$(( $(date +%s) - START_TS ))
ELAPSED_FMT=$(printf "%02d:%02d" $(( TOTAL_ELAPSED/60 )) $(( TOTAL_ELAPSED%60 )))
printf "\n\n"

if [[ $EXIT_CODE -eq 0 ]]; then
  ok "Import completed in ${ELAPSED_FMT}."
  log "Import completed successfully in ${ELAPSED_FMT}."
else
  warn "Import finished with exit code ${EXIT_CODE}. Check log for warnings/errors."
  log "Import finished with exit code ${EXIT_CODE} in ${ELAPSED_FMT}."
fi

printf "  ${DIM}Log saved to: ${LOG_FILE}${RESET}\n\n"
exit $EXIT_CODE