\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.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,
    ROW_NUMBER() OVER (PARTITION BY po.user_id, po.group_id ORDER BY po.paid_at DESC, po.id DESC) AS rn_latest
  FROM paid_orders po
), latest_expired_renewals AS (
  SELECT *
  FROM ordered
  WHERE rn_latest = 1
    AND prev_paid_at IS NOT NULL
    AND prev_validity_days > 0
    AND paid_at >= (prev_paid_at + make_interval(days => prev_validity_days))
)
SELECT 'count|' || count(*)
FROM latest_expired_renewals ler
JOIN user_subscriptions s ON s.user_id = ler.user_id AND s.group_id = ler.group_id AND s.deleted_at IS NULL
WHERE s.starts_at < ler.paid_at;
SELECT u.id, u.email, ler.group_id, g.name, s.id, to_char(s.starts_at AT TIME ZONE 'Asia/Shanghai','YYYY-MM-DD HH24:MI:SS'), to_char(ler.paid_at AT TIME ZONE 'Asia/Shanghai','YYYY-MM-DD HH24:MI:SS')
FROM latest_expired_renewals ler
JOIN user_subscriptions s ON s.user_id = ler.user_id AND s.group_id = ler.group_id AND s.deleted_at IS NULL
JOIN users u ON u.id = ler.user_id
JOIN groups g ON g.id = ler.group_id
WHERE s.starts_at < ler.paid_at
ORDER BY ler.paid_at DESC;
