--
-- Tigase Message Archiving Component - Implementation of Message Archiving component for Tigase XMPP Server.
-- Copyright (C) 2012 Tigase, Inc. (office@tigase.com)
--
-- This program is free software: you can redistribute it and/or modify
-- it under the terms of the GNU Affero General Public License as published by
-- the Free Software Foundation, version 3 of the License.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU Affero General Public License for more details.
--
-- You should have received a copy of the GNU Affero General Public License
-- along with this program. Look for COPYING file in the top folder.
-- If not, see http://www.gnu.org/licenses/.
--

-- QUERY START:
create table tig_ma_jids (
	jid_id bigint generated by default as identity not null,
	jid varchar(2049),
	jid_sha1 varchar(50),
	
	primary key(jid_id)
);
-- QUERY END:

-- QUERY START:
create unique index tig_ma_jids_jid on tig_ma_jids (jid);
-- QUERY END:

-- QUERY START:
create table tig_ma_msgs (
	msg_id bigint generated by default as identity not null,
	owner_id bigint not null references tig_ma_jids (jid_id),
	buddy_id bigint not null references tig_ma_jids (jid_id),
	ts timestamp,
	direction smallint,
	"type" varchar(20),
	body varchar(32672),
	msg varchar(32672),
	stanza_hash varchar(50),

	primary key (msg_id)
);
-- QUERY END:

-- QUERY START:
create index tig_ma_msgs_owner_id_index on tig_ma_msgs (owner_id);
-- QUERY END:
-- QUERY START:
create index tig_ma_msgs_owner_id_buddy_id_index on tig_ma_msgs (owner_id, buddy_id);
-- QUERY END:
-- QUERY START:
create index tig_ma_msgs_owner_id_buddy_id_ts_index on tig_ma_msgs (owner_id, buddy_id, ts);
-- QUERY END:
-- QUERY START:
create unique index tig_ma_msgs_owner_id_ts_buddy_id_stanza_hash_index on tig_ma_msgs (owner_id, ts, buddy_id, stanza_hash);
-- QUERY END:

-- QUERY START:
create table tig_ma_tags (
	tag_id bigint generated by default as identity not null,
	owner_id bigint not null  references tig_ma_jids (jid_id) on delete cascade,
	tag varchar(255),

	primary key (tag_id)
);
-- QUERY END:

-- QUERY START:
create index tig_ma_tags_owner_id on tig_ma_tags (owner_id);
-- QUERY END:
-- QUERY START:
create unique index tig_ma_tags_tag_owner_id on tig_ma_tags (owner_id, tag);
-- QUERY END:

-- QUERY START:
create table tig_ma_msgs_tags (
	msg_id bigint not null references tig_ma_msgs (msg_id) on delete cascade,
	tag_id bigint not null references tig_ma_tags (tag_id) on delete cascade,

	primary key (msg_id, tag_id)
);
-- QUERY END:

-- QUERY START:
create index tig_ma_tags_msg_id on tig_ma_msgs_tags (msg_id);
-- QUERY END:
-- QUERY START:
create index tig_ma_tags_tag_id on tig_ma_msgs_tags (tag_id);
-- QUERY END:

-- additional changes introduced later - after original schema clarified

-- addition of buddy_res field which should contain resource of buddy
-- QUERY START:
alter table tig_ma_msgs add buddy_res varchar(1024);
-- QUERY END:
-- QUERY START:
create index tig_ma_msgs_owner_id_buddy_id_buddy_res_index on tig_ma_msgs (owner_id, buddy_id, buddy_res);
-- QUERY END:

-- addition of domain field to jids table for easier removal of expired messages
-- QUERY START:
alter table tig_ma_jids add "domain" varchar(1024);
-- QUERY END:
-- QUERY START:
update tig_ma_jids set "domain" = SUBSTR(jid, LOCATE('@', jid) + 1) where "domain" is null;
-- QUERY END:
-- QUERY START:
create index tig_ma_jids_domain_index on tig_ma_jids ("domain");
-- QUERY END:

-- additional index on tig_ma_msgs to improve removal of expired messages
-- QUERY START:
create index tig_ma_msgs_ts_index on tig_ma_msgs (ts); 
-- QUERY END:

-- added unique constraint on tig_ma_msgs_tags
-- QUERY START:
--alter table tig_ma_msgs_tags add primary key (msgs_id, tag_id);
-- QUERY END:

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

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

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

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

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

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

-- QUERY START:
create function Tig_MA_EnsureJid(jid varchar(2049))
	RETURNS bigint
	PARAMETER STYLE JAVA
	NO SQL
	LANGUAGE JAVA
	EXTERNAL NAME 'tigase.archive.db.derby.StoredProcedures.ensureJid';
-- QUERY END:

-- QUERY START:
create procedure Tig_MA_AddMessage(ownerJid varchar(2049), buddyJid varchar(2049), buddyRes varchar(1024), ts timestamp, 
	direction smallint, "type" varchar(20), "body" varchar(32672), "msg" varchar(32672), "hash" varchar(50))
	PARAMETER STYLE JAVA
	LANGUAGE JAVA
	MODIFIES SQL DATA
	DYNAMIC RESULT SETS 1
	EXTERNAL NAME 'tigase.archive.db.derby.StoredProcedures.addMessage';
-- QUERY END:

-- QUERY START:
create procedure Tig_MA_AddTagToMessage(msg_id bigint, tag varchar(255))
	PARAMETER STYLE JAVA
	LANGUAGE JAVA
	MODIFIES SQL DATA
	EXTERNAL NAME 'tigase.archive.db.derby.StoredProcedures.addTagToMessage';
-- QUERY END:

-- QUERY START:
create procedure Tig_MA_RemoveMessages(ownerJid varchar(2049), buddyJid varchar(2049), "from" timestamp, "to" timestamp)
	PARAMETER STYLE JAVA
	LANGUAGE JAVA
	MODIFIES SQL DATA
	EXTERNAL NAME 'tigase.archive.db.derby.StoredProcedures.removeMessages';
-- QUERY END:

-- QUERY START:
create procedure Tig_MA_DeleteExpiredMessages("domain" varchar(1024), "before" timestamp)
	PARAMETER STYLE JAVA
	LANGUAGE JAVA
	MODIFIES SQL DATA
	EXTERNAL NAME 'tigase.archive.db.derby.StoredProcedures.deleteExpiredMessages';
-- QUERY END:

-- QUERY START:
create procedure Tig_MA_GetTagsForUser(ownerJid varchar(2049), tagStartsWith varchar(255), "limit" int, "offset" int)
	PARAMETER STYLE JAVA
	LANGUAGE JAVA
	READS SQL DATA
	DYNAMIC RESULT SETS 1
	EXTERNAL NAME 'tigase.archive.db.derby.StoredProcedures.getTagsForUser';
-- QUERY END:

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

-- QUERY START:
call TigSetComponentVersion('message-archiving', '2.0.0');
-- QUERY END:
