<?php
// Only allow POST requests
if ($_SERVER['REQUEST_METHOD'] !== 'POST') {
	echo json_encode(["status" => "error", "message" => "Method not allowed"]);
	exit;
}

// assume $conn is your mysqli connection

// Read raw JSON input
$jsonData = file_get_contents('php://input');

// Attempt to decode JSON
$data = json_decode($jsonData, true);
if ($data === null && json_last_error() !== JSON_ERROR_NONE) {
	echo json_encode(["status" => "error", "message" => "Invalid JSON: " . json_last_error_msg()]);
	exit;
}

// Extract competition data
$competition = $data['Competition'];

// top‐level attribute "code"
$competition_code   = isset($competition['code'])
	? $conn->real_escape_string($competition['code'])
	: '';

// element text under ["…"]["_text"]
$competition_name   = isset($competition['Name']['_text'])
	? $conn->real_escape_string($competition['Name']['_text'])
	: '';
$discipline         = isset($competition['Discipline']['_text'])
	? $conn->real_escape_string($competition['Discipline']['_text'])
	: '';
$organizing_country = isset($competition['OrganizingCountry']['_text'])
	? $conn->real_escape_string($competition['OrganizingCountry']['_text'])
	: '';
$venue              = isset($competition['Venue']['_text'])
	? $conn->real_escape_string($competition['Venue']['_text'])
	: '';
$site_code          = isset($competition['SiteCode']['_text'])
	? $conn->real_escape_string($competition['SiteCode']['_text'])
	: '';
$beginning_date     = isset($competition['BeginningDate']['_text'])
	? $conn->real_escape_string($competition['BeginningDate']['_text'])
	: '';
$end_date           = isset($competition['EndDate']['_text'])
	? $conn->real_escape_string($competition['EndDate']['_text'])
	: '';

// TournamentType text and its attribute EmsCode
$tournament_type      = isset($competition['TournamentType']['_text'])
	? $conn->real_escape_string($competition['TournamentType']['_text'])
	: '';
$tournament_type_code = isset($competition['TournamentType']['EmsCode'])
	? $conn->real_escape_string($competition['TournamentType']['EmsCode'])
	: '';

// AgeGroup text and code
$age_group      = isset($competition['AgeGroup']['_text'])
	? $conn->real_escape_string($competition['AgeGroup']['_text'])
	: '';
$age_group_code = isset($competition['AgeGroup']['EmsCode'])
	? $conn->real_escape_string($competition['AgeGroup']['EmsCode'])
	: '';

// Insert or update competition
$sql = "SELECT 1 FROM competitions WHERE code = '$competition_code'";
if ($conn->query($sql)->num_rows) {
	$upd = "
		UPDATE competitions SET
			name = '$competition_name',
			discipline = '$discipline',
			organizing_country = '$organizing_country',
			tournament_type = '$tournament_type',
			tournament_code = '$tournament_type_code',
			venue = '$venue',
			site_code = '$site_code',
			beginning_date = '$beginning_date',
			end_date = '$end_date',
			age_group = '$age_group',
			timestamp = NOW()
		WHERE code = '$competition_code'
	";
	$conn->query($upd);
} else {
	$ins = "
		INSERT INTO competitions (
			code, name, discipline, organizing_country,
			tournament_type, tournament_code, venue, site_code,
			beginning_date, end_date, age_group, timestamp
		) VALUES (
			'$competition_code', '$competition_name', '$discipline', '$organizing_country',
			'$tournament_type','$tournament_type_code', '$venue', '$site_code',
			'$beginning_date', '$end_date', '$age_group', NOW()
		)
	";
	$conn->query($ins);
}

// ----------------------------------------------------------------------------
// EVENTS
// ----------------------------------------------------------------------------
if (!empty($competition['Events']['Event'])) {
	$events = $competition['Events']['Event'];
	if (!isset($events[0])) {
		$events = [$events];
	}
	foreach ($events as $ev) {
		$event_name = isset($ev['Event'])
			? $conn->real_escape_string($ev['Event'])
			: '';
		$event_code = isset($ev['EmsCode'])
			? $conn->real_escape_string($ev['EmsCode'])
			: '';
		$class_name = isset($ev['Class']['_text'])
			? $conn->real_escape_string($ev['Class']['_text'])
			: '';
		$class_ems_code = isset($ev['Class']['EmsCode'])
		? $conn->real_escape_string($ev['Class']['EmsCode'])
		: '';

		$chk = "
			SELECT 1 FROM events
			WHERE event_code = '$event_code'
			  AND competition_code = '$competition_code'
		";
		if ($conn->query($chk)->num_rows) {
			$upd = "
				UPDATE events SET
					event_name = '$event_name',
					class = '$class_name',
					ems_code = '$class_ems_code'
				WHERE event_code = '$event_code'
				  AND competition_code = '$competition_code'
			";
			$conn->query($upd);
		} else {
			$ins = "
				INSERT INTO events (
					event_code, competition_code, event_name, class, ems_code
				) VALUES (
					'$event_code', '$competition_code', '$event_name', '$class_name', '$class_ems_code'
				)
			";
			$conn->query($ins);
		}
	}
}

// ----------------------------------------------------------------------------
// JURY PANEL ⇒ OFFICIALS
// ----------------------------------------------------------------------------
if (!empty($competition['JuryPanel']['Official'])) {
	$ofs = $competition['JuryPanel']['Official'];
	if (!isset($ofs[0])) {
		$ofs = [$ofs];
	}
	foreach ($ofs as $o) {
		$IWWFId    = isset($o['IWWFId'])
			? $conn->real_escape_string($o['IWWFId'])
			: '';
		$position  = isset($o['Position']['_text'])
			? $conn->real_escape_string($o['Position']['_text'])
			: '';
		$ems_code  = isset($o['Position']['EmsCode'])
			? $conn->real_escape_string($o['Position']['EmsCode'])
			: '';
		$last      = isset($o['LastName']['_text'])
			? $conn->real_escape_string($o['LastName']['_text'])
			: '';
		$first     = isset($o['FirstName']['_text'])
			? $conn->real_escape_string($o['FirstName']['_text'])
			: '';
		$country   = isset($o['Country']['_text'])
			? $conn->real_escape_string($o['Country']['_text'])
			: '';
		$region    = isset($o['Region']['_text'])
			? $conn->real_escape_string($o['Region']['_text'])
			: '';

		$chk = "
			SELECT 1 FROM officials
			WHERE competition_code = '$competition_code'
			  AND last_name = '$last'
			  AND first_name = '$first'
		";
		if ($conn->query($chk)->num_rows) {
			$upd = "
				UPDATE officials SET
					position = '$position',
					ems_code = '$ems_code',
					country = '$country',
					region = '$region'
				WHERE competition_code = '$competition_code'
				  AND last_name = '$last'
				  AND first_name = '$first'
			";
			$conn->query($upd);
		} else {
			$ins = "
				INSERT INTO officials (
					competition_code,iwwf_id,position,ems_code,
					last_name,first_name,country,region
				) VALUES (
					'$competition_code','$IWWFId','$position','$ems_code',
					'$last','$first','$country','$region'
				)
			";
			$conn->query($ins);
		}
	}
}

// ----------------------------------------------------------------------------
// ATHLETES
// ----------------------------------------------------------------------------
$numAthletesFromXml  = 0;
$numAthletesInserted = 0;
$numAthletesUpdated  = 0;
$numAthletesDeleted  = 0;
$allXmlAthletes      = [];

if (!empty($competition['Athletes']['Athlete'])) {
	$ats = $competition['Athletes']['Athlete'];
	if (!isset($ats[0])) {
		$ats = [$ats];
	}
	$numAthletesFromXml = count($ats);

	foreach ($ats as $ath) {
		$IWWFId      = isset($ath['IWWFId'])
			? $conn->real_escape_string($ath['IWWFId'])
			: '';
		$last_name   = isset($ath['LastName']['_text'])
			? $conn->real_escape_string($ath['LastName']['_text'])
			: '';
		$first_name  = isset($ath['FirstName']['_text'])
			? $conn->real_escape_string($ath['FirstName']['_text'])
			: '';
		$country     = isset($ath['Country']['_text'])
			? $conn->real_escape_string($ath['Country']['_text'])
			: '';
		$gender      = isset($ath['Gender']['_text'])
			? $conn->real_escape_string($ath['Gender']['_text'])
			: '';
		$year_of_birth = isset($ath['YearOfBirth']['_text'])
			? $conn->real_escape_string($ath['YearOfBirth']['_text'])
			: '';

		if (!empty($ath['Events']['Event'])) {
			$evs = $ath['Events']['Event'];
			if (!isset($evs[0])) {
				$evs = [$evs];
			}
			foreach ($evs as $ev) {
				// pull all the per‐event fields from ["…"]["_text"]
				$event_code      = isset($ev['EmsCode'])
					? $conn->real_escape_string($ev['EmsCode'])
					: '';
				$entry_type      = isset($ev['EntryType']['_text'])
					? $conn->real_escape_string($ev['EntryType']['_text'])
					: '';
				$participation   = isset($ev['Participation']['_text'])
					? (int)filter_var($ev['Participation']['_text'], FILTER_VALIDATE_BOOLEAN)
					: 0;
				$cat_in_comp     = isset($ev['CategoryInCompetition']['_text'])
					? $conn->real_escape_string($ev['CategoryInCompetition']['_text'])
					: '';
				$birth_city      = isset($ev['BirthCity']['_text'])
					? $conn->real_escape_string($ev['BirthCity']['_text'])
					: '';
				$club            = isset($ev['Club']['_text'])
					? $conn->real_escape_string($ev['Club']['_text'])
					: '';
				$city            = isset($ev['City']['_text'])
					? $conn->real_escape_string($ev['City']['_text'])
					: '';
				$occupation      = isset($ev['Occupation']['_text'])
					? $conn->real_escape_string($ev['Occupation']['_text'])
					: '';
				$hobbies         = isset($ev['Hobbies']['_text'])
					? $conn->real_escape_string($ev['Hobbies']['_text'])
					: '';
				$favourite_food  = isset($ev['FavouriteFood']['_text'])
					? $conn->real_escape_string($ev['FavouriteFood']['_text'])
					: '';
				$favourite_music = isset($ev['FavouriteMusic']['_text'])
					? $conn->real_escape_string($ev['FavouriteMusic']['_text'])
					: '';
				$first_compet    = isset($ev['FirstCompetition']['_text'])
					? $conn->real_escape_string($ev['FirstCompetition']['_text'])
					: '';
				$started_ski     = isset($ev['StartedSkiingRiding']['_text'])
					? $conn->real_escape_string($ev['StartedSkiingRiding']['_text'])
					: '';
				$fav_trick       = isset($ev['FavouriteTrick']['_text'])
					? $conn->real_escape_string($ev['FavouriteTrick']['_text'])
					: '';
				$first_flip      = isset($ev['FirstFlipInvert']['_text'])
					? $conn->real_escape_string($ev['FirstFlipInvert']['_text'])
					: '';
				$training_site   = isset($ev['TrainingSite']['_text'])
					? $conn->real_escape_string($ev['TrainingSite']['_text'])
					: '';
				$coaches         = isset($ev['Coaches']['_text'])
					? $conn->real_escape_string($ev['Coaches']['_text'])
					: '';
				$sponsors        = isset($ev['Sponsors']['_text'])
					? $conn->real_escape_string($ev['Sponsors']['_text'])
					: '';
				$homepage        = isset($ev['HomePage']['_text'])
					? $conn->real_escape_string($ev['HomePage']['_text'])
					: '';
				$philosophy      = isset($ev['PhilosophyOfLife']['_text'])
					? $conn->real_escape_string($ev['PhilosophyOfLife']['_text'])
					: '';

				// RankingPlace nested under ["…"]["RankingListData"]["RankingListData"]["RankingPlace"]["_text"]
				$ranking_place = null;
				if (isset($ev['RankingListData']['RankingListData']['RankingPlace']['_text'])) {
					$ranking_place = (int)$ev['RankingListData']['RankingListData']['RankingPlace']['_text'];
				}
				// BestPerformance if present
				$best_perf = null;
				if (isset($ev['RankingListData']['RankingListData']['BestPerformance']['_text'])) {
					$best_perf = (float)$ev['RankingListData']['RankingListData']['BestPerformance']['_text'];
				}

				// track for deletes
				$lookupKey = $IWWFId . '___' . $event_code;
				$allXmlAthletes[$lookupKey] = true;

				// check existing
				$chkAth = "
					SELECT * FROM athletes
					WHERE fed_id = '$IWWFId'
					  AND competition_code = '$competition_code'
					  AND event_code = '$event_code'
				";
				$resAth = $conn->query($chkAth);

				if ($resAth->num_rows) {
					// compare & update if needed
					$exist = $resAth->fetch_assoc();
					$newData = [
						'last_name'              => $last_name,
						'first_name'             => $first_name,
						'country'                => $country,
						'gender'                 => $gender,
						'year_of_birth'          => $year_of_birth,
						'birth_city'             => $birth_city,
						'club'                   => $club,
						'city'                   => $city,
						'occupation'             => $occupation,
						'hobbies'                => $hobbies,
						'favourite_food'         => $favourite_food,
						'favourite_music'        => $favourite_music,
						'first_competition'      => $first_compet,
						'started_skiing_riding'  => $started_ski,
						'favourite_trick'        => $fav_trick,
						'first_flip_invert'      => $first_flip,
						'training_site'          => $training_site,
						'coaches'                => $coaches,
						'sponsors'               => $sponsors,
						'homepage'               => $homepage,
						'philosophy_of_life'     => $philosophy,
						'entry_type'             => $entry_type,
						'participation'          => $participation,
						'category_in_competition'=> $cat_in_comp,
						'ranking_place'          => $ranking_place,
						'best_performance'       => $best_perf,
					];
					$needsUpdate = false;
					foreach ($newData as $col => $val) {
						$old = $exist[$col];
						if ($col === 'ranking_place' && $old !== null) $old = (int)$old;
						if ($col === 'best_performance' && $old !== null) $old = (float)$old;
						if ($old != $val) {
							$needsUpdate = true;
							break;
						}
					}
					if ($needsUpdate) {
						$updAth = "
							UPDATE athletes SET
								last_name              = '$last_name',
								first_name             = '$first_name',
								country                = '$country',
								gender                 = '$gender',
								year_of_birth          = '$year_of_birth',
								birth_city             = '$birth_city',
								club                   = '$club',
								city                   = '$city',
								occupation             = '$occupation',
								hobbies                = '$hobbies',
								favourite_food         = '$favourite_food',
								favourite_music        = '$favourite_music',
								first_competition      = '$first_compet',
								started_skiing_riding  = '$started_ski',
								favourite_trick        = '$fav_trick',
								first_flip_invert      = '$first_flip',
								training_site          = '$training_site',
								coaches                = '$coaches',
								sponsors               = '$sponsors',
								homepage               = '$homepage',
								philosophy_of_life     = '$philosophy',
								entry_type             = '$entry_type',
								participation          = '$participation',
								category_in_competition= '$cat_in_comp',
								ranking_place          = " . ($ranking_place !== null ? $ranking_place : 0) . ",
								best_performance       = " . ($best_perf !== null ? "'$best_perf'" : "NULL") . "
							WHERE fed_id = '$IWWFId'
							  AND competition_code = '$competition_code'
							  AND event_code = '$event_code'
						";
						$conn->query($updAth);
						$numAthletesUpdated++;
					}
				} else {
					// insert new athlete row
					$insAth = "
						INSERT INTO athletes (
							fed_id, competition_code,
							last_name, first_name, country, gender, year_of_birth,
							birth_city, club, city, occupation, hobbies,
							favourite_food, favourite_music,
							first_competition, started_skiing_riding,
							favourite_trick, first_flip_invert, training_site,
							coaches, sponsors, homepage, philosophy_of_life,
							entry_type, participation, category_in_competition,
							event_code, ranking_place, best_performance
						) VALUES (
							'$IWWFId', '$competition_code',
							'$last_name', '$first_name', '$country', '$gender', '$year_of_birth',
							'$birth_city', '$club', '$city', '$occupation', '$hobbies',
							'$favourite_food', '$favourite_music',
							'$first_compet', '$started_ski',
							'$fav_trick', '$first_flip', '$training_site',
							'$coaches', '$sponsors', '$homepage', '$philosophy',
							'$entry_type', '$participation', '$cat_in_comp',
							'$event_code',
							" . ($ranking_place !== null ? $ranking_place : 0) . ",
							" . ($best_perf !== null ? "'$best_perf'" : "NULL") . "
						)
					";
					$conn->query($insAth);
					$numAthletesInserted++;
				}
			} // end foreach event
		} // end if athlete has events
	} // end foreach athlete
}

// DELETE any athlete no longer in JSON
$sqlAll = "
	SELECT fed_id, event_code
	FROM athletes
	WHERE competition_code = '$competition_code'
";
$resAll = $conn->query($sqlAll);
while ($row = $resAll->fetch_assoc()) {
	$key = $row['fed_id'] . '___' . $row['event_code'];
	if (!isset($allXmlAthletes[$key])) {
		$df = $conn->real_escape_string($row['fed_id']);
		$de = $conn->real_escape_string($row['event_code']);
		$del = "
			DELETE FROM athletes
			WHERE competition_code = '$competition_code'
			  AND fed_id = '$df'
			  AND event_code = '$de'
		";
		$conn->query($del);
		$numAthletesDeleted++;
	}
}

// -----------------------------------------------------------------------------
// Define the variable expected by generate_competition.php
// -----------------------------------------------------------------------------
$competitionCode = $competition_code;

// Optionally regenerate competition
include('generate_competition.php');

// Return summary
echo json_encode([
	'status'               => 'success',
	'message'              => 'Data processed successfully',
	'Competition Code:'    => $competitionCode,
	'Imported athletes:'   => $numAthletesFromXml,
	'Updated athletes:'    => $numAthletesUpdated,
	'Deleted athletes:'    => $numAthletesDeleted,
	'New athletes:'        => $numAthletesInserted,
	'Schedule information' => $scheduleAlert
]);


?>