-- 015_firms_onrc_extras.sql
-- Two additional ONRC bulk CSVs we weren't importing yet:
--   1. od_reprezentanti_if.csv — administrators of "Întreprinderi Familiale"
--      (~80K rows). The persoană field plus locality+county of birth gives us
--      a separate small "owner registry" parallel to rep_legali on firms.entities.
--   2. od_sucursale_alte_state_membre.csv — branches of RO companies registered
--      in other EU states (~tiny, ~hundreds of rows). Useful for follow-the-money
--      questions like "RO firm with EU branches winning EU-funded contracts".
--
-- Both are keyed by cod_inmatriculare which we already have on firms.entities,
-- so JOINs are trivial. Idempotent: TRUNCATE-and-reload on each ONRC snapshot.

CREATE TABLE IF NOT EXISTS firms.reprezentanti_if (
  cod_inmatriculare    text NOT NULL,
  nume                 text,
  data_nastere         text,                  -- raw DD.MM.YYYY string from ONRC
  localitate_nastere   text,
  judet_nastere        text,
  tara_nastere         text,
  calitate             text
);
CREATE INDEX IF NOT EXISTS idx_rep_if_cod ON firms.reprezentanti_if(cod_inmatriculare);

CREATE TABLE IF NOT EXISTS firms.sucursale_ue (
  cod_inmatriculare    text NOT NULL,
  tip_unitate          text,                  -- usually "Sucursală"
  denumire_sucursala   text,
  euid                 text,
  cod_fiscal_strain    text,                  -- ONRC field is COD_FISCAL but it's the foreign one
  tara                 text                   -- destination country
);
CREATE INDEX IF NOT EXISTS idx_sucursale_ue_cod ON firms.sucursale_ue(cod_inmatriculare);