app.py

import gradio as gr
import requests
import json
import os

from pyvis.network import Network
import networkx as nx

from openai import OpenAI

# Env Vars
METABASE_USERNAME = os.getenv('METABASE_USERNAME')
METABASE_PASSWORD = os.getenv('METABASE_PASSWORD')
OPENAI_API_KEY = os.getenv('OPENAI_API_KEY')

# # get by local_config.json file
# with open("local_config.json") as f:
#     config = json.load(f)
#     METABASE_USERNAME = config['METABASE_USERNAME']
#     METABASE_PASSWORD = config['METABASE_PASSWORD']
#     OPENAI_API_KEY = config['OPENAI_API_KEY']

# OpenAI API
OPENAI_API_CLIENT = OpenAI(api_key=OPENAI_API_KEY)

# Original elements
elements = [
    {'data': {'id': 'jnc-4-05-1-1', 'label': '真分數、假分數與帶分數的命名及說、讀、聽、寫、做'}},
    {'data': {'id': 'jnc-4-05-2-1', 'label': '假分數與帶分數的互換'}},
    {'data': {'id': 'jnc-4-05-3-1', 'label': '同分母分數的大小比較'}},
    {'data': {'id': 'jnc-4-05-3-2', 'label': '同分母分數的加減'}},
    {'data': {'id': 'jnc-4-05-3-3', 'label': '分數的整數倍'}},
    {'data': {'id': 'jnc-4-06-1-1', 'label': '認識等值分數'}},
    {'data': {'id': 'jnc-4-06-1-2', 'label': '找出等值分數'}},
    {'data': {'id': 'jnc-4-06-2-1', 'label': '簡單異分母分數的比較'}},
    {'data': {'id': 'jnc-4-06-2-2', 'label': '簡單異分母分數的加減'}},
    {'data': {'id': 'jnc-4-06-3-1', 'label': '分數與一位小數的互換'}},
    {'data': {'id': 'jnc-4-06-3-2', 'label': '分數與二位小數的互換'}},
    {'data': {'id': 'jnc-4-08-2-1', 'label': '認識分數數線'}},
    {'data': {'id': 'jnc-4-08-2-2', 'label': '數線的整數、分數、小數'}},
    {'data': {'source': 'jnc-4-05-1-1', 'target': 'jnc-4-05-2-1'}},
    {'data': {'source': 'jnc-4-05-1-1', 'target': 'jnc-4-05-3-1'}},
    {'data': {'source': 'jnc-4-05-2-1', 'target': 'jnc-4-05-3-1'}},
    {'data': {'source': 'jnc-4-05-1-1', 'target': 'jnc-4-05-3-2'}},
    {'data': {'source': 'jnc-4-05-2-1', 'target': 'jnc-4-05-3-2'}},
    {'data': {'source': 'jnc-4-05-2-1', 'target': 'jnc-4-05-3-3'}},
    {'data': {'source': 'jnc-4-05-3-2', 'target': 'jnc-4-05-3-3'}},
    {'data': {'source': 'jnc-4-05-1-1', 'target': 'jnc-4-06-1-1'}},
    {'data': {'source': 'jnc-4-06-1-1', 'target': 'jnc-4-06-1-2'}},
    {'data': {'source': 'jnc-4-06-1-1', 'target': 'jnc-4-06-2-1'}},
    {'data': {'source': 'jnc-4-06-1-2', 'target': 'jnc-4-06-2-1'}},
    {'data': {'source': 'jnc-4-06-1-1', 'target': 'jnc-4-06-2-2'}},
    {'data': {'source': 'jnc-4-06-1-2', 'target': 'jnc-4-06-2-2'}},
    {'data': {'source': 'jnc-4-06-3-1', 'target': 'jnc-4-06-3-2'}},
    {'data': {'source': 'jnc-4-05-1-1', 'target': 'jnc-4-08-2-1'}},
    {'data': {'source': 'jnc-4-08-2-1', 'target': 'jnc-4-08-2-2'}},
    {'data': {'source': 'jnc-4-05-1-1', 'target': 'jnc-4-06-3-1'}}
]


# Create a NetworkX graph
nx_graph = nx.DiGraph()

# Add nodes and edges
for element in elements:
    if 'source' not in element['data']:  # It's a node
        nx_graph.add_node(element['data']['id'], title=element['data']['label'])
    else:  # It's an edge
        nx_graph.add_edge(element['data']['source'], element['data']['target'])

def update_node_colors(result_data):
    color_mapping = {'1': 'green', '0': 'red', 'default': 'orange'}
    for node in nx_graph.nodes:
        if node in result_data:
            score = str(result_data[node])
            color = color_mapping.get(score, color_mapping['default'])
        else:
            color = color_mapping['default']
        nx_graph.nodes[node]['color'] = color

# Function to generate the graph in hierarchical layout
def needs_analysis(topic_result=None, exercise_quiz_result=None):
    if topic_result:
        update_node_colors(topic_result)
    nt = Network(directed=True)
    nt.from_nx(nx_graph)
    nt.repulsion(node_distance=120, central_gravity=0.0, spring_length=100, spring_strength=0.05, damping=0.09)
    nt.set_options("""
    {
        "layout": {
            "hierarchical": {
                "enabled": false,
                "levelSeparation": 150,
                "nodeSpacing": 200,
                "treeSpacing": 500,
                "blockShifting": true,
                "edgeMinimization": true,
                "parentCentralization": true,
                "direction": "UD",
                "sortMethod": "directed"
            }
        }
    }
    """)
    map_html = nt.generate_html()
    # Replace single quotes with double quotes in HTML
    map_html = map_html.replace("'", "\"")
    html = f"""<iframe style="width: 100%; height: 600px;margin:0 auto" name="result" allow="midi; geolocation; microphone; camera;
    display-capture; encrypted-media;" sandbox="allow-modals allow-forms
    allow-scripts allow-same-origin allow-popups
    allow-top-navigation-by-user-activation allow-downloads" allowfullscreen=""
    allowpaymentrequest="" frameborder="0" srcdoc='{map_html}'></iframe>"""

    topic_table = generate_html_table(topic_result, exercise_quiz_result)
    html += f"<h2>Topic Table</h2>{topic_table}"

    return html


def query_metabase_topic(class_code, topic_card_id, user_id, username=METABASE_USERNAME, password=METABASE_PASSWORD):
    try:
        # 获取会话令牌
        session_response = requests.post(
            'https://metabase.cloud.junyiacademy.org/api/session',
            headers={'Content-Type': 'application/json'},
            json={'username': username, 'password': password}
        )
        session_response.raise_for_status()
        session_token = session_response.json()['id']
        print(f"Session token: {session_token}")

        # 打印请求信息
        request_payload = {
            "parameters": [
                {
                    "type": "category",
                    "target": ["variable", ["template-tag", "class_code"]],
                    "value": class_code
                },
                {
                    "type": "category",
                    "target": ["variable", ["template-tag", "user_id"]],
                    "value": user_id
                }
            ]
        }
        print(f"Request payload: {json.dumps(request_payload, indent=2)}")

        # 使用提供的 card_id 查询 Metabase 卡片
        query_response = requests.post(
            f'https://metabase.cloud.junyiacademy.org/api/card/{topic_card_id}/query/json',
            headers={
                'Content-Type': 'application/json',
                'X-Metabase-Session': session_token
            },
            json=request_payload
        )
    
        query_response.raise_for_status()

        # filter class_code and user_id
        query_response = query_response.json()
        query_response = [item for item in query_response if item['class_code'] == class_code and item['user_id'] == user_id]

        return query_response[0] if query_response else {}
    except requests.RequestException as e:
        print(f"Failed to query Metabase card: {str(e)}")
        return {"error": f"Failed to query Metabase card: {str(e)}"}
    except Exception as e:
        print(f"Error: {str(e)}")
        return {"error": str(e)}

def query_metabase_exercise_quiz(class_code, exercise_card_id, user_id, username=METABASE_USERNAME, password=METABASE_PASSWORD):
    try:
        # 获取会话令牌
        session_response = requests.post(
            'https://metabase.cloud.junyiacademy.org/api/session',
            headers={'Content-Type': 'application/json'},
            json={'username': username, 'password': password}
        )
        session_response.raise_for_status()
        session_token = session_response.json()['id']
        print(f"Session token: {session_token}")

        # 打印请求信息
        request_payload = {
            "parameters": [
                {
                    "type": "category",
                    "target": ["variable", ["template-tag", "class_code"]],
                    "value": class_code
                },
                {
                    "type": "category",
                    "target": ["variable", ["template-tag", "user_id"]],
                    "value": user_id
                }
            ]
        }
        print(f"Request payload: {json.dumps(request_payload, indent=2)}")

        # 使用提供的 card_id 查询 Metabase 卡片
        query_response = requests.post(
            f'https://metabase.cloud.junyiacademy.org/api/card/{exercise_card_id}/query/json',
            headers={
                'Content-Type': 'application/json',
                'X-Metabase-Session': session_token
            },
            json=request_payload
        )
    
        query_response.raise_for_status()

        # filter class_code and user_id
        query_response = query_response.json()
        print(f"query_response: {query_response}")
        query_response = [item for item in query_response if item['class_code'] == class_code and item['user_id'] == user_id]

        return query_response
    except requests.RequestException as e:
        print(f"Failed to query Metabase card: {str(e)}")
        return {"error": f"Failed to query Metabase card: {str(e)}"}
    except Exception as e:
        print(f"Error: {str(e)}")
        return {"error": str(e)}

def generate_html_table(result_data, exercise_quiz_result):
    color_mapping = {'1': 'green', '0': 'red', 'default': 'orange'}
    table_html = """
    <table style='width:100%; border: 1px solid black; border-collapse: collapse;'>
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Exercise Results</th>
        </tr>
    """
    
    for node in nx_graph.nodes:
        name = nx_graph.nodes[node].get('title', node)
        if node in result_data:
            score = str(result_data[node])
            color = color_mapping.get(score, color_mapping['default'])
        else:
            color = color_mapping['default']
        
        exercise_results = [
            f"<li>Quiz ID: {quiz['quiz_id']}, Correct: {quiz['is_correct']}, Time Taken: {quiz['total_time_taken']}, Hint Used: {quiz['is_hint_used']}</li>"
            for quiz in exercise_quiz_result
            if quiz['exercise_title'] == name and quiz['user_id'] == result_data['user_id'] and quiz['class_code'] == result_data['class_code']
        ]
        exercise_results_html = '<ul>' + ''.join(exercise_results) + '</ul>'
        
        row_id = f"row-{node}"
        
        table_html += f"""
        <tr>
            <td style='background-color: {color};'><a href='#{row_id}-details'>{node}</a></td>
            <td>{name}</td>
            <td>{exercise_results_html}</td>
        </tr>
        """
    
    table_html += "</table>"
    return table_html

def get_ai_suggestion(topic_result, exercise_quiz_result):
    # 使用 OPenAI API 获取建议
    model = "gpt-4o"
    sys_content = f"""
        You are a professional teaching expert. I am a classroom teacher. 
        Please provide user (he/she is a teacher) a personalized analysis 
    """
    user_content = f"""
        Based on the data, 
        topic: {topic_result},
        exercise_quiz_result: {exercise_quiz_result}
        Please provide suggestions for the course. 

        rules:
        - The suggestions should be based on the data provided.
        - The suggestions should be actionable and specific.
        - The suggestions should be relevant to the course content.
        - use ZH-TW language, this is very important.
        - return markdown format
        - user will get a knowledge graph and data, 1 will be the best, 0 will be the worst. 
        then 1 will show color green, 0 will show color red, others will show color orange. 
        so you can just transfer the number to color to explain the user's performance.

        restrictions:
        - don't use any personal information
        - don't use any sensitive information
        - don't use any offensive language
        - don't use any inappropriate language
        - don't use any 簡體字，or 大陸用語，ex: 視頻請替換成影片、練習冊請替換成練習本、等等
        

        for this student based on the given knowledge graph and data:
        1. How to interpret this knowledge graph.
        2. How to assess the student's strengths and weaknesses.
        3. How I can help this student improve.

        for example:
        # 針對 emdob01 學生的課程建議
        ### 1. 如何解讀此知識圖與數據
        知識圖呈現學生在不同知識點上的掌握程度，用數字顯示熟悉度(0-1)。例如：
        - `jnc-4-05-3-1` : 1(綠色) 代表完全掌握
        - `jnc-4-06-2-2` : 0（紅色） 代表未掌握

        該學生的表現數據顯示各個練習題的回答正確性與所花費的時間。這有助於了解學生對不同題目的理解程度和答題速度。

        # 2. 如何評估學生的強項與弱項
        ### 強項
        - 學生在某些特定的練習題中得到了正確答案。例如：
        - `認識等值分數` 題目 `quiz_id` 108363，答對且用時62秒
        - `真分數、假分數與帶分數的命名及說、讀、聽、寫、做` 題目 `quiz_id` 116882，答對且用時123秒

        ### 弱項
        - 學生在大多數練習題中答錯問題，特別在以下主題：
        - `認識等值分數`：多次答錯
        - `簡單異分母分數的比較`：多次答錯，且用時較長
        - `假分數與帶分數的互換`：多次答錯

        # 3. 如何幫助學生改進
        基於以上分析，給予以下改善建議：

        ### 針對弱點加強練習
        1. **認識等值分數**：
        - 提供更多視覺化的教學資源，如分數圖表和視頻講解。
        - 設計一系列有針對性的互動練習，幫助學生理解分數等值的概念。
        - 安排小組討論，讓學生分享他們的解題思路並相互學習。

        2. **簡單異分母分數的比較**：
        - 強化基本概念教學，如找共同分母的方法。
        - 設計漸進增難度的習題，從簡單到複雜，逐步提高學生的理解和應用能力。
        - 鼓勵學生使用實物模型或數線來理解異分母分數的比較。

        3. **假分數與帶分數的互換**：
        - 重點教學如何將假分數轉換為帶分數，並展示具體步驟。
        - 制作練習題目集，讓學生反復練習，並提供及時的反饋和指導。
        - 透過遊戲或互動活動來增強學生對該概念的興趣和記憶。

        # 總結與提點建議
        - 在課後提供補充資料（如影片、練習冊）供學生複習。
        - 設計定期的小測試來檢驗學生的進步情況。
        - 鼓勵學生對於每次錯誤進行反思，分析失誤原因並加以改正。
        - 建立個性化學習計劃，根據每次測試結果調整練習重點和方法。

        希望這些建議能夠幫助該學生提升學習效果並更好地掌握課程內容。
    """

    print(f"user_content: {user_content}")

    messages = [
        {"role": "system", "content": sys_content},
        {"role": "user", "content": user_content}
    ]
    request_payload = {
        "model": model,
        "messages": messages,
        "max_tokens": 4000,
    }

    response = OPENAI_API_CLIENT.chat.completions.create(**request_payload)
    suggestion = response.choices[0].message.content

    return suggestion


with gr.Blocks() as app:
    gr.Markdown("# Metabase Query and Visualization")

    with gr.Row():
        topic_card_id = gr.Textbox(label="Topic Card ID", value="6267")
        exercise_card_id = gr.Textbox(label="Exercis Quiz Card ID", value="6284")
        class_code = gr.Textbox(label="Class Code", value="CAFSR")
        user_id = gr.Textbox(label="User ID", value="emdob01")

    all_in_one_button = gr.Button("Fetch Data and Generate Graph")

    with gr.Accordion(open=False, label="Raw Data"):
        topic_result = gr.JSON(label="Query Result")
        exercise_quiz_result = gr.JSON(label="Quiz Query Result")

    with gr.Row():
        graph_html = gr.HTML()

    with gr.Row():
        gr.Markdown("# AI suggestion Powered by Junyi Academy")
    with gr.Row():
        ai_suggestion = gr.Markdown()

    all_in_one_button.click(
        fn=query_metabase_topic,
        inputs=[class_code, topic_card_id, user_id],
        outputs=topic_result
    ).then(
        fn=query_metabase_exercise_quiz,
        inputs=[class_code, exercise_card_id, user_id],
        outputs=exercise_quiz_result
    ).then(
        fn=needs_analysis,
        inputs=[topic_result, exercise_quiz_result],
        outputs=graph_html
    ).then(
        fn=get_ai_suggestion,
        inputs=[topic_result, exercise_quiz_result],
        outputs=ai_suggestion
    )

app.launch()