#!/usr/bin/env zsh

SESSION_NAME="crussell-dev"
SEED_SCRIPT="/tmp/seed_data.sh"

setopt NO_UNSET
setopt PIPE_FAIL
setopt ERR_EXIT

# --- UI Helpers ---
C_RESET=$'\033[0m'
C_GREEN=$'\033[32m'
C_RED=$'\033[31m'
C_BLUE=$'\033[34m'
C_YELLOW=$'\033[33m'

log_info() { echo "🔹 $1" }
log_success() { echo "✅ $1" }
log_error() { echo "❌ $1" }
log_step() { echo "▶️  $1" }

# --- 1. Environment Setup ---
if [ -f .env ]; then
  set -a
  source .env
  set +a
  log_success "Loaded environment variables"
else
  log_error ".env file not found!"
  exit 1
fi

# --- 2. Docker Checks ---
if ! docker info > /dev/null 2>&1; then
  log_info "Docker daemon not running. Starting..."
  sudo systemctl start docker
  sleep 2
  if ! docker info > /dev/null 2>&1; then
    log_error "Failed to start Docker."
    exit 1
  fi
  log_success "Docker started"
fi

# --- 3. Database Reset ---
log_step "Resetting PostgreSQL..."
docker compose down -v postgres > /dev/null 2>&1
docker compose up postgres -d > /dev/null 2>&1
log_success "PostgreSQL reset complete"

# --- 3a. Wait for PostgreSQL to be ready ---
log_step "Waiting for PostgreSQL to initialize..."
MAX_STARTUP_ATTEMPTS=30
STARTUP_ATTEMPT=0
while [ $STARTUP_ATTEMPT -lt $MAX_STARTUP_ATTEMPTS ]; do
  if docker exec postgres psql -U myuser -d mydb -c "SELECT 1;" > /dev/null 2>&1; then
    log_success "PostgreSQL is ready"
    break
  fi
  STARTUP_ATTEMPT=$((STARTUP_ATTEMPT+1))
  sleep 1
done
if [ $STARTUP_ATTEMPT -eq $MAX_STARTUP_ATTEMPTS ]; then
  log_error "PostgreSQL failed to start after $MAX_STARTUP_ATTEMPTS attempts"
  exit 1
fi

# --- 3a-extra. Additional wait for full PostgreSQL initialization ---
log_step "Allowing PostgreSQL to fully initialize..."
sleep 5

# --- 3b. Create Test Database ---
log_step "Cleaning up stale test databases..."
docker exec postgres psql -U myuser -d mydb -t -c "
  SELECT datname FROM pg_database WHERE datname LIKE 'crussell_test%';
" 2>&1 | grep crussell_test | while read -r dbname; do
  docker exec postgres psql -U myuser -d mydb -c "
    SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '$dbname' AND pid != pg_backend_pid();
    DROP DATABASE IF EXISTS \"$dbname\";
  " 2>&1
done
log_success "Test databases cleaned"

# --- 3c. Rustfs (wipe data for fresh start) ---
log_step "Wiping Rustfs (S3 storage)..."
docker compose stop rustfs > /dev/null 2>&1 || true
docker compose rm -f rustfs > /dev/null 2>&1 || true
docker volume rm crussell_rustfs_data > /dev/null 2>&1 || true
docker compose up rustfs -d > /dev/null 2>&1
log_success "Rustfs wiped and restarted"
sleep 2

# --- 3f. SabreDAV (start early to create tables) ---
log_step "Starting SabreDAV (CardDAV/CalDAV)..."
docker compose up sabredav -d > /dev/null 2>&1
log_success "SabreDAV started"
sleep 3

# --- 4. Tmux Session Setup ---
if tmux has-session -t $SESSION_NAME 2>/dev/null; then
  log_info "Killing existing tmux session..."
  tmux kill-session -t $SESSION_NAME
fi

log_step "Starting tmux session '$SESSION_NAME'..."
tmux new-session -d -s $SESSION_NAME -n "Workspace"

# Pass env vars to tmux session (must be after session creation)
tmux set-environment -t $SESSION_NAME POSTGRES_USER "$POSTGRES_USER"
tmux set-environment -t $SESSION_NAME POSTGRES_PASSWORD "$POSTGRES_PASSWORD"
tmux set-environment -t $SESSION_NAME POSTGRES_DB "$POSTGRES_DB"
tmux set-environment -t $SESSION_NAME POSTGRES_HOST "$POSTGRES_HOST"
tmux set-environment -t $SESSION_NAME POSTGRES_PORT "$POSTGRES_PORT"
tmux set-environment -t $SESSION_NAME JWT_SECRET_KEY "$JWT_SECRET_KEY"
tmux set-environment -t $SESSION_NAME S3_ENDPOINT "$S3_ENDPOINT"
tmux set-environment -t $SESSION_NAME S3_PUBLIC_URL "$S3_PUBLIC_URL"
tmux set-environment -t $SESSION_NAME S3_ACCESS_KEY "$S3_ACCESS_KEY"
tmux set-environment -t $SESSION_NAME S3_SECRET_KEY "$S3_SECRET_KEY"
tmux set-environment -t $SESSION_NAME S3_BUCKET "$S3_BUCKET"
tmux set-environment -t $SESSION_NAME AWS_REGION "$AWS_REGION"
tmux set-environment -t $SESSION_NAME VITE_BACKEND_URL "$VITE_BACKEND_URL"
tmux set-environment -t $SESSION_NAME SQUARE_ACCESS_TOKEN "${SQUARE_ACCESS_TOKEN:-}"
tmux set-environment -t $SESSION_NAME SQUARE_LOCATION_ID "${SQUARE_LOCATION_ID:-}"
tmux set-environment -t $SESSION_NAME SQUARE_ENVIRONMENT "${SQUARE_ENVIRONMENT:-mock}"
tmux set-environment -t $SESSION_NAME SQUARE_WEBHOOK_SIGNATURE_KEY "${SQUARE_WEBHOOK_SIGNATURE_KEY:-}"
tmux set-environment -t $SESSION_NAME GO_TESTING "1"

# Pane 0: Database
tmux send-keys -t $SESSION_NAME 'docker exec -it postgres psql -U myuser -d mydb -c "SELECT relname AS table_name, n_live_tup AS row_count FROM pg_stat_user_tables ORDER BY table_name;"'
tmux select-pane -t $SESSION_NAME:0.0 -T "DB"

# Pane 1: Backend (Split Horizontally)
tmux split-window -v -t $SESSION_NAME
tmux send-keys -t $SESSION_NAME "set -a; source .env > /dev/null 2>&1; cd backend && POSTGRES_HOST=localhost go run -tags dev ./main.go" Enter
tmux select-pane -t $SESSION_NAME:0.1 -T "Backend"

# Pane 2: Frontend (Split Vertically from Backend)
tmux split-window -h -t $SESSION_NAME:0.1
tmux send-keys -t $SESSION_NAME "set -a; source .env > /dev/null 2>&1; cd frontend && npm run dev -- --host" Enter
tmux select-pane -t $SESSION_NAME:0.2 -T "Frontend"

# Pane 3: Rustfs (S3-compatible storage) - Split from Frontend
tmux split-window -v -t $SESSION_NAME:0.2
tmux send-keys -t $SESSION_NAME "docker logs -f rustfs" Enter
tmux select-pane -t $SESSION_NAME:0.3 -T "Rustfs"

# Layout configuration
tmux select-layout -t $SESSION_NAME even-vertical
tmux select-pane -t $SESSION_NAME:0.0

# --- 5. Seed Script Generation ---
log_step "Generating seed script..."

cat > $SEED_SCRIPT << 'SEED_EOF'
#!/bin/bash

# --- Config ---
ADMIN_EMAIL="admin@example.com"
ADMIN_PASS="password"
USER_EMAIL="user@example.com"
USER_PASS="password"
BASE_URL="http://localhost:8080/api"

# Safe booking times — mid-morning to early afternoon to avoid closing-hour conflicts.
# Adjust these if your business hours differ.
SLOT_A="10:00:00"
SLOT_B="11:30:00"
SLOT_C="13:00:00"
SLOT_D="14:30:00"
SLOT_E="12:15:00"  # Used only for pending demo bookings — avoids conflicts with A-D rotation

# --- Formatting ---
C_RESET=$'\033[0m'
C_GREEN=$'\033[32m'
C_RED=$'\033[31m'
C_BLUE=$'\033[34m'
C_YELLOW=$'\033[33m'

# --- Global for ID Capture ---
LAST_BOOKING_ID=""

# --- Helper: API Request ---
# Returns the root-level "id" from the response body on success.
api_post() {
  local url="$1"
  local data="$2"
  local desc="$3"
  local token="$4"

  local curl_opts=(-s -w "\n%{http_code}" -X POST -H 'Content-Type: application/json')
  [[ -n "$token" ]] && curl_opts+=(-H "Authorization: Bearer $token")
  [[ -n "$data" ]] && curl_opts+=(-d "$data")

  local response=$(curl "${curl_opts[@]}" "$url")
  local http_code=$(echo "$response" | tail -n1)
  local body=$(echo "$response" | sed '$d')

  if [[ "$http_code" =~ ^2 ]]; then
    # Strip nested "user" object so we reliably get the root "id" (UUID)
    local extracted
    extracted=$(echo "$body" | tr -d '\n' | sed 's/"user":{[^}]*}//' | grep -o '"id":"[^"]*' | cut -d'"' -f4 | tr -d '\r\n ')
    # Only emit the value if it looks like a UUID (hex + hyphens, 8+ chars)
    if [[ "$extracted" =~ ^[0-9a-f-]{8,}$ ]]; then
      echo "$extracted"
    fi
    return 0
  else
    printf "${C_RED}❌ Failed: %s (HTTP %s)${C_RESET}\n" "$desc" "$http_code"
    printf "   Request: %s\n" "$data"
    printf "   Response: %s\n" "$body"
    return 1
  fi
}

# --- Helper: Login and return token ---
login() {
  local email="$1" pass="$2"
  curl -s -X POST -H 'Content-Type: application/json' \
    -d "{\"email\":\"$email\",\"password\":\"$pass\"}" \
    "$BASE_URL/login" \
    | tr -d '\r\n\t ' \
    | sed -n 's/.*"token":"\([^"]*\)".*/\1/p'
}

wait_for_backend() {
  echo -ne "⏳ Waiting for backend..."
  for ((i=1; i<=60; i++)); do
    if curl -s --connect-timeout 2 http://localhost:8080/api/register > /dev/null 2>&1; then
      echo -e "\r⏳ Waiting for backend... ${C_GREEN}Ready!${C_RESET}"
      return 0
    fi
    echo -n "."
    sleep 1
  done
  echo -e "\r⏳ Waiting for backend... ${C_RED}Timed out${C_RESET}"
  exit 1
}

# --- Main Execution ---
wait_for_backend

# ===========================================================================
# 1. REGISTER USERS
# ===========================================================================
echo -e "\n${C_BLUE}👤 Registering Users...${C_RESET}"
success=0
total=20

# Admin
if api_post "$BASE_URL/register" '{"firstName":"Chelsea","lastName":"Russell","email":"admin@example.com","password":"password","phone":"+447000000000","dateOfBirth":"1985-01-01","agreedToPolicy":true}' "Register Admin" "" > /dev/null; then success=$((success+1)); fi

# Primary test user (no-deposit, easy to book with)
if api_post "$BASE_URL/register" '{"firstName":"Regular","lastName":"User","email":"user@example.com","password":"password","phone":"+447000000001","dateOfBirth":"1990-05-15","agreedToPolicy":true}' "Register User" "" > /dev/null; then success=$((success+1)); fi

# Loyal regulars
if api_post "$BASE_URL/register" '{"firstName":"Emma","lastName":"Johnson","email":"emma.johnson@example.com","password":"password","phone":"+447000000002","dateOfBirth":"1988-03-22","agreedToPolicy":true}' "Register Emma Johnson" "" > /dev/null; then success=$((success+1)); fi
if api_post "$BASE_URL/register" '{"firstName":"Sophie","lastName":"Williams","email":"sophie.williams@example.com","password":"password","phone":"+447000000003","dateOfBirth":"1995-11-08","agreedToPolicy":true}' "Register Sophie Williams" "" > /dev/null; then success=$((success+1)); fi
if api_post "$BASE_URL/register" '{"firstName":"Amelia","lastName":"Jones","email":"amelia.jones@example.com","password":"password","phone":"+447000000004","dateOfBirth":"1993-09-30","agreedToPolicy":true}' "Register Amelia Jones" "" > /dev/null; then success=$((success+1)); fi
if api_post "$BASE_URL/register" '{"firstName":"Isla","lastName":"Davies","email":"isla.davies@example.com","password":"password","phone":"+447000000005","dateOfBirth":"1989-12-25","agreedToPolicy":true}' "Register Isla Davies" "" > /dev/null; then success=$((success+1)); fi
if api_post "$BASE_URL/register" '{"firstName":"Lily","lastName":"Wilson","email":"lily.wilson@example.com","password":"password","phone":"+447000000006","dateOfBirth":"1996-08-03","agreedToPolicy":true}' "Register Lily Wilson" "" > /dev/null; then success=$((success+1)); fi
if api_post "$BASE_URL/register" '{"firstName":"Ava","lastName":"Walker","email":"ava.walker@example.com","password":"password","phone":"+447000000007","dateOfBirth":"1993-03-07","agreedToPolicy":true}' "Register Ava Walker" "" > /dev/null; then success=$((success+1)); fi

# Occasional clients
if api_post "$BASE_URL/register" '{"firstName":"Oliver","lastName":"Smith","email":"oliver.smith@example.com","password":"password","phone":"+447000000008","dateOfBirth":"1992-07-14","agreedToPolicy":true}' "Register Oliver Smith" "" > /dev/null; then success=$((success+1)); fi
if api_post "$BASE_URL/register" '{"firstName":"Harry","lastName":"Brown","email":"harry.brown@example.com","password":"password","phone":"+447000000009","dateOfBirth":"1987-02-19","agreedToPolicy":true}' "Register Harry Brown" "" > /dev/null; then success=$((success+1)); fi
if api_post "$BASE_URL/register" '{"firstName":"George","lastName":"Roberts","email":"george.roberts@example.com","password":"password","phone":"+447000000010","dateOfBirth":"1986-01-29","agreedToPolicy":true}' "Register George Roberts" "" > /dev/null; then success=$((success+1)); fi
if api_post "$BASE_URL/register" '{"firstName":"Charlie","lastName":"Wright","email":"charlie.wright@example.com","password":"password","phone":"+447000000011","dateOfBirth":"1990-10-11","agreedToPolicy":true}' "Register Charlie Wright" "" > /dev/null; then success=$((success+1)); fi
if api_post "$BASE_URL/register" '{"firstName":"Noah","lastName":"Robinson","email":"noah.robinson@example.com","password":"password","phone":"+447000000012","dateOfBirth":"1988-11-16","agreedToPolicy":true}' "Register Noah Robinson" "" > /dev/null; then success=$((success+1)); fi
if api_post "$BASE_URL/register" '{"firstName":"Thomas","lastName":"Evans","email":"thomas.evans@example.com","password":"password","phone":"+447000000013","dateOfBirth":"1994-04-17","agreedToPolicy":true}' "Register Thomas Evans" "" > /dev/null; then success=$((success+1)); fi
if api_post "$BASE_URL/register" '{"firstName":"Oscar","lastName":"Hughes","email":"oscar.hughes@example.com","password":"password","phone":"+447000000014","dateOfBirth":"1991-02-04","agreedToPolicy":true}' "Register Oscar Hughes" "" > /dev/null; then success=$((success+1)); fi

# Deposit-required users (demonstrate deposit snapshot behaviour)
if api_post "$BASE_URL/register" '{"firstName":"Poppy","lastName":"Thompson","email":"poppy.thompson@example.com","password":"password","phone":"+447000000015","dateOfBirth":"1997-06-21","agreedToPolicy":true}' "Register Poppy Thompson" "" > /dev/null; then success=$((success+1)); fi
if api_post "$BASE_URL/register" '{"firstName":"Mia","lastName":"White","email":"mia.white@example.com","password":"password","phone":"+447000000016","dateOfBirth":"1995-07-28","agreedToPolicy":true}' "Register Mia White" "" > /dev/null; then success=$((success+1)); fi

# Young client (for age-restricted service testing)
if api_post "$BASE_URL/register" '{"firstName":"Chloe","lastName":"Park","email":"chloe.park@example.com","password":"password","phone":"+447000000017","dateOfBirth":"2009-04-12","agreedToPolicy":true}' "Register Chloe Park" "" > /dev/null; then success=$((success+1)); fi

# Patch-test-complete user (will have gel allergy test recorded)
if api_post "$BASE_URL/register" '{"firstName":"Grace","lastName":"Fletcher","email":"grace.fletcher@example.com","password":"password","phone":"+447000000018","dateOfBirth":"1991-08-30","agreedToPolicy":true}' "Register Grace Fletcher" "" > /dev/null; then success=$((success+1)); fi

# No-show history user (to demonstrate 48h booking restriction)
if api_post "$BASE_URL/register" '{"firstName":"Liam","lastName":"Caldwell","email":"liam.caldwell@example.com","password":"password","phone":"+447000000019","dateOfBirth":"1989-03-14","agreedToPolicy":true}' "Register Liam Caldwell" "" > /dev/null; then success=$((success+1)); fi

# --- Promote admin and set deposit flags via DB ---
docker exec postgres psql -U myuser -d mydb -c "UPDATE users SET account_role = 'admin' WHERE email = 'admin@example.com'"
# Primary test user: verified role for card testing, zero deposit requirement, 4 loyalty stamps
docker exec postgres psql -U myuser -d mydb -c "UPDATE users SET account_role = 'verified_email', deposits_required = 0, loyalty_stamps = 4 WHERE email = 'user@example.com'"
# Loyal regulars: zero deposit requirement
docker exec postgres psql -U myuser -d mydb -c "UPDATE users SET deposits_required = 0 WHERE email IN ('emma.johnson@example.com','sophie.williams@example.com','amelia.jones@example.com','isla.davies@example.com','lily.wilson@example.com','ava.walker@example.com','grace.fletcher@example.com')" > /dev/null 2>&1
# Deposit-required users: 3 no-shows on record
docker exec postgres psql -U myuser -d mydb -c "UPDATE users SET deposits_required = 3 WHERE email IN ('poppy.thompson@example.com','mia.white@example.com')" > /dev/null 2>&1
# Liam: 1 no-show, in 48h restriction window
docker exec postgres psql -U myuser -d mydb -c "UPDATE users SET deposits_required = 1 WHERE email = 'liam.caldwell@example.com'" > /dev/null 2>&1

echo "${C_GREEN}✅ Registered $success/$total Users${C_RESET}"
user_success=$success
user_total=$total

# ===========================================================================
# 2. LOGIN
# ===========================================================================
echo -e "\n${C_BLUE}🔑 Authenticating...${C_RESET}"

ADMIN_TOKEN=$(login "$ADMIN_EMAIL" "$ADMIN_PASS")
if [ -z "$ADMIN_TOKEN" ]; then echo "❌ Admin auth failed"; exit 1; fi

USER_TOKEN=$(login "$USER_EMAIL" "$USER_PASS")
if [ -z "$USER_TOKEN" ]; then echo "❌ User auth failed"; exit 1; fi

EMMA_TOKEN=$(login "emma.johnson@example.com" "password")
SOPHIE_TOKEN=$(login "sophie.williams@example.com" "password")
AMELIA_TOKEN=$(login "amelia.jones@example.com" "password")
ISLA_TOKEN=$(login "isla.davies@example.com" "password")
LILY_TOKEN=$(login "lily.wilson@example.com" "password")
AVA_TOKEN=$(login "ava.walker@example.com" "password")
POPPY_TOKEN=$(login "poppy.thompson@example.com" "password")
MIA_TOKEN=$(login "mia.white@example.com" "password")
GRACE_TOKEN=$(login "grace.fletcher@example.com" "password")

# Look up user IDs for admin booking creation
get_user_id() {
  docker exec postgres psql -U myuser -d mydb -tAc "SELECT id FROM users WHERE email='$1';" 2>/dev/null | tr -d '\r\n\t '
}

ADMIN_USER_ID=$(get_user_id "admin@example.com")
USER_USER_ID=$(get_user_id "user@example.com")
EMMA_ID=$(get_user_id "emma.johnson@example.com")
SOPHIE_ID=$(get_user_id "sophie.williams@example.com")
AMELIA_ID=$(get_user_id "amelia.jones@example.com")
ISLA_ID=$(get_user_id "isla.davies@example.com")
LILY_ID=$(get_user_id "lily.wilson@example.com")
AVA_ID=$(get_user_id "ava.walker@example.com")
OLIVER_ID=$(get_user_id "oliver.smith@example.com")
HARRY_ID=$(get_user_id "harry.brown@example.com")
CHARLIE_ID=$(get_user_id "charlie.wright@example.com")
NOAH_ID=$(get_user_id "noah.robinson@example.com")
POPPY_ID=$(get_user_id "poppy.thompson@example.com")
MIA_ID=$(get_user_id "mia.white@example.com")
GRACE_ID=$(get_user_id "grace.fletcher@example.com")
LIAM_ID=$(get_user_id "liam.caldwell@example.com")
THOMAS_ID=$(get_user_id "thomas.evans@example.com")

echo "${C_GREEN}✅ Authentication successful${C_RESET}"
sleep 1

# ===========================================================================
# 3. CREATE SERVICES
# ===========================================================================
echo -e "\n${C_BLUE}💅 Creating Services...${C_RESET}"

SERVICES=(
  '{"name":"Classic Manicure","description":"Nail shaping, cuticle care, hand massage, and polish.","price":25.00,"duration_minutes":45,"minimum_age_required":0}'
  '{"name":"Gel Manicure (BIAB)","description":"Hard-wearing gel polish with Builder In A Bottle base. Long-lasting with added nail strength.","price":35.00,"duration_minutes":60,"minimum_age_required":0}'
  '{"name":"Luxury Pedicure","description":"Foot soak, scrub, mask, extended massage, and polish.","price":45.00,"duration_minutes":75,"minimum_age_required":0}'
  '{"name":"Express Mani & Pedi","description":"Quick file, shape, and polish for both hands and feet.","price":40.00,"duration_minutes":60,"minimum_age_required":0}'
  '{"name":"Gel Polish Removal","description":"Safe soak-off removal of existing gel polish, including aftercare oil treatment.","price":10.00,"duration_minutes":20,"minimum_age_required":0}'
  '{"name":"Nail Art Add-on","description":"Custom nail art per two fingers — French tips, florals, gems, and more.","price":5.00,"duration_minutes":15,"minimum_age_required":0}'
  '{"name":"Gel Polish Full Set","description":"Full gel polish application over natural nails. Requires patch test 24h before first appointment.","price":45.00,"duration_minutes":60,"minimum_age_required":0}'
  '{"name":"Luxury Gel Manicure","description":"Premium gel polish with extended hand massage and cuticle treatment. Requires patch test 24h before first appointment.","price":55.00,"duration_minutes":75,"minimum_age_required":0}'
  '{"name":"Acrylic Full Set","description":"Full set of acrylic extensions, shaped and polished to your preference.","price":55.00,"duration_minutes":90,"minimum_age_required":16}'
  '{"name":"Acrylic Infill","description":"Maintenance infill for existing acrylic extensions.","price":35.00,"duration_minutes":60,"minimum_age_required":16}'
  '{"name":"Paraffin Wax Treatment","description":"Deeply moisturising paraffin wax hand or foot treatment, great as an add-on.","price":12.00,"duration_minutes":20,"minimum_age_required":0}'
  '{"name":"Bridal Nail Package","description":"Luxury manicure and pedicure with nail art, paraffin wax, and extended massage for the big day.","price":120.00,"duration_minutes":150,"minimum_age_required":0}'
)

SERVICE_IDS=()
success=0
total=${#SERVICES[@]}

for svc in "${SERVICES[@]}"; do
  NAME=$(echo "$svc" | grep -o '"name":"[^"]*' | cut -d'"' -f4)
  ID=$(api_post "$BASE_URL/admin/services" "$svc" "Create $NAME" "$ADMIN_TOKEN")
  if [[ -n "$ID" && "$ID" =~ ^[0-9a-f-]{8,}$ ]]; then
    SERVICE_IDS+=("$ID")
    success=$((success+1))
  fi
done
echo "${C_GREEN}✅ Created $success/$total Services${C_RESET}"

get_svc() { echo "${SERVICE_IDS[$1]}"; }

# ===========================================================================
# 3b. CREATE PATCH TESTS (seeded directly via SQL — no creation API endpoint)
# ===========================================================================
echo -e "\n${C_BLUE}🧪 Creating Patch Tests...${C_RESET}"

# Gel Allergy Test covers: Gel Manicure BIAB (idx 1), Gel Polish Full Set (idx 6), Luxury Gel Manicure (idx 7)
GEL_SVC_1="${SERVICE_IDS[1]}"
GEL_SVC_6="${SERVICE_IDS[6]}"
GEL_SVC_7="${SERVICE_IDS[7]}"

# Insert patch test directly via SQL (no creation API endpoint exists)
# service_ids cast explicitly to uuid[] to handle typed columns
PATCH_TEST_RESULT=$(docker exec postgres psql -U myuser -d mydb -tAc \
  "INSERT INTO patch_tests (name, description, notice_duration_hours, expiry_months, service_ids) VALUES ('Gel Allergy Test', 'Mandatory patch test for all gel polish services. Must be completed at least 24 hours before your first gel appointment.', 24, 6, ARRAY['${GEL_SVC_1}','${GEL_SVC_6}','${GEL_SVC_7}']) RETURNING id;" 2>&1)
PATCH_TEST_ID=$(echo "$PATCH_TEST_RESULT" | head -1 | tr -d '\r\t ')

# Validate it looks like a UUID; print raw output if not to help diagnose
if [[ -z "$PATCH_TEST_ID" || ! "$PATCH_TEST_ID" =~ ^[0-9a-f-]{8,}$ ]]; then
  echo "${C_YELLOW}⚠️ Patch test SQL failed. Raw output: $PATCH_TEST_RESULT${C_RESET}"
  PATCH_TEST_ID=""
fi

# Record patch tests for ALL users who book gel services so no booking gets 400'd
PATCH_TEST_USERS=(
  "$GRACE_ID:Grace Fletcher"  # Luxury Gel Manicure / Gel Full Set
  "$EMMA_ID:Emma Johnson"     # Gel Manicure (BIAB) — regular gel client
  "$ISLA_ID:Isla Davies"      # Gel Manicure (BIAB) + Nail Art
  "$LILY_ID:Lily Wilson"      # Gel Manicure (BIAB) + Nail Art
)
patch_recorded=0
if [[ -n "$PATCH_TEST_ID" ]]; then
  for entry in "${PATCH_TEST_USERS[@]}"; do
    uid="${entry%%:*}"
    name="${entry#*:}"
    if [[ -n "$uid" ]]; then
      api_post "$BASE_URL/admin/users/$uid/patch-tests" \
        "{\"patch_test_id\":\"$PATCH_TEST_ID\"}" \
        "Record patch test for $name" "$ADMIN_TOKEN" > /dev/null \
        && patch_recorded=$((patch_recorded + 1))
    fi
  done
  echo "${C_GREEN}✅ Patch test seeded (SQL) and recorded for $patch_recorded users${C_RESET}"
else
  echo "${C_YELLOW}⚠️ Patch test seeding failed (check DB connection or patch_tests table)${C_RESET}"
fi

# Seed-day shorthand helpers (used by time-blockers, bookings, and beyond)
format_london_time() {
  TZ=Europe/London date -d "$1 $2" +"%Y-%m-%dT%H:%M:%S%:z"
}
# Returns the nearest OPEN business day at or after the given date.
# Business days: Tue(2)-Sat(6). Closed: Sun(0), Mon(1). (GNU date %w)
open_day() {
  local d="$1"
  local max=7
  for ((i=0; i<max; i++)); do
    local dow
    dow=$(TZ=Europe/London date -d "$d" +%w)
    if [[ "$dow" == "0" || "$dow" == "1" ]]; then
      d=$(TZ=Europe/London date -d "$d +1 day" +%Y-%m-%d)
    else
      echo "$d"
      return
    fi
  done
  echo "$d"
}
# Same but shifts BACKWARDS to find an open day (for past bookings)
open_day_past() {
  local d="$1"
  local max=7
  for ((i=0; i<max; i++)); do
    local dow
    dow=$(TZ=Europe/London date -d "$d" +%w)
    if [[ "$dow" == "0" || "$dow" == "1" ]]; then
      d=$(TZ=Europe/London date -d "$d -1 day" +%Y-%m-%d)
    else
      echo "$d"
      return
    fi
  done
  echo "$d"
}
TODAY=$(TZ=Europe/London date +%Y-%m-%d)
TOMORROW=$(open_day "$(TZ=Europe/London date -d "tomorrow" +%Y-%m-%d)")

# ===========================================================================
# 7. TIME BLOCKERS (admin-blocked time)
# ===========================================================================
echo -e "\n${C_BLUE}🚫 Creating Time Blockers...${C_RESET}"
count_blockers=0

tb() {
  local time="$1" dur="$2" desc="$3"
  local json="{\"start_time\":\"$time\",\"duration_minutes\":$dur,\"description\":\"$desc\"}"
  local resp=$(curl -s -w "\n%{http_code}" -X POST -H 'Content-Type: application/json' -H "Authorization: Bearer $ADMIN_TOKEN" -d "$json" "$BASE_URL/admin/time-blockers")
  local code=$(echo "$resp" | tail -n1)
  [[ "$code" =~ ^2 ]] && count_blockers=$((count_blockers+1))
}

tb "$(format_london_time "$TOMORROW" "14:00:00")" 60 "Staff meeting"
tb "$(format_london_time "$(open_day "$(TZ=Europe/London date -d "$TODAY +7 days" +%Y-%m-%d)")" "$SLOT_A")" 120 "Holiday — closed morning"
tb "$(format_london_time "$TOMORROW" "09:00:00")" 120 "Late start — closed until 11am"

tb "$(format_london_time "$(open_day "$(TZ=Europe/London date -d "$TODAY +3 days" +%Y-%m-%d)")" "$SLOT_B")" 90 "Equipment maintenance"
tb "$(format_london_time "$(open_day "$(TZ=Europe/London date -d "$TODAY +10 days" +%Y-%m-%d)")" "$SLOT_C")" 60 "Training session"
tb "$(format_london_time "$(open_day "$(TZ=Europe/London date -d "$TODAY +14 days" +%Y-%m-%d)")" "09:00:00")" 60 "Opening delay"
tb "$(format_london_time "$(open_day "$(TZ=Europe/London date -d "$TODAY +5 days" +%Y-%m-%d)")" "$SLOT_D")" 45 "Supplier visit"
tb "$(format_london_time "$(open_day "$(TZ=Europe/London date -d "$TODAY +8 days" +%Y-%m-%d)")" "$SLOT_A")" 120 "Deep clean — morning closed"

echo "${C_GREEN}✅ Created $count_blockers Time Blockers${C_RESET}"

# ===========================================================================
# 4. BOOKINGS
# ===========================================================================
echo -e "\n${C_BLUE}📅 Creating Bookings...${C_RESET}"

# Calculate total duration for a services JSON array by looking up each
# service index in the SERVICES array and summing duration_minutes.
calc_duration() {
  local services_json="$1"
  local total=0
  local ids
  ids=$(echo "$services_json" | tr -d '[]"' | tr ',' '\n')
  while IFS= read -r sid; do
    [[ -z "$sid" ]] && continue
    for idx in "${!SERVICE_IDS[@]}"; do
      if [[ "${SERVICE_IDS[$idx]}" == "$sid" ]]; then
        local raw="${SERVICES[$idx]}"
        local dur
        dur=$(echo "$raw" | grep -o '"duration_minutes":[0-9]*' | cut -d: -f2)
        total=$((total + dur))
        break
      fi
    done
  done <<< "$ids"
  echo "$total"
}

# Query /api/available-hours for a date and pick a valid start time at
# 15-minute intervals (00/15/30/45) that fits the given duration.
# Prints "HH:MM:SS" on success, empty string on failure.
pick_avail_slot() {
  local date="$1" duration_minutes="$2"
  curl -s "$BASE_URL/scheduling/available-hours?start=$date&end=$date" 2>/dev/null | python3 -c "
import json, sys
data = json.load(sys.stdin)
duration = int(sys.argv[1])
target = sys.argv[2]
for day in data:
    if day.get('date') == target and day.get('isOpen'):
        for slot in day.get('slots', []):
            start_parts = slot.get('startTime', '00:00').split(':')
            end_parts = slot.get('endTime', '00:00').split(':')
            start_min = int(start_parts[0]) * 60 + int(start_parts[1])
            end_min = int(end_parts[0]) * 60 + int(end_parts[1])
            slot_start = ((start_min + 14) // 15) * 15
            if slot_start + duration <= end_min:
                h = slot_start // 60
                m = slot_start % 60
                print(f'{h:02d}:{m:02d}:00')
                sys.exit(0)
sys.exit(1)
" "$duration_minutes" "$date" 2>/dev/null
}

# Create a booking as a regular user. Uses available-hours to pick a
# valid start time within a 15-min increment that fits the services.
create_booking() {
  local token=$1 date=$2 services=$3 notes=$4 name=$5
  # Calculate total duration for slot selection
  local duration
  duration=$(calc_duration "$services")
  local slot
  slot=$(pick_avail_slot "$date" "$duration")
  if [[ -z "$slot" ]]; then
    LAST_BOOKING_ID=""
    return 1
  fi
  local time
  time=$(format_london_time "$date" "$slot")
  local json="{\"start_time\":\"$time\",\"service_ids\":$services"
  [[ -n "$notes" ]] && json="$json,\"notes\":\"$notes\""
  json="$json}"
  local id
  id=$(api_post "$BASE_URL/bookings" "$json" "$name" "$token")
  if [[ -n "$id" && "$id" =~ ^[0-9a-f-]{8,}$ ]]; then
    LAST_BOOKING_ID="$id"
    return 0
  fi
  LAST_BOOKING_ID=""
  return 1
}

# Create a booking as admin, using available-hours to pick a valid slot.
create_admin_booking() {
  local user_id=$1 date=$2 services=$3 notes=$4 name=$5
  local duration
  duration=$(calc_duration "$services")
  local slot
  slot=$(pick_avail_slot "$date" "$duration")
  if [[ -z "$slot" ]]; then
    LAST_BOOKING_ID=""
    return 1
  fi
  local time
  time=$(format_london_time "$date" "$slot")
  local json="{\"user_id\":\"$user_id\",\"start_time\":\"$time\",\"service_ids\":$services,\"service_overrides\":[],\"enforce_deposits\":false"
  [[ -n "$notes" ]] && json="$json,\"notes\":\"$notes\""
  json="$json}"

  local id=$(api_post "$BASE_URL/admin/bookings" "$json" "Admin Book: $name" "$ADMIN_TOKEN")
  if [[ -n "$id" && "$id" =~ ^[0-9a-f-]{8,}$ ]]; then
    LAST_BOOKING_ID="$id"
    return 0
  else
    LAST_BOOKING_ID=""
    return 1
  fi
}

# Arrays to collect upcoming booking IDs that need notes confirmed
PENDING_BOOKING_IDS=()
PENDING_BOOKING_NAMES=()

# ---------------------------------------------------------------------------
# PAST BOOKINGS — all via admin (bypasses past-time rejection)
# ---------------------------------------------------------------------------
echo -e "\n${C_YELLOW}📅 Creating Past Bookings...${C_RESET}"
count_past=0

# Emma — loyal weekly gel client
for day_offset in 7 14 21 28; do
  D=$(open_day_past "$(TZ=Europe/London date -d "today -$day_offset days" +%Y-%m-%d)")
  if create_admin_booking "$EMMA_ID"   "$D" "[\"$(get_svc 1)\"]" "" "Emma - Gel Manicure $D"; then count_past=$((count_past+1)); fi
done

# Sophie — pedicure every few weeks
for day_offset in 6 20; do
  D=$(open_day_past "$(TZ=Europe/London date -d "today -$day_offset days" +%Y-%m-%d)")
  if create_admin_booking "$SOPHIE_ID" ""$D"" "[\"$(get_svc 2)\"]" "" "Sophie - Luxury Pedicure $D"; then count_past=$((count_past+1)); fi
done

# Amelia — classic manicure regular
for day_offset in 5 19; do
  D=$(open_day_past "$(TZ=Europe/London date -d "today -$day_offset days" +%Y-%m-%d)")
  if create_admin_booking "$AMELIA_ID" "$D" "[\"$(get_svc 0)\"]" "" "Amelia - Classic Manicure $D"; then count_past=$((count_past+1)); fi
done

# Isla — mixed services
D=$(open_day_past "$(TZ=Europe/London date -d "today -3 days" +%Y-%m-%d)")
if create_admin_booking "$ISLA_ID" "$D" "[\"$(get_svc 3)\"]" "" "Isla - Express Mani & Pedi"; then count_past=$((count_past+1)); fi
D=$(open_day_past "$(TZ=Europe/London date -d "today -17 days" +%Y-%m-%d)")
if create_admin_booking "$ISLA_ID" ""$D"" "[\"$(get_svc 0)\",\"$(get_svc 5)\"]" "" "Isla - Classic + Nail Art"; then count_past=$((count_past+1)); fi

# Lily — gel with nail art combo
D=$(open_day_past "$(TZ=Europe/London date -d "today -10 days" +%Y-%m-%d)")
if create_admin_booking "$LILY_ID" ""$D"" "[\"$(get_svc 1)\",\"$(get_svc 5)\"]" "" "Lily - Gel + Nail Art"; then count_past=$((count_past+1)); fi

# Ava — occasional pedicure
D=$(open_day_past "$(TZ=Europe/London date -d "today -12 days" +%Y-%m-%d)")
if create_admin_booking "$AVA_ID" "$D" "[\"$(get_svc 2)\"]" "" "Ava - Luxury Pedicure"; then count_past=$((count_past+1)); fi

# Grace — gel client (patch test already recorded)
D=$(open_day_past "$(TZ=Europe/London date -d "today -9 days" +%Y-%m-%d)")
if create_admin_booking "$GRACE_ID" "$D" "[\"$(get_svc 6)\"]" "" "Grace - Gel Full Set"; then count_past=$((count_past+1)); fi
D=$(open_day_past "$(TZ=Europe/London date -d "today -23 days" +%Y-%m-%d)")
if create_admin_booking "$GRACE_ID" "$D" "[\"$(get_svc 7)\"]" "" "Grace - Luxury Gel Manicure"; then count_past=$((count_past+1)); fi

# Primary test user — variety of past bookings
for day_offset in 2 4 8 11 15 18 20 22 24 26 28; do
  D=$(open_day_past "$(TZ=Europe/London date -d "today -$day_offset days" +%Y-%m-%d)")
  SVC_IDX=$(( (day_offset % 4) ))
  if create_admin_booking "$USER_USER_ID" ""$D"" "[\"$(get_svc $SVC_IDX)\"]" "" "User - Past booking $D"; then count_past=$((count_past+1)); fi
done

# Poppy / Mia — deposit-required past visits
D=$(open_day_past "$(TZ=Europe/London date -d "today -5 days" +%Y-%m-%d)")
if create_admin_booking "$POPPY_ID" ""$D"" "[\"$(get_svc 0)\"]" "" "Poppy - Past (deposit snapshotted)"; then count_past=$((count_past+1)); fi
D=$(open_day_past "$(TZ=Europe/London date -d "today -13 days" +%Y-%m-%d)")
if create_admin_booking "$MIA_ID"   ""$D"" "[\"$(get_svc 2)\"]" "" "Mia - Past"; then count_past=$((count_past+1)); fi

echo "${C_GREEN}✅ Created $count_past Past Bookings${C_RESET}"

# ---------------------------------------------------------------------------
# TODAY'S BOOKINGS — all via admin (bypass 1-hour advance)
# ---------------------------------------------------------------------------
echo -e "\n${C_YELLOW}📅 Creating Today's Bookings...${C_RESET}"
count_today=0

if create_admin_booking "$EMMA_ID"      ""$TODAY"" "[\"$(get_svc 1)\"]"                   "" "Emma - Gel Manicure (today)";           then count_today=$((count_today+1)); fi
if create_admin_booking "$SOPHIE_ID"    ""$TODAY"" "[\"$(get_svc 2)\"]"                   "" "Sophie - Luxury Pedicure (today)";      then count_today=$((count_today+1)); fi
if create_admin_booking "$AMELIA_ID"    ""$TODAY"" "[\"$(get_svc 0)\",\"$(get_svc 5)\"]"  "" "Amelia - Classic + Nail Art (today)";   then count_today=$((count_today+1)); fi
if create_admin_booking "$USER_USER_ID" ""$TODAY"" "[\"$(get_svc 3)\"]"                   "" "User - Express Mani & Pedi (today)";    then count_today=$((count_today+1)); fi
if create_admin_booking "$ISLA_ID"      ""$TODAY"" "[\"$(get_svc 1)\"]"                   "" "Isla - Gel Manicure (today)";           then count_today=$((count_today+1)); fi
if create_admin_booking "$GRACE_ID"     ""$TODAY"" "[\"$(get_svc 6)\"]"                   "" "Grace - Gel Full Set (today)";          then count_today=$((count_today+1)); fi
if create_admin_booking "$LILY_ID"      ""$TODAY"" "[\"$(get_svc 0)\"]"                   "" "Lily - Classic Manicure (today)";       then count_today=$((count_today+1)); fi

echo "${C_GREEN}✅ Created $count_today Today's Bookings${C_RESET}"

# ---------------------------------------------------------------------------
# TOMORROW'S BOOKINGS — all via admin (avoids booking_status enum bug in
# POST /bookings). We simulate the pending/notes scenario by creating 2
# bookings via user tokens (which intentionally hit the notes path) and
# leaving them unconfirmed for admin review.
# ---------------------------------------------------------------------------
echo -e "\n${C_YELLOW}📅 Creating Tomorrow's Bookings...${C_RESET}"
count_tomorrow=0

# Bulk of tomorrow's bookings — admin-created, all confirmed
if create_admin_booking "$EMMA_ID"   ""$TOMORROW"" "[\"$(get_svc 1)\"]" "" "Emma - Gel Manicure (tomorrow)";           then count_tomorrow=$((count_tomorrow+1)); fi
if create_admin_booking "$SOPHIE_ID" ""$TOMORROW"" "[\"$(get_svc 2)\"]" "" "Sophie - Luxury Pedicure (tomorrow)";       then count_tomorrow=$((count_tomorrow+1)); fi
if create_admin_booking "$AMELIA_ID" ""$TOMORROW"" "[\"$(get_svc 3)\"]" "" "Amelia - Express Mani & Pedi (tomorrow)";   then count_tomorrow=$((count_tomorrow+1)); fi
if create_admin_booking "$USER_USER_ID" ""$TOMORROW"" "[\"$(get_svc 0)\"]" "" "User - Classic Manicure (tomorrow)";     then count_tomorrow=$((count_tomorrow+1)); fi
if create_admin_booking "$LILY_ID"   ""$TOMORROW"" "[\"$(get_svc 2)\"]" "" "Lily - Luxury Pedicure (tomorrow)";         then count_tomorrow=$((count_tomorrow+1)); fi

# Pending bookings — admin-created (avoids enum bug), then force to pending via DB.
# Notes are included so the admin UI shows the request context.
if create_admin_booking "$ISLA_ID" ""$TOMORROW"" "[\"$(get_svc 1)\",\"$(get_svc 5)\"]" "Would love a French tip look with some floral art on the ring fingers if possible?" "Isla - Gel + Nail Art (tomorrow, pending)"; then
  count_tomorrow=$((count_tomorrow+1))
  if [[ -n "$LAST_BOOKING_ID" ]]; then
    docker exec postgres psql -U myuser -d mydb -c "UPDATE bookings SET status = 'pending' WHERE id = '${LAST_BOOKING_ID}'" > /dev/null 2>&1
    PENDING_BOOKING_IDS+=("$LAST_BOOKING_ID"); PENDING_BOOKING_NAMES+=("Isla - Gel + Nail Art (pending)")
  fi
fi
if create_admin_booking "$AVA_ID" ""$TOMORROW"" "[\"$(get_svc 0)\"]" "Could I get a specific nail shape — stiletto if possible?" "Ava - Classic Manicure (tomorrow, pending)"; then
  count_tomorrow=$((count_tomorrow+1))
  if [[ -n "$LAST_BOOKING_ID" ]]; then
    docker exec postgres psql -U myuser -d mydb -c "UPDATE bookings SET status = 'pending' WHERE id = '${LAST_BOOKING_ID}'" > /dev/null 2>&1
    PENDING_BOOKING_IDS+=("$LAST_BOOKING_ID"); PENDING_BOOKING_NAMES+=("Ava - Classic Manicure (pending)")
  fi
fi

echo "${C_GREEN}✅ Created $count_tomorrow Tomorrow's Bookings${C_RESET}"

# ---------------------------------------------------------------------------
# UPCOMING — next 14 days, all via admin to avoid booking_status enum bug.
# Bridal package uses user token (notes → pending) as the one exception.
# ---------------------------------------------------------------------------
echo -e "\n${C_YELLOW}📅 Creating Upcoming Bookings (next 14 days)...${C_RESET}"
count_future=0

UP_IDS=(   "$EMMA_ID"   "$SOPHIE_ID"      "$AMELIA_ID"        "$ISLA_ID"             "$LILY_ID"      "$AVA_ID"          "$GRACE_ID"            "$USER_USER_ID"   )
UP_SVCS=(  "1"          "2"               "0"                  "3"                    "1"             "2"                "7"                    "0"               )
UP_SLOTS=( "$SLOT_A"    "$SLOT_C"         "$SLOT_D"            "$SLOT_B"              "$SLOT_C"       "$SLOT_A"          "$SLOT_D"              "$SLOT_B"         )
UP_NAMES=( "Gel Manicure" "Luxury Pedicure" "Classic Manicure" "Express Mani & Pedi" "Gel Manicure" "Luxury Pedicure" "Luxury Gel Manicure" "Classic Manicure" )

for day_offset in {2..15}; do
  RAW_DATE=$(TZ=Europe/London date -d "$TODAY +$day_offset days" +%Y-%m-%d)
  FUTURE_DATE=$(open_day "$RAW_DATE")
  idx=$(( (day_offset - 2) % ${#UP_IDS[@]} ))
  uid="${UP_IDS[$idx]}"
  svc_idx="${UP_SVCS[$idx]}"
  slot="${UP_SLOTS[$idx]}"
  label="${UP_NAMES[$idx]}"

  if create_admin_booking "$uid" ""$FUTURE_DATE"" "[\"$(get_svc $svc_idx)\"]" "" "$label ($FUTURE_DATE)"; then
    count_future=$((count_future+1))
    # NOTE: do NOT add admin bookings to PENDING_BOOKING_IDS — they're already confirmed
  fi
done

# Multi-service admin bookings for variety
D2=$(open_day "$(TZ=Europe/London date -d "$TODAY +3 days" +%Y-%m-%d)")
if create_admin_booking "$USER_USER_ID" ""$D2"" "[\"$(get_svc 0)\",\"$(get_svc 5)\"]" "" "User - Classic + Nail Art (+3 days)"; then
  count_future=$((count_future+1))
fi

D3=$(open_day "$(TZ=Europe/London date -d "$TODAY +6 days" +%Y-%m-%d)")
if create_admin_booking "$EMMA_ID" ""$D3"" "[\"$(get_svc 1)\",\"$(get_svc 4)\"]" "" "Emma - Gel Manicure + Removal (+6 days)"; then
  count_future=$((count_future+1))
fi

# Bridal package — admin-created with notes, then forced to pending via DB.
D4=$(open_day "$(TZ=Europe/London date -d "$TODAY +12 days" +%Y-%m-%d)")
if create_admin_booking "$SOPHIE_ID" ""$D4"" "[\"$(get_svc 11)\"]" "Bride-to-be — please can we discuss nail art options beforehand?" "Sophie - Bridal Package (+12 days, pending)"; then
  count_future=$((count_future+1))
  if [[ -n "$LAST_BOOKING_ID" ]]; then
    docker exec postgres psql -U myuser -d mydb -c "UPDATE bookings SET status = 'pending' WHERE id = '${LAST_BOOKING_ID}'" > /dev/null 2>&1
    PENDING_BOOKING_IDS+=("$LAST_BOOKING_ID"); PENDING_BOOKING_NAMES+=("Sophie - Bridal Package (pending)")
  fi
fi

# Deposit-required user — admin-created to bypass deposit check
D5=$(open_day "$(TZ=Europe/London date -d "$TODAY +4 days" +%Y-%m-%d)")
if create_admin_booking "$POPPY_ID" ""$D5"" "[\"$(get_svc 0)\"]" "" "Poppy - Future (deposit snapshotted)"; then
  count_future=$((count_future+1))
fi

echo "${C_GREEN}✅ Created $count_future Upcoming Bookings${C_RESET}"

# ---------------------------------------------------------------------------
# CONFIRM roughly 2/3 of pending bookings, leave ~1/3 for admin review
# ---------------------------------------------------------------------------
echo -e "\n${C_BLUE}🔒 Confirming Upcoming Bookings (approx. 2/3)...${C_RESET}"
confirmed_count=0
skipped_count=0
sleep 1

for ((i=0; i<${#PENDING_BOOKING_IDS[@]}; i++)); do
  id="${PENDING_BOOKING_IDS[$i]}"
  [[ -z "$id" ]] && continue
  [[ ! "$id" =~ ^[0-9a-f-]{8,}$ ]] && continue

  if [ $((RANDOM % 3)) -ne 0 ]; then
    RESPONSE=$(curl -s -w "\n%{http_code}" -X POST \
      -H "Content-Type: application/json" \
      -H "Authorization: Bearer $ADMIN_TOKEN" \
      -d '{"service_overrides":[],"notes":""}' \
      "$BASE_URL/admin/bookings/$id/confirm")
    HTTP_CODE=$(echo "$RESPONSE" | tail -n1)
    [ "$HTTP_CODE" = "200" ] && confirmed_count=$((confirmed_count+1))
    sleep 0.05
  else
    skipped_count=$((skipped_count+1))
  fi
done
echo "${C_GREEN}✅ Confirmed $confirmed_count bookings, left $skipped_count pending for admin review${C_RESET}"

# ===========================================================================
# 5b. USER BOOKINGS WITH NOTES (triggers pending_booking notifications)
# These use the public endpoint so notifications are created.
# The create_booking function now retries across 5 time slots (A-E) to find
# one that satisfies: within hours, open day, non-overlapping, no blocker.
# ===========================================================================
echo -e "\n${C_BLUE}📝 Creating User Bookings with Notes (notification triggers)..."

USER_NOTE_COUNT=0

# --- NOTE BOOKINGS (existing, now with retry) ---

# Emma — booking with special request notes
D_NOTE1=$(open_day "$(TZ=Europe/London date -d "$TODAY +8 days" +%Y-%m-%d)")
if create_booking "$EMMA_TOKEN" ""$D_NOTE1"" "[\"$(get_svc 1)\"]" "Hi! Could I please have a nude base with white french tips and a single gold foil accent on the ring finger? Also I have a slight nail ridge on my left thumb - nothing major but worth noting." "Emma - French tips + gold foil (+8 days)"; then
  USER_NOTE_COUNT=$((USER_NOTE_COUNT+1))
fi

# Isla — booking with notes for a different day (multi-service)
D_NOTE2=$(open_day "$(TZ=Europe/London date -d "$TODAY +10 days" +%Y-%m-%d)")
if create_booking "$ISLA_TOKEN" ""$D_NOTE2"" "[\"$(get_svc 1)\",\"$(get_svc 5)\"]" "I'd like a chrome/mirror-ball effect on all nails if possible. Also I'm thinking of getting married soon so want to trial a bridal look - can we discuss options?" "Isla - Chrome trial (+10 days)"; then
  USER_NOTE_COUNT=$((USER_NOTE_COUNT+1))
fi

# --- NEW: THIS WEEK REMAINING (user-facing, no notes but triggers normal flow) ---
echo -e "\n${C_YELLOW}📝 Adding more user bookings across this week...${C_RESET}"

# Sophie — Luxury Pedicure + Paraffin Wax (multi-service, +3 days)
D_W1=$(open_day "$(TZ=Europe/London date -d "$TODAY +3 days" +%Y-%m-%d)")
if create_booking "$SOPHIE_TOKEN" ""$D_W1"" "[\"$(get_svc 2)\",\"$(get_svc 10)\"]" "" "Sophie - Pedicure + Paraffin (+3 days)"; then
  USER_NOTE_COUNT=$((USER_NOTE_COUNT+1))
fi

# Amelia — Classic Manicure (single, +4 days)
D_W2=$(open_day "$(TZ=Europe/London date -d "$TODAY +4 days" +%Y-%m-%d)")
if create_booking "$AMELIA_TOKEN" ""$D_W2"" "[\"$(get_svc 0)\"]" "" "Amelia - Classic Manicure (+4 days)"; then
  USER_NOTE_COUNT=$((USER_NOTE_COUNT+1))
fi

# Lily — Gel BIAB + Nail Art (multi-service, +5 days — patch test passes: >24h notice)
D_W3=$(open_day "$(TZ=Europe/London date -d "$TODAY +5 days" +%Y-%m-%d)")
if create_booking "$LILY_TOKEN" ""$D_W3"" "[\"$(get_svc 1)\",\"$(get_svc 5)\"]" "" "Lily - Gel + Nail Art (+5 days)"; then
  USER_NOTE_COUNT=$((USER_NOTE_COUNT+1))
fi

# Ava — Luxury Pedicure (single, +6 days)
D_W4=$(open_day "$(TZ=Europe/London date -d "$TODAY +6 days" +%Y-%m-%d)")
if create_booking "$AVA_TOKEN" ""$D_W4"" "[\"$(get_svc 2)\"]" "" "Ava - Luxury Pedicure (+6 days)"; then
  USER_NOTE_COUNT=$((USER_NOTE_COUNT+1))
fi

# --- PAST-WEEK BOOKINGS (admin-created, bypasses past-time rejection) ---
echo -e "\n${C_YELLOW}📝 Adding past-week bookings (admin-created)...${C_RESET}"
count_past_extra=0

# Oliver — Classic Manicure + Nail Art (multi-service, 3 days ago)
D_P1=$(open_day_past "$(TZ=Europe/London date -d "today -3 days" +%Y-%m-%d)")
if create_admin_booking "$OLIVER_ID" ""$D_P1"" "[\"$(get_svc 0)\",\"$(get_svc 5)\"]" "" "Oliver - Classic + Nail Art (past)"; then count_past_extra=$((count_past_extra+1)); fi

# Harry — Luxury Pedicure + Paraffin Wax (multi-service, 4 days ago)
D_P2=$(open_day_past "$(TZ=Europe/London date -d "today -4 days" +%Y-%m-%d)")
if create_admin_booking "$HARRY_ID" ""$D_P2"" "[\"$(get_svc 2)\",\"$(get_svc 10)\"]" "" "Harry - Pedicure + Paraffin (past)"; then count_past_extra=$((count_past_extra+1)); fi

# Charlie — Gel Manicure BIAB (single, 2 days ago — will FAIL patch test 24h notice)
D_P3=$(open_day_past "$(TZ=Europe/London date -d "today -2 days" +%Y-%m-%d)")
if create_admin_booking "$CHARLIE_ID" ""$D_P3"" "[\"$(get_svc 1)\"]" "" "Charlie - Gel BIAB (past, no patch test)"; then count_past_extra=$((count_past_extra+1)); fi

# Noah — Express Mani & Pedi + Nail Art (multi-service, 1 day ago)
D_P4=$(open_day_past "$(TZ=Europe/London date -d "today -1 day" +%Y-%m-%d)")
if create_admin_booking "$NOAH_ID" ""$D_P4"" "[\"$(get_svc 3)\",\"$(get_svc 5)\"]" "" "Noah - Express + Nail Art (past)"; then count_past_extra=$((count_past_extra+1)); fi

# Thomas — Classic Manicure + Paraffin Wax (multi-service, 6 days ago)
D_P5=$(open_day_past "$(TZ=Europe/London date -d "today -6 days" +%Y-%m-%d)")
if create_admin_booking "$THOMAS_ID" ""$D_P5"" "[\"$(get_svc 0)\",\"$(get_svc 10)\"]" "" "Thomas - Classic + Paraffin (past)"; then count_past_extra=$((count_past_extra+1)); fi

echo "${C_GREEN}✅ Created $count_past_extra past-week admin bookings${C_RESET}"
count_past=$((count_past + count_past_extra))

# --- UPCOMING MONTH (user-facing, spread across next 14-30 days) ---
echo -e "\n${C_YELLOW}📝 Adding upcoming month user bookings...${C_RESET}"

# Emma — Gel BIAB + Nail Art + Paraffin (multi, +14 days — patch test passes)
D_U1=$(open_day "$(TZ=Europe/London date -d "$TODAY +14 days" +%Y-%m-%d)")
if create_booking "$EMMA_TOKEN" ""$D_U1"" "[\"$(get_svc 1)\",\"$(get_svc 5)\",\"$(get_svc 10)\"]" "" "Emma - Gel + Nail Art + Wax (+14 days)"; then
  USER_NOTE_COUNT=$((USER_NOTE_COUNT+1))
fi

# Grace — Luxury Gel Manicure + Nail Art (multi, +18 days — patch test passes)
D_U2=$(open_day "$(TZ=Europe/London date -d "$TODAY +18 days" +%Y-%m-%d)")
if create_booking "$GRACE_TOKEN" ""$D_U2"" "[\"$(get_svc 7)\",\"$(get_svc 5)\"]" "" "Grace - Luxury Gel + Nail Art (+18 days)"; then
  USER_NOTE_COUNT=$((USER_NOTE_COUNT+1))
fi

# Poppy — already has a confirmed admin-created booking (deposit demo user),
# so skip the user-facing call (deposit constraint: 1 active booking max).

# Sophie — Bridal Package (multi, +25 days)
D_U4=$(open_day "$(TZ=Europe/London date -d "$TODAY +25 days" +%Y-%m-%d)")
if create_booking "$SOPHIE_TOKEN" ""$D_U4"" "[\"$(get_svc 11)\"]" "" "Sophie - Bridal Package (+25 days)"; then
  USER_NOTE_COUNT=$((USER_NOTE_COUNT+1))
fi

# User — Express Mani & Pedi + Gel Polish Removal (multi, +28 days)
D_U5=$(open_day "$(TZ=Europe/London date -d "$TODAY +28 days" +%Y-%m-%d)")
if create_booking "$USER_TOKEN" ""$D_U5"" "[\"$(get_svc 3)\",\"$(get_svc 4)\"]" "" "User - Express + Removal (+28 days)"; then
  USER_NOTE_COUNT=$((USER_NOTE_COUNT+1))
fi

# Ava — Luxury Pedicure (single, +30 days)
D_U6=$(open_day "$(TZ=Europe/London date -d "$TODAY +30 days" +%Y-%m-%d)")
if create_booking "$AVA_TOKEN" ""$D_U6"" "[\"$(get_svc 2)\"]" "" "Ava - Luxury Pedicure (+30 days)"; then
  USER_NOTE_COUNT=$((USER_NOTE_COUNT+1))
fi

echo "${C_GREEN}✅ Created $USER_NOTE_COUNT User Bookings (incl. past-week + upcoming month)${C_RESET}"

# ===========================================================================
# 6. GUEST BOOKINGS
# ===========================================================================
echo -e "\n${C_BLUE}👤 Creating Guest Accounts & Bookings...${C_RESET}"

create_guest() {
  local name="$1" email="$2" phone="$3"
  curl -s -X POST -H 'Content-Type: application/json' \
    -d "{\"firstName\":\"$name\",\"lastName\":\"Guest\",\"email\":\"$email\",\"phone\":\"$phone\"}" \
    "$BASE_URL/users/guest" \
    | tr -d '\r\n\t ' \
    | sed -n 's/.*"id":"\([^"]*\)".*/\1/p'
}

GUEST1_ID=$(create_guest "Nina" "nina.guest@example.com" "+447000000020")
GUEST2_ID=$(create_guest "Bob" "bob.guest@example.com" "+447000000021")
GUEST3_ID=$(create_guest "Carol" "carol.guest@example.com" "+447000000022")
GUEST4_ID=$(create_guest "Diana" "nina.guest@example.com" "+447000000023")
GUEST5_ID=$(create_guest "Evil" "$USER_EMAIL" "+447000000024")

count_guest=0

guest_book() {
  local gid="$1" date="$2" svc_idx="$3"
  [[ -z "$gid" ]] && return
  local services="[\"$(get_svc $svc_idx)\"]"
  local duration
  duration=$(calc_duration "$services")
  local slot
  slot=$(pick_avail_slot "$date" "$duration")
  [[ -z "$slot" ]] && return
  local time
  time=$(format_london_time "$date" "$slot")
  local reserve_json="{\"start_time\":\"$time\",\"service_ids\":$services}"
  local res_resp=$(curl -s -w "\n%{http_code}" -X POST -H 'Content-Type: application/json' -d "$reserve_json" "$BASE_URL/bookings/reserve")
  local res_code=$(echo "$res_resp" | tail -n1)
  [[ ! "$res_code" =~ ^2 ]] && return
  local book_json="{\"user_id\":\"$gid\",\"start_time\":\"$time\",\"service_ids\":$services}"
  local book_resp=$(curl -s -w "\n%{http_code}" -X POST -H 'Content-Type: application/json' -d "$book_json" "$BASE_URL/bookings")
  local book_code=$(echo "$book_resp" | tail -n1)
  [[ "$book_code" =~ ^2 ]] && count_guest=$((count_guest+1))
}

G1_DATE=$(open_day "$(TZ=Europe/London date -d "$TODAY +16 days" +%Y-%m-%d)")
G2_DATE=$(open_day "$(TZ=Europe/London date -d "$TODAY +20 days" +%Y-%m-%d)")
G3_DATE=$(open_day "$(TZ=Europe/London date -d "$TODAY +22 days" +%Y-%m-%d)")
[[ -n "$GUEST1_ID" ]] && guest_book "$GUEST1_ID" "$G1_DATE" 0
[[ -n "$GUEST2_ID" ]] && guest_book "$GUEST2_ID" "$G2_DATE" 3
[[ -n "$GUEST3_ID" ]] && guest_book "$GUEST3_ID" "$G3_DATE" 1

echo "${C_GREEN}✅ Created $count_guest Guest Bookings${C_RESET}"



# ---------------------------------------------------------------------------
# CANCELLATIONS
# Both POST /admin/bookings/{id}/cancel and DELETE /bookings/{id} have backend
# bugs (enum value mismatch + FK violation). Patching status directly in DB.
# Auto-detects the actual enum values from pg_enum to avoid hardcoding.
# ---------------------------------------------------------------------------
echo -e "\n${C_BLUE}🚫 Simulating Cancellations...${C_RESET}"
cancel_count=0

# Discover actual booking_status enum values
ENUM_VALUES=$(docker exec postgres psql -U myuser -d mydb -tAc \
  "SELECT string_agg(enumlabel, ',' ORDER BY enumsortorder) FROM pg_enum e JOIN pg_type t ON e.enumtypid = t.oid WHERE t.typname = 'booking_status';" 2>/dev/null | tr -d '\r\n\t ')

CLIENT_CANCEL_STATUS=$(echo "$ENUM_VALUES" | tr ',' '\n' | grep -i 'client' | head -1)
ADMIN_CANCEL_STATUS=$(echo  "$ENUM_VALUES" | tr ',' '\n' | grep -iE 'we_|admin' | head -1)
[[ -z "$CLIENT_CANCEL_STATUS" ]] && CLIENT_CANCEL_STATUS=$(echo "$ENUM_VALUES" | tr ',' '\n' | grep -i 'cancel' | head -1)
[[ -z "$ADMIN_CANCEL_STATUS"  ]] && ADMIN_CANCEL_STATUS="$CLIENT_CANCEL_STATUS"

db_cancel() {
  local booking_id="$1" status="$2" reason="$3"
  [[ -z "$booking_id" || -z "$status" ]] && return 1
  # Discover the cancel reason column name (varies by schema)
  local reason_col
  reason_col=$(docker exec postgres psql -U myuser -d mydb -tAc \
    "SELECT column_name FROM information_schema.columns WHERE table_name='bookings' AND column_name IN ('cancel_reason','cancellation_reason','reason') LIMIT 1;" 2>/dev/null | head -1 | tr -d '\r\t ')
  local sql
  if [[ -n "$reason_col" ]]; then
    sql="UPDATE bookings SET status = '$status', $reason_col = '$reason' WHERE id = '$booking_id' RETURNING id;"
  else
    sql="UPDATE bookings SET status = '$status' WHERE id = '$booking_id' RETURNING id;"
  fi
  local result
  result=$(docker exec postgres psql -U myuser -d mydb -tAc "$sql" 2>&1)
  local rows
  rows=$(echo "$result" | head -1 | tr -d '\r\t ')
  if [[ -n "$rows" && "$rows" =~ ^[0-9a-f-]{8,}$ ]]; then
    return 0
  else
    echo "${C_RED}    SQL: $sql${C_RESET}"
    echo "${C_RED}    Result: $result${C_RESET}"
    return 1
  fi
}

D_CANCEL=$(open_day "$(TZ=Europe/London date -d "$TODAY +5 days" +%Y-%m-%d)")
if create_admin_booking "$AVA_ID" ""$D_CANCEL"" "[\"$(get_svc 0)\"]" "" "Ava - to be cancelled"; then
  if db_cancel "$LAST_BOOKING_ID" "$CLIENT_CANCEL_STATUS" "Something came up, really sorry!"; then
    cancel_count=$((cancel_count+1))
  else
    echo "${C_RED}❌ DB cancel failed for Ava${C_RESET}"
  fi
fi

D_CANCEL2=$(open_day "$(TZ=Europe/London date -d "$TODAY +7 days" +%Y-%m-%d)")
if create_admin_booking "$USER_USER_ID" ""$D_CANCEL2"" "[\"$(get_svc 2)\"]" "" "User - to be cancelled"; then
  if db_cancel "$LAST_BOOKING_ID" "$CLIENT_CANCEL_STATUS" "Plans changed, apologies for the late notice."; then
    cancel_count=$((cancel_count+1))
  else
    echo "${C_RED}❌ DB cancel failed for User${C_RESET}"
  fi
fi

D_ADMIN_CANCEL=$(open_day "$(TZ=Europe/London date -d "$TODAY +9 days" +%Y-%m-%d)")
if create_admin_booking "$NOAH_ID" ""$D_ADMIN_CANCEL"" "[\"$(get_svc 1)\"]" "" "Noah - to be admin-cancelled"; then
  if db_cancel "$LAST_BOOKING_ID" "$ADMIN_CANCEL_STATUS" "Slot no longer available due to schedule change."; then
    cancel_count=$((cancel_count+1))
  else
    echo "${C_RED}❌ DB cancel failed for Noah${C_RESET}"
  fi
fi

echo "${C_GREEN}✅ Simulated $cancel_count cancellations${C_RESET}"

# ===========================================================================
# 6b. PAYMENTS (seeded via SQL — no Square integration yet)
# Payment over the booking total counts as a tip.
# ===========================================================================
echo -e "\n${C_BLUE}💳 Creating Payments...${C_RESET}"

# Mark past confirmed bookings as completed so payments can be seeded
docker exec postgres psql -U myuser -d mydb -c \
  "UPDATE bookings SET status = 'completed'
   WHERE status = 'confirmed' AND start_time < NOW() - INTERVAL '1 hour'" > /dev/null 2>&1
completed_count=$(docker exec postgres psql -U myuser -d mydb -tAc \
  "SELECT COUNT(*) FROM bookings WHERE status = 'completed'" 2>/dev/null)

# --- Seeding campaigns and stacked discounts for user@example.com ---
echo -e "\n${C_BLUE}🎟️  Seeding Campaigns & Loyalty Stacked Discounts...${C_RESET}"
docker exec -i postgres psql -U myuser -d mydb << 'CAMPAIGN_SQL' > /dev/null 2>&1
DO $$
DECLARE
  u_id CHAR(12);
  past_camp_id CHAR(12);
  active_camp_id CHAR(12);
  milestone_camp_id CHAR(12);
  b_rec RECORD;
  b_idx INT := 1;
  red_id CHAR(12);
  b_total NUMERIC(10,2);
  loyalty_disc NUMERIC(10,2);
  camp_disc NUMERIC(10,2);
  balance NUMERIC(10,2);
BEGIN
  -- 1. Get user id
  SELECT id INTO u_id FROM users WHERE email = 'user@example.com';

  -- 2. Insert 3 campaigns
  INSERT INTO discount_campaigns (name, description, campaign_type, discount_percent, scope, start_date, end_date, status, max_redemptions)
  VALUES ('Demo Sale', 'Get 10% off visits because teehee', 'time_based', 10.00, 'all_bookings', NOW() - INTERVAL '30 days', NOW() - INTERVAL '10 days', 'active', 500)
  RETURNING id INTO past_camp_id;

  INSERT INTO discount_campaigns (name, description, campaign_type, discount_percent, scope, start_date, end_date, status, max_redemptions)
  VALUES ('Summer Sale', 'Enjoy 15% off all summer treatments', 'time_based', 15.00, 'all_bookings', NOW() - INTERVAL '1 day', NOW() + INTERVAL '30 days', 'active', 1000)
  RETURNING id INTO active_camp_id;

  INSERT INTO discount_campaigns (name, description, campaign_type, discount_percent, scope, status, milestone_type, milestone_value)
  VALUES ('10th Visit Celebration', 'Receive 20% off on your 10th milestone visit', 'milestone', 20.00, 'all_bookings', 'active', 'per_user_booking_count', 10)
  RETURNING id INTO milestone_camp_id;

  -- 3. Loop through completed bookings of user@example.com in chronological order
  FOR b_rec IN
    SELECT b.id, b.start_time,
           COALESCE(SUM(COALESCE(bs.override_price, s.price)), 0) as total
    FROM bookings b
    JOIN booking_services bs ON bs.booking_id = b.id
    JOIN services s ON bs.service_id = s.id
    WHERE b.user_id = u_id AND b.status = 'completed'
    GROUP BY b.id, b.start_time
    ORDER BY b.start_time ASC
  LOOP
    b_total := b_rec.total;

    -- Booking 6 (chronologically Day 18) reaches 10 stamps (stamps go 4 -> 5 -> 6 -> 7 -> 8 -> 9 -> 10)
    IF b_idx = 6 THEN
      INSERT INTO loyalty_redemptions (user_id, stamps_redeemed, status, redeemed_at)
      VALUES (u_id, 10, 'pending', b_rec.start_time)
      RETURNING id INTO red_id;
    END IF;

    -- Booking 7 (chronologically Day 15) consumes the redemption AND gets past campaign (since Day 15 is within past campaign dates)
    IF b_idx = 7 THEN
      -- Get the pending redemption
      SELECT id INTO red_id FROM loyalty_redemptions WHERE user_id = u_id AND status = 'pending' ORDER BY redeemed_at ASC LIMIT 1;

      IF red_id IS NOT NULL THEN
        loyalty_disc := ROUND(b_total * 0.10, 2);
        camp_disc := ROUND(b_total * 0.10, 2);
        balance := ROUND(b_total - loyalty_disc - camp_disc, 2);

        -- Create loyalty discount row
        INSERT INTO booking_discounts (booking_id, user_id, discount_source, source_id, discount_percent, original_total, discount_amount, applied_at)
        VALUES (b_rec.id, u_id, 'loyalty', red_id, 10.00, b_total, loyalty_disc, b_rec.start_time);

        -- Create loyalty payment row
        INSERT INTO payments (booking_id, payment_type, payment_method, amount, status, created_at)
        VALUES (b_rec.id, 'partial', 'discount', loyalty_disc, 'completed', b_rec.start_time);

        -- Update loyalty redemption
        UPDATE loyalty_redemptions
        SET status = 'applied', applied_to_booking_id = b_rec.id, applied_at = b_rec.start_time
        WHERE id = red_id;

        -- Create campaign discount row
        INSERT INTO booking_discounts (booking_id, user_id, discount_source, source_id, campaign_type, discount_percent, original_total, discount_amount, applied_at)
        VALUES (b_rec.id, u_id, 'campaign', past_camp_id, 'time_based', 10.00, b_total, camp_disc, b_rec.start_time);

        -- Create campaign payment row
        INSERT INTO payments (booking_id, payment_type, payment_method, amount, status, created_at)
        VALUES (b_rec.id, 'partial', 'discount', camp_disc, 'completed', b_rec.start_time);

        -- Increment campaign times_redeemed
        UPDATE discount_campaigns SET times_redeemed = times_redeemed + 1 WHERE id = past_camp_id;

        -- Create standard payment balance row
        INSERT INTO payments (booking_id, payment_type, payment_method, amount, status, created_at)
        VALUES (b_rec.id, 'balance', 'in_person_card', balance, 'completed', b_rec.start_time);
      END IF;

    -- Other bookings during past campaign (Bookings 1-5, and 8: Day 28, 26, 24, 22, 20, 11) get campaign discount only
    ELSIF b_rec.start_time >= (NOW() - INTERVAL '30 days') AND b_rec.start_time <= (NOW() - INTERVAL '10 days') THEN
      camp_disc := ROUND(b_total * 0.10, 2);
      balance := ROUND(b_total - camp_disc, 2);

      -- Create campaign discount row
      INSERT INTO booking_discounts (booking_id, user_id, discount_source, source_id, campaign_type, discount_percent, original_total, discount_amount, applied_at)
      VALUES (b_rec.id, u_id, 'campaign', past_camp_id, 'time_based', 10.00, b_total, camp_disc, b_rec.start_time);

      -- Create campaign payment row
      INSERT INTO payments (booking_id, payment_type, payment_method, amount, status, created_at)
      VALUES (b_rec.id, 'partial', 'discount', camp_disc, 'completed', b_rec.start_time);

      -- Increment campaign times_redeemed
      UPDATE discount_campaigns SET times_redeemed = times_redeemed + 1 WHERE id = past_camp_id;

      -- Create standard payment balance row
      INSERT INTO payments (booking_id, payment_type, payment_method, amount, status, created_at)
      VALUES (b_rec.id, 'balance', 'in_person_card', balance, 'completed', b_rec.start_time);
    END IF;

    b_idx := b_idx + 1;
  END LOOP;

  -- Update final stamps to 5
  UPDATE users SET loyalty_stamps = 5 WHERE id = u_id;
END $$;
CAMPAIGN_SQL

# Pure SQL payment seeding — randomized per booking, no bash loops
docker exec -i postgres psql -U myuser -d mydb << 'PAYMENT_SQL' > /dev/null 2>&1
DO $$
DECLARE
  rec RECORD;
  booking_total NUMERIC(10,2);
  scenario INT;
  deposit NUMERIC(10,2);
  partial NUMERIC(10,2);
  balance NUMERIC(10,2);
  tip NUMERIC(10,2);
  paid NUMERIC(10,2);
BEGIN
  FOR rec IN
    SELECT b.id,
           COALESCE(SUM(COALESCE(bs.override_price, s.price)), 0) as total
    FROM bookings b
    JOIN booking_services bs ON bs.booking_id = b.id
    JOIN services s ON bs.service_id = s.id
    WHERE b.status = 'completed'
      AND NOT EXISTS (SELECT 1 FROM payments WHERE booking_id = b.id)
    GROUP BY b.id
  LOOP
    booking_total := rec.total;
    scenario := floor(random() * 5)::INT;

    CASE scenario
      WHEN 0 THEN
        deposit := ROUND(booking_total * 0.25, 2);
        balance := ROUND(booking_total - deposit, 2);
        INSERT INTO payments (booking_id, payment_type, payment_method, amount, status)
        VALUES (rec.id, 'deposit', 'in_person_card', deposit, 'completed');
        INSERT INTO payments (booking_id, payment_type, payment_method, amount, status)
        VALUES (rec.id, 'balance', 'in_person_card', balance, 'completed');
      WHEN 1 THEN
        INSERT INTO payments (booking_id, payment_type, payment_method, amount, status)
        VALUES (rec.id, 'full', 'in_person_card', booking_total, 'completed');
      WHEN 2 THEN
        tip := ROUND((random() * 15 + 5)::NUMERIC, 2);
        paid := ROUND(booking_total + tip, 2);
        INSERT INTO payments (booking_id, payment_type, payment_method, amount, status)
        VALUES (rec.id, 'full', 'in_person_card', paid, 'completed');
        INSERT INTO payments (booking_id, payment_type, payment_method, amount, status)
        VALUES (rec.id, 'tip', 'in_person_card', tip, 'completed');
      WHEN 3 THEN
        partial := ROUND(booking_total * 0.5, 2);
        balance := ROUND(booking_total - partial, 2);
        tip := ROUND((random() * 10 + 3)::NUMERIC, 2);
        INSERT INTO payments (booking_id, payment_type, payment_method, amount, status)
        VALUES (rec.id, 'partial', 'in_person_card', partial, 'completed');
        INSERT INTO payments (booking_id, payment_type, payment_method, amount, status)
        VALUES (rec.id, 'balance', 'in_person_card', balance, 'completed');
        INSERT INTO payments (booking_id, payment_type, payment_method, amount, status)
        VALUES (rec.id, 'tip', 'in_person_card', tip, 'completed');
      WHEN 4 THEN
        deposit := ROUND(booking_total * 0.20, 2);
        partial := ROUND(booking_total * 0.30, 2);
        balance := ROUND(booking_total - deposit - partial, 2);
        tip := ROUND((random() * 20 + 5)::NUMERIC, 2);
        INSERT INTO payments (booking_id, payment_type, payment_method, amount, status)
        VALUES (rec.id, 'deposit', 'in_person_card', deposit, 'completed');
        INSERT INTO payments (booking_id, payment_type, payment_method, amount, status)
        VALUES (rec.id, 'partial', 'in_person_card', partial, 'completed');
        INSERT INTO payments (booking_id, payment_type, payment_method, amount, status)
        VALUES (rec.id, 'balance', 'in_person_card', balance, 'completed');
        INSERT INTO payments (booking_id, payment_type, payment_method, amount, status)
        VALUES (rec.id, 'tip', 'in_person_card', tip, 'completed');
    END CASE;
  END LOOP;
END $$;
PAYMENT_SQL

payment_count=$(docker exec postgres psql -U myuser -d mydb -tAc \
  "SELECT COUNT(DISTINCT booking_id) FROM payments" 2>/dev/null)
echo "${C_GREEN}✅ Created payments for $payment_count completed bookings${C_RESET}"

# --- Seeding refunds for user@example.com ---
echo -e "\n${C_BLUE}🔄 Creating Refunds...${C_RESET}"
docker exec -i postgres psql -U myuser -d mydb << 'REFUND_SQL' > /dev/null 2>&1
INSERT INTO refunds (payment_id, booking_id, amount, status, reason, created_by, created_at)
SELECT p.id, p.booking_id, p.amount, 'completed', 'Client cancelled – over 72h notice, full refund',
       u.id, p.created_at + INTERVAL '1 day'
FROM payments p
JOIN bookings b ON p.booking_id = b.id
JOIN users u ON b.user_id = u.id
WHERE u.email = 'user@example.com'
  AND p.payment_method = 'in_person_card'
  AND p.status = 'completed'
  AND p.payment_type = 'balance'
ORDER BY p.created_at ASC
LIMIT 1;

INSERT INTO refunds (payment_id, booking_id, amount, status, reason, created_by, created_at)
SELECT p.id, p.booking_id, ROUND(p.amount * 0.5, 2), 'completed',
       'Client cancelled – 24-72h notice, protected deposit retained',
       u.id, p.created_at + INTERVAL '7 days'
FROM payments p
JOIN bookings b ON p.booking_id = b.id
JOIN users u ON b.user_id = u.id
WHERE u.email = 'user@example.com'
  AND p.payment_method = 'in_person_card'
  AND p.status = 'completed'
  AND p.payment_type = 'deposit'
ORDER BY p.created_at ASC
LIMIT 1;

-- Clear the LAST_BOOKING_ID that may have been set by the refund operations
REFUND_ID=""
REFUND_SQL

refund_count=$(docker exec postgres psql -U myuser -d mydb -tAc \
  "SELECT COUNT(*) FROM refunds r JOIN payments p ON r.payment_id = p.id JOIN bookings b ON p.booking_id = b.id WHERE b.user_id = (SELECT id FROM users WHERE email = 'user@example.com')" 2>/dev/null)
echo "${C_GREEN}✅ Created $refund_count refunds for user@example.com${C_RESET}"

# ===========================================================================
# 5. EXCEPTIONAL SCHEDULING GROUPS
# ===========================================================================
echo -e "\n${C_BLUE}🗓️ Creating Exceptional Schedule Groups...${C_RESET}"
sched_success=0

# Easter break — fully closed
EASTER_BREAK='{
  "name":"Easter Break",
  "description":"Closed for Easter weekend",
  "hours":[
    {"weekday":0,"startTime":"00:00:00","endTime":"00:00:00","isOpen":false},
    {"weekday":1,"startTime":"00:00:00","endTime":"00:00:00","isOpen":false},
    {"weekday":2,"startTime":"00:00:00","endTime":"00:00:00","isOpen":false},
    {"weekday":3,"startTime":"00:00:00","endTime":"00:00:00","isOpen":false},
    {"weekday":4,"startTime":"00:00:00","endTime":"00:00:00","isOpen":false},
    {"weekday":5,"startTime":"00:00:00","endTime":"00:00:00","isOpen":false},
    {"weekday":6,"startTime":"00:00:00","endTime":"00:00:00","isOpen":false}
  ],
  "weekStarts":["2026-04-06"]
}'

# Summer half-term — reduced hours (mornings only, Tue-Sat)
HALF_TERM='{
  "name":"Spring Half-Term",
  "description":"Reduced hours during half-term week — mornings only",
  "hours":[
    {"weekday":0,"startTime":"00:00:00","endTime":"00:00:00","isOpen":false},
    {"weekday":1,"startTime":"09:30:00","endTime":"13:00:00","isOpen":true},
    {"weekday":2,"startTime":"09:30:00","endTime":"13:00:00","isOpen":true},
    {"weekday":3,"startTime":"09:30:00","endTime":"13:00:00","isOpen":true},
    {"weekday":4,"startTime":"09:30:00","endTime":"13:00:00","isOpen":true},
    {"weekday":5,"startTime":"09:30:00","endTime":"13:00:00","isOpen":true},
    {"weekday":6,"startTime":"00:00:00","endTime":"00:00:00","isOpen":false}
  ],
  "weekStarts":["2026-06-01"]
}'

# Christmas holiday — fully closed across two weeks
XMAS_BREAK='{
  "name":"Christmas Holiday Period",
  "description":"Closed for Christmas and New Year",
  "hours":[
    {"weekday":0,"startTime":"00:00:00","endTime":"00:00:00","isOpen":false},
    {"weekday":1,"startTime":"00:00:00","endTime":"00:00:00","isOpen":false},
    {"weekday":2,"startTime":"00:00:00","endTime":"00:00:00","isOpen":false},
    {"weekday":3,"startTime":"00:00:00","endTime":"00:00:00","isOpen":false},
    {"weekday":4,"startTime":"00:00:00","endTime":"00:00:00","isOpen":false},
    {"weekday":5,"startTime":"00:00:00","endTime":"00:00:00","isOpen":false},
    {"weekday":6,"startTime":"00:00:00","endTime":"00:00:00","isOpen":false}
  ],
  "weekStarts":["2026-12-21","2026-12-28"]
}'

if api_post "$BASE_URL/scheduling/exceptional-groups" "$EASTER_BREAK" "Easter Break" "$ADMIN_TOKEN" > /dev/null; then sched_success=$((sched_success+1)); fi
if api_post "$BASE_URL/scheduling/exceptional-groups" "$HALF_TERM"   "Spring Half-Term" "$ADMIN_TOKEN" > /dev/null; then sched_success=$((sched_success+1)); fi
if api_post "$BASE_URL/scheduling/exceptional-groups" "$XMAS_BREAK"  "Christmas Break" "$ADMIN_TOKEN" > /dev/null; then sched_success=$((sched_success+1)); fi

echo "${C_GREEN}✅ Created $sched_success/3 Exceptional Schedule Groups${C_RESET}"

# ===========================================================================
# 7b. EDIT REQUESTS (for testing the edit request UI)
# ===========================================================================
echo -e "\n${C_BLUE}✏️ Creating Edit Requests...${C_RESET}"
edit_req_count=0

# Create edit requests via the user API for upcoming confirmed bookings
# Use a wider time window to find more bookings (any future confirmed booking)
CONFIRMED_BOOKINGS=$(docker exec postgres psql -U myuser -d mydb -tAc \
  "SELECT b.id, b.user_id, b.start_time FROM bookings b
   WHERE b.status = 'confirmed' AND b.start_time > NOW()
   ORDER BY b.start_time ASC LIMIT 8;" 2>/dev/null)

if [[ -n "$CONFIRMED_BOOKINGS" ]]; then
  req_idx=0
  while IFS='|' read -r booking_id user_id start_time; do
    [[ -z "$booking_id" ]] && continue
    # Get user token
    user_email=$(docker exec postgres psql -U myuser -d mydb -tAc \
      "SELECT email FROM users WHERE id = '$user_id'" 2>/dev/null | tr -d '\r\t ')
    [[ -z "$user_email" ]] && continue
    user_tok=$(login "$user_email" "password")
    [[ -z "$user_tok" ]] && continue

    # Alternate between time-only and service+time requests
    if (( req_idx % 3 == 0 )); then
      # Time-only request
      new_time=$(TZ=Europe/London date -d "$start_time +2 hours" +"%Y-%m-%dT%H:%M:%S%:z" 2>/dev/null)
      [[ -z "$new_time" ]] && continue
      resp=$(curl -s -w "\n%{http_code}" -X POST \
        -H 'Content-Type: application/json' \
        -H "Authorization: Bearer $user_tok" \
        -d "{\"new_start_time\":\"$new_time\",\"notes\":\"Would like to move this appointment 2 hours later please\"}" \
        "$BASE_URL/bookings/$booking_id/edit-request")
    elif (( req_idx % 3 == 1 )); then
      # Service change request (add nail art)
      resp=$(curl -s -w "\n%{http_code}" -X POST \
        -H 'Content-Type: application/json' \
        -H "Authorization: Bearer $user_tok" \
        -d "{\"new_services\":[\"$(get_svc 0)\",\"$(get_svc 5)\"],\"notes\":\"Would like to add nail art to my appointment\"}" \
        "$BASE_URL/bookings/$booking_id/edit-request")
    else
      # Both time and services
      new_time=$(TZ=Europe/London date -d "$start_time -1 hours" +"%Y-%m-%dT%H:%M:%S%:z" 2>/dev/null)
      [[ -z "$new_time" ]] && continue
      resp=$(curl -s -w "\n%{http_code}" -X POST \
        -H 'Content-Type: application/json' \
        -H "Authorization: Bearer $user_tok" \
        -d "{\"new_start_time\":\"$new_time\",\"new_services\":[\"$(get_svc 1)\"],\"notes\":\"Need to reschedule earlier and switch to gel\"}" \
        "$BASE_URL/bookings/$booking_id/edit-request")
    fi
    code=$(echo "$resp" | tail -n1)
    if [[ "$code" =~ ^2 ]]; then
      edit_req_count=$((edit_req_count+1))
    fi
    req_idx=$((req_idx+1))
  done <<< "$CONFIRMED_BOOKINGS"
fi

echo "${C_GREEN}✅ Created $edit_req_count Edit Requests${C_RESET}"

# ===========================================================================
# 7c. NAME HISTORY (for testing "formerly" display)
# ===========================================================================
echo -e "\n${C_BLUE}📝 Creating name history entry for test user...${C_RESET}"
USER_TOKEN=$(login "user@example.com" "password")
if [[ -n "$USER_TOKEN" ]]; then
  name_resp=$(curl -s -w "\n%{http_code}" -X PUT \
    -H 'Content-Type: application/json' \
    -H "Authorization: Bearer $USER_TOKEN" \
    -d '{"firstName":"Regular","lastName":"Joe","phone":"+447000000001"}' \
    "$BASE_URL/user/profile")
  name_code=$(echo "$name_resp" | tail -n1)
  if [[ "$name_code" =~ ^2 ]]; then
    echo "${C_GREEN}✅ Name history entry created (Regular User → Joe shows as 'formerly')${C_RESET}"
  else
    echo "${C_YELLOW}⚠️ Failed to update user name (HTTP $name_code)${C_RESET}"
  fi
else
  echo "${C_YELLOW}⚠️ Could not log in as test user for name change${C_RESET}"
fi

# ===========================================================================
# SUMMARY
# ===========================================================================
echo ""
echo -e "${C_GREEN}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${C_RESET}"
echo -e "${C_GREEN}🎉 Seeding Complete!${C_RESET}"
echo -e "${C_GREEN}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${C_RESET}"
echo -e "  Users registered   : $user_success/$user_total"
echo -e "  Services created   : ${#SERVICE_IDS[@]}/${#SERVICES[@]}"
if [[ -n "$PATCH_TEST_ID" ]]; then
  echo -e "  Patch tests        : 1 (Gel Allergy Test, recorded for Grace)"
else
  echo -e "  Patch tests        : ⚠️ failed — see output above"
fi
echo -e "  Bookings — past    : $count_past"
echo -e "  Bookings — today   : $count_today"
echo -e "  Bookings — tomorrow: $count_tomorrow"
echo -e "  Bookings — future  : $count_future"
echo -e "  Bookings — user    : $USER_NOTE_COUNT (public endpoint)"
echo -e "  Bookings — guest   : $count_guest"
echo -e "  Bookings — total   : $((count_past + count_today + count_tomorrow + count_future + count_guest))"
echo -e "  Cancellations      : $cancel_count"
echo -e "  Confirmed          : $confirmed_count  |  Still pending: $skipped_count"
echo -e "  Payments           : $payment_count completed bookings"
echo -e "  Time blockers      : $count_blockers"
echo -e "  Edit requests      : $edit_req_count"
echo -e "  Schedule groups    : $sched_success/3"
echo ""
echo -e "  Quick login creds (all pass: ${C_YELLOW}password${C_RESET})"
echo -e "  Admin  : ${C_YELLOW}admin@example.com${C_RESET}"
echo -e "  User   : ${C_YELLOW}user@example.com${C_RESET}"
echo -e "  Deposit: ${C_YELLOW}poppy.thompson@example.com${C_RESET}"
echo -e "  Gel ✓  : ${C_YELLOW}grace.fletcher@example.com${C_RESET} (patch test done)"
echo -e "${C_GREEN}━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━${C_RESET}"

echo -e "\n${C_YELLOW}Press ENTER to run tests...${C_RESET}"
read -r
echo -e "${C_GREEN}⏳ Running tests...${C_RESET}"

cd /home/popertots/Crussell/backend
export POSTGRES_USER=myuser POSTGRES_PASSWORD=mypassword POSTGRES_HOST=localhost POSTGRES_DB=crussell_test GO_TESTING=1
TEST_OUTPUT_FILE=$(mktemp)
START_TIME=$(date +%s)
  go test -tags "test,dev" -v -count=1 ./... 2>&1 | tee "$TEST_OUTPUT_FILE" || true
END_TIME=$(date +%s)
DURATION=$((END_TIME - START_TIME))
MINUTES=$((DURATION / 60))
SECONDS=$((DURATION % 60))
TEST_OUTPUT=$(cat "$TEST_OUTPUT_FILE")
rm -f "$TEST_OUTPUT_FILE"
cd ..

TOTAL_TESTS=$(echo "$TEST_OUTPUT" | grep "^=== RUN" | grep -cv "/" || echo "0")
FAILED_TESTS=$(echo "$TEST_OUTPUT" | grep -c "^--- FAIL" || echo "0")
SKIPPED_TESTS=$(echo "$TEST_OUTPUT" | grep -c "^--- SKIP" || echo "0")
PASSED_TESTS=$(echo "$TEST_OUTPUT" | grep -c "^--- PASS" || echo "0")
TOTAL_TESTS=${TOTAL_TESTS:-0}
PASSED_TESTS=${PASSED_TESTS:-0}
FAILED_TESTS=${FAILED_TESTS:-0}
SKIPPED_TESTS=${SKIPPED_TESTS:-0}

echo ""
echo -e "${C_YELLOW}⏱️  Duration: ${MINUTES}m ${SECONDS}s${C_RESET}"
if [ "$SKIPPED_TESTS" -gt 0 ] 2>/dev/null; then
  echo -e "${C_YELLOW}⚠️ Tests Skipped: $SKIPPED_TESTS${C_RESET}"
fi
if [ "$FAILED_TESTS" -gt 0 ] 2>/dev/null; then
  echo -e "${C_RED}❌ Tests Failed: $FAILED_TESTS/$TOTAL_TESTS failed${C_RESET}"
  echo ""
  echo -e "${C_RED}--- Failed Tests ---${C_RESET}"
  echo "$TEST_OUTPUT" | grep "^--- FAIL" | head -20
else
  echo -e "${C_GREEN}✅ All Tests Passed: $PASSED_TESTS/$TOTAL_TESTS${C_RESET}"
fi
echo ""

echo -e "${C_YELLOW}Press ENTER to restore 4-pane layout...${C_RESET}"
read -r

tmux select-window -t $SESSION_NAME:0
tmux select-pane -t $SESSION_NAME:0.0

SEED_EOF

chmod +x $SEED_SCRIPT

# --- 6. Execute Seed Script in Tmux ---
log_step "Starting seeding process in new window..."
tmux new-window -t $SESSION_NAME -n "Seeding" "SESSION_NAME=$SESSION_NAME $SEED_SCRIPT"

# --- 7. Finalize ---
trap 'rm -f $SEED_SCRIPT' EXIT
log_success "Environment ready. Attaching to session..."
tmux attach-session -t $SESSION_NAME
