\pset tuples_only on
\pset format unaligned
WITH paid_orders AS (
  SELECT
    p.id,
    p.user_id,
    p.group_id,
    p.out_trade_no,
    p.amount_fen,
    COALESCE(NULLIF(p.validity_days, 0), g.default_validity_days) AS validity_days,
    COALESCE(p.notify_at, p.created_at) AS paid_at
  FROM payment_orders p
  LEFT JOIN groups g ON g.id = p.group_id
  WHERE p.status = 'paid'
    AND COALESCE(p.biz_type, 'subscription') = 'subscription'
    AND p.group_id IS NOT NULL
), ordered AS (
  SELECT
    po.*,
    LAG(po.id) OVER (PARTITION BY po.user_id, po.group_id ORDER BY po.paid_at, po.id) AS prev_order_id,
    LAG(po.amount_fen) OVER (PARTITION BY po.user_id, po.group_id ORDER BY po.paid_at, po.id) AS prev_amount_fen,
    LAG(po.paid_at) OVER (PARTITION BY po.user_id, po.group_id ORDER BY po.paid_at, po.id) AS prev_paid_at,
    LAG(po.validity_days) OVER (PARTITION BY po.user_id, po.group_id ORDER BY po.paid_at, po.id) AS prev_validity_days
  FROM paid_orders po
)
SELECT o.user_id, u.email, o.group_id, g.name,
       o.prev_order_id, o.prev_amount_fen, o.prev_validity_days,
       o.id, o.amount_fen, o.validity_days,
       to_char(o.prev_paid_at AT TIME ZONE 'Asia/Shanghai','YYYY-MM-DD HH24:MI:SS'),
       to_char(o.paid_at AT TIME ZONE 'Asia/Shanghai','YYYY-MM-DD HH24:MI:SS')
FROM ordered o
JOIN users u ON u.id = o.user_id
JOIN groups g ON g.id = o.group_id
WHERE o.prev_paid_at IS NOT NULL
  AND o.prev_validity_days IS NOT NULL
  AND o.paid_at >= (o.prev_paid_at + make_interval(days => o.prev_validity_days))
ORDER BY o.paid_at DESC;
