"""Email-group join requests (in-Helix owner approval).

Revision ID: 397
Revises: 396
Create Date: 2026-07-13

Backs the directory's "点『申请加入』,走审批;负责人通过后你就成为成员"
promise. A member submits a join request; the group's owner decides it
inside Helix (not a Feishu template — owners are arbitrary employees);
on approval the applicant is added to the Google group.

``open_request_key`` is a generated column that is non-NULL only while a
row is ``pending`` (``CONCAT(group_id,'|',requester_id)``); a UNIQUE index
on it enforces at-most-one open request per (group, requester) without a
partial index (unsupported on MySQL/TiDB), while still allowing a re-apply
after a terminal decision. Mirrors the DAX ``active_semantic_key`` trick.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op
from app.core.migration_helpers import (
    id_type_for,
    normalize_fk_column_collation,
    table_kw_for,
)

revision = "397"
down_revision = "396"
branch_labels = None
depends_on = None

TABLE = "email_group_join_requests"


def _table_exists(bind, table: str) -> bool:
    return bool(bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.tables "
            "WHERE table_schema = DATABASE() AND table_name = :t LIMIT 1",
        ),
        {"t": table},
    ).scalar())


def upgrade() -> None:
    bind = op.get_bind()
    if _table_exists(bind, TABLE):
        return

    row_id = id_type_for("email_groups")
    group_id = id_type_for("email_groups")
    person_id = id_type_for("identity_persons")
    kw = table_kw_for("identity_persons")

    op.create_table(
        TABLE,
        sa.Column("id", row_id, primary_key=True),
        sa.Column("email_group_id", group_id, nullable=False),
        sa.Column("requester_id", person_id, nullable=False),
        sa.Column("reason", sa.String(500), nullable=True),
        sa.Column(
            "status", sa.String(16), nullable=False, server_default="pending",
        ),
        sa.Column("decided_by_id", person_id, nullable=True),
        sa.Column("decided_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("decision_note", sa.String(500), nullable=True),
        sa.Column(
            "open_request_key",
            sa.String(80),
            sa.Computed(
                "CASE WHEN status = 'pending' THEN "
                "CONCAT(email_group_id, '|', requester_id) ELSE NULL END",
            ),
            nullable=True,
        ),
        sa.Column(
            "created_at", sa.DateTime(timezone=True), nullable=False,
            server_default=sa.func.now(),
        ),
        sa.Column(
            "updated_at", sa.DateTime(timezone=True), nullable=False,
            server_default=sa.func.now(),
        ),
        **kw,
    )
    for column, parent, nullable in (
        ("email_group_id", "email_groups", False),
        ("requester_id", "identity_persons", False),
        ("decided_by_id", "identity_persons", True),
    ):
        normalize_fk_column_collation(TABLE, column, parent, nullable=nullable)
    op.create_foreign_key(
        "fk_email_group_join_requests_group",
        TABLE,
        "email_groups",
        ["email_group_id"],
        ["id"],
        ondelete="CASCADE",
    )
    op.create_foreign_key(
        "fk_email_group_join_requests_requester",
        TABLE,
        "identity_persons",
        ["requester_id"],
        ["id"],
        ondelete="CASCADE",
    )
    op.create_foreign_key(
        "fk_email_group_join_requests_decided_by",
        TABLE,
        "identity_persons",
        ["decided_by_id"],
        ["id"],
        ondelete="SET NULL",
    )
    op.create_index(
        "ix_email_group_join_requests_group_status",
        TABLE,
        ["email_group_id", "status"],
    )
    op.create_index(
        "ix_email_group_join_requests_requester",
        TABLE,
        ["requester_id"],
    )
    op.create_index(
        "uq_email_group_join_requests_open",
        TABLE,
        ["open_request_key"],
        unique=True,
    )


def downgrade() -> None:
    op.drop_table(TABLE)