-- 016_firms_financials_categories.sql
-- Separate tables for the non-WEB_UU/BL_BS_SL MFP financial categories.
-- Schemas differ enough between categories (ONG=46 indicators with separate
-- non-profit + economic activity tracking; banks=23 IFRS-specific indicators)
-- that lumping them with firms.financials would distort the existing schema.
--
-- We store raw indicators in JSONB to avoid 46-column tables and to absorb
-- future ANAF schema tweaks without migrations. A handful of canonical
-- columns lifted out of JSONB for fast querying / recipe filtering.

-- ─── ONG (Asociații, Fundații) ────────────────────────────────────────────
-- Source: WEB_ONG_AN20XX.txt on data.gov.ro situatii_financiare_<year>
-- Schema: CUI, CAEN, CAENO, i1..i46
--   i12 = Capitaluri proprii
--   i37 = Venituri totale (prevederi anuale)
--   i38 = Venituri totale (realizat la 31.12)
--   i39 = Cheltuieli totale (prevederi anuale)
--   i40 = Cheltuieli totale (realizat la 31.12)
--   i41 = Excedent / Profit (prevederi anuale)
--   i42 = Excedent / Profit (realizat la 31.12)
--   i45 = Personal activități fără scop patrimonial
--   i46 = Personal activități economice

CREATE TABLE IF NOT EXISTS firms.financials_ong (
  cui                  text NOT NULL,
  year                 integer NOT NULL,
  caen                 text,                  -- activitate economică
  caeno                text,                  -- activitate fără scop patrimonial
  -- Lifted convenience columns:
  capitaluri_proprii   numeric(20,2),         -- i12
  venituri_total       numeric(20,2),         -- i38
  cheltuieli_total     numeric(20,2),         -- i40
  excedent             numeric(20,2),         -- i42
  personal_neeconomic  bigint,                -- i45
  personal_economic    bigint,                -- i46
  -- Raw indicators for completeness:
  indicators           jsonb NOT NULL,        -- {i1..i46} all values
  source               text DEFAULT 'mfinante:WEB_ONG',
  fetched_at           timestamptz DEFAULT now(),
  PRIMARY KEY (cui, year)
);
CREATE INDEX IF NOT EXISTS idx_fin_ong_year ON firms.financials_ong(year);
CREATE INDEX IF NOT EXISTS idx_fin_ong_caen ON firms.financials_ong(caen);

-- Staging table: 49 columns (CUI, CAEN, CAENO, i1..i46)
CREATE TABLE IF NOT EXISTS firms.staging_ong (
  cui   text,
  caen  text,
  caeno text,
  i1  text, i2  text, i3  text, i4  text, i5  text, i6  text, i7  text,
  i8  text, i9  text, i10 text, i11 text, i12 text, i13 text, i14 text,
  i15 text, i16 text, i17 text, i18 text, i19 text, i20 text, i21 text,
  i22 text, i23 text, i24 text, i25 text, i26 text, i27 text, i28 text,
  i29 text, i30 text, i31 text, i32 text, i33 text, i34 text, i35 text,
  i36 text, i37 text, i38 text, i39 text, i40 text, i41 text, i42 text,
  i43 text, i44 text, i45 text, i46 text
);


-- ─── Bănci / Instituții de Credit ─────────────────────────────────────────
-- Source: WEB_Inst_de_credit_20XX.txt on data.gov.ro situatii_financiare_<year>
-- Schema: CUI, CAEN, i1..i23 (IFRS bank-specific)
--   i6  = Active financiare evaluate la cost amortizat
--   i14 = Capital social
--   i17 = Profit / (-) pierdere aferent exercițiului
--   i19 = Profit / pierdere din operațiuni continue înainte de impozitare
--   i22 = Profit / pierdere aferent exercițiului
--   i23 = Cifra de afaceri netă
-- (Note: source CSV has a typo for i18 — column header "18" without "i"
--  prefix. Importer treats it consistently as i18.)

CREATE TABLE IF NOT EXISTS firms.financials_banks (
  cui                       text NOT NULL,
  year                      integer NOT NULL,
  caen                      text,
  -- Lifted convenience columns:
  active_financiare_amortiz numeric(20,2),    -- i6
  capital_social            numeric(20,2),    -- i14
  profit_exercitiu          numeric(20,2),    -- i22
  profit_inainte_impozit    numeric(20,2),    -- i19
  cifra_afaceri             numeric(20,2),    -- i23
  -- Raw indicators for completeness:
  indicators                jsonb NOT NULL,   -- {i1..i23}
  source                    text DEFAULT 'mfinante:WEB_Inst_de_credit',
  fetched_at                timestamptz DEFAULT now(),
  PRIMARY KEY (cui, year)
);
CREATE INDEX IF NOT EXISTS idx_fin_banks_year ON firms.financials_banks(year);

CREATE TABLE IF NOT EXISTS firms.staging_banks (
  cui  text,
  caen text,
  i1  text, i2  text, i3  text, i4  text, i5  text, i6  text, i7  text,
  i8  text, i9  text, i10 text, i11 text, i12 text, i13 text, i14 text,
  i15 text, i16 text, i17 text, i18 text, i19 text, i20 text, i21 text,
  i22 text, i23 text
);
