#!/usr/bin/env bash
#
# fix_id_primary_key.sh
#
# Ensure tables in a database have an AUTO_INCREMENT `id` PRIMARY KEY.
# SAFE BY DEFAULT: this script NEVER drops an existing primary key. It only
# performs changes that cannot lose data, and reports everything else for you
# to decide on manually.
#
# Per-table behaviour:
#   * No PK and no `id` column         -> ADD `id` <type> NOT NULL AUTO_INCREMENT PRIMARY KEY
#   * `id` is sole PK, no AUTO_INCREMENT (integer) -> MODIFY to add AUTO_INCREMENT
#   * No PK, integer `id` exists        -> promote to AUTO_INCREMENT PK *iff* its
#                                          values are unique and non-null (data-checked)
#   * Table already has a DIFFERENT PK  -> reported, NOT modified
#   * `id` exists but non-integer/dups  -> reported, NOT modified
#   * Already correct                   -> skipped
#
# Default is a dry run (report + SQL). Use --apply to execute.
#
# Usage:
#   ./fix_id_primary_key.sh -d mydb -u myuser
#   ./fix_id_primary_key.sh -d mydb -u myuser -o fixes.sql
#   ./fix_id_primary_key.sh -d mydb -u myuser --apply
#   ./fix_id_primary_key.sh -d mydb -u myuser --type "INT UNSIGNED" -t onetable
#
# Options:
#   -d DATABASE  Database to inspect/fix                         [required]
#   -u USER      MySQL user
#   -H HOST      MySQL host
#   -P PORT      MySQL port
#   -t TABLE     Limit to a single table (default: all)
#   --type T     Type for newly-added id columns (default: BIGINT UNSIGNED)
#   -o FILE      Also write the generated SQL to FILE
#   --apply      Execute the SQL (else dry run)
#   -y           Skip confirmation prompt when applying
#
# Notes:
#   * Password is prompted once and passed via a temp 0600 option file.
#   * ALTERs that add/modify a PK rebuild the table -> can be slow and need
#     temp/disk space on large tables. Review the dry-run output first.

set -euo pipefail

DB="" USER="" HOST="" PORT="" ONLY="" OUT="" APPLY=0 ASSUME_YES=0
NEWTYPE="BIGINT UNSIGNED"

die() { echo "Error: $*" >&2; exit 1; }

while [[ $# -gt 0 ]]; do
  case "$1" in
    -d) DB="${2:-}"; shift 2 ;;
    -u) USER="${2:-}"; shift 2 ;;
    -H) HOST="${2:-}"; shift 2 ;;
    -P) PORT="${2:-}"; shift 2 ;;
    -t) ONLY="${2:-}"; shift 2 ;;
    --type) NEWTYPE="${2:-}"; shift 2 ;;
    -o) OUT="${2:-}"; shift 2 ;;
    --apply) APPLY=1; shift ;;
    -y) ASSUME_YES=1; shift ;;
    -h|--help) sed -n '2,52p' "$0" | sed 's/^# \{0,1\}//'; exit 0 ;;
    *) die "unknown argument: $1 (use --help)" ;;
  esac
done

[[ -n "$DB" ]] || die "missing -d DATABASE (use --help)"

WORK="$(mktemp -d)"; CNF="$WORK/my.cnf"
trap 'rm -rf "$WORK"' EXIT

read -rsp "MySQL password (blank if none): " PW; echo >&2
{
  echo "[client]"
  [[ -n "$USER" ]] && echo "user=$USER"
  [[ -n "$PW"   ]] && echo "password=$PW"
  [[ -n "$HOST" ]] && echo "host=$HOST"
  [[ -n "$PORT" ]] && echo "port=$PORT"
} > "$CNF"
chmod 600 "$CNF"; unset PW

mysql_q() { mysql --defaults-extra-file="$CNF" -N -B "$DB" -e "$1"; }
esc() { printf '%s' "$1" | sed "s/'/''/g"; }
DBE="$(esc "$DB")"
ONLYSQL=""
[[ -n "$ONLY" ]] && ONLYSQL="AND c.TABLE_NAME='$(esc "$ONLY")'"

# ---- 1) pull every column for every base table ----------------------------
mysql_q "SELECT c.TABLE_NAME, c.COLUMN_NAME, c.COLUMN_KEY, LOWER(c.DATA_TYPE),
                c.COLUMN_TYPE, c.IS_NULLABLE, c.EXTRA
         FROM information_schema.COLUMNS c
         JOIN information_schema.TABLES t
           ON t.TABLE_SCHEMA=c.TABLE_SCHEMA AND t.TABLE_NAME=c.TABLE_NAME
         WHERE c.TABLE_SCHEMA='$DBE' AND t.TABLE_TYPE='BASE TABLE' $ONLYSQL
         ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION;" > "$WORK/cols.tsv"

[[ -s "$WORK/cols.tsv" ]] || die "no base tables found in '$DB'${ONLY:+ matching '$ONLY'}"

: > "$WORK/fixes.sql"
: > "$WORK/report.tsv"
: > "$WORK/bcand.tsv"

# ---- 2) classify each table ------------------------------------------------
awk -F'\t' -v newtype="$NEWTYPE" \
    -v SQL="$WORK/fixes.sql" -v RPT="$WORK/report.tsv" -v BC="$WORK/bcand.tsv" '
  function isint(dt){ return (dt ~ /^(tinyint|smallint|mediumint|int|integer|bigint)$/) }
  function flush(t,   pk_is_id_only) {
    if (t=="") return
    pk_is_id_only = (pri_count==1 && id_key=="PRI")
    if (pri_count>0) {                                   # table HAS a primary key
      if (pk_is_id_only) {
        if (id_ai) {
          print "OK\t" t "\tid is already AUTO_INCREMENT PRIMARY KEY" >> RPT
        } else if (isint(id_dt)) {
          print "ALTER TABLE `" t "` MODIFY `id` " id_ctype " NOT NULL AUTO_INCREMENT;" >> SQL
          print "SET-AI\t" t "\tid is sole PK; adding AUTO_INCREMENT" >> RPT
        } else {
          print "MANUAL\t" t "\tid is sole PK but type (" id_dt ") is not integer" >> RPT
        }
      } else {
        print "MANUAL\t" t "\thas a different PRIMARY KEY (" pk_cols "); not modified" >> RPT
      }
    } else {                                             # table has NO primary key
      if (!has_id) {
        print "ALTER TABLE `" t "` ADD COLUMN `id` " newtype " NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;" >> SQL
        print "ADD-ID\t" t "\tno PK and no id column; adding id PK" >> RPT
      } else if (isint(id_dt)) {
        print t "\t" id_ctype >> BC                      # needs data uniqueness check
      } else {
        print "MANUAL\t" t "\tid column exists but type (" id_dt ") is not integer" >> RPT
      }
    }
  }
  {
    t=$1
    if (t!=cur) { flush(cur); cur=t; pri_count=0; pk_cols=""; has_id=0; id_key=""; id_dt=""; id_ctype=""; id_null=""; id_ai=0 }
    col=$2; key=$3; dt=$4; ctype=$5; nullable=$6; extra=(NF>=7?$7:"")
    if (key=="PRI") { pri_count++; pk_cols=(pk_cols==""?col:pk_cols ", " col) }
    if (col=="id") {
      has_id=1; id_key=key; id_dt=dt; id_ctype=ctype; id_null=nullable
      if (tolower(extra) ~ /auto_increment/) id_ai=1
    }
  }
  END { flush(cur) }
' "$WORK/cols.tsv"

# ---- 3) data-check the "promote existing id" candidates --------------------
if [[ -s "$WORK/bcand.tsv" ]]; then
  while IFS=$'\t' read -r tbl ctype; do
    [[ -n "$tbl" ]] || continue
    safe="$(mysql_q "SELECT COUNT(*)=COUNT(DISTINCT \`id\`) FROM \`$tbl\`;")"
    if [[ "$safe" == "1" ]]; then
      printf 'ALTER TABLE `%s` MODIFY `id` %s NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (`id`);\n' \
        "$tbl" "$ctype" >> "$WORK/fixes.sql"
      printf 'PROMOTE\t%s\tid values unique & non-null; promoting to AUTO_INCREMENT PK\n' "$tbl" >> "$WORK/report.tsv"
    else
      printf 'SKIP\t%s\tid has NULLs or duplicate values; fix data before promoting\n' "$tbl" >> "$WORK/report.tsv"
    fi
  done < "$WORK/bcand.tsv"
fi

# ---- 4) report -------------------------------------------------------------
echo "===== Report (database: $DB) ====="
if [[ -s "$WORK/report.tsv" ]]; then
  sort "$WORK/report.tsv" | awk -F'\t' '{ printf "  %-9s %-32s %s\n", $1, $2, $3 }'
else
  echo "  (no tables)"
fi
echo
echo "  Legend: ADD-ID/SET-AI/PROMOTE = will change ; OK = fine ; SKIP/MANUAL = left for you"

# ---- 5) SQL + optional apply ----------------------------------------------
if [[ ! -s "$WORK/fixes.sql" ]]; then
  echo; echo "Nothing to change automatically." ; exit 0
fi

echo; echo "===== Generated SQL ====="
cat "$WORK/fixes.sql"

[[ -n "$OUT" ]] && { cp "$WORK/fixes.sql" "$OUT"; echo "-- Written to: $OUT" >&2; }

if [[ "$APPLY" -eq 1 ]]; then
  if [[ "$ASSUME_YES" -ne 1 ]]; then
    echo >&2
    echo "WARNING: these ALTERs rebuild tables and modify primary keys." >&2
    read -r -p "Apply the above to '$DB'? [y/N] " ans
    [[ "$ans" == "y" || "$ans" == "Y" ]] || { echo "Aborted." >&2; exit 0; }
  fi
  echo "-- Applying..." >&2
  mysql --defaults-extra-file="$CNF" "$DB" < "$WORK/fixes.sql"
  echo "-- Done." >&2
fi