vreau-digital/services/seap-scraper/sql/001_init.sql

-- SEAP Data Schema for Harta Banilor Publici
-- Runs inside architools_db, isolated in schema "seap"
-- ZERO modifications to existing public.* tables

BEGIN;

-- Enable extensions needed for fuzzy matching
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS unaccent;

CREATE SCHEMA IF NOT EXISTS seap;

-- ── Entități SEAP (autorități contractante + furnizori) ──

CREATE TABLE seap.entities (
  entity_id     INTEGER PRIMARY KEY,
  entity_type   TEXT NOT NULL CHECK (entity_type IN ('authority', 'supplier')),
  fiscal_number TEXT,
  name          TEXT NOT NULL,
  city          TEXT,
  county        TEXT,
  address       TEXT,
  postal_code   TEXT,
  is_utility    BOOLEAN,
  siruta        TEXT REFERENCES public."GisUat"(siruta),
  match_score   REAL,
  fetched_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_entities_fiscal ON seap.entities(fiscal_number);
CREATE INDEX idx_entities_siruta ON seap.entities(siruta);
CREATE INDEX idx_entities_type   ON seap.entities(entity_type);
CREATE INDEX idx_entities_county ON seap.entities(county);

-- ── Achiziții directe ──

CREATE TABLE seap.direct_acquisitions (
  id                  INTEGER PRIMARY KEY,
  unique_code         TEXT UNIQUE,
  name                TEXT,
  cpv_code            TEXT,
  cpv_name            TEXT,
  publication_date    TIMESTAMPTZ,
  finalization_date   TIMESTAMPTZ,
  estimated_value     NUMERIC(15,2),
  closing_value       NUMERIC(15,2),
  currency            TEXT DEFAULT 'RON',
  state_id            INTEGER,
  state_text          TEXT,
  contract_type_id    INTEGER,
  contract_type_text  TEXT,
  eu_fund_id          INTEGER,
  eu_fund_text        TEXT,
  authority_id        INTEGER REFERENCES seap.entities(entity_id),
  supplier_id         INTEGER REFERENCES seap.entities(entity_id),
  fetched_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_da_authority     ON seap.direct_acquisitions(authority_id);
CREATE INDEX idx_da_supplier      ON seap.direct_acquisitions(supplier_id);
CREATE INDEX idx_da_finalization  ON seap.direct_acquisitions(finalization_date);
CREATE INDEX idx_da_publication   ON seap.direct_acquisitions(publication_date);
CREATE INDEX idx_da_cpv           ON seap.direct_acquisitions(cpv_code);
CREATE INDEX idx_da_value         ON seap.direct_acquisitions(closing_value);

-- ── Licitații publice (contract award notices) ──

CREATE TABLE seap.public_notices (
  id                  INTEGER PRIMARY KEY,
  notice_no           TEXT,
  contract_title      TEXT,
  cpv_code            TEXT,
  cpv_name            TEXT,
  estimated_value     NUMERIC(15,2),
  contract_value      NUMERIC(15,2),
  currency            TEXT DEFAULT 'RON',
  publication_date    TIMESTAMPTZ,
  state_date          TIMESTAMPTZ,
  procedure_type_id   INTEGER,
  procedure_type_text TEXT,
  contract_type_id    INTEGER,
  contract_type_text  TEXT,
  notice_type_id      INTEGER,
  state_id            INTEGER,
  state_text          TEXT,
  authority_id        INTEGER REFERENCES seap.entities(entity_id),
  authority_city      TEXT,
  authority_county    TEXT,
  authority_siruta    TEXT REFERENCES public."GisUat"(siruta),
  has_lots            BOOLEAN DEFAULT false,
  fetched_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_pn_authority ON seap.public_notices(authority_id);
CREATE INDEX idx_pn_date      ON seap.public_notices(publication_date);
CREATE INDEX idx_pn_siruta    ON seap.public_notices(authority_siruta);
CREATE INDEX idx_pn_cpv       ON seap.public_notices(cpv_code);

-- ── Contracte câștigate (din section 5 a licitațiilor) ──

CREATE TABLE seap.notice_contracts (
  id              SERIAL PRIMARY KEY,
  notice_id       INTEGER REFERENCES seap.public_notices(id),
  lot_number      INTEGER,
  lot_title       TEXT,
  contract_value  NUMERIC(15,2),
  currency        TEXT DEFAULT 'RON',
  contract_date   DATE,
  winner_id       INTEGER REFERENCES seap.entities(entity_id),
  winner_name     TEXT,
  winner_fiscal   TEXT,
  winner_city     TEXT,
  winner_county   TEXT,
  winner_siruta   TEXT REFERENCES public."GisUat"(siruta),
  num_offers      INTEGER
);

CREATE INDEX idx_nc_notice       ON seap.notice_contracts(notice_id);
CREATE INDEX idx_nc_winner       ON seap.notice_contracts(winner_id);
CREATE INDEX idx_nc_winner_siruta ON seap.notice_contracts(winner_siruta);

-- ── Matching localități SEAP → SIRUTA ──

CREATE TABLE seap.locality_map (
  seap_city    TEXT NOT NULL,
  seap_county  TEXT NOT NULL,
  siruta       TEXT REFERENCES public."GisUat"(siruta),
  match_type   TEXT,
  confidence   REAL,
  PRIMARY KEY (seap_city, seap_county)
);

-- ── Stare sync scraper ──

CREATE TABLE seap.sync_state (
  source     TEXT PRIMARY KEY,
  last_date  TIMESTAMPTZ,
  last_id    INTEGER,
  status     TEXT,
  updated_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO seap.sync_state (source, status) VALUES
  ('da', 'pending'),
  ('notices', 'pending');

-- ── Helper: normalize locality names ──

CREATE OR REPLACE FUNCTION seap.normalize_locality(input TEXT)
RETURNS TEXT LANGUAGE sql IMMUTABLE AS $$
  SELECT lower(trim(unaccent(
    regexp_replace(input, '\s+', ' ', 'g')
  )));
$$;

-- ── Materialized view: procurement stats per UAT ──

CREATE MATERIALIZED VIEW seap.uat_procurement_stats AS
SELECT
  u.siruta,
  u.name   AS uat_name,
  u.county,
  COALESCE(da_stats.da_count, 0)        AS da_count,
  COALESCE(da_stats.da_total_value, 0)   AS da_total_value,
  COALESCE(pn_stats.notice_count, 0)     AS notice_count,
  COALESCE(pn_stats.notice_total_value, 0) AS notice_total_value,
  COALESCE(da_stats.da_count, 0) + COALESCE(pn_stats.notice_count, 0) AS total_contracts,
  COALESCE(da_stats.da_total_value, 0) + COALESCE(pn_stats.notice_total_value, 0) AS total_value
FROM public."GisUat" u
LEFT JOIN LATERAL (
  SELECT
    COUNT(*)           AS da_count,
    SUM(da.closing_value) AS da_total_value
  FROM seap.direct_acquisitions da
  JOIN seap.entities e ON e.entity_id = da.authority_id
  WHERE e.siruta = u.siruta
) da_stats ON true
LEFT JOIN LATERAL (
  SELECT
    COUNT(*)                AS notice_count,
    SUM(pn.contract_value)  AS notice_total_value
  FROM seap.public_notices pn
  WHERE pn.authority_siruta = u.siruta
) pn_stats ON true;

CREATE UNIQUE INDEX idx_ups_siruta ON seap.uat_procurement_stats(siruta);

COMMIT;