# IKEv2 Paid vpnid_list Sync E2E Checklist

## Scope
- Verify paid order lifecycle events reliably synchronize into vpnid_list.
- Verify retry queue (app_ikev2_vpnid_sync_queue) recovers transient failures independently from provisioning.

## Target Events
- New paid order (orderformupdate)
- Extend period (orderinquiryExtendPeriod)
- Refund/terminate (orderinquiryrefund)
- Plan/order metadata change event (ikev2_shop_sync_event_receiver)

## Preconditions
- Test member with paid order item that contains IKEv2 target in it_spec_remote_sw.
- At least one order in status 입금 or 완료.
- DB access for SELECT queries.

---

## Quick SQL: Queue Health
```sql
-- newest queue rows
SELECT sync_id, od_id, it_id, trigger_source, status, final_status,
       attempt_count, fail_count, next_retry_at, last_result, last_error,
       created_at, updated_at
FROM app_ikev2_vpnid_sync_queue
ORDER BY sync_id DESC
LIMIT 50;
```

```sql
-- due retry candidates (should be retried by cron)
SELECT sync_id, od_id, it_id, status, final_status, next_retry_at
FROM app_ikev2_vpnid_sync_queue
WHERE final_status <> 'success'
  AND (next_retry_at IS NULL OR next_retry_at <= NOW())
ORDER BY next_retry_at ASC, sync_id ASC;
```

---

## Quick SQL: vpnid_list Reflection
```sql
-- latest paid profile source rows
SELECT no, member_id, server_name, member_id_expire, country_name,
       enable, od_id,
       COALESCE(plan_name, '') AS plan_name,
       COALESCE(memo, '') AS memo,
       COALESCE(update_datetime, datetime) AS touched_at
FROM vpnid_list
ORDER BY no DESC
LIMIT 100;
```

```sql
-- specific order reflection check
-- replace :OD_ID and :MB_ID manually
SELECT no, member_id, server_name, member_id_expire, enable, od_id,
       COALESCE(plan_name, '') AS plan_name,
       COALESCE(update_datetime, datetime) AS touched_at
FROM vpnid_list
WHERE od_id = ':OD_ID'
   OR member_id = ':MB_ID'
ORDER BY no DESC;
```

---

## Scenario A: New Paid Order
1. Create paid order and complete payment to 입금.
2. Confirm queue insert and success in app_ikev2_vpnid_sync_queue.
3. Confirm vpnid_list row exists/updated for member_id + server_name.

Expected
- queue final_status = success
- vpnid_list enable = 1
- member_id_expire populated

## Scenario B: Extend Period
1. Execute extend purchase on existing order item.
2. Check queue row for same od_id/it_id trigger_source=shop_order_extend.
3. Verify member_id_expire in vpnid_list increased.

Expected
- queue success
- vpnid_list member_id_expire moved forward

## Scenario C: Refund/Terminate
1. Execute refund flow on paid order item.
2. Check trigger_source=shop_order_refund queue success.
3. Verify vpnid_list enable becomes 0 when ct_status maps to cancel/return in source data path.

Expected
- queue success
- affected vpnid_list row is disabled when cancellation path applies

## Scenario D: Plan Change / Sync Receiver Event
1. Trigger shop sync event with od_id/it_id changes.
2. Inspect receiver response field vpnid_sync_reflection.
3. Verify queue/row update in vpnid_list.

Expected
- receiver accepted includes vpnid_sync_reflection
- queue success and vpnid_list updated

---

## Retry Cron Validation
```bash
# CLI one-shot retry (recommended)
php __CRON/cron_ikev2_vpnid_sync_retry_cli.php --limit=50
```

Expected
- processed > 0 when retryable rows exist
- fail count decreases over repeated successful runs

---

## Pass Criteria
- All A/B/C/D scenarios reflected in vpnid_list without manual DB edits.
- Failed sync rows recover through retry cron.
- No divergence between paid profile query source and latest order state.
