-- 032_aaas.sql
-- AAAS — Autoritatea pentru Administrarea Activelor Statului.
-- Manages the state's residual ownership in privatized firms + collects
-- post-privatization debts. Tagging firms with "state still owns" /
-- "owes state money" / "post-priv investment obligation" is rare and powerful.
--
-- Sources investigated 2026-05-10:
--   1. https://www.aaas.gov.ro/despre-aaas/1-9-guvernanta-corporativa-aaas/
--      1-9-3-companii-sub-autoritatea-aaas/
--      → 12 named active-portfolio companies; each has a clean subpage with
--        CUI, J-number, address, phone, web, email, AAAS share %.
--   2. https://www.aaas.gov.ro/4-oferta-a-a-a-s/4-2-vanzari-actiuni/
--      → "SECȚIUNE ÎN CONSTRUCȚIE" — only EXPO PARC SRL Iași as PDF teaser.
--   3. https://www.aaas.gov.ro/4-oferta-a-a-a-s/4-3-valorificare-creante/
--      → "SECȚIUNE ÎN CONSTRUCȚIE" — debt list not published structured online.
--   4. https://gwp.aaas.gov.ro/Directia-creante
--      → Login-gated services portal; no anonymous CUI/debtor lookup.
--
-- This schema is intentionally narrow: 12-15 confirmed CUIs is small but
-- HIGH SIGNAL — every firms profile that joins back here gets "STAT DEȚINE
-- ACȚIUNI" tag. Future passes can ingest historical lists (e.g. ORDIN
-- 278/2005 PDF — 800+ commercial companies × 41 counties as legacy snapshot).
--
-- Cross-source value:
--   aaas.firme.cui × seap.announcements.supplier_cui
--     = "Companies in active state portfolio winning more state contracts"
--   aaas.firme.cui × anaf.datornici
--     = "State-owned company that itself owes the state money"
--   aaas.firme.cui × firms.financials
--     = "How is the residual state-owned portfolio actually performing?"

CREATE SCHEMA IF NOT EXISTS aaas;

-- ── 1. Firme sub autoritatea AAAS / monitorizate de AAAS ───────────────────
-- One row per company, keyed by CUI. Status enum captures the AAAS
-- relationship type. Re-runs UPSERT on cui (last_action / state_share_pct
-- can change). Original AAAS subpage URL preserved for traceability.
CREATE TABLE IF NOT EXISTS aaas.firme (
  cui                text PRIMARY KEY,
  name               text NOT NULL,                         -- raw name as published by AAAS
  name_normalized    text,                                  -- firms.normalize_company_name(name)
  reg_number         text,                                  -- "J40/8215/2020"
  aaas_status        text NOT NULL,                         -- 'active_holding' | 'post_priv_debt' | 'insolventa' | 'recuperare' | 'vanzare_actiuni' | 'vanzare_creante'
  state_share_pct    numeric(6,3),                          -- "Participatie AAAS: 100%"
  debt_to_state_lei  numeric(20,2),                         -- only when AAAS publishes structured amount
  last_action        text,                                  -- short description of last AAAS action (free-form)
  last_action_date   date,
  address            text,                                  -- "Adresa: ..." line
  phone              text,
  email              text,
  website            text,
  source_url         text NOT NULL,                         -- specific AAAS subpage
  notes              text,
  raw                jsonb,                                 -- full parsed key/value bag
  cui_match_score    numeric(4,3),                          -- only if matched via fuzzy (NULL if AAAS itself published the CUI)
  cui_match_method   text,                                  -- 'aaas_published' | 'exact_norm' | 'trgm_unique'
  fetched_at         timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_aaas_firme_status        ON aaas.firme(aaas_status);
CREATE INDEX IF NOT EXISTS idx_aaas_firme_debt          ON aaas.firme(debt_to_state_lei DESC NULLS LAST);
CREATE INDEX IF NOT EXISTS idx_aaas_firme_share_pct     ON aaas.firme(state_share_pct DESC NULLS LAST);
CREATE INDEX IF NOT EXISTS idx_aaas_firme_name_trgm     ON aaas.firme USING gin (name_normalized gin_trgm_ops);

COMMENT ON TABLE aaas.firme IS
  'Firme aflate sub autoritatea AAAS / monitorizate de AAAS. PK = CUI. '
  'Sursă primară: aaas.gov.ro subpages 1.9.3 + 4.2 + 4.3 (HTML scrape).';
COMMENT ON COLUMN aaas.firme.aaas_status IS
  'active_holding = AAAS deține pachet de acțiuni; '
  'post_priv_debt = obligații post-privatizare neîndeplinite; '
  'insolventa = în procedură de insolvență administrată de AAAS; '
  'recuperare = creanță în recuperare; '
  'vanzare_actiuni = ofertă de vânzare acțiuni publicată; '
  'vanzare_creante = ofertă de vânzare creanță publicată.';
COMMENT ON COLUMN aaas.firme.state_share_pct IS
  'Procent de participație AAAS în acționariat. 100 = stat unic acționar.';
COMMENT ON COLUMN aaas.firme.cui_match_method IS
  'aaas_published = CUI publicat direct de AAAS (autoritativ); '
  'exact_norm = match exact pe firms.normalize_company_name; '
  'trgm_unique = match trigram unic peste 0.85.';

-- ── 2. Scrape log (mirrors anre.scrape_log convention) ────────────────────
CREATE TABLE IF NOT EXISTS aaas.scrape_log (
  id            bigserial PRIMARY KEY,
  scraper       text NOT NULL,            -- 'portfolio' | 'vanzari_actiuni' | 'vanzari_creante'
  source_url    text NOT NULL,
  rows_seen     integer NOT NULL DEFAULT 0,
  rows_inserted integer NOT NULL DEFAULT 0,
  rows_updated  integer NOT NULL DEFAULT 0,
  rows_skipped  integer NOT NULL DEFAULT 0,
  duration_ms   integer NOT NULL DEFAULT 0,
  started_at    timestamptz NOT NULL,
  finished_at   timestamptz NOT NULL DEFAULT now(),
  error         text
);

CREATE INDEX IF NOT EXISTS idx_aaas_scrape_log_started ON aaas.scrape_log(started_at DESC);

-- ── 3. Materialized view: per-CUI rollup for joinability ──────────────────
-- Trivial today (1 row per cui), but the MV pattern is consistent with
-- anre.mv_licente_per_cui / regas / etc. and keeps the join API uniform
-- when more AAAS sources land. Refresh: REFRESH MATERIALIZED VIEW
-- CONCURRENTLY aaas.mv_per_cui;
CREATE MATERIALIZED VIEW IF NOT EXISTS aaas.mv_per_cui AS
SELECT
  cui,
  array_agg(DISTINCT aaas_status) AS statusuri,
  MAX(state_share_pct)            AS max_state_share_pct,
  SUM(debt_to_state_lei)          AS total_debt_to_state_lei,
  MAX(fetched_at)                 AS last_seen_at,
  COUNT(*)                        AS rows_count
FROM aaas.firme
GROUP BY cui;

CREATE UNIQUE INDEX IF NOT EXISTS idx_aaas_mv_per_cui ON aaas.mv_per_cui(cui);

COMMENT ON MATERIALIZED VIEW aaas.mv_per_cui IS
  'Rollup AAAS per CUI. Refresh: REFRESH MATERIALIZED VIEW CONCURRENTLY aaas.mv_per_cui.';