drop database if exists `test`;

create database `test`
    character set 'utf8';

use `test`;

create table `groups`(
    `id` int primary key auto_increment,
    `title` varchar(100) unique
) engine = InnoDB;

create table `modules`(
    `id` int primary key auto_increment,
    `title` varchar(30) unique
) engine = InnoDB;

create table `actions`(
    `id` int primary key auto_increment,
    `title` varchar(30),
    `module_id` int,

    foreign key (`module_id`) references `modules`(`id`)
) engine = InnoDB;

create table `group_permissions`(
    `group_id` int,
    `action_id` int,
    `flag` boolean default false,

    foreign key (`group_id`) references `groups`(`id`),
    foreign key (`action_id`) references `actions`(`id`)
) engine = InnoDB;

create table `users`(
    `id` int primary key auto_increment,
    `login` varchar(30) unique,
    `password` char(32),
    `group_id` int,
    `state` boolean default false,
    `createdOn` int,
    `modifiedOn` int,

    foreign key (`group_id`) references `groups`(`id`)
) engine = InnoDB;

create table `sertificates`(
    `id` int primary key auto_increment,
    `title` varchar(100) unique,
    `power` tinyint,
    `createdOn` int,
    `modifiedOn` int
) engine = InnoDB;

create table `user_sertificates`(
    `user_id` int,
    `given_by` int,
    `sertificate_id` int,
    `createdOn` int,
    `expireOn` int,

    foreign key (`user_id`) references `users`(`id`),
    foreign key (`given_by`) references `users`(`id`),
    foreign key (`sertificate_id`) references `sertificates`(`id`)
) engine = InnoDB;

create table `sertificate_permissions`(
    `sertificate_id` int,
    `action_id` int,
    `flag` boolean default false,

    foreign key (`sertificate_id`) references `sertificates`(`id`),
    foreign key (`action_id`) references `actions`(`id`)
) engine = InnoDB;


INSERT INTO `modules` VALUES (2,'gb'),(1,'lib');

INSERT INTO `actions` VALUES (1,'download',1),(2,'view',1),(3,'add',2),(4,'delete',2);

INSERT INTO `groups` VALUES (1,'users');
INSERT INTO `groups` VALUES (2,'moderes');

INSERT INTO `group_permissions` VALUES (1,2,1),(1,1,0),(1,3,1),(1,4,0);
INSERT INTO `group_permissions` VALUES (2,2,1),(2,1,1),(2,3,1),(2,4,0);

INSERT INTO `users` VALUES (1,'just_a_user',NULL,1,0,1310862915,0);
INSERT INTO `users` VALUES (2,'a moder',NULL,2,0,1310875518,0);

INSERT INTO `user_sertificates` VALUES (1,2,1,1310875594,0),(1,2,2,1310876807,0);