CREATE TABLE `candidates`
(
    `candidate_code`                  varchar(50) COLLATE utf8mb4_unicode_ci  NOT NULL,
    `registration_number`             varchar(50) COLLATE utf8mb4_unicode_ci  NOT NULL unique,
    `tenant_code`                     varchar(20) COLLATE utf8mb4_unicode_ci  NOT NULL,
    `last_name`                       varchar(50) COLLATE utf8mb4_unicode_ci  NOT NULL,
    `middle_name`                     varchar(50) COLLATE utf8mb4_unicode_ci  NULL     DEFAULT NULL,
    `first_name`                      varchar(50) COLLATE utf8mb4_unicode_ci  NOT NULL,
    `name`                            varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
    `marital_status`                  varchar(20) COLLATE utf8mb4_unicode_ci  NOT NULL,
    `gender`                          varchar(10) COLLATE utf8mb4_unicode_ci  NOT NULL,
    `date_of_birth`                   date COLLATE utf8mb4_unicode_ci         NOT NULL,

    `email`                           varchar(190) COLLATE utf8mb4_unicode_ci not null,
    `phone_number`                    varchar(20) COLLATE utf8mb4_unicode_ci  NOT NULL,
    `contact_address`                 tinytext COLLATE utf8mb4_unicode_ci     NOT NULL,
    `zone_id`                         varchar(100)                            NOT NULL,
    `lga_id`                          varchar(100)                            NOT NULL,

    `nin_number`                      varchar(50) COLLATE utf8mb4_unicode_ci  NOT NULL,
    `voters_card_number`              varchar(100) COLLATE utf8mb4_unicode_ci NULL,
    `state_id_number`                 varchar(100) COLLATE utf8mb4_unicode_ci NULL,

    `photo_url`                       varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
    `photo_thumbnail_url`             varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
    `nin_image_url`                   varchar(250) COLLATE utf8mb4_unicode_ci NULL,
    `nin_image_url_thumbnail`         varchar(250) COLLATE utf8mb4_unicode_ci NULL,
    `voters_card_image_url`           varchar(250) COLLATE utf8mb4_unicode_ci NULL,
    `voters_card_image_url_thumbnail` varchar(250) COLLATE utf8mb4_unicode_ci NULL,
    `state_id_image_url`              varchar(250) COLLATE utf8mb4_unicode_ci NULL,
    `state_id_image_url_thumbnail`    varchar(250) COLLATE utf8mb4_unicode_ci NULL,


    `created_by`                      varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
    `remarks`                         text COLLATE utf8mb4_unicode_ci,
    `created_at`                      timestamp                               NULL     DEFAULT NULL,
    `updated_at`                      timestamp                               NULL     DEFAULT NULL,
    `deleted_at`                      timestamp                               NULL     DEFAULT NULL,

    is_anointed                       varchar(3)                              not null default 'No',
    anointed_by                       varchar(100)                            null,
    anointing_date                    datetime                                null,
    area_specialization               varchar(100)                            null,
    academic_qualification            varchar(100)                            null,
    age                               int                                     not null default 18,

    constraint pk_candidate_key primary key (candidate_code),
    constraint uk_candidate_email unique (email),
    index (registration_number),
    index (tenant_code),
    index (zone_id),
    index (lga_id),
    index (gender),
    index (marital_status),
    index (created_by)
) ENGINE = InnoDB;

create table if not exists subjects
(
    subject_id  int                                    not null auto_increment primary key,
    subject     varchar(50)                            not null unique,
    created_at  timestamp                              not null,
    updated_at  datetime                               not null,
    created_by  varchar(150)                           not null,
    tenant_code varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL
);

create table if not exists cbt_questions
(
    question_id    int                                    not null primary key auto_increment,
    subject_id     int                                    not null,
    question       longtext                               not null,
    option_a       longtext                               not null,
    option_b       longtext                               not null,
    option_c       longtext,
    option_d       longtext,
    correct_option varchar(1)                             not null,
    created_at     datetime                               not null,
    updated_at     datetime                               not null,
    created_by     varchar(150)                           not null,

    `tenant_code`  varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
    `zone_id`      varchar(100)                           NOT NULL,
    `lga_id`       varchar(100)                           NOT NULL,

    index (subject_id),
    index (tenant_code),
    index (zone_id),
    index (lga_id),
    index (created_by),

    constraint fk_cbt_questions_subject_id foreign key (subject_id)
        references subjects (subject_id) on update cascade on delete cascade
) engine = innodb;


create table if not exists cbt_test
(
    test_id                       varchar(50)                            not null,
    title                         varchar(150)                           not null,
    description                   text                                   not null,
    test_hours                    int                                    not null,
    test_minutes                  int                                    not null,
    points_per_question           int                                    not null,
    start_date                    datetime                               not null,
    end_date                      datetime                               not null,

    created_at                    timestamp                              not null,
    updated_at                    datetime                               not null,
    created_by                    varchar(150)                           not null,

    discontinued                  varchar(3)                             not null default 'no',

    `tenant_code`                 varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
    `zone_id`                     varchar(100)                           NOT NULL,
    `lga_id`                      varchar(100)                           NOT NULL,
    number_of_questions_to_answer int                                    not null default 10,

    constraint pk_cbt_test_test_id primary key (test_id),
    index (tenant_code),
    index (zone_id),
    index (lga_id),
    index (created_by)
) engine = innodb;


create table if not exists cbt_test_questions
(
    id          int         not null primary key auto_increment,
    test_id     varchar(50) not null,
    question_id int         not null,
    subject_id  int         not null,

    constraint fk_qid_test_quest foreign key (question_id) references cbt_questions (question_id) on update cascade on delete cascade,
    constraint fk_tid_test_quest foreign key (test_id) references cbt_test (test_id) on update cascade on delete cascade,
    constraint fk_sub_id_test_quest foreign key (subject_id) references subjects (subject_id) on update cascade on delete cascade
) engine = innodb;



create table if not exists cbt_test_registration_centers
(
    center_id    int          not null primary key auto_increment,
    name         varchar(150) not null,
    address      varchar(150) NOT NULL,
    phone_number varchar(50)  NOT NULL,

    created_at   timestamp    not null,
    updated_at   datetime     not null,
    created_by   varchar(150) not null,
    ranking      int          not null default 0,

    index (created_by)
) engine = innodb;



create table if not exists cbt_test_registrations
(
    registration_token varchar(50)                            not null,
    center_id          int                                    not null,
    test_id            varchar(50)                            not null,
    candidate_code     varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,

    created_at         timestamp                              not null,
    updated_at         datetime                               not null,
    created_by         varchar(150)                           not null,

    `tenant_code`      varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
    `zone_id`          varchar(100)                           NOT NULL,
    `lga_id`           varchar(100)                           NOT NULL,

    exam_date          date                                   null,
    exam_time          varchar(20)                            null,
    start_date         date                                   null,
    start_time         varchar(20)                            null,
    elapsed_time       varchar(20)                            null,
    seat_number        varchar(50)                            null,
    completed          varchar(3)                             not null default 'no',

    constraint pk_cbt_test_reg_token primary key (registration_token),
    index (tenant_code),
    index (zone_id),
    index (lga_id),
    index (created_by),

    constraint fk_candidate_code_test_reg foreign key (candidate_code)
        references candidates (candidate_code) on update cascade on delete cascade,
    constraint fk_tid_test_reg foreign key (test_id)
        references cbt_test (test_id) on update cascade on delete cascade,
    constraint fk_cid_test_reg foreign key (center_id)
        references cbt_test_registration_centers (center_id) on update cascade on delete cascade
) engine = innodb;


create table if not exists cbt_test_progress
(
    test_progress_id int                                    not null primary key auto_increment,
    test_id          varchar(50)                            not null,
    question_id      int                                    not null,
    subject_id       int                                    not null,

    correct_option   varchar(1)                             not null,
    chosen_option    varchar(1)                             not null,
    candidate_code   varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,

    constraint fk_c_code_test_progr foreign key (candidate_code)
        references candidates (candidate_code) on update cascade on delete cascade,
    constraint fk_qid_test_quest_prog foreign key (question_id)
        references cbt_questions (question_id) on update cascade on delete cascade,
    constraint fk_sub_id_test_quest_prog foreign key (subject_id)
        references subjects (subject_id) on update cascade on delete cascade,
    constraint fk_tid_test_quest_prog foreign key (test_id) references cbt_test (test_id) on update cascade on delete cascade
) engine = innodb;



create table if not exists cbt_test_result
(
    test_result_id              int                                    not null primary key auto_increment,
    test_id                     varchar(50)                            not null,
    subject_id                  int                                    not null,
    test_time                   varchar(20)                            not null,
    start_time                  varchar(20)                            not null,
    end_time                    varchar(20)                            not null,
    elapsed_time                varchar(20)                            not null,
    total_questions             int                                    not null,
    number_questions_answered   int                                    not null,
    number_questions_unanswered int                                    not null,
    number_questions_correct    int                                    not null,
    number_questions_wrong      int                                    not null,
    test_score                  int                                    not null,
    total_score                 int                                    not null,
    candidate_code              varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
    constraint fk_c_code_test_res_ foreign key (candidate_code)
        references candidates (candidate_code) on update cascade on delete cascade,
    constraint fk_sub_id_test_res_ foreign key (subject_id)
        references subjects (subject_id) on update cascade on delete cascade,
    constraint fk_tid_test_res_ foreign key (test_id)
        references cbt_test (test_id) on update no action on delete cascade
) engine = innodb;



CREATE TABLE exam_file_uploads
(
    id                 int UNSIGNED NOT NULL auto_increment primary key,
    name               varchar(255) NOT NULL,
    full_path          text         NOT NULL,
    local_path         text         NOT NULL,
    mime_type          varchar(50)  NOT NULL,
    size               int          NOT NULL,
    category           varchar(30)  NOT NULL,

    subject_id         int          NOT NULL,
    registration_token varchar(50)  not null,
    exam_time          varchar(20)  not null,

    tenant_code        varchar(20)  NOT NULL,
    created_at         timestamp    NULL DEFAULT NULL,
    updated_at         timestamp    NULL DEFAULT NULL,

    index (exam_time),
    index (registration_token),
    index (subject_id),
    index (category),

    constraint fk_reg_token_uploads foreign key (registration_token)
        references cbt_test_registrations (registration_token) on update cascade on delete cascade,
    constraint fk_sub_id_uploads foreign key (subject_id)
        references subjects (subject_id) on update cascade on delete cascade
) ENGINE = InnoDB;



INSERT INTO `cbt_test_registration_centers` (`center_id`, `name`, `address`, `phone_number`, `created_at`, `updated_at`,
                                             `created_by`, ranking)
VALUES (NULL, 'SWINTEC COMPUTER INSTITUTE', 'NO 5/9 HOSPITAL/ORLU ROAD, BY WAREHOUSE OWERRI IMO STATE', '08033315602',
        NOW(), NOW(), 'Ibrahim Haruna (harsoftng@gmail.com)', 0),

       (NULL, 'LITTLE ROCK SCHOOL', '5 SCHOOL ROAD, WORKS LAYOUT, OPP. CRADLE HOTELS, OWERRI, IMO STATE', '08062668089',
        NOW(), NOW(), 'Ibrahim Haruna (harsoftng@gmail.com)', 0),

       (NULL, 'PRIME TIME COLLOSSEUM',
        'PLOT 9-11 FEDERAL SECRETARIAT LAYOUT BESIDE FEDERAL HIGH COURT,NEW OWERRI, IMO STATE.', '07030441002', NOW(),
        NOW(), 'Ibrahim Haruna (harsoftng@gmail.com)', 0),

       (NULL, 'SPRING TOWER COMPUTER INSTITUTE', 'BESIDE /AMATA HEALTH CENTRE IKEDURU LGA, IMO STATE', '08036239478',
        NOW(), NOW(), 'Ibrahim Haruna (harsoftng@gmail.com)', 0),

       (NULL, 'UNIQUE COMPUTER INSTITUTE', '2/3 HOSPITAL /ORLU ROAD BY WAREHOUSE BUS STOP, OWERRI, IMO STATE',
        '08065034490', NOW(), NOW(), 'Ibrahim Haruna (harsoftng@gmail.com)', 0),

       (NULL, 'SORONARA VENTURES NIGERIA LIMITED', 'FOUNDATION ROAD, UMUDAGU MBIERI, MBAITOLI, IMO STATE',
        '08037912265', NOW(), NOW(), 'Ibrahim Haruna (harsoftng@gmail.com)', 0);



# insert into subjects(subject, created_at, updated_at, created_by)
# values ('Biology', now(), now(), 'Ibrahim Haruna <harsoftng@gmail.com>')

alter table subjects
    add
        tenant_code varchar(20) COLLATE utf8mb4_unicode_ci NULL;

alter table cbt_test_registrations
    add
        elapsed_time varchar(20) null;

alter table cbt_test_registrations
    add
        completed varchar(3) not null default 'no';
