#!/usr/bin/env python
"""Check all tables in database and count records"""
import pymysql

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

print("=" * 100)
print("COMPLETE DATABASE ANALYSIS")
print("=" * 100)

try:
    with conn.cursor() as cursor:
        # Get all tables
        cursor.execute("SHOW TABLES")
        tables = [row[0] for row in cursor.fetchall()]
        
        print(f"\nTotal tables in database: {len(tables)}\n")
        
        # Count records in each table
        table_counts = {}
        for table in sorted(tables):
            try:
                cursor.execute(f"SELECT COUNT(*) FROM `{table}`")
                count = cursor.fetchone()[0]
                table_counts[table] = count
            except Exception as e:
                table_counts[table] = f"Error: {str(e)}"
        
        # Show tables with data
        print("=" * 100)
        print("TABLES WITH DATA")
        print("=" * 100)
        print(f"{'Table Name':<50} {'Record Count':<20}")
        print("-" * 70)
        
        has_data = False
        for table, count in sorted(table_counts.items(), key=lambda x: (isinstance(x[1], str), -x[1] if isinstance(x[1], int) else 0)):
            if isinstance(count, int) and count > 0:
                has_data = True
                status = "✓" if count > 0 else " "
                print(f"{status} {table:<48} {count:>18,}")
        
        if not has_data:
            print("  No tables with data found")
        
        # Show empty tables
        print("\n" + "=" * 100)
        print("EMPTY TABLES")
        print("=" * 100)
        empty_count = 0
        for table, count in sorted(table_counts.items()):
            if isinstance(count, int) and count == 0:
                empty_count += 1
                if empty_count <= 20:  # Show first 20
                    print(f"  - {table}")
        
        if empty_count > 20:
            print(f"  ... and {empty_count - 20} more empty tables")
        
        # Summary
        print("\n" + "=" * 100)
        print("SUMMARY")
        print("=" * 100)
        
        total_records = sum(count for count in table_counts.values() if isinstance(count, int))
        tables_with_data = sum(1 for count in table_counts.values() if isinstance(count, int) and count > 0)
        empty_tables = sum(1 for count in table_counts.values() if isinstance(count, int) and count == 0)
        
        print(f"\nTotal tables: {len(tables)}")
        print(f"Tables with data: {tables_with_data}")
        print(f"Empty tables: {empty_tables}")
        print(f"Total records: {total_records:,}")
        
        # Key tables
        print("\n" + "=" * 100)
        print("KEY GRAZURI TABLES")
        print("=" * 100)
        
        key_tables = {
            'user': 'Staff/Admin Users',
            'borrowers': 'Clients/Borrowers',
            'loan_info': 'Loans',
            'payments': 'Loan Payments',
            'branches': 'Branches',
            'journal_transactions': 'Journal Entries',
            'gl_codes': 'GL Accounts',
            'loan_fees': 'Loan Fees',
            'loan_guarantors': 'Guarantors',
        }
        
        for table, description in key_tables.items():
            count = table_counts.get(table, 'Not found')
            if isinstance(count, int):
                status = "✓" if count > 0 else "⚠"
                print(f"{status} {description:<30} ({table:<25}): {count:>10,} records")
            else:
                print(f"✗ {description:<30} ({table:<25}): Table not found")
        
        print("\n" + "=" * 100)

except Exception as e:
    print(f"Error: {str(e)}")
    import traceback
    traceback.print_exc()
finally:
    conn.close()