<?php

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

$CI = &get_instance();

if (!$CI->db->table_exists(db_prefix() . 'project_costing_client_categories')) {
    $CI->db->query('CREATE TABLE `' . db_prefix() . "project_costing_client_categories` (
        `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
        `name` VARCHAR(191) NOT NULL,
        `description` TEXT NULL,
        `status` TINYINT(1) NOT NULL DEFAULT 1,
        `datecreated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `addedfrom` INT UNSIGNED NULL,
        PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
}

if (!$CI->db->table_exists(db_prefix() . 'project_costing_engagement_types')) {
    $CI->db->query('CREATE TABLE `' . db_prefix() . "project_costing_engagement_types` (
        `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
        `client_category_id` INT UNSIGNED NOT NULL,
        `name` VARCHAR(191) NOT NULL,
        `sst_percent` DECIMAL(10,2) NOT NULL DEFAULT 0.00,

        `bidding_sme` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        `bidding_admin` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        `bidding_profit` DECIMAL(10,2) NOT NULL DEFAULT 0.00,

        `non_bidding_sme` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        `non_bidding_admin` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        `non_bidding_profit` DECIMAL(10,2) NOT NULL DEFAULT 0.00,

        `status` TINYINT(1) NOT NULL DEFAULT 1,
        `datecreated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `addedfrom` INT UNSIGNED NULL,
        PRIMARY KEY (`id`),
        KEY `client_category_id` (`client_category_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
}

if (!$CI->db->table_exists(db_prefix() . 'project_costing_approval_settings')) {
    $CI->db->query('CREATE TABLE `' . db_prefix() . "project_costing_approval_settings` (
        `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
        `finance_reviewer_staff_id` INT UNSIGNED NULL,
        `hod_approver_staff_id` INT UNSIGNED NULL,
        `datecreated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` DATETIME NULL,
        `updated_by` INT UNSIGNED NULL,
        PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
}

if (!$CI->db->table_exists(db_prefix() . 'project_costing_projects')) {
    $CI->db->query('CREATE TABLE `' . db_prefix() . "project_costing_projects` (
        `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
        `proposal_name` VARCHAR(255) NOT NULL,
        `client_name` VARCHAR(255) NOT NULL,
        `client_category_id` INT UNSIGNED NULL,
        `engagement_type_id` INT UNSIGNED NULL,
        `engagement_mode` ENUM('bidding','non_bidding') NULL,
        `sst_percent` DECIMAL(10,2) NOT NULL DEFAULT 0.00,

        `bidding_sme_percent` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        `bidding_admin_percent` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
        `bidding_profit_percent` DECIMAL(10,2) NOT NULL DEFAULT 0.00,

        `submitted_by` INT UNSIGNED NULL,
        `finance_reviewer_staff_id` INT UNSIGNED NULL,
        `hod_approver_staff_id` INT UNSIGNED NULL,

        `programme_total` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
        `other_related_total` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
        `indirect_total` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
        `overall_total` DECIMAL(15,2) NOT NULL DEFAULT 0.00,

        `finance_status` VARCHAR(50) DEFAULT 'pending',
        `finance_comment` TEXT NULL,
        `finance_approved_at` DATETIME NULL,

        `hod_status` VARCHAR(50) DEFAULT 'pending',
        `hod_comment` TEXT NULL,
        `hod_approved_at` DATETIME NULL,

        `overall_status` VARCHAR(50) DEFAULT 'draft',
        `datecreated` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        `updated_at` DATETIME NULL,

        PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
}

if (!$CI->db->table_exists(db_prefix() . 'project_costing_project_items')) {
    $CI->db->query('CREATE TABLE `' . db_prefix() . "project_costing_project_items` (
        `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
        `project_id` INT UNSIGNED NOT NULL,
        `section_type` ENUM('programme','other_related','indirect') NOT NULL,
        `item_name` VARCHAR(255) NOT NULL,
        `qty` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
        `frequency` DECIMAL(15,2) NULL DEFAULT 0.00,
        `rate` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
        `amount` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
        `remarks` TEXT NULL,
        PRIMARY KEY (`id`),
        KEY `project_id` (`project_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
}
if (!$CI->db->table_exists(db_prefix() . 'project_costing_notifications')) {
    $CI->db->query('CREATE TABLE `' . db_prefix() . "project_costing_notifications` (
        `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
        `project_id` INT UNSIGNED NOT NULL,
        `recipient_staff_id` INT UNSIGNED NULL,
        `recipient_email` VARCHAR(191) NULL,
        `notification_type` VARCHAR(100) NOT NULL,
        `subject` VARCHAR(255) NOT NULL,
        `message` MEDIUMTEXT NULL,
        `channel` VARCHAR(50) NOT NULL DEFAULT 'email',
        `status` VARCHAR(50) NOT NULL DEFAULT 'sent',
        `date_sent` DATETIME NOT NULL,
        `sent_by` INT UNSIGNED NULL,
        PRIMARY KEY (`id`),
        KEY `project_id` (`project_id`),
        KEY `recipient_staff_id` (`recipient_staff_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
}
if (!$CI->db->field_exists('finance_cc_emails', db_prefix() . 'project_costing_approval_settings')) {
    $CI->db->query('ALTER TABLE `' . db_prefix() . 'project_costing_approval_settings` ADD `finance_cc_emails` TEXT NULL');
}

if (!$CI->db->field_exists('hod_cc_emails', db_prefix() . 'project_costing_approval_settings')) {
    $CI->db->query('ALTER TABLE `' . db_prefix() . 'project_costing_approval_settings` ADD `hod_cc_emails` TEXT NULL');
}