insert into dbversion (version, release, description)
       values (25, now(), 'Work In Progress');

create table scheduler_metrics (
  lister_id uuid not null references listers(id),
  visit_type text not null,
  last_update timestamptz not null,
  origins_known int not null default 0,
  origins_enabled int not null default 0,
  origins_never_visited int not null default 0,
  origins_with_pending_changes int not null default 0,

  primary key (lister_id, visit_type)
);

comment on table scheduler_metrics is 'Cache of per-lister metrics for the scheduler, collated between the listed_origins and origin_visit_stats tables.';
comment on column scheduler_metrics.lister_id is 'Lister instance on which metrics have been aggregated';
comment on column scheduler_metrics.visit_type is 'Visit type on which metrics have been aggregated';
comment on column scheduler_metrics.last_update is 'Last update of these metrics';
comment on column scheduler_metrics.origins_known is 'Number of known (enabled or disabled) origins';
comment on column scheduler_metrics.origins_enabled is 'Number of origins that were present in the latest listing';
comment on column scheduler_metrics.origins_never_visited is 'Number of origins that have never been successfully visited';
comment on column scheduler_metrics.origins_with_pending_changes is 'Number of enabled origins with known activity since our last visit';


create or replace function update_metrics(lister_id uuid default NULL, ts timestamptz default now())
  returns setof scheduler_metrics
  language sql
as $$
  insert into scheduler_metrics (
    lister_id, visit_type, last_update,
    origins_known, origins_enabled,
    origins_never_visited, origins_with_pending_changes
  )
    select
      lo.lister_id, lo.visit_type, coalesce(ts, now()) as last_update,
      count(*) as origins_known,
      count(*) filter (where enabled) as origins_enabled,
      count(*) filter (where
        enabled and last_snapshot is NULL
      ) as origins_never_visited,
      count(*) filter (where
        enabled and lo.last_update > greatest(ovs.last_eventful, ovs.last_uneventful)
      ) as origins_with_pending_changes
    from listed_origins lo
    left join origin_visit_stats ovs using (url, visit_type)
    where
      -- update only for the requested lister
      update_metrics.lister_id = lo.lister_id
      -- or for all listers if the function argument is null
      or update_metrics.lister_id is null
    group by (lister_id, visit_type)
  on conflict (lister_id, visit_type) do update
    set
      last_update = EXCLUDED.last_update,
      origins_known = EXCLUDED.origins_known,
      origins_enabled = EXCLUDED.origins_enabled,
      origins_never_visited = EXCLUDED.origins_never_visited,
      origins_with_pending_changes = EXCLUDED.origins_with_pending_changes
  returning *
$$;


comment on function update_metrics(uuid, timestamptz) is 'Update metrics for the given lister_id';