<?php

function addHeatsystemRoundsToSchedule($conn, $competitionCode, $eventId, $category, $gender, $athleteCount) {
	global $scheduleAlert;
	
	

	// 1) Load competition & beginning_date
	$compQuery = "SELECT id, beginning_date FROM competitions WHERE code = ?";
	$compStmt  = $conn->prepare($compQuery);
	if (!$compStmt) {
		$scheduleAlert .= "Database error: Failed to prepare the competition query.\n\n";
		return;
	}
	$compStmt->bind_param("s", $competitionCode);
	$compStmt->execute();
	$compResult    = $compStmt->get_result();
	$compRow       = $compResult->fetch_assoc() ?: [];
	$competitionId = $compRow['id']             ?? null;
	$beginningDate = $compRow['beginning_date'] ?? null; 
	$compStmt->close();
	
	// 1) Retrieve the heatsystem from the settings table
	$sqlJudging = "SELECT heatsystem FROM settings WHERE competition_code = ?";
	$stmt = $conn->prepare($sqlJudging);
	if (!$stmt) {
		throw new Exception("Failed to prepare statement: " . $conn->error);
	}
	
	// competitionCode ist ein String, also "s"
	$stmt->bind_param("s", $competitionCode);
	$stmt->execute();
	
	// Result holen und auslesen
	$result = $stmt->get_result();
	$row    = $result->fetch_assoc();
	$heatsystem = isset($row['heatsystem'])
		? (int)$row['heatsystem']
		: null;
	
	$stmt->close();
	
	if ($heatsystem === null) {
		$scheduleAlert .= "Kein heatsystem in settings gefunden für Code '$competitionCode'.\n\n";
		return;
	}

	if (!$competitionId) {
		$scheduleAlert .= "No competition found for the code '$competitionCode'.\n\n";
		return;
	}
	if (!$beginningDate) {
		$scheduleAlert .= "No 'beginning_date' found for competition code '$competitionCode'.\n\n";
		return;
	}

	// 2) Load Category ID
	$categoryQuery = "SELECT id FROM categories WHERE code = ? AND gender = ?";
	$categoryStmt  = $conn->prepare($categoryQuery);
	if (!$categoryStmt) {
		$scheduleAlert .= "Database error: Failed to prepare the category query.\n\n";
		return;
	}
	$categoryStmt->bind_param("ss", $category, $gender);
	$categoryStmt->execute();
	$categoryResult = $categoryStmt->get_result();
	$categoryId     = $categoryResult->fetch_assoc()['id'] ?? null;
	$categoryStmt->close();

	if (!$categoryId) {
		$scheduleAlert .= "No category ID found for category code '$category' with gender '$gender'.\n\n";
		return;
	}

	// 3) Check if any run has run.status=1 => abort if found
	$runCheckSql  = "
		SELECT r.id
		FROM runs r
		JOIN schedule s ON r.schedule = s.id
		WHERE s.comp     = ?
		  AND s.event    = ?
		  AND s.category = ?
		  AND r.status   = 1
		LIMIT 1
	";
	$runCheckStmt = $conn->prepare($runCheckSql);
	if (!$runCheckStmt) {
		$scheduleAlert .= "Database error: Failed to prepare the run-check query.\n\n";
		return;
	}
	$runCheckStmt->bind_param("iii", $competitionId, $eventId, $categoryId);
	$runCheckStmt->execute();
	$runCheckResult = $runCheckStmt->get_result();
	if ($runCheckResult->num_rows > 0) {
		$scheduleAlert .= "For category $category $gender there is at least one run with status=1. Schedule modification is disallowed.\n\n";
		return;
	}
	$runCheckStmt->close();

	// 4) Retrieve the heatsystem rounds
	$heatsystemQuery = "
		SELECT h.id AS heatsystem_id, h.round, h.heatsystem
		FROM heatsystem h
		WHERE h.athletes = ? AND h.heatsystem = ?
		ORDER BY h.round ASC
	";
	$stmt = $conn->prepare($heatsystemQuery);
	if (!$stmt) {
		$scheduleAlert .= "Database error: Failed to prepare the heatsystem query.\n\n";
		return;
	}
	$stmt->bind_param("ii", $athleteCount, $heatsystem);
	$stmt->execute();
	$result = $stmt->get_result();
	if ($result->num_rows === 0) {
		$scheduleAlert .= "No matching heatsystem found for the athlete count $athleteCount.\n\n";
		return;
	}
	$neededRounds = [];
	while ($row = $result->fetch_assoc()) {
		$neededRounds[] = [
			'heatsystem_id' => $row['heatsystem_id'],
			'round'         => $row['round']
		];
	}
	$stmt->close();

	// 5) Get current max order
	$orderQuery = "SELECT MAX(`order`) AS max_order FROM schedule WHERE comp = ?";
	$orderStmt  = $conn->prepare($orderQuery);
	if (!$orderStmt) {
		$scheduleAlert .= "Database error: Failed to prepare the order query.\n\n";
		return;
	}
	$orderStmt->bind_param("i", $competitionId);
	$orderStmt->execute();
	$orderResult = $orderStmt->get_result();
	$maxOrder    = $orderResult->fetch_assoc()['max_order'] ?? 0;
	$orderStmt->close();

	$currentOrder   = $maxOrder + 1;
	$timePlannedVal = strtotime($beginningDate . ' 08:00:00');
	$firstInsert    = true;

	// Track if we actually inserted any new row
	$insertedSomething = false;

	// 6) Insert each needed round if not existing
	foreach ($neededRounds as $roundData) {
		$heatsystemId = $roundData['heatsystem_id'];
		$round        = $roundData['round'];

		$checkQuery = "
			SELECT id
			FROM schedule
			WHERE comp       = ?
			  AND event      = ?
			  AND category   = ?
			  AND heatsystem = ?
		";
		$checkStmt = $conn->prepare($checkQuery);
		if (!$checkStmt) {
			$scheduleAlert .= "Database error: Failed to prepare the schedule-existence query.\n\n";
			return;
		}
		$checkStmt->bind_param("iiii", $competitionId, $eventId, $categoryId, $heatsystemId);
		$checkStmt->execute();
		$checkResult = $checkStmt->get_result();

		if ($checkResult->num_rows === 0) {
			// => Insert a new schedule row
			$timePlannedForThisRound = ($firstInsert) ? $timePlannedVal : null;
			$insertSql = "
				INSERT INTO schedule (
					comp, event, category, heatsystem,
					`order`,
					status, timestamp
				)
				VALUES (?, ?, ?, ?, ?, 0, CURRENT_TIMESTAMP)
			";
			$insertStmt = $conn->prepare($insertSql);
			if (!$insertStmt) {
				$scheduleAlert .= "Database error: Failed to prepare the schedule-insert query.\n\n";
				return;
			}
			$insertStmt->bind_param(
				"iiiii",
				$competitionId,
				$eventId,
				$categoryId,
				$heatsystemId,
				$currentOrder
			);
			$insertStmt->execute();
			$insertStmt->close();
			
			// 10) Success => Return JSON
			$scheduleAlert .= "Competition $competitionCode - Event $eventId - category $category $gender - $athleteCount athletes: Heatsystem rounds are created!\n\n";
			
			$currentOrder++;
			$firstInsert        = false;
			$insertedSomething  = true;
		}

		$checkStmt->close();
		
	}

	// 7) If at least one NEW row was inserted, delete all "old" schedules
	//    with same (comp, event, category) but different athlete count
	if ($insertedSomething) {
		$deleteSql = "
			DELETE s
			FROM schedule s
			JOIN heatsystem h ON s.heatsystem = h.id
			WHERE s.comp     = ?
			  AND s.event    = ?
			  AND s.category = ?
			  AND h.athletes <> ?
		";
		$deleteStmt = $conn->prepare($deleteSql);
		if (!$deleteStmt) {
			$scheduleAlert .= "Database error: Failed to prepare the schedule-delete query.\n\n";
			return;
		}
		$deleteStmt->bind_param("iiii", $competitionId, $eventId, $categoryId, $athleteCount);
		$deleteStmt->execute();
		$numDeleted = $deleteStmt->affected_rows;
		$deleteStmt->close();

		// 10) Success => Return JSON
		$scheduleAlert .= "Competition $competitionCode - Event $eventId - category $category $gender - $athleteCount athletes: Heatsystem rounds are deleted!\n\n";
	}

	// -----------------------------------------------------------
	// 9) Re-ORDER all schedules so that `order` is contiguous
	// -----------------------------------------------------------
	// E.g., reorder them for the entire competition.
	$reorderSql = "
		SELECT id
		FROM schedule
		WHERE comp = ?
		ORDER BY `order` ASC
	";
	$reorderStmt = $conn->prepare($reorderSql);
	if (!$reorderStmt) {
		$scheduleAlert .= " Database error: Could not prepare reorder query.\n\n";
		return;
	}
	$reorderStmt->bind_param("i", $competitionId);
	$reorderStmt->execute();
	$reorderRes = $reorderStmt->get_result();

	$sequence = 1;
	while ($row = $reorderRes->fetch_assoc()) {
		$schedId       = $row['id'];
		$updateOrderSql = "UPDATE schedule SET `order` = ? WHERE id = ?";
		$updateOrderStmt = $conn->prepare($updateOrderSql);
		if ($updateOrderStmt) {
			$updateOrderStmt->bind_param("ii", $sequence, $schedId);
			$updateOrderStmt->execute();
			$updateOrderStmt->close();
		}
		$sequence++;
	}
	$reorderStmt->close();

}

?>