scripts/migrate_supabase.py

"""
Run this script once to create the Supabase users table.
Usage: python scripts/migrate_supabase.py

Requires the SUPABASE_URL and SUPABASE_SERVICE_KEY env vars to be set.
This uses the Supabase REST API to execute SQL via the pg_query extension.
If that fails, manually run migrations/001_create_users.sql in your Supabase SQL editor.
"""
import os
import sys

import httpx

SQL = """
CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    hf_username TEXT UNIQUE NOT NULL,
    hf_email TEXT DEFAULT '',
    hf_avatar_url TEXT DEFAULT '',
    created_at TIMESTAMPTZ DEFAULT now(),
    last_login_at TIMESTAMPTZ DEFAULT now(),
    onboarding_complete BOOLEAN DEFAULT false
);

CREATE INDEX IF NOT EXISTS idx_users_hf_username ON users (hf_username);

-- Enable RLS
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Drop existing policies if they exist and recreate
DROP POLICY IF EXISTS "Users can read own row" ON users;
CREATE POLICY "Users can read own row" ON users
    FOR SELECT USING (hf_username = current_setting('request.jwt.claims', true)::json->>'hf_username');

DROP POLICY IF EXISTS "Users can update own row" ON users;
CREATE POLICY "Users can update own row" ON users
    FOR UPDATE USING (hf_username = current_setting('request.jwt.claims', true)::json->>'hf_username')
    WITH CHECK (hf_username = current_setting('request.jwt.claims', true)::json->>'hf_username');
"""


async def main():
    url = os.environ.get("SUPABASE_URL", "").rstrip("/")
    service_key = os.environ.get("SUPABASE_SERVICE_KEY", "")
    anon_key = os.environ.get("SUPABASE_ANON_KEY", "")

    if not url or not service_key:
        print("ERROR: SUPABASE_URL and SUPABASE_SERVICE_KEY must be set")
        print("\nAlternatively, run the SQL in migrations/001_create_users.sql")
        print("directly in your Supabase dashboard SQL editor.")
        sys.exit(1)

    headers = {
        "apikey": anon_key or service_key,
        "Authorization": f"Bearer {service_key}",
        "Content-Type": "application/json",
    }

    # Try using pg_query RPC (requires the pg_query function to exist)
    print("Attempting migration via pg_query RPC...")
    async with httpx.AsyncClient(timeout=15) as client:
        resp = await client.post(
            f"{url}/rest/v1/rpc/pg_query",
            json={"query": SQL},
            headers=headers,
        )
        if resp.status_code == 200:
            print("Migration successful via pg_query RPC.")
            return

        # If pg_query doesn't exist, try raw SQL via the management API
        print(f"pg_query RPC failed ({resp.status_code}), trying /sql endpoint...")

        resp2 = await client.post(
            f"{url}/sql",
            json={"query": SQL},
            headers=headers,
        )
        if resp2.status_code == 200:
            print("Migration successful via /sql endpoint.")
            return

        print(f"/sql endpoint failed ({resp2.status_code})")
        print(f"Response: {resp2.text[:200]}")
        print("\nPlease run the SQL in migrations/001_create_users.sql")
        print("manually in your Supabase dashboard SQL editor.")
        print(f"URL: {url}/project/default/sql/new")


if __name__ == "__main__":
    import asyncio

    asyncio.run(main())