#!/usr/bin/env bash
# Online ALTER TABLE for tables where `ALGORITHM=INSTANT` is refused
# (e.g. END_USER: its FULLTEXT index `search` blocks INSTANT ADD COLUMN).
#
# Usage:
#   scripts/db/pt-osc.sh TABLE "ALTER SPEC"            # dry-run (default, safe)
#   scripts/db/pt-osc.sh TABLE "ALTER SPEC" --execute  # really run it
#
# Example:
#   scripts/db/pt-osc.sh END_USER "ADD COLUMN IS_VIP TINYINT(1) NULL DEFAULT NULL" --execute
#
# Reads DB_* from the project .env. Requires percona-toolkit:
#   sudo apt install percona-toolkit
#
# Run it BEFORE `php artisan migrate`; the matching migration must be
# idempotent (`Schema::hasColumn`) so it skips the column you added here.
# See docs/ai/coding_rules.md §13.
set -euo pipefail

TABLE="${1:-}"
ALTER="${2:-}"
MODE="${3:---dry-run}"

if [[ -z "$TABLE" || -z "$ALTER" ]]; then
    sed -n '2,17p' "$0"
    exit 1
fi
if [[ "$MODE" != "--dry-run" && "$MODE" != "--execute" ]]; then
    echo "third argument must be --dry-run or --execute" >&2
    exit 1
fi
command -v pt-online-schema-change >/dev/null || { echo "pt-online-schema-change not found: sudo apt install percona-toolkit" >&2; exit 1; }

ROOT="$(cd "$(dirname "$0")/../.." && pwd)"
env_val() { grep -E "^$1=" "$ROOT/.env" | head -1 | cut -d= -f2- | sed -e 's/^"//' -e 's/"$//' -e "s/^'//" -e "s/'$//"; }
DB_HOST="$(env_val DB_HOST)"; DB_PORT="$(env_val DB_PORT)"; DB_DATABASE="$(env_val DB_DATABASE)"
DB_USERNAME="$(env_val DB_USERNAME)"; DB_PASSWORD="$(env_val DB_PASSWORD)"

# Keep the password out of `ps` / shell history.
CNF="$(mktemp)"; chmod 600 "$CNF"; trap 'rm -f "$CNF"' EXIT
printf '[client]\nhost=%s\nport=%s\nuser=%s\npassword=%s\n' "$DB_HOST" "${DB_PORT:-3306}" "$DB_USERNAME" "$DB_PASSWORD" > "$CNF"

# Why these flags (learned on prod 2026-09-08, END_USER 2.5M rows, MySQL 8.4 / pt-osc 3.2.1):
# - recursion-method=none : pt-osc 3.2.1 runs `SHOW SLAVE HOSTS`, removed in MySQL 8.4 -> fatal.
# - foreign_key_checks=0  : MySQL 8 only does ADD FOREIGN KEY INPLACE with checks off; with
#                           checks on, rebuild_constraints would COPY every child table
#                           (END_USER_LAB_ORDER 4.5M rows). With it off, 12 FKs rebuilt in 1s.
# - sql_mode=NO_ENGINE_SUBSTITUTION : legacy rows like DATE_OF_BIRTH='0000-00-00' raise a
#                           warning under the server's strict/NO_ZERO_DATE mode and pt-osc aborts
#                           the copy. Non-strict session copies them byte-for-byte instead.
# Child FK constraint names come back prefixed with "_" (pt-osc behaviour); harmless.

echo "== Queries running > 10s that touch $TABLE (they will block the final table swap):"
mysql --defaults-file="$CNF" -N -e "
    SELECT ID, TIME, USER, LEFT(INFO, 120)
    FROM information_schema.PROCESSLIST
    WHERE COMMAND <> 'Sleep' AND TIME > 10 AND INFO LIKE '%$TABLE%' AND ID <> CONNECTION_ID()" "$DB_DATABASE" || true
echo

exec pt-online-schema-change \
    --defaults-file="$CNF" \
    "D=$DB_DATABASE,t=$TABLE" \
    --alter "$ALTER" \
    --alter-foreign-keys-method=rebuild_constraints \
    --recursion-method=none \
    --chunk-size=1000 --chunk-time=0.5 \
    --max-load="Threads_running=25" --critical-load="Threads_running=50" \
    --set-vars="lock_wait_timeout=5,foreign_key_checks=0,sql_mode=NO_ENGINE_SUBSTITUTION" \
    --tries="create_triggers:10:1,drop_triggers:10:1,swap_tables:10:1,update_foreign_keys:10:1" \
    --progress=time,30 \
    "$MODE"