-- Phase 5E.3: read-only aggregate evidence. Execute remotely with Wrangler.
-- Never export hashes, tokens, emails, metadata_json, or raw identities.
-- D1 remote read mode rejects temporary-table setup, so each SELECT uses an
-- inline CTE. The project allowlist is intentionally explicit and bounded.
WITH phase5e3_projects(project_id) AS (VALUES
('0b9b8881-f0f4-4987-a114-9993e1fac039'),
('1b96aa25-ab11-4d3f-bd2d-b39b2a645584'),
('1d2f7381-59da-4e6c-959c-1d08ae5d903f'),
('2311fa60-ac9e-40f7-9b29-b005f671b61c'),
('296357f8-07a3-4085-b136-e9e2967b8356'),
('4566d83c-63db-45a1-852d-b94841f4fa1c'),
('55e5903d-4773-44b2-9a52-d2ef449012e6'),
('7349b26d-54f8-4047-80a3-e9f462a622f1'),
('7f5b0a1d-c605-49d0-8135-4b2b5aa5d06f'),
('87dc27c3-0b45-41d5-905b-4402cd757bca'),
('881bace2-2ca9-41b4-941d-f1f3e40ae420'),
('8b635b7e-9792-4c63-8239-7671afd8c330'),
('b3241454-baab-4bfa-8862-6bfdd3d76898'),
('bc1b0c98-5211-412d-b33d-cbd93fd3e108'),
('bca2d270-644e-4780-ab42-fe84269a0f3e'),
('be2968c9-11d2-4a09-bfd7-79efd1f767ca'),
('c13ed1e9-12e7-440d-8a34-2e10c4f2801d'),
('cb2185b9-0a13-49a5-95d0-967e372d4b09'),
('db681518-9a02-4dbe-b9f6-822483956392'),
('e700bb12-3f3a-4cd8-9ceb-a5435028a680'),
('ff2c1636-ec10-4510-8a25-529cc36b4b62'))

SELECT u.project_id,
       COUNT(*) AS users_total,
       SUM(CASE WHEN u.password_hash IS NOT NULL THEN 1 ELSE 0 END) AS password_credential_count,
       SUM(CASE WHEN json_extract(u.user_metadata_json, '$.p2hs_authority.source') = 'workspace' THEN 1 ELSE 0 END) AS workspace_derived_count,
       SUM(CASE WHEN json_extract(u.user_metadata_json, '$.p2hs_authority.source') IS NULL THEN 1 ELSE 0 END) AS app_local_count,
       SUM(CASE WHEN u.status = 'active' THEN 1 ELSE 0 END) AS active_user_count,
       SUM(CASE WHEN u.deleted_at IS NOT NULL THEN 1 ELSE 0 END) AS deleted_user_count
FROM cloud_app_users u
JOIN phase5e3_projects p ON p.project_id = u.project_id
GROUP BY u.project_id
ORDER BY u.project_id;

WITH phase5e3_projects(project_id) AS (VALUES
('0b9b8881-f0f4-4987-a114-9993e1fac039'), ('1b96aa25-ab11-4d3f-bd2d-b39b2a645584'), ('1d2f7381-59da-4e6c-959c-1d08ae5d903f'), ('2311fa60-ac9e-40f7-9b29-b005f671b61c'), ('296357f8-07a3-4085-b136-e9e2967b8356'), ('4566d83c-63db-45a1-852d-b94841f4fa1c'), ('55e5903d-4773-44b2-9a52-d2ef449012e6'), ('7349b26d-54f8-4047-80a3-e9f462a622f1'), ('7f5b0a1d-c605-49d0-8135-4b2b5aa5d06f'), ('87dc27c3-0b45-41d5-905b-4402cd757bca'), ('881bace2-2ca9-41b4-941d-f1f3e40ae420'), ('8b635b7e-9792-4c63-8239-7671afd8c330'), ('b3241454-baab-4bfa-8862-6bfdd3d76898'), ('bc1b0c98-5211-412d-b33d-cbd93fd3e108'), ('bca2d270-644e-4780-ab42-fe84269a0f3e'), ('be2968c9-11d2-4a09-bfd7-79efd1f767ca'), ('c13ed1e9-12e7-440d-8a34-2e10c4f2801d'), ('cb2185b9-0a13-49a5-95d0-967e372d4b09'), ('db681518-9a02-4dbe-b9f6-822483956392'), ('e700bb12-3f3a-4cd8-9ceb-a5435028a680'), ('ff2c1636-ec10-4510-8a25-529cc36b4b62'))
SELECT e.project_id, e.event_type, e.provider, COUNT(*) AS event_count
FROM cloud_auth_events e
JOIN phase5e3_projects p ON p.project_id = e.project_id
GROUP BY e.project_id, e.event_type, e.provider
ORDER BY e.project_id, e.event_type, e.provider;

WITH phase5e3_projects(project_id) AS (VALUES
('0b9b8881-f0f4-4987-a114-9993e1fac039'), ('1b96aa25-ab11-4d3f-bd2d-b39b2a645584'), ('1d2f7381-59da-4e6c-959c-1d08ae5d903f'), ('2311fa60-ac9e-40f7-9b29-b005f671b61c'), ('296357f8-07a3-4085-b136-e9e2967b8356'), ('4566d83c-63db-45a1-852d-b94841f4fa1c'), ('55e5903d-4773-44b2-9a52-d2ef449012e6'), ('7349b26d-54f8-4047-80a3-e9f462a622f1'), ('7f5b0a1d-c605-49d0-8135-4b2b5aa5d06f'), ('87dc27c3-0b45-41d5-905b-4402cd757bca'), ('881bace2-2ca9-41b4-941d-f1f3e40ae420'), ('8b635b7e-9792-4c63-8239-7671afd8c330'), ('b3241454-baab-4bfa-8862-6bfdd3d76898'), ('bc1b0c98-5211-412d-b33d-cbd93fd3e108'), ('bca2d270-644e-4780-ab42-fe84269a0f3e'), ('be2968c9-11d2-4a09-bfd7-79efd1f767ca'), ('c13ed1e9-12e7-440d-8a34-2e10c4f2801d'), ('cb2185b9-0a13-49a5-95d0-967e372d4b09'), ('db681518-9a02-4dbe-b9f6-822483956392'), ('e700bb12-3f3a-4cd8-9ceb-a5435028a680'), ('ff2c1636-ec10-4510-8a25-529cc36b4b62'))
SELECT s.project_id,
       COUNT(*) AS session_total,
       SUM(CASE WHEN s.revoked_at IS NULL THEN 1 ELSE 0 END) AS active_session_count,
       SUM(CASE WHEN s.revoked_at IS NOT NULL THEN 1 ELSE 0 END) AS revoked_session_count
FROM cloud_app_sessions s
JOIN phase5e3_projects p ON p.project_id = s.project_id
GROUP BY s.project_id
ORDER BY s.project_id;