"""meituan_coupon 首页 feed 分页复合索引(销量最高 / 智能推荐)

「销量最高」「智能推荐」两个 tab 都是
    WHERE city_id = ? [+ 过滤]  →  DISTINCT ON (dedup_key) ORDER BY dedup_key, <排序键> DESC
的形状。列顺序对齐后 Postgres 可以顺着索引流式去重,免掉「每翻一页就把该城全部券重排一遍」,
这是首页下滑到底越来越慢的根因之一(另一半在 app 层:见 api/v1/meituan.py 的 _paged_dedup_ids)。

⚠️ 本文件同时是一个 **merge 迁移**:主干此前有 3 个并行 head
(comparison_user_created_idx / monitoring_audit_rbac / notification_table),
`alembic upgrade head` 会因 multiple heads 报错。这里一并收敛回单 head。

Revision ID: meituan_coupon_feed_indexes
Revises: comparison_user_created_idx, monitoring_audit_rbac, notification_table
Create Date: 2026-07-23 10:00:00.000000

"""
from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

# revision identifiers, used by Alembic.
revision: str = "meituan_coupon_feed_indexes"
down_revision: Union[str, Sequence[str], None] = (
    "comparison_user_created_idx",
    "monitoring_audit_rbac",
    "notification_table",
)
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # 销量最高:WHERE city_id=? AND sale_volume_num IS NOT NULL
    #           ORDER BY dedup_key, sale_volume_num DESC, commission_percent DESC
    op.create_index(
        "ix_meituan_coupon_city_dedup_sales",
        "meituan_coupon",
        ["city_id", "dedup_key", sa.text("sale_volume_num DESC"), sa.text("commission_percent DESC")],
    )
    # 智能推荐:WHERE city_id=? AND commission_percent>=3.0
    #           ORDER BY dedup_key, commission_percent DESC
    op.create_index(
        "ix_meituan_coupon_city_dedup_comm",
        "meituan_coupon",
        ["city_id", "dedup_key", sa.text("commission_percent DESC")],
    )


def downgrade() -> None:
    op.drop_index("ix_meituan_coupon_city_dedup_comm", table_name="meituan_coupon")
    op.drop_index("ix_meituan_coupon_city_dedup_sales", table_name="meituan_coupon")
