import streamlit as st
import pandas as pd
import numpy as np

#st.set_page_config(layout="wide")

df = pd.read_csv('last_results_5.csv')

temp_data = pd.read_csv('temp_data(2).csv')

image1 = 'images/rs_pmpa.PNG'

title_html = """
    <style>
        @font-face {
            font-family: 'Quicksand';
            src: url('font/Quicksand-VariableFont_wght.ttf') format('truetype');
        }
        body {
            font-family: 'Quicksand', sans-serif;
        }
        .custom-title {
            color: darkgreen;
            font-size: 30px;
            font-weight: bold;
        }
    </style>
    <span class='custom-title'>PREVISÕES DE RECEITAS</span>
"""

# Set a fixed width for the sidebar
st.markdown(
    """
    <style>
        .sidebar .sidebar-content {
            width: 300px;
        }
    </style>
    """,
    unsafe_allow_html=True
)

with st.sidebar:
    st.image(image1, use_column_width=True)
    st.markdown(title_html, unsafe_allow_html=True)
    selected_instituicao = st.selectbox('Seleciona Instituição', df['Instituição'].unique())
    selected_conta = st.selectbox('Seleciona Conta', df['Conta'].unique())

# Filter the DataFrame based on selected values
filtered_df = df[(df['Instituição'] == selected_instituicao) & (df['Conta'] == selected_conta)]

#col1, col2, col3 = st.columns(3) # This divides the page into three equal parts

# Set custom width for columns
col1_width = 400
col2_width = 400
col1, col2 = st.columns([col1_width, col2_width])

# Display the Forecasts values in the first column
col1.header('Valores previstos')

if not filtered_df.empty:
    data_string = filtered_df['Forecasts'].iloc[0]

    # Split the string into lines
    lines = data_string.split('\n')

    # Iterate through the lines and extract the values
    for line in lines[:-2]:
        period, value = line.split()
        num_float = float(value)
        monetary_value = f'R$ {num_float:,.2f}'  # Adding commas for thousands separator
        col1.write(f"Período {period}: {monetary_value}")
else:
    col1.warning('No data available for the selected filters.')

# Display the Forecasts values as line plots in the second column
col2.header('Gráfico com previsões')

if not filtered_df.empty:
    data_string = filtered_df['Forecasts'].iloc[0]

    # Create a list to store data for each period
    data = []

    # Split the string into lines
    lines = data_string.split('\n')

    # Iterate through the lines and extract the values
    for line in lines[:-2]:
        period, value = line.split()
        num_float = float(value)
        monetary_value = f'R$ {num_float:,.2f}'  # Adding commas for thousands separator
        data.append({'Period': int(period), 'Monetary Value': num_float})

    # Create a DataFrame from the list
    chart_data = pd.DataFrame(data)

    # Sort the DataFrame by 'Period'
    chart_data = chart_data.sort_values(by='Period')

    # Display line chart with "period" on X-axis and "Monetary Value" on Y-axis
    col2.line_chart(chart_data.set_index('Period'))

else:
    col2.warning('No data available for the selected filters.')

# Display the table in the third column
#col3 = st.columns(1)  # You can use st.columns(1) to create a single column layout

#col3.header('Resultados')
if not filtered_df.empty:
    # Filter the DataFrame for the selected institution
    tab_df = df[df['Instituição'] == selected_instituicao]

    # Create an empty list to store data
    data = []

    # Iterate through each unique 'Conta' in the filtered DataFrame
    for conta in tab_df['Conta'].unique():
        # Filter the DataFrame for the current 'Conta'
        conta_df = tab_df[tab_df['Conta'] == conta]

        # Initialize a variable to store the sum for the current 'Conta'
        conta_sum = 0.0

        # Take the first 'Modelo' for simplicity
        modelo = conta_df['Modelo'].iloc[0]

        # Iterate over each row in the filtered DataFrame for the current 'Conta'
        for _, row in conta_df.iterrows():
            lines = row['Forecasts'].split('\n')
            for line in lines[:-1]:  # Skip the summary line
                if line.strip():
                    parts = line.split()
                    value = parts[-1]
                    try:
                        conta_sum += float(value)
                    except ValueError:
                        print(f"Skipping line unable to convert to float: {line}")

        # Format the sum as a monetary value
        monetary_value = f'R$ {conta_sum:,.2f}'

        # Append the data to the list
        data.append({'Conta': conta, 'Modelo': modelo, 'Valor Monetário': monetary_value})

    # Convert the list to a DataFrame
    table_data = pd.DataFrame(data)

    # Calculate the grand total sum of all 'Conta' values
    total_sum = sum(float(row['Valor Monetário'].replace('R$ ', '').replace(',', '')) for row in data)

    # Append the "Total" row
    total_row = pd.DataFrame({'Conta': ['TOTAL (RLIT)'], 'Modelo': [''], 'Valor Monetário': [f'R$ {total_sum:,.2f}']})
    table_data = pd.concat([table_data, total_row], ignore_index=True)

    # Calculate and append the rows for "Saúde (12% da RLIT)" and "Educação (25% da RLIT)"
    saude_value = total_sum * 0.15
    educacao_value = total_sum * 0.25
    saude_row = pd.DataFrame({'Conta': ['Saúde (15% da RLIT)'], 'Modelo': [''], 'Valor Monetário': [f'R$ {saude_value:,.2f}']})
    educacao_row = pd.DataFrame({'Conta': ['Educação (25% da RLIT)'], 'Modelo': [''], 'Valor Monetário': [f'R$ {educacao_value:,.2f}']})

    # Append these rows to the table data
    table_data = pd.concat([table_data, saude_row, educacao_row], ignore_index=True)

    # Convert 'Data_Completa' to datetime format to ensure correct processing
    temp_data['Data_Completa'] = pd.to_datetime(temp_data['Data_Completa'])

    # Assuming 'Data_Completa' is sorted, if not, you should sort it.
    # temp_data = temp_data.sort_values(by='Data_Completa', ascending=False)

    # Initialize an empty list to store data including the 'Último ano' sums
    data_with_last_year = []

    # Iterate over each unique 'Instituição' and 'Conta' combination in 'df'
    for instituicao in df['Instituição'].unique():
        for conta in df[df['Instituição'] == instituicao]['Conta'].unique():
            # Filter temp_data for the current 'Instituição' and 'Conta'
            filtered_temp = temp_data[(temp_data['Instituição'] == instituicao) & (temp_data['Conta'] == conta)]

            # Get the last 12 periods of 'Data_Completa'
            last_12_periods = filtered_temp.nlargest(12, 'Data_Completa')

            # Calculate the sum of 'Valor' for these periods
            last_year_sum = last_12_periods['Valor'].sum()

            # Append this information to the data list
            data_with_last_year.append({
                'Instituição': instituicao,
                'Conta': conta,
                'Último ano': last_year_sum
            })

    # Convert the list to a DataFrame
    last_year_data = pd.DataFrame(data_with_last_year)

    # Merge this DataFrame with your existing table data to add the 'Último ano' column
    # Assuming 'table_data' is your existing DataFrame that you want to add the column to
    # You might need to adjust column names or merge keys based on your actual data structure
    table_data = table_data.merge(last_year_data, on=['Instituição', 'Conta'], how='left')


    # Display the table
    st.table(table_data)

else:
    col3.warning('No data available for the selected filters.')


st.markdown("""
    <b>Observação:</b> Previsões realizadas com dados extraídos do Relatório Resumido de Execução Orçamentária (RREO) até o 6º bimestre de 2023 no Sistema de Informações Contábeis e Fiscais do Setor Público Brasileiro (SICONFI). 
    [Link](https://siconfi.tesouro.gov.br/)
    """, unsafe_allow_html=True)