#!/bin/bash
#
# mysql2json.sh - Export a MySQL table (or arbitrary rows of it) to JSON.
#
# Dependencies: bash + the `mysql` client + awk. That's it.
#   No jq, no python, no perl, no mysqldump.
#
# The trick: MySQL itself builds the JSON (JSON_OBJECT), so all quoting,
# escaping and UTF-8 handling is done server-side and is always valid JSON.
# The shell only stitches the per-row objects into an array.
#
# Requires MySQL >= 5.7 (or MariaDB >= 10.2) for JSON_OBJECT().
#
# Examples:
#   MYSQL_PWD=pokpok ./mysql2json.sh -h babetgoug.info -u root \
#       -d crossfit -t SugarWodImportedWorkouts -o workouts.json
#
#   # just look at the schema
#   MYSQL_PWD=pokpok ./mysql2json.sh -h babetgoug.info -u root \
#       -d crossfit -t SugarWodImportedWorkouts --schema
#
#   # newline-delimited JSON, filtered, streamed
#   MYSQL_PWD=pokpok ./mysql2json.sh -h babetgoug.info -u root \
#       -d crossfit -t SugarWodImportedWorkouts \
#       --ndjson --where "date >= '2025-01-01'" --order-by "date DESC"

set -euo pipefail

PROG=${0##*/}

HOST=127.0.0.1
PORT=3306
USER=$USER
SOCKET=
DB=
TABLE=
WHERE=
ORDER_BY=
LIMIT=
OUT=
FORMAT=array      # array | ndjson
SCHEMA_ONLY=0
ASK_PASS=0

die() { printf '%s: %s\n' "$PROG" "$*" >&2; exit 1; }

usage() {
  sed -n '2,30p' "$0" | sed 's/^# \{0,1\}//'
  cat <<'EOF'

Options:
  -h, --host HOST        MySQL host (default 127.0.0.1)
  -P, --port PORT        MySQL port (default 3306)
  -S, --socket PATH      Unix socket (overrides host/port)
  -u, --user USER        MySQL user (default $USER)
  -p, --ask-pass         Prompt for the password interactively
  -d, --database DB      Database name                     [required]
  -t, --table TABLE      Table name                        [required]
      --where SQL        WHERE clause (without the WHERE keyword)
      --order-by SQL     ORDER BY clause (without the keyword)
      --limit N          LIMIT N
      --ndjson           One JSON object per line instead of a JSON array
      --schema           Print the table schema and exit (no data export)
  -o, --output FILE      Write to FILE instead of stdout
      --help             This help

Password handling (never pass it on the command line - it shows up in `ps`):
  * export MYSQL_PWD=...       (simplest, used by this script automatically)
  * or -p to be prompted
  * or put it in ~/.my.cnf:
        [client]
        user=root
        password=...
    and chmod 600 ~/.my.cnf
EOF
}

while [[ $# -gt 0 ]]; do
  case $1 in
    -h|--host)      HOST=$2; shift 2 ;;
    -P|--port)      PORT=$2; shift 2 ;;
    -S|--socket)    SOCKET=$2; shift 2 ;;
    -u|--user)      USER_ARG=$2; USER=$2; shift 2 ;;
    -p|--ask-pass)  ASK_PASS=1; shift ;;
    -d|--database)  DB=$2; shift 2 ;;
    -t|--table)     TABLE=$2; shift 2 ;;
    --where)        WHERE=$2; shift 2 ;;
    --order-by)     ORDER_BY=$2; shift 2 ;;
    --limit)        LIMIT=$2; shift 2 ;;
    --ndjson)       FORMAT=ndjson; shift ;;
    --schema)       SCHEMA_ONLY=1; shift ;;
    -o|--output)    OUT=$2; shift 2 ;;
    --help)         usage; exit 0 ;;
    *)              die "unknown option '$1' (try --help)" ;;
  esac
done

command -v mysql >/dev/null || die "the 'mysql' client is not installed (apt install default-mysql-client)"
[[ -n $DB    ]] || die "missing -d/--database"
[[ -n $TABLE ]] || die "missing -t/--table"

# Identifiers are interpolated into SQL, so keep them boring.
[[ $DB    =~ ^[A-Za-z0-9_$]+$ ]] || die "refusing unusual database name: $DB"
[[ $TABLE =~ ^[A-Za-z0-9_$]+$ ]] || die "refusing unusual table name: $TABLE"
[[ -z $LIMIT || $LIMIT =~ ^[0-9]+$ ]] || die "--limit must be a number"

conn=(--user="$USER" --default-character-set=utf8mb4)
#if [[ -n $SOCKET ]]; then
#  conn+=(--socket="$SOCKET")
#else
 conn+=(--host="$HOST" --port="$PORT" --protocol=TCP)
#fi
(( ASK_PASS )) && conn+=(--password)

run_sql() {  # tabular output, for humans
  mysql "${conn[@]}" --table -e "$1"
}
run_sql_raw() {  # one raw value per line, for machines
  mysql "${conn[@]}" --batch --raw --skip-column-names --quick -e "$1"
}

if (( SCHEMA_ONLY )); then
  run_sql "SHOW FULL COLUMNS FROM \`$DB\`.\`$TABLE\`;"
  run_sql "SHOW INDEX FROM \`$DB\`.\`$TABLE\`;"
  mysql "${conn[@]}" --batch --raw --skip-column-names \
    -e "SHOW CREATE TABLE \`$DB\`.\`$TABLE\`;" | cut -f2-
  exit 0
fi

# Fail early with a clear message instead of a cryptic SQL error.
exists=$(run_sql_raw "SELECT COUNT(*) FROM information_schema.TABLES
                      WHERE TABLE_SCHEMA='$DB' AND TABLE_NAME='$TABLE';")
[[ $exists == 1 ]] || die "table \`$DB\`.\`$TABLE\` not found (or not visible to '$USER')"

tail_sql=""
[[ -n $WHERE    ]] && tail_sql+=" WHERE $WHERE"
[[ -n $ORDER_BY ]] && tail_sql+=" ORDER BY $ORDER_BY"
[[ -n $LIMIT    ]] && tail_sql+=" LIMIT $LIMIT"

# Build "colname", value pairs for JSON_OBJECT(), with sane type coercions:
#   - temporal types -> CHAR, so you get "2025-01-31" not a weird JSON scalar
#   - DECIMAL        -> CHAR, so money-like values keep full precision
#   - BIT/binary     -> base64, so invalid UTF-8 can't break the JSON
#   - everything else passes through (INT -> number, NULL -> null)
read -r -d '' export_sql <<SQL || true
SET SESSION group_concat_max_len = 16777216;
SET @cols := (
  SELECT GROUP_CONCAT(
           CONCAT(QUOTE(COLUMN_NAME), ',',
             CASE
               WHEN DATA_TYPE IN ('date','datetime','timestamp','time','year')
                 THEN CONCAT('CAST(\`', COLUMN_NAME, '\` AS CHAR)')
               WHEN DATA_TYPE IN ('decimal','newdecimal')
                 THEN CONCAT('CAST(\`', COLUMN_NAME, '\` AS CHAR)')
               WHEN DATA_TYPE IN ('binary','varbinary','blob','tinyblob',
                                  'mediumblob','longblob','bit')
                 THEN CONCAT('TO_BASE64(\`', COLUMN_NAME, '\`)')
               ELSE CONCAT('\`', COLUMN_NAME, '\`')
             END)
           ORDER BY ORDINAL_POSITION SEPARATOR ',')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = '$DB' AND TABLE_NAME = '$TABLE'
);
SET @q := CONCAT('SELECT JSON_OBJECT(', @cols, ') FROM \`$DB\`.\`$TABLE\`$tail_sql');
PREPARE st FROM @q;
EXECUTE st;
DEALLOCATE PREPARE st;
SQL

# JSON_OBJECT() never emits a literal newline or tab (it escapes them), so
# "one row == one line" holds. --raw is required so the client does not
# double-escape the backslashes that are already part of the JSON.
emit() {
  if [[ $FORMAT == ndjson ]]; then
    cat
  else
    awk '
      NR == 1 { printf "[\n" }
      NR  > 1 { printf ",\n" }
              { printf "  %s", $0 }
      END     { if (NR == 0) print "[]"; else printf "\n]\n" }
    '
  fi
}

if [[ -n $OUT ]]; then
  run_sql_raw "$export_sql" | emit > "$OUT"
  printf '%s: wrote %s\n' "$PROG" "$OUT" >&2
else
  run_sql_raw "$export_sql" | emit
fi