"""notification enhancements

Revision ID: 049_notification_enhancements
Revises: 048_notifications
Create Date: 2026-06-20
"""

import sqlalchemy as sa
from alembic import op


revision = "049_notification_enhancements"
down_revision = "048_notifications"
branch_labels = None
depends_on = None


def _table_names(bind) -> set[str]:
    return set(sa.inspect(bind).get_table_names())


def _column_names(bind, table: str) -> set[str]:
    return {c["name"] for c in sa.inspect(bind).get_columns(table)}


def _index_names(bind, table: str) -> set[str]:
    return {i["name"] for i in sa.inspect(bind).get_indexes(table)}


def upgrade() -> None:
    bind = op.get_bind()
    tables = _table_names(bind)

    if "notifications" in tables:
        cols = _column_names(bind, "notifications")
        indexes = _index_names(bind, "notifications")

        if "level" not in cols:
            op.add_column(
                "notifications",
                sa.Column("level", sa.String(length=16), nullable=False, server_default=sa.text("'info'")),
            )
        if "sender_id" not in cols:
            op.add_column("notifications", sa.Column("sender_id", sa.BigInteger(), nullable=True))
        if "sender_name" not in cols:
            op.add_column("notifications", sa.Column("sender_name", sa.String(length=100), nullable=True))
        if "sender_type" not in cols:
            op.add_column(
                "notifications",
                sa.Column("sender_type", sa.String(length=32), nullable=False, server_default=sa.text("'system'")),
            )
        if "action_label" not in cols:
            op.add_column("notifications", sa.Column("action_label", sa.String(length=40), nullable=True))
        if "action_url" not in cols:
            op.add_column("notifications", sa.Column("action_url", sa.String(length=256), nullable=True))
        if "handled_at" not in cols:
            op.add_column("notifications", sa.Column("handled_at", sa.DateTime(timezone=True), nullable=True))
        if "archived_at" not in cols:
            op.add_column("notifications", sa.Column("archived_at", sa.DateTime(timezone=True), nullable=True))

        if "ix_notifications_level" not in indexes:
            op.create_index("ix_notifications_level", "notifications", ["level"])
        if "ix_notifications_sender_id" not in indexes:
            op.create_index("ix_notifications_sender_id", "notifications", ["sender_id"])
        if "ix_notifications_handled_at" not in indexes:
            op.create_index("ix_notifications_handled_at", "notifications", ["handled_at"])
        if "ix_notifications_archived_at" not in indexes:
            op.create_index("ix_notifications_archived_at", "notifications", ["archived_at"])

    if "notification_preferences" not in tables:
        op.create_table(
            "notification_preferences",
            sa.Column("user_id", sa.BigInteger(), nullable=False),
            sa.Column("category", sa.String(length=24), nullable=False),
            sa.Column("in_app_enabled", sa.Boolean(), nullable=False, server_default=sa.text("1")),
            sa.Column("email_enabled", sa.Boolean(), nullable=False, server_default=sa.text("0")),
            sa.Column("sms_enabled", sa.Boolean(), nullable=False, server_default=sa.text("0")),
            sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
            sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
            sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
            sa.ForeignKeyConstraint(["user_id"], ["users.id"], ondelete="CASCADE"),
            sa.PrimaryKeyConstraint("id"),
            sa.UniqueConstraint("user_id", "category", name="uq_notification_pref_user_category"),
        )
        op.create_index("ix_notification_preferences_user_id", "notification_preferences", ["user_id"])
        op.create_index("ix_notification_preferences_category", "notification_preferences", ["category"])


def downgrade() -> None:
    bind = op.get_bind()
    tables = _table_names(bind)

    if "notification_preferences" in tables:
        op.drop_table("notification_preferences")

    if "notifications" in tables:
        cols = _column_names(bind, "notifications")
        indexes = _index_names(bind, "notifications")
        for name in [
            "ix_notifications_archived_at",
            "ix_notifications_handled_at",
            "ix_notifications_sender_id",
            "ix_notifications_level",
        ]:
            if name in indexes:
                op.drop_index(name, table_name="notifications")
        for name in [
            "archived_at",
            "handled_at",
            "action_url",
            "action_label",
            "sender_type",
            "sender_name",
            "sender_id",
            "level",
        ]:
            if name in cols:
                op.drop_column("notifications", name)
