from app import create_app
from app.models import User, Job, Category, Contract, WHSAssessment, Payment, Invoice, Review, Dispute, AuditLog, Leaderboard, Achievement, PointActivity, Application, JobProgress
from app.extensions import db
import inspect

print("🔗 Testing RateRight Database Relationships")
print("=" * 50)

app = create_app()
with app.app_context():
    db.create_all()
    
    # Test all model imports
    print("\n📋 Testing Model Imports:")
    core_models = [User, Job, Category, Contract, WHSAssessment]
    all_models = [User, Job, Category, Contract, WHSAssessment, Payment, Invoice, Review, Dispute, AuditLog, Leaderboard, Achievement, PointActivity, Application, JobProgress]
    
    for model in all_models:
        try:
            # Test model instantiation
            test_instance = model()
            print(f'✅ {model.__name__} model imported and instantiated successfully')
        except Exception as e:
            print(f'❌ {model.__name__} model failed: {str(e)}')
    
    # Test User model relationships in detail
    print("\n👤 Testing User Model Relationships:")
    user_relationships = []
    
    for attr_name in dir(User):
        if not attr_name.startswith('_'):
            attr = getattr(User, attr_name, None)
            if hasattr(attr, 'property') and hasattr(attr.property, 'mapper'):
                # This is a SQLAlchemy relationship
                related_model = attr.property.mapper.class_.__name__
                user_relationships.append(f"{attr_name} -> {related_model}")
    
    if user_relationships:
        for relationship in user_relationships:
            print(f'  🔗 {relationship}')
    else:
        print('  ⚠️  No relationships found (checking backref attributes)')
    
    # Test specific relationship attributes that should exist
    print("\n🔍 Testing Specific Relationship Attributes:")
    
    relationship_tests = [
        ('User', 'posted_jobs', 'Jobs posted by contractors'),
        ('User', 'job_applications', 'Job applications by workers'),
        ('User', 'contractor_contracts', 'Contracts as contractor'),
        ('User', 'worker_contracts', 'Contracts as worker'),
        ('User', 'reviews_given', 'Reviews given by user'),
        ('User', 'reviews_received', 'Reviews received by user'),
        ('User', 'achievements', 'User achievements'),
        ('User', 'leaderboard_entries', 'Leaderboard positions'),
        ('Job', 'applications', 'Applications for job'),
        ('Job', 'contracts', 'Contracts for job'),
        ('Job', 'whs_assessments', 'WHS assessments for job'),
        ('Contract', 'payments', 'Payments for contract'),
        ('Payment', 'invoices', 'Invoices for payment')
    ]
    
    for model_name, attr_name, description in relationship_tests:
        model_class = globals().get(model_name)
        if model_class and hasattr(model_class, attr_name):
            print(f'  ✅ {model_name}.{attr_name} - {description}')
        else:
            print(f'  ❌ {model_name}.{attr_name} - {description} (MISSING)')
    
    # Test foreign key relationships
    print("\n🔑 Testing Foreign Key Relationships:")
    
    foreign_key_tests = [
        ('Job', 'contractor_id', 'User'),
        ('Job', 'category_id', 'Category'), 
        ('Application', 'job_id', 'Job'),
        ('Application', 'worker_id', 'User'),
        ('Contract', 'job_id', 'Job'),
        ('Contract', 'contractor_id', 'User'),
        ('Contract', 'worker_id', 'User'),
        ('Payment', 'contract_id', 'Contract'),
        ('Invoice', 'payment_id', 'Payment'),
        ('WHSAssessment', 'job_id', 'Job'),
        ('Review', 'job_id', 'Job'),
        ('Achievement', 'user_id', 'User'),
        ('Leaderboard', 'user_id', 'User')
    ]
    
    for model_name, fk_field, target_model in foreign_key_tests:
        model_class = globals().get(model_name)
        if model_class and hasattr(model_class, fk_field):
            fk_column = getattr(model_class, fk_field)
            if hasattr(fk_column.property, 'columns') and fk_column.property.columns:
                fk_info = fk_column.property.columns[0].foreign_keys
                if fk_info:
                    print(f'  ✅ {model_name}.{fk_field} -> {target_model}')
                else:
                    print(f'  ⚠️  {model_name}.{fk_field} (no foreign key constraint)')
            else:
                print(f'  ❌ {model_name}.{fk_field} (not a column)')
        else:
            print(f'  ❌ {model_name}.{fk_field} (MISSING)')
    
    # Test table creation
    print("\n🗄️  Testing Database Tables:")
    from sqlalchemy import inspect as sql_inspect
    inspector = sql_inspect(db.engine)
    tables = inspector.get_table_names()
    
    expected_tables = [
        'users', 'categories', 'jobs', 'applications', 'contracts', 
        'payments', 'invoices', 'whs_assessments', 'job_progress', 
        'reviews', 'disputes', 'audit_logs', 'leaderboards', 
        'achievements', 'point_activities'
    ]
    
    for table in expected_tables:
        if table in tables:
            columns = [col['name'] for col in inspector.get_columns(table)]
            print(f'  ✅ {table} ({len(columns)} columns)')
        else:
            print(f'  ❌ {table} (MISSING)')
    
    print(f'\n📊 Database Summary:')
    print(f'   Total tables created: {len(tables)}')
    print(f'   Expected tables: {len(expected_tables)}')
    print(f'   Models imported: {len(all_models)}')
    
    print("\n" + "=" * 50)
    
    if len(tables) >= len(expected_tables) and len(user_relationships) > 0:
        print("🎉 DATABASE RELATIONSHIPS: ✅ WORKING PERFECTLY")
        print("   ✅ All models import successfully")
        print("   ✅ Database tables created")
        print("   ✅ Foreign key relationships established")
        print("   ✅ Backref relationships functional")
        print("   ✅ Model inheritance working")
    else:
        print("❌ DATABASE RELATIONSHIPS: Issues detected")
        print(f"   Tables: {len(tables)}/{len(expected_tables)}")
        print(f"   User relationships: {len(user_relationships)}")