create domain type_account_name as varchar(128);
create domain type_user_name as varchar(20);
create domain type_email as varchar(128);
create domain type_phone as varchar(20);
create domain type_street_address as varchar(128);
create domain type_city as varchar(30);
create domain type_country as varchar(50);
create domain type_zip_code as varchar(20);
create domain type_addressbook_name as varchar(20);
create domain type_url as varchar(128);

create table domains
(
 id serial unique,
 name type_account_name not null primary key unique,
 enable boolean default true,
 allow_signup boolean default false,
 reserved_usernames varchar[] not null default array['root','anonymous','nobody','mail','mailer-daemon', 'daemon'],
 new_account_defaults varchar default null
);

create table state (
        uid smallint,
        name varchar,
        value varchar,
        unique(uid, name)
);

create table users
(
	account type_account_name not null primary key,
	domain_id smallint not null references domains(id) on delete cascade,
	user_dir varchar(255) not null,
	first_name type_user_name,
	middle_name type_user_name,
	last_name type_user_name,
    sex char not null default 'M',
	title varchar(20),
	initials varchar(20),
	birthday date default null,
	street_address type_street_address,
	zip_code type_zip_code,
	city type_city,
	state type_city,
	country type_country,
	email type_email,
	email2 type_email,
	email3 type_email,
	im type_email,
	im2 type_email,
	im3 type_email,
	phone type_phone,
	fax type_phone,
	mobile type_phone,
	phone_work type_phone,
	fax_work type_phone,
	mobile_work type_phone,
	company varchar,
	department varchar,
	work_function varchar,
	mailbox_quota integer default 1048576, --1G
	files_quota integer default 1048576,  --1G
	uid serial unique,
	ctime timestamp not null default now(),
	mtime timestamp not null default now(),
	status smallint not null default 2 -- 0 disabled, 1 -- enabled, 2-signup
);

create table permissions (
	id serial primary key,
	uid smallint references users(uid) on delete cascade,
	kind varchar not null,
	target varchar not null,
	acl_read smallint[],
	acl_write smallint[],
	unique(kind,target)
);

create table todo (
        uid smallint references users(uid) on delete cascade,
        id serial primary key,
        name varchar not null,
        completed boolean not null default false,
        due_time timestamp default null,
        reminder_multipiler integer
);


create table user_session
(
 	account type_account_name not null primary key references users(account) on delete cascade,
	password varchar(64) default null,
	password_md5 varchar(64) default null,
	password_sha1 varchar(256) default null,
	password_sha256 varchar default null,
	session_id varchar(256) default null,
	last_login timestamp default null,
	session_data text default null
);

create table preferences (
	uid smallint not null primary key references users(uid) on delete cascade,
	real_name type_user_name default null,
	language varchar(6) not null default 'en_us',
	forward type_account_name default null,
	auto_reply boolean not null default false,
	mtime timestamp not null default now()
);

create table notes (
	uid smallint not null primary key references users(uid) on delete cascade,
	note text,
	mtime timestamp not null default now()
);

create table feeds (
	id serial primary key,
	uid smallint not null references users(uid) on delete cascade,
	url varchar(256) not null,
	mtime timestamp not null default now(),
	unique(uid,url)
);

create table addressbook (
	id serial primary key,
    name type_addressbook_name,
	is_default boolean default false,
	uid smallint references users(uid) ON DELETE CASCADE,
	mtime timestamp not null default now(),
    unique(name, uid)
);


create table addressbook_company (
        id serial primary key,
		ab_id integer not null references addressbook(id) on delete cascade,
		uid smallint references users(uid) on DELETE CASCADE,
        name varchar(80) not null,
        phone type_phone,
        fax type_phone,
        email type_email,
        homepage type_url,
        bank_number varchar(128),
        vat_number varchar(128),
        visit_street_address type_street_address,
        visit_zip_code type_zip_code,
        visit_city type_city,
        visit_state type_city,
        visit_country type_country,
        post_street_address type_street_address,
        post_zip_code type_zip_code,
        post_city type_city,
        post_state type_city,
        post_country type_country,
        comments text,
        ctime timestamp not null default now(),
        mtime timestamp default now(),
        unique (uid, name)
);

CREATE TABLE addressbook_contact (
        id serial primary key,
		ab_id integer not null references addressbook(id) on delete cascade,
		uid smallint not null references users(uid) ON DELETE CASCADE,
        first_name type_user_name,
        middle_name type_user_name,
        last_name type_user_name,
        title varchar(20),
        initials varchar(20),
        sex char not null default 'M',
        salutation varchar,
        birthday date,
        street_address type_street_address,
        zip_code type_zip_code,
        city type_city,
        state type_city,
        country type_country,
        email type_email,
        email2 type_email,
        email3 type_email,
        im type_email,
        im2 type_email,
        im3 type_email,
        phone type_phone,
        fax type_phone,
        mobile type_phone,
        phone_work type_phone,
        fax_work type_phone,
        mobile_work type_phone,
        company varchar,
        department varchar,
        work_function varchar,
        comments text,
		ctime timestamp not null default now(),
		mtime timestamp not null default now()
);

create table spam_settings (
	uid smallint not null references users(uid) on delete cascade,
	preference varchar(30) default '' not null,
	value varchar(100) default '' not null,
	id serial primary key,
	unique(uid, preference, value)
);

create table dspam_virtual_uids (
	uid serial primary key,
	username varchar(128) references users(account) on delete cascade
) WITHOUT OIDS;

create unique index id_virtual_uids_01 on dspam_virtual_uids(username);
create unique index id_virtual_uids_02 on dspam_virtual_uids(uid);

CREATE TABLE dspam_token_data (
	uid smallint references dspam_virtual_uids(uid) on delete cascade,
	token bigint,
	spam_hits int,
	innocent_hits int,
	last_hit date,
	UNIQUE (uid, token)
) WITHOUT OIDS;
 
CREATE TABLE dspam_signature_data (
	uid smallint references dspam_virtual_uids(uid) on delete cascade,
   	signature varchar(128),
	data bytea,
	length int,
	created_on date,
	UNIQUE (uid, signature)
) WITHOUT OIDS;

CREATE TABLE dspam_stats (
	uid smallint PRIMARY KEY references dspam_virtual_uids(uid) on delete cascade,
	spam_learned int,
	innocent_learned int,
	spam_misclassified int,
	innocent_misclassified int,
	spam_corpusfed int,
	innocent_corpusfed int,
	spam_classified int,
	innocent_classified int
) WITHOUT OIDS;
 
CREATE TABLE dspam_preferences (
	uid smallint references dspam_virtual_uids(uid) on delete cascade,
	preference varchar(128),
	value varchar(128),
	UNIQUE (uid, preference)
) WITHOUT OIDS;

create function lookup_tokens(integer,bigint[]) returns setof dspam_token_data as $$
declare
	v_rec record;
begin
	for v_rec in select * from dspam_token_data
		where uid=$1 and token in (select $2[i] from generate_series(array_lower($2,1),
           array_upper($2,1)) s(i))
	loop
		return next v_rec;
	end loop;
return;
end;
$$ language plpgsql;
 
CREATE INDEX id_token_data_sumhits ON dspam_token_data ((spam_hits + innocent_hits));
