-- the statement of a request for a few parents (the events of 5 of 1,000, their items normalized through the lookup table) as the first statement of a new backend: a pool that gives every request a connection of its own plans it with no verdict kept, no lookup rows or dictionaries in the backend, and the statistics of the index read from the shared record or from the index
-- label: the events of 5 parents, `LEFT JOIN LATERAL (.. WHERE COALESCE(t.scope, '{}') = '{}' AND t.kind = e.type AND t.part = o.part AND t.sub = o.sub LIMIT 1)` over a lookup table of 1,200,000 rows, each timed statement in a new backend; `warm_backend` is the same statement as the second in a backend that has run it
-- ordered: yes
-- fresh: yes
--@ setup
DO $do$ BEGIN
	IF to_regclass('pd_events') IS NULL THEN
		CREATE TABLE pd_entities (id int PRIMARY KEY, type text NOT NULL);
		INSERT INTO pd_entities SELECT g, (ARRAY['type_a', 'type_a', 'type_a', 'type_b', 'type_c', 'type_d'])[1 + g % 6] FROM generate_series(1, 1000) g;
		CREATE TABLE pd_map (id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, scope int[], kind text NOT NULL, part text NOT NULL, sub text NOT NULL, canon_part text, canon_sub text);
		INSERT INTO pd_map (scope, kind, part, sub, canon_part, canon_sub)
		SELECT CASE WHEN g % 40 = 0 THEN ARRAY[g % 7] END, (ARRAY['type_a', 'type_b', 'type_c', 'type_d'])[1 + g % 4], 'p' || g % 52, md5(g::text), CASE WHEN g % 10 <> 0 THEN 'm' || g % 18 END, 'ms' || g % 362
		FROM generate_series(1, {rows} * 6) g ORDER BY random();
		CREATE UNIQUE INDEX pd_map_key ON pd_map (COALESCE(scope, '{}'::int[]), kind, part, sub);
		CREATE TABLE pd_events (id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, dim_a_id int NOT NULL, event_date date NOT NULL, deleted_at timestamptz, doc jsonb NOT NULL);
		INSERT INTO pd_events (dim_a_id, event_date, doc)
		SELECT e.id, date '2025-02-01' + (random() * 272)::int,
		       CASE WHEN random() < .55 THEN jsonb_build_object('lang', 'en', 'items', (
		              SELECT jsonb_agg(jsonb_build_object('part', 'p' || r % 52, 'sub', md5(r::text), 'dir_a', (ARRAY['up', 'down', 'flat'])[1 + (random() * 2)::int], 'score', round((3 + random() * 7)::numeric, 1)))
		              FROM (SELECT 1 + floor(random() * 12000)::int * 97 AS r FROM generate_series(1, 1 + (random() * 3)::int + (e.id * 0))) x))
		            ELSE '{}'::jsonb END
		FROM pd_entities e, generate_series(1, 120) g ORDER BY random();
		CREATE INDEX pd_events_col ON pd_events USING sift_col (id, dim_a_id, event_date, deleted_at, doc)
			WITH (key_cut = dim_a_id, paths = 'doc.lang astext, doc.normalized astext, doc.items[]{part astext, sub astext, dir_a astext, score float8}');
	END IF;
END $do$;
VACUUM (FREEZE, ANALYZE) pd_events, pd_entities;
ANALYZE pd_map
--@ pg
WITH dim_b AS MATERIALIZED (SELECT id, type FROM pd_entities WHERE id <= 5),
base_events AS (
  SELECT ev.*, dim_b.type AS kind FROM pd_events ev INNER JOIN dim_b ON dim_b.id = ev.dim_a_id
  WHERE ev.deleted_at IS NULL AND ev.event_date >= date '2025-02-01' AND ev.event_date <= date '2025-10-31'),
per_event AS (
  SELECT be.id, mo.part, count(be.id) AS items, avg(score) AS score
  FROM base_events be
  INNER JOIN LATERAL (
    SELECT (CASE WHEN be.doc ? 'normalized' THEN o.part ELSE rm.canon_part END) AS part, o.score
    FROM jsonb_to_recordset(be.doc -> 'items') AS o(part text, sub text, score float8)
    LEFT JOIN LATERAL (SELECT canon_part FROM pd_map t
                       WHERE COALESCE(t.scope, '{}') = '{}' AND t.canon_part IS NOT NULL AND t.kind = be.kind AND t.part = o.part AND t.sub = o.sub
                         AND NOT (be.doc ? 'normalized') LIMIT 1) rm ON true) mo ON true
  WHERE mo.part IS NOT NULL GROUP BY be.id, mo.part)
SELECT part, avg(score) AS score, count(*) AS events, sum(items) AS items FROM per_event GROUP BY part ORDER BY 1