"""cps_group / cps_activity / cps_order tables (CPS 分发与对账)

Revision ID: cps_tables
Revises: b3f1a2c4d5e6
Create Date: 2026-06-16 12:40:00.000000

群发 CPS 优惠券的分发与对账:cps_group(群=sid) + cps_activity(可推广活动池) +
cps_order(美团 query_order 按 sid 拉回的订单明细)。金额统一存「分」。
order_id 全局唯一(reconcile 按它 upsert,订单状态会变则更新)。
"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql


revision: str = "cps_tables"
down_revision: Union[str, Sequence[str], None] = "b3f1a2c4d5e6"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None

_NOW = sa.text("CURRENT_TIMESTAMP")


def upgrade() -> None:
    op.create_table(
        "cps_group",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("sid", sa.String(length=64), nullable=False),
        sa.Column("name", sa.String(length=128), nullable=False),
        sa.Column("member_count", sa.Integer(), nullable=True),
        sa.Column("status", sa.String(length=20), nullable=False, server_default="active"),
        sa.Column("remark", sa.String(length=256), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=_NOW, nullable=False),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("sid", name="uq_cps_group_sid"),
    )
    op.create_index("ix_cps_group_sid", "cps_group", ["sid"])

    op.create_table(
        "cps_activity",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("platform", sa.String(length=20), nullable=False, server_default="meituan"),
        sa.Column("name", sa.String(length=128), nullable=False),
        sa.Column("act_id", sa.String(length=64), nullable=True),
        sa.Column("product_view_sign", sa.String(length=128), nullable=True),
        sa.Column("status", sa.String(length=20), nullable=False, server_default="active"),
        sa.Column("remark", sa.String(length=256), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=_NOW, nullable=False),
        sa.PrimaryKeyConstraint("id"),
    )

    op.create_table(
        "cps_order",
        sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
        sa.Column("order_id", sa.String(length=64), nullable=False),
        sa.Column("sid", sa.String(length=64), nullable=True),
        sa.Column("act_id", sa.String(length=64), nullable=True),
        sa.Column("biz_line", sa.Integer(), nullable=True),
        sa.Column("trade_type", sa.Integer(), nullable=True),
        sa.Column("pay_price_cents", sa.Integer(), nullable=True),
        sa.Column("commission_cents", sa.Integer(), nullable=True),
        sa.Column("commission_rate", sa.String(length=16), nullable=True),
        sa.Column("refund_price_cents", sa.Integer(), nullable=True),
        sa.Column("refund_profit_cents", sa.Integer(), nullable=True),
        sa.Column("mt_status", sa.String(length=8), nullable=True),
        sa.Column("invalid_reason", sa.String(length=128), nullable=True),
        sa.Column("product_name", sa.String(length=512), nullable=True),
        sa.Column("pay_time", sa.DateTime(timezone=True), nullable=True),
        sa.Column("mt_update_time", sa.DateTime(timezone=True), nullable=True),
        sa.Column(
            "raw",
            sa.JSON().with_variant(postgresql.JSONB(), "postgresql"),
            nullable=False,
        ),
        sa.Column("first_seen", sa.DateTime(timezone=True), server_default=_NOW, nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), server_default=_NOW, nullable=False),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("order_id", name="uq_cps_order_order_id"),
    )
    op.create_index("ix_cps_order_order_id", "cps_order", ["order_id"])
    op.create_index("ix_cps_order_sid", "cps_order", ["sid"])
    op.create_index("ix_cps_order_act_id", "cps_order", ["act_id"])
    op.create_index("ix_cps_order_mt_status", "cps_order", ["mt_status"])
    op.create_index("ix_cps_order_pay_time", "cps_order", ["pay_time"])


def downgrade() -> None:
    op.drop_table("cps_order")
    op.drop_table("cps_activity")
    op.drop_table("cps_group")