-- Financial indicators per firm-year, from Ministerul Finanțelor "Situații financiare"
-- annual datasets on data.gov.ro (CC-BY 4.0).
--
-- 21 indicators (I1-I20 + CAEN) extracted from balance sheet + P&L + headcount.
-- Schema covers years 2020-2024 initially; older years available too if needed.

BEGIN;

CREATE TABLE IF NOT EXISTS firms.financials (
  cui                 TEXT NOT NULL,
  year                INT NOT NULL,
  caen                TEXT,

  -- ── Bilanț — active ──
  active_imobilizate  NUMERIC(20,2),    -- I1
  active_circulante   NUMERIC(20,2),    -- I2
  stocuri             NUMERIC(20,2),    -- I3
  creante             NUMERIC(20,2),    -- I4
  casa_banci          NUMERIC(20,2),    -- I5
  cheltuieli_avans    NUMERIC(20,2),    -- I6

  -- ── Bilanț — datorii / pasive ──
  datorii             NUMERIC(20,2),    -- I7
  venituri_avans      NUMERIC(20,2),    -- I8
  provizioane         NUMERIC(20,2),    -- I9
  capitaluri_total    NUMERIC(20,2),    -- I10
  capital_subscris    NUMERIC(20,2),    -- I11
  patrimoniul_regiei  NUMERIC(20,2),    -- I12

  -- ── Cont profit/pierdere ──
  cifra_afaceri       NUMERIC(20,2),    -- I13 (cifră afaceri netă)
  venituri_total      NUMERIC(20,2),    -- I14
  cheltuieli_total    NUMERIC(20,2),    -- I15
  profit_brut         NUMERIC(20,2),    -- I16
  pierdere_bruta      NUMERIC(20,2),    -- I17
  profit_net          NUMERIC(20,2),    -- I18
  pierdere_neta       NUMERIC(20,2),    -- I19

  -- ── HR ──
  numar_salariati     BIGINT,           -- I20 (some data anomalies need wider range)

  -- ── Metadata ──
  source              TEXT DEFAULT 'mfinante.data.gov.ro',
  fetched_at          TIMESTAMPTZ DEFAULT now(),

  PRIMARY KEY (cui, year)
);

CREATE INDEX IF NOT EXISTS idx_fin_cui ON firms.financials(cui);
CREATE INDEX IF NOT EXISTS idx_fin_year ON firms.financials(year);
CREATE INDEX IF NOT EXISTS idx_fin_ca_desc ON firms.financials(year, cifra_afaceri DESC NULLS LAST);
CREATE INDEX IF NOT EXISTS idx_fin_profit_desc ON firms.financials(year, profit_net DESC NULLS LAST);
CREATE INDEX IF NOT EXISTS idx_fin_salariati_desc ON firms.financials(year, numar_salariati DESC NULLS LAST);
CREATE INDEX IF NOT EXISTS idx_fin_caen ON firms.financials(caen);

-- Materialized view: latest year financials per CUI for fast profile lookup
CREATE MATERIALIZED VIEW IF NOT EXISTS firms.mv_financials_latest AS
SELECT DISTINCT ON (cui) *
FROM firms.financials
WHERE cui IS NOT NULL
ORDER BY cui, year DESC;

CREATE UNIQUE INDEX IF NOT EXISTS idx_mv_fin_latest_pk ON firms.mv_financials_latest(cui);

-- Staging table for raw CSV imports
CREATE TABLE IF NOT EXISTS firms.staging_financials (
  cui   TEXT,
  caen  TEXT,
  i1    NUMERIC, i2 NUMERIC, i3 NUMERIC, i4 NUMERIC, i5 NUMERIC,
  i6    NUMERIC, i7 NUMERIC, i8 NUMERIC, i9 NUMERIC, i10 NUMERIC,
  i11   NUMERIC, i12 NUMERIC, i13 NUMERIC, i14 NUMERIC, i15 NUMERIC,
  i16   NUMERIC, i17 NUMERIC, i18 NUMERIC, i19 NUMERIC, i20 NUMERIC
);

COMMIT;