#!/bin/bash
# Worker Supply Dashboard — queries Supabase directly
# Usage: bash /home/ccuser/susan/scripts/supply-dashboard.sh

source /home/ccuser/the-50-dollar-app/.env.local
BASE="https://eciepjpcyfurbkfzekok.supabase.co/rest/v1"
AUTH="-H \"apikey: $NEXT_PUBLIC_SUPABASE_ANON_KEY\" -H \"Authorization: Bearer $SUPABASE_SERVICE_ROLE_KEY\""

echo "=========================================="
echo "  RATERIGHT WORKER SUPPLY DASHBOARD"
echo "  $(date '+%Y-%m-%d %H:%M %Z')"
echo "=========================================="
echo ""

# Workers
WORKERS=$(eval curl -s "$BASE/profiles?select=id,suburb,onboarding_completed&type=eq.worker" $AUTH)
# Contractors
CONTRACTORS=$(eval curl -s "$BASE/profiles?select=id,suburb,onboarding_completed&type=eq.contractor" $AUTH)
# Worker details
WORKER_DETAILS=$(eval curl -s "$BASE/worker_profiles?select=profile_id,trades,availability" $AUTH)
# Jobs
JOBS=$(eval curl -s "$BASE/jobs?select=id,trade,suburb,status" $AUTH)
# Matches
MATCHES=$(eval curl -s "$BASE/matches?select=id,status" $AUTH)

python3 << 'PYEOF'
import json, sys

workers = json.loads('''WORKERS_PLACEHOLDER''')
contractors = json.loads('''CONTRACTORS_PLACEHOLDER''')
worker_details = json.loads('''DETAILS_PLACEHOLDER''')
jobs = json.loads('''JOBS_PLACEHOLDER''')
matches = json.loads('''MATCHES_PLACEHOLDER''')

# Totals
w_total = len(workers)
w_onboarded = sum(1 for w in workers if w.get('onboarding_completed'))
c_total = len(contractors)
c_onboarded = sum(1 for c in contractors if c.get('onboarding_completed'))

print(f"USERS:      {w_total + c_total} total ({w_total} workers, {c_total} contractors)")
print(f"ONBOARDED:  {w_onboarded} workers, {c_onboarded} contractors")
print(f"JOBS:       {len(jobs)} ({sum(1 for j in jobs if j.get('status')=='active')} active)")
print(f"MATCHES:    {len(matches)} ({sum(1 for m in matches if m.get('status')=='hired')} hired)")
print()

# Workers by suburb (city mapping)
SYDNEY_SUBURBS = {'Parramatta','Botany','Randwick','Sydney','Blacktown','Penrith',
    'Chatswood','Liverpool','Campbelltown','Burwood','Bankstown','Matraville',
    'North Sydney','Miranda','Matraville, New South Wales'}

print("WORKER SUPPLY BY CITY:")
print("-" * 50)
sydney = sum(1 for w in workers if w.get('onboarding_completed') and (w.get('suburb','') or '') in SYDNEY_SUBURBS or 'Sydney' in (w.get('suburb','') or '') or 'New South Wales' in (w.get('suburb','') or ''))
# For now all are Sydney metro
for w in workers:
    s = w.get('suburb') or 'Unknown'

onboarded_suburbs = {}
for w in workers:
    if w.get('onboarding_completed'):
        s = w.get('suburb') or 'Unknown'
        onboarded_suburbs[s] = onboarded_suburbs.get(s, 0) + 1

print(f"  Sydney Metro:    {w_onboarded} workers (threshold: 25)")
if w_onboarded >= 25:
    print(f"    ✅ THRESHOLD MET — contractor outreach unlocked")
else:
    print(f"    🔴 {25 - w_onboarded} more needed to unlock contractor outreach")
print(f"  Newcastle:       0 workers (threshold: 15)")
print(f"    🔴 15 more needed")
print(f"  Melbourne:       0 workers (threshold: 20)")
print(f"    ⬜ Phase 2")
print()

# Trade breakdown
trades = {}
for wd in worker_details:
    for t in (wd.get('trades') or []):
        trades[t] = trades.get(t, 0) + 1

print("TRADE MIX:")
print("-" * 50)
for t, c in sorted(trades.items(), key=lambda x: -x[1]):
    bar = '█' * c
    print(f"  {t:20s} {bar} {c}")

# Thresholds check
needed = {'Formworker': 3, 'Steelfixer': 3, 'Concreter': 3, 'Labourer': 5}
# Map variations
trade_map = {'Steel Fixer': 'Steelfixer'}
mapped = {}
for t, c in trades.items():
    key = trade_map.get(t, t)
    mapped[key] = mapped.get(key, 0) + c

print()
print("TRADE THRESHOLD CHECK (Sydney):")
for trade, req in needed.items():
    have = mapped.get(trade, 0)
    status = '✅' if have >= req else '🔴'
    print(f"  {status} {trade}: {have}/{req}")

print()
print(f"SUBURBS COVERED: {len(onboarded_suburbs)}")
for s, c in sorted(onboarded_suburbs.items(), key=lambda x: -x[1]):
    print(f"  {s}: {c}")
PYEOF