import asyncio
import os
from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy import text

async def fix_stubs():
    database_url = os.getenv('DATABASE_URL')
    print(f"Connecting to database...")

    engine = create_async_engine(database_url)
    try:
        async with engine.connect() as conn:
            # Create stub tables in PUBLIC schema (not policy_service)
            print("\n1. Creating organizations table in PUBLIC schema...")
            await conn.execute(text("""
                CREATE TABLE IF NOT EXISTS public.organizations (
                    id TEXT PRIMARY KEY,
                    name TEXT NOT NULL,
                    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
                    deleted_at TIMESTAMPTZ
                )
            """))

            print("2. Creating users table in PUBLIC schema...")
            await conn.execute(text("""
                CREATE TABLE IF NOT EXISTS public.users (
                    id TEXT PRIMARY KEY,
                    email TEXT NOT NULL,
                    role TEXT NOT NULL,
                    organization_id TEXT NOT NULL,
                    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
                    deleted_at TIMESTAMPTZ
                )
            """))

            print("3. Creating customers table in PUBLIC schema...")
            await conn.execute(text("""
                CREATE TABLE IF NOT EXISTS public.customers (
                    id TEXT PRIMARY KEY,
                    organization_id TEXT NOT NULL,
                    first_name TEXT,
                    last_name TEXT,
                    email TEXT,
                    phone TEXT,
                    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
                    deleted_at TIMESTAMPTZ
                )
            """))

            await conn.commit()
            print("\n✅ Stub tables created in PUBLIC schema!")

            # Insert test data
            print("\n4. Inserting test organization...")
            await conn.execute(text("""
                INSERT INTO public.organizations (id, name)
                VALUES ('test-org-001', 'Test Organization')
                ON CONFLICT (id) DO NOTHING
            """))

            print("5. Inserting test user...")
            await conn.execute(text("""
                INSERT INTO public.users (id, email, role, organization_id)
                VALUES ('test-admin-user', 'admin@test.com', 'admin', 'test-org-001')
                ON CONFLICT (id) DO NOTHING
            """))

            print("6. Inserting test customers...")
            customers = [
                ('cus_01JEMN4XZ8Y6Q3KPVWRST2FGHA', 'John', 'Doe', 'john.doe@example.com', '555-0001'),
                ('cus_02JEMN4XZ8Y6Q3KPVWRST2FGHB', 'Jane', 'Smith', 'jane.smith@example.com', '555-0002'),
                ('cus_03JEMN4XZ8Y6Q3KPVWRST2FGHC', 'Bob', 'Johnson', 'bob.johnson@example.com', '555-0003'),
                ('cus_04JEMN4XZ8Y6Q3KPVWRST2FGHD', 'Alice', 'Williams', 'alice.williams@example.com', '555-0004'),
                ('cus_05JEMN4XZ8Y6Q3KPVWRST2FGHE', 'Charlie', 'Brown', 'charlie.brown@example.com', '555-0005'),
            ]

            for cust_id, first, last, email, phone in customers:
                await conn.execute(text("""
                    INSERT INTO public.customers (id, organization_id, first_name, last_name, email, phone)
                    VALUES (:id, 'test-org-001', :first, :last, :email, :phone)
                    ON CONFLICT (id) DO NOTHING
                """), {"id": cust_id, "first": first, "last": last, "email": email, "phone": phone})

            await conn.commit()
            print("✅ Test data inserted successfully!")

            # Verify
            print("\n7. Verifying data in PUBLIC schema...")
            result = await conn.execute(text("SELECT COUNT(*) FROM public.organizations"))
            print(f"   Organizations: {result.scalar()}")

            result = await conn.execute(text("SELECT COUNT(*) FROM public.users"))
            print(f"   Users: {result.scalar()}")

            result = await conn.execute(text("SELECT COUNT(*) FROM public.customers"))
            print(f"   Customers: {result.scalar()}")

            result = await conn.execute(text("SELECT id, first_name, last_name FROM public.customers ORDER BY id LIMIT 5"))
            customers_list = result.fetchall()
            for row in customers_list:
                print(f"      - {row[0]}: {row[1]} {row[2]}")

            print("\n✅ All stub tables fixed in PUBLIC schema!")

    except Exception as e:
        print(f"❌ Error: {type(e).__name__}: {e}")
        raise
    finally:
        await engine.dispose()

if __name__ == "__main__":
    asyncio.run(fix_stubs())