"""Make Prompt.chat_id optional

Revision ID: 6de4fe1a2745
Revises: baa5fe7dc011
Create Date: 2025-09-12 13:27:15.211060

"""

from collections.abc import Sequence

import sqlalchemy as sa
from sqlalchemy import text

from alembic import op

# revision identifiers, used by Alembic.
revision: str = "6de4fe1a2745"
down_revision: str | None = "baa5fe7dc011"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.alter_column("prompt", "chat_id", existing_type=sa.UUID(), nullable=True)
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###

    # Check for any prompts with NULL chat_id before making it NOT NULL
    connection = op.get_bind()
    result = connection.execute(
        text("SELECT COUNT(*) FROM prompt WHERE chat_id IS NULL")
    )
    null_count = result.scalar()

    if null_count and null_count > 0:
        raise ValueError(
            f"Cannot downgrade: {null_count} prompt(s) have NULL chat_id. "
            "Please manually handle these records before downgrading:\n"
            "  - Option 1: DELETE FROM prompt WHERE chat_id IS NULL;\n"
            "  - Option 2: Assign valid chat_ids to these prompts"
        )

    op.alter_column("prompt", "chat_id", existing_type=sa.UUID(), nullable=False)
    # ### end Alembic commands ###
