#!/usr/bin/env python3
"""
Debug foreign key constraint issues in detail
"""

import os
import sys
import django

# Setup Django
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings')
django.setup()

from django.db import connection

def get_detailed_column_info(table_name, column_name):
    """Get comprehensive column information"""
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT 
                COLUMN_NAME,
                DATA_TYPE,
                COLUMN_TYPE,
                IS_NULLABLE,
                COLUMN_DEFAULT,
                CHARACTER_SET_NAME,
                COLLATION_NAME,
                COLUMN_KEY,
                EXTRA
            FROM information_schema.columns 
            WHERE table_schema = DATABASE() 
            AND table_name = %s 
            AND column_name = %s
        """, [table_name, column_name])
        return cursor.fetchone()

def get_table_charset_collation(table_name):
    """Get table charset and collation"""
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT 
                TABLE_COLLATION,
                TABLE_SCHEMA
            FROM information_schema.tables 
            WHERE table_schema = DATABASE() 
            AND table_name = %s
        """, [table_name])
        return cursor.fetchone()

def main():
    print("=== DETAILED ANALYSIS OF FOREIGN KEY ISSUE ===\n")
    
    # Check loans.id column
    print("LOANS.ID COLUMN:")
    loans_id_info = get_detailed_column_info('loans', 'id')
    if loans_id_info:
        for i, field in enumerate(['COLUMN_NAME', 'DATA_TYPE', 'COLUMN_TYPE', 'IS_NULLABLE', 'COLUMN_DEFAULT', 'CHARACTER_SET_NAME', 'COLLATION_NAME', 'COLUMN_KEY', 'EXTRA']):
            print(f"  {field}: {loans_id_info[i]}")
    
    print("\nLOANS TABLE CHARSET/COLLATION:")
    loans_table_info = get_table_charset_collation('loans')
    if loans_table_info:
        print(f"  TABLE_COLLATION: {loans_table_info[0]}")
        print(f"  TABLE_SCHEMA: {loans_table_info[1]}")
    
    # Check if penalty_charges table exists and its structure
    print("\n=== CHECKING IF PENALTY_CHARGES EXISTS ===")
    with connection.cursor() as cursor:
        cursor.execute("SHOW TABLES LIKE 'penalty_charges'")
        if cursor.fetchone():
            print("penalty_charges table EXISTS")
            cursor.execute("DESCRIBE penalty_charges")
            columns = cursor.fetchall()
            for col in columns:
                print(f"  {col}")
        else:
            print("penalty_charges table does NOT exist")
    
    # Try to create a simple test table with foreign key
    print("\n=== TESTING SIMPLE FOREIGN KEY CREATION ===")
    try:
        with connection.cursor() as cursor:
            # Drop test table if exists
            cursor.execute("DROP TABLE IF EXISTS test_penalty_fk")
            
            # Create test table
            cursor.execute(f"""
                CREATE TABLE test_penalty_fk (
                    id char(32) NOT NULL PRIMARY KEY,
                    loan_id char(32) NOT NULL,
                    amount decimal(10,2) NOT NULL
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            """)
            print("✓ Test table created successfully")
            
            # Try to add foreign key constraint
            cursor.execute("""
                ALTER TABLE test_penalty_fk 
                ADD CONSTRAINT test_penalty_fk_loan_id_fk 
                FOREIGN KEY (loan_id) REFERENCES loans(id)
            """)
            print("✓ Foreign key constraint added successfully")
            
            # Clean up
            cursor.execute("DROP TABLE test_penalty_fk")
            print("✓ Test table cleaned up")
            
    except Exception as e:
        print(f"✗ Foreign key test failed: {e}")

if __name__ == "__main__":
    main()