import asyncio
import secrets
from uuid import uuid4
from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import sessionmaker
from sqlalchemy import text
from core.security import hash_password
from core.constants import SystemRole

DATABASE_URL = "postgresql+asyncpg://telegram_saas:saas_secret_password_2026@127.0.0.1:5432/telegram_saas"

TABLES_TO_CLEAR = [
    "bot_updates",
    "bot_users",
    "bot_referrals",
    "bot_commands",
    "bot_message_buttons",
    "bot_message_versions",
    "bot_message_templates",
    "installed_bot_instances",
    "bot_modules",
    "bot_settings",
    "bot_webhooks",
    "bot_credentials",
    "bots",
    "broadcast_recipients",
    "broadcast_campaigns",
    "coupon_redemptions",
    "coupons",
    "refunds",
    "payment_webhook_events",
    "payment_orders",
    "invoices",
    "wallet_transactions",
    "wallets",
    "subscriptions",
    "notifications",
    "audit_logs",
    "mcp_execution_logs",
    "invitations",
    "ai_conversations",
    "ai_messages",
    "ai_usage_logs",
    "ai_agents",
    "tenant_ai_keys",
    "tenant_plugins",
    "tenant_usages",
    "tenant_locales",
    "tenant_members",
    "sessions",
    "users",
    "tenants",
]

async def clean_and_seed():
    print("--- STARTING PRODUCTION DATABASE PURGE & INITIALIZATION ---")
    engine = create_async_engine(DATABASE_URL, echo=False)
    async_session = sessionmaker(engine, expire_on_commit=False, class_=AsyncSession)

    async with engine.connect() as conn:
        print("\n1. Truncating test & development tables (CASCADE)...")
        for table in TABLES_TO_CLEAR:
            try:
                await conn.execute(text(f'TRUNCATE TABLE "{table}" CASCADE;'))
                print(f"  ✓ Purged: {table}")
            except Exception as e:
                print(f"  ⚠ Skipping {table}: {e}")
        await conn.commit()

    async with async_session() as session:
        print("\n2. Seeding Initial Production Admin & Enterprise Organization...")

        admin_id = uuid4()
        tenant_id = uuid4()
        admin_email = "admin@telebotcloud.com"
        admin_password = "TeleBotAdmin2026!"
        hashed_password = hash_password(admin_password)

        # 1. Create Admin User
        await session.execute(
            text("""
                INSERT INTO users (id, email, password_hash, full_name, status, is_superuser, preferred_locale, created_at, updated_at)
                VALUES (:id, :email, :pw, :name, 'active', true, 'en', NOW(), NOW());
            """),
            {
                "id": admin_id,
                "email": admin_email,
                "pw": hashed_password,
                "name": "TeleBot Administrator",
            }
        )

        # 2. Create Primary Workspace Tenant
        await session.execute(
            text("""
                INSERT INTO tenants (id, name, slug, status, plan_id, created_at, updated_at)
                VALUES (:id, :name, :slug, 'active', 'enterprise', NOW(), NOW());
            """),
            {
                "id": tenant_id,
                "name": "TeleBot Cloud Enterprise",
                "slug": "telebot-primary",
            }
        )

        # 3. Create Tenant Membership (Owner)
        await session.execute(
            text("""
                INSERT INTO tenant_members (id, tenant_id, user_id, role, is_active, created_at, updated_at)
                VALUES (:id, :tenant_id, :user_id, 'owner', true, NOW(), NOW());
            """),
            {
                "id": uuid4(),
                "tenant_id": tenant_id,
                "user_id": admin_id,
            }
        )

        # 4. Create Pre-funded Enterprise Wallet (₹50,000 for cloud resources, AI tokens, and broadcasts)
        wallet_id = uuid4()
        await session.execute(
            text("""
                INSERT INTO wallets (id, tenant_id, balance, currency, is_frozen, created_at, updated_at)
                VALUES (:id, :tenant_id, 5000000, 'INR', false, NOW(), NOW());
            """),
            {
                "id": wallet_id,
                "tenant_id": tenant_id,
            }
        )

        # 5. Create Initial Subscription (Enterprise Plan)
        await session.execute(
            text("""
                INSERT INTO subscriptions (id, tenant_id, plan_id, status, gateway_provider, current_period_start, current_period_end, cancel_at_period_end, created_at, updated_at)
                VALUES (:id, :tenant_id, 'enterprise', 'active', 'manual', NOW(), NOW() + INTERVAL '365 days', false, NOW(), NOW());
            """),
            {
                "id": uuid4(),
                "tenant_id": tenant_id,
            }
        )

        # 6. Add Initial Welcome Notification
        await session.execute(
            text("""
                INSERT INTO notifications (id, tenant_id, title, message, category, is_read, created_at, updated_at)
                VALUES (:id, :tenant_id, 'Welcome to TeleBot Cloud Production', 'Your production cloud cluster is operational. Connect your Telegram bot tokens from @BotFather to launch 24/7 autonomous instances.', 'system', false, NOW(), NOW());
            """),
            {
                "id": uuid4(),
                "tenant_id": tenant_id,
            }
        )

        await session.commit()
        print("  ✓ Admin User Created: admin@telebotcloud.com")
        print("  ✓ Primary Tenant Workspace: TeleBot Cloud Enterprise (Slug: telebot-primary)")
        print("  ✓ Pre-funded Enterprise Cloud Wallet: ₹50,000")
        print("  ✓ Enterprise 365-Day Plan Activated")

    # 3. Verify Database Status
    async with engine.connect() as conn:
        print("\n3. Database Row Counts After Production Cleanup:")
        check_tables = [
            "users", "tenants", "tenant_members", "bots", "bot_modules", "wallets",
            "subscriptions", "marketplace_templates", "marketplace_modules", "plugins", "plans", "custom_emojis"
        ]
        for t in check_tables:
            res = await conn.execute(text(f'SELECT count(*) FROM "{t}";'))
            print(f"  • {t}: {res.scalar()} rows")

    await engine.dispose()
    print("\n--- PRODUCTION CLEANUP & SEEDING COMPLETED SUCCESSFULLY! ---")

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