# Generated by Django 5.2.5 on 2025-08-26 22:02

import django.db.models.deletion
from django.db import migrations, models


class Migration(migrations.Migration):

    dependencies = [
        ('loans', '0010_loan_deleted_at_loan_deleted_by_loan_is_deleted'),
        ('utils', '0004_alter_document_document_type_and_more'),
    ]

    operations = [
        # Removing the 'app' field (db column 'related_application_id') can fail
        # if the column or its constraints do not exist in the database. Use a
        # guarded RunPython operation that drops the FK/index/column only when
        # present to make this migration idempotent and safe.
        migrations.RunPython(
            code=lambda apps, schema_editor: _safe_remove_related_application_column(schema_editor),
            reverse_code=migrations.RunPython.noop,
        ),
        # Add 'loan_app' field only if the underlying column doesn't already exist
        migrations.RunPython(
            code=lambda apps, schema_editor: _safe_add_loan_app_column(schema_editor),
            reverse_code=migrations.RunPython.noop,
        ),
    ]


def _safe_remove_related_application_column(schema_editor):
    """Drop FK, indexes and the related_application_id column if they exist.

    This is defensive because some databases in the wild are missing the
    column (or its FK/index) and an unconditional RemoveField would fail.
    """
    conn = schema_editor.connection
    with conn.cursor() as cursor:
        # The model's table name for app 'utils' and model 'Notification' is
        # typically 'utils_notification'. Try that name.
        table_names = ['utils_notification', 'notifications']
        for table in table_names:
            # Check if the table exists in the current database
            cursor.execute("""
                SELECT COUNT(*)
                FROM information_schema.TABLES
                WHERE TABLE_SCHEMA = DATABASE()
                  AND TABLE_NAME = %s
            """, (table,))
            if cursor.fetchone()[0] == 0:
                continue

            # 1) Drop foreign key constraints referencing the column
            cursor.execute("""
                SELECT CONSTRAINT_NAME
                FROM information_schema.KEY_COLUMN_USAGE
                WHERE TABLE_SCHEMA = DATABASE()
                  AND TABLE_NAME = %s
                  AND COLUMN_NAME = 'related_application_id'
                  AND REFERENCED_TABLE_NAME IS NOT NULL
            """, (table,))
            fks = cursor.fetchall()
            for fk in fks:
                try:
                    cursor.execute(f"ALTER TABLE {table} DROP FOREIGN KEY `{fk[0]}`")
                except Exception:
                    # ignore failures and continue
                    pass

            # 2) Drop any non-primary indexes on the column
            cursor.execute("""
                SELECT DISTINCT INDEX_NAME
                FROM information_schema.STATISTICS
                WHERE TABLE_SCHEMA = DATABASE()
                  AND TABLE_NAME = %s
                  AND COLUMN_NAME = 'related_application_id'
            """, (table,))
            idxs = cursor.fetchall()
            for idx in idxs:
                idx_name = idx[0]
                if idx_name and idx_name.upper() != 'PRIMARY':
                    try:
                        cursor.execute(f"DROP INDEX `{idx_name}` ON {table}")
                    except Exception:
                        pass

            # 3) Drop the column if it exists
            cursor.execute("""
                SELECT COUNT(*)
                FROM information_schema.COLUMNS
                WHERE TABLE_SCHEMA = DATABASE()
                  AND TABLE_NAME = %s
                  AND COLUMN_NAME = 'related_application_id'
            """, (table,))
            if cursor.fetchone()[0] > 0:
                try:
                    cursor.execute(f"ALTER TABLE {table} DROP COLUMN `related_application_id`")
                except Exception:
                    pass
                # If we've handled one table, we're done
                return


def _safe_add_loan_app_column(schema_editor):
    """Add `loan_app_id` column and FK to utils_notification if missing.

    This mirrors what the original AddField would do but guards against the
    column already existing (which causes duplicate column errors during
    migrate).
    """
    conn = schema_editor.connection
    with conn.cursor() as cursor:
        table_names = ['utils_notification', 'notifications']
        for table in table_names:
            # Check if table exists
            cursor.execute("""
                SELECT COUNT(*)
                FROM information_schema.TABLES
                WHERE TABLE_SCHEMA = DATABASE()
                  AND TABLE_NAME = %s
            """, (table,))
            if cursor.fetchone()[0] == 0:
                continue

            # Check if column already exists
            cursor.execute("""
                SELECT COUNT(*)
                FROM information_schema.COLUMNS
                WHERE TABLE_SCHEMA = DATABASE()
                  AND TABLE_NAME = %s
                  AND COLUMN_NAME = 'loan_app_id'
            """, (table,))
            if cursor.fetchone()[0] > 0:
                # Column already present, skip
                return

            # Add the column (use CHAR(32) to match existing PK types)
            try:
                cursor.execute(f"ALTER TABLE {table} ADD COLUMN `loan_app_id` CHAR(32) NULL")
            except Exception:
                # if addition fails, skip
                return

            # Create an index on the column
            try:
                cursor.execute(f"CREATE INDEX `{table}_loan_app_id_idx` ON {table} (`loan_app_id`)")
            except Exception:
                pass

            # Add FK constraint if possible
            try:
                fk_name = f"{table}_loan_app_id_fk"
                cursor.execute(f"ALTER TABLE {table} ADD CONSTRAINT `{fk_name}` FOREIGN KEY (`loan_app_id`) REFERENCES loans_loanapplication(`id`)")
            except Exception:
                # ignore FK failures (type mismatch or missing referenced table)
                pass
            return