-- the keys of a correlated `LIMIT 1` child over a btree whose first column is an expression, probed in the btree itself a batch at a time: each item of an event is normalized through a lookup table of 1,200,000 rows at about 12,000 random places of its leaf pages
-- label: 120,000 events of 1,000 entities with 1 to 4 items each, `LEFT JOIN LATERAL (.. WHERE COALESCE(t.scope, '{}') = '{}' AND t.canon_part IS NOT NULL AND t.kind = e.type AND t.part = o.part AND t.sub = o.sub LIMIT 1)`, unique index on `(COALESCE(scope, '{}'), kind, part, sub)`
-- ordered: 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 <= 1000),
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