create or replace function public.create_message (
  message message_dto,
  recipients recipient_dto[]
)
returns bigint as 
$body$
  declare
    message_id bigint;
    
  begin

    -- insert message
    insert into message (
      message_id, 
      subject, 
      body_html, 
      body_text
    ) values (
      default, 
      message.subject, 
      message.body_html, 
      message.body_text
    )
    returning message.message_id 
    into message_id;

    -- insert addresses
    insert into address (
      name, 
      address
    )
    select
      a.name,
      a.address
    from 
      unnest(recipients) as a        
    where not exists (
      select 1
      from
        address as b
      where
            a.name = b.name
        and a.address = b.address
    )
    group by
      a.name,
      a.address
    ;

    -- insert recipients
    insert into recipient (
      message_id,
      recipient_type_id,
      address_id
    ) 
    select 
      message_id,
      a.recipient_type_id,
      b.address_id
    from 
      unnest(recipients) as a
    inner join address b on 
        a.name = b.name and 
        a.address = b.address
    group by 
      a.recipient_type_id, 
      b.address_id;

    return message_id;
    
  end;
$body$
language plpgsql;