-- Source data for "Public GitHub repos with an agent-instruction file"
-- Run on 2026-04-29.
--
-- ----------------------------------------------------------------------
-- Why this is not a SQL query
-- ----------------------------------------------------------------------
-- This file is named .sql for parity with the chart's "Queries (SQL)"
-- download link, but the canonical sources for the May 2025 - April 2026
-- window are *GitHub APIs*, not BigQuery. The two BigQuery options we
-- considered both failed:
--
--   1. bigquery-public-data.github_repos.commits  -- has full file lists
--      per commit, which would be ideal. Last refreshed 2025-10-14, well
--      before our endpoint, so unusable for the second half of the window.
--
--   2. githubarchive.day.YYYYMMDD                  -- modern PushEvent
--      payloads no longer carry the commits[] array (stripped circa
--      Oct/Nov 2025) and gharchive.day collection itself broke on
--      2025-10-09 (daily PushEvents collapsed from ~1.7M to ~12k).
--
-- We therefore built the time series directly from the GitHub APIs.
--
-- ----------------------------------------------------------------------
-- Methodology
-- ----------------------------------------------------------------------
-- For each of the seven file patterns, the same two-step procedure:
--
--   STEP 1 (sample, GitHub code search REST API):
--     Pull the first 5 pages * 100 results = 500 unique repos that
--     currently contain a file matching the pattern. Total sample:
--     3,478 repos across 7 patterns. Snapshot total_count from the same
--     calls (the "X" in "Showing first 1000 of X results"):
--       AGENTS.md:                82,304 files
--       CLAUDE.md:                25,408
--       GEMINI.md:                 4,064
--       .github/copilot-instructions.md:  29,472
--       .cursorrules:              8,200
--       .clinerules:               1,544
--       .windsurfrules:            2,208
--       --------------------------------
--       TOTAL:                   153,200 files
--
--   STEP 2 (first-commit date, GitHub GraphQL):
--     For each sampled (owner, repo, path), fetch the full commit history
--     for that exact path:
--
--       repository(owner: $o, name: $n) {
--         object(expression: "HEAD") {
--           ... on Commit {
--             history(path: $path, first: 100) {
--               totalCount
--               nodes { committedDate }
--             }
--           }
--         }
--       }
--
--     The OLDEST node is the first commit that touched the file, i.e.
--     the date the file appeared in this repo. Calls are batched 15 repos
--     per request via GraphQL aliases (one point per request, ~232
--     requests for 3,478 repos).
--
-- STEP 3 (aggregate + scale):
--     Group sampled repos by (pattern, first-commit-month). For each
--     pattern, scale_factor = snapshot_total / sample_n (e.g. AGENTS.md
--     scale = 82304/500 = 164.6). Per-month cumulative repo count is
--     scale_factor * count(sample repos with first_commit_month <= M).
--     Sum across patterns gives the all-pattern series. The Apr 2026
--     endpoint naturally lands at ~153K because by Apr 2026 every
--     sampled repo has been "discovered" in the snapshot.
--
-- ----------------------------------------------------------------------
-- The pseudo-query (one per pattern, repeated 7x)
-- ----------------------------------------------------------------------
-- Step 1 (REST):
--   GET https://api.github.com/search/code
--       ?q=filename:AGENTS.md+path:/
--       &per_page=100
--       &page=1..5
--
-- Step 2 (GraphQL, batched 15 per request):
--   query {
--     rateLimit { cost remaining resetAt }
--     r0: repository(owner: "OWNER", name: "REPO") {
--       object(expression: "HEAD") {
--         ... on Commit {
--           history(path: "AGENTS.md", first: 100) {
--             totalCount
--             nodes { committedDate }
--           }
--         }
--       }
--     }
--     r1: repository(owner: "OWNER2", name: "REPO2") { ... }
--     ...
--     r14: repository(owner: "OWNER15", name: "REPO15") { ... }
--   }
--
-- Step 3 (aggregation, Python pseudo-code):
--   for repo in sample:
--     m = first_commit_iso[:7]                    # "YYYY-MM"
--     per_pat[pattern][m] += 1
--   for pattern, snapshot in SNAPSHOTS.items():
--     scale = snapshot / sum(per_pat[pattern].values())
--     cumulative = pre_window_count(pattern)
--     for month in MAY_2025 .. APR_2026:
--       cumulative += per_pat[pattern].get(month, 0)
--       cumulative_scaled[pattern][month] = round(cumulative * scale)
--   all_cum[m] = sum(cumulative_scaled[p][m] for p in patterns)
--
-- ----------------------------------------------------------------------
-- API budget used
-- ----------------------------------------------------------------------
--   Code search:      35 requests   (5 pages * 7 patterns)
--   GraphQL history:  ~232 requests (3,478 repos / 15 per batch)
--   GraphQL points:   ~232          (each batched request = 1 point)
--   Hours:            well under one (5,000 graphql points/hour budget)
--   BigQuery scanned: 0 GB (nothing run on BQ for this version)
--   $ spent:          $0
--
-- ----------------------------------------------------------------------
-- Caveats worth knowing
-- ----------------------------------------------------------------------
-- 1. Survivorship bias: this counts repos whose agent-instruction file
--    *exists today*. Repos that added one and later deleted it are
--    invisible to both the sample and the snapshot. The series is
--    therefore "repos that have an agent-instruction file by month M
--    AND still have it on 2026-04-29".
--
-- 2. Code search ranking: GitHub does not document the sort order for
--    code search beyond "best match". Our 500-repo sample is therefore
--    not a true uniform random sample; it skews toward repos GitHub's
--    indexer surfaces first. The shape of the curve is the main signal;
--    absolute monthly numbers are scaled estimates with that caveat.
--
-- 3. Cross-pattern double counting: a repo with both AGENTS.md and
--    CLAUDE.md is counted in both per-pattern series and contributes to
--    the all-pattern sum twice. This is consistent with how the
--    snapshot total (153,200 files) sums per-pattern totals.
--
-- 4. The Apr 2026 cumulative (152,804) is slightly below the snapshot
--    total (153,200) because the data was queried mid-month (2026-04-29);
--    files added in the final two days of April 2026 won't all show up
--    in the sampled first-commit set.
