import os
import time
import subprocess
import pandas as pd
from datetime import datetime


# Close any running Excel processes (Windows)
def close_excel():
    os.system("taskkill /f /im excel.exe")


# Add millisecond timestamps to new rows in a CSV file
def add_timestamp_to_csv(input_file_path, output_file_path):
    try:
        # Read CSV with UTF-8 BOM first; fallback to GBK if needed
        df = pd.read_csv(input_file_path, encoding="utf-8-sig")
    except UnicodeDecodeError:
        df = pd.read_csv(input_file_path, encoding="gbk")

    # Create an empty Timestamp column if it does not exist
    if 'Timestamp' not in df.columns:
        df['Timestamp'] = None

    # Find the last timestamp in the file (if any)
    last_timestamp = df['Timestamp'].max()
    if pd.isnull(last_timestamp):
        current_time = datetime.now()
    else:
        current_time = datetime.strptime(last_timestamp, '%Y/%m/%d %H:%M:%S.%f')

    timestamps_added = False

    # Add timestamps to rows that lack it but contain data
    for i, row in df.iterrows():
        if pd.isnull(row['Timestamp']) and pd.notnull(row.iloc[0]):
            timestamp = current_time.strftime('%Y/%m/%d %H:%M:%S.%f')[:-3]  # keep milliseconds
            df.at[i, 'Timestamp'] = timestamp
            current_time += pd.Timedelta(milliseconds=200)  # 200 ms between rows
            timestamps_added = True

    # Write back only if new timestamps were added
    if timestamps_added:
        df.to_csv(output_file_path, index=False, encoding="utf-8-sig")
        print(f"Time-stamps added and saved to {output_file_path}")
    else:
        print("No new data to add time-stamps.")


# Open the CSV in the system's default spreadsheet app
def open_in_spreadsheet(path):
    try:
        # Windows
        os.startfile(path)  # type: ignore[attr-defined]
    except AttributeError:
        # macOS or Linux fallback
        try:
            if sys.platform == "darwin":
                subprocess.Popen(["open", path])
            else:
                subprocess.Popen(["xdg-open", path])
        except Exception as e:
            print(f"Unable to open spreadsheet automatically: {e}")


# Create or update the output CSV with timestamps, then open it
def open_csv():
    input_file_path = r"C:\Users\asus\Desktop\mindwave.csv"
    output_file_path = r"C:\Users\asus\Desktop\mindwave_with_timestamps.csv"

    # Create or update the output file with timestamps
    add_timestamp_to_csv(input_file_path, output_file_path)

    # Open the output file for viewing
    open_in_spreadsheet(output_file_path)


# Periodically refresh the CSV and append timestamps to new rows
def refresh_csv():
    last_update_time = None

    while True:
        # Close Excel to ensure the CSV is not locked
        close_excel()
        time.sleep(5)

        # Reopen and process CSV (create/update with timestamps)
        open_csv()

        # Log time since last update (simple heartbeat)
        current_time = datetime.now()
        if last_update_time is None:
            last_update_time = current_time
        else:
            time_difference = current_time - last_update_time
            print(f"Time since last update: {time_difference}")
            last_update_time = current_time

        time.sleep(5)


if __name__ == "__main__":
    refresh_csv()
