--
-- Tigase Unified Archive Component - Extension of implementation of Message Archiving component for Tigase XMPP Server
-- Copyright (C) 2015 Tigase, Inc. (office@tigase.com) - All Rights Reserved
-- Unauthorized copying of this file, via any medium is strictly prohibited
-- Proprietary and confidential
--

-- QUERY START:
drop procedure if exists Tig_UA_AddItem;
-- QUERY END:

delimiter //

-- QUERY START:
create procedure Tig_UA_AddItem(_ownerJid varchar(2049) CHARSET utf8, _buddyJid varchar(2049) CHARSET utf8, _ts timestamp(6),
    _stableId varchar(36) CHARSET utf8, _stanzaId varchar(64) CHARSET utf8, _refStableId varchar(36) CHARSET utf8,
	_body mediumtext CHARSET utf8mb4 collate utf8mb4_bin, _msg mediumtext CHARSET utf8mb4 collate utf8mb4_bin,
	_itemType smallint, _offline smallint)
begin
    declare _owner_id bigint;
	declare _buddy_id bigint;
    -- DO NOT REMOVE, required for properly handle exceptions within transactions!
    DECLARE exit handler for sqlexception
    BEGIN
        -- ERROR
        ROLLBACK;
        RESIGNAL;
    END;

	START TRANSACTION;
	call Tig_MA_EnsureJid(_ownerJid, _owner_id);
	COMMIT;
    START TRANSACTION;
	call Tig_MA_EnsureJid(_buddyJid, _buddy_id);
    COMMIT;

    START TRANSACTION;
    if _itemType = 2  then
        insert into tig_ma_msgs (owner_id, stable_id, buddy_id, ts, stanza_id, is_ref, ref_stable_id, body, msg,
                                 item_type, offline)
        select _owner_id, Tig_MA_UuidToOrdered(_stableId), _buddy_id, _ts, _stanzaId, case when _refStableId is null then 0 else 1 end, Tig_MA_UuidToOrdered(_refStableId), _body, _msg,
               _itemType, _offline
        where not exists (
                select 1 from tig_ma_msgs where owner_id = _owner_id and stable_id = Tig_MA_UuidToOrdered(_stableId)
        ) and not exists (
                select 1 from tig_ma_msgs where owner_id = _owner_id and buddy_id = _buddy_id and ts between TIMESTAMPADD(MINUTE, -30,_ts) and TIMESTAMPADD(MINUTE, 30, _ts) and stanza_id = _stanzaId
        );
    else
        insert into tig_ma_msgs (owner_id, stable_id, buddy_id, ts, stanza_id, is_ref, ref_stable_id, body, msg,
                                 item_type, offline)
        select _owner_id, Tig_MA_UuidToOrdered(_stableId), _buddy_id, _ts, _stanzaId, case when _refStableId is null then 0 else 1 end, Tig_MA_UuidToOrdered(_refStableId), _body, _msg,
               _itemType, _offline
        where not exists (
                select 1 from tig_ma_msgs where owner_id = _owner_id and stable_id = Tig_MA_UuidToOrdered(_stableId)
        );
    end if;
	COMMIT;
end //
-- QUERY END:

delimiter ;