import os
import psycopg2
from dotenv import load_dotenv

def get_supabase_schema():
    load_dotenv()
    conn = psycopg2.connect(os.getenv('DATABASE_URL'))
    cursor = conn.cursor()
    
    # Get tables and columns
    cursor.execute("""
        SELECT table_name, column_name, data_type, is_nullable, column_default
        FROM information_schema.columns
        WHERE table_schema = 'public'
        ORDER BY table_name, ordinal_position
    """)
    schema = {}
    for table, column, dtype, nullable, default in cursor.fetchall():
        if table not in schema:
            schema[table] = []
        schema[table].append({
            'column': column,
            'type': dtype,
            'nullable': nullable == 'YES',
            'default': default
        })
    
    # Get foreign keys
    cursor.execute("""
        SELECT
            tc.table_name, 
            kcu.column_name, 
            ccu.table_name AS foreign_table_name,
            ccu.column_name AS foreign_column_name 
        FROM 
            information_schema.table_constraints AS tc 
            JOIN information_schema.key_column_usage AS kcu
              ON tc.constraint_name = kcu.constraint_name
            JOIN information_schema.constraint_column_usage AS ccu
              ON ccu.constraint_name = tc.constraint_name
        WHERE tc.constraint_type = 'FOREIGN KEY'
    """)
    fks = cursor.fetchall()
    
    cursor.close()
    conn.close()
    return schema, fks

def compare_with_plan(schema, fks):
    # TODO: Implement comparison with project_plan.md
    # For now just print the schema
    print("Current Supabase Schema:")
    for table, columns in schema.items():
        print(f"\nTable: {table}")
        for col in columns:
            print(f"  {col['column']}: {col['type']} {'(nullable)' if col['nullable'] else ''}")

if __name__ == "__main__":
    schema, fks = get_supabase_schema()
    compare_with_plan(schema, fks)