-- 添加订单过期时间字段和复合索引
DO $$
BEGIN
    -- 添加 expires_at 字段（订单过期时间，NULL 表示不过期）
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_name='tb_order'
        AND column_name='expires_at'
    ) THEN
        ALTER TABLE tb_order ADD COLUMN expires_at TIMESTAMPTZ;
        COMMENT ON COLUMN tb_order.expires_at IS '订单过期时间（NULL表示不过期，待支付订单默认30分钟后过期）';
    END IF;

    -- 创建复合索引：用于定时任务扫描超时待支付订单
    IF NOT EXISTS (
        SELECT 1 FROM pg_indexes
        WHERE tablename='tb_order'
        AND indexname='idx_order_expires'
    ) THEN
        CREATE INDEX idx_order_expires ON tb_order (expires_at, payment_status)
        WHERE expires_at IS NOT NULL AND deleted_at IS NULL;
    END IF;
END
$$;