#!/bin/bash
################################################################################
# Script Name: mysql-exporter.sh
# Version: 1.0
# Description: Prometheus textfile exporter for MySQL — server status,
#              connections, QPS, slow queries, buffer pool, replication,
#              thread counts, and table lock waits via mysql CLI
#
# Author: Phil Connor
# Contact: contact@mylinux.work
# Website: https://mylinux.work
# License: MIT
#
# Prerequisites:
#   - mysql CLI client
#   - Credentials via ~/.my.cnf or --user/--password flags
#   - Network access to MySQL server
#   - netcat (nc) for HTTP mode
#
# Usage:
#   # Output to stdout
#   ./mysql-exporter.sh
#
#   # HTTP server mode
#   ./mysql-exporter.sh --http -p 9207
#
#   # Textfile collector mode
#   ./mysql-exporter.sh --textfile
#
# Metrics Exported:
#   - mysql_overview_up - Exporter status (1=up, 0=down)
#   - mysql_overview_version_info{version} - Server version
#   - mysql_overview_uptime_seconds - Server uptime
#   - mysql_overview_active_connections - Current active connections
#   - mysql_overview_max_connections - Maximum allowed connections
#   - mysql_overview_aborted_connections_total - Aborted connections
#   - mysql_overview_aborted_clients_total - Aborted clients
#   - mysql_overview_queries_total - Total queries executed
#   - mysql_overview_slow_queries_total - Total slow queries
#   - mysql_overview_tmp_disk_tables_total - Temp tables on disk
#   - mysql_overview_threads_running - Running threads
#   - mysql_overview_threads_cached - Cached threads
#   - mysql_overview_threads_connected - Connected threads
#   - mysql_overview_buffer_pool_size_bytes - Buffer pool total size
#   - mysql_overview_buffer_pool_used_bytes - Buffer pool used size
#   - mysql_overview_buffer_pool_hit_rate - Buffer pool hit rate
#   - mysql_overview_table_lock_waits_total - Table lock waits
#   - mysql_overview_slave_running - Replication running status
#   - mysql_overview_seconds_behind_master - Replication lag
#   - mysql_overview_relay_log_space_bytes - Relay log space
#   - mysql_overview_binlog_size_bytes - Binary log size
#   - mysql_overview_exporter_duration_seconds - Script execution time
#   - mysql_overview_exporter_last_run_timestamp - Last successful run
#
# Configuration:
#   Default HTTP port: 9207
#   Textfile directory: /var/lib/node_exporter
#
################################################################################

# ============================================================================
# CONFIGURATION VARIABLES
# ============================================================================

TEXTFILE_DIR="/var/lib/node_exporter"
OUTPUT_FILE=""
HTTP_MODE=false
HTTP_PORT=9207
MYSQL_USER=""
MYSQL_PASSWORD=""
MYSQL_HOST="127.0.0.1"
MYSQL_PORT="3306"

# ============================================================================
# HELPER FUNCTIONS
# ============================================================================

show_usage() {
    cat <<EOF
Usage: $0 [OPTIONS]

Export MySQL statistics as Prometheus metrics.

MODES:
    --textfile      Write to node_exporter textfile collector
    --http          Run HTTP server on port $HTTP_PORT

OPTIONS:
    -p, --port      HTTP port (default: 9207)
    -o, --output    Output file path
    --user          MySQL username
    --password      MySQL password
    --host          MySQL host (default: 127.0.0.1)
    --mysql-port    MySQL port (default: 3306)

EXAMPLES:
    $0 --textfile                              # Write to textfile collector
    $0 --http --port 9207                      # Run HTTP server
    $0 --user monitoring --password secret     # Explicit credentials
    $0 -o /tmp/mysql.prom                      # Write to custom file

EOF
    exit 0
}

parse_args() {
    while [[ $# -gt 0 ]]; do
        case $1 in
            -h|--help) show_usage ;;
            --textfile) OUTPUT_FILE="$TEXTFILE_DIR/mysql.prom"; shift ;;
            --http) HTTP_MODE=true; shift ;;
            -p|--port) HTTP_PORT="$2"; shift 2 ;;
            -o|--output) OUTPUT_FILE="$2"; shift 2 ;;
            --user) MYSQL_USER="$2"; shift 2 ;;
            --password) MYSQL_PASSWORD="$2"; shift 2 ;;
            --host) MYSQL_HOST="$2"; shift 2 ;;
            --mysql-port) MYSQL_PORT="$2"; shift 2 ;;
            *) echo "Unknown option: $1" >&2; exit 1 ;;
        esac
    done
}

# Build mysql command with optional credentials
build_mysql_cmd() {
    local cmd="mysql -h $MYSQL_HOST -P $MYSQL_PORT"
    [ -n "$MYSQL_USER" ] && cmd="$cmd -u $MYSQL_USER"
    [ -n "$MYSQL_PASSWORD" ] && cmd="$cmd -p$MYSQL_PASSWORD"
    cmd="$cmd --batch --skip-column-names"
    echo "$cmd"
}

# Check if mysql client is available and server is reachable
check_mysql() {
    if ! command -v mysql >/dev/null 2>&1; then
        echo "ERROR: mysql client not found" >&2
        return 1
    fi

    local mysql_cmd
    mysql_cmd=$(build_mysql_cmd)
    if ! $mysql_cmd -e "SELECT 1" >/dev/null 2>&1; then
        echo "ERROR: Cannot connect to MySQL server" >&2
        return 1
    fi

    return 0
}

# Execute a MySQL query and return the result
mysql_query() {
    local query="$1"
    local mysql_cmd
    mysql_cmd=$(build_mysql_cmd)
    $mysql_cmd -e "$query" 2>/dev/null
}

# Get a specific global status variable
get_status_var() {
    local var_name="$1"
    mysql_query "SHOW GLOBAL STATUS LIKE '$var_name'" | awk '{print $2}'
}

# Get a specific global variable
get_variable() {
    local var_name="$1"
    mysql_query "SHOW GLOBAL VARIABLES LIKE '$var_name'" | awk '{print $2}'
}

# ============================================================================
# METRIC GENERATION
# ============================================================================

generate_metrics() {
    local script_start
    script_start=$(date +%s)

    if ! check_mysql; then
        cat <<EOF
# HELP mysql_overview_up MySQL exporter status
# TYPE mysql_overview_up gauge
mysql_overview_up 0
EOF
        return
    fi

    # Gather all metrics
    local version uptime max_conn active_conn
    version=$(mysql_query "SELECT VERSION()" 2>/dev/null)
    uptime=$(get_status_var "Uptime")
    max_conn=$(get_variable "max_connections")
    active_conn=$(get_status_var "Threads_connected")

    local queries slow_queries tmp_disk_tables
    queries=$(get_status_var "Queries")
    slow_queries=$(get_status_var "Slow_queries")
    tmp_disk_tables=$(get_status_var "Created_tmp_disk_tables")

    local threads_running threads_cached threads_connected
    threads_running=$(get_status_var "Threads_running")
    threads_cached=$(get_status_var "Threads_cached")
    threads_connected=$(get_status_var "Threads_connected")

    local bp_size bp_pages_total bp_pages_free bp_read_requests bp_reads
    bp_size=$(get_variable "innodb_buffer_pool_size")
    bp_pages_total=$(get_status_var "Innodb_buffer_pool_pages_total")
    bp_pages_free=$(get_status_var "Innodb_buffer_pool_pages_free")
    bp_read_requests=$(get_status_var "Innodb_buffer_pool_read_requests")
    bp_reads=$(get_status_var "Innodb_buffer_pool_reads")

    local table_lock_waits aborted_conn aborted_clients
    table_lock_waits=$(get_status_var "Table_locks_waited")
    aborted_conn=$(get_status_var "Aborted_connects")
    aborted_clients=$(get_status_var "Aborted_clients")

    # Calculate buffer pool metrics
    local bp_used_pages bp_used_bytes bp_hit_rate
    bp_pages_total=${bp_pages_total:-0}
    bp_pages_free=${bp_pages_free:-0}
    bp_used_pages=$((bp_pages_total - bp_pages_free))
    # InnoDB page size is 16384 bytes by default
    bp_used_bytes=$((bp_used_pages * 16384))

    bp_read_requests=${bp_read_requests:-0}
    bp_reads=${bp_reads:-0}
    if [ "$bp_read_requests" -gt 0 ] 2>/dev/null; then
        bp_hit_rate=$(awk "BEGIN {printf \"%.6f\", 1 - ($bp_reads / $bp_read_requests)}" 2>/dev/null || echo "0")
    else
        bp_hit_rate="1.000000"
    fi

    # Replication status
    local slave_running seconds_behind relay_log_space
    local repl_output
    repl_output=$(mysql_query "SHOW SLAVE STATUS\G" 2>/dev/null)
    if [ -n "$repl_output" ]; then
        slave_running=$(echo "$repl_output" | grep "Slave_SQL_Running:" | awk '{print $2}')
        [ "$slave_running" = "Yes" ] && slave_running=1 || slave_running=0
        seconds_behind=$(echo "$repl_output" | grep "Seconds_Behind_Master:" | awk '{print $2}')
        [ "$seconds_behind" = "NULL" ] && seconds_behind=-1
        relay_log_space=$(echo "$repl_output" | grep "Relay_Log_Space:" | awk '{print $2}')
    else
        slave_running=0
        seconds_behind=-1
        relay_log_space=0
    fi

    # Binary log size
    local binlog_size
    binlog_size=$(mysql_query "SHOW BINARY LOGS" 2>/dev/null | awk '{sum += $2} END {print sum+0}')

    # Output all metrics
    cat <<EOF
# HELP mysql_overview_up MySQL exporter status
# TYPE mysql_overview_up gauge
mysql_overview_up 1

# HELP mysql_overview_version_info MySQL server version
# TYPE mysql_overview_version_info gauge
mysql_overview_version_info{version="${version:-unknown}"} 1

# HELP mysql_overview_uptime_seconds MySQL server uptime in seconds
# TYPE mysql_overview_uptime_seconds gauge
mysql_overview_uptime_seconds ${uptime:-0}

# HELP mysql_overview_active_connections Current active connections
# TYPE mysql_overview_active_connections gauge
mysql_overview_active_connections ${active_conn:-0}

# HELP mysql_overview_max_connections Maximum allowed connections
# TYPE mysql_overview_max_connections gauge
mysql_overview_max_connections ${max_conn:-0}

# HELP mysql_overview_aborted_connections_total Total aborted connections
# TYPE mysql_overview_aborted_connections_total counter
mysql_overview_aborted_connections_total ${aborted_conn:-0}

# HELP mysql_overview_aborted_clients_total Total aborted clients
# TYPE mysql_overview_aborted_clients_total counter
mysql_overview_aborted_clients_total ${aborted_clients:-0}

# HELP mysql_overview_queries_total Total queries executed
# TYPE mysql_overview_queries_total counter
mysql_overview_queries_total ${queries:-0}

# HELP mysql_overview_slow_queries_total Total slow queries
# TYPE mysql_overview_slow_queries_total counter
mysql_overview_slow_queries_total ${slow_queries:-0}

# HELP mysql_overview_tmp_disk_tables_total Temp tables created on disk
# TYPE mysql_overview_tmp_disk_tables_total counter
mysql_overview_tmp_disk_tables_total ${tmp_disk_tables:-0}

# HELP mysql_overview_threads_running Currently running threads
# TYPE mysql_overview_threads_running gauge
mysql_overview_threads_running ${threads_running:-0}

# HELP mysql_overview_threads_cached Cached threads
# TYPE mysql_overview_threads_cached gauge
mysql_overview_threads_cached ${threads_cached:-0}

# HELP mysql_overview_threads_connected Connected threads
# TYPE mysql_overview_threads_connected gauge
mysql_overview_threads_connected ${threads_connected:-0}

# HELP mysql_overview_buffer_pool_size_bytes InnoDB buffer pool total size
# TYPE mysql_overview_buffer_pool_size_bytes gauge
mysql_overview_buffer_pool_size_bytes ${bp_size:-0}

# HELP mysql_overview_buffer_pool_used_bytes InnoDB buffer pool used size
# TYPE mysql_overview_buffer_pool_used_bytes gauge
mysql_overview_buffer_pool_used_bytes ${bp_used_bytes:-0}

# HELP mysql_overview_buffer_pool_hit_rate InnoDB buffer pool hit rate
# TYPE mysql_overview_buffer_pool_hit_rate gauge
mysql_overview_buffer_pool_hit_rate $bp_hit_rate

# HELP mysql_overview_table_lock_waits_total Table lock waits
# TYPE mysql_overview_table_lock_waits_total counter
mysql_overview_table_lock_waits_total ${table_lock_waits:-0}

# HELP mysql_overview_slave_running Replication slave running status
# TYPE mysql_overview_slave_running gauge
mysql_overview_slave_running $slave_running

# HELP mysql_overview_seconds_behind_master Replication lag in seconds
# TYPE mysql_overview_seconds_behind_master gauge
mysql_overview_seconds_behind_master ${seconds_behind:--1}

# HELP mysql_overview_relay_log_space_bytes Relay log space usage
# TYPE mysql_overview_relay_log_space_bytes gauge
mysql_overview_relay_log_space_bytes ${relay_log_space:-0}

# HELP mysql_overview_binlog_size_bytes Total binary log size
# TYPE mysql_overview_binlog_size_bytes gauge
mysql_overview_binlog_size_bytes ${binlog_size:-0}

EOF

    # Exporter runtime
    local script_end script_duration
    script_end=$(date +%s)
    script_duration=$((script_end - script_start))

    cat <<EOF
# HELP mysql_overview_exporter_duration_seconds Time to generate all metrics
# TYPE mysql_overview_exporter_duration_seconds gauge
mysql_overview_exporter_duration_seconds $script_duration

# HELP mysql_overview_exporter_last_run_timestamp Unix timestamp of last successful run
# TYPE mysql_overview_exporter_last_run_timestamp gauge
mysql_overview_exporter_last_run_timestamp $script_end

EOF
}

# ============================================================================
# HTTP SERVER MODE
# ============================================================================

run_http_server() {
    echo "Starting MySQL exporter on port $HTTP_PORT..." >&2

    if ! command -v nc >/dev/null 2>&1; then
        echo "ERROR: netcat (nc) required for HTTP mode" >&2
        exit 1
    fi

    while true; do
        {
            read -r request
            if [[ "$request" =~ ^GET\ /metrics ]]; then
                echo -e "HTTP/1.1 200 OK\r\nContent-Type: text/plain; version=0.0.4\r\n\r"
                generate_metrics
            else
                echo -e "HTTP/1.1 200 OK\r\nContent-Type: text/html\r\n\r"
                cat <<EOF
<!DOCTYPE html>
<html>
<head><title>MySQL Exporter</title></head>
<body>
<h1>MySQL Prometheus Exporter</h1>
<p><a href="/metrics">Metrics</a></p>
</body>
</html>
EOF
            fi
        } | nc -l -p "$HTTP_PORT" -q 1 2>/dev/null
    done
}

# ============================================================================
# MAIN EXECUTION
# ============================================================================

main() {
    parse_args "$@"

    if [ "$HTTP_MODE" = true ]; then
        run_http_server
    elif [ -n "$OUTPUT_FILE" ]; then
        local output_dir
        output_dir="$(dirname "$OUTPUT_FILE")"
        mkdir -p "$output_dir"

        local temp_file
        temp_file=$(mktemp "${output_dir}/.mysql_metrics.XXXXXX")

        if ! generate_metrics > "$temp_file" 2>/dev/null; then
            rm -f "$temp_file"
            echo "ERROR: Failed to generate metrics" >&2
            exit 1
        fi

        local file_lines
        file_lines=$(wc -l < "$temp_file" 2>/dev/null || echo 0)

        if [ "$file_lines" -lt 10 ]; then
            rm -f "$temp_file"
            echo "ERROR: Metrics file too small ($file_lines lines), keeping previous" >&2
            exit 1
        fi

        chmod 644 "$temp_file"
        mv -f "$temp_file" "$OUTPUT_FILE"

        echo "Metrics written to $OUTPUT_FILE ($file_lines lines)" >&2
    else
        generate_metrics
    fi
}

main "$@"