"""mps_notification table

Revision ID: 5d01b163b805
Revises: 27bc1bfc33dc
Create Date: 2026-05-14 09:44:13.684534

Adds the mps_notification table — one row per in-app notification.
Created alongside each transactional email send (offer events, auction
outbid, purchase, sale) so the user has a permanent in-app inbox
that doesn't depend on whether they opened the email.
"""
from alembic import op
import sqlalchemy as sa


revision = "5d01b163b805"
down_revision = "27bc1bfc33dc"
branch_labels = None
depends_on = None


from make_post_sell.models.meta import UUIDType


def _table_exists(name):
    conn = op.get_bind()
    result = conn.execute(
        sa.text("SELECT name FROM sqlite_master WHERE type='table' AND name=:name"),
        {"name": name},
    )
    return result.fetchone() is not None


def upgrade():
    if _table_exists("mps_notification"):
        return
    op.create_table(
        "mps_notification",
        sa.Column("id", UUIDType, primary_key=True, index=True),
        sa.Column("user_id", UUIDType, sa.ForeignKey("mps_user.id"), nullable=False),
        sa.Column("shop_id", UUIDType, sa.ForeignKey("mps_shop.id"), nullable=True),
        sa.Column("kind", sa.Unicode(64), nullable=False),
        sa.Column("subject", sa.Unicode(256), nullable=False),
        sa.Column("body", sa.UnicodeText(), nullable=True),
        sa.Column("link_url", sa.Unicode(512), nullable=True),
        sa.Column(
            "offer_id", UUIDType, sa.ForeignKey("mps_offer.id"), nullable=True,
        ),
        sa.Column(
            "auction_id", UUIDType, sa.ForeignKey("mps_auction.id"), nullable=True,
        ),
        sa.Column(
            "invoice_id", UUIDType, sa.ForeignKey("mps_invoice.id"), nullable=True,
        ),
        sa.Column("created_timestamp", sa.BigInteger(), nullable=False),
        sa.Column("updated_timestamp", sa.BigInteger(), nullable=False),
        sa.Column(
            "read",
            sa.Boolean(),
            nullable=False,
            server_default="0",
        ),
        sa.Column("read_timestamp", sa.BigInteger(), nullable=True),
    )
    op.create_index(
        "ix_mps_notification_user_id",
        "mps_notification",
        ["user_id"],
    )
    op.create_index(
        "ix_mps_notification_user_read_created",
        "mps_notification",
        ["user_id", "read", "created_timestamp"],
    )


def downgrade():
    if not _table_exists("mps_notification"):
        return
    op.drop_index(
        "ix_mps_notification_user_read_created",
        table_name="mps_notification",
    )
    op.drop_index("ix_mps_notification_user_id", table_name="mps_notification")
    op.drop_table("mps_notification")
