"""Add document_id column to research_resources

This migration adds the document_id column that was added to the
ResearchResource model in commit 2033f977e but lacked a corresponding
Alembic migration.  Databases created before that commit (or stamped
at revision 0001 before the column existed) will be missing it, which
causes OperationalError on any Library page query that references the
column.

Revision ID: 0005
Revises: 0004
Create Date: 2026-04-04

Column Added:
=============
- document_id (VARCHAR(36)): Optional FK linking a resource to a
  library Document.  Nullable, indexed.

Migration Notes:
===============
- Uses SQLite batch mode (table recreation) for ALTER TABLE operations
- The FK constraint is NOT added here because SQLite batch-mode ALTER
  cannot reliably add foreign keys.  Referential integrity is enforced
  at the ORM level via the relationship on ResearchResource.
- Idempotent: skips the column if it already exists (fresh databases
  created after the model change will already have it).

Downgrade Behavior:
==================
The downgrade() function removes the document_id column and its index.
Any data stored in document_id will be LOST on downgrade.
"""

from alembic import op
import sqlalchemy as sa
from sqlalchemy import inspect

# revision identifiers, used by Alembic.
revision = "0005"
down_revision = "0004"
branch_labels = None
depends_on = None


def column_exists(table_name: str, column_name: str) -> bool:
    """Check if a column exists in a table."""
    bind = op.get_bind()
    inspector = inspect(bind)

    if not inspector.has_table(table_name):
        return False

    columns = {col["name"] for col in inspector.get_columns(table_name)}
    return column_name in columns


def upgrade():
    """Add document_id column to research_resources if it doesn't exist."""
    bind = op.get_bind()
    inspector = inspect(bind)

    if not inspector.has_table("research_resources"):
        # Table doesn't exist yet, will be created by initial migration
        return

    if column_exists("research_resources", "document_id"):
        # Column already exists (fresh database created after model change)
        return

    # Use batch mode for SQLite compatibility
    with op.batch_alter_table("research_resources") as batch_op:
        batch_op.add_column(
            sa.Column("document_id", sa.String(36), nullable=True)
        )
        batch_op.create_index(
            "ix_research_resources_document_id", ["document_id"]
        )


def downgrade():
    """Remove document_id column from research_resources."""
    bind = op.get_bind()
    inspector = inspect(bind)

    if not inspector.has_table("research_resources"):
        return

    if not column_exists("research_resources", "document_id"):
        return

    with op.batch_alter_table("research_resources") as batch_op:
        batch_op.drop_index("ix_research_resources_document_id")
        batch_op.drop_column("document_id")
