#!/usr/bin/env python3
"""
Enhanced Client Deletion System
Handles all foreign key relationships and dependencies safely
"""

import os
import sys
import django
from django.db import transaction, connection
from django.core.exceptions import ObjectDoesNotExist

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

from users.models import CustomUser
from loans.models import Loan, LoanApplication, Repayment, RolloverRequest
from utils.models import LoanOffer, Receipt, LoanStatement, AuditLog, Document, DocumentShare, Notification
from reports.models import LoanScoring


class SafeClientDeletion:
    """Safe client deletion with comprehensive cleanup"""
    
    def __init__(self):
        self.deleted_items = {
            'loans': 0,
            'applications': 0,
            'repayments': 0,
            'offers': 0,
            'receipts': 0,
            'statements': 0,
            'documents': 0,
            'notifications': 0,
            'audit_logs': 0,
            'scoring_records': 0,
            'rollover_requests': 0
        }
        self.errors = []
    
    def get_client_dependencies(self, client):
        """Get all dependencies for a client"""
        dependencies = {}
        
        try:
            # Loans
            dependencies['loans'] = Loan.objects.filter(borrower=client)
            print(f"Found {dependencies['loans'].count()} loans")
            
            # Loan Applications
            dependencies['applications'] = LoanApplication.objects.filter(borrower=client)
            print(f"Found {dependencies['applications'].count()} loan applications")
            
            # Repayments (through loans)
            loan_ids = list(dependencies['loans'].values_list('id', flat=True))
            dependencies['repayments'] = Repayment.objects.filter(loan_id__in=loan_ids)
            print(f"Found {dependencies['repayments'].count()} repayments")
            
            # Rollover Requests
            dependencies['rollover_requests'] = RolloverRequest.objects.filter(borrower=client)
            print(f"Found {dependencies['rollover_requests'].count()} rollover requests")
            
            # Utils models
            dependencies['offers'] = LoanOffer.objects.filter(borrower=client)
            dependencies['receipts'] = Receipt.objects.filter(borrower=client)
            dependencies['statements'] = LoanStatement.objects.filter(borrower=client)
            dependencies['notifications'] = Notification.objects.filter(user=client)
            
            print(f"Found {dependencies['offers'].count()} loan offers")
            print(f"Found {dependencies['receipts'].count()} receipts")
            print(f"Found {dependencies['statements'].count()} statements")
            print(f"Found {dependencies['notifications'].count()} notifications")
            
            # Documents
            dependencies['documents'] = Document.objects.filter(uploaded_by=client)
            dependencies['document_shares'] = DocumentShare.objects.filter(shared_with=client)
            print(f"Found {dependencies['documents'].count()} documents")
            print(f"Found {dependencies['document_shares'].count()} document shares")
            
            # Reports
            try:
                dependencies['scoring_records'] = LoanScoring.objects.filter(borrower=client)
                print(f"Found {dependencies['scoring_records'].count()} scoring records")
            except:
                dependencies['scoring_records'] = []
                print("No scoring records found or table doesn't exist")
            
            # Audit logs
            dependencies['audit_logs'] = AuditLog.objects.filter(user=client)
            print(f"Found {dependencies['audit_logs'].count()} audit logs")
            
        except Exception as e:
            print(f"Error getting dependencies: {e}")
            self.errors.append(f"Error getting dependencies: {e}")
        
        return dependencies
    
    def safe_delete_client(self, client_id, delete_mode='blacklist'):
        """
        Safely delete a client with all dependencies
        delete_mode: 'blacklist' (soft delete) or 'permanent' (hard delete)
        """
        try:
            client = CustomUser.objects.get(id=client_id, role='borrower')
        except CustomUser.DoesNotExist:
            return False, "Client not found"
        
        print(f"\n=== Processing client: {client.get_full_name()} ===")
        print(f"Delete mode: {delete_mode}")
        
        if delete_mode == 'blacklist':
            return self.blacklist_client(client)
        else:
            return self.permanent_delete_client(client)
    
    def blacklist_client(self, client):
        """Soft delete - blacklist the client"""
        try:
            with transaction.atomic():
                # Update client status
                client.status = 'blacklisted'
                client.is_active = False
                client.save()
                
                # Create audit log
                AuditLog.objects.create(
                    user=None,  # System action
                    action='blacklist',
                    model_name='CustomUser',
                    object_id=str(client.id),
                    description=f'Client blacklisted: {client.get_full_name()}'
                )
                
                print(f"✅ Client {client.get_full_name()} has been blacklisted")
                return True, f"Client {client.get_full_name()} has been blacklisted successfully"
                
        except Exception as e:
            error_msg = f"Error blacklisting client: {e}"
            print(f"❌ {error_msg}")
            self.errors.append(error_msg)
            return False, error_msg
    
    def permanent_delete_client(self, client):
        """Permanently delete client and all related data"""
        try:
            with transaction.atomic():
                client_name = client.get_full_name()
                dependencies = self.get_client_dependencies(client)
                
                print(f"\n--- Starting permanent deletion for {client_name} ---")
                
                # Delete in correct order to avoid foreign key constraints
                
                # 1. Delete receipts first (they reference repayments and loans)
                if dependencies.get('receipts'):
                    count = dependencies['receipts'].count()
                    dependencies['receipts'].delete()
                    self.deleted_items['receipts'] = count
                    print(f"✅ Deleted {count} receipts")
                
                # 2. Delete loan statements
                if dependencies.get('statements'):
                    count = dependencies['statements'].count()
                    dependencies['statements'].delete()
                    self.deleted_items['statements'] = count
                    print(f"✅ Deleted {count} loan statements")
                
                # 3. Delete loan offers
                if dependencies.get('offers'):
                    count = dependencies['offers'].count()
                    dependencies['offers'].delete()
                    self.deleted_items['offers'] = count
                    print(f"✅ Deleted {count} loan offers")
                
                # 4. Delete rollover requests
                if dependencies.get('rollover_requests'):
                    count = dependencies['rollover_requests'].count()
                    dependencies['rollover_requests'].delete()
                    self.deleted_items['rollover_requests'] = count
                    print(f"✅ Deleted {count} rollover requests")
                
                # 5. Delete repayments
                if dependencies.get('repayments'):
                    count = dependencies['repayments'].count()
                    dependencies['repayments'].delete()
                    self.deleted_items['repayments'] = count
                    print(f"✅ Deleted {count} repayments")
                
                # 6. Delete loans
                if dependencies.get('loans'):
                    count = dependencies['loans'].count()
                    dependencies['loans'].delete()
                    self.deleted_items['loans'] = count
                    print(f"✅ Deleted {count} loans")
                
                # 7. Delete loan applications
                if dependencies.get('applications'):
                    count = dependencies['applications'].count()
                    dependencies['applications'].delete()
                    self.deleted_items['applications'] = count
                    print(f"✅ Deleted {count} loan applications")
                
                # 8. Delete scoring records
                if dependencies.get('scoring_records'):
                    count = len(dependencies['scoring_records'])
                    if count > 0:
                        dependencies['scoring_records'].delete()
                        self.deleted_items['scoring_records'] = count
                        print(f"✅ Deleted {count} scoring records")
                
                # 9. Delete document shares
                if dependencies.get('document_shares'):
                    count = dependencies['document_shares'].count()
                    dependencies['document_shares'].delete()
                    print(f"✅ Deleted {count} document shares")
                
                # 10. Delete documents
                if dependencies.get('documents'):
                    count = dependencies['documents'].count()
                    # Delete physical files
                    for doc in dependencies['documents']:
                        try:
                            if doc.file and os.path.exists(doc.file.path):
                                os.remove(doc.file.path)
                        except:
                            pass
                    dependencies['documents'].delete()
                    self.deleted_items['documents'] = count
                    print(f"✅ Deleted {count} documents")
                
                # 11. Delete notifications
                if dependencies.get('notifications'):
                    count = dependencies['notifications'].count()
                    dependencies['notifications'].delete()
                    self.deleted_items['notifications'] = count
                    print(f"✅ Deleted {count} notifications")
                
                # 12. Update audit logs (don't delete, just anonymize)
                if dependencies.get('audit_logs'):
                    count = dependencies['audit_logs'].update(user=None)
                    self.deleted_items['audit_logs'] = count
                    print(f"✅ Anonymized {count} audit logs")
                
                # 13. Remove user from all groups before deletion
                try:
                    # First try using Django's ORM if possible
                    if hasattr(client, 'groups'):
                        try:
                            client.groups.clear()
                            print("✅ Removed user from all groups using ORM")
                        except Exception as orm_error:
                            print(f"ORM clear failed, falling back to raw SQL: {orm_error}")
                            raise  # Re-raise to trigger the SQL fallback
                    else:
                        raise AttributeError("No groups attribute found on user model")
                            
                except Exception as e:
                    print(f"Falling back to direct SQL cleanup: {e}")
                    try:
                        with connection.cursor() as cursor:
                            # Try to delete using the correct column name based on the actual table structure
                            # First, check if the table exists
                            cursor.execute("""
                                SELECT COUNT(*) FROM information_schema.tables 
                                WHERE table_schema = DATABASE() AND table_name = 'users_groups'
                            """)
                            if cursor.fetchone()[0] > 0:
                                # Try to find the correct column name by checking the table structure
                                cursor.execute("SHOW COLUMNS FROM users_groups")
                                columns = [row[0] for row in cursor.fetchall()]
                                
                                # Look for a column that references the user (not id, not group_id)
                                user_columns = [col for col in columns if col not in ('id', 'group_id')]
                                
                                if user_columns:
                                    user_column = user_columns[0]
                                    # Try to delete using the found column
                                    try:
                                        cursor.execute(
                                            f"DELETE FROM users_groups WHERE {user_column} = %s",
                                            [str(client.id)]
                                        )
                                        print(f"✅ Removed user from all groups using column: {user_column}")
                                    except Exception as delete_error:
                                        print(f"⚠️ Error deleting with {user_column}: {delete_error}")
                                        # If that fails, try with a different approach
                                        try:
                                            cursor.execute("""
                                                DELETE ug FROM users_groups ug
                                                INNER JOIN users_customuser u ON ug.{} = u.id
                                                WHERE u.id = %s
                                            """.format(user_column), [str(client.id)])
                                            print(f"✅ Removed user from groups using join with {user_column}")
                                        except Exception as join_error:
                                            print(f"⚠️ Failed to remove groups with join: {join_error}")
                                            raise
                                else:
                                    print("⚠️ Could not identify user column in users_groups table")
                            else:
                                print("⚠️ users_groups table does not exist, skipping group cleanup")
                    except Exception as sql_error:
                        error_msg = f"Error during group cleanup: {sql_error}"
                        print(f"⚠️ {error_msg}")
                        self.errors.append(error_msg)
                except Exception as e:
                    error_msg = f"Error removing user from groups: {e}"
                    print(f"⚠️ {error_msg}")
                    self.errors.append(error_msg)
                
                # 14. Finally delete the client
                client.delete()
                
                # Create final audit log
                AuditLog.objects.create(
                    user=None,  # System action
                    action='delete',
                    model_name='CustomUser',
                    object_id=str(client.id),
                    description=f'Permanently deleted client: {client_name} and all related data'
                )
                
                print(f"\n✅ Successfully deleted client {client_name} and all related data")
                return True, f"Client {client_name} and all related data have been permanently deleted"
                
        except Exception as e:
            error_msg = f"Error during permanent deletion: {e}"
            print(f"❌ {error_msg}")
            self.errors.append(error_msg)
            return False, error_msg
    
    def get_deletion_summary(self):
        """Get summary of deletion operation"""
        return {
            'deleted_items': self.deleted_items,
            'errors': self.errors,
            'total_items_deleted': sum(self.deleted_items.values())
        }


def main():
    """Main function for testing"""
    if len(sys.argv) < 2:
        print("Usage: python fix_client_deletion.py <client_id> [delete_mode]")
        print("delete_mode: 'blacklist' (default) or 'permanent'")
        return
    
    client_id = sys.argv[1]
    delete_mode = sys.argv[2] if len(sys.argv) > 2 else 'blacklist'
    
    deleter = SafeClientDeletion()
    success, message = deleter.safe_delete_client(client_id, delete_mode)
    
    print(f"\n{'='*50}")
    print(f"RESULT: {'SUCCESS' if success else 'FAILED'}")
    print(f"MESSAGE: {message}")
    
    if not success:
        print(f"\nERRORS:")
        for error in deleter.errors:
            print(f"  - {error}")
    
    summary = deleter.get_deletion_summary()
    print(f"\nDELETION SUMMARY:")
    for item_type, count in summary['deleted_items'].items():
        if count > 0:
            print(f"  {item_type}: {count}")
    
    print(f"Total items processed: {summary['total_items_deleted']}")


if __name__ == '__main__':
    main()
