<?php

function groupAthletesByEventCategoryGender($conn, $competitionCode)
{
	if (empty($competitionCode)) {
		return ["success" => false, "error" => "Der Wettbewerbscode ist leer."];
	}

	// 1) Find the competition ID
	$compQuery = "SELECT id FROM competitions WHERE code = ? LIMIT 1";
	$compStmt = $conn->prepare($compQuery);
	if (!$compStmt) {
		return [
			"success" => false,
			"error"   => "Fehler beim Vorbereiten der Wettbewerb-Abfrage: " . $conn->error
		];
	}
	$compStmt->bind_param("s", $competitionCode);
	$compStmt->execute();
	$compResult = $compStmt->get_result();
	$competition = $compResult->fetch_assoc();
	$compStmt->close();

	if (!$competition) {
		return [
			"success" => false,
			"error"   => "Keine Competition mit dem Code '$competitionCode' gefunden."
		];
	}

	$competitionId = $competition['id'];

	// 2) Query all athletes for this competition
$athletesQuery = "
		SELECT 
			a.id              AS athlete_id,
			a.first_name      AS athlete_firstname,
			a.last_name       AS athlete_lastname,
			a.gender          AS athlete_gender,
			a.event_code      AS event_code,
			a.category_in_competition AS category,
			a.ranking_place   AS athlete_ranking
		FROM athletes a
		WHERE a.competition_code = ?
		ORDER BY
		  a.event_code,
		  a.category_in_competition,
		  a.gender,
		  /* FIRST: non-zero places (false=0) -> THEN zero places (true=1) */
		  (a.ranking_place = 0) ASC,
		  /* THEN among non-zero places, sort ascending by ranking_place */
		  a.ranking_place ASC
	";
	$stmt = $conn->prepare($athletesQuery);
	if (!$stmt) {
		return [
			"success" => false,
			"error"   => "Fehler beim Vorbereiten der Athleten-Abfrage: " . $conn->error
		];
	}

	$stmt->bind_param("s", $competitionCode);
	$stmt->execute();
	$result = $stmt->get_result();

	// 3) We'll store data in an associative array keyed by event_code
	$byEvent = [];

	// We can track separate ranking across changes in gender or category if needed,
	// but often the athlete_ranking is stored in DB. For demonstration, 
	// we’ll keep the existing approach for re-assigning rankingPlace 
	// whenever we see a new gender or category. 
	$lastKey = '';    // We'll track "event_code-category-gender" to reset ranking when that changes
	$rankingPlace = 1;

	while ($row = $result->fetch_assoc()) {
		$eventCode = $row['event_code'] ?: 'UNKNOWN_EVENT';
		$category  = $row['category']    ?: 'UNKNOWN_CAT';
		$gender    = $row['athlete_gender'] ?: 'X';

		// We'll build a key => "eventCode-category-gender"
		$thisKey = $eventCode . '-' . $category . '-' . $gender;
		if ($thisKey !== $lastKey) {
			$rankingPlace = 1;
			$lastKey = $thisKey;
		}

		// Make sure we have an array for this event code
		if (!isset($byEvent[$eventCode])) {
			$byEvent[$eventCode] = [
				'event_code' => $eventCode,
				'athletes'   => []    // we'll just push athletes here
			];
		}

		// Optional: get the category_id for schedule linking
		$categoryIdQuery = "
			SELECT id 
			FROM categories 
			WHERE code = ? 
			  AND gender = ?
			LIMIT 1
		";
		$catStmt = $conn->prepare($categoryIdQuery);
		if (!$catStmt) {
			// If it fails, skip
			continue;
		}
		$catStmt->bind_param("ss", $category, $gender);
		$catStmt->execute();
		$catRes = $catStmt->get_result();
		$category_id = $catRes->fetch_assoc()['id'] ?? null;
		$catStmt->close();

		// Attempt to find a schedule row for round=1
		$schedule = null;
		if ($category_id) {
			$scheduleQuery = "
				SELECT r.schedule 
				FROM runs r
				JOIN schedule s ON r.schedule = s.id 
				JOIN competitions c ON s.comp = c.id
				JOIN heatsystem h ON s.heatsystem = h.id
				WHERE h.round = 1
				  AND s.category = ?
				  AND c.code = ?
				  AND s.event = (
					  SELECT e.id
					  FROM events e
					  WHERE e.event_code = ?
						AND e.competition_code = ?
					  LIMIT 1
				  )
				LIMIT 1
			";
			$schedStmt = $conn->prepare($scheduleQuery);
			if ($schedStmt) {
				$schedStmt->bind_param("isss", $category_id, $competitionCode, $eventCode, $competitionCode);
				$schedStmt->execute();
				$schedRes = $schedStmt->get_result();
				$schedule = $schedRes->fetch_assoc()['schedule'] ?? null;
				$schedStmt->close();
			}
		}

		// Build an athlete object
		$athleteObj = [
			"ranking_place" => $rankingPlace,
			"athlete_id"    => $row['athlete_id'],
			"name"          => $row['athlete_firstname'] . " " . $row['athlete_lastname'],
			"ranking_points"=> $row['athlete_ranking'],
			"gender"        => $gender,
			"category"      => $category,
			"schedule"      => $schedule
		];

		// Add to byEvent[eventCode]['athletes']
		$byEvent[$eventCode]['athletes'][] = $athleteObj;

		$rankingPlace++;
	}

	$stmt->close();

	// 4) Reformat the final result => an array of events, each with event_code + athletes
	// The array_values() ensures we turn the assoc array into a numeric array
	$finalData = array_values($byEvent);

	return ["success" => true, "data" => $finalData];
}

/**
 * Bestimmt einen Standard-Schedule, wenn keiner gefunden wird.
 */
function getDefaultSchedule($conn, $category_id) {
	$defaultQuery = "SELECT id FROM schedule WHERE category = ? LIMIT 1";
	$defaultStmt = $conn->prepare($defaultQuery);
	if (!$defaultStmt) {
		return -1; // fallback if query fails
	}
	$defaultStmt->bind_param("i", $category_id);
	$defaultStmt->execute();
	$result = $defaultStmt->get_result();
	$defaultSchedule = $result->fetch_assoc()['id'] ?? -1;
	$defaultStmt->close();

	return $defaultSchedule;
}

/**
 * Updates runs with the athlete_id from JSON data.
 */
function updateRunsWithAthleteIdFromJson($conn, $data) {
	 // 1) Basic checks
	 if (empty($data['data']) || !is_array($data['data'])) {
		 return ["success" => false, "error" => "Keine oder ungültige Athleten-Daten übergeben."];
	 }
 
	 // For debug (optional)
	 // print_r($data);
 
	 // 2) Prepare your UPDATE statement once
	 $updateQuery = "
		 UPDATE runs
			SET athlete = ?
		  WHERE schedule = ?
			AND origin = CONCAT('0,', ?)
	 ";
	 $updateStmt = $conn->prepare($updateQuery);
	 if (!$updateStmt) {
		 return [
			 "success" => false,
			 "error"   => "Fehler beim Vorbereiten der Update-Abfrage: " . $conn->error
		 ];
	 }
 
	 $updatedRows = 0;
 
	 /**
	  * 3) data => array of event blocks:
	  *  [
	  *    [ "event_code" => "E703", "athletes" => [ {...}, {...} ] ],
	  *    [ "event_code" => "E704", "athletes" => [ {...}, {...} ] ],
	  *  ]
	  */
	 foreach ($data['data'] as $eventBlock) {
		 // Make sure eventBlock has 'athletes' array
		 if (empty($eventBlock['athletes']) || !is_array($eventBlock['athletes'])) {
			 continue;
		 }
 
		 // 4) Iterate over each athlete item
		 foreach ($eventBlock['athletes'] as $athlete) {
			 $athleteId    = $athlete['athlete_id']    ?? null;
			 $scheduleId   = $athlete['schedule']      ?? null;
			 $rankingPlace = $athlete['ranking_place'] ?? null;
 
			 // Skip if any required piece is missing
			 if (!$athleteId || !$scheduleId || !$rankingPlace) {
				 continue;
			 }
 
			 // 5) Bind and execute update
			 $updateStmt->bind_param("iii", $athleteId, $scheduleId, $rankingPlace);
			 if ($updateStmt->execute() && $updateStmt->affected_rows > 0) {
				 $updatedRows++;
			 }
		 }
	 }
 
	 $updateStmt->close();
 
	 return ["success" => true, "updated_rows" => $updatedRows];
 }

?>