"""Add FK constraint on domain_term_dictionary.source_document_id -> documents.id with CASCADE delete.

When a document is deleted, all domain terms sourced from it are automatically removed.
Terms with NULL source_document_id are unaffected.

Revision ID: 1a2b3c4d5e6f
Revises: 0b1c2d3e4f5a
Create Date: 2026-04-09 10:00:00.000000

"""
from typing import Sequence, Union

from alembic import op

# revision identifiers, used by Alembic.
revision: str = '1a2b3c4d5e6f'
down_revision: Union[str, None] = '0b1c2d3e4f5a'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # NULL out orphaned source_document_id values before adding FK
    op.execute("""
        UPDATE domain_term_dictionary
        SET source_document_id = NULL
        WHERE source_document_id IS NOT NULL
        AND source_document_id NOT IN (SELECT id FROM documents)
    """)

    op.create_foreign_key(
        'fk_domain_term_source_document',
        'domain_term_dictionary',
        'documents',
        ['source_document_id'],
        ['id'],
        ondelete='CASCADE',
    )


def downgrade() -> None:
    op.drop_constraint(
        'fk_domain_term_source_document',
        'domain_term_dictionary',
        type_='foreignkey',
    )
