\pset tuples_only on
\pset format unaligned
DROP TABLE IF EXISTS tmp_impacted_sub_renewals;
CREATE TEMP TABLE tmp_impacted_sub_renewals AS
WITH paid_orders AS (
  SELECT
    p.id,
    p.user_id,
    p.group_id,
    p.out_trade_no,
    p.plan_key,
    p.plan_variant_key,
    COALESCE(NULLIF(p.validity_days, 0), g.default_validity_days) AS validity_days,
    COALESCE(p.notify_at, p.created_at) AS paid_at,
    p.status
  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.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
    o.*,
    (o.prev_paid_at + make_interval(days => COALESCE(o.prev_validity_days, 0))) AS prev_expected_expires_at
  FROM ordered o
  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))
), latest_expired_renewals AS (
  SELECT * FROM expired_renewals WHERE rn_latest = 1
)
SELECT
  u.id AS user_id,
  u.email,
  g.id AS group_id,
  g.name AS group_name,
  s.id AS subscription_id,
  s.status AS subscription_status,
  s.starts_at,
  s.expires_at,
  s.daily_window_start,
  s.weekly_window_start,
  s.monthly_window_start,
  ler.id AS latest_order_id,
  ler.out_trade_no AS latest_order_no,
  ler.plan_key,
  ler.plan_variant_key,
  ler.validity_days,
  ler.paid_at AS latest_paid_at,
  ler.prev_order_id,
  ler.prev_paid_at,
  ler.prev_expected_expires_at,
  CASE WHEN s.starts_at < ler.paid_at THEN 1 ELSE 0 END AS stale_starts_at,
  CASE WHEN s.daily_window_start IS NOT NULL AND s.daily_window_start < ler.paid_at THEN 1 ELSE 0 END AS stale_daily,
  CASE WHEN s.weekly_window_start IS NOT NULL AND s.weekly_window_start < ler.paid_at THEN 1 ELSE 0 END AS stale_weekly,
  CASE WHEN s.monthly_window_start IS NOT NULL AND s.monthly_window_start < ler.paid_at THEN 1 ELSE 0 END AS stale_monthly
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 = s.user_id
JOIN groups g ON g.id = s.group_id
WHERE s.starts_at < ler.paid_at
   OR (s.daily_window_start IS NOT NULL AND s.daily_window_start < ler.paid_at)
   OR (s.weekly_window_start IS NOT NULL AND s.weekly_window_start < ler.paid_at)
   OR (s.monthly_window_start IS NOT NULL AND s.monthly_window_start < ler.paid_at);

SELECT 'count|' || count(*) FROM tmp_impacted_sub_renewals;
COPY (
  SELECT
    user_id,
    email,
    group_id,
    group_name,
    subscription_id,
    subscription_status,
    to_char(starts_at AT TIME ZONE 'Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS') AS starts_at,
    to_char(expires_at AT TIME ZONE 'Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS') AS expires_at,
    to_char(latest_paid_at AT TIME ZONE 'Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS') AS latest_paid_at,
    validity_days,
    latest_order_id,
    latest_order_no,
    stale_starts_at,
    stale_daily,
    stale_weekly,
    stale_monthly,
    to_char(prev_paid_at AT TIME ZONE 'Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS') AS prev_paid_at,
    to_char(prev_expected_expires_at AT TIME ZONE 'Asia/Shanghai', 'YYYY-MM-DD HH24:MI:SS') AS prev_expected_expires_at
  FROM tmp_impacted_sub_renewals
  ORDER BY latest_paid_at DESC, user_id, group_id
) TO STDOUT WITH CSV HEADER;
