<?php
/**
 * Reverse the starting order of run=2 based on final placements from run=1.
 * The best from run=1 receives the highest new_order in run=2 => 
 *    new_order = maxOrder + (countR2 - rank + 1).
 */
function reversedStartingOrder($conn, $runId, $competitionCode) {
	// 1) Find schedule, group, and run info
	$sql = "SELECT schedule, `group`, `run` FROM runs WHERE id = ? LIMIT 1";
	$stmt = $conn->prepare($sql);
	$stmt->bind_param("i", $runId);
	$stmt->execute();
	$res = $stmt->get_result();
	$row = $res->fetch_assoc();
	$stmt->close();

	if (!$row) {
		return;
	}

	$scheduleId = (int)$row['schedule'];
	$groupNum   = (int)$row['group'];
	$thisRunNum = (int)$row['run'];

	// Only reorder if we just finished run=1
	if ($thisRunNum !== 1) {
		return;
	}

	// 2) Gather run=1 data => athlete => best_of_run1
	$athleteMap = [];

	$runsSql = "
		SELECT r.id AS run_id, r.athlete AS athlete_id, r.status AS run_status
		FROM runs r
		WHERE r.schedule = ?
		  AND r.`group`  = ?
		  AND r.run      = 1
	";
	$stmtRuns = $conn->prepare($runsSql);
	$stmtRuns->bind_param("ii", $scheduleId, $groupNum);
	$stmtRuns->execute();
	$resRuns = $stmtRuns->get_result();

	if ($resRuns->num_rows === 0) {
		$stmtRuns->close();
		return;
	}

	while ($rr = $resRuns->fetch_assoc()) {
		$run1Id    = (int)$rr["run_id"];
		$athleteId = (int)$rr["athlete_id"];
		$runStatus = (int)$rr["run_status"];

		if ($athleteId < 1) {
			continue;
		}
		if (!isset($athleteMap[$athleteId])) {
			$athleteMap[$athleteId] = ["best_of_run1" => 0.0, "dsq" => false];
		}
		if ($runStatus === 4) {
			$athleteMap[$athleteId]["dsq"] = true;
			continue;
		}
		if ($runStatus === 2 || $runStatus === 3) {
			continue;
		}

		$scoreQuery = "SELECT score FROM scores WHERE run = ?";
		$stmtScore  = $conn->prepare($scoreQuery);
		if (!$stmtScore) {
			continue;
		}
		$stmtScore->bind_param("i", $run1Id);
		$stmtScore->execute();
		$resScore = $stmtScore->get_result();

		$sum = 0.0;
		$count = 0;
		while ($sRow = $resScore->fetch_assoc()) {
			$sum += (float)$sRow["score"];
			$count++;
		}
		$stmtScore->close();

		$thisRunTotal = ($count > 0) ? ($sum / $count) : 0.0;
		if ($thisRunTotal > $athleteMap[$athleteId]["best_of_run1"]) {
			$athleteMap[$athleteId]["best_of_run1"] = $thisRunTotal;
		}
	}
	$stmtRuns->close();

	// 3) Sort descending by best_of_run1
	$athList = [];
	foreach ($athleteMap as $aId => $data) {
		if ($data["dsq"]) {
			$data["best_of_run1"] = 0.0;
		}
		$athList[] = [
			"athlete_id"   => $aId,
			"best_of_run1" => (float)$data["best_of_run1"],
			"dsq"          => (bool)$data["dsq"],
		];
	}
	usort($athList, function($a, $b) {
		return $b["best_of_run1"] <=> $a["best_of_run1"];
	});

	// 4) Gather run=2 => find maxOrder => reorder
	$runs2 = [];
	$sql2 = "
		SELECT id, athlete, `order`
		FROM runs
		WHERE schedule = ?
		  AND `group` = ?
		  AND run = 2
		ORDER BY `order` ASC
	";
	$stmt2 = $conn->prepare($sql2);
	$stmt2->bind_param("ii", $scheduleId, $groupNum);
	$stmt2->execute();
	$res2 = $stmt2->get_result();

	$maxOrder = 0;
	while ($r = $res2->fetch_assoc()) {
		$runs2[] = [
			'id'       => (int)$r['id'],
			'athlete'  => (int)$r['athlete'],
			'oldOrder' => (int)$r['order']
		];
	}
	$stmt2->close();

	if (empty($runs2)) {
		return;
	}

	// 5) Create rank map => best => rank=1
	$placementRankMap = [];
	foreach ($athList as $idx => $item) {
		$placementRankMap[$item["athlete_id"]] = ($idx + 1);
	}

	$countR2  = count($runs2);
	$maxOrder = $countR2;

	// 6) new_order = maxOrder + (countR2 - rank + 1)
	$finalOrderMap = [];
	foreach ($runs2 as $r2) {
		$rank     = $placementRankMap[$r2['athlete']] ?? ($countR2 + 99);
		$newOrder = $maxOrder + ($countR2 - $rank + 1);
		//$newOrder = ($countR2 - $rank + 1);
		$finalOrderMap[] = [
			'run2_id'   => $r2['id'],
			'athlete'   => $r2['athlete'],
			'old_order' => $r2['oldOrder'],
			'rank'      => $rank,
			'new_order' => $newOrder
		];
	}
	usort($finalOrderMap, function($a, $b) {
		return $a['new_order'] <=> $b['new_order'];
	});

	// 7) Update DB
	foreach ($finalOrderMap as $item) {
		$updSql = "UPDATE runs SET `order` = ? WHERE id = ? LIMIT 1";
		$stmtU = $conn->prepare($updSql);
		$stmtU->bind_param("ii", $item['new_order'], $item['run2_id']);
		$stmtU->execute();
		$stmtU->close();
	}
}

?>