import streamlit as st
import pandas as pd
import plotly.express as px

file_path = 'structured_data.xlsx'
sheets = pd.read_excel(file_path, sheet_name=None)

data = []

for sheet_name, sheet_data in sheets.items():
    year = sheet_name.split('_')[0]
    sheet_data['Year'] = year
    sheet_data['state']=sheet_name.split('_')[1]
    data.append(sheet_data)
df = pd.concat(data, ignore_index=True)
graph_type=st.selectbox('Select Graph Type',['Year-Wise','Branch-Wise'])
if graph_type=='Branch-Wise':
    st.title('Branch-wise Cutoff Analysis')
    branch = st.selectbox('Select Branch Stream', df['Stream'].unique())
    state = st.selectbox('Select State', df['state'].unique())
    category_delhi=['GNGND', 'EWGND', 'OBGND', 'SCGND', 'STGND',
       'GNGLD', 'EWGLD', 'OBGLD', 'SCGLD', 'STGLD', 'GNCWD', 'EWCWD', 'OBCWD',
       'SCCWD', 'STCWD', 'GNPDD', 'EWPDD', 'OBPDD', 'SCPDD', 'STPDD', 'KM']
    category_outside=['GNGNO', 'EWGNO', 'OBGNO', 'SCGNO', 'STGNO',
       'GNGLO', 'EWGLO', 'OBGLO', 'SCGLO', 'STGLO', 'GNCWO', 'EWCWO', 'OBCWO',
       'SCCWO', 'STCWO', 'GNPDO', 'EWPDO', 'OBPDO', 'SCPDO', 'STPDO']
    if state=='Delhi':
        #category = st.selectbox('Select Category',category_delhi)
        category = 'GNGND'
    else:
        #category = st.selectbox('Select Category',category_outside)
        category = 'GNGNO'
    branch_data = df[df['Stream'] == branch]
    branch_data = branch_data[branch_data['state'] == state]
    branch_data=branch_data[['Course', 'Year', category,]]
    if not branch_data.empty:
        fig = px.histogram(branch_data, x='Year', y=category, color='Course',
                  title=f'Year-wise General Category Cutoff for {branch}',
                  labels={category: f'{category}Cutoff'})

        fig.update_layout(
        xaxis_title='Year',
        yaxis_title='Cutoff Rank',
        yaxis_type='linear',
        template='plotly_white'
        )

        st.plotly_chart(fig)
    else:
        st.write(f"No data available for the selected branch: {branch}")

        st.write("Raw data for the selected branch:")
    st.dataframe(branch_data)
else:
    st.title('Year-wise Cutoff Analysis')
    year = st.selectbox('Select Year', df['Year'].unique())
    state = st.selectbox('Select State', df['state'].unique())
    category_delhi=['GNGND', 'EWGND', 'OBGND', 'SCGND', 'STGND',
       'GNGLD', 'EWGLD', 'OBGLD', 'SCGLD', 'STGLD', 'GNCWD', 'EWCWD', 'OBCWD',
       'SCCWD', 'STCWD', 'GNPDD', 'EWPDD', 'OBPDD', 'SCPDD', 'STPDD', 'KM']
    category_outside=['GNGNO', 'EWGNO', 'OBGNO', 'SCGNO', 'STGNO',
       'GNGLO', 'EWGLO', 'OBGLO', 'SCGLO', 'STGLO', 'GNCWO', 'EWCWO', 'OBCWO',
       'SCCWO', 'STCWO', 'GNPDO', 'EWPDO', 'OBPDO', 'SCPDO', 'STPDO']
    if state=='Delhi':
        #category = st.selectbox('Select Category',category_delhi)
        category='GNGND'
    else:
        #category = st.selectbox('Select Category',category_outside)
        category='GNGNO'
    year_data = df[df['Year'] == year]
    year_data = year_data[year_data['state'] == state]
    year_data=year_data[['Course', 'Stream', category,]]
    if not year_data.empty:
        fig = px.histogram(year_data, x='Course', y=category, color='Stream',
                  title=f'Branch-wise General Category Cutoff for {year}',
                  labels={category: f'{category}Cutoff'},
                  )

        fig.update_layout(
        xaxis_title='Branch',
        yaxis_title='Cutoff Rank',
        yaxis_type='linear',
        template='plotly_white'
        )

        st.plotly_chart(fig)
    else:
        st.write(f"No data available for the selected year: {year}")

        st.write("Raw data for the selected year:")
    st.dataframe(year_data)