Claude VM
a6c03a091e
Moved from gov-agreg/src/pages/achizitii/* to root (drop prefix). - 22 pages migrated, 127 files total - All internal links: /achizitii/X → /X (176 occurrences fixed) - AchizitiiLayout subnav rewritten: /X paths, top-right link to vreaudigital.ro hub - BaseLayout new (vreau.digital branding, OG tags, site URL) - astro.config.mjs: site https://vreau.digital, server output (was static) - docker-compose: port 5096 (vreaudigital is 5095), container vreau-digital - deploy.sh: paths /opt/vreau-digital, log /var/log/vreau-digital-deploy.log Backend shared with gov-agreg: - PostgreSQL satra (same schemas: seap, firms, anaf, anre, ...) - Photon, Martin tiles - Infisical /vreaudigital path (DATABASE_URL etc. shared) build: PASS (npx astro check 0 errors, npm run build 5s vite + 10s server)
119 lines
6.5 KiB
SQL
119 lines
6.5 KiB
SQL
-- 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.';
|