<?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,
            '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);
    }




    //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']);

    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)$engagementType['bidding_sme'];
        $otherPercent     = (float)$engagementType['bidding_admin'];
        $indirectPercent  = (float)$engagementType['bidding_profit'];
        //$indirectPercent  = (float)$engagementType['bidding_profit'];
    } else {
        $programmePercent = (float)$engagementType['non_bidding_sme'];
        $otherPercent     = (float)$engagementType['non_bidding_admin'];
        $indirectPercent  = (float)$engagementType['non_bidding_profit'];
        //$indirectPercent  = (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 = $indirectPercent / 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)) * ($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;
    }
}

   $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'],

    '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->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
    );

    return $projectId;
}
public function create_project_submission__OLD_v1($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') {
        $smePercent    = (float)$engagementType['bidding_sme'];
        $adminPercent  = (float)$engagementType['bidding_admin'];
        $profitPercent = (float)$engagementType['bidding_profit'];
    } else {
        $smePercent    = (float)$engagementType['non_bidding_sme'];
        $adminPercent  = (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
    );

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

    $smeDecimal    = $smePercent / 100;
    $adminDecimal  = $adminPercent / 100;
    $profitDecimal = $profitPercent / 100;

    $divisor = $smeDecimal + $adminDecimal;

    if ($divisor <= 0) {
        return false;
    }

    $derivedProfitTotal = round((($programmeTotal + $otherRelatedTotal) / $divisor) * $profitDecimal, 2);
    $departmentTotal    = round($programmeTotal + $otherRelatedTotal + $indirectTotal + $derivedProfitTotal, 2);

    if ($departmentTotal <= 0) {
        return false;
    }

    $percentageSME   = round(($programmeTotal / $departmentTotal) * 100, 2);
    $percentageAdmin = round(($otherRelatedTotal / $departmentTotal) * 100, 2);
    $maxProfitAllowed = round($departmentTotal * ($profitPercent / 100), 2);

    if ($percentageSME > round($smePercent, 2)) {
        return false;
    }

    if ($percentageAdmin > round($adminPercent, 2)) {
        return false;
    }

    if (round($indirectTotal, 2) > $maxProfitAllowed) {
        return false;
    }

    $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'],

        'bidding_sme_percent'       => $smePercent,
        'bidding_admin_percent'     => $adminPercent,
        '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'             => $departmentTotal,

        '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
    );

    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;
    }

    $mode = $data['engagement_mode'];

    if ($mode === 'bidding') {
        $smePercent    = (float)$engagementType['bidding_sme'];
        $adminPercent  = (float)$engagementType['bidding_admin'];
        $profitPercent = (float)$engagementType['bidding_profit'];
    } else {
        $smePercent    = (float)$engagementType['non_bidding_sme'];
        $adminPercent  = (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
    );


    $contractTotal = $programmeTotal + $otherRelatedTotal + $indirectTotal;

if ($contractTotal > 0 && $otherRelatedTotal > 0) {
    $programmePct = ($programmeTotal / $contractTotal) * 100;
    $otherPct = ($otherRelatedTotal / $contractTotal) * 100;
    $indirectPct = ($indirectTotal / $contractTotal) * 100;

    if ($programmePct > $smePercent) {
        return false;
    }

    if ($otherPct > $adminPercent) {
        return false;
    }

    if ($indirectPct > $profitPercent) {
        return false;
    }
}

//     $overallTotal = $programmeTotal + $otherRelatedTotal + $indirectTotal;
//  $departmentTotal = 0;

// $smeDecimal = $smePercent / 100;
// $adminDecimal = $adminPercent / 100;
// $profitDecimal = $profitPercent / 100;

// $divisor = $smeDecimal + $adminDecimal;

// if ($divisor > 0) {
//     $derivedProfitTotal = (($programmeTotal + $otherRelatedTotal) / $divisor) * $profitDecimal;
//     $departmentTotal = $programmeTotal + $otherRelatedTotal + $indirectTotal + $derivedProfitTotal;
// }

// if ($departmentTotal > 0) {
//     $percentageSME = ($programmeTotal / $departmentTotal) * 100;
//     $percentageAdmin = ($otherRelatedTotal / $departmentTotal) * 100;
//     $maxProfitAllowed = $departmentTotal * ($profitPercent / 100);

//     if ($percentageSME > $smePercent) {
//         return false;
//     }

//     if ($percentageAdmin > $adminPercent) {
//         return false;
//     }

//     if ($indirectTotal > $maxProfitAllowed) {
//         return false;
//     }
// }

    $projectInsert = [
        'proposal_name'             => $data['proposal_name'],
        'client_name'               => $data['client_name'],
        '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'       => $smePercent,
        'bidding_admin_percent'     => $adminPercent,
        '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'           => $programmeTotal,
        'other_related_total'       => $otherRelatedTotal,
        'indirect_total'            => $indirectTotal,
        'overall_total'             => $overallTotal,

        '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
    );

    return $projectId;
}

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)
{
    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']);
    $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)$engagementType['bidding_sme'];
        $otherPercent     = (float)$engagementType['bidding_admin'];
        $indirectPercent  = (float)$engagementType['bidding_profit'];
    } else {
        $programmePercent = (float)$engagementType['non_bidding_sme'];
        $otherPercent     = (float)$engagementType['non_bidding_admin'];
        $indirectPercent  = (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 update_project_submission__OLD($id, $data)
{
    $project = $this->get_project($id);

    if (!$project) {
        return false;
    }

    // safest path: delete old items and re-save fresh
    $this->db->where('project_id', $id);
    $this->db->delete(db_prefix() . 'project_costing_project_items');

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

    if (!$engagementType) {
        return false;
    }

    $mode = $data['engagement_mode'];

    if ($mode === 'bidding') {
        $smePercent    = (float)$engagementType['bidding_sme'];
        $adminPercent  = (float)$engagementType['bidding_admin'];
        $profitPercent = (float)$engagementType['bidding_profit'];
    } else {
        $smePercent    = (float)$engagementType['non_bidding_sme'];
        $adminPercent  = (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
    );

    $overallTotal = $programmeTotal + $otherRelatedTotal + $indirectTotal;

    $update = [
        'proposal_name'             => trim($data['proposal_name']),
        'client_name'               => trim($data['client_name']),
        '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'       => $smePercent,
        'bidding_admin_percent'     => $adminPercent,
        'bidding_profit_percent'    => $profitPercent,

        'finance_reviewer_staff_id' => $settings['finance_reviewer_staff_id'] ?? null,
        'hod_approver_staff_id'     => $settings['hod_approver_staff_id'] ?? null,

        'programme_total'           => $programmeTotal,
        'other_related_total'       => $otherRelatedTotal,
        'indirect_total'            => $indirectTotal,
        'overall_total'             => $overallTotal,

        // reset flow when edited
        '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
    );

    $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);
    return $this->db->update(db_prefix() . 'project_costing_projects', $update);
}

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);
    return $this->db->update(db_prefix() . 'project_costing_projects', $update);
}





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;
}
}