#!/usr/bin/env bash
# =============================================================================
#  redmine-backup.sh — Sauvegarde & Restauration complète de Redmine
#  Version : 2.0.0
#  Date    : 2026-02-26
#  Usage   : sudo ./redmine-backup.sh [backup|restore|list|help]
# =============================================================================

set -euo pipefail
IFS=$'\n\t'

# ── Couleurs ──────────────────────────────────────────────────────────────────
RED='\033[0;31m';  GREEN='\033[0;32m'; YELLOW='\033[1;33m'
BLUE='\033[0;34m'; CYAN='\033[0;36m';  BOLD='\033[1m'; RESET='\033[0m'

# ── Répertoire du script ──────────────────────────────────────────────────────
SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
ENV_FILE="${SCRIPT_DIR}/.env"
LOG_FILE="/var/log/redmine-backup.log"
TIMESTAMP="$(date +%Y%m%d_%H%M%S)"

# =============================================================================
#  FONCTIONS UTILITAIRES
# =============================================================================

log() {
    local level="$1"; shift
    local msg="$*"
    local ts; ts="$(date '+%Y-%m-%d %H:%M:%S')"
    echo -e "${ts} [${level}] ${msg}" | tee -a "${LOG_FILE}" >/dev/null
    case "${level}" in
        INFO)    echo -e "${GREEN}[INFO]${RESET}  ${msg}" ;;
        WARN)    echo -e "${YELLOW}[WARN]${RESET}  ${msg}" ;;
        ERROR)   echo -e "${RED}[ERROR]${RESET} ${msg}" ;;
        STEP)    echo -e "${CYAN}[STEP]${RESET}  ${BOLD}${msg}${RESET}" ;;
        SUCCESS) echo -e "${GREEN}[OK]${RESET}    ${msg}" ;;
    esac
}

die() {
    log ERROR "$*"
    exit 1
}

require_root() {
    [[ "${EUID}" -eq 0 ]] || die "Ce script doit être exécuté en tant que root (sudo)."
}

require_command() {
    command -v "$1" &>/dev/null || die "Commande requise introuvable : $1"
}

confirm() {
    local prompt="${1:-Continuer ?} [o/N] "
    read -rp "$(echo -e "${YELLOW}${prompt}${RESET}")" answer
    [[ "${answer,,}" =~ ^(o|oui|y|yes)$ ]]
}

spinner() {
    local pid=$1; local msg="${2:-Traitement en cours...}"
    local spin='⠋⠙⠹⠸⠼⠴⠦⠧⠇⠏'
    local i=0
    while kill -0 "${pid}" 2>/dev/null; do
        printf "\r${CYAN}%s${RESET} %s" "${spin:$((i % ${#spin})):1}" "${msg}"
        ((i++)); sleep 0.1
    done
    printf "\r%-60s\r" " "
}

hr() { echo -e "${BLUE}$(printf '─%.0s' {1..70})${RESET}"; }

# =============================================================================
#  CHARGEMENT DE LA CONFIGURATION
# =============================================================================

load_env() {
    [[ -f "${ENV_FILE}" ]] || die "Fichier .env introuvable : ${ENV_FILE}"

    # Valeurs par défaut
    REDMINE_ROOT=""
    BACKUP_DIR="/opt/Backups"
    RAILS_ENV="production"
    DB_CONFIG=""
    DB_TYPE=""
    KEEP_BACKUPS=7
    INCLUDE_LOG="false"

    # Chargement sécurisé (ignore commentaires et lignes vides)
    while IFS='=' read -r key value; do
        [[ "${key}" =~ ^[[:space:]]*# ]] && continue
        [[ -z "${key// /}" ]] && continue
        key="${key// /}"
        value="${value%%#*}"          # supprime commentaires inline
        value="${value%"${value##*[![:space:]]}"}"  # trim trailing spaces
        value="${value#\"}" ; value="${value%\"}"   # supprime guillemets doubles
        value="${value#\'}" ; value="${value%\'}"   # supprime guillemets simples
        export "${key}=${value}" 2>/dev/null || true
    done < "${ENV_FILE}"

    # Vérifications obligatoires
    [[ -n "${REDMINE_ROOT}" ]] || die "REDMINE_ROOT n'est pas défini dans .env"
    [[ -d "${REDMINE_ROOT}" ]] || die "REDMINE_ROOT introuvable : ${REDMINE_ROOT}"

    mkdir -p "${BACKUP_DIR}" || die "Impossible de créer BACKUP_DIR : ${BACKUP_DIR}"
    touch "${LOG_FILE}" 2>/dev/null || LOG_FILE="/tmp/redmine-backup.log"
}

# =============================================================================
#  DÉTECTION DE LA BASE DE DONNÉES
# =============================================================================

detect_db() {
    local db_config="${DB_CONFIG:-${REDMINE_ROOT}/config/database.yml}"
    [[ -f "${db_config}" ]] || die "Fichier database.yml introuvable : ${db_config}"

    # Extraction de la section correspondant à RAILS_ENV
    local section
    section=$(awk "/^${RAILS_ENV}:/,/^[a-z]/" "${db_config}" | head -n -1)

    DB_ADAPTER="${DB_TYPE}"
    if [[ -z "${DB_ADAPTER}" ]]; then
        DB_ADAPTER=$(echo "${section}" | grep -E '^\s+adapter:' | awk '{print $2}' | tr -d '"' | head -1)
    fi
    DB_NAME=$(echo "${section}"    | grep -E '^\s+database:' | awk '{print $2}' | tr -d '"' | head -1)
    DB_HOST=$(echo "${section}"    | grep -E '^\s+host:'     | awk '{print $2}' | tr -d '"' | head -1)
    DB_PORT=$(echo "${section}"    | grep -E '^\s+port:'     | awk '{print $2}' | tr -d '"' | head -1)
    DB_USER=$(echo "${section}"    | grep -E '^\s+username:' | awk '{print $2}' | tr -d '"' | head -1)
    DB_PASS=$(echo "${section}"    | grep -E '^\s+password:' | awk '{print $2}' | tr -d '"' | head -1)

    DB_HOST="${DB_HOST:-localhost}"
    DB_PORT="${DB_PORT:-}"

    [[ -n "${DB_ADAPTER}" ]] || die "Impossible de détecter l'adaptateur DB dans database.yml"
    [[ -n "${DB_NAME}" ]]    || die "Impossible de détecter le nom de la base de données"
}

# =============================================================================
#  FONCTIONS DE SAUVEGARDE DE LA BASE DE DONNÉES
# =============================================================================

backup_db_postgresql() {
    log STEP "Sauvegarde PostgreSQL → ${DB_NAME}"
    local dump_file="${TMP_DIR}/database.sql"
    local pg_opts=(-U "${DB_USER}" -h "${DB_HOST}")
    [[ -n "${DB_PORT}" ]] && pg_opts+=(-p "${DB_PORT}")

    if [[ -n "${DB_PASS}" ]]; then
        PGPASSWORD="${DB_PASS}" pg_dump "${pg_opts[@]}" \
            --no-owner --no-acl --format=custom \
            -f "${dump_file}.custom" "${DB_NAME}" &
    else
        pg_dump "${pg_opts[@]}" --no-owner --no-acl --format=custom \
            -f "${dump_file}.custom" "${DB_NAME}" &
    fi
    spinner $! "Dump PostgreSQL en cours..."
    wait $!
    mv "${dump_file}.custom" "${dump_file}"
    log SUCCESS "Dump PostgreSQL terminé"
}

backup_db_mysql2() {
    log STEP "Sauvegarde MySQL/MariaDB → ${DB_NAME}"
    local dump_file="${TMP_DIR}/database.sql"
    local my_opts=(--single-transaction --routines --triggers --events)
    [[ -n "${DB_HOST}" ]] && my_opts+=(-h "${DB_HOST}")
    [[ -n "${DB_PORT}" ]] && my_opts+=(-P "${DB_PORT}")
    [[ -n "${DB_USER}" ]] && my_opts+=(-u "${DB_USER}")

    if [[ -n "${DB_PASS}" ]]; then
        MYSQL_PWD="${DB_PASS}" mysqldump "${my_opts[@]}" "${DB_NAME}" > "${dump_file}" &
    else
        mysqldump "${my_opts[@]}" "${DB_NAME}" > "${dump_file}" &
    fi
    spinner $! "Dump MySQL en cours..."
    wait $!
    log SUCCESS "Dump MySQL terminé"
}

backup_db_sqlite3() {
    log STEP "Sauvegarde SQLite3 → ${DB_NAME}"
    local dump_file="${TMP_DIR}/database.sql"
    local sqlite_path="${DB_NAME}"
    # Si chemin relatif, le résoudre depuis REDMINE_ROOT
    [[ "${sqlite_path}" != /* ]] && sqlite_path="${REDMINE_ROOT}/${sqlite_path}"
    [[ -f "${sqlite_path}" ]] || die "Fichier SQLite introuvable : ${sqlite_path}"
    sqlite3 "${sqlite_path}" .dump > "${dump_file}" &
    spinner $! "Dump SQLite3 en cours..."
    wait $!
    log SUCCESS "Dump SQLite3 terminé"
}

# =============================================================================
#  FONCTIONS DE RESTAURATION DE LA BASE DE DONNÉES
# =============================================================================

restore_db_postgresql() {
    log STEP "Restauration PostgreSQL → ${DB_NAME}"
    local dump_file="${TMP_DIR}/database.sql"
    local pg_opts=(-U "${DB_USER}" -h "${DB_HOST}")
    [[ -n "${DB_PORT}" ]] && pg_opts+=(-p "${DB_PORT}")

    # Recrée la base si nécessaire
    if [[ -n "${DB_PASS}" ]]; then
        PGPASSWORD="${DB_PASS}" psql "${pg_opts[@]}" -d postgres \
            -c "DROP DATABASE IF EXISTS \"${DB_NAME}\";" 2>/dev/null || true
        PGPASSWORD="${DB_PASS}" psql "${pg_opts[@]}" -d postgres \
            -c "CREATE DATABASE \"${DB_NAME}\" OWNER \"${DB_USER}\";"
        PGPASSWORD="${DB_PASS}" pg_restore "${pg_opts[@]}" \
            --no-owner --no-acl -d "${DB_NAME}" "${dump_file}" &
    else
        psql "${pg_opts[@]}" -d postgres \
            -c "DROP DATABASE IF EXISTS \"${DB_NAME}\";" 2>/dev/null || true
        psql "${pg_opts[@]}" -d postgres \
            -c "CREATE DATABASE \"${DB_NAME}\" OWNER \"${DB_USER}\";"
        pg_restore "${pg_opts[@]}" --no-owner --no-acl \
            -d "${DB_NAME}" "${dump_file}" &
    fi
    spinner $! "Restauration PostgreSQL en cours..."
    wait $! || log WARN "pg_restore a retourné des avertissements (normal si objets existants)"
    log SUCCESS "Restauration PostgreSQL terminée"
}

restore_db_mysql2() {
    log STEP "Restauration MySQL/MariaDB → ${DB_NAME}"
    local dump_file="${TMP_DIR}/database.sql"
    local my_opts=()
    [[ -n "${DB_HOST}" ]] && my_opts+=(-h "${DB_HOST}")
    [[ -n "${DB_PORT}" ]] && my_opts+=(-P "${DB_PORT}")
    [[ -n "${DB_USER}" ]] && my_opts+=(-u "${DB_USER}")

    if [[ -n "${DB_PASS}" ]]; then
        MYSQL_PWD="${DB_PASS}" mysql "${my_opts[@]}" \
            -e "DROP DATABASE IF EXISTS \`${DB_NAME}\`; CREATE DATABASE \`${DB_NAME}\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
        MYSQL_PWD="${DB_PASS}" mysql "${my_opts[@]}" "${DB_NAME}" < "${dump_file}" &
    else
        mysql "${my_opts[@]}" \
            -e "DROP DATABASE IF EXISTS \`${DB_NAME}\`; CREATE DATABASE \`${DB_NAME}\` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
        mysql "${my_opts[@]}" "${DB_NAME}" < "${dump_file}" &
    fi
    spinner $! "Restauration MySQL en cours..."
    wait $!
    log SUCCESS "Restauration MySQL terminée"
}

restore_db_sqlite3() {
    log STEP "Restauration SQLite3 → ${DB_NAME}"
    local dump_file="${TMP_DIR}/database.sql"
    local sqlite_path="${DB_NAME}"
    [[ "${sqlite_path}" != /* ]] && sqlite_path="${REDMINE_ROOT}/${sqlite_path}"

    [[ -f "${sqlite_path}" ]] && cp "${sqlite_path}" "${sqlite_path}.pre_restore_bak"
    sqlite3 "${sqlite_path}" < "${dump_file}" &
    spinner $! "Restauration SQLite3 en cours..."
    wait $!
    log SUCCESS "Restauration SQLite3 terminée"
}

# =============================================================================
#  SAUVEGARDE PRINCIPALE
# =============================================================================

do_backup() {
    hr
    echo -e "${BOLD}${CYAN}  ██████╗  █████╗  ██████╗██╗  ██╗██╗   ██╗██████╗ ${RESET}"
    echo -e "${BOLD}${CYAN}  ██╔══██╗██╔══██╗██╔════╝██║ ██╔╝██║   ██║██╔══██╗${RESET}"
    echo -e "${BOLD}${CYAN}  ██████╔╝███████║██║     █████╔╝ ██║   ██║██████╔╝${RESET}"
    echo -e "${BOLD}${CYAN}  ██╔══██╗██╔══██║██║     ██╔═██╗ ██║   ██║██╔═══╝ ${RESET}"
    echo -e "${BOLD}${CYAN}  ██████╔╝██║  ██║╚██████╗██║  ██╗╚██████╔╝██║     ${RESET}"
    echo -e "${BOLD}${CYAN}  ╚═════╝ ╚═╝  ╚═╝ ╚═════╝╚═╝  ╚═╝ ╚═════╝ ╚═╝     ${RESET}"
    hr
    log INFO "Démarrage de la sauvegarde Redmine — ${TIMESTAMP}"
    log INFO "Source      : ${REDMINE_ROOT}"
    log INFO "Destination : ${BACKUP_DIR}"
    log INFO "Environnement Rails : ${RAILS_ENV}"

    detect_db
    log INFO "Adaptateur DB détecté : ${DB_ADAPTER} / Base : ${DB_NAME}"

    # Dossier temporaire
    TMP_DIR="$(mktemp -d /tmp/redmine_backup_XXXXXX)"
    trap 'rm -rf "${TMP_DIR}"' EXIT

    ARCHIVE_NAME="redmine_backup_${TIMESTAMP}.tar.gz"
    ARCHIVE_PATH="${BACKUP_DIR}/${ARCHIVE_NAME}"

    # ── 1. Dump de la base de données ─────────────────────────────────────────
    case "${DB_ADAPTER}" in
        postgresql) backup_db_postgresql ;;
        mysql2|mysql) backup_db_mysql2  ;;
        sqlite3)    backup_db_sqlite3   ;;
        *) die "Adaptateur DB non supporté : ${DB_ADAPTER}" ;;
    esac

    # ── 2. Informations de configuration ──────────────────────────────────────
    log STEP "Collecte des informations système"
    {
        echo "=== REDMINE BACKUP MANIFEST ==="
        echo "Timestamp     : ${TIMESTAMP}"
        echo "Hostname      : $(hostname -f)"
        echo "REDMINE_ROOT  : ${REDMINE_ROOT}"
        echo "RAILS_ENV     : ${RAILS_ENV}"
        echo "DB_ADAPTER    : ${DB_ADAPTER}"
        echo "DB_NAME       : ${DB_NAME}"
        echo "DB_HOST       : ${DB_HOST}"
        echo "DB_USER       : ${DB_USER}"
        echo ""
        echo "=== VERSION REDMINE ==="
        [[ -f "${REDMINE_ROOT}/Gemfile.lock" ]] && \
            grep -A1 "^GEM$" "${REDMINE_ROOT}/Gemfile.lock" | head -5 || echo "N/A"
        [[ -f "${REDMINE_ROOT}/app/models/setting.rb" ]] && \
            grep -E "VERSION|REVISION" "${REDMINE_ROOT}/app/models/setting.rb" 2>/dev/null | head -3 || true
        [[ -f "${REDMINE_ROOT}/lib/redmine/version.rb" ]] && \
            grep -E "MAJOR|MINOR|TINY|VERSION" "${REDMINE_ROOT}/lib/redmine/version.rb" || true
        echo ""
        echo "=== PLUGINS INSTALLÉS ==="
        if [[ -d "${REDMINE_ROOT}/plugins" ]]; then
            for d in "${REDMINE_ROOT}/plugins"/*/; do
                local pname; pname=$(basename "${d}")
                [[ "${pname}" == "*" ]] && continue
                local pver="N/A"
                [[ -f "${d}/init.rb" ]] && pver=$(grep -oP "version\s*[=:>]+\s*['\"]?\K[0-9a-zA-Z._-]+" "${d}/init.rb" | head -1 || echo "N/A")
                echo "  - ${pname} (${pver})"
            done
        else
            echo "  Aucun plugin trouvé"
        fi
        echo ""
        echo "=== GEMS (Gemfile.lock) ==="
        [[ -f "${REDMINE_ROOT}/Gemfile.lock" ]] && \
            grep "^    [a-z]" "${REDMINE_ROOT}/Gemfile.lock" | head -50 || echo "N/A"
        echo ""
        echo "=== VARIABLES D'ENVIRONNEMENT SYSTÈME ==="
        ruby --version 2>/dev/null || echo "ruby: N/A"
        bundler --version 2>/dev/null || echo "bundler: N/A"
    } > "${TMP_DIR}/manifest.txt"
    log SUCCESS "Manifest créé"

    # ── 3. Copie des fichiers de configuration ─────────────────────────────────
    log STEP "Sauvegarde des fichiers de configuration"
    mkdir -p "${TMP_DIR}/config"
    for cfg in database.yml configuration.yml email.yml secrets.yml; do
        [[ -f "${REDMINE_ROOT}/config/${cfg}" ]] && \
            cp "${REDMINE_ROOT}/config/${cfg}" "${TMP_DIR}/config/" && \
            log INFO "  config/${cfg} inclus"
    done
    # .env du script lui-même
    [[ -f "${ENV_FILE}" ]] && cp "${ENV_FILE}" "${TMP_DIR}/config/.env.backup"

    # ── 4. Création de l'archive complète ────────────────────────────────────
    log STEP "Création de l'archive TAR.GZ"

    local tar_excludes=(
        "--exclude=${REDMINE_ROOT}/tmp"
        "--exclude=${REDMINE_ROOT}/.git"
        "--exclude=${REDMINE_ROOT}/log/*.log"
    )
    [[ "${INCLUDE_LOG}" != "true" ]] && tar_excludes+=("--exclude=${REDMINE_ROOT}/log")

    local tar_sources=("${REDMINE_ROOT}")
    # Inclure fichiers temporaires de dump
    local tmp_archive="${TMP_DIR}/files_backup.tar.gz"

    (
        tar "${tar_excludes[@]}" \
            --exclude="${REDMINE_ROOT}/tmp/cache" \
            -czf "${tmp_archive}" \
            -C "$(dirname "${REDMINE_ROOT}")" \
            "$(basename "${REDMINE_ROOT}")" 2>/dev/null
    ) &
    spinner $! "Archivage des fichiers Redmine..."
    wait $!
    log SUCCESS "Archive des fichiers créée"

    # ── 5. Assemblage de l'archive finale ─────────────────────────────────────
    log STEP "Assemblage de l'archive finale"
    (
        tar -czf "${ARCHIVE_PATH}" \
            -C "${TMP_DIR}" \
            database.sql \
            manifest.txt \
            config \
            -C "${TMP_DIR}" \
            files_backup.tar.gz 2>/dev/null
    ) &
    spinner $! "Assemblage final..."
    wait $!

    local size; size=$(du -sh "${ARCHIVE_PATH}" | cut -f1)
    log SUCCESS "Archive créée : ${ARCHIVE_PATH} (${size})"

    # ── 6. Rotation des sauvegardes ───────────────────────────────────────────
    log STEP "Rotation des anciennes sauvegardes (conservation : ${KEEP_BACKUPS})"
    local count
    count=$(find "${BACKUP_DIR}" -maxdepth 1 -name "redmine_backup_*.tar.gz" | wc -l)
    if [[ "${count}" -gt "${KEEP_BACKUPS}" ]]; then
        local to_delete=$(( count - KEEP_BACKUPS ))
        find "${BACKUP_DIR}" -maxdepth 1 -name "redmine_backup_*.tar.gz" \
            -printf '%T+ %p\n' | sort | head -n "${to_delete}" | \
            awk '{print $2}' | while read -r old; do
                rm -f "${old}"
                log INFO "  Supprimé : $(basename "${old}")"
            done
    else
        log INFO "  Aucune rotation nécessaire (${count}/${KEEP_BACKUPS})"
    fi

    hr
    echo -e "${GREEN}${BOLD}  ✔  Sauvegarde terminée avec succès !${RESET}"
    echo -e "${GREEN}     Archive : ${ARCHIVE_PATH}${RESET}"
    echo -e "${GREEN}     Taille  : ${size}${RESET}"
    hr
}

# =============================================================================
#  RESTAURATION PRINCIPALE
# =============================================================================

list_backups() {
    local backups=()
    while IFS= read -r f; do
        backups+=("${f}")
    done < <(find "${BACKUP_DIR}" -maxdepth 1 -name "redmine_backup_*.tar.gz" | sort -r)
    echo "${backups[@]+"${backups[@]}"}"
}

do_restore() {
    hr
    echo -e "${BOLD}${YELLOW}  ██████╗ ███████╗███████╗████████╗ ██████╗ ██████╗ ███████╗${RESET}"
    echo -e "${BOLD}${YELLOW}  ██╔══██╗██╔════╝██╔════╝╚══██╔══╝██╔═══██╗██╔══██╗██╔════╝${RESET}"
    echo -e "${BOLD}${YELLOW}  ██████╔╝█████╗  ███████╗   ██║   ██║   ██║██████╔╝█████╗  ${RESET}"
    echo -e "${BOLD}${YELLOW}  ██╔══██╗██╔══╝  ╚════██║   ██║   ██║   ██║██╔══██╗██╔══╝  ${RESET}"
    echo -e "${BOLD}${YELLOW}  ██║  ██║███████╗███████║   ██║   ╚██████╔╝██║  ██║███████╗${RESET}"
    echo -e "${BOLD}${YELLOW}  ╚═╝  ╚═╝╚══════╝╚══════╝   ╚═╝    ╚═════╝ ╚═╝  ╚═╝╚══════╝${RESET}"
    hr

    # ── Sélection interactive de l'archive ────────────────────────────────────
    local backup_list
    mapfile -t backup_list < <(find "${BACKUP_DIR}" -maxdepth 1 -name "redmine_backup_*.tar.gz" | sort -r)

    if [[ ${#backup_list[@]} -eq 0 ]]; then
        die "Aucune archive de sauvegarde trouvée dans ${BACKUP_DIR}"
    fi

    echo -e "\n${BOLD}  Sauvegardes disponibles :${RESET}\n"
    local i=1
    for archive in "${backup_list[@]}"; do
        local fname; fname=$(basename "${archive}")
        local fsize; fsize=$(du -sh "${archive}" | cut -f1)
        local fdate; fdate=$(stat -c '%y' "${archive}" | cut -d'.' -f1)
        printf "  ${CYAN}[%2d]${RESET}  %-45s  ${YELLOW}%6s${RESET}  %s\n" \
               "${i}" "${fname}" "${fsize}" "${fdate}"
        i=$(( i + 1 ))
    done
    echo -e "  ${CYAN}[ 0]${RESET}  Annuler\n"
    hr

    local choice
    while true; do
        read -rp "$(echo -e "${BOLD}  Choisir une archive [0-$((i-1))] : ${RESET}")" choice
        [[ "${choice}" =~ ^[0-9]+$ ]] || continue
        [[ "${choice}" -eq 0 ]] && { log INFO "Restauration annulée."; return 0; }
        [[ "${choice}" -ge 1 && "${choice}" -le ${#backup_list[@]} ]] && break
        echo -e "${RED}  Choix invalide.${RESET}"
    done

    local selected="${backup_list[$((choice-1))]}"
    log INFO "Archive sélectionnée : $(basename "${selected}")"

    # ── Options de restauration ────────────────────────────────────────────────
    hr
    echo -e "\n${BOLD}  Options de restauration :${RESET}\n"
    echo -e "  ${CYAN}[1]${RESET}  Restauration complète (DB + fichiers + config)"
    echo -e "  ${CYAN}[2]${RESET}  Base de données uniquement"
    echo -e "  ${CYAN}[3]${RESET}  Fichiers uniquement (sans DB)"
    echo -e "  ${CYAN}[4]${RESET}  Configuration uniquement"
    echo -e "  ${CYAN}[0]${RESET}  Annuler\n"
    hr

    local mode
    while true; do
        read -rp "$(echo -e "${BOLD}  Mode de restauration [0-4] : ${RESET}")" mode
        [[ "${mode}" =~ ^[0-4]$ ]] && break
        echo -e "${RED}  Choix invalide.${RESET}"
    done
    [[ "${mode}" -eq 0 ]] && { log INFO "Restauration annulée."; return 0; }

    # ── Confirmation finale ────────────────────────────────────────────────────
    hr
    echo -e "\n${RED}${BOLD}  ⚠  ATTENTION : Cette opération va écraser les données existantes !${RESET}"
    echo -e "  Archive   : $(basename "${selected}")"
    echo -e "  Cible     : ${REDMINE_ROOT}"
    case "${mode}" in
        1) echo -e "  Mode      : ${BOLD}Restauration complète${RESET}" ;;
        2) echo -e "  Mode      : ${BOLD}Base de données seulement${RESET}" ;;
        3) echo -e "  Mode      : ${BOLD}Fichiers seulement${RESET}" ;;
        4) echo -e "  Mode      : ${BOLD}Configuration seulement${RESET}" ;;
    esac
    echo ""
    confirm "  Confirmer la restauration ?" || { log INFO "Restauration annulée."; return 0; }

    detect_db

    # ── Extraction de l'archive ────────────────────────────────────────────────
    TMP_DIR="$(mktemp -d /tmp/redmine_restore_XXXXXX)"
    trap 'rm -rf "${TMP_DIR}"' EXIT

    log STEP "Extraction de l'archive"
    (tar -xzf "${selected}" -C "${TMP_DIR}") &
    spinner $! "Extraction en cours..."
    wait $!
    log SUCCESS "Archive extraite dans ${TMP_DIR}"

    # Afficher le manifest
    if [[ -f "${TMP_DIR}/manifest.txt" ]]; then
        hr
        echo -e "${BOLD}  Informations de la sauvegarde :${RESET}"
        head -20 "${TMP_DIR}/manifest.txt" | sed 's/^/  /'
        hr
    fi

    # ── Restauration base de données ──────────────────────────────────────────
    if [[ "${mode}" -eq 1 || "${mode}" -eq 2 ]]; then
        [[ -f "${TMP_DIR}/database.sql" ]] || die "Dump de base de données introuvable dans l'archive"
        case "${DB_ADAPTER}" in
            postgresql) restore_db_postgresql ;;
            mysql2|mysql) restore_db_mysql2   ;;
            sqlite3)    restore_db_sqlite3    ;;
            *) die "Adaptateur DB non supporté : ${DB_ADAPTER}" ;;
        esac
    fi

    # ── Restauration fichiers ─────────────────────────────────────────────────
    if [[ "${mode}" -eq 1 || "${mode}" -eq 3 ]]; then
        [[ -f "${TMP_DIR}/files_backup.tar.gz" ]] || \
            die "Archive des fichiers introuvable dans la sauvegarde"
        log STEP "Restauration des fichiers Redmine"
        local parent_dir; parent_dir="$(dirname "${REDMINE_ROOT}")"
        local base_name; base_name="$(basename "${REDMINE_ROOT}")"
        # Sauvegarde du dossier actuel
        if [[ -d "${REDMINE_ROOT}" ]]; then
            mv "${REDMINE_ROOT}" "${REDMINE_ROOT}.pre_restore_${TIMESTAMP}" && \
                log INFO "Ancienne installation sauvegardée : ${REDMINE_ROOT}.pre_restore_${TIMESTAMP}"
        fi
        (tar -xzf "${TMP_DIR}/files_backup.tar.gz" -C "${parent_dir}") &
        spinner $! "Restauration des fichiers..."
        wait $!
        log SUCCESS "Fichiers restaurés dans ${REDMINE_ROOT}"
    fi

    # ── Restauration configuration uniquement ────────────────────────────────
    if [[ "${mode}" -eq 4 ]]; then
        [[ -d "${TMP_DIR}/config" ]] || die "Dossier config introuvable dans l'archive"
        log STEP "Restauration des fichiers de configuration"
        for cfg in "${TMP_DIR}/config/"*; do
            local fname; fname=$(basename "${cfg}")
            [[ "${fname}" == ".env.backup" ]] && continue
            local dest="${REDMINE_ROOT}/config/${fname}"
            cp "${cfg}" "${dest}" && log INFO "  Restauré : config/${fname}"
        done
        log SUCCESS "Configuration restaurée"
    fi

    # ── Post-restauration ─────────────────────────────────────────────────────
    if [[ "${mode}" -eq 1 || "${mode}" -eq 3 ]]; then
        log STEP "Post-restauration : ajustement des permissions"
        local redmine_user
        redmine_user=$(stat -c '%U' "${REDMINE_ROOT}" 2>/dev/null || echo "www-data")
        chown -R "${redmine_user}:${redmine_user}" "${REDMINE_ROOT}" 2>/dev/null || \
            log WARN "Impossible d'ajuster les permissions (continuez manuellement)"
        log SUCCESS "Permissions ajustées pour '${redmine_user}'"

        if [[ "${mode}" -eq 1 ]]; then
            log INFO "Relance conseillée : bundle exec rake db:migrate RAILS_ENV=${RAILS_ENV}"
            log INFO "Relance conseillée : touch ${REDMINE_ROOT}/tmp/restart.txt"
        fi
    fi

    hr
    echo -e "${GREEN}${BOLD}  ✔  Restauration terminée avec succès !${RESET}"
    hr
}

# =============================================================================
#  LISTING DES SAUVEGARDES
# =============================================================================

do_list() {
    hr
    echo -e "${BOLD}  Sauvegardes disponibles dans : ${BACKUP_DIR}${RESET}\n"
    local count=0
    while IFS= read -r archive; do
        local fname; fname=$(basename "${archive}")
        local fsize; fsize=$(du -sh "${archive}" | cut -f1)
        local fdate; fdate=$(stat -c '%y' "${archive}" | cut -d'.' -f1)
        printf "  ${CYAN}%-50s${RESET}  ${YELLOW}%6s${RESET}  %s\n" "${fname}" "${fsize}" "${fdate}"
        count=$(( count + 1 ))
    done < <(find "${BACKUP_DIR}" -maxdepth 1 -name "redmine_backup_*.tar.gz" | sort -r)
    [[ "${count}" -eq 0 ]] && echo -e "  ${YELLOW}Aucune sauvegarde trouvée.${RESET}"
    echo ""
    echo -e "  Total : ${BOLD}${count} archive(s)${RESET}"
    hr
}

# =============================================================================
#  AIDE
# =============================================================================

do_help() {
    hr
    echo -e "${BOLD}  redmine-backup.sh — Outil de sauvegarde/restauration Redmine${RESET}\n"
    echo -e "  ${BOLD}Usage :${RESET}"
    echo -e "    sudo $0 backup           Creer une sauvegarde complete"
    echo -e "    sudo $0 restore          Menu interactif de restauration"
    echo -e "    sudo $0 list             Lister les sauvegardes disponibles"
    echo -e "    sudo $0 cron             Gerer la planification automatique"
    echo -e "    sudo $0 help             Afficher cette aide\n"
    echo -e "  ${BOLD}Configuration (.env) :${RESET}"
    echo -e "    REDMINE_ROOT    Repertoire racine de Redmine"
    echo -e "    BACKUP_DIR      Dossier de stockage des archives"
    echo -e "    RAILS_ENV       Environnement Rails (production)"
    echo -e "    DB_CONFIG       Chemin custom vers database.yml (optionnel)"
    echo -e "    DB_TYPE         Forcer l'adaptateur DB (optionnel)"
    echo -e "    KEEP_BACKUPS    Nombre d'archives a conserver (defaut: 7)"
    echo -e "    INCLUDE_LOG     Inclure les logs dans la sauvegarde (true/false)\n"
    echo -e "  ${BOLD}Adaptateurs DB supportes :${RESET}"
    echo -e "    postgresql, mysql2, sqlite3\n"
    echo -e "  ${BOLD}Exemples cron utiles :${RESET}"
    echo -e "    0 2 * * *     Tous les jours a 02h00"
    echo -e "    0 2 * * 1     Tous les lundis a 02h00"
    echo -e "    0 2 1 * *     Le 1er de chaque mois a 02h00\n"
    echo -e "  ${BOLD}Journal :${RESET} ${LOG_FILE}"
    hr
}

# =============================================================================
#  GESTION CRON
# =============================================================================

do_cron() {
    local script_path; script_path="$(realpath "${BASH_SOURCE[0]}")"
    local cron_tag="# redmine-backup-auto"
    local cron_user="root"

    hr
    echo -e "${BOLD}${CYAN}  Planification des sauvegardes automatiques (Cron)${RESET}"
    hr

    # ── Afficher les entrées cron existantes ──────────────────────────────────
    echo -e "\n${BOLD}  Entrées cron actuelles pour redmine-backup :${RESET}\n"
    local existing
    existing=$(crontab -u "${cron_user}" -l 2>/dev/null | grep "${cron_tag}" || true)
    if [[ -n "${existing}" ]]; then
        echo "${existing}" | while read -r line; do
            echo -e "  ${YELLOW}${line}${RESET}"
        done
    else
        echo -e "  ${YELLOW}Aucune planification active.${RESET}"
    fi

    echo ""
    hr
    echo -e "\n${BOLD}  Choisir une fréquence de sauvegarde :${RESET}\n"
    echo -e "  ${CYAN}[1]${RESET}  Quotidienne  — tous les jours  à 02h00"
    echo -e "  ${CYAN}[2]${RESET}  Biquotidienne — 2x par jour   à 02h00 et 14h00"
    echo -e "  ${CYAN}[3]${RESET}  Hebdomadaire — tous les lundis à 02h00"
    echo -e "  ${CYAN}[4]${RESET}  Mensuelle    — le 1er du mois  à 02h00"
    echo -e "  ${CYAN}[5]${RESET}  Personnalisée — saisir une expression cron manuellement"
    echo -e "  ${CYAN}[6]${RESET}  Supprimer    — retirer toutes les planifications"
    echo -e "  ${CYAN}[0]${RESET}  Retour au menu principal"
    echo ""
    hr

    local cron_choice
    while true; do
        read -rp "$(echo -e "${BOLD}  Votre choix [0-6] : ${RESET}")" cron_choice
        [[ "${cron_choice}" =~ ^[0-6]$ ]] && break
        echo -e "${RED}  Choix invalide.${RESET}"
    done

    [[ "${cron_choice}" -eq 0 ]] && return 0

    # ── Suppression ────────────────────────────────────────────────────────────
    if [[ "${cron_choice}" -eq 6 ]]; then
        local current_cron
        # Supprime la ligne cron ET la ligne MAILTO qui la précède si elle existe
        current_cron=$(crontab -u "${cron_user}" -l 2>/dev/null \
            | grep -v "${cron_tag}" \
            || true)
        if [[ -n "${current_cron}" ]]; then
            echo "${current_cron}" | crontab -u "${cron_user}" -
        else
            crontab -u "${cron_user}" - <<< ""
        fi
        log SUCCESS "Toutes les planifications redmine-backup ont ete supprimees"
        return 0
    fi

    # ── Construction de l'expression cron ─────────────────────────────────────
    local cron_expr=""
    case "${cron_choice}" in
        1) cron_expr="0 2 * * *" ;;
        2) cron_expr="0 2,14 * * *" ;;
        3) cron_expr="0 2 * * 1" ;;
        4) cron_expr="0 2 1 * *" ;;
        5)
            echo -e "\n  ${BOLD}Format :${RESET} minute heure jour_du_mois mois jour_de_semaine"
            echo -e "  ${YELLOW}Exemples :${RESET}"
            echo -e "    0 3 * * *     -> tous les jours a 03h00"
            echo -e "    30 1 * * 0    -> tous les dimanches a 01h30"
            echo -e "    0 4 */2 * *   -> tous les 2 jours a 04h00"
            echo ""
            while true; do
                read -rp "$(echo -e "${BOLD}  Expression cron : ${RESET}")" cron_expr
                # Validation basique : 5 champs
                local field_count; field_count=$(echo "${cron_expr}" | wc -w)
                [[ "${field_count}" -eq 5 ]] && break
                echo -e "${RED}  Expression invalide (5 champs requis).${RESET}"
            done
            ;;
    esac

    # ── Option : notification par email ───────────────────────────────────────
    local mail_opt=""
    echo ""
    read -rp "$(echo -e "${YELLOW}  Envoyer les logs cron par email ? (laisser vide pour ignorer) : ${RESET}")" mail_addr
    if [[ -n "${mail_addr}" ]]; then
        mail_opt="MAILTO=${mail_addr}"
    else
        mail_opt="MAILTO=\"\""
    fi

    # ── Affichage de la ligne cron finale ──────────────────────────────────────
    local cron_line="${cron_expr} ${script_path} backup ${cron_tag}"
    echo ""
    hr
    echo -e "  ${BOLD}Entree cron qui sera ajoutee :${RESET}"
    echo ""
    [[ "${mail_opt}" != 'MAILTO=""' ]] && echo -e "  ${YELLOW}${mail_opt}${RESET}"
    echo -e "  ${YELLOW}${cron_line}${RESET}"
    echo ""

    confirm "  Confirmer l'installation de cette tache cron ?" || {
        log INFO "Planification annulee."
        return 0
    }

    # ── Installation ───────────────────────────────────────────────────────────
    # Supprimer anciennes entrées redmine-backup (MAILTO + ligne cron), puis ajouter
    local clean_cron
    clean_cron=$(crontab -u "${cron_user}" -l 2>/dev/null \
        | grep -v "${cron_tag}" \
        | grep -v "^MAILTO=.*${cron_tag}" \
        || true)

    {
        [[ -n "${clean_cron}" ]] && echo "${clean_cron}"
        # MAILTO sur sa propre ligne, sans commentaire (cron l'interdit)
        echo "${mail_opt}"
        echo "${cron_line}"
    } | crontab -u "${cron_user}" -

    log SUCCESS "Tache cron installée pour root"
    log INFO "Vérification : crontab -u root -l | grep redmine"

    # ── Vérification s'assurer que cron tourne ────────────────────────────────
    if ! systemctl is-active --quiet cron 2>/dev/null && \
       ! systemctl is-active --quiet crond 2>/dev/null; then
        log WARN "Le service cron ne semble pas actif. Démarrer avec : systemctl start cron"
    else
        log INFO "Service cron actif"
    fi

    hr
    echo -e "${GREEN}${BOLD}  Planification cron configuree avec succes !${RESET}"
    hr
}

# =============================================================================
#  MENU INTERACTIF (si lancé sans argument)
# =============================================================================

interactive_menu() {
    while true; do
        clear
        hr
        echo -e "${BOLD}${CYAN}"
        echo "       ██████╗ ███████╗██████╗ ███╗   ███╗██╗███╗   ██╗███████╗"
        echo "       ██╔══██╗██╔════╝██╔══██╗████╗ ████║██║████╗  ██║██╔════╝"
        echo "       ██████╔╝█████╗  ██║  ██║██╔████╔██║██║██╔██╗ ██║█████╗  "
        echo "       ██╔══██╗██╔══╝  ██║  ██║██║╚██╔╝██║██║██║╚██╗██║██╔══╝  "
        echo "       ██║  ██║███████╗██████╔╝██║ ╚═╝ ██║██║██║ ╚████║███████╗"
        echo "       ╚═╝  ╚═╝╚══════╝╚═════╝ ╚═╝     ╚═╝╚═╝╚═╝  ╚═══╝╚══════╝"
        echo -e "${RESET}${BOLD}                  Gestionnaire de Sauvegarde Redmine${RESET}"
        hr
        echo -e "  ${BOLD}Environnement :${RESET} ${RAILS_ENV}   ${BOLD}Cible :${RESET} ${REDMINE_ROOT}"
        hr
        echo ""
        echo -e "  ${CYAN}[1]${RESET}  ${BOLD}Creer une sauvegarde${RESET}   - Sauvegarde complete (DB + fichiers + config)"
        echo -e "  ${CYAN}[2]${RESET}  ${BOLD}Restaurer${RESET}              - Menu interactif de restauration"
        echo -e "  ${CYAN}[3]${RESET}  ${BOLD}Lister les sauvegardes${RESET} - Voir les archives disponibles"
        echo -e "  ${CYAN}[4]${RESET}  ${BOLD}Planification Cron${RESET}     - Gerer les sauvegardes automatiques"
        echo -e "  ${CYAN}[5]${RESET}  ${BOLD}Afficher l'aide${RESET}        - Documentation complete"
        echo -e "  ${CYAN}[0]${RESET}  ${BOLD}Quitter${RESET}"
        echo ""
        hr
        read -rp "$(echo -e "${BOLD}  Votre choix : ${RESET}")" choice
        echo ""
        case "${choice}" in
            1) do_backup  ;;
            2) do_restore ;;
            3) do_list    ;;
            4) do_cron    ;;
            5) do_help    ;;
            0) echo -e "${GREEN}  Au revoir !${RESET}\n"; exit 0 ;;
            *) echo -e "${RED}  Choix invalide.${RESET}" ; sleep 1; continue ;;
        esac
        # Pause courte puis retour automatique au menu
        echo ""
        echo -e "${BLUE}  ↩  Retour au menu dans 10 secondes...${RESET}"
        sleep 10
    done
}

# =============================================================================
#  POINT D'ENTRÉE
# =============================================================================

main() {
    require_root
    require_command tar
    require_command gzip
    load_env

    local cmd="${1:-menu}"
    case "${cmd}" in
        backup)  do_backup  ;;
        restore) do_restore ;;
        list)    do_list    ;;
        cron)    do_cron    ;;
        help|-h|--help) do_help ;;
        menu|"")  interactive_menu ;;
        *) echo -e "${RED}Commande inconnue : ${cmd}${RESET}"; do_help; exit 1 ;;
    esac
}

main "$@"