


/*==============================================Sun July 23rd 2023==============================================*/


create table if not exists cbt_questions
(
    question_id      int                                    not null primary key auto_increment,
    category_subject varchar(50)                            not null,
    question         longtext                               not null,
    option_a         longtext                               not null,
    option_b         longtext                               not null,
    option_c         longtext,
    option_d         longtext,
    option_e         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 (category_subject),
    index (tenant_code),
    index (zone_id),
    index (lga_id),
    index (created_by)
) 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,

    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
) 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_progress
(
    test_progress_id int                                    not null primary key auto_increment,
    test_id          varchar(50)                            not null,
    question_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_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,
    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_tid_test_res_ foreign key (test_id) references cbt_test (test_id) on update no action 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);

alter table cbt_test_registration_centers
    add ranking int not null default 0;


/*=============================================================*/
alter table cbt_test
    add number_of_questions_to_answer int not null default 10;



