<?php
declare(strict_types=1);

function dal_stmt(mysqli $con, string $sql): mysqli_stmt {
    $stmt = $con->prepare($sql);
    if (!$stmt) throw new RuntimeException('Unable to prepare report query.');
    return $stmt;
}

function dal_rows(mysqli $con, string $sql, string $types = '', array $params = []): array {
    $stmt = dal_stmt($con, $sql);
    if ($types !== '') $stmt->bind_param($types, ...$params);
    if (!$stmt->execute()) throw new RuntimeException('Unable to execute report query.');
    $rows = [];
    $result = $stmt->get_result();
    while ($row = $result->fetch_assoc()) $rows[] = $row;
    $stmt->close();
    return $rows;
}

function dal_table_exists(mysqli $con, string $table): bool {
    $stmt = dal_stmt($con, 'SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=?');
    $stmt->bind_param('s', $table); $stmt->execute();
    $ok = (bool)$stmt->get_result()->fetch_row(); $stmt->close();
    return $ok;
}

function dal_columns(mysqli $con, string $table): array {
    $rows = dal_rows($con, 'SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=?', 's', [$table]);
    return array_column($rows, 'COLUMN_NAME');
}

function dal_has(array $columns, string $name): bool { return in_array($name, $columns, true); }

function dal_int($value): int {
    $value = filter_var($value, FILTER_VALIDATE_INT);
    return $value === false || $value < 0 ? 0 : (int)$value;
}

function dal_date(string $value): ?string {
    $value = trim($value);
    $date = DateTimeImmutable::createFromFormat('!Y-m-d', $value);
    return $date && $date->format('Y-m-d') === $value ? $value : null;
}

function dal_valid_option(array $options, int $id): bool {
    if ($id === 0) return true;
    foreach ($options as $row) if ((int)$row['id'] === $id) return true;
    return false;
}

function dal_time_minutes(string $value): ?int {
    $value = trim($value);
    if ($value === '' || $value === '0' || $value === '00:00:00') return null;
    $stamp = strtotime($value);
    return $stamp === false ? null : ((int)date('H', $stamp) * 60) + (int)date('i', $stamp);
}

function dal_duration(string $in, string $out, string $entryDate, string $exitDate): int {
    $start = strtotime($entryDate . ' ' . $in);
    $endDate = $exitDate !== '' && $exitDate !== '0000-00-00' ? $exitDate : $entryDate;
    $end = strtotime($endDate . ' ' . $out);
    if ($start === false || $end === false) return 0;
    if ($end < $start) $end += 86400;
    return max(0, (int)round(($end - $start) / 60));
}

function dal_hhmm(int $minutes): string {
    return sprintf('%02d:%02d', intdiv(max(0, $minutes), 60), max(0, $minutes) % 60);
}

function dal_display_time(string $value): string {
    $stamp = strtotime($value);
    return $stamp === false ? $value : date('h:i A', $stamp);
}

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

function dal_filters(array $source): array {
    return [
        'date' => dal_date((string)($source['filter_date'] ?? '')) ?? date('Y-m-d'),
        'shift' => dal_int($source['filter_shift'] ?? 0),
        'department' => dal_int($source['filter_dept'] ?? 0),
        'payroll' => dal_int($source['filter_payrolllocation'] ?? 0),
        'terminal' => dal_int($source['filter_orglocation'] ?? 0),
        'branch' => dal_int($source['filter_branchlocation'] ?? 0),
        'subbranch' => dal_int($source['filt_subbranch'] ?? 0),
        'category' => dal_int($source['filt_cat'] ?? 0),
    ];
}

function dal_validate(array $filters, array $options): array {
    $errors = [];
    foreach ([
        ['shift','shifts','shift'], ['department','departments','department'],
        ['payroll','payrolls','payroll location'], ['terminal','locations','terminal location'],
        ['branch','locations','branch location'], ['subbranch','subbranches','sub branch'],
        ['category','categories','category'],
    ] as [$key,$list,$label]) {
        if (!dal_valid_option($options[$list], (int)$filters[$key])) $errors[] = 'Invalid or unauthorized ' . $label . '.';
    }
    return $errors;
}

function dal_report(mysqli $con, array $f): array {
    $where = ['e.status=1'];
    $types = ''; $params = [];
    foreach ([['e.depat','department'],['e.payroll_location','payroll'],['e.branch_location','branch'],['e.subbranch','subbranch'],['e.category','category']] as [$column,$key]) {
        if ((int)$f[$key] > 0) { $where[] = "$column=?"; $types .= 'i'; $params[] = (int)$f[$key]; }
    }
    $employeeSql = 'SELECT DISTINCT e.id,e.name,e.orderby,e.branch_location,o.name branch_name FROM jos_app_employee e LEFT JOIN jos_app_employee_locationlog el ON el.app_empid=e.id LEFT JOIN jos_app_orglocation o ON o.id=e.branch_location WHERE '.implode(' AND ',$where);
    if ((int)$f['terminal'] > 0) { $employeeSql .= ' AND el.orglocation=?'; $types .= 'i'; $params[]=(int)$f['terminal']; }
    $employeeSql .= ' ORDER BY e.orderby=0,e.orderby,e.name,e.id';
    $employees = dal_rows($con, $employeeSql, $types, $params);
    if (!$employees) return ['detail'=>[],'absent'=>[],'summary'=>['login'=>0,'absent'=>0,'weeklyoff'=>0,'holiday'=>0,'total'=>0]];

    $ids = array_map(static fn($r)=>(int)$r['id'],$employees);
    $marks = implode(',',array_fill(0,count($ids),'?'));
    $logTypes = str_repeat('i',count($ids)).'s';
    $logParams = array_merge($ids,[$f['date']]);
    $logWhere = ["a.empid IN ($marks)",'DATE(a.entrydate)=?'];
    if ((int)$f['shift'] > 0) { $logWhere[]='a.shiftid=?'; $logTypes.='i'; $logParams[]=(int)$f['shift']; }
    if ((int)$f['terminal'] > 0) { $logWhere[]='a.orglocation=?'; $logTypes.='i'; $logParams[]=(int)$f['terminal']; }
    $logs = dal_rows($con, 'SELECT a.*,s.shift_type,s.shortname,s.in_time shift_in,s.out_time shift_out,l.name location_name,l.gatepass FROM jos_app_attendance_log a LEFT JOIN jos_app_empshift s ON s.id=a.shiftid LEFT JOIN jos_app_orglocation l ON l.id=a.orglocation WHERE '.implode(' AND ',$logWhere).' ORDER BY STR_TO_DATE(a.in_time,"%l:%i %p"),a.id', $logTypes, $logParams);
    $byEmployee=[]; foreach($logs as $log)$byEmployee[(int)$log['empid']][]=$log;

    $holiday = false;
    if (dal_table_exists($con,'jos_app_holiday')) {
        $holidayRows=dal_rows($con,'SELECT id FROM jos_app_holiday WHERE DATE(date)=? LIMIT 1','s',[$f['date']]);
        $holiday=(bool)$holidayRows;
    }
    $leaveByEmployee=[];
    if (dal_table_exists($con,'jos_app_leavestatus_log')) {
        $leaveRows=dal_rows($con,"SELECT empid,status,leavetype FROM jos_app_leavestatus_log WHERE empid IN ($marks) AND DATE(leavedate)=? AND status IN (2,3) ORDER BY id DESC",$logTypes,$logParams=array_merge($ids,[$f['date']]));
        foreach($leaveRows as $row) if(!isset($leaveByEmployee[(int)$row['empid']]))$leaveByEmployee[(int)$row['empid']]=$row;
    }
    $weeklyOff = strtolower(date('l',strtotime($f['date']))) === 'sunday';
    $detail=[];$absent=[];$summary=['login'=>0,'absent'=>0,'weeklyoff'=>0,'holiday'=>0,'total'=>count($employees)];
    foreach($employees as $employee){
        $empLogs=$byEmployee[(int)$employee['id']]??[];
        if($empLogs){
            $normal=[];$gate=[];$worked=0;$gateMinutes=0;$notLogout=false;$shift=null;
            foreach($empLogs as $log){
                $shift=$shift??$log;
                $pair=[];
                if(trim((string)$log['in_time'])!=='')$pair[]=dal_display_time((string)$log['in_time']);
                if(trim((string)$log['out_time'])!=='')$pair[]=dal_display_time((string)$log['out_time']); else $notLogout=true;
                $mins=dal_duration((string)$log['in_time'],(string)$log['out_time'],substr((string)$log['entrydate'],0,10),substr((string)($log['exitdate']??''),0,10));
                $target=((int)($log['gatepass']??1)===0)?'gate':'normal';
                if($target==='gate'){$gate[]=['location'=>$log['location_name'],'pair'=>$pair];$gateMinutes+=$mins;}else{$normal[]=['location'=>$log['location_name'],'pair'=>$pair];$worked+=$mins;}
            }
            $summary['login']++;
            $detail[]=['employee'=>$employee,'logs'=>$normal,'gate_logs'=>$gate,'worked'=>$worked,'gate_minutes'=>$gateMinutes,'not_logout'=>$notLogout,'status'=>$notLogout?'Not logout':'Present','shift'=>$shift];
        }else{
            $status=isset($leaveByEmployee[(int)$employee['id']])?'Leave':($holiday?'Holiday':($weeklyOff?'Weeklyoff':'Absent'));
            if($status==='Absent')$summary['absent']++; elseif($status==='Weeklyoff')$summary['weeklyoff']++; elseif($status==='Holiday')$summary['holiday']++;
            $absent[]=['employee'=>$employee,'status'=>$status];
        }
    }
    usort($detail,static function($a,$b){$at=$a['logs'][0]['pair'][0]??'11:59 PM';$bt=$b['logs'][0]['pair'][0]??'11:59 PM';return strtotime($at)<=>strtotime($bt);});
    return ['detail'=>$detail,'absent'=>$absent,'summary'=>$summary];
}
