--
-- 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
--

-- LOAD FILE: database/derby-message-archiving-2.0.0.sql;

-- QUERY START:
alter table tig_ma_msgs add item_type varchar(50);
-- QUERY END:
-- QUERY START:
update tig_ma_msgs set item_type = 'chat' where item_type is null;
-- QUERY END:
-- QUERY START:
create index tig_ma_msgs_owner_id_item_type_index on tig_ma_msgs (owner_id, item_type);
-- QUERY END:

-- QUERY START:
alter table tig_ma_msgs add offline int;
-- QUERY END:
-- QUERY START:
update tig_ma_msgs set offline = 0 where offline is null;
-- QUERY END:
-- QUERY START:
create index tig_ma_msgs_owner_id_offline_index on tig_ma_msgs (owner_id, offline);
-- QUERY END:

-- QUERY START:
alter table tig_ma_msgs add jingle_offline int;
-- QUERY END:
-- QUERY START:
update tig_ma_msgs set jingle_offline = 0 where jingle_offline is null;
-- QUERY END:
-- QUERY START:
alter table tig_ma_msgs add jingle_sid varchar(128);
-- QUERY END:
-- QUERY START:
alter table tig_ma_msgs add jingle_action varchar(50);
-- QUERY END:
-- QUERY START:
alter table tig_ma_msgs add jingle_reason varchar(50);
-- QUERY END:



-- ---------------------
-- Stored procedures
-- ---------------------

-- QUERY START:
create procedure Tig_UA_GetItems(ownerJid varchar(2049), buddyJid varchar(2049), "from" timestamp, "to" timestamp, "tags" varchar(32672), "contains" varchar(32672), "item_type" varchar(50), "offline" int, "limit" int, "offset" int)
	PARAMETER STYLE JAVA
	LANGUAGE JAVA
	READS SQL DATA
	DYNAMIC RESULT SETS 1
	EXTERNAL NAME 'tigase.archive.unified.db.derby.StoredProcedures.getItems';
-- QUERY END:

-- QUERY START:
create procedure Tig_UA_GetItemsCount(ownerJid varchar(2049), buddyJid varchar(2049), "from" timestamp, "to" timestamp, "tags" varchar(32672), "contains" varchar(32672), "item_type" varchar(50), "offline" int)
	PARAMETER STYLE JAVA
	LANGUAGE JAVA
	READS SQL DATA
	DYNAMIC RESULT SETS 1
	EXTERNAL NAME 'tigase.archive.unified.db.derby.StoredProcedures.getItemsCount';
-- QUERY END:

-- QUERY START:
create procedure Tig_UA_GetItemPosition(ownerJid varchar(2049), buddyJid varchar(2049), "from" timestamp, "to" timestamp, "tags" varchar(32672), "contains" varchar(32672), "hash" varchar(50), "item_type" varchar(50), "offline" int)
	PARAMETER STYLE JAVA
	LANGUAGE JAVA
	READS SQL DATA
	DYNAMIC RESULT SETS 1
	EXTERNAL NAME 'tigase.archive.unified.db.derby.StoredProcedures.getItemPosition';
-- QUERY END:

-- QUERY START:
create procedure Tig_UA_GetCollections(ownerJid varchar(2049), buddyJid varchar(2049), "from" timestamp, "to" timestamp, "tags" varchar(32672), "contains" varchar(32672), byType int, "item_type" varchar(50), "offline" int, "limit" int, "offset" int)
	PARAMETER STYLE JAVA
	LANGUAGE JAVA
	READS SQL DATA
	DYNAMIC RESULT SETS 1
	EXTERNAL NAME 'tigase.archive.unified.db.derby.StoredProcedures.getCollections';
-- QUERY END:

-- QUERY START:
create procedure Tig_UA_GetCollectionsCount(ownerJid varchar(2049), buddyJid varchar(2049), "from" timestamp, "to" timestamp, "tags" varchar(32672), "contains" varchar(32672), byType int, "item_type" varchar(50), "offline" int)
	PARAMETER STYLE JAVA
	LANGUAGE JAVA
	READS SQL DATA
	DYNAMIC RESULT SETS 1
	EXTERNAL NAME 'tigase.archive.unified.db.derby.StoredProcedures.getCollectionsCount';
-- QUERY END:

-- QUERY START:
create procedure Tig_UA_AddItem(ownerJid varchar(2049), buddyJid varchar(2049), buddyRes varchar(1024), ts timestamp, 
	direction int, "type" varchar(20), "body" varchar(32672), "msg" varchar(32672), "hash" varchar(50), "item_type" varchar(50), "offline" int,
	jingle_sid varchar(128), jingle_action varchar(50), jingle_reason varchar(50), jingle_offline int)
	PARAMETER STYLE JAVA
	LANGUAGE JAVA
	MODIFIES SQL DATA
	DYNAMIC RESULT SETS 1
	EXTERNAL NAME 'tigase.archive.unified.db.derby.StoredProcedures.addItem';
-- QUERY END:

-- QUERY START:
create procedure Tig_UA_QueryRecent(ownerJid varchar(2049), "from" timestamp, "to" timestamp, "chat" smallint, "groupchat" smallint, "call" smallint, "call_success" smallint, "call_missed" smallint, "call_canceled" smallint, "limit" int, "offset" int)
	PARAMETER STYLE JAVA
	LANGUAGE JAVA
	READS SQL DATA
	DYNAMIC RESULT SETS 1
	EXTERNAL NAME 'tigase.archive.unified.db.derby.StoredProcedures.queryRecent';
-- QUERY END:

-- QUERY START:
create procedure Tig_UA_QueryRecentCount(ownerJid varchar(2049), "from" timestamp, "to" timestamp, "chat" smallint, "groupchat" smallint, "call" smallint, "call_success" smallint, "call_missed" smallint, "call_canceled" smallint)
	PARAMETER STYLE JAVA
	LANGUAGE JAVA
	READS SQL DATA
	DYNAMIC RESULT SETS 1
	EXTERNAL NAME 'tigase.archive.unified.db.derby.StoredProcedures.queryRecentCount';
-- QUERY END:

-- ----------------------------------------------------------------------------
-- Stored procedures for JDBCFlexibleOfflineMessageRetrievalRepository
-- ----------------------------------------------------------------------------

-- QUERY START:
create procedure Tig_UA_FOR_GetMessagesCount(ownerJid varchar(2049))
	PARAMETER STYLE JAVA
	LANGUAGE JAVA
	READS SQL DATA
	DYNAMIC RESULT SETS 1
	EXTERNAL NAME 'tigase.archive.unified.db.derby.StoredProcedures.for_GetMessagesCount';
-- QUERY END:

-- QUERY START:
create procedure Tig_UA_FOR_GetMessagesList(ownerJid varchar(2049))
	PARAMETER STYLE JAVA
	LANGUAGE JAVA
	READS SQL DATA
	DYNAMIC RESULT SETS 1
	EXTERNAL NAME 'tigase.archive.unified.db.derby.StoredProcedures.for_GetMessagesList';
-- QUERY END:

-- QUERY START:
create procedure Tig_UA_FOR_GetMessages(ownerJid varchar(2049), msgIds varchar(32672))
	PARAMETER STYLE JAVA
	LANGUAGE JAVA
	READS SQL DATA
	DYNAMIC RESULT SETS 1
	EXTERNAL NAME 'tigase.archive.unified.db.derby.StoredProcedures.for_GetMessages';
-- QUERY END:

-- QUERY START:
create procedure Tig_UA_FOR_RemoveMessages(ownerJid varchar(2049), msgIds varchar(32672))
	PARAMETER STYLE JAVA
	LANGUAGE JAVA
	MODIFIES SQL DATA
	EXTERNAL NAME 'tigase.archive.unified.db.derby.StoredProcedures.for_RemoveMessages';
-- QUERY END:

-- QUERY START:
create procedure Tig_UA_FOR_UnmarkOfflineMessages(ownerJid varchar(2049), msgIds varchar(32672))
	PARAMETER STYLE JAVA
	LANGUAGE JAVA
	MODIFIES SQL DATA
	EXTERNAL NAME 'tigase.archive.unified.db.derby.StoredProcedures.for_UnmarkOfflineMessages';
-- QUERY END:

-- QUERY START:
call TigSetComponentVersion('unified-archive', '2.0.0');
-- QUERY END:
