-- Upgrade text-import attribute catalog to typed values with optional context.

ALTER TABLE DOC.doc_attribute_name
    ADD COLUMN IF NOT EXISTS attribute_context VARCHAR(100);

UPDATE DOC.doc_attribute_name
SET attribute_context = 'GLOBAL'
WHERE attribute_context IS NULL OR BTRIM(attribute_context) = '';

ALTER TABLE DOC.doc_attribute_name
    ALTER COLUMN attribute_context SET NOT NULL;

ALTER TABLE DOC.doc_attribute_name
    ADD COLUMN IF NOT EXISTS attribute_value_type VARCHAR(32);

UPDATE DOC.doc_attribute_name
SET attribute_value_type = 'STRING'
WHERE attribute_value_type IS NULL OR BTRIM(attribute_value_type) = '';

ALTER TABLE DOC.doc_attribute_name
    ALTER COLUMN attribute_value_type SET NOT NULL;

DROP INDEX IF EXISTS DOC.idx_doc_attr_name_normalized;
CREATE UNIQUE INDEX IF NOT EXISTS idx_doc_attr_name_normalized_ctx
    ON DOC.doc_attribute_name(normalized_name, attribute_context);
CREATE INDEX IF NOT EXISTS idx_doc_attr_name_context
    ON DOC.doc_attribute_name(attribute_context);

ALTER TABLE DOC.doc_document_attribute
    ADD COLUMN IF NOT EXISTS string_value TEXT;

DO $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'doc'
          AND table_name = 'doc_document_attribute'
          AND column_name = 'attribute_value'
    ) THEN
        UPDATE DOC.doc_document_attribute
        SET string_value = attribute_value
        WHERE string_value IS NULL AND attribute_value IS NOT NULL;
    END IF;
END
$$;

ALTER TABLE DOC.doc_document_attribute
    ADD COLUMN IF NOT EXISTS number_value NUMERIC;

ALTER TABLE DOC.doc_document_attribute
    ADD COLUMN IF NOT EXISTS date_value DATE;

ALTER TABLE DOC.doc_document_attribute
    ADD COLUMN IF NOT EXISTS boolean_value BOOLEAN;

ALTER TABLE DOC.doc_document_attribute
    DROP COLUMN IF EXISTS attribute_value;

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 number_value IS NOT NULL THEN 1 ELSE 0 END) +
        (CASE WHEN date_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_attribute_name.attribute_context IS 'Optional namespace/context for avoiding name conflicts. GLOBAL is the default context.';
COMMENT ON COLUMN DOC.doc_attribute_name.attribute_value_type IS 'Declared type of the attribute value for this catalog entry.';
COMMENT ON COLUMN DOC.doc_document_attribute.string_value IS 'String representation when the catalog entry type is STRING.';
COMMENT ON COLUMN DOC.doc_document_attribute.number_value IS 'Numeric representation when the catalog entry type is NUMBER.';
COMMENT ON COLUMN DOC.doc_document_attribute.date_value IS 'Date representation when the catalog entry type is DATE.';
COMMENT ON COLUMN DOC.doc_document_attribute.boolean_value IS 'Boolean representation when the catalog entry type is BOOLEAN.';
