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

$aColumns = [
    db_prefix().'annual_external_engagements.id as id',
    db_prefix().'annual_external_engagements.staff_name as staff_name',
    db_prefix().'annual_external_engagements.staff_code as staff_code',
    db_prefix().'annual_external_engagements.department as department',
    db_prefix().'annual_external_engagements.submission_type as submission_type',
    'IFNULL(ec.eng_count,0) as eng_count',
    'IFNULL(ec.total_payment,0) as total_payment',
    db_prefix().'annual_external_engagements.submission_date as submission_date',
    db_prefix().'annual_external_engagements.created_at as created_at',
];

$sIndexColumn = 'id';
$sTable       = db_prefix().'annual_external_engagements';

$join = [
    'LEFT JOIN (
        SELECT engagement_id,
               COUNT(*) AS eng_count,
               SUM(IFNULL(payment,0)) AS total_payment
        FROM '.db_prefix().'annual_external_engagement_items
        GROUP BY engagement_id
    ) ec ON ec.engagement_id = '.db_prefix().'annual_external_engagements.id',
];

$where = [];

if (staff_cant('view', 'annual_external_engagement') && staff_can('view_own', 'annual_external_engagement')) {
    $where[] = 'AND '.db_prefix().'annual_external_engagements.staff_id = '.(int)get_staff_user_id();
}

$additionalSelect = [
    db_prefix().'annual_external_engagements.staff_id',
];

$result  = data_tables_init($aColumns, $sIndexColumn, $sTable, $join, $where, $additionalSelect);
$output  = $result['output'];
$rResult = $result['rResult'];

foreach ($rResult as $aRow) {
    $row = [];

    $id      = (int)$aRow['id'];
    $ownerId = (int)($aRow['staff_id'] ?? 0);
    $logged  = (int)get_staff_user_id();

    $canAll = staff_can('view', 'annual_external_engagement');
    $canOwn = staff_can('view_own', 'annual_external_engagement') && ($ownerId === $logged);

    $canEdit   = staff_can('edit', 'annual_external_engagement')   && ($canAll || $canOwn);
    $canDelete = staff_can('delete', 'annual_external_engagement') && ($canAll || $canOwn);

    // ID
    $row[] = '<span class="tw-font-medium">#'.$id.'</span>';

    // Staff Name (row options here)
    $viewUrl = admin_url('annual_external_engagement/view/'.$id);
    $nameCol = '<a href="'.$viewUrl.'" class="tw-font-medium">'.e($aRow['staff_name'] ?? '-').'</a>';
    $nameCol .= '<div class="row-options">';
    $nameCol .= '<a href="'.$viewUrl.'">'._l('view').'</a>';
    if ($canEdit)   $nameCol .= ' | <a href="'.admin_url('annual_external_engagement/edit/'.$id).'">'._l('edit').'</a>';
    if ($canDelete) $nameCol .= ' | <a href="'.admin_url('annual_external_engagement/delete/'.$id).'" class="_delete text-danger">'._l('delete').'</a>';
    $nameCol .= '</div>';
    $row[] = $nameCol;

    $row[] = e($aRow['staff_code'] ?? '-');
    $row[] = e($aRow['department'] ?? '-');
    $row[] = e($aRow['submission_type'] ?? '-');

    $row[] = (int)($aRow['eng_count'] ?? 0);

    $row[] = number_format((float)($aRow['total_payment'] ?? 0), 2);

    $row[] = !empty($aRow['submission_date']) ? e(_d($aRow['submission_date'])) : '-';
    $row[] = !empty($aRow['created_at']) ? e(_dt($aRow['created_at'])) : '-';

    $row['DT_RowClass'] = 'has-row-options';
    $output['aaData'][] = $row;
}
