CREATE TABLE users (
        id              serial unique,
        username        varchar(32) not null unique,
        password        varchar(256) not null default 'x',
        name            text not null,
        ctime           timestamp not null default now()
);

CREATE TABLE addresses (
        id              serial unique,
        ref_name        text not null,
        ip              inet not null unique,
        reverse         text,
        notes           text
);

CREATE TABLE clients (
        id              serial unique,
        ref_number      integer,
        username        varchar(32) not null unique,
        password        varchar(256) not null,
        name            text not null,
        address         text,
        email           text not null
);

CREATE TABLE billing (
        client_id       integer not null references clients (id) on update cascade on delete cascade,
        plan_name       text not null,
        amount          float,
        start_time      timestamp,
        recurrance      interval,
        end_time        timestamp
);

CREATE TABLE domains (
        id              serial unique,
        owner           integer references clients (id) on update cascade on delete set null,
        domain          text not null unique,
        local_dns       boolean not null default 't',
        slave_dns       boolean not null default 'f',
        dynamic_dns     boolean not null default 'f',
        master_server   inet
);

CREATE TABLE apache_hosts (
        id              serial unique,
        owner           integer references clients (id) on update cascade on delete set null,
        hostname        text not null unique,
        documentroot    text not null,
        systemuser      varchar(32),
        systemgroup     varchar(32),
        admin_email     text,
        nossl           boolean,
        ssl             boolean,
        php             boolean,
        cgi             boolean,
        jsp             boolean,
        override        text
);

CREATE TABLE apache_options (
        host_id         integer references apache_hosts(id) on update cascade on delete cascade,
        name            text,
        value           text,
        CONSTRAINT DISTINCT ON (host_id,name,value)
);

CREATE TABLE apache_aliases (
        host_id         integer references apache_hosts(id) on update cascade on delete cascade,
        alias           text not null unique
);

CREATE TABLE apache_ips (
        host_id         integer references apache_hosts(id) on update cascade on delete cascade,
        ip_ref          integer references addresses(id) on update cascade on delete cascade,
        CONSTRAINT DISTINCT ON (host_id,ip_ref)
);

CREATE TABLE databases (
        id              serial unique,
        owner           integer references clients (id) on update cascade on delete set null,
        dbname          text not null,
        dbsystem        char not null,
        username        varchar(32),
        CONSTRAINT DISTINCT ON (dbname,dbsystem)
);

CREATE TABLE email_domains (
        id              serial unique,
        owner           integer references clients (id) on update cascade on delete set null,
        domain          text not null unique,
        prefix          varchar(16) unique,
        organization    text not null,
        backup_only     boolean not null default 'f'
);