--
-- 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 (
	owner_id bigint not null references tig_ma_jids (jid_id),
	buddy_id bigint not null references tig_ma_jids (jid_id),
	ts timestamp,
	stable_id varchar(36) not null,
	stanza_id varchar(64),
	is_ref smallint not null,
	ref_stable_id varchar(36),
	body varchar(32672),
	msg varchar(32672),

	primary key (owner_id, stable_id)
);
-- QUERY END:

-- QUERY START:
create index tig_ma_msgs_owner_id_is_ref_ts_index on tig_ma_msgs (owner_id, is_ref, ts);
-- QUERY END:
-- QUERY START:
create index tig_ma_msgs_owner_id_buddy_id_is_ref_ts_index on tig_ma_msgs (owner_id, buddy_id, is_ref, ts);
-- QUERY END:
-- QUERY START:
create index tig_ma_msgs_owner_id_buddy_id_stanza_id_ts_index on tig_ma_msgs (owner_id, buddy_id, stanza_id, ts);
-- QUERY END:
-- QUERY START:
create index tig_ma_msgs_ref_stable_id_owner_id_index on tig_ma_msgs (ref_stable_id, owner_id);
-- 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_owner_id bigint not null,
	msg_stable_id varchar(36) not null,
	tag_id bigint not null references tig_ma_tags (tag_id) on delete cascade,

	primary key (msg_owner_id, msg_stable_id, tag_id),
	foreign key (msg_owner_id, msg_stable_id) references tig_ma_msgs (owner_id, stable_id) on delete cascade
);
-- 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 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, "refType" smallint, "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, "refType" smallint, "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, "refType" smallint, "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), "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))
	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), ts timestamp,
    "stableId" varchar(36), "stanzaId" varchar(64), "refStableId" varchar(36),
	"body" varchar(32672), "msg" varchar(32672))
	PARAMETER STYLE JAVA
	LANGUAGE JAVA
	MODIFIES SQL DATA
	EXTERNAL NAME 'tigase.archive.db.derby.StoredProcedures.addMessage';
-- QUERY END:

-- QUERY START:
create procedure Tig_MA_AddTagToMessage(ownerJid varchar(2049), stableId varchar(36), 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:
