D:\host\scoreman.in\upstate2025closed\sync_results.php

<?php
// sync_results.php  (no mysqli_stmt_get_result; PHP 5.6 compatible)
header('Content-Type: application/json; charset=UTF-8');
date_default_timezone_set('Asia/Kolkata');

if (isset($_GET['ping'])) { echo json_encode(['ok'=>1,'ts'=>time()]); exit; }

// ---------- configure ----------
$SECRET = 'change-me-to-a-long-random-secret';

$ALLOWED_FIELDS = array_flip([
  'rink1','rink2','rink3','road1','road2',
  'rink4','rink5','rink6','rink7',
  'p2p','elim',
  'road3','road4','road5',
  'p2prd','elimrd',
  'relay','mixedrelay','marathon'
]);

$VALID_CODES = array_flip(['1','2','3','P','A']);           // keep as posted
$RANK = ['1'=>5, '2'=>4, '3'=>3, 'P'=>2, 'A'=>1];           // strength

// ---------- auth ----------
$hdrs = function_exists('getallheaders') ? getallheaders() : [];
$syncKey = '';
if (isset($hdrs['X-Sync-Key']))             $syncKey = $hdrs['X-Sync-Key'];
elseif (isset($_SERVER['HTTP_X_SYNC_KEY'])) $syncKey = $_SERVER['HTTP_X_SYNC_KEY'];

if ($SECRET !== '' && $syncKey !== $SECRET) {
  http_response_code(401);
  echo json_encode(['ok'=>0,'fail'=>0,'skip'=>0,'errors'=>['Unauthorized']]);
  exit;
}

// ---------- load JSON ----------
$raw  = file_get_contents('php://input');
$body = json_decode($raw, true);
if (!is_array($body)) {
  echo json_encode(['ok'=>0,'fail'=>0,'skip'=>0,'errors'=>['Invalid JSON']]); exit;
}
$repdistrict = isset($body['repdistrict']) ? trim((string)$body['repdistrict']) : '';
$results     = (isset($body['results']) && is_array($body['results'])) ? $body['results'] : [];
if ($repdistrict === '' || empty($results)) {
  echo json_encode(['ok'=>0,'fail'=>0,'skip'=>0,'errors'=>['Missing repdistrict or results']]); exit;
}

// ---------- DB ----------
require_once __DIR__ . '/dbconnect.php';
if (!($con instanceof mysqli)) {
  echo json_encode(['ok'=>0,'fail'=>0,'skip'=>0,'errors'=>['DB not connected']]); exit;
}
$con->set_charset('utf8mb4');
mysqli_report(MYSQLI_REPORT_OFF);
set_time_limit(300);

// ---- helpers: portable fetch (no mysqlnd) ----
function stmt_fetch_assoc_once($stmt) {
  // store and bind dynamically
  mysqli_stmt_store_result($stmt);
  $meta = mysqli_stmt_result_metadata($stmt);
  if (!$meta) return null;
  $fields = []; $rowBuf = []; $bind = [];
  while ($f = mysqli_fetch_field($meta)) {
    $fields[] = $f->name;
    $bind[] = &$rowBuf[$f->name];
  }
  call_user_func_array('mysqli_stmt_bind_result', array_merge([$stmt], $bind));
  if (!mysqli_stmt_fetch($stmt)) { mysqli_free_result($meta); return null; }
  $row = [];
  foreach ($fields as $name) $row[$name] = $rowBuf[$name];
  mysqli_free_result($meta);
  return $row;
}

// cache prepared statements by field
$updateStmt = [];
$checkStmt  = [];

$getUpdateStmt = function($field) use ($con, &$updateStmt) {
  if (!isset($updateStmt[$field])) {
    $sql = "UPDATE `applications` SET `$field`=? 
            WHERE `appno`=? AND `repdistrict`=? AND `order_status`='Success'";
    $st = mysqli_prepare($con, $sql);
    if (!$st) throw new RuntimeException('Prepare failed: '.$con->error);
    $updateStmt[$field] = $st;
  }
  return $updateStmt[$field];
};

$getCheckStmt = function($field) use ($con, &$checkStmt) {
  if (!isset($checkStmt[$field])) {
    $sql = "SELECT `$field`, `order_status`
            FROM `applications`
            WHERE `appno`=? AND `repdistrict`=? LIMIT 1";
    $st = mysqli_prepare($con, $sql);
    if (!$st) throw new RuntimeException('Prepare failed: '.$con->error);
    $checkStmt[$field] = $st;
  }
  return $checkStmt[$field];
};

// ---------- process ----------
$ok=0; $skip=0; $fail=0; $errors=[];

foreach ($results as $idx => $row) {
  $pid   = isset($row['pid'])   ? trim((string)$row['pid'])   : '';
  $field = isset($row['field']) ? trim((string)$row['field']) : '';
  $code  = isset($row['code'])  ? strtoupper(trim((string)$row['code'])) : '';
  $race  = isset($row['race'])  ? (string)$row['race'] : ''; // optional info

  if ($pid === '' || $field === '' || !isset($ALLOWED_FIELDS[$field])) {
    $skip++; $errors[] = "[$idx] skip (bad pid/field): pid='$pid' field='$field'"; continue;
  }
  if (!isset($VALID_CODES[$code])) {
    $skip++; $errors[] = "[$idx] skip (bad code '$code') for field '$field' pid='$pid'"; continue;
  }

  try {
    // read current
    $chk = $getCheckStmt($field);
    mysqli_stmt_bind_param($chk, 'ss', $pid, $repdistrict);
    if (!mysqli_stmt_execute($chk)) {
      $fail++; $errors[] = "[$idx] check failed: ".$con->error; continue;
    }
    $r = stmt_fetch_assoc_once($chk);
    if (!$r) {
      $fail++; $errors[] = "[$idx] no matching row for pid='$pid' repdistrict='$repdistrict'"; continue;
    }
    $curr = (string)$r[$field];
    $ord  = (string)$r['order_status'];

    if ($ord !== 'Success') {
      $skip++; $errors[] = "[$idx] row order_status='$ord' (not Success) pid='$pid'"; continue;
    }

    $currRank = isset($VALID_CODES[$curr]) ? $RANK[$curr] : 0;
    $newRank  = $RANK[$code];

    if ($curr !== '' && $currRank > $newRank) {
      $skip++; $errors[] = "[$idx] skip downgrade (have '$curr' > incoming '$code') pid='$pid' field='$field'"; continue;
    }
    if ($curr === $code) { $skip++; continue; }

    // update
    $st = $getUpdateStmt($field);
    mysqli_stmt_bind_param($st, 'sss', $code, $pid, $repdistrict);
    if (!mysqli_stmt_execute($st)) {
      $fail++; $errors[] = "[$idx] exec failed: ".$con->error; continue;
    }

    $aff = mysqli_stmt_affected_rows($st);
    if ($aff > 0) { $ok++; }
    else {
      // recheck (no get_result)
      $chk2 = $getCheckStmt($field);
      mysqli_stmt_bind_param($chk2, 'ss', $pid, $repdistrict);
      if (mysqli_stmt_execute($chk2)) {
        $r2 = stmt_fetch_assoc_once($chk2);
        if ($r2) {
          $curr2 = (string)$r2[$field];
          if ($curr2 === $code) $skip++;
          else { $fail++; $errors[] = "[$idx] unexpected no-op pid='$pid' field='$field' curr='$curr2' want='$code'"; }
        } else {
          $fail++; $errors[] = "[$idx] unexpected recheck miss pid='$pid'";
        }
      } else {
        $fail++; $errors[] = "[$idx] recheck failed: ".$con->error;
      }
    }
  } catch (Exception $ex) {
    $fail++; $errors[] = "[$idx] exception: ".$ex->getMessage();
  }
}

// ---------- tidy ----------
foreach ($updateStmt as $st) { @mysqli_stmt_close($st); }
foreach ($checkStmt  as $st) { @mysqli_stmt_close($st); }
$con->close();

if (count($errors) > 50) { $errors = array_slice($errors, 0, 50); $errors[] = '... (truncated)'; }

echo json_encode(['ok'=>$ok,'skip'=>$skip,'fail'=>$fail,'errors'=>$errors]);