vreau-digital/services/seap-scraper/sql/005_wsp_views.sql

-- Materialized views for hub UI — refreshed nightly after WSP sync.
-- Provides fast aggregations for "Achiziții România live" dashboards.
BEGIN;

-- ── Daily totals: count + value per day (across all WSP sources) ──
CREATE MATERIALIZED VIEW IF NOT EXISTS seap.mv_daily_totals AS
SELECT
    date_trunc('day', publication_date)::date AS day,
    type,
    count(*) AS notices,
    sum(awarded_value) FILTER (WHERE awarded_value IS NOT NULL) AS total_awarded,
    sum(estimated_value) FILTER (WHERE estimated_value IS NOT NULL) AS total_estimated,
    count(DISTINCT authority_cui) AS distinct_authorities,
    count(DISTINCT supplier_cui) AS distinct_suppliers
FROM seap.announcements
WHERE source LIKE 'wsp_%'
  AND publication_date >= now() - interval '24 months'
GROUP BY 1, 2;

CREATE INDEX IF NOT EXISTS idx_mv_daily_totals_day ON seap.mv_daily_totals(day DESC);


-- ── Top contracting authorities (last 12 months by total awarded value) ──
CREATE MATERIALIZED VIEW IF NOT EXISTS seap.mv_top_authorities AS
SELECT
    authority_cui,
    authority_name,
    county_code,
    count(*) AS notices_count,
    count(*) FILTER (WHERE type = 'ca_notice') AS awarded_count,
    sum(awarded_value) FILTER (WHERE awarded_value IS NOT NULL) AS total_awarded,
    avg(awarded_value) FILTER (WHERE awarded_value IS NOT NULL) AS avg_awarded,
    array_agg(DISTINCT cpv_code) FILTER (WHERE cpv_code IS NOT NULL) AS cpv_codes,
    max(publication_date) AS most_recent
FROM seap.announcements
WHERE source LIKE 'wsp_%'
  AND authority_cui IS NOT NULL
  AND publication_date >= now() - interval '12 months'
GROUP BY 1, 2, 3
HAVING count(*) >= 1;

CREATE INDEX IF NOT EXISTS idx_mv_top_auth_value ON seap.mv_top_authorities(total_awarded DESC NULLS LAST);
CREATE INDEX IF NOT EXISTS idx_mv_top_auth_cui ON seap.mv_top_authorities(authority_cui);
CREATE INDEX IF NOT EXISTS idx_mv_top_auth_county ON seap.mv_top_authorities(county_code);


-- ── Top suppliers (firms that won contracts) ──
CREATE MATERIALIZED VIEW IF NOT EXISTS seap.mv_top_suppliers AS
SELECT
    supplier_cui,
    supplier_name,
    count(*) AS contracts_won,
    sum(awarded_value) FILTER (WHERE awarded_value IS NOT NULL) AS total_awarded,
    avg(awarded_value) FILTER (WHERE awarded_value IS NOT NULL) AS avg_awarded,
    count(DISTINCT authority_cui) AS distinct_clients,
    array_agg(DISTINCT cpv_code) FILTER (WHERE cpv_code IS NOT NULL) AS cpv_codes,
    max(publication_date) AS most_recent
FROM seap.announcements
WHERE source LIKE 'wsp_%'
  AND supplier_cui IS NOT NULL
  AND type = 'ca_notice'
  AND publication_date >= now() - interval '12 months'
GROUP BY 1, 2;

CREATE INDEX IF NOT EXISTS idx_mv_top_supp_value ON seap.mv_top_suppliers(total_awarded DESC NULLS LAST);
CREATE INDEX IF NOT EXISTS idx_mv_top_supp_cui ON seap.mv_top_suppliers(supplier_cui);


-- ── Top CPV codes (most-used categories) ──
CREATE MATERIALIZED VIEW IF NOT EXISTS seap.mv_top_cpv AS
SELECT
    cpv_code,
    count(*) AS notices_count,
    sum(awarded_value) FILTER (WHERE awarded_value IS NOT NULL) AS total_awarded,
    count(DISTINCT authority_cui) AS distinct_buyers,
    count(DISTINCT supplier_cui) AS distinct_winners
FROM seap.announcements
WHERE source LIKE 'wsp_%'
  AND cpv_code IS NOT NULL
  AND publication_date >= now() - interval '12 months'
GROUP BY 1;

CREATE INDEX IF NOT EXISTS idx_mv_top_cpv_value ON seap.mv_top_cpv(total_awarded DESC NULLS LAST);


-- ── County totals (for map) ──
CREATE MATERIALIZED VIEW IF NOT EXISTS seap.mv_county_totals AS
SELECT
    county_code,
    type,
    count(*) AS notices_count,
    sum(awarded_value) FILTER (WHERE awarded_value IS NOT NULL) AS total_awarded
FROM seap.announcements
WHERE source LIKE 'wsp_%'
  AND county_code IS NOT NULL
  AND publication_date >= now() - interval '12 months'
GROUP BY 1, 2;

CREATE INDEX IF NOT EXISTS idx_mv_county_totals_code ON seap.mv_county_totals(county_code);


-- ── Refresh function (called by cron after daily sync) ──
CREATE OR REPLACE FUNCTION seap.refresh_wsp_views()
RETURNS void AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY seap.mv_daily_totals;
    REFRESH MATERIALIZED VIEW CONCURRENTLY seap.mv_top_authorities;
    REFRESH MATERIALIZED VIEW CONCURRENTLY seap.mv_top_suppliers;
    REFRESH MATERIALIZED VIEW CONCURRENTLY seap.mv_top_cpv;
    REFRESH MATERIALIZED VIEW CONCURRENTLY seap.mv_county_totals;
EXCEPTION WHEN feature_not_supported THEN
    -- CONCURRENTLY requires unique index; first refresh is non-concurrent
    REFRESH MATERIALIZED VIEW seap.mv_daily_totals;
    REFRESH MATERIALIZED VIEW seap.mv_top_authorities;
    REFRESH MATERIALIZED VIEW seap.mv_top_suppliers;
    REFRESH MATERIALIZED VIEW seap.mv_top_cpv;
    REFRESH MATERIALIZED VIEW seap.mv_county_totals;
END;
$$ LANGUAGE plpgsql;

COMMIT;