-- 迁移脚本：为 ODS 表添加"取最新版本"复合索引
-- 支持 DISTINCT ON (pk) ORDER BY pk, fetched_at DESC 查询模式
-- 注意：CREATE INDEX CONCURRENTLY 不能在事务块内执行，需逐条手动运行或用支持单语句模式的工具
-- 日期：2026-02-17
-- 关联需求：ods-dedup-standardize Requirements 6.1, 6.2, 6.3

-- 1. assistant_accounts_master (pk: id)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ods_assistant_accounts_master_latest
    ON ods.assistant_accounts_master (id, fetched_at DESC);

-- 2. settlement_records (pk: id)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ods_settlement_records_latest
    ON ods.settlement_records (id, fetched_at DESC);

-- 3. table_fee_transactions (pk: id)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ods_table_fee_transactions_latest
    ON ods.table_fee_transactions (id, fetched_at DESC);

-- 4. assistant_service_records (pk: id)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ods_assistant_service_records_latest
    ON ods.assistant_service_records (id, fetched_at DESC);

-- 5. assistant_cancellation_records (pk: id)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ods_assistant_cancellation_records_latest
    ON ods.assistant_cancellation_records (id, fetched_at DESC);

-- 6. store_goods_sales_records (pk: id)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ods_store_goods_sales_records_latest
    ON ods.store_goods_sales_records (id, fetched_at DESC);

-- 7. payment_transactions (pk: id)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ods_payment_transactions_latest
    ON ods.payment_transactions (id, fetched_at DESC);

-- 8. refund_transactions (pk: id)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ods_refund_transactions_latest
    ON ods.refund_transactions (id, fetched_at DESC);

-- 9. platform_coupon_redemption_records (pk: id)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ods_platform_coupon_redemption_records_latest
    ON ods.platform_coupon_redemption_records (id, fetched_at DESC);

-- 10. member_profiles (pk: id)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ods_member_profiles_latest
    ON ods.member_profiles (id, fetched_at DESC);

-- 11. member_stored_value_cards (pk: id)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ods_member_stored_value_cards_latest
    ON ods.member_stored_value_cards (id, fetched_at DESC);

-- 12. member_balance_changes (pk: id)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ods_member_balance_changes_latest
    ON ods.member_balance_changes (id, fetched_at DESC);

-- 13. recharge_settlements (pk: id)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ods_recharge_settlements_latest
    ON ods.recharge_settlements (id, fetched_at DESC);

-- 14. group_buy_packages (pk: id)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ods_group_buy_packages_latest
    ON ods.group_buy_packages (id, fetched_at DESC);

-- 15. group_buy_redemption_records (pk: id)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ods_group_buy_redemption_records_latest
    ON ods.group_buy_redemption_records (id, fetched_at DESC);

-- 16. goods_stock_summary (pk: siteGoodsId)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ods_goods_stock_summary_latest
    ON ods.goods_stock_summary (siteGoodsId, fetched_at DESC);

-- 17. goods_stock_movements (pk: siteGoodsStockId)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ods_goods_stock_movements_latest
    ON ods.goods_stock_movements (siteGoodsStockId, fetched_at DESC);

-- 18. site_tables_master (pk: id)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ods_site_tables_master_latest
    ON ods.site_tables_master (id, fetched_at DESC);

-- 19. stock_goods_category_tree (pk: id)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ods_stock_goods_category_tree_latest
    ON ods.stock_goods_category_tree (id, fetched_at DESC);

-- 20. store_goods_master (pk: id)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ods_store_goods_master_latest
    ON ods.store_goods_master (id, fetched_at DESC);

-- 21. table_fee_discount_records (pk: id)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ods_table_fee_discount_records_latest
    ON ods.table_fee_discount_records (id, fetched_at DESC);

-- 22. tenant_goods_master (pk: id)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ods_tenant_goods_master_latest
    ON ods.tenant_goods_master (id, fetched_at DESC);

-- 23. settlement_ticket_details (pk: orderSettleId)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_ods_settlement_ticket_details_latest
    ON ods.settlement_ticket_details (orderSettleId, fetched_at DESC);
