create table users
(
  id              int(16) auto_increment
    primary key,
  email           varchar(32)      not null,
  password        varchar(512)     not null,
  firstname       varchar(16)      not null,
  lastname        varchar(16)      not null,
  full_name       varchar(64)      not null,
  permission      int(1) default 0 not null,
  prolile_picture varchar(128)     not null
);

create table courses
(
  id           int(16) auto_increment
    primary key,
  title        varchar(32)  not null,
  holder_id    int(16)      not null,
  holder       varchar(32)  not null,
  labels       varchar(64)  not null comment 'Labels need to been seperated with a " , " ',
  episodes     int(4)       not null,
  total_lenght time         not null,
  description  text         not null,
  thumbnail    varchar(128) not null,
  constraint fk_course_holder_full_name
    foreign key (holder) references users (full_name)
      on update cascade on delete cascade,
  constraint fk_course_holder_id
    foreign key (holder_id) references users (id)
      on update cascade on delete cascade
);

create index index_course_holder
  on courses (holder);

create index index_course_holder_id
  on courses (holder_id);

create index index_course_id
  on courses (id);

create index index_course_title
  on courses (title);

create index index_full_name
  on users (full_name);

create index index_user_id
  on users (id);

create table videos
(
  id          int(16) auto_increment
    primary key,
  title       varchar(32)                        not null,
  uploader_id int(16)                            not null,
  uploader    varchar(32)                        not null,
  labels      varchar(64)                        not null comment 'Labels need to been seperated with a " , "',
  course_id   int(16)                            not null,
  course      varchar(16)                        not null,
  lenght      time                               not null,
  description text                               not null,
  thumbnail   varchar(128)                       not null,
  date        datetime default CURRENT_TIMESTAMP not null,
  constraint index_course_id
    unique (course_id),
  constraint fk_course_id
    foreign key (course_id) references courses (id)
      on update cascade on delete cascade,
  constraint fk_course_title
    foreign key (course) references courses (title)
      on update cascade on delete cascade,
  constraint fk_uploader_full_name
    foreign key (uploader) references users (full_name)
      on update cascade on delete cascade,
  constraint fk_uploader_id
    foreign key (uploader_id) references users (id)
      on update cascade on delete cascade
);

create index index_course_title
  on videos (course);

create index index_uploader
  on videos (uploader);

create index index_uploader_id
  on videos (uploader_id);