--
-- Tigase MUC - Multi User Chat component for Tigase
-- Copyright (C) 2007 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_muc_rooms (
	room_id bigint generated by default as identity not null,
	jid varchar(2049) not null,
	jid_sha1 varchar(50) not null,
	name varchar(1024),
	config varchar(32672),
	creator varchar(2049) not null,
	creation_date timestamp not null,
    subject varchar(32672),
    subject_creator_nick varchar(1024),
    subject_date timestamp,

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

-- QUERY START:
create unique index tig_muc_rooms_jid on tig_muc_rooms ( jid_sha1 );
-- QUERY END:

-- QUERY START:
create table tig_muc_room_affiliations (
	room_id bigint not null references tig_muc_rooms (room_id),
    jid varchar(2049) not null,
    jid_sha1 varchar(50) not null,
    affiliation varchar(20) not null
);
-- QUERY END:

-- QUERY START:
create unique index tig_muc_room_affiliations_room_id_jid on tig_muc_room_affiliations ( room_id, jid_sha1 );
-- QUERY END:

-- QUERY START:
create index tig_muc_room_affiliations_room_id on tig_muc_room_affiliations ( room_id );
-- QUERY END:

-- QUERY START:
create table tig_muc_room_history (
	room_jid varchar(2049) not null,
	room_jid_sha1 varchar(50) not null,
    event_type int,
    ts timestamp not null,
    sender_jid varchar(3074),
    sender_nickname varchar(1024),
	body varchar(32672),
	public_event boolean,
	msg varchar(32672)
);
-- QUERY END:

-- QUERY START:
create index tig_muc_room_history_room_jid on tig_muc_room_history ( room_jid_sha1 );
-- QUERY END:

-- QUERY START:
create index tig_muc_room_history_room_jid_ts on tig_muc_room_history ( room_jid_sha1, ts );
-- QUERY END:

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

-- QUERY START:
create procedure Tig_MUC_CreateRoom(roomJid varchar(2049), creatorJid varchar(2049), creationDate timestamp,
        roomName varchar(1024), roomConfig varchar(32672))
	PARAMETER STYLE JAVA
	LANGUAGE JAVA
	MODIFIES SQL DATA
	DYNAMIC RESULT SETS 1
	EXTERNAL NAME 'tigase.muc.repository.derby.StoredProcedures.tigMucCreateRoom';
-- QUERY END:

-- QUERY START:
create procedure Tig_MUC_DestroyRoom(roomJid varchar(2049))
    PARAMETER STYLE JAVA
    LANGUAGE JAVA
    MODIFIES SQL DATA
    EXTERNAL NAME 'tigase.muc.repository.derby.StoredProcedures.tigMucDestroyRoom';
-- QUERY END:

-- QUERY START:
create procedure Tig_MUC_GetRoomAffiliations(roomId bigint)
    PARAMETER STYLE JAVA
    LANGUAGE JAVA
    READS SQL DATA
    DYNAMIC RESULT SETS 1
    EXTERNAL NAME 'tigase.muc.repository.derby.StoredProcedures.tigMucGetRoomAffiliations';
-- QUERY END:

-- QUERY START:
create procedure Tig_MUC_GetRoom(roomJid varchar(2049))
    PARAMETER STYLE JAVA
    LANGUAGE JAVA
    READS SQL DATA
    DYNAMIC RESULT SETS 1
    EXTERNAL NAME 'tigase.muc.repository.derby.StoredProcedures.tigMucGetRoom';
-- QUERY END:

-- QUERY START:
create procedure Tig_MUC_GetRoomsJids()
    PARAMETER STYLE JAVA
    LANGUAGE JAVA
    READS SQL DATA
    DYNAMIC RESULT SETS 1
    EXTERNAL NAME 'tigase.muc.repository.derby.StoredProcedures.tigMucGetRoomsJids';
-- QUERY END:

-- QUERY START:
create procedure Tig_MUC_SetRoomAffiliation(roomId bigint, jid varchar(2049), affiliation varchar(20))
    PARAMETER STYLE JAVA
    LANGUAGE JAVA
    MODIFIES SQL DATA
    EXTERNAL NAME 'tigase.muc.repository.derby.StoredProcedures.tigMucSetRoomAffiliation';
-- QUERY END:

-- QUERY START:
create procedure Tig_MUC_SetRoomSubject(roomId bigint, subject varchar(32672), creator varchar(1024),
        changeDate timestamp)
    PARAMETER STYLE JAVA
    LANGUAGE JAVA
    MODIFIES SQL DATA
    EXTERNAL NAME 'tigase.muc.repository.derby.StoredProcedures.tigMucSetRoomSubject';
-- QUERY END:

-- QUERY START:
create procedure Tig_MUC_SetRoomConfig(roomJid varchar(2049), name varchar(1024), config varchar(32672))
    PARAMETER STYLE JAVA
    LANGUAGE JAVA
    MODIFIES SQL DATA
    EXTERNAL NAME 'tigase.muc.repository.derby.StoredProcedures.tigMucSetRoomConfig';
-- QUERY END:

-- QUERY START:
create procedure Tig_MUC_AddMessage(roomJid varchar(2049), "ts" timestamp, senderJid varchar(3074),
        senderNick varchar(1024), "body" varchar(32672), publicEvent boolean, "msg" varchar(32672))
    PARAMETER STYLE JAVA
    LANGUAGE JAVA
    MODIFIES SQL DATA
    EXTERNAL NAME 'tigase.muc.repository.derby.StoredProcedures.tigMucAddMessage';
-- QUERY END:

-- QUERY START:
create procedure Tig_MUC_DeleteMessages(roomJid varchar(2049))
    PARAMETER STYLE JAVA
    LANGUAGE JAVA
    MODIFIES SQL DATA
    EXTERNAL NAME 'tigase.muc.repository.derby.StoredProcedures.tigMucDeleteMessages';
-- QUERY END:

-- QUERY START:
create procedure Tig_MUC_GetMessages(roomJid varchar(2049), maxMessages int, "since" timestamp)
    PARAMETER STYLE JAVA
    LANGUAGE JAVA
    READS SQL DATA
    DYNAMIC RESULT SETS 1
    EXTERNAL NAME 'tigase.muc.repository.derby.StoredProcedures.tigMucGetMessages';
-- QUERY END:

-- QUERY START:
create procedure Tig_MUC_MAM_GetMessages(roomJid varchar(2049), "since" timestamp, "to" timestamp, "nickname" varchar(1024), "limit" int, "offset" int)
    PARAMETER STYLE JAVA
    LANGUAGE JAVA
    READS SQL DATA
    DYNAMIC RESULT SETS 1
    EXTERNAL NAME 'tigase.muc.repository.derby.StoredProcedures.tigMucMamGetMessages';
-- QUERY END:

-- QUERY START:
create procedure Tig_MUC_MAM_GetMessagePosition(roomJid varchar(2049), "since" timestamp, "to" timestamp, "nickname" varchar(1024), "id_ts" timestamp)
    PARAMETER STYLE JAVA
    LANGUAGE JAVA
    READS SQL DATA
    DYNAMIC RESULT SETS 1
    EXTERNAL NAME 'tigase.muc.repository.derby.StoredProcedures.tigMucMamGetMessagePosition';
-- QUERY END:

-- QUERY START:
create procedure Tig_MUC_MAM_GetMessagesCount(roomJid varchar(2049), "since" timestamp, "to" timestamp, "nickname" varchar(1024))
    PARAMETER STYLE JAVA
    LANGUAGE JAVA
    READS SQL DATA
    DYNAMIC RESULT SETS 1
    EXTERNAL NAME 'tigase.muc.repository.derby.StoredProcedures.tigMucMamGetMessagesCount';
-- QUERY END:

-- QUERY START:
call TigSetComponentVersion('muc', '3.0.0');
-- QUERY END:
