#!/usr/bin/env python3
"""
Quick Schema Health Check Script

This script performs a quick health check on the database schema
to ensure everything is in order. Run this regularly to catch issues early.

Usage:
    python check_schema_health.py
"""

import os
import sys
import django

# Setup Django
sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings')
django.setup()

from django.db import connection


def check_schema_health():
    """Perform quick health checks"""
    print("="*80)
    print("DATABASE SCHEMA HEALTH CHECK")
    print("="*80)
    
    issues = []
    
    with connection.cursor() as cursor:
        # Check 1: Verify critical tables exist
        print("\n1. Checking critical tables...")
        critical_tables = ['users', 'branches', 'loans', 'loan_applications', 'loan_products']
        for table in critical_tables:
            cursor.execute("""
                SELECT COUNT(*)
                FROM information_schema.tables
                WHERE table_schema = DATABASE()
                AND table_name = %s
            """, [table])
            if cursor.fetchone()[0] == 0:
                issues.append(f"Critical table '{table}' is missing")
                print(f"   ❌ {table} - MISSING")
            else:
                print(f"   ✅ {table}")
        
        # Check 2: Verify foreign key columns in loans table
        print("\n2. Checking loans table foreign keys...")
        loan_fks = ['borrower_id', 'application_id', 'loan_officer_id', 'approved_by_id', 'disbursed_by_id']
        for fk in loan_fks:
            cursor.execute("""
                SELECT COUNT(*)
                FROM information_schema.columns
                WHERE table_schema = DATABASE()
                AND table_name = 'loans'
                AND column_name = %s
            """, [fk])
            if cursor.fetchone()[0] == 0:
                issues.append(f"Foreign key 'loans.{fk}' is missing")
                print(f"   ❌ {fk} - MISSING")
            else:
                print(f"   ✅ {fk}")
        
        # Check 3: Verify loan_applications foreign keys
        print("\n3. Checking loan_applications table foreign keys...")
        app_fks = ['borrower_id', 'loan_product_id', 'reviewed_by_id', 'loan_officer_id', 'approved_by_id']
        for fk in app_fks:
            cursor.execute("""
                SELECT COUNT(*)
                FROM information_schema.columns
                WHERE table_schema = DATABASE()
                AND table_name = 'loan_applications'
                AND column_name = %s
            """, [fk])
            if cursor.fetchone()[0] == 0:
                issues.append(f"Foreign key 'loan_applications.{fk}' is missing")
                print(f"   ❌ {fk} - MISSING")
            else:
                print(f"   ✅ {fk}")
        
        # Check 4: Verify data integrity
        print("\n4. Checking data integrity...")
        
        # Check for orphaned loans (loans without borrowers)
        cursor.execute("""
            SELECT COUNT(*)
            FROM loans l
            LEFT JOIN users u ON l.borrower_id = u.id
            WHERE u.id IS NULL AND l.borrower_id IS NOT NULL
        """)
        orphaned_loans = cursor.fetchone()[0]
        if orphaned_loans > 0:
            issues.append(f"{orphaned_loans} loans have invalid borrower_id references")
            print(f"   ⚠️  {orphaned_loans} loans with invalid borrower references")
        else:
            print(f"   ✅ All loans have valid borrower references")
        
        # Check for orphaned applications
        cursor.execute("""
            SELECT COUNT(*)
            FROM loan_applications la
            LEFT JOIN users u ON la.borrower_id = u.id
            WHERE u.id IS NULL AND la.borrower_id IS NOT NULL
        """)
        orphaned_apps = cursor.fetchone()[0]
        if orphaned_apps > 0:
            issues.append(f"{orphaned_apps} applications have invalid borrower_id references")
            print(f"   ⚠️  {orphaned_apps} applications with invalid borrower references")
        else:
            print(f"   ✅ All applications have valid borrower references")
        
        # Check 5: Verify indexes exist
        print("\n5. Checking critical indexes...")
        critical_indexes = [
            ('loans', 'idx_loans_borrower_id'),
            ('loans', 'idx_loans_application_id'),
            ('loan_applications', 'idx_loan_apps_borrower_id'),
            ('loan_applications', 'idx_loan_apps_loan_product_id'),
        ]
        for table, index_name in critical_indexes:
            cursor.execute("""
                SELECT COUNT(*)
                FROM information_schema.statistics
                WHERE table_schema = DATABASE()
                AND table_name = %s
                AND index_name = %s
            """, [table, index_name])
            if cursor.fetchone()[0] == 0:
                issues.append(f"Index '{index_name}' on table '{table}' is missing")
                print(f"   ⚠️  {index_name} on {table} - MISSING")
            else:
                print(f"   ✅ {index_name} on {table}")
        
        # Check 6: Database statistics
        print("\n6. Database statistics...")
        cursor.execute("SELECT COUNT(*) FROM users WHERE role = 'borrower'")
        borrower_count = cursor.fetchone()[0]
        print(f"   📊 Total borrowers: {borrower_count}")
        
        cursor.execute("SELECT COUNT(*) FROM loans WHERE is_deleted = 0")
        active_loans = cursor.fetchone()[0]
        print(f"   📊 Active loans: {active_loans}")
        
        cursor.execute("SELECT COUNT(*) FROM loan_applications WHERE status = 'pending'")
        pending_apps = cursor.fetchone()[0]
        print(f"   📊 Pending applications: {pending_apps}")
    
    # Final report
    print("\n" + "="*80)
    if issues:
        print("❌ HEALTH CHECK FAILED")
        print("="*80)
        print("\nIssues found:")
        for issue in issues:
            print(f"  • {issue}")
        print("\nRecommendation: Run 'python fix_schema_migration_comprehensive.py' to fix issues")
        return 1
    else:
        print("✅ HEALTH CHECK PASSED")
        print("="*80)
        print("\nAll checks passed! Database schema is healthy.")
        return 0


if __name__ == '__main__':
    sys.exit(check_schema_health())