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

$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'");


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

$db = contracts_db();

function out_bad($msg, $detail = null, $code = 400) {
  $resp = ['error' => $msg];
  if ($detail !== null) $resp['detail'] = $detail;
  json_out($resp, $code);
}

$ym          = $_GET['ym'] ?? date('Y-m');
$status      = $_GET['status'] ?? 'pending'; // pending|approved|rejected|all
$plan_id     = (int)($_GET['plan_id'] ?? 0);
$operator_id = (int)($_GET['operator_id'] ?? 0);
$q           = trim($_GET['q'] ?? '');
$page        = max(1, (int)($_GET['page'] ?? 1));
$limit       = max(1, min(200, (int)($_GET['limit'] ?? 30)));
$off         = ($page - 1) * $limit;

$ym_q = $db->real_escape_string($ym);

$where = "WHERE s.ym = '$ym_q' ";

if (in_array($status, ['pending','approved','rejected'])) {
  $s_q = $db->real_escape_string($status);
  $where .= "AND s.status = '$s_q' ";
}

if ($plan_id > 0) {
  $where .= "AND s.plan_id = $plan_id ";
}
if ($operator_id > 0) {
  $where .= "AND s.operator_id = $operator_id ";
}
if ($q !== '') {
  $qq = $db->real_escape_string($q);
  $where .= "AND (o.name LIKE '%$qq%' OR o.code LIKE '%$qq%' OR i.title LIKE '%$qq%' OR p.name LIKE '%$qq%' OR s.username_ref LIKE '%$qq%' OR s.text_content LIKE '%$qq%') ";
}

$join = "FROM kpi_submissions s
         JOIN operators o ON o.id = s.operator_id
         JOIN kpi_items i ON i.id = s.item_id
         JOIN kpi_plans p ON p.id = s.plan_id
         LEFT JOIN users u ON u.id = s.reviewer_id ";

// 1) COUNT
$count_sql = "SELECT COUNT(*) AS total $join $where";
$count_res = $db->query($count_sql);
if (!$count_res) {
  out_bad('DB error (count)', $db->error, 500);
}
$total_row = $count_res->fetch_assoc();
$total = (int)($total_row['total'] ?? 0);

// 2) DATA
$data_sql = "SELECT
               s.id, s.plan_id, s.item_id, s.operator_id, s.ym,
               s.text_content, s.username_ref, s.file_path, s.file_hash, s.file_mime, s.file_size,
               s.status, s.reviewer_id, s.review_note, s.created_at, s.reviewed_at,
               o.name AS operator_name, o.code AS operator_code,
               i.title AS item_title,
               p.name AS plan_name,
               u.username AS reviewer_name
             $join
             $where
             ORDER BY s.created_at DESC
             LIMIT $off, $limit";
$data_res = $db->query($data_sql);
if (!$data_res) {
  out_bad('DB error (query)', $db->error, 500);
}

$rows = [];
while ($r = $data_res->fetch_assoc()) $rows[] = $r;

json_out(['rows' => $rows, 'total' => $total, 'page' => $page, 'limit' => $limit, 'ok' => true]);
