#!/usr/bin/env python3
"""
Test script to verify MASTERASSETIDS field implementation
Shows master assets and their potential derivatives
"""
import os
import sys
import psycopg2
from dotenv import load_dotenv

# Load env vars from current directory
script_dir = os.path.dirname(os.path.abspath(__file__))
load_dotenv(os.path.join(script_dir, '.env'))

try:
    conn = psycopg2.connect(
        host=os.getenv('DB_HOST', 'localhost'),
        port=os.getenv('DB_PORT', '5437'),
        database='ferrero_tracking',
        user=os.getenv('DB_USER'),
        password=os.getenv('DB_PASSWORD')
    )
    cursor = conn.cursor()

    print("=" * 80)
    print("MASTERASSETIDS FIELD TESTING REPORT")
    print("=" * 80)

    # 1. Show master assets available for testing
    print("\n📋 MASTER ASSETS (Available for Testing)")
    print("-" * 80)
    cursor.execute("""
        SELECT
            tracking_id,
            opentext_id,
            local_campaign_id,
            original_filename,
            created_at
        FROM master_assets
        ORDER BY created_at DESC
        LIMIT 10
    """)

    print(f"{'Tracking ID':<12} {'OpenText ID':<45} {'Campaign':<15} {'Filename':<30}")
    print("-" * 80)
    for row in cursor.fetchall():
        tracking_id, opentext_id, campaign_id, filename, created_at = row
        filename_short = (filename[:27] + '...') if filename and len(filename) > 30 else filename or 'N/A'
        print(f"{tracking_id:<12} {opentext_id:<45} {campaign_id:<15} {filename_short:<30}")

    # 2. Show derivative assets (if any exist)
    print("\n\n📦 DERIVATIVE ASSETS (Uploaded from Agency)")
    print("-" * 80)
    cursor.execute("""
        SELECT
            da.tracking_id,
            da.dam_asset_id,
            da.derivative_filename,
            ma.opentext_id as master_opentext_id,
            ma.local_campaign_id,
            da.created_at
        FROM derivative_assets da
        LEFT JOIN master_assets ma ON da.tracking_id = ma.tracking_id
        ORDER BY da.created_at DESC
        LIMIT 10
    """)

    derivative_rows = cursor.fetchall()
    if derivative_rows:
        print(f"{'Tracking ID':<12} {'Derivative DAM ID':<45} {'Master DAM ID (should be in MASTERASSETIDS)':<50}")
        print("-" * 80)
        for row in derivative_rows:
            tracking_id, dam_asset_id, filename, master_opentext_id, campaign_id, created_at = row
            print(f"{tracking_id:<12} {dam_asset_id or 'N/A':<45} {master_opentext_id or 'N/A':<50}")
    else:
        print("(No derivative assets found)")
        print("\nℹ️  Derivatives are created when Agency returns localized assets (A2→A3 flow)")

    # 3. Show campaigns ready for testing
    print("\n\n🧪 CAMPAIGNS READY FOR TESTING")
    print("-" * 80)
    cursor.execute("""
        SELECT
            cs.campaign_number,
            cs.campaign_name,
            cs.status,
            COUNT(ma.id) as master_count,
            MAX(cs.updated_at) as last_updated
        FROM campaign_status cs
        LEFT JOIN master_assets ma ON cs.campaign_number = ma.local_campaign_id
        WHERE cs.status IN ('A2', 'A3')
        GROUP BY cs.campaign_number, cs.campaign_name, cs.status
        ORDER BY last_updated DESC
    """)

    test_campaigns = cursor.fetchall()
    if test_campaigns:
        print(f"{'Campaign':<15} {'Status':<8} {'Master Assets':<15} {'Campaign Name':<40}")
        print("-" * 80)
        for row in test_campaigns:
            campaign_num, campaign_name, status, count, last_updated = row
            print(f"{campaign_num:<15} {status:<8} {count:<15} {campaign_name[:37]}")
    else:
        print("(No campaigns in A2 or A3 status)")

    # 4. Get a sample tracking ID for testing
    print("\n\n🔬 TEST SCENARIO")
    print("-" * 80)
    cursor.execute("""
        SELECT tracking_id, opentext_id, local_campaign_id, original_filename
        FROM master_assets
        ORDER BY created_at DESC
        LIMIT 1
    """)

    sample = cursor.fetchone()
    if sample:
        tracking_id, opentext_id, campaign_id, filename = sample
        print(f"Sample Master Asset for Testing:")
        print(f"  Tracking ID:    {tracking_id}")
        print(f"  OpenText ID:    {opentext_id}")
        print(f"  Campaign:       {campaign_id}")
        print(f"  Filename:       {filename or 'N/A'}")
        print(f"\nTo test MASTERASSETIDS field:")
        print(f"  1. Upload a derivative file to Box with tracking ID: {tracking_id}")
        print(f"  2. Run: python scripts/a2_to_a3_upload_polling.py --dryrun")
        print(f"  3. Check for FERRERO.MASTERASSETIDS field with value: {opentext_id}")
        print(f"\nNote: Field is only active in PPR environment (ppr.dam.ferrero.com)")

    # 5. Environment check
    print("\n\n🌍 ENVIRONMENT CONFIGURATION")
    print("-" * 80)
    dam_url = os.getenv('DAM_BASE_URL', 'Not configured')
    print(f"DAM Base URL:    {dam_url}")
    if 'ppr.dam.ferrero.com' in dam_url:
        print("Environment:     PPR (MASTERASSETIDS field is ENABLED ✅)")
    elif 'dam.ferrero.com' in dam_url:
        print("Environment:     PROD (MASTERASSETIDS field is DISABLED ⚠️)")
    else:
        print("Environment:     Unknown")

    print("\n" + "=" * 80)

    conn.close()

except Exception as e:
    print(f"❌ Error: {e}")
    sys.exit(1)