<?php
declare(strict_types=1);

require_once __DIR__ . '/daily-attendance-log-common.php';

function dml_options(mysqli $con): array {
    return [
        'payrolls' => dal_rows($con, 'SELECT id,name FROM jos_app_payrolllocation WHERE status=1 ORDER BY name,id'),
        'departments' => dal_rows($con, 'SELECT id,department name FROM jos_app_department ORDER BY department,id'),
        'categories' => dal_rows($con, 'SELECT id,designation_name name FROM jos_app_emp_category ORDER BY designation_name,id'),
        'branches' => dal_rows($con, 'SELECT id,name FROM jos_app_orglocation ORDER BY name,id'),
        'shifts' => dal_rows($con, 'SELECT id,COALESCE(NULLIF(shift_type,""),shortname) name FROM jos_app_empshift ORDER BY shift_type,id'),
    ];
}

function dml_filters(array $source): array {
    $fromSubmitted = array_key_exists('filter_fdate', $source);
    $toSubmitted = array_key_exists('filter_tdate', $source);
    $fromRaw = trim((string)($source['filter_fdate'] ?? ''));
    $toRaw = trim((string)($source['filter_tdate'] ?? ''));
    $fromDate = dal_date($fromRaw);
    $toDate = dal_date($toRaw);
    return [
        'from' => $fromDate ?? date('Y-m-d'),
        'to' => $toDate ?? date('Y-m-d'),
        'from_valid' => !$fromSubmitted || $fromDate !== null,
        'to_valid' => !$toSubmitted || $toDate !== null,
        'payroll' => dal_int($source['filter_payrolllocation'] ?? 0),
        'department' => dal_int($source['filter_department'] ?? 0),
        'category' => dal_int($source['filt_cat'] ?? 0),
        'branch' => dal_int($source['filter_orglocation'] ?? 0),
        'shift' => dal_int($source['filter_shift'] ?? 0),
    ];
}

function dml_validate(array $filters, array $options): array {
    $errors = [];
    if (!$filters['from_valid']) $errors[] = 'Enter a valid From Date.';
    if (!$filters['to_valid']) $errors[] = 'Enter a valid To Date.';
    if (!$filters['payroll']) $errors[] = 'Payroll Location is required.';
    if ($filters['from_valid'] && $filters['to_valid'] && strtotime($filters['from']) > strtotime($filters['to'])) $errors[] = 'From Date must be on or before To Date.';
    foreach ([
        ['payroll','payrolls','payroll location'], ['department','departments','department'],
        ['category','categories','category'], ['branch','branches','branch location'], ['shift','shifts','shift'],
    ] as [$key,$list,$label]) {
        if (!dal_valid_option($options[$list], (int)$filters[$key])) $errors[] = 'Invalid or unauthorized ' . $label . '.';
    }
    return $errors;
}

function dml_report(mysqli $con, array $f): array {
    $where = ['e.status=1', 'e.payroll_location=?', 'DATE(a.entrydate) BETWEEN ? AND ?'];
    $types = 'iss';
    $params = [$f['payroll'], $f['from'], $f['to']];
    foreach ([['e.depat','department'],['e.category','category'],['e.branch_location','branch'],['a.shiftid','shift']] as [$column,$key]) {
        if ($f[$key]) { $where[] = $column . '=?'; $types .= 'i'; $params[] = $f[$key]; }
    }
    $sql = 'SELECT a.*,e.id employee_id,e.name employee_name,e.designation designation_id,e.branch_location,d.designation,o.name branch_name '
         . 'FROM jos_app_attendance_log a '
         . 'INNER JOIN jos_app_employee e ON e.id=a.empid '
         . 'LEFT JOIN jos_app_designation d ON d.id=e.designation '
         . 'LEFT JOIN jos_app_orglocation o ON o.id=e.branch_location '
         . 'WHERE ' . implode(' AND ', $where) . ' '
         . 'ORDER BY STR_TO_DATE(a.in_time,"%l:%i %p"),a.entrydate,e.name,e.id,a.id';
    $candidates = dal_rows($con, $sql, $types, $params);
    if (!$candidates) return [];

    // The legacy join could repeat the same employee/day. Keep one business row per employee/day.
    $days = [];
    foreach ($candidates as $row) {
        $date = substr((string)$row['entrydate'], 0, 10);
        if ($date === '') continue;
        $key = (int)$row['employee_id'] . '|' . $date;
        if (!isset($days[$key])) $days[$key] = ['employee' => $row, 'date' => $date, 'punches' => []];
        $in = trim((string)($row['in_time'] ?? ''));
        $out = trim((string)($row['out_time'] ?? ''));
        if ($in !== '' && $in !== '00:00:00') $days[$key]['punches'][] = $in;
        if ($out !== '' && $out !== '00:00:00') $days[$key]['punches'][] = $out;
    }

    if (dal_table_exists($con, 'jos_app_attendance_log_temp')) {
        $ids = array_values(array_unique(array_map(static fn($r)=>(int)$r['employee_id'], $candidates)));
        $marks = implode(',', array_fill(0, count($ids), '?'));
        $columns = dal_columns($con, 'jos_app_attendance_log_temp');
        $dateColumn = dal_has($columns, 'date') ? 'date' : (dal_has($columns, 'entrydate') ? 'entrydate' : '');
        $timeColumn = dal_has($columns, 'time') ? 'time' : (dal_has($columns, 'in_time') ? 'in_time' : '');
        if ($dateColumn !== '' && $timeColumn !== '') {
            $tempWhere = ["empid IN ($marks)", "DATE(`$dateColumn`) BETWEEN ? AND ?"];
            if (dal_has($columns, 'type')) $tempWhere[] = 'COALESCE(type,0)<>4';
            $tempTypes = str_repeat('i', count($ids)) . 'ss';
            $tempParams = array_merge($ids, [$f['from'], $f['to']]);
            $tempRows = dal_rows($con, "SELECT empid,`$dateColumn` punch_date,`$timeColumn` punch_time FROM jos_app_attendance_log_temp WHERE " . implode(' AND ', $tempWhere), $tempTypes, $tempParams);
            foreach ($tempRows as $row) {
                $date = substr((string)$row['punch_date'], 0, 10);
                $key = (int)$row['empid'] . '|' . $date;
                $time = trim((string)$row['punch_time']);
                if (isset($days[$key]) && $time !== '' && $time !== '00:00:00') $days[$key]['punches'][] = $time;
            }
        }
    }

    $result = [];
    foreach ($days as $day) {
        if (count($day['punches']) < 1 || count($day['punches']) % 2 === 0) continue;
        usort($day['punches'], static fn($a,$b)=>(strtotime($a) ?: 0) <=> (strtotime($b) ?: 0));
        $result[] = $day;
    }
    return $result;
}

function dml_render(array $rows): void { ?>
<div class="overflow-x-auto"><table class="min-w-[1050px] w-full text-sm"><thead class="bg-slate-900 text-white"><tr><?php foreach (['Sr No','Employee Name','Date','Designation','Branch','Log'] as $heading): ?><th class="border border-slate-700 px-3 py-3 text-center font-bold"><?=h($heading)?></th><?php endforeach; ?></tr></thead><tbody class="divide-y divide-slate-100"><?php foreach ($rows as $index => $row): $employee=$row['employee']; ?><tr class="align-top"><td class="border p-3 text-center"><?=$index+1?></td><td class="border p-3 text-center font-bold"><?=h((string)$employee['employee_name'])?></td><td class="border p-3 text-center"><?=h(date('d-m-Y',strtotime($row['date'])))?></td><td class="border p-3 text-center"><?=h((string)($employee['designation'] ?? ''))?></td><td class="border p-3 text-center"><?=h((string)($employee['branch_name'] ?? ''))?></td><td class="border p-3"><div class="flex flex-wrap justify-center gap-2"><?php foreach ($row['punches'] as $i => $punch): ?><span class="rounded-md bg-slate-100 px-2.5 py-1 font-semibold text-slate-700"><?=h(($i%2===0?'In - ':'Out - ').dal_display_time((string)$punch))?></span><?php endforeach; ?></div></td></tr><?php endforeach; if (!$rows): ?><tr><td colspan="6" class="px-6 py-12 text-center font-semibold text-slate-400">No misspunch logs match the selected filters.</td></tr><?php endif; ?></tbody></table></div>
<?php }
