#!/usr/bin/env bash
set -euo pipefail

REPO_ROOT="$(cd "$(dirname "$0")/.." && pwd)"
DB_PATH="$REPO_ROOT/data/DB/dbt_nba.duckdb"
REPORTS_DB="$REPO_ROOT/dbt_nba/reports/sources/nba/dbt_nba.duckdb"

# Load .env if it exists
if [ -f "$REPO_ROOT/.env" ]; then
  set -a
  source "$REPO_ROOT/.env"
  set +a
fi

# Check prerequisites
if [ -z "${POSTGRES_URL:-}" ]; then
  echo "ERROR: POSTGRES_URL environment variable is not set" >&2
  echo "Please export POSTGRES_URL or configure it in $REPO_ROOT/.env" >&2
  exit 1
fi

echo "==> [1/4] Extracting from Postgres into DuckDB..."
mkdir -p "$(dirname "$DB_PATH")"
mkdir -p "$(dirname "$REPORTS_DB")"
rm -f "$DB_PATH"
envsubst < "$REPO_ROOT/scripts/extract.sql" | duckdb "$DB_PATH"

echo "==> [2/4] Running dbt build --full-refresh..."
export DBT_PROJECT_DIR="$REPO_ROOT/dbt_nba"
export DBT_PROFILES_DIR="$REPO_ROOT/dbt_nba"
export DBT_DUCKDB_PATH="$DB_PATH"

uv run --project "$REPO_ROOT" dbt deps --project-dir "$DBT_PROJECT_DIR" --profiles-dir "$DBT_PROFILES_DIR"
uv run --project "$REPO_ROOT" dbt build --full-refresh --project-dir "$DBT_PROJECT_DIR" --profiles-dir "$DBT_PROFILES_DIR"

echo "==> [3/4] Copying database to reports directory..."
cp "$DB_PATH" "$REPORTS_DB"

echo "==> [4/4] Syncing database artifact to Cloudflare R2 / S3..."
if [ -n "${S3_ENDPOINT_URL:-}" ] || [ -n "${R2_ENDPOINT_URL:-}" ]; then
  uv run --project "$REPO_ROOT" python "$REPO_ROOT/scripts/db_storage.py" upload
  echo "==> Cloudflare R2 upload complete!"
else
  echo "==> S3_ENDPOINT_URL not set; skipping remote upload (local DB ready)."
fi

echo "==> Pipeline complete successfully!"