-- Repair DOC document enum/check alignment for TIME documents on databases
-- that still carry the pre-TIME family/type constraints.

DO $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM pg_type t
        JOIN pg_namespace n ON n.oid = t.typnamespace
        WHERE n.nspname = 'doc'
          AND t.typname = 'doc_document_type'
    ) THEN
        ALTER TYPE DOC.doc_document_type ADD VALUE IF NOT EXISTS 'TED_PACKAGE';
        ALTER TYPE DOC.doc_document_type ADD VALUE IF NOT EXISTS 'TED_NOTICE_LOT';
        ALTER TYPE DOC.doc_document_type ADD VALUE IF NOT EXISTS 'TIME_ENTRY';
    END IF;
END
$$;

DO $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM pg_type t
        JOIN pg_namespace n ON n.oid = t.typnamespace
        WHERE n.nspname = 'doc'
          AND t.typname = 'doc_document_family'
    ) THEN
        ALTER TYPE DOC.doc_document_family ADD VALUE IF NOT EXISTS 'TIME';
    END IF;
END
$$;

DO $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM information_schema.tables
        WHERE table_schema = 'doc'
          AND table_name = 'doc_document'
    ) THEN
        ALTER TABLE DOC.doc_document DROP CONSTRAINT IF EXISTS doc_document_document_type_check;
        ALTER TABLE DOC.doc_document
            ADD CONSTRAINT doc_document_document_type_check
            CHECK (
                document_type IN (
                    'TED_PACKAGE',
                    'TED_NOTICE',
                    'TED_NOTICE_LOT',
                    'TIME_ENTRY',
                    'EMAIL',
                    'MIME_MESSAGE',
                    'PDF',
                    'DOCX',
                    'HTML',
                    'XML_GENERIC',
                    'TEXT',
                    'MARKDOWN',
                    'ZIP_ARCHIVE',
                    'GENERIC_BINARY',
                    'UNKNOWN'
                )
            );

        ALTER TABLE DOC.doc_document DROP CONSTRAINT IF EXISTS doc_document_document_family_check;
        ALTER TABLE DOC.doc_document
            ADD CONSTRAINT doc_document_document_family_check
            CHECK (
                document_family IN (
                    'PROCUREMENT',
                    'TIME',
                    'MAIL',
                    'ATTACHMENT',
                    'KNOWLEDGE',
                    'GENERIC'
                )
            );
    END IF;
END
$$;
