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

usage() {
    cat <<'EOF'
Usage:
  analytics-sql [--csv] [--check-only] "SELECT ... LIMIT 20"
  analytics-sql [--csv] [--check-only] < query.sql

Environment:
  ANALYTICS_DB_PASSWORD   Required unless entered at hidden prompt.
  ANALYTICS_DB_USER       Required unless entered at prompt.
  ANALYTICS_DB_HOST       Defaults to online analytics RDS host.
  ANALYTICS_DB_PORT       Defaults to 5432.
  ANALYTICS_DB_NAME       Defaults to data_label_platform_analytics.
  ANALYTICS_DB_SSLMODE    Defaults to prefer.

Access:
  Apply for database credentials at https://applink.feishu.cn/T98bU4gTRcRs

Only SELECT, WITH, EXPLAIN, and SHOW are accepted.
EOF
}

check_only=0
csv=0

while [[ $# -gt 0 ]]; do
    case "$1" in
        --check-only)
            check_only=1
            shift
            ;;
        --csv)
            csv=1
            shift
            ;;
        -h|--help)
            usage
            exit 0
            ;;
        --)
            shift
            break
            ;;
        -*)
            echo "Unknown option: $1" >&2
            usage >&2
            exit 2
            ;;
        *)
            break
            ;;
    esac
done

if [[ $# -gt 0 ]]; then
    sql="$*"
else
    sql="$(cat)"
fi

trimmed_sql="$(printf '%s' "$sql" | sed -e 's/^[[:space:]]*//' -e 's/[[:space:]]*$//')"
if [[ -z "$trimmed_sql" ]]; then
    echo "SQL is empty" >&2
    exit 2
fi

sql_without_final_semicolon="$(printf '%s' "$trimmed_sql" | sed -E 's/[[:space:]]*;[[:space:]]*$//')"
if printf '%s' "$sql_without_final_semicolon" | grep -q ';'; then
    echo "Rejected: multiple SQL statements are not allowed" >&2
    exit 2
fi

if ! printf '%s' "$sql_without_final_semicolon" | grep -Eiq '^(select|with|explain|show)[[:space:]]'; then
    echo "Rejected: only SELECT, WITH, EXPLAIN, or SHOW statements are allowed" >&2
    exit 2
fi

unsafe_re='(^|[^[:alnum:]_])(insert|update|delete|drop|alter|create|truncate|grant|revoke|vacuum|analyze|copy|call|do|merge|refresh|set|reset|listen|notify|lock|comment)([^[:alnum:]_]|$)'
if printf '%s' "$sql_without_final_semicolon" | grep -Eiq "$unsafe_re"; then
    echo "Rejected: SQL contains a blocked keyword for production read-only access" >&2
    exit 2
fi

if [[ "$check_only" -eq 1 ]]; then
    echo "OK: read-only SQL check passed"
    exit 0
fi

psql_bin="${PSQL_BIN:-psql}"
if ! command -v "$psql_bin" >/dev/null 2>&1; then
    if [[ -x /opt/homebrew/opt/libpq/bin/psql ]]; then
        psql_bin="/opt/homebrew/opt/libpq/bin/psql"
    else
        echo "psql not found. Install libpq/postgresql client or set PSQL_BIN." >&2
        exit 127
    fi
fi

host="${ANALYTICS_DB_HOST:-pc-2zeh15g9z2r440cr8.rwlb.rds.aliyuncs.com}"
port="${ANALYTICS_DB_PORT:-5432}"
database="${ANALYTICS_DB_NAME:-data_label_platform_analytics}"
sslmode="${ANALYTICS_DB_SSLMODE:-prefer}"
statement_timeout="${ANALYTICS_DB_STATEMENT_TIMEOUT_MS:-30000}"

if [[ -z "${ANALYTICS_DB_USER:-}" ]]; then
    if [[ -r /dev/tty ]]; then
        read -rp "Analytics DB user: " ANALYTICS_DB_USER </dev/tty
    else
        echo "ANALYTICS_DB_USER is required. Apply for access: https://applink.feishu.cn/T98bU4gTRcRs" >&2
        exit 2
    fi
fi

if [[ -z "${ANALYTICS_DB_PASSWORD:-}" ]]; then
    if [[ -r /dev/tty ]]; then
        read -rsp "Analytics DB password: " ANALYTICS_DB_PASSWORD </dev/tty
    else
        echo "ANALYTICS_DB_PASSWORD is required. Apply for access: https://applink.feishu.cn/T98bU4gTRcRs" >&2
        exit 2
    fi
    echo >&2
fi

format_args=(-P pager=off -P null=NULL)
if [[ "$csv" -eq 1 ]]; then
    format_args+=(--csv)
fi

PGPASSWORD="$ANALYTICS_DB_PASSWORD" \
PGCONNECT_TIMEOUT="${PGCONNECT_TIMEOUT:-10}" \
PGSSLMODE="$sslmode" \
PGOPTIONS="-c default_transaction_read_only=on -c statement_timeout=${statement_timeout} -c idle_in_transaction_session_timeout=30000" \
"$psql_bin" \
    --no-psqlrc \
    --quiet \
    --single-transaction \
    --set=ON_ERROR_STOP=1 \
    --host="$host" \
    --port="$port" \
    --username="$ANALYTICS_DB_USER" \
    --dbname="$database" \
    "${format_args[@]}" \
    --command="$sql_without_final_semicolon" || {
        status=$?
        if [[ "$status" -eq 2 ]]; then
            echo "Connection failed. Check Feilian/飞连 VPN status, then retry." >&2
        fi
        exit "$status"
    }
