<?php
declare(strict_types=1);

function gsr_stmt(mysqli $con,string $sql):mysqli_stmt{$stmt=$con->prepare($sql);if(!$stmt)throw new RuntimeException('Unable to prepare report query.');return $stmt;}
function gsr_columns(mysqli $con,string $table):array{$rows=[];$result=$con->query('SHOW COLUMNS FROM `'.preg_replace('/[^A-Za-z0-9_]/','',$table).'`');if($result)while($row=$result->fetch_assoc())$rows[]=$row['Field'];return $rows;}
function gsr_access_value(string $value):?array{$value=trim($value);if($value===''||$value==='0'||strtolower($value)==='all')return null;preg_match_all('/\d+/',$value,$m);return array_values(array_unique(array_map('intval',$m[0]??[])));}
function gsr_access(mysqli $con):array{
    $user=current_user()?:[];if((int)($user['role_id']??0)===1)return ['payroll'=>null,'branch'=>null,'subbranch'=>null,'additional'=>null,'terminal'=>null];
    $cols=gsr_columns($con,'jos_admin_users');$wanted=['payroll_access','branch_access','subbranch_access','additional_payroll_access','terminal_access'];$available=array_values(array_intersect($wanted,$cols));
    if(!$available)return ['payroll'=>[],'branch'=>[],'subbranch'=>[],'additional'=>[],'terminal'=>[]];
    $uid=(int)($user['id']??0);$stmt=gsr_stmt($con,'SELECT `'.implode('`,`',$available).'` FROM jos_admin_users WHERE id=? AND status=1 LIMIT 1');$stmt->bind_param('i',$uid);$stmt->execute();$row=$stmt->get_result()->fetch_assoc()?:[];$stmt->close();
    return ['payroll'=>gsr_access_value((string)($row['payroll_access']??'')),'branch'=>gsr_access_value((string)($row['branch_access']??'')),'subbranch'=>gsr_access_value((string)($row['subbranch_access']??'')),'additional'=>gsr_access_value((string)($row['additional_payroll_access']??'')),'terminal'=>gsr_access_value((string)($row['terminal_access']??''))];
}
function gsr_add_in(string $column,?array $ids,array &$where,string &$types,array &$params):void{if($ids===null)return;if($ids===[]){$where[]='1=0';return;}$where[]=$column.' IN ('.implode(',',array_fill(0,count($ids),'?')).')';$types.=str_repeat('i',count($ids));array_push($params,...$ids);}
function gsr_scope(array $access,array &$where,string &$types,array &$params):void{gsr_add_in('f.payroll_location',$access['payroll'],$where,$types,$params);gsr_add_in('f.branch_location',$access['branch'],$where,$types,$params);gsr_add_in('f.subbranch',$access['subbranch'],$where,$types,$params);gsr_add_in('f.addpayroll_location',$access['additional'],$where,$types,$params);gsr_add_in('b.orglocation',$access['terminal'],$where,$types,$params);}
function gsr_rows(mysqli $con,string $sql,string $types='',array $params=[]):array{$stmt=gsr_stmt($con,$sql);if($types!=='')$stmt->bind_param($types,...$params);$stmt->execute();$rows=[];$result=$stmt->get_result();while($row=$result->fetch_assoc())$rows[]=$row;$stmt->close();return $rows;}
function gsr_options(mysqli $con,string $sql):array{return gsr_rows($con,$sql);}
function gsr_authorized_payrolls(mysqli $con,array $access):array{$where=['status=1'];$types='';$params=[];gsr_add_in('id',$access['payroll'],$where,$types,$params);return gsr_rows($con,'SELECT id,name FROM jos_app_payrolllocation WHERE '.implode(' AND ',$where).' ORDER BY name,id',$types,$params);}
function gsr_category_enabled(mysqli $con):bool{$stmt=gsr_stmt($con,'SELECT catonof FROM jos_app_usersetting ORDER BY id ASC LIMIT 1');$stmt->execute();$row=$stmt->get_result()->fetch_assoc();$stmt->close();return (int)($row['catonof']??0)===1;}
function gsr_id_in(array $rows,int $id):bool{foreach($rows as $row)if((int)$row['id']===$id)return true;return false;}
function gsr_filters(mysqli $con,array $access,array &$errors):array{
    $payrolls=gsr_authorized_payrolls($con,$access);$categories=gsr_category_enabled($con)?gsr_options($con,'SELECT id,designation_name name FROM jos_app_emp_category ORDER BY designation_name,id'):[];$months=gsr_options($con,'SELECT id,month name FROM jos_app_mmonth ORDER BY id');$years=gsr_options($con,'SELECT id,year name FROM jos_app_year ORDER BY id DESC LIMIT 3');$salaryTypes=gsr_options($con,'SELECT id,name FROM jos_app_salarytype ORDER BY id');
    $f=['filter_emp'=>max(0,(int)($_GET['filter_emp']??0)),'filter_orglocation'=>max(0,(int)($_GET['filter_orglocation']??0)),'filt_cat'=>max(0,(int)($_GET['filt_cat']??0)),'filt_month'=>max(0,(int)($_GET['filt_month']??0)),'filt_year'=>max(0,(int)($_GET['filt_year']??0)),'filt_salary'=>max(0,(int)($_GET['filt_salary']??0))];
    foreach([['filter_orglocation',$payrolls,'payroll location'],['filt_cat',$categories,'category'],['filt_month',$months,'month'],['filt_year',$years,'year'],['filt_salary',$salaryTypes,'salary type']] as [$key,$rows,$label])if($f[$key]&&!gsr_id_in($rows,$f[$key])){$errors[]='Invalid or unauthorized '.$label.'.';$f[$key]=0;}
    $employees=gsr_employee_options($con,$access);if($f['filter_emp']&&!gsr_id_in($employees,$f['filter_emp'])){$errors[]='Invalid or unauthorized employee.';$f['filter_emp']=0;}
    return [$f,$payrolls,$categories,$months,$years,$salaryTypes,$employees];
}
function gsr_employee_options(mysqli $con,array $access):array{$where=['f.status IN (0,1)'];$types='';$params=[];gsr_scope($access,$where,$types,$params);return gsr_rows($con,'SELECT DISTINCT f.id,f.name FROM jos_app_employee f LEFT JOIN jos_app_employee_locationlog b ON b.app_empid=f.id WHERE '.implode(' AND ',$where).' ORDER BY f.name,f.id LIMIT 5000',$types,$params);}
function gsr_query(mysqli $con,array $access,array $f,int $limit=0,int $offset=0,bool $count=false){$where=['f.status IN (0,1)'];$types='';$params=[];gsr_scope($access,$where,$types,$params);foreach([['filter_emp','p.empid'],['filter_orglocation','f.payroll_location'],['filt_cat','f.category'],['filt_year','p.year'],['filt_month','p.month'],['filt_salary','p.s_type']] as [$key,$column])if($f[$key]){$where[]=$column.'=?';$types.='i';$params[]=$f[$key];}$from=' FROM jos_app_emp_gross_pay p INNER JOIN jos_app_employee f ON p.empid=f.id INNER JOIN jos_app_employee_locationlog b ON f.id=b.app_empid LEFT JOIN jos_app_salarytype st ON st.id=p.s_type WHERE '.implode(' AND ',$where);if($count){$stmt=gsr_stmt($con,'SELECT COUNT(DISTINCT p.id) total'.$from);if($types)$stmt->bind_param($types,...$params);$stmt->execute();$total=(int)($stmt->get_result()->fetch_assoc()['total']??0);$stmt->close();return $total;}$sql='SELECT p.id,p.empid,p.month,p.year,p.s_type,p.grosspay,p.rateperday,p.rateperhr,f.name employee_name,st.name salary_type'.$from.' GROUP BY p.id ORDER BY p.id ASC';if($limit>0){$sql.=' LIMIT ? OFFSET ?';$types.='ii';$params[]=$limit;$params[]=$offset;}return gsr_rows($con,$sql,$types,$params);}
function gsr_month_name($value):string{$month=(int)$value;return $month>=1&&$month<=12?date('F',mktime(0,0,0,$month,1)):(string)$value;}
function gsr_query_string(array $f):string{return http_build_query(array_filter($f,fn($v)=>(int)$v>0));}
