#!/bin/bash
# =============================================================================
# rebuild.sh — Reconstructs the STC database from scratch on a clean PostgreSQL instance.
# Run AFTER creating the DB and the two users (elfi1953 + elfi1953_stc-eu).
#
# Usage:
#   PGPASSWORD='pass' ./database/rebuild.sh host port user dbname
#
# The schema/ folder must be up-to-date (run extract_schema.sh after every migration).
# =============================================================================

set -e

DB_HOST="${1:?Usage: PGPASSWORD='pass' $0 host port user dbname}"
DB_PORT="${2:?Missing port}"
DB_USER="${3:?Missing user}"
DB_NAME="${4:?Missing database name}"

BASE="$(cd "$(dirname "$0")" && pwd)"
SCHEMA="$BASE/schema"

psql_run() {
  psql -h "$DB_HOST" -p "$DB_PORT" -U "$DB_USER" -d "$DB_NAME" -f "$1" -v ON_ERROR_STOP=1
}

echo "════════════════════════════════════════════"
echo "  STC Database rebuild — $DB_NAME"
echo "════════════════════════════════════════════"
echo ""

# ─── 1. Types (domains and enums) ────────────────────────────────────────────

echo "── Types ──"
for f in \
  valid_email valid_timestamp intl_phone iso_country \
  account_status enrollment_status \
  participant_type session_status; do
  psql_run "$SCHEMA/types/${f}.sql"
  echo "  ✓ $f"
done

# ─── 2. Tables (FK dependency order) ─────────────────────────────────────────

echo ""
echo "── Tables ──"
for tbl in \
  partners \
  courses \
  registration_messages \
  guests \
  promotion_reasons \
  system_config \
  tutor_profiles \
  professor_profiles \
  superadmin_profiles \
  levels \
  course_config \
  email_templates \
  email_dispatch_rules \
  course_professor_levels \
  course_admins \
  student_profiles \
  registrations_true \
  student_lifecycle_log \
  session_schedules \
  sessions \
  session_participants \
  session_log \
  session_notifications_log \
  session_schedule_exceptions; do
  psql_run "$SCHEMA/tables/${tbl}.sql"
  echo "  ✓ $tbl"
done

# ─── 3. Views ─────────────────────────────────────────────────────────────────

echo ""
echo "── Views ──"
for v in \
  vw_user_identity_roles \
  vw_partner_list \
  vw_student_management_master; do
  psql_run "$SCHEMA/views/${v}.sql"
  echo "  ✓ $v"
done

# ─── 4. Functions ─────────────────────────────────────────────────────────────

echo ""
echo "── Functions ──"
for f in "$SCHEMA/functions/"*.sql; do
  psql_run "$f"
  echo "  ✓ $(basename "$f" .sql)"
done

# ─── 5. Seed data ─────────────────────────────────────────────────────────────

echo ""
echo "── Seed data ──"
for tbl in \
  courses \
  levels \
  registration_messages \
  email_templates \
  email_dispatch_rules \
  promotion_reasons \
  system_config; do
  psql_run "$SCHEMA/seed/${tbl}.sql"
  echo "  ✓ $tbl"
done

# ─── 6. Grants ────────────────────────────────────────────────────────────────

echo ""
echo "── Grants ──"
psql_run "$SCHEMA/grants.sql"
echo "  ✓ grants applied"

echo ""
echo "════════════════════════════════════════════"
echo "  ✓ Rebuild complete"
echo "════════════════════════════════════════════"