-- --------------------------------------------------------------------------- --
--                                                                             --
--    Postgresql scheme for iSida Jabber Bot                                   --
--    Copyright (C) 2012 diSabler <dsy@dsy.name>                               --
--                                                                             --
--    This program is free software: you can redistribute it and/or modify     --
--    it under the terms of the GNU General Public License as published by     --
--    the Free Software Foundation, either version 3 of the License, or        --
--    (at your option) any later version.                                      --
--                                                                             --
--    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 General Public License for more details.                             --
--                                                                             --
--    You should have received a copy of the GNU General Public License        --
--    along with this program.  If not, see <http://www.gnu.org/licenses/>.    --
--                                                                             --
-- --------------------------------------------------------------------------- --

-- --------------------------------------------------------------------------- --
-- Conference config
--
CREATE TABLE config_conf (
	room text,
	option text,
	value text
);

CREATE INDEX config_conf_r ON config_conf (room);
CREATE INDEX config_conf_ro ON config_conf (room,option);

-- --------------------------------------------------------------------------- --
-- Owner config
--
CREATE TABLE config_owner (
	option text unique,
	value text
);

CREATE INDEX config_owner_o ON config_owner (option);

-- --------------------------------------------------------------------------- --
-- Top command
--
CREATE TABLE top (
	room text unique,
	count integer,
	time integer
);

CREATE INDEX top_r ON top (room);
CREATE INDEX top_rc ON top (room,count);

-- --------------------------------------------------------------------------- --
-- Aliases
--
CREATE TABLE alias (
	room text,
	match text,
	cmd text
);

CREATE INDEX alias_r ON alias (room);
CREATE INDEX alias_rm ON alias (room,match);

-- --------------------------------------------------------------------------- --
-- Blacklist for rooms
--
CREATE TABLE blacklist (
	room text unique
);

-- --------------------------------------------------------------------------- --
-- Comm ON/OFF
--
CREATE TABLE commonoff (
	room text,
	cmd text
);

CREATE INDEX commonoff_r ON commonoff (room);
CREATE INDEX commonoff_rm ON commonoff (room,cmd);

-- --------------------------------------------------------------------------- --
-- Rooms list
--
CREATE TABLE conference (
	room text unique,
	passwd text
);

-- --------------------------------------------------------------------------- --
-- Feeds
--
CREATE TABLE feed (
	url text,
	update text,
	type text,
	time integer,
	room text,
	hash text array[10]
);

CREATE INDEX feed_r ON feed (room);
CREATE INDEX feed_rt ON feed (room,time);

-- --------------------------------------------------------------------------- --
-- Hiden rooms
--
CREATE TABLE hiden_rooms (
	room text unique
);

-- --------------------------------------------------------------------------- --
-- Bot Ignore
--
CREATE TABLE bot_ignore (
	pattern text unique
);

CREATE INDEX bot_ignore_p ON bot_ignore (pattern);

-- --------------------------------------------------------------------------- --
-- Bot Owner
--
CREATE TABLE bot_owner (
	jid text unique
);

CREATE INDEX bot_owner_j ON bot_owner (jid);

-- --------------------------------------------------------------------------- --
-- Logs in rooms
--
CREATE TABLE log_rooms (
	room text unique
);

CREATE INDEX log_rooms_r ON log_rooms (room);

-- --------------------------------------------------------------------------- --
-- Spy for activity
--
CREATE TABLE spy (
	room text unique,
	time integer,
	participant integer,
	message integer,
	pattern text
);

CREATE INDEX spy_r ON spy (room);
CREATE INDEX spy_rt ON spy (room,time);

-- --------------------------------------------------------------------------- --
-- Temporary ban
--
CREATE TABLE tmp_ban (
	room text,
	jid text,
	time integer
);

CREATE INDEX tmp_ban_r ON tmp_ban (room);
CREATE INDEX tmp_ban_rt ON tmp_ban (room,time);

-- --------------------------------------------------------------------------- --
-- Say to owner
--
CREATE TABLE saytoowner (
	jid text unique,
	time integer
);

-- --------------------------------------------------------------------------- --
-- Ignore ban for global_ban
--
CREATE TABLE ignore_ban (
	room text unique
);

-- --------------------------------------------------------------------------- --
-- First join time
--
CREATE TABLE first_join (
	room text,
	jid text,
	time integer
);

CREATE INDEX join_rj ON first_join (room,jid);

-- --------------------------------------------------------------------------- --
-- THE END
-- --------------------------------------------------------------------------- --