-- Yatify PostgreSQL reference model for National Treasury eTender OCDS
-- Public analytical model deliberately omits tender/contract monetary values.
CREATE TABLE etender_release (
  release_key text PRIMARY KEY,
  ocid text NOT NULL,
  release_id text,
  release_date timestamptz,
  tags text[],
  raw_json jsonb NOT NULL,
  ingested_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ix_etender_release_ocid_date ON etender_release(ocid, release_date DESC);

CREATE TABLE etender_process_latest (
  ocid text PRIMARY KEY,
  release_date date,
  stage text,
  buyer text,
  province text,
  delivery_location text,
  title text,
  sector text,
  civil_score integer,
  civil_type text,
  asphalt_intensity text,
  bitumen_seal_intensity text,
  is_western_cape boolean,
  is_cape_town boolean,
  tender_start date,
  tender_end date,
  classifier_evidence text[]
);

-- Latest release per contracting process
WITH ranked AS (
 SELECT ocid, release_date, raw_json,
        ROW_NUMBER() OVER(PARTITION BY ocid ORDER BY release_date DESC, release_key DESC) AS rn
 FROM etender_release
)
SELECT * FROM ranked WHERE rn=1;

-- Western Cape road procurement signal by stage
SELECT stage, asphalt_intensity, COUNT(*) AS processes
FROM etender_process_latest
WHERE is_western_cape AND sector='Roads / pavement'
GROUP BY stage, asphalt_intensity
ORDER BY stage, asphalt_intensity;