<?php
require_once __DIR__ . '/../config.php';

header('Content-Type: application/json; charset=utf-8');
date_default_timezone_set('Asia/Tashkent');

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

/* ------------ Helpers ------------ */
if (!function_exists('json_out')) {
  function json_out($d,$c=200){
    http_response_code($c);
    header('Content-Type: application/json; charset=utf-8');
    echo json_encode($d, JSON_UNESCAPED_UNICODE);
    exit;
  }
}
function jerr($msg,$detail=null,$code=400){ $p=['ok'=>false,'error'=>$msg]; if($detail!==null)$p['detail']=$detail; json_out($p,$code); }
function norm($s){ return is_string($s) ? trim($s) : ''; }
function i($v){ return (int)$v; }
function ensure_dir($p){ return is_dir($p) ? is_writable($p) : @mkdir($p,0755,true); }
function safe_filename_u($name){
  $name = preg_replace('/[^\p{L}\p{N}\s\.\-_]+/u','_', (string)$name);
  $name = preg_replace('/_+/','_', $name);
  $name = trim($name);
  return $name ?: ('file_'.time());
}

/* --- CSV sanitizatsiya --- */
function _strip_bom($s){ return (substr($s,0,3) === "\xEF\xBB\xBF") ? substr($s,3) : $s; }
function _to_utf8($s){
  if ($s === null) return null;
  $src = is_string($s) ? $s : (string)$s;
  if (!mb_check_encoding($src,'UTF-8')) {
    $src = @iconv('CP1251','UTF-8//IGNORE',$src) ?: @iconv('ISO-8859-1','UTF-8//IGNORE',$src) ?: $src;
  }
  // boshqaruv belgilarini olib tashlash (CR/LF qoladi)
  $src = preg_replace('/[\x00-\x09\x0B-\x0C\x0E-\x1F\x7F]/u','',$src);
  return $src;
}
function _norm_header($h){
  $h = strtolower(_to_utf8($h));
  $h = preg_replace('/\s+/',' ', $h);
  return trim($h);
}

/* -------- Settings (jadval & tariflar) -------- */
function _ensure_settings_table($db){
  $db->query("CREATE TABLE IF NOT EXISTS attendance_settings (
    id TINYINT PRIMARY KEY,
    mon_work TINYINT DEFAULT 1, tue_work TINYINT DEFAULT 1, wed_work TINYINT DEFAULT 1,
    thu_work TINYINT DEFAULT 1, fri_work TINYINT DEFAULT 1, sat_work TINYINT DEFAULT 0, sun_work TINYINT DEFAULT 0,
    start_time TIME DEFAULT '09:00:00', end_time TIME DEFAULT '18:00:00', break_min INT DEFAULT 0,
    late_min_penalty INT DEFAULT 100, absent_day_penalty INT DEFAULT 100000,
    present_hour_bonus INT DEFAULT 2000, ontime_day_bonus INT DEFAULT 5000,
    early_leave_min_penalty INT DEFAULT 100,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  )");
  $r=$db->query("SELECT 1 FROM attendance_settings WHERE id=1");
  if(!$r || !$r->num_rows){
    $db->query("INSERT INTO attendance_settings (id) VALUES (1)");
  }
  // yangi ustun bo‘lmasa qo‘shamiz (compat)
  $col=$db->query("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='attendance_settings' AND COLUMN_NAME='early_leave_min_penalty'");
  if(!$col || !$col->num_rows){
    @$db->query("ALTER TABLE attendance_settings ADD COLUMN early_leave_min_penalty INT DEFAULT 100");
  }
}
function _load_settings($db){
  _ensure_settings_table($db);
  $s=$db->query("SELECT * FROM attendance_settings WHERE id=1")->fetch_assoc();
  if(!$s){
    return [
      'mon_work'=>1,'tue_work'=>1,'wed_work'=>1,'thu_work'=>1,'fri_work'=>1,'sat_work'=>0,'sun_work'=>0,
      'start_time'=>'09:00:00','end_time'=>'18:00:00','break_min'=>0,
      'late_min_penalty'=>100,'absent_day_penalty'=>100000,'present_hour_bonus'=>2000,'ontime_day_bonus'=>5000,
      'early_leave_min_penalty'=>100
    ];
  }
  return $s;
}
function _save_settings($db,$in){
  _ensure_settings_table($db);

  // 1) Avval hammasini o‘zgaruvchilarga yozib olamiz
  $mon_work = i($in['mon_work'] ?? 1);
  $tue_work = i($in['tue_work'] ?? 1);
  $wed_work = i($in['wed_work'] ?? 1);
  $thu_work = i($in['thu_work'] ?? 1);
  $fri_work = i($in['fri_work'] ?? 1);
  $sat_work = i($in['sat_work'] ?? 0);
  $sun_work = i($in['sun_work'] ?? 0);

  // TIME ustunlar — string ko‘rinishida ('H:i:s')
  $start_time = isset($in['start_time']) ? $in['start_time'] : '09:00:00';
  if (strlen($start_time) === 5) $start_time .= ':00';

  $end_time   = isset($in['end_time']) ? $in['end_time'] : '18:00:00';
  if (strlen($end_time) === 5) $end_time .= ':00';

  $break_min  = i($in['break_min'] ?? 0);

  // Tariflar
  $late_min_penalty        = i($in['late_min_penalty'] ?? 100);
  $absent_day_penalty      = i($in['absent_day_penalty'] ?? 100000);
  $present_hour_bonus      = i($in['present_hour_bonus'] ?? 2000);
  $ontime_day_bonus        = i($in['ontime_day_bonus'] ?? 5000);
  $early_leave_min_penalty = i($in['early_leave_min_penalty'] ?? 100);

  // 2) UPDATE
  $sql = "UPDATE attendance_settings SET
            mon_work=?,
            tue_work=?,
            wed_work=?,
            thu_work=?,
            fri_work=?,
            sat_work=?,
            sun_work=?,
            start_time=?,
            end_time=?,
            break_min=?,
            late_min_penalty=?,
            absent_day_penalty=?,
            present_hour_bonus=?,
            ontime_day_bonus=?,
            early_leave_min_penalty=?
          WHERE id=1";

  $st = $db->prepare($sql);
  if(!$st) jerr('DB prepare error: '.$db->error, null, 500);

  // 7 * i + 2 * s + 6 * i = 15 ta parametr → "iiiiiiissiiiiii"
  $st->bind_param(
    "iiiiiiissiiiiii",
    $mon_work,
    $tue_work,
    $wed_work,
    $thu_work,
    $fri_work,
    $sat_work,
    $sun_work,
    $start_time,
    $end_time,
    $break_min,
    $late_min_penalty,
    $absent_day_penalty,
    $present_hour_bonus,
    $ontime_day_bonus,
    $early_leave_min_penalty
  );

  if(!$st->execute()) jerr('DB exec error: '.$st->error,null,500);
}


/* -------- Exceptions (sababli) -------- */
function _ensure_exceptions_table($db){
  $db->query("CREATE TABLE IF NOT EXISTS attendance_exceptions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ym CHAR(7) NOT NULL,
    emp_code VARCHAR(64) NULL,
    fio_key VARCHAR(255) NULL,
    day DATE NOT NULL,
    excused TINYINT DEFAULT 1,
    reason VARCHAR(255) NULL,
    created_by INT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_exc (day, emp_code, fio_key)
  )");
}
function _fio_key($fio){ return mb_strtolower(trim((string)$fio),'UTF-8'); }
function _load_exceptions($db,$ym,$emp_code,$fio_key){
  _ensure_exceptions_table($db);
  $ymE=$db->real_escape_string($ym);
  if ($emp_code!==''){
    $res=$db->query("SELECT day,excused,reason FROM attendance_exceptions
                     WHERE ym='$ymE' AND emp_code='".$db->real_escape_string($emp_code)."'");
  } else {
    $res=$db->query("SELECT day,excused,reason FROM attendance_exceptions
                     WHERE ym='$ymE' AND fio_key='".$db->real_escape_string($fio_key)."'");
  }
  $map=[];
  if($res){ while($x=$res->fetch_assoc()){ $map[$x['day']]=$x; } }
  return $map;
}

/* --- Events map (by emp_code yoki fio fallback) --- */
function _events_map_by_day($db, $ym, $identity_emp_code=null, $identity_fio_key=null){
  $ymEsc = $db->real_escape_string($ym);
  if ($identity_emp_code !== null && $identity_emp_code !== '') {
    $cond = "emp_code='".$db->real_escape_string($identity_emp_code)."'";
  } else {
    $cond = "(emp_code IS NULL OR emp_code='') AND LOWER(TRIM(fio))='".$db->real_escape_string($identity_fio_key??'')."'";
  }
  $rows=[];
  $r=$db->query("SELECT DATE(event_dt) d, TIME(event_dt) t 
                 FROM attendance_rows 
                 WHERE $cond AND DATE_FORMAT(event_dt,'%Y-%m')='$ymEsc'
                 ORDER BY event_dt ASC");
  while($x=$r->fetch_assoc()) $rows[]=$x;
  $map=[];
  foreach($rows as $ev){ $map[$ev['d']][]=$ev['t']; }
  return $map;
}
function _mins_between($d,$t1,$t2){ $a=strtotime("$d $t1"); $b=strtotime("$d $t2"); return ($a && $b) ? max(0,(int)round(($b-$a)/60)) : 0; }

/* --- Oylik hisob (settings + sababli + erta ketish) + “bugungacha” --- */
function _calc_month($db, $ym, $emp_code, $fio){
  $settings = _load_settings($db);
  $sched = [
    'mon_work'=>i($settings['mon_work']??1),'tue_work'=>i($settings['tue_work']??1),'wed_work'=>i($settings['wed_work']??1),
    'thu_work'=>i($settings['thu_work']??1),'fri_work'=>i($settings['fri_work']??1),'sat_work'=>i($settings['sat_work']??0),'sun_work'=>i($settings['sun_work']??0),
    'start_time'=>$settings['start_time']??'09:00:00','end_time'=>$settings['end_time']??'18:00:00','break_min'=>i($settings['break_min']??0)
  ];
  $tar   = [
    'late_min_penalty'=>i($settings['late_min_penalty']??100),
    'absent_day_penalty'=>i($settings['absent_day_penalty']??100000),
    'present_hour_bonus'=>i($settings['present_hour_bonus']??2000),
    'ontime_day_bonus'=>i($settings['ontime_day_bonus']??5000),
    'early_leave_min_penalty'=>i($settings['early_leave_min_penalty']??100),
  ];

  $wk    = ['sun','mon','tue','wed','thu','fri','sat'];
  $start=strtotime($ym.'-01');
  $endMonth=strtotime(date('Y-m-t',$start));
  $todayTs=strtotime(date('Y-m-d'));
  $loopEnd = $endMonth;
  if (date('Y-m',$start) === date('Y-m')) $loopEnd = min($endMonth, $todayTs);

  $fio_key = _fio_key($fio);
  $evmap   = _events_map_by_day($db, $ym, $emp_code, $fio_key);
  $excmap  = _load_exceptions($db, $ym, $emp_code, $fio_key);

  $days=[]; 
  $sum = ['present_days'=>0,'absent_days'=>0,'late_min'=>0,'worked_min'=>0,'ontime_days'=>0];

  for($ts=$start;$ts<=strtotime(date('Y-m-t',$start));$ts+=86400){
    $day = date('Y-m-d',$ts);
    $wd  = $wk[ date('w',$ts) ];
    $isWork = !empty($sched[$wd.'_work']);

    $isFuture = ($ts > $loopEnd);
    $firstIn=null; $lastOut=null; $late=0; $early=0; $abs=0; $worked=0; $ont=0; $pen=0; $bon=0; $note=[];

    if(!$isFuture && $isWork){
      if(!empty($evmap[$day])){
        sort($evmap[$day]); $firstIn=$evmap[$day][0]; $lastOut=end($evmap[$day]);
      }
      if($firstIn){
        // kechikish
        $late = _mins_between($day, $sched['start_time'], $firstIn);
        // erta ketish
        if($lastOut){
          $diffEnd = _mins_between($day, $lastOut, $sched['end_time']); // lastOut < end_time => musbat
          $early   = max(0,$diffEnd);
        }
        $sum['present_days']++; if($late===0 && $early===0) $ont=1;
        if($lastOut){ $worked=_mins_between($day,$firstIn,$lastOut)-(int)$sched['break_min']; if($worked<0)$worked=0; }
      }else{
        $abs=1; $sum['absent_days']++;
      }
      $sum['late_min']+=$late; $sum['worked_min']+=$worked; $sum['ontime_days']+=$ont;

      // tarif: kechikish / kelmagan / ishlagan / vaqtida / erta ketish
      if($abs){ $pen += $tar['absent_day_penalty']; $note[]='kelmagan (ish kuni)'; }
      if($late>0){ $pen += $tar['late_min_penalty']*$late; $note[]='kechikish: '.$late.' minut'; }
      if($early>0){ $pen += $tar['early_leave_min_penalty']*$early; $note[]='erta ketish: '.$early.' minut'; }
      if($worked>0){ $wh=floor($worked/60); if($wh>0){ $bon += $tar['present_hour_bonus']*$wh; $note[]='ish vaqti: '.$wh.' soat'; } }
      if(!$abs && $late===0 && $early===0){ $bon += $tar['ontime_day_bonus']; $note[]='o‘z vaqtida'; }
    } else {
      if($isFuture) $note[]='kelajak';
      if(!$isWork)  $note[]='ish kuni emas';
    }

    // SABABLI — jarimani bekor
    if(isset($excmap[$day]) && i($excmap[$day]['excused'])===1){
      $pen = 0;
      $r = trim((string)$excmap[$day]['reason']);
      $note[] = 'sababli'.($r!==''?(': '.$r):'');
    }

    $days[]=[
      'day'=>$day,'is_work'=>$isWork?1:0,
      'first_in'=>$firstIn,'last_out'=>$lastOut,
      'late_min'=>$late,'early_leave_min'=>$early,'worked_min'=>$worked,'absent'=>$abs,
      'penalty'=>$pen,'bonus'=>$bon,'note'=>implode(', ',$note),
      'excused'=> isset($excmap[$day]) ? i($excmap[$day]['excused']) : 0,
      'reason'=>  isset($excmap[$day]) ? (string)$excmap[$day]['reason'] : null
    ];
  }

  // oylik ± faqat bugungacha
  $penTotal = 0; $bonTotal=0;
  foreach($days as $d){ if(strpos($d['note'],'kelajak')!==false) continue; $penTotal += $d['penalty']; $bonTotal += $d['bonus']; }

  return [$days,$sum,$sched,$penTotal,$bonTotal];
}

/* ================= ROUTING ================= */
$method = $_SERVER['REQUEST_METHOD'] ?? 'GET';
$action = $_GET['action'] ?? '';

/* ---------- SETTINGS GET/POST ---------- */
if ($method==='GET' && $action==='settings') {
  try{ json_out(['ok'=>true,'settings'=>_load_settings($db)]); }catch(Throwable $e){ jerr($e->getMessage(),null,500); }
}
if ($method==='POST' && $action==='settings_save') {
  try{
    $in=json_decode(file_get_contents('php://input'),true);
    if(!is_array($in)) jerr('Bad JSON');
    _save_settings($db,$in);
    json_out(['ok'=>true]);
  }catch(Throwable $e){ jerr($e->getMessage(),null,500); }
}

/* ---------- SABABLI (exceptions) SET ---------- */
if ($method==='POST' && $action==='exception_set') {
  try{
    _ensure_exceptions_table($db);
    $in=json_decode(file_get_contents('php://input'),true);
    if(!is_array($in)) jerr('Bad JSON');

    $ym   = norm($in['ym'] ?? '');
    $day  = norm($in['day'] ?? '');
    $exc  = i($in['excused'] ?? 0);
    $rsn  = norm($in['reason'] ?? '');
    $emp_code = norm($in['emp_code'] ?? '');
    $fio     = norm($in['fio'] ?? '');
    $fio_key = _fio_key($fio);
    if(!$ym || !$day) jerr('ym/day shart');

    $uid = (int)(current_user()['id'] ?? 0);

    // upsert
    $emp_col = ($emp_code!=='') ? "'".$db->real_escape_string($emp_code)."'" : "NULL";
    $fio_col = ($emp_code!=='') ? "NULL" : "'".$db->real_escape_string($fio_key)."'";
    $ymE = $db->real_escape_string($ym);
    $dayE= $db->real_escape_string($day);
    $rsE = $db->real_escape_string($rsn);
    $db->query("INSERT INTO attendance_exceptions (ym,emp_code,fio_key,day,excused,reason,created_by)
                VALUES ('$ymE',$emp_col,$fio_col,'$dayE',$exc,'$rsE',$uid)
                ON DUPLICATE KEY UPDATE excused=VALUES(excused), reason=VALUES(reason), created_by=VALUES(created_by), created_at=CURRENT_TIMESTAMP");

    json_out(['ok'=>true]);
  }catch(Throwable $e){ jerr($e->getMessage(),null,500); }
}

/* ---------- CSV UPLOAD (auto-create employees) ---------- */
if ($method==='POST' && $action==='upload_csv') {
  try{
    $ym = norm($_POST['ym'] ?? date('Y-m'));
    if(!isset($_FILES['file']) || !is_uploaded_file($_FILES['file']['tmp_name'])) jerr('CSV fayl tanlanmadi');

    $folder = str_replace('-','',$ym);
    $base = realpath(__DIR__.'/..').'/uploads/attendance/'.$folder;
    if(!ensure_dir($base)) jerr('Upload katalogi yaratilmaydi',null,500);

    $orig   = $_FILES['file']['name'];
    $stored = time().'_'.bin2hex(random_bytes(4)).'_'.safe_filename_u($orig);
    $dest   = $base.'/'.$stored;

    if(!move_uploaded_file($_FILES['file']['tmp_name'],$dest)){
      if(!@copy($_FILES['file']['tmp_name'],$dest)) jerr('Fayl saqlanmadi',null,500);
      @unlink($_FILES['file']['tmp_name']);
    }

    // import log
    $uid = (int)(current_user()['id'] ?? 0);
    $st=$db->prepare("INSERT INTO attendance_imports (ym,file_name,stored_name,imported_by) VALUES (?,?,?,?)");
    $st->bind_param("sssi",$ym,$orig,$stored,$uid);
    $st->execute(); $import_id=$st->insert_id;

    // CSV ni UTF-8 ga tozalash
    $raw = file_get_contents($dest);
    if($raw === false) jerr('Fayl o‘qilmadi',null,500);
    $raw = _strip_bom($raw);
    $raw = _to_utf8($raw);
    $tmp = $base.'/_norm_'.uniqid().'.csv';
    file_put_contents($tmp, $raw);

    $fh = fopen($tmp,'r');
    if(!$fh) jerr('CSV o‘qilmadi',null,500);
    $first = fgets($fh);
    if($first===false) jerr('Bo‘sh fayl');
    $delim = (substr_count($first,';')>substr_count($first,',')) ? ';' : ',';
    rewind($fh);

    $header = fgetcsv($fh, 0, $delim);
    if(!$header){ jerr('CSV header topilmadi'); }
    $cols = array_map('_norm_header',$header);

    // moslashuvchan sarlavha qidirish
    $findCol = function(array $keys) use($cols){
      foreach($keys as $k){ $i=array_search(_norm_header($k),$cols); if($i!==false) return $i; }
      return false;
    };
    $idx = [
      'emp_code'=> $findCol(['employee no.','employee no','employeeid','employee id','user id','emp code','code']),
      'fio'     => $findCol(['name','employee name','full name','fio']),
      'dt'      => $findCol(['time','datetime','date time','timestamp']),
      'event'   => $findCol(['status','event','io type','in/out'])
    ];

    $ins=$db->prepare("INSERT INTO attendance_rows (import_id,emp_code,fio,event_dt,event_type,raw_row) VALUES (?,?,?,?,?,?)");

    while(($row=fgetcsv($fh,0,$delim))!==false){
      if (count($row)<1) continue;
      foreach($row as $k=>$v) $row[$k] = _to_utf8($v);

      $emp = ($idx['emp_code']!==false ? ($row[$idx['emp_code']] ?? '') : '');
      if($emp==='' && isset($row[0])) $emp = trim(trim($row[0]),"'\"");
      $fio = ($idx['fio']!==false ? ($row[$idx['fio']] ?? '') : '');
      $dt  = ($idx['dt']!==false ? ($row[$idx['dt']] ?? '') : '');
      $ev  = ($idx['event']!==false ? strtolower($row[$idx['event']] ?? '') : '');

      $event_type = (str_contains($ev,'in') || str_contains($ev,'checkin')) ? 'in'
                    : ((str_contains($ev,'out') || str_contains($ev,'checkout')) ? 'out' : 'raw');

      $rawRow  = implode($delim, $row);
      if ($event_type==='raw') {
        $rr = strtolower($rawRow);
        if (strpos($rr,'check-in')!==false || strpos($rr,'kelish')!==false)  $event_type='in';
        if (strpos($rr,'check-out')!==false|| strpos($rr,'ketish')!==false) $event_type='out';
      }

      $evts = strtotime($dt ?: 'now');
      $evdt = date('Y-m-d H:i:s',$evts);

      $ins->bind_param("isssss",$import_id,$emp,$fio,$evdt,$event_type,$rawRow);
      $ins->execute();

      // employees ga auto-create
      if($emp!==''){
        $esc_emp=$db->real_escape_string($emp);
        $exists=$db->query("SELECT id FROM employees WHERE emp_code='$esc_emp'")->fetch_assoc();
        if(!$exists){
          $fio_s = $fio ?: ('Emp '.$emp);
          $db->query("INSERT INTO employees (emp_code,fio,is_active) VALUES ('$esc_emp','".$db->real_escape_string($fio_s)."',1)");
        }
      }
    }
    fclose($fh);
    @unlink($tmp);

    json_out(['ok'=>true,'import_id'=>$import_id,'ym'=>$ym,'file'=>$orig]);
  }catch(Throwable $e){ jerr($e->getMessage(),null,500); }
}

/* ---------- IMPORTS LIST ---------- */
if ($method==='GET' && $action==='imports') {
  try{
    $rows=[]; 
    $r=$db->query("SELECT id,ym,file_name,stored_name,imported_by,created_at FROM attendance_imports ORDER BY id DESC");
    while($x=$r->fetch_assoc()) $rows[]=$x;
    json_out(['ok'=>true,'rows'=>$rows]);
  }catch(Throwable $e){ jerr($e->getMessage(),null,500); }
}

/* ---------- MONTH COMPUTE (dedupe, emp_code bo‘lmaganlarni tashlab) ---------- */
if ($method==='GET' && $action==='month_compute') {
  try{
    $ym    = norm($_GET['ym'] ?? date('Y-m'));
    $page  = max(1, (int)($_GET['page'] ?? 1));
    $limit = max(1, min(100, (int)($_GET['limit'] ?? 20)));
    $off   = ($page-1)*$limit;
    $q     = norm($_GET['q'] ?? '');

    $whereQ = " AND b.emp_code IS NOT NULL AND b.emp_code<>''"; // EmpCode yo‘qlarni chiqarma
    if($q!==''){
      $qq=$db->real_escape_string($q);
      $whereQ .= " AND (COALESCE(e.fio,b.fio) LIKE '%$qq%' OR e.emp_code LIKE '%$qq%' OR b.emp_code LIKE '%$qq%' OR e.dept LIKE '%$qq%')";
    }

    $sql = "SELECT SQL_CALC_FOUND_ROWS
              COALESCE(e.id,0) AS employee_id,
              b.emp_code, b.fio, e.dept, e.position
            FROM (
              SELECT
                CASE WHEN (emp_code IS NULL OR emp_code='') THEN LOWER(TRIM(fio)) ELSE emp_code END AS k,
                MAX(NULLIF(emp_code,'')) AS emp_code,
                MAX(fio) AS fio
              FROM attendance_rows
              WHERE DATE_FORMAT(event_dt,'%Y-%m')='".$db->real_escape_string($ym)."'
              GROUP BY k
            ) b
            LEFT JOIN employees e ON e.emp_code = b.emp_code
            WHERE 1=1 $whereQ
            ORDER BY b.fio, b.emp_code
            LIMIT $off,$limit";

    $lst=[]; $r=$db->query($sql);
    while($x=$r->fetch_assoc()) $lst[]=$x;
    $total=(int)($db->query("SELECT FOUND_ROWS() x")->fetch_assoc()['x'] ?? 0);

    $rows=[];
    foreach($lst as $e){
      $emp_code = $e['emp_code'] ?? '';
      $fio      = $e['fio'] ?? '';
      [$days,$sum,$sched,$pen,$bon] = _calc_month($db, $ym, $emp_code, $fio);
      $rows[] = [
        'employee_id'=>(int)$e['employee_id'],
        'emp_code'=>$emp_code ?: '',
        'fio'=>$fio,
        'dept'=>$e['dept'],
        'position'=>$e['position'],
        'present_days'=>$sum['present_days'],
        'absent_days'=>$sum['absent_days'],
        'late_min'=>$sum['late_min'],
        'worked_min'=>$sum['worked_min'],
        'penalties'=>$pen,
        'bonuses'=>$bon,
        'net'=>$bon - $pen
      ];
    }

    json_out(['ok'=>true,'rows'=>$rows,'page'=>$page,'limit'=>$limit,'total'=>$total,'ym'=>$ym]);
  }catch(Throwable $e){ jerr($e->getMessage(),null,500); }
}

/* ---------- EMPLOYEE MONTH DAILY ---------- */
if ($method==='GET' && $action==='employee_month_calc') {
  try{
    $employee_id = (int)($_GET['employee_id'] ?? 0);
    $ym = norm($_GET['ym'] ?? date('Y-m'));

    $emp_code = null; $fio=null; $emp=[];
    if($employee_id){
      $emp=$db->query("SELECT id,emp_code,fio FROM employees WHERE id=$employee_id")->fetch_assoc();
      if(!$emp) jerr('Employee not found',null,404);
      $emp_code = $emp['emp_code']; $fio = $emp['fio'];
    }else{
      $emp_code = norm($_GET['emp_code'] ?? '');
      if($emp_code==='') jerr('employee_id yoki emp_code shart');
      $emp = $db->query("SELECT id,emp_code,fio FROM employees WHERE emp_code='".$db->real_escape_string($emp_code)."'")->fetch_assoc() ?: ['id'=>0,'emp_code'=>$emp_code,'fio'=>null];
      $fio = $emp['fio'];
    }

    [$days,$sum,$sched,$pen,$bon] = _calc_month($db, $ym, $emp_code, $fio);

    json_out([
      'ok'=>true,
      'employee'=>['id'=>$emp['id']??0,'emp_code'=>$emp_code,'fio'=>$fio],
      'ym'=>$ym,
      'schedule'=>$sched,
      'summary'=>[
        'present_days'=>$sum['present_days'],'absent_days'=>$sum['absent_days'],
        'late_min'=>$sum['late_min'],'worked_min'=>$sum['worked_min'],
        'ontime_days'=>$sum['ontime_days'],'penalties'=>$pen,'bonuses'=>$bon,'net'=>$bon-$pen
      ],
      'days'=>$days
    ]);
  }catch(Throwable $e){ jerr($e->getMessage(),null,500); }
}

/* ---------- DELETE EMP (oy bo‘yicha) ---------- */
if ($method==='POST' && $action==='delete_emp_month') {
  try{
    $in=json_decode(file_get_contents('php://input'),true);
    if(!is_array($in)) jerr('Bad JSON');
    $ym  = norm($in['ym'] ?? '');
    $emp = norm($in['emp_code'] ?? '');
    if($ym==='' || $emp==='') jerr('ym va emp_code shart');
    $ymE=$db->real_escape_string($ym); $empE=$db->real_escape_string($emp);

    // attendance_rows va exceptions dagi shu oy, shu emp_code bo‘yicha hammasini o‘chiramiz
    $db->query("DELETE FROM attendance_rows WHERE emp_code='$empE' AND DATE_FORMAT(event_dt,'%Y-%m')='$ymE'");
    $db->query("DELETE FROM attendance_exceptions WHERE emp_code='$empE' AND ym='$ymE'");
    json_out(['ok'=>true]);
  }catch(Throwable $e){ jerr($e->getMessage(),null,500); }
}

/* ---------- DEFAULT ---------- */
json_out(['ok'=>false,'error'=>'Unknown action'],404);
