import streamlit as st
from utils.sql_utils import load_data, load_defaultdb_schema_text
from utils.handle_sql_commands import execute_sql_duckdb

st.markdown(
    """
<style>
    /* Base styles for both themes */
    .stPageLink {
        background-image: linear-gradient(to right, #007BFF, #6610F2); /* Gradient background */
        color: white !important; /* Ensure text is readable on the gradient */
        padding: 12px 20px !important; /* Slightly larger padding */
        border-radius: 8px !important; /* More rounded corners */
        border: none !important; /* Remove default border */
        text-decoration: none !important;
        font-weight: 500 !important; /* Slightly lighter font weight */
        transition: transform 0.2s ease-in-out, box-shadow 0.2s ease-in-out; /* Smooth transitions */
        box-shadow: 0 2px 5px rgba(0, 0, 0, 0.15); /* Subtle shadow for depth */
        display: inline-flex;
        align-items: center;
        justify-content: center;
    }

    .stPageLink:hover {
        transform: scale(1.03); /* Slight scale up on hover */
        box-shadow: 0 4px 8px rgba(0, 0, 0, 0.2); /* Increased shadow on hover */
    }

    .stPageLink span { /* Style the label text */
        margin-left: 5px; /* Space between icon and text */
    }

    /* Dark theme adjustments (optional, if needed for better contrast) */
    /* Consider using Streamlit's theme variables if possible for a more robust solution */
    /* For simplicity, this example uses fixed colors that should work reasonably well */
    /* [data-theme="dark"] .stPageLink {
    }

    [data-theme="dark"] .stPageLink:hover {
    } */
</style>
""",
    unsafe_allow_html=True,
)

default_dfs = load_data()

if "uploaded_dataframes" not in st.session_state:
    st.session_state.uploaded_dataframes = {}

uploaded_dataframes = st.session_state.uploaded_dataframes


with st.popover("Click here to see Database Schema", use_container_width=True):
    uploaded_df_schema = st.session_state.get("uploaded_df_schema", False)

    choice = st.segmented_control(
        "Choose",
        ["Default DB", "Uploaded Files"],
        label_visibility="collapsed",
        disabled=uploaded_df_schema == False,
        default="Default DB" if uploaded_df_schema == False else "Uploaded Files",
    )

    if uploaded_df_schema is False:
        st.markdown("""> If you want to use your own files(csv/xls), click here - """)
        st.page_link(
            page="pages/3 📂File Upload for SQL.py",
            label="Upload your own CSV or Excel files",
            icon="📜",
        )
        schema = load_defaultdb_schema_text()
        st.markdown(schema, unsafe_allow_html=True)
    elif choice == "Default DB":
        schema = load_defaultdb_schema_text()
        st.markdown(schema, unsafe_allow_html=True)
    else:
        pretty_schema, markdown = st.tabs(["Schema", "Copy Schema in Markdown"])
        with pretty_schema:
            st.info(
                "You can copy this schema, and give it to any state of the art LLM models like (Gemini /ChatGPT /Claude etc) to cross check your answers.\n You can run the queries directly here, by using ***Manual Query Executer*** in the sidebar and download your results 😊",
                icon="ℹ️",
            )
            st.markdown(uploaded_df_schema, unsafe_allow_html=True)
        with markdown:
            st.info(
                "You can copy this schema, and give it to any state of the art LLM models like (Gemini /ChatGPT /Claude etc) to cross check your answers.\n You can run the queries directly here, by using ***Manual Query Executer*** in the sidebar and download your results 😊",
                icon="ℹ️",
            )
            st.markdown(f"```\n{uploaded_df_schema}\n```")


data_source = None
if uploaded_dataframes is None or len(uploaded_dataframes) == 0:
    data_source = "Default Database"
    col1, col2 = st.columns([4, 1])
    with col1:
        st.caption(
            "Use this dialog to execute SQL commands on the Default Database. To use your own Excel/CSV files"
        )
    with col2:
        st.page_link(page="pages/3 📂File Upload for SQL.py", label="Click Here")
else:
    data_source = st.radio("Select Data Source", ["Default Database", "Uploaded Files"])

if data_source == "Default Database":
    dataframes = default_dfs
    if uploaded_dataframes is not None and len(uploaded_dataframes) > 0:
        st.caption(
            "Use this dialog to execute SQL commands on the Default Database. To use your own Excel/CSV files"
        )
else:
    dataframes = uploaded_dataframes
    st.caption("Use this dialog to execute SQL commands on Your Uploaded Files")

sql_command = st.text_area("Enter your SQL command here:")
if st.button("Execute"):
    df = execute_sql_duckdb(sql_command, dataframes)
    if df is not None:
        st.dataframe(df)
        st.info(f"Rows x Columns: {df.shape[0]} x {df.shape[1]}")
        st.subheader("Data Description:")
        st.markdown(df.describe().T.to_markdown())
        st.subheader("Data Types:")
        st.write(df.dtypes)
    else:
        st.error(
            "An error occurred while executing the SQL command. Please cross check your command as well as tables & columns names."
        )