CREATE user eximtest with password 'riesahdoov';
CREATE DATABASE eximtest with owner eximtest;
\connect eximtest eximtest
CREATE TABLE domains (
	id       BIGSERIAL,
	fullname TEXT NOT NULL DEFAULT '' UNIQUE,
	PRIMARY KEY (id)
);
CREATE UNIQUE INDEX ui_domains_fullname ON domains (fullname);

CREATE OR REPLACE FUNCTION cleanup_domains() RETURNS TRIGGER AS
$BODY$
DECLARE
BEGIN
    NEW.fullname := trim(both FROM lower(NEW.fullname));
    RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER cleanup_domains BEFORE INSERT OR UPDATE ON domains FOR EACH ROW EXECUTE PROCEDURE cleanup_domains();

CREATE OR REPLACE FUNCTION is_local_domain(in_domain TEXT) RETURNS TEXT as $BODY$
DECLARE
    tempint INT4;
BEGIN
    SELECT id INTO tempint FROM domains WHERE fullname = trim(both FROM lower(in_domain));
    IF NOT FOUND THEN
        RETURN NULL;
    END IF;
    RETURN in_domain;
END;
$BODY$ LANGUAGE plpgsql;

CREATE TABLE aliases (
	id          BIGSERIAL,
	domain_id   INT4 NOT NULL DEFAULT 0 references domains (id),
	username    TEXT NOT NULL DEFAULT '',
	destination TEXT NOT NULL DEFAULT '',
	PRIMARY KEY (id)
);

CREATE OR REPLACE FUNCTION cleanup_aliases() RETURNS TRIGGER AS
$BODY$
DECLARE
BEGIN
    NEW.username := trim(both FROM lower(NEW.username));
    NEW.destination := trim(both FROM lower(NEW.destination));
    RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER cleanup_aliases BEFORE INSERT OR UPDATE ON aliases FOR EACH ROW EXECUTE PROCEDURE cleanup_aliases();

CREATE OR REPLACE FUNCTION get_alias_destinations(in_username TEXT, in_domain TEXT) RETURNS SETOF TEXT AS '
DECLARE
    use_username text := trim(both FROM lower(in_username));
    use_domain   text := trim(both FROM lower(in_domain));
    temprec      record;
BEGIN
    FOR temprec in
        SELECT a.*
        FROM aliases a JOIN domains d ON a.domain_id = d.id
        WHERE a.username = use_username AND d.fullname = use_domain
    LOOP
        RETURN NEXT temprec.destination;
    END LOOP;
    RETURN;
END;
' LANGUAGE 'plpgsql';

CREATE TABLE setup (
	id    SERIAL,
	param TEXT NOT NULL DEFAULT '' UNIQUE,
	value TEXT NOT NULL DEFAULT '',
	PRIMARY KEY (id)
);

CREATE OR REPLACE FUNCTION setup_set(in_param TEXT, in_value TEXT) RETURNS BOOL AS '
DECLARE
BEGIN
    LOOP
        UPDATE setup SET value = in_value WHERE param = in_param;
        IF found THEN
            RETURN true;
        END IF;
        BEGIN
            INSERT INTO setup(param, value) VALUES (in_param, in_value);
            RETURN false;
        EXCEPTION WHEN unique_violation THEN
        END;
    END LOOP;
END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION setup_get(in_param TEXT) RETURNS TEXT AS '
DECLARE
    reply text;
BEGIN
    select value into reply from setup where param = in_param;
    return reply;
END;
' LANGUAGE 'plpgsql';

CREATE TABLE accounts (
	id        BIGSERIAL,
	username  TEXT NOT NULL DEFAULT '',
	domain_id INT4 NOT NULL DEFAULT 0 references domains (id),
	password  TEXT NOT NULL DEFAULT '',
	PRIMARY KEY (id)
);
CREATE UNIQUE INDEX ui_accounts_udi ON accounts (username, domain_id);

\connect - postgres
\i /usr/share/postgresql/8.2/contrib/pgcrypto.sql
\connect - eximtest


CREATE OR REPLACE FUNCTION cleanup_and_crypt_accounts() RETURNS TRIGGER AS
$BODY$
DECLARE
BEGIN
    NEW.username := trim(both FROM lower(NEW.username));
    IF substr(NEW.password, 1, 3) <> '$1$' THEN
        NEW.password := crypt( NEW.password, gen_salt('md5') );
    END IF;
    RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER cleanup_and_crypt_accounts BEFORE INSERT OR UPDATE ON accounts FOR EACH ROW EXECUTE PROCEDURE cleanup_and_crypt_accounts();


CREATE OR REPLACE FUNCTION is_local_user(in_username TEXT, in_domain TEXT) RETURNS TEXT as $BODY$
DECLARE
    use_username text := trim(both FROM lower(in_username));
    use_domain   text := trim(both FROM lower(in_domain));
    tempint INT4;
BEGIN
    SELECT a.id INTO tempint FROM accounts a join domains d on a.domain_id = d.id WHERE d.fullname = use_domain AND a.username = use_username;
    IF NOT FOUND THEN
        RETURN NULL;
    END IF;
    RETURN in_username || '@' || in_domain;
END;
$BODY$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION get_account_maildir(in_username TEXT, in_domain TEXT) RETURNS TEXT as $BODY$
DECLARE
    use_username text := trim(both FROM lower(in_username));
    use_domain   text := trim(both FROM lower(in_domain));
    temptext TEXT;
BEGIN
    temptext := is_local_user(in_username, in_domain);
    IF temptext IS NULL THEN
        RETURN NULL;
    END IF;
    RETURN setup_get('mails_top_dir') || '/' || use_domain || '/' || use_username || '/maildir';
END;
$BODY$ LANGUAGE plpgsql;


CREATE TABLE customers (
	id       SERIAL,
	codename TEXT NOT NULL DEFAULT '' UNIQUE,
	quota_mb INT4 NOT NULL DEFAULT 0,
	PRIMARY KEY (id)
);

ALTER TABLE domains add column quota INT4 NOT NULL DEFAULT 0;
ALTER TABLE domains add column customer_id INT4 references customers (id); 
INSERT INTO customers (codename) VALUES ('DEFAULT');
UPDATE domains SET customer_id = currval('customers_id_seq');
ALTER TABLE domains ALTER column customer_id SET NOT NULL;

ALTER TABLE accounts add column quota_mb INT4 NOT NULL DEFAULT 0;

CREATE OR REPLACE FUNCTION get_account_quota(in_username TEXT, in_domain TEXT) RETURNS TEXT as $BODY$
DECLARE
    use_username text := trim(both FROM lower(in_username));
    use_domain   text := trim(both FROM lower(in_domain));
    reply TEXT;
BEGIN
    SELECT a.quota_mb::TEXT || 'M' INTO reply FROM accounts a join domains d on a.domain_id = d.id WHERE d.fullname = use_domain AND a.username = use_username;
    IF NOT FOUND THEN
        RETURN NULL;
    END IF;
    RETURN reply;
END;
$BODY$ LANGUAGE plpgsql;

ALTER TABLE customers add column system_gid INT4;
ALTER TABLE domains add column system_uid INT4;

CREATE OR REPLACE FUNCTION set_system_gid() RETURNS TRIGGER AS
$BODY$
DECLARE
    tempint int4;
BEGIN
    SELECT coalesce(max(system_gid) + 1, 10000) INTO tempint FROM customers;
    NEW.system_gid := tempint;
    RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER set_system_gid BEFORE INSERT on customers for each row execute procedure set_system_gid();

CREATE OR REPLACE FUNCTION set_system_uid() RETURNS TRIGGER AS
$BODY$
DECLARE 
    tempint int4;
BEGIN
    SELECT coalesce(max(system_uid) + 1, 10000) INTO tempint FROM domains;
    NEW.system_uid := tempint;
    RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER set_system_uid BEFORE INSERT on domains for each row execute procedure set_system_uid();

CREATE TRIGGER set_system_gid_tmp BEFORE UPDATE on customers for each row execute procedure set_system_gid();
UPDATE customers SET id = id WHERE system_gid IS NULL;
DROP trigger set_system_gid_tmp on customers;

CREATE TRIGGER set_system_uid_tmp BEFORE UPDATE on domains for each row execute procedure set_system_uid();
UPDATE domains SET id = id WHERE system_uid IS NULL;
DROP trigger set_system_uid_tmp on domains;

ALTER TABLE customers ALTER column system_gid SET NOT NULL;
ALTER TABLE domains ALTER column system_uid SET NOT NULL;
CREATE UNIQUE INDEX ui_customers_sg on customers (system_gid);
CREATE UNIQUE INDEX ui_domains_su on domains (system_uid);

CREATE OR REPLACE FUNCTION get_account_uid(in_username TEXT, in_domain TEXT) RETURNS TEXT as $BODY$
DECLARE
    use_username text := trim(both FROM lower(in_username));
    use_domain   text := trim(both FROM lower(in_domain));
    reply INT4;
BEGIN
    SELECT d.system_uid INTO reply
        FROM accounts a join domains d on a.domain_id = d.id 
        WHERE d.fullname = use_domain AND a.username = use_username;
    IF NOT FOUND THEN
        RETURN NULL;
    END IF;
    RETURN reply;
END;
$BODY$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION get_account_gid(in_username TEXT, in_domain TEXT) RETURNS TEXT as $BODY$
DECLARE
    use_username text := trim(both FROM lower(in_username));
    use_domain   text := trim(both FROM lower(in_domain));
    reply INT4;
BEGIN
    SELECT c.system_gid INTO reply
        FROM accounts a join domains d on a.domain_id = d.id join customers c on d.customer_id = c.id
        WHERE d.fullname = use_domain AND a.username = use_username;
    IF NOT FOUND THEN
        RETURN NULL;
    END IF;
    RETURN reply;
END;
$BODY$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION get_account_homedir(in_username TEXT, in_domain TEXT) RETURNS TEXT as $BODY$
DECLARE
    use_username text := trim(both FROM lower(in_username));
    use_domain   text := trim(both FROM lower(in_domain));
    temptext TEXT;
BEGIN
    temptext := is_local_user(in_username, in_domain);
    IF temptext IS NULL THEN
        RETURN NULL;
    END IF;
    RETURN setup_get('mails_top_dir') || '/' || use_domain || '/' || use_username;
END;
$BODY$ LANGUAGE plpgsql;