"""notification_preferences — per-person control over the notification firehose.

Revision ID: 300
Revises: 299
Create Date: 2026-06-17

Backs the notification-governance feature (PR1 of the employee↔IT
communication theme): per-category × per-channel muting plus a daily
quiet-hours window. One sparse row per person (PK = person id); a missing
row means "all defaults". The person FK is declared with the parent's
charset/collation (``id_type_for`` / ``table_kw_for``) so the inline FK to
``identity_persons.id`` can't trip error 3780 on prod TiDB.
"""
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 = "300"
down_revision = "299"
branch_labels = None
depends_on = None

TABLE = "notification_preferences"


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()
    if _table_exists(bind, TABLE):
        return
    persons_id = id_type_for("identity_persons")
    kw = table_kw_for("identity_persons")
    op.create_table(
        TABLE,
        sa.Column("person_id", persons_id, primary_key=True),
        sa.Column("muted_channels", sa.JSON(), nullable=False),
        sa.Column(
            "quiet_hours_enabled", sa.Boolean(),
            nullable=False, server_default=sa.text("0"),
        ),
        sa.Column("quiet_hours_start", sa.String(5), nullable=True),
        sa.Column("quiet_hours_end", sa.String(5), 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.ForeignKeyConstraint(
            ["person_id"], ["identity_persons.id"],
            name="fk_notification_preferences_person",
        ),
        **kw,
    )


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