#!/usr/bin/env python
"""
Import populated SQL file and verify all users can be imported to Haven Grazuri system
"""
import pymysql
import subprocess
import os

print("=" * 100)
print("GRAZURI USER IMPORT - COMPLETE VERIFICATION")
print("=" * 100)

# Database connection
conn = pymysql.connect(
    host='localhost',
    user='root',
    password='password',
    database='xygbfpsg_graz',
    charset='utf8mb4'
)

try:
    with conn.cursor() as cursor:
        print("\n[STEP 1] Importing populated SQL file...")
        print("-" * 100)
        
        # Import the populated SQL file
        sql_file = r"c:\Users\Teamjoint company\Desktop\branchsystem\xygbfpsg_loans_populated.sql"
        
        if not os.path.exists(sql_file):
            print(f"❌ SQL file not found: {sql_file}")
            exit(1)
        
        print(f"✓ Found SQL file: {sql_file}")
        print("  Importing... (this may take a moment)")
        
        # Read and execute SQL file
        with open(sql_file, 'r', encoding='utf-8', errors='ignore') as f:
            sql_content = f.read()
            
            # Split by statements and execute
            statements = sql_content.split(';')
            executed = 0
            errors = 0
            
            for statement in statements:
                statement = statement.strip()
                if statement and not statement.startswith('--'):
                    try:
                        cursor.execute(statement)
                        executed += 1
                    except Exception as e:
                        # Ignore table exists errors
                        if 'already exists' not in str(e) and 'Duplicate' not in str(e):
                            errors += 1
            
            conn.commit()
            print(f"✓ Executed {executed} SQL statements ({errors} skipped/errors)")
        
        print("\n[STEP 2] Verifying Grazuri user table...")
        print("-" * 100)
        
        # Check if user table exists
        cursor.execute("SHOW TABLES LIKE 'user'")
        if not cursor.fetchone():
            print("❌ Grazuri 'user' table does NOT exist after import")
            exit(1)
        
        print("✓ Grazuri 'user' table exists")
        
        # Count users
        cursor.execute("SELECT COUNT(*) FROM user")
        user_count = cursor.fetchone()[0]
        print(f"✓ Found {user_count} users in Grazuri database")
        
        if user_count == 0:
            print("\n⚠️  WARNING: The user table is EMPTY!")
            print("   The SQL file may not contain user data.")
            exit(1)
        
        print("\n[STEP 3] Fetching all users from Grazuri database...")
        print("-" * 100)
        
        # Fetch all users
        cursor.execute("""
            SELECT userid, name, username, email, phone, role, 
                   gender, id_number, date_of_birth, branch, password
            FROM user 
            ORDER BY userid
        """)
        users = cursor.fetchall()
        
        print(f"\n✓ Successfully fetched {len(users)} users:\n")
        
        # Display users
        for i, user in enumerate(users, 1):
            print(f"{i}. {user[1]} (@{user[2]})")
            print(f"   Email: {user[3]}")
            print(f"   Phone: {user[4]}")
            print(f"   Role: {user[5]}")
            print(f"   ID Number: {user[7]}")
            print(f"   Branch: {user[9]}")
            print()
        
        print("\n[STEP 4] Verifying system compatibility...")
        print("-" * 100)
        
        # Check role mapping
        role_mapping = {
            'admin': 'admin',
            'Admin': 'admin',
            'Credit Controller': 'loan_officer',
            'Loan Officer': 'loan_officer',
            'Team Leader': 'team_leader',
            'Secretary': 'secretary',
            'Auditor': 'auditor',
            'Manager': 'team_leader',
        }
        
        roles_found = {}
        unmapped_roles = []
        
        for user in users:
            role = user[5]
            if role in role_mapping:
                mapped_role = role_mapping[role]
                roles_found[mapped_role] = roles_found.get(mapped_role, 0) + 1
            else:
                if role not in unmapped_roles:
                    unmapped_roles.append(role)
                roles_found['borrower'] = roles_found.get('borrower', 0) + 1
        
        print("✓ Role mapping verified:")
        for role, count in sorted(roles_found.items()):
            print(f"   - {role}: {count} users")
        
        if unmapped_roles:
            print(f"\n⚠️  Unmapped roles (will default to 'borrower'): {', '.join(unmapped_roles)}")
        
        print("\n[STEP 5] Testing data quality...")
        print("-" * 100)
        
        issues = []
        valid_users = 0
        
        for user in users:
            userid, name, username, email, phone, role, gender, id_number, dob, branch, password = user
            
            # Check required fields
            if not username or not name:
                issues.append(f"User {userid}: Missing username or name")
                continue
            
            if not password:
                issues.append(f"User {username}: Missing password")
            
            valid_users += 1
        
        print(f"✓ Valid users: {valid_users}/{len(users)}")
        
        if issues:
            print(f"\n⚠️  Found {len(issues)} data quality issues:")
            for issue in issues[:10]:  # Show first 10
                print(f"   - {issue}")
            if len(issues) > 10:
                print(f"   ... and {len(issues) - 10} more")
        else:
            print("✓ No data quality issues found")
        
        print("\n" + "=" * 100)
        print("VERIFICATION SUMMARY")
        print("=" * 100)
        print(f"✅ SQL file imported successfully")
        print(f"✅ Grazuri user table exists and is populated")
        print(f"✅ {user_count} users found in database")
        print(f"✅ All users can be fetched without errors")
        print(f"✅ Role mapping is compatible")
        print(f"✅ {valid_users} users are ready for import")
        print("\n🎉 SYSTEM IS READY! You can import these users to Haven Grazuri anytime.")
        print("\n📝 To import users, run: python import_grazuri_users.py")
        print("=" * 100)
        
except Exception as e:
    print(f"\n❌ ERROR: {str(e)}")
    import traceback
    traceback.print_exc()
finally:
    conn.close()