app.py

import streamlit as st
import pandas as pd
from neo4j import GraphDatabase
from dotenv import load_dotenv
import os
import google.generativeai as genai
import torch
import timm
from PIL import Image
import numpy as np
from torchkge.models import TransEModel, ComplExModel
from torchkge.data_structures import KnowledgeGraph
import graphviz

# ----- CONFIGURAZIONI ---------
load_dotenv()

st.set_page_config(page_title="FIFA World Cup Knowledge Graph Explorer", layout="wide")

# Configurazione del modello per gli embedding delle immagini
@st.cache_resource
def setup_image_model():
    """Configura il modello per l'estrazione degli embedding dalle immagini."""
    device = torch.device('cuda' if torch.cuda.is_available() else 'cpu')
    model = timm.create_model('efficientnet_b0', pretrained=True, num_classes=0).to(device)
    model.eval()

    data_config = timm.data.resolve_model_data_config(model)
    transform = timm.data.create_transform(**data_config, is_training=False)

    return model, transform, device

try:
    genai.configure(api_key=os.getenv("GOOGLE_API_KEY"))
    llm = genai.GenerativeModel('gemini-2.5-flash')
except Exception as e:
    st.error(f"Errore nella configurazione del modello generativo: {e}")
    st.stop()

# Connessione a Neo4j
@st.cache_resource
def get_neo4j_driver():
    uri = os.getenv("NEO4J_URI")
    user = os.getenv("NEO4J_USER")
    password = os.getenv("NEO4J_PASSWORD")

    try:
        driver = GraphDatabase.driver(uri, auth=(user, password))
        driver.verify_connectivity()
        return driver
    except Exception as e:
        st.error(f"Errore nella connessione a Neo4j: {e}")

# --- STRUCTURAL QUERY RETRIEVAL FUNCTIONS ---
@st.cache_data
def get_all_teams(_driver):
    with _driver.session(database=os.getenv("NEO4J_DATABASE")) as session:
        results = session.run("MATCH (t:Team) RETURN t.teamName AS name ORDER BY name")
        return [{"name": record["name"]} for record in results]

@st.cache_data
def get_all_positions(_driver):
    with _driver.session(database=os.getenv("NEO4J_DATABASE")) as session:
        results = session.run("MATCH (p:Player) WHERE p.position IS NOT NULL RETURN DISTINCT p.position AS position ORDER BY position")
        return [{"position": record["position"]} for record in results if record["position"]]

@st.cache_data
def get_all_stages(_driver):
    with _driver.session(database=os.getenv("NEO4J_DATABASE")) as session:
        results = session.run("MATCH (m:Match) WHERE m.stageName IS NOT NULL RETURN DISTINCT m.stageName AS stage ORDER BY stage")
        return [{"stage": record["stage"]} for record in results if record["stage"]]

# ---  SEMANTIC QUERY RETRIEVAL ---

# Funzione di utilità per recuperare liste di entità
@st.cache_data
def get_all_entities(_driver, entity_type):
    queries = {
        "Player": "MATCH (n:Player) RETURN n.playerId AS id, coalesce(n.givenName, '') + ' ' + coalesce(n.familyName, '') AS name ORDER BY name",
        "Team": "MATCH (n:Team) RETURN n.teamId AS id, n.teamName AS name ORDER BY name",
        "Match": "MATCH (n:Match) RETURN n.matchId AS id, n.matchName AS name ORDER BY name",
        "Manager": "MATCH (n:Manager) RETURN n.managerId AS id, coalesce(n.givenName, '') + ' ' + coalesce(n.familyName, '') AS name ORDER BY name"
    }

    query = queries.get(entity_type)
    if not query:
        return []
    with _driver.session(database=os.getenv("NEO4J_DATABASE")) as session:
        results = session.run(query)
        return [{"id": record["id"], "name": record["name"].strip()} for record in results if record["name"]]

# Jaccard Similarity (GDS)
@st.cache_data
def find_similar_jaccard(_driver, source_id, node_label, top_k=5):
    id_property_map = {
        "Player": "playerId",
        "Team": "teamId",
        "Match": "matchId",
        "Manager": "managerId"
    }
    id_property = id_property_map.get(node_label)
    if not id_property:
        return []

    query = f"""
    MATCH (sourceNode:{node_label} {{{id_property}: $source_id}})
    CALL gds.nodeSimilarity.stream('fifa_graph', {{ topK: $top_k, similarityMetric: 'JACCARD'}})
    YIELD node1, node2, similarity
    WHERE elementId(gds.util.asNode(node1)) = elementId(sourceNode)
    WITH gds.util.asNode(node2) AS targetNode, similarity
    WHERE elementId(targetNode) <> elementId(sourceNode)
    RETURN
        CASE
            WHEN targetNode:Player THEN targetNode.playerId
            WHEN targetNode:Team THEN targetNode.teamId
            WHEN targetNode:Manager THEN targetNode.managerId
            WHEN targetNode:Match THEN targetNode.matchId
            ELSE elementId(targetNode)
        END AS id,
        similarity
    ORDER BY similarity DESC
    LIMIT $top_k
    """

    with _driver.session(database=os.getenv("NEO4J_DATABASE")) as session:
        results = session.run(query, source_id=source_id, top_k=top_k)
        return [record.data() for record in results]

# KGE (TransE, ComplEx)
def load_kge_models_and_data():
    df = pd.read_csv('fifa_triplets.tsv', sep='\t', header=None, names=['from', 'rel', 'to'])
    kg = KnowledgeGraph(df=df)
    ent2ix = kg.ent2ix
    ix2ent = {v: k for k, v in ent2ix.items()}
    embedding_dim = 100

    transe_model = TransEModel(embedding_dim, kg.n_ent, kg.n_rel, dissimilarity_type='L2')
    transe_model.load_state_dict(torch.load('models/transe_fifa.pt', map_location=torch.device('cpu')))
    transe_model.eval()

    complex_model = ComplExModel(embedding_dim, kg.n_ent, kg.n_rel)
    complex_model.load_state_dict(torch.load('models/complex_fifa.pt', map_location=torch.device('cpu')))
    complex_model.eval()

    transe_emb = transe_model.ent_emb.weight.data.cpu().detach().numpy()
    complex_re = complex_model.re_ent_emb.weight.data.cpu().detach().numpy()
    complex_im = complex_model.im_ent_emb.weight.data.cpu().detach().numpy()
    complex_emb = np.concatenate((complex_re, complex_im), axis=1)

    return kg, ent2ix, ix2ent, transe_emb, complex_emb

def find_similar_kge(embeddings, entity_name, ent2ix, ix2ent, top_k=5):
    if entity_name not in ent2ix:
        return []
    entity_idx = ent2ix[entity_name]
    query_emb = embeddings[entity_idx]
    similarities = (embeddings @ query_emb) / (np.linalg.norm(embeddings, axis=1) * np.linalg.norm(query_emb))
    top_k_indices = np.argsort(similarities)[::-1][1:top_k+1]
    return [(ix2ent[i], float(similarities[i])) for i in top_k_indices]

@st.cache_data
def get_entity_info_from_neo4j(_driver, entity_ids):
    if not entity_ids:
        return {}

    query = """
    UNWIND $entity_ids AS entity_id
    OPTIONAL MATCH (p:Player {playerId: entity_id})
    OPTIONAL MATCH (t:Team {teamId: entity_id})
    OPTIONAL MATCH (m:Match {matchId: entity_id})
    OPTIONAL MATCH (s:Substitution {substitutionId: entity_id})
    OPTIONAL MATCH (g:Goal {goalId: entity_id})
    OPTIONAL MATCH (b:Booking {bookingId: entity_id})
    OPTIONAL MATCH (mg:Manager {managerId: entity_id})
    OPTIONAL MATCH (a:Award {awardId: entity_id})

    // Per i Player, otteniamo anche teamName
    OPTIONAL MATCH (p)-[:playsForTeam]->(player_team:Team)
    // Per le Substitution, otteniamo info sul player e il suo team
    OPTIONAL MATCH (s)-[:hasPlayerComingOn|hasPlayerGoingOff]->(sp:Player)-[:playsForTeam]->(sub_team:Team)
    // Per i Goal, otteniamo info sul player e il suo team
    OPTIONAL MATCH (g)-[:scoredBy]->(gp:Player)-[:playsForTeam]->(goal_team:Team)
    // Per i Booking, otteniamo info sul player e il suo team
    OPTIONAL MATCH (b)-[:receivedBy]->(bp:Player)-[:playsForTeam]->(book_team:Team)
    // Per gli Award, otteniamo info sul player
    OPTIONAL MATCH (a)<-[:wonAward]-(ap:Player)

    RETURN entity_id,
        CASE
            WHEN p IS NOT NULL THEN 'Player'
            WHEN t IS NOT NULL THEN 'Team'
            WHEN m IS NOT NULL THEN 'Match'
            WHEN s IS NOT NULL THEN 'Substitution'
            WHEN g IS NOT NULL THEN 'Goal'
            WHEN b IS NOT NULL THEN 'Booking'
            WHEN mg IS NOT NULL THEN 'Manager'
            WHEN a IS NOT NULL THEN 'Award'
            ELSE 'Unknown'
        END AS entity_type,
        CASE
            WHEN p IS NOT NULL THEN coalesce(p.givenName, '') + ' ' + coalesce(p.familyName, '')
            WHEN t IS NOT NULL THEN t.teamName
            WHEN m IS NOT NULL THEN coalesce(m.matchName, 'Match ' + m.matchId)
            WHEN s IS NOT NULL THEN 'Substitution for ' + coalesce(sp.givenName, '') + ' ' + coalesce(sp.familyName, '')
            WHEN g IS NOT NULL THEN 'Goal by ' + coalesce(gp.givenName, '') + ' ' + coalesce(gp.familyName, '')
            WHEN b IS NOT NULL THEN 'Booking for ' + coalesce(bp.givenName, '') + ' ' + coalesce(bp.familyName, '')
            WHEN mg IS NOT NULL THEN coalesce(mg.givenName, '') + ' ' + coalesce(mg.familyName, '')
            WHEN a IS NOT NULL THEN a.awardName
            ELSE 'Unknown'
        END AS entity_name,
        CASE
            WHEN p IS NOT NULL THEN player_team.teamName
            WHEN s IS NOT NULL THEN sub_team.teamName
            WHEN g IS NOT NULL THEN goal_team.teamName
            WHEN b IS NOT NULL THEN book_team.teamName
            ELSE null
        END AS team_name,
        CASE
            WHEN a IS NOT NULL THEN coalesce(ap.givenName, '') + ' ' + coalesce(ap.familyName, '')
            ELSE null
        END AS award_player
    """
    with _driver.session(database=os.getenv("NEO4J_DATABASE")) as session:
        result = session.run(query, entity_ids=entity_ids)
        info_dict = {}
        for record in result:
            entity_id = record["entity_id"]
            info = {
                'Type': record['entity_type'],
                'Name': record['entity_name'].strip(),
                'Team': record['team_name'],
                'Player': record['award_player']
            }
            info_dict[entity_id] = {k: v for k, v in info.items() if v is not None}
        return info_dict

@st.cache_data
def get_shortest_path(_driver, source_entity_id, target_entity_id):
    """
    Trova lo shortest path tra due entità qualsiasi e lo formatta per Graphviz
    """
    query = """
    MATCH (source), (target)
    WHERE (source.playerId = $source_id OR source.teamId = $source_id OR source.matchId = $source_id OR source.managerId = $source_id OR source.goalId = $source_id OR source.bookingId = $source_id OR source.awardId = $source_id OR source.substitutionId = $source_id OR source.stadiumId = $source_id)
    AND (target.playerId = $target_id OR target.teamId = $target_id OR target.matchId = $target_id OR target.managerId = $target_id OR target.goalId = $target_id OR target.bookingId = $target_id OR target.awardId = $target_id OR target.substitutionId = $target_id OR target.stadiumId = $target_id)
    MATCH p = shortestPath((source)-[*]-(target))
    RETURN p
    """

    with _driver.session(database=os.getenv("NEO4J_DATABASE")) as session:
        result = session.run(query, source_id=source_entity_id, target_id=target_entity_id).single()
        if not result:
            return None

        path = result["p"]
        dot_string = "digraph G {\n"
        dot_string += '  rankdir=LR;\n' # Layout orizzontale
        dot_string += '  node [shape=box, style="rounded,filled", fillcolor="#A6C9E2"];\n'
        dot_string += '  edge [fontname="Helvetica", fontsize=10];\n'

        nodes_in_path = path.nodes
        for i, node in enumerate(nodes_in_path):
            labels = list(node.labels)
            label = labels[0] if labels else 'Node'

            # Gestistiamo diversi tipi di entità per il nome
            if label == 'Player':
                name = f"{node.get('givenName', '')} {node.get('familyName', '')}".strip()
            elif label == 'Team':
                name = node.get('teamName', '')
            elif label == 'Match':
                name = node.get('matchName', f"Match {node.get('matchId', '')}")
            elif label == 'Manager':
                name = f"{node.get('givenName', '')} {node.get('familyName', '')}".strip()
            elif label == 'Stadium':
                name = node.get('stadiumName', '')
            elif label == 'Goal':
                name = f"Goal (min {node.get('minuteRegulation', '')})"
            elif label == 'Booking':
                name = f"Booking (min {node.get('minuteRegulation', '')})"
            elif label == 'Award':
                name = node.get('awardName', '')
            elif label == 'Substitution':
                name = f"Substitution (min {node.get('minuteRegulation', '')})"
            else:
                name = 'Unknown'

            dot_string += f'  node{i} [label="{label}\\n({name})"];\n'

        for i, rel in enumerate(path.relationships):
            start_node_idx = nodes_in_path.index(rel.start_node)
            end_node_idx = nodes_in_path.index(rel.end_node)
            dot_string += f'  node{start_node_idx} -> node{end_node_idx} [label="{rel.type}"];\n'

        dot_string += "}"
        return dot_string

# --- IMAGE SIMILARITY ---
def find_similar_players_by_image(driver, query_embedding, top_k=5):
    """
    Trova giocatori simili basati sull'embedding dell'immagine.
    """
    query = """
    MATCH (target:Player)
    WHERE target.imageEmbedding IS NOT NULL
    WITH target, gds.similarity.cosine(target.imageEmbedding, $query_embedding) AS similarity
    RETURN target.playerId AS id, target.givenName + ' ' + target.familyName AS name, similarity
    ORDER BY similarity DESC
    LIMIT $top_k
    """

    with driver.session(database=os.getenv("NEO4J_DATABASE")) as session:
        results = session.run(query, query_embedding=query_embedding, top_k=top_k)
        return [record.data() for record in results]

@st.cache_data
def get_player_image_folder_path(_driver, player_id):
    """
    Recupera la cartella delle immagini di un giocatore dal database Neo4j.
    Restituisce il percorso completo alla cartella delle immagini del giocatore.
    """
    query = """
    MATCH (p:Player {playerId: $player_id})
    RETURN p.imageFolder AS image_folder_name
    """

    with _driver.session(database=os.getenv("NEO4J_DATABASE")) as session:
        result = session.run(query, player_id=player_id).single()

    if not result:
        return None

    image_folder_name = result["image_folder_name"]

    if not image_folder_name:
        return None

    normalized_path = image_folder_name.replace('\\', os.sep).strip()

    if os.path.exists(normalized_path) and os.path.isdir(normalized_path):
        return normalized_path

    return None

def extract_image_embedding(uploaded_file):
    """
    Estrae l'embedding da un'immagine caricata dall'utente.
    """
    model, transform, device = setup_image_model()

    # Converte il file uploadato in un'immagine PIL
    img = Image.open(uploaded_file).convert('RGB')

    # Applica le trasformazioni
    img_tensor = transform(img).unsqueeze(0).to(device)

    # Estrae l'embedding
    with torch.no_grad():
        embedding = model(img_tensor).squeeze().cpu().numpy().tolist()

    return embedding


# --- RAG WITH LLM ---
def generate_cypher_query(question):

    prompt = f"""
    You are an expert of Neo4j Cypher query language.
    Your task is to generate a Cypher query based on the user's question and the provided database schema.
    Return only the Cypher query without any additional text or explanation.
    If the question is not answerable with the given schema, return "UNANSWERABLE".

    Schema:
    ---

    ENTITY and Properties:
        Tournament = (tournamentId, tournamentName, year, startDate, endDate, countTeams, hadGroupStage ,hadSecondGroupStage, hadRoundOf16 ,hadQuarterFinals ,hadSemiFinals, hadThirdPlaceMatch ,hadFinal)
        Team = (teamId, teamName, teamCode, finalPosition, gamesPlayed, wins, draws, losses, goalsFor, goalsAgainst, goalDifference, points, federationName, regionName, teamWikipediaLink, federationWikipediaLink)
        Player = (playerId, familyName, givenName, birthDate, position, positionCode, playerWikipediaLink, club, appearances, goalScored, assistProvided, brandSponsor, dribblesPer90, interceptionsPer90, tacklesPer90, duelsWonPer90, savePercentage, cleanSheets)
        Match = (matchId, matchName, matchDate, stageName, homeTeamScore, awayTeamScore, hadPenaltyShootout, homeTeamPenaltyScore, awayTeamPenaltyScore, homeTeamWin, awayTeamWin)
        Manager = (managerId, familyName, givenName, managerWikipediaLink)
        Stadium = (stadiumId, stadiumName, stadiumCapacity, cityName, countryName, stadiumWikipediaLink, cityWikipediaLink)
        Goal = (goalId, minuteRegulation, isOwnGoal, isPenalty)
        Booking = (bookingId, minuteRegulation, isFirstYellowCard, isSecondYellowCard, isRedCard)
        Award = (awardId, awardName, awardDescription, awardYearIntroduced)
        Substitution = (substitutionID, minuteRegulation)

    RELATIONSHIP:
        (Player)-[playsForTeam]->(Team)
        (Manager)-[coachesTeam]->(Team)
        (Player)-[participatedInMatch]->(Match)
        (Team)-[wasHomeTeamIn]->(Match)
        (Team)-[wasAwayTeamIn]->(Match)
        (Match)-[playedAt]->(Stadium)
        (Goal)-[scoredIn]->(Match)
        (Goal)-[scoredBy]->(Player)
        (Booking)-[givenIn]->(Match)
        (Booking)-[receivedBy]->(Player)
        (Player)-[wonAward]->(Award)
        (Substitution)-[occurredInMatch]->(Match)
        (Substitution)-[hasPlayerComingOn]->(Player)
        (Substitution)-[hasPlayerGoingOff]->(Player)
        (Tournament)-[hasParticipant]->(Team)
        (Tournament)-[includesMatch]->(Match)
        (Tournament)-[hasWinner]->(Team)
    ---

    USER QUESTION: "{question}"

    CYTHER QUERY:
    """
    try:
        response = llm.generate_content(prompt)
        cypher_query = response.text.strip().replace("```cypher", "").replace("```", "").strip()
        if "UNANSWERABLE" in cypher_query:
            return None
        return cypher_query
    except Exception as e:
        st.error(f"Errore nella generazione della query Cypher: {e}")
        return None

def run_cypher_query(_driver, query, parameters=None):
    with _driver.session(database=os.getenv("NEO4J_DATABASE")) as session:
        results = session.run(query, parameters or {})
        return [record.data() for record in results]

def generate_natural_language_answer(question, data):
    prompt = f"""
    Answer the user's question clearly and concisely
    Based **exclusively** on data provided by the Knowledge Graph. Do not add information not present in the data.

    **EXAMPLE 1**
    User question: "How many goals has Mbappé scored?"
    Data from Knowledge Graph: [{{"goalScored": 8}}].
    Final Answer: During the FIFA World Cup 2022, Kylian Mbappé scored 8 goals.
    ---
    **EXAMPLE 2**
    User Question: "In which stadium was the final played?"
    Data from Knowledge Graph: [{{"stadiumName": "Lusail Stadium"}}].
    Final Answer: The final of the FIFA World Cup 2022 was played at Lusail Stadium.

    ---
    ACTUAL QUESTION AND DATA:
    USER QUESTION: "{question}"

    DATA FROM KNOWLEDGE GRAPH:
    ---
    {data}
    ---

    FINAL ANSWER:
    """

    try:
        response = llm.generate_content(prompt)
        return response.text.strip()
    except Exception as e:
        st.error(f"Errore nella generazione della risposta in linguaggio naturale: {e}")
        return "Sorry, I couldn't generate an answer at this time."

# ----- STREAMLIT APP ---------
st.sidebar.title("Sections")
app_mode = st.sidebar.radio("Go to", ["Home", "Structural Query Retrieval", "Semantic Similarity Search", "LLM-driven Graph Querying", "Player Similarity by Image"])

if app_mode == "Home":
    st.title("FIFA World Cup 2022 Knowledge Graph Explorer")
    st.markdown("""
    This application provides an advanced interface for exploring the FIFA World Cup 2022 dataset, modeled as a comprehensive Knowledge Graph.
    """)

    st.info("Navigate through the sections using the sidebar to begin your analysis.")

elif app_mode == "Structural Query Retrieval":
    st.title("Structural Query Retrieval")

    driver = get_neo4j_driver()
    if driver is None:
        st.error("Impossibile connettersi a Neo4j. Controlla le tue credenziali e la connessione.")
        st.stop()

    question_options = [
        "In which stadium was a specific stage of the tournament played?",
        "Shows goal minutes for players of a certain position and team",
        "In which cities did a specific team play?",
        "Who scored in a match between two teams?",
        "Who is the manager of the team that won the tournament?",
        "Who are the players who have received a card but have never scored a goal?",
        "List all managers and, if their players have won awards, show which ones.",
        "Who are the players that received a yellow card and were then substituted (leaving the field) in the same match?",
        "For each team, it lists all the stadiums in which it played, without duplicates.",
        "Identifies 'super-sub': players who entered a match as substitutes and scored a goal in that same match."
    ]

    selected_question = st.selectbox("Select a question to ask the Knowledge Graph:", question_options)

    # QUERY 1: Stadio per una certa fase del torneo
    if selected_question == question_options[0]:
        all_stages = get_all_stages(driver)
        selected_stage = st.selectbox(
            "Select the stage of the tournament:",
            options=all_stages,
            format_func=lambda x: x["stage"]
        )

        if st.button("Ask the Knowledge Graph"):
            query = """
            MATCH (m:Match)-[:playedAt]->(s:Stadium)
            WHERE m.stageName = $stage_name
            RETURN DISTINCT s.stadiumName AS Stadium, m.matchName As Match
            """

            results = run_cypher_query(driver, query, {"stage_name": selected_stage["stage"]})

            if results:
                st.code(query, language='cypher')
                st.subheader(f"Stadiums for {selected_stage['stage']} stage:")
                results_df = pd.DataFrame(results)
                st.dataframe(results_df, width='stretch')
            else:
                st.warning("No stadiums found for the selected stage.")

    # QUERY 2: Goal per ruolo e squadra
    elif selected_question == question_options[1]:
        all_teams = get_all_teams(driver)
        all_positions = get_all_positions(driver)

        col1, col2 = st.columns(2)
        with col1:
            selected_team = st.selectbox(
                "Select a Team:",
                options=all_teams,
                format_func=lambda x: x["name"]
            )
        with col2:
            selected_position = st.selectbox(
                "Select a Player Position:",
                options=all_positions,
                format_func=lambda x: x["position"]
            )

        if st.button("Ask the Knowledge Graph"):
            query = """
            MATCH (p:Player)-[:playsForTeam]->(t:Team)
            WHERE t.teamName = $team_name AND p.position = $position
            OPTIONAL MATCH (p)<-[:scoredBy]-(g:Goal)
            RETURN p.givenName + ' ' + p.familyName AS Player, collect(g.minuteRegulation) AS GoalMinutes
            ORDER BY Player
            """

            results = run_cypher_query(driver, query, {"team_name": selected_team["name"], "position": selected_position["position"]})

            if results:
                st.code(query, language='cypher')
                st.subheader(f"Goal minutes for {selected_position['position']} players from {selected_team['name']}:")

                # Trasforma i risultati per gestire i valori nulli
                processed_results = []
                for result in results:
                    processed_result = result.copy()
                    # Se GoalMinutes è una lista vuota o None, sostituisci con "No goals"
                    if not processed_result.get('GoalMinutes') or processed_result.get('GoalMinutes') == []:
                        processed_result['GoalMinutes'] = "No goals"
                    else:
                        # Converti la lista di minuti in una stringa leggibile
                        minutes = processed_result['GoalMinutes']
                        if isinstance(minutes, list) and all(m is not None for m in minutes):
                            processed_result['GoalMinutes'] = ", ".join(map(str, sorted(minutes)))
                        else:
                            processed_result['GoalMinutes'] = "No goals"
                    processed_results.append(processed_result)

                results_df = pd.DataFrame(processed_results)
                st.dataframe(results_df, width='stretch')
            else:
                st.warning("No players found for the selected team and position.")

    # QUERY 3: In quali città ha giocato una certa squadra?
    elif selected_question == question_options[2]:
        all_teams = get_all_teams(driver)

        selected_team = st.selectbox(
            "Select a Team:",
            options=all_teams,
            format_func=lambda x: x["name"]
        )

        if st.button("Ask the Knowledge Graph"):
            query = """
            MATCH (t:Team)-[:wasHomeTeamIn|:wasAwayTeamIn]->(m:Match)-[:playedAt]->(s:Stadium)
            WHERE t.teamName = $team_name
            RETURN DISTINCT s.cityName AS City, s.stadiumName AS Stadium
            ORDER BY City
            """

            results = run_cypher_query(driver, query, {"team_name": selected_team["name"]})

            if results:
                st.code(query, language='cypher')
                st.subheader(f"Cities where {selected_team['name']} played:")
                results_df = pd.DataFrame(results)
                st.dataframe(results_df, width='stretch')
            else:
                st.warning("No cities found for the selected team.")

    # QUERY 4: Marcatori in una partita tra due squadre
    elif selected_question == question_options[3]:
        all_teams = get_all_teams(driver)

        col1, col2 = st.columns(2)
        with col1:
            selected_team1 = st.selectbox(
                "Select the First Team:",
                options=all_teams,
                format_func=lambda x: x["name"],
                key="team1"
            )
        with col2:
            selected_team2 = st.selectbox(
                "Select the Second Team:",
                options=all_teams,
                format_func=lambda x: x["name"],
                key="team2"
            )

        if st.button("Ask the Knowledge Graph"):
            query = """
            MATCH (t1:Team {teamName: $team1_name})
            MATCH (t2:Team {teamName: $team2_name})
            MATCH (t1)-[:wasHomeTeamIn|:wasAwayTeamIn]->(m:Match)<-[:wasHomeTeamIn|:wasAwayTeamIn]-(t2)
            OPTIONAL MATCH (m)<-[:scoredIn]-(g:Goal)-[:scoredBy]->(p:Player)
            RETURN p.givenName + ' ' + p.familyName AS Player, g.minuteRegulation AS GoalMinute, m.matchName AS Match
            ORDER BY GoalMinute
            """

            results = run_cypher_query(driver, query, {"team1_name": selected_team1["name"], "team2_name": selected_team2["name"]})
            if results:
                st.code(query, language='cypher')
                st.subheader(f"Goal scorers in the match between {selected_team1['name']} and {selected_team2['name']}:")
                results_df = pd.DataFrame(results)
                st.dataframe(results_df, width='stretch')
            else:
                st.warning("No goals found in the match between the selected teams.")

    # QUERY 5: Manager della squadra vincitrice del torneo
    elif selected_question == question_options[4]:
        if st.button("Ask the Knowledge Graph"):
            query = """
            MATCH (manager:Manager)-[:coachesTeam]->(team:Team)<-[:hasWinner]-(tournament:Tournament)
            RETURN manager.givenName + ' ' + manager.familyName AS Manager, team.teamName AS Team
            """

            results = run_cypher_query(driver, query)

            if results:
                st.code(query, language='cypher')
                st.subheader("Manager of the team that won the tournament:")
                results_df = pd.DataFrame(results)
                st.dataframe(results_df, width='stretch')
            else:
                st.warning("No manager found for the winning team.")

    # QUERY 6: Giocatori che hanno ricevuto una cartellino ma non hanno mai segnato
    elif selected_question == question_options[5]:
        if st.button("Ask the Knowledge Graph"):
            query = """
            MATCH (p:Player)<-[:receivedBy]-(b:Booking)
            WHERE NOT EXISTS {(p)<-[:scoredBy]-(:Goal)}
            WITH count(b) AS bookingCount, p
            RETURN DISTINCT p.givenName + ' ' + p.familyName AS Player, bookingCount
            ORDER BY Player
            """

            results = run_cypher_query(driver, query)

            if results:
                st.code(query, language='cypher')
                st.subheader("Players who received a card but never scored a goal:")
                results_df = pd.DataFrame(results)
                st.dataframe(results_df, width='stretch')
            else:
                st.warning("No players found who received a card but never scored.")

    # QUERY 7: Manager e premi vinti dai loro giocatori
    elif selected_question == question_options[6]:
        if st.button("Ask the Knowledge Graph"):
            query = """
            MATCH (m:Manager)-[:coachesTeam]->(t:Team)<-[:playsForTeam]-(p:Player)
            OPTIONAL MATCH (p)-[:wonAward]->(a:Award)
            RETURN m.givenName + ' ' + m.familyName AS Manager, t.teamName AS Team, collect(a.awardName) AS Awards
            """

            results = run_cypher_query(driver, query)

            if results:
                st.code(query, language='cypher')
                st.subheader("Managers and awards won by their players:")

                # Trasforma i risultati per gestire i valori nulli
                processed_results = []
                for result in results:
                    processed_result = result.copy()
                    # Se Awards è una lista vuota o None, sostituisci con "No awards"
                    if not processed_result.get('Awards') or processed_result.get('Awards') == []:
                        processed_result['Awards'] = "No awards"
                    else:
                        # Converti la lista di premi in una stringa leggibile
                        awards = processed_result['Awards']
                        if isinstance(awards, list) and all(a is not None for a in awards):
                            processed_result['Awards'] = ", ".join(awards)
                        else:
                            processed_result['Awards'] = "No awards"
                    processed_results.append(processed_result)

                results_df = pd.DataFrame(processed_results)
                st.dataframe(results_df, width='stretch')
            else:
                st.warning("No managers found.")

    # QUERY 8: Giocatori che hanno ricevuto un cartellino giallo e sono stati sostituiti nella stessa partita
    elif selected_question == question_options[7]:
        if st.button("Ask the Knowledge Graph"):
            query = """
            MATCH (player:Player)<-[:receivedBy]-(booking:Booking)-[:givenIn]->(match:Match)
            WHERE booking.isFirstYellowCard = true
            MATCH (player)<-[:hasPlayerGoingOff]-(substitution:Substitution)-[:occurredInMatch]->(match)
            RETURN DISTINCT player.givenName + ' ' + player.familyName AS Player, match.matchName AS Match
            ORDER BY Player
            """

            results = run_cypher_query(driver, query)

            if results:
                st.code(query, language='cypher')
                st.subheader("Players who received a yellow card and were then substituted in the same match:")
                results_df = pd.DataFrame(results)
                st.dataframe(results_df, width='stretch')
            else:
                st.warning("No players found who received a yellow card and were substituted in the same match.")

    # QUERY 9: Per ogni squadra, elenca tutti gli stadi in cui ha giocato, senza duplicati
    elif selected_question == question_options[8]:
        if st.button("Ask the Knowledge Graph"):
            query = """
            MATCH (team:Team)-[:wasHomeTeamIn|:wasAwayTeamIn]->(match:Match)-[:playedAt]->(stadium:Stadium)
            RETURN team.teamName AS Team, collect(DISTINCT stadium.stadiumName) AS Stadiums
            ORDER BY Team
            """

            results = run_cypher_query(driver, query)

            if results:
                st.code(query, language='cypher')
                st.subheader("Stadiums where each team played:")

                # Trasforma i risultati per gestire i valori nulli
                processed_results = []
                for result in results:
                    processed_result = result.copy()
                    # Se Stadiums è una lista vuota o None, sostituisci con "No stadiums"
                    if not processed_result.get('Stadiums') or processed_result.get('Stadiums') == []:
                        processed_result['Stadiums'] = "No stadiums"
                    else:
                        # Converti la lista di stadi in una stringa leggibile
                        stadiums = processed_result['Stadiums']
                        if isinstance(stadiums, list) and all(s is not None for s in stadiums):
                            processed_result['Stadiums'] = ", ".join(stadiums)
                        else:
                            processed_result['Stadiums'] = "No stadiums"
                    processed_results.append(processed_result)

                results_df = pd.DataFrame(processed_results)
                st.dataframe(results_df, width='stretch')
            else:
                st.warning("No teams found.")

    # QUERY 10: Identifica i 'super-sub': giocatori che sono entrati in una partita come sostituti e hanno segnato un gol nella stessa partita
    elif selected_question == question_options[9]:
        if st.button("Ask the Knowledge Graph"):
            query = """
            MATCH (sub:Substitution)-[r1:hasPlayerComingOn]->(player:Player),
            (sub)-[r2:occurredInMatch]->(match:Match),
            (goal:Goal)-[r3:scoredBy]->(player),
            (goal)-[r4:scoredIn]->(match)
            RETURN DISTINCT player.givenName + ' ' + player.familyName AS Player, match.matchName AS Match
            """

            results = run_cypher_query(driver, query)

            if results:
                st.code(query, language='cypher')
                st.subheader("Super-sub players who scored after coming on as substitutes:")
                results_df = pd.DataFrame(results)
                st.dataframe(results_df, width='stretch')
            else:
                st.warning("No super-sub players found.")

elif app_mode == "Semantic Similarity Search":
    st.title("Semantic Similarity Search")

    driver = get_neo4j_driver()
    if driver is None:
        st.error("Impossibile connettersi a Neo4j. Controlla le tue credenziali e la connessione.")
        st.stop()

    col1, col2 = st.columns(2)
    with col1:
        entity_type = st.selectbox(
            "1. Select Entity Type:",
            ["Player", "Team", "Match", "Manager"]
        )

    with col2:
        algorithm = st.selectbox(
            "2. Select Similarity Algorithm:",
            ["Jaccard", "KGE - TransE", "KGE - ComplEx"]
        )

    entity_list = get_all_entities(driver, entity_type)
    if not entity_list:
        st.warning(f"No entities found for type {entity_type}.")
        st.stop()

    selected_entity = st.selectbox(
        "2. Select an Entity:",
        options=entity_list,
        format_func=lambda x: x["name"]
    )

    top_k = st.slider("3. Select number of similar entities to retrieve (k):", min_value=1, max_value=15, value=5)

    if st.button("Find Similar Entities"):
        st.markdown("---")
        st.subheader(f"Results for '{selected_entity['name']}' ({entity_type}) using {algorithm}:")

        results_data = []

        if algorithm == "Jaccard":
            with st.spinner("Calculating Jaccard similarities..."):
                similar_entities = find_similar_jaccard(driver, selected_entity["id"], entity_type, top_k=top_k)
                if similar_entities:
                    all_ids = [res['id'] for res in similar_entities]
                    entity_info = get_entity_info_from_neo4j(driver, all_ids)

                    for res in similar_entities:
                        entity_id = res['id']
                        info = entity_info.get(entity_id, {})
                        results_data.append({
                            "ID": entity_id,
                            "Type": info.get('Type', 'Unknown'),
                            "Name": info.get('Name', 'N/A'),
                            "Similarity": f"{res['similarity']:.4f}"
                        })
                else:
                    st.warning("No similar entities found.")
        else:
            try:
                with st.spinner("Loading KGE models and data..."):
                    kg, ent2ix, ix2ent, transe_emb, complex_emb = load_kge_models_and_data()
            except FileNotFoundError as e:
                st.error(f"Error loading files: {e}. Make sure 'fifa_triplets.tsv' and model files are present.")
                st.stop()

            if selected_entity['id'] not in ent2ix:
                st.error(f"Entity '{selected_entity['name']}' (ID: {selected_entity['id']}) not found in the vocabulary of the KGE models. It might not have any relationships in the training data.")
                st.stop()

            with st.spinner(f"Finding similar entities with {algorithm}..."):
                embeddings = transe_emb if algorithm == 'KGE - TransE' else complex_emb
                kge_results = find_similar_kge(embeddings, selected_entity['id'], ent2ix, ix2ent, top_k)

                if kge_results:
                    all_ids = [res[0] for res in kge_results]
                    entity_info = get_entity_info_from_neo4j(driver, all_ids)

                    for entity_id, similarity in kge_results:
                        info = entity_info.get(entity_id, {})
                        results_data.append({
                            "ID": entity_id,
                            "Type": info.get('Type', 'Unknown'),
                            "Name": info.get('Name', 'N/A'),
                            "Similarity": f"{similarity:.4f}"
                        })
                else:
                    st.warning(f"No similar entities found with {algorithm}.")

        if results_data:
            df = pd.DataFrame(results_data)
            st.dataframe(df, width='stretch')

            # Mostra lo shortest path per la prima entità simile
            if results_data:
                first_similar_entity_id = results_data[0]["ID"]
                first_similar_entity_name = results_data[0]["Name"]

                st.markdown("---")
                st.subheader(f"Shortest Path between '{selected_entity['name']}' and '{first_similar_entity_name}'")

                with st.spinner("Computing shortest path..."):
                    dot_graph = get_shortest_path(driver, selected_entity["id"], first_similar_entity_id)

                    if dot_graph:
                        try:
                            st.graphviz_chart(dot_graph)
                        except ImportError:
                            st.error("Graphviz package not available. Install it with: pip install graphviz")
                            st.code(dot_graph, language='dot')
                    else:
                        st.warning(f"No path found between '{selected_entity['name']}' and '{first_similar_entity_name}'")
        else:
            st.warning(f"No similar entities found with {algorithm}.")


elif app_mode == "LLM-driven Graph Querying":
    st.title("LLM-driven Graph Querying")
    user_question = st.text_area("Enter your question about the FIFA World Cup 2022 dataset:")

    if st.button("Get Answer"):
        if user_question.strip() == "":
            st.warning("Please enter a question.")
        else:
            driver = get_neo4j_driver()

            with st.spinner("Generating Cypher query..."):

                st.subheader("Text-to-Cypher Generation")
                cypher_query = generate_cypher_query(user_question)
                if cypher_query:
                    st.code(cypher_query, language='cypher')

                    st.subheader("Data Retrieved from Knowledge Graph")
                    kg_data = run_cypher_query(driver, cypher_query)

                    if kg_data:
                        st.json(kg_data)

                        st.subheader("Natural Language Answer")
                        answer = generate_natural_language_answer(user_question, kg_data)
                        st.markdown(f"**Answer:** {answer}")
                    else:
                        st.warning("No data retrieved from the Knowledge Graph for the generated query.")
                else:
                    st.warning("The question is unanswerable with the given schema or an error occurred during query generation.")

elif app_mode == "Player Similarity by Image":
    st.title("Similarity by Image (Query-by-Example)")

    uploaded_file = st.file_uploader("Upload a player's image to find similar players", type=["png", "jpg", "jpeg"])

    if uploaded_file is not None:
        col1, col2 = st.columns([1, 3])
        with col1:
            st.image(uploaded_file, caption='Uploaded Image')

        if st.button("Find Similar Players"):
            with st.spinner("Extracting image embedding and finding similar players..."):

                query_embedding = extract_image_embedding(uploaded_file)

                driver = get_neo4j_driver()
                if driver is None:
                    st.error("Impossibile connettersi a Neo4j. Controlla le tue credenziali e la connessione.")
                    st.stop()

                similar_players = find_similar_players_by_image(driver, query_embedding)

                st.subheader("Top 5 Similar Players Found:")

                if similar_players:

                    cols = st.columns(5)
                    for idx, player in enumerate(similar_players):
                        with cols[idx]:
                            st.write(f"**{player['name']}**")
                            st.write(f"Sim: {player['similarity']:.3f}")
                            player_folder_path = get_player_image_folder_path(driver, player['id'])

                            if player_folder_path and os.path.isdir(player_folder_path):
                                image_files = [f for f in os.listdir(player_folder_path) if f.lower().endswith(('.png', '.jpg', '.jpeg'))]

                                if image_files:
                                    image_path = os.path.join(player_folder_path, image_files[0])

                                    try:
                                        if os.path.exists(image_path):
                                            img = Image.open(image_path)
                                            st.image(img, use_container_width=True)
                                        else:
                                            st.error("Image file does not exist.")
                                    except Exception as e:
                                        st.error(f"Error loading image: {e}")
                                else:
                                    st.write("Folder found, but no images inside.")
                            else:
                                st.write("No image folder found.")
                else:
                    st.write("No similar players found.")