"""Anonymous community feed and moderation records.

Revision ID: 521
Revises: 520

Anonymous is a reader-facing presentation mode. The existing author foreign
keys stay intact so ownership, active-employee checks and the platform audit
trail do not become blind spots. ``feed_reports`` supplies the governance
queue; identity disclosure is never persisted there and remains an audited
read of the original content record.
"""

from __future__ import annotations

import sqlalchemy as sa

from alembic import op
from app.core.migration_helpers import id_type_for, table_kw_for

revision = "521"
down_revision = "520"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # MySQL/TiDB DDL is non-transactional.  A failed first attempt may have
    # already added the visibility columns, so make the recovery rerunnable.
    bind = op.get_bind()
    inspector = sa.inspect(bind)

    if "author_visibility" not in {c["name"] for c in inspector.get_columns("feed_posts")}:
        op.add_column(
            "feed_posts",
            sa.Column(
                "author_visibility", sa.String(length=16), nullable=False, server_default="named"
            ),
        )
    if "author_visibility" not in {c["name"] for c in inspector.get_columns("feed_comments")}:
        op.add_column(
            "feed_comments",
            sa.Column(
                "author_visibility", sa.String(length=16), nullable=False, server_default="named"
            ),
        )

    if "feed_reports" not in inspector.get_table_names():
        # ``identity_persons.id`` is CHAR(36) on production TiDB but can be
        # VARCHAR(36) under local MySQL.  Match both its type and collation;
        # a plain String(36) trips TiDB/MySQL errno 3780 for this FK.
        person_id_type = id_type_for("identity_persons")
        op.create_table(
            "feed_reports",
            sa.Column("id", sa.String(length=36), primary_key=True),
            sa.Column("resource_type", sa.String(length=16), nullable=False),
            sa.Column("resource_id", sa.String(length=36), nullable=False),
            sa.Column(
                "reporter_person_id",
                person_id_type,
                sa.ForeignKey("identity_persons.id"),
                nullable=False,
            ),
            sa.Column("reason", sa.String(length=32), nullable=False),
            sa.Column("detail", sa.Text(), nullable=True),
            sa.Column("status", sa.String(length=16), nullable=False, server_default="open"),
            sa.Column(
                "resolved_by_person_id",
                id_type_for("identity_persons"),
                sa.ForeignKey("identity_persons.id"),
                nullable=True,
            ),
            sa.Column("resolution_note", sa.Text(), nullable=True),
            sa.Column("resolved_at", sa.DateTime(timezone=True), nullable=True),
            sa.Column(
                "created_at",
                sa.DateTime(timezone=True),
                nullable=False,
                server_default=sa.func.now(),
            ),
            sa.UniqueConstraint(
                "resource_type",
                "resource_id",
                "reporter_person_id",
                name="uq_feed_reports_resource_reporter",
            ),
            **table_kw_for("identity_persons"),
        )
    # A table can exist while one of its indexes is missing after an
    # interrupted non-transactional DDL run. Refresh the inspector after a
    # possible create and repair indexes independently of table creation.
    report_indexes = {i["name"] for i in sa.inspect(bind).get_indexes("feed_reports")}
    if "ix_feed_reports_status_created" not in report_indexes:
        op.create_index("ix_feed_reports_status_created", "feed_reports", ["status", "created_at"])
    if "ix_feed_reports_resource" not in report_indexes:
        op.create_index(
            "ix_feed_reports_resource", "feed_reports", ["resource_type", "resource_id"]
        )


def downgrade() -> None:
    op.drop_index("ix_feed_reports_resource", table_name="feed_reports")
    op.drop_index("ix_feed_reports_status_created", table_name="feed_reports")
    op.drop_table("feed_reports")
    op.drop_column("feed_comments", "author_visibility")
    op.drop_column("feed_posts", "author_visibility")