CREATE TABLE timeline (
    id         int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    type       text NOT NULL,
    created_by int UNSIGNED,
    created_on DATETIME,
    updated_on DATETIME
)TYPE=InnoDB CHARACTER SET utf8;

CREATE TABLE texts (
    id    int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    timeline_id int UNSIGNED,
    title text,
    text  mediumtext NOT NULL,
    foreign key(timeline_id) references timeline(id) on delete cascade
)TYPE=InnoDB CHARACTER SET utf8;

CREATE TABLE photos (
    id      int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    timeline_id int UNSIGNED,
    photo   text,
    caption mediumtext NOT NULL,
    foreign key(timeline_id) references timeline(id) on delete cascade
)TYPE=InnoDB CHARACTER SET utf8;

CREATE TABLE quotes (
    id     int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    timeline_id int UNSIGNED,
    quote  mediumtext,
    source mediumtext,
    foreign key(timeline_id) references timeline(id) on delete cascade
)TYPE=InnoDB CHARACTER SET utf8;

CREATE TABLE links (
    id   int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    timeline_id int UNSIGNED,
    name text,
    url  text NOT NULL,
    foreign key(timeline_id) references timeline(id) on delete cascade
)TYPE=InnoDB CHARACTER SET utf8;

CREATE TABLE videos (
    id   int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    timeline_id int UNSIGNED,
    url  text NOT NULL,
    text text,
    foreign key(timeline_id) references timeline(id) on delete cascade
)TYPE=InnoDB CHARACTER SET utf8;

CREATE TABLE users (
    id           int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name         varchar(255) UNIQUE NOT NULL,
    password     text NOT NULL,
    mail         varchar(255) NOT NULL,
    lastlogin_at DATETIME,
    created_on   DATETIME,
    updated_on   DATETIME
)TYPE=InnoDB CHARACTER SET utf8;

CREATE TABLE roles (
    id   int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name text NOT NULL
)TYPE=InnoDB;

CREATE TABLE user_role (
    id      int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id int UNSIGNED,
    role_id int UNSIGNED,
    UNIQUE (user_id, role_id)
)TYPE=InnoDB;

CREATE TABLE session (
    id           varchar(72) PRIMARY KEY,
    session_data mediumtext,
    expires      text
)TYPE=InnoDB;

INSERT INTO roles VALUES( 1, 'admin' );
INSERT INTO roles VALUES( 2, 'user' );

INSERT INTO users VALUES(
    1,
    'administrator',
    'e727d1464ae12436e899a726da5b2f11d8381b26',
    'taro@cpan.org',
    '',
    '',
    ''
);

INSERT INTO user_role VALUES( 1, 1, 1 );