"""add per-session coupon claim event table

Revision ID: coupon_claim_event
Revises: 8e04cc13a211
Create Date: 2026-07-23
"""

from collections.abc import Sequence

import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

from alembic import op

revision: str = "coupon_claim_event"
down_revision: str | Sequence[str] | None = "8e04cc13a211"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None

_JSON = sa.JSON().with_variant(postgresql.JSONB(), "postgresql")


def upgrade() -> None:
    op.create_table(
        "coupon_claim_event",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("trace_id", sa.String(length=64), nullable=False),
        sa.Column("device_id", sa.String(length=64), nullable=False),
        sa.Column("user_id", sa.Integer(), nullable=True),
        sa.Column("coupon_id", sa.String(length=64), nullable=False),
        sa.Column("claim_date", sa.Date(), nullable=False),
        sa.Column("status", sa.String(length=24), nullable=False),
        sa.Column("app_env", sa.String(length=16), nullable=True),
        sa.Column("vendor", sa.String(length=48), nullable=True),
        sa.Column("coupon_name", sa.String(length=128), nullable=True),
        sa.Column("claimed_count", sa.Integer(), nullable=True),
        sa.Column("reason", sa.String(length=255), nullable=True),
        sa.Column("extra", _JSON, nullable=True),
        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.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint(
            "trace_id", "coupon_id",
            name="uq_coupon_claim_event_trace_coupon",
        ),
    )
    op.create_index(
        "ix_coupon_claim_event_date_env",
        "coupon_claim_event",
        ["claim_date", "app_env"],
        unique=False,
    )
    op.create_index(
        op.f("ix_coupon_claim_event_app_env"),
        "coupon_claim_event",
        ["app_env"],
        unique=False,
    )
    op.create_index(
        op.f("ix_coupon_claim_event_trace_id"),
        "coupon_claim_event",
        ["trace_id"],
        unique=False,
    )
    op.create_index(
        op.f("ix_coupon_claim_event_user_id"),
        "coupon_claim_event",
        ["user_id"],
        unique=False,
    )

    # 旧表按 (device, coupon, 自然日) 去重，trace_id 可空且不在唯一键里：同一
    # (trace_id, coupon_id) 可能散落在多行（如一次会话的 /step 帧跨零点，把同一张券
    # 写进相邻两天）。新表按 (trace_id, coupon_id) 唯一，整表 1:1 复制会撞
    # uq_coupon_claim_event_trace_coupon。回填时按 (trace_id, coupon_id) 只取 id 最大
    # （最近写入）的一行。历史上已被每日去重覆盖的关联仍无法恢复。
    op.execute(
        """
        INSERT INTO coupon_claim_event (
            trace_id, device_id, user_id, coupon_id, claim_date, status, app_env,
            vendor, coupon_name, claimed_count, reason, extra, created_at, updated_at
        )
        SELECT
            trace_id, device_id, user_id, coupon_id, claim_date, status, app_env,
            vendor, coupon_name, claimed_count, reason, extra, created_at, updated_at
        FROM coupon_claim_record
        WHERE trace_id IS NOT NULL
          AND id IN (
              SELECT MAX(id)
              FROM coupon_claim_record
              WHERE trace_id IS NOT NULL
              GROUP BY trace_id, coupon_id
          )
        """
    )


def downgrade() -> None:
    op.drop_index(op.f("ix_coupon_claim_event_user_id"), table_name="coupon_claim_event")
    op.drop_index(op.f("ix_coupon_claim_event_trace_id"), table_name="coupon_claim_event")
    op.drop_index(op.f("ix_coupon_claim_event_app_env"), table_name="coupon_claim_event")
    op.drop_index("ix_coupon_claim_event_date_env", table_name="coupon_claim_event")
    op.drop_table("coupon_claim_event")
