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

$CI = &get_instance();

if (!$CI->db->table_exists(db_prefix().'integrity_pacts')) {
    $CI->db->query("
        CREATE TABLE `".db_prefix()."integrity_pacts` (
          `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
          `year` SMALLINT NOT NULL,
          `staff_id` INT UNSIGNED NOT NULL,
          `staff_name` VARCHAR(191) NULL,
          `staff_code` VARCHAR(50) NULL,
          `nric_passport` VARCHAR(50) NULL,
          `department` VARCHAR(191) NULL,
          `department_id` INT UNSIGNED NULL,
          `designation` VARCHAR(191) NULL,

          `agreed` TINYINT(1) NOT NULL DEFAULT 1,
          `declaration_version` VARCHAR(50) NULL,

          `employee_signature` VARCHAR(191) NULL,
          `employee_date` DATE NULL,

          `hod_id` INT UNSIGNED NULL,
          `hod_name` VARCHAR(191) NULL,
          `hod_designation` VARCHAR(191) NULL,
          `hod_date` DATETIME NULL,
          `hod_comment` TEXT NULL,
          `hod_status` VARCHAR(20) NOT NULL DEFAULT 'pending',

          `status` VARCHAR(20) NOT NULL DEFAULT 'submitted',
          `submitted_at` DATETIME NULL,
          `created_at` DATETIME NULL,
          `updated_at` DATETIME NULL,

          PRIMARY KEY (`id`),
          UNIQUE KEY `uniq_staff_year` (`staff_id`,`year`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    ");
}

if (!$CI->db->table_exists(db_prefix().'integrity_pact_files')) {
    $CI->db->query("
        CREATE TABLE `".db_prefix()."integrity_pact_files` (
          `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
          `pact_id` INT UNSIGNED NOT NULL,
          `staff_id` INT UNSIGNED NULL,
          `file_name` VARCHAR(191) NOT NULL,
          `original_name` VARCHAR(191) NULL,
          `filetype` VARCHAR(60) NULL,
          `filesize` INT NULL,
          `dateadded` DATETIME NULL,
          PRIMARY KEY (`id`),
          KEY `pact_id` (`pact_id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    ");
}

// Options for extra recipients (optional)
// HOD is ALWAYS notified, these are “also notify”.
if (function_exists('add_option')) {
    if (get_option('ip_notify_role_ids') === null)  add_option('ip_notify_role_ids', '');
    if (get_option('ip_notify_staff_ids') === null) add_option('ip_notify_staff_ids', '');
}
