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

async def insert_customers():
    database_url = os.getenv('DATABASE_URL')
    engine = create_async_engine(database_url)
    try:
        async with engine.connect() as conn:
            # Check existing structure
            result = await conn.execute(text("""
                SELECT column_name
                FROM information_schema.columns
                WHERE table_schema = 'public' AND table_name = 'customers'
                ORDER BY ordinal_position
            """))
            columns = [row[0] for row in result.fetchall()]
            print(f"Existing columns in public.customers: {columns}")

            # Insert customers without phone if it doesn't exist
            print("\nInserting 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 public.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("✅ Customers inserted!")

            # Verify
            result = await conn.execute(text("""
                SELECT id, first_name, last_name, organization_id, deleted_at
                FROM public.customers
                ORDER BY id
            """))
            print("\nCustomers in database:")
            for row in result.fetchall():
                print(f"  - {row[0]}: {row[1]} {row[2]} (org: {row[3]}, deleted: {row[4]})")

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

asyncio.run(insert_customers())