---

-- DROP FUNCTION mail.api_new_message_group(text, text, int4, text, text, int4, text);

CREATE OR REPLACE FUNCTION mail.api_new_message_group(curtitul text, curbody text, group_addr integer, curfiles text DEFAULT NULL::text, datesend text DEFAULT NULL::text, curmailbox integer DEFAULT 1, messtype text DEFAULT 'text'::text)
RETURNS text
LANGUAGE plpgsql
AS $function$declare
    -- attr     text[];
    attr_email text[];
    attr_ui text[];
    attr_chat_portal text[];
    listfiles text[];
    listid    int[];
    result    text;
    dformat text := 'DD.MM.YYYY HH24:MI:SS';
    tempdt    timestamp := null;
    namefiles text[];
    byteaarr bytea[];
    to_portal_v boolean;
    to_email_v boolean;
    to_ui_v boolean;
    chat_idx_v text[];
    notification_type_v text[];
    status_v text;
    status_email_v text;
    status_portal_v text;
begin
    -- select array_agg(mail) into attr from mail.members_group_recipients where id_group=group_addr;
    -- raise notice ' add - %', attr;
    if datesend is not null then
        begin
         tempdt := to_timestamp(datesend,dformat);
        exception
            when others then
                tempdt := null;
        end;
    end if;
    raise notice ' dt - %', tempdt;
    if curfiles is not null then
        listfiles := string_to_array(curfiles,',');
        listid := (string_to_array(curfiles,','))::int[];
--        raise notice ' curfiles - % listid - %', curfiles,listid;
    end if;
    if listid is not null then
        select array_agg(name), array_agg(bytes) into namefiles, byteaarr from dev.upload_file
        where id in (select unnest(listid) as id);
        delete from dev.upload_file where id in (select unnest(listid) as id);
    end if;



    select array_remove(array_agg(mail), NULL) into attr_email from mail.members_group_recipients where id_group=group_addr AND COALESCE(to_email, false) = true;
    select array_remove(array_agg(mail), NULL) into attr_ui from mail.members_group_recipients where id_group=group_addr AND COALESCE(to_ui, false) = true;
    --select array_remove(array_agg(chat_idx), NULL) into attr_chat_portal from mail.members_group_recipients where id_group=group_addr AND COALESCE(to_portal, false) = true;

----------------------------------------------------------------------------
    -- select to_portal, to_email, to_ui, chat_idx into to_portal_v, to_email_v, to_ui_v, chat_idx_v from mail.list_group_recipients where id=group_addr;
    -- select to_email, to_ui, to_portal, chat_idx from mail.members_group_recipients where id_group=group_addr;
/* select
        coalesce(true = ANY(array_agg(to_email)), false) AS to_email,
        coalesce(true = ANY(array_agg(to_ui)), false) AS to_ui,
        coalesce(true = ANY(array_agg(to_portal)), false) AS to_portal,
        array_remove(array_agg(chat_idx), NULL)
    into to_email_v, to_ui_v, to_portal_v, chat_idx_v
    from mail.members_group_recipients where id_group=group_addr; */

select
    coalesce(true = ANY(array_agg(to_email)), false),
    coalesce(true = ANY(array_agg(to_ui)), false)
into to_email_v, to_ui_v
from mail.members_group_recipients
where id_group = group_addr;

select
    coalesce(to_portal, false),
    case
        when coalesce(to_portal, false) = true
             and chat_idx is not null
        then ARRAYchat_idx]::text[
        else ARRAY[]::text[]
    end
into to_portal_v, attr_chat_portal
from mail.list_group_recipients
where id = group_addr;

    notification_type_v := '{}'::text[];

    -- raise notice 'to_portal - %, to_email - %, to_ui - %', to_portal_v, to_email_v, to_ui_v;

    if coalesce(to_email_v, false) = true then
        notification_type_v := array_append(notification_type_v, 'email');
    end if;
    if coalesce(to_ui_v, false) = true then
        notification_type_v := array_append(notification_type_v, 'ui');
    end if;
    if coalesce(to_portal_v, false) = true then
        notification_type_v := array_append(notification_type_v, 'portal');
    end if;

    status_email_v := '';
    status_portal_v := '';
    --status
    if coalesce(to_ui_v, false) = true and coalesce(to_email_v, false) = false and coalesce(to_portal_v, false) = false then
        status_v := 'processed';
        status_email_v := '';
        status_portal_v := '';
    end if;
    if coalesce(to_email_v, false) = true or coalesce(to_portal_v, false) = true then
        status_v := 'waiting';
        status_email_v := '';
        status_portal_v := '';
        if coalesce(to_email_v, false) = true then
            status_email_v := 'waiting';
        end if;
        if coalesce(to_portal_v, false) = true then
            status_portal_v := 'waiting';
        end if;
    end if;
------------------------------------------------------
-- portal_chat_id_list, group_recipients_id, list_recipient_ui
-- status, status_email, status_portal

    insert into mail.list_mails
    (message, list_recipient, title, list_attachment_bin, list_filename_bin, date_send, sendmailbox, type_message, notification_type, group_recipients_id, list_recipient_ui, portal_chat_id_list, "status", status_email, status_portal)
    values (curbody, attr_email, curtitul,byteaarr,namefiles, tempdt, curmailbox, messtype, notification_type_v, group_addr, attr_ui, attr_chat_portal, status_v, status_email_v, status_portal_v);
/*    raise notice 'namefiles %',namefiles;
    insert into mail.test_send (titul, body, adresats, files, dates)
    values (curtitul, curbody, attr, listfiles, tempdt);*/
    return 'Ok';
end;
$function$
;