#!/bin/bash
set -e

# ---- CONFIG ----
BASE=~/Desktop/MCRO_export
CSV="$BASE/csv"
HOST=aws-0-us-west-2.pooler.supabase.com
DB=postgres
USER=postgres.ibfmjtwahkwqzcmeyqii
PSQL="psql -h $HOST -p 5432 -d $DB -U $USER"
PGDUMP="pg_dump -h $HOST -p 5432 -d $DB -U $USER"

mkdir -p "$CSV"
cd "$BASE"

echo "========================================="
echo "  MCRO FULL EXPORT — $(date)"
echo "========================================="

# ---- 1. SCHEMA DUMP (public + api) ----
echo ""
echo ">>> SCHEMA DUMP (public + api)..."
$PGDUMP --schema-only -n public -n api -f "$BASE/schema_only.sql"

# ---- 2. FULL COMPRESSED BACKUP ----
echo ""
echo ">>> FULL COMPRESSED BACKUP..."
$PGDUMP -Fc -f "$BASE/full_backup.dump"

# ---- 3. PUBLIC TABLES (53) ----
echo ""
echo ">>> PUBLIC TABLES..."
$PSQL -t -A -c "SELECT tablename FROM pg_tables WHERE schemaname = 'public' ORDER BY tablename;" | while read table; do
  echo "  TABLE: $table"
  $PSQL -c "\COPY $table TO '$CSV/${table}.csv' WITH CSV HEADER"
done

# ---- 4. PUBLIC VIEWS (10) ----
echo ""
echo ">>> PUBLIC VIEWS..."
$PSQL -t -A -c "SELECT viewname FROM pg_views WHERE schemaname = 'public' ORDER BY viewname;" | while read view; do
  echo "  VIEW: $view"
  $PSQL -c "\COPY (SELECT * FROM $view) TO '$CSV/${view}__view.csv' WITH CSV HEADER"
done

# ---- 5. API TABLES (3) ----
echo ""
echo ">>> API TABLES..."
for table in hp_export_context hp_pair_context hp_params; do
  echo "  API TABLE: $table"
  $PSQL -c "\COPY api.$table TO '$CSV/api__${table}.csv' WITH CSV HEADER"
done

# ---- 6. API MATERIALIZED VIEWS (16) ----
echo ""
echo ">>> API MATERIALIZED VIEWS..."
$PSQL -t -A -c "SELECT matviewname FROM pg_matviews WHERE schemaname = 'api' ORDER BY matviewname;" | while read mv; do
  echo "  API MATVIEW: $mv"
  $PSQL -c "\COPY (SELECT * FROM api.$mv) TO '$CSV/api__${mv}__matview.csv' WITH CSV HEADER"
done

# ---- 7. API VIEWS (31) ----
echo ""
echo ">>> API VIEWS (some may timeout — matviews have the same data pre-computed)..."
$PSQL -t -A -c "SELECT viewname FROM pg_views WHERE schemaname = 'api' ORDER BY viewname;" | while read view; do
  echo "  API VIEW: $view"
  $PSQL -c "\COPY (SELECT * FROM api.$view) TO '$CSV/api__${view}__view.csv' WITH CSV HEADER" 2>&1 || echo "    SKIPPED: $view (timeout or requires params)"
done

# ---- 8. ZIP ----
echo ""
echo ">>> ZIPPING CSVs..."
cd "$BASE"
zip -r mcro_csv_export.zip csv/

echo ""
echo "========================================="
echo "  DONE — $(date)"
echo "========================================="
echo ""
echo "Contents of $BASE:"
ls -lh "$BASE"
echo ""
echo "CSV count:"
ls "$CSV"/*.csv 2>/dev/null | wc -l
```

**What this produces in `~/Desktop/MCRO_export/`:**
```
MCRO_export/
├── schema_only.sql          ← DDL for public + api schemas
├── full_backup.dump         ← compressed full backup (pg_restore-compatible)
├── mcro_csv_export.zip      ← zipped CSVs
└── csv/
    ├── children.csv                    ← 53 public tables
    ├── parents.csv
    ├── ...
    ├── v_case_identity_best__view.csv  ← 10 public views
    ├── ...
    ├── api__hp_params.csv              ← 3 api tables
    ├── api__mv_hp_rows_bodyish__matview.csv  ← 16 api matviews
    ├── ...
    └── api__search_docs__view.csv      ← 31 api views