# %%
# -*- coding: utf-8 -*-
"""
Spyder Editor

This is a temporary script file.
"""




from numpy import arange
import xarray as xr
import highspy
from linopy import Model, EQUAL
import pandas as pd
import plotly.express as px
import streamlit as st
import sourced as src
st.set_page_config(layout="wide")
# you can create columns to better manage the flow of your page
# this command makes 3 columns of equal width
col1, col2, col3, col4 = st.columns(4)
col1.header("Data Input")
col4.header("Download Results")
# %%
with col1:
    with open('Input_Jahr_2021.xlsx', 'rb') as f:
        st.download_button('Download Excel Template', f, file_name='Input_Jahr_2021.xlsx')  # Defaults to 'application/octet-stream'

#url_excel = r'Input_Jahr_2021.xlsx'
    url_excel = st.file_uploader(label = 'Excel Upload')


if url_excel == None:
    url_excel = r'Input_Jahr_2021.xlsx'
    sets_dict, params_dict= src.load_data_from_excel(url_excel, load_from_pickle_flag = True)
    with col4:
        st.write('Running with standard data')
else:
    sets_dict, params_dict= src.load_data_from_excel(url_excel, load_from_pickle_flag = False)
    with col4:
        st.write('Running with user data')

# # %%

def timstep_aggregate(time_steps_aggregate, xr ):
    return xr.rolling( t = time_steps_aggregate).mean().sel(t = t[0::time_steps_aggregate])

#s_t_r_iRes = timstep_aggregate(6,s_t_r_iRes)



# %%
#sets_dict, params_dict= src.load_data_from_excel(url_excel,write_to_pickle_flag=True)

# %%



#sets_dict, params_dict= load_data_from_excel(url_excel, load_from_pickle_flag = False)


dt = 6
# Unpack sets_dict into the workspace
t = sets_dict['t']
i = sets_dict['i']
iSto = sets_dict['iSto']
iConv = sets_dict['iConv']
iPtG = sets_dict['iPtG']
iRes = sets_dict['iRes']
iHyRes = sets_dict['iHyRes']

# Unpack params_dict into the workspace
l_co2 = params_dict['l_co2']
p_co2 = params_dict['p_co2']

D_t = timstep_aggregate(dt,params_dict['D_t'])
eff_i = params_dict['eff_i']
c_fuel_i = params_dict['c_fuel_i']
c_other_i = params_dict['c_other_i']
c_inv_i = params_dict['c_inv_i']
co2_factor_i = params_dict['co2_factor_i']
#c_var_i = params_dict['c_var_i']
s_t_r_iRes = timstep_aggregate(dt,params_dict['s_t_r_iRes'])
K_0_i = params_dict['K_0_i']
e2p_iSto = params_dict['e2p_iSto']
h_t = timstep_aggregate(dt,params_dict['h_t'])

t = D_t.get_index('t')
partial_year_factor = (8760/len(t))/dt
# # Slider for gas price [€/MWh_th]
#price_gas = st.slider(value=100, min_value=0, max_value=400, label="Natural gas price [€/MWh]", step=10)

# Slider for CO2 price [€/t]
#price_co2 = st.slider(value=0, min_value=0, max_value=400, label="CO2 price [€/t CO2eq]", step=10)

with col2:
    # Slider for CO2 limit [mio. t]
    l_co2 = st.slider(value=int(params_dict['l_co2']), min_value=0, max_value=750, label="CO2 limit [mio. t]", step=50)

    # Slider for H2 price / usevalue [€/MWH_th]
    price_h2 = st.slider(value=100, min_value=0, max_value=300, label="Hydrogen price [€/MWh]", step=10)

    for i_idx in c_fuel_i.get_index('i'):
            if i_idx in ['Lignite']:
                c_fuel_i.loc[i_idx] = st.slider(value=int(c_fuel_i.loc[i_idx]), min_value=0, max_value=300, label=i_idx + ' Price' , step=10)

with col3:
    # Slider for CO2 limit [mio. t]
    for i_idx in c_fuel_i.get_index('i'):
            if i_idx in ['Fossil Hard coal', 'Fossil Oil','Fossil Gas']:
                c_fuel_i.loc[i_idx] = st.slider(value=int(c_fuel_i.loc[i_idx]), min_value=0, max_value=300, label=i_idx + ' Price' , step=10)


#time_steps_aggregate = 6
#= xr_profiles.rolling( time_step = time_steps_aggregate).mean().sel(time_step = time[0::time_steps_aggregate])
price_co2 = 0


#technologies_no_invest = st.multiselect(label='Technolgy invest', options=i)
technologies_no_invest = ['Electrolyzer','Biomass','RoR']
# %%
### Variables
m = Model()

C_tot = m.add_variables(name = 'C_tot')     # Total costs
C_op = m.add_variables(name = 'C_op', lower = 0)       # Operational costs
C_inv = m.add_variables(name = 'C_inv', lower = 0)     # Investment costs

K = m.add_variables(coords = [i], name = 'K', lower = 0)          # Endogenous capacity
y = m.add_variables(coords = [t,i], name = 'y', lower = 0)          # Electricity production --> für Elektrolyseure ausschließen
y_ch = m.add_variables(coords = [t,i], name = 'y_ch', lower = 0)    # Electricity consumption --> für alles außer Elektrolyseure und Speicher ausschließen
l = m.add_variables(coords = [t,i], name = 'l', lower = 0)          # Storage filling level
w = m.add_variables(coords = [t], name = 'w', lower = 0)          # RES curtailment


## Objective function
C_tot = C_op + C_inv
m.add_objective(C_tot)

## Costs terms for objective function
# Operational costs minus revenue for produced hydrogen
C_op_sum = m.add_constraints((y * c_fuel_i/eff_i).sum()*dt*partial_year_factor  == C_op, name = 'C_op_sum')

# Investment costs
C_inv_sum = m.add_constraints((K * c_inv_i).sum() == C_inv, name = 'C_inv_sum')

## Load serving
loadserve_t = m.add_constraints(((y ).sum(dims = 'i') - (w ) - y_ch.sum(dims = 'i')  == D_t.sel(t = t) ), name =  'load')

## Maximum capacity limit
maxcap_i_t = m.add_constraints((y - K <= K_0_i), name =  'max_cap')

## Maximum capacity limit
maxcap_invest_i = m.add_constraints((K.sel(i = technologies_no_invest) <= 0), name =  'max_cap_invest')

## Maximum storage charging and discharging
maxcha_iSto_t = m.add_constraints((y.sel(i = iSto) + y_ch.sel(i = iSto)  - K.sel(i = iSto) <= K_0_i.sel(i = iSto)), name =  'max_cha')

## Maximum electrolyzer capacity
ptg_prod_iPtG_t = m.add_constraints((y_ch.sel(i = iPtG) - K.sel(i = iPtG)<= K_0_i.sel(i = iPtG)), name = 'max_cha_ptg')

## Infeed of renewables
infeed_iRes_t = m.add_constraints((y.sel(i = iRes) - s_t_r_iRes.sel(i = iRes).sel(t = t) * K.sel(i = iRes) <= s_t_r_iRes.sel(i = iRes).sel(t = t) * K_0_i.sel(i = iRes)), name =  'infeed')

## Maximum filling level restriction storage power plant --> Energy-to-Power-Ratio eingeführt. (JR)
maxcapsto_iSto_t = m.add_constraints((l.sel(i = iSto) - K.sel(i = iSto) * e2p_iSto.sel(i = iSto) <= K_0_i.sel(i = iSto) * e2p_iSto.sel(i = iSto)), name = 'max_sto_filling')

## Filling level restriction hydro reservoir --> Ist Kreisbedingung erfüllt? (JR)
filling_iHydro_t = m.add_constraints(l.sel(i = iHyRes) - l.sel(i = iHyRes).roll(t = -1) + y.sel(i = iHyRes) * dt == h_t.sel(t = t) * dt, name = 'filling_level_hydro')

## Filling level restriction other storages --> Ist Kreisbedingung erfüllt? (JR)
filling_iSto_t = m.add_constraints(l.sel(i = iSto) - (l.sel(i = iSto).roll(t = -1) + (y.sel(i = iSto) ) * dt - y_ch.sel(i = iSto) * eff_i.sel(i = iSto) * dt) == 0, name = 'filling_level')

## CO2 limit --> ggf. hier auch mit Subset arbeiten (Technologien, die Brennstoff verbrauchen). (JR)
CO2_limit = m.add_constraints(((y / eff_i) * co2_factor_i * dt).sum()* partial_year_factor <= l_co2*1_000_000 , name = 'CO2_limit')


# %%
m.solve(solver_name = 'highs')

st.markdown("---")

colb1, colb2 = st.columns(2)

# %%
#c_var_i.to_dataframe(name='VarCosts')
# %%
# Installed Cap
# Assuming df_excel has columns 'All' and 'Capacities'

fig = px.bar((m.solution['K']+K_0_i).to_dataframe(name='K').reset_index(), \
                y='i', x='K', orientation='h', title='Total Installed Capacities', color='i')

#fig

# %%
df_new_capacities = m.solution['K'].to_dataframe().reset_index()
fig = px.bar(m.solution['K'].to_dataframe().reset_index(), y='i', x='K', orientation='h', title='New Capacities', color='i')

with colb1:
    fig

# %%
i_with_capacity = m.solution['K'].where( m.solution['K'] > 0).dropna(dim = 'i').get_index('i')
df_production = m.solution['y'].sel(i = i_with_capacity).to_dataframe().reset_index()
fig = px.area(m.solution['y'].sel(i = i_with_capacity).to_dataframe().reset_index(), y='y', x='t',  title='Production', color='i')


with colb2:
    fig


# %%

df_price = m.constraints['load'].dual.to_dataframe().reset_index()
df_price['dual'] = df_price['dual']/dt

# %%
fig = px.line(df_price, y='dual', x='t',  title='Prices')
with colb1:
    fig


# %% 

df_contr_marg = m.constraints['max_cap'].dual.to_dataframe().reset_index()
df_contr_marg['dual'] = df_contr_marg['dual']/dt
# %%

fig = px.line(m.constraints['max_cap'].dual.to_dataframe().reset_index(), y='dual', x='t',title='contribution margin', color='i')
with colb2:
    fig

    

# %%
df_Co2_price = pd.DataFrame({'CO2_Price': [float(m.constraints['CO2_limit'].dual.values)]})

with colb2:
    st.write('CO2 Price ' + str(df_Co2_price))


# %%
((m.solution['y'] / eff_i) * co2_factor_i * dt).sum()
# %%

import pandas as pd
from io import BytesIO
#from pyxlsb import open_workbook as open_xlsb
import streamlit as st
import xlsxwriter
# %%
output = BytesIO()


# Create a Pandas Excel writer using XlsxWriter as the engine
with pd.ExcelWriter(output, engine='xlsxwriter') as writer:
    # Write each DataFrame to a different sheet
    df_price.to_excel(writer, sheet_name='Prices', index=False)
    df_contr_marg.to_excel(writer, sheet_name='Contribution Margin', index=False)
    df_new_capacities.to_excel(writer, sheet_name='Capacities', index=False)
    df_production.to_excel(writer, sheet_name='Production', index=False)
    df_Co2_price.to_excel(writer, sheet_name='CO2_Price', index=False)
    

with col4:
    st.download_button(
        label="Download Excel workbook Results",
        data=output.getvalue(),
        file_name="workbook.xlsx",
        mime="application/vnd.ms-excel"
    )