<?php
/*******************************************************
 * teamResults.php
 * Returns team results grouped by country,
 * also including event_name and category_name fields
 * for each athlete’s event/category.
 *
 * GET parameter: ?competitionCode=XXXX
 *******************************************************/

// 0) Check for required parameter
$competitionCode = isset($_GET['competitionCode']) ? trim($_GET['competitionCode']) : null;
if (!$competitionCode) {
	echo json_encode([
		"status"  => false,
		"message" => "Missing competitionCode parameter."
	]);
	exit;
}

// 1) Look up competition ID
try {
	$stmt = $conn->prepare("
		SELECT id
		FROM competitions
		WHERE code = ?
		LIMIT 1
	");
	if (!$stmt) {
		throw new Exception("Prepare failed: " . $conn->error);
	}
	$stmt->bind_param("s", $competitionCode);
	$stmt->execute();
	$result       = $stmt->get_result();
	$competition  = $result->fetch_assoc();
	$stmt->close();

	if (!$competition) {
		echo json_encode([
			"status"  => false,
			"message" => "No competition found with code '$competitionCode'."
		]);
		exit;
	}

	$competitionId = (int)$competition['id'];

} catch (Exception $e) {
	echo json_encode([
		"status"  => false,
		"message" => "Error fetching competition: " . $e->getMessage()
	]);
	exit;
}

// 2) Query the athletes + event/category
try {
	$query = "
		SELECT
			a.id            AS athlete_id,
			a.first_name    AS first_name,
			a.last_name     AS last_name,
			a.country       AS country_code,
			a.final_placement,
			cty.name        AS country_name,
			r.five_star     AS ranking_points,
			e.event_name    AS event_name,
			cat.name        AS category_name

		FROM athletes a

		-- Countries join
		LEFT JOIN countries cty
			ON a.country = cty.code

		-- If you have a table that maps final_placement => ranking points
		LEFT JOIN ranking_points r
			ON a.final_placement = r.rank

		-- Join events by matching a.event_code => e.event_code AND e.competition_code => a.competition_code
		LEFT JOIN events e
			ON a.event_code = e.event_code
		   AND e.competition_code = a.competition_code

		-- Join categories table if your schema tracks them via athlete + gender
		LEFT JOIN categories cat
			ON a.gender = cat.gender
		   AND a.category_in_competition = cat.code

		WHERE a.competition_code = ?

		ORDER BY a.country, a.final_placement
	";

	$stmt = $conn->prepare($query);
	if (!$stmt) {
		throw new Exception("Prepare failed: " . $conn->error);
	}
	$stmt->bind_param("s", $competitionCode);
	$stmt->execute();
	$result = $stmt->get_result();
	$rows   = $result->fetch_all(MYSQLI_ASSOC);
	$stmt->close();

	if (!$rows) {
		// No athlete data => just return empty
		echo json_encode([
			"status" => true,
			"data"   => []
		]);
		exit;
	}

	/**
	 * 2A) First pass: Determine how many distinct countries are present
	 *     for each (event_name, category_name).
	 */
	$groupCountries = [];  
	// Example structure => $groupCountries["Wakeboard__Open Men"] = ["GER","FRA","GBR"]

	foreach ($rows as $r) {
		$evt = $r["event_name"]    ?: "_no_evt_";
		$cat = $r["category_name"] ?: "_no_cat_";
		$key = $evt . "__" . $cat;

		$countryCode = $r["country_code"] ?: "UNK";

		if (!isset($groupCountries[$key])) {
			$groupCountries[$key] = [];
		}
		$groupCountries[$key][$countryCode] = true;  // Using an assoc for uniqueness
	}

	/**
	 * 2B) Second pass: For each row => if that row's (event_name, category_name)
	 *     has fewer than 3 distinct countries => divide the ranking_points by 2
	 */
	foreach ($rows as &$r) {
		$evt = $r["event_name"]    ?: "_no_evt_";
		$cat = $r["category_name"] ?: "_no_cat_";
		$key = $evt . "__" . $cat;

		$distinctCountriesCount = isset($groupCountries[$key])
			? count($groupCountries[$key])
			: 0;

		// If fewer than 3 countries => half the ranking points
		if ($distinctCountriesCount > 0 && $distinctCountriesCount < 3) {
			$r["ranking_points"] = (int) floor( ($r["ranking_points"] ?? 0) / 2 );
		}
	}
	unset($r); // Good practice for references

	/**
	 * 3) Build an array grouped by country
	 */
	$teams = [];
	foreach ($rows as $row) {
		$countryCode = $row['country_code'] ?: 'UNK';
		$countryName = !empty($row['country_name'])
			? $row['country_name']
			: $countryCode;

		// If not existing => init
		if (!isset($teams[$countryCode])) {
			$teams[$countryCode] = [
				"country"      => $countryCode,
				"country_name" => $countryName,
				"team_points"  => 0, // We'll recalc after we set status
				"athletes"     => []
			];
		}

		// Add the athlete (status=0 by default, we’ll fix later)
		$teams[$countryCode]["athletes"][] = [
			"athlete_id"      => (int)$row["athlete_id"],
			"athlete_name"    => trim($row["first_name"] . " " . $row["last_name"]),
			"country_code"    => $countryCode,
			"final_placement" => (int)($row["final_placement"] ?? 0),
			"ranking_points"  => (int)($row["ranking_points"]   ?? 0),
			// The new fields
			"event_name"      => $row["event_name"]    ?: null,
			"category_name"   => $row["category_name"] ?: null,
			"status"          => 0  // We will assign 1 only to the best in each group
		];
	}

	/**
	 * 4) For each country’s "athletes" => group by (event_name, category_name)
	 *    => best final_placement => status=1, all others => status=0
	 */
	foreach ($teams as $cCode => &$countryData) {
		$byEventCat = []; // e.g. [ "Wakeboard__OpenMen" => [index1, index2,...], ... ]

		// (a) Group athlete indexes by event+category
		foreach ($countryData["athletes"] as $idx => $ath) {
			$evt  = $ath["event_name"]    ?: "_no_evt_";
			$cat  = $ath["category_name"] ?: "_no_cat_";
			$key  = $evt . "__" . $cat;

			if (!isset($byEventCat[$key])) {
				$byEventCat[$key] = [];
			}
			$byEventCat[$key][] = $idx;
		}

		// (b) For each event+category => find the best final_placement => set status=1
		foreach ($byEventCat as $groupKey => $indexes) {
			// Sort indexes by final_placement ascending
			usort($indexes, function($idxA, $idxB) use ($countryData){
				return ($countryData["athletes"][$idxA]["final_placement"]
					  - $countryData["athletes"][$idxB]["final_placement"]);
			});

			// The first in that sorted list => best => status=1
			if (!empty($indexes)) {
				$bestIdx = $indexes[0];
				$countryData["athletes"][$bestIdx]["status"] = 1;
			}
		}

		// (c) Recalculate team_points: sum only athletes with status=1
		$sumPoints = 0;
		foreach ($countryData["athletes"] as $athlete) {
			if ($athlete["status"] === 1) {
				$sumPoints += $athlete["ranking_points"];
			}
		}
		$countryData["team_points"] = $sumPoints;
	}
	unset($countryData);

	// Convert associative array to an indexed one
	$teamResults = array_values($teams);

	// 5) Output JSON
	echo json_encode([
		"status" => true,
		"data"   => $teamResults
	]);
	exit;

} catch (Exception $e) {
	echo json_encode([
		"status"  => false,
		"message" => "Error fetching team results: " . $e->getMessage()
	]);
	exit;
}