"""
Seed reference_ranges table in Supabase from data/reference_ranges.json

Usage:
    cd labcard-backend
    python scripts/seed_reference_ranges.py

Requires SUPABASE_URL and SUPABASE_SERVICE_KEY in .env
"""
from __future__ import annotations

import json
import sys
from pathlib import Path

# Allow running from repo root
sys.path.insert(0, str(Path(__file__).parent.parent))

from dotenv import load_dotenv
load_dotenv(".env")

from app.config import get_settings
from app.db.supabase import get_supabase


def main() -> None:
    settings = get_settings()
    if not settings.has_supabase:
        print("❌  SUPABASE_URL / SUPABASE_SERVICE_KEY not set in .env")
        sys.exit(1)

    db = get_supabase()
    if db is None:
        print("❌  Supabase client failed to initialise")
        sys.exit(1)

    data_file = Path(__file__).parent.parent / "data" / "reference_ranges.json"
    payload   = json.loads(data_file.read_text())
    ranges    = payload["ranges"]

    rows = []
    for r in ranges:
        rows.append({
            "biomarker":      r["name"],
            "std_name":       r.get("std", r["name"]),
            "gender":         r.get("gender", "A"),
            "age_min":        r.get("age_min", 0),
            "age_max":        r.get("age_max", 120),
            "normal_low":     r.get("low"),
            "normal_high":    r.get("high"),
            "critical_low":   r.get("critical_low"),
            "critical_high":  r.get("critical_high"),
            "unit":           r.get("unit", ""),
            "category":       r.get("category", "Other"),
            "notes":          r.get("notes", ""),
        })

    # Upsert — safe to re-run
    result = db.table("reference_ranges").upsert(rows, on_conflict="biomarker,gender").execute()
    print(f"✅  Seeded {len(rows)} reference ranges")
    print(f"    Supabase response rows: {len(result.data)}")


if __name__ == "__main__":
    main()