"""
Seed test data for Commission Distribution verification.

Creates:
1. Three users with a referral chain: UserC (buyer) -> UserB (Gen1) -> UserA (Gen2)
2. An active L2_COMMISSION rule with commission_percent and upline_commission_percent

Usage:
    docker compose exec sf_api python /app/backend/tests/active/seed_commission_test_data.py
"""

import asyncio
import os
import sys

sys.path.insert(0, "/app/backend")

from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession, async_sessionmaker
from sqlalchemy import text
from datetime import date


async def seed():
    database_url = os.getenv(
        "DATABASE_URL",
        "postgresql+asyncpg://service_finder:service_finder@postgres:5432/service_finder",
    )
    engine = create_async_engine(database_url, echo=False)
    async_session = async_sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)

    async with async_session() as db:
        # Check if test users already exist
        existing = await db.execute(
            text("SELECT id, email FROM identity.users WHERE email LIKE 'commission_test_%' ORDER BY id")
        )
        existing_users = existing.fetchall()
        if existing_users:
            print(f"⚠️  Test users already exist: {[u.email for u in existing_users]}")
            print("   Skipping user creation.")

            # Find the chain
            result = await db.execute(
                text("""
                    SELECT u1.id AS buyer_id, u1.email AS buyer_email,
                           u2.id AS gen1_id, u2.email AS gen1_email,
                           u3.id AS gen2_id, u3.email AS gen2_email
                    FROM identity.users u1
                    JOIN identity.users u2 ON u1.referred_by_id = u2.id
                    JOIN identity.users u3 ON u2.referred_by_id = u3.id
                    WHERE u1.email = 'commission_test_buyer@test.com'
                """)
            )
            row = result.fetchone()
            if row:
                print(f"\n✅ Referral chain intact:")
                print(f"   Buyer(Gen0): ID={row.buyer_id} < {row.buyer_email}")
                print(f"   Gen1: ID={row.gen1_id} < {row.gen1_email}")
                print(f"   Gen2: ID={row.gen2_id} < {row.gen2_email}")
        else:
            print("Creating test users with referral chain...")

            # Create Gen2 (top-level referrer) - UserA
            result = await db.execute(
                text("""
                    INSERT INTO identity.users
                        (email, hashed_password, role, commission_tier, is_active, ui_mode,
                         subscription_plan, is_vip, is_deleted, preferred_language,
                         region_code, preferred_currency, scope_level,
                         custom_permissions, alternative_emails, email_history,
                         visual_settings, created_at)
                    VALUES
                        ('commission_test_gen2@test.com', 'abc123', 'USER', 'STANDARD', TRUE, 'personal',
                         'free', FALSE, FALSE, 'hu',
                         'HU', 'HUF', 'user',
                         '{}'::json, '[]'::json, '[]'::json,
                         '{}'::jsonb, NOW())
                    RETURNING id
                """)
            )
            gen2_id = result.scalar()
            print(f"   Created Gen2 (Upline): ID={gen2_id}")

            # Create Gen1 (referrer) - UserB, referred by Gen2
            result = await db.execute(
                text("""
                    INSERT INTO identity.users
                        (email, hashed_password, role, commission_tier, is_active, ui_mode,
                         referred_by_id,
                         subscription_plan, is_vip, is_deleted, preferred_language,
                         region_code, preferred_currency, scope_level,
                         custom_permissions, alternative_emails, email_history,
                         visual_settings, created_at)
                    VALUES
                        ('commission_test_gen1@test.com', 'abc123', 'USER', 'STANDARD', TRUE, 'personal',
                         :gen2_id,
                         'free', FALSE, FALSE, 'hu',
                         'HU', 'HUF', 'user',
                         '{}'::json, '[]'::json, '[]'::json,
                         '{}'::jsonb, NOW())
                    RETURNING id
                """).bindparams(gen2_id=gen2_id)
            )
            gen1_id = result.scalar()
            print(f"   Created Gen1 (Referrer): ID={gen1_id}")

            # Create Buyer (Gen0) - UserC, referred by Gen1
            result = await db.execute(
                text("""
                    INSERT INTO identity.users
                        (email, hashed_password, role, commission_tier, is_active, ui_mode,
                         referred_by_id,
                         subscription_plan, is_vip, is_deleted, preferred_language,
                         region_code, preferred_currency, scope_level,
                         custom_permissions, alternative_emails, email_history,
                         visual_settings, created_at)
                    VALUES
                        ('commission_test_buyer@test.com', 'abc123', 'USER', 'STANDARD', TRUE, 'personal',
                         :gen1_id,
                         'free', FALSE, FALSE, 'hu',
                         'HU', 'HUF', 'user',
                         '{}'::json, '[]'::json, '[]'::json,
                         '{}'::jsonb, NOW())
                    RETURNING id
                """).bindparams(gen1_id=gen1_id)
            )
            buyer_id = result.scalar()
            print(f"   Created Buyer (Gen0): ID={buyer_id}")

            print(f"\n✅ Referral chain created:")
            print(f"   Buyer(Gen0): ID={buyer_id} -> referred_by={gen1_id}")
            print(f"   Gen1: ID={gen1_id} -> referred_by={gen2_id}")
            print(f"   Gen2: ID={gen2_id}")

        # Check for existing L2_COMMISSION rules
        rule_result = await db.execute(
            text("""
                SELECT id, name, commission_percent, upline_commission_percent,
                       commission_max_amount, is_active
                FROM marketplace.commission_rules
                WHERE rule_type = 'L2_COMMISSION'
                ORDER BY id
            """)
        )
        existing_rules = rule_result.fetchall()
        if existing_rules:
            print(f"\n⚠️  L2_COMMISSION rules already exist:")
            for r in existing_rules:
                print(f"   ID={r.id}, Name={r.name}, Active={r.is_active}, "
                      f"commission={r.commission_percent}%, upline={r.upline_commission_percent}%, "
                      f"max={r.commission_max_amount}")
        else:
            print("\nCreating L2_COMMISSION rule...")
            result = await db.execute(
                text("""
                    INSERT INTO marketplace.commission_rules
                        (rule_type, name, description, tier, region_code,
                         commission_percent, upline_commission_percent,
                         commission_max_amount, is_campaign, is_active,
                         start_date, end_date, created_by)
                    VALUES
                        ('L2_COMMISSION', 'Standard 2-Level Commission', 'Default 2-level MLM commission rule',
                         'STANDARD', 'HU',
                         5.00, 2.00,
                         50000.00, FALSE, TRUE,
                         :start_date, NULL, 1)
                    RETURNING id
                """).bindparams(start_date=date(2026, 1, 1))
            )
            rule_id = result.scalar()
            print(f"   Created L2_COMMISSION rule: ID={rule_id}")
            print(f"   commission_percent=5.00%, upline_commission_percent=2.00%, max=50000.00")

        await db.commit()
        print("\n✅ Seed data created successfully!")

    await engine.dispose()


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