src/chatbot/dashboard/dashboard_page.py

import os
import sqlite3
import pandas as pd
import gradio as gr
import plotly.graph_objects as go
import plotly.express as px

from dashboard.visualize_component import (
    generate_budget_utilization_gauge_chart,
    generate_deliverable_budget_vs_expense_bar_chart,
    generate_spending_distribution_pie_chart,
    generate_daily_spending_bar_chart,
    generate_cumulative_spending_line_chart,
    generate_risk_level_distribution_pie_chart,
    generate_deliverable_timeline_gantt_chart,
    render_deliverable_summary_cards
)

DB_PATH = os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(__file__))), 'database', 'hello_earth_data_2.db')

# ------------------------ Database Utilities ------------------------

def connect_db():
    return sqlite3.connect(DB_PATH)

def fetch_df(query, params=None):
    conn = connect_db()
    df = pd.read_sql_query(query, conn, params=params or ())
    conn.close()
    return df

def get_project_data():
    return fetch_df("SELECT * FROM Project")

def get_deliverable_data(project_id):
    return fetch_df("SELECT * FROM Deliverable WHERE project_id = ?", (project_id,))

def get_all_deliverable_budgets(project_id):
    query = """
        SELECT d.title, db.*
        FROM Deliverable_Budget db
        JOIN Deliverable d ON db.deliverable_id = d.deliverable_id
        WHERE d.project_id = ?
    """
    return fetch_df(query, (project_id,))

def get_all_expenses(project_id):
    query = """
        SELECT 
            e.expense_id,
            e.associated_deliverable_id,
            d.title AS deliverable_title,
            e.seller_name,
            e.seller_address,
            e.seller_phone_number,
            e.buyer_name,
            e.buyer_address,
            e.transaction_date,
            e.total_payment_amount,
            e.expense_description,
            e.status
        FROM Expense e
        JOIN Deliverable d ON e.associated_deliverable_id = d.deliverable_id
        WHERE d.project_id = ?
    """
    return fetch_df(query, (project_id,))

# ------------------------ Business Logic ------------------------

def get_total_expense(df, status=None):
    if status:
        df = df[df['status'] == status]
    return df['total_payment_amount'].sum()

def get_total_budget(df):
    return df[['wage', 'materials', 'tools_equipment', 'services', 'misc']].sum().sum()

def get_title_by_id(project_options, pid):
    return next((p['title'] for p in project_options if p['project_id'] == pid), "Unknown")

def filter_expenses(search_term, df):
    if not search_term:
        return df.drop(columns=["expense_id", "associated_deliverable_id"]).rename(columns={
            "deliverable_title": "การส่งมอบ",
            "seller_name": "ชื่อผู้ขาย",
            "seller_address": "ที่อยู่ผู้ขาย",
            "seller_phone_number": "เบอร์ผู้ขาย",
            "buyer_name": "ชื่อผู้ซื้อ",
            "buyer_address": "ที่อยู่ผู้ซื้อ",
            "transaction_date": "วันที่ทำรายการ",
            "total_payment_amount": "ยอดค่าใช้จ่าย",
            "expense_description": "รายละเอียดค่าใช้จ่าย",
            "status": "สถานะ"
        })
    
    search_term = search_term.lower()
    mask = pd.Series(False, index=df.index)
    for col in [
        'deliverable_title', 'seller_name', 'seller_address', 'seller_phone_number',
        'buyer_name', 'buyer_address', 'transaction_date',
        'total_payment_amount', 'expense_description', 'status'
    ]:
        mask |= df[col].astype(str).str.lower().str.contains(search_term)
    
    return df[mask].drop(columns=["expense_id", "associated_deliverable_id"]).rename(columns={
        "deliverable_title": "การส่งมอบ",
        "seller_name": "ชื่อผู้ขาย",
        "seller_address": "ที่อยู่ผู้ขาย",
        "seller_phone_number": "เบอร์ผู้ขาย",
        "buyer_name": "ชื่อผู้ซื้อ",
        "buyer_address": "ที่อยู่ผู้ซื้อ",
        "transaction_date": "วันที่ทำรายการ",
        "total_payment_amount": "ยอดค่าใช้จ่าย",
        "expense_description": "รายละเอียดค่าใช้จ่าย",
        "status": "สถานะ"
    })


def update_project_detail(project_id):
    project = get_project_data()
    info = project.loc[project['project_id'] == project_id, 'detail_information'].values
    return info[0] if len(info) > 0 else "No description available."

# ------------------------ UI Setup ------------------------

def create_dashboard_page():
    project_list = get_project_data()
    project_options = project_list[['project_id', 'title']].to_dict("records")

    with gr.Blocks(theme=gr.themes.Soft(primary_hue="blue")) as page:
        selected_project_id = gr.State()
        expense_df_state = gr.State()

        # --- Page 1: Project Selection ---
        with gr.Column(visible=True) as project_selector_page:
            gr.Markdown("# 🌍 โครงการ")
            project_dropdown = gr.Dropdown(
                choices=[(p['title'], p['project_id']) for p in project_options],
                label="เลือกโครงการ"
            )
            project_go_button = gr.Button("ดูแดชบอร์ด")

        # --- Page 2: Dashboard View ---
        with gr.Column(visible=False) as dashboard_page:
            dashboard_header = gr.Markdown("")
            project_detail_md = gr.Markdown()
            back_button = gr.Button("⬅ กลับหน้าเลือกโครงการ")

            with gr.Column():
                gr.Markdown("## 💰 ภาพรวมทางการเงินของโครงการทั้งหมด")
                
                gr.Markdown("### สรุปงบประมาณ")
                with gr.Row():
                    total_budget_md = gr.Markdown()
                    total_expense_md = gr.Markdown()
                    leftover_md = gr.Markdown()

                gr.Markdown("### เกจแสดงการใช้จ่าย")
                gauge_plot = gr.Plot()

                gr.Markdown("## 📦 ข้อมูลเชิงลึกทางการเงินของแต่ละการส่งมอบงาน")

                gr.Markdown("### งบประมาณและค่าใช้จ่าย")
                with gr.Row():
                    bar_chart_plot = gr.Plot()
                    pie_chart_plot = gr.Plot()

                gr.Markdown("### ค่าใช้จ่ายแต่ละช่วงเวลา")
                with gr.Row():
                    daily_plot = gr.Plot()
                    cum_plot = gr.Plot()

                gr.Markdown("### ความเสี่ยงและการส่งมอบ")
                with gr.Row():
                    with gr.Column(scale=1):
                        risk_plot = gr.Plot()
                    with gr.Column(scale=2):
                        cards_html = gr.HTML()

                gr.Markdown("### ไทม์ไลน์การส่งมอบ (Gantt Chart)")
                gantt_plot = gr.Plot()


                gr.Markdown("## 💵 ตารางค่าใช้จ่าย")
                search_box = gr.Textbox(placeholder="ค้นหารายการ", label="ค้นหา")
                expense_table = gr.DataFrame()

        # --- Event Handlers ---

        search_box.change(
            fn=filter_expenses,
            inputs=[search_box, expense_df_state],
            outputs=expense_table
        )

        def go_to_dashboard(project_id):
            deliverables = get_deliverable_data(project_id)
            budgets = get_all_deliverable_budgets(project_id)
            expenses = get_all_expenses(project_id)

            total_budget = get_total_budget(budgets)
            total_expense = get_total_expense(expenses, status='approved')
            leftover = total_budget - total_expense

            if leftover < 0:
                status, color = "⚠️ เกินงบประมาณ", "red"
            elif leftover < total_budget * 0.1:
                status, color = "⚠️ งบประมาณเหลือน้อย", "orange"
            else:
                status, color = "✅ งบประมาณเป็นไปตามแผน", "green"

            detail_text = update_project_detail(project_id)

            return [
                project_id,
                gr.update(visible=False),
                gr.update(visible=True),
                gr.update(value=f"# 📊 แดชบอร์ดการเงิน — {get_title_by_id(project_options, project_id)}"),
                gr.update(value=detail_text),
                gr.update(value=f"### งบประมาณ\n## {total_budget:,.0f}"),
                gr.update(value=f"### ค่าใช้จ่าย\n## {total_expense:,.0f}\n({(total_expense / total_budget * 100) if total_budget != 0 else 0:.1f}% ของงบประมาณทั้งหมด)"),
                gr.update(value=f"### งบประมาณคงเหลือ\n## {leftover:,.0f}\n<span style='color: {color};'>{status}</span>"),
                gr.update(value=generate_budget_utilization_gauge_chart(total_budget, total_expense)),
                gr.update(value=generate_deliverable_budget_vs_expense_bar_chart(budgets, expenses)),
                gr.update(value=generate_spending_distribution_pie_chart(expenses, budgets)),
                gr.update(value=generate_daily_spending_bar_chart(expenses)),
                gr.update(value=generate_cumulative_spending_line_chart(expenses)),
                gr.update(value=generate_risk_level_distribution_pie_chart(deliverables)),
                gr.update(value=generate_deliverable_timeline_gantt_chart(deliverables)),
                gr.update(value=render_deliverable_summary_cards(deliverables)),
                gr.update(value=(
                    expenses
                    .drop(columns=["expense_id", "associated_deliverable_id"])
                    .rename(columns={
                        "deliverable_title": "การส่งมอบ",
                        "seller_name": "ชื่อผู้ขาย",
                        "seller_address": "ที่อยู่ผู้ขาย",
                        "seller_phone_number": "เบอร์ผู้ขาย",
                        "buyer_name": "ชื่อผู้ซื้อ",
                        "buyer_address": "ที่อยู่ผู้ซื้อ",
                        "transaction_date": "วันที่ทำรายการ",
                        "total_payment_amount": "ยอดค่าใช้จ่าย",
                        "expense_description": "รายละเอียดค่าใช้จ่าย",
                        "status": "สถานะ"
                    })
                )),
                expenses
            ]

        def go_back_to_project_list():
            return [gr.update(visible=True), gr.update(visible=False)]

        # --- Button Triggers ---

        project_go_button.click(
            fn=go_to_dashboard,
            inputs=project_dropdown,
            outputs=[
                selected_project_id,
                project_selector_page,
                dashboard_page,
                dashboard_header,
                project_detail_md,
                total_budget_md,
                total_expense_md,
                leftover_md,
                gauge_plot,
                bar_chart_plot,
                pie_chart_plot,
                daily_plot,
                cum_plot,
                risk_plot,
                gantt_plot,
                cards_html,
                expense_table,
                expense_df_state
            ]
        )

        back_button.click(
            fn=go_back_to_project_list,
            outputs=[project_selector_page, dashboard_page]
        )

    return page

if __name__ == "__main__":
    page = create_dashboard_page()
    page.launch()