"""
Check User Database Status
===========================
Quick diagnostic script to check the current state of users in the database
before running the update_legacy_users.py script.

Usage: python check_user_status.py
"""

from app import create_app
from app.extensions import db
from app.models.user import User
from sqlalchemy import inspect


def check_database_schema():
    """Check the actual database schema"""
    print("=" * 70)
    print("DATABASE SCHEMA CHECK")
    print("=" * 70)
    print()
    
    inspector = inspect(db.engine)
    
    if 'users' not in inspector.get_table_names():
        print("✗ ERROR: 'users' table not found in database!")
        return False
    
    columns = {col['name']: col for col in inspector.get_columns('users')}
    
    print(f"Found {len(columns)} columns in 'users' table")
    print()
    
    # Check for recently added columns
    recent_additions = {
        'gst_registered': 'Oct 29, 2025',
        'stripe_account_id': 'Oct 20, 2025',
        'stripe_onboarding_complete': 'Oct 20, 2025',
        'stripe_payouts_enabled': 'Oct 20, 2025',
        'stripe_charges_enabled': 'Oct 20, 2025',
        'stripe_account_created_at': 'Oct 20, 2025',
        'password_reset_token': 'Nov 4, 2025',
        'password_reset_expires': 'Nov 4, 2025',
        'profile_picture': 'Nov 13, 2025',
    }
    
    print("Recent column additions status:")
    for col_name, date_added in recent_additions.items():
        status = "✓ EXISTS" if col_name in columns else "✗ MISSING"
        nullable = ""
        if col_name in columns:
            nullable = f" (nullable: {columns[col_name]['nullable']})"
        print(f"  {status:<12} {col_name:<30} Added: {date_added}{nullable}")
    
    print()
    return True


def analyze_user_data():
    """Analyze current user data for missing values"""
    print("=" * 70)
    print("USER DATA ANALYSIS")
    print("=" * 70)
    print()
    
    users = User.query.all()
    total_users = len(users)
    
    if total_users == 0:
        print("No users found in database")
        return
    
    print(f"Total users: {total_users}")
    print()
    
    # Fields to check
    fields_to_check = {
        'gst_registered': 'GST Registration',
        'stripe_onboarding_complete': 'Stripe Onboarding',
        'stripe_payouts_enabled': 'Stripe Payouts',
        'stripe_charges_enabled': 'Stripe Charges',
        'total_points': 'Gamification Points',
        'current_level': 'Gamification Level',
        'seasonal_league': 'Seasonal League',
        'jobs_completed': 'Jobs Completed',
        'total_reviews': 'Total Reviews',
        'average_rating': 'Average Rating',
        'public_liability_insurance': 'Public Liability',
        'workers_comp_insurance': 'Workers Comp',
        'is_active': 'Account Active',
        'account_status': 'Account Status',
        'privacy_consent': 'Privacy Consent',
        'terms_accepted': 'Terms Accepted',
        'username': 'Username',
    }
    
    issues = {}
    
    for field, label in fields_to_check.items():
        null_count = 0
        empty_count = 0
        
        for user in users:
            value = getattr(user, field, None)
            
            if value is None:
                null_count += 1
            elif isinstance(value, str) and not value.strip():
                empty_count += 1
        
        if null_count > 0 or empty_count > 0:
            issues[field] = {
                'label': label,
                'null': null_count,
                'empty': empty_count
            }
    
    if not issues:
        print("✓ All users have all required fields populated!")
        print()
        return
    
    print("Fields with missing data:")
    print("-" * 70)
    
    for field, data in sorted(issues.items(), key=lambda x: x[1]['null'] + x[1]['empty'], reverse=True):
        total_issues = data['null'] + data['empty']
        percentage = (total_issues / total_users) * 100
        
        print(f"✗ {data['label']:<30} {total_issues:>3}/{total_users} users ({percentage:>5.1f}%)")
        if data['null'] > 0:
            print(f"  └─ NULL values: {data['null']}")
        if data['empty'] > 0:
            print(f"  └─ Empty values: {data['empty']}")
    
    print()
    print("-" * 70)
    print(f"Total users needing updates: {len(issues)} field types")
    print()


def show_sample_users():
    """Show sample user data"""
    print("=" * 70)
    print("SAMPLE USER DATA (First 3 Users)")
    print("=" * 70)
    print()
    
    users = User.query.limit(3).all()
    
    for i, user in enumerate(users, 1):
        print(f"User {i}: {user.email}")
        print(f"  Role: {user.role}")
        print(f"  Username: {user.username or 'NOT SET'}")
        print(f"  GST Registered: {user.gst_registered}")
        print(f"  Stripe Onboarded: {user.stripe_onboarding_complete}")
        print(f"  Total Points: {user.total_points}")
        print(f"  Current Level: {user.current_level}")
        print(f"  Jobs Completed: {user.jobs_completed}")
        print(f"  Privacy Consent: {user.privacy_consent}")
        print(f"  Terms Accepted: {user.terms_accepted}")
        print(f"  Profile Picture: {user.profile_picture or 'NOT SET'}")
        print()


def main():
    """Main diagnostic function"""
    print()
    print("RateRight User Database Diagnostic")
    print()
    
    app = create_app()
    
    with app.app_context():
        # Check schema
        schema_ok = check_database_schema()
        
        if not schema_ok:
            print("✗ Schema check failed. Run migrations first: flask db upgrade")
            return
        
        # Analyze data
        analyze_user_data()
        
        # Show samples
        show_sample_users()
        
        # Recommendation
        print("=" * 70)
        print("RECOMMENDATION")
        print("=" * 70)
        print()
        
        users_with_issues = User.query.filter(
            (User.gst_registered == None) |
            (User.total_points == None) |
            (User.current_level == None) |
            (User.privacy_consent == None) |
            (User.terms_accepted == None)
        ).count()
        
        if users_with_issues > 0:
            print(f"⚠ Found {users_with_issues} users with missing data")
            print()
            print("Run the update script to fix:")
            print("  python update_legacy_users.py")
        else:
            print("✓ All users appear to be up-to-date!")
            print()
            print("No action needed at this time.")
        
        print()
        print("=" * 70)


if __name__ == '__main__':
    main()