-- Extend typed text-import attributes with INTEGER and DATETIME support.

ALTER TABLE DOC.doc_document_attribute
    ADD COLUMN IF NOT EXISTS integer_value BIGINT;

ALTER TABLE DOC.doc_document_attribute
    ADD COLUMN IF NOT EXISTS datetime_value TIMESTAMP WITH TIME ZONE;

ALTER TABLE DOC.doc_document_attribute
    DROP CONSTRAINT IF EXISTS chk_doc_document_attribute_single_typed_value;

ALTER TABLE DOC.doc_document_attribute
    ADD CONSTRAINT chk_doc_document_attribute_single_typed_value CHECK (
        (CASE WHEN string_value IS NOT NULL THEN 1 ELSE 0 END) +
        (CASE WHEN integer_value IS NOT NULL THEN 1 ELSE 0 END) +
        (CASE WHEN number_value IS NOT NULL THEN 1 ELSE 0 END) +
        (CASE WHEN date_value IS NOT NULL THEN 1 ELSE 0 END) +
        (CASE WHEN datetime_value IS NOT NULL THEN 1 ELSE 0 END) +
        (CASE WHEN boolean_value IS NOT NULL THEN 1 ELSE 0 END) = 1
    ) NOT VALID;

COMMENT ON COLUMN DOC.doc_document_attribute.integer_value IS 'Integer representation when the catalog entry type is INTEGER.';
COMMENT ON COLUMN DOC.doc_document_attribute.datetime_value IS 'Date-time representation when the catalog entry type is DATETIME.';
