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

$aColumns = [
    db_prefix().'coi_disclosures.id as id',
    db_prefix().'coi_disclosures.staff_name as staff_name',
    db_prefix().'coi_disclosures.staff_code as staff_code',
    db_prefix().'coi_disclosures.department as department',
    'IFNULL(yc.yes_count,0) as yes_count',
    db_prefix().'coi_disclosures.signed_date as signed_date',
    db_prefix().'coi_disclosures.created_at as created_at',
];


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

// Join a derived table that already aggregates yes_count
$join = [
    'LEFT JOIN (
        SELECT disclosure_id,
               SUM(CASE WHEN LOWER(answer) = "yes" THEN 1 ELSE 0 END) AS yes_count
        FROM '.db_prefix().'coi_disclosure_items
        GROUP BY disclosure_id
    ) yc ON yc.disclosure_id = '.db_prefix().'coi_disclosures.id',
];

$where = [];

// Permission filter (view_own)
if (staff_cant('view', 'coi_disclosure') && staff_can('view_own', 'coi_disclosure')) {
    $where[] = 'AND '.db_prefix().'coi_disclosures.staff_id = '.(int)get_staff_user_id();
}

$additionalSelect = [
    db_prefix().'coi_disclosures.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', 'coi_disclosure');
    $canOwn = staff_can('view_own', 'coi_disclosure') && ($ownerId === $logged);

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

    // ID column with row options
   // ID column (no row options)
$row[] = '<a href="' . admin_url('coi_disclosure/view/'.$id) . '" class="tw-font-medium">#' . $id . '</a>';

// Staff Name column with row options (like gift_policy_statement)
$name = $aRow['staff_name'] ?? '-';
$viewUrl = admin_url('coi_disclosure/view/' . $id);

$nameCol = '<a href="' . $viewUrl . '" class="tw-font-medium">' . e($name) . '</a>';
$nameCol .= '<div class="row-options">';
$nameCol .= '<a href="' . $viewUrl . '">' . _l('view') . '</a>';

if ($canEdit) {
    $nameCol .= ' | <a href="' . admin_url('coi_disclosure/edit/' . $id) . '">' . _l('edit') . '</a>';
}
if ($canDelete) {
    $nameCol .= ' | <a href="' . admin_url('coi_disclosure/delete/' . $id) . '" class="_delete text-danger">' . _l('delete') . '</a>';
}
$nameCol .= '</div>';

$row[] = $nameCol;


    // Staff ID
    $row[] = e($aRow['staff_code'] ?? '');

    // Department
    $row[] = e($aRow['department'] ?? '');

    // Yes count
    $yesCount = (int)($aRow['yes_count'] ?? 0);
    $row[] = $yesCount > 0
        ? '<span class="label label-danger">'.$yesCount.'</span>'
        : '<span class="label label-default">0</span>';

    // Signed Date
    $row[] = !empty($aRow['signed_date']) ? e(_d($aRow['signed_date'])) : '-';

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

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