agentmail_pkb.sql

/*
  Agentmail package body.
  Run after agentmail_pkh.sql.
*/
set define off;

CREATE OR REPLACE PACKAGE BODY agentmail AS

   FUNCTION get_default_token RETURN VARCHAR2 IS
      l_token agentmail_api_token.token_value%TYPE;
   BEGIN
      SELECT token_value
      INTO l_token
      FROM agentmail_api_token
      WHERE token_name = 'DEFAULT';
      RETURN l_token;
   END get_default_token;

   PROCEDURE list_messages (
      p_inbox_id     IN VARCHAR2,
      p_limit        IN NUMBER   DEFAULT 50,          -- reasonable default
      p_page_token   IN VARCHAR2 DEFAULT NULL,        -- for next page
      p_labels       IN VARCHAR2 DEFAULT NULL,        -- optional: comma-separated labels filter (can extend to array later)
      p_after        IN TIMESTAMP WITH TIME ZONE DEFAULT NULL -- optional: filter messages after this timestamp
   ) IS
      l_url          VARCHAR2(2000);
      l_response     CLOB;
      l_status       NUMBER;
      l_qs           VARCHAR2(500) := NULL;           -- query string builder
   BEGIN
      -- Clear previous values
      last_response_json   := NULL;
      last_status_code     := NULL;
      last_response_message := NULL;

      -- Build base URL
      l_url := 'https://api.agentmail.to/v0/inboxes/' ||
               p_inbox_id ||
               '/messages';

      -- Build query string (simple concatenation; safe since values are controlled)
      IF p_limit IS NOT NULL THEN
         l_qs := l_qs || CASE WHEN l_qs IS NULL THEN '?' ELSE '&' END || 'limit=' || TO_CHAR(p_limit);
      END IF;

      IF p_page_token IS NOT NULL THEN
         l_qs := l_qs || CASE WHEN l_qs IS NULL THEN '?' ELSE '&' END || 'page_token=' || apex_util.url_encode(p_page_token);
         -- Note: docs use 'page_token' for input, but response returns 'next_page_token'
      END IF;

      IF p_labels IS NOT NULL THEN
         l_qs := l_qs || CASE WHEN l_qs IS NULL THEN '?' ELSE '&' END || 'labels=' || apex_util.url_encode(p_labels);
         -- If multiple: labels=label1&labels=label2 (repeat param)
      END IF;

      IF p_after IS NOT NULL THEN
         l_qs := l_qs || CASE WHEN l_qs IS NULL THEN '?' ELSE '&' END ||
                 'after=' || apex_util.url_encode(
                    to_char(p_after at time zone 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"')
                 );
      END IF;

      l_url := l_url || l_qs;

      -- Set headers (only Authorization needed for GET)
      apex_web_service.g_request_headers.delete();
      apex_web_service.g_request_headers(1).name  := 'Authorization';
      apex_web_service.g_request_headers(1).value := 'Bearer ' || get_default_token();

      -- Make the GET request
      l_response := apex_web_service.make_rest_request(
         p_url         => l_url,
         p_http_method => 'GET'
         -- no p_body needed
      );

      last_response_json   := l_response;
      last_status_code     := apex_web_service.g_status_code;

      IF last_status_code BETWEEN 200 AND 299 THEN
         -- Success → parse into tables
         parse_list_messages_response(p_response => l_response);
         last_response_message := 'Success: ' || JSON_VALUE(l_response, '$.count') || ' messages processed';
      ELSE
         -- Error
         last_response_message := 'AgentMail list failed. Status: ' || last_status_code ||
                                  ' Response: ' || SUBSTR(l_response, 1, 1000);
         RAISE_APPLICATION_ERROR(-20001, last_response_message);
      END IF;

   EXCEPTION
      WHEN OTHERS THEN
         apex_debug.error('list_messages failed: %s', SQLERRM);
         last_response_message := 'Exception: ' || SQLERRM;
         RAISE;
   END list_messages;

   PROCEDURE list_inboxes (
      p_limit        IN NUMBER   DEFAULT NULL
   ) IS
      l_url          VARCHAR2(2000) := 'https://api.agentmail.to/v0/inboxes';
      l_response     CLOB;
      l_qs           VARCHAR2(500) := NULL;
   BEGIN
      last_response_json    := NULL;
      last_status_code      := NULL;
      last_response_message := NULL;

      IF p_limit IS NOT NULL THEN
         l_qs := '?limit=' || TO_CHAR(p_limit);
      END IF;

      l_url := l_url || l_qs;

      apex_web_service.g_request_headers.delete();
      apex_web_service.g_request_headers(1).name  := 'Authorization';
      apex_web_service.g_request_headers(1).value := 'Bearer ' || get_default_token();

      l_response := apex_web_service.make_rest_request(
         p_url         => l_url,
         p_http_method => 'GET'
      );

      last_response_json := l_response;
      last_status_code   := apex_web_service.g_status_code;

      IF last_status_code BETWEEN 200 AND 299 THEN
         parse_list_inboxes_response(p_response => l_response);
         last_response_message := 'Success: ' || JSON_VALUE(l_response, '$.count') || ' inboxes processed';
      ELSE
         last_response_message := 'AgentMail list_inboxes failed. Status: ' || last_status_code ||
                                  ' Response: ' || SUBSTR(l_response, 1, 1000);
         RAISE_APPLICATION_ERROR(-20001, last_response_message);
      END IF;

   EXCEPTION
      WHEN OTHERS THEN
         apex_debug.error('list_inboxes failed: %s', SQLERRM);
         last_response_message := 'Exception: ' || SQLERRM;
         RAISE;
   END list_inboxes;

   PROCEDURE create_inbox (
      p_username     IN VARCHAR2 DEFAULT NULL,
      p_domain       IN VARCHAR2 DEFAULT NULL,
      p_display_name IN VARCHAR2 DEFAULT NULL,
      p_client_id    IN VARCHAR2 DEFAULT NULL
   ) IS
      l_url       VARCHAR2(2000) := 'https://api.agentmail.to/v0/inboxes';
      l_response  CLOB;
      l_json_obj  JSON_OBJECT_T;
      l_body      CLOB;
   BEGIN
      last_response_json   := NULL;
      last_status_code     := NULL;
      last_response_message := NULL;

      l_json_obj := JSON_OBJECT_T();
      IF p_username IS NOT NULL THEN
         l_json_obj.put('username', p_username);
      END IF;
      IF p_domain IS NOT NULL THEN
         l_json_obj.put('domain', p_domain);
      END IF;
      IF p_display_name IS NOT NULL THEN
         l_json_obj.put('display_name', p_display_name);
      END IF;
      IF p_client_id IS NOT NULL THEN
         l_json_obj.put('client_id', p_client_id);
      END IF;
      l_body := l_json_obj.to_string();

      apex_web_service.g_request_headers.delete();
      apex_web_service.g_request_headers(1).name  := 'Authorization';
      apex_web_service.g_request_headers(1).value := 'Bearer ' || get_default_token();
      apex_web_service.g_request_headers(2).name  := 'Content-Type';
      apex_web_service.g_request_headers(2).value := 'application/json';

      l_response := apex_web_service.make_rest_request(
         p_url         => l_url,
         p_http_method => 'POST',
         p_body        => l_body
      );

      last_response_json   := l_response;
      last_status_code     := apex_web_service.g_status_code;

      IF last_status_code BETWEEN 200 AND 299 THEN
         last_inbox_id := JSON_VALUE(l_response, '$.inbox_id');
         -- Merge single inbox into agentmail_inboxes (same shape as list response item)
         MERGE INTO agentmail_inboxes t
         USING (
            SELECT jt.pod_id, jt.inbox_id, jt.display_name, jt.client_id, jt.updated_at, jt.created_at
            FROM JSON_TABLE(l_response, '$'
               COLUMNS (
                  pod_id       VARCHAR2(100) PATH '$.pod_id',
                  inbox_id     VARCHAR2(100) PATH '$.inbox_id',
                  display_name VARCHAR2(500) PATH '$.display_name',
                  client_id    VARCHAR2(200) PATH '$.client_id',
                  updated_at   TIMESTAMP WITH TIME ZONE PATH '$.updated_at',
                  created_at   TIMESTAMP WITH TIME ZONE PATH '$.created_at'
               )
            ) jt
         ) s ON (t.inbox_id = s.inbox_id)
         WHEN MATCHED THEN
            UPDATE SET
               t.pod_id       = s.pod_id,
               t.display_name = s.display_name,
               t.client_id    = s.client_id,
               t.updated_at   = s.updated_at,
               t.created_at   = s.created_at,
               t.raw_json     = l_response
         WHEN NOT MATCHED THEN
            INSERT (pod_id, inbox_id, display_name, client_id, updated_at, created_at, raw_json)
            VALUES (s.pod_id, s.inbox_id, s.display_name, s.client_id, s.updated_at, s.created_at, l_response);
         COMMIT;
         last_response_message := 'Success: inbox_id=' || last_inbox_id;
      ELSE
         last_response_message := 'AgentMail create_inbox failed. Status: ' || last_status_code ||
                                  ' Response: ' || SUBSTR(l_response, 1, 1000);
         RAISE_APPLICATION_ERROR(-20001, last_response_message);
      END IF;
   EXCEPTION
      WHEN OTHERS THEN
         ROLLBACK;
         apex_debug.error('create_inbox failed: %s', SQLERRM);
         last_response_message := 'Exception: ' || SQLERRM;
         RAISE;
   END create_inbox;

   PROCEDURE parse_list_inboxes_response (p_response IN CLOB) IS
   BEGIN
      MERGE INTO agentmail_inboxes t
      USING (
         SELECT
            jt.pod_id,
            jt.inbox_id,
            jt.display_name,
            jt.client_id,
            jt.updated_at,
            jt.created_at
         FROM JSON_TABLE(p_response, '$.inboxes[*]'
            COLUMNS (
               pod_id       VARCHAR2(100) PATH '$.pod_id',
               inbox_id     VARCHAR2(100) PATH '$.inbox_id',
               display_name VARCHAR2(500) PATH '$.display_name',
               client_id    VARCHAR2(200) PATH '$.client_id',
               updated_at   TIMESTAMP WITH TIME ZONE PATH '$.updated_at',
               created_at   TIMESTAMP WITH TIME ZONE PATH '$.created_at'
            )
         ) jt
      ) s ON (t.inbox_id = s.inbox_id)
      WHEN MATCHED THEN
         UPDATE SET
            t.pod_id       = s.pod_id,
            t.display_name = s.display_name,
            t.client_id    = s.client_id,
            t.updated_at   = s.updated_at,
            t.created_at   = s.created_at,
            t.raw_json     = p_response
      WHEN NOT MATCHED THEN
         INSERT (pod_id, inbox_id, display_name, client_id, updated_at, created_at, raw_json)
         VALUES (s.pod_id, s.inbox_id, s.display_name, s.client_id, s.updated_at, s.created_at, p_response);

      COMMIT;
   EXCEPTION
      WHEN OTHERS THEN
         ROLLBACK;
         RAISE;
   END parse_list_inboxes_response;

   PROCEDURE parse_list_messages_response (p_response IN CLOB) IS
BEGIN
   -- Main messages – skip if message_id already exists (idempotent)
   INSERT INTO agentmail_messages (
      inbox_id, thread_id, message_id, labels, timestamp_, from_addr,
      subject, preview, size_bytes, created_at, updated_at,
      in_reply_to, raw_json
   )
   SELECT
      jt.inbox_id,
      jt.thread_id,
      jt.message_id,
      jt.labels_str,
      jt.timestamp_,
      jt.from_addr,
      jt.subject,
      jt.preview,
      jt.size_bytes,
      jt.created_at,
      jt.updated_at,
      jt.in_reply_to,
      p_response
   FROM JSON_TABLE(p_response, '$.messages[*]'
      COLUMNS (
         inbox_id      VARCHAR2(100) PATH '$.inbox_id',
         thread_id     VARCHAR2(100) PATH '$.thread_id',
         message_id    VARCHAR2(100) PATH '$.message_id',
         labels_str    VARCHAR2(4000) FORMAT JSON PATH '$.labels',
         timestamp_    TIMESTAMP WITH TIME ZONE PATH '$.timestamp',
         from_addr     VARCHAR2(500) PATH '$.from',
         subject       VARCHAR2(1000) PATH '$.subject',
         preview       VARCHAR2(4000) PATH '$.preview',
         size_bytes    NUMBER PATH '$.size',
         created_at    TIMESTAMP WITH TIME ZONE PATH '$.created_at',
         updated_at    TIMESTAMP WITH TIME ZONE PATH '$.updated_at',
         in_reply_to   VARCHAR2(500) PATH '$.in_reply_to'
      )
   ) jt
   WHERE NOT EXISTS (
      SELECT 1 FROM agentmail_messages m WHERE m.message_id = jt.message_id
   );

   -- TO recipients – skip if already exists
   INSERT INTO agentmail_recipients (message_id, recipient_type, email_addr)
   SELECT jt.message_id, 'TO', jt.email
   FROM JSON_TABLE(p_response, '$.messages[*]'
      COLUMNS (
         message_id VARCHAR2(100) PATH '$.message_id',
         NESTED PATH '$.to[*]' COLUMNS (email VARCHAR2(500) PATH '$')
      )
   ) jt
   WHERE jt.email IS NOT NULL
     AND NOT EXISTS (
        SELECT 1 FROM agentmail_recipients r
        WHERE r.message_id = jt.message_id AND r.recipient_type = 'TO' AND r.email_addr = jt.email
     );

   -- CC recipients – skip if already exists
   INSERT INTO agentmail_recipients (message_id, recipient_type, email_addr)
   SELECT jt.message_id, 'CC', jt.email
   FROM JSON_TABLE(p_response, '$.messages[*]'
      COLUMNS (
         message_id VARCHAR2(100) PATH '$.message_id',
         NESTED PATH '$.cc[*]' COLUMNS (email VARCHAR2(500) PATH '$')
      )
   ) jt
   WHERE jt.email IS NOT NULL
     AND NOT EXISTS (
        SELECT 1 FROM agentmail_recipients r
        WHERE r.message_id = jt.message_id AND r.recipient_type = 'CC' AND r.email_addr = jt.email
     );

   -- BCC recipients – skip if already exists
   INSERT INTO agentmail_recipients (message_id, recipient_type, email_addr)
   SELECT jt.message_id, 'BCC', jt.email
   FROM JSON_TABLE(p_response, '$.messages[*]'
      COLUMNS (
         message_id VARCHAR2(100) PATH '$.message_id',
         NESTED PATH '$.bcc[*]' COLUMNS (email VARCHAR2(500) PATH '$')
      )
   ) jt
   WHERE jt.email IS NOT NULL
     AND NOT EXISTS (
        SELECT 1 FROM agentmail_recipients r
        WHERE r.message_id = jt.message_id AND r.recipient_type = 'BCC' AND r.email_addr = jt.email
     );

   -- Attachments – skip if already exists
   INSERT INTO agentmail_attachments (
      message_id, attachment_id, filename, size_bytes, content_type,
      content_disposition, content_id
   )
   SELECT
      jt.message_id,
      jt.attachment_id,
      jt.filename,
      jt.size_bytes,
      jt.content_type,
      jt.content_disposition,
      jt.content_id
   FROM JSON_TABLE(p_response, '$.messages[*]'
      COLUMNS (
         message_id VARCHAR2(100) PATH '$.message_id',
         NESTED PATH '$.attachments[*]'
            COLUMNS (
               attachment_id       VARCHAR2(100) PATH '$.attachment_id',
               filename            VARCHAR2(500) PATH '$.filename',
               size_bytes          NUMBER        PATH '$.size',
               content_type        VARCHAR2(200) PATH '$.content_type',
               content_disposition VARCHAR2(100) PATH '$.content_disposition',
               content_id          VARCHAR2(200) PATH '$.content_id'
            )
      )
   ) jt
   WHERE jt.attachment_id IS NOT NULL
     AND NOT EXISTS (
        SELECT 1 FROM agentmail_attachments a
        WHERE a.message_id = jt.message_id AND a.attachment_id = jt.attachment_id
     );

   COMMIT;

EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK;
      RAISE;
END parse_list_messages_response;

PROCEDURE get_message (
   p_inbox_id    IN VARCHAR2,
   p_message_id  IN VARCHAR2
) IS
   l_url      VARCHAR2(2000);
   l_response CLOB;
   l_message_id VARCHAR2(100);
BEGIN
   -- Clear previous values
   last_response_json    := NULL;
   last_status_code      := NULL;
   last_response_message := NULL;

   IF p_message_id IS NULL THEN
      RAISE_APPLICATION_ERROR(-20002, 'p_message_id is required');
   END IF;

   l_url := 'https://api.agentmail.to/v0/inboxes/' ||
            p_inbox_id ||
            '/messages/' || apex_util.url_encode(p_message_id);

   apex_web_service.g_request_headers.delete();
   apex_web_service.g_request_headers(1).name  := 'Authorization';
   apex_web_service.g_request_headers(1).value := 'Bearer ' || get_default_token();

   l_response := apex_web_service.make_rest_request(
      p_url         => l_url,
      p_http_method => 'GET'
   );

   last_response_json := l_response;
   last_status_code   := apex_web_service.g_status_code;

   IF last_status_code BETWEEN 200 AND 299 THEN
      l_message_id := JSON_VALUE(l_response, '$.message_id');
      IF l_message_id IS NULL THEN
         RAISE_APPLICATION_ERROR(-20004, 'AgentMail get_message returned no message_id');
      END IF;

      -- Upsert full message detail (single object payload)
      MERGE INTO agentmail_message_detail d
      USING (
         SELECT *
         FROM JSON_TABLE(l_response, '$'
            COLUMNS (
               inbox_id         VARCHAR2(100) PATH '$.inbox_id',
               thread_id        VARCHAR2(100) PATH '$.thread_id',
               message_id       VARCHAR2(100) PATH '$.message_id',
               labels_json      CLOB FORMAT JSON PATH '$.labels',
               timestamp_       TIMESTAMP WITH TIME ZONE PATH '$.timestamp',
               from_addr        VARCHAR2(500) PATH '$.from',
               reply_to_json    CLOB FORMAT JSON PATH '$.reply_to',
               to_json          CLOB FORMAT JSON PATH '$.to',
               cc_json          CLOB FORMAT JSON PATH '$.cc',
               bcc_json         CLOB FORMAT JSON PATH '$.bcc',
               subject          VARCHAR2(1000) PATH '$.subject',
               preview          VARCHAR2(4000) PATH '$.preview',
               text_body        CLOB PATH '$.text',
               html_body        CLOB PATH '$.html',
               extracted_text   CLOB PATH '$.extracted_text',
               extracted_html   CLOB PATH '$.extracted_html',
               attachments_json CLOB FORMAT JSON PATH '$.attachments',
               in_reply_to      VARCHAR2(500) PATH '$.in_reply_to',
               references_json  CLOB FORMAT JSON PATH '$.references',
               headers_json     CLOB FORMAT JSON PATH '$.headers',
               size_bytes       NUMBER PATH '$.size',
               created_at       TIMESTAMP WITH TIME ZONE PATH '$.created_at',
               updated_at       TIMESTAMP WITH TIME ZONE PATH '$.updated_at'
            )
         )
      ) s
      ON (d.message_id = s.message_id)
      WHEN MATCHED THEN UPDATE SET
         d.inbox_id         = s.inbox_id,
         d.thread_id        = s.thread_id,
         d.labels_json      = s.labels_json,
         d.timestamp_       = s.timestamp_,
         d.from_addr        = s.from_addr,
         d.reply_to_json    = s.reply_to_json,
         d.to_json          = s.to_json,
         d.cc_json          = s.cc_json,
         d.bcc_json         = s.bcc_json,
         d.subject          = s.subject,
         d.preview          = s.preview,
         d.text_body        = s.text_body,
         d.html_body        = s.html_body,
         d.extracted_text   = s.extracted_text,
         d.extracted_html   = s.extracted_html,
         d.attachments_json = s.attachments_json,
         d.in_reply_to      = s.in_reply_to,
         d.references_json  = s.references_json,
         d.headers_json     = s.headers_json,
         d.size_bytes       = s.size_bytes,
         d.created_at       = s.created_at,
         d.updated_at       = s.updated_at,
         d.fetched_at       = SYSTIMESTAMP,
         d.raw_json         = l_response
      WHEN NOT MATCHED THEN INSERT (
         inbox_id, thread_id, message_id, labels_json, timestamp_, from_addr,
         reply_to_json, to_json, cc_json, bcc_json,
         subject, preview, text_body, html_body, extracted_text, extracted_html,
         attachments_json, in_reply_to, references_json, headers_json,
         size_bytes, created_at, updated_at, fetched_at, raw_json
      ) VALUES (
         s.inbox_id, s.thread_id, s.message_id, s.labels_json, s.timestamp_, s.from_addr,
         s.reply_to_json, s.to_json, s.cc_json, s.bcc_json,
         s.subject, s.preview, s.text_body, s.html_body, s.extracted_text, s.extracted_html,
         s.attachments_json, s.in_reply_to, s.references_json, s.headers_json,
         s.size_bytes, s.created_at, s.updated_at, SYSTIMESTAMP, l_response
      );

      -- Also keep the metadata table in sync (idempotent upsert)
      MERGE INTO agentmail_messages m
      USING (
         SELECT *
         FROM JSON_TABLE(l_response, '$'
            COLUMNS (
               inbox_id    VARCHAR2(100) PATH '$.inbox_id',
               thread_id   VARCHAR2(100) PATH '$.thread_id',
               message_id  VARCHAR2(100) PATH '$.message_id',
               labels_str  VARCHAR2(4000) FORMAT JSON PATH '$.labels',
               timestamp_  TIMESTAMP WITH TIME ZONE PATH '$.timestamp',
               from_addr   VARCHAR2(500) PATH '$.from',
               subject     VARCHAR2(1000) PATH '$.subject',
               preview     VARCHAR2(4000) PATH '$.preview',
               size_bytes  NUMBER PATH '$.size',
               created_at  TIMESTAMP WITH TIME ZONE PATH '$.created_at',
               updated_at  TIMESTAMP WITH TIME ZONE PATH '$.updated_at',
               in_reply_to VARCHAR2(500) PATH '$.in_reply_to'
            )
         )
      ) s
      ON (m.message_id = s.message_id)
      WHEN MATCHED THEN UPDATE SET
         m.inbox_id    = s.inbox_id,
         m.thread_id   = s.thread_id,
         m.labels      = s.labels_str,
         m.timestamp_  = s.timestamp_,
         m.from_addr   = s.from_addr,
         m.subject     = s.subject,
         m.preview     = s.preview,
         m.size_bytes  = s.size_bytes,
         m.created_at  = s.created_at,
         m.updated_at  = s.updated_at,
         m.in_reply_to = s.in_reply_to,
         m.raw_json    = l_response
      WHEN NOT MATCHED THEN INSERT (
         inbox_id, thread_id, message_id, labels, timestamp_, from_addr,
         subject, preview, size_bytes, created_at, updated_at, in_reply_to, raw_json
      ) VALUES (
         s.inbox_id, s.thread_id, s.message_id, s.labels_str, s.timestamp_, s.from_addr,
         s.subject, s.preview, s.size_bytes, s.created_at, s.updated_at, s.in_reply_to, l_response
      );

      -- Refresh normalized child tables for this message (simple + deterministic)
      DELETE FROM agentmail_recipients  WHERE message_id = l_message_id;
      DELETE FROM agentmail_attachments WHERE message_id = l_message_id;

      -- Recipients
      INSERT INTO agentmail_recipients (message_id, recipient_type, email_addr)
      SELECT l_message_id, 'TO', jt.email
      FROM JSON_TABLE(l_response, '$.to[*]' COLUMNS (email VARCHAR2(500) PATH '$')) jt
      WHERE jt.email IS NOT NULL;

      INSERT INTO agentmail_recipients (message_id, recipient_type, email_addr)
      SELECT l_message_id, 'CC', jt.email
      FROM JSON_TABLE(l_response, '$.cc[*]' COLUMNS (email VARCHAR2(500) PATH '$')) jt
      WHERE jt.email IS NOT NULL;

      INSERT INTO agentmail_recipients (message_id, recipient_type, email_addr)
      SELECT l_message_id, 'BCC', jt.email
      FROM JSON_TABLE(l_response, '$.bcc[*]' COLUMNS (email VARCHAR2(500) PATH '$')) jt
      WHERE jt.email IS NOT NULL;

      -- Attachments
      INSERT INTO agentmail_attachments (
         message_id, attachment_id, filename, size_bytes, content_type,
         content_disposition, content_id
      )
      SELECT
         l_message_id,
         jt.attachment_id,
         jt.filename,
         jt.size_bytes,
         jt.content_type,
         jt.content_disposition,
         jt.content_id
      FROM JSON_TABLE(l_response, '$.attachments[*]'
         COLUMNS (
            attachment_id       VARCHAR2(100) PATH '$.attachment_id',
            filename            VARCHAR2(500) PATH '$.filename',
            size_bytes          NUMBER        PATH '$.size',
            content_type        VARCHAR2(200) PATH '$.content_type',
            content_disposition VARCHAR2(100) PATH '$.content_disposition',
            content_id          VARCHAR2(200) PATH '$.content_id'
         )
      ) jt
      WHERE jt.attachment_id IS NOT NULL;

      COMMIT;
      last_response_message := 'Success: message ' || l_message_id || ' stored';
   ELSE
      last_response_message := 'AgentMail get_message failed. Status: ' || last_status_code ||
                               ' Response: ' || SUBSTR(l_response, 1, 1000);
      RAISE_APPLICATION_ERROR(-20003, last_response_message);
   END IF;

EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK;
      apex_debug.error('get_message failed: %s', SQLERRM);
      last_response_message := 'Exception: ' || SQLERRM;
      RAISE;
END get_message;

PROCEDURE get_attachment (
   p_inbox_id      IN VARCHAR2,
   p_message_id    IN VARCHAR2,
   p_attachment_id IN VARCHAR2
) IS
   l_url              VARCHAR2(2000);
   l_response         CLOB;

   l_attachment_id    VARCHAR2(100);
   l_filename         VARCHAR2(500);
   l_size_bytes       NUMBER;
   l_content_type     VARCHAR2(200);
   l_content_disp     VARCHAR2(100);
   l_content_id       VARCHAR2(200);
   l_download_url     VARCHAR2(2000);
   l_expires_at       TIMESTAMP WITH TIME ZONE;

   l_content          BLOB;
   l_req              UTL_HTTP.req;
   l_resp             UTL_HTTP.resp;
   l_resp_open        BOOLEAN := FALSE;
   l_raw              RAW(32767);
BEGIN
   -- Clear previous values
   last_response_json    := NULL;
   last_status_code      := NULL;
   last_response_message := NULL;

   IF p_message_id IS NULL THEN
      RAISE_APPLICATION_ERROR(-20012, 'p_message_id is required');
   END IF;
   IF p_attachment_id IS NULL THEN
      RAISE_APPLICATION_ERROR(-20013, 'p_attachment_id is required');
   END IF;

   -- 1) Fetch attachment metadata (includes download_url)
   l_url := 'https://api.agentmail.to/v0/inboxes/' ||
            p_inbox_id ||
            '/messages/' || apex_util.url_encode(p_message_id) ||
            '/attachments/' || apex_util.url_encode(p_attachment_id);

   apex_web_service.g_request_headers.delete();
   apex_web_service.g_request_headers(1).name  := 'Authorization';
   apex_web_service.g_request_headers(1).value := 'Bearer ' || get_default_token();

   l_response := apex_web_service.make_rest_request(
      p_url         => l_url,
      p_http_method => 'GET'
   );

   last_response_json := l_response;
   last_status_code   := apex_web_service.g_status_code;

   IF last_status_code NOT BETWEEN 200 AND 299 THEN
      last_response_message := 'AgentMail get_attachment failed. Status: ' || last_status_code ||
                               ' Response: ' || SUBSTR(l_response, 1, 1000);
      RAISE_APPLICATION_ERROR(-20014, last_response_message);
   END IF;

   SELECT
      jt.attachment_id,
      jt.filename,
      jt.size_bytes,
      jt.content_type,
      jt.content_disposition,
      jt.content_id,
      jt.download_url,
      jt.expires_at
   INTO
      l_attachment_id,
      l_filename,
      l_size_bytes,
      l_content_type,
      l_content_disp,
      l_content_id,
      l_download_url,
      l_expires_at
   FROM JSON_TABLE(l_response, '$'
      COLUMNS (
         attachment_id       VARCHAR2(100) PATH '$.attachment_id',
         filename            VARCHAR2(500) PATH '$.filename',
         size_bytes          NUMBER        PATH '$.size',
         content_type        VARCHAR2(200) PATH '$.content_type',
         content_disposition VARCHAR2(100) PATH '$.content_disposition',
         content_id          VARCHAR2(200) PATH '$.content_id',
         download_url        VARCHAR2(2000) PATH '$.download_url',
         expires_at          TIMESTAMP WITH TIME ZONE PATH '$.expires_at'
      )
   ) jt;

   IF l_download_url IS NULL THEN
      RAISE_APPLICATION_ERROR(-20015, 'AgentMail get_attachment returned no download_url');
   END IF;

   BEGIN
      l_req := UTL_HTTP.begin_request(l_download_url, 'GET', 'HTTP/1.1');
      -- UTL_HTTP.set_header(l_req, 'User-Agent', 'oracle-agentmail');
      -- UTL_HTTP.set_header(l_req, 'Accept', '*/*');

      l_resp := UTL_HTTP.get_response(l_req);
      l_resp_open := TRUE;

      IF l_resp.status_code NOT BETWEEN 200 AND 299 THEN
         RAISE_APPLICATION_ERROR(-20016, 'Attachment download failed. HTTP status: ' || l_resp.status_code);
      END IF;

      DBMS_LOB.createtemporary(l_content, TRUE);

      LOOP
         BEGIN
            UTL_HTTP.read_raw(l_resp, l_raw, 32767);
            DBMS_LOB.writeappend(l_content, UTL_RAW.length(l_raw), l_raw);
         EXCEPTION
            WHEN UTL_HTTP.end_of_body THEN
               EXIT;
         END;
      END LOOP;

      UTL_HTTP.end_response(l_resp);
      l_resp_open := FALSE;
   EXCEPTION
      WHEN OTHERS THEN
         IF l_resp_open THEN
            BEGIN
               UTL_HTTP.end_response(l_resp);
            EXCEPTION
               WHEN OTHERS THEN
                  NULL;
            END;
         END IF;
         RAISE;
   END;

   -- 3) Upsert into table (message_id + attachment_id)
   MERGE INTO agentmail_attachment_content c
   USING (
      SELECT
         p_message_id          AS message_id,
         l_attachment_id       AS attachment_id,
         l_filename            AS filename,
         l_size_bytes          AS size_bytes,
         l_content_type        AS content_type,
         l_content_disp        AS content_disposition,
         l_content_id          AS content_id,
         l_download_url        AS download_url,
         l_expires_at          AS expires_at
      FROM dual
   ) s
   ON (c.message_id = s.message_id AND c.attachment_id = s.attachment_id)
   WHEN MATCHED THEN UPDATE SET
      c.filename            = s.filename,
      c.size_bytes          = s.size_bytes,
      c.content_type        = s.content_type,
      c.content_disposition = s.content_disposition,
      c.content_id          = s.content_id,
      c.download_url        = s.download_url,
      c.expires_at          = s.expires_at,
      c.content             = l_content,
      c.fetched_at          = SYSTIMESTAMP,
      c.raw_json            = l_response
   WHEN NOT MATCHED THEN INSERT (
      message_id, attachment_id, filename, size_bytes, content_type,
      content_disposition, content_id, download_url, expires_at,
      content, fetched_at, raw_json
   ) VALUES (
      s.message_id, s.attachment_id, s.filename, s.size_bytes, s.content_type,
      s.content_disposition, s.content_id, s.download_url, s.expires_at,
      l_content, SYSTIMESTAMP, l_response
   );

   COMMIT;

   last_response_message := 'Success: attachment ' || l_attachment_id || ' stored (' ||
                            DBMS_LOB.getlength(l_content) || ' bytes)';

   IF DBMS_LOB.istemporary(l_content) = 1 THEN
      DBMS_LOB.freetemporary(l_content);
   END IF;

EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK;
      IF DBMS_LOB.istemporary(l_content) = 1 THEN
         BEGIN
            DBMS_LOB.freetemporary(l_content);
         EXCEPTION
            WHEN OTHERS THEN
               NULL;
         END;
      END IF;
      apex_debug.error('get_attachment failed: %s', SQLERRM);
      last_response_message := 'Exception: ' || SQLERRM;
      IF SQLCODE IN (-29273, -24247, -28750) THEN
         last_response_message := last_response_message ||
            ' (Network ACL may block UTL_HTTP to download_url host. Grant connect to that host.)';
      END IF;
      RAISE;
END get_attachment;

PROCEDURE send_message (
   p_inbox_id  IN VARCHAR2,
   p_email     IN VARCHAR2,
   p_subject   IN VARCHAR2,
   p_body      IN VARCHAR2,
   p_to_email  IN VARCHAR2,
   p_labels    IN label_array DEFAULT NULL
) IS
   l_url       VARCHAR2(1000);
   l_response  CLOB;
   l_status    NUMBER;
   l_labels_json JSON_ARRAY_T;
   l_json_obj   JSON_OBJECT_T;
BEGIN
   apex_web_service.g_request_headers.delete();

   apex_web_service.g_request_headers(1).name  := 'Authorization';
   apex_web_service.g_request_headers(1).value := 'Bearer ' || get_default_token();

   apex_web_service.g_request_headers(2).name  := 'Content-Type';
   apex_web_service.g_request_headers(2).value := 'application/json';

   l_url := 'https://api.agentmail.to/v0/inboxes/' ||
            p_inbox_id ||
            '/messages/send';

   -- Build JSON object with array
   l_json_obj := JSON_OBJECT_T();
   l_json_obj.put('email', p_email);
   l_json_obj.put('subject', p_subject);
   l_json_obj.put('body', p_body);
   l_json_obj.put('to', p_to_email);
   
   -- Convert label array to JSON array
   l_labels_json := JSON_ARRAY_T();
   IF p_labels IS NOT NULL AND p_labels.COUNT > 0 THEN
      FOR i IN 1..p_labels.COUNT LOOP
         l_labels_json.append(p_labels(i));
      END LOOP;
   END IF;
   l_json_obj.put('labels', l_labels_json);

   last_response_json := apex_web_service.make_rest_request(
      p_url         => l_url,
      p_http_method => 'POST',
      p_body        => l_json_obj.to_string()
   );

   last_status_code := apex_web_service.g_status_code;

   IF last_status_code BETWEEN 200 AND 299 THEN
      -- Success – optionally log or return something
      last_response_message := last_response_json;
   ELSE
      RAISE_APPLICATION_ERROR(-20001,
         'AgentMail send_message failed. Status: ' || last_status_code ||
         ' Response: ' || SUBSTR(last_response_json, 1, 1000));
   END IF;

EXCEPTION
   WHEN OTHERS THEN
      apex_debug.error('send_message failed: %s', SQLERRM);
      RAISE;
END send_message;

PROCEDURE reply_to_message (
   p_inbox_id    IN VARCHAR2,
   p_message_id  IN VARCHAR2,
   p_text        IN CLOB,
   p_html        IN CLOB DEFAULT NULL,
   p_reply_all   IN VARCHAR2 DEFAULT 'Y', -- Y/N/NULL, controls reply_all flag
   p_labels      IN label_array DEFAULT NULL,
   p_to          IN label_array DEFAULT NULL,
   p_cc          IN label_array DEFAULT NULL,
   p_bcc         IN label_array DEFAULT NULL
) IS
   l_url            VARCHAR2(2000);
   l_reply_all_bool BOOLEAN;
   l_json_obj       JSON_OBJECT_T;
   l_labels_json    JSON_ARRAY_T;
   l_to_json        JSON_ARRAY_T;
   l_cc_json        JSON_ARRAY_T;
   l_bcc_json       JSON_ARRAY_T;
   l_message_id     VARCHAR2(100);
   l_thread_id      VARCHAR2(100);
   i                PLS_INTEGER;
BEGIN
   -- Clear previous values
   last_response_json    := NULL;
   last_status_code      := NULL;
   last_response_message := NULL;

   IF p_message_id IS NULL THEN
      RAISE_APPLICATION_ERROR(-20021, 'p_message_id is required');
   END IF;

   IF p_text IS NULL AND p_html IS NULL THEN
      RAISE_APPLICATION_ERROR(-20022, 'Either p_text or p_html must be provided');
   END IF;

   l_url := 'https://api.agentmail.to/v0/inboxes/' ||
            p_inbox_id ||
            '/messages/' || apex_util.url_encode(p_message_id) ||
            '/reply';

   apex_web_service.g_request_headers.delete();
   apex_web_service.g_request_headers(1).name  := 'Authorization';
   apex_web_service.g_request_headers(1).value := 'Bearer ' || get_default_token();
   apex_web_service.g_request_headers(2).name  := 'Content-Type';
   apex_web_service.g_request_headers(2).value := 'application/json';

   l_json_obj := JSON_OBJECT_T();

   IF p_text IS NOT NULL THEN
      l_json_obj.put('text', p_text);
   END IF;

   IF p_html IS NOT NULL THEN
      l_json_obj.put('html', p_html);
   END IF;

   IF p_reply_all IS NOT NULL THEN
      IF UPPER(TRIM(p_reply_all)) IN ('Y', 'YES', 'TRUE', '1') THEN
         l_reply_all_bool := TRUE;
      ELSE
         l_reply_all_bool := FALSE;
      END IF;
      l_json_obj.put('reply_all', l_reply_all_bool);
   END IF;

   -- Labels array (optional)
   l_labels_json := JSON_ARRAY_T();
   IF p_labels IS NOT NULL AND p_labels.COUNT > 0 THEN
      FOR i IN 1 .. p_labels.COUNT LOOP
         l_labels_json.append(p_labels(i));
      END LOOP;
   END IF;
   l_json_obj.put('labels', l_labels_json);

   -- Helper: addresses arrays (to/cc/bcc)
   IF p_to IS NOT NULL AND p_to.COUNT > 0 THEN
      IF p_to.COUNT = 1 THEN
         l_json_obj.put('to', p_to(1));
      ELSE
         l_to_json := JSON_ARRAY_T();
         FOR i IN 1 .. p_to.COUNT LOOP
            l_to_json.append(p_to(i));
         END LOOP;
         l_json_obj.put('to', l_to_json);
      END IF;
   END IF;

   IF p_cc IS NOT NULL AND p_cc.COUNT > 0 THEN
      IF p_cc.COUNT = 1 THEN
         l_json_obj.put('cc', p_cc(1));
      ELSE
         l_cc_json := JSON_ARRAY_T();
         FOR i IN 1 .. p_cc.COUNT LOOP
            l_cc_json.append(p_cc(i));
         END LOOP;
         l_json_obj.put('cc', l_cc_json);
      END IF;
   END IF;

   IF p_bcc IS NOT NULL AND p_bcc.COUNT > 0 THEN
      IF p_bcc.COUNT = 1 THEN
         l_json_obj.put('bcc', p_bcc(1));
      ELSE
         l_bcc_json := JSON_ARRAY_T();
         FOR i IN 1 .. p_bcc.COUNT LOOP
            l_bcc_json.append(p_bcc(i));
         END LOOP;
         l_json_obj.put('bcc', l_bcc_json);
      END IF;
   END IF;

   -- Perform POST /v0/inboxes/{inbox_id}/messages/{message_id}/reply
   last_response_json := apex_web_service.make_rest_request(
      p_url         => l_url,
      p_http_method => 'POST',
      p_body        => l_json_obj.to_string()
   );

   last_status_code := apex_web_service.g_status_code;

   IF last_status_code BETWEEN 200 AND 299 THEN
      l_message_id := JSON_VALUE(last_response_json, '$.message_id');
      l_thread_id  := JSON_VALUE(last_response_json, '$.thread_id');
      last_response_message := 'Success: reply ' || NVL(l_message_id, '<unknown>') ||
                               ' in thread ' || NVL(l_thread_id, '<unknown>');
   ELSE
      RAISE_APPLICATION_ERROR(
         -20023,
         'AgentMail reply_to_message failed. Status: ' || last_status_code ||
         ' Response: ' || SUBSTR(last_response_json, 1, 1000)
      );
   END IF;

EXCEPTION
   WHEN OTHERS THEN
      apex_debug.error('reply_to_message failed: %s', SQLERRM);
      last_response_message := 'Exception: ' || SQLERRM;
      RAISE;
END reply_to_message;

END agentmail;
/