import streamlit as st
import sqlite3
import pandas as pd
import os

## Label Metrics Size
st.markdown("""<style>[data-testid="stMetricValue"] {font-size: 20px;}</style>""", unsafe_allow_html=True,)

## Horizontal Radio Button
st.write('<style>div.row-widget.stRadio > div{flex-direction:row;justify-content: left;} </style>', unsafe_allow_html=True)

#st.set_page_config(page_title="The Ramsey Highlights", layout="wide")
st.markdown("""<style>[data-testid="stSidebar"][aria-expanded="true"] > div:first-child{width: 370px;}
    [data-testid="stSidebar"][aria-expanded="false"] > div:first-child{width: 370px;margin-left: -370px;}""",
    unsafe_allow_html=True,
)

pro_edp = 'https://huggingface.co/datasets/strauss-oak/afa-bd/raw/main/tb-professors.csv'
sch_edp = 'https://huggingface.co/datasets/strauss-oak/afa-bd/resolve/main/drw-inep.csv'
std_edp = 'https://huggingface.co/datasets/strauss-oak/afa-bd/resolve/main/tb-students.csv'

conn = sqlite3.connect("afa-db.db")

sql_example = """SELECT alu_id, alu_nome, alu_uf, alu_cep, progresso_alfabetizacao FROM tb_students WHERE progresso_alfabetizacao >= 6 LIMIT 100"""

txt_example = """Qual um dos estudantes com maior nota no estado de SP?"""

@st.cache_resource
def refresh_data_professors():
    df_pro = pd.read_csv(pro_edp)
    n_pro = df_pro.shape[0]
    df_pro.to_sql("tb_professors", conn, if_exists="replace")
    df_pro = df_pro[:10]
    return df_pro, n_pro

@st.cache_resource
def refresh_data_schools():
    df_sch = pd.read_csv(sch_edp, sep=';')
    n_sch = df_sch.shape[0]
    df_sch.to_sql("tb_schools", conn, if_exists="replace")
    df_sch = df_sch[:10]
    return df_sch, n_sch

@st.cache_resource
def refresh_data_students():
    df_std = pd.read_csv(std_edp)
    n_std = df_std.shape[0]
    df_std.to_sql("tb_students", conn, if_exists="replace")
    df_std = df_std[:10]
    return df_std, n_std

def execute_sql(sql):
    data = None
    try:
        data = pd.read_sql(sql, conn)
    except Exception as err:
        st.error(err)
    return data

st.info('Amostras de Dados:')
e1 = st.expander('Professores:', expanded=False)
with e1:
    df, n = refresh_data_professors()
    st.dataframe(df)
    st.warning('A tabela professores possui {0} registros'.format(n))

e2 = st.expander('Escolas:', expanded=False)
with e2:
    df, n = refresh_data_schools()
    st.dataframe(df)
    st.warning('A tabela escolas possui {0} registros'.format(n))

e3 = st.expander('Estudantes:', expanded=False)
with e3:
    df, n = refresh_data_students()
    st.dataframe(df)
    st.warning('A tabela estudantes possui {0} registros'.format(n))

st.info('Consultas:')
e4 = st.expander('Linguagem SQL:', expanded=False)
with e4:
    sql = st.text_input("Digite uma instrução SQL válida:", sql_example)
    if st.button("Processar instrução"):
        df = execute_sql(sql)
        if df is not None:
            st.dataframe(df)
            st.warning('A consulta retornou {0} registros'.format(df.shape[0]))
            del df

e5 = st.expander('Linguagem Natural:', expanded=False)
with e5:
    txt = st.text_input("Digite um texto de consulta válido:", txt_example)
    if st.button("Processar texto"):
        st.write("Em construção")


#sql = """SELECT alu_id, alu_nome, alu_uf, alu_cep, progresso_alfabetizacao FROM tb_students
#         WHERE progresso_alfabetizacao >= 6 LIMIT 100"""
#dfp_std = pd.read_sql(sql, conn)
#st.dataframe(dfp_std)
#del dfp_std
#dfp_std.head(4)