<?php

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

class Project_costing_model extends App_Model
{
    public function count_projects()
    {
        return $this->db->count_all_results(db_prefix() . 'project_costing_projects');
    }

    public function count_projects_by_status($field, $status)
    {
        return $this->db->where($field, $status)->count_all_results(db_prefix() . 'project_costing_projects');
    }

    public function get_projects_for_admin()
    {
        $this->db->select('p.*, 
            cc.name as client_category_name,
            et.name as engagement_type_name,
            s1.firstname as finance_firstname, s1.lastname as finance_lastname,
            s2.firstname as hod_firstname, s2.lastname as hod_lastname,
            s3.firstname as submitter_firstname, s3.lastname as submitter_lastname');
        $this->db->from(db_prefix() . 'project_costing_projects p');
        $this->db->join(db_prefix() . 'project_costing_client_categories cc', 'cc.id = p.client_category_id', 'left');
        $this->db->join(db_prefix() . 'project_costing_engagement_types et', 'et.id = p.engagement_type_id', 'left');
        $this->db->join(db_prefix() . 'staff s1', 's1.staffid = p.finance_reviewer_staff_id', 'left');
        $this->db->join(db_prefix() . 'staff s2', 's2.staffid = p.hod_approver_staff_id', 'left');
        $this->db->join(db_prefix() . 'staff s3', 's3.staffid = p.submitted_by', 'left');
        $this->db->order_by('p.id', 'desc');

        return $this->db->get()->result_array();
    }

    public function get_client_categories($id = '')
    {
        if ($id) {
            return $this->db->where('id', $id)->get(db_prefix() . 'project_costing_client_categories')->row_array();
        }

        $this->db->order_by('id', 'desc');
        return $this->db->get(db_prefix() . 'project_costing_client_categories')->result_array();
    }

    public function get_client_categories_active()
    {
        $this->db->where('status', 1);
        $this->db->order_by('name', 'asc');
        return $this->db->get(db_prefix() . 'project_costing_client_categories')->result_array();
    }

    public function add_client_category($data)
    {
        $insert = [
            'name'        => trim($data['name']),
            'description' => $data['description'] ?? null,
            'status'      => isset($data['status']) ? 1 : 0,
            'addedfrom'   => get_staff_user_id(),
        ];

        $this->db->insert(db_prefix() . 'project_costing_client_categories', $insert);
        return $this->db->insert_id();
    }

    public function update_client_category($id, $data)
    {
        $update = [
            'name'        => trim($data['name']),
            'description' => $data['description'] ?? null,
            'status'      => isset($data['status']) ? 1 : 0,
        ];

        $this->db->where('id', $id);
        return $this->db->update(db_prefix() . 'project_costing_client_categories', $update);
    }

    public function delete_client_category($id)
    {
        $this->db->where('client_category_id', $id);
        $linked = $this->db->count_all_results(db_prefix() . 'project_costing_engagement_types');

        if ($linked > 0) {
            return false;
        }

        $this->db->where('id', $id);
        return $this->db->delete(db_prefix() . 'project_costing_client_categories');
    }

    public function get_engagement_types($id = '')
    {
        $this->db->select('et.*, cc.name as client_category_name');
        $this->db->from(db_prefix() . 'project_costing_engagement_types et');
        $this->db->join(db_prefix() . 'project_costing_client_categories cc', 'cc.id = et.client_category_id', 'left');

        if ($id) {
            $this->db->where('et.id', $id);
            return $this->db->get()->row_array();
        }

        $this->db->order_by('et.id', 'desc');
        return $this->db->get()->result_array();
    }

    public function add_engagement_type($data)
    {
        $insert = [
            'client_category_id' => (int)$data['client_category_id'],
            'name'               => trim($data['name']),
            'sst_percent'        => (float)$data['sst_percent'],

            'bidding_sme'        => (float)$data['bidding_sme'],
            'bidding_admin'      => (float)$data['bidding_admin'],
            'bidding_profit'     => (float)$data['bidding_profit'],

            'non_bidding_sme'    => (float)$data['non_bidding_sme'],
            'non_bidding_admin'  => (float)$data['non_bidding_admin'],
            'non_bidding_profit' => (float)$data['non_bidding_profit'],

            'status'             => isset($data['status']) ? 1 : 0,
            'addedfrom'          => get_staff_user_id(),
        ];

        $this->db->insert(db_prefix() . 'project_costing_engagement_types', $insert);
        return $this->db->insert_id();
    }

    public function update_engagement_type($id, $data)
    {
        $update = [
            'client_category_id' => (int)$data['client_category_id'],
            'name'               => trim($data['name']),
            'sst_percent'        => (float)$data['sst_percent'],

            'bidding_sme'        => (float)$data['bidding_sme'],
            'bidding_admin'      => (float)$data['bidding_admin'],
            'bidding_profit'     => (float)$data['bidding_profit'],

            'non_bidding_sme'    => (float)$data['non_bidding_sme'],
            'non_bidding_admin'  => (float)$data['non_bidding_admin'],
            'non_bidding_profit' => (float)$data['non_bidding_profit'],

            'status'             => isset($data['status']) ? 1 : 0,
        ];

        $this->db->where('id', $id);
        return $this->db->update(db_prefix() . 'project_costing_engagement_types', $update);
    }

    public function delete_engagement_type($id)
    {
        $this->db->where('engagement_type_id', $id);
        $linked = $this->db->count_all_results(db_prefix() . 'project_costing_projects');

        if ($linked > 0) {
            return false;
        }

        $this->db->where('id', $id);
        return $this->db->delete(db_prefix() . 'project_costing_engagement_types');
    }

    public function get_approval_settings()
    {
        return $this->db->get(db_prefix() . 'project_costing_approval_settings')->row_array();
    }


    public function save_approval_settings($data)
{
    $payload = [
        'finance_reviewer_staff_id' => !empty($data['finance_reviewer_staff_id']) ? (int)$data['finance_reviewer_staff_id'] : null,
        'hod_approver_staff_id'     => !empty($data['hod_approver_staff_id']) ? (int)$data['hod_approver_staff_id'] : null,
        'finance_cc_emails'         => isset($data['finance_cc_emails']) ? trim($data['finance_cc_emails']) : null,
        'hod_cc_emails'             => isset($data['hod_cc_emails']) ? trim($data['hod_cc_emails']) : null,
        'updated_at'                => date('Y-m-d H:i:s'),
        'updated_by'                => get_staff_user_id(),
    ];

    $row = $this->get_approval_settings();

    if ($row) {
        $this->db->where('id', $row['id']);
        return $this->db->update(db_prefix() . 'project_costing_approval_settings', $payload);
    }

    $payload['datecreated'] = date('Y-m-d H:i:s');
    return $this->db->insert(db_prefix() . 'project_costing_approval_settings', $payload);
}
private function parse_cc_emails($emails)
{
    if (empty($emails)) {
        return [];
    }

    $parts = array_map('trim', explode(',', $emails));
    $parts = array_filter($parts, function ($email) {
        return filter_var($email, FILTER_VALIDATE_EMAIL);
    });

    return array_values(array_unique($parts));
}






    //For Staff
    public function get_engagement_types_by_category($clientCategoryId)
{
    $this->db->where('client_category_id', $clientCategoryId);
    $this->db->where('status', 1);
    $this->db->order_by('name', 'asc');
    return $this->db->get(db_prefix() . 'project_costing_engagement_types')->result_array();
}

public function get_engagement_type_row($id)
{
    return $this->db->where('id', $id)->get(db_prefix() . 'project_costing_engagement_types')->row_array();
}

public function get_my_projects($staffId)
{
    $this->db->select('p.*, cc.name as client_category_name, et.name as engagement_type_name');
    $this->db->from(db_prefix() . 'project_costing_projects p');
    $this->db->join(db_prefix() . 'project_costing_client_categories cc', 'cc.id = p.client_category_id', 'left');
    $this->db->join(db_prefix() . 'project_costing_engagement_types et', 'et.id = p.engagement_type_id', 'left');
    $this->db->where('p.submitted_by', $staffId);
    $this->db->order_by('p.id', 'desc');
    return $this->db->get()->result_array();
}

public function get_project($id)
{
    $this->db->select('p.*,
        cc.name as client_category_name,
        et.name as engagement_type_name,
        finance.firstname as finance_firstname, finance.lastname as finance_lastname,
        hod.firstname as hod_firstname, hod.lastname as hod_lastname,
        submitter.firstname as submitter_firstname, submitter.lastname as submitter_lastname');
    $this->db->from(db_prefix() . 'project_costing_projects p');
    $this->db->join(db_prefix() . 'project_costing_client_categories cc', 'cc.id = p.client_category_id', 'left');
    $this->db->join(db_prefix() . 'project_costing_engagement_types et', 'et.id = p.engagement_type_id', 'left');
    $this->db->join(db_prefix() . 'staff finance', 'finance.staffid = p.finance_reviewer_staff_id', 'left');
    $this->db->join(db_prefix() . 'staff hod', 'hod.staffid = p.hod_approver_staff_id', 'left');
    $this->db->join(db_prefix() . 'staff submitter', 'submitter.staffid = p.submitted_by', 'left');
    $this->db->where('p.id', $id);

    return $this->db->get()->row_array();
}

public function get_project_items_grouped($projectId)
{
    $items = $this->db->where('project_id', $projectId)->get(db_prefix() . 'project_costing_project_items')->result_array();

    $grouped = [
        'programme'     => [],
        'other_related' => [],
        'indirect'      => [],
    ];

    foreach ($items as $item) {
        $grouped[$item['section_type']][] = $item;
    }

    return $grouped;
}
public function create_project_submission($data)
{
    $settings = $this->get_approval_settings();

    //$engagementType = $this->get_engagement_type_row($data['engagement_type_id']);
    $engagementType = $this->get_engagement_type_row($data['engagement_type_id'] ?? 0);

    if (!$engagementType) {
        return false;
    }

    $proposalName = trim($data['proposal_name'] ?? '');
    $clientName   = trim($data['client_name'] ?? '');
    $mode         = $data['engagement_mode'] ?? '';

    if ($proposalName === '' || $clientName === '') {
        return false;
    }

    if (!in_array($mode, ['bidding', 'non_bidding'], true)) {
        return false;
    }

    if ($mode === 'bidding') {
        $programmePercent = (float)($data['display_sme'] ?? 0);
        $otherPercent     = (float)($data['display_admin'] ?? 0);
        $profitPercent    = (float)($data['display_profit'] ?? 0);

        $totalPercent = round($programmePercent + $otherPercent + $profitPercent, 2);

        if ($totalPercent != 100.00) {
            return false;
        }

        if ($programmePercent > $profitPercent) {
            return false;
        }
    } else {
        $programmePercent = (float)$engagementType['non_bidding_sme'];
        $otherPercent     = (float)$engagementType['non_bidding_admin'];
        $profitPercent    = (float)$engagementType['non_bidding_profit'];
    }

$sstPercent = isset($data['display_sst']) ? (float)$data['display_sst'] : null;

if (!in_array((string)$sstPercent, ['0', '6', '8'], true) && !in_array($sstPercent, [0.0, 6.0, 8.0], true)) {
    return false;
}

$programmeTypes      = $data['programme_type'] ?? [];
$programmeJobGrades  = $data['programme_job_grade'] ?? [];
$otherNames          = $data['other_name'] ?? [];
$otherRaLevels       = $data['other_ra_level'] ?? [];

// Validate SME Internal -> Job Grade required
foreach (($data['programme_name'] ?? []) as $i => $name) {
    $itemName = trim($name ?? '');
    if ($itemName === '') {
        continue;
    }

    $expertType = trim($programmeTypes[$i] ?? '');
    $jobGrade   = trim($programmeJobGrades[$i] ?? '');

    if ($expertType === 'Internal' && $jobGrade === '') {
        return false;
    }
}

// Validate Research Assistant -> RA Level required
foreach ($otherNames as $i => $name) {
    $itemName = trim($name ?? '');
    if ($itemName === '') {
        continue;
    }

    $raLevel = trim($otherRaLevels[$i] ?? '');

    if ($itemName === 'Research Assistant' && !in_array($raLevel, ['Degree', 'Master', 'PhD'], true)) {
        return false;
    }
}

   $programmeTotal = $this->calculate_items_total_with_rules(
    'programme',
    $data['programme_name'] ?? [],
    $data['programme_qty'] ?? [],
    $data['programme_frequency'] ?? [],
    $data['programme_rate'] ?? [],
    true,
    $data['programme_type'] ?? [],
    $data['programme_job_grade'] ?? [],
    []
);

$otherRelatedTotal = $this->calculate_items_total_with_rules(
    'other_related',
    $data['other_name'] ?? [],
    $data['other_qty'] ?? [],
    $data['other_frequency'] ?? [],
    $data['other_rate'] ?? [],
    true,
    [],
    [],
    $data['other_ra_level'] ?? []
);

    $indirectTotal = $this->calculate_items_total(
        $data['indirect_name'] ?? [],
        $data['indirect_qty'] ?? [],
        [],
        $data['indirect_rate'] ?? [],
        false
    );

    $overallBaseTotal = round($programmeTotal + $otherRelatedTotal + $indirectTotal, 2);

    $profitInceif = 0;
    $totalContractValue = $overallBaseTotal;
    $sstAmount = 0;
    $grandTotal = $overallBaseTotal;
    $validationWarning = 0;

    $divisorPercent = $programmePercent + $otherPercent;

    if ($overallBaseTotal > 0 && $divisorPercent > 0) {
        $profitInceif = ($overallBaseTotal / ($divisorPercent / 100)) * ($profitPercent / 100);
        $totalContractValue = $overallBaseTotal + $profitInceif;
        //$sstAmount = $totalContractValue * (((float)$engagementType['sst_percent']) / 100);
        $sstAmount = $totalContractValue * ($sstPercent / 100);
        $grandTotal = $totalContractValue + $sstAmount;
    }

    // warning starts only when B has value
    if ($totalContractValue > 0 && $otherRelatedTotal > 0) {
        $actualProgrammePct = round(($programmeTotal / $totalContractValue) * 100, 2);
        $actualOtherPct     = round(($otherRelatedTotal / $totalContractValue) * 100, 2);
        $actualIndirectPct  = round(($indirectTotal / $totalContractValue) * 100, 2);

        if ($actualProgrammePct > round($programmePercent, 2)) {
            $validationWarning = 1;
        }

        if ($actualOtherPct > round($otherPercent, 2)) {
            $validationWarning = 1;
        }

        // C uses admin rule
        if ($actualIndirectPct > round($otherPercent, 2)) {
            $validationWarning = 1;
        }
    }

    $projectInsert = [
        'proposal_name'             => $proposalName,
        'client_name'               => $clientName,
        'client_category_id'        => $data['client_category_id'] ?? null,
        'engagement_type_id'        =>  $data['engagement_type_id'] ?? null,
        'engagement_mode'           => $mode,
        //'sst_percent'               => (float)$engagementType['sst_percent'],
        'sst_percent' => $sstPercent,

        'bidding_sme_percent'       => $programmePercent,
        'bidding_admin_percent'     => $otherPercent,
        'bidding_profit_percent'    => $profitPercent,

        'submitted_by'              => get_staff_user_id(),
        'finance_reviewer_staff_id' => $settings['finance_reviewer_staff_id'] ?? null,
        'hod_approver_staff_id'     => $settings['hod_approver_staff_id'] ?? null,

        'programme_total'           => round($programmeTotal, 2),
        'other_related_total'       => round($otherRelatedTotal, 2),
        'indirect_total'            => round($indirectTotal, 2),
        'overall_total'             => round($totalContractValue, 2),



'indirect_total'       => round($indirectTotal, 2),
'profit_inceif'        => round($profitInceif, 2),
'total_contract_value' => round($totalContractValue, 2),
'sst_amount'           => round($sstAmount, 2),
'grand_total'          => round($grandTotal, 2),


        'validation_warning'        => $validationWarning,
        'validation_note'           => $validationWarning ? 'Submitted with percentage warning. Subject to approval.' : null,

        'finance_status'            => 'pending',
        'hod_status'                => 'pending',
        'overall_status'            => 'submitted',
        'updated_at'                => date('Y-m-d H:i:s'),
    ];

    $this->db->insert(db_prefix() . 'project_costing_projects', $projectInsert);
    $projectId = $this->db->insert_id();

    if (!$projectId) {
        return false;
    }


    $this->save_project_items(
    $projectId,
    'programme',
    $data['programme_name'] ?? [],
    $data['programme_qty'] ?? [],
    $data['programme_frequency'] ?? [],
    $data['programme_rate'] ?? [],
    $data['programme_remarks'] ?? [],
    true,
    $data['programme_type'] ?? [],
    $data['programme_job_grade'] ?? [],
    []
);

$this->save_project_items(
    $projectId,
    'other_related',
    $data['other_name'] ?? [],
    $data['other_qty'] ?? [],
    $data['other_frequency'] ?? [],
    $data['other_rate'] ?? [],
    $data['other_remarks'] ?? [],
    true,
    [],
    [],
    $data['other_ra_level'] ?? []
);
$this->save_project_items(
    $projectId,
    'indirect',
    $data['indirect_name'] ?? [],
    $data['indirect_qty'] ?? [],
    [],
    $data['indirect_rate'] ?? [],
    $data['indirect_remarks'] ?? [],
    false,
    [],
    [],
    []
);
    // $this->save_project_items(
    //     $projectId,
    //     'programme',
    //     $data['programme_name'] ?? [],
    //     $data['programme_qty'] ?? [],
    //     $data['programme_frequency'] ?? [],
    //     $data['programme_rate'] ?? [],
    //     $data['programme_remarks'] ?? [],
    //     true
    // );

    // $this->save_project_items(
    //     $projectId,
    //     'other_related',
    //     $data['other_name'] ?? [],
    //     $data['other_qty'] ?? [],
    //     $data['other_frequency'] ?? [],
    //     $data['other_rate'] ?? [],
    //     $data['other_remarks'] ?? [],
    //     true
    // );

    // $this->save_project_items(
    //     $projectId,
    //     'indirect',
    //     $data['indirect_name'] ?? [],
    //     $data['indirect_qty'] ?? [],
    //     [],
    //     $data['indirect_rate'] ?? [],
    //     $data['indirect_remarks'] ?? [],
    //     false
    // );

    $this->notify_finance_reviewer_on_submission($projectId);

    return $projectId;
}
public function create_project_submission__OLD($data)
{
    $settings = $this->get_approval_settings();

    $engagementType = $this->get_engagement_type_row($data['engagement_type_id']);

    if (!$engagementType) {
        return false;
    }

    $proposalName = trim($data['proposal_name'] ?? '');
    $clientName   = trim($data['client_name'] ?? '');
    $mode         = $data['engagement_mode'] ?? '';

    if ($proposalName === '' || $clientName === '') {
        return false;
    }

    if (!in_array($mode, ['bidding', 'non_bidding'], true)) {
        return false;
    }

if ($mode === 'bidding') {
    $programmePercent = (float)($data['display_sme'] ?? 0);
    $otherPercent     = (float)($data['display_admin'] ?? 0);
    $profitPercent    = (float)($data['display_profit'] ?? 0);

    $totalPercent = round($programmePercent + $otherPercent + $profitPercent, 2);

    if ($totalPercent != 100.00) {
        return false;
    }

    if ($programmePercent > $profitPercent) {
        return false;
    }
} else {
    $programmePercent = (float)$engagementType['non_bidding_sme'];
    $otherPercent     = (float)$engagementType['non_bidding_admin'];
    $profitPercent    = (float)$engagementType['non_bidding_profit'];
}

    $programmeTotal = $this->calculate_items_total(
        $data['programme_name'] ?? [],
        $data['programme_qty'] ?? [],
        $data['programme_frequency'] ?? [],
        $data['programme_rate'] ?? [],
        true
    );

    $otherRelatedTotal = $this->calculate_items_total(
        $data['other_name'] ?? [],
        $data['other_qty'] ?? [],
        $data['other_frequency'] ?? [],
        $data['other_rate'] ?? [],
        true
    );

    $indirectTotal = $this->calculate_items_total(
        $data['indirect_name'] ?? [],
        $data['indirect_qty'] ?? [],
        [],
        $data['indirect_rate'] ?? [],
        false
    );

$A = $programmeTotal;
$B = $otherRelatedTotal;
$C = $indirectTotal;
$indirectRate = $profitPercent / 100;

// Excel logic
//$indirectCalculated = ($A + $B) * $indirectRate;
$indirectCalculated = $C;
$beforeProfit = $A + $B + $indirectCalculated;

// using same third setting as the configured rate bucket
$profitRate = $indirectRate;
$profitAmount = 0;

if ($profitRate > 0 && $profitRate < 1) {
    $profitAmount = ($beforeProfit / (1 - $profitRate)) * $profitRate;
}

$overallBaseTotal = round($programmeTotal + $otherRelatedTotal + $indirectTotal, 2);

$profitInceif = 0;
$totalContractValue = $overallBaseTotal;
$sstAmount = 0;
$grandTotal = $overallBaseTotal;
$validationWarning = 0;

$divisorPercent = $programmePercent + $otherPercent;

if ($overallBaseTotal > 0 && $divisorPercent > 0) {
    $profitInceif = ($overallBaseTotal / ($divisorPercent / 100)) * ($profitPercent / 100);
    $totalContractValue = $overallBaseTotal + $profitInceif;
    $sstAmount = $totalContractValue * (((float)$engagementType['sst_percent']) / 100);
    $grandTotal = $totalContractValue + $sstAmount;
}

// warning starts only when B has value
if ($totalContractValue > 0 && $otherRelatedTotal > 0) {
    $actualProgrammePct = round(($programmeTotal / $totalContractValue) * 100, 2);
    $actualOtherPct     = round(($otherRelatedTotal / $totalContractValue) * 100, 2);
    $actualIndirectPct  = round(($indirectTotal / $totalContractValue) * 100, 2);

    if ($actualProgrammePct > round($programmePercent, 2)) {
        $validationWarning = 1;
    }

    if ($actualOtherPct > round($otherPercent, 2)) {
        $validationWarning = 1;
    }

    // C uses admin rule as requested
    if ($actualIndirectPct > round($otherPercent, 2)) {
        $validationWarning = 1;
    }
}

   $projectInsert = [
    'proposal_name'             => $proposalName,
    'client_name'               => $clientName,
    'client_category_id'        => $data['client_category_id'],
    'engagement_type_id'        => $data['engagement_type_id'],
    'engagement_mode'           => $mode,
    //'sst_percent'               => (float)$engagementType['sst_percent'],
    'sst_percent' => $sstPercent,

    'bidding_sme_percent'       => $programmePercent,
    'bidding_admin_percent'     => $otherPercent,
 'bidding_profit_percent' => $profitPercent,

    'submitted_by'              => get_staff_user_id(),
    'finance_reviewer_staff_id' => $settings['finance_reviewer_staff_id'] ?? null,
    'hod_approver_staff_id'     => $settings['hod_approver_staff_id'] ?? null,

  

    'programme_total'    => round($programmeTotal, 2),
'other_related_total'=> round($otherRelatedTotal, 2),
'indirect_total'     => round($indirectTotal, 2),
'overall_total'      => round($totalContractValue, 2),

    'validation_warning'        => $validationWarning,
    'validation_note'           => $validationWarning ? 'Submitted with percentage warning. Subject to approval.' : null,

    'finance_status'            => 'pending',
    'hod_status'                => 'pending',
    'overall_status'            => 'submitted',
    'updated_at'                => date('Y-m-d H:i:s'),
];

    $this->db->insert(db_prefix() . 'project_costing_projects', $projectInsert);
    $projectId = $this->db->insert_id();

    if (!$projectId) {
        return false;
    }

    $this->save_project_items(
        $projectId,
        'programme',
        $data['programme_name'] ?? [],
        $data['programme_qty'] ?? [],
        $data['programme_frequency'] ?? [],
        $data['programme_rate'] ?? [],
        $data['programme_remarks'] ?? [],
        true
    );

    $this->save_project_items(
        $projectId,
        'other_related',
        $data['other_name'] ?? [],
        $data['other_qty'] ?? [],
        $data['other_frequency'] ?? [],
        $data['other_rate'] ?? [],
        $data['other_remarks'] ?? [],
        true
    );

    $this->save_project_items(
        $projectId,
        'indirect',
        $data['indirect_name'] ?? [],
        $data['indirect_qty'] ?? [],
        [],
        $data['indirect_rate'] ?? [],
        $data['indirect_remarks'] ?? [],
        false
    );
    $this->notify_finance_reviewer_on_submission($projectId);

    return $projectId;
}


private function calculate_items_total_with_rules(
    $sectionType,
    $names,
    $qtys,
    $frequencies,
    $rates,
    $hasFrequency = true,
    $expertTypes = [],
    $jobGrades = [],
    $raLevels = []
) {
    $total = 0;

    foreach ($names as $index => $name) {
        $itemName = trim($name ?? '');
        if ($itemName === '') {
            continue;
        }

        $qty       = isset($qtys[$index]) ? (float)$qtys[$index] : 1;
        $rate      = isset($rates[$index]) ? (float)$rates[$index] : 0;
        $frequency = $hasFrequency ? (isset($frequencies[$index]) ? (float)$frequencies[$index] : 1) : 0;

        $expertType = isset($expertTypes[$index]) ? trim($expertTypes[$index]) : null;
        $jobGrade   = isset($jobGrades[$index]) ? trim($jobGrades[$index]) : null;
        $raLevel    = isset($raLevels[$index]) ? trim($raLevels[$index]) : null;

        if ($sectionType === 'other_related' && $itemName === 'Research Assistant') {
            $qty = 1;

            if ($raLevel === 'Degree') {
                $rate = 1500;
            } elseif ($raLevel === 'Master') {
                $rate = 2000;
            } elseif ($raLevel === 'PhD') {
                $rate = 2500;
            }
        }

        if ($qty <= 0) {
            $qty = 1;
        }

        if ($hasFrequency && $frequency <= 0) {
            $frequency = 1;
        }

        $amount = $hasFrequency ? ($qty * $frequency * $rate) : ($qty * $rate);
        $total += round($amount, 2);
    }

    return round($total, 2);
}
private function calculate_items_total($names, $qtys, $frequencies, $rates, $hasFrequency = true)
{
    $total = 0;

    foreach ($names as $index => $name) {
        $itemName = trim($name);
        if ($itemName === '') {
            continue;
        }

        $qty = isset($qtys[$index]) ? (float)$qtys[$index] : 0;
        $rate = isset($rates[$index]) ? (float)$rates[$index] : 0;
        $frequency = $hasFrequency ? (isset($frequencies[$index]) ? (float)$frequencies[$index] : 1) : 1;

        if ($qty <= 0) {
            $qty = 1;
        }

        if ($frequency <= 0) {
            $frequency = 1;
        }

        $amount = $qty * $frequency * $rate;
        $total += $amount;
    }

    return round($total, 2);
}
private function save_project_items(
    $projectId,
    $sectionType,
    $names,
    $qtys,
    $frequencies,
    $rates,
    $remarks,
    $hasFrequency = true,
    $expertTypes = [],
    $jobGrades = [],
    $raLevels = []
) {
    foreach ($names as $index => $name) {
        $itemName = trim($name ?? '');
        if ($itemName === '') {
            continue;
        }

        $qty       = isset($qtys[$index]) ? (float) $qtys[$index] : 1;
        $rate      = isset($rates[$index]) ? (float) $rates[$index] : 0;
        $frequency = $hasFrequency ? (isset($frequencies[$index]) ? (float) $frequencies[$index] : 1) : 0;
        $remark    = isset($remarks[$index]) ? trim($remarks[$index]) : null;

        $expertType = isset($expertTypes[$index]) ? trim($expertTypes[$index]) : null;
        $jobGrade   = isset($jobGrades[$index]) ? trim($jobGrades[$index]) : null;
        $raLevel    = isset($raLevels[$index]) ? trim($raLevels[$index]) : null;

        // SME logic
        if ($sectionType === 'programme') {
            if (!in_array($expertType, ['Internal', 'External'], true)) {
                $expertType = null;
            }

            if ($expertType !== 'Internal') {
                $jobGrade = null;
            } elseif ($jobGrade === '') {
                $jobGrade = null;
            }
        } else {
            $expertType = null;
            $jobGrade   = null;
        }

        // Admin Research Assistant logic
        if ($sectionType === 'other_related') {
            if ($itemName === 'Research Assistant') {
                if (!in_array($raLevel, ['Degree', 'Master', 'PhD'], true)) {
                    $raLevel = null;
                }

                // Force qty = 1 for Research Assistant
                $qty = 1;

                // Auto-set rate by qualification
                if ($raLevel === 'Degree') {
                    $rate = 1500;
                } elseif ($raLevel === 'Master') {
                    $rate = 2000;
                } elseif ($raLevel === 'PhD') {
                    $rate = 2500;
                }
            } else {
                $raLevel = null;
            }
        } else {
            $raLevel = null;
        }

        if ($qty <= 0) {
            $qty = 1;
        }

        if ($hasFrequency && $frequency <= 0) {
            $frequency = 1;
        }

        $amount = $hasFrequency
            ? ($qty * $frequency * $rate)
            : ($qty * $rate);

        $this->db->insert(db_prefix() . 'project_costing_project_items', [
            'project_id'   => $projectId,
            'section_type' => $sectionType,
            'item_name'    => $itemName,
            'qty'          => $qty,
            'frequency'    => $hasFrequency ? $frequency : 0,
            'rate'         => $rate,
            'amount'       => round($amount, 2),
            'remarks'      => $remark,
            'expert_type'  => $expertType,
            'job_grade'    => $jobGrade,
            'ra_level'     => $raLevel,
        ]);
    }
}
private function save_project_items__OLD($projectId, $sectionType, $names, $qtys, $frequencies, $rates, $remarks, $hasFrequency = true)
{
    foreach ($names as $index => $name) {
        $itemName = trim($name);
        if ($itemName === '') {
            continue;
        }

        $qty = isset($qtys[$index]) ? (float)$qtys[$index] : 1;
        $rate = isset($rates[$index]) ? (float)$rates[$index] : 0;
        $frequency = $hasFrequency ? (isset($frequencies[$index]) ? (float)$frequencies[$index] : 1) : 0;
        $remark = isset($remarks[$index]) ? $remarks[$index] : null;

        if ($qty <= 0) {
            $qty = 1;
        }

        if ($hasFrequency && $frequency <= 0) {
            $frequency = 1;
        }

        $amount = $hasFrequency ? ($qty * $frequency * $rate) : ($qty * $rate);

        $this->db->insert(db_prefix() . 'project_costing_project_items', [
            'project_id'    => $projectId,
            'section_type'  => $sectionType,
            'item_name'     => $itemName,
            'qty'           => $qty,
            'frequency'     => $hasFrequency ? $frequency : 0,
            'rate'          => $rate,
            'amount'        => round($amount, 2),
            'remarks'       => $remark,
        ]);
    }
}


public function get_finance_projects($staffId)
{
    $this->db->select('p.*, cc.name as client_category_name, et.name as engagement_type_name');
    $this->db->from(db_prefix() . 'project_costing_projects p');
    $this->db->join(db_prefix() . 'project_costing_client_categories cc', 'cc.id = p.client_category_id', 'left');
    $this->db->join(db_prefix() . 'project_costing_engagement_types et', 'et.id = p.engagement_type_id', 'left');
    $this->db->where('p.finance_reviewer_staff_id', $staffId);
    $this->db->order_by('p.id', 'desc');
    return $this->db->get()->result_array();
}

public function get_hod_projects($staffId)
{
    $this->db->select('p.*, cc.name as client_category_name, et.name as engagement_type_name');
    $this->db->from(db_prefix() . 'project_costing_projects p');
    $this->db->join(db_prefix() . 'project_costing_client_categories cc', 'cc.id = p.client_category_id', 'left');
    $this->db->join(db_prefix() . 'project_costing_engagement_types et', 'et.id = p.engagement_type_id', 'left');
    $this->db->where('p.hod_approver_staff_id', $staffId);
    $this->db->order_by('p.id', 'desc');
    return $this->db->get()->result_array();
}

public function delete_project($id)
{
    $this->db->where('project_id', $id);
    $this->db->delete(db_prefix() . 'project_costing_project_items');

    $this->db->where('id', $id);
    return $this->db->delete(db_prefix() . 'project_costing_projects');
}


public function update_project_submission($id, $data)
{
    $project = $this->get_project($id);

    if (!$project) {
        return false;
    }

    $engagementType = $this->get_engagement_type_row($data['engagement_type_id'] ?? 0);
    $settings       = $this->get_approval_settings();

    if (!$engagementType) {
        return false;
    }

    $proposalName = trim($data['proposal_name'] ?? '');
    $clientName   = trim($data['client_name'] ?? '');
    $mode         = $data['engagement_mode'] ?? '';

    if ($proposalName === '' || $clientName === '') {
        return false;
    }

    if (!in_array($mode, ['bidding', 'non_bidding'], true)) {
        return false;
    }

    // Percentages
    if ($mode === 'bidding') {
        $programmePercent = (float)($data['display_sme'] ?? 0);
        $otherPercent     = (float)($data['display_admin'] ?? 0);
        $profitPercent    = (float)($data['display_profit'] ?? 0);

        $totalPercent = round($programmePercent + $otherPercent + $profitPercent, 2);

        if ($totalPercent != 100.00) {
            return false;
        }

        // SME % cannot be more than Profit %
        if ($programmePercent > $profitPercent) {
            return false;
        }
    } else {
        $programmePercent = (float)$engagementType['non_bidding_sme'];
        $otherPercent     = (float)$engagementType['non_bidding_admin'];
        $profitPercent    = (float)$engagementType['non_bidding_profit'];
    }

    $sstPercent = isset($data['display_sst']) ? (float)$data['display_sst'] : null;

if (!in_array((string)$sstPercent, ['0', '6', '8'], true) && !in_array($sstPercent, [0.0, 6.0, 8.0], true)) {
    return false;
}

    //New
    $programmeTypes      = $data['programme_type'] ?? [];
$programmeJobGrades  = $data['programme_job_grade'] ?? [];
$otherNames          = $data['other_name'] ?? [];
$otherRaLevels       = $data['other_ra_level'] ?? [];

// Validate SME Internal -> Job Grade required
foreach (($data['programme_name'] ?? []) as $i => $name) {
    $itemName = trim($name ?? '');
    if ($itemName === '') {
        continue;
    }

    $expertType = trim($programmeTypes[$i] ?? '');
    $jobGrade   = trim($programmeJobGrades[$i] ?? '');

    if ($expertType === 'Internal' && $jobGrade === '') {
        return false;
    }
}

// Validate Research Assistant -> RA Level required
foreach ($otherNames as $i => $name) {
    $itemName = trim($name ?? '');
    if ($itemName === '') {
        continue;
    }

    $raLevel = trim($otherRaLevels[$i] ?? '');

    if ($itemName === 'Research Assistant' && !in_array($raLevel, ['Degree', 'Master', 'PhD'], true)) {
        return false;
    }
}

    // Totals
  $programmeTotal = $this->calculate_items_total_with_rules(
    'programme',
    $data['programme_name'] ?? [],
    $data['programme_qty'] ?? [],
    $data['programme_frequency'] ?? [],
    $data['programme_rate'] ?? [],
    true,
    $data['programme_type'] ?? [],
    $data['programme_job_grade'] ?? [],
    []
);

$otherRelatedTotal = $this->calculate_items_total_with_rules(
    'other_related',
    $data['other_name'] ?? [],
    $data['other_qty'] ?? [],
    $data['other_frequency'] ?? [],
    $data['other_rate'] ?? [],
    true,
    [],
    [],
    $data['other_ra_level'] ?? []
);

    $indirectTotal = $this->calculate_items_total(
        $data['indirect_name'] ?? [],
        $data['indirect_qty'] ?? [],
        [],
        $data['indirect_rate'] ?? [],
        false
    );

    // Contract calculation
    $overallBaseTotal   = round($programmeTotal + $otherRelatedTotal + $indirectTotal, 2);
    $profitInceif       = 0;
    $totalContractValue = $overallBaseTotal;
    $sstAmount          = 0;
    $grandTotal         = $overallBaseTotal;
    $validationWarning  = 0;

    $divisorPercent = $programmePercent + $otherPercent;

    if ($overallBaseTotal > 0 && $divisorPercent > 0) {
        $profitInceif = ($overallBaseTotal / ($divisorPercent / 100)) * ($profitPercent / 100);
        $totalContractValue = $overallBaseTotal + $profitInceif;
        //$sstAmount = $totalContractValue * (((float)$engagementType['sst_percent']) / 100);
        $sstAmount = $totalContractValue * ($sstPercent / 100);
        $grandTotal = $totalContractValue + $sstAmount;
    }

    // warning starts only when B has value
    if ($totalContractValue > 0 && $otherRelatedTotal > 0) {
        $actualProgrammePct = round(($programmeTotal / $totalContractValue) * 100, 2);
        $actualOtherPct     = round(($otherRelatedTotal / $totalContractValue) * 100, 2);
        $actualIndirectPct  = round(($indirectTotal / $totalContractValue) * 100, 2);

        if ($actualProgrammePct > round($programmePercent, 2)) {
            $validationWarning = 1;
        }

        if ($actualOtherPct > round($otherPercent, 2)) {
            $validationWarning = 1;
        }

        // C uses admin rule
        if ($actualIndirectPct > round($otherPercent, 2)) {
            $validationWarning = 1;
        }
    }

    // remove old items first
    $this->db->where('project_id', $id);
    $this->db->delete(db_prefix() . 'project_costing_project_items');

    $update = [
        'proposal_name'             => $proposalName,
        'client_name'               => $clientName,
        'client_category_id'        => $data['client_category_id'],
        'engagement_type_id'        => $data['engagement_type_id'],
        'engagement_mode'           => $mode,
        //'sst_percent'               => (float)$engagementType['sst_percent'],
        'sst_percent' => $sstPercent,

        'bidding_sme_percent'       => $programmePercent,
        'bidding_admin_percent'     => $otherPercent,
        'bidding_profit_percent'    => $profitPercent,

        'submitted_by'              => get_staff_user_id(),
        'finance_reviewer_staff_id' => $settings['finance_reviewer_staff_id'] ?? null,
        'hod_approver_staff_id'     => $settings['hod_approver_staff_id'] ?? null,

        'programme_total'           => round($programmeTotal, 2),
        'other_related_total'       => round($otherRelatedTotal, 2),
        'indirect_total'            => round($indirectTotal, 2),
        'overall_total'             => round($totalContractValue, 2),

'indirect_total'       => round($indirectTotal, 2),
'profit_inceif'        => round($profitInceif, 2),
'total_contract_value' => round($totalContractValue, 2),
'sst_amount'           => round($sstAmount, 2),
'grand_total'          => round($grandTotal, 2),

        'validation_warning'        => $validationWarning,
        'validation_note'           => $validationWarning ? 'Submitted with percentage warning. Subject to approval.' : null,

        // reset workflow after edit
        'finance_status'            => 'pending',
        'finance_comment'           => null,
        'finance_approved_at'       => null,
        'hod_status'                => 'pending',
        'hod_comment'               => null,
        'hod_approved_at'           => null,
        'overall_status'            => 'submitted',
        'updated_at'                => date('Y-m-d H:i:s'),
    ];

    $this->db->where('id', $id);
    $this->db->update(db_prefix() . 'project_costing_projects', $update);


    $this->save_project_items(
    $id,
    'programme',
    $data['programme_name'] ?? [],
    $data['programme_qty'] ?? [],
    $data['programme_frequency'] ?? [],
    $data['programme_rate'] ?? [],
    $data['programme_remarks'] ?? [],
    true,
    $data['programme_type'] ?? [],
    $data['programme_job_grade'] ?? [],
    []
);

$this->save_project_items(
    $id,
    'other_related',
    $data['other_name'] ?? [],
    $data['other_qty'] ?? [],
    $data['other_frequency'] ?? [],
    $data['other_rate'] ?? [],
    $data['other_remarks'] ?? [],
    true,
    [],
    [],
    $data['other_ra_level'] ?? []
);

$this->save_project_items(
    $id,
    'indirect',
    $data['indirect_name'] ?? [],
    $data['indirect_qty'] ?? [],
    [],
    $data['indirect_rate'] ?? [],
    $data['indirect_remarks'] ?? [],
    false,
    [],
    [],
    []
);

    // $this->save_project_items(
    //     $id,
    //     'programme',
    //     $data['programme_name'] ?? [],
    //     $data['programme_qty'] ?? [],
    //     $data['programme_frequency'] ?? [],
    //     $data['programme_rate'] ?? [],
    //     $data['programme_remarks'] ?? [],
    //     true
    // );

    // $this->save_project_items(
    //     $id,
    //     'other_related',
    //     $data['other_name'] ?? [],
    //     $data['other_qty'] ?? [],
    //     $data['other_frequency'] ?? [],
    //     $data['other_rate'] ?? [],
    //     $data['other_remarks'] ?? [],
    //     true
    // );

    // $this->save_project_items(
    //     $id,
    //     'indirect',
    //     $data['indirect_name'] ?? [],
    //     $data['indirect_qty'] ?? [],
    //     [],
    //     $data['indirect_rate'] ?? [],
    //     $data['indirect_remarks'] ?? [],
    //     false
    // );

    return true;
}
public function update_project_submission__OLD($id, $data)
{
    $project = $this->get_project($id);

    if (!$project) {
        return false;
    }

    $engagementType = $this->get_engagement_type_row($data['engagement_type_id']);
    $settings       = $this->get_approval_settings();

    if (!$engagementType) {
        return false;
    }

    $proposalName = trim($data['proposal_name'] ?? '');
    $clientName   = trim($data['client_name'] ?? '');
    $mode         = $data['engagement_mode'] ?? '';

    if ($proposalName === '' || $clientName === '') {
        return false;
    }

    if (!in_array($mode, ['bidding', 'non_bidding'], true)) {
        return false;
    }

if ($mode === 'bidding') {
    $programmePercent = (float)($data['display_sme'] ?? 0);
    $otherPercent     = (float)($data['display_admin'] ?? 0);
    $profitPercent    = (float)($data['display_profit'] ?? 0);

    $totalPercent = round($programmePercent + $otherPercent + $profitPercent, 2);

    if ($totalPercent != 100.00) {
        return false;
    }

    if ($programmePercent > $profitPercent) {
        return false;
    }
} else {
    $programmePercent = (float)$engagementType['non_bidding_sme'];
    $otherPercent     = (float)$engagementType['non_bidding_admin'];
    $profitPercent    = (float)$engagementType['non_bidding_profit'];
}
    $programmeTotal = $this->calculate_items_total(
        $data['programme_name'] ?? [],
        $data['programme_qty'] ?? [],
        $data['programme_frequency'] ?? [],
        $data['programme_rate'] ?? [],
        true
    );

    $otherRelatedTotal = $this->calculate_items_total(
        $data['other_name'] ?? [],
        $data['other_qty'] ?? [],
        $data['other_frequency'] ?? [],
        $data['other_rate'] ?? [],
        true
    );

    $indirectTotal = $this->calculate_items_total(
        $data['indirect_name'] ?? [],
        $data['indirect_qty'] ?? [],
        [],
        $data['indirect_rate'] ?? [],
        false
    );

$overallBaseTotal = round($programmeTotal + $otherRelatedTotal + $indirectTotal, 2);

$profitInceif = 0;
$totalContractValue = $overallBaseTotal;
$sstAmount = 0;
$grandTotal = $overallBaseTotal;
$validationWarning = 0;

$divisorPercent = $programmePercent + $otherPercent;

if ($overallBaseTotal > 0 && $divisorPercent > 0) {
    $profitInceif = ($overallBaseTotal / ($divisorPercent / 100)) * ($indirectPercent / 100);
    $totalContractValue = $overallBaseTotal + $profitInceif;
    $sstAmount = $totalContractValue * (((float)$engagementType['sst_percent']) / 100);
    $grandTotal = $totalContractValue + $sstAmount;
}

// warning starts only when B has value
if ($totalContractValue > 0 && $otherRelatedTotal > 0) {
    $actualProgrammePct = round(($programmeTotal / $totalContractValue) * 100, 2);
    $actualOtherPct     = round(($otherRelatedTotal / $totalContractValue) * 100, 2);
    $actualIndirectPct  = round(($indirectTotal / $totalContractValue) * 100, 2);

    if ($actualProgrammePct > round($programmePercent, 2)) {
        $validationWarning = 1;
    }

    if ($actualOtherPct > round($otherPercent, 2)) {
        $validationWarning = 1;
    }

    // C uses admin rule as requested
    if ($actualIndirectPct > round($otherPercent, 2)) {
        $validationWarning = 1;
    }
}

    $this->db->where('project_id', $id);
    $this->db->delete(db_prefix() . 'project_costing_project_items');

    $update = [
       'proposal_name'             => $proposalName,
    'client_name'               => $clientName,
    'client_category_id'        => $data['client_category_id'],
    'engagement_type_id'        => $data['engagement_type_id'],
    'engagement_mode'           => $mode,
    'sst_percent'               => (float)$engagementType['sst_percent'],

    'bidding_sme_percent'       => $programmePercent,
    'bidding_admin_percent'     => $otherPercent,
    'bidding_profit_percent'    => $indirectPercent,

    'submitted_by'              => get_staff_user_id(),
    'finance_reviewer_staff_id' => $settings['finance_reviewer_staff_id'] ?? null,
    'hod_approver_staff_id'     => $settings['hod_approver_staff_id'] ?? null,

    'programme_total'    => round($programmeTotal, 2),
'other_related_total'=> round($otherRelatedTotal, 2),
'indirect_total'     => round($indirectTotal, 2),
'overall_total'      => round($totalContractValue, 2),

    'validation_warning'        => $validationWarning,
    'validation_note'           => $validationWarning ? 'Submitted with percentage warning. Subject to approval.' : null,

    'finance_status'            => 'pending',
    'hod_status'                => 'pending',
    'overall_status'            => 'submitted',
    'updated_at'                => date('Y-m-d H:i:s'),
    ];

    $this->db->where('id', $id);
    $this->db->update(db_prefix() . 'project_costing_projects', $update);

    $this->save_project_items(
        $id,
        'programme',
        $data['programme_name'] ?? [],
        $data['programme_qty'] ?? [],
        $data['programme_frequency'] ?? [],
        $data['programme_rate'] ?? [],
        $data['programme_remarks'] ?? [],
        true
    );

    $this->save_project_items(
        $id,
        'other_related',
        $data['other_name'] ?? [],
        $data['other_qty'] ?? [],
        $data['other_frequency'] ?? [],
        $data['other_rate'] ?? [],
        $data['other_remarks'] ?? [],
        true
    );

    $this->save_project_items(
        $id,
        'indirect',
        $data['indirect_name'] ?? [],
        $data['indirect_qty'] ?? [],
        [],
        $data['indirect_rate'] ?? [],
        $data['indirect_remarks'] ?? [],
        false
    );

    return true;
}


public function finance_decide($id, $action, $comment = null)
{
    $project = $this->get_project($id);

    if (!$project || $project['finance_status'] !== 'pending') {
        return false;
    }

    if (!in_array($action, ['approved', 'rejected'], true)) {
        return false;
    }

    $update = [
        'finance_status'      => $action,
        'finance_comment'     => $comment,
        'finance_approved_at' => date('Y-m-d H:i:s'),
        'updated_at'          => date('Y-m-d H:i:s'),
    ];

    if ($action === 'rejected') {
        $update['overall_status'] = 'rejected';
    } else {
        $update['overall_status'] = 'pending_hod';
    }

    $this->db->where('id', $id);
    $done = $this->db->update(db_prefix() . 'project_costing_projects', $update);

    if ($done) {
        if ($action === 'approved') {
            $this->notify_hod_on_finance_approval($id);
            $this->log_project_costing_activity('Finance approved Project Costing ID: ' . $id);
        } else {
            $this->notify_submitter_on_finance_rejection($id);
            $this->log_project_costing_activity('Finance rejected Project Costing ID: ' . $id);
        }
    }

    return $done;
}

public function hod_decide($id, $action, $comment = null)
{
    $project = $this->get_project($id);

    if (!$project || $project['finance_status'] !== 'approved' || $project['hod_status'] !== 'pending') {
        return false;
    }

    if (!in_array($action, ['approved', 'rejected'], true)) {
        return false;
    }

    $update = [
        'hod_status'      => $action,
        'hod_comment'     => $comment,
        'hod_approved_at' => date('Y-m-d H:i:s'),
        'updated_at'      => date('Y-m-d H:i:s'),
    ];

    if ($action === 'rejected') {
        $update['overall_status'] = 'rejected';
    } else {
        $update['overall_status'] = 'approved';
    }

    $this->db->where('id', $id);
    $done = $this->db->update(db_prefix() . 'project_costing_projects', $update);

    if ($done) {
        $this->notify_submitter_on_hod_decision($id, $action);
        $this->log_project_costing_activity('HOD ' . $action . ' Project Costing ID: ' . $id);
    }

    return $done;
}





private function copyRowStyleAdvanced($sheet, $sourceRow, $targetRow, $fromCol = 'A', $toCol = 'G')
{
    foreach (range($fromCol, $toCol) as $col) {
        $sheet->duplicateStyle(
            $sheet->getStyle($col . $sourceRow),
            $col . $targetRow
        );
        $sheet->setCellValue($col . $targetRow, $sheet->getCell($col . $sourceRow)->getValue());
    }

    $sheet->getRowDimension($targetRow)->setRowHeight(
        $sheet->getRowDimension($sourceRow)->getRowHeight()
    );
}

private function clearRowRange($sheet, $row, $fromCol = 'A', $toCol = 'G')
{
    foreach (range($fromCol, $toCol) as $col) {
        $sheet->setCellValue($col . $row, '');
    }
}

private function setCurrencyCell($sheet, $cell, $value)
{
    $sheet->setCellValue($cell, (float)$value);
    $sheet->getStyle($cell)->getNumberFormat()->setFormatCode('"RM"#,##0.00');
}

private function setPercentCell($sheet, $cell, $value)
{
    $sheet->setCellValue($cell, (float)$value);
    $sheet->getStyle($cell)->getNumberFormat()->setFormatCode('0.00%');
}

private function styleSummaryRow($sheet, $row, $fillColor = null, $bold = true, $borderStyle = 'thin')
{
    $range = "A{$row}:G{$row}";

    if ($fillColor) {
        $sheet->getStyle($range)->getFill()->setFillType(
            \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID
        );
        $sheet->getStyle($range)->getFill()->getStartColor()->setARGB($fillColor);
    }

    if ($bold) {
        $sheet->getStyle($range)->getFont()->setBold(true);
    }

    $style = $sheet->getStyle($range);
    $style->getBorders()->getAllBorders()->setBorderStyle(
        $borderStyle === 'medium'
            ? \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM
            : \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN
    );

    $style->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
}

private function styleDetailArea($sheet, $fromRow, $toRow)
{
    if ($toRow < $fromRow) {
        return;
    }

    $range = "A{$fromRow}:G{$toRow}";
    $sheet->getStyle($range)->getAlignment()->setVertical(
        \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER
    );
    $sheet->getStyle("B{$fromRow}:G{$toRow}")->getAlignment()->setWrapText(true);
}

private function deleteUnusedRows($sheet, $startRow, $templateCount, $actualCount, &$affectedRows = [])
{
    $toDelete = $templateCount - $actualCount;

    if ($toDelete > 0) {
        $deleteFrom = $startRow + $actualCount;
        $sheet->removeRow($deleteFrom, $toDelete);

        foreach ($affectedRows as $key => $rowNumber) {
            if ($rowNumber >= $deleteFrom) {
                $affectedRows[$key] = $rowNumber - $toDelete;
            }
        }
    }
}
public function export_project_excel_dynamic($project, $items, $showProfit = true)
{
    require_once(FCPATH . 'vendor/autoload.php');

    $templatePath = module_dir_path('project_costing', 'assets/templates/project_costing_template.xlsx');

    if (!file_exists($templatePath)) {
        show_error('Excel template file not found: ' . $templatePath);
    }

    $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($templatePath);
    $sheet = $spreadsheet->getActiveSheet();

    $programmeItems = $items['programme'] ?? [];
    $otherItems     = $items['other_related'] ?? [];
    $indirectItems  = $items['indirect'] ?? [];

    // ---------------------------------
    // Template row configuration
    // Adjust these only if template changes
    // ---------------------------------
    $rowAStart        = 9;
    $rowADetailSample = 10;
    $rowADefaultCount = 4;
    $rowATotal        = 15;

    $rowBHeader       = 16;
    $rowBStart        = 17;
    $rowBDetailSample = 17;
    $rowBDefaultCount = 4;
    $rowBTotal        = 21;

    $rowABTotal       = 22;
    $rowIndirect      = 23;
    $rowBeforeProfit  = 24;
    $rowProfit        = 25;
    $rowContract      = 26;
    $rowSst           = 27;
    $rowGrandTotal    = 28;

    // ---------------------------------
    // Dynamic row adjustment - Section A
    // ---------------------------------
    $programmeCount = count($programmeItems);
    $extraA = max(0, $programmeCount - $rowADefaultCount);

    if ($extraA > 0) {
        $sheet->insertNewRowBefore($rowATotal, $extraA);

        for ($i = 0; $i < $extraA; $i++) {
            $newRow = $rowATotal + $i;
            $this->copyRowStyleAdvanced($sheet, $rowADetailSample, $newRow);
            $this->clearRowRange($sheet, $newRow);
        }

        $rowATotal       += $extraA;
        $rowBHeader      += $extraA;
        $rowBStart       += $extraA;
        $rowBTotal       += $extraA;
        $rowABTotal      += $extraA;
        $rowIndirect     += $extraA;
        $rowBeforeProfit += $extraA;
        $rowProfit       += $extraA;
        $rowContract     += $extraA;
        $rowSst          += $extraA;
        $rowGrandTotal   += $extraA;
    } elseif ($programmeCount < $rowADefaultCount) {
        $affectedRows = [
            'rowATotal'       => $rowATotal,
            'rowBHeader'      => $rowBHeader,
            'rowBStart'       => $rowBStart,
            'rowBTotal'       => $rowBTotal,
            'rowABTotal'      => $rowABTotal,
            'rowIndirect'     => $rowIndirect,
            'rowBeforeProfit' => $rowBeforeProfit,
            'rowProfit'       => $rowProfit,
            'rowContract'     => $rowContract,
            'rowSst'          => $rowSst,
            'rowGrandTotal'   => $rowGrandTotal,
        ];

        $this->deleteUnusedRows($sheet, $rowAStart, $rowADefaultCount, $programmeCount, $affectedRows);

        $rowATotal       = $affectedRows['rowATotal'];
        $rowBHeader      = $affectedRows['rowBHeader'];
        $rowBStart       = $affectedRows['rowBStart'];
        $rowBTotal       = $affectedRows['rowBTotal'];
        $rowABTotal      = $affectedRows['rowABTotal'];
        $rowIndirect     = $affectedRows['rowIndirect'];
        $rowBeforeProfit = $affectedRows['rowBeforeProfit'];
        $rowProfit       = $affectedRows['rowProfit'];
        $rowContract     = $affectedRows['rowContract'];
        $rowSst          = $affectedRows['rowSst'];
        $rowGrandTotal   = $affectedRows['rowGrandTotal'];
    }

    // ---------------------------------
    // Dynamic row adjustment - Section B
    // ---------------------------------
    $otherCount = count($otherItems);
    $extraB = max(0, $otherCount - $rowBDefaultCount);

    if ($extraB > 0) {
        $sheet->insertNewRowBefore($rowBTotal, $extraB);

        for ($i = 0; $i < $extraB; $i++) {
            $newRow = $rowBTotal + $i;
            $this->copyRowStyleAdvanced($sheet, $rowBDetailSample, $newRow);
            $this->clearRowRange($sheet, $newRow);
        }

        $rowBTotal       += $extraB;
        $rowABTotal      += $extraB;
        $rowIndirect     += $extraB;
        $rowBeforeProfit += $extraB;
        $rowProfit       += $extraB;
        $rowContract     += $extraB;
        $rowSst          += $extraB;
        $rowGrandTotal   += $extraB;
    } elseif ($otherCount < $rowBDefaultCount) {
        $affectedRows = [
            'rowBTotal'       => $rowBTotal,
            'rowABTotal'      => $rowABTotal,
            'rowIndirect'     => $rowIndirect,
            'rowBeforeProfit' => $rowBeforeProfit,
            'rowProfit'       => $rowProfit,
            'rowContract'     => $rowContract,
            'rowSst'          => $rowSst,
            'rowGrandTotal'   => $rowGrandTotal,
        ];

        $this->deleteUnusedRows($sheet, $rowBStart, $rowBDefaultCount, $otherCount, $affectedRows);

        $rowBTotal       = $affectedRows['rowBTotal'];
        $rowABTotal      = $affectedRows['rowABTotal'];
        $rowIndirect     = $affectedRows['rowIndirect'];
        $rowBeforeProfit = $affectedRows['rowBeforeProfit'];
        $rowProfit       = $affectedRows['rowProfit'];
        $rowContract     = $affectedRows['rowContract'];
        $rowSst          = $affectedRows['rowSst'];
        $rowGrandTotal   = $affectedRows['rowGrandTotal'];
    }

    // ---------------------------------
    // Header values
    // ---------------------------------
    $sheet->setCellValue('A1', 'CLIENT:');
    $sheet->setCellValue('B1', $project['client_name']);
    $sheet->setCellValue('A2', 'Project type : ' . ucfirst(str_replace('_', ' ', $project['engagement_mode'])));
    $sheet->setCellValue('A3', 'Payment type : based on per day basis');
    $sheet->setCellValue('A4', 'TRAINING PROPOSAL : ' . $project['proposal_name']);
    $sheet->setCellValue('A5', 'PROJECT COSTING');

    // ---------------------------------
    // Write Section A
    // ---------------------------------
    $currentRow = $rowAStart;
    foreach ($programmeItems as $index => $item) {
        if ($index === 0) {
            $sheet->setCellValue("A{$currentRow}", '1');
        }

        $sheet->setCellValue("B{$currentRow}", $item['item_name']);
        $this->setCurrencyCell($sheet, "C{$currentRow}", $item['rate']);
        $sheet->setCellValue("D{$currentRow}", (float)$item['qty']);
        $sheet->setCellValue("E{$currentRow}", (float)$item['frequency']);
        $this->setCurrencyCell($sheet, "F{$currentRow}", $item['amount']);
        $sheet->setCellValue("G{$currentRow}", $item['remarks']);
        $currentRow++;
    }

    // ---------------------------------
    // Section B header
    // ---------------------------------
    $sheet->setCellValue("A{$rowBHeader}", '2');
    $sheet->setCellValue("B{$rowBHeader}", 'B. Other Related Cost (Additional Cost for F2F session)');

    // ---------------------------------
    // Write Section B
    // ---------------------------------
    $currentRow = $rowBStart;
    foreach ($otherItems as $item) {
        $sheet->setCellValue("B{$currentRow}", $item['item_name']);
        $this->setCurrencyCell($sheet, "C{$currentRow}", $item['rate']);
        $sheet->setCellValue("D{$currentRow}", (float)$item['qty']);
        $sheet->setCellValue("E{$currentRow}", (float)$item['frequency']);
        $this->setCurrencyCell($sheet, "F{$currentRow}", $item['amount']);
        $sheet->setCellValue("G{$currentRow}", $item['remarks']);
        $currentRow++;
    }

    // ---------------------------------
    // Totals and calculations
    // ---------------------------------
    $programmeTotal = (float)$project['programme_total'];
    $otherTotal     = (float)$project['other_related_total'];
    $indirectTotal  = (float)$project['indirect_total'];

    $programmePercent = (float)$project['bidding_sme_percent'];
    $otherPercent     = (float)$project['bidding_admin_percent'];
    $profitPercent    = (float)$project['bidding_profit_percent'];
    $sstPercent       = (float)$project['sst_percent'];

    $baseTotal = $programmeTotal + $otherTotal + $indirectTotal;

    $divisorPercent = $programmePercent + $otherPercent;
    $profitInceif = 0;

    if ($baseTotal > 0 && $divisorPercent > 0) {
        $profitInceif = ($baseTotal / ($divisorPercent / 100)) * ($profitPercent / 100);
    }

    $totalContractValue = $baseTotal + $profitInceif;
    $sstAmount = $totalContractValue * ($sstPercent / 100);
    $grandTotal = $totalContractValue + $sstAmount;

    $programmePctActual = $totalContractValue > 0 ? ($programmeTotal / $totalContractValue) : 0;
    $otherPctActual     = $totalContractValue > 0 ? ($otherTotal / $totalContractValue) : 0;
    $indirectPctActual  = $totalContractValue > 0 ? ($indirectTotal / $totalContractValue) : 0;
    $profitPctActual    = $totalContractValue > 0 ? ($profitInceif / $totalContractValue) : 0;
    $sstPctActual       = $totalContractValue > 0 ? ($sstAmount / $totalContractValue) : 0;
    $grandPctActual     = $totalContractValue > 0 ? ($grandTotal / $totalContractValue) : 0;

    // ---------------------------------
    // Summary rows
    // ---------------------------------
    $sheet->setCellValue("E{$rowATotal}", 'Total (A)');
    $this->setCurrencyCell($sheet, "F{$rowATotal}", $programmeTotal);
    $this->setPercentCell($sheet, "G{$rowATotal}", $programmePctActual);

    $sheet->setCellValue("E{$rowBTotal}", 'Total (B)');
    $this->setCurrencyCell($sheet, "F{$rowBTotal}", $otherTotal);
    $this->setPercentCell($sheet, "G{$rowBTotal}", $otherPctActual);

    $sheet->setCellValue("E{$rowABTotal}", 'Total (A +B)');
    $this->setCurrencyCell($sheet, "F{$rowABTotal}", $programmeTotal + $otherTotal);

    $sheet->setCellValue("A{$rowIndirect}", '3');
    $sheet->setCellValue("B{$rowIndirect}", 'Indirect Cost');
    $this->setCurrencyCell($sheet, "F{$rowIndirect}", $indirectTotal);
    $this->setPercentCell($sheet, "G{$rowIndirect}", $indirectPctActual);

    $sheet->setCellValue("B{$rowBeforeProfit}", 'Total before profit');
    $this->setCurrencyCell($sheet, "F{$rowBeforeProfit}", $baseTotal);

    if ($showProfit) {
        $sheet->setCellValue("A{$rowProfit}", '4');
        $sheet->setCellValue("B{$rowProfit}", 'Profit INCEIF');
        $this->setCurrencyCell($sheet, "F{$rowProfit}", $profitInceif);
        $this->setPercentCell($sheet, "G{$rowProfit}", $profitPctActual);

        $sheet->setCellValue("A{$rowContract}", '5');
        $sheet->setCellValue("B{$rowContract}", 'Total Contract Value');
        $this->setCurrencyCell($sheet, "F{$rowContract}", $totalContractValue);
        $this->setPercentCell($sheet, "G{$rowContract}", 1);

        $sheet->setCellValue("A{$rowSst}", '6');
        $sheet->setCellValue("B{$rowSst}", 'SST (' . number_format($sstPercent, 0) . '%)');
        $sheet->setCellValue("D{$rowSst}", $sstPercent / 100);
        $sheet->getStyle("D{$rowSst}")->getNumberFormat()->setFormatCode('0%');
        $this->setCurrencyCell($sheet, "F{$rowSst}", $sstAmount);
        $this->setPercentCell($sheet, "G{$rowSst}", $sstPctActual);

        $sheet->setCellValue("A{$rowGrandTotal}", '7');
        $sheet->setCellValue("B{$rowGrandTotal}", 'Total Contract Value + SST');
        $this->setCurrencyCell($sheet, "F{$rowGrandTotal}", $grandTotal);
        $this->setPercentCell($sheet, "G{$rowGrandTotal}", $grandPctActual);
    } else {
        $sheet->removeRow($rowProfit, 1);
        $rowContract--;
        $rowSst--;
        $rowGrandTotal--;

        $sheet->setCellValue("A{$rowContract}", '4');
        $sheet->setCellValue("B{$rowContract}", 'Total Contract Value');
        $this->setCurrencyCell($sheet, "F{$rowContract}", $totalContractValue);
        $this->setPercentCell($sheet, "G{$rowContract}", 1);

        $sheet->setCellValue("A{$rowSst}", '5');
        $sheet->setCellValue("B{$rowSst}", 'SST (' . number_format($sstPercent, 0) . '%)');
        $sheet->setCellValue("D{$rowSst}", $sstPercent / 100);
        $sheet->getStyle("D{$rowSst}")->getNumberFormat()->setFormatCode('0%');
        $this->setCurrencyCell($sheet, "F{$rowSst}", $sstAmount);
        $this->setPercentCell($sheet, "G{$rowSst}", $sstPctActual);

        $sheet->setCellValue("A{$rowGrandTotal}", '6');
        $sheet->setCellValue("B{$rowGrandTotal}", 'Total Contract Value + SST');
        $this->setCurrencyCell($sheet, "F{$rowGrandTotal}", $grandTotal);
        $this->setPercentCell($sheet, "G{$rowGrandTotal}", $grandPctActual);
    }

    // ---------------------------------
    // Visual formatting
    // ---------------------------------
    $lastRow = $rowGrandTotal;

    $sheet->getStyle('A1:G5')->getFont()->setBold(true);
    $sheet->getStyle('A1:G5')->getAlignment()->setVertical(
        \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER
    );
    $sheet->getStyle('A1:G5')->getAlignment()->setWrapText(true);

    $headerRow = 7;
    $sheet->getStyle("A{$headerRow}:G{$headerRow}")->getFont()->setBold(true);
    $sheet->getStyle("A{$headerRow}:G{$headerRow}")->getAlignment()->setHorizontal(
        \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER
    );
    $sheet->getStyle("A{$headerRow}:G{$headerRow}")->getAlignment()->setVertical(
        \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER
    );

    $this->styleDetailArea($sheet, $rowAStart, max($rowAStart, $rowATotal - 1));
    $this->styleDetailArea($sheet, $rowBStart, max($rowBStart, $rowBTotal - 1));

    $this->styleSummaryRow($sheet, $rowATotal, 'D9EAD3', true, 'thin');
    $this->styleSummaryRow($sheet, $rowBTotal, 'D9EAD3', true, 'thin');
    $this->styleSummaryRow($sheet, $rowABTotal, 'EDEDED', true, 'thin');
    $this->styleSummaryRow($sheet, $rowIndirect, 'EDEDED', true, 'thin');
    $this->styleSummaryRow($sheet, $rowBeforeProfit, 'E2F0D9', true, 'thin');

    if ($showProfit) {
        $this->styleSummaryRow($sheet, $rowProfit, 'FFF2CC', true, 'medium');
    }

    $this->styleSummaryRow($sheet, $rowContract, 'F4B183', true, 'medium');
    $this->styleSummaryRow($sheet, $rowSst, 'F4B183', true, 'thin');
    $this->styleSummaryRow($sheet, $rowGrandTotal, 'F4B183', true, 'medium');

    $sheet->getStyle("C{$rowAStart}:F{$lastRow}")->getAlignment()->setHorizontal(
        \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT
    );
    $sheet->getStyle("D{$rowAStart}:E{$lastRow}")->getAlignment()->setHorizontal(
        \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER
    );
    $sheet->getStyle("G{$rowAStart}:G{$lastRow}")->getAlignment()->setHorizontal(
        \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER
    );

    $sheet->getColumnDimension('A')->setWidth(6);
    $sheet->getColumnDimension('B')->setWidth(48);
    $sheet->getColumnDimension('C')->setWidth(16);
    $sheet->getColumnDimension('D')->setWidth(12);
    $sheet->getColumnDimension('E')->setWidth(16);
    $sheet->getColumnDimension('F')->setWidth(16);
    $sheet->getColumnDimension('G')->setWidth(28);

    $sheet->getStyle("G{$rowAStart}:G{$lastRow}")->getAlignment()->setWrapText(true);
    $sheet->getStyle("A1:G{$lastRow}")->getAlignment()->setWrapText(true);

    for ($r = 1; $r <= $lastRow; $r++) {
        if ($sheet->getRowDimension($r)->getRowHeight() < 20) {
            $sheet->getRowDimension($r)->setRowHeight(20);
        }
    }

    $sheet->freezePane('A8');

    // ---------------------------------
    // Output
    // ---------------------------------
    $filename = 'project_costing_' . $project['id'] . '.xlsx';

    while (ob_get_level()) {
        ob_end_clean();
    }

    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="' . $filename . '"');
    header('Cache-Control: max-age=0');

    $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
    $writer->save('php://output');
    exit;
}









private function get_staff_member($staffId)
{
    return $this->db
        ->where('staffid', $staffId)
        ->where('active', 1)
        ->get(db_prefix() . 'staff')
        ->row_array();
}

private function build_project_link($projectId)
{
    return admin_url('project_costing/project_costing/view/' . $projectId);
}

private function log_project_costing_activity($description)
{
    $this->db->insert(db_prefix() . 'activity_log', [
        'description' => $description,
        'date'        => date('Y-m-d H:i:s'),
        'staffid'     => get_staff_full_name(get_staff_user_id()),
       
    ]);
}

private function get_status_badge_html($status)
{
    $status = strtolower((string)$status);

    $bg = '#6c757d';
    if ($status === 'approved') {
        $bg = '#28a745';
    } elseif ($status === 'rejected') {
        $bg = '#dc3545';
    } elseif ($status === 'pending' || $status === 'submitted' || $status === 'pending_hod') {
        $bg = '#f0ad4e';
    }

    return '<span style="display:inline-block;padding:6px 12px;border-radius:14px;background:' . $bg . ';color:#ffffff;font-weight:700;font-size:12px;text-transform:uppercase;">'
        . html_escape($status) .
        '</span>';
}

private function build_project_email_html($heading, $intro, $project, $ctaLabel = 'View Submission')
{
    $link = $this->build_project_link($project['id']);
    $statusBadge = $this->get_status_badge_html($project['overall_status']);

    $submitterName = trim(($project['submitter_firstname'] ?? '') . ' ' . ($project['submitter_lastname'] ?? ''));
    $financeName   = trim(($project['finance_firstname'] ?? '') . ' ' . ($project['finance_lastname'] ?? ''));
    $hodName       = trim(($project['hod_firstname'] ?? '') . ' ' . ($project['hod_lastname'] ?? ''));

    $warningRow = '';
    if (!empty($project['validation_warning'])) {
        $warningRow = '
            <tr>
                <td style="padding:10px;border:1px solid #ddd;background:#fafafa;font-weight:700;">Validation Warning</td>
                <td style="padding:10px;border:1px solid #ddd;color:#d58512;font-weight:700;">
                    Yes - Submitted with percentage warning
                </td>
            </tr>';
    }

    $financeCommentRow = '';
    if (!empty($project['finance_comment'])) {
        $financeCommentRow = '
            <tr>
                <td style="padding:10px;border:1px solid #ddd;background:#fafafa;font-weight:700;">Finance Comment</td>
                <td style="padding:10px;border:1px solid #ddd;">' . nl2br(html_escape($project['finance_comment'])) . '</td>
            </tr>';
    }

    $hodCommentRow = '';
    if (!empty($project['hod_comment'])) {
        $hodCommentRow = '
            <tr>
                <td style="padding:10px;border:1px solid #ddd;background:#fafafa;font-weight:700;">HOD Comment</td>
                <td style="padding:10px;border:1px solid #ddd;">' . nl2br(html_escape($project['hod_comment'])) . '</td>
            </tr>';
    }

    return '
    <div style="font-family:Arial,Helvetica,sans-serif;font-size:14px;color:#333;line-height:1.6;">
        <h2 style="margin:0 0 15px;color:#1f3c5a;">' . html_escape($heading) . '</h2>
        <p style="margin:0 0 20px;">' . nl2br(html_escape($intro)) . '</p>

        <div style="margin:0 0 15px;">
            ' . $statusBadge . '
        </div>

        <table style="width:100%;border-collapse:collapse;margin:0 0 20px;">
            <tr>
                <td style="padding:10px;border:1px solid #ddd;background:#fafafa;font-weight:700;width:220px;">Proposal Name</td>
                <td style="padding:10px;border:1px solid #ddd;">' . html_escape($project['proposal_name']) . '</td>
            </tr>
            <tr>
                <td style="padding:10px;border:1px solid #ddd;background:#fafafa;font-weight:700;">Client</td>
                <td style="padding:10px;border:1px solid #ddd;">' . html_escape($project['client_name']) . '</td>
            </tr>
            <tr>
                <td style="padding:10px;border:1px solid #ddd;background:#fafafa;font-weight:700;">Project Type</td>
                <td style="padding:10px;border:1px solid #ddd;">' . html_escape(ucfirst(str_replace('_', ' ', $project['engagement_mode']))) . '</td>
            </tr>
            <tr>
                <td style="padding:10px;border:1px solid #ddd;background:#fafafa;font-weight:700;">Submitted By</td>
                <td style="padding:10px;border:1px solid #ddd;">' . html_escape($submitterName) . '</td>
            </tr>
            <tr>
                <td style="padding:10px;border:1px solid #ddd;background:#fafafa;font-weight:700;">Finance Reviewer</td>
                <td style="padding:10px;border:1px solid #ddd;">' . html_escape($financeName) . '</td>
            </tr>
            <tr>
                <td style="padding:10px;border:1px solid #ddd;background:#fafafa;font-weight:700;">HOD Approver</td>
                <td style="padding:10px;border:1px solid #ddd;">' . html_escape($hodName) . '</td>
            </tr>
            <tr>
                <td style="padding:10px;border:1px solid #ddd;background:#fafafa;font-weight:700;">Total Contract Value</td>
                <td style="padding:10px;border:1px solid #ddd;">RM ' . number_format((float)$project['overall_total'], 2) . '</td>
            </tr>
            <tr>
                <td style="padding:10px;border:1px solid #ddd;background:#fafafa;font-weight:700;">Date Created</td>
                <td style="padding:10px;border:1px solid #ddd;">' . _dt($project['datecreated']) . '</td>
            </tr>
            ' . $warningRow . '
            ' . $financeCommentRow . '
            ' . $hodCommentRow . '
        </table>

        <div style="margin:25px 0;">
            <a href="' . $link . '" style="display:inline-block;padding:12px 18px;background:#007bff;color:#ffffff;text-decoration:none;border-radius:6px;font-weight:700;">'
                . html_escape($ctaLabel) .
            '</a>
        </div>

        <p style="margin-top:25px;color:#666;">Regards,<br>Project Costing System</p>
    </div>';
}

private function send_project_costing_email($projectId, $recipientStaffId, $toEmail, $subject, $htmlMessage, $type, array $ccEmails = [])
{
    if (empty($toEmail)) {
        return false;
    }

    $this->load->library('email');

    $this->email->clear(true);
    $this->email->from(get_option('smtp_email') ?: get_option('companyemail'), get_option('companyname'));
    $this->email->to($toEmail);

    if (!empty($ccEmails)) {
        $this->email->cc($ccEmails);
    }

    $this->email->subject($subject);
    $this->email->message($htmlMessage);

    $sent = $this->email->send();

    $this->log_project_costing_notification(
        $projectId,
        $recipientStaffId,
        $toEmail . (!empty($ccEmails) ? ' | CC: ' . implode(', ', $ccEmails) : ''),
        $type,
        $subject,
        $htmlMessage,
        'email',
        $sent ? 'sent' : 'failed'
    );

    return $sent;
}





private function notify_finance_reviewer_on_submission($projectId)
{
    $project = $this->get_project($projectId);

    if (!$project || empty($project['finance_reviewer_staff_id'])) {
        return false;
    }

    $finance = $this->get_staff_member($project['finance_reviewer_staff_id']);
    if (!$finance || empty($finance['email'])) {
        return false;
    }

    $subject = 'New Project Costing Submitted - Finance Review Required';
    $intro   = 'A new project costing has been submitted and requires your finance review.';
    $html    = $this->build_project_email_html($subject, $intro, $project, 'Review Submission');

    $settings = $this->get_approval_settings();
    $ccEmails = $this->parse_cc_emails($settings['finance_cc_emails'] ?? '');

   $sent = $this->send_project_costing_email(
    $projectId,
    $finance['staffid'],
    $finance['email'],
    $subject,
    $html,
    'submission_to_finance',
    $ccEmails
);

    $this->send_project_costing_in_app_notification(
        $finance['staffid'],
        $projectId,
        'New project costing submitted and awaiting your finance review'
    );

    if ($sent) {
        $this->log_project_costing_activity('Project Costing Email Sent [Submission -> Finance Reviewer] for Project ID: ' . $projectId . ' to ' . $finance['email']);
    }

    return $sent;
}

private function notify_hod_on_finance_approval($projectId)
{
    $project = $this->get_project($projectId);

    if (!$project || empty($project['hod_approver_staff_id'])) {
        return false;
    }

    $hod = $this->get_staff_member($project['hod_approver_staff_id']);
    if (!$hod || empty($hod['email'])) {
        return false;
    }

    $subject = 'Project Costing Awaiting HOD Approval';
    $intro   = 'Finance has approved this project costing. It now requires your approval.';
    $html    = $this->build_project_email_html($subject, $intro, $project, 'Review Submission');

    $settings = $this->get_approval_settings();
    $ccEmails = $this->parse_cc_emails($settings['hod_cc_emails'] ?? '');

    $sent = $this->send_project_costing_email(
    $projectId,
    $hod['staffid'],
    $hod['email'],
    $subject,
    $html,
    'finance_approved_to_hod',
    $ccEmails
);

    $this->send_project_costing_in_app_notification(
        $hod['staffid'],
        $projectId,
        'Project costing approved by Finance and awaiting your HOD approval'
    );

    if ($sent) {
        $this->log_project_costing_activity('Project Costing Email Sent [Finance Approved -> HOD] for Project ID: ' . $projectId . ' to ' . $hod['email']);
    }

    return $sent;
}
public function get_notification_log($id)
{
    return $this->db
        ->where('id', $id)
        ->get(db_prefix() . 'project_costing_notifications')
        ->row_array();
}

public function resend_notification($notificationId)
{
    $notification = $this->get_notification_log($notificationId);

    if (!$notification) {
        return false;
    }

    $recipientEmail = $notification['recipient_email'];

    // strip any " | CC: ..." text from stored recipient log
    if (strpos($recipientEmail, ' | CC: ') !== false) {
        $parts = explode(' | CC: ', $recipientEmail);
        $recipientEmail = trim($parts[0]);
    }

    if (empty($recipientEmail)) {
        return false;
    }

    $this->load->library('email');

    $this->email->clear(true);
    $this->email->from(get_option('smtp_email') ?: get_option('companyemail'), get_option('companyname'));
    $this->email->to($recipientEmail);
    $this->email->subject($notification['subject']);
    $this->email->message($notification['message']);

    $sent = $this->email->send();

    $this->log_project_costing_notification(
        $notification['project_id'],
        $notification['recipient_staff_id'],
        $recipientEmail,
        $notification['notification_type'] . '_resend',
        $notification['subject'],
        $notification['message'],
        'email',
        $sent ? 'sent' : 'failed'
    );

    $this->log_project_costing_activity('Project Costing Notification Resent for Project ID: ' . $notification['project_id'] . ' to ' . $recipientEmail);

    return $sent;
}
private function notify_submitter_on_finance_rejection($projectId)
{
    $project = $this->get_project($projectId);

    if (!$project || empty($project['submitted_by'])) {
        return false;
    }

    $submitter = $this->get_staff_member($project['submitted_by']);
    if (!$submitter || empty($submitter['email'])) {
        return false;
    }

    $subject = 'Project Costing Rejected by Finance';
    $intro   = 'Your project costing submission has been rejected by the Finance Reviewer.';
    $html    = $this->build_project_email_html($subject, $intro, $project, 'View Decision');

    $sent = $this->send_project_costing_email(
        $projectId,
        $submitter['staffid'],
        $submitter['email'],
        $subject,
        $html,
        'finance_rejected_to_submitter'
    );

    $this->send_project_costing_in_app_notification(
        $submitter['staffid'],
        $projectId,
        'Your project costing has been rejected by Finance'
    );

    if ($sent) {
        $this->log_project_costing_activity('Project Costing Email Sent [Finance Rejected -> Submitter] for Project ID: ' . $projectId . ' to ' . $submitter['email']);
    }

    return $sent;
}

private function notify_submitter_on_hod_decision($projectId, $decision)
{
    $project = $this->get_project($projectId);

    if (!$project || empty($project['submitted_by'])) {
        return false;
    }

    $submitter = $this->get_staff_member($project['submitted_by']);
    if (!$submitter || empty($submitter['email'])) {
        return false;
    }

    $subject = 'Project Costing Decision - ' . ucfirst($decision);
    $intro   = 'Your project costing submission has been ' . strtolower($decision) . ' by the HOD Approver.';
    $html    = $this->build_project_email_html($subject, $intro, $project, 'View Decision');

    $sent = $this->send_project_costing_email(
        $projectId,
        $submitter['staffid'],
        $submitter['email'],
        $subject,
        $html,
        'hod_' . strtolower($decision) . '_to_submitter'
    );

    $this->send_project_costing_in_app_notification(
        $submitter['staffid'],
        $projectId,
        'Your project costing has been ' . strtolower($decision) . ' by the HOD approver'
    );

    if ($sent) {
        $this->log_project_costing_activity('Project Costing Email Sent [HOD ' . ucfirst($decision) . ' -> Submitter] for Project ID: ' . $projectId . ' to ' . $submitter['email']);
    }

    return $sent;
}


private function log_project_costing_notification($projectId, $recipientStaffId, $recipientEmail, $type, $subject, $message, $channel = 'email', $status = 'sent')
{
    $this->db->insert(db_prefix() . 'project_costing_notifications', [
        'project_id'          => $projectId,
        'recipient_staff_id'  => $recipientStaffId ?: null,
        'recipient_email'     => $recipientEmail,
        'notification_type'   => $type,
        'subject'             => $subject,
        'message'             => $message,
        'channel'             => $channel,
        'status'              => $status,
        'date_sent'           => date('Y-m-d H:i:s'),
        'sent_by'             => get_staff_user_id(),
    ]);
}

public function get_project_notifications($projectId)
{
    $this->db->select('n.*, s.firstname, s.lastname');
    $this->db->from(db_prefix() . 'project_costing_notifications n');
    $this->db->join(db_prefix() . 'staff s', 's.staffid = n.recipient_staff_id', 'left');
    $this->db->where('n.project_id', $projectId);
    $this->db->order_by('n.id', 'desc');

    return $this->db->get()->result_array();
}

private function send_project_costing_in_app_notification($staffId, $projectId, $message)
{
    if (empty($staffId)) {
        return false;
    }

    if (!function_exists('add_notification')) {
        return false;
    }

    add_notification([
        'description'     => $message,
        'touserid'        => $staffId,
        'fromuserid'      => get_staff_user_id(),
        'fromclientid'    => 0,
        'link'            => 'project_costing/project_costing/view/' . $projectId,
        'additional_data' => serialize([
            $projectId
        ]),
    ]);

    pusher_trigger_notification([$staffId]);

    return true;
}
    public function get_projects_for_expense_dropdown($staffId, $isAdmin = false)
{
    $this->db->select('p.id, p.proposal_name, p.client_name, p.overall_total, p.submitted_by');
    $this->db->from(db_prefix() . 'project_costing_projects p');

    if (!$isAdmin) {
        $this->db->where('p.submitted_by', $staffId);
    }

    $this->db->order_by('p.id', 'desc');

    return $this->db->get()->result_array();
}
public function get_project_costing_expense_ledger($projectCostingId)
{
    $this->db->select('e.id, e.expense_name, e.reference_no, e.amount, e.date, e.dateadded, e.note, c.name as category_name');
    $this->db->from(db_prefix() . 'expenses e');
    $this->db->join(db_prefix() . 'expenses_categories c', 'c.id = e.category', 'left');
    $this->db->where('e.project_costing_id', $projectCostingId);
    $this->db->order_by('e.date', 'desc');
    $this->db->order_by('e.id', 'desc');

    return $this->db->get()->result_array();
}


public function get_project_costing_expense_summary($projectCostingId, $excludeExpenseId = null)
{
    $project = $this->db
        ->where('id', $projectCostingId)
        ->get(db_prefix() . 'project_costing_projects')
        ->row_array();

    if (!$project) {
        return false;
    }

    $this->db->select_sum('amount');
    $this->db->from(db_prefix() . 'expenses');
    $this->db->where('project_costing_id', $projectCostingId);

    if (!empty($excludeExpenseId)) {
        $this->db->where('id !=', $excludeExpenseId);
    }

    $claimed = $this->db->get()->row();

    $claimedAmount = (float)($claimed->amount ?? 0);
    $allowedAmount = (float)$project['overall_total'];
    $remainingAmount = $allowedAmount - $claimedAmount;

    return [
        'proposal_name'    => $project['proposal_name'],
        'client_name'      => $project['client_name'],
        'engagement_mode'  => $project['engagement_mode'],
        'allowed_amount'   => $allowedAmount,
        'claimed_amount'   => $claimedAmount,
        'remaining_amount' => $remainingAmount,
    ];
}
public function get_project_costing_expense_summary__OLD($projectCostingId)
{
    $project = $this->db
        ->where('id', $projectCostingId)
        ->get(db_prefix() . 'project_costing_projects')
        ->row_array();

    if (!$project) {
        return false;
    }

    $claimed = $this->db
        ->select_sum('amount')
        ->where('project_costing_id', $projectCostingId)
        ->get(db_prefix() . 'expenses')
        ->row();

    $claimedAmount = (float)($claimed->amount ?? 0);
    $allowedAmount = (float)$project['overall_total'];
    $remainingAmount = $allowedAmount - $claimedAmount;

    return [
        'proposal_name'   => $project['proposal_name'],
        'client_name'     => $project['client_name'],
        'allowed_amount'  => $allowedAmount,
        'claimed_amount'  => $claimedAmount,
        'remaining_amount'=> $remainingAmount,
    ];
}
}