import pandas as pd
import streamlit as st
import plotly.graph_objects as go

df = pd.read_excel("result.xlsx")
def get_lat_lon(row):
    row = eval(row['result_dadata'])[0]
    lat = row['geo_lat']
    lon = row['geo_lon']
    adress = row['result']

    return lat, lon, adress

def get_colors(row):
    if row > 10000:
        return 'red'
    elif row <= 10000 and row > 4000:
        return 'blue'
    elif row <= 4000 and row > 2000:
        return 'yellow'
    # elif row <= 2000 and row > 1000:
    #     return 'green'
    # elif row <= 200 and row > 100:
    #     return 'purple'
    # elif row <= 100 and row > 30:
    #     return 'orange'
    else:
        return 'black'

df[['lat', 'lon', 'adress']] = df.apply(get_lat_lon, axis=1, result_type='expand')
viz_df = df[['lpu_name','lat', 'lon', 'adress', 'MT_SUM_RUR']].copy()
viz_df['MT_SUM_RUR'] = viz_df['MT_SUM_RUR']/1000

viz_df[['lat','lon']] = viz_df[['lat','lon']].astype(float)
viz_df['colors'] = viz_df['MT_SUM_RUR'].apply(get_colors)

text_series ="Название клиники: "+ viz_df['lpu_name'] + "<br>" + "Адрес: " + viz_df["adress"] + "<br>" + "Сумма:" + viz_df['MT_SUM_RUR'].astype(str) + " тыс.руб"
fig_bubbles_coords = go.Figure(data=go.Scattermapbox(
    lat=viz_df['lat'],
    lon=viz_df['lon'],
    mode='markers',
    marker=go.scattermapbox.Marker(
        size=viz_df['MT_SUM_RUR'],
        color='red',
        sizemode='area',
        sizeref=5,
        opacity=0.6
    ),
    text=text_series,
))

fig_bubbles_coords.update_layout(
    mapbox_style='open-street-map',
    autosize=True,
    hovermode='closest',
    showlegend=False,
    mapbox=dict(
        center=dict(lat=viz_df['lat'].mean(), lon=viz_df['lon'].mean()),
        zoom=9
    ),
    width=900,
    height=900
)

fig_bubbles = go.Figure(data=go.Scattermapbox(
    lat=viz_df['lat'],
    lon=viz_df['lon'],
    mode='markers',
    marker=go.scattermapbox.Marker(
        size=viz_df['MT_SUM_RUR'],
        color='red',
        sizemode='area',
        sizeref=5,
        opacity=0.6,
    ),
    customdata=text_series,
))

fig_bubbles.update_layout(
    mapbox_style='open-street-map',
    autosize=True,
    hovermode='closest',
    showlegend=False,
    mapbox=dict(
        center=dict(lat=viz_df['lat'].mean(), lon=viz_df['lon'].mean()),
        zoom=9
    ),
    width=900,
    height=900
)

fig_bubbles.update_traces(hovertemplate='<b>%{customdata}</b>')

fig_heatbar = go.Figure(data=go.Scattermapbox(
    lat=viz_df['lat'],
    lon=viz_df['lon'],
    mode='markers',
    marker=go.scattermapbox.Marker(
        color=viz_df['MT_SUM_RUR'],
        sizemode='area',
        sizeref=5,
        opacity=1,
        colorscale='rdylbu',  # Градиент цветов от светло-синего до темно-синего
        colorbar=dict(title='MT_SUM_RUR')
    ),
    customdata=text_series,
))

fig_heatbar.update_layout(
    mapbox_style='open-street-map',
    autosize=True,
    hovermode='closest',
    showlegend=False,
    mapbox=dict(
        center=dict(lat=viz_df['lat'].mean(), lon=viz_df['lon'].mean()),
        zoom=9
    ),
    width=900,
    height=900
)

fig_heatbar.update_traces(hovertemplate='<b>%{customdata}</b>')

dct_colors = {'red':'больше 10000',
              'blue':'10000-4000',
              'yellow':'4000-2000',
            #   'green':'400-200',
            #   'purple':'200-100',
            #   'orange':'100-30',
              'black':'меньше 2000'}
colors = ['red',
          'blue', 
          'yellow', 
        #   'green',
        #   'purple',
        #   'orange',
          'black']

fig_colored = go.Figure()

for color in colors:
    temp_df = viz_df[viz_df['colors'] == color]
    text_series_temp = "Название клиники: "+ temp_df['lpu_name'] + "<br>" + "Адрес: " + temp_df["adress"] + "<br>" + "Сумма: " + temp_df['MT_SUM_RUR'].astype(str) + " тыс.руб"
    fig_colored.add_trace(go.Scattermapbox(
        lat=temp_df['lat'],
        lon=temp_df['lon'],
        mode='markers',
        marker=go.scattermapbox.Marker(
            color=color,
            sizemode='area',
            sizeref=5,
            opacity=1,
        ),
        name=dct_colors[color],
        customdata=text_series_temp,
    ))


fig_colored.update_layout(
    mapbox_style='open-street-map',
    autosize=True,
    hovermode='closest',
    showlegend=True,
    mapbox=dict(
        center=dict(lat=viz_df['lat'].mean(), lon=viz_df['lon'].mean()),
        zoom=9
    ),
    width=900,
    height=900
)
fig_colored.update_traces(hovertemplate='<b>%{customdata}</b>')

viz_df = viz_df.rename(columns={"MT_SUM_RUR":"Сумма"})

st.dataframe(data=viz_df[['lpu_name', 'Сумма', 'adress']])
st.plotly_chart(fig_bubbles)
st.plotly_chart(fig_heatbar)
st.plotly_chart(fig_colored)

# %%