SET search_path TO DOC, TED, public;

CREATE TABLE IF NOT EXISTS DOC.doc_mail_message (
    document_id uuid PRIMARY KEY REFERENCES DOC.doc_document(id) ON DELETE CASCADE,
    source_id uuid REFERENCES DOC.doc_source(id) ON DELETE SET NULL,
    provider_type varchar(64) NOT NULL,
    account_key varchar(255),
    folder_key varchar(255),
    provider_message_key varchar(500),
    provider_thread_key varchar(500),
    message_id varchar(1000),
    in_reply_to varchar(1000),
    references_header text,
    thread_key varchar(1000),
    subject varchar(1000),
    normalized_subject varchar(1000),
    from_display_name varchar(500),
    from_email varchar(500),
    from_raw varchar(1000),
    reply_to_raw text,
    sent_at timestamptz,
    received_at timestamptz,
    raw_message_hash varchar(64),
    raw_header_hash varchar(64),
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_doc_mail_message_source_id
    ON DOC.doc_mail_message (source_id);
CREATE INDEX IF NOT EXISTS idx_doc_mail_message_message_id
    ON DOC.doc_mail_message (message_id);
CREATE INDEX IF NOT EXISTS idx_doc_mail_message_thread_key
    ON DOC.doc_mail_message (thread_key);
CREATE INDEX IF NOT EXISTS idx_doc_mail_message_provider_identity
    ON DOC.doc_mail_message (provider_type, account_key, folder_key, provider_message_key);
CREATE UNIQUE INDEX IF NOT EXISTS uq_doc_mail_message_provider_identity
    ON DOC.doc_mail_message (provider_type, account_key, folder_key, provider_message_key)
    WHERE provider_message_key IS NOT NULL;

CREATE TABLE IF NOT EXISTS DOC.doc_mail_recipient (
    id uuid PRIMARY KEY,
    mail_document_id uuid NOT NULL REFERENCES DOC.doc_mail_message(document_id) ON DELETE CASCADE,
    recipient_type varchar(16) NOT NULL,
    display_name varchar(500),
    email_address varchar(500),
    raw_value text,
    sort_order integer NOT NULL DEFAULT 0,
    created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_doc_mail_recipient_mail
    ON DOC.doc_mail_recipient (mail_document_id);
CREATE INDEX IF NOT EXISTS idx_doc_mail_recipient_type
    ON DOC.doc_mail_recipient (recipient_type);
CREATE INDEX IF NOT EXISTS idx_doc_mail_recipient_email
    ON DOC.doc_mail_recipient (email_address);

CREATE TABLE IF NOT EXISTS DOC.doc_mail_attachment (
    id uuid PRIMARY KEY,
    mail_document_id uuid NOT NULL REFERENCES DOC.doc_mail_message(document_id) ON DELETE CASCADE,
    attachment_document_id uuid NOT NULL REFERENCES DOC.doc_document(id) ON DELETE CASCADE,
    disposition varchar(32),
    content_id varchar(500),
    filename varchar(1000),
    mime_type varchar(255),
    size_bytes bigint,
    attachment_index integer,
    part_path varchar(500),
    path_in_archive text,
    extraction_status varchar(32) NOT NULL DEFAULT 'IMPORTED',
    error_message text,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_doc_mail_attachment_mail
    ON DOC.doc_mail_attachment (mail_document_id);
CREATE INDEX IF NOT EXISTS idx_doc_mail_attachment_document
    ON DOC.doc_mail_attachment (attachment_document_id);
CREATE INDEX IF NOT EXISTS idx_doc_mail_attachment_part_path
    ON DOC.doc_mail_attachment (part_path);
CREATE INDEX IF NOT EXISTS idx_doc_mail_attachment_attachment_index
    ON DOC.doc_mail_attachment (attachment_index);
CREATE UNIQUE INDEX IF NOT EXISTS uq_doc_mail_attachment_mail_index
    ON DOC.doc_mail_attachment (mail_document_id, attachment_index)
    WHERE attachment_index IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS uq_doc_mail_attachment_mail_part
    ON DOC.doc_mail_attachment (mail_document_id, part_path)
    WHERE part_path IS NOT NULL;

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