\pset tuples_only on
\pset format unaligned
DROP TABLE IF EXISTS tmp_latest_expired_renewals;
CREATE TEMP TABLE tmp_latest_expired_renewals AS
WITH paid_orders AS (
  SELECT
    p.id,
    p.user_id,
    p.group_id,
    p.out_trade_no,
    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
), expired_renewals AS (
  SELECT *, (prev_paid_at + make_interval(days => prev_validity_days)) AS prev_expected_expires_at
  FROM ordered
  WHERE prev_paid_at IS NOT NULL
    AND prev_validity_days IS NOT NULL
    AND paid_at >= (prev_paid_at + make_interval(days => prev_validity_days))
)
SELECT * FROM expired_renewals WHERE rn_latest = 1;

DROP TABLE IF EXISTS tmp_all_expired_renewals;
CREATE TEMP TABLE tmp_all_expired_renewals AS
WITH paid_orders AS (
  SELECT
    p.id,
    p.user_id,
    p.group_id,
    p.out_trade_no,
    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
)
SELECT *, (prev_paid_at + make_interval(days => prev_validity_days)) AS prev_expected_expires_at
FROM ordered
WHERE prev_paid_at IS NOT NULL
  AND prev_validity_days IS NOT NULL
  AND paid_at >= (prev_paid_at + make_interval(days => prev_validity_days));

SELECT 'expired_renewal_events|' || count(*) FROM tmp_all_expired_renewals;
SELECT 'expired_renewal_user_groups|' || count(*) FROM tmp_latest_expired_renewals;
SELECT 'expired_renewal_users|' || count(DISTINCT user_id) FROM tmp_latest_expired_renewals;
SELECT 'current_rows_with_stale_starts_at|' || count(*)
FROM tmp_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;
COPY (
  SELECT ler.user_id, u.email, ler.group_id, g.name AS group_name,
         to_char(ler.prev_paid_at AT TIME ZONE 'Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS') AS prev_paid_at,
         to_char(ler.prev_expected_expires_at AT TIME ZONE 'Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS') AS prev_expected_expires_at,
         to_char(ler.paid_at AT TIME ZONE 'Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS') AS latest_paid_at,
         s.id AS subscription_id,
         to_char(s.starts_at AT TIME ZONE 'Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS') AS current_starts_at,
         CASE WHEN s.starts_at < ler.paid_at THEN 1 ELSE 0 END AS stale_starts_at
  FROM tmp_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
  ORDER BY ler.paid_at DESC
) TO STDOUT WITH CSV HEADER;
