Skip to content

Open-source agentic AI Search Execution Platform. Track AI visibility, find opportunities, and let agents turn signals into prioritized actions and drafted fixes across ChatGPT, Claude, Gemini, Google AI Overviews, Google AI Mode, Perplexity, Grok, and Microsoft Copilot.

70.6 KB
Raw Blame
-- ============================================================================
-- 00164: topic-filtered Insights reads from the prompt-grain rollups (#862)
-- ============================================================================
-- Every Insights read that filters by topic still scanned raw prompt_results:
-- the brand-grain rollups have no prompt dimension, so dailyWindow() sent any
-- call with a topic to the raw RPCs. On the largest topic (91 prompts, 35k
-- answers) competitor_aggregates took 6-12 s for 30 days as a signed-in
-- member, and the topic detail page's all-time reads 18 s and 11 s under the
-- service role it falls back to. Most of the cost is unpacking every answer's
-- competitor_mentions jsonb.
--
-- The prompt-grain tables already carry most of the measures (00130, 00131).
-- This adds the rest, and every *_daily read the Insights page and the topic
-- detail page use takes p_topic_id. With a topic they read the prompt-grain
-- tables, through prompts.topic_id at read time, so a prompt moved to
-- another topic still takes its history along — the reason topic filters
-- stayed on the raw path. Without one they read what they read before:
-- insights_brand_daily also counts answers with no prompt, which the
-- prompt-grain tables leave out.
--
--   * insights_prompt_daily gains sum_visibility, sum_visibility_visible and
--     max_created_at; insights_competitor_prompt_daily (the sightings: one
--     row per competitor, prompt and engine-day where the competitor was
--     mentioned, cited or scored) gains sum_visibility and total_citations.
--     An answer element that isn't a sighting contributes zero to all of
--     them, so the sightings sum to what the raw RPCs compute. Nullable with
--     no default, like 00130's: a row not rewritten since reads NULL, not a
--     plausible zero. Run backfill-insights-daily.js before the web deploy.
--   * The one measure sightings can't give is a competitor's answer count:
--     every answer lists every tracked competitor, so for a topic it is the
--     topic's answer count on that engine, and every live competitor is
--     listed, sighted or not. A competitor added partway through the window
--     is counted against the whole window's answers, an accepted drift.
--   * (brand_id, prompt_id, day) on both prompt-grain tables, so a topic read
--     follows the topic's rows rather than the brand's.
--   * refresh_insights_daily (00162's body) takes a per-brand transaction
--     lock: two overlapping refreshes of the same brand-day (a completed run
--     and the nightly sweep, or a backfill chunk) each delete before the
--     other's insert commits, and the day's rows land twice (#1020). Its
--     position measures skip positions that aren't positive, so a stray 0
--     can't divide by zero and abort the brand's refresh; positions are
--     1-based (response-parser.js), so real data is unchanged.
--   * The seven aggregate reads (00066, 00112, 00113, 00131) and the three
--     comparison reads (00133, 00148, 00149) gain a parameter. CREATE OR
--     REPLACE would add it as a second overload, and PostgREST would find
--     every call that omits it ambiguous, so the old signatures are dropped
--     first. Recreating them resets their settings, so each keeps its own:
--     competitor_aggregates_daily 00113's plpgsql, work_mem and
--     plan_cache_mode, visibility_rate_trend_daily 00112's materialized CTEs
--     and work_mem, and the comparison reads their security definer and
--     explicit organization check (00147, 00148).
--
-- Designed and first written by @arambu1a in #867; rebuilt here on the
-- functions as main has them now.

-- ─── Tables ─────────────────────────────────────────────────────────────────

alter table public.insights_prompt_daily
  add column if not exists sum_visibility         numeric,
  add column if not exists sum_visibility_visible numeric,
  add column if not exists max_created_at         timestamptz;

comment on column public.insights_prompt_daily.max_created_at is
  'Latest answer in the row (00164). NULL until refresh_insights_daily rewrites the day.';

alter table public.insights_competitor_prompt_daily
  add column if not exists sum_visibility  numeric,
  add column if not exists total_citations bigint;

comment on column public.insights_competitor_prompt_daily.sum_visibility is
  'Sum of the competitor''s visibility_score over the sighting''s answers (00164). NULL until refresh_insights_daily rewrites the day.';

-- On a large table, build these by hand first with `create index
-- concurrently`, which doesn't hold back tracking's writes; these statements
-- then find them in place.
create index if not exists idx_insights_prompt_daily_brand_prompt_day
  on public.insights_prompt_daily (brand_id, prompt_id, day);

create index if not exists idx_insights_competitor_prompt_daily_brand_prompt_day
  on public.insights_competitor_prompt_daily (brand_id, prompt_id, day);

-- ─── Refresh: 00162 plus the lock and the new columns ───────────────────────

create or replace function public.refresh_insights_daily(
  p_brand_id uuid,
  p_day_from date,
  p_day_to date
) returns void
language plpgsql
set search_path to 'public'
as $$
declare
  -- The range's bounds as instants, so the answers are found through
  -- (brand_id, created_at) rather than by reading all of the brand's.
  v_from timestamptz := p_day_from::timestamp at time zone 'utc';
  v_to   timestamptz := (p_day_to + 1)::timestamp at time zone 'utc';
begin
  -- One refresh per brand at a time; the lock releases at commit.
  perform pg_advisory_xact_lock(
    hashtextextended('refresh_insights_daily:' || p_brand_id::text, 0));

  delete from public.insights_brand_daily
    where brand_id = p_brand_id and day between p_day_from and p_day_to;
  delete from public.insights_competitor_daily
    where brand_id = p_brand_id and day between p_day_from and p_day_to;
  delete from public.insights_prompt_daily
    where brand_id = p_brand_id and day between p_day_from and p_day_to;
  delete from public.insights_competitor_prompt_daily
    where brand_id = p_brand_id and day between p_day_from and p_day_to;

  insert into public.insights_brand_daily (
    brand_id, day, model_used, platform, region,
    answer_count, mention_answers, citation_answers, mentioning_answers,
    sum_visibility, sum_visibility_visible, total_mentions, total_citations,
    positive_count, sum_inv_position, position_count, max_created_at,
    negative_count, sum_position)
  select
    p_brand_id,
    (pr.created_at at time zone 'utc')::date,
    pr.model_used, pr.platform, pr.region,
    count(*),
    count(*) filter (where pr.mention_count > 0),
    count(*) filter (where pr.citation_count > 0),
    count(*) filter (where pr.mention_count > 0 or pr.citation_count > 0),
    coalesce(sum(pr.visibility_score), 0),
    coalesce(sum(pr.visibility_score)
      filter (where pr.mention_count > 0 or pr.citation_count > 0), 0),
    coalesce(sum(pr.mention_count), 0),
    coalesce(sum(pr.citation_count), 0),
    count(*) filter (where pr.sentiment = 'positive'),
    sum(1.0 / pr.mention_position) filter (where pr.mention_position > 0),
    count(*) filter (where pr.mention_position > 0),
    max(pr.created_at),
    count(*) filter (where pr.sentiment = 'negative'),
    sum(pr.mention_position) filter (where pr.mention_position > 0)
  from public.prompt_results pr
  where pr.brand_id = p_brand_id
    and pr.platform <> 'chatgpt-shopping'  -- #155 — isolate from Insights
    and pr.created_at >= v_from
    and pr.created_at < v_to
  group by 2, pr.model_used, pr.platform, pr.region;

  insert into public.insights_prompt_daily (
    brand_id, day, prompt_id, model_used, platform, region,
    answer_count, has_mention, has_citation,
    mention_answers, citation_answers, mentioning_answers, positive_count,
    total_mentions, total_citations, sum_inv_position, position_count,
    negative_count, sum_position,
    sum_visibility, sum_visibility_visible, max_created_at)
  select
    p_brand_id,
    (pr.created_at at time zone 'utc')::date,
    pr.prompt_id, pr.model_used, pr.platform, pr.region,
    count(*),
    bool_or(pr.mention_count > 0),
    bool_or(pr.citation_count > 0),
    count(*) filter (where pr.mention_count > 0),
    count(*) filter (where pr.citation_count > 0),
    count(*) filter (where pr.mention_count > 0 or pr.citation_count > 0),
    count(*) filter (where pr.sentiment = 'positive'),
    coalesce(sum(pr.mention_count), 0),
    coalesce(sum(pr.citation_count), 0),
    sum(1.0 / pr.mention_position) filter (where pr.mention_position > 0),
    count(*) filter (where pr.mention_position > 0),
    count(*) filter (where pr.sentiment = 'negative'),
    sum(pr.mention_position) filter (where pr.mention_position > 0),
    coalesce(sum(pr.visibility_score), 0),
    coalesce(sum(pr.visibility_score)
      filter (where pr.mention_count > 0 or pr.citation_count > 0), 0),
    max(pr.created_at)
  from public.prompt_results pr
  where pr.brand_id = p_brand_id
    and pr.prompt_id is not null
    and pr.platform <> 'chatgpt-shopping'
    and pr.created_at >= v_from
    and pr.created_at < v_to
  group by 2, pr.prompt_id, pr.model_used, pr.platform, pr.region;

  insert into public.insights_competitor_daily (
    brand_id, day, competitor_id, model_used, platform, region,
    answer_count, sum_visibility, total_mentions, total_citations,
    mention_answers, citation_answers, sum_inv_position, position_count)
  select
    p_brand_id,
    (pr.created_at at time zone 'utc')::date,
    cm.value->>'competitor_id',
    pr.model_used, pr.platform, pr.region,
    count(*),
    sum((cm.value->>'visibility_score')::numeric),
    coalesce(sum(coalesce((cm.value->>'mention_count')::int, 0)), 0),
    coalesce(sum(coalesce((cm.value->>'citation_count')::int, 0)), 0),
    count(*) filter (where coalesce((cm.value->>'mention_count')::int, 0) > 0),
    count(*) filter (where coalesce((cm.value->>'citation_count')::int, 0) > 0),
    sum(1.0 / (cm.value->>'mention_position')::numeric)
      filter (where (cm.value->>'mention_position')::numeric > 0),
    count(*) filter (where (cm.value->>'mention_position')::numeric > 0)
  from public.prompt_results pr,
       lateral jsonb_array_elements(coalesce(pr.competitor_mentions, '[]'::jsonb)) cm
  where pr.brand_id = p_brand_id
    and pr.platform <> 'chatgpt-shopping'
    and pr.created_at >= v_from
    and pr.created_at < v_to
    and cm.value ? 'competitor_id'
  group by 2, cm.value->>'competitor_id', pr.model_used, pr.platform, pr.region;

  -- Sightings only, as in 00098; the measures follow insights_competitor_daily.
  insert into public.insights_competitor_prompt_daily (
    brand_id, day, competitor_id, prompt_id, model_used, platform, region, mention_count,
    mention_answers, citation_answers, sum_inv_position, position_count,
    sum_visibility, total_citations)
  select
    p_brand_id,
    (pr.created_at at time zone 'utc')::date,
    cm.value->>'competitor_id',
    pr.prompt_id, pr.model_used, pr.platform, pr.region,
    sum(coalesce((cm.value->>'mention_count')::int, 0)),
    count(*) filter (where coalesce((cm.value->>'mention_count')::int, 0) > 0),
    count(*) filter (where coalesce((cm.value->>'citation_count')::int, 0) > 0),
    sum(1.0 / (cm.value->>'mention_position')::numeric)
      filter (where (cm.value->>'mention_position')::numeric > 0),
    count(*) filter (where (cm.value->>'mention_position')::numeric > 0),
    sum((cm.value->>'visibility_score')::numeric),
    coalesce(sum(coalesce((cm.value->>'citation_count')::int, 0)), 0)
  from public.prompt_results pr,
       lateral jsonb_array_elements(coalesce(pr.competitor_mentions, '[]'::jsonb)) cm
  where pr.brand_id = p_brand_id
    and pr.prompt_id is not null
    and pr.platform <> 'chatgpt-shopping'
    and pr.created_at >= v_from
    and pr.created_at < v_to
    and cm.value ? 'competitor_id'
    and (coalesce((cm.value->>'mention_count')::int, 0) > 0
      or coalesce((cm.value->>'citation_count')::int, 0) > 0
      or coalesce((cm.value->>'visibility_score')::numeric, 0) > 0)
  group by 2, cm.value->>'competitor_id', pr.prompt_id, pr.model_used, pr.platform, pr.region;
end;
$$;

revoke execute on function public.refresh_insights_daily(uuid, date, date)
  from public, anon, authenticated;

-- ─── Read functions ─────────────────────────────────────────────────────────
--
-- Payloads are unchanged. Each source the function reads becomes a pair of
-- branches gated on p_topic_id: the brand-grain table when it is null (the
-- read is then exactly what it was), the prompt-grain table restricted to the
-- topic's current prompts when it is set. The gate references only a
-- parameter, so the planner turns it into a one-time filter and the unused
-- branch is never scanned.
--
-- The gate must stay a branch of its own: folding it into one predicate
-- ("p_topic_id is null or prompt_id = any (...)") stops the planner from
-- using the (brand_id, prompt_id, day) index and scans the whole brand.
--
-- The topic's prompts are resolved once per call (topic_prompts), the same
-- set the raw RPCs' EXISTS (prompts.topic_id = p_topic_id) admits.
--
-- Competitor measures for a topic come from the sightings. Where a function
-- lists competitors, it also lists the live competitors the topic's answers
-- never sighted (with zero measures), as the raw RPCs do: each answer lists
-- every tracked competitor. Each function folds competitor rows to the grain
-- it reports at before joining competitors.

drop function if exists public.insights_aggregates_daily(uuid, text, text[], text, date, date);
drop function if exists public.visible_prompt_stats_daily(uuid, text, text[], text, date, date);
drop function if exists public.tracked_prompt_count_daily(uuid, text, text[], text, date, date);
drop function if exists public.ai_visibility_aggregates_daily(uuid, text, text[], text, date, date);
drop function if exists public.competitor_aggregates_daily(uuid, text, text[], text, date, date);
drop function if exists public.share_of_voice_aggregates_daily(uuid, text, text[], text, date, date);
drop function if exists public.visibility_rate_trend_daily(uuid, text, text[], text, date, date);
-- And the new ones, so this file can be run again.
drop function if exists public.insights_aggregates_daily(uuid, text, text[], text, date, date, uuid);
drop function if exists public.visible_prompt_stats_daily(uuid, text, text[], text, date, date, uuid);
drop function if exists public.tracked_prompt_count_daily(uuid, text, text[], text, date, date, uuid);
drop function if exists public.ai_visibility_aggregates_daily(uuid, text, text[], text, date, date, uuid);
drop function if exists public.competitor_aggregates_daily(uuid, text, text[], text, date, date, uuid);
drop function if exists public.share_of_voice_aggregates_daily(uuid, text, text[], text, date, date, uuid);
drop function if exists public.visibility_rate_trend_daily(uuid, text, text[], text, date, date, uuid);


-- insights_aggregates over rollups, with 00131's citation, negative and
-- position totals.
create function public.insights_aggregates_daily(
  p_brand_id uuid,
  p_platform text default null,
  p_models text[] default null,
  p_region text default null,
  p_day_from date default null,
  p_day_to date default null,
  p_topic_id uuid default null
) returns jsonb
language sql
stable
set search_path to 'public'
as $$
  with topic_prompts as (
    select array(select p.id from public.prompts p where p.topic_id = p_topic_id) as ids
  ),
  filtered as (
    select d.model_used, d.answer_count, d.sum_visibility, d.total_mentions,
           d.total_citations, d.positive_count, d.mentioning_answers, d.max_created_at,
           d.citation_answers, d.negative_count, d.sum_position, d.position_count
    from public.insights_brand_daily d
    where p_topic_id is null
      and d.brand_id = p_brand_id
      and (p_platform is null or d.platform = p_platform)
      and (p_models is null or d.model_used = any (p_models))
      and (p_region is null or d.region = p_region)
      and (p_day_from is null or d.day >= p_day_from)
      and (p_day_to is null or d.day <= p_day_to)
    union all
    select d.model_used, d.answer_count, d.sum_visibility, d.total_mentions,
           d.total_citations, d.positive_count, d.mentioning_answers, d.max_created_at,
           d.citation_answers, d.negative_count, d.sum_position, d.position_count
    from public.insights_prompt_daily d, topic_prompts tp
    where p_topic_id is not null
      and d.brand_id = p_brand_id
      and d.prompt_id = any (tp.ids)
      and (p_platform is null or d.platform = p_platform)
      and (p_models is null or d.model_used = any (p_models))
      and (p_region is null or d.region = p_region)
      and (p_day_from is null or d.day >= p_day_from)
      and (p_day_to is null or d.day <= p_day_to)
  ),
  totals as (
    select
      coalesce(sum(answer_count), 0)       as total_results,
      coalesce(sum(sum_visibility), 0)     as sum_visibility,
      coalesce(sum(total_mentions), 0)     as total_mentions,
      coalesce(sum(total_citations), 0)    as total_citations,
      coalesce(sum(positive_count), 0)     as positive_count,
      coalesce(sum(mentioning_answers), 0) as mentioning_results,
      max(max_created_at)                  as last_checked_at,
      coalesce(sum(citation_answers), 0)   as citation_answers,
      coalesce(sum(negative_count), 0)     as negative_count,
      sum(sum_position)                    as sum_position,
      coalesce(sum(position_count), 0)     as position_count,
      -- A day not rewritten since 00131 has no negative or position totals.
      not exists (select 1 from filtered where negative_count is null) as kpi_complete
    from filtered
  ),
  by_model as (
    select
      coalesce(model_used, 'unknown') as model_used,
      sum(sum_visibility)             as sum_visibility,
      sum(answer_count)               as result_count
    from filtered
    group by coalesce(model_used, 'unknown')
  )
  select jsonb_build_object(
    'total_results',      t.total_results,
    'sum_visibility',     t.sum_visibility,
    'total_mentions',     t.total_mentions,
    'total_citations',    t.total_citations,
    'positive_count',     t.positive_count,
    'mentioning_results', t.mentioning_results,
    'last_checked_at',    t.last_checked_at,
    'citation_answers',   t.citation_answers,
    'negative_count',     t.negative_count,
    'sum_position',       t.sum_position,
    'position_count',     t.position_count,
    'kpi_complete',       t.kpi_complete,
    'by_model', coalesce(
      (select jsonb_agg(jsonb_build_object(
                'model_used',     bm.model_used,
                'sum_visibility', bm.sum_visibility,
                'result_count',   bm.result_count)
              order by bm.result_count desc, bm.model_used)
       from by_model bm),
      '[]'::jsonb)
  )
  from totals t;
$$;

-- visible_prompt_stats over rollups.
create function public.visible_prompt_stats_daily(
  p_brand_id uuid,
  p_platform text default null,
  p_models text[] default null,
  p_region text default null,
  p_day_from date default null,
  p_day_to date default null,
  p_topic_id uuid default null
) returns jsonb
language sql
stable
set search_path to 'public'
as $$
  with topic_prompts as (
    select array(select p.id from public.prompts p where p.topic_id = p_topic_id) as ids
  ),
  brand_rows as (
    select bd.mentioning_answers, bd.sum_visibility_visible
    from public.insights_brand_daily bd
    where p_topic_id is null
      and bd.brand_id = p_brand_id
      and (p_platform is null or bd.platform = p_platform)
      and (p_models is null or bd.model_used = any (p_models))
      and (p_region is null or bd.region = p_region)
      and (p_day_from is null or bd.day >= p_day_from)
      and (p_day_to is null or bd.day <= p_day_to)
    union all
    select pd.mentioning_answers, pd.sum_visibility_visible
    from public.insights_prompt_daily pd, topic_prompts tp
    where p_topic_id is not null
      and pd.brand_id = p_brand_id
      and pd.prompt_id = any (tp.ids)
      and (p_platform is null or pd.platform = p_platform)
      and (p_models is null or pd.model_used = any (p_models))
      and (p_region is null or pd.region = p_region)
      and (p_day_from is null or pd.day >= p_day_from)
      and (p_day_to is null or pd.day <= p_day_to)
  ),
  visible_rows as (
    select pd.prompt_id
    from public.insights_prompt_daily pd
    where p_topic_id is null
      and pd.brand_id = p_brand_id
      and (pd.has_mention or pd.has_citation)
      and (p_platform is null or pd.platform = p_platform)
      and (p_models is null or pd.model_used = any (p_models))
      and (p_region is null or pd.region = p_region)
      and (p_day_from is null or pd.day >= p_day_from)
      and (p_day_to is null or pd.day <= p_day_to)
    union all
    select pd.prompt_id
    from public.insights_prompt_daily pd, topic_prompts tp
    where p_topic_id is not null
      and pd.brand_id = p_brand_id
      and pd.prompt_id = any (tp.ids)
      and (pd.has_mention or pd.has_citation)
      and (p_platform is null or pd.platform = p_platform)
      and (p_models is null or pd.model_used = any (p_models))
      and (p_region is null or pd.region = p_region)
      and (p_day_from is null or pd.day >= p_day_from)
      and (p_day_to is null or pd.day <= p_day_to)
  )
  select jsonb_build_object(
    'visible_prompts',
      (select count(distinct prompt_id) from visible_rows),
    'visible_results',
      coalesce((select sum(mentioning_answers) from brand_rows), 0),
    'sum_visibility_visible',
      coalesce((select sum(sum_visibility_visible) from brand_rows), 0)
  );
$$;

-- tracked_prompt_count over rollups.
create function public.tracked_prompt_count_daily(
  p_brand_id uuid,
  p_platform text default null,
  p_models text[] default null,
  p_region text default null,
  p_day_from date default null,
  p_day_to date default null,
  p_topic_id uuid default null
) returns integer
language sql
stable
set search_path to 'public'
as $$
  with topic_prompts as (
    select array(select p.id from public.prompts p where p.topic_id = p_topic_id) as ids
  )
  select count(distinct r.prompt_id)::integer
  from (
    select pd.prompt_id
    from public.insights_prompt_daily pd
    where p_topic_id is null
      and pd.brand_id = p_brand_id
      and (p_platform is null or pd.platform = p_platform)
      and (p_models is null or pd.model_used = any (p_models))
      and (p_region is null or pd.region = p_region)
      and (p_day_from is null or pd.day >= p_day_from)
      and (p_day_to is null or pd.day <= p_day_to)
    union all
    select pd.prompt_id
    from public.insights_prompt_daily pd, topic_prompts tp
    where p_topic_id is not null
      and pd.brand_id = p_brand_id
      and pd.prompt_id = any (tp.ids)
      and (p_platform is null or pd.platform = p_platform)
      and (p_models is null or pd.model_used = any (p_models))
      and (p_region is null or pd.region = p_region)
      and (p_day_from is null or pd.day >= p_day_from)
      and (p_day_to is null or pd.day <= p_day_to)
  ) r
$$;

-- ai_visibility_aggregates over rollups. Liveness join preserved; it runs on
-- the per-competitor totals rather than per row. For a topic, a live
-- competitor with no sightings gets a zero row once the topic has answers.
create function public.ai_visibility_aggregates_daily(
  p_brand_id uuid,
  p_platform text default null,
  p_models text[] default null,
  p_region text default null,
  p_day_from date default null,
  p_day_to date default null,
  p_topic_id uuid default null
) returns jsonb
language sql
stable
set search_path to 'public'
as $$
  with topic_prompts as (
    select array(select p.id from public.prompts p where p.topic_id = p_topic_id) as ids
  ),
  brand_rows as (
    select d.answer_count, d.mention_answers, d.citation_answers,
           d.sum_inv_position, d.position_count
    from public.insights_brand_daily d
    where p_topic_id is null
      and d.brand_id = p_brand_id
      and (p_platform is null or d.platform = p_platform)
      and (p_models is null or d.model_used = any (p_models))
      and (p_region is null or d.region = p_region)
      and (p_day_from is null or d.day >= p_day_from)
      and (p_day_to is null or d.day <= p_day_to)
    union all
    select d.answer_count, d.mention_answers, d.citation_answers,
           d.sum_inv_position, d.position_count
    from public.insights_prompt_daily d, topic_prompts tp
    where p_topic_id is not null
      and d.brand_id = p_brand_id
      and d.prompt_id = any (tp.ids)
      and (p_platform is null or d.platform = p_platform)
      and (p_models is null or d.model_used = any (p_models))
      and (p_region is null or d.region = p_region)
      and (p_day_from is null or d.day >= p_day_from)
      and (p_day_to is null or d.day <= p_day_to)
  ),
  comp_rows as (
    select cd.competitor_id, cd.mention_answers, cd.citation_answers,
           cd.sum_inv_position, cd.position_count
    from public.insights_competitor_daily cd
    where p_topic_id is null
      and cd.brand_id = p_brand_id
      and (p_platform is null or cd.platform = p_platform)
      and (p_models is null or cd.model_used = any (p_models))
      and (p_region is null or cd.region = p_region)
      and (p_day_from is null or cd.day >= p_day_from)
      and (p_day_to is null or cd.day <= p_day_to)
    union all
    select cpd.competitor_id, cpd.mention_answers, cpd.citation_answers,
           cpd.sum_inv_position, cpd.position_count
    from public.insights_competitor_prompt_daily cpd, topic_prompts tp
    where p_topic_id is not null
      and cpd.brand_id = p_brand_id
      and cpd.prompt_id = any (tp.ids)
      and (p_platform is null or cpd.platform = p_platform)
      and (p_models is null or cpd.model_used = any (p_models))
      and (p_region is null or cpd.region = p_region)
      and (p_day_from is null or cpd.day >= p_day_from)
      and (p_day_to is null or cpd.day <= p_day_to)
    union all
    select c.id::text, 0, 0, null::numeric, 0
    from public.competitors c
    where p_topic_id is not null
      and c.brand_id = p_brand_id
      and exists (select 1 from brand_rows)
  ),
  brand_agg as (
    select
      coalesce(sum(answer_count), 0)    as answers,
      coalesce(sum(mention_answers), 0) as mention_answers,
      coalesce(sum(citation_answers), 0) as citation_answers,
      sum(sum_inv_position) / nullif(sum(position_count), 0) as position_factor
    from brand_rows
  ),
  comp_totals as (
    select competitor_id,
           sum(mention_answers)  as mention_answers,
           sum(citation_answers) as citation_answers,
           sum(sum_inv_position) as sum_inv_position,
           sum(position_count)   as position_count
    from comp_rows
    group by competitor_id
  ),
  comp_agg as (
    select ct.competitor_id, c.name, ct.mention_answers, ct.citation_answers,
           ct.sum_inv_position / nullif(ct.position_count, 0) as position_factor
    from comp_totals ct
    join public.competitors c
      on c.id::text = ct.competitor_id and c.brand_id = p_brand_id
  )
  select jsonb_build_object(
    'answers',          b.answers,
    'mention_answers',  b.mention_answers,
    'citation_answers', b.citation_answers,
    'position_factor',  b.position_factor,
    'by_competitor', coalesce(
      (select jsonb_agg(jsonb_build_object(
                'competitor_id',    ca.competitor_id,
                'name',             ca.name,
                'mention_answers',  ca.mention_answers,
                'citation_answers', ca.citation_answers,
                'position_factor',  ca.position_factor)
              order by ca.mention_answers desc, ca.competitor_id)
       from comp_agg ca),
      '[]'::jsonb)
  )
  from brand_agg b;
$$;

-- competitor_aggregates over rollups. Competitor measures fold to one row per
-- (competitor, engine) first, and the liveness join runs on that; the
-- visible-prompt sightings fold to distinct (competitor, engine, prompt).
-- Sightings of competitors that no longer exist need no join of their own:
-- they only ever attach to rows that passed the liveness join. Engine keys
-- join with IS NOT DISTINCT FROM because model_used can be null.
--
-- For a topic, every live competitor gets a row on each engine the topic has
-- answers on: row_count is that engine's answer count (each answer lists
-- every tracked competitor), the other measures are the sightings' sums.
--
-- plpgsql with force_custom_plan and 64 MB work_mem, as 00113 and 00110 set
-- them: planned with the argument values in hand, sorts kept in memory.
create function public.competitor_aggregates_daily(
  p_brand_id uuid,
  p_platform text default null,
  p_models text[] default null,
  p_region text default null,
  p_day_from date default null,
  p_day_to date default null,
  p_topic_id uuid default null
) returns jsonb
language plpgsql
stable
set search_path to 'public'
set work_mem to '64MB'
set plan_cache_mode to 'force_custom_plan'
as $$
begin
  return (
    with topic_prompts as (
      select array(select p.id from public.prompts p where p.topic_id = p_topic_id) as ids
    ),
    brand_days as (
      select d.model_used, d.platform, d.answer_count, d.sum_visibility,
             d.total_mentions, d.total_citations
      from public.insights_brand_daily d
      where p_topic_id is null
        and d.brand_id = p_brand_id
        and (p_platform is null or d.platform = p_platform)
        and (p_models is null or d.model_used = any (p_models))
        and (p_region is null or d.region = p_region)
        and (p_day_from is null or d.day >= p_day_from)
        and (p_day_to is null or d.day <= p_day_to)
      union all
      select d.model_used, d.platform, d.answer_count, d.sum_visibility,
             d.total_mentions, d.total_citations
      from public.insights_prompt_daily d, topic_prompts tp
      where p_topic_id is not null
        and d.brand_id = p_brand_id
        and d.prompt_id = any (tp.ids)
        and (p_platform is null or d.platform = p_platform)
        and (p_models is null or d.model_used = any (p_models))
        and (p_region is null or d.region = p_region)
        and (p_day_from is null or d.day >= p_day_from)
        and (p_day_to is null or d.day <= p_day_to)
    ),
    prompt_days as (
      select d.model_used, d.platform, d.prompt_id, d.has_mention, d.has_citation
      from public.insights_prompt_daily d
      where p_topic_id is null
        and d.brand_id = p_brand_id
        and (p_platform is null or d.platform = p_platform)
        and (p_models is null or d.model_used = any (p_models))
        and (p_region is null or d.region = p_region)
        and (p_day_from is null or d.day >= p_day_from)
        and (p_day_to is null or d.day <= p_day_to)
      union all
      select d.model_used, d.platform, d.prompt_id, d.has_mention, d.has_citation
      from public.insights_prompt_daily d, topic_prompts tp
      where p_topic_id is not null
        and d.brand_id = p_brand_id
        and d.prompt_id = any (tp.ids)
        and (p_platform is null or d.platform = p_platform)
        and (p_models is null or d.model_used = any (p_models))
        and (p_region is null or d.region = p_region)
        and (p_day_from is null or d.day >= p_day_from)
        and (p_day_to is null or d.day <= p_day_to)
    ),
    -- The topic's sightings, one row per (competitor, engine, prompt, day).
    topic_sightings as (
      select cpd.competitor_id, cpd.model_used, cpd.platform, cpd.prompt_id,
             cpd.sum_visibility, cpd.mention_count, cpd.total_citations
      from public.insights_competitor_prompt_daily cpd, topic_prompts tp
      where p_topic_id is not null
        and cpd.brand_id = p_brand_id
        and cpd.prompt_id = any (tp.ids)
        and (p_platform is null or cpd.platform = p_platform)
        and (p_models is null or cpd.model_used = any (p_models))
        and (p_region is null or cpd.region = p_region)
        and (p_day_from is null or cpd.day >= p_day_from)
        and (p_day_to is null or cpd.day <= p_day_to)
    ),
    comp_engine as (
      select cd.competitor_id, cd.model_used, cd.platform,
             sum(cd.sum_visibility)  as sum_visibility,
             sum(cd.answer_count)    as row_count,
             sum(cd.total_mentions)  as total_mentions,
             sum(cd.total_citations) as total_citations
      from public.insights_competitor_daily cd
      where p_topic_id is null
        and cd.brand_id = p_brand_id
        and (p_platform is null or cd.platform = p_platform)
        and (p_models is null or cd.model_used = any (p_models))
        and (p_region is null or cd.region = p_region)
        and (p_day_from is null or cd.day >= p_day_from)
        and (p_day_to is null or cd.day <= p_day_to)
      group by cd.competitor_id, cd.model_used, cd.platform
      union all
      select c.id::text, te.model_used, te.platform,
             coalesce(s.sum_visibility, 0),
             te.row_count,
             coalesce(s.total_mentions, 0),
             coalesce(s.total_citations, 0)
      from (
        select model_used, platform, sum(answer_count) as row_count
        from brand_days
        where p_topic_id is not null
        group by model_used, platform
      ) te
      cross join public.competitors c
      left join (
        select competitor_id, model_used, platform,
               sum(sum_visibility)  as sum_visibility,
               sum(mention_count)   as total_mentions,
               sum(total_citations) as total_citations
        from topic_sightings
        group by competitor_id, model_used, platform
      ) s on s.competitor_id = c.id::text
         and s.model_used is not distinct from te.model_used
         and s.platform is not distinct from te.platform
      where c.brand_id = p_brand_id
    ),
    live_engine as (
      select ce.*, c.name
      from comp_engine ce
      join public.competitors c
        on c.id::text = ce.competitor_id and c.brand_id = p_brand_id
    ),
    comp_sightings as (
      select distinct s.competitor_id, s.model_used, s.platform, s.prompt_id
      from (
        select cpd.competitor_id, cpd.model_used, cpd.platform, cpd.prompt_id
        from public.insights_competitor_prompt_daily cpd
        where p_topic_id is null
          and cpd.brand_id = p_brand_id
          and (p_platform is null or cpd.platform = p_platform)
          and (p_models is null or cpd.model_used = any (p_models))
          and (p_region is null or cpd.region = p_region)
          and (p_day_from is null or cpd.day >= p_day_from)
          and (p_day_to is null or cpd.day <= p_day_to)
        union all
        select ts.competitor_id, ts.model_used, ts.platform, ts.prompt_id
        from topic_sightings ts
      ) s
    ),
    brand_totals as (
      select
        coalesce((select sum(answer_count) from brand_days), 0)    as row_count,
        coalesce((select sum(sum_visibility) from brand_days), 0)  as sum_visibility,
        coalesce((select sum(total_mentions) from brand_days), 0)  as total_mentions,
        coalesce((select sum(total_citations) from brand_days), 0) as total_citations,
        (select count(distinct prompt_id) from prompt_days)        as prompt_count,
        (select count(distinct prompt_id) from prompt_days
          where has_mention or has_citation)                       as visible_prompts
    ),
    by_brand_provider as (
      select b.model_used, b.platform, b.sum_visibility, b.row_count,
             p.prompt_count, p.visible_prompts
      from (
        select model_used, platform,
               sum(sum_visibility) as sum_visibility,
               sum(answer_count)   as row_count
        from brand_days group by model_used, platform
      ) b
      join (
        select model_used, platform,
               count(distinct prompt_id) as prompt_count,
               count(distinct prompt_id)
                 filter (where has_mention or has_citation) as visible_prompts
        from prompt_days group by model_used, platform
      ) p on p.model_used is not distinct from b.model_used
         and p.platform is not distinct from b.platform
    ),
    visible_by_comp as (
      select competitor_id, count(distinct prompt_id) as visible_prompts
      from comp_sightings group by competitor_id
    ),
    visible_by_comp_engine as (
      select competitor_id, model_used, platform, count(*) as visible_prompts
      from comp_sightings group by competitor_id, model_used, platform
    ),
    by_competitor as (
      select
        le.competitor_id,
        max(le.name)              as name,
        sum(le.sum_visibility)    as sum_visibility,
        sum(le.row_count)         as row_count,
        coalesce(sum(le.total_mentions), 0)::bigint  as total_mentions,
        coalesce(sum(le.total_citations), 0)::bigint as total_citations,
        coalesce(max(v.visible_prompts), 0)          as visible_prompts
      from live_engine le
      left join visible_by_comp v on v.competitor_id = le.competitor_id
      group by le.competitor_id
    ),
    by_competitor_provider as (
      select
        le.model_used, le.platform, le.competitor_id,
        le.name                            as competitor_name,
        le.sum_visibility,
        le.row_count,
        coalesce(v.visible_prompts, 0)     as visible_prompts
      from live_engine le
      left join visible_by_comp_engine v
        on v.competitor_id = le.competitor_id
       and v.model_used is not distinct from le.model_used
       and v.platform is not distinct from le.platform
    )
    select jsonb_build_object(
      'brand_row_count',       b.row_count,
      'brand_sum_visibility',  b.sum_visibility,
      'brand_total_mentions',  b.total_mentions,
      'brand_total_citations', b.total_citations,
      'brand_prompt_count',    b.prompt_count,
      'brand_visible_prompts', b.visible_prompts,
      'by_competitor', coalesce(
        (select jsonb_agg(jsonb_build_object(
                  'competitor_id',   bc.competitor_id,
                  'name',            bc.name,
                  'sum_visibility',  bc.sum_visibility,
                  'row_count',       bc.row_count,
                  'total_mentions',  bc.total_mentions,
                  'total_citations', bc.total_citations,
                  'visible_prompts', bc.visible_prompts)
                order by bc.row_count desc, bc.competitor_id)
         from by_competitor bc),
        '[]'::jsonb),
      'by_brand_provider', coalesce(
        (select jsonb_agg(jsonb_build_object(
                  'model_used',      bbp.model_used,
                  'platform',        bbp.platform,
                  'sum_visibility',  bbp.sum_visibility,
                  'row_count',       bbp.row_count,
                  'prompt_count',    bbp.prompt_count,
                  'visible_prompts', bbp.visible_prompts)
                order by bbp.platform nulls last, bbp.model_used nulls last)
         from by_brand_provider bbp),
        '[]'::jsonb),
      'by_competitor_provider', coalesce(
        (select jsonb_agg(jsonb_build_object(
                  'model_used',      bcp.model_used,
                  'platform',        bcp.platform,
                  'competitor_id',   bcp.competitor_id,
                  'competitor_name', bcp.competitor_name,
                  'sum_visibility',  bcp.sum_visibility,
                  'row_count',       bcp.row_count,
                  'visible_prompts', bcp.visible_prompts)
                order by bcp.platform nulls last, bcp.model_used nulls last, bcp.competitor_id)
         from by_competitor_provider bcp),
        '[]'::jsonb)
    )
    from brand_totals b
  );
end;
$$;

-- share_of_voice_aggregates over rollups. No competitors join anywhere — the
-- raw RPC sums every jsonb element regardless of liveness (00066). For a
-- topic, competitor mentions are the sightings' mention_count: an element
-- with mentions is always a sighting. Both sides fold to (day, engine) once;
-- the totals, per-engine and per-day series are all read from those.
create function public.share_of_voice_aggregates_daily(
  p_brand_id uuid,
  p_platform text default null,
  p_models text[] default null,
  p_region text default null,
  p_day_from date default null,
  p_day_to date default null,
  p_topic_id uuid default null
) returns jsonb
language sql
stable
set search_path to 'public'
as $$
  with topic_prompts as (
    select array(select p.id from public.prompts p where p.topic_id = p_topic_id) as ids
  ),
  brand_days as (
    select r.day, r.model_used, r.platform, sum(r.total_mentions) as total_mentions
    from (
      select d.day, d.model_used, d.platform, d.total_mentions
      from public.insights_brand_daily d
      where p_topic_id is null
        and d.brand_id = p_brand_id
        and (p_platform is null or d.platform = p_platform)
        and (p_models is null or d.model_used = any (p_models))
        and (p_region is null or d.region = p_region)
        and (p_day_from is null or d.day >= p_day_from)
        and (p_day_to is null or d.day <= p_day_to)
      union all
      select d.day, d.model_used, d.platform, d.total_mentions
      from public.insights_prompt_daily d, topic_prompts tp
      where p_topic_id is not null
        and d.brand_id = p_brand_id
        and d.prompt_id = any (tp.ids)
        and (p_platform is null or d.platform = p_platform)
        and (p_models is null or d.model_used = any (p_models))
        and (p_region is null or d.region = p_region)
        and (p_day_from is null or d.day >= p_day_from)
        and (p_day_to is null or d.day <= p_day_to)
    ) r
    group by r.day, r.model_used, r.platform
  ),
  comp_days as (
    select r.day, r.model_used, r.platform, sum(r.total_mentions) as total_mentions
    from (
      select d.day, d.model_used, d.platform, d.total_mentions
      from public.insights_competitor_daily d
      where p_topic_id is null
        and d.brand_id = p_brand_id
        and (p_platform is null or d.platform = p_platform)
        and (p_models is null or d.model_used = any (p_models))
        and (p_region is null or d.region = p_region)
        and (p_day_from is null or d.day >= p_day_from)
        and (p_day_to is null or d.day <= p_day_to)
      union all
      select d.day, d.model_used, d.platform, d.mention_count
      from public.insights_competitor_prompt_daily d, topic_prompts tp
      where p_topic_id is not null
        and d.brand_id = p_brand_id
        and d.prompt_id = any (tp.ids)
        and (p_platform is null or d.platform = p_platform)
        and (p_models is null or d.model_used = any (p_models))
        and (p_region is null or d.region = p_region)
        and (p_day_from is null or d.day >= p_day_from)
        and (p_day_to is null or d.day <= p_day_to)
    ) r
    group by r.day, r.model_used, r.platform
  ),
  totals as (
    select
      coalesce((select sum(total_mentions) from brand_days), 0)::bigint as total_brand_mentions,
      coalesce((select sum(total_mentions) from comp_days), 0)::bigint  as total_competitor_mentions
  ),
  by_platform as (
    select b.model_used, b.platform,
           b.brand_mentions,
           coalesce(c.competitor_mentions, 0) as competitor_mentions
    from (
      select model_used, platform,
             coalesce(sum(total_mentions), 0)::bigint as brand_mentions
      from brand_days group by model_used, platform
    ) b
    left join (
      select model_used, platform,
             coalesce(sum(total_mentions), 0)::bigint as competitor_mentions
      from comp_days group by model_used, platform
    ) c on c.model_used is not distinct from b.model_used
       and c.platform is not distinct from b.platform
  ),
  by_day as (
    select b.day,
           b.brand_mentions,
           coalesce(c.competitor_mentions, 0) as competitor_mentions
    from (
      select to_char(day, 'YYYY-MM-DD') as day,
             coalesce(sum(total_mentions), 0)::bigint as brand_mentions
      from brand_days group by day
    ) b
    left join (
      select to_char(day, 'YYYY-MM-DD') as day,
             coalesce(sum(total_mentions), 0)::bigint as competitor_mentions
      from comp_days group by day
    ) c on c.day = b.day
  )
  select jsonb_build_object(
    'total_brand_mentions',      t.total_brand_mentions,
    'total_competitor_mentions', t.total_competitor_mentions,
    'by_platform', coalesce(
      (select jsonb_agg(jsonb_build_object(
                'model_used',          bp.model_used,
                'platform',            bp.platform,
                'brand_mentions',      bp.brand_mentions,
                'competitor_mentions', bp.competitor_mentions)
              order by bp.platform nulls last, bp.model_used nulls last)
       from by_platform bp),
      '[]'::jsonb),
    'by_day', coalesce(
      (select jsonb_agg(jsonb_build_object(
                'day',                 bd.day,
                'brand_mentions',      bd.brand_mentions,
                'competitor_mentions', bd.competitor_mentions)
              order by bd.day)
       from by_day bd),
      '[]'::jsonb)
  )
  from totals t;
$$;

-- visibility_rate_trend over rollups. Liveness join preserved, on the
-- per-day competitor totals; visible sightings attach to those rows only.
-- For a topic, a live competitor with no sightings gets a zero row on each
-- day the topic has answers.
--
-- prompt_daily, comp_daily and comp_visible stay MATERIALIZED and work_mem
-- stays 64 MB (00112): each is referenced once, inside the per-day
-- subselect, and inlined they were re-aggregated for every day.
create function public.visibility_rate_trend_daily(
  p_brand_id uuid,
  p_platform text default null,
  p_models text[] default null,
  p_region text default null,
  p_day_from date default null,
  p_day_to date default null,
  p_topic_id uuid default null
) returns jsonb
language sql
stable
set search_path to 'public'
set work_mem to '64MB'
as $$
  with topic_prompts as (
    select array(select p.id from public.prompts p where p.topic_id = p_topic_id) as ids
  ),
  brand_rows as (
    select d.day, d.answer_count, d.mention_answers, d.citation_answers,
           d.sum_inv_position, d.position_count
    from public.insights_brand_daily d
    where p_topic_id is null
      and d.brand_id = p_brand_id
      and (p_platform is null or d.platform = p_platform)
      and (p_models is null or d.model_used = any (p_models))
      and (p_region is null or d.region = p_region)
      and (p_day_from is null or d.day >= p_day_from)
      and (p_day_to is null or d.day <= p_day_to)
    union all
    select d.day, d.answer_count, d.mention_answers, d.citation_answers,
           d.sum_inv_position, d.position_count
    from public.insights_prompt_daily d, topic_prompts tp
    where p_topic_id is not null
      and d.brand_id = p_brand_id
      and d.prompt_id = any (tp.ids)
      and (p_platform is null or d.platform = p_platform)
      and (p_models is null or d.model_used = any (p_models))
      and (p_region is null or d.region = p_region)
      and (p_day_from is null or d.day >= p_day_from)
      and (p_day_to is null or d.day <= p_day_to)
  ),
  brand_daily as (
    select day,
           sum(answer_count)     as answers,
           sum(mention_answers)  as mention_answers,
           sum(citation_answers) as citation_answers,
           sum(sum_inv_position) / nullif(sum(position_count), 0) as position_factor,
           sum(position_count)   as position_n
    from brand_rows
    group by day
  ),
  prompt_daily as materialized (
    select r.day,
           count(distinct r.prompt_id) as prompt_count,
           count(distinct r.prompt_id)
             filter (where r.has_mention or r.has_citation) as visible_prompts
    from (
      select d.day, d.prompt_id, d.has_mention, d.has_citation
      from public.insights_prompt_daily d
      where p_topic_id is null
        and d.brand_id = p_brand_id
        and (p_platform is null or d.platform = p_platform)
        and (p_models is null or d.model_used = any (p_models))
        and (p_region is null or d.region = p_region)
        and (p_day_from is null or d.day >= p_day_from)
        and (p_day_to is null or d.day <= p_day_to)
      union all
      select d.day, d.prompt_id, d.has_mention, d.has_citation
      from public.insights_prompt_daily d, topic_prompts tp
      where p_topic_id is not null
        and d.brand_id = p_brand_id
        and d.prompt_id = any (tp.ids)
        and (p_platform is null or d.platform = p_platform)
        and (p_models is null or d.model_used = any (p_models))
        and (p_region is null or d.region = p_region)
        and (p_day_from is null or d.day >= p_day_from)
        and (p_day_to is null or d.day <= p_day_to)
    ) r
    group by r.day
  ),
  comp_totals as (
    select r.day, r.competitor_id,
           sum(r.mention_answers)  as mention_answers,
           sum(r.citation_answers) as citation_answers,
           sum(r.sum_inv_position) / nullif(sum(r.position_count), 0) as position_factor,
           sum(r.position_count)   as position_n
    from (
      select cd.day, cd.competitor_id, cd.mention_answers, cd.citation_answers,
             cd.sum_inv_position, cd.position_count
      from public.insights_competitor_daily cd
      where p_topic_id is null
        and cd.brand_id = p_brand_id
        and (p_platform is null or cd.platform = p_platform)
        and (p_models is null or cd.model_used = any (p_models))
        and (p_region is null or cd.region = p_region)
        and (p_day_from is null or cd.day >= p_day_from)
        and (p_day_to is null or cd.day <= p_day_to)
      union all
      select cpd.day, cpd.competitor_id, cpd.mention_answers, cpd.citation_answers,
             cpd.sum_inv_position, cpd.position_count
      from public.insights_competitor_prompt_daily cpd, topic_prompts tp
      where p_topic_id is not null
        and cpd.brand_id = p_brand_id
        and cpd.prompt_id = any (tp.ids)
        and (p_platform is null or cpd.platform = p_platform)
        and (p_models is null or cpd.model_used = any (p_models))
        and (p_region is null or cpd.region = p_region)
        and (p_day_from is null or cpd.day >= p_day_from)
        and (p_day_to is null or cpd.day <= p_day_to)
      union all
      select td.day, c.id::text, 0, 0, null::numeric, 0
      from (select distinct day from brand_rows where p_topic_id is not null) td
      cross join public.competitors c
      where c.brand_id = p_brand_id
    ) r
    group by r.day, r.competitor_id
  ),
  comp_daily as materialized (
    select ct.*
    from comp_totals ct
    join public.competitors c
      on c.id::text = ct.competitor_id and c.brand_id = p_brand_id
  ),
  comp_visible as materialized (
    select r.day, r.competitor_id, count(distinct r.prompt_id) as visible_prompts
    from (
      select cpd.day, cpd.competitor_id, cpd.prompt_id
      from public.insights_competitor_prompt_daily cpd
      where p_topic_id is null
        and cpd.brand_id = p_brand_id
        and (p_platform is null or cpd.platform = p_platform)
        and (p_models is null or cpd.model_used = any (p_models))
        and (p_region is null or cpd.region = p_region)
        and (p_day_from is null or cpd.day >= p_day_from)
        and (p_day_to is null or cpd.day <= p_day_to)
      union all
      select cpd.day, cpd.competitor_id, cpd.prompt_id
      from public.insights_competitor_prompt_daily cpd, topic_prompts tp
      where p_topic_id is not null
        and cpd.brand_id = p_brand_id
        and cpd.prompt_id = any (tp.ids)
        and (p_platform is null or cpd.platform = p_platform)
        and (p_models is null or cpd.model_used = any (p_models))
        and (p_region is null or cpd.region = p_region)
        and (p_day_from is null or cpd.day >= p_day_from)
        and (p_day_to is null or cpd.day <= p_day_to)
    ) r
    group by r.day, r.competitor_id
  )
  select coalesce(
    jsonb_agg(jsonb_build_object(
      'day',              bd.day,
      'prompt_count',     coalesce(pd.prompt_count, 0),
      'visible_prompts',  coalesce(pd.visible_prompts, 0),
      'answers',          bd.answers,
      'mention_answers',  bd.mention_answers,
      'citation_answers', bd.citation_answers,
      'position_factor',  bd.position_factor,
      'position_n',       bd.position_n,
      'competitors', coalesce(
        (select jsonb_agg(jsonb_build_object(
                  'competitor_id',    cd.competitor_id,
                  'visible_prompts',  coalesce(cv.visible_prompts, 0),
                  'mention_answers',  cd.mention_answers,
                  'citation_answers', cd.citation_answers,
                  'position_factor',  cd.position_factor,
                  'position_n',       cd.position_n)
                order by cd.competitor_id)
         from comp_daily cd
         left join comp_visible cv
           on cv.day = cd.day and cv.competitor_id = cd.competitor_id
         where cd.day = bd.day),
        '[]'::jsonb)
    ) order by bd.day),
    '[]'::jsonb)
  from brand_daily bd
  left join prompt_daily pd on pd.day = bd.day;
$$;

-- ─── Comparison reads ───────────────────────────────────────────────────────
--
-- The Insights page's comparisons (Same prompts, Why it changed, By AI
-- platform) read the prompt-grain rollups already; for a topic they keep the
-- topic's current prompts. The bodies are 00149's, 00148's and 00133's with
-- that one filter. The same-prompts read still lists the competitors tracked
-- in each window from insights_competitor_daily: each answer lists every
-- tracked competitor, so a topic's answers track the same ones.

drop function if exists public.insights_same_prompt_compare_daily(
  uuid, date, date, date, date, text[], text);
drop function if exists public.insights_change_drivers_daily(
  uuid, date, date, date, date, text[], text, boolean);
drop function if exists public.insights_platform_compare_daily(
  uuid, date, date, date, date, text[], text, boolean);
drop function if exists public.insights_same_prompt_compare_daily(
  uuid, date, date, date, date, text[], text, uuid);
drop function if exists public.insights_change_drivers_daily(
  uuid, date, date, date, date, text[], text, boolean, uuid);
drop function if exists public.insights_platform_compare_daily(
  uuid, date, date, date, date, text[], text, boolean, uuid);

create function public.insights_same_prompt_compare_daily(
  p_brand_id uuid,
  p_cur_from date,
  p_cur_to date,
  p_prev_from date,
  p_prev_to date,
  p_models text[] default null,
  p_region text default null,
  p_topic_id uuid default null
)
returns jsonb
language plpgsql
stable
security definer
set search_path to 'public'
set work_mem to '64MB'
set plan_cache_mode to 'force_custom_plan'
as $$
declare
  v_result jsonb;
  -- The topic's prompts, resolved once; null reads every prompt.
  v_topic  uuid[] := case when p_topic_id is not null then
                       array(select p.id from public.prompts p where p.topic_id = p_topic_id)
                     end;
begin
  if auth.role() is distinct from 'service_role' and not exists (
    select 1 from public.brands b
    join public.profiles pf on pf.organization_id = b.organization_id
    where b.id = p_brand_id and pf.id = auth.uid()
  ) then
    return null;
  end if;

  with per_prompt as (
    -- One row per prompt and window: nothing below reads the per-day rows.
    select d.prompt_id,
           case when d.day between p_cur_from and p_cur_to then 'cur' else 'prev' end as win,
           sum(d.answer_count)                as answers,
           sum(d.mention_answers)             as mention_answers,
           sum(d.citation_answers)            as citation_answers,
           sum(d.sum_inv_position)            as sum_inv_position,
           sum(d.position_count)              as position_count,
           sum(d.total_mentions)              as total_mentions,
           sum(d.total_citations)             as total_citations,
           sum(d.positive_count)              as positive_count,
           sum(d.mentioning_answers)          as mentioning_answers,
           sum(d.negative_count)              as negative_count,
           sum(d.sum_position)                as sum_position,
           bool_or(d.has_mention or d.has_citation) as visible,
           bool_or(d.mention_answers is null or d.negative_count is null) as stale
    from public.insights_prompt_daily d
    where d.brand_id = p_brand_id
      and (v_topic is null or d.prompt_id = any (v_topic))
      and (p_models is null or d.model_used = any (p_models))
      and (p_region is null or d.region = p_region)
      and (d.day between p_cur_from and p_cur_to or d.day between p_prev_from and p_prev_to)
    group by 1, 2
  ),
  presence as (
    select prompt_id,
           bool_or(win = 'cur' and answers > 0)  as in_cur,
           bool_or(win = 'prev' and answers > 0) as in_prev
    from per_prompt
    group by prompt_id
  ),
  same_prompts as (
    select prompt_id from presence where in_cur and in_prev
  ),
  brand_agg as (
    select p.win,
           sum(p.answers)            as answers,
           sum(p.mention_answers)    as mention_answers,
           sum(p.citation_answers)   as citation_answers,
           sum(p.sum_inv_position) / nullif(sum(p.position_count), 0) as position_factor,
           sum(p.total_mentions)     as total_mentions,
           sum(p.total_citations)    as total_citations,
           sum(p.positive_count)     as positive_count,
           sum(p.mentioning_answers) as mentioning_answers,
           sum(p.negative_count)     as negative_count,
           sum(p.sum_position)       as sum_position,
           sum(p.position_count)     as position_count,
           count(*) filter (where p.visible) as visible_prompts
    from per_prompt p
    join same_prompts s using (prompt_id)
    group by p.win
  ),
  comp_sums as (
    select case when cd.day between p_cur_from and p_cur_to then 'cur' else 'prev' end as win,
           cd.competitor_id,
           sum(cd.mention_answers)  as mention_answers,
           sum(cd.citation_answers) as citation_answers,
           sum(cd.sum_inv_position) / nullif(sum(cd.position_count), 0) as position_factor,
           sum(cd.mention_count)    as mentions,
           bool_or(cd.mention_answers is null) as stale
    from public.insights_competitor_prompt_daily cd
    where cd.brand_id = p_brand_id
      and cd.prompt_id in (select prompt_id from same_prompts)
      and (p_models is null or cd.model_used = any (p_models))
      and (p_region is null or cd.region = p_region)
      and (cd.day between p_cur_from and p_cur_to or cd.day between p_prev_from and p_prev_to)
    group by 1, 2
  ),
  -- Competitors tracked in each window, from the dense day-grain table (see
  -- 00130): one added during the period then has no previous value.
  tracked as (
    select distinct
           case when cd.day between p_cur_from and p_cur_to then 'cur' else 'prev' end as win,
           cd.competitor_id
    from public.insights_competitor_daily cd
    where cd.brand_id = p_brand_id
      and (p_models is null or cd.model_used = any (p_models))
      and (p_region is null or cd.region = p_region)
      and (cd.day between p_cur_from and p_cur_to or cd.day between p_prev_from and p_prev_to)
  ),
  comp_agg as (
    select t.win,
           t.competitor_id,
           c.name,
           coalesce(cs.mention_answers, 0)  as mention_answers,
           coalesce(cs.citation_answers, 0) as citation_answers,
           cs.position_factor
    from tracked t
    join public.competitors c
      on c.id::text = t.competitor_id and c.brand_id = p_brand_id
    left join comp_sums cs
      on cs.win = t.win and cs.competitor_id = t.competitor_id
  ),
  -- Every competitor's mentions on the shared prompts, for share of voice
  -- (which counts all competitors, as share_of_voice_aggregates does).
  comp_mentions as (
    select win, sum(mentions) as mentions from comp_sums group by win
  ),
  windows(win) as (values ('cur'), ('prev'))
  select jsonb_build_object(
    'complete', not exists (select 1 from per_prompt where stale)
                and not exists (select 1 from comp_sums where stale),
    'same_prompts',    (select count(*) from same_prompts),
    'new_prompts',     (select count(*) from presence where in_cur and not in_prev),
    'dropped_prompts', (select count(*) from presence where in_prev and not in_cur)
  ) || (
    select jsonb_object_agg(w.win, jsonb_build_object(
      'answers',            coalesce(b.answers, 0),
      'mention_answers',    coalesce(b.mention_answers, 0),
      'citation_answers',   coalesce(b.citation_answers, 0),
      'position_factor',    b.position_factor,
      'total_mentions',     coalesce(b.total_mentions, 0),
      'total_citations',    coalesce(b.total_citations, 0),
      'positive_count',     coalesce(b.positive_count, 0),
      'mentioning_answers', coalesce(b.mentioning_answers, 0),
      'negative_count',     coalesce(b.negative_count, 0),
      'sum_position',       b.sum_position,
      'position_count',     coalesce(b.position_count, 0),
      'visible_prompts',    coalesce(b.visible_prompts, 0),
      'competitor_mentions', coalesce((select cm.mentions from comp_mentions cm where cm.win = w.win), 0),
      'by_competitor', coalesce(
        (select jsonb_agg(jsonb_build_object(
                  'competitor_id',    ca.competitor_id,
                  'name',             ca.name,
                  'mention_answers',  ca.mention_answers,
                  'citation_answers', ca.citation_answers,
                  'position_factor',  ca.position_factor)
                order by ca.mention_answers desc, ca.competitor_id)
         from comp_agg ca where ca.win = w.win),
        '[]'::jsonb)))
    from windows w
    left join brand_agg b on b.win = w.win
  )
  into v_result;

  return v_result;
end;
$$;

create function public.insights_change_drivers_daily(
  p_brand_id uuid,
  p_cur_from date,
  p_cur_to date,
  p_prev_from date,
  p_prev_to date,
  p_models text[] default null,
  p_region text default null,
  p_same_prompts boolean default true,
  p_topic_id uuid default null
)
returns jsonb
language plpgsql
stable
security definer
set search_path to 'public'
set work_mem to '64MB'
set plan_cache_mode to 'force_custom_plan'
as $$
declare
  v_result jsonb;
  -- The topic's prompts, resolved once; null reads every prompt.
  v_topic  uuid[] := case when p_topic_id is not null then
                       array(select p.id from public.prompts p where p.topic_id = p_topic_id)
                     end;
begin
  if auth.role() is distinct from 'service_role' and not exists (
    select 1 from public.brands b
    join public.profiles pf on pf.organization_id = b.organization_id
    where b.id = p_brand_id and pf.id = auth.uid()
  ) then
    return null;
  end if;

  with per_engine as (
    -- One row per prompt, engine and window: nothing below reads the
    -- per-day rows.
    select d.prompt_id, d.model_used, d.platform,
           case when d.day between p_cur_from and p_cur_to then 'cur' else 'prev' end as win,
           sum(d.answer_count)                as answers,
           sum(d.mention_answers)             as mention_answers,
           sum(d.citation_answers)            as citation_answers,
           sum(d.sum_inv_position)            as sum_inv_position,
           sum(d.position_count)              as position_count,
           bool_or(d.mention_answers is null) as stale
    from public.insights_prompt_daily d
    where d.brand_id = p_brand_id
      and (v_topic is null or d.prompt_id = any (v_topic))
      and (p_models is null or d.model_used = any (p_models))
      and (p_region is null or d.region = p_region)
      and (d.day between p_cur_from and p_cur_to or d.day between p_prev_from and p_prev_to)
    group by 1, 2, 3, 4
  ),
  presence as (
    select prompt_id,
           bool_or(win = 'cur')  as in_cur,
           bool_or(win = 'prev') as in_prev
    from per_engine
    group by prompt_id
  ),
  scoped as (
    select r.* from per_engine r join presence p using (prompt_id)
    where not p_same_prompts or (p.in_cur and p.in_prev)
  ),
  by_prompt as (
    select prompt_id, win,
           sum(answers)          as answers,
           sum(mention_answers)  as mention_answers,
           sum(citation_answers) as citation_answers,
           sum(sum_inv_position) as sum_inv_position,
           sum(position_count)   as position_count
    from scoped
    group by prompt_id, win
  ),
  by_engine as (
    select model_used, platform, win,
           sum(answers)          as answers,
           sum(mention_answers)  as mention_answers,
           sum(citation_answers) as citation_answers,
           sum(sum_inv_position) as sum_inv_position,
           sum(position_count)   as position_count
    from scoped
    group by model_used, platform, win
  ),
  added as (
    select p.created_at
    from presence pr
    join public.prompts p on p.id = pr.prompt_id
    where pr.in_cur and not pr.in_prev
  )
  select jsonb_build_object(
    'complete', not exists (select 1 from per_engine where stale),
    'prompts', coalesce(
      (select jsonb_agg(jsonb_build_object(
                'prompt_id',        b.prompt_id,
                'win',              b.win,
                'answers',          b.answers,
                'mention_answers',  b.mention_answers,
                'citation_answers', b.citation_answers,
                'sum_inv_position', b.sum_inv_position,
                'position_count',   b.position_count))
       from by_prompt b),
      '[]'::jsonb),
    'engines', coalesce(
      (select jsonb_agg(jsonb_build_object(
                'model_used',       e.model_used,
                'platform',         e.platform,
                'win',              e.win,
                'answers',          e.answers,
                'mention_answers',  e.mention_answers,
                'citation_answers', e.citation_answers,
                'sum_inv_position', e.sum_inv_position,
                'position_count',   e.position_count))
       from by_engine e),
      '[]'::jsonb),
    'prompt_info', coalesce(
      (select jsonb_agg(jsonb_build_object(
                'id',         p.id,
                'text',       p.text,
                'topic_id',   p.topic_id,
                'topic_name', t.name))
       from public.prompts p
       left join public.topics t on t.id = p.topic_id
       where p.id in (select prompt_id from by_prompt)),
      '[]'::jsonb),
    -- Dates only for prompts created this period: one answered again after
    -- a pause is new to the comparison, not newly added.
    'new_prompts', (select jsonb_build_object(
                      'count',       count(*),
                      'first_added', min(created_at) filter (
                                       where created_at >= (p_cur_from::timestamp at time zone 'utc')),
                      'last_added',  max(created_at) filter (
                                       where created_at >= (p_cur_from::timestamp at time zone 'utc')))
                    from added)
  )
  into v_result;

  return v_result;
end;
$$;

create function public.insights_platform_compare_daily(
  p_brand_id uuid,
  p_cur_from date default null,
  p_cur_to date default null,
  p_prev_from date default null,
  p_prev_to date default null,
  p_models text[] default null,
  p_region text default null,
  p_same_prompts boolean default true,
  p_topic_id uuid default null
)
returns jsonb
language sql
stable
security invoker
set search_path to 'public'
as $$
  with prompt_rows as (
    select d.*,
           case when d.day between coalesce(p_cur_from, '-infinity'::date)
                               and coalesce(p_cur_to, 'infinity'::date)
                then 'cur' else 'prev' end as win
    from public.insights_prompt_daily d
    where d.brand_id = p_brand_id
      and (p_topic_id is null or d.prompt_id in (
             select p.id from public.prompts p where p.topic_id = p_topic_id))
      and (p_models is null or d.model_used = any (p_models))
      and (p_region is null or d.region = p_region)
      and (d.day between coalesce(p_cur_from, '-infinity'::date)
                     and coalesce(p_cur_to, 'infinity'::date)
           or d.day between p_prev_from and p_prev_to)
  ),
  shared as (
    select prompt_id
    from prompt_rows
    group by prompt_id
    having bool_or(win = 'cur') and bool_or(win = 'prev')
  ),
  scoped as (
    select * from prompt_rows
    where not p_same_prompts or prompt_id in (select prompt_id from shared)
  ),
  platforms as (
    select model_used, platform, win,
           sum(answer_count)     as answers,
           sum(mention_answers)  as mention_answers,
           sum(citation_answers) as citation_answers,
           sum(sum_inv_position) as sum_inv_position,
           sum(position_count)   as position_count,
           sum(sum_position)     as sum_position,
           case when win = 'cur' then
             coalesce(array_agg(distinct prompt_id) filter (where has_mention or has_citation),
                      '{}')
           end                   as visible_prompt_ids,
           case when win = 'cur' then array_agg(distinct prompt_id) end
                                 as active_prompt_ids
    from scoped
    group by model_used, platform, win
  )
  select jsonb_build_object(
    'complete', not exists (
                  select 1 from prompt_rows
                  where mention_answers is null or negative_count is null),
    'platforms', coalesce(
      (select jsonb_agg(jsonb_build_object(
                'model_used',         p.model_used,
                'platform',           p.platform,
                'win',                p.win,
                'answers',            p.answers,
                'mention_answers',    p.mention_answers,
                'citation_answers',   p.citation_answers,
                'sum_inv_position',   p.sum_inv_position,
                'position_count',     p.position_count,
                'sum_position',       p.sum_position,
                'visible_prompt_ids', p.visible_prompt_ids,
                'active_prompt_ids',  p.active_prompt_ids))
       from platforms p),
      '[]'::jsonb)
  );
$$;

revoke execute on function public.insights_same_prompt_compare_daily(
  uuid, date, date, date, date, text[], text, uuid
) from public, anon;
grant execute on function public.insights_same_prompt_compare_daily(
  uuid, date, date, date, date, text[], text, uuid
) to authenticated, service_role;

revoke execute on function public.insights_change_drivers_daily(
  uuid, date, date, date, date, text[], text, boolean, uuid
) from public, anon;
grant execute on function public.insights_change_drivers_daily(
  uuid, date, date, date, date, text[], text, boolean, uuid
) to authenticated, service_role;

revoke all on function public.insights_platform_compare_daily(
  uuid, date, date, date, date, text[], text, boolean, uuid
) from public;
grant execute on function public.insights_platform_compare_daily(
  uuid, date, date, date, date, text[], text, boolean, uuid
) to authenticated, service_role;

comment on function public.insights_platform_compare_daily(
  uuid, date, date, date, date, text[], text, boolean, uuid
) is
  'Insights summary line (VIS-006, 00132): Visibility components per (model, platform) in both comparison windows, over the prompts answered in both when p_same_prompts, from the prompt-grain rollups; p_topic_id keeps a topic''s prompts (00164). complete=false until they are backfilled (00130).';

-- PostgREST caches function signatures; make it see the new ones now.
notify pgrst, 'reload schema';