<?php
// 1) Include your scoring logic (scoring.php must define getFinalScore(...)):
include('scoring.php'); // Assumes getFinalScore(...) is defined here

function finalPlacement(mysqli $conn, int $scheduleId, int $groupNumber) 
{
	// 2) Grab the current round & schedule info
	$infoSql = "
		SELECT 
			h.round         AS current_round,
			h.termination   AS termination,
			h.groups        AS groups,
			h.qualify       AS qualify,
			h.athletes      AS athletes,
			s.category      AS schedule_category,
			s.event         AS event,
			c.id,
			c.code          AS competitionCode
		FROM schedule s
		JOIN heatsystem h ON s.heatsystem = h.id
		JOIN competitions c ON s.comp = c.id
		WHERE s.id = ?
		LIMIT 1
	";
	$stmtInfo = $conn->prepare($infoSql);
	$stmtInfo->bind_param("i", $scheduleId);
	$stmtInfo->execute();
	$resInfo = $stmtInfo->get_result();
	$rowInfo = $resInfo->fetch_assoc();
	$stmtInfo->close();

	if (!$rowInfo) {
		return [
			'error' => "No data found for scheduleId=$scheduleId"
		];
	}

	// Current round data
	$round           = (int) $rowInfo["current_round"];
	$termination     = (int) $rowInfo["termination"];
	$groups          = (int) $rowInfo["groups"];
	$qualify         = (int) $rowInfo["qualify"];
	$category        = $rowInfo["schedule_category"]; 
	$event           = $rowInfo["event"];
	$competitionCode = $rowInfo["competitionCode"];
	$athletes        = $rowInfo["athletes"];

	// 3) Fetch all runs for this schedule & group
	//    (We group by run_id to avoid duplicates if multiple scores exist.)
	$runSql = "
		SELECT 
			r.id           AS run_id,
			r.schedule     AS schedule_id,
			r.group        AS group_number,
			r.athlete      AS athlete_id
		FROM runs r
		LEFT JOIN scores s ON s.run = r.id
		WHERE r.schedule = ?
		  AND r.group    = ?
		GROUP BY r.id
		ORDER BY r.id ASC
	";
	$stmtRuns = $conn->prepare($runSql);
	$stmtRuns->bind_param("ii", $scheduleId, $groupNumber);
	$stmtRuns->execute();
	$resRuns = $stmtRuns->get_result();
	$stmtRuns->close();

	// Store each run + its computed final score
	$runsWithScores = [];
	while ($rowRun = $resRuns->fetch_assoc()) {
		$runId = (int) $rowRun['run_id'];

		// 4) Call the scoring function for each run
		//    This should query scores table and compute final score 
		//    based on your judging mode (in scoring.php).
		$finalScore = getFinalScore($runId, $conn, $competitionCode);

		$runsWithScores[] = [
			'run_id'       => $runId,
			'schedule_id'  => (int) $rowRun['schedule_id'],
			'group_number' => (int) $rowRun['group_number'],
			'athlete_id'   => (int) $rowRun['athlete_id'],
			'final_score'  => $finalScore
		];
	}

	// 5) Group by athlete => find the highest final_score among all runs
	$athleteBestScores = [];  // Key: athlete_id => best final_score
	foreach ($runsWithScores as $runData) {
		$athleteId  = $runData['athlete_id'];
		$score      = $runData['final_score'];

		if (!isset($athleteBestScores[$athleteId]) || $score > $athleteBestScores[$athleteId]) {
			$athleteBestScores[$athleteId] = $score;
		}
	}

	// Convert to array for sorting
	$bestScoresList = [];
	foreach ($athleteBestScores as $athleteId => $bestScore) {
		$bestScoresList[] = [
			'athlete_id' => $athleteId,
			'best_score' => $bestScore
		];
	}

	// Sort descending by best_score
	usort($bestScoresList, function ($a, $b) {
		return $b['best_score'] <=> $a['best_score'];
	});

	// 6) Continue your existing logic, e.g. handle previous round
	$prevRoundData = null;
	if ($round > 1) {
		$prevRound = $round - 1;
		$prevSql = "
			SELECT
				athletes    AS prev_athletes,
				round       AS prev_round,
				termination AS prev_termination,
				qualify     AS prev_qualify
			FROM heatsystem 
			WHERE athletes = ?
			  AND round = ?
			LIMIT 1
		";
		$stmtPrev = $conn->prepare($prevSql);
		$stmtPrev->bind_param("ii", $athletes, $prevRound);
		$stmtPrev->execute();
		$resPrev = $stmtPrev->get_result();
		$rowPrev = $resPrev->fetch_assoc();
		$stmtPrev->close();

		if ($rowPrev) {
			$prevRoundData = [
				'prev_round'       => (int) $rowPrev['prev_round'],
				'prev_termination' => (int) $rowPrev['prev_termination'],
				'prev_qualify'     => (int) $rowPrev['prev_qualify'],
			];
		}
	}

	// Determine how many athletes continue if the previous round = no termination
	$termination_athletes = 0;
	if (!empty($rowPrev) && $rowPrev['prev_termination'] == 0) {
		$termination_athletes = $rowPrev['prev_qualify'];
	}

	// 7) final_placement calculation (from your example)
	if ($groups === 0) {
		return [
			'error' => "Groups is zero—cannot calculate final placement"
		];
	}
	
	// Prepare update statement once
	$upd = $conn->prepare("
		UPDATE athletes 
		   SET final_placement = ?
		 WHERE id = ?
	");
					 
	// Assign simple 1-based placement (1 = highest score, 2 = second highest, etc.)
	$placement = 1;
	foreach ($bestScoresList as $index => $data) {
		
		$placementValue = $index + 1; // Possibly from your logic
		$final_placement = $qualify 
						 + $termination_athletes 
						 + ((($placementValue - ($qualify / $groups)) * $groups) - ($groups - 1));
						 
		
		if($placement > ($qualify / $groups) && $termination == 1)
		{
			$bestScoresList[$index]['final_placement'] = $final_placement;
		}else
		{
			$bestScoresList[$index]['final_placement'] = NULL;
		}
		
		// write back to DB
		$upd->bind_param("ii", $bestScoresList[$index]['final_placement'], $bestScoresList[$index]['athlete_id']);
		$upd->execute();
		
		$bestScoresList[$index]['placement'] = $placement++;
		
	}
	$upd->close();

	// 8) Merge all data into the final result
	$result = [
		'round'                => $round,
		'termination'          => $termination,
		'groups'               => $groups,
		'qualify'              => $qualify,
		'category'             => $category,
		'event'                => $event,
		'competition_code'     => $competitionCode,
		'termination_athletes' => $termination_athletes,
		'placement_value'      => $placementValue,
		'final_placement'      => $final_placement,

		// All runs with their final_score
		'runs_with_scores'     => $runsWithScores,

		// Ranking by athlete => highest final_score => placement
		'ranking'              => $bestScoresList 
	];

	// Merge in previous round data if found
	if ($prevRoundData) {
		$result = array_merge($result, $prevRoundData);
	}

	//return $result;
	
}

// 2) Example usage of the function
// Let's say we have 12 athletes, max 4 per group, 3 groups, round 2, etc.:
/*include('dbconnect.php');
$schedule_id = $_GET['schedule'];
$groupNumber = $_GET['groupNumber'];

// Call the function
$result = finalPlacement(
	$conn,
	$schedule_id,
	$groupNumber
);

echo "<pre>";
print_r($result);
echo "</pre>";*/

?>
