-- Per-host summary for stats-backfill. Cross-tool sibling of
-- `mini host-summary --pds <host>`. Same shape buckets where they
-- apply, so the two outputs can be eyeballed side-by-side.
--
-- Schema reference: stats-backfill/src/db.rs:38-104.
--
-- Run with sqlite3, parameter set ahead of `.read`:
--
--     sqlite3 path/to/stats-backfill.db -bail \
--       -cmd ".parameter set :host 'berlin-user.eurosky.social'" \
--       ".read stats-backfill/sql/host_summary.sql"
--
-- Conceptual caveat: `repos.host` is the upsert-chosen host for a DID
-- (one row per DID, max-rev tie-break). mini's R/ is keyed (pds, did),
-- so a multi-homed DID appears under every host there. For migrated
-- accounts (started on bsky, now on indie), this view may put the DID
-- under the legacy host while mini has it under the indie host. Same
-- skew as compare-dump's resolver.

.mode column
.headers on

-- 1. Disposition by fetch_state (captured = 'done', failed_terminal,
--    still pending). Sum across rows = total DIDs sb chose this host for.
SELECT '--- fetch_state ---' AS section;
SELECT fetch_state, COUNT(*) AS dids
FROM repos
WHERE host = :host
GROUP BY fetch_state
ORDER BY dids DESC;

-- 2. listRepos disposition. Mirrors mini's inactive bucket sub-
--    breakdown by status.
SELECT '--- listrepos disposition ---' AS section;
SELECT listrepos_active, listrepos_status, COUNT(*) AS dids
FROM repos
WHERE host = :host
GROUP BY listrepos_active, listrepos_status
ORDER BY dids DESC;

-- 3. Top failure reasons among failed_terminal. Mirrors mini's
--    `failed_by_error` histogram.
SELECT '--- last_error (failed_terminal, top 25) ---' AS section;
SELECT last_error, COUNT(*) AS dids
FROM repos
WHERE host = :host AND fetch_state = 'failed_terminal'
GROUP BY last_error
ORDER BY dids DESC
LIMIT 25;

-- 4. Measured totals over captured DIDs. Mirrors mini's totals line.
--    `star_zstd_bytes` is stats-backfill-only (mini doesn't compute it).
SELECT '--- measured totals (captured set) ---' AS section;
SELECT
    COUNT(*)                    AS measured_dids,
    SUM(m.car_bytes)            AS total_car_bytes,
    SUM(m.car_wire_bytes)       AS total_car_wire_bytes,
    SUM(m.star_zstd_bytes)      AS total_star_zstd_bytes,
    SUM(m.record_count)         AS total_records,
    SUM(m.blob_ref_count)       AS total_blob_refs,
    SUM(m.blob_total_size)      AS total_blob_bytes
FROM repos r
JOIN repo_measurements m ON m.did = r.did
WHERE r.host = :host;

-- 5. Recent fetch_attempt_log activity. Surfaces clustering of errors
--    in time. mini has no equivalent (it doesn't keep an attempt log).
SELECT '--- recent attempts (last 20) ---' AS section;
SELECT
    datetime(attempted_at, 'unixepoch') AS attempted_at_iso,
    substr(did, 1, 40)                  AS did,
    http_status,
    classification,
    substr(error_summary, 1, 80)        AS error_summary
FROM fetch_attempt_log
WHERE host = :host
ORDER BY attempted_at DESC
LIMIT 20;