"""allow unlinked complaints with a free-text customer name

Revision ID: 9b8f4c2a7d10
Revises: faf2abc1c57c
Create Date: 2026-08-09

Complaints no longer have to hang off a connection. The link stays optional so
tickets can be logged straight from a phone call, and `customer_name` carries
whatever the desk typed.
"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


revision: str = '9b8f4c2a7d10'
down_revision: Union[str, None] = 'faf2abc1c57c'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    op.add_column(
        'complaints',
        sa.Column('customer_name', sa.String(length=120), nullable=True),
    )
    op.alter_column('complaints', 'connection_id', existing_type=sa.Integer(), nullable=True)


def downgrade() -> None:
    # Back-fill the name onto any unlinked rows from its connection before the link
    # becomes mandatory again.
    op.execute(
        sa.text(
            """
            UPDATE complaints c
            SET customer_name = cu.full_name
            FROM connections cn JOIN customers cu ON cu.id = cn.customer_id
            WHERE c.connection_id = cn.id AND c.customer_name IS NULL
            """
        )
    )
    op.alter_column('complaints', 'connection_id', existing_type=sa.Integer(), nullable=False)
    op.drop_column('complaints', 'customer_name')