-- CPV nomenclature: 9,454 codes with Romanian names + EU emojis.
-- Loaded from samhallskod/cpv-eu (data sourced from official EU CPV 2008 XML).
BEGIN;

CREATE TABLE IF NOT EXISTS seap.cpv_codes (
  code           TEXT PRIMARY KEY,            -- 8-digit (no check digit), e.g. '45000000'
  code_full      TEXT,                         -- 8-digit + check, e.g. '45000000-7'
  name_ro        TEXT NOT NULL,
  name_en        TEXT,
  level          INT NOT NULL,                 -- 1=division (45), 2=group (450), 3=class (4500), ...
  division_code  TEXT NOT NULL,                -- first 2 digits + 6 zeroes, e.g. '45000000' (top-level parent)
  parent_code    TEXT,                         -- one level up
  emoji          TEXT,                         -- only set on division level
  imported_at    TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_cpv_division ON seap.cpv_codes(division_code);
CREATE INDEX IF NOT EXISTS idx_cpv_parent ON seap.cpv_codes(parent_code);
CREATE INDEX IF NOT EXISTS idx_cpv_level ON seap.cpv_codes(level);
CREATE INDEX IF NOT EXISTS idx_cpv_name_trgm ON seap.cpv_codes USING gin(name_ro gin_trgm_ops);


-- Helper: normalize "45123456-7" or "45123456" or empty → "45123456" (8-digit, no dash)
CREATE OR REPLACE FUNCTION seap.cpv_normalize(code TEXT)
RETURNS TEXT AS $$
BEGIN
  IF code IS NULL OR code = '' THEN RETURN NULL; END IF;
  -- Strip the check digit suffix (-X) and any whitespace
  RETURN regexp_replace(trim(code), '-[0-9]$', '');
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;


-- Helper: get division code (first 2 digits + 6 zeros)
CREATE OR REPLACE FUNCTION seap.cpv_division(code TEXT)
RETURNS TEXT AS $$
BEGIN
  IF code IS NULL OR length(code) < 2 THEN RETURN NULL; END IF;
  RETURN substr(seap.cpv_normalize(code), 1, 2) || '000000';
END;
$$ LANGUAGE plpgsql IMMUTABLE STRICT;


-- Get name_ro for a code, fallback to division name, fallback to code itself
CREATE OR REPLACE FUNCTION seap.cpv_name(code TEXT)
RETURNS TEXT AS $$
DECLARE
  result TEXT;
BEGIN
  SELECT name_ro INTO result FROM seap.cpv_codes WHERE code = seap.cpv_normalize($1);
  IF result IS NOT NULL THEN RETURN result; END IF;
  SELECT name_ro INTO result FROM seap.cpv_codes WHERE code = seap.cpv_division($1);
  IF result IS NOT NULL THEN RETURN result; END IF;
  RETURN $1;
END;
$$ LANGUAGE plpgsql STABLE;


-- Get top-level category name + emoji for any code
CREATE OR REPLACE VIEW seap.cpv_division_lookup AS
SELECT code AS division_code, name_ro AS division_name, emoji
FROM seap.cpv_codes WHERE level = 1;


-- Add denormalized columns to announcements for fast queries
ALTER TABLE seap.announcements ADD COLUMN IF NOT EXISTS cpv_division TEXT;
ALTER TABLE seap.announcements ADD COLUMN IF NOT EXISTS cpv_name_ro  TEXT;

CREATE INDEX IF NOT EXISTS idx_ann_cpv_division ON seap.announcements(cpv_division);

COMMIT;