# design:
# satu user bisa manage bbrp module
# satu module bisa dimanage bbrp user
# satu sertifikat mengandung bbrp module dengan bobot tertentu
# satu module bisa jadi anggota bbrp sertifikat, masing2 dgn bobot berbeda
# satu module mengandung beberapa pertanyaan
# satu pertanyaan adalah anggota dari satu module
# satu pertanyaan terdiri dari beberapa jawaban, positif & negatif
# satu pertanyaan bisa dikomentari


create table user (
    id bigint unsigned not null,
    fullname varchar(255) not null,
    address1 varchar(255) not null,
    address2 varchar(255) not null,
    city varchar(255) not null,
    state varchar(255) not null,
    zip varchar(10) not null,
    country varchar(255) not null,
    email varchar(255) not null,
    password varchar(32) not null,
    www varchar(255) not null,
    superuser enum('no', 'yes') not null default 'no',
    primary key (id),
    unique (email)
);

create table module (
    id bigint unsigned not null,
    modulename varchar(255) not null,
    description blob not null,
    primary key (id)
);

create table module_author (
    userid bigint unsigned not null,
    moduleid bigint unsigned not null,
    primary key (userid, moduleid),
    index (moduleid)
);

create table certification (
    id bigint unsigned not null,
    certname varchar(255) not null,
    description blob not null,
);

create table certmodule (
    certid bigint unsigned not null,
    moduleid bigint unsigned not null,
    weight tinyint unsigned not null default 1,
    primary key (certid, moduleid),
    index (moduleid)
);

create table question (
    id bigint unsigned not null,
    module bigint unsigned not null,
    creator bigint unsigned not null,
    positive_question blob not null,
    negative_question blob not null,
    code blob not null, # code will be rendered as image in prop fonts
    date_created datetime not null,
    rating_avg float,       # cache field
    rating_good_num int unsigned not null default 0,  # cache field
    rating_bad_num int unsigned not null default 0,   # cache field
    primary key (id),
    index (module),
    index (creator)
);

create table answer (
    id bigint unsigned not null,
    question bigint unsigned not null,
    anstype enum('positive', 'negative') not null default 'positive',
    anstext blob not null,
    primary key (id),
    index (question)
);

create table question_approval (
    userid bigint unsigned not null,
    questionid bigint unsigned not null,
    rating tinyint signed not null default 0, # -1 bad 1 good
    primary key (userid, questionid),
    index (questionid)
);

create table question_comment (
    id bigint unsigned not null,
    question bigint unsigned not null,
    comment blob not null,
    when datetime not null,
    primary key (id),
    index (question)
);

create table module_comment_thread (
    id bigint unsigned not null,
    startedby bigint unsigned not null,
    module bigint unsigned not null,
    subject varchar(255) not null,
    firstpost datetime not null,
    lastpost datetime not null,
    primary key (id),
    index (startedby),
    index (module)
);

create table module_comment (
    id bigint unsigned not null,
    thread bigint unsigned not null,
    owner bigint unsigned not null,
    comment blob not null,
    when datetime not null,
    primary key (id),
    index (thread),
    index (owner)
);