<?php
declare(strict_types=1);

function mfl_stmt(mysqli $con,string $sql):mysqli_stmt{$s=$con->prepare($sql);if(!$s)throw new RuntimeException('Unable to prepare report query.');return $s;}
function mfl_rows(mysqli $con,string $sql,string $types='',array $params=[]):array{$s=mfl_stmt($con,$sql);if($types!=='')$s->bind_param($types,...$params);if(!$s->execute())throw new RuntimeException('Unable to execute report query.');$out=[];$r=$s->get_result();while($x=$r->fetch_assoc())$out[]=$x;$s->close();return$out;}
function mfl_table(mysqli $con,string $name):bool{$r=mfl_rows($con,'SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=?','s',[$name]);return(bool)$r;}
function mfl_dates(string $from,string $to):array{$a=[];$d=new DateTimeImmutable($from);$end=new DateTimeImmutable($to);while($d<=$end){$a[]=$d->format('Y-m-d');$d=$d->modify('+1 day');}return$a;}
function mfl_hhmm(int $m):string{$m=max(0,$m);return sprintf('%02d:%02d',intdiv($m,60),$m%60);}
function mfl_clock(string $v):string{$t=strtotime($v);return$t===false?'':date('h:i A',$t);}
function mfl_minutes(string $v):?int{$t=strtotime($v);return trim($v)===''||$t===false?null:(int)date('H',$t)*60+(int)date('i',$t);}
function mfl_duration(string $in,string $out,string $entry,string $exit=''):int{$a=strtotime($entry.' '.$in);$ed=$exit!==''&&$exit!=='0000-00-00'?$exit:$entry;$b=strtotime($ed.' '.$out);if($a===false||$b===false||trim($in)===''||trim($out)==='')return 0;if($b<$a)$b+=86400;return max(0,(int)(($b-$a)/60));}
function mfl_ids($v):array{return array_values(array_unique(array_filter(array_map('intval',(array)$v),fn($x)=>$x>0)));}
function mfl_has(array $rows,int $id):bool{if($id===0)return true;foreach($rows as $r)if((int)$r['id']===$id)return true;return false;}
function mfl_options(mysqli $con):array{return[
 'employees'=>mfl_rows($con,'SELECT id,name,status,payroll_location FROM jos_app_employee ORDER BY name,id'),
 'months'=>mfl_rows($con,'SELECT id,month name FROM jos_app_mmonth ORDER BY id'),
 'years'=>mfl_rows($con,'SELECT id,year name FROM jos_app_year ORDER BY id DESC LIMIT 5'),
 'departments'=>mfl_rows($con,'SELECT id,department name FROM jos_app_department ORDER BY department,id'),
 'branches'=>mfl_rows($con,'SELECT id,name FROM jos_app_orglocation ORDER BY name,id'),
 'subbranches'=>mfl_table($con,'jos_app_subbranch')?mfl_rows($con,'SELECT id,subbranch name FROM jos_app_subbranch ORDER BY subbranch,id'):[],
 'payrolls'=>mfl_rows($con,'SELECT id,name FROM jos_app_payrolllocation WHERE status=1 ORDER BY name,id'),
 'categories'=>mfl_rows($con,'SELECT id,designation_name name FROM jos_app_emp_category ORDER BY designation_name,id')];}
function mfl_filters(array $s,bool $all=false):array{$limits=[20,40,50,100,200];$limit=(int)($s['filter_limit']??40);return[
 'employees'=>mfl_ids($s['filter_name1']??[]),'month'=>max(0,min(12,(int)($s['filt_month']??0))),'year'=>(int)($s['filt_year']??0),
 'department'=>max(0,(int)($s['filter_dept']??0)),'branch'=>max(0,(int)($s['filt_branch_location']??0)),'subbranch'=>max(0,(int)($s['filt_subbranch']??0)),
 'payroll'=>max(0,(int)($s['filt_payroll_location']??0)),'category'=>max(0,(int)($s['filt_cat']??0)),'status'=>in_array((int)($s['filt_emp']??1),[0,1,2],true)?(int)($s['filt_emp']??1):1,
 'limit'=>$all?100000:(in_array($limit,$limits,true)?$limit:40)];}
function mfl_validate(array $f,array $o):array{$e=[];if($f['month']<1)$e[]='Month is required.';if($f['year']<2000||$f['year']>2100)$e[]='Year is required.';foreach([['department','departments','department'],['branch','branches','branch location'],['subbranch','subbranches','sub branch'],['payroll','payrolls','payroll location'],['category','categories','category']]as[$k,$l,$n])if(!mfl_has($o[$l],$f[$k]))$e[]='Invalid or unauthorized '.$n.'.';$allowed=array_map('intval',array_column($o['employees'],'id'));foreach($f['employees']as$id)if(!in_array($id,$allowed,true))$e[]='Invalid or unauthorized employee.';return array_values(array_unique($e));}
function mfl_report(mysqli $con,array $f):array{
 $from=sprintf('%04d-%02d-01',$f['year'],$f['month']);$to=date('Y-m-t',strtotime($from));$dates=mfl_dates($from,$to);$where=['e.status=?'];$types='i';$p=[$f['status']];if($f['payroll']>0&&!$f['employees']){$where[]='e.payroll_location=?';$types.='i';$p[]=$f['payroll'];}
 foreach([['e.depat','department'],['e.branch_location','branch'],['e.subbranch','subbranch'],['e.category','category']]as[$c,$k])if($f[$k]>0){$where[]="$c=?";$types.='i';$p[]=$f[$k];}
 if($f['employees']){$where[]='e.id IN ('.implode(',',array_fill(0,count($f['employees']),'?')).')';$types.=str_repeat('i',count($f['employees']));array_push($p,...$f['employees']);}$types.='i';$p[]=$f['limit'];
 $emps=mfl_rows($con,'SELECT e.id,e.name,e.photo,e.orderby,d.department,b.name branch_name,pl.name payroll_name FROM jos_app_employee e LEFT JOIN jos_app_department d ON d.id=e.depat LEFT JOIN jos_app_orglocation b ON b.id=e.branch_location LEFT JOIN jos_app_payrolllocation pl ON pl.id=e.payroll_location WHERE '.implode(' AND ',$where).' ORDER BY e.orderby=0,e.orderby,e.name,e.id LIMIT ?',$types,$p);
 if(!$emps)return['from'=>$from,'to'=>$to,'dates'=>$dates,'rows'=>[]];$ids=array_map('intval',array_column($emps,'id'));$marks=implode(',',array_fill(0,count($ids),'?'));$lt=str_repeat('i',count($ids)).'ss';$lp=array_merge($ids,[$from,$to]);
 $logs=mfl_rows($con,'SELECT a.id,a.empid,DATE(a.entrydate) workdate,a.entrydate,a.exitdate,a.in_time,a.out_time,a.shiftid,a.orglocation,s.shortname,s.shift_type,s.in_time shift_in,s.out_time shift_out,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 a.empid IN ('.$marks.') AND DATE(a.entrydate) BETWEEN ? AND ? ORDER BY a.empid,a.entrydate,a.id',$lt,$lp);
 $temp=[];if(mfl_table($con,'jos_app_attendance_log_temp'))$temp=mfl_rows($con,'SELECT t.id,t.empid,DATE(t.date) workdate,t.date entrydate,t.exitdate,t.time,t.shiftid,t.orglocation FROM jos_app_attendance_log_temp t WHERE t.empid IN ('.$marks.') AND DATE(t.date) BETWEEN ? AND ? AND COALESCE(t.type,0)<>4 ORDER BY t.empid,t.date,t.time,t.id',$lt,$lp);
 $by=[];foreach($logs as$x){$d=(string)$x['workdate'];$in=trim((string)$x['in_time']);$out=trim((string)$x['out_time']);$gate=(int)($x['gatepass']??0)!==1;if($gate&&$in!=='')$by[(int)$x['empid']][$d]['punch'][]=['ts'=>strtotime($d.' '.$in),'v'=>mfl_clock($in)];if($gate&&$out!==''){$ed=substr((string)($x['exitdate']??''),0,10);if($ed===''||$ed==='0000-00-00')$ed=$d;$by[(int)$x['empid']][$d]['punch'][]=['ts'=>strtotime($ed.' '.$out),'v'=>mfl_clock($out)];}$mins=mfl_duration($in,$out,$d,substr((string)($x['exitdate']??''),0,10));$by[(int)$x['empid']][$d]['worked']=($by[(int)$x['empid']][$d]['worked']??0)+($gate?$mins:0);$by[(int)$x['empid']][$d]['shift']=$x['shortname']?:$x['shift_type'];$by[(int)$x['empid']][$d]['scheduled']=mfl_duration((string)$x['shift_in'],(string)$x['shift_out'],$d);}
 foreach($temp as$x){$d=(string)$x['workdate'];$v=trim((string)$x['time']);if($v==='')continue;$ed=substr((string)($x['exitdate']??''),0,10);if($ed===''||$ed==='0000-00-00')$ed=$d;$by[(int)$x['empid']][$d]['punch'][]=['ts'=>strtotime($ed.' '.$v),'v'=>mfl_clock($v)];}
 $rows=[];foreach($emps as$e){$days=[];$sum=['present'=>0,'absent'=>0,'worked'=>0,'late'=>0];foreach($dates as$d){$x=$by[(int)$e['id']][$d]??[];$ps=$x['punch']??[];usort($ps,fn($a,$b)=>$a['ts']<=>$b['ts']);$worked=(int)($x['worked']??0);$scheduled=(int)($x['scheduled']??0);$present=(bool)$ps;$first=$ps[0]['v']??'';$last=$ps?end($ps)['v']:'';$late=0;if($first&&isset($x['shift'])){$fm=mfl_minutes($first);}$days[$d]=['shift'=>(string)($x['shift']??''),'status'=>$present?'P':'A','first'=>$first,'last'=>$last,'worked'=>$worked,'extra'=>max(0,$worked-$scheduled),'loss'=>max(0,$scheduled-$worked),'late'=>$late];$sum[$present?'present':'absent']++;$sum['worked']+=$worked;} $rows[]=['employee'=>$e,'days'=>$days,'summary'=>$sum];}
 return['from'=>$from,'to'=>$to,'dates'=>$dates,'rows'=>$rows];}
