"""add repair public links and notifications

Revision ID: d4f6a2b8c901
Revises: c8e2f1a9b704
Create Date: 2026-07-04 12:00:00.000000

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa


revision: str = "d4f6a2b8c901"
down_revision: Union[str, Sequence[str], None] = "c8e2f1a9b704"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


PUBLIC_LINK_PERMISSION = (
    "repairs.public_link.manage",
    "Reparatur-Statuslinks verwalten",
    "Öffentliche Statuslinks für Reparaturen verwalten",
    "repairs",
)

ROLE_PERMISSIONS = {
    "administrator": ["repairs.public_link.manage"],
    "management": ["repairs.public_link.manage"],
    "support": ["repairs.public_link.manage"],
}


def upgrade() -> None:
    op.create_table(
        "repair_public_access_tokens",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("repair_id", sa.Integer(), nullable=False),
        sa.Column("token_hash", sa.String(length=64), nullable=False),
        sa.Column("token_hint", sa.String(length=16), nullable=True),
        sa.Column("is_active", sa.Boolean(), server_default="true", nullable=False),
        sa.Column("expires_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("last_used_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.Column("revoked_at", sa.DateTime(timezone=True), nullable=True),
        sa.ForeignKeyConstraint(["repair_id"], ["repairs.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("token_hash"),
    )
    op.create_index(op.f("ix_repair_public_access_tokens_is_active"), "repair_public_access_tokens", ["is_active"], unique=False)
    op.create_index(op.f("ix_repair_public_access_tokens_repair_id"), "repair_public_access_tokens", ["repair_id"], unique=False)
    op.create_index(op.f("ix_repair_public_access_tokens_token_hash"), "repair_public_access_tokens", ["token_hash"], unique=True)

    op.create_table(
        "repair_notification_events",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("repair_id", sa.Integer(), nullable=False),
        sa.Column("event_type", sa.String(length=80), nullable=False),
        sa.Column("channel", sa.String(length=40), nullable=False),
        sa.Column("recipient", sa.String(length=255), server_default="", nullable=False),
        sa.Column("subject", sa.String(length=255), nullable=False),
        sa.Column("status", sa.String(length=40), nullable=False),
        sa.Column("error_message", sa.Text(), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.Column("sent_at", sa.DateTime(timezone=True), nullable=True),
        sa.CheckConstraint("status in ('pending','sent','failed','skipped')", name="ck_repair_notification_events_status"),
        sa.ForeignKeyConstraint(["repair_id"], ["repairs.id"], ondelete="CASCADE"),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index(op.f("ix_repair_notification_events_channel"), "repair_notification_events", ["channel"], unique=False)
    op.create_index(op.f("ix_repair_notification_events_event_type"), "repair_notification_events", ["event_type"], unique=False)
    op.create_index(op.f("ix_repair_notification_events_repair_id"), "repair_notification_events", ["repair_id"], unique=False)
    op.create_index(op.f("ix_repair_notification_events_status"), "repair_notification_events", ["status"], unique=False)

    name, display_name, description, module = PUBLIC_LINK_PERMISSION
    op.execute(
        sa.text(
            """
            insert into permissions (name, display_name, description, module)
            values (:name, :display_name, :description, :module)
            on conflict (name) do update set
                display_name = excluded.display_name,
                description = excluded.description,
                module = excluded.module
            """
        ).bindparams(name=name, display_name=display_name, description=description, module=module)
    )

    for role_name, permissions in ROLE_PERMISSIONS.items():
        for permission_name in permissions:
            op.execute(
                sa.text(
                    """
                    insert into role_permissions (role_id, permission_id)
                    select roles.id, permissions.id
                    from roles, permissions
                    where roles.name = :role_name and permissions.name = :permission_name
                    on conflict do nothing
                    """
                ).bindparams(role_name=role_name, permission_name=permission_name)
            )


def downgrade() -> None:
    op.execute(
        sa.text(
            """
            delete from role_permissions
            using permissions
            where role_permissions.permission_id = permissions.id
              and permissions.name = :permission_name
            """
        ).bindparams(permission_name=PUBLIC_LINK_PERMISSION[0])
    )
    op.execute(sa.text("delete from permissions where name = :name").bindparams(name=PUBLIC_LINK_PERMISSION[0]))

    op.drop_index(op.f("ix_repair_notification_events_status"), table_name="repair_notification_events")
    op.drop_index(op.f("ix_repair_notification_events_repair_id"), table_name="repair_notification_events")
    op.drop_index(op.f("ix_repair_notification_events_event_type"), table_name="repair_notification_events")
    op.drop_index(op.f("ix_repair_notification_events_channel"), table_name="repair_notification_events")
    op.drop_table("repair_notification_events")

    op.drop_index(op.f("ix_repair_public_access_tokens_token_hash"), table_name="repair_public_access_tokens")
    op.drop_index(op.f("ix_repair_public_access_tokens_repair_id"), table_name="repair_public_access_tokens")
    op.drop_index(op.f("ix_repair_public_access_tokens_is_active"), table_name="repair_public_access_tokens")
    op.drop_table("repair_public_access_tokens")
