/* Create a user backupmx to connect database */
use mysql;
insert into user (Host, User, Password)
    values ('localhost', 'backupmx', password ('******'));
/* on duplicate key update; */

/* Allow backupmx user to access backupmx db */
use mysql;
insert into db (Host, Db, User, Select_priv)
    values ('localhost', 'backupmx', 'backupmx', 'Y');

/* Create simpleweb db */
create database backupmx;

/* Create user table */
use backupmx;
create table users (
    uid integer auto_increment, /* user id */
    login varchar (255) default '' not null,
    gender integer default 0, /* 0: Mister, 1: Miss, 2:Misses */
    name varchar (255) default '' not null,
    firstname varchar (255) default '',
    email varchar (255) default '',
    pmail varchar (1023) default '',
    password varchar (255) default '' not null,
--
    primary key (uid)
);

/* Create host table */
use backupmx;
create table hosts (
    hid integer not null auto_increment, /* host id */
    domain varchar(255) default '' not null,
    validity date not null,
    owner integer not null, /* users.uid */
--
    primary key (hid)
);

/* Create weekly statistic table */
use backupmx;
create table weekly_stats (
    mid integer not null auto_increment, /* mail id */
    date date not null,
    time time not null,
    size integer not null,
    host integer not null, /* hosts.hid */
--
    primary key (mid)
);

/* Create monthly statistic table */
use backupmx;
create table monthly_stats (
    sid integer not null auto_increment, /* stat id */
    year integer not null,
    month integer not null,
    day integer not null,
    nb_emails integer not null,
    nb_bytes integer not null,
    host integer not null, /* hosts.hid */
--
    primary key (sid)
);

/* Crete session table */
use backupmx;
create table sessions (
  sid integer not null auto_increment, /* session id */
  uid integer not null,
  expire double not null,
--
  primary key (sid)
);

/* Grant backupmx user to backupmx tables */
grant select, insert, update, delete on users to backupmx;
grant select, insert, update, delete on hosts to backupmx;
grant select, insert, update, delete on weekly_stats to backupmx;
grant select, insert, update, delete on monthly_stats to backupmx;
grant select, insert, update, delete on sessions to backupmx;

/* Flush all the stuff */
flush query cache;
flush privileges;

/* Create admin account with uid = 1 */
use backupmx;
insert into users set login = 'admin', name = 'Administrator',  password = password ('******'), uid = 0;