"""「公司还没有这个流程，我想要一个」 — the front door, and the loop back.

The register publishes what exists (553) and asks its owners to stand
behind it twice a year (556). This is how something gets to exist. Until
now an employee who needed a new approval had nowhere to ask: the Feishu
console belongs to IT, so the request lived in a chat message nobody
could track.

Four columns carry the design:

* ``purpose`` is NOT NULL and validated to a real sentence. IT decides
  from it, and it becomes the flow's published description — the answer
  to "when do I use this one" that the next employee reads.
* ``proposed_owner_person_id`` defaults to the requester. The person who
  needed the flow is usually the person who knows it, and a flow that
  arrives already owned never enters the ownerless queue.
* ``status`` treats ``approved`` as OPEN and only ``registered`` as
  done. An approved request nobody built looks finished from IT's side
  and is an employee still waiting from theirs; collapsing the two is
  how a request goes quiet for a fortnight.
* ``registered_flow_id`` is the loop closing — the request points at the
  real registry row it produced.

**No Feishu approval template of its own, deliberately.** The other 18
flows go through Feishu because their approvers are the business; this
one's approver is IT, and what IT does next is manual work in the Feishu
console. Routing it through Feishu would mean creating one more approval
template in order to be allowed to ask for approval templates.

**And Helix never creates the definition itself**, although the API
would allow it: a definition created through the API can never be
deactivated or deleted — Feishu documents this, recommends self-built
apps not use it, and this tenant already carries two undeletable probe
definitions. Taking away an owner's ability to stop their own flow later
is not a trade worth one click.

Revision ID: 557
Revises: 556
"""
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 = "557"
down_revision = "556"
branch_labels = None
depends_on = None

TABLE = "registry_approval_flow_requests"


def upgrade() -> None:
    conn = op.get_bind()
    if sa.inspect(conn).has_table(TABLE):
        return

    op.create_table(
        TABLE,
        sa.Column("id", id_type_for("identity_persons"), primary_key=True),
        sa.Column(
            "requester_person_id", id_type_for("identity_persons"),
            nullable=False,
        ),
        # A suggestion. IT names it in the console, and the definition
        # sync takes the real name from Feishu afterwards.
        sa.Column("proposed_name", sa.String(255), nullable=False),
        # Required: it is what IT decides from AND what gets published
        # as the flow's description.
        sa.Column("purpose", sa.Text(), nullable=False),
        sa.Column("proposed_category", sa.String(128), nullable=True),
        sa.Column(
            "proposed_owner_person_id", id_type_for("identity_persons"),
            nullable=True,
        ),
        # pending | approved | registered | rejected | cancelled
        sa.Column(
            "status", sa.String(16), nullable=False, server_default="pending",
        ),
        sa.Column("decided_by", id_type_for("identity_persons"), nullable=True),
        sa.Column("decided_at", sa.DateTime(timezone=True), nullable=True),
        # Required on a rejection at the service layer: 「不批」 without a
        # reason sends the employee back to guessing, and the guess is
        # usually the same request a week later.
        sa.Column("decision_note", sa.Text(), nullable=True),
        sa.Column(
            "registered_flow_id", id_type_for("identity_persons"), nullable=True,
        ),
        sa.Column("registered_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column(
            "created_at", sa.DateTime(timezone=True), nullable=False,
            server_default=sa.text("CURRENT_TIMESTAMP"),
        ),
        sa.Column(
            "updated_at", sa.DateTime(timezone=True), nullable=False,
            server_default=sa.text("CURRENT_TIMESTAMP"),
            onupdate=sa.text("CURRENT_TIMESTAMP"),
        ),
        sa.ForeignKeyConstraint(
            ["requester_person_id"], ["identity_persons.id"], ondelete="CASCADE",
        ),
        sa.ForeignKeyConstraint(
            ["proposed_owner_person_id"], ["identity_persons.id"],
            ondelete="SET NULL",
        ),
        sa.ForeignKeyConstraint(
            ["decided_by"], ["identity_persons.id"], ondelete="SET NULL",
        ),
        # SET NULL rather than CASCADE: if the flow is later archived and
        # hard-removed, the request is still a record of what was asked
        # and answered.
        sa.ForeignKeyConstraint(
            ["registered_flow_id"], ["registry_approval_flows.id"],
            ondelete="SET NULL",
        ),
        **table_kw_for("identity_persons"),
    )
    op.create_index(
        "ix_registry_flow_requests_requester", TABLE,
        ["requester_person_id", "status"],
    )
    op.create_index(
        "ix_registry_flow_requests_status", TABLE, ["status", "created_at"],
    )


def downgrade() -> None:
    conn = op.get_bind()
    if sa.inspect(conn).has_table(TABLE):
        op.drop_table(TABLE)