import os
import numpy as np
import matplotlib.pyplot as plt
import pandas as pd
def select_columns(df, cols):
    """
    Returns a new dataframe containing only the specified columns.
    
    Parameters:
    df (pandas.DataFrame): The input dataframe.
    cols (list): A list of column names to select.
    
    Returns:
    pandas.DataFrame: A new dataframe containing only the specified columns.
    """
    return df[cols]


def plot_steam(df, freq):
    """
    Creates separate box plots of the 'total_steam' column of a dataframe for each year or other specified frequency,
    and saves each plot to a separate file in a folder named 'plot_steam_output'.
    
    Parameters:
    df (pandas.DataFrame): The input dataframe.
    freq (str): The grouping frequency for the 'total_steam' data (e.g. 'D', 'H', 'W', etc.).
    
    Returns:
    None
    """
    steam_by_freq = df['total_steam'].groupby(pd.Grouper(freq=freq)).apply(list)
    years = sorted(set(steam_by_freq.index.year))
    if not os.path.exists('plot_steam_output'):
        os.makedirs('plot_steam_output')
    for year in years:
        year_data = steam_by_freq[steam_by_freq.index.year == year]
        plt.figure(figsize=(19.2, 10.8))
        boxplot = plt.boxplot(year_data.values, showfliers=False)
        plt.xlabel('Date')
        plt.ylabel('Total Steam')
        plt.title(f'Total Steam Box Plot ({freq} frequency) - {year}')
        plt.xticks(range(1, len(year_data)+1), year_data.index.strftime('%Y-%m-%d'), rotation=90)
        plt.grid(axis='y', linestyle='--', alpha=0.7)
        for i, box in enumerate(boxplot['boxes']):
            box.set(color='C{}'.format(i % 10), linewidth=1)
        filename = os.path.join('plot_steam_output', f'{year}_boxplot.png')
        plt.savefig(filename, dpi=300)
        plt.close()
import pandas as pd
def add_season(df):
    """
    Adds a 'season' column to a dataframe based on the datetime index.
    """
    seasons = ['Winter', 'Spring', 'Summer', 'Fall']
    month_days = [(12, 21), (3, 20), (6, 21), (9, 22)]
    month_day_map = dict(zip(range(1, 13), month_days))
    
    # create a boolean mask indicating whether the day is greater than or equal to the season start date
    mask = df.index.day >= pd.Series(df.index.month).map(month_day_map).apply(pd.Series)[1]
    
    # calculate the season using vectorized operations
    season_indices = (df.index.month + mask.astype(int)) // 3 % 4
    season_map = dict(zip(range(4), seasons))
    df['season'] = season_indices.map(season_map)
    return df
import pandas as pd
import numpy as np
import lightgbm as lgb
import matplotlib.pyplot as plt
from sklearn.metrics import mean_squared_error
from sklearn.model_selection import TimeSeriesSplit

def train_lgbm(df_total):
    # Make a copy of the input dataframe
    df_lagged = df_total.copy()
    df_lagged = df_lagged.drop('DD-MM-YYYY', axis=1)
    # Drop irrelevant columns
    cols = ['F1015_COMM/F_1015', 'F2015_COMM/F_2015', 'F3015_COMM/F_3015', 'F4015_COMM/F_4015']
    for col in cols:
        if col in df_lagged.columns:
            df_lagged.drop(col, axis=1, inplace=True)

    # Define lag range
    lag_start = 24
    lag_end = lag_start + 12

    # Create new columns with lagged features
    for i in range(lag_start, lag_end+1):
        df_lagged[f'total_steam_lag_{i}'] = df_lagged['total_steam'].shift(i)

    # Drop rows with missing values
    df_lagged = df_lagged.dropna(subset=[f'total_steam_lag_{lag_start}'], how='any')

    # Convert season column to one-hot encoded format
    df_lagged = pd.concat([df_lagged, pd.get_dummies(df_lagged['season'])], axis=1)
    df_lagged = df_lagged.drop('season', axis=1)

    # Set parameters for LightGBM model
    params = {
        'objective': 'regression',
        'metric': 'mse',
        'num_leaves': 10,
        'learning_rate': 0.05,
        'feature_fraction': 0.9
    }

    # Split data into train and test sets using time series split
    tscv = TimeSeriesSplit(n_splits=5)
    for train_index, test_index in tscv.split(df_lagged):
        train = df_lagged.iloc[train_index]
        test = df_lagged.iloc[test_index]
        X_train = train.drop(['total_steam'], axis=1)
        y_train = train['total_steam']
        X_test = test.drop(['total_steam'], axis=1)
        y_test = test['total_steam']
        
        # Create LightGBM datasets
        train_data = lgb.Dataset(X_train, label=y_train)
        test_data = lgb.Dataset(X_test, label=y_test)

        # Train the model
        model = lgb.train(params, train_data, num_boost_round=1000, valid_sets=[train_data, test_data],
                          early_stopping_rounds=50, verbose_eval=100)

        # Predict on test set and compute MSE
        y_pred = model.predict(X_test)
        mse = mean_squared_error(y_test, y_pred)
        print('MSE:', mse)

        # Plot predicted vs actual values
        plt.figure(figsize=(10, 6))
        plt.plot(y_test, label='actual')
        plt.plot(y_pred, label='predicted')
        plt.xlabel('Time')
        plt.ylabel('Total Steam (kg/s)')
        plt.title('Actual vs Predicted Total Steam')
        plt.legend()
        plt.show()
    
    return model
import pandas as pd
from statsmodels.tsa.api import ExponentialSmoothing

import pandas as pd
import matplotlib.pyplot as plt
from statsmodels.tsa.holtwinters import SimpleExpSmoothing
import pandas as pd
import matplotlib.pyplot as plt

def exponential_smoothing(df, column_name, alpha, plot=True):
    # Sort the data by index
    df = df.sort_index()
    
    # Initialize the smoothed column
    smoothed_col = f'{column_name}_smoothed'
    df[smoothed_col] = df[column_name].copy()
    
    # Calculate the first smoothed value as the first observed value
    df.loc[df.index[0], smoothed_col] = df.loc[df.index[0], column_name]
    
    # Apply exponential smoothing
    for i in range(1, len(df)):
        df.loc[df.index[i], smoothed_col] = alpha * df.loc[df.index[i], column_name] + \
                                            (1 - alpha) * df.loc[df.index[i-1], smoothed_col]
    
    # Plot the smoothed data if plot=True
    if plot:
        plt.plot(df.index, df[column_name], label='Original')
        plt.plot(df.index, df[smoothed_col], label='Smoothed')
        plt.xlabel('Date')
        plt.ylabel(column_name)
        plt.legend()
        plt.show()
    
    return df
def remove_outliers2(df, column_name):
    # Calculate z-score
    z_score = np.abs((df[column_name] - df[column_name].mean()) / df[column_name].std())

    # Filter rows where z-score is less than 3 and the value is greater than 0
    df = df[(z_score < 3) & (df[column_name] > 0)]

    # Calculate the 0.01 quantile and filter rows where the value is greater than that
    quantile_001 = df[column_name].quantile(0.01)
    df = df[df[column_name] > quantile_001]

    return df
import numpy as np
import pandas as pd



def remove_outliers(df, column_name, zscore_threshold=3, quantile_threshold=0.01, plot=True):
    # Calculate z-score
    zscore = np.abs((df[column_name] - df[column_name].mean()) / df[column_name].std())

    # Remove rows with z-score greater than threshold
    df = df[zscore < zscore_threshold]

    # Remove rows with value less than quantile threshold
    quantile_value = df[column_name].quantile(quantile_threshold)
    df = df[df[column_name] > quantile_value]

    # Smooth the data using exponential smoothing
    alpha = 0.28
    df[column_name+'_smoothed'] = df[column_name].ewm(alpha=alpha).mean()

    # Plot the data before and after removing outliers if plot=True
    if plot:
        fig, ax = plt.subplots(figsize=(10,6))
        ax.plot(df.iloc[:250].index, df.iloc[:250][column_name], label='Unsmoothed')
        ax.plot(df.iloc[:250].index, df.iloc[:250][column_name+'_smoothed'], label='Smoothed')
        ax.legend()
        ax.set_title('Data Before and After Removing Outliers')
        ax.set_xlabel('Date')
        ax.set_ylabel(column_name)
        plt.show()

    return df

def process_data_boilers(df):
    # Load the Excel file into a Pandas dataframe
    df = df.copy()
    df = df.rename(columns={'začátek hod.': 'DateTime'})
    df['year'] = df['DateTime'].dt.year
    df['month'] = df['DateTime'].dt.month
    df['day'] = df['DateTime'].dt.day
    df['hour'] = df['DateTime'].dt.hour
    df['DD-MM-YYYY'] = df['day'].astype(str) + '-' + df['month'].astype(str) + '-' + df['year'].astype(str)
    df_weather = pd.read_excel('weather_Day.xlsx')

    # Define a list of columns to select
    cols = ['F1015_COMM/F_1015', 'F2015_COMM/F_2015', 'F3015_COMM/F_3015', 'F4015_COMM/F_4015']
    time_cols = ["year", "month", "day", "hour", 'DD-MM-YYYY', "DateTime"]
    # Use the select_columns function to create a new dataframe containing only the selected columns
    df = select_columns(df, cols + time_cols)
    # Create a new column called 'total_steam' that is the sum of the selected columns
    df['total_steam'] = df[cols].sum(axis=1)
    df_total = pd.merge(df, df_weather, on='DD-MM-YYYY', how='left')
    df_total = df_total.set_index('DateTime')
    add_season(df_total)
    df_total = df_total.drop(['rok', 'Měsíc', 'Den'], axis=1)
    # Add maintenance_period column based on total_steam column
    df_total['maintenance_period'] = df_total['total_steam'].apply(lambda x: 1 if x == 0 else 0)
    cols = ['F1015_COMM/F_1015', 'F2015_COMM/F_2015', 'F3015_COMM/F_3015', 'F4015_COMM/F_4015']
    df_total['boilers_active'] = df_total[cols].apply(lambda x: (x > 1).sum(), axis=1)

    # Remove outliers
    df_total = remove_outliers(df_total, "total_steam", zscore_threshold=2, quantile_threshold=0.01, plot=True)

    # Smooth data


    return df_total


import pandas as pd
import numpy as np
from lightgbm import LGBMRegressor
from sklearn.metrics import mean_absolute_error
from sklearn.model_selection import TimeSeriesSplit, train_test_split
import lightgbm as lgb

import os
import matplotlib.pyplot as plt
import plotly.graph_objects as go


def train_lgbm_model(df_total, target_col):
    from sklearn.model_selection import TimeSeriesSplit
    from sklearn.model_selection import train_test_split
    import lightgbm as lgb
    from sklearn.metrics import mean_absolute_error
    riginal_column_name = target_col.replace("_smoothed", "")

    if riginal_column_name in df_total.columns:
        df_total.drop(riginal_column_name, axis=1, inplace=True)
    else:
        pass

    # Create lagged columns
    df_lagged = df_total.copy()
    df_lagged = df_lagged.drop('DD-MM-YYYY', axis=1)
    cols = ['F1015_COMM/F_1015', 'F2015_COMM/F_2015', 'F3015_COMM/F_3015', 'F4015_COMM/F_4015']
    for col in cols:
        if col in df_lagged.columns:
            df_lagged.drop(col, axis=1, inplace=True)
    lag_start = 24
    lag_end = lag_start + 12
    for i in range(lag_start, lag_end+1):
        df_lagged[f'{target_col}_lag_{i}'] = df_lagged[target_col].shift(i)

    # Drop rows with missing values
    df_lagged = df_lagged.dropna(subset=[f'{target_col}_lag_{lag_end}'], how='any')

    # Convert season column to one-hot encoded format
    df_lagged = pd.concat([df_lagged, pd.get_dummies(df_lagged['season'])], axis=1)
    df_lagged = df_lagged.drop('season', axis=1)

    # Set parameters for LightGBM model
    params = {
        'objective': 'regression',
        'metric': 'mse',
        'num_leaves': 10,
        'learning_rate': 0.05,
        'feature_fraction': 0.9
    }

    # Split data into train and test sets
    train, test = train_test_split(df_lagged, test_size=0.2, shuffle=False)

    # Split train and test sets into features and target
    X_train = train.drop([target_col], axis=1)
    y_train = train[target_col]
    X_test = test.drop([target_col], axis=1)
    y_test = test[target_col]

    # Create LightGBM datasets
    train_data = lgb.Dataset(X_train, label=y_train)
    test_data = lgb.Dataset(X_test, label=y_test)

    # Train the model
    model = lgb.train(params, train_data, num_boost_round=1000, valid_sets=[train_data, test_data],
                      early_stopping_rounds=50, verbose_eval=100)

    # Predict on test set and compute MSE
    y_pred = pd.Series(model.predict(X_test), index=X_test.index)
    mse = mean_absolute_error(y_test, y_pred)
    print('MSE:', mse)

    # Plot predicted vs actual values
    
    fig, ax = plt.subplots(figsize=(12,6))
    ax.plot(y_test.index, y_test, label='True')
    ax.plot(y_test.index, y_pred, label='Predicted')
    ax.set_title('Predicted vs Actual Values')
    ax.set_xlabel('Time')
    ax.set_ylabel('Total Steam (kg/s)')
    ax.legend()
    # Save the figure as a PNG and SVG
    save_path = 'plot_steam_output'
    if not os.path.exists(save_path):
        os.makedirs(save_path)
        fig.savefig(f'{save_path}/predicted_vs_actual.png', dpi=100, bbox_inches='tight')
        fig.savefig(f'{save_path}/predicted_vs_actual.svg', bbox_inches='tight')

    # Display plot
    plt.show()

    # Plot feature importances
    fig, ax = plt.subplots(figsize=(8, 6))
    lgb.plot_importance(model, ax=ax)
    ax.set_title('Feature Importances')
    plt.show()

    # Plot predicted vs actual values using Plotly
    import plotly.graph_objects as go

    fig = go.Figure()
    fig.add_trace(go.Scatter(x=y_test.index, y=y_test, mode='lines', name='Actual'))
    fig.add_trace(go.Scatter(x=y_test.index, y=y_pred, mode='lines', name='Predicted'))
    fig.update_layout(title='Actual vs Predicted', xaxis_title='Date', yaxis_title='Total Steam (kg/s)')
    fig.show(renderer='browser')
    column_names = df_lagged.columns.tolist() 
    return (model, column_names )

def preprocess_flow_data(df):
    """
    Preprocesses the flow data in the input DataFrame.

    Parameters:
    df (pandas.DataFrame): The input DataFrame containing the flow data.

    Returns:
    df_flow (pandas.DataFrame): The preprocessed DataFrame containing only the necessary columns and the sum of the external flows.
    """
    df = df.copy()
    df = df.rename(columns={'začátek hod.': 'DateTime'})
        # Select necessary columns
        # Convert columns to numeric type
    for col in ['Q_PARA150_OKAM', 'Q_VYST_PARA_OKAM', 'FI0226', 'Q_VODA_OKAM', '10QHS01CF001/AIN', 'FI0224/AIN', 'FI0216/AIN']:
        df[col] = pd.to_numeric(df[col], errors='coerce')
    #items = ['F1015_COMM/F_1015', 'F2015_COMM/F_2015', 'F3015_COMM/F_3015', 'F4015_COMM/F_4015', '20LBA53CF001', 'DIF',
    #         '20LBS10CF001', 'Q_PARA150_OKAM', 'Q_VYST_PARA_OKAM', 'FI0226', 'Q_VODA_OKAM', '10QHS01CF001/AIN',
    #         'FI0224/AIN', 'FI0216/AIN',"DateTime"]
    items = ['F1015_COMM/F_1015', 'F2015_COMM/F_2015', 'F3015_COMM/F_3015', 'F4015_COMM/F_4015', '20LBA53CF001', 'DIF',
            '20LBS10CF001', 'Q_PARA150_OKAM', 'Q_VYST_PARA_OKAM', 'FI0226', '10QHS01CF001/AIN',
            'FI0224/AIN', 'FI0216/AIN',"DateTime"]
    df = df[items]
    df['year'] = df['DateTime'].dt.year
    df['month'] = df['DateTime'].dt.month
    df['day'] = df['DateTime'].dt.day
    df['hour'] = df['DateTime'].dt.hour
    df['DD-MM-YYYY'] = df['day'].astype(str) + '-' + df['month'].astype(str) + '-' + df['year'].astype(str)
    df_weather = pd.read_excel('weather_Day.xlsx')

    # Define a list of columns to select
    cols = ['F1015_COMM/F_1015', 'F2015_COMM/F_2015', 'F3015_COMM/F_3015', 'F4015_COMM/F_4015']
    # Create a new column called 'total_steam' that is the sum of the selected columns
    df['total_steam'] = df[cols].sum(axis=1)
    df = pd.merge(df, df_weather, on='DD-MM-YYYY', how='left')
    df = df.set_index('DateTime')
    add_season(df)
    df_flow = df.drop(['rok', 'Měsíc', 'Den'], axis=1)




    # Remove outliers
    #df_flow = remove_outliers(df_flow, "Q_VYST_PARA_OKAM", zscore_threshold=3, quantile_threshold=0.01, plot=True)
    #df_flow = remove_outliers(df_flow, "Q_VODA_OKAM", zscore_threshold=3, quantile_threshold=0.01, plot=True)

    # Replace NaN and "non" values with 0
    df_flow.fillna(0, inplace=True)
    df_flow.replace("non", 0, inplace=True)

    # Calculate sum of external flows
    #df_flow['externals'] = df_flow[['Q_PARA150_OKAM', 'Q_VYST_PARA_OKAM', 'FI0226', 'Q_VODA_OKAM',
    #                                '10QHS01CF001/AIN', 'FI0224/AIN', 'FI0216/AIN']].sum(axis=1, skipna=True)
    df_flow['externals'] = df_flow[['Q_PARA150_OKAM', 'Q_VYST_PARA_OKAM', 'FI0226',
                                    '10QHS01CF001/AIN', 'FI0224/AIN', 'FI0216/AIN']].sum(axis=1, skipna=True)
    df_flow = remove_outliers(df_flow, "externals", zscore_threshold=3, quantile_threshold=0.01, plot=True)
    return df_flow

import plotly.graph_objects as go

def plot_line_with_ma(df, column, window=24, figsize=(10, 6)):
    """
    Plots a line plot of the specified column in the DataFrame,
    with a moving average line included, and the actual vs predicted values using Plotly.
    
    Parameters:
    df (pandas.DataFrame): The input dataframe.
    column (str): The name of the column to plot.
    window (int): The window size for the moving average.
    figsize (tuple): The figure size (width, height) in inches.
    
    Returns:
    None
    """
    # Compute moving average
    ma = df[column].rolling(window=window).mean()
    
    # Plot line plot and moving average
    fig, ax = plt.subplots(figsize=figsize)
    ax.plot(df[column], label='Original')
    ax.plot(ma, label=f'Moving Average (Window={window})')
    ax.legend()
    
    # Set title and axis labels
    ax.set_title(f'{column} with Moving Average')
    ax.set_xlabel('Date')
    ax.set_ylabel(column)
    
    # Plot actual vs predicted using Plotly
    fig = go.Figure()
    fig.add_trace(go.Scatter(x=df.index, y=df[column], mode='lines', name='Actual'))
    fig.add_trace(go.Scatter(x=df.index, y=ma, mode='lines', name='Moving Average'))
    fig.update_layout(title=f'{column} with Moving Average', xaxis_title='Date', yaxis_title=column)
    fig.show(renderer='browser')


# %%
import plotly.graph_objects as go

def plot_moving_average(df, columns, window=7):
    """
    Plots the moving averages of the specified columns in the DataFrame using Plotly.
    
    Parameters:
    df (pandas.DataFrame): The input dataframe.
    columns (list): The list of column names to plot.
    window (int): The window size for the moving average.
    
    Returns:
    None
    """
    # Compute moving averages
    ma_df = df[columns].rolling(window=window).mean().dropna()
    
    # Create plotly figure
    fig = go.Figure()
    
    # Add traces for each column
    for col in columns:
        fig.add_trace(go.Scatter(x=ma_df.index, y=ma_df[col], mode='lines', name=f'{col} (Moving Average, Window={window})'))
    
    # Set title and axis labels
    fig.update_layout(title=f'Moving Averages ({window}-Day Window)', xaxis_title='Date', yaxis_title='Value')
    
    # Show plot
    fig.show(renderer='browser')

