"""announcement_requests — Company-wide Announcement Request flow.

The Feishu template (flow_key=announcement_request, external_code
configured per-integration) asks for one field — Application
description (paragraph). On approval IT publishes the announcement
in the company channel; there's no ongoing privilege to revoke, so
the lifecycle ends at approved / rejected / cancelled.

Revision ID: 106
"""
from __future__ import annotations

import sqlalchemy as sa
from alembic import op


revision = "106"
down_revision = "105"
branch_labels = None
depends_on = None


def _fk_source_col_type(bind, target_table: str, target_column: str, *, length: int = 36):
    """See migrations 098 / 101 / 102 / 103 — mirror the FK target's
    DATA_TYPE + COLLATION so TiDB / MySQL 8 don't reject the
    constraint with error 3780."""
    row = bind.execute(
        sa.text(
            "SELECT DATA_TYPE, COLLATION_NAME FROM "
            "information_schema.COLUMNS "
            "WHERE TABLE_SCHEMA = DATABASE() "
            "  AND TABLE_NAME = :t AND COLUMN_NAME = :c"
        ),
        {"t": target_table, "c": target_column},
    ).fetchone()
    data_type = (row[0] or "char").lower() if row else "char"
    collation = row[1] if row else None
    type_cls = sa.CHAR if data_type == "char" else sa.String
    return type_cls(length, collation=collation) if collation else type_cls(length)


def upgrade() -> None:
    bind = op.get_bind()
    persons_id = _fk_source_col_type(bind, "identity_persons", "id")
    approval_requests_id = _fk_source_col_type(bind, "approval_requests", "id")
    op.create_table(
        "announcement_requests",
        sa.Column("id", sa.String(36), primary_key=True),
        sa.Column("person_id", persons_id, nullable=False),
        sa.Column("description", sa.Text, nullable=False),
        # State machine: pending → approved / rejected / cancelled.
        # No revoke — the announcement is one-shot once published.
        sa.Column("status", sa.String(32), nullable=False, server_default="pending"),
        sa.Column("feishu_instance_code", sa.String(64), nullable=True),
        sa.Column("approval_request_id", approval_requests_id, nullable=True),
        sa.Column("granted_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column(
            "created_at", sa.DateTime(timezone=True),
            server_default=sa.func.now(), nullable=False,
        ),
        sa.Column(
            "updated_at", sa.DateTime(timezone=True),
            server_default=sa.func.now(), nullable=False,
        ),
        sa.ForeignKeyConstraint(
            ["person_id"], ["identity_persons.id"],
            name="fk_announcement_requests_person",
        ),
        sa.ForeignKeyConstraint(
            ["approval_request_id"], ["approval_requests.id"],
            name="fk_announcement_requests_approval_request",
            ondelete="SET NULL",
        ),
        sa.Index("ix_announcement_requests_person", "person_id", "status"),
        sa.Index(
            "ix_announcement_requests_feishu_instance", "feishu_instance_code",
            unique=True,
            mysql_length={"feishu_instance_code": 64},
        ),
    )


def downgrade() -> None:
    op.drop_table("announcement_requests")