--
-- 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 smallint default 1;
-- 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:
create table tig_ua_jingle (
	stable_id varchar(36) not null,
	owner_id bigint not null,
    direction smallint not null,
	sid varchar(128) not null,
    action varchar(50) not null,
    reason varchar(50),
    offline smallint not null default 0,

	primary key (owner_id, stable_id),
	foreign key (owner_id, stable_id) references tig_ma_msgs (owner_id, stable_id) on delete cascade,
	foreign key (owner_id) references tig_ma_jids (jid_id)
);
-- QUERY END:

-- QUERY START:
create table tig_ua_for (
	stable_id varchar(36) not null,
	owner_id bigint not null,
	buddy_res varchar(1024),

	primary key (owner_id, stable_id),
	foreign key (owner_id, stable_id) references tig_ma_msgs (owner_id, stable_id) on delete cascade,
	foreign key (owner_id) references tig_ma_jids (jid_id)
);
-- QUERY END:

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

-- QUERY START:
create procedure Tig_UA_GetItems(ownerJid varchar(2049), buddyJid varchar(2049), "from" timestamp, "to" timestamp, "refType" smallint,  "tags" varchar(32672), "contains" varchar(32672), "item_type" smallint, "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, "refType" smallint, "tags" varchar(32672), "contains" varchar(32672), "item_type" smallint, "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, "refType" smallint, "tags" varchar(32672), "contains" varchar(32672), "stableId" varchar(36), "item_type" smallint, "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" smallint, "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" smallint, "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), ts timestamp, "stableId" varchar(36), "stanzaId" varchar(64), "refStableId" varchar(50),
	"body" varchar(32672), "msg" varchar(32672), "item_type" smallint, "offline" int)
	PARAMETER STYLE JAVA
	LANGUAGE JAVA
	MODIFIES SQL DATA
	EXTERNAL NAME 'tigase.archive.unified.db.derby.StoredProcedures.addItem';
-- QUERY END:

-- QUERY START:
create procedure Tig_UA_FOR_AddItemOffline(ownerJid varchar(2049), "stanzaId" varchar(64), buddyRes varchar(1024))
	PARAMETER STYLE JAVA
	LANGUAGE JAVA
	MODIFIES SQL DATA
	EXTERNAL NAME 'tigase.archive.unified.db.derby.StoredProcedures.addItemOffline';
-- QUERY END:
-- QUERY START:
create procedure Tig_UA_AddItemJingle(ownerJid varchar(2049), "stableId" varchar(36), jingle_sid varchar(128), jingle_action varchar(50), jingle_reason varchar(50), jingle_offline int, direction int)
	PARAMETER STYLE JAVA
	LANGUAGE JAVA
	MODIFIES SQL DATA
	EXTERNAL NAME 'tigase.archive.unified.db.derby.StoredProcedures.addItemJingle';
-- 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:
