create table if not exists `user` (
  `name` varchar(50) not null unique comment 'unique name of the user',
  `pubkey` text not null comment 'armored gnupg public key of the user',
  primary key (`name`)
) engine=InnoDB default charset=utf8 comment='list of all registered users and their public keys';

create table if not exists `message` (
        `id` int not null auto_increment comment "id of the message, it is used in the client to check if a message has already been downloaded or not",
        `time` timestamp default current_timestamp comment "time when the message has been stored on the server",
        `user` varchar(50) not null comment "name of the user that sent the message",
        `msg` longtext not null comment "message content, must be armored gnupg encrypted format",
        primary key (id),
        foreign key (user)
          references user(name)
          on delete cascade
          on update cascade
      ) character set utf8 engine=innodb
        comment="table to hold all messages for later download by the receiver";

/* table to sore arbitrary options */
create table if not exists `options` (
       `name` varchar(50) not null unique comment "option name",
       `value` text not null comment "option value",
       primary key (name)
) engine=InnoDB default charset=utf8 comment="table for system settings";