-- Phase 4.1 enum/check-constraint expansion for newly introduced generic document/source types.
-- Supports both:
-- 1) PostgreSQL ENUM-backed columns created by Flyway
-- 2) legacy VARCHAR + CHECK constraint variants that may exist in local/dev databases

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';
    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_source_type'
    ) THEN
        ALTER TYPE DOC.doc_source_type ADD VALUE IF NOT EXISTS 'PACKAGE_CHILD';
        ALTER TYPE DOC.doc_source_type ADD VALUE IF NOT EXISTS 'MAIL_ATTACHMENT';
    END IF;
END
$$;

DO $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM pg_constraint c
        JOIN pg_class r ON r.oid = c.conrelid
        JOIN pg_namespace n ON n.oid = r.relnamespace
        WHERE n.nspname = 'doc'
          AND r.relname = 'doc_document'
          AND c.conname = 'doc_document_document_type_check'
    ) THEN
        ALTER TABLE DOC.doc_document DROP CONSTRAINT 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','EMAIL', 'MIME_MESSAGE', 'PDF', 'DOCX', 'HTML',
                    'XML_GENERIC', 'TEXT', 'MARKDOWN', 'ZIP_ARCHIVE', 'GENERIC_BINARY', 'TIME_ENTRY', 'UNKNOWN'
                )
            );
    END IF;
END
$$;

DO $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM pg_constraint c
        JOIN pg_class r ON r.oid = c.conrelid
        JOIN pg_namespace n ON n.oid = r.relnamespace
        WHERE n.nspname = 'doc'
          AND r.relname = 'doc_source'
          AND c.conname = 'doc_source_source_type_check'
    ) THEN
        ALTER TABLE DOC.doc_source DROP CONSTRAINT doc_source_source_type_check;
        ALTER TABLE DOC.doc_source
            ADD CONSTRAINT doc_source_source_type_check
            CHECK (
                source_type IN (
                    'TED_PACKAGE', 'PACKAGE_CHILD', 'MAIL', 'MAIL_ATTACHMENT', 'FILE_SYSTEM', 'REST_UPLOAD',
                    'MANUAL_UPLOAD', 'ZIP_CHILD', 'API', 'MIGRATION'
                )
            );
    END IF;
END
$$;
