<?php defined('BASEPATH') or exit('No direct script access allowed');

$CI = &get_instance();

if (!$CI->db->table_exists(db_prefix().'annual_external_engagements')) {
    $CI->db->query('CREATE TABLE `'.db_prefix().'annual_external_engagements` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `staff_id` INT(11) NOT NULL,
        `staff_name` VARCHAR(191) NULL,
        `staff_code` VARCHAR(50) NULL,
        `position` VARCHAR(191) NULL,
        `department` VARCHAR(191) NULL,
        `division` VARCHAR(191) NULL,

        `submission_type` VARCHAR(191) NULL,

        `conflict_competing` VARCHAR(10) NULL,
        `conflict_family` VARCHAR(10) NULL,
        `conflict_ip` VARCHAR(10) NULL,
        `conflict_details` TEXT NULL,

        `ip_use` VARCHAR(10) NULL,
        `ip_details` TEXT NULL,

        `day_limit` VARCHAR(10) NULL,
        `interfere` VARCHAR(10) NULL,
        `mitigation` TEXT NULL,

        `declaration` VARCHAR(50) NULL,
        `digital_signature` VARCHAR(191) NOT NULL,
        `submission_date` DATE NOT NULL,

        `created_at` DATETIME NULL,
        `updated_at` DATETIME NULL,

        PRIMARY KEY (`id`),
        KEY `staff_id` (`staff_id`),
        KEY `submission_date` (`submission_date`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;');
}

if (!$CI->db->table_exists(db_prefix().'annual_external_engagement_items')) {
    $CI->db->query('CREATE TABLE `'.db_prefix().'annual_external_engagement_items` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `engagement_id` INT(11) NOT NULL,
        `sort_order` INT(11) NOT NULL DEFAULT 1,
        `engagement_type` VARCHAR(191) NULL,
        `organisation` VARCHAR(191) NULL,
        `position_held` VARCHAR(191) NULL,
        `start_date` DATE NULL,
        `end_date` DATE NULL,
        `frequency` VARCHAR(191) NULL,
        `payment` DECIMAL(12,2) NULL DEFAULT 0.00,
        PRIMARY KEY (`id`),
        KEY `engagement_id` (`engagement_id`),
        KEY `sort_order` (`sort_order`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;');
}

if (!$CI->db->table_exists(db_prefix().'annual_external_engagement_files')) {
    $CI->db->query('CREATE TABLE `'.db_prefix().'annual_external_engagement_files` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `engagement_id` INT(11) NOT NULL,
        `staff_id` INT(11) NOT NULL,
        `file_name` VARCHAR(191) NOT NULL,
        `original_name` VARCHAR(191) NULL,
        `filetype` VARCHAR(100) NULL,
        `filesize` VARCHAR(50) NULL,
        `created_at` DATETIME NULL,
        PRIMARY KEY (`id`),
        KEY `engagement_id` (`engagement_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;');
}

// notification recipients options
if (function_exists('add_option')) {
    if (get_option('see_notify_role_ids') === null) add_option('see_notify_role_ids', '');
    if (get_option('see_notify_staff_ids') === null) add_option('see_notify_staff_ids', '');
}
