"""add feedback username snapshot

Revision ID: 20260730_07
Revises: 20260730_06
Create Date: 2026-07-30
"""
from __future__ import annotations

from collections.abc import Sequence

import sqlalchemy as sa
from alembic import op

revision: str = "20260730_07"
down_revision: str | None = "20260730_06"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    op.add_column(
        "demand_feedback",
        sa.Column(
            "feedback_username_snapshot",
            sa.String(length=64),
            nullable=False,
            server_default="",
            comment="反馈人登录账号快照",
        ),
    )
    feedback = sa.table(
        "demand_feedback",
        sa.column("feedback_user_id", sa.Integer()),
        sa.column("feedback_username_snapshot", sa.String(length=64)),
    )
    user_account = sa.table(
        "auth_user",
        sa.column("id", sa.Integer()),
        sa.column("username", sa.String(length=64)),
    )
    username = (
        sa.select(user_account.c.username)
        .where(user_account.c.id == feedback.c.feedback_user_id)
        .scalar_subquery()
    )
    op.execute(
        feedback.update()
        .where(feedback.c.feedback_username_snapshot == "")
        .values(feedback_username_snapshot=username)
    )
    with op.batch_alter_table("demand_feedback") as batch_op:
        batch_op.alter_column(
            "feedback_username_snapshot",
            existing_type=sa.String(length=64),
            server_default=None,
        )


def downgrade() -> None:
    with op.batch_alter_table("demand_feedback") as batch_op:
        batch_op.drop_column("feedback_username_snapshot")