#!/usr/bin/python3.12

print("Content-Type: text/html; charset=utf-8\n\n")

import sys
import os
import pandas as pd
import folium
import logging

basedir = os.path.dirname(os.path.abspath(__file__))
LOG_FILE = os.path.join(basedir, "analysis.log")

def main():

	try:

		print("<!DOCTYPE html><html><head></head><body>")
		
		# Load data, skipping the redundant header rows
		df = pd.read_csv('output/20260514_data.csv')
		df = df[df['Line'] != 'Line'] # Remove rows where the header is repeated

		df = df[df['LookupDate'] != 'LookupDate'].copy()

		# Convert types
		df['Delay'] = pd.to_numeric(df['Delay'], errors='coerce')
		df['Delay'] = df['Delay'].fillna(999)
		df['ScheduledDeparture'] = pd.to_datetime(df['ScheduledDeparture'], format='%y%m%d%H%M')

		# Deduplicate: Keep only the latest lookup for each specific train line and departure
		df = df.sort_values('LookupDate').drop_duplicates(subset=['Line', 'ScheduledDeparture'], keep='last')

		
		
		
		# 2. Extract Final Destination
		# Split by '|' and take the last element
		df['FinalDestination'] = df['ScheduledDestination'].apply(lambda x: x.split('|')[-1].strip() if pd.notnull(x) else 'Unknown')

		df['FinalDestination'] = df['Station'].apply(lambda x: x.split('|')[-1].strip() if pd.notnull(x) else 'Unknown')
		
		

		# 3. Calculate Statistics
		stats_now = df.groupby(['Line', 'Station', 'ScheduledDestination','ScheduledDeparture'])['Delay'].agg(['max']).reset_index()
		stats_now.columns = ['Line', 'Station', 'Destination','Departure','Delay']
		stats_now['Departure'] = pd.to_datetime(stats_now['Departure'])
		stats_now = stats_now.sort_values('Departure', ascending=True)
		stats_now = stats_now.drop_duplicates(
			subset=['Line','Station', 'Destination','Departure'], 
			keep='last'
		).reset_index(drop=True)
		
		stats = df.groupby(['Line', 'FinalDestination'])['Delay'].agg(['mean', 'max', 'count']).reset_index()
		stats.columns = ['Line', 'Destination', 'AvgDelay', 'MaxDelay', 'Count']

		# 4. Map Coordinates (Latitude, Longitude)
		geo_coords = {
			# --- Hannover Core City Stations ---
			"Hannover Hbf": [52.3767, 9.7410],
			"Hannover Bismarckstr.": [52.3532, 9.7540],
			"Hannover-Nordstadt": [52.3905, 9.7180],
			"Hannover-Ledeburg": [52.4042, 9.6865],
			"Hannover-Vinnhorst": [52.4234, 9.7151],
			"Hannover-Leinhausen": [52.3934, 9.6804],
			"Hannover-Linden/Fischerhof": [52.3444, 9.7118],
			"Hannover-Bornum": [52.3524, 9.6881],
			"Hannover Karl-Wiechert-Allee": [52.3742, 9.8092],
			"Hannover-Kleefeld": [52.3734, 9.7822],
			"Hannover-Anderten-Misburg": [52.3691, 9.8610],
			"Hannover Messe/Laatzen": [52.3235, 9.7895],
			"Hannover Flughafen": [52.4601, 9.7022],

			# --- S1/S2 West (Minden/Nienburg) ---
			"Letter": [52.3995, 9.6433],
			"Seelze": [52.3919, 9.5939],
			"Dedensen-Gümmer": [52.4042, 9.5312],
			"Wunstorf": [52.4305, 9.4442],
			"Haste": [52.3831, 9.3834],
			"Lindhorst(Schaumb-Lippe)": [52.3614, 9.2825],
			"Stadthagen": [52.3331, 9.2084],
			"Kirchhorsten": [52.3082, 9.1231],
			"Bückeburg": [52.2682, 9.0520],
			"Minden(Westf)": [52.2894, 8.9249],
			"Poggenhagen": [52.4788, 9.4524],
			"Neustadt am Rübenberge": [52.5028, 9.4705],
			"Eilvese": [52.5444, 9.4292],
			"Hagen(Han)": [52.5739, 9.4086],
			"Linsburg": [52.5934, 9.3231],
			"Nienburg(Weser)": [52.6458, 9.2194],

			# --- S1/S2/S21 South-West (Deister) ---
			"Empelde": [52.3432, 9.6644],
			"Ronnenberg": [52.3164, 9.6641],
			"Weetzen": [52.2944, 9.6441],
			"Lemmie": [52.2882, 9.6132], # RE-UPDATED: Platform center near Bahnhofstr. 5
			"Wennigsen(Deister)": [52.2741, 9.6171],
			"Egestorf(Deister)": [52.2858, 9.5103],
			"Kirchdorf(Deister)": [52.2922, 9.5194],
			"Barsinghausen": [52.3061, 9.4815],
			"Winninghausen": [52.3205, 9.4357],
			"Bantorf": [52.3323, 9.4175],
			"Bad Nenndorf": [52.3414, 9.3813],

			# --- S3/S6/S7 East & North (Celle/Hildesheim) ---
			"Ahlten": [52.3761, 9.9042],
			"Lehrte": [52.3768, 9.9774],
			"Sehnde": [52.3168, 9.9584],
			"Algermissen": [52.2536, 9.9715],
			"Harsum": [52.2078, 9.9134],
			"Hildesheim Hbf": [52.1606, 9.9535],
			"Aligse": [52.3994, 9.9774],
			"Burgdorf": [52.4492, 10.0028],
			"Otze": [52.4741, 10.0121],
			"Ehlershausen": [52.5076, 10.0244],
			"Celle": [52.6201, 10.0632],

			# --- S4 North (Wedemark) ---
			"Langenhagen Mitte": [52.4405, 9.7262],
			"Langenhagen Pferdemarkt": [52.4506, 9.7364],
			"Langenhagen-Kaltenweide": [52.4651, 9.7344],
			"Bissendorf": [52.5231, 9.7544],
			"Mellendorf": [52.5514, 9.7325],
			"Bennemühlen": [52.5804, 9.7275],

			# --- S4/S5 South (Hameln/Paderborn) ---
			"Rethen(Leine)": [52.2861, 9.8144],
			"Sarstedt": [52.2415, 9.8451],
			"Barnten": [52.1952, 9.8149],
			"Emmerke": [52.1746, 9.8973],
			"Holtensen/Linderte": [52.2793, 9.6453],
			"Bennigsen": [52.2393, 9.6644],
			"Völksen/Eldagsen": [52.2164, 9.6221],
			"Springe": [52.2131, 9.5514],
			"Bad Münder(Deister)": [52.1873, 9.4939],
			"Hameln": [52.1054, 9.3751],
			"Emmerthal": [52.0528, 9.3871],
			"Bad Pyrmont": [51.9745, 9.2726],
			"Lügde": [51.9568, 9.2452],
			"Schieder": [51.9174, 9.1555],
			"Steinheim(Westf)": [51.8654, 9.0981],
			"Altenbeken": [51.7664, 8.9422],
			"Paderborn Hbf": [51.7131, 8.7406]
		}
		

		# 5. Build the Folium Map of current delays
		print("<p>Current delays</p>")
		# Centered on Hannover
		m = folium.Map(location=[52.375, 9.732], zoom_start=10,width=800, height=800)
		for _, row in stats_now.iterrows():
			dest = row['Station']
			if dest in geo_coords:
				# Define color based on delay severity
				if row['Delay'] < 5:
					color = 'green'
				elif row['Delay'] < 15:
					color = 'orange'
				elif row['Delay'] < 999:
					color = 'red'
				else:
					color = 'black'

				# Add a marker for each route endpoint
				folium.CircleMarker(
					location=geo_coords[dest],
					radius= 3 + (min(10,row['Delay']) * 2.0), # Size scales with delay
					popup=(f"<b>Line:</b> {row['Line']}<br>"
						    f"<b>Station:</b> {row['Station']}<br>"
       						f"<b>Destination:</b> {row['Destination']}<br>"
						    f"<b>Departure:</b> {row['Departure']}<br>"
       						f"<b>Delay:</b> {'Canceled' if row['Delay'] >= 999 else f'{row['Delay']:.1f} min'}<br>"),
					color=color,
					fill=True,
					fill_opacity=0.6
				).add_to(m)
		# Print the map to an HTML file
		print("<div>")
		print(f"<div>{m._repr_html_()}</div>")
		
		print("<br>")
		print("<p>Average delays</p>")
		
		# 5. Build the Folium Map
		# Centered on Hannover
		m = folium.Map(location=[52.375, 9.732], zoom_start=10, width=500, height=400)
		for _, row in stats.iterrows():
			dest = row['Destination']
			if dest in geo_coords:
				# Define color based on delay severity
				if row['AvgDelay'] < 5:
					color = 'green'
				elif row['AvgDelay'] < 15:
					color = 'orange'
				elif row['AvgDelay'] < 999:
					color = 'red'
				else:
					color = 'black'

				# Add a marker for each route endpoint
				folium.CircleMarker(
					location=geo_coords[dest],
					radius= 3 + (min(10,row['AvgDelay']) * 2.0), # Size scales with delay
					popup=(f"<b>Line:</b> {row['Line']}<br>"
						   f"<b>Destination:</b> {dest}<br>"
						   f"<b>Avg Delay:</b> {row['AvgDelay']:.1f} min<br>"
						   f"<b>Train Count:</b> {row['Count']}"),
					color=color,
					fill=True,
					fill_opacity=0.6
				).add_to(m)
		# Print the map to an HTML file
		print(f"<div>{m._repr_html_()}</div>")
		print("</div>")
		
		print("</body></html>")
	except:
		logging.exception("analysis failed")
	
if __name__ == "__main__":

	logging.basicConfig(
			filename = LOG_FILE,
			level = logging.INFO,
			format = "%(asctime)s ¦ %(levelname)s ¦ %(message)s"
	)
	main()