#!/usr/bin/env bash
# shellcheck disable=SC2034,SC2015,SC2059

#########################################################################################
#### gitlab-upgrade-path-calculator.sh — Calculate required GitLab upgrade stops     ####
#### and PostgreSQL compatibility from 13.x through 18.x                             ####
####                                                                                 ####
#### Author: Phil Connor                                                             ####
#### Contact: contact@mylinux.work                                                   ####
#### License: MIT                                                                    ####
#### Version 1.00                                                                    ####
####                                                                                 ####
#### Usage:                                                                          ####
####   ./gitlab-upgrade-path-calculator.sh --from 16.3.0 --to 18.2.0                 ####
####   ./gitlab-upgrade-path-calculator.sh --from 15.4.0 --to latest --pg-version 13 ####
####   ./gitlab-upgrade-path-calculator.sh --db-check --from 16.0.0 --pg-version 13  ####
####   ./gitlab-upgrade-path-calculator.sh --list-stops                              ####
####                                                                                 ####
#### See --help for all options.                                                     ####
#########################################################################################

set -euo pipefail

# ── Defaults ──────────────────────────────────────────────────────────────────

RUN_MODE=""
FROM_VERSION=""
TO_VERSION=""
PG_VERSION=""
DB_SIZE=""          # small, medium, large, xlarge
FORMAT="text"
SKIP_CONDITIONAL=false
VERBOSE="${VERBOSE:-false}"
COLOR="${COLOR:-auto}"
SCRIPT_NAME="$(basename "$0")"

# ── Required Upgrade Stops ────────────────────────────────────────────────────
# Format: "version|conditional|notes"
# conditional: 0 = always required, 1 = conditional

STOPS=(
    "13.0.14|0|Required stop for 13.x upgrades"
    "13.1.11|0|Required stop (CSRF token migration)"
    "13.8.8|0|Required stop (duplicate services migration)"
    "13.12.15|0|Required stop (last 13.x before 14.0)"
    "14.0.12|0|Required stop for 14.x upgrades"
    "14.3.6|0|Required stop"
    "14.9.5|0|Required stop"
    "14.10.5|0|Required stop"
    "15.0.5|0|Required stop for 15.x upgrades"
    "15.1.6|1|Required only for multi-node instances"
    "15.4.6|0|Required stop"
    "15.11.13|0|Required stop"
    "16.0.10|1|Required only for instances with many users or large pipeline variables"
    "16.1.8|1|Required only for instances with NPM packages in package registry"
    "16.2.11|1|Required only for instances with large pipeline variables history"
    "16.3.9|0|Required stop"
    "16.7.10|0|Required stop"
    "16.11.10|0|Required stop"
    "17.1.8|1|Required only for instances with large ci_pipeline_messages tables"
    "17.3.7|0|Required stop"
    "17.5.5|0|Required stop"
    "17.8.7|0|Required stop"
    "17.11.7|0|Required stop"
    "18.2.0|0|Required stop (18.x predictable schedule)"
    "18.5.0|0|Required stop (18.x predictable schedule)"
    "18.8.0|0|Required stop (18.x predictable schedule)"
    "18.11.0|0|Required stop (18.x predictable schedule)"
)

# ── PostgreSQL Requirements ───────────────────────────────────────────────────
# Format: "gl_major|pg_min|pg_max"

PG_REQS=(
    "13|11|13"
    "14|12|13"
    "15|12|14"
    "16|13|15"
    "17|14|16"
    "18|16|17"
)

LATEST_VERSION="18.11.0"

# ── Colors ────────────────────────────────────────────────────────────────────

setup_colors() {
    if [[ "$COLOR" == "never" ]]; then
        RED="" GREEN="" YELLOW="" BLUE="" BOLD="" DIM="" RESET="" CYAN=""
        return
    fi
    if [[ "$COLOR" == "auto" && ! -t 1 ]]; then
        RED="" GREEN="" YELLOW="" BLUE="" BOLD="" DIM="" RESET="" CYAN=""
        return
    fi
    RED='\033[0;31m'
    GREEN='\033[0;32m'
    YELLOW='\033[0;33m'
    BLUE='\033[0;34m'
    CYAN='\033[0;36m'
    BOLD='\033[1m'
    DIM='\033[2m'
    RESET='\033[0m'
}

# ── Logging ───────────────────────────────────────────────────────────────────

log()     { printf "${GREEN}%s${RESET}\n" "$*"; }
warn()    { printf "${YELLOW}⚠ %s${RESET}\n" "$*" >&2; }
err()     { printf "${RED}✗ %s${RESET}\n" "$*" >&2; }
verbose() { [[ "$VERBOSE" == "true" ]] && printf "${DIM}  %s${RESET}\n" "$*" >&2 || true; }
die()     { err "$*"; exit 1; }

# ── Help ──────────────────────────────────────────────────────────────────────

show_help() {
    cat <<EOF
Usage: $SCRIPT_NAME [MODE] [OPTIONS]

Modes:
  --path              Calculate upgrade path (default when --from/--to given)
  --check             Auto-detect installed GitLab version, show path to latest
  --list-stops        List all known required upgrade stops
  --db-check          Check PostgreSQL compatibility for the upgrade path

Options:
  --from VERSION      Current GitLab version (e.g., 15.4.0)
  --to VERSION        Target GitLab version (e.g., 18.2.0 or "latest")
  --pg-version VER    Current PostgreSQL major version (e.g., 13, 14, 16)
  --db-size SIZE      Database size: small (<10GB), medium (10-50GB),
                      large (50-200GB), xlarge (200GB+) — affects migration
                      time estimates (default: estimates software time only)
  --skip-conditional  Exclude conditional stops from the path
  --format FMT        Output format: text, json (default: text)
  --no-color          Disable colored output
  --verbose           Debug output
  --help              Show this help

Environment Variables:
  COLOR               Color mode: auto, always, never
  VERBOSE             Debug output (true/false)

Examples:
  $SCRIPT_NAME --from 15.4.0 --to 18.2.0
  $SCRIPT_NAME --from 16.3.0 --to latest --pg-version 13 --db-size large
  $SCRIPT_NAME --check
  $SCRIPT_NAME --check --pg-version 14
  $SCRIPT_NAME --list-stops
  $SCRIPT_NAME --db-check --from 16.0.0 --to 18.5.0 --pg-version 13
  $SCRIPT_NAME --from 15.0.0 --to 18.2.0 --format json
  $SCRIPT_NAME --from 16.3.0 --to 17.8.7 --skip-conditional
EOF
}

# ── Argument Parsing ──────────────────────────────────────────────────────────

parse_args() {
    while [[ $# -gt 0 ]]; do
        case "$1" in
            --path)             RUN_MODE="path"; shift ;;
            --check)            RUN_MODE="check"; shift ;;
            --list-stops)       RUN_MODE="list-stops"; shift ;;
            --db-check)         RUN_MODE="db-check"; shift ;;
            --from)             FROM_VERSION="$2"; shift 2 ;;
            --to)               TO_VERSION="$2"; shift 2 ;;
            --pg-version)       PG_VERSION="${2%%.*}"; shift 2 ;;
            --db-size)          DB_SIZE="$2"; shift 2 ;;
            --skip-conditional) SKIP_CONDITIONAL=true; shift ;;
            --format)           FORMAT="$2"; shift 2 ;;
            --no-color)         COLOR="never"; shift ;;
            --verbose)          VERBOSE="true"; shift ;;
            --help)             show_help; exit 0 ;;
            *)                  die "Unknown option: $1" ;;
        esac
    done

    if [[ -z "$RUN_MODE" ]]; then
        if [[ -n "$FROM_VERSION" || -n "$TO_VERSION" ]]; then
            RUN_MODE="path"
        else
            err "No mode specified"; echo ""; show_help; exit 1
        fi
    fi
}

# ── Version Helpers ───────────────────────────────────────────────────────────

version_to_int() {
    local ver="$1"
    local major minor patch
    IFS='.' read -r major minor patch <<< "$ver"
    patch="${patch:-0}"
    printf '%d' $(( major * 10000 + minor * 100 + patch ))
}

version_major() {
    echo "${1%%.*}"
}

validate_version() {
    local ver="$1"
    if [[ ! "$ver" =~ ^[0-9]+\.[0-9]+\.[0-9]+$ ]]; then
        die "Invalid version format: $ver (expected X.Y.Z)"
    fi
    local major
    major=$(version_major "$ver")
    if (( major < 13 )); then
        die "Version $ver is below minimum supported (13.0.0)"
    fi
}

# ── Detection ─────────────────────────────────────────────────────────────────

detect_gitlab_version() {
    local ver=""
    if command -v gitlab-ctl >/dev/null 2>&1; then
        ver=$(gitlab-ctl version 2>/dev/null | grep -oP '\d+\.\d+\.\d+' | head -1 || true)
    fi
    if [[ -z "$ver" ]] && command -v dpkg >/dev/null 2>&1; then
        ver=$(dpkg -l gitlab-ce gitlab-ee 2>/dev/null | awk '/^ii/{print $3}' | grep -oP '\d+\.\d+\.\d+' | head -1 || true)
    fi
    if [[ -z "$ver" ]] && command -v rpm >/dev/null 2>&1; then
        ver=$(rpm -q gitlab-ce gitlab-ee 2>/dev/null | grep -oP '\d+\.\d+\.\d+' | head -1 || true)
    fi
    echo "$ver"
}

detect_pg_version() {
    local ver=""
    if command -v gitlab-psql >/dev/null 2>&1; then
        ver=$(gitlab-psql --version 2>/dev/null | grep -oP '\d+' | head -1 || true)
    fi
    if [[ -z "$ver" ]] && command -v psql >/dev/null 2>&1; then
        ver=$(psql --version 2>/dev/null | grep -oP '\d+' | head -1 || true)
    fi
    echo "$ver"
}

# ── Core Logic ────────────────────────────────────────────────────────────────

get_pg_req() {
    local gl_major="$1"
    local entry
    for entry in "${PG_REQS[@]}"; do
        IFS='|' read -r req_gl req_min req_max <<< "$entry"
        if [[ "$req_gl" == "$gl_major" ]]; then
            echo "${req_min}|${req_max}"
            return
        fi
    done
    echo "unknown|unknown"
}

build_upgrade_path() {
    local from_int to_int
    from_int=$(version_to_int "$FROM_VERSION")
    to_int=$(version_to_int "$TO_VERSION")

    UPGRADE_PATH=()
    local entry ver ver_int conditional notes
    for entry in "${STOPS[@]}"; do
        IFS='|' read -r ver conditional notes <<< "$entry"
        ver_int=$(version_to_int "$ver")

        if (( ver_int <= from_int )); then
            continue
        fi
        if (( ver_int > to_int )); then
            continue
        fi
        if [[ "$SKIP_CONDITIONAL" == "true" && "$conditional" == "1" ]]; then
            verbose "Skipping conditional stop: $ver"
            continue
        fi

        UPGRADE_PATH+=("$entry")
    done

    # Add target if it's not already in the path
    local last_ver=""
    if [[ ${#UPGRADE_PATH[@]} -gt 0 ]]; then
        IFS='|' read -r last_ver _ _ <<< "${UPGRADE_PATH[-1]}"
    fi
    if [[ "$last_ver" != "$TO_VERSION" ]]; then
        UPGRADE_PATH+=("${TO_VERSION}|0|Target version")
    fi
}

get_pg_warnings() {
    PG_WARNINGS=()
    if [[ -z "$PG_VERSION" ]]; then
        return
    fi

    local from_major to_major
    from_major=$(version_major "$FROM_VERSION")
    to_major=$(version_major "$TO_VERSION")

    local gl_major
    for (( gl_major = from_major; gl_major <= to_major; gl_major++ )); do
        local req
        req=$(get_pg_req "$gl_major")
        IFS='|' read -r pg_min pg_max <<< "$req"
        if [[ "$pg_min" == "unknown" ]]; then
            continue
        fi
        if (( PG_VERSION < pg_min )); then
            # Find the last stop before this major version
            local boundary_stop=""
            local prev_major=$(( gl_major - 1 ))
            local entry ver
            for entry in "${STOPS[@]}"; do
                IFS='|' read -r ver _ _ <<< "$entry"
                if [[ "$(version_major "$ver")" == "$prev_major" ]]; then
                    boundary_stop="$ver"
                fi
            done
            PG_WARNINGS+=("PostgreSQL ${PG_VERSION} is below minimum for GitLab ${gl_major}.x (requires ${pg_min}+)|Upgrade PostgreSQL to ${pg_min}+ before upgrading past GitLab ${boundary_stop:-${prev_major}.x}|${gl_major}|${pg_min}|${pg_max}")
        fi
    done
}

estimate_downtime() {
    local stop_count=${#UPGRADE_PATH[@]}
    local pg_upgrade_count=${#PG_WARNINGS[@]}

    # Software time: package install + gitlab-ctl reconfigure per stop
    DT_SW_LOW=$(( stop_count * 5 ))
    DT_SW_HIGH=$(( stop_count * 15 ))

    # Background migration time per stop, based on database size
    # These run between stops and must complete before proceeding
    local mig_low=0 mig_high=0
    case "$DB_SIZE" in
        small)   mig_low=2;   mig_high=10  ;;  # <10GB: minutes
        medium)  mig_low=10;  mig_high=30  ;;  # 10-50GB: tens of minutes
        large)   mig_low=30;  mig_high=90  ;;  # 50-200GB: up to hours
        xlarge)  mig_low=60;  mig_high=240 ;;  # 200GB+: hours per stop
        *)       mig_low=0;   mig_high=0   ;;  # unknown: show software only
    esac
    DT_MIG_LOW=$(( stop_count * mig_low ))
    DT_MIG_HIGH=$(( stop_count * mig_high ))

    # PostgreSQL major upgrade time
    DT_PG_LOW=$(( pg_upgrade_count * 15 ))
    DT_PG_HIGH=$(( pg_upgrade_count * 60 ))

    DT_GL_LOW=$(( DT_SW_LOW + DT_MIG_LOW ))
    DT_GL_HIGH=$(( DT_SW_HIGH + DT_MIG_HIGH ))
    DT_TOTAL_LOW=$(( DT_GL_LOW + DT_PG_LOW ))
    DT_TOTAL_HIGH=$(( DT_GL_HIGH + DT_PG_HIGH ))
}

# ── Text Output ───────────────────────────────────────────────────────────────

print_header() {
    printf "\n${BOLD}GitLab Upgrade Path Calculator${RESET}\n"
    printf "══════════════════════════════════════════════════════════════\n\n"
}

format_path_text() {
    print_header

    printf "  ${BOLD}From:${RESET}    %s\n" "$FROM_VERSION"
    printf "  ${BOLD}To:${RESET}      %s\n" "$TO_VERSION"
    printf "  ${BOLD}Stops:${RESET}   %d\n" "${#UPGRADE_PATH[@]}"

    if [[ -n "$PG_VERSION" ]]; then
        if [[ ${#PG_WARNINGS[@]} -gt 0 ]]; then
            local last_warn="${PG_WARNINGS[-1]}"
            IFS='|' read -r _ _ _ final_pg_min _ <<< "$last_warn"
            printf "\n  ${BOLD}PostgreSQL:${RESET}  ${YELLOW}Currently ${PG_VERSION} → Must upgrade to ${final_pg_min}+ before GitLab ${TO_VERSION}${RESET}\n"
        else
            printf "\n  ${BOLD}PostgreSQL:${RESET}  ${GREEN}${PG_VERSION} — compatible with target${RESET}\n"
        fi
    fi

    printf "\n  ── Upgrade Path ──────────────────────────────────────────\n\n"
    printf "  ${DIM}Step  Version       Notes                                    PG Required${RESET}\n"
    printf "  ${DIM}────  ────────────  ───────────────────────────────────────  ──────────${RESET}\n"

    local step=0 entry ver conditional notes
    for entry in "${UPGRADE_PATH[@]}"; do
        IFS='|' read -r ver conditional notes <<< "$entry"
        step=$((step + 1))

        local gl_major pg_range
        gl_major=$(version_major "$ver")
        local req
        req=$(get_pg_req "$gl_major")
        IFS='|' read -r pg_min pg_max <<< "$req"
        if [[ "$pg_min" != "unknown" ]]; then
            pg_range="${pg_min}-${pg_max}"
        else
            pg_range="—"
        fi

        local cond_marker=""
        if [[ "$conditional" == "1" ]]; then
            cond_marker=" ⓘ"
        fi

        local ver_color="$RESET"
        if [[ "$notes" == "Target version" ]]; then
            ver_color="$GREEN"
        fi

        printf "  %3d    ${ver_color}%-12s${RESET}  %-39s  %s\n" "$step" "$ver" "${notes}${cond_marker}" "$pg_range"
    done

    if [[ ${#PG_WARNINGS[@]} -gt 0 ]]; then
        printf "\n  ── PostgreSQL Upgrade Required ───────────────────────────\n\n"
        local warning
        for warning in "${PG_WARNINGS[@]}"; do
            IFS='|' read -r msg action gl_major pg_min pg_max <<< "$warning"
            printf "  ${YELLOW}⚠ %s${RESET}\n" "$msg"
            printf "    → %s\n\n" "$action"
        done
    fi

    estimate_downtime
    printf "  ── Estimated Downtime ────────────────────────────────────\n\n"
    printf "  Software:         %d stops × 5-15 min = %d-%d min\n" "${#UPGRADE_PATH[@]}" "$DT_SW_LOW" "$DT_SW_HIGH"
    printf "                    ${DIM}(package install + gitlab-ctl reconfigure)${RESET}\n"
    if [[ -n "$DB_SIZE" ]]; then
        printf "  Migrations:       %d stops × %s db = %d-%d min\n" "${#UPGRADE_PATH[@]}" "$DB_SIZE" "$DT_MIG_LOW" "$DT_MIG_HIGH"
        printf "                    ${DIM}(background migrations must complete per stop)${RESET}\n"
    else
        printf "  Migrations:       ${DIM}use --db-size (small/medium/large/xlarge) for estimates${RESET}\n"
    fi
    if [[ ${#PG_WARNINGS[@]} -gt 0 ]]; then
        printf "  PG upgrades:      %d × 15-60 min = %d-%d min\n" "${#PG_WARNINGS[@]}" "$DT_PG_LOW" "$DT_PG_HIGH"
    fi
    printf "\n  ${BOLD}Total estimate:   %d-%d min${RESET}" "$DT_TOTAL_LOW" "$DT_TOTAL_HIGH"
    if (( DT_TOTAL_HIGH >= 120 )); then
        local hours_low=$(( DT_TOTAL_LOW / 60 ))
        local hours_high=$(( DT_TOTAL_HIGH / 60 ))
        printf " (%d-%d hrs — plan a full maintenance window)" "$hours_low" "$hours_high"
    fi
    printf "\n\n"

    if [[ "$SKIP_CONDITIONAL" == "false" ]]; then
        local has_conditional=false
        for entry in "${UPGRADE_PATH[@]}"; do
            IFS='|' read -r _ conditional _ <<< "$entry"
            if [[ "$conditional" == "1" ]]; then
                has_conditional=true
                break
            fi
        done
        if [[ "$has_conditional" == "true" ]]; then
            printf "  ${DIM}ⓘ = conditional stop (may be skippable — use --skip-conditional)${RESET}\n\n"
        fi
    fi
}

format_path_json() {
    local steps_json="["
    local step=0 first=true entry ver conditional notes
    for entry in "${UPGRADE_PATH[@]}"; do
        IFS='|' read -r ver conditional notes <<< "$entry"
        step=$((step + 1))
        local gl_major req pg_min pg_max
        gl_major=$(version_major "$ver")
        req=$(get_pg_req "$gl_major")
        IFS='|' read -r pg_min pg_max <<< "$req"

        [[ "$first" == "true" ]] || steps_json+=","
        first=false
        steps_json+=$(printf '{"step":%d,"version":"%s","conditional":%s,"notes":"%s","pg_min":"%s","pg_max":"%s"}' \
            "$step" "$ver" "$( [[ "$conditional" == "1" ]] && echo "true" || echo "false" )" "$notes" "$pg_min" "$pg_max")
    done
    steps_json+="]"

    local pg_upgrades_json="["
    first=true
    if [[ ${#PG_WARNINGS[@]} -gt 0 ]]; then
        local warning
        for warning in "${PG_WARNINGS[@]}"; do
            IFS='|' read -r msg action gl_major pg_min pg_max <<< "$warning"
            [[ "$first" == "true" ]] || pg_upgrades_json+=","
            first=false
            pg_upgrades_json+=$(printf '{"before_gitlab":"%s.0.0","min_pg":"%s","max_pg":"%s"}' "$gl_major" "$pg_min" "$pg_max")
        done
    fi
    pg_upgrades_json+="]"

    estimate_downtime

    printf '{\n'
    printf '  "from": "%s",\n' "$FROM_VERSION"
    printf '  "to": "%s",\n' "$TO_VERSION"
    printf '  "total_stops": %d,\n' "${#UPGRADE_PATH[@]}"
    printf '  "pg_current": "%s",\n' "${PG_VERSION:-null}"
    printf '  "pg_upgrades_needed": %s,\n' "$pg_upgrades_json"
    printf '  "steps": %s,\n' "$steps_json"
    printf '  "db_size": "%s",\n' "${DB_SIZE:-unknown}"
    printf '  "estimated_downtime_min": {"software": {"low": %d, "high": %d}, "migrations": {"low": %d, "high": %d}, "pg_upgrades": {"low": %d, "high": %d}, "total": {"low": %d, "high": %d}}\n' \
        "$DT_SW_LOW" "$DT_SW_HIGH" "$DT_MIG_LOW" "$DT_MIG_HIGH" "$DT_PG_LOW" "$DT_PG_HIGH" "$DT_TOTAL_LOW" "$DT_TOTAL_HIGH"
    printf '}\n'
}

# ── Mode: --path ──────────────────────────────────────────────────────────────

run_path() {
    if [[ -z "$FROM_VERSION" ]]; then
        verbose "Detecting installed GitLab version..."
        FROM_VERSION=$(detect_gitlab_version)
        if [[ -z "$FROM_VERSION" ]]; then
            die "Could not detect installed GitLab version. Use --from VERSION."
        fi
        log "Detected GitLab version: $FROM_VERSION"
    fi
    if [[ -z "$TO_VERSION" || "$TO_VERSION" == "latest" ]]; then
        TO_VERSION="$LATEST_VERSION"
    fi

    validate_version "$FROM_VERSION"
    validate_version "$TO_VERSION"

    local from_int to_int
    from_int=$(version_to_int "$FROM_VERSION")
    to_int=$(version_to_int "$TO_VERSION")
    if (( from_int >= to_int )); then
        die "Target version ($TO_VERSION) must be higher than current version ($FROM_VERSION)"
    fi

    build_upgrade_path
    get_pg_warnings

    if [[ "$FORMAT" == "json" ]]; then
        format_path_json
    else
        format_path_text
    fi
}

# ── Mode: --check ─────────────────────────────────────────────────────────────

run_check() {
    verbose "Detecting installed GitLab version..."
    FROM_VERSION=$(detect_gitlab_version)

    if [[ -z "$FROM_VERSION" ]]; then
        die "Could not detect installed GitLab version. Use --path --from VERSION instead."
    fi

    log "Detected GitLab version: $FROM_VERSION"

    if [[ -z "$PG_VERSION" ]]; then
        verbose "Detecting PostgreSQL version..."
        PG_VERSION=$(detect_pg_version)
        if [[ -n "$PG_VERSION" ]]; then
            log "Detected PostgreSQL version: $PG_VERSION"
        fi
    fi

    if [[ -z "$TO_VERSION" || "$TO_VERSION" == "latest" ]]; then
        TO_VERSION="$LATEST_VERSION"
    fi

    validate_version "$FROM_VERSION"
    validate_version "$TO_VERSION"

    build_upgrade_path
    get_pg_warnings

    if [[ "$FORMAT" == "json" ]]; then
        format_path_json
    else
        format_path_text
    fi
}

# ── Mode: --list-stops ────────────────────────────────────────────────────────

run_list_stops() {
    print_header
    printf "  ── All Known Required Upgrade Stops ──────────────────────\n\n"
    printf "  ${DIM}Version       Type          Notes                                    PG Required${RESET}\n"
    printf "  ${DIM}────────────  ────────────  ───────────────────────────────────────  ──────────${RESET}\n"

    local entry ver conditional notes
    for entry in "${STOPS[@]}"; do
        IFS='|' read -r ver conditional notes <<< "$entry"

        local gl_major req pg_min pg_max pg_range type_label
        gl_major=$(version_major "$ver")
        req=$(get_pg_req "$gl_major")
        IFS='|' read -r pg_min pg_max <<< "$req"
        if [[ "$pg_min" != "unknown" ]]; then
            pg_range="${pg_min}-${pg_max}"
        else
            pg_range="—"
        fi

        if [[ "$conditional" == "1" ]]; then
            type_label="${YELLOW}conditional${RESET}  "
        else
            type_label="${GREEN}required${RESET}     "
        fi

        printf "  %-12s  %b  %-39s  %s\n" "$ver" "$type_label" "$notes" "$pg_range"
    done

    printf "\n  ── PostgreSQL Version Requirements ───────────────────────\n\n"
    printf "  ${DIM}GitLab     Min PG    Max PG${RESET}\n"
    printf "  ${DIM}─────────  ────────  ────────${RESET}\n"
    local pg_entry
    for pg_entry in "${PG_REQS[@]}"; do
        IFS='|' read -r gl_major pg_min pg_max <<< "$pg_entry"
        printf "  %-9s  %-8s  %s\n" "${gl_major}.x" "$pg_min" "$pg_max"
    done
    printf "\n"
}

# ── Mode: --db-check ──────────────────────────────────────────────────────────

run_db_check() {
    if [[ -z "$PG_VERSION" ]]; then
        verbose "Detecting PostgreSQL version..."
        PG_VERSION=$(detect_pg_version)
        if [[ -z "$PG_VERSION" ]]; then
            die "Could not detect PostgreSQL version. Use --pg-version VERSION."
        fi
        log "Detected PostgreSQL version: $PG_VERSION"
    fi

    if [[ -z "$FROM_VERSION" ]]; then
        FROM_VERSION=$(detect_gitlab_version)
        if [[ -z "$FROM_VERSION" ]]; then
            die "Could not detect GitLab version. Use --from VERSION."
        fi
        log "Detected GitLab version: $FROM_VERSION"
    fi

    if [[ -z "$TO_VERSION" || "$TO_VERSION" == "latest" ]]; then
        TO_VERSION="$LATEST_VERSION"
    fi

    validate_version "$FROM_VERSION"
    validate_version "$TO_VERSION"

    build_upgrade_path
    get_pg_warnings

    if [[ "$FORMAT" == "json" ]]; then
        local pg_json="["
        local first=true
        if [[ ${#PG_WARNINGS[@]} -gt 0 ]]; then
            local warning
            for warning in "${PG_WARNINGS[@]}"; do
                IFS='|' read -r msg action gl_major pg_min pg_max <<< "$warning"
                [[ "$first" == "true" ]] || pg_json+=","
                first=false
                pg_json+=$(printf '{"message":"%s","action":"%s","gitlab_major":"%s","pg_min":"%s","pg_max":"%s"}' \
                    "$msg" "$action" "$gl_major" "$pg_min" "$pg_max")
            done
        fi
        pg_json+="]"
        printf '{"pg_current":"%s","from":"%s","to":"%s","compatible":%s,"warnings":%s}\n' \
            "$PG_VERSION" "$FROM_VERSION" "$TO_VERSION" \
            "$( [[ ${#PG_WARNINGS[@]} -eq 0 ]] && echo "true" || echo "false" )" "$pg_json"
        return
    fi

    print_header
    printf "  ${BOLD}PostgreSQL Compatibility Check${RESET}\n\n"
    printf "  Current GitLab:      %s\n" "$FROM_VERSION"
    printf "  Target GitLab:       %s\n" "$TO_VERSION"
    printf "  Current PostgreSQL:  %s\n\n" "$PG_VERSION"

    local from_major to_major
    from_major=$(version_major "$FROM_VERSION")
    to_major=$(version_major "$TO_VERSION")

    printf "  ── Requirements by GitLab Version ────────────────────────\n\n"
    printf "  ${DIM}GitLab     Min PG    Max PG    Your PG %s    Status${RESET}\n" "$PG_VERSION"
    printf "  ${DIM}─────────  ────────  ────────  ──────────  ──────────${RESET}\n"

    local gl_major
    for (( gl_major = from_major; gl_major <= to_major; gl_major++ )); do
        local req pg_min pg_max status
        req=$(get_pg_req "$gl_major")
        IFS='|' read -r pg_min pg_max <<< "$req"

        if (( PG_VERSION < pg_min )); then
            status="${RED}✗ Too low${RESET}"
        elif (( PG_VERSION > pg_max )); then
            status="${YELLOW}⚠ Too high${RESET}"
        else
            status="${GREEN}✓ OK${RESET}"
        fi
        printf "  %-9s  %-8s  %-8s  %-10s  %b\n" "${gl_major}.x" "$pg_min" "$pg_max" "$PG_VERSION" "$status"
    done

    if [[ ${#PG_WARNINGS[@]} -gt 0 ]]; then
        printf "\n  ── Action Required ───────────────────────────────────────\n\n"
        local warning
        for warning in "${PG_WARNINGS[@]}"; do
            IFS='|' read -r msg action gl_major pg_min pg_max <<< "$warning"
            printf "  ${YELLOW}⚠ %s${RESET}\n" "$msg"
            printf "    → %s\n\n" "$action"
        done
    else
        printf "\n  ${GREEN}✓ PostgreSQL %s is compatible with the full upgrade path.${RESET}\n\n" "$PG_VERSION"
    fi
}

# ── Main ──────────────────────────────────────────────────────────────────────

main() {
    setup_colors
    parse_args "$@"
    setup_colors

    case "$RUN_MODE" in
        path)       run_path ;;
        check)      run_check ;;
        list-stops) run_list_stops ;;
        db-check)   run_db_check ;;
        *)          die "Unknown mode: $RUN_MODE" ;;
    esac
}

main "$@"