ex.postgresql

module.exports = ({
  messageId,
  destination,
  source,
  subject,
  snippet,
  html,
  text
}) => `
  WITH data(
    subject,
    html,
    text,
    snippet,
    text_as_html,
    labels,
    message_id
  ) AS (
    VALUES(
      text '${subject}',
      text '${html}',
      text '${text}',
      text '${snippet}',
      text '${html}',
      '{SENT}'::label[],
      text '${messageId}'
    )
  ),
  threads_instance AS (
    INSERT INTO threads(
      user_id
    )
    SELECT id AS user_id FROM users WHERE email_address = '${source[0]}'
    RETURNING id AS thread_id
  ),
  messages_instance AS (
    INSERT INTO messages(
      user_id,
      subject,
      html,
      text,
      snippet,
      text_as_html,
      labels,
      message_id,
      thread_id
    )
    SELECT
      (SELECT id FROM users WHERE email_address = '${source[0]}'),
      data.subject,
      data.html,
      data.text,
      data.snippet,
      data.text_as_html,
      data.labels,
      data.message_id,
      thread_id
    FROM
      data,
      threads_instance
    RETURNING *
  ),
  source_instance AS (
    INSERT INTO entities(
      email_address,
      entity_type
    )
    select * from unnest(
      '{${source[0]}}'::text[],
      array_fill(
        'SOURCE'::entity_type,
        ARRAY[cardinality(
          '{${source[0]}}'::text[]
        )]
      )
    )
    RETURNING *
  ),
  destination_instance AS (
    INSERT INTO entities(
      email_address,
      entity_type
    )
    select * from unnest(
      '{${destination[0]}}'::text[],
      array_fill(
        'DESTINATION'::entity_type,
        ARRAY[cardinality(
          '{${destination[0]}}'::text[]
        )]
      )
    )
    RETURNING *
  ),
  source_connection AS (
    INSERT INTO messages_entities(
      message_id,
      entity_id
    )
    SELECT messages_instance.id, source_instance.id
    FROM messages_instance, source_instance
    RETURNING *
  ),
  destination_connection AS (
    INSERT INTO messages_entities(
      message_id,
      entity_id
    )
    SELECT messages_instance.id, destination_instance.id
    FROM messages_instance, destination_instance
    RETURNING *
  )
  SELECT
    messages_instance.*,
    source,
    destination
  FROM
    messages_instance,
    LATERAL(
      SELECT json_agg(
        json_build_object(
          'id', s.id,
          'emailAddress', s.email_address,
          'name', s.name,
          'entityType', s.entity_type
        )
      ) AS source
      FROM (
        SELECT *
        FROM source_instance
      ) AS s
    ) AS source,
    LATERAL(
      SELECT json_agg(
        json_build_object(
          'id', d.id,
          'emailAddress', d.email_address,
          'name', d.name,
          'entityType', d.entity_type
        )
      ) AS destination
      FROM (
        SELECT *
        FROM destination_instance
      ) AS d
    ) AS destination
`