cservice.sql

------------------------------------------------------------------------------------
-- "$Id: cservice.sql,v 1.87 2008/11/12 20:45:42 mrbean_ Exp $"
-- Channel service DB SQL file for PostgreSQL.

-- ChangeLog:
-- 2026-01-09: MrIron
--			   Added column for scram records.
--             Added table for TLS fingerprints.
-- 2025-04-01: Empus
--             Added ident column to user_sec_history table
--             Added deleted column to user_sec_history table
--             Added materialized views functions to track linked users by IP and ident
--             Added indexes on user_sec_history table to improve performance
--             Added get_linked_users function to get the linked users for a given username
-- 2024-08-05: MrIron
--             Added columns for the JOINLIM feature written by Telac.
-- 2017-01-24:Empus
--             Added table 'pending_chanfix_scores' to integrate 
--				channel applications with mod.openchanfix
-- 2014-08-11: Seven
--             Added table 'user_sec_history' to track user login history
-- 2013-12-07: Seven
--             Changed 'pending_traffic' table 'ip_number' column type to inet
--             Changed 'whitelist' table 'IP' column type to inet
--             Updated table structure 'ip_restrict';
-- 2012-05-25: MrBean
--	       Added 'totp_key' colum  to 'users' table	
-- 2011-12-12: Spike
--	       Added gline and whitelist tables.
-- 2006-08-10: nighty
--             Modified flags definitions for table channels, according to actual
--             truth, thus adding a previously undocumented flag : 0x200 for FLOATLIM Active.
-- 2006-07-17: nighty
--             Added description for MIA Review Tag
-- 2005-11-17: nighty
--             Moved table 'ip_restrict' from cservice.web.sql to cservice.sql
--             Added indexes to table 'adminlog'
--             Added a new table for X to process notices to info channel from the website
-- 2002-03-09: nighty
--             Added 'maxlogins' column in 'users' table.
-- 2002-03-07: nighty
--             Updated channel_log/user_log events types.
-- 2002-02-16: nighty
--             Added one field to 'users' for TimeZone.
-- 2001-12-29: nighty
--             Added two fields to 'channels' to reflect changes on FLOATING LIMIT in mod.cservice
-- 2001-12-06: nighty
--             Added two fields to 'users' table to take care of an abuse hunting module.
-- 2001-10-14: nighty
--             Corrected bogus table entries and added missing fields / tables.
-- 2001-04-30: Gte
--             Redesigned deletion system, new table called deletion_transactions
--             to store deletion details for CMaster's to clear cached values.
-- 2001-01-04: Gte
--             Added 'deleted' flag, to flag records as deleted
--             (To enable CMaster to see deletions - can be *really* deleted during
--             routine maintainence/vacuum).
--             If deleted, value is non zero - null value treated as undeleted.
-- 2000-12-30: Gte
--             Added some update notification events for CMaster to listen
--             on and refresh its internal cache.
--
-- 2000-12-22: Gte
--             Fixed invalid UserID reference in userlog table.
--
-- 2000-12-10: Gte
--             Fixed a few typo's, changed TIMESTAMP's to INT4's.
--
-- 2000-10-22: Isomer
--             Removed 'ChannelManager' information from channels table
--             Added email/url/public_key to users table
--             Defined the flags
--             added channellog/userlog for combined ilc databases etc.
--             Changed many strings to 'TEXT'.
--             removed nick_flood_pro.
--             added defaults for flood_pro's.
--             changed the key type on the bans table.
--             added supporters table.
--             lotsa misc things
--             added 'added_by' and 'added_by_ts' to access table
--             users now have language, not channels
--             checked current CS sources in case we missed something.
--
-- 2000-09-21: Bleep
--             Added last_updated timestamps
--
-- Prior: Maintained by moof.

-- The service supports multiple languages, defined in language
-- files.
CREATE TABLE languages (
	id SERIAL,
	code VARCHAR( 16 ) UNIQUE,
	name VARCHAR( 16 ),
	last_updated INT4 NOT NULL,
	deleted INT2 DEFAULT '0',
	PRIMARY KEY(id)
);

-- Translations for multi-lingual support.
CREATE TABLE translations (
	language_id INT4 CONSTRAINT translations_language_id_ref REFERENCES languages ( id ),
	response_id INT4 NOT NULL DEFAULT '0',
	text TEXT,
	last_updated INT4 NOT NULL,
	deleted INT2 DEFAULT '0',

	PRIMARY KEY (language_id, response_id)
);

CREATE TABLE help (
	topic VARCHAR(20) NOT NULL,
	language_id INT4 CONSTRAINT help_language_id_ref REFERENCES languages ( id ),
	contents TEXT
);

CREATE INDEX help_topic_idx ON help (topic);
CREATE INDEX help_language_id_idx ON help (language_id);

-- Create the channel table first since we'll be referring back to it
-- frequently.
CREATE TABLE channels (
	id SERIAL,
	name TEXT NOT NULL UNIQUE,
	canon_name TEXT NOT NULL UNIQUE,
	flags INT4 NOT NULL DEFAULT '0',
-- 0x0000 0001 - No Purge
-- 0x0000 0002 - Special Channel
-- 0x0000 0004 - No Reg -- Don't register for a time period after which is
			-- reviewed by cservice admin.
-- 0x0000 0008 - Never Reg -- Never register
-- 0x0000 0010 - Channel Suspended
			-- everyone (including the 500) is suspended.
-- 0x0000 0020 - Temp Manager
-- 0x0000 0040 - Cautioned
-- 0x0000 0080 - Manager on Vacation
-- 0x0000 0100 - Channel Locked.
-- 0x0000 0200 - FLOATLIM Active
-- 0x0000 0400 - MIA Review Tag

-- 0x0001 0000 - AlwaysOp
-- 0x0002 0000 - StrictOp
-- 0x0004 0000 - NoOp
-- 0x0008 0000 - AutoTopic
-- 0x0010 0000 - OpOnly (Depricated).
-- 0x0020 0000 - AutoJoin

	-- Do we want to keep either or both of these?
	-- nb: removed nickflood pro.  not useful.
	mass_deop_pro INT2 NOT NULL DEFAULT 3,
	flood_pro INT4 NOT NULL DEFAULT '0',
	url VARCHAR (128),
	description VARCHAR (300),
	-- Any administrative comments that apply globally to this
	-- channel.
	comment VARCHAR (300),
	keywords VARCHAR(300),
	registered_ts INT4,
	channel_ts INT4 NOT NULL,
	channel_mode VARCHAR(26),

-- USERFLAGS: Defaults new access records to:
-- 0: No Default
-- 1: AutoOP
-- 2: AutoVOICE

	userflags INT2 DEFAULT '0',

	limit_offset INT4 DEFAULT '3',
	limit_period INT4 DEFAULT '20',
	limit_grace INT4 DEFAULT '1',
	limit_max INT4 DEFAULT '0',
	no_take INT4 DEFAULT '0',

	last_updated INT4 NOT NULL,
	deleted INT2 DEFAULT '0',
-- max_bans: override global max_bans setting
--   if set to 0, use global setting - there is NO unlimited option.
	max_bans INT4 DEFAULT '0',

	welcome VARCHAR(300) DEFAULT '',

	limit_joinmax INT4 DEFAULT '3',
	limit_joinsecs INT4 DEFAULT '1',
	limit_joinperiod INT4 DEFAULT '180',
	limit_joinmode VARCHAR(255) DEFAULT '+rb *!~*@*',
	PRIMARY KEY (id)
);

-- A channel is inactive if the manager hasn't logged in for 21 days
CREATE UNIQUE INDEX channels_name_idx ON channels(LOWER(name));
CREATE UNIQUE INDEX channels_canon_name_idx ON channels(canon_name);

-- Table for bans; channel_id references the channel entry this ban belongs to.
CREATE TABLE bans (

	id SERIAL,
	channel_id INT4 CONSTRAINT bans_channel_id_ref REFERENCES channels (id),
	banmask VARCHAR (128) NOT NULL,
	set_by VARCHAR (128),			-- nick!user@host
	set_ts INT4,
	level INT2,
	expires INT4,					-- Expiration timestamp.
	reason VARCHAR (300),
	last_updated INT4 NOT NULL,
	deleted INT2 DEFAULT '0',

	PRIMARY KEY (banmask,channel_id)
);

CREATE INDEX bans_expires_idx ON bans(expires);
CREATE INDEX bans_channelkey_idx ON bans(channel_id);

-- Access entries; admin access kept on channel '*'.
CREATE TABLE users (

	id SERIAL,
	user_name TEXT NOT NULL,
	password VARCHAR (40) NOT NULL,
	email TEXT,
	url  VARCHAR(128),
-- Which question the user provided the answer too from the signup page.
	question_id INT2,
-- The answer to the question. 42?
	verificationdata VARCHAR(30),
	language_id INT4 CONSTRAINT language_channel_id_ref REFERENCES languages (id),
	public_key TEXT,
	post_forms int4 DEFAULT 0 NOT NULL,
	flags INT4 NOT NULL DEFAULT '0',
-- 0x00 01 -- Suspended globally.
-- 0x00 02 -- Logged in (Depricated).
-- 0x00 04 -- Invisible.
-- 0x00 08 -- Fraud username.
-- 0x00 10 -- "No-Notes" - We don't want to be sent notes.
-- 0x00 20 -- "No Purge" - Don't remove the username for being idle.
-- 0x00 40 -- "No Admin" - Any * authentication is disabled (verify/commands) (its the DISABLEAUTH ON/OFF setting online)
-- 0x00 80 -- "Alumni" - Any * privileged is gone, *excepted* the verify; more like an honorary position
-- 0x01 00 -- IRC Operator - User is flagged as being 'supposed' to be an official operator, as for allowing them
	   --                special accesses such as posting complaints even when system is closed to public.
-- 0x02 00 -- "No ADDUSER" - prevent anyone adding username to channels (user-set flag, default disabled)
	last_updated_by VARCHAR (128),		-- nick!user@host
	last_updated INT4 NOT NULL,
	deleted INT2 DEFAULT '0',
	tz_setting VARCHAR(255) DEFAULT '',
	signup_cookie VARCHAR(255) DEFAULT '',
	signup_ts INT4,
	signup_ip VARCHAR(15),
	maxlogins INT4 DEFAULT 1,
	scram_record TEXT,
	totp_key  VARCHAR(60) DEFAULT '',
	PRIMARY KEY ( id )
) ;

CREATE INDEX users_username_idx ON users( lower(user_name) );
CREATE INDEX users_email_idx ON users( lower(email) );
CREATE INDEX users_signup_ts_idx ON users( signup_ts );
CREATE INDEX users_signup_ip_idx ON users( signup_ip );

-- This table used to store TLS fingerprints.

CREATE TABLE users_fingerprints (
    user_id INT4 NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    fingerprint VARCHAR(128) NOT NULL UNIQUE,
    added_ts BIGINT NOT NULL,
    added_by VARCHAR(128) NOT NULL,
	note TEXT
);

-- This table used to store the "Last Seen" informatation previously
-- routinely updated in the users table.
CREATE TABLE users_lastseen (
	user_id INT4 CONSTRAINT lastseen_users_id_ref REFERENCES users ( id ),
	last_seen INT4,
	last_hostmask VARCHAR( 256 ),
	last_ip VARCHAR( 256 ),
	last_updated INT4 NOT NULL,
	PRIMARY KEY (user_id)
);

-- Create a table to track user login history
CREATE TABLE user_sec_history (
	user_id INT4 NOT NULL,
	user_name TEXT NOT NULL,
	command TEXT NOT NULL,
	ip VARCHAR( 256 ) NOT NULL,
	ident TEXT NOT NULL,
	hostmask VARCHAR( 256 ) NOT NULL,
	timestamp INT4 NOT NULL,
	deleted TEXT NOT NULL DEFAULT 'N'
);

CREATE INDEX idx_user_sec_history_user_id ON user_sec_history(user_id);
CREATE INDEX idx_user_sec_history_hostmask ON user_sec_history(hostmask);
CREATE INDEX idx_user_sec_history_deleted ON user_sec_history(deleted);
CREATE INDEX idx_user_sec_history_ip_hostmask ON user_sec_history(ip, hostmask);
CREATE INDEX idx_ip_ident_username ON user_sec_history(ip, ident, user_name);

-- Create a materialized view to track the number of unique users per IP and ident
CREATE MATERIALIZED VIEW multiusers_ip_ident AS
SELECT 
  ip,
  ident,
  COUNT(*) AS user_count,
  array_agg(user_name ORDER BY user_name) AS user_names
FROM (
  SELECT DISTINCT ip, ident, user_name
  FROM user_sec_history
  WHERE deleted = 'N'
) AS unique_rows
GROUP BY ip, ident
HAVING COUNT(*) > 3;

CREATE INDEX idx_multiusers_ip_ident_usercount ON multiusers_ip_ident(user_count DESC);
-- REFRESH MATERIALIZED VIEW multiusers_ip_ident;

-- Create a materialized view to correlate linked users by IP and ident, with a linked_count and list of users
CREATE MATERIALIZED VIEW multiusers_linked AS
WITH user_fingerprints AS (
  SELECT DISTINCT ip, ident, user_name
  FROM user_sec_history
  WHERE ident IS NOT NULL AND deleted = 'N'
),
linked_pairs AS (
  SELECT DISTINCT a.user_name AS user_name, b.user_name AS linked_user
  FROM user_fingerprints a
  JOIN user_fingerprints b
    ON a.ip = b.ip AND a.ident = b.ident
   AND a.user_name <> b.user_name
)
SELECT
  user_name,
  COUNT(*) AS user_count,
  array_agg(linked_user ORDER BY linked_user) AS linked_usernames
FROM linked_pairs
GROUP BY user_name
HAVING COUNT(*) > 0
ORDER BY user_count DESC;

CREATE INDEX idx_multiusers_linked_username ON multiusers_linked(user_name);
CREATE INDEX idx_multiusers_linked_usercount ON multiusers_linked(user_count DESC);
-- REFRESH MATERIALIZED VIEW multiusers_linked;

-- Create a function to get the linked users for a given username
CREATE OR REPLACE FUNCTION get_linked_users(user_id INTEGER)
RETURNS TABLE (
  total_usernames INTEGER,
  all_usernames TEXT[]
)
AS $$
DECLARE
  uname TEXT;
BEGIN
  -- Get the most recent user_name for this user_id (in case of renames)
  SELECT ush.user_name INTO uname
  FROM user_sec_history ush
  WHERE ush.user_id = get_linked_users.user_id AND deleted = 'N'
  ORDER BY timestamp DESC
  LIMIT 1;

  IF uname IS NULL THEN
    RETURN;
  END IF;

  -- Recursively get all linked usernames
  RETURN QUERY
  WITH RECURSIVE link_graph(user_name) AS (
    SELECT user_name
    FROM multiusers_linked
    WHERE user_name = uname

    UNION

    SELECT unnest(linked_usernames)
    FROM multiusers_linked
    JOIN link_graph ON multiusers_linked.user_name = link_graph.user_name
  )
  SELECT
    COUNT(DISTINCT user_name)::INTEGER,
    array_agg(DISTINCT user_name ORDER BY user_name)
  FROM link_graph
  WHERE user_name <> uname;
END;
$$ LANGUAGE plpgsql STABLE;




-- Channel access table
CREATE TABLE levels (

	channel_id INT4 CONSTRAINT levels_channel_id_ref REFERENCES channels ( id ),
	user_id INT4 CONSTRAINT levels_users_id_ref REFERENCES users ( id ),
	access INT4 NOT NULL DEFAULT '0',
	flags INT2 NOT NULL DEFAULT '0',
-- 0x00 01 -- AutoOp
-- 0x00 02 -- Protect  (From CS source, unused)
-- 0x00 04 -- Temp forced access - Temp used by bot, ignore.
-- 0x00 08 -- AutoVoice
	suspend_expires INT4 DEFAULT '0',
	suspend_level INT4 DEFAULT '0',
	suspend_by VARCHAR( 128 ),
	suspend_reason VARCHAR( 300 ),
	added INT4,
	added_By VARCHAR( 128 ),
	last_Modif INT4,
	last_Modif_By VARCHAR( 128 ),
	last_Updated INT4 NOT NULL,
	deleted INT2 DEFAULT '0',

	PRIMARY KEY( channel_id, user_id )
);

CREATE INDEX levels_access_idx ON levels( access ) ;
CREATE INDEX levels_userid_idx ON levels( user_id ) ;

-- Note: The below index is a new postgres 7.2 feature which vastly speeds up the
-- checking of expired suspension levels. If you still use <7.2, stick to the index
-- commented out below.
-- CREATE INDEX levels_suspendexpires_idx ON levels( suspend_expires ) ;
CREATE INDEX levels_suspendexpires_idx ON levels( suspend_expires ) WHERE suspend_expires <> 0;

CREATE TABLE channellog (
	ts INT4,
	channelID INT4 CONSTRAINT channel_log_ref REFERENCES channels ( id ),
	event INT2 DEFAULT '0',
	-- Defines the message event type, so we can filter nice reports.
-- 1  -- EV_MISC - Uncategorised event.
-- 2  -- EV_JOIN - When someone 'JOIN's the bot.
-- 3  -- EV_PART - When someone 'PART's the bot.
-- 4  -- EV_OPERJOIN - When an oper 'JOIN's the bot.
-- 5  -- EV_OPERPART - When an oper 'PART's the bot.
-- 6  -- EV_FORCE - When someone FORCE's access in a channel.
-- 7  -- EV_REGISTER - When this channel is (re)registered.
-- 8  -- EV_PURGE - When this channle is purged.
-- 9  -- EV_COMMENT - Generic comments.
-- 10 -- EV_REMOVEALL - When REMOVEALL command is used.
-- 11 -- EV_IDLE - When a channel is idle for > 48 hours.
-- 12 -- EV_MGRCHANGE - When a channel switched managers either temporarily or permanently
-- 13 -- EV_ADMREJECT - When an application gets manually rejected by an admin on the web
-- 14 -- EV_WITHDRAW - When an application gets cancelled by its applicant on the web
-- 15 -- EV_NEWAPP - When a new application is posted by a user on the web
-- 16 -- EV_NONSUPPORT - When an application gets rejected due to NON-SUPPORT from one of the supporters on the web
-- 17 -- EV_ADMREVIEW - When an admins "Review" an application
-- 18 -- EV_CLRREVIEW - When a Reviewed application get its "Reviewed" flag cleared by an admin.
-- 19 -- EV_SUSPEND - When a channel is suspended by an administrator (channel.flags & 0x10 is 'set')
-- 20 -- EV_UNSUSPEND - When a channel is unsuspended by an administrator (channels.flags & 0x10 is 'unset')
	message TEXT,
	last_updated INT4 NOT NULL,
	deleted INT2 DEFAULT '0'
);

CREATE INDEX channellog_channelID_idx ON channellog(channelID);
CREATE INDEX channellog_event_idx ON channellog(event);

CREATE TABLE userlog (
	ts INT4,
	user_id INT4 CONSTRAINT user_log_ref REFERENCES users ( id ),
	event INT4 DEFAULT '0',
-- 1 -- EV_SUSPEND - Notification/Reason for suspension.
-- 2 -- EV_UNSUSPEND - Notification of an unsuspend.
-- 3 -- EV_MODIF - Modification of user record by an admin.
-- 4 -- EV_MISC - Uncategorised event.
-- 5 -- EV_COMMENT - Admin comment on username.
-- 6 -- EV_MGRCHANGE - When a user status is to swith manager with another user (also logged) on a channel
-- 7 -- EV_MAILCHANGE - When a user's email gets changed
-- 8 -- EV_PWRESET - When a user's verif q/a get changed
-- 9 -- EV_FPASSWD - When a user uses "Forgotten Password"
-- 10-- EV_PWCHANGE - When a user changes his password on the web (New Password)
-- 11-- EV_POSTCOMPLAINT - When a user identified posts a complaint on the web
-- 12-- EV_POSTCLOSE - When a Ticket/Complaint is closed or resolved.
	message TEXT,
	last_updated INT4 NOT NULL
);

CREATE INDEX userlog_channelID_idx ON userlog(user_id);
CREATE INDEX userlog_event_idx ON userlog(event);

CREATE TABLE supporters (
	channel_id INT4 CONSTRAINT channel_supporters_ref REFERENCES channels ( id ),
	user_id INT4 CONSTRAINT users_supporters_ref REFERENCES users( id ),
	support CHAR DEFAULT '?',
-- ? - Not answered yet.
-- Y - Supports this channel.
-- N - Doesn't support this channel.
	noticed CHAR NOT NULL DEFAULT 'N',
	reason TEXT,
-- Reason for not supporting it if required.
	join_count INT4 DEFAULT '0',
-- Number of times this 'supporter' has joined the channel.
-- Field updated by CMaster to reflect channel 'traffic'.
	last_updated INT4 NOT NULL,
	deleted INT2 DEFAULT '0',

	PRIMARY KEY(channel_id,user_id)
);

CREATE INDEX supporters_support_idx ON supporters(support);
CREATE INDEX supporters_user_id_idx ON supporters(user_id);

-- Pending channel applications table
CREATE TABLE pending (
	channel_id INT4 CONSTRAINT pending_channel_ref REFERENCES channels (id),
	manager_id INT4 CONSTRAINT pending_manager_ref REFERENCES users (id),
	created_ts INT4 NOT NULL,
	check_start_ts INT4 NOT NULL,
	status INT4 DEFAULT '0',
	-- Status of 'pending' channel:
	-- 0 = 'Pending Supporters Confirmation'
	-- 1 = 'Traffic Check'
	-- 2 = 'Notification'
	-- 3 = 'Completed'
	-- 4 = 'Cancelled by applicant'
	-- 8 = 'Pending Admin Review'
	-- 9 = 'Rejected'
	join_count INT4 DEFAULT '0',
	unique_join_count INT4 DEFAULT '0',
	decision_ts INT4,
	decision TEXT,
	managername VARCHAR (80),
	reg_acknowledged CHAR DEFAULT 'N',
	comments TEXT,
	last_updated INT4 NOT NULL,
	description TEXT,
	reviewed CHAR NOT NULL DEFAULT 'N',
	first_init CHAR NOT NULL DEFAULT 'N',
	reviewed_by_id INT4 CONSTRAINT pending_review_ref REFERENCES users (id),
	PRIMARY KEY(channel_id)
);

CREATE INDEX pending_status_idx ON pending(status);
CREATE INDEX pending_manager_id_idx ON pending(manager_id);

-- Traffic checking during channel applications
CREATE TABLE pending_traffic (
	channel_id INT4 CONSTRAINT pending_traffic_channel_ref REFERENCES channels (id),
	ip_number inet,
	join_count INT4,
	PRIMARY KEY(channel_id, ip_number)
);

CREATE INDEX pending_traffic_channel_id_idx ON pending_traffic(channel_id);

-- Chanfix scores during channel applications
CREATE TABLE pending_chanfix_scores (
	channel_id INT4 CONSTRAINT pending_chanfix_scores_channel_ref REFERENCES channels (id),
	user_id TEXT NOT NULL DEFAULT '0',
	rank INT4 NOT NULL DEFAULT '0',
	score INT4 NOT NULL DEFAULT '0',
	account VARCHAR(20) NOT NULL,
	first_opped VARCHAR(10),
	last_opped VARCHAR(20),
	last_updated INT4 NOT NULL DEFAULT date_part('epoch', CURRENT_TIMESTAMP)::int,
	first CHAR NOT NULL DEFAULT 'Y'
);

CREATE INDEX pending_chanfix_scores_channel_id_idx ON pending_chanfix_scores(channel_id);


CREATE TABLE domain (
	id SERIAL,
	domain varchar(1024) NOT NULL UNIQUE,
	flags INT2 NOT NULL DEFAULT '1',
-- Flags are exclusive and can be SELECT'd on.
-- 0x00 01 - Bad Domain.
-- 0x00 02 - Good Domain.
-- 0x00 04 - Pending Domain.
	last_updated INT4 NOT NULL,
	deleted INT2 DEFAULT '0',

	PRIMARY KEY(id)
);

CREATE INDEX domain_domain_idx ON domain(domain);

CREATE TABLE deletion_transactions (
	tableID INT4,
-- Table Types:
-- 1 = users
-- 2 = channels
-- 3 = levels
-- 4 = bans
	key1 INT4,
	key2 INT4,
	key3 INT4,
-- Up to 3 key's that uniquely identify the data
-- being deleted in this table. See CMaster source
-- to determine how this is interpreted.
	last_updated INT4 NOT NULL
);

-- Table to deal with the whole no-reg schema.
-- We use username and channelname instead of id's because these records may
-- exist past the lifetime of particular user accounts, and we'll want
-- to make sure certain email address's remain unable to register, etc.
-- Specific flags are INT4's becuase postgres does not want to index on anything
-- smaller :/
CREATE TABLE noreg (
	id SERIAL,
	user_name TEXT,
	email TEXT,
	channel_name TEXT,
	type INT4 NOT NULL,
	-- 1 - Non-support registered against this channel/manager application.
	-- 2 - Abuse.
	-- 3 - Elective.
	-- 4 - Fraud Username.
	-- 5 - Username pattern'd lock (able to lock any new username from matching *mp3* for example)
	-- 6 - Verification answer lock (stored in 'user_name', planning on db fields renaming for more customisation later)
	never_reg INT4 NOT NULL DEFAULT '0',
	-- Never, ever register this channel, or user or pair.
	for_review INT4 NOT NULL DEFAULT '0',
	-- Don't automatically expire this, post for review.
	expire_time INT4,
	created_ts INT4,
 	set_by TEXT,
	reason TEXT
);

CREATE INDEX noreg_user_name_idx ON noreg (lower(user_name));
CREATE INDEX noreg_email_idx ON noreg (lower(email));
CREATE INDEX noreg_channel_name_idx ON noreg (lower(channel_name));
CREATE INDEX noreg_expire_time_idx ON noreg (expire_time);

-- User notes table
CREATE TABLE notes (
	message_id SERIAL,
	user_id INT4 CONSTRAINT users_notes_ref REFERENCES users( id ),
	from_user_id INT4 CONSTRAINT users_notes_ref2 REFERENCES users( id ),
	message VARCHAR( 300 ),
	last_updated INT4 NOT NULL,
	PRIMARY KEY(message_id, user_id)
);


CREATE TABLE notices (
	message_id SERIAL,
	user_id INT4 CONSTRAINT users_notes_ref REFERENCES users( id ),
	message VARCHAR( 300 ),
	last_updated INT4 NOT NULL,

	PRIMARY KEY(message_id, user_id)
);

--CREATE TABLE mailq (
--	user_id INT4 CONSTRAINT mailq_users_ref REFERENCES users(id),
--	channel_id INT4 CONSTRAINT mailq_channels_ref REFERENCES channels(id),
--	created_ts INT4,
--	template INT4,
--	var1 CHAR(128),
--	var2 CHAR(128),
--	var3 CHAR(128),
--	var4 CHAR(128),
--	var5 CHAR(128)
--);

-- Values of Templates:
-- MT_SUPPORTER		1
-- MT_REJECTED		2
-- MT_REGISTERED	3

-- Template arguments:
-- MT_REJECTED
-- var1		Reject Reason

-- Update notification rules.
-- (N.B: Disabled, aparently conditional RULES are no longer
-- supported in postgres 7.1.x).

--CREATE RULE cm1 AS ON UPDATE TO channels DO NOTIFY channels_u;
--CREATE RULE cm2 AS ON UPDATE TO bans DO NOTIFY bans_u;
--CREATE RULE cm3 AS ON UPDATE TO users DO NOTIFY users_u;
--CREATE RULE cm4 AS ON UPDATE TO levels DO NOTIFY levels_u;

CREATE FUNCTION update_users() RETURNS TRIGGER AS '
BEGIN
	NOTIFY users_u;
	RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE FUNCTION update_channels() RETURNS TRIGGER AS '
BEGIN
	NOTIFY channels_u;
	RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE FUNCTION update_levels() RETURNS TRIGGER AS '
BEGIN
	NOTIFY levels_u;
	RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE FUNCTION update_bans() RETURNS TRIGGER AS '
BEGIN
	NOTIFY bans_u;
	RETURN NEW;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER t_update_users AFTER UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE update_users();
CREATE TRIGGER t_update_bans AFTER UPDATE ON bans FOR EACH ROW EXECUTE PROCEDURE update_bans();
CREATE TRIGGER t_update_channels AFTER UPDATE ON channels FOR EACH ROW EXECUTE PROCEDURE update_channels();
CREATE TRIGGER t_update_levels AFTER UPDATE ON levels FOR EACH ROW EXECUTE PROCEDURE update_levels();

-- Function to create a new users_lastseen record for each new user added.
-- If the function fails, you may need to add the plpgsql scripting language support
-- to your database:
-- /usr/local/pgsql/bin/createlang plpgsql dbname -L /usr/local/pgsql/lib/

CREATE FUNCTION new_user() RETURNS TRIGGER AS '
-- creates the users associated last_seen record
BEGIN
	INSERT INTO users_lastseen (user_id, last_seen, last_updated) VALUES(NEW.id, extract(epoch FROM now())::int, extract(epoch FROM now())::int);
	RETURN NEW;
END;
' LANGUAGE 'plpgsql';

-- Trigger to call the function upon insert to users.

CREATE TRIGGER t_new_user AFTER INSERT ON users FOR EACH ROW EXECUTE PROCEDURE new_user();

-- Functions to automatically generate "Deletion Stubs" for removed records, so CMaster
-- can pick up on these and clear its cache.

CREATE FUNCTION delete_user() RETURNS TRIGGER AS '
BEGIN
	INSERT INTO deletion_transactions (tableID, key1, key2, key3, last_updated)
	VALUES(1, OLD.id, 0, 0, extract(epoch FROM now())::int);
	RETURN OLD;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER t_delete_user AFTER DELETE ON users FOR EACH ROW EXECUTE PROCEDURE delete_user();

-- Channel table Deletion Stubs
--

CREATE FUNCTION delete_channel() RETURNS TRIGGER AS '
BEGIN
	INSERT INTO deletion_transactions (tableID, key1, key2, key3, last_updated)
	VALUES(2, OLD.id, 0, 0, extract(epoch FROM now())::int);
	RETURN OLD;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER t_delete_channel AFTER DELETE ON channels FOR EACH ROW EXECUTE PROCEDURE delete_channel();

-- Level table Deletion Stubs
--

CREATE FUNCTION delete_level() RETURNS TRIGGER AS '
BEGIN
	INSERT INTO deletion_transactions (tableID, key1, key2, key3, last_updated)
	VALUES(3, OLD.channel_id, OLD.user_id, 0, extract(epoch FROM now())::int);
	RETURN OLD;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER t_delete_level AFTER DELETE ON levels FOR EACH ROW EXECUTE PROCEDURE delete_level();

-- Ban table Deletion Stubs
--

CREATE FUNCTION delete_ban() RETURNS TRIGGER AS '
BEGIN
	INSERT INTO deletion_transactions (tableID, key1, key2, key3, last_updated)
	VALUES(4, OLD.id, 0, 0, extract(epoch FROM now())::int);
	RETURN OLD;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER t_delete_ban AFTER DELETE ON bans FOR EACH ROW EXECUTE PROCEDURE delete_ban();

-- Table used to store run-time configurable settings.
CREATE TABLE variables (
	var_name VARCHAR(30),
	contents text,
	hint text,
	last_updated INT4,
	PRIMARY KEY(var_name)
);

-- Table used to store the admin log (converted from file to db).
CREATE TABLE adminlog (
	id SERIAL,
	user_id INT4 NOT NULL,
	cmd VARCHAR(100),
	args VARCHAR(255),
	timestamp INT4 NOT NULL,
	issue_by VARCHAR(255),
	PRIMARY KEY(id)
);

CREATE INDEX adminlog_c_idx ON adminlog(cmd,timestamp);
CREATE INDEX adminlog_u_idx ON adminlog(user_id,timestamp);
CREATE INDEX adminlog_a_idx ON adminlog(args);
CREATE INDEX adminlog_i_idx ON adminlog(issue_by);

-- IPR entries table
CREATE TABLE ip_restrict (
	id		SERIAL,
	user_id		int4 NOT NULL,
	added		int4 NOT NULL,
	added_by	int4 NOT NULL,
	type		int4 NOT NULL DEFAULT 0,
	value		inet NOT NULL,
	last_updated	int4 NOT NULL DEFAULT date_part('epoch', CURRENT_TIMESTAMP)::int,
	last_used	int4 NOT NULL DEFAULT 0,
	expiry		int4 NOT NULL,
	description	VARCHAR(255)
);

CREATE INDEX ip_restrict_idx ON ip_restrict(user_id,type);

-- Table to store messages from website for channel relay
CREATE TABLE webnotices (
	id 	SERIAL,
	created_ts 	int4 NOT NULL,
	contents 	VARCHAR(255) NOT NULL,
	PRIMARY KEY(id)
);

-- Network glines table
CREATE TABLE glines (
        Id SERIAL,
        Host VARCHAR(128) UNIQUE NOT NULL,
        AddedBy VARCHAR(128) NOT NULL,
        AddedOn INT4 NOT NULL,
        ExpiresAt INT4 NOT NULL,
        LastUpdated INT4 NOT NULL DEFAULT date_part('epoch', CURRENT_TIMESTAMP)::int,
        Reason VARCHAR(255)
);

-- Whitelist entry table to be read by website
CREATE TABLE whitelist (
        Id SERIAL,
        IP inet UNIQUE NOT NULL,
        AddedBy VARCHAR(128) NOT NULL,
        AddedOn INT4 NOT NULL,
        ExpiresAt INT4 NOT NULL,
        Reason VARCHAR(255)
);


-----------------------------------------------------------------------------------------
-- CService Website Related Tables
-- TODO: Determine which are required by X even without the website.

CREATE TABLE acl (
	acl_id SERIAL,
	user_id INT4 NOT NULL,
	isstaff INT2 NOT NULL,
	flags INT4 DEFAULT '0' NOT NULL,
-- 0x0001 - ACL_XCHGMGR_REVIEW
-- 0x0002 - ACL_XCHGMGR_ADMIN
-- 0x0004 - ACL_XMAILCH_REVIEW
-- 0x0008 - ACL_XMAILCH_ADMIN
-- 0x0016 - ACL_XHELP
-- 0x0032 - ACL_XHELP_CAN_ADD
-- 0x0064 - ACL_XHELP_CAN_EDIT
-- 0x0128 - ACL_WEBAXS_2
-- 0x0256 - ACL_WEBAXS_3
-- .. to be completed in the future ..
	xtra INT4 NOT NULL,
-- may vary, for example if 0x0016 is set in 'flags',
-- 'xtra' will contain the 'language_id' the user have power over (or '0' for *all*)
--
	last_updated INT4 NOT NULL,
	last_updated_by INT4 NOT NULL,
	suspend_expire INT4 DEFAULT '0' NOT NULL,
	suspend_by INT4 DEFAULT '0' NOT NULL,
	deleted INT2 DEFAULT '0' NOT NULL
);


CREATE TABLE fraud_lists (
	id SERIAL,
	name VARCHAR(255) NOT NULL
);

CREATE TABLE fraud_list_data (
	list_id INT4 NOT NULL,
	user_id INT4 REFERENCES users(id) NOT NULL
);


CREATE TABLE pending_pwreset (
        cookie VARCHAR(128) NOT NULL,
        user_id INT4 NOT NULL,
        question_id INT2 NOT NULL,
        verificationdata VARCHAR(30) NOT NULL,
        expiration INT4 NOT NULL
);

CREATE INDEX pending_pwreset_cookie_idx ON pending_pwreset(cookie);
CREATE INDEX pending_pwreset_user_id_idx ON pending_pwreset(user_id);
CREATE INDEX pending_pwreset_expiration_idx ON pending_pwreset(expiration);

CREATE TABLE locks (
	section INT2,
-- section: 1 : GLOBAL SITE LOCK
-- section: 2 : NEW REGISTRATIONS LOCK
-- section: 3 : NEW USERS LOCK
	since INT4,
	by INT4
);

CREATE TABLE counts (
-- OBSOLETE !!!
	count_type INT2,
-- type: 1 : NEW USERS
	count_count INT4
);

CREATE TABLE statistics (
	users_id	INT4 NOT NULL,
	stats_type	INT4 NOT NULL,
--	1	Total reviewed applications count
--	...	more capabilities
	stats_value_int	INT4 DEFAULT 0 NOT NULL,
	stats_value_chr	VARCHAR(255) DEFAULT '' NOT NULL,
	last_updated	INT4 NOT NULL
);

CREATE INDEX statistics_users_id_idx ON statistics(users_id);

--CREATE TABLE helpmgr_users (
--	user_id INT4 NOT NULL,
--	language_id INT2,
--	flags INT2 DEFAULT '1'
-- CAN_EDIT	: 0x01
-- CAN_ADD	: 0x02
--);

--CREATE INDEX helpmgr_users_user_id_idx ON helpmgr_users(user_id);
--CREATE INDEX helpmgr_language_id_idx ON helpmgr_users(language_id);
--CREATE INDEX helpmgr_flags_idx ON helpmgr_users(flags);

CREATE TABLE pending_mgrchange (
	id SERIAL,
	channel_id INT4 NOT NULL,
	manager_id INT4 NOT NULL,
	new_manager_id INT4 NOT NULL,
	change_type INT2,
-- change_type : 0 : temporary
-- change_type : 1 : permanent
	opt_duration INT4,
-- duration in seconds if temporary, 0 if permanent.
	reason TEXT,
	expiration INT4 DEFAULT 0,
	crc VARCHAR(128),
	confirmed INT2 DEFAULT 0,
	from_host VARCHAR(15) DEFAULT '0.0.0.0'
);

CREATE INDEX pending_mgrchange_id_idx ON pending_mgrchange(id);
CREATE INDEX pending_mgrchange_channel_id_idx ON pending_mgrchange(channel_id);
CREATE INDEX pending_mgrchange_manager_id_idx ON pending_mgrchange(manager_id);
CREATE INDEX pending_mgrchange_new_manager_id_idx ON pending_mgrchange(new_manager_id);
CREATE INDEX pending_mgrchange_change_type_idx ON pending_mgrchange(change_type);
CREATE INDEX pending_mgrchange_opt_duration_idx ON pending_mgrchange(opt_duration);
CREATE INDEX pending_mgrchange_expiration_idx ON pending_mgrchange(expiration);
CREATE INDEX pending_mgrchange_crc_idx ON pending_mgrchange(crc);
CREATE INDEX pending_mgrchange_confirmed_idx ON pending_mgrchange(confirmed);

--CREATE TABLE xatadmins (
--	admin_id INT4 NOT NULL,
--	admin_type INT2 DEFAULT 0
--);

--CREATE INDEX xatadmins_admin_id_idx ON xatadmins(admin_id);


CREATE TABLE pendingusers (
	user_name VARCHAR(12),
	cookie VARCHAR(32),
	email VARCHAR(255),
	expire INT4,
	question_id INT2,
	verificationdata VARCHAR(30),
	poster_ip VARCHAR(15) DEFAULT '',
	language INT4 NOT NULL
);

CREATE INDEX pendingusers_cookie_idx ON pendingusers(cookie);

CREATE TABLE pending_emailchanges (
	cookie VARCHAR(128) NOT NULL,
	user_id INT4 NOT NULL,
	old_email VARCHAR(255) NOT NULL,
	new_email VARCHAR(255) NOT NULL,
	expiration INT4 NOT NULL,
	phase INT4 NOT NULL
);

CREATE INDEX pending_emailchanges_cookie_idx ON pending_emailchanges(cookie);
CREATE INDEX pending_emailchanges_user_id_idx ON pending_emailchanges(user_id);
CREATE INDEX pending_emailchanges_expiration_idx ON pending_emailchanges(expiration);

CREATE TABLE pending_passwordchanges (
	cookie VARCHAR(128) NOT NULL,
	user_id INT4 NOT NULL,
	old_crypt VARCHAR(40) NOT NULL,
	new_crypt VARCHAR(40) NOT NULL,
	new_clrpass VARCHAR(255) NOT NULL,
	created_ts INT4 NOT NULL
);

-- This table stores the timestamp of the last request
-- from a particular IP.
-- Used to block abuse, such as requesting a password 50,000
-- times a minute.

CREATE TABLE lastrequests (
	ip VARCHAR(15) DEFAULT '0.0.0.0',
	last_request_ts INT4
);

CREATE INDEX lastrequests_ip_idx ON lastrequests(ip);

 -- list of admins that have the ability to modify NOREG entries (other admins may only list them)

--CREATE TABLE webaccessteam (
--	admin_id int4 REFERENCES users(id) NOT NULL,
--	level INT4 NOT NULL DEFAULT '0'
--);

-- recorded objections for channels.
CREATE TABLE objections (
	channel_id int4 REFERENCES channels(id) NOT NULL,
	user_id int4 REFERENCES users(id) NOT NULL,
	comment text NOT NULL,
	created_ts int4 NOT NULL,
	admin_only varchar(1) DEFAULT 'N'
-- 'Y' : the objection is an admin comment on only * users sees it.
-- 'N' : the objection is a regular one and everyone can see it.
);

CREATE TABLE timezones (
	tz_index SERIAL,
	tz_name VARCHAR(128) NOT NULL,
	tz_countrycode VARCHAR(5) NOT NULL,
	tz_acronym VARCHAR(10) NOT NULL,
	deleted INT2 DEFAULT '0',
	last_updated INT4 NOT NULL
);

CREATE TABLE complaints (
	id SERIAL,
	from_id int4 NOT NULL,
	from_email varchar (255) NOT NULL,
	inrec_email varchar (255) NOT NULL,
	complaint_type int4 NOT NULL,
	complaint_text text NOT NULL,
	complaint_logs text NOT NULL,
	complaint_channel1_id int4 NOT NULL,
	complaint_channel1_name text NOT NULL,
	complaint_channel2_id int4 NOT NULL,
	complaint_channel2_name text NOT NULL,
	complaint_users_id int4 NOT NULL,
	status int4 NOT NULL,
	nicelevel int4 NOT NULL,
	reviewed_by_id int4 NOT NULL,
	reviewed_ts int4 NOT NULL,
	created_ts int4 NOT NULL,
	created_ip varchar (15) DEFAULT '0.0.0.0' NOT NULL,
	created_crc varchar (128) NOT NULL,
	crc_expiration int4 NOT NULL,
	ticket_number varchar(32) NOT NULL,
	current_owner int4 NOT NULL,
	PRIMARY KEY (id)
);

CREATE TABLE complaints_threads (
	id SERIAL,
	complaint_ref int4 NOT NULL CONSTRAINT complaints_threads_ref REFERENCES complaints (id),
	reply_by int4 NOT NULL,
	reply_ts int4 NOT NULL,
	reply_text text NOT NULL,
	actions_text text NOT NULL,
	in_reply_to int4 NOT NULL,
	PRIMARY KEY (id)
);


CREATE TABLE complaint_types (
	-- not used for now...
	id SERIAL,
	complaint_label varchar(255) NOT NULL,
	PRIMARY KEY (id)
);

DELETE FROM complaint_types;
COPY "complaint_types" FROM stdin;
1	My username is suspended
2	Members of a registered channel are spamming my channel
3	I object to this channel application but I want to do so anonymously
4	My channel was purged and I want you to reconsider
5	My channel was purged and I want to know why
99	Other complaint
\.

CREATE TABLE complaints_reference (
	complaints_ref int4 NOT NULL CONSTRAINT complaints_reference_ref REFERENCES complaints (id),
	referenced_by int4 NOT NULL,
	referenced_to int4 NOT NULL,
	reference_ts int4 NOT NULL,
	is_new int4 DEFAULT '1' NOT NULL
);

CREATE INDEX complaints_ref_ref ON complaints_reference(complaints_ref,referenced_to);


CREATE TABLE default_msgs (
	id	SERIAL,
	type	int4 NOT NULL,
	label	varchar(255) NOT NULL,
	content	text NOT NULL
);

CREATE INDEX default_msgs_idx ON default_msgs(type);