import global_variables as gv
import os
import pandas as pd
import numpy as np

def return_df_aggregated_and_prod_share(commodity, product_short_list, b_normalise_to_bat_chemical, refining_method):
    dict_df_sorted = dict()

    for product_short in product_short_list:
        df_read, source, product, bat_chemical_for_commodity, conversion_factor_metal_to_bat_chemical, commodity_production_2022_in_kt = \
            read_df(commodity, product_short)
        df_properties, string_y_label, col_name_production, emission_types_converted, share_of_global_production = \
            prepare_df(df_read, b_normalise_to_bat_chemical, commodity)
        df_properties_with_refining, emission_types_normalised = \
            add_refining_and_errors_to_df(df_properties, commodity, b_normalise_to_bat_chemical, product_short, refining_method)

        dict_df_sorted[product_short] = df_properties_with_refining.sort_values(by="Total modelled emissions (kg_CO2e/t_paid)")

    df_aggregated = pd.concat([ele for ele in dict_df_sorted.values()]).reset_index(drop=True).sort_values(by=f"Total emissions (kg_CO2e/kg_{bat_chemical_for_commodity})")
    share_of_global_production = df_aggregated[col_name_production].sum() / (commodity_production_2022_in_kt / conversion_factor_metal_to_bat_chemical) * 100

    return df_aggregated, product, bat_chemical_for_commodity, conversion_factor_metal_to_bat_chemical, commodity_production_2022_in_kt, string_y_label, col_name_production, emission_types_converted, share_of_global_production, emission_types_normalised


def read_df(commodity, product_short):
    global source, product, bat_chemical_for_commodity, conversion_factor_metal_to_bat_chemical, commodity_production_2022_in_kt

    excel_file_path = os.path.join(gv.path_project, "resources", "SP CIQ", commodity+"_properties_SP_CIQ.xlsx")
    sheet_name = commodity + " - " + product_short +" - Py Import"

    source                                      = pd.read_excel(excel_file_path, sheet_name= sheet_name, usecols="B", skiprows=1, nrows=1, header=None).iloc[0, 0]
    product                                     = pd.read_excel(excel_file_path, sheet_name= sheet_name, usecols="B", skiprows=2, nrows=1, header=None).iloc[0, 0]
    bat_chemical_for_commodity                  = pd.read_excel(excel_file_path, sheet_name= sheet_name, usecols="B", skiprows=3, nrows=1, header=None).iloc[0, 0]
    conversion_factor_metal_to_bat_chemical     = pd.read_excel(excel_file_path, sheet_name= sheet_name, usecols="B", skiprows=4, nrows=1, header=None).iloc[0, 0]
    commodity_production_2022_in_kt             = pd.read_excel(excel_file_path, sheet_name= sheet_name, usecols="B", skiprows=5, nrows=1, header=None).iloc[0, 0]

    df_read = pd.read_excel(excel_file_path, sheet_name= sheet_name, skiprows=7, header=0)

    return df_read, source, product, bat_chemical_for_commodity, conversion_factor_metal_to_bat_chemical, commodity_production_2022_in_kt

def prepare_df(df_properties, b_normalise_to_bat_chemical, commodity):
    global string_y_label, col_name_production, emission_types_converted, emission_types_orig_with_unit, share_of_global_production

    emission_types_orig = ["Total modelled emissions", "Electricity emissions", "On-site transportation emissions", "Reagents emissions", "Off-site transportation emissions"]
    emission_types_orig_with_unit = []
    emission_types_converted = []
    if b_normalise_to_bat_chemical:
        for emission_type in emission_types_orig:
            original_col_name = f"{emission_type} (kg_CO2e/t_paid)"
            col_name_emissions = f"{emission_type} (kg_CO2e/kg_{bat_chemical_for_commodity})"
            emission_types_orig_with_unit.append(original_col_name)
            emission_types_converted.append(col_name_emissions)

            df_properties[col_name_emissions] = convert_t_metal_to_kg_chemical(df_properties[original_col_name])

        #col_name_emissions = "Total modelled emissions (kg_CO2e/kg_" + bat_chemical_for_commodity + ")"
        col_name_production = bat_chemical_for_commodity + " Equivalent [kilotonnes]"
        string_y_label = f"CF [kg$_{{CO_{{2e}}}}$/kg$_{{{bat_chemical_for_commodity}}}$]"
        df_properties[col_name_production] = df_properties["Paid " + commodity + " Produced (kilotonnes)"] / conversion_factor_metal_to_bat_chemical
        share_of_global_production = df_properties[col_name_production].sum() / (commodity_production_2022_in_kt / conversion_factor_metal_to_bat_chemical) * 100
    else:
        for emission_type in emission_types_orig:
            original_col_name = f"{emission_type} (kg_CO2e/t_paid)"
            col_name_emissions = f"{emission_type} (kg_CO2e/kg_paid)"
            emission_types_converted.append(col_name_emissions)

            df_properties[col_name_emissions] = df_properties[original_col_name] / 1000

        #col_name_emissions = "Total modelled emissions (kg_CO2e/kg_paid)"
        col_name_production = commodity + " Produced (kilotonnes " + commodity + " paid)"
        string_y_label = f"CF (kg$_{{CO_{{2e}}}}$/kg$_{{paid}}$)"
        #df_properties[col_name_emissions] = df_properties["Total modelled emissions (kg_CO2e/t_paid)"] / 1000
        df_properties[col_name_production] = df_properties["Paid " + commodity + " Produced (kilotonnes)"]
        share_of_global_production = df_properties[col_name_production].sum() / commodity_production_2022_in_kt * 100
    return df_properties, string_y_label, col_name_production, emission_types_converted, share_of_global_production

def add_refining_and_errors_to_df(df_to_be_modified, commodity, b_normalise_to_bat_chemical, product_short, refining_method):
    # refining_method options:
    #   min_max_literature_values: use mean of literature values for refining emissions and min-max of literature values for error
    #   TCRC_scale (not implemented): equate mean of TC/RC with mean of literature values and scale refining emission of properties linerarly with deviations from TCRC mean. Unsure how to determine the error
    #   TCRC_literature_values_coupled: couple ranges from TCRC and literature and "clamp" both ends to one another for refining emissions (wihtin product category). Use lower and upper ends of literature values for determining the error.

    global emission_types_normalised

    if not b_normalise_to_bat_chemical:
        print(f"bool {b_normalise_to_bat_chemical} is set to False which is not allowed.")
        return -1

    ## error calculation for modelled emissions
    MC_value_all_col_names = ["MC " + el for el in emission_types_orig_with_unit]
    MC_total_modelled_emission_col_name = MC_value_all_col_names[0]

    # 0th position refers to 5th percentile, 1st postition to 50th and 2nd to 95th
    df_to_be_modified[f"p5_total_modelled_emission (kg_CO2e/kg_{bat_chemical_for_commodity})"] = convert_t_metal_to_kg_chemical(df_to_be_modified[MC_total_modelled_emission_col_name].str.split(',').str[0])
    df_to_be_modified[f"p50_total_modelled_emission (kg_CO2e/kg_{bat_chemical_for_commodity})"] = convert_t_metal_to_kg_chemical(df_to_be_modified[MC_total_modelled_emission_col_name].str.split(',').str[1])
    df_to_be_modified[f"p95_total_modelled_emission (kg_CO2e/kg_{bat_chemical_for_commodity})"] = convert_t_metal_to_kg_chemical(df_to_be_modified[MC_total_modelled_emission_col_name].str.split(',').str[2])

    # calculating the margin of error (+- value) for the modelled emissions based on a 90% confidence interval
    df_to_be_modified[f"moe_modelled_emissions (kg_CO2e/kg_{bat_chemical_for_commodity})"] = (df_to_be_modified[f"p95_total_modelled_emission (kg_CO2e/kg_{bat_chemical_for_commodity})"]-df_to_be_modified[f"p5_total_modelled_emission (kg_CO2e/kg_{bat_chemical_for_commodity})"])/2


    ## addiging refining emissions and margin of error

    excel_literature_file_path = os.path.join(gv.path_project, "resources", "Literature_values.xlsx")
    sheet_name = commodity + " - " + product_short + " - " + "Breakdown"

    df_breakdowns = pd.read_excel(excel_literature_file_path, sheet_name=sheet_name, usecols="A:F", skiprows=1, header=0).dropna().fillna(0)
    df_breakdowns = df_breakdowns.set_index("Phase").transpose()

    df_breakdowns.loc['Subtotal'] = df_breakdowns.loc['Refinement'] + df_breakdowns.loc['Other']

    min_refining_literature = min(df_breakdowns.loc["Subtotal"])
    max_refining_literature = max(df_breakdowns.loc["Subtotal"])
    mean_refining_literature = np.mean(df_breakdowns.loc["Subtotal"])

    # overwrite refining method for lithium or graphite, as S&P data for lithium does not contain TC/RC
    if ((refining_method == "TCRC_literature_values_coupled" and commodity == "Lithium") or
            (refining_method == "TCRC_literature_values_coupled" and commodity == "Nickel" and ("Lat" in product_short)) or
            (commodity == "Graphite")):
        print(f"Note: For {commodity}, {product_short}, the refining method was changed from {refining_method} to min_max_literature_values.")
        refining_method="min_max_literature_values"
    if refining_method == "min_max_literature_values":
        df_to_be_modified[f"Refining emissions (kg_CO2e/kg_{bat_chemical_for_commodity})"] = mean_refining_literature

        df_to_be_modified[f"moe_refining_emissions_lower_bound (kg_CO2e/kg_{bat_chemical_for_commodity})"] = (max_refining_literature - min_refining_literature) / 2
        df_to_be_modified[f"moe_refining_emissions_upper_bound (kg_CO2e/kg_{bat_chemical_for_commodity})"] = df_to_be_modified[f"moe_refining_emissions_lower_bound (kg_CO2e/kg_{bat_chemical_for_commodity})"]

    elif refining_method == "TCRC_literature_values_coupled":
        min_tcrc = min(df_to_be_modified["Treatment & Refining Charges (USD/t_paid)"])
        max_tcrc = max(df_to_be_modified["Treatment & Refining Charges (USD/t_paid)"])

        df_to_be_modified[f"Refining emissions (kg_CO2e/kg_{bat_chemical_for_commodity})"] = df_to_be_modified["Treatment & Refining Charges (USD/t_paid)"].apply(lambda x: linear_interpolation(min_tcrc, min_refining_literature, max_tcrc, max_refining_literature, x))

        df_to_be_modified[f"moe_refining_emissions_lower_bound (kg_CO2e/kg_{bat_chemical_for_commodity})"] = (df_to_be_modified[f"Refining emissions (kg_CO2e/kg_{bat_chemical_for_commodity})"]-min_refining_literature)
        df_to_be_modified[f"moe_refining_emissions_upper_bound (kg_CO2e/kg_{bat_chemical_for_commodity})"] = (max_refining_literature-df_to_be_modified[f"Refining emissions (kg_CO2e/kg_{bat_chemical_for_commodity})"])

    else:
        print("Invalid refining method.")
        return -1


    ## aggregating emissions and errors
    df_to_be_modified[f"Total emissions (kg_CO2e/kg_{bat_chemical_for_commodity})"] = df_to_be_modified[f"Total modelled emissions (kg_CO2e/kg_{bat_chemical_for_commodity})"] + df_to_be_modified[f"Refining emissions (kg_CO2e/kg_{bat_chemical_for_commodity})"]

    df_to_be_modified[f"moe_total_emissions_lower_bound (kg_CO2e/kg_{bat_chemical_for_commodity})"] = df_to_be_modified[f"moe_modelled_emissions (kg_CO2e/kg_{bat_chemical_for_commodity})"] + df_to_be_modified[f"moe_refining_emissions_lower_bound (kg_CO2e/kg_{bat_chemical_for_commodity})"]
    df_to_be_modified[f"moe_total_emissions_upper_bound (kg_CO2e/kg_{bat_chemical_for_commodity})"] = df_to_be_modified[f"moe_modelled_emissions (kg_CO2e/kg_{bat_chemical_for_commodity})"] + df_to_be_modified[f"moe_refining_emissions_upper_bound (kg_CO2e/kg_{bat_chemical_for_commodity})"]

    emission_types_normalised = emission_types_converted + [f"Refining emissions (kg_CO2e/kg_{bat_chemical_for_commodity})"]

    return df_to_be_modified, emission_types_normalised

def linear_interpolation(x1, y1, x2, y2, x):
    """
    Perform linear interpolation.

    Parameters:
    - x1, y1: coordinates of the first point.
    - x2, y2: coordinates of the second point.
    - x: the x-value for which to interpolate.

    Returns:
    - The interpolated y-value corresponding to the given x.
    """
    if x1 == x2:
        raise ValueError("x1 and x2 cannot be the same value.")
    y = y1 + ((x - x1) * (y2 - y1)) / (x2 - x1)
    return y

def lit_values_to_df(commodity):
    excel_literature_file_path = os.path.join(gv.path_project, "resources", "Literature_values.xlsx")
    sheet_name = commodity + " - List"

    df_lit_values = pd.read_excel(excel_literature_file_path, sheet_name=sheet_name, usecols="A:C", skiprows=1, header=0)
    df_lit_values = df_lit_values.rename(columns={df_lit_values.columns[1]: "GWP"}).dropna()

    return df_lit_values

def convert_t_metal_to_kg_chemical(input):
    # Ensure the input series elements are treated as numeric values
    numeric_input = pd.to_numeric(input, errors='coerce')  # 'coerce' will set invalid parsing as NaN

    # conversion factor from Excel file and mass unit change from t to kg
    return numeric_input * conversion_factor_metal_to_bat_chemical / 1000