-- 为换货单补充流程类型和真实业务时间字段
ALTER TABLE tb_exchange_order
    ADD COLUMN IF NOT EXISTS flow_type VARCHAR(20) NOT NULL DEFAULT 'shipping',
    ADD COLUMN IF NOT EXISTS shipped_at TIMESTAMPTZ,
    ADD COLUMN IF NOT EXISTS completed_at TIMESTAMPTZ;

COMMENT ON COLUMN tb_exchange_order.flow_type IS '换货流程类型 shipping-物流换货 direct-直接换货';
COMMENT ON COLUMN tb_exchange_order.shipped_at IS '发货时间，仅物流换货发货成功后写入';
COMMENT ON COLUMN tb_exchange_order.completed_at IS '换货完成时间，物流换货和直接换货完成后写入';

CREATE INDEX IF NOT EXISTS idx_exchange_order_flow_status
    ON tb_exchange_order (flow_type, status)
    WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_exchange_order_new_asset
    ON tb_exchange_order (new_asset_type, new_asset_id)
    WHERE deleted_at IS NULL AND new_asset_id IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_exchange_order_completed_at
    ON tb_exchange_order (completed_at DESC)
    WHERE deleted_at IS NULL AND completed_at IS NOT NULL;