#!/usr/bin/env bash

set -euo pipefail

SPRINGTIME_DB_ROOT="${SPRINGTIME_DB_ROOT:-/home/ubuntu/springtime-db}"
SP_DIR="$SPRINGTIME_DB_ROOT/store_procedure"
MYSQL_BIN="${MYSQL_BIN:-mysql}"
INPUT_FILE=""
UPDATE=false

usage() {
    echo "Usage: $0 [--update] [--input FILE]"
    echo "  --update      Update the springtime-db Git repository before applying SQL"
    echo "  --input FILE  Apply one SQL file; also searches under $SP_DIR"
    echo "  no option     Apply every SQL file under $SP_DIR"
}

while [[ $# -gt 0 ]]; do
    case "$1" in
        --input)
            if [[ $# -lt 2 || -z "$2" ]]; then
                echo "Missing file name after --input" >&2
                usage >&2
                exit 2
            fi
            INPUT_FILE="$2"
            shift 2
            ;;
        --input=*)
            INPUT_FILE="${1#--input=}"
            if [[ -z "$INPUT_FILE" ]]; then
                echo "Missing file name after --input=" >&2
                usage >&2
                exit 2
            fi
            shift
            ;;
        --update)
            UPDATE=true
            shift
            ;;
        --help|-h)
            usage
            exit 0
            ;;
        *)
            echo "Unknown option: $1" >&2
            usage >&2
            exit 2
            ;;
    esac
done

if [[ "$UPDATE" == true ]]; then
    if ! command -v git >/dev/null 2>&1; then
        echo "Git not found" >&2
        exit 1
    fi

    if ! git -C "$SPRINGTIME_DB_ROOT" rev-parse --is-inside-work-tree >/dev/null 2>&1; then
        echo "Springtime DB root is not a Git repository: $SPRINGTIME_DB_ROOT" >&2
        exit 1
    fi

    echo "Updating springtime-db repository: $SPRINGTIME_DB_ROOT"
    git -C "$SPRINGTIME_DB_ROOT" pull --ff-only
fi

if ! command -v "$MYSQL_BIN" >/dev/null 2>&1; then
    echo "MySQL client not found: $MYSQL_BIN" >&2
    exit 1
fi

if [[ -n "$INPUT_FILE" ]]; then
    if [[ ! -f "$INPUT_FILE" && -f "$SP_DIR/$INPUT_FILE" ]]; then
        INPUT_FILE="$SP_DIR/$INPUT_FILE"
    fi

    if [[ ! -r "$INPUT_FILE" ]]; then
        echo "SQL file not found or not readable: $INPUT_FILE" >&2
        exit 1
    fi

    echo "Applying stored procedure SQL: $INPUT_FILE"
    "$MYSQL_BIN" -u root -p < "$INPUT_FILE"
    echo "Applied successfully: $INPUT_FILE"
    exit 0
fi

if [[ ! -d "$SP_DIR" ]]; then
    echo "Stored procedure directory not found: $SP_DIR" >&2
    exit 1
fi

shopt -s nullglob
sql_files=("$SP_DIR"/*.sql)
shopt -u nullglob

if [[ ${#sql_files[@]} -eq 0 ]]; then
    echo "No SQL files found under: $SP_DIR" >&2
    exit 1
fi

echo "Applying ${#sql_files[@]} stored procedure SQL files from: $SP_DIR"
printf '  %s\n' "${sql_files[@]}"

for sql_file in "${sql_files[@]}"; do
    cat "$sql_file"
    printf '\n'
done | "$MYSQL_BIN" -u root -p

echo "Applied all stored procedure SQL files successfully"