vreau-digital/services/seap-scraper/sql/046_cauta_default_facets_snapshot.sql

-- 046: snapshot of /achizitii/cauta no-filter facet aggregates.
--
-- Problem: /cauta home (no filters) still ~1.9s after sql/045's pub_date
-- index fix. Main query is now ~5ms but 6 parallel facet aggregates each
-- do full-table scans:
--   - count(*) GROUP BY type             (~200ms)
--   - count(*) GROUP BY county_code       (~200ms)
--   - count(*) GROUP BY cpv_division      (~200ms)
--   - count(*) GROUP BY procedure_type    (~200ms)
--   - count(*) GROUP BY procedure_state   (~200ms)
--   - count(*) WHERE awarded_value bucket (~200ms)
--
-- Fix: materialize a single snapshot table holding all default-facet
-- counts. Search code short-circuits to read from snapshot when filters
-- are empty.
--
-- Wins only the no-filter case; any active filter still does live
-- aggregates. That's intentional: filter combinations are exponentially
-- many (cannot pre-materialize) and selective filters keep aggregates
-- fast anyway.

BEGIN;

CREATE TABLE IF NOT EXISTS public_kpi.cauta_default_facets (
  facet_name  text   NOT NULL,
  key         text   NOT NULL,
  label       text,
  emoji       text,
  count       bigint NOT NULL,
  sort_order  int    NOT NULL DEFAULT 0,
  computed_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (facet_name, key)
);

CREATE TABLE IF NOT EXISTS public_kpi.cauta_default_totals (
  id          smallint PRIMARY KEY DEFAULT 1 CHECK (id = 1),
  total       bigint   NOT NULL,
  sum_awarded numeric  NOT NULL,
  computed_at timestamptz NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION public_kpi.refresh_cauta_defaults()
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
  -- Totals (single row, idempotent UPSERT)
  INSERT INTO public_kpi.cauta_default_totals (id, total, sum_awarded, computed_at)
  SELECT 1, count(*), COALESCE(sum(awarded_value), 0), now()
    FROM seap.announcements
  ON CONFLICT (id) DO UPDATE SET
    total = EXCLUDED.total,
    sum_awarded = EXCLUDED.sum_awarded,
    computed_at = EXCLUDED.computed_at;

  -- Wipe facet table, re-populate. Single transaction so reads see
  -- consistent state during refresh.
  DELETE FROM public_kpi.cauta_default_facets;

  -- types (top 12)
  INSERT INTO public_kpi.cauta_default_facets (facet_name, key, label, count, sort_order)
  SELECT 'type', type, type, count(*),
         ROW_NUMBER() OVER (ORDER BY count(*) DESC)
    FROM seap.announcements
   GROUP BY type
   ORDER BY count(*) DESC
   LIMIT 12;

  -- counties (top 20)
  INSERT INTO public_kpi.cauta_default_facets (facet_name, key, label, count, sort_order)
  SELECT 'county', county_code, county_code, count(*),
         ROW_NUMBER() OVER (ORDER BY count(*) DESC)
    FROM seap.announcements
   WHERE county_code IS NOT NULL
   GROUP BY county_code
   ORDER BY count(*) DESC
   LIMIT 20;

  -- cpv divisions (top 15, with label + emoji from cpv_codes)
  INSERT INTO public_kpi.cauta_default_facets (facet_name, key, label, emoji, count, sort_order)
  SELECT 'cpv', a.cpv_division, c.name_ro, c.emoji, count(*),
         ROW_NUMBER() OVER (ORDER BY count(*) DESC)
    FROM seap.announcements a
    LEFT JOIN seap.cpv_codes c ON c.code = a.cpv_division
   WHERE a.cpv_division IS NOT NULL
   GROUP BY a.cpv_division, c.name_ro, c.emoji
   ORDER BY count(*) DESC
   LIMIT 15;

  -- procedure types (top 10)
  INSERT INTO public_kpi.cauta_default_facets (facet_name, key, label, count, sort_order)
  SELECT 'procedure', procedure_type, procedure_type, count(*),
         ROW_NUMBER() OVER (ORDER BY count(*) DESC)
    FROM seap.announcements
   WHERE procedure_type IS NOT NULL
   GROUP BY procedure_type
   ORDER BY count(*) DESC
   LIMIT 10;

  -- procedure states (top 8)
  INSERT INTO public_kpi.cauta_default_facets (facet_name, key, label, count, sort_order)
  SELECT 'state', procedure_state, procedure_state, count(*),
         ROW_NUMBER() OVER (ORDER BY count(*) DESC)
    FROM seap.announcements
   WHERE procedure_state IS NOT NULL
   GROUP BY procedure_state
   ORDER BY count(*) DESC
   LIMIT 8;

  -- value buckets (5 buckets + "fără valoare")
  INSERT INTO public_kpi.cauta_default_facets (facet_name, key, label, count, sort_order)
  SELECT 'value', bucket, bucket, count(*),
         CASE bucket
           WHEN 'sub 100K'     THEN 1
           WHEN '100K – 1M'    THEN 2
           WHEN '1M – 10M'     THEN 3
           WHEN '10M – 100M'   THEN 4
           WHEN 'peste 100M'   THEN 5
           WHEN 'fără valoare' THEN 6
         END
    FROM (
      SELECT CASE
        WHEN awarded_value IS NULL OR awarded_value = 0 THEN 'fără valoare'
        WHEN awarded_value < 100000      THEN 'sub 100K'
        WHEN awarded_value < 1000000     THEN '100K – 1M'
        WHEN awarded_value < 10000000    THEN '1M – 10M'
        WHEN awarded_value < 100000000   THEN '10M – 100M'
        ELSE                                  'peste 100M'
      END AS bucket
        FROM seap.announcements
    ) b
   GROUP BY bucket;

END;
$$;

COMMIT;

-- Initial population
SELECT public_kpi.refresh_cauta_defaults();