scripts/populate_schemes_from_chunks.py

"""
GovBridge India — Populate schemes table from document_chunks
This bridges the gap: document_chunks has ingested data,
schemes table is empty, home page needs schemes table.
Run: python3 gov_backend/scripts/populate_schemes_from_chunks.py
"""
import os
from typing import cast, Dict, Any, List, Set
from supabase import create_client
from config import settings

url = settings.SUPABASE_URL
key = settings.SUPABASE_KEY
if not url or not key:
    print("Missing env vars")
    exit()

sb = create_client(url, key)

print("Fetching document_chunks...")
result = sb.table('document_chunks').select('*').execute()
chunks = cast(List[Dict[str, Any]], result.data or [])
print(f"Found {len(chunks)} chunks")

# Deduplicate by scheme_title
seen_titles: Set[str] = set()
schemes_to_insert: List[Dict[str, Any]] = []

for chunk in chunks:
    title = (
        chunk.get('scheme_title') or 
        chunk.get('title') or 
        'Government Document'
    )
    
    if title in seen_titles:
        continue
    seen_titles.add(title)
    
    doc_type = chunk.get('doc_type', 'general')
    category_map = {
        'scheme': 'Scheme',
        'press_release': 'Press Release', 
        'gazette': 'Gazette',
        'general': 'General',
        'agriculture': 'Agriculture',
        'education': 'Education',
        'health': 'Health',
    }
    category = category_map.get(doc_type, 'General')
    
    text = chunk.get('text', '') or ''
    summary = text[:500] if len(text) > 50 else ''
    
    scheme = {
        'title': title[:500],
        'category': category,
        'ministry': chunk.get('ministry', 'Government of India'),
        'state_applicability': [chunk.get('state', 'All India')] 
                               if chunk.get('state') else ['All India'],
        'summary': summary,
        'source_url': chunk.get('source_url', ''),
        'is_active': True,
        'is_verified': False,
    }
    schemes_to_insert.append(scheme)

print(f"Inserting {len(schemes_to_insert)} unique schemes...")

batch_size = 50
for i in range(0, len(schemes_to_insert), batch_size):
    batch = schemes_to_insert[i:i+batch_size]
    try:
        sb.table('schemes').upsert(
            batch, 
            on_conflict='title'
        ).execute()
        print(f"Batch {i//batch_size + 1}: {len(batch)} inserted")
    except Exception as e:
        print(f"Batch error: {e}")

print("Done. Run audit to verify.")