-- Adds the canonical TED_NOTICE_LOT document type used for per-lot semantic representations.
-- The lot document stores derived semantic text in DOC.doc_text_representation; no DOC.doc_content row is required.

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_NOTICE_LOT';
    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
$$;

CREATE INDEX IF NOT EXISTS idx_doc_document_ted_lot_business_key
    ON DOC.doc_document(business_key)
    WHERE document_type = 'TED_NOTICE_LOT';

CREATE INDEX IF NOT EXISTS idx_doc_text_repr_ted_lot_builder
    ON DOC.doc_text_representation(builder_key)
    WHERE builder_key = 'ted-lot-structured-text';
