"""
Migrazione 0018: Livello di Rischio e Riservatezza per Comunicazione (Feature N3).

Aggiunge alla tabella `messages`:
  - risk_level     ENUM(low, medium, high, critical)   nullable
  - confidentiality ENUM(public, internal, confidential, secret) nullable

Entrambi i campi sono nullable: rimangono NULL finche' non vengono
impostati manualmente o da una regola di smistamento (set_risk_level /
set_confidentiality).
"""

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

revision = "0018"
down_revision = "0017"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # ── Crea i nuovi ENUM types ─────────────────────────────────────────────────
    risk_level_enum = postgresql.ENUM(
        "low", "medium", "high", "critical",
        name="risk_level",
        create_type=True,
    )
    confidentiality_enum = postgresql.ENUM(
        "public", "internal", "confidential", "secret",
        name="confidentiality_level",
        create_type=True,
    )
    risk_level_enum.create(op.get_bind(), checkfirst=True)
    confidentiality_enum.create(op.get_bind(), checkfirst=True)

    # ── Aggiunge le colonne a messages ──────────────────────────────────────────
    op.add_column(
        "messages",
        sa.Column(
            "risk_level",
            sa.Enum("low", "medium", "high", "critical", name="risk_level", create_type=False),
            nullable=True,
        ),
    )
    op.add_column(
        "messages",
        sa.Column(
            "confidentiality",
            sa.Enum("public", "internal", "confidential", "secret", name="confidentiality_level", create_type=False),
            nullable=True,
        ),
    )

    # ── Indici per filtrare rapidamente per rischio/riservatezza ────────────────
    op.create_index(
        "idx_messages_risk_level",
        "messages",
        ["tenant_id", "risk_level"],
        postgresql_where=sa.text("risk_level IS NOT NULL"),
    )
    op.create_index(
        "idx_messages_confidentiality",
        "messages",
        ["tenant_id", "confidentiality"],
        postgresql_where=sa.text("confidentiality IS NOT NULL"),
    )


def downgrade() -> None:
    op.drop_index("idx_messages_confidentiality", table_name="messages")
    op.drop_index("idx_messages_risk_level", table_name="messages")
    op.drop_column("messages", "confidentiality")
    op.drop_column("messages", "risk_level")

    risk_level_enum = postgresql.ENUM(name="risk_level")
    confidentiality_enum = postgresql.ENUM(name="confidentiality_level")
    risk_level_enum.drop(op.get_bind(), checkfirst=True)
    confidentiality_enum.drop(op.get_bind(), checkfirst=True)
