CREATE TABLE vmail_domain (
    id SERIAL,
    name character varying(100) NOT NULL,
    sys_user character varying(10) NOT NULL,
    sys_edit timestamp without time zone NOT NULL,
    UNIQUE(name)
);

CREATE UNIQUE INDEX vmail_domain_id ON vmail_domain USING btree (id);

CREATE TABLE vmail_user (
    id SERIAL,
    vmail_domain_id integer NOT NULL,
    username character varying(50) NOT NULL,
    password character varying(50),
    active integer DEFAULT 0 NOT NULL,
    sys_user character varying(10) NOT NULL,
    sys_edit timestamp without time zone NOT NULL,
    UNIQUE(vmail_domain_id,username)
);

CREATE UNIQUE INDEX vmail_user_id ON vmail_user USING btree (id);
CREATE INDEX vmail_user_vmail_domain_id ON vmail_user USING btree (vmail_domain_id);

-- ALTER TABLE ONLY vmail_user
--     ADD CONSTRAINT vmail_user_vmail_domain_id_username_key UNIQUE (vmail_domain_id, username);

CREATE TABLE vmail_alias (
    id SERIAL,
    vmail_domain_id integer NOT NULL,
    username character varying(50) NOT NULL,
    destination TEXT DEFAULT NULL,
    islist integer DEFAULT 0 NOT NULL,
    active integer DEFAULT 0 NOT NULL,
    sys_user character varying(10) NOT NULL,
    sys_edit timestamp without time zone NOT NULL,
    UNIQUE(vmail_domain_id,username)
);

CREATE UNIQUE INDEX vmail_alias_id ON vmail_alias USING btree (id);
CREATE INDEX vmail_alias_vmail_domain_id ON vmail_alias USING btree (vmail_domain_id);

ALTER TABLE ONLY vmail_user
    ADD CONSTRAINT "$1" FOREIGN KEY (vmail_domain_id) REFERENCES vmail_domain(id);

ALTER TABLE ONLY vmail_alias
    ADD CONSTRAINT "$1" FOREIGN KEY (vmail_domain_id) REFERENCES vmail_domain(id);

REVOKE ALL ON TABLE vmail_user FROM PUBLIC;
GRANT SELECT ON TABLE vmail_user TO "vmail";
GRANT ALL ON TABLE vmail_user TO "hallinta";

REVOKE ALL ON SEQUENCE vmail_user_id_seq FROM PUBLIC;
GRANT ALL ON SEQUENCE vmail_user_id_seq TO "hallinta";

REVOKE ALL ON TABLE vmail_alias FROM PUBLIC;
GRANT SELECT ON TABLE vmail_alias TO "vmail";
GRANT ALL ON TABLE vmail_alias TO "hallinta";

REVOKE ALL ON SEQUENCE vmail_alias_id_seq FROM PUBLIC;
GRANT ALL ON SEQUENCE vmail_alias_id_seq TO "hallinta";

REVOKE ALL ON TABLE vmail_domain FROM PUBLIC;
GRANT SELECT ON TABLE vmail_domain TO "vmail";
GRANT ALL ON TABLE vmail_domain TO "hallinta";

REVOKE ALL ON SEQUENCE vmail_domain_id_seq FROM PUBLIC;
GRANT ALL ON SEQUENCE vmail_domain_id_seq TO "hallinta";

-- Shared Folders -------------------------------------------------------

CREATE TABLE vmail_shares (
  id SERIAL,
  from_user varchar(100) not null,
  to_user varchar(100) not null,
  isset char(1) DEFAULT '1',    -- always '1' currently
  primary key (from_user, to_user)
);

CREATE INDEX to_user ON vmail_shares (to_user); -- because we always search for to_user

REVOKE ALL ON TABLE vmail_shares FROM PUBLIC;
GRANT SELECT ON TABLE vmail_shares TO "vmail";
GRANT ALL ON TABLE vmail_shares TO "hallinta";

REVOKE ALL ON SEQUENCE vmail_shares_id_seq FROM PUBLIC;
GRANT ALL ON SEQUENCE vmail_shares_id_seq TO "hallinta";


CREATE TABLE vmail_anyone_shares (
  id SERIAL,
  from_user varchar(100) not null,
  isset char(1) DEFAULT '1',    -- always '1' currently
  primary key (from_user)
);

REVOKE ALL ON TABLE vmail_anyone_shares FROM PUBLIC;
GRANT SELECT ON TABLE vmail_anyone_shares TO "vmail";
GRANT ALL ON TABLE vmail_anyone_shares TO "hallinta";

REVOKE ALL ON SEQUENCE vmail_anyone_shares_id_seq FROM PUBLIC;
GRANT ALL ON SEQUENCE vmail_anyone_shares_id_seq TO "hallinta";


CREATE TABLE vmail_folder (
    id SERIAL,
    vmail_user_id integer NOT NULL,
    folder character varying(100) NOT NULL,
    sys_user character varying(10) NOT NULL,
    sys_edit timestamp without time zone NOT NULL,
    UNIQUE(vmail_user_id,folder)
);

CREATE UNIQUE INDEX vmail_folder_id ON vmail_folder USING btree (id);
CREATE INDEX vmail_folder_vmail_user_id ON vmail_folder USING btree (vmail_user_id);

ALTER TABLE ONLY vmail_folder
    ADD CONSTRAINT "$1" FOREIGN KEY (vmail_user_id) REFERENCES vmail_user(id);

REVOKE ALL ON TABLE vmail_folder FROM PUBLIC;
GRANT ALL ON TABLE vmail_folder TO "hallinta";

REVOKE ALL ON SEQUENCE vmail_folder_id_seq FROM PUBLIC;
GRANT ALL ON SEQUENCE vmail_folder_id_seq TO "hallinta";


CREATE TABLE vmail_folder_user (
    id SERIAL,
    vmail_folder_id integer NOT NULL,
    vmail_user_id integer NOT NULL,
    sys_user character varying(10) NOT NULL,
    sys_edit timestamp without time zone NOT NULL,
    UNIQUE(vmail_folder_id,vmail_user_id)
);

CREATE UNIQUE INDEX vmail_folder_user_id ON vmail_folder_user USING btree (id);
CREATE INDEX vmail_folder_user_vmail_folder_id ON vmail_folder_user USING btree (vmail_folder_id);
CREATE INDEX vmail_folder_user_vmail_user_id ON vmail_folder_user USING btree (vmail_user_id);

ALTER TABLE ONLY vmail_folder_user
    ADD CONSTRAINT "$1" FOREIGN KEY (vmail_folder_id) REFERENCES vmail_folder(id);
ALTER TABLE ONLY vmail_folder_user
    ADD CONSTRAINT "$2" FOREIGN KEY (vmail_user_id) REFERENCES vmail_user(id);

REVOKE ALL ON TABLE vmail_folder_user FROM PUBLIC;
GRANT SELECT ON TABLE vmail_folder_user TO "vmail";
GRANT ALL ON TABLE vmail_folder_user TO "hallinta";

REVOKE ALL ON SEQUENCE vmail_folder_user_id_seq FROM PUBLIC;
GRANT ALL ON SEQUENCE vmail_folder_user_id_seq TO "hallinta";

----------------------------------------------------------------------

CREATE TABLE vmail_filter_extension (
    id SERIAL PRIMARY KEY,
    extension varchar(10) NOT NULL,
    sys_user character varying(10) NOT NULL,
    sys_edit timestamp without time zone NOT NULL,
    UNIQUE(extension)
);

CREATE UNIQUE INDEX vmail_filter_extension_id ON vmail_filter_extension USING btree (id);
CREATE UNIQUE INDEX vmail_filter_extension_extension ON vmail_filter_extension USING btree (extension);

REVOKE ALL ON TABLE vmail_filter_extension FROM PUBLIC;
GRANT SELECT ON TABLE vmail_filter_extension TO "vmail";
GRANT ALL ON TABLE vmail_filter_extension TO "hallinta";

REVOKE ALL ON SEQUENCE vmail_filter_extension_id_seq FROM PUBLIC;
GRANT ALL ON SEQUENCE vmail_filter_extension_id_seq TO "hallinta";
