#!/usr/bin/env python3
"""
Comprehensive Schema Migration Verification Script

This script verifies that all references to old database field names
have been properly updated to match the new schema.

Key Changes from Old to New Schema:
1. Many tables removed from new schema (auth_permission data, cache_table data, etc.)
2. Field name consistency maintained (borrower_id, loan_product_id, etc.)
3. New tables added (expenses, expenses_backup, etc.)
4. Some tables restructured (loans table has many new fields)
"""

import os
import sys
import django
import re
from pathlib import Path

# 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
from django.conf import settings


class SchemaVerifier:
    """Verify database schema migration"""
    
    def __init__(self):
        self.issues = []
        self.warnings = []
        self.successes = []
        
    def log_issue(self, message):
        """Log an issue"""
        self.issues.append(message)
        print(f"❌ ISSUE: {message}")
    
    def log_warning(self, message):
        """Log a warning"""
        self.warnings.append(message)
        print(f"⚠️  WARNING: {message}")
    
    def log_success(self, message):
        """Log a success"""
        self.successes.append(message)
        print(f"✅ {message}")
    
    def check_table_exists(self, table_name):
        """Check if a table exists in the database"""
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT COUNT(*)
                FROM information_schema.tables
                WHERE table_schema = DATABASE()
                AND table_name = %s
            """, [table_name])
            return cursor.fetchone()[0] > 0
    
    def check_column_exists(self, table_name, column_name):
        """Check if a column exists in a table"""
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT COUNT(*)
                FROM information_schema.columns
                WHERE table_schema = DATABASE()
                AND table_name = %s
                AND column_name = %s
            """, [table_name, column_name])
            return cursor.fetchone()[0] > 0
    
    def get_table_columns(self, table_name):
        """Get all columns for a table"""
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT column_name, data_type, is_nullable, column_default
                FROM information_schema.columns
                WHERE table_schema = DATABASE()
                AND table_name = %s
                ORDER BY ordinal_position
            """, [table_name])
            return cursor.fetchall()
    
    def verify_core_tables(self):
        """Verify that core tables exist"""
        print("\n" + "="*80)
        print("VERIFYING CORE TABLES")
        print("="*80)
        
        core_tables = [
            'users',
            'branches',
            'loans',
            'loan_applications',
            'loan_products',
            'loans_repayment',
            'mpesa_transactions',
            'mpesa_configurations',
            'receipts',
            'loan_statements',
            'offer_letters',
            'rollover_requests',
            'customer_requests',
            'expenses',
        ]
        
        for table in core_tables:
            if self.check_table_exists(table):
                self.log_success(f"Table '{table}' exists")
            else:
                self.log_issue(f"Table '{table}' is missing")
    
    def verify_foreign_key_columns(self):
        """Verify that foreign key columns exist and are properly named"""
        print("\n" + "="*80)
        print("VERIFYING FOREIGN KEY COLUMNS")
        print("="*80)
        
        # Define expected foreign key columns for each table
        fk_checks = {
            'loans': [
                ('borrower_id', 'CHAR(32)'),
                ('application_id', 'CHAR(32)'),
                ('loan_officer_id', 'CHAR(32)'),
                ('approved_by_id', 'CHAR(32)'),
                ('disbursed_by_id', 'CHAR(32)'),
                ('deleted_by_id', 'CHAR(32)'),
                ('created_by_id', 'CHAR(32)'),
                ('updated_by_id', 'CHAR(32)'),
            ],
            'loan_applications': [
                ('borrower_id', 'CHAR(32)'),
                ('loan_product_id', 'CHAR(32)'),
                ('reviewed_by_id', 'CHAR(32)'),
                ('loan_officer_id', 'CHAR(32)'),
                ('approved_by_id', 'CHAR(32)'),
                ('disbursed_by_id', 'CHAR(32)'),
            ],
            'loan_statements': [
                ('loan_id', 'CHAR(32)'),
                ('borrower_id', 'CHAR(32)'),
            ],
            'receipts': [
                ('repayment_id', 'CHAR(32)'),
                ('loan_id', 'CHAR(32)'),
                ('borrower_id', 'CHAR(32)'),
            ],
            'offer_letters': [
                ('application_id', 'CHAR(32)'),
                ('borrower_id', 'CHAR(32)'),
            ],
            'rollover_requests': [
                ('loan_id', 'CHAR(32)'),
                ('borrower_id', 'CHAR(32)'),
                ('reviewed_by_id', 'CHAR(32)'),
            ],
            'customer_requests': [
                ('customer_id', 'CHAR(32)'),
                ('assigned_to_id', 'CHAR(32)'),
                ('related_loan_id', 'CHAR(32)'),
                ('related_application_id', 'CHAR(32)'),
                ('resolved_by_id', 'CHAR(32)'),
            ],
            'expenses': [
                ('branch_id', 'VARCHAR(32)'),
                ('loan_id', 'VARCHAR(32)'),
                ('staff_id', 'VARCHAR(32)'),
                ('approved_by_id', 'VARCHAR(32)'),
            ],
            'mpesa_transactions': [
                ('loan_id', 'CHAR(32)'),
                ('borrower_id', 'CHAR(32)'),
            ],
            'loan_scoring': [
                ('user_id', 'CHAR(32)'),
            ],
        }
        
        for table, columns in fk_checks.items():
            if not self.check_table_exists(table):
                self.log_warning(f"Table '{table}' does not exist, skipping FK checks")
                continue
            
            print(f"\nChecking table: {table}")
            for column_name, expected_type in columns:
                if self.check_column_exists(table, column_name):
                    self.log_success(f"  Column '{table}.{column_name}' exists")
                else:
                    self.log_issue(f"  Column '{table}.{column_name}' is missing")
    
    def verify_users_table_structure(self):
        """Verify the users table has all required fields"""
        print("\n" + "="*80)
        print("VERIFYING USERS TABLE STRUCTURE")
        print("="*80)
        
        if not self.check_table_exists('users'):
            self.log_issue("Users table does not exist")
            return
        
        required_columns = [
            'id',
            'email',
            'phone_number',
            'first_name',
            'last_name',
            'role',
            'status',
            'branch_id',
            'portfolio_manager_id',
            'approved_by_id',
            'rejected_by_id',
            'verified_by_id',
        ]
        
        for column in required_columns:
            if self.check_column_exists('users', column):
                self.log_success(f"Column 'users.{column}' exists")
            else:
                self.log_issue(f"Column 'users.{column}' is missing")
    
    def verify_loans_table_structure(self):
        """Verify the loans table has all required fields"""
        print("\n" + "="*80)
        print("VERIFYING LOANS TABLE STRUCTURE")
        print("="*80)
        
        if not self.check_table_exists('loans'):
            self.log_issue("Loans table does not exist")
            return
        
        required_columns = [
            'id',
            'loan_number',
            'borrower_id',
            'application_id',
            'principal_amount',
            'interest_amount',
            'processing_fee',
            'total_amount',
            'disbursement_date',
            'due_date',
            'duration_days',
            'status',
            'amount_paid',
            'outstanding_balance',
            'loan_officer_id',
            'approved_by_id',
            'disbursed_by_id',
            'is_deleted',
            'deleted_by_id',
            'registration_fee',
        ]
        
        for column in required_columns:
            if self.check_column_exists('loans', column):
                self.log_success(f"Column 'loans.{column}' exists")
            else:
                self.log_issue(f"Column 'loans.{column}' is missing")
    
    def check_python_code_references(self):
        """Check Python code for references to old field names"""
        print("\n" + "="*80)
        print("CHECKING PYTHON CODE FOR FIELD REFERENCES")
        print("="*80)
        
        # Patterns to search for
        patterns = {
            'borrower_id': r'\bborrower_id\b',
            'loan_product_id': r'\bloan_product_id\b',
            'reviewed_by_id': r'\breviewed_by_id\b',
            'loan_officer_id': r'\bloan_officer_id\b',
            'approved_by_id': r'\bapproved_by_id\b',
            'disbursed_by_id': r'\bdisbursed_by_id\b',
            'application_id': r'\bapplication_id\b',
        }
        
        # Directories to search
        search_dirs = [
            'users',
            'loans',
            'utils',
            'mpesa',
        ]
        
        base_path = Path(__file__).parent
        
        for dir_name in search_dirs:
            dir_path = base_path / dir_name
            if not dir_path.exists():
                continue
            
            print(f"\nSearching in {dir_name}/")
            
            for py_file in dir_path.rglob('*.py'):
                if '__pycache__' in str(py_file):
                    continue
                
                try:
                    with open(py_file, 'r', encoding='utf-8') as f:
                        content = f.read()
                        
                    for field_name, pattern in patterns.items():
                        matches = re.findall(pattern, content)
                        if matches:
                            count = len(matches)
                            rel_path = py_file.relative_to(base_path)
                            self.log_success(f"  {rel_path}: {count} references to '{field_name}'")
                
                except Exception as e:
                    self.log_warning(f"  Could not read {py_file}: {e}")
    
    def verify_model_definitions(self):
        """Verify Django model definitions match database schema"""
        print("\n" + "="*80)
        print("VERIFYING DJANGO MODEL DEFINITIONS")
        print("="*80)
        
        try:
            from users.models import CustomUser
            from loans.models import Loan, LoanApplication, LoanProduct
            from utils.models import Receipt, LoanStatement, OfferLetter
            
            # Check User model
            user_fields = [f.name for f in CustomUser._meta.get_fields()]
            print("\nCustomUser model fields:")
            for field in ['branch', 'portfolio_manager', 'approved_by', 'rejected_by', 'verified_by']:
                if field in user_fields:
                    self.log_success(f"  CustomUser.{field} field exists")
                else:
                    self.log_issue(f"  CustomUser.{field} field is missing")
            
            # Check Loan model
            loan_fields = [f.name for f in Loan._meta.get_fields()]
            print("\nLoan model fields:")
            for field in ['borrower', 'application', 'loan_officer', 'approved_by', 'disbursed_by', 'deleted_by']:
                if field in loan_fields:
                    self.log_success(f"  Loan.{field} field exists")
                else:
                    self.log_issue(f"  Loan.{field} field is missing")
            
            # Check LoanApplication model
            app_fields = [f.name for f in LoanApplication._meta.get_fields()]
            print("\nLoanApplication model fields:")
            for field in ['borrower', 'loan_product', 'reviewed_by']:
                if field in app_fields:
                    self.log_success(f"  LoanApplication.{field} field exists")
                else:
                    self.log_issue(f"  LoanApplication.{field} field is missing")
            
        except Exception as e:
            self.log_issue(f"Error loading models: {e}")
    
    def generate_report(self):
        """Generate final report"""
        print("\n" + "="*80)
        print("VERIFICATION REPORT")
        print("="*80)
        
        print(f"\n✅ Successes: {len(self.successes)}")
        print(f"⚠️  Warnings: {len(self.warnings)}")
        print(f"❌ Issues: {len(self.issues)}")
        
        if self.issues:
            print("\n" + "="*80)
            print("CRITICAL ISSUES FOUND:")
            print("="*80)
            for issue in self.issues:
                print(f"  • {issue}")
        
        if self.warnings:
            print("\n" + "="*80)
            print("WARNINGS:")
            print("="*80)
            for warning in self.warnings:
                print(f"  • {warning}")
        
        print("\n" + "="*80)
        if self.issues:
            print("❌ VERIFICATION FAILED - Please address the issues above")
        elif self.warnings:
            print("⚠️  VERIFICATION PASSED WITH WARNINGS")
        else:
            print("✅ VERIFICATION PASSED - All checks successful!")
        print("="*80 + "\n")
        
        return len(self.issues) == 0


def main():
    """Run all verification checks"""
    print("="*80)
    print("DATABASE SCHEMA MIGRATION VERIFICATION")
    print("="*80)
    
    verifier = SchemaVerifier()
    
    # Run all checks
    verifier.verify_core_tables()
    verifier.verify_foreign_key_columns()
    verifier.verify_users_table_structure()
    verifier.verify_loans_table_structure()
    verifier.check_python_code_references()
    verifier.verify_model_definitions()
    
    # Generate report
    success = verifier.generate_report()
    
    return 0 if success else 1


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