"""Owner triage on cloud efficiency recommendations.

Revision ID: 422
Revises: 421
Create Date: 2026-07-20

Why this exists
---------------
The cloud efficiency recommendations (``cmdb_server_efficiency``) were a
read-only advisory list: an admin saw every idle/oversized box in one
place and could not realistically process them, and a resource owner saw
their own box flagged with no way to respond. This table adds the
"让大家都参与" participation layer — a lightweight, NON-DESTRUCTIVE owner
decision on a server's current recommendation so the item can leave the
"待处理" queue:

``cmdb_server_efficiency_triage``
    One row per server. ``status`` ∈ (kept / acknowledged / snoozed).
    ``verdict_at_decision`` pins the decision to the verdict the owner
    was looking at; the read side treats the triage as stale (→ back to
    ``open``) once the daily recompute drifts the verdict away, so a
    "keep this idle box" decision never silently suppresses a materially
    different future finding. Kept in its OWN table (not columns on
    ``cmdb_server_efficiency``) so it survives the daily overwrite of the
    efficiency row and keeps an independent audit trail.

This table records intent only — it NEVER executes a cloud action
(stop / decommission / downsize stay in the existing lifecycle surface).

Idempotency
-----------
``CREATE TABLE IF NOT EXISTS`` semantics via ``_table_exists``.
"""
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 = "422"
down_revision = "421"
branch_labels = None
depends_on = None


TRIAGE_TABLE = "cmdb_server_efficiency_triage"


def _table_exists(bind, name: str) -> bool:
    return bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.TABLES "
            "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :n"
        ),
        {"n": name},
    ).scalar() is not None


def upgrade() -> None:
    bind = op.get_bind()
    server_id_t = id_type_for("cmdb_servers")
    person_id_t = id_type_for("identity_persons")
    kw = table_kw_for("cmdb_servers")

    if _table_exists(bind, TRIAGE_TABLE):
        return

    op.create_table(
        TRIAGE_TABLE,
        sa.Column("id", server_id_t, primary_key=True),
        sa.Column("server_id", server_id_t, nullable=False),
        sa.Column(
            "status", sa.String(16), nullable=False,
            server_default="acknowledged",
        ),
        sa.Column("reason", sa.Text(), nullable=True),
        sa.Column("verdict_at_decision", sa.String(32), nullable=True),
        sa.Column("decided_by_person_id", person_id_t, nullable=True),
        sa.Column(
            "decided_at", sa.DateTime(timezone=True), nullable=False,
            server_default=sa.func.now(),
        ),
        sa.Column("snooze_until", sa.DateTime(timezone=True), 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(),
        ),
        sa.UniqueConstraint("server_id", name="uq_server_efficiency_triage"),
        **kw,
    )
    normalize_fk_column_collation(
        TRIAGE_TABLE, "server_id", "cmdb_servers", nullable=False,
    )
    op.create_foreign_key(
        "fk_server_efficiency_triage_server",
        TRIAGE_TABLE, "cmdb_servers",
        ["server_id"], ["id"], ondelete="CASCADE",
    )
    normalize_fk_column_collation(
        TRIAGE_TABLE, "decided_by_person_id", "identity_persons",
        nullable=True,
    )
    op.create_foreign_key(
        "fk_server_efficiency_triage_person",
        TRIAGE_TABLE, "identity_persons",
        ["decided_by_person_id"], ["id"], ondelete="SET NULL",
    )


def downgrade() -> None:
    bind = op.get_bind()
    if _table_exists(bind, TRIAGE_TABLE):
        op.drop_table(TRIAGE_TABLE)