# /// script
# requires-python = ">=3.10"
# dependencies = [
#     "pandas",
# ]
# ///
import pandas as pd

aoi_metrics = pd.read_csv("../aoi_metrics.csv")
enrichment_sections = pd.read_csv("../sections.csv")  # Found in the enrichment download
timeseries_sections = pd.read_csv(
    "../sections.csv"
)  # Found in the Timeseries bulk download (from project)
output_path = "../ttff_from_event.csv"

# Merging enrichment_sections with timeseries_sections on 'recording id' and selecting specified columns
merged_sections = pd.merge(
    enrichment_sections[
        ["recording id", "section start time [ns]", "start event name"]
    ],
    timeseries_sections[
        ["recording id", "section start time [ns]", "start event name"]
    ],
    on="recording id",
    how="inner",
    suffixes=("_enrichment", "_timeseries"),
)

# Calculating the time difference between enrichment and timeseries section start times in milliseconds
merged_sections["time_diff_ms"] = (
    merged_sections["section start time [ns]_timeseries"]
    - merged_sections["section start time [ns]_enrichment"]
) / 1e6

# Merging this result back into aoi_metrics on 'recording id' to compute the ttff column
# This will allow us to bring in the calculated `time_diff_ms` and `start event name` from enrichment sections
final_merged_data = pd.merge(
    aoi_metrics,
    merged_sections[["recording id", "time_diff_ms", "start event name_enrichment"]],
    on="recording id",
    how="left",
)

# Calculating 'ttff' column in final_merged_data
final_merged_data["ttff"] = (
    final_merged_data["time to first fixation [ms]"] - final_merged_data["time_diff_ms"]
)

# Selecting relevant columns to show the result
result_with_ttff = final_merged_data[
    [
        "recording id",
        "recording name",
        "aoi name",
        "time to first fixation [ms]",
        "time_diff_ms",
        "start event name_enrichment",
        "ttff",
    ]
]
# Printing and saving
print(result_with_ttff.head())
result_with_ttff.to_csv(output_path)
print(f"File saved {output_path}")