<?php

/**
 * 1) Grab iwwf_id from the query string: ?iwwf_id=viewer or ?iwwf_id=M02854, etc.
 */
$iwwfId = isset($_GET['iwwf_id']) ? $_GET['iwwf_id'] : null;

if (!$iwwfId) {
	// If no iwwf_id at all, decide how to handle. 
	// We can show an empty array or show all. 
	// Here let's show an empty array:
	echo json_encode(["message" => "No iwwf_id provided."]);
	exit;
}

/**
 * 2) If iwwf_id = 'viewer', we show all competitions.
 *    Otherwise, we only show competitions for which the user has a row in `officials`.
 */
if ($iwwfId === "viewer") {
	// 2A) Show all competitions:
	$sql = "
		SELECT 
			c.id,
			c.code,
			c.name AS event_name,
			c.beginning_date,
			c.end_date,
			c.venue,
			c.tournament_type AS type,
	
			-- For 'disciplines' as before
			(
				SELECT GROUP_CONCAT(e.event_name SEPARATOR ', ')
				FROM events e
				WHERE e.competition_code = c.code
			) AS disciplines,
	
			-- NEW: subquery to get categories' full name from `categories` table
			(
				SELECT GROUP_CONCAT(DISTINCT cat.name SEPARATOR ', ')
				  FROM athletes a
				  JOIN categories cat 
					ON cat.code   = a.category_in_competition
				   AND cat.gender = a.gender
				 WHERE a.competition_code = c.code
			) AS categories,
	
			(
				SELECT COUNT(*) 
				FROM athletes 
				WHERE athletes.competition_code = c.code
			) AS athletes_registered,
	
			c.timestamp AS last_import,
	
			'Viewer' AS my_role,
			'assets/img/QR_code_for_mobile_English_Wikipedia.svg.png' AS qr_code_link,
			1 AS selected,
			0 AS export_results
	
		FROM competitions c
	";

	$stmt = $conn->prepare($sql);
	if (!$stmt) {
		echo json_encode(["error" => "Query prepare failed: " . $conn->error]);
		$conn->close();
		exit;
	}

	$stmt->execute();
	$result = $stmt->get_result();
	
} else {
	// 2B) If iwwf_id is something else, filter by `officials`.
	//  Only show competitions where iwwf_id matches the user in `officials`
$sql = "
	  SELECT 
		c.id,
		c.code,
		c.name            AS event_name,
		c.beginning_date,
		c.end_date,
		c.venue,
		c.tournament_type AS type,
	
		(
		  SELECT GROUP_CONCAT(e.event_name SEPARATOR ', ')
		  FROM events e
		  WHERE e.competition_code = c.code
		) AS disciplines,
	
		(
		  SELECT GROUP_CONCAT(DISTINCT cat.name SEPARATOR ', ')
		  FROM athletes a
		  JOIN categories cat
			ON cat.code   = a.category_in_competition
		   AND cat.gender = a.gender
		  WHERE a.competition_code = c.code
		) AS categories,
	
		(
		  SELECT COUNT(*)
		  FROM athletes 
		  WHERE athletes.competition_code = c.code
		) AS athletes_registered,
	
		c.timestamp AS last_import,
	
		-- label role: Council Admin first, then Federation Admin, else official
		CASE
		  WHEN u.council_admin = 1 
			THEN 'Council Admin'
		  WHEN u.fed_admin IS NOT NULL 
			AND u.fed_admin = c.organizing_country
			THEN 'Federation Admin'
		  ELSE o.position
		END AS my_role,
	
		'assets/img/QR_code_for_mobile_English_Wikipedia.svg.png' AS qr_code_link,
		1 AS selected,
	
		CASE
		  WHEN (
			(SELECT COUNT(*) 
			 FROM runs r2 
			 INNER JOIN schedule s ON r2.schedule = s.id 
			 WHERE s.comp = c.id) > 0
			AND
			(SELECT COUNT(*) 
			 FROM runs r2 
			 INNER JOIN schedule s ON r2.schedule = s.id 
			 WHERE s.comp = c.id AND r2.status = 0) = 0
		  ) THEN 1
		  ELSE 0
		END AS export_results
	
	  FROM competitions c
	
	  -- grab the user record
	  JOIN user u
		ON u.iwwf_id = ?
	
	  -- left‐join officials so non‐admins only see comps they’re on
	  LEFT JOIN officials o
		ON o.competition_code = c.code
	   AND o.iwwf_id         = u.iwwf_id
	
	  -- show:
	  --  • all comps if council_admin
	  --  • all comps where fed_admin = organizing_country
	  --  • otherwise only those they've got an officials row for
	  WHERE u.council_admin = 1
		 OR (u.fed_admin IS NOT NULL
			 AND u.fed_admin = c.organizing_country)
		 OR o.iwwf_id IS NOT NULL
	
	  ORDER BY c.beginning_date DESC
	";

	$stmt = $conn->prepare($sql);
	if (!$stmt) {
		echo json_encode(["error" => "Query prepare failed: " . $conn->error]);
		$conn->close();
		exit;
	}

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

$response = [];

if ($result->num_rows > 0) {
	while ($row = $result->fetch_assoc()) {
		// Convert 'disciplines' from CSV to array
		$disciplinesStr = $row["disciplines"] ?? "";
		$disciplinesArr = array_filter(array_map('trim', explode(',', $disciplinesStr)));

		// Convert 'age_group' (categories) from CSV to array
		$categoriesStr  = $row["categories"] ?? "";
		$categoriesArr  = array_filter(array_map('trim', explode(',', $categoriesStr)));

		$response[] = [
			"event" => [
				"id"                 => $row["id"],
				"code"               => $row["code"],
				"name"               => $row["event_name"],
				"dates"              => $row["beginning_date"] . " - " . $row["end_date"],
				"location"           => $row["venue"] ?? "Unknown",
				"type"               => $row["type"]  ?? "Unknown",
				"disciplines"        => $disciplinesArr,
				"categories"         => $categoriesArr,
				"athletes_registered"=> (int)$row["athletes_registered"],
				"last_import"        => $row["last_import"],
				// from 'officials' if iwwf_id != viewer, or 'Viewer' if iwwf_id=viewer
				"my_role"            => $row["my_role"] ?? "Viewer",
				"qr_code_link"       => $row["qr_code_link"],
				"selected"           => (int)$row["selected"],
				"export_results"     => (int)$row["export_results"]
			]
		];
	}
	echo json_encode($response, JSON_PRETTY_PRINT);
} else {
	echo json_encode(["message" => "No competitions found."]);
}

$stmt->close();

?>