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

async def create_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
            print("\n1. Creating organizations table...")
            await conn.execute(text("""
                CREATE TABLE IF NOT EXISTS policy_service.organizations (
                    id TEXT PRIMARY KEY,
                    name TEXT NOT NULL,
                    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
                )
            """))

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

            print("3. Creating customers table...")
            await conn.execute(text("""
                CREATE TABLE IF NOT EXISTS policy_service.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
                )
            """))

            await conn.commit()
            print("\n✅ Stub tables created successfully!")

            # Insert test data
            print("\n4. Inserting test organization...")
            await conn.execute(text("""
                INSERT INTO policy_service.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 policy_service.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'),
                ('cus_02JEMN4XZ8Y6Q3KPVWRST2FGHB', 'Jane', 'Smith', 'jane.smith@example.com'),
                ('cus_03JEMN4XZ8Y6Q3KPVWRST2FGHC', 'Bob', 'Johnson', 'bob.johnson@example.com'),
                ('cus_04JEMN4XZ8Y6Q3KPVWRST2FGHD', 'Alice', 'Williams', 'alice.williams@example.com'),
                ('cus_05JEMN4XZ8Y6Q3KPVWRST2FGHE', 'Charlie', 'Brown', 'charlie.brown@example.com'),
            ]

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

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

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

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

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

            print("\n✅ All stub tables created and populated!")

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

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