use Bliako;

drop table if exists comment;
drop table if exists event;
drop table if exists user;

create table user (
    user_id smallint unsigned not null primary key auto_increment,
    name varchar(32) not null
);

create table event (
    event_id smallint unsigned not null primary key auto_increment,
    type enum('play','concert') not null,
    name varchar(32) not null,
    unique key type_name_uk (type, name)
);

create table comment (
    comment_id smallint unsigned not null primary key auto_increment,
    user_id smallint unsigned not null,
    event_id smallint unsigned not null,
    text blob,
    key event_id_key (event_id),
    key user_id_key (user_id),
    unique key event_user_uk (event_id, user_id),
    constraint event_id foreign key(event_id) references event (event_id),
    constraint user_id foreign key(user_id) references user (user_id)
);

insert into user values (null, 'Bliako'), (null, '1nickt');
select user_id into @bliako_id from user where name = 'Bliako';
select user_id into @1nickt_id from user where name = '1nickt';
insert into event values (null, 'play', 'Hamlet');
insert into event values (null, 'concert', 'Rolling Stones');
select event_id into @play_id from event where name ='Hamlet';
select event_id into @concert_id from event where name ='Rolling Stones';
insert into comment values
  (null, @bliako_id, @play_id, 'Somewhat depressing'),
  (null, @bliako_id, @concert_id, 'Greatest rock and roll band in the world!'),
  (null, @1nickt_id, @concert_id, 'There is cool, then there is Charlie Watts.');