scripts/migrate_mongo_to_supabase.py

import os
import uuid
import datetime
from pymongo import MongoClient
from supabase import create_client, Client
from dotenv import load_dotenv

load_dotenv()

# MongoDB Configuration
MONGO_URI = os.getenv("MONGO_URI", "mongodb://localhost:27017/")
MONGO_DB = "medical_kb"

# Supabase Configuration
SUPABASE_URL = os.getenv("SUPABASE_URL")
# Use Service Role Key for migration to bypass RLS
SUPABASE_KEY = os.getenv("SUPABASE_SERVICE_ROLE_KEY") or os.getenv("SUPABASE_KEY")

DEFAULT_PATIENT_ID = os.getenv("DEFAULT_PATIENT_UUID")

def migrate():
    print("Starting migration from MongoDB to Supabase...")
    
    if not SUPABASE_URL or not SUPABASE_KEY:
        print("Error: Supabase configuration missing.")
        return

    mongo_client = MongoClient(MONGO_URI)
    mongo_db = mongo_client[MONGO_DB]
    supabase: Client = create_client(SUPABASE_URL, SUPABASE_KEY)

    # 1. Migrate Patients
    print("Migrating Patients...")
    patients = list(mongo_db["patient"].find())
    for p in patients:
        p_id = p.get("id") or str(p.get("_id"))
        if p_id == "anonymous":
            p_id = DEFAULT_PATIENT_ID
            
        resource = {
            "resourceType": "Patient",
            "id": p_id,
            "name": p.get("name", [{"text": "Unknown"}]),
            "active": p.get("active", True)
        }
        data = {
            "id": p_id,
            "resource": resource,
            "last_updated": datetime.datetime.now(datetime.timezone.utc).isoformat()
        }
        supabase.table("patients").upsert(data).execute()
    print(f"Migrated {len(patients)} patients.")

    # 2. Migrate Observations
    print("Migrating Observations...")
    observations = list(mongo_db["observation"].find())
    for o in observations:
        obs_id = o.get("id") or str(o.get("_id"))
        # Map patient reference
        ref = o.get("subject", {}).get("reference", "")
        patient_id = None
        if "Patient/" in ref:
            patient_id = ref.split("/")[-1]
        
        if not patient_id or patient_id == "anonymous":
            patient_id = DEFAULT_PATIENT_ID
            
        o.pop("_id", None)
        data = {
            "id": obs_id,
            "patient_id": patient_id,
            "resource": o,
            "last_updated": datetime.datetime.now(datetime.timezone.utc).isoformat()
        }
        supabase.table("observations").upsert(data).execute()
    print(f"Migrated {len(observations)} observations.")

    # 3. Migrate Communications (Chat History)
    print("Migrating Communications...")
    comms = list(mongo_db["patient_data"].find())
    for c in comms:
        c_id = c.get("id") or str(c.get("_id"))
        ref = c.get("subject", {}).get("reference", "")
        patient_id = None
        if "Patient/" in ref:
            patient_id = ref.split("/")[-1]
            
        if not patient_id or patient_id == "anonymous":
            patient_id = DEFAULT_PATIENT_ID
            
        c.pop("_id", None)
        data = {
            "id": c_id,
            "patient_id": patient_id,
            "resource": c,
            "last_updated": datetime.datetime.now(datetime.timezone.utc).isoformat()
        }
        supabase.table("communications").upsert(data).execute()
    print(f"Migrated {len(comms)} communications.")

    print("Migration complete!")

if __name__ == "__main__":
    migrate()