jajajajaja

drop database slitdb;
create database slitdb;

create table module(
module_id int not null auto_increment,
module_name varchar(50) not null,
description varchar(1000),
rights int,
primary key(module_id)
);

create table resource(
resource_id int not null auto_increment,
module_id int,
description varchar(100),
primary key(resource_id),
foreign key(module_id) references module(module_id)
);

create table requirement(
requirement_id int not null auto_increment,
module_id int,
description varchar(100),
primary key(requirement_id),
foreign key(module_id) references module(module_id)
);

create table blog(
blog_id int not null auto_increment,
primary key(blog_id)
);

create table blog_post(
blog_post_id int not null auto_increment,
blog_id int,
blog_post_date date,
title varchar(100),
content varchar(1000),
primary key(blog_post_id),
foreign key(blog_id) references blog(blog_id)
);

create table semester_plan(
semester_plan_id int not null auto_increment,
primary key(semester_plan_id)
);

create table module_plan(
module_plan_id int not null auto_increment,
semester_plan_id int,
module_id int,
planned_date date,
primary key(module_plan_id),
foreign key(semester_plan_id) references semester_plan(semester_plan_id),
foreign key(module_id) references module(module_id)
);

create table users(
users_id int auto_increment,
first_name varchar(100),
last_name varchar(100),
email varchar(100),
phone_number int,
rights int,
blog_id int,
semester_plan_id int,
primary key(users_id),
foreign key(blog_id) references blog(blog_id),
foreign key(semester_plan_id) references semester_plan(semester_plan_id)
);

create table delivery(
delivery_id int not null auto_increment,
module_id int,
users_id int,
delivery_status boolean,
delivery_comment varchar(500),
date_delivered date,
date_approved date,
primary key(delivery_id),
foreign key(module_id) references module(module_id),
foreign key(users_id) references users(users_id)
);

create table delivery_file(
delivery_file_id int not null auto_increment,
delivery_id int,
content longblob,
primary key(delivery_file_id),
foreign key(delivery_id) references delivery(delivery_id)
);

#TESTDATA
insert into module values(null, "Modul 1", "Testmodul", 2);
insert into resource values(null, 1, "www.test.no");
insert into requirement values(null, 1, "Læremål1");
insert into blog values(null);
insert into blog_post values(null, 1, 20161005, "Bloginnlegg 1", "Dette er en test");
insert into semester_plan values(null);
insert into module_plan values(null, 1, 1, 20161005);
insert into users values(null, "Ådne", "Pådne", "pådne@mail.com", 911, 1, 1, 1);
insert into delivery values(null, 1, 1, false, "Test", 20161005, 20161005);
insert into delivery_file values(null, 1, 1110010110100011100101);