import sqlite3
import pandas as pd
import gradio as gr
import plotly.express as px


from objects import Team
import game_functions


sim_data_db = sqlite3.connect("SimDatabase.db", check_same_thread=False)

all_teams_df: pd.DataFrame = pd.read_sql_query(
    "SELECT name from sqlite_master WHERE type='table'",
    sim_data_db,
)

all_teams = list(all_teams_df.to_numpy().flatten())
all_teams = [team[len("Year ") :] for team in all_teams if "1972" not in team]
all_teams.sort(reverse=True)


def main(
    home_team: str, away_team: str, n_simulations: int
) -> tuple[gr.Plot, gr.Plot, gr.Dataframe, gr.Dataframe]:
    # select databse tables based on user input
    team1_data = pd.read_sql_query(f"SELECT * FROM 'Year {home_team}'", con=sim_data_db)
    team2_data = pd.read_sql_query(f"SELECT * FROM 'Year {home_team}'", con=sim_data_db)

    team1_year = home_team[:5]
    team2_year = away_team[:5]
    team1_name = home_team[5:]
    team2_name = away_team[5:]

    # generate teams with the data queried above
    Team1 = Team(team1_name, team1_year, team1_data, lineup_settings="auto")
    Team2 = Team(team2_name, team2_year, team2_data, lineup_settings="auto")

    # begin simulation
    pbp_df, _ = game_functions.simulation(n_simulations, Team1, Team2, 0)

    pie = px.pie(
        title=f"{Team1.year} {Team1.name} vs. {Team2.year} {Team2.name}",
        values=[Team1.wins / n_simulations, Team2.wins / n_simulations],
        names=[Team1.name, Team2.name],
    )

    # Prepare data
    outcome_counts = pbp_df[["PA Result", "Hitter Team"]].value_counts().reset_index()
    outcome_counts.columns = ["PA Result", "Hitting Team", "Count"]
    outcome_dist = px.bar(
        outcome_counts,
        x="PA Result",
        y="Count",
        title="Distribution of Plate Appearance Results by Team",
        color="Hitting Team",
    )

    hit_df = pd.concat(
        [
            pd.concat(
                [
                    hitter.display_rate_stats()
                    for hitter in Team1.hitters
                    if hitter.AB > 0
                ]
            ),
            pd.concat(
                [
                    hitter.display_rate_stats()
                    for hitter in Team2.hitters
                    if hitter.AB > 0
                ]
            ),
        ]
    )

    pit_df = pd.concat(
        [
            pd.concat(
                [
                    pitcher.display_rate_stats()
                    for pitcher in Team1.pitchers
                    if pitcher.BF > 0
                ]
            ),
            pd.concat(
                [
                    pitcher.display_rate_stats()
                    for pitcher in Team2.pitchers
                    if pitcher.BF > 0
                ]
            ),
        ]
    )

    return (
        gr.Plot(pie),
        gr.Plot(outcome_dist),
        gr.Dataframe(hit_df, label="Hitter Results", type="pandas"),
        gr.Dataframe(pit_df, label="Pitcher Results", type="pandas"),
    )


if __name__ == "__main__":

    disclaimer = (
        """
        <u>IMPORTANT NOTE</u>: This project was written as a way for me to learn how to code for the first time. It is messy, it is sloppy, but it runs! <br>
        I do not plan on refactoring it or making any updates to it because I want to be able to look back on this in the future to remind myself of where I started. <br>
        I am sure that there are many bugs, so just be aware.
        """
    )

    with gr.Blocks() as interface:
        gr.Markdown("# MLB Time Machine Simulator")
        gr.Markdown(disclaimer)
        with gr.Row():
            inputs = [
                gr.Dropdown(choices=all_teams, label="Team 1"),
                gr.Dropdown(choices=all_teams, label="Team 2"),
                gr.Slider(minimum=162, maximum=1_620, step=162, label="N Simulations"),
            ]
            btn = gr.Button("Run")

        with gr.Row():
            plt_outputs = [gr.Plot(), gr.Plot()]

        with gr.Row():
            df_outputs = [gr.DataFrame(), gr.DataFrame()]

        btn.click(fn=main, inputs=inputs, outputs=plt_outputs + df_outputs)
    interface.launch(show_error=True)