Constant SQL 

pub const SQL: &str = "
    WITH file_revs AS (
        -- (rev, path) is the changes PK, so COUNT(rev) == COUNT(DISTINCT rev)
        -- per path. Plain COUNT avoids DuckDB's distinct-tracking overhead.
        SELECT path, COUNT(rev) AS revs
        FROM changes
        GROUP BY path
        HAVING revs >= ?
    ),
    file_complexity AS (
        SELECT path, MAX(cognitive) AS cognitive
        FROM {cm_src}
        GROUP BY path
    ),
    -- File-level MI body is swapped at build time depending on whether
    -- grouping is active — see `FILE_MI_RAW` / `FILE_MI_GROUPED`. The
    -- raw form joins `entities` to filter `kind='unit'`; the grouped
    -- form reads pre-baked `mi` from `complexity_metrics_grouped`.
    {file_mi_cte},
    -- Per-file AI-attribution percentage: share of commits touching this
    -- file that carry an AI-attribution signal (ai-assisted or
    -- ai-authored). NULLIF guards against div-by-zero on the empty join
    -- (defensive; file_revs already filters revs >= min_revs).
    -- Categories come from identity::bots — schema is stable.
    file_ai AS (
        SELECT
            ch.path,
            COUNT(CASE WHEN co.ai_attribution IN ('ai-assisted', 'ai-authored')
                       THEN 1 END) * 100.0
                / NULLIF(COUNT(ch.rev), 0) AS ai_pct
        FROM {ai_src} ch
        INNER JOIN commits co ON co.rev = ch.rev
        GROUP BY ch.path
    ),
    -- Repo-relative MI percentile rank, computed ONLY over files that
    -- actually have a `mi` value — files without a `kind='unit'` entry
    -- (unsupported language / skipped file) MUST NOT skew the
    -- distribution. PERCENT_RANK over file_mi emits values in [0, 1].
    -- See analyses/mi.rs for why bands are repo-relative rather than
    -- absolute Coleman/SEI thresholds (literature thresholds would
    -- classify ~100% of any large codebase as 'low maintainability').
    file_mi_ranked AS (
        SELECT
            path,
            mi,
            PERCENT_RANK() OVER (ORDER BY mi) AS mi_rank
        FROM file_mi
    ),
    joined AS (
        SELECT
            fr.path,
            fr.revs,
            COALESCE(fc.cognitive, 0) AS cognitive,
            fmr.mi AS mi,
            fmr.mi_rank AS mi_rank,
            fa.ai_pct AS ai_pct
        FROM file_revs fr
        LEFT JOIN file_complexity fc ON fc.path = fr.path
        LEFT JOIN file_mi_ranked fmr ON fmr.path = fr.path
        LEFT JOIN file_ai fa ON fa.path = fr.path
    ),
    ranked AS (
        SELECT
            path,
            revs,
            cognitive,
            mi,
            mi_rank,
            ai_pct,
            PERCENT_RANK() OVER (ORDER BY revs) AS pr_rev,
            PERCENT_RANK() OVER (ORDER BY cognitive) AS pr_cx,
            CASE
                WHEN MAX(cognitive) OVER () > 0
                THEN cognitive / MAX(cognitive) OVER ()
                ELSE 0
            END AS norm_cx
        FROM joined
    )
    SELECT
        path,
        revs,
        cognitive,
        GREATEST(0.0, LEAST(100.0, 100.0 * (1.0 - 0.40 * norm_cx))) AS cognitive_health,
        pr_rev * pr_cx * (100.0 - GREATEST(0.0, LEAST(100.0, 100.0 * (1.0 - 0.40 * norm_cx)))) / 4.0 AS score,
        mi,
        mi_rank,
        ai_pct
    FROM ranked
    ORDER BY score DESC, path ASC
    LIMIT ?
";