-- Materialized views for slow /achizitii/retete pages.
-- Refresh nightly via vreaudigital-mvs.timer.

BEGIN;

-- ──────────────────────────────────────────────────────────────────────
-- mv_top_cpv_divisions: powers /retete/top-categorii-bani + cpv-directe-mari
-- ──────────────────────────────────────────────────────────────────────
DROP MATERIALIZED VIEW IF EXISTS seap.mv_top_cpv_divisions CASCADE;
CREATE MATERIALIZED VIEW seap.mv_top_cpv_divisions AS
SELECT
  a.cpv_division,
  c.name_ro AS cpv_name,
  c.emoji,
  COUNT(*)::int AS contracts,
  COALESCE(SUM(a.awarded_value), 0)::numeric(20,2) AS total_value,
  COALESCE(SUM(a.awarded_value) FILTER (WHERE a.type = 'da'), 0)::numeric(20,2) AS direct_value,
  COUNT(DISTINCT a.authority_cui)::int AS distinct_authorities,
  COUNT(DISTINCT a.supplier_cui)::int AS distinct_suppliers,
  CASE WHEN COALESCE(SUM(a.awarded_value), 0) > 0
       THEN COALESCE(SUM(a.awarded_value) FILTER (WHERE a.type = 'da'), 0) / SUM(a.awarded_value)
       ELSE 0
  END::numeric(8,4) AS direct_pct
FROM seap.announcements a
LEFT JOIN seap.cpv_codes c ON c.code = a.cpv_division
WHERE a.cpv_division IS NOT NULL
  AND a.awarded_value IS NOT NULL
GROUP BY a.cpv_division, c.name_ro, c.emoji;

CREATE UNIQUE INDEX IF NOT EXISTS idx_mv_top_cpv_div_pk ON seap.mv_top_cpv_divisions(cpv_division);
CREATE INDEX IF NOT EXISTS idx_mv_top_cpv_div_value ON seap.mv_top_cpv_divisions(total_value DESC NULLS LAST);
CREATE INDEX IF NOT EXISTS idx_mv_top_cpv_div_directpct ON seap.mv_top_cpv_divisions(direct_pct DESC) WHERE total_value >= 100000000;


-- ──────────────────────────────────────────────────────────────────────
-- mv_top_suppliers: powers /retete/top-firme-castigatoare + firme-multe-judete
-- ──────────────────────────────────────────────────────────────────────
DROP MATERIALIZED VIEW IF EXISTS seap.mv_top_suppliers CASCADE;
CREATE MATERIALIZED VIEW seap.mv_top_suppliers AS
WITH agg AS (
  SELECT
    regexp_replace(upper(a.supplier_cui), '(^RO)|\s+', '', 'g') AS cui_norm,
    MIN(a.supplier_name) AS name,
    MIN(cl.county) AS county,
    COUNT(*)::int AS contracts,
    COALESCE(SUM(a.awarded_value), 0)::numeric(20,2) AS total_value,
    COUNT(DISTINCT a.authority_cui)::int AS distinct_buyers,
    COUNT(DISTINCT acl.county)::int AS county_count
  FROM seap.announcements a
  LEFT JOIN seap.cui_location cl ON cl.cui = regexp_replace(upper(a.supplier_cui), '(^RO)|\s+', '', 'g')
  LEFT JOIN seap.cui_location acl ON acl.cui = a.authority_cui
  WHERE a.supplier_cui IS NOT NULL
    AND a.awarded_value IS NOT NULL
  GROUP BY 1
)
SELECT * FROM agg WHERE total_value > 0;

CREATE UNIQUE INDEX IF NOT EXISTS idx_mv_top_suppliers_pk ON seap.mv_top_suppliers(cui_norm);
CREATE INDEX IF NOT EXISTS idx_mv_top_suppliers_value ON seap.mv_top_suppliers(total_value DESC NULLS LAST);
CREATE INDEX IF NOT EXISTS idx_mv_top_suppliers_counties ON seap.mv_top_suppliers(county_count DESC NULLS LAST);


-- ──────────────────────────────────────────────────────────────────────
-- mv_top_authorities: powers /retete/top-autoritati-cheltuitori
-- ──────────────────────────────────────────────────────────────────────
DROP MATERIALIZED VIEW IF EXISTS seap.mv_top_authorities CASCADE;
CREATE MATERIALIZED VIEW seap.mv_top_authorities AS
SELECT
  a.authority_cui,
  MIN(a.authority_name) AS name,
  MIN(cl.county) AS county,
  MIN(a.authority_type) AS authority_type,
  MIN(cl.siruta) AS siruta,
  COUNT(*)::int AS contracts,
  COALESCE(SUM(a.awarded_value), 0)::numeric(20,2) AS total_value,
  COUNT(DISTINCT a.supplier_cui)::int AS distinct_suppliers
FROM seap.announcements a
LEFT JOIN seap.cui_location cl ON cl.cui = a.authority_cui
WHERE a.authority_cui IS NOT NULL
  AND a.awarded_value IS NOT NULL
GROUP BY a.authority_cui;

CREATE UNIQUE INDEX IF NOT EXISTS idx_mv_top_auth_pk ON seap.mv_top_authorities(authority_cui);
CREATE INDEX IF NOT EXISTS idx_mv_top_auth_value ON seap.mv_top_authorities(total_value DESC NULLS LAST);


-- ──────────────────────────────────────────────────────────────────────
-- mv_recurrent_pairs: powers /retete/perechi-recurente
-- ──────────────────────────────────────────────────────────────────────
DROP MATERIALIZED VIEW IF EXISTS seap.mv_recurrent_pairs CASCADE;
CREATE MATERIALIZED VIEW seap.mv_recurrent_pairs AS
SELECT
  a.authority_cui,
  MIN(a.authority_name) AS authority_name,
  regexp_replace(upper(a.supplier_cui), '(^RO)|\s+', '', 'g') AS supplier_cui_norm,
  MIN(a.supplier_name) AS supplier_name,
  MIN(cl.county) AS county,
  COUNT(*)::int AS contracts,
  COALESCE(SUM(a.awarded_value), 0)::numeric(20,2) AS total_value,
  MIN(EXTRACT(YEAR FROM a.publication_date))::int AS first_year,
  MAX(EXTRACT(YEAR FROM a.publication_date))::int AS last_year
FROM seap.announcements a
LEFT JOIN seap.cui_location cl ON cl.cui = a.authority_cui
WHERE a.authority_cui IS NOT NULL
  AND a.supplier_cui IS NOT NULL
  AND a.awarded_value IS NOT NULL
GROUP BY a.authority_cui, regexp_replace(upper(a.supplier_cui), '(^RO)|\s+', '', 'g')
HAVING COUNT(*) >= 5;

CREATE UNIQUE INDEX IF NOT EXISTS idx_mv_recurr_pk ON seap.mv_recurrent_pairs(authority_cui, supplier_cui_norm);
CREATE INDEX IF NOT EXISTS idx_mv_recurr_value ON seap.mv_recurrent_pairs(total_value DESC NULLS LAST);


-- ──────────────────────────────────────────────────────────────────────
-- mv_supplier_cpv_share: powers /retete/firme-specializate-extrem
-- ──────────────────────────────────────────────────────────────────────
DROP MATERIALIZED VIEW IF EXISTS seap.mv_supplier_cpv_share CASCADE;
CREATE MATERIALIZED VIEW seap.mv_supplier_cpv_share AS
WITH supplier_cpv AS (
  SELECT
    regexp_replace(upper(a.supplier_cui), '(^RO)|\s+', '', 'g') AS cui,
    MIN(a.supplier_name) AS name,
    a.cpv_division,
    MIN(c.name_ro) AS cpv_name,
    MIN(c.emoji) AS emoji,
    COUNT(*)::int AS contracts,
    COALESCE(SUM(a.awarded_value), 0)::numeric(20,2) AS cpv_value
  FROM seap.announcements a
  LEFT JOIN seap.cpv_codes c ON c.code = a.cpv_division
  WHERE a.supplier_cui IS NOT NULL
    AND a.cpv_division IS NOT NULL
    AND a.awarded_value IS NOT NULL
  GROUP BY 1, a.cpv_division
),
supplier_total AS (
  SELECT cui, SUM(cpv_value) AS total
  FROM supplier_cpv
  GROUP BY cui
  HAVING SUM(cpv_value) >= 5000000
),
ranked AS (
  SELECT
    sc.cui, sc.name, sc.cpv_division, sc.cpv_name, sc.emoji,
    sc.contracts, sc.cpv_value,
    st.total,
    (sc.cpv_value / st.total)::numeric(8,4) AS share,
    ROW_NUMBER() OVER (PARTITION BY sc.cui ORDER BY sc.cpv_value DESC) AS rn
  FROM supplier_cpv sc
  JOIN supplier_total st ON st.cui = sc.cui
)
SELECT * FROM ranked WHERE rn = 1;

CREATE UNIQUE INDEX IF NOT EXISTS idx_mv_sup_cpv_pk ON seap.mv_supplier_cpv_share(cui);
CREATE INDEX IF NOT EXISTS idx_mv_sup_cpv_share ON seap.mv_supplier_cpv_share(share DESC, total DESC);

COMMIT;

-- Refresh helper
CREATE OR REPLACE FUNCTION seap.refresh_recipe_mvs() RETURNS void LANGUAGE sql AS $$
  REFRESH MATERIALIZED VIEW CONCURRENTLY seap.mv_top_cpv_divisions;
  REFRESH MATERIALIZED VIEW CONCURRENTLY seap.mv_top_suppliers;
  REFRESH MATERIALIZED VIEW CONCURRENTLY seap.mv_top_authorities;
  REFRESH MATERIALIZED VIEW CONCURRENTLY seap.mv_recurrent_pairs;
  REFRESH MATERIALIZED VIEW CONCURRENTLY seap.mv_supplier_cpv_share;
$$;