-- 018_fonduri_beneficiar_privat.sql
-- Achiziții făcute de beneficiarii PRIVAȚI ai fondurilor europene
-- (firme care au primit POIM/POR/PNRR/AFIR etc. și trebuie să facă achiziții
-- transparente conform Manualului Beneficiarului — dar NU intră în SEAP fiindcă
-- sunt privați, nu autorități publice).
--
-- Source: https://beneficiar.fonduri-ue.ro:8080/anunturi
-- Volume: ~48,650 anunțuri (and growing) cu loturi atașate.
-- Joinable to firms.entities prin name (no CUI in source — fuzzy match).
-- Joinable to fonduri.afir_plati prin name (overlap pe beneficiari AFIR).
-- Joinable to seap.announcements prin supplier_cui când suppliers selected pe
-- aceste proceduri devin furnizori la stat (cross-source signal).

CREATE TABLE IF NOT EXISTS fonduri.beneficiar_anunt (
  id                       integer PRIMARY KEY,    -- the numeric ID din URL /anunturi/details/2/{ID}
  -- Project linkage
  smis_proiect_id          integer,                -- id linkable cu /proiecte/details/{type}/{id}
  smis_proiect_type        smallint,               -- 1=SMIS, 2=MySMIS, 3=PNRR, etc. (URL prefix)
  smis_proiect_code        text,                   -- "319946" (for display)
  smis_proiect_name        text,                   -- "CONSTRUIRE CAMIN BATRANI..."
  -- Beneficiar (privat)
  beneficiar_name          text NOT NULL,
  beneficiar_program_tag   text,                   -- "SMIS" | "MySMIS" | other
  beneficiar_adresa        text,
  beneficiar_contact       text,
  beneficiar_telefon       text,
  beneficiar_regiune       text,
  beneficiar_judet         text,
  beneficiar_localitate    text,
  -- Anunț status / details
  procedura_status         text,                   -- "în curs de ofertare" | "închisă"
  data_publicare           date,
  data_limita_oferta       date,
  ora_limita_oferta        text,
  judet                    text,                   -- județul anunțului (poate diferi de beneficiar_judet)
  tip_contract             text,                   -- "Furnizare" | "Servicii" | "Lucrări"
  versiune_specificatii    text,
  titlu                    text,                   -- titlul anunțului (din list page)
  -- CUI enrichment (fuzzy match against firms.entities, second pass)
  cui                      text,
  cui_match_score          real,
  cui_match_method         text,                   -- 'exact_name' | 'trgm' | 'manual'
  matched_at               timestamptz,
  -- Source tracking
  fetched_at               timestamptz DEFAULT now(),
  raw_html_sha256          char(64)                -- to detect re-fetch needed
);

CREATE INDEX IF NOT EXISTS idx_ben_anunt_smis ON fonduri.beneficiar_anunt(smis_proiect_id);
CREATE INDEX IF NOT EXISTS idx_ben_anunt_judet ON fonduri.beneficiar_anunt(judet);
CREATE INDEX IF NOT EXISTS idx_ben_anunt_data ON fonduri.beneficiar_anunt(data_publicare DESC);
CREATE INDEX IF NOT EXISTS idx_ben_anunt_cui ON fonduri.beneficiar_anunt(cui) WHERE cui IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_ben_anunt_name_trgm ON fonduri.beneficiar_anunt USING gin (beneficiar_name gin_trgm_ops);


CREATE TABLE IF NOT EXISTS fonduri.beneficiar_anunt_lot (
  id              bigserial PRIMARY KEY,
  anunt_id        integer NOT NULL REFERENCES fonduri.beneficiar_anunt(id) ON DELETE CASCADE,
  lot_no          integer NOT NULL,                -- 1, 2, 3... (ordinal)
  lot_label       text,                            -- "1" or "Achiziție licențe" — heading text
  descriere_url   text,                            -- /desc-lot?d={lot_id}
  durata_contract text,                            -- e.g. "6 luni"
  buget_lei       numeric(20,2),
  cpv_cod         text,                            -- when present
  spec_url        text,
  fetched_at      timestamptz DEFAULT now(),
  UNIQUE(anunt_id, lot_no)
);

-- Add lot_label column if migrating from earlier version
ALTER TABLE fonduri.beneficiar_anunt_lot ADD COLUMN IF NOT EXISTS lot_label text;

CREATE INDEX IF NOT EXISTS idx_ben_lot_anunt ON fonduri.beneficiar_anunt_lot(anunt_id);
CREATE INDEX IF NOT EXISTS idx_ben_lot_buget ON fonduri.beneficiar_anunt_lot(buget_lei DESC NULLS LAST);


-- Optional sister table for the EU project itself (we link via smis_proiect_id).
-- Populated by a separate /proiecte scraper later; placeholder schema for now:
CREATE TABLE IF NOT EXISTS fonduri.beneficiar_proiect (
  id                  integer PRIMARY KEY,         -- /proiecte/details/{type}/{id} → id
  proiect_type        smallint,                    -- 1=SMIS, 2=MySMIS, etc.
  smis_code           text,                        -- "319946"
  titlu               text,
  beneficiar_name     text,
  program_op          text,                        -- POIM/POR/POCU/PNRR/...
  axa_prioritara      text,
  valoare_totala_lei  numeric(20,2),
  valoare_eligibila_lei numeric(20,2),
  contributie_ue_lei  numeric(20,2),
  data_start          date,
  data_end            date,
  data_actualizare    date,
  judet               text,
  localitate          text,
  cui                 text,
  cui_match_score     real,
  fetched_at          timestamptz DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_ben_proiect_smis ON fonduri.beneficiar_proiect(smis_code);
CREATE INDEX IF NOT EXISTS idx_ben_proiect_program ON fonduri.beneficiar_proiect(program_op);
CREATE INDEX IF NOT EXISTS idx_ben_proiect_cui ON fonduri.beneficiar_proiect(cui) WHERE cui IS NOT NULL;