-- $Id: maia-pgsql.sql 1568 2011-07-01 01:12:12Z rjl $
--
-- PLEASE NOTE: WHEN EDITING THIS FILE, USE iso-8859-1
--
--
-- MAIA MAILGUARD LICENSE v.1.0
--
-- Copyright 2004 by Robert LeBlanc <rjl@renaissoft.com>
--                   David Morton   <mortonda@dgrmm.net>
-- All rights reserved.
--
-- PREAMBLE
--
-- This License is designed for users of Maia Mailguard
-- ("the Software") who wish to support the Maia Mailguard project by
-- leaving "Maia Mailguard" branding information in the HTML output
-- of the pages generated by the Software, and providing links back
-- to the Maia Mailguard home page.  Users who wish to remove this
-- branding information should contact the copyright owner to obtain
-- a Rebranding License.
--
-- DEFINITION OF TERMS
--
-- The "Software" refers to Maia Mailguard, including all of the
-- associated PHP, Perl, and SQL scripts, documentation files, graphic
-- icons and logo images.
--
-- GRANT OF LICENSE
--
-- Redistribution and use in source and binary forms, with or without
-- modification, are permitted provided that the following conditions
-- are met:
--
-- 1. Redistributions of source code must retain the above copyright
--    notice, this list of conditions and the following disclaimer.
--
-- 2. Redistributions in binary form must reproduce the above copyright
--    notice, this list of conditions and the following disclaimer in the
--    documentation and/or other materials provided with the distribution.
--
-- 3. The end-user documentation included with the redistribution, if
--    any, must include the following acknowledgment:
--
--    "This product includes software developed by Robert LeBlanc
--    <rjl@renaissoft.com>."
--
--    Alternately, this acknowledgment may appear in the software itself,
--    if and wherever such third-party acknowledgments normally appear.
--
-- 4. At least one of the following branding conventions must be used:
--
--    a. The Maia Mailguard logo appears in the page-top banner of
--       all HTML output pages in an unmodified form, and links
--       directly to the Maia Mailguard home page; or
--
--    b. The "Powered by Maia Mailguard" graphic appears in the HTML
--       output of all gateway pages that lead to this software,
--       linking directly to the Maia Mailguard home page; or
--
--    c. A separate Rebranding License is obtained from the copyright
--       owner, exempting the Licensee from 4(a) and 4(b), subject to
--       the additional conditions laid out in that license document.
--
-- THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDER AND CONTRIBUTORS
-- "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
-- LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
-- FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
-- COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
-- INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
-- BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS
-- OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
-- ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR
-- TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE
-- USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.


-- [users] contains maiad's per-address settings, and
-- links e-mail addresses to Maia users.

CREATE TABLE users (
  id                SERIAL PRIMARY KEY,
  priority          INTEGER DEFAULT '7' NOT NULL,
  policy_id         INTEGER DEFAULT '1' NOT NULL, -- policy.id
  email             BYTEA NOT NULL UNIQUE,
  maia_user_id      INTEGER NOT NULL, -- maia_users.id
  maia_domain_id    INTEGER NOT NULL -- maia_domains.id
);
CREATE UNIQUE INDEX users_idx_email ON users(email);
CREATE INDEX users_idx_maia_user_id ON users(maia_user_id);

-- [mailaddr] contains a list of sender e-mail addresses
-- referenced by users' whitelists and blacklists.

CREATE TABLE mailaddr (
  id                   SERIAL PRIMARY KEY,
  priority             INTEGER DEFAULT '7' NOT NULL,
  email                BYTEA NOT NULL UNIQUE
);
CREATE UNIQUE INDEX mailaddr_idx_email ON mailaddr(email);


-- [wblist] contains the whitelist and blacklist records, on
-- a per-user (not per-address) basis.

CREATE TABLE wblist (
  rid                  INTEGER NOT NULL, -- maia_users.id
  sid                  INTEGER NOT NULL, -- mailaddr.id
  wb                   CHAR(1) NOT NULL,
  PRIMARY KEY (rid,sid)
);


-- [policy] contains maiad's policy settings, which
-- Maia applies on a per-address basis (i.e. each e-mail address
-- is assigned its own unique policy record).  Each domain also
-- has a set of policy defaults, and the system default policy
-- is stored as the '@.' user's policy record.

CREATE TABLE policy (
  id                   SERIAL PRIMARY KEY,
  policy_name          VARCHAR(255),
  virus_lover          CHAR(1) DEFAULT 'Y',
  spam_lover           CHAR(1) DEFAULT 'Y',
  banned_files_lover   CHAR(1) DEFAULT 'Y',
  bad_header_lover     CHAR(1) DEFAULT 'Y',
  bypass_virus_checks  CHAR(1) DEFAULT 'Y',
  bypass_spam_checks   CHAR(1) DEFAULT 'Y',
  bypass_banned_checks CHAR(1) DEFAULT 'Y',
  bypass_header_checks CHAR(1) DEFAULT 'Y',
  discard_viruses      CHAR(1) DEFAULT 'N',
  discard_spam         CHAR(1) DEFAULT 'N',
  discard_banned_files CHAR(1) DEFAULT 'N',
  discard_bad_headers  CHAR(1) DEFAULT 'N',
  spam_modifies_subj   CHAR(1) DEFAULT 'N',
  spam_quarantine_to   VARCHAR(64) DEFAULT NULL,
  spam_tag_level       NUMERIC(7,3) DEFAULT '999',
  spam_tag2_level      NUMERIC(7,3) DEFAULT '999',
  spam_kill_level      NUMERIC(7,3) DEFAULT '999'
);


-- [maia_config] contains Maia's configuration settings, as set
-- and modified by the super-administrator.

CREATE TABLE maia_config (
   id					INTEGER DEFAULT '0' PRIMARY KEY,
   enable_user_autocreation		CHAR(1) DEFAULT 'N' NOT NULL, -- 'Y', 'N'
   enable_false_negative_management	CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
   enable_stats_tracking		CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
   enable_virus_scanning		CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
   enable_spam_filtering		CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
   enable_banned_files_checking		CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
   enable_bad_header_checking		CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
   enable_charts			CHAR(1) DEFAULT 'N' NOT NULL, -- 'Y', 'N'
   enable_spamtraps			CHAR(1) DEFAULT 'N' NOT NULL, -- 'Y', 'N'
   enable_stats_reporting		CHAR(1) DEFAULT 'N' NOT NULL, -- 'Y', 'N'
   enable_address_linking		CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
   enable_privacy_invasion              CHAR(1) DEFAULT 'N' NOT NULL, -- 'Y', 'N'
   enable_username_changes              CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
   internal_auth			CHAR(1) DEFAULT 'N' NOT NULL, -- 'Y', 'N'
   system_default_user_is_local         CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
   user_virus_scanning                  CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
   user_spam_filtering                  CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
   user_banned_files_checking           CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
   user_bad_header_checking             CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
   admin_email				VARCHAR(255),
   expiry_period			INTEGER DEFAULT '30', -- days
   ham_cache_expiry_period		INTEGER DEFAULT '5', -- days
   reminder_threshold_count		INTEGER DEFAULT '100', -- items
   reminder_threshold_size		INTEGER DEFAULT '500000', -- bytes
   reminder_template_file		VARCHAR(255) DEFAULT 'reminder.tpl',
   reminder_login_url			VARCHAR(255),
   newuser_template_file		VARCHAR(255) DEFAULT 'newuser.tpl',
   smtp_server				VARCHAR(255) DEFAULT 'localhost',
   smtp_port				INTEGER DEFAULT '10025',
   currency_label			VARCHAR(15) DEFAULT '$',
   bandwidth_cost			NUMERIC(14,2) DEFAULT '0.0' NOT NULL,
   chart_ham_colour			VARCHAR(32) DEFAULT '#DDDDB7',
   chart_spam_colour			VARCHAR(32) DEFAULT '#FFAAAA',
   chart_virus_colour			VARCHAR(32) DEFAULT '#CCFFCC',
   chart_fp_colour			VARCHAR(32) DEFAULT '#C4CA73',
   chart_fn_colour			VARCHAR(32) DEFAULT '#FF7575',
   chart_suspected_ham_colour		VARCHAR(32) DEFAULT '#FFFFB7',
   chart_suspected_spam_colour		VARCHAR(32) DEFAULT '#FFCCCC',
   chart_wl_colour			VARCHAR(32) DEFAULT '#eeeeee',
   chart_bl_colour			VARCHAR(32) DEFAULT '#888888',
   chart_background_colour		VARCHAR(32) DEFAULT '#B0ECFF',
   chart_font_colour			VARCHAR(32) DEFAULT '#3D3D50',
   chart_autogeneration_interval	INTEGER DEFAULT '60', -- minutes
   banner_title				VARCHAR(255) DEFAULT 'Maia Mailguard',
   use_icons				CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
   use_logo				CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
   logo_url				VARCHAR(255) DEFAULT 'http://www.maiamailguard.com/',
   logo_file				VARCHAR(255) DEFAULT 'images/maia-logotoolbar.gif',
   logo_alt_text			VARCHAR(255) DEFAULT 'Maia Mailguard Home Page',
   virus_info_url			VARCHAR(255) DEFAULT 'http://www.google.com/search?q=%%VIRUSNAME%%+virus+information',
   virus_lookup				VARCHAR(20) DEFAULT 'google',
   primary_report_server		VARCHAR(255) DEFAULT 'maia.renaissoft.com',
   primary_report_port			INTEGER DEFAULT '443',
   secondary_report_server		VARCHAR(255),
   secondary_report_port		INTEGER DEFAULT '443',
   reporter_sitename			VARCHAR(255),
   reporter_username			VARCHAR(50),
   reporter_password			VARCHAR(50),
   size_limit				INTEGER DEFAULT '1000000',
   oversize_policy			CHAR(1) DEFAULT 'B' NOT NULL, -- 'P', 'B'
   sa_score_set				INTEGER DEFAULT '0' NOT NULL,
   key_file				VARCHAR(255) DEFAULT 'blowfish.key'
);


-- [maia_languages] contains a list of the installed languages
-- and their ISO-639 two-letter abbreviations.

CREATE TABLE maia_languages (
   id			SERIAL PRIMARY KEY,
   language_name	VARCHAR(100) NOT NULL, -- e.g. 'English'
   abbreviation		CHAR(2) NOT NULL UNIQUE, -- e.g. 'en'
   installed		CHAR(1) DEFAULT 'N' NOT NULL
);
CREATE UNIQUE INDEX maia_languages_idx_abbreviation ON maia_languages(abbreviation);


-- [maia_users] contains mail filter settings that apply to
-- e-mail recipients who have registered with Maia.  The
-- user_level is stored as one of (U)ser, (A)dministrator,
-- or (S)uper-Administrator.

CREATE TABLE maia_users (
   id				SERIAL PRIMARY KEY,
   user_name			VARCHAR(255) NOT NULL UNIQUE,
   user_level			CHAR(1) DEFAULT 'U' NOT NULL, -- 'U', 'A', 'S'
   reminders            	CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
   charts               	CHAR(1) DEFAULT 'N' NOT NULL, -- 'Y', 'N'
   primary_email_id		INTEGER DEFAULT '0' NOT NULL, -- users.id
   language			VARCHAR(10) DEFAULT 'en' NOT NULL,
   charset			VARCHAR(20) DEFAULT 'ISO-8859-1' NOT NULL,
   spamtrap			CHAR(1) DEFAULT 'N' NOT NULL, -- 'Y', 'N'
   password			VARCHAR(32), -- 32-byte MD5 hash
   auto_whitelist		CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
   items_per_page		INTEGER DEFAULT '50' NOT NULL,
   spam_quarantine_sort		CHAR(2) DEFAULT 'XA' NOT NULL, -- [XDFS][AD]
   virus_quarantine_sort	CHAR(2) DEFAULT 'DA' NOT NULL, -- [DFS][AD]
   header_quarantine_sort	CHAR(2) DEFAULT 'DA' NOT NULL, -- [DFS][AD]
   attachment_quarantine_sort	CHAR(2) DEFAULT 'DA' NOT NULL, -- [DFS][AD]
   ham_cache_sort		CHAR(2) DEFAULT 'XD' NOT NULL, -- [XDFS][AD]
   discard_ham			CHAR(1) DEFAULT 'N' NOT NULL, -- 'Y', 'N'
   theme_id			INTEGER DEFAULT '1' NOT NULL,
   quarantine_digest_interval  INTEGER  DEFAULT '0' NOT NULL, 
   last_digest_sent   TIMESTAMP,
   truncate_subject  INTEGER  DEFAULT '20' NOT NULL, 
   truncate_email  INTEGER  DEFAULT '20' NOT NULL 
);
CREATE UNIQUE INDEX maia_users_idx_user_name ON maia_users(user_name);
CREATE UNIQUE INDEX primary_email_idx ON maia_users(primary_email_id);


-- [maia_domains] contains mail filter settings that apply to
-- entire e-mail domains as defaults for users who are not
-- registered with Maia.

CREATE TABLE maia_domains (
   id				SERIAL PRIMARY KEY,
   domain			VARCHAR(255) NOT NULL UNIQUE,
   reminders			CHAR(1) DEFAULT 'N' NOT NULL, -- 'Y', 'N'
   charts			CHAR(1) DEFAULT 'N' NOT NULL, -- 'Y', 'N'
   enable_user_autocreation	CHAR(1) DEFAULT 'Y' NOT NULL, -- 'Y', 'N'
   language			VARCHAR(10) DEFAULT 'en' NOT NULL,
   charset			VARCHAR(20) DEFAULT 'ISO-8859-1' NOT NULL,
   routing_domain               VARCHAR(255) DEFAULT '' NOT NULL,
   transport                    VARCHAR(255) DEFAULT ':' NOT NULL
);
CREATE UNIQUE INDEX maia_domains_idx_domain ON maia_domains(domain);
CREATE INDEX maia_domains_idx_routing_domain ON maia_domains(routing_domain);


-- [maia_domain_admins] is a one-to-many mapping of domains to
-- users with administrator privileges (domains can have
-- multiple administrators).

CREATE TABLE maia_domain_admins (
   domain_id		INTEGER NOT NULL, -- maia_domains.id
   admin_id		INTEGER NOT NULL, -- maia_users.id
   PRIMARY KEY (domain_id, admin_id)
);


-- [maia_stats] is a table of per-user statistics that keeps
-- track of the total number of mail items of each type,
-- along with total sizes and superlatives (e.g. largest,
-- smallest, oldest, newest, etc.).

CREATE TABLE maia_stats (
   user_id		INTEGER DEFAULT '0' NOT NULL, -- maia_users.id

   -- suspected ham
   oldest_suspected_ham_date	TIMESTAMP,
   newest_suspected_ham_date	TIMESTAMP,
   smallest_suspected_ham_size	INTEGER DEFAULT '0' NOT NULL,
   largest_suspected_ham_size	INTEGER DEFAULT '0' NOT NULL,
   total_suspected_ham_size	BIGINT DEFAULT '0' NOT NULL,
   lowest_suspected_ham_score	NUMERIC(7,3) DEFAULT '0.0' NOT NULL,
   highest_suspected_ham_score	NUMERIC(7,3) DEFAULT '0.0' NOT NULL,
   total_suspected_ham_score	NUMERIC(14,3) DEFAULT '0.0' NOT NULL,
   total_suspected_ham_items	INTEGER DEFAULT '0' NOT NULL,

   -- ham
   oldest_ham_date	TIMESTAMP,
   newest_ham_date	TIMESTAMP,
   smallest_ham_size	INTEGER DEFAULT '0' NOT NULL,
   largest_ham_size	INTEGER DEFAULT '0' NOT NULL,
   total_ham_size	BIGINT DEFAULT '0' NOT NULL,
   lowest_ham_score	NUMERIC(7,3) DEFAULT '0.0' NOT NULL,
   highest_ham_score	NUMERIC(7,3) DEFAULT '0.0' NOT NULL,
   total_ham_score	NUMERIC(14,3) DEFAULT '0.0' NOT NULL,
   total_ham_items	INTEGER DEFAULT '0' NOT NULL,

   -- mail from whitelisted senders
   oldest_wl_date	TIMESTAMP,
   newest_wl_date	TIMESTAMP,
   smallest_wl_size	INTEGER DEFAULT '0' NOT NULL,
   largest_wl_size	INTEGER DEFAULT '0' NOT NULL,
   total_wl_size	BIGINT DEFAULT '0' NOT NULL,
   total_wl_items	INTEGER DEFAULT '0' NOT NULL,

   -- mail from blacklisted senders
   oldest_bl_date	TIMESTAMP,
   newest_bl_date	TIMESTAMP,
   smallest_bl_size	INTEGER DEFAULT '0' NOT NULL,
   largest_bl_size	INTEGER DEFAULT '0' NOT NULL,
   total_bl_size	BIGINT DEFAULT '0' NOT NULL,
   total_bl_items	INTEGER DEFAULT '0' NOT NULL,

   -- suspected spam
   oldest_suspected_spam_date	TIMESTAMP,
   newest_suspected_spam_date	TIMESTAMP,
   smallest_suspected_spam_size	INTEGER DEFAULT '0' NOT NULL,
   largest_suspected_spam_size	INTEGER DEFAULT '0' NOT NULL,
   total_suspected_spam_size	BIGINT DEFAULT '0' NOT NULL,
   lowest_suspected_spam_score	NUMERIC(7,3) DEFAULT '0.0' NOT NULL,
   highest_suspected_spam_score	NUMERIC(7,3) DEFAULT '0.0' NOT NULL,
   total_suspected_spam_score	NUMERIC(14,3) DEFAULT '0.0' NOT NULL,
   total_suspected_spam_items	INTEGER DEFAULT '0' NOT NULL,

   -- false positives (i.e. rescued ham)
   oldest_fp_date	TIMESTAMP,
   newest_fp_date	TIMESTAMP,
   smallest_fp_size	INTEGER DEFAULT '0' NOT NULL,
   largest_fp_size	INTEGER DEFAULT '0' NOT NULL,
   total_fp_size	BIGINT DEFAULT '0' NOT NULL,
   lowest_fp_score	NUMERIC(7,3) DEFAULT '0.0' NOT NULL,
   highest_fp_score	NUMERIC(7,3) DEFAULT '0.0' NOT NULL,
   total_fp_score	NUMERIC(14,3) DEFAULT '0.0' NOT NULL,
   total_fp_items	INTEGER DEFAULT '0' NOT NULL,

   -- false negatives (i.e. reported spam)
   oldest_fn_date	TIMESTAMP,
   newest_fn_date	TIMESTAMP,
   smallest_fn_size	INTEGER DEFAULT '0' NOT NULL,
   largest_fn_size	INTEGER DEFAULT '0' NOT NULL,
   total_fn_size	BIGINT DEFAULT '0' NOT NULL,
   lowest_fn_score	NUMERIC(7,3) DEFAULT '0.0' NOT NULL,
   highest_fn_score	NUMERIC(7,3) DEFAULT '0.0' NOT NULL,
   total_fn_score	NUMERIC(14,3) DEFAULT '0.0' NOT NULL,
   total_fn_items	INTEGER DEFAULT '0' NOT NULL,

   -- confirmed spam
   oldest_spam_date	TIMESTAMP,
   newest_spam_date	TIMESTAMP,
   smallest_spam_size	INTEGER DEFAULT '0' NOT NULL,
   largest_spam_size	INTEGER DEFAULT '0' NOT NULL,
   total_spam_size	BIGINT DEFAULT '0' NOT NULL,
   lowest_spam_score	NUMERIC(7,3) DEFAULT '0.0' NOT NULL,
   highest_spam_score	NUMERIC(7,3) DEFAULT '0.0' NOT NULL,
   total_spam_score	NUMERIC(14,3) DEFAULT '0.0' NOT NULL,
   total_spam_items	INTEGER DEFAULT '0' NOT NULL,

   -- viruses
   oldest_virus_date	TIMESTAMP,
   newest_virus_date	TIMESTAMP,
   smallest_virus_size	INTEGER DEFAULT '0' NOT NULL,
   largest_virus_size	INTEGER DEFAULT '0' NOT NULL,
   total_virus_size	BIGINT DEFAULT '0' NOT NULL,
   total_virus_items	INTEGER DEFAULT '0' NOT NULL,

   -- mail with invalid headers
   oldest_bad_header_date	TIMESTAMP,
   newest_bad_header_date	TIMESTAMP,
   smallest_bad_header_size	INTEGER DEFAULT '0' NOT NULL,
   largest_bad_header_size	INTEGER DEFAULT '0' NOT NULL,
   total_bad_header_size	BIGINT DEFAULT '0' NOT NULL,
   total_bad_header_items	INTEGER DEFAULT '0' NOT NULL,

   -- mail containing banned file attachments
   oldest_banned_file_date	TIMESTAMP,
   newest_banned_file_date	TIMESTAMP,
   smallest_banned_file_size	INTEGER DEFAULT '0' NOT NULL,
   largest_banned_file_size	INTEGER DEFAULT '0' NOT NULL,
   total_banned_file_size	BIGINT DEFAULT '0' NOT NULL,
   total_banned_file_items	INTEGER DEFAULT '0' NOT NULL,

   -- oversized items
   oldest_oversized_date	TIMESTAMP,
   newest_oversized_date	TIMESTAMP,
   smallest_oversized_size	INTEGER DEFAULT '0' NOT NULL,
   largest_oversized_size	INTEGER DEFAULT '0' NOT NULL,
   total_oversized_size		BIGINT DEFAULT '0' NOT NULL,
   total_oversized_items	INTEGER DEFAULT '0' NOT NULL,

   PRIMARY KEY (user_id)
);


-- [maia_mail] stores mail items of five types:
-- Suspected (S)pam, (V)iruses, Banned (F)ile Attachments,
-- (B)ad Headers, and Suspected (H)am.

CREATE TABLE maia_mail (
   id               SERIAL PRIMARY KEY,
   received_date    TIMESTAMP NOT NULL,
   size             INTEGER NOT NULL,
   sender_email     BYTEA NOT NULL,
   envelope_to      BYTEA NOT NULL,
   subject          VARCHAR(255) NOT NULL,
   contents         BYTEA NOT NULL,
   score            NUMERIC(7,3), -- only supplied for (S)pam
   autolearn_status VARCHAR(15) DEFAULT 'unavailable' NOT NULL -- 'ham', 'spam', 'no', 'disabled', 'failed', 'unavailable'
);
CREATE INDEX maia_mail_idx_received_date ON maia_mail(received_date);
CREATE INDEX maia_mail_idx_sender_email ON maia_mail(sender_email);
CREATE INDEX maia_mail_idx_subject ON maia_mail(subject);
CREATE INDEX maia_mail_idx_score ON maia_mail(score);


-- [maia_mail_recipients] is a one-to-many mapping of
-- mail items to recipients.  These records
-- are deleted when a recipient rescues an item, or the
-- item is deleted.

CREATE TABLE maia_mail_recipients (
   mail_id		INTEGER NOT NULL, -- maia_mail.id
   recipient_id		INTEGER NOT NULL, -- maia_users.id
   type			CHAR(1) NOT NULL, -- 'S', 'V', 'F', 'B', 'H', 'L', 'W'
   token        CHAR(64) NOT NULL, 
   PRIMARY KEY (mail_id, recipient_id)
);
CREATE UNIQUE INDEX token_idx ON maia_mail_recipients(token);
CREATE INDEX maia_mail_recipients_idx_type ON maia_mail_recipients(type);
CREATE INDEX maia_mail_recipients_idx_mail_id ON maia_mail_recipients(mail_id);
CREATE INDEX maia_mail_recipients_idx_recipient_id ON maia_mail_recipients(recipient_id);


-- [maia_viruses] contains a list of the "official" names of
-- viruses that have been detected by the virus scanners.
-- These are the names that will be displayed in stats
-- tables and charts.

CREATE TABLE maia_viruses (
   id			SERIAL PRIMARY KEY,
   virus_name		VARCHAR(255) NOT NULL UNIQUE,
   count		INTEGER DEFAULT '0' NOT NULL
);
CREATE UNIQUE INDEX maia_viruses_idx_virus_name ON maia_viruses(virus_name);


-- [maia_virus_aliases] is a one-to-many mapping of viruses
-- to aliases for those viruses, as detected by other
-- virus scanners.

CREATE TABLE maia_virus_aliases (
   virus_id		INTEGER NOT NULL, -- maia_viruses.id
   virus_alias		VARCHAR(255) NOT NULL,
   PRIMARY KEY (virus_id, virus_alias)
);


-- [maia_viruses_detected] is a one-to-many mapping of
-- mail items to viruses found.  These
-- entries are deleted when a mail item is rescued
-- or deleted.

CREATE TABLE maia_viruses_detected (
   mail_id		INTEGER NOT NULL, -- maia_mail.id
   virus_id		INTEGER NOT NULL, -- maia_viruses.id
   PRIMARY KEY (mail_id, virus_id)
);


-- [maia_sa_rules] contains a list of all the SpamAssassin
-- rules installed on the system, along with their
-- text descriptions and score values.

CREATE TABLE maia_sa_rules (
   id			SERIAL PRIMARY KEY,
   rule_name		VARCHAR(255) NOT NULL UNIQUE,
   rule_description	VARCHAR(255) DEFAULT '' NOT NULL,
   rule_score_0		NUMERIC(7,3) DEFAULT '1.0' NOT NULL,
   rule_score_1		NUMERIC(7,3) DEFAULT '1.0' NOT NULL,
   rule_score_2		NUMERIC(7,3) DEFAULT '1.0' NOT NULL,
   rule_score_3		NUMERIC(7,3) DEFAULT '1.0' NOT NULL,
   rule_count		INTEGER DEFAULT '0' NOT NULL
);
CREATE UNIQUE INDEX maia_sa_rules_idx_rule_name ON maia_sa_rules(rule_name);


-- [maia_sa_rules_triggered] is a one-to-many mapping of
-- mail items to SpamAssassin rules triggered
-- by that mail item.  These entries are deleted when a
-- mail item is rescued or deleted.

CREATE TABLE maia_sa_rules_triggered (
   mail_id		INTEGER NOT NULL, -- maia_mail.id
   rule_id		INTEGER NOT NULL, -- maia_sa_rules.id
   rule_score		NUMERIC(7,3) DEFAULT '0.0' NOT NULL,
   PRIMARY KEY (mail_id, rule_id)
);


-- [maia_banned_attachments_found] is a one-to-many mapping of
-- mail items to file attachments that were found in that
-- mail item.  These entries are deleted when a mail item
-- is rescued or deleted.
CREATE TABLE maia_banned_attachments_found (
   mail_id		INTEGER NOT NULL, -- maia_mail.id
   file_name		VARCHAR(255) NOT NULL,
   file_type		VARCHAR(20) DEFAULT 'Unknown' NOT NULL,
   PRIMARY KEY (mail_id, file_name)
);


-- [maia_stats_history] is a table used to store snapshots of the
-- more relevant items from the [maia_stats] table at (H)ourly,
-- (D)aily, (M)onthly, and (Y)early intervals.  Entries are
-- automatically expired, so this table has a more or less
-- fixed size of ((24 + 31 + 12 + 1/year) * users) rows.
--
CREATE TABLE maia_stats_history (
   id				SERIAL PRIMARY KEY,
   user_id			INTEGER NOT NULL, -- maia_users.id
   type				CHAR(1) DEFAULT 'H' NOT NULL, -- 'H', 'D', 'M', 'Y'
   taken_at			TIMESTAMP NOT NULL,
   total_ham_items		INTEGER DEFAULT '0' NOT NULL,
   total_ham_size		BIGINT DEFAULT '0' NOT NULL,
   total_spam_items		INTEGER DEFAULT '0' NOT NULL,
   total_spam_size		BIGINT DEFAULT '0' NOT NULL,
   total_virus_items		INTEGER DEFAULT '0' NOT NULL,
   total_virus_size		BIGINT DEFAULT '0' NOT NULL,
   total_fp_items		INTEGER DEFAULT '0' NOT NULL,
   total_fp_size		BIGINT DEFAULT '0' NOT NULL,
   total_fn_items		INTEGER DEFAULT '0' NOT NULL,
   total_fn_size		BIGINT DEFAULT '0' NOT NULL,
   total_banned_file_items	INTEGER DEFAULT '0' NOT NULL,
   total_banned_file_size	BIGINT DEFAULT '0' NOT NULL,
   total_bad_header_items	INTEGER DEFAULT '0' NOT NULL,
   total_bad_header_size	BIGINT DEFAULT '0' NOT NULL,
   total_wl_items		INTEGER DEFAULT '0' NOT NULL,
   total_wl_size		BIGINT DEFAULT '0' NOT NULL,
   total_bl_items		INTEGER DEFAULT '0' NOT NULL,
   total_bl_size		BIGINT DEFAULT '0' NOT NULL,
   total_oversized_items	INTEGER DEFAULT '0' NOT NULL,
   total_oversized_size		BIGINT DEFAULT '0' NOT NULL
);


-- [maia_themes] stores information about each of the installed
-- user-selectable themes.
--
CREATE TABLE maia_themes (
  id        SERIAL PRIMARY KEY,
  name      VARCHAR(30) NOT NULL,
  path      VARCHAR(30) NOT NULL
);

-- maia_tokens stores temporary MD5 authentication tokens for various systems.
CREATE TABLE maia_tokens (
id            SERIAL PRIMARY KEY,
token_system  VARCHAR(32) NOT NULL ,
token         CHAR(64) NOT NULL ,
data          bytea NOT NULL ,
expires       TIMESTAMP NOT NULL
);
CREATE UNIQUE INDEX token_system_key ON maia_tokens (token,token_system);
CREATE INDEX token_system ON maia_tokens ( token_system );
CREATE INDEX expires ON maia_tokens ( expires );

-- schema_info contains the schema version status of the current database. Software upgrades will use this value
-- to upgrade the database appropriately.  Note: this table is constructed to adhere to the pattern set by
-- RubyOnRails' Migrations, for future compatability 
CREATE TABLE schema_info (
version INTEGER
);
INSERT INTO schema_info VALUES(15);

-- [awl] is SpamAssassin 3.x's Auto-WhiteList database
--
CREATE TABLE awl (
  username 	VARCHAR(100) NOT NULL DEFAULT '',
  email 	BYTEA NOT NULL DEFAULT '',
  ip 		VARCHAR(40) NOT NULL DEFAULT '',
  count 	BIGINT NOT NULL DEFAULT '0',
  totscore 	FLOAT NOT NULL DEFAULT '0',
  signedby  varchar(255) NOT NULL default '',
  lastupdate timestamp NOT NULL,
  PRIMARY KEY (username, email, signedby, ip)
);

CREATE FUNCTION awl_stamp() RETURNS trigger AS $awl_stamp$
   BEGIN
     NEW.lastupdate := current_timestamp;
     RETURN NEW;
   END;
 $awl_stamp$ LANGUAGE plpgsql;

CREATE TRIGGER awl_stamp BEFORE INSERT OR UPDATE ON awl
   FOR EACH ROW EXECUTE PROCEDURE awl_stamp();


-- [bayes_expire] is SpamAssassin 3.x's Bayes database expiry reference
--
CREATE TABLE bayes_expire (
  id 		INTEGER NOT NULL DEFAULT '0',
  runtime 	INTEGER NOT NULL DEFAULT '0'
) WITHOUT OIDS;
CREATE INDEX bayes_expire_idx1 ON bayes_expire (id);


-- [bayes_global_vars] is SpamAssassin 3.x's Bayes database global variable list
--
CREATE TABLE bayes_global_vars (
  variable 	VARCHAR(30) NOT NULL DEFAULT '',
  value 	VARCHAR(200) NOT NULL DEFAULT '',
  PRIMARY KEY (variable)
) WITHOUT OIDS;


-- [bayes_seen] is SpamAssassin 3.x's Bayes database token reference
--
CREATE TABLE bayes_seen (
  id 		INTEGER NOT NULL DEFAULT '0',
  msgid 	VARCHAR(200) NOT NULL DEFAULT '',
  flag 		CHAR(1) NOT NULL DEFAULT '',
  PRIMARY KEY (id, msgid)
) WITHOUT OIDS;


-- [bayes_token] is SpamAssassin 3.x's Bayes database token list
--
CREATE TABLE bayes_token (
  id 		INTEGER NOT NULL DEFAULT '0',
  token 	BYTEA NOT NULL DEFAULT '',
  spam_count 	INTEGER NOT NULL DEFAULT '0',
  ham_count 	INTEGER NOT NULL DEFAULT '0',
  atime 	INTEGER NOT NULL DEFAULT '0',
  PRIMARY KEY (id, token)
) WITHOUT OIDS;
CREATE INDEX bayes_token_idx1 ON bayes_token (token);


-- [bayes_vars] is SpamAssassin 3.x's Bayes database variable scorecard
--
CREATE TABLE bayes_vars (
  id 			SERIAL NOT NULL,
  username 		VARCHAR(200) NOT NULL DEFAULT '',
  spam_count 		INTEGER NOT NULL DEFAULT '0',
  ham_count 		INTEGER NOT NULL DEFAULT '0',
  token_count 		INTEGER NOT NULL DEFAULT '0',
  last_expire 		INTEGER NOT NULL DEFAULT '0',
  last_atime_delta 	INTEGER NOT NULL DEFAULT '0',
  last_expire_reduce 	INTEGER NOT NULL DEFAULT '0',
  oldest_token_age 	INTEGER NOT NULL DEFAULT '2147483647',
  newest_token_age 	INTEGER NOT NULL DEFAULT '0',
  PRIMARY KEY  (id)
) WITHOUT OIDS;
CREATE UNIQUE INDEX bayes_vars_idx1 ON bayes_vars (username);

CREATE OR REPLACE FUNCTION greatest_int (integer, integer)
 RETURNS INTEGER
 IMMUTABLE STRICT
 AS 'SELECT CASE WHEN $1 < $2 THEN $2 ELSE $1 END;'
 LANGUAGE SQL;

CREATE OR REPLACE FUNCTION least_int (integer, integer)
 RETURNS INTEGER
 IMMUTABLE STRICT
 AS 'SELECT CASE WHEN $1 < $2 THEN $1 ELSE $2 END;'
 LANGUAGE SQL;

CREATE OR REPLACE FUNCTION put_tokens(INTEGER,
                                      BYTEA[],
                                      INTEGER,
                                      INTEGER,
                                      INTEGER)
RETURNS VOID AS ' 
DECLARE
  inuserid      ALIAS FOR $1;
  intokenary    ALIAS FOR $2;
  inspam_count  ALIAS FOR $3;
  inham_count   ALIAS FOR $4;
  inatime       ALIAS FOR $5;
  _token BYTEA;
  new_tokens INTEGER := 0;
BEGIN
  for i in array_lower(intokenary, 1) .. array_upper(intokenary, 1)
  LOOP
    _token := intokenary[i];
    UPDATE bayes_token
       SET spam_count = greatest_int(spam_count + inspam_count, 0),
           ham_count = greatest_int(ham_count + inham_count, 0),
           atime = greatest_int(atime, inatime)
     WHERE id = inuserid 
       AND token = _token;
    IF NOT FOUND THEN 
      -- we do not insert negative counts, just return true
      IF NOT (inspam_count < 0 OR inham_count < 0) THEN
        INSERT INTO bayes_token (id, token, spam_count, ham_count, atime) 
        VALUES (inuserid, _token, inspam_count, inham_count, inatime); 
        IF FOUND THEN
          new_tokens := new_tokens + 1;
        END IF;
      END IF;
    END IF;
  END LOOP;

  IF new_tokens > 0 AND inatime > 0 THEN
    UPDATE bayes_vars
       SET token_count = token_count + new_tokens,
           newest_token_age = greatest_int(newest_token_age, inatime),
           oldest_token_age = least_int(oldest_token_age, inatime)
     WHERE id = inuserid;
  ELSIF new_tokens > 0 AND NOT inatime > 0 THEN
    UPDATE bayes_vars
       SET token_count = token_count + new_tokens
     WHERE id = inuserid;
  ELSIF NOT new_tokens > 0 AND inatime > 0 THEN
    UPDATE bayes_vars
       SET newest_token_age = greatest_int(newest_token_age, inatime),
           oldest_token_age = least_int(oldest_token_age, inatime)
     WHERE id = inuserid;
  END IF;
  RETURN;
END; 
' LANGUAGE 'plpgsql'; 


-- Database initialization script

-- Create a permissive system default policy and create the '@.' domain

INSERT INTO policy VALUES (DEFAULT, 'Default', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'N', 'N', 'N', NULL, 999, 999, 999);
INSERT INTO users VALUES (DEFAULT, 0, 1, '@.', 1, 1);
INSERT INTO maia_users (id, user_name, primary_email_id, reminders, discard_ham) VALUES (DEFAULT, '@.', 1, 'N', 'Y');
INSERT INTO maia_domains (id, domain, routing_domain) VALUES (DEFAULT, '@.', '*');

-- Instantiate a default system configuration

INSERT INTO maia_config (id) VALUES (0);

-- Load the ISO-639 language names and abbreviations

INSERT INTO maia_languages (abbreviation, language_name) VALUES ('aa', 'Afar');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ab', 'Abkhazian');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('af', 'Afrikaans');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('am', 'Amharic');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ar', '&#xFE94;&#xFEF4;&#xFE90;&#xFEAE;&#xFECC;&#xFEE0;&#xFE8D;&#x0020;');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('as', 'Assamese');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ay', 'Aymara');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('az', 'Az&#x0446;ri');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ba', 'Bashkir');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('be', '&#x0411;&#x0435;&#x043B;&#x0430;&#x0440;&#x0443;&#x0441;&#x043A;&#x0430;&#x044F;');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('bg', '&#x0411;&#x044A;&#x043B;&#x0433;&#x0430;&#x0440;&#x0441;&#x043A;&#x0438;');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('bh', 'Bihari');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('bi', 'Bislama');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('bn', 'Bengali');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('bo', 'Tibetan');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('br', 'Brezhoneg');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ca', 'Català');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('co', 'Corsican');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('cs', '&#x010C;etina');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('cy', 'Cymraeg');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('da', 'Dansk');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('de', 'Deutsch');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('dz', 'Bhutani');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('el', '&#x0395;&#x03BB;&#x03BB;&#x03B7;&#x03BD;&#x03B9;&#x03BA;&#x03AC;');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('en', 'English');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('eo', 'Esperanto');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('es', 'Español');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('et', 'Eesti');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('eu', 'Euskaraz');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('fa', 'Persian');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('fi', 'Suomeksi');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('fj', 'Fiji');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('fo', 'Føroyskt');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('fr', 'Fran&ccedil;ais');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('fy', 'Frysk');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ga', 'Gaeilge');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('gd', 'Gàidhlig');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('gl', 'Galician');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('gn', 'Guarani');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('gu', 'Gujarati');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('gv', 'Ghaelg');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ha', 'Hausa');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('he', '&#x05E2;&#x05D1;&#x05E8;&#x05D9;&#x05EA;');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('hi', 'Hindi');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('hr', 'Hrvatski');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('hu', 'Magyar');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('hy', 'Armenian');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ia', 'Interlingua');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('id', 'Bahasa Indonesia');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ie', 'Interlingue');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ik', 'Inupiak');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('is', 'Íslenska');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('it', 'Italiano');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('iu', 'Inuktitut');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ja', 'Nihongo');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('jw', 'Javanese');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ka', 'Georgian');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('kk', 'Kazakh');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('kl', 'Kalaallísut');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('km', 'Cambodian');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('kn', 'Kannada');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ko', 'Korean');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ks', 'Kashmiri');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ku', 'Kurmancî (Kurdî)');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ky', 'Kirghiz');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('la', 'Latina');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('lb', 'Lëtzebuergesch');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ln', 'Lingala');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('lo', 'Laothian');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('lt', 'Lietuvi&#x0173;');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('lv', 'Latvieu');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('mg', 'Malagasy');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('mi', 'Maori');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('mk', '&#x041C;&#x0430;&#x043A;&#x0435;&#x0434;&#x043E;&#x043D;&#x0441;&#x043A;&#x0438;');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ml', 'Malayalam');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('mn', 'Mongolian');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('mo', 'Moldavian');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('mr', 'Marathi');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ms', 'Malay');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('mt', 'Malti');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('my', 'Burmese');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('na', 'Nauru');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ne', 'Nepali');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('nl', 'Nederlands');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('no', 'Norsk');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('oc', 'Occitan');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('om', 'Oromo');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('or', 'Oriya');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('pa', 'Punjabi');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('pl', 'Polski');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ps', 'Pashto');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('pt', 'Português');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('qu', 'Quechua');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('rm', 'Rumantsch');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('rn', 'Kirundi');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ro', 'Rom&#x00E2;&#x006E;&#x0103;');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ru', '&#x0420;&#x0443;&#x0441;&#x0441;&#x043A;&#x0438;&#x0439;');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('rw', 'Kinyarwanda');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sa', 'Sanskrit');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sd', 'Sindhi');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('se', 'Davvisámegiella');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sg', 'Sangho');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sh', 'Serbo-Croatian');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('si', 'Sinhalese');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sk', 'Sloven&#x010D;ina');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sl', 'Slovenski');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sm', 'Samoan');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sn', 'Shona');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('so', 'af Soomaali');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sq', 'Shqip');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sr', '&#x0421;&#x0440;&#x043F;&#x0441;&#x043A;&#x0438;');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ss', 'Siswati');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('st', 'Sesotho');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('su', 'Sundanese');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sv', 'Svenska');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('sw', 'Kiswahili');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ta', 'Tamil');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('te', 'Telugu');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('tg', 'Tajik');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('th', 'Thai');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ti', 'Tigrinya');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('tk', 'Turkmen');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('tl', 'Tagalog');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('tn', 'Setswana');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('to', 'Tonga');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('tr', 'Türk&ccedil;e');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ts', 'Tsonga');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('tt', 'Tatar');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('tw', 'Twi');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ug', 'Uighur');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('uk', '&#x0423;&#x043A;&#x0440;&#x0430;&#x00EF;&#x043D;&#x0441;&#x044C;&#x043A;&#x0430;');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('ur', 'Urdu');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('uz', 'Uzbek');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('vi', '&#x0054;&#x0069;&#x1EBF;&#x006E;&#x0067;&#x0020;&#x0056;&#x0069;&#x1EC7;&#x0074;');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('vo', 'Volapuk');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('wo', 'Wolof');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('xh', 'Xhosa');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('yi', 'Jiddi&#x0161;');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('yo', 'Yoruba');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('za', 'Zhuang');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('zh', 'Zh&#x014D;ng-wén');
INSERT INTO maia_languages (abbreviation, language_name) VALUES ('zu', 'Zulu');

-- Mark the default language (English) as "installed"

UPDATE maia_languages SET installed = 'Y' WHERE abbreviation = 'en';

-- Insert default themes
                                                                                                                                               
INSERT INTO maia_themes VALUES (1, 'Ocean Surf', 'ocean_surf'); 
INSERT INTO maia_themes VALUES (2, 'Desert Sand', 'desert_sand'); 
-- delaying this theme till 1.1
--INSERT INTO maia_themes VALUES (3, 'DGM', 'dgm');

-- Set SpamAssassin global variables

INSERT INTO bayes_global_vars VALUES ('VERSION', '3');

-- End of database initialization script

