<?php
require_once __DIR__ . '/../config.php';
require_admin();

function earned_rule($approved,$target,$amount,$calc,$minp,$allow){
  if($target<=0 || $amount<=0) return 0;
  $pct = $approved / $target;
  if(!$allow) $pct = min(1.0, $pct);
  if($pct < $minp) return 0;
  if($calc==='threshold') return ($pct>=1.0)?$amount:0;
  return (int)round($amount*$pct);
}


$db = contracts_db();
mysqli_set_charset($db, 'utf8mb4');             // <-- juda muhim
$db->query("SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci");
$db->query("SET time_zone = '+05:00'");


$ym=$_GET['ym'] ?? date('Y-m'); $dept=trim($_GET['dept']??''); $region=trim($_GET['region']??''); $q=trim($_GET['q']??''); $op=(int)($_GET['op']??0);
$db=db(); $where="WHERE active=1"; if($op>0) $where.=" AND id=".$op;
if($dept!==''){ $d=$db->real_escape_string($dept); $where.=" AND department LIKE '%$d%'"; }
if($region!==''){ $r=$db->real_escape_string($region); $where.=" AND region LIKE '%$r%'"; }
if($q!==''){ $qq=$db->real_escape_string($q); $where.=" AND (name LIKE '%$qq%' OR code LIKE '%$qq%')"; }

$ops=[]; $r=$db->query("SELECT * FROM operators $where ORDER BY name ASC"); while($x=$r->fetch_assoc()) $ops[]=$x;
$st=$db->prepare("SELECT * FROM operator_monthly_metrics WHERE ym=?"); $st->bind_param("s",$ym); $st->execute(); $rr=$st->get_result(); $mm=[]; while($row=$rr->fetch_assoc()) $mm[(int)$row['operator_id']]=$row; $st->close();

header("Content-Type: application/vnd.ms-excel; charset=utf-8");
header("Content-Disposition: attachment; filename=payroll_$ym.xls");
echo "<meta charset='utf-8'>";
echo "<style>.hdr{background:#0d6efd;color:#fff;font-weight:bold;} .sec{background:#198754;color:#fff;} .muted{color:#666;} table{border-collapse:collapse} td,th{border:1px solid #ddd;padding:6px}.right{text-align:right}</style>";
echo "<table><tr class='hdr'><th colspan='6'>Oylik hisobot ($ym)</th></tr>";

$TG=0;$TN=0;
foreach($ops as $o){
  $m=$mm[$o['id']]??['ym'=>$ym];
  $base_amount    = isset($m['base_amount'])?(int)$m['base_amount']:1500000;
  $base_confirmed = (int)($m['base_confirmed']??0);
  $base           = $base_confirmed ? $base_amount : 0;
  $penalties      = (int)($m['late_minutes']??0)*300 + (int)($m['absent_days']??0)*100000 +
                    (int)($m['bad_comm_count']??0)*30000 + (int)($m['missing_info_count']??0)*20000 +
                    (int)($m['other_penalty']??0);

  // approved
  $ap=[]; $qr=$db->query("SELECT item_id, COUNT(*) c FROM kpi_submissions WHERE operator_id=".$o['id']." AND ym='".$db->real_escape_string($ym)."' AND status='approved' GROUP BY item_id");
  while($x=$qr->fetch_assoc()){ $ap[(int)$x['item_id']] = (int)$x['c']; }

  // dynamic + rows
  $dyn=0; $dynRows = "<tr><th>Punkt</th><th class='right'>Reja</th><th class='right'>Approved</th><th>Usul</th><th class='right'>Mukofot</th><th class='right'>Earned</th></tr>";
  $qi=$db->query("SELECT i.id,i.title,i.target_int,i.reward_amount,i.reward_calc,i.reward_min_pct,i.reward_allow_over
                  FROM kpi_items i JOIN kpi_plans p ON p.id=i.plan_id JOIN kpi_assignments a ON a.plan_id=p.id AND a.operator_id=".$o['id']." WHERE p.ym='".$db->real_escape_string($ym)."' ORDER BY i.plan_id,i.sort_order,i.id");
  while($it=$qi->fetch_assoc()){
    $iid=(int)$it['id']; $tgt=(int)$it['target_int']; $rew=(int)$it['reward_amount']; $calc=$it['reward_calc']??'proportional';
    $minp=(float)($it['reward_min_pct']??0.0); $allow=!empty($it['reward_allow_over'])?1:0; $approved=$ap[$iid]??0;
    $earned = earned_rule($approved,$tgt,$rew,$calc,$minp,$allow); $dyn += $earned;
    $dynRows .= "<tr><td>&nbsp;&nbsp;• ".htmlspecialchars($it['title'])."</td><td class='right'>$tgt</td><td class='right'>$approved</td><td>".htmlspecialchars($calc)."</td><td class='right'>$rew</td><td class='right'>$earned</td></tr>";
  }

  $gross = $base + $dyn + (int)($m['other_bonus']??0);
  $net   = max(0, $gross - $penalties);

  echo "<tr class='sec'><th colspan='6'>".$o['name']." (".$o['code'].") — ".$o['department']." / ".$o['region']."</th></tr>";
  echo "<tr><td>Asosiy</td><td colspan='2'>".($base_confirmed?'tasdiqlangan':'kutilmoqda')."</td><td colspan='3' class='right'>".$base_amount."</td></tr>";
  echo "<tr><td>Dynamic bonus</td><td colspan='5' class='right'>$dyn</td></tr>";
  echo $dynRows;
  echo "<tr><td>Gross</td><td colspan='2' class='right'>$gross</td><td>Net</td><td colspan='2' class='right'>$net</td></tr>";
  echo "<tr><td colspan='6' class='muted'>Jarimalar: $penalties</td></tr>";

  $TG += $gross; $TN += $net;
}
echo "<tr class='hdr'><th>Jami gross</th><th colspan='2' class='right'>$TG</th><th>Jami net</th><th colspan='2' class='right'>$TN</th></tr></table>";
