# -*- coding: utf-8 -*-
import pandas as pd
import plotly.graph_objs as go
import plotly.io as pio

def plot_time_series_from_excel(file_path, selected_date=None):
    # Load the Excel file
    df = pd.read_excel(file_path)
    
    # Print all column names for debugging
    print("All columns found in the file:", df.columns.tolist())
    
    # Names of the date and time columns in German
    date_column = 'Datum'
    time_column = 'Zeit'
    
    # Check if the date and time columns exist
    if date_column in df.columns and time_column in df.columns:
        print(f"Columns '{date_column}' and '{time_column}' found.")
        
        # Drop rows where time or date is NaN
        df = df.dropna(subset=[date_column, time_column])
        
        # Convert the 'Datum' column to datetime for filtering
        df[date_column] = pd.to_datetime(df[date_column], format='%d.%m.%Y', errors='coerce')
        
        # Handle the case where selected_date is a range (e.g., '02.08.2024-14.08.2024')
        if selected_date:
            if '-' in selected_date:
                # Split the string by '-' to get the start and end date
                start_date_str, end_date_str = selected_date.split('-')
                start_date = pd.to_datetime(start_date_str.strip(), format='%d.%m.%Y')
                end_date = pd.to_datetime(end_date_str.strip(), format='%d.%m.%Y')
                df = df[(df[date_column] >= start_date) & (df[date_column] <= end_date)]
                print(f"Filtered data for range {start_date.date()} to {end_date.date()}")
            else:
                # If it's a single date, handle it as before
                selected_date = pd.to_datetime(selected_date, format='%d.%m.%Y')
                df = df[df[date_column] == selected_date]
                print(f"Filtered data for {selected_date.date()}")
        
        # Fix time formatting issues in 'Zeit' column
        df[time_column] = pd.to_datetime(df[time_column], format='%H:%M:%S', errors='coerce').dt.time

        # Combine date and time into a single datetime column for plotting purposes
        df['datetime'] = pd.to_datetime(df[date_column].astype(str) + ' ' + df[time_column].astype(str), 
                                        format='%Y-%m-%d %H:%M:%S', errors='coerce')
        
        # Check for conversion errors
        if df['datetime'].isnull().any():
            print("There are NaT (Not a Time) values in the datetime column after cleaning. Check your date and time formats.")
            print(df[df['datetime'].isnull()])
            return
        
        # Set datetime as index
        df.set_index('datetime', inplace=True)
        
        # Loop through the columns after 'Zeit' for plotting
        for column in df.columns[2:]:
            if pd.notna(column):
                print(f"Plotting column '{column}'")

                # Create a trace for the plot
                trace = go.Scatter(x=df.index, y=df[column], mode='lines', name=column)
                
                # Create the layout for the plot
                layout = go.Layout(
                    title=f'Time Series Plot for {column}',
                    xaxis=dict(title='Datetime', rangeslider=dict(visible=True), type='date'),
                    yaxis=dict(title=column),
                    hovermode='x',
                    showlegend=True
                )
                
                # Create the figure with the trace and layout
                fig = go.Figure(data=[trace], layout=layout)
                
                # Save the figure as an HTML file
                if '-' in selected_date:
                    file_name = f"{column}({start_date.strftime('%d.%m.%Y')}-{end_date.strftime('%d.%m.%Y')}).html"
                else:
                    file_name = f"{column}({selected_date.strftime('%d.%m.%Y')}).html"
                
                pio.write_html(fig, file=file_name, auto_open=False)
                print(f"Saved interactive plot to {file_name}")
            else:
                print(f"Encountered an empty column, stopping plotting.")
                break
    else:
        print(f"The required columns '{date_column}' and/or '{time_column}' are missing in the Excel file.")

# Example of usage with a date range
file_path = 'BHKW_08_2024-1.xlsx'
selected_date = '01.08.2024-31.08.2024'  # Example date range

plot_time_series_from_excel(file_path, selected_date)