-- Attribute catalog for text-import name/value pairs.

CREATE TABLE IF NOT EXISTS DOC.doc_attribute_name (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    attribute_name VARCHAR(255) NOT NULL,
    normalized_name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS DOC.doc_document_attribute (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    document_id UUID NOT NULL REFERENCES DOC.doc_document(id) ON DELETE CASCADE,
    attribute_name_id UUID NOT NULL REFERENCES DOC.doc_attribute_name(id),
    attribute_value TEXT NOT NULL,
    attribute_value_hash VARCHAR(64) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_doc_attr_name_normalized ON DOC.doc_attribute_name(normalized_name);
CREATE INDEX IF NOT EXISTS idx_doc_attr_name_name ON DOC.doc_attribute_name(attribute_name);

CREATE INDEX IF NOT EXISTS idx_doc_doc_attr_document ON DOC.doc_document_attribute(document_id);
CREATE INDEX IF NOT EXISTS idx_doc_doc_attr_name ON DOC.doc_document_attribute(attribute_name_id);
CREATE INDEX IF NOT EXISTS idx_doc_doc_attr_value_hash ON DOC.doc_document_attribute(attribute_value_hash);
CREATE UNIQUE INDEX IF NOT EXISTS idx_doc_doc_attr_doc_name_hash
    ON DOC.doc_document_attribute(document_id, attribute_name_id, attribute_value_hash);

COMMENT ON TABLE DOC.doc_attribute_name IS 'Global catalog of reusable metadata attribute names';
COMMENT ON TABLE DOC.doc_document_attribute IS 'Intersection table linking documents to catalog-backed name/value attributes';
