"""backfill claimable rewards for existing active servers

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


revision = "037_backfill_rewards"
down_revision = "036_point_reward_claims"
branch_labels = None
depends_on = None


def upgrade() -> None:
    bind = op.get_bind()
    tables = set(sa.inspect(bind).get_table_names())
    required = {
        "instances",
        "orders",
        "point_rewards",
        "point_logs",
        "user_memberships",
        "member_levels",
    }
    if not required.issubset(tables):
        return

    bind.execute(
        sa.text(
            """
            INSERT INTO point_rewards
                (user_id, order_id, instance_id, source_type, status, amount_cents,
                 reward_rate, points, ref_no, remark, payload_snapshot, created_at, updated_at)
            SELECT
                i.user_id,
                o.id,
                i.id,
                'server_purchase',
                'claimable',
                FLOOR(GREATEST(o.total_amount - COALESCE(o.discount_amount, 0), 0) / oc.instance_count),
                ml.points_cashback_rate,
                FLOOR(
                    FLOOR(GREATEST(o.total_amount - COALESCE(o.discount_amount, 0), 0) / oc.instance_count)
                    * ml.points_cashback_rate / 10000
                ) * 10,
                o.out_order_no,
                CONCAT(
                    '旧服务器积分回填，比例',
                    FORMAT(ml.points_cashback_rate / 100, 1),
                    '%'
                ),
                JSON_OBJECT('instance_id', i.id, 'backfill', true),
                NOW(),
                NOW()
            FROM instances i
            JOIN orders o ON o.id = i.order_id
            JOIN (
                SELECT order_id, COUNT(*) AS instance_count
                FROM instances
                GROUP BY order_id
            ) oc ON oc.order_id = i.order_id
            JOIN user_memberships um ON um.user_id = i.user_id
            JOIN member_levels ml ON ml.id = um.member_level_id
            LEFT JOIN point_rewards pr ON pr.instance_id = i.id
            LEFT JOIN point_logs pl ON pl.order_id = o.id AND pl.change_type = 'order_reward'
            WHERE i.status IN ('active', 'suspended')
              AND pr.id IS NULL
              AND pl.id IS NULL
              AND ml.points_cashback_rate > 0
              AND FLOOR(GREATEST(o.total_amount - COALESCE(o.discount_amount, 0), 0) / oc.instance_count) > 0
              AND FLOOR(
                    FLOOR(GREATEST(o.total_amount - COALESCE(o.discount_amount, 0), 0) / oc.instance_count)
                    * ml.points_cashback_rate / 10000
                  ) * 10 > 0
            """
        )
    )


def downgrade() -> None:
    bind = op.get_bind()
    tables = set(sa.inspect(bind).get_table_names())
    if "point_rewards" not in tables:
        return

    bind.execute(
        sa.text(
            """
            DELETE FROM point_rewards
            WHERE source_type = 'server_purchase'
              AND JSON_EXTRACT(payload_snapshot, '$.backfill') = true
              AND status = 'claimable'
              AND point_log_id IS NULL
            """
        )
    )
