Scoring views

CREATE VIEW public.agent_score_model_family_v1 AS
WITH author_aggregates AS (
  SELECT
    author,
    SUM(downloads)::BIGINT                                              AS total_downloads,
    SUM(likes)::INTEGER                                                 AS total_likes,
    COUNT(model_id)::INTEGER                                            AS model_count,
    MAX(downloads)::BIGINT                                              AS top_model_downloads,
    MAX(likes)::INTEGER                                                 AS top_model_likes,
    MAX(last_modified_at)                                               AS most_recent_modified,
    ROUND(
      100.0 * SUM(CASE WHEN COALESCE(gated, FALSE) THEN 1 ELSE 0 END)::numeric
            / NULLIF(COUNT(model_id), 0),
      1
    )::numeric(5,1)                                                     AS gated_pct
  FROM public.hf_models
  WHERE removed_at IS NULL
  GROUP BY author
),
hf_sub_scores AS (
  SELECT
    agg.*,
    LEAST(100, ROUND( LOG(10, GREATEST(1, agg.total_downloads))     * 15 ))::INTEGER AS hf_downloads_score,
    LEAST(100, ROUND( LOG(10, GREATEST(1, agg.total_likes))         * 25 ))::INTEGER AS hf_likes_score,
    CASE
      WHEN agg.most_recent_modified > NOW() - INTERVAL '30 days'  THEN 100
      WHEN agg.most_recent_modified > NOW() - INTERVAL '90 days'  THEN 75
      WHEN agg.most_recent_modified > NOW() - INTERVAL '180 days' THEN 50
      WHEN agg.most_recent_modified > NOW() - INTERVAL '365 days' THEN 25
      ELSE 0
    END::INTEGER                                                                     AS hf_recency_score,
    LEAST(100, ROUND( LOG(10, GREATEST(1, agg.model_count))         * 40 ))::INTEGER AS hf_breadth_score,
    LEAST(100, ROUND( LOG(10, GREATEST(1, agg.top_model_downloads)) * 13 ))::INTEGER AS hf_top_model_score
  FROM author_aggregates agg
),
hf_basket AS (
  SELECT
    *,
    ROUND(
      hf_downloads_score  * 0.30 +
      hf_likes_score      * 0.20 +
      hf_recency_score    * 0.20 +
      hf_breadth_score    * 0.15 +
      hf_top_model_score  * 0.15
    )::INTEGER AS hf_score
  FROM hf_sub_scores
),
lmarena_per_agent AS (
  SELECT
    a.id                                                  AS agent_id,
    MAX(lm.arena_score)::NUMERIC                          AS lmarena_top_score,
    COUNT(lm.model_name)::INTEGER                         AS lmarena_matched_count,
    SUM(lm.votes)::BIGINT                                 AS lmarena_total_votes
  FROM public.agents a
  LEFT JOIN public.lmarena_models lm
    ON lm.model_name = ANY(a.lmarena_model_keys)
    AND lm.removed_at IS NULL
  WHERE a.primary_category = 'model_family'
  GROUP BY a.id
),
derivatives_per_author AS (
  SELECT
    base_author                                                         AS author,
    SUM(derivatives_count)::INTEGER                                     AS total_derivatives,
    SUM(derivatives_total_downloads)::BIGINT                            AS total_derivative_downloads,
    COUNT(base_model)::INTEGER                                          AS base_models_with_derivatives,
    MAX(derivatives_count)::INTEGER                                     AS top_base_derivatives_count
  FROM public.hf_derivatives
  GROUP BY base_author
),
citations_per_agent AS (
  SELECT
    a.id                                                                AS agent_id,
    SUM(pc.citation_count)::BIGINT                                      AS total_citations,
    SUM(pc.influential_citation_count)::INTEGER                         AS total_influential_citations,
    COUNT(pc.paper_id)::INTEGER                                         AS papers_matched_count,
    MAX(pc.citation_count)::INTEGER                                     AS top_paper_citations
  FROM public.agents a
  LEFT JOIN LATERAL unnest(a.semantic_scholar_paper_ids) AS t(token) ON TRUE
  LEFT JOIN public.paper_citations pc
    ON pc.arxiv_id = REGEXP_REPLACE(t.token, '^arxiv:', '', 'i')
    AND pc.removed_at IS NULL
  WHERE a.primary_category = 'model_family'
  GROUP BY a.id
),
-- NEW IN v1.4: per-agent deployment aggregation across all 6 source tables
deployments_per_agent AS (
  SELECT
    model_family_handle,
    SUM(deployment_count)::INTEGER                                      AS total_deployments,
    COUNT(source_table)::INTEGER                                        AS sources_with_deployments,
    MAX(deployment_count)::INTEGER                                      AS top_source_deployments,
    jsonb_object_agg(source_table, deployment_count)                    AS deployments_by_source
  FROM public.model_family_deployments
  GROUP BY model_family_handle
)
SELECT
  a.id                                       AS agent_id,
  a.handle,
  a.display_name,
  a.hf_author,
  a.lmarena_model_keys,
  a.semantic_scholar_paper_ids,
  a.model_family_search_keywords,

  -- Raw HF aggregates
  COALESCE(hb.total_downloads, 0)::BIGINT     AS total_downloads,
  COALESCE(hb.total_likes, 0)::INTEGER        AS total_likes,
  COALESCE(hb.model_count, 0)::INTEGER        AS model_count,
  COALESCE(hb.top_model_downloads, 0)::BIGINT AS top_model_downloads,
  COALESCE(hb.top_model_likes, 0)::INTEGER    AS top_model_likes,
  hb.most_recent_modified,
  hb.gated_pct,

  -- LMArena raw aggregates
  lma.lmarena_top_score                       AS lmarena_top_arena_score,
  lma.lmarena_matched_count,
  lma.lmarena_total_votes,

  -- Derivatives raw aggregates
  COALESCE(dpa.total_derivatives, 0)::INTEGER          AS total_derivatives,
  COALESCE(dpa.base_models_with_derivatives, 0)::INTEGER AS base_models_with_derivatives,
  COALESCE(dpa.top_base_derivatives_count, 0)::INTEGER AS top_base_derivatives_count,
  COALESCE(dpa.total_derivative_downloads, 0)::BIGINT  AS total_derivative_downloads,

  -- Citations raw aggregates
  COALESCE(cpa.total_citations, 0)::BIGINT             AS total_citations,
  COALESCE(cpa.total_influential_citations, 0)::INTEGER AS total_influential_citations,
  COALESCE(cpa.papers_matched_count, 0)::INTEGER       AS papers_matched_count,
  COALESCE(cpa.top_paper_citations, 0)::INTEGER        AS top_paper_citations,

  -- NEW: Deployment raw aggregates
  COALESCE(dep.total_deployments, 0)::INTEGER          AS total_deployments,
  COALESCE(dep.sources_with_deployments, 0)::INTEGER   AS sources_with_deployments,
  COALESCE(dep.top_source_deployments, 0)::INTEGER     AS top_source_deployments,
  dep.deployments_by_source                            AS deployments_by_source,

  -- HF sub-scores
  COALESCE(hb.hf_downloads_score, 0)::INTEGER  AS hf_downloads_score,
  COALESCE(hb.hf_likes_score, 0)::INTEGER      AS hf_likes_score,
  COALESCE(hb.hf_recency_score, 0)::INTEGER    AS hf_recency_score,
  COALESCE(hb.hf_breadth_score, 0)::INTEGER    AS hf_breadth_score,
  COALESCE(hb.hf_top_model_score, 0)::INTEGER  AS hf_top_model_score,

  -- Top-level signal slices
  COALESCE(hb.hf_score, 0)::INTEGER            AS hf_score,
  CASE
    WHEN dpa.total_derivatives IS NULL OR dpa.total_derivatives = 0 THEN NULL::INTEGER
    ELSE LEAST(100, ROUND( LOG(10, GREATEST(1, dpa.total_derivatives::numeric)) * 25 ))::INTEGER
  END                                          AS derivatives_score,
  CASE
    WHEN lma.lmarena_top_score IS NULL THEN NULL::INTEGER
    ELSE LEAST(100, GREATEST(0, ROUND((lma.lmarena_top_score - 700) / 8)))::INTEGER
  END                                          AS lmarena_score,
  CASE
    WHEN cpa.total_citations IS NULL OR cpa.total_citations = 0 THEN NULL::INTEGER
    ELSE LEAST(100, ROUND( LOG(10, GREATEST(1, cpa.total_citations::numeric)) * 16 ))::INTEGER
  END                                          AS citations_score,
  -- NEW: Deployment score (replaces previously-reserved social_score slot)
  CASE
    WHEN dep.total_deployments IS NULL OR dep.total_deployments = 0 THEN NULL::INTEGER
    ELSE LEAST(100, ROUND( LOG(10, GREATEST(1, dep.total_deployments::numeric)) * 30 ))::INTEGER
  END                                          AS deployment_score,

  -- Composite (weights: HF 30, derivatives 20, LMArena 25, citations 15, deployment 10)
  ROUND(
    COALESCE(hb.hf_score, 0)                                       * 0.30 +
    COALESCE(
      CASE WHEN dpa.total_derivatives IS NULL OR dpa.total_derivatives = 0 THEN 0
           ELSE LEAST(100, ROUND( LOG(10, GREATEST(1, dpa.total_derivatives::numeric)) * 25 ))
      END, 0
    )                                                              * 0.20 +
    COALESCE(
      CASE WHEN lma.lmarena_top_score IS NULL THEN 0
           ELSE LEAST(100, GREATEST(0, ROUND((lma.lmarena_top_score - 700) / 8)))
      END, 0
    )                                                              * 0.25 +
    COALESCE(
      CASE WHEN cpa.total_citations IS NULL OR cpa.total_citations = 0 THEN 0
           ELSE LEAST(100, ROUND( LOG(10, GREATEST(1, cpa.total_citations::numeric)) * 16 ))
      END, 0
    )                                                              * 0.15 +
    COALESCE(
      CASE WHEN dep.total_deployments IS NULL OR dep.total_deployments = 0 THEN 0
           ELSE LEAST(100, ROUND( LOG(10, GREATEST(1, dep.total_deployments::numeric)) * 30 ))
      END, 0
    )                                                              * 0.10
  )::INTEGER                                   AS model_family_score,

  RANK() OVER (
    ORDER BY (
      COALESCE(hb.hf_score, 0) * 0.30 +
      COALESCE(
        CASE WHEN dpa.total_derivatives IS NULL OR dpa.total_derivatives = 0 THEN 0
             ELSE LEAST(100, ROUND( LOG(10, GREATEST(1, dpa.total_derivatives::numeric)) * 25 ))
        END, 0
      ) * 0.20 +
      COALESCE(
        CASE WHEN lma.lmarena_top_score IS NULL THEN 0
             ELSE LEAST(100, GREATEST(0, ROUND((lma.lmarena_top_score - 700) / 8)))
        END, 0
      ) * 0.25 +
      COALESCE(
        CASE WHEN cpa.total_citations IS NULL OR cpa.total_citations = 0 THEN 0
             ELSE LEAST(100, ROUND( LOG(10, GREATEST(1, cpa.total_citations::numeric)) * 16 ))
        END, 0
      ) * 0.15 +
      COALESCE(
        CASE WHEN dep.total_deployments IS NULL OR dep.total_deployments = 0 THEN 0
             ELSE LEAST(100, ROUND( LOG(10, GREATEST(1, dep.total_deployments::numeric)) * 30 ))
        END, 0
      ) * 0.10
    ) DESC
  )::INTEGER                                   AS rank_in_model_family,

  -- Signals available count: HF + derivatives + LMArena + citations + deployment
  (
    (CASE WHEN COALESCE(hb.hf_score, 0) > 0 THEN 1 ELSE 0 END) +
    (CASE WHEN dpa.total_derivatives IS NOT NULL AND dpa.total_derivatives > 0 THEN 1 ELSE 0 END) +
    (CASE WHEN lma.lmarena_top_score IS NOT NULL THEN 1 ELSE 0 END) +
    (CASE WHEN cpa.total_citations IS NOT NULL AND cpa.total_citations > 0 THEN 1 ELSE 0 END) +
    (CASE WHEN dep.total_deployments IS NOT NULL AND dep.total_deployments > 0 THEN 1 ELSE 0 END)
  )::INTEGER                                   AS signals_available_count,

  -- Evidence-ready rule UPDATED for v1.4:
  --   3 of 5 signals AND >=1 capability signal.
  --   Capability signals now: derivatives, LMArena, citations, deployment.
  --   (Deployment counts as capability because cross-protocol agent-economy
  --   mentions are downstream usage evidence — harder to manipulate than any
  --   single source, and only AgentCrush can compute this.)
  (
    (
      (CASE WHEN COALESCE(hb.hf_score, 0) > 0 THEN 1 ELSE 0 END) +
      (CASE WHEN dpa.total_derivatives IS NOT NULL AND dpa.total_derivatives > 0 THEN 1 ELSE 0 END) +
      (CASE WHEN lma.lmarena_top_score IS NOT NULL THEN 1 ELSE 0 END) +
      (CASE WHEN cpa.total_citations IS NOT NULL AND cpa.total_citations > 0 THEN 1 ELSE 0 END) +
      (CASE WHEN dep.total_deployments IS NOT NULL AND dep.total_deployments > 0 THEN 1 ELSE 0 END)
    ) >= 3
    AND (
      (dpa.total_derivatives IS NOT NULL AND dpa.total_derivatives > 0)
      OR lma.lmarena_top_score IS NOT NULL
      OR (cpa.total_citations IS NOT NULL AND cpa.total_citations > 0)
      OR (dep.total_deployments IS NOT NULL AND dep.total_deployments > 0)
    )
  )                                            AS evidence_ready_for_public_rank,

  'v1.4-with-deployment'::TEXT                 AS methodology_version

FROM public.agents a
LEFT JOIN hf_basket hb               ON hb.author     = a.hf_author
LEFT JOIN lmarena_per_agent lma      ON lma.agent_id  = a.id
LEFT JOIN derivatives_per_author dpa ON dpa.author    = a.hf_author
LEFT JOIN citations_per_agent cpa    ON cpa.agent_id  = a.id
LEFT JOIN deployments_per_agent dep  ON dep.model_family_handle = a.handle
WHERE a.primary_category = 'model_family';

CREATE VIEW public.agent_score_tokenized_v1 AS
WITH tokenized_agents AS (
  SELECT
    id, handle, display_name, virtuals_id, secondary_categories, primary_category,
    socially_visible, bot_fetch_friendliness_score,
    avatar_url, custom_background_url, x_handle, website_url
  FROM public.agents
  WHERE primary_category = 'tokenized' OR 'tokenized' = ANY(secondary_categories)
),
joined AS (
  SELECT
    t.*,
    v.name                 AS virtuals_name,
    v.ticker               AS virtuals_ticker,
    v.token_address,
    v.market_cap_usd,
    v.liquidity_usd,
    v.volume_24h_usd,
    v.tvl_usd,
    v.holders,
    v.top10_holder_pct,
    v.price_change_pct_24h,
    v.token_price_usd,
    v.image_url            AS virtuals_image_url,
    v.twitter_url          AS virtuals_twitter_url
  FROM tokenized_agents t
  LEFT JOIN public.virtuals_agents v ON v.virtuals_id = t.virtuals_id
),
sub_scores AS (
  SELECT
    j.*,

    -- Market cap (log-scaled USD, coef 12)
    CASE WHEN market_cap_usd IS NULL OR market_cap_usd <= 0 THEN NULL::INTEGER
         ELSE LEAST(100, ROUND(LOG(10, GREATEST(1, market_cap_usd)) * 12))::INTEGER
    END AS market_cap_score,

    -- Liquidity (log-scaled, coef 16)
    CASE WHEN liquidity_usd IS NULL OR liquidity_usd <= 0 THEN NULL::INTEGER
         ELSE LEAST(100, ROUND(LOG(10, GREATEST(1, liquidity_usd)) * 16))::INTEGER
    END AS liquidity_score,

    -- Volume 24h (log-scaled, coef 13)
    CASE WHEN volume_24h_usd IS NULL OR volume_24h_usd <= 0 THEN NULL::INTEGER
         ELSE LEAST(100, ROUND(LOG(10, GREATEST(1, volume_24h_usd)) * 13))::INTEGER
    END AS volume_24h_score,

    -- Holder count (log-scaled, coef 18)
    CASE WHEN holders IS NULL OR holders <= 0 THEN NULL::INTEGER
         ELSE LEAST(100, ROUND(LOG(10, GREATEST(1, holders::numeric)) * 18))::INTEGER
    END AS holders_count_score,

    -- Top-10 concentration (inverse, 0-100)
    CASE WHEN top10_holder_pct IS NULL THEN NULL::INTEGER
         ELSE GREATEST(0, LEAST(100, 100 - ROUND(top10_holder_pct)))::INTEGER
    END AS holder_concentration_score,

    -- Price momentum 24h (bounded around neutral 50)
    CASE
      WHEN price_change_pct_24h IS NULL THEN NULL::INTEGER
      WHEN ABS(price_change_pct_24h) > 100 THEN 50
      ELSE GREATEST(0, LEAST(100, 50 + ROUND(price_change_pct_24h)))::INTEGER
    END AS price_momentum_score,

    -- NEW v1.1: TVL (log-scaled USD, coef 14) — replaces cross-protocol placeholder
    CASE WHEN tvl_usd IS NULL OR tvl_usd <= 0 THEN NULL::INTEGER
         ELSE LEAST(100, ROUND(LOG(10, GREATEST(1, tvl_usd)) * 14))::INTEGER
    END AS tvl_score,

    -- Social visibility (binary curated flag, NULL when absent)
    CASE
      WHEN socially_visible IS NULL THEN NULL::INTEGER
      WHEN socially_visible = TRUE THEN 100
      ELSE 0
    END AS social_score

  FROM joined j
),
baskets AS (
  SELECT
    *,
    CASE WHEN liquidity_score IS NULL AND volume_24h_score IS NULL THEN NULL::INTEGER
         ELSE ROUND(COALESCE(liquidity_score, 0) * 0.65 + COALESCE(volume_24h_score, 0) * 0.35)::INTEGER
    END AS liquidity_volume_score,
    CASE WHEN holders_count_score IS NULL AND holder_concentration_score IS NULL THEN NULL::INTEGER
         ELSE ROUND(COALESCE(holders_count_score, 0) * 0.55 + COALESCE(holder_concentration_score, 0) * 0.45)::INTEGER
    END AS holders_basket_score
  FROM sub_scores
)
SELECT
  id                                                  AS agent_id,
  handle,
  display_name,
  virtuals_id,
  primary_category,
  secondary_categories,
  virtuals_name,
  virtuals_ticker,
  token_address,
  market_cap_usd,
  liquidity_usd,
  volume_24h_usd,
  tvl_usd,
  holders,
  top10_holder_pct,
  price_change_pct_24h,
  token_price_usd,

  -- Sub-scores
  market_cap_score,
  liquidity_score,
  volume_24h_score,
  liquidity_volume_score,
  holders_count_score,
  holder_concentration_score,
  holders_basket_score,
  price_momentum_score,
  tvl_score,
  social_score,

  -- Composite (weights: mc 25, liq+vol 20, holders 15, momentum 10, TVL 15, social 15)
  ROUND(
    COALESCE(market_cap_score, 0)         * 0.25 +
    COALESCE(liquidity_volume_score, 0)   * 0.20 +
    COALESCE(holders_basket_score, 0)     * 0.15 +
    COALESCE(price_momentum_score, 0)     * 0.10 +
    COALESCE(tvl_score, 0)                * 0.15 +
    COALESCE(social_score, 0)             * 0.15
  )::INTEGER AS tokenized_score,

  RANK() OVER (
    ORDER BY (
      COALESCE(market_cap_score, 0)       * 0.25 +
      COALESCE(liquidity_volume_score, 0) * 0.20 +
      COALESCE(holders_basket_score, 0)   * 0.15 +
      COALESCE(price_momentum_score, 0)   * 0.10 +
      COALESCE(tvl_score, 0)              * 0.15 +
      COALESCE(social_score, 0)           * 0.15
    ) DESC
  )::INTEGER AS rank_in_tokenized,

  -- Signals available count (6 signals: mc, liq+vol, holders, momentum, TVL, social)
  (
    (CASE WHEN market_cap_score IS NOT NULL THEN 1 ELSE 0 END) +
    (CASE WHEN liquidity_volume_score IS NOT NULL THEN 1 ELSE 0 END) +
    (CASE WHEN holders_basket_score IS NOT NULL THEN 1 ELSE 0 END) +
    (CASE WHEN price_momentum_score IS NOT NULL THEN 1 ELSE 0 END) +
    (CASE WHEN tvl_score IS NOT NULL THEN 1 ELSE 0 END) +
    (CASE WHEN social_score IS NOT NULL THEN 1 ELSE 0 END)
  )::INTEGER AS signals_available_count,

  -- Evidence-ready: 3-of-6 AND ≥1 economic signal (mc OR liq OR holders OR tvl > 0)
  (
    (
      (CASE WHEN market_cap_score IS NOT NULL THEN 1 ELSE 0 END) +
      (CASE WHEN liquidity_volume_score IS NOT NULL THEN 1 ELSE 0 END) +
      (CASE WHEN holders_basket_score IS NOT NULL THEN 1 ELSE 0 END) +
      (CASE WHEN price_momentum_score IS NOT NULL THEN 1 ELSE 0 END) +
      (CASE WHEN tvl_score IS NOT NULL THEN 1 ELSE 0 END) +
      (CASE WHEN social_score IS NOT NULL THEN 1 ELSE 0 END)
    ) >= 3
    AND (
      (market_cap_usd IS NOT NULL AND market_cap_usd > 0)
      OR (liquidity_usd IS NOT NULL AND liquidity_usd > 0)
      OR (holders IS NOT NULL AND holders > 0)
      OR (tvl_usd IS NOT NULL AND tvl_usd > 0)
    )
  ) AS evidence_ready_for_public_rank,

  'v1.1-tokenized-tvl'::TEXT AS methodology_version

FROM baskets;

CREATE VIEW public.agent_score_service_v1 AS
WITH service_agents AS (
  SELECT
    id, handle, display_name, primary_category, secondary_categories,
    github_full_name, github_repo_url, agentverse_id,
    avatar_url, custom_background_url, website_url,
    socially_visible
  FROM public.agents
  WHERE primary_category = 'service' OR 'service' = ANY(secondary_categories)
),
joined AS (
  SELECT
    s.*,
    a.stars                AS a2a_stars,
    a.forks                AS a2a_forks,
    a.signal_strength      AS a2a_signal_strength,
    a.last_pushed_at       AS a2a_last_pushed_at,
    a.topics               AS a2a_topics,
    v.interactions_count   AS av_interactions,
    v.rating               AS av_rating,
    v.uptime_pct           AS av_uptime_pct,
    v.is_active            AS av_is_active,
    v.protocols            AS av_protocols,
    v.last_seen_at         AS av_last_seen_at
  FROM service_agents s
  LEFT JOIN public.a2a_agents a       ON a.repo_full_name = s.github_full_name
  LEFT JOIN public.agentverse_agents v ON v.agentverse_id  = s.agentverse_id
),
sub_scores AS (
  SELECT
    j.*,

    GREATEST(
      CASE WHEN a2a_stars IS NULL OR a2a_stars <= 0 THEN 0
           ELSE LEAST(100, ROUND(LOG(10, GREATEST(1, a2a_stars::numeric)) * 18))::INTEGER
      END,
      CASE WHEN av_interactions IS NULL OR av_interactions <= 0 THEN 0
           ELSE LEAST(100, ROUND(LOG(10, GREATEST(1, av_interactions::numeric)) * 22))::INTEGER
      END
    ) AS adoption_score_raw,

    GREATEST(
      COALESCE(a2a_signal_strength, 0),
      COALESCE(ROUND(av_rating * 20)::INTEGER, 0)
    ) AS source_quality_score_raw,

    CASE
      WHEN GREATEST(a2a_last_pushed_at, av_last_seen_at) > NOW() - INTERVAL '7 days'   THEN 100
      WHEN GREATEST(a2a_last_pushed_at, av_last_seen_at) > NOW() - INTERVAL '30 days'  THEN 80
      WHEN GREATEST(a2a_last_pushed_at, av_last_seen_at) > NOW() - INTERVAL '90 days'  THEN 60
      WHEN GREATEST(a2a_last_pushed_at, av_last_seen_at) > NOW() - INTERVAL '180 days' THEN 40
      WHEN GREATEST(a2a_last_pushed_at, av_last_seen_at) > NOW() - INTERVAL '365 days' THEN 20
      ELSE NULL::INTEGER
    END AS activity_score_raw,

    GREATEST(
      LEAST(100, COALESCE(jsonb_array_length(CASE WHEN jsonb_typeof(a2a_topics) = 'array' THEN a2a_topics ELSE '[]'::jsonb END), 0) * 25),
      LEAST(100, COALESCE(jsonb_array_length(CASE WHEN jsonb_typeof(av_protocols) = 'array' THEN av_protocols ELSE '[]'::jsonb END), 0) * 25)
    ) AS protocol_breadth_score_raw,

    -- NEW v1.1: Forks score — replaces cross-protocol placeholder
    CASE WHEN a2a_forks IS NULL OR a2a_forks <= 0 THEN NULL::INTEGER
         ELSE LEAST(100, ROUND(LOG(10, GREATEST(1, a2a_forks::numeric)) * 22))::INTEGER
    END AS forks_score_raw,

    CASE
      WHEN socially_visible IS NULL THEN NULL::INTEGER
      WHEN socially_visible = TRUE THEN 100
      ELSE NULL::INTEGER
    END AS social_score_raw

  FROM joined j
)
SELECT
  id                                                  AS agent_id,
  handle,
  display_name,
  primary_category,
  secondary_categories,
  github_full_name,
  github_repo_url,
  agentverse_id,

  a2a_stars,
  a2a_forks,
  a2a_signal_strength,
  a2a_last_pushed_at,
  av_interactions,
  av_rating,
  av_uptime_pct,
  av_is_active,
  av_protocols,
  av_last_seen_at,

  NULLIF(adoption_score_raw, 0)         AS adoption_score,
  NULLIF(source_quality_score_raw, 0)   AS source_quality_score,
  activity_score_raw                    AS activity_score,
  NULLIF(protocol_breadth_score_raw, 0) AS protocol_breadth_score,
  forks_score_raw                       AS forks_score,
  social_score_raw                      AS social_score,

  -- Composite (weights: adoption 25, quality 20, recency 15, protocols 15, forks 15, social 10)
  ROUND(
    COALESCE(NULLIF(adoption_score_raw, 0), 0)         * 0.25 +
    COALESCE(NULLIF(source_quality_score_raw, 0), 0)   * 0.20 +
    COALESCE(activity_score_raw, 0)                    * 0.15 +
    COALESCE(NULLIF(protocol_breadth_score_raw, 0), 0) * 0.15 +
    COALESCE(forks_score_raw, 0)                       * 0.15 +
    COALESCE(social_score_raw, 0)                      * 0.10
  )::INTEGER AS service_score,

  RANK() OVER (
    ORDER BY (
      COALESCE(NULLIF(adoption_score_raw, 0), 0)         * 0.25 +
      COALESCE(NULLIF(source_quality_score_raw, 0), 0)   * 0.20 +
      COALESCE(activity_score_raw, 0)                    * 0.15 +
      COALESCE(NULLIF(protocol_breadth_score_raw, 0), 0) * 0.15 +
      COALESCE(forks_score_raw, 0)                       * 0.15 +
      COALESCE(social_score_raw, 0)                      * 0.10
    ) DESC
  )::INTEGER AS rank_in_service,

  (
    (CASE WHEN NULLIF(adoption_score_raw, 0)         IS NOT NULL THEN 1 ELSE 0 END) +
    (CASE WHEN NULLIF(source_quality_score_raw, 0)   IS NOT NULL THEN 1 ELSE 0 END) +
    (CASE WHEN activity_score_raw                    IS NOT NULL THEN 1 ELSE 0 END) +
    (CASE WHEN NULLIF(protocol_breadth_score_raw, 0) IS NOT NULL THEN 1 ELSE 0 END) +
    (CASE WHEN forks_score_raw                       IS NOT NULL THEN 1 ELSE 0 END) +
    (CASE WHEN social_score_raw                      IS NOT NULL THEN 1 ELSE 0 END)
  )::INTEGER AS signals_available_count,

  (
    (
      (CASE WHEN NULLIF(adoption_score_raw, 0)         IS NOT NULL THEN 1 ELSE 0 END) +
      (CASE WHEN NULLIF(source_quality_score_raw, 0)   IS NOT NULL THEN 1 ELSE 0 END) +
      (CASE WHEN activity_score_raw                    IS NOT NULL THEN 1 ELSE 0 END) +
      (CASE WHEN NULLIF(protocol_breadth_score_raw, 0) IS NOT NULL THEN 1 ELSE 0 END) +
      (CASE WHEN forks_score_raw                       IS NOT NULL THEN 1 ELSE 0 END) +
      (CASE WHEN social_score_raw                      IS NOT NULL THEN 1 ELSE 0 END)
    ) >= 3
    AND (
      (a2a_stars IS NOT NULL AND a2a_stars > 0)
      OR (av_interactions IS NOT NULL AND av_interactions > 0)
      OR (a2a_forks IS NOT NULL AND a2a_forks > 0)
    )
  ) AS evidence_ready_for_public_rank,

  'v1.1-service-forks'::TEXT AS methodology_version

FROM sub_scores;

-- ── 4. Scoring view — joins agents + latest snapshot + mcp_server_metadata ───
-- agent_snapshots has: agent_id (FK to agents.id), github_stars, github_forks,
-- follower_count, score, rank. We take the most recent snapshot per agent.
CREATE OR REPLACE VIEW agent_score_mcp_server_v1 AS
WITH latest_snap AS (
  SELECT DISTINCT ON (agent_id)
    agent_id,
    github_stars,
    github_forks,
    follower_count,
    score AS snapshot_score,
    rank  AS snapshot_rank,
    created_at AS snapped_at
  FROM agent_snapshots
  ORDER BY agent_id, created_at DESC
)
SELECT
  a.handle,
  a.display_name                         AS name,
  a.bio                                  AS description,
  a.primary_category                     AS category,
  a.tier,
  COALESCE(ls.github_stars, 0)           AS github_stars,
  COALESCE(ls.github_forks, 0)           AS github_forks,
  COALESCE(ls.follower_count, 0)         AS follower_count,
  m.tool_count,
  m.registry_listings,
  m.transport_types,
  m.npx_install,
  -- Score: stars 30% + tool_count 25% + registry_count 20% + forks 15% + followers 10%
  ROUND(
    (LEAST(COALESCE(ls.github_stars,   0), 50000)::NUMERIC / 50000) * 30 +
    (LEAST(COALESCE(m.tool_count,      0), 30   )::NUMERIC / 30   ) * 25 +
    (LEAST(COALESCE(ARRAY_LENGTH(m.registry_listings, 1), 0), 5)::NUMERIC / 5) * 20 +
    (LEAST(COALESCE(ls.github_forks,   0), 5000 )::NUMERIC / 5000 ) * 15 +
    (LEAST(COALESCE(ls.follower_count, 0), 100000)::NUMERIC / 100000) * 10
  , 1) AS score,
  a.website_url                          AS homepage_url,
  ls.snapped_at                          AS updated_at
FROM agents a
LEFT JOIN latest_snap ls ON ls.agent_id = a.id
LEFT JOIN mcp_server_metadata m ON m.handle = a.handle
WHERE a.primary_category = 'mcp_server'
ORDER BY score DESC NULLS LAST;

CREATE OR REPLACE VIEW public.agent_score_model_family_v1_with_confidence AS
SELECT
  base.*,
  -- Model-family has 5 total signal slots: HF, derivatives, LMArena, citations, deployment
  public.score_confidence_tier(base.signals_available_count, 5) AS confidence_tier
FROM public.agent_score_model_family_v1 base;

CREATE OR REPLACE VIEW).

-- ── Helper function: build a JSONB risk-flag entry ────────────────────────────

CREATE OR REPLACE FUNCTION public.build_risk_flag(
  flag_code    TEXT,
  flag_label   TEXT,
  severity     TEXT,  -- 'high' | 'medium' | 'low'
  detail       TEXT
) RETURNS JSONB
LANGUAGE SQL
IMMUTABLE PARALLEL SAFE
AS $$
  SELECT jsonb_build_object(
    'code',     flag_code,
    'label',    flag_label,
    'severity', severity,
    'detail',   detail
  );