"""add claimable point rewards

Revision ID: 036_point_reward_claims
Revises: 035_agent_tier_names
Create Date: 2026-06-17
"""
import sqlalchemy as sa
from alembic import op


revision = "036_point_reward_claims"
down_revision = "035_agent_tier_names"
branch_labels = None
depends_on = None


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


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


def upgrade() -> None:
    bind = op.get_bind()
    tables = set(sa.inspect(bind).get_table_names())

    if "orders" in tables:
        order_cols = _column_names(bind, "orders")
        if "points_claimed" not in order_cols:
            op.add_column(
                "orders",
                sa.Column("points_claimed", sa.Boolean(), nullable=False, server_default=sa.text("0"), comment="是否已领取该订单的自购返积分"),
            )
        if "points_claimed_at" not in order_cols:
            op.add_column("orders", sa.Column("points_claimed_at", sa.DateTime(timezone=True), nullable=True, comment="首次领取自购返积分时间"))
        if "points_claimed_amount" not in order_cols:
            op.add_column(
                "orders",
                sa.Column("points_claimed_amount", sa.Integer(), nullable=False, server_default=sa.text("0"), comment="已领取自购返积分数量"),
            )

    if "point_rewards" not in tables:
        op.create_table(
            "point_rewards",
            sa.Column("user_id", sa.BigInteger(), nullable=False),
            sa.Column("order_id", sa.Integer(), nullable=False),
            sa.Column("instance_id", sa.Integer(), nullable=True),
            sa.Column("source_type", sa.String(64), nullable=False),
            sa.Column("status", sa.String(24), nullable=False, server_default=sa.text("'pending'")),
            sa.Column("amount_cents", sa.Integer(), nullable=False, comment="Reward base amount in cents"),
            sa.Column("reward_rate", sa.Integer(), nullable=False, comment="Reward rate in basis points"),
            sa.Column("points", sa.Integer(), nullable=False, comment="Reward points"),
            sa.Column("ref_no", sa.String(64), nullable=True),
            sa.Column("point_log_id", sa.Integer(), nullable=True),
            sa.Column("reverse_point_log_id", sa.Integer(), nullable=True),
            sa.Column("claimed_at", sa.DateTime(timezone=True), nullable=True),
            sa.Column("cancelled_at", sa.DateTime(timezone=True), nullable=True),
            sa.Column("reversed_at", sa.DateTime(timezone=True), nullable=True),
            sa.Column("remark", sa.Text(), nullable=True),
            sa.Column("payload_snapshot", sa.JSON(), nullable=True),
            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(["instance_id"], ["instances.id"], ondelete="SET NULL"),
            sa.ForeignKeyConstraint(["order_id"], ["orders.id"], ondelete="CASCADE"),
            sa.ForeignKeyConstraint(["point_log_id"], ["point_logs.id"], ondelete="SET NULL"),
            sa.ForeignKeyConstraint(["reverse_point_log_id"], ["point_logs.id"], ondelete="SET NULL"),
            sa.ForeignKeyConstraint(["user_id"], ["users.id"], ondelete="CASCADE"),
            sa.PrimaryKeyConstraint("id"),
        )
        op.create_index("ix_point_rewards_user_id", "point_rewards", ["user_id"])
        op.create_index("ix_point_rewards_order_id", "point_rewards", ["order_id"])
        op.create_index("ix_point_rewards_instance_id", "point_rewards", ["instance_id"])
        op.create_index("ix_point_rewards_source_type", "point_rewards", ["source_type"])
        op.create_index("ix_point_rewards_status", "point_rewards", ["status"])
        op.create_index("ix_point_rewards_ref_no", "point_rewards", ["ref_no"])
    else:
        indexes = _index_names(bind, "point_rewards")
        for index_name, column in (
            ("ix_point_rewards_user_id", "user_id"),
            ("ix_point_rewards_order_id", "order_id"),
            ("ix_point_rewards_instance_id", "instance_id"),
            ("ix_point_rewards_source_type", "source_type"),
            ("ix_point_rewards_status", "status"),
            ("ix_point_rewards_ref_no", "ref_no"),
        ):
            if index_name not in indexes:
                op.create_index(index_name, "point_rewards", [column])


def downgrade() -> None:
    bind = op.get_bind()
    tables = set(sa.inspect(bind).get_table_names())

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

    if "orders" in tables:
        order_cols = _column_names(bind, "orders")
        if "points_claimed_amount" in order_cols:
            op.drop_column("orders", "points_claimed_amount")
        if "points_claimed_at" in order_cols:
            op.drop_column("orders", "points_claimed_at")
        if "points_claimed" in order_cols:
            op.drop_column("orders", "points_claimed")
