-- 021_regas_ajutoare.sql
-- RegAS — Registrul Ajutoarelor de Stat (Consiliul Concurenței).
-- Source: https://regas.consiliulconcurentei.ro/transparenta/index.html
-- API: POST /apitransparenta/cautareTransparenta (XSRF-TOKEN cookie + header)
-- Volume at first scrape (2026-05-09): 132,363 ajutoare individuale.
--
-- Each row = one ajutor de stat acordat unei firme (denumireBeneficiar+cui),
-- cu masura (referintaMasura SA.xxx/yyyy), categorie/subcategorie, suma în RON,
-- finantator (ministerul/agentia care a dat banii), instrumentAcordare
-- (fonduri nerambursabile / credite / garantii / scutiri).
--
-- Cheie naturală: (cui, idMasura, dataAcordare, ajutorAcordatSubcategorie) —
-- același beneficiar poate primi multiple tranșe pe aceeași măsură. Folosim
-- un id sintetic SHA1 pentru ON CONFLICT idempotent.

CREATE SCHEMA IF NOT EXISTS regas;

CREATE TABLE IF NOT EXISTS regas.ajutoare (
  id                          char(40) PRIMARY KEY,        -- sha1(cui|idMasura|dataAcordare|subcategorie|sumaSubcategorie)
  cui                         text NOT NULL,
  denumire_beneficiar         text NOT NULL,
  raf                         text,                        -- "Registrul Ajutoarelor Fiscale" id (rare)
  alt_cod                     text,                        -- ID alternativ (rare)
  dimensiune_intreprindere    text,                        -- "întreprindere mare" / "IMM" / etc.
  regiune                     text[],                      -- ["Regiunea I Nord-Est", ...] sau ["Toate regiunile"]
  domeniu_activitate          text,                        -- CAEN cod (4 digits)
  data_acordare               date,                        -- "DD/MM/YYYY" parsed
  id_masura                   integer NOT NULL,            -- FK logic la /apitransparenta/downloadPdfMasura/{idMasura}
  denumire_masura             text NOT NULL,
  referinta_masura            text,                        -- "SA.104966/2022"
  activitati_finantate        text[],                      -- CAEN codes
  categorie                   text,
  subcategorie                text,
  obiectiv                    text,
  ajutor_acordat_categorie    numeric(20,2),               -- RON
  ajutor_acordat_subcategorie numeric(20,2),               -- RON (de obicei = categorie pentru o singură subcategorie)
  intermediari_financiari     text[],
  executanti                  text[],
  instrument_acordare         text,                        -- "fonduri nerambursabile" / "garantii" / "credite"
  intensitate                 numeric(6,2),                -- procent (e.g. 65.00)
  finantator                  text,                        -- "Ministerul ... (MIPE)"
  pdf_masura                  text,                        -- "1195_MASURA.pdf"
  fetched_at                  timestamptz DEFAULT now(),
  raw_json                    jsonb
);

CREATE INDEX IF NOT EXISTS idx_regas_ajutoare_cui ON regas.ajutoare(cui);
CREATE INDEX IF NOT EXISTS idx_regas_ajutoare_data ON regas.ajutoare(data_acordare DESC NULLS LAST);
CREATE INDEX IF NOT EXISTS idx_regas_ajutoare_finantator ON regas.ajutoare(finantator);
CREATE INDEX IF NOT EXISTS idx_regas_ajutoare_referinta ON regas.ajutoare(referinta_masura);
CREATE INDEX IF NOT EXISTS idx_regas_ajutoare_id_masura ON regas.ajutoare(id_masura);

COMMENT ON TABLE regas.ajutoare IS
  'State aid records from Consiliul Concurentei RegAS portal. One row per ajutor acordat. Source: https://regas.consiliulconcurentei.ro/transparenta/';

COMMENT ON COLUMN regas.ajutoare.id IS
  'Synthetic sha1 of (cui|idMasura|dataAcordare|subcategorie|ajutorAcordatSubcategorie). Used for idempotent upsert.';

-- Materialized view aggregating per CUI for fast firma profile lookup.
CREATE MATERIALIZED VIEW IF NOT EXISTS regas.mv_ajutoare_per_cui AS
SELECT
  cui,
  COUNT(*) AS nr_ajutoare,
  SUM(ajutor_acordat_subcategorie) AS total_ron,
  COUNT(DISTINCT id_masura) AS nr_masuri,
  COUNT(DISTINCT finantator) AS nr_finantatori,
  MIN(data_acordare) AS prima_acordare,
  MAX(data_acordare) AS ultima_acordare,
  array_agg(DISTINCT finantator) FILTER (WHERE finantator IS NOT NULL) AS finantatori,
  array_agg(DISTINCT instrument_acordare) FILTER (WHERE instrument_acordare IS NOT NULL) AS instrumente
FROM regas.ajutoare
GROUP BY cui;

CREATE UNIQUE INDEX IF NOT EXISTS idx_regas_mv_cui ON regas.mv_ajutoare_per_cui(cui);