-- Controlled vocabularies (term sources) and their terms.
CREATE TABLE vocabulary (
    id  UUID PRIMARY KEY,
    key TEXT NOT NULL UNIQUE          -- e.g. 'material', 'object_name'
);

CREATE TABLE term (
    id            UUID PRIMARY KEY,
    vocabulary_id UUID NOT NULL REFERENCES vocabulary (id) ON DELETE RESTRICT,
    external_uri  TEXT                 -- e.g. Getty AAT / KulturNav / Wikidata URI
);
CREATE INDEX term_vocabulary_idx ON term (vocabulary_id);

CREATE TABLE term_label (
    term_id UUID NOT NULL REFERENCES term (id) ON DELETE CASCADE,
    lang    TEXT NOT NULL CHECK (lang <> ''),
    label   TEXT NOT NULL CHECK (label <> ''),
    PRIMARY KEY (term_id, lang)
);

-- Authority records: person / organisation / place. Store once, link many.
CREATE TABLE authority (
    id           UUID PRIMARY KEY,
    kind         TEXT NOT NULL CHECK (kind IN ('person', 'organisation', 'place')),
    external_uri TEXT
);
CREATE INDEX authority_kind_idx ON authority (kind);

CREATE TABLE authority_label (
    authority_id UUID NOT NULL REFERENCES authority (id) ON DELETE CASCADE,
    lang         TEXT NOT NULL CHECK (lang <> ''),
    label        TEXT NOT NULL CHECK (label <> ''),
    PRIMARY KEY (authority_id, lang)
);
