src/app.py

"""SPV 都更財務動態系統 - Urban Renewal Financial Management System.

This module provides a comprehensive Streamlit-based dashboard for managing SPV
(Special Purpose Vehicle) urban renewal project finances, including:
- P&L and profit analysis with waterfall charts
- Dynamic cash flow tracking with cumulative balance
- Project scheduling with Gantt charts and milestones
- Financing and sales management
- Landowner management and agreement tracking
- Risk dashboard and scenario simulation
- PDF/Excel report generation
"""

import datetime
import sqlite3
from pathlib import Path
from typing import Optional

import pandas as pd
import plotly.express as px
import plotly.figure_factory as ff
import plotly.graph_objects as go
import streamlit as st

from database import (
    DB_PATH,
    backup_current_project,
    calculate_scenario_impact,
    clear_to_actual,
    get_budget_vs_actual,
    get_landowner_stats,
    get_metadata,
    get_milestone_summary,
    init_db,
    load_full_project_example,
    set_metadata,
    get_database_file,
    restore_database_from_bytes,
)
from reports import (
    calculate_cashflow_summary,
    generate_excel_report,
    generate_pdf_report,
)
from scenarios import (
    ScenarioParams,
    run_sensitivity_analysis,
    simulate_scenario,
)
from price_lookup import (
    TAIPEI_DISTRICTS,
    get_average_prices,
    get_district_statistics,
    query_real_prices,
)

# ============ Design System ============
COLORS = {
    "primary": "#F59E0B",
    "secondary": "#FBBF24",
    "cta": "#8B5CF6",
    "bg_dark": "#0F172A",
    "bg_card": "#1E293B",
    "text": "#F8FAFC",
    "muted": "#94A3B8",
    "border": "#334155",
    "income": "#10B981",
    "expense": "#EF4444",
    "warning": "#F97316",
    "info": "#3B82F6",
}

PLOTLY_LAYOUT = {
    "paper_bgcolor": "rgba(0,0,0,0)",
    "plot_bgcolor": "rgba(0,0,0,0)",
    "font": {"color": COLORS["text"], "family": "Inter, Noto Sans TC, sans-serif"},
    "xaxis": {
        "gridcolor": COLORS["border"],
        "linecolor": COLORS["border"],
        "tickfont": {"color": COLORS["muted"]},
    },
    "yaxis": {
        "gridcolor": COLORS["border"],
        "linecolor": COLORS["border"],
        "tickfont": {"color": COLORS["muted"]},
    },
    "legend": {"font": {"color": COLORS["text"]}},
    "margin": {"l": 40, "r": 40, "t": 60, "b": 40},
}


def load_css() -> None:
    """Load custom CSS from style.css file."""
    css_path = Path(__file__).parent / "style.css"
    if css_path.exists():
        with open(css_path, encoding="utf-8") as f:
            st.markdown(f"<style>{f.read()}</style>", unsafe_allow_html=True)


def get_data() -> dict[str, pd.DataFrame]:
    """Load all data from database.

    Returns:
        dict: Dictionary containing all DataFrames.
    """
    conn = sqlite3.connect(DB_PATH)
    data = {
        "transactions": pd.read_sql_query(
            "SELECT * FROM finance_transactions WHERE 類型='實際'", conn
        ),
        "params": pd.read_sql_query("SELECT * FROM project_params", conn),
        "stages": pd.read_sql_query("SELECT * FROM project_stages", conn),
        "loans": pd.read_sql_query("SELECT * FROM loan_contracts", conn),
        "inventory": pd.read_sql_query("SELECT * FROM inventory", conn),
        "landowners": pd.read_sql_query("SELECT * FROM landowners", conn),
        "milestones": pd.read_sql_query("SELECT * FROM milestones", conn),
        "risks": pd.read_sql_query("SELECT * FROM risk_events", conn),
        "scenarios": pd.read_sql_query("SELECT * FROM scenarios", conn),
    }
    # 嘗試載入土地成本表 (可能不存在於舊DB)
    try:
        data["land_costs"] = pd.read_sql_query("SELECT * FROM land_costs", conn)
    except Exception:
        data["land_costs"] = pd.DataFrame()
    conn.close()
    return data


# ============ Chart Functions ============


def create_waterfall_chart(total_in: float, exp_df: pd.DataFrame) -> go.Figure:
    """Create styled waterfall chart for P&L analysis."""
    fig = go.Figure(
        go.Waterfall(
            orientation="v",
            measure=["absolute"] + ["relative"] * len(exp_df) + ["total"],
            x=["總收入預估"] + exp_df["科目名稱"].tolist() + ["最終淨利"],
            y=[total_in] + (-exp_df["金額"]).tolist() + [0],
            connector={"line": {"color": COLORS["border"]}},
            increasing={"marker": {"color": COLORS["income"]}},
            decreasing={"marker": {"color": COLORS["expense"]}},
            totals={"marker": {"color": COLORS["primary"]}},
            textposition="outside",
            textfont={"color": COLORS["text"]},
        )
    )
    fig.update_layout(**PLOTLY_LAYOUT, title_text="專案獲利結構分析", showlegend=False)
    return fig


def create_gantt_chart(
    stages: pd.DataFrame, project_start: datetime.date
) -> Optional[go.Figure]:
    """Create styled Gantt chart for project scheduling."""
    if stages.empty:
        return None

    df_g = [
        {
            "Task": r["工項名稱"],
            "Start": project_start + datetime.timedelta(days=int(r["開始月份"] * 30.4)),
            "Finish": project_start
            + datetime.timedelta(days=int((r["開始月份"] + r["持續月份"]) * 30.4)),
            "Resource": "工程",
        }
        for _, r in stages.iterrows()
    ]
    fig = ff.create_gantt(
        df_g,
        index_col="Resource",
        group_tasks=True,
        colors=[COLORS["primary"]],
        show_colorbar=False,
    )
    fig.update_layout(**PLOTLY_LAYOUT, title_text="工程排程甘特圖")
    return fig


def create_cumulative_cashflow_chart(df: pd.DataFrame) -> go.Figure:
    """Create cumulative cash flow chart."""
    if df.empty:
        return go.Figure()

    # 計算每月淨現金流
    monthly = df.groupby("月份").apply(
        lambda x: x[x["科目性質"] == "收入"]["金額"].sum()
        - x[x["科目性質"] == "支出"]["金額"].sum()
    )
    cumulative = monthly.cumsum()

    fig = go.Figure()
    fig.add_trace(
        go.Scatter(
            x=cumulative.index.tolist(),
            y=cumulative.values.tolist(),
            mode="lines+markers",
            name="累計現金流",
            line={"color": COLORS["primary"], "width": 3},
            marker={"size": 6},
            fill="tozeroy",
            fillcolor="rgba(245, 158, 11, 0.1)",
        )
    )

    # 添加零線和警戒線
    fig.add_hline(y=0, line_dash="dash", line_color=COLORS["muted"])

    # 標記最低點
    min_val = cumulative.min()
    min_idx = cumulative.idxmin()
    if min_val < 0:
        fig.add_annotation(
            x=min_idx,
            y=min_val,
            text=f"資金最低點: {min_val:,.0f}萬",
            showarrow=True,
            arrowhead=2,
            arrowcolor=COLORS["expense"],
            font={"color": COLORS["expense"]},
        )

    fig.update_layout(
        **PLOTLY_LAYOUT,
        title_text="累計現金流走勢",
        xaxis_title="月份",
        yaxis_title="金額 (萬)",
    )
    return fig


def create_budget_vs_actual_chart(df: pd.DataFrame) -> go.Figure:
    """Create budget vs actual comparison chart."""
    if df.empty:
        return go.Figure()

    fig = go.Figure()
    fig.add_trace(
        go.Bar(
            name="預算",
            x=df["工項名稱"],
            y=df["預算金額"],
            marker_color=COLORS["info"],
        )
    )
    fig.add_trace(
        go.Bar(
            name="實際支出",
            x=df["工項名稱"],
            y=df["實際支出"],
            marker_color=COLORS["primary"],
        )
    )
    fig.update_layout(
        **PLOTLY_LAYOUT,
        title_text="預算 vs 實際支出",
        barmode="group",
    )
    return fig


def create_landowner_pie_chart(stats: dict) -> go.Figure:
    """Create landowner agreement status pie chart."""
    status_counts = stats.get("status_counts", {})
    if not status_counts:
        return go.Figure()

    colors_map = {
        "已簽署": COLORS["income"],
        "協商中": COLORS["warning"],
        "待聯繫": COLORS["info"],
        "拒絕中": COLORS["expense"],
    }

    fig = go.Figure(
        go.Pie(
            labels=list(status_counts.keys()),
            values=list(status_counts.values()),
            marker={"colors": [colors_map.get(k, COLORS["muted"]) for k in status_counts]},
            textinfo="label+percent",
            textfont={"color": COLORS["text"]},
            hole=0.4,
        )
    )
    fig.update_layout(**PLOTLY_LAYOUT, title_text="地主同意書狀態", showlegend=True)
    return fig


# ============ Main Application ============


def main() -> None:
    """Main application entry point."""
    init_db()

    st.set_page_config(
        page_title="都更財務動態系統",
        page_icon="📊",
        layout="wide",
        initial_sidebar_state="expanded",
    )

    load_css()

    st.title("都更計畫：現金流整合管理系統")

    # ============ Sidebar ============
    with st.sidebar:
        st.header("專案管理面板")
        
        st.info("💡 提示：各項專案設定已移動至右側對應分頁中。")

        col_a, col_b = st.columns(2)
        if col_a.button("切換至原專案", use_container_width=True):
            backup_current_project()
            clear_to_actual()
            st.rerun()
        if col_b.button("載入範例", type="primary", use_container_width=True):
            load_full_project_example(100000.0)  # 預設範例預算
            st.rerun()

        st.divider()
        st.divider()
        
        with st.expander("專案檔案管理", expanded=False):
            # 專案匯出
            db_bytes = get_database_file()
            if db_bytes:
                st.download_button(
                    label="匯出專案檔 (.db)",
                    data=db_bytes,
                    file_name=f"project_export_{datetime.datetime.now().strftime('%Y%m%d')}.db",
                    mime="application/x-sqlite3",
                    use_container_width=True,
                )
            
            # 專案匯入
            uploaded_db = st.file_uploader("匯入專案檔", type=["db"], key="project_import")
            if uploaded_db:
                if st.button("確認覆蓋目前專案", type="primary", use_container_width=True):
                    if restore_database_from_bytes(uploaded_db.getvalue()):
                        st.success("專案已成功還原！")
                        st.rerun()
                    else:
                        st.error("還原失敗，請檢查檔案格式。")

        st.caption("版本: v1.3.0 (Import/Export)")

    # 初始化專案設定
    start_date_str = get_metadata("project_start", "2026-01-20")
    try:
        project_start = datetime.datetime.strptime(start_date_str, "%Y-%m-%d").date()
    except ValueError:
        project_start = datetime.date(2026, 1, 20)

    # ============ Main Tabs ============
    tabs = st.tabs([
        "損益分析",
        "現金流總表",
        "工程與時程",
        "融資與銷售",
        "地主管理",
        "風險與情境",
        "實價登錄查詢",
    ])

    data = get_data()
    df = data["transactions"]

    # ============ Tab 1: P&L Analysis ============
    with tabs[0]:
        # 專案設定區塊
        with st.expander("專案設定", expanded=True):
            current_params = data["params"].set_index("參數名稱")["數值"].to_dict()
            current_budget = current_params.get("總工程預算", 100000.0)
            
            new_budget = st.number_input(
                "總工程預算 (萬)", 
                value=float(current_budget), 
                step=1000.0,
                key="tab1_budget"
            )
            
            if new_budget != current_budget:
                conn = sqlite3.connect(DB_PATH)
                conn.execute(
                    "INSERT OR REPLACE INTO project_params VALUES (?, ?)", 
                    ("總工程預算", new_budget)
                )
                conn.commit()
                conn.close()
                st.rerun()

        if not df.empty:
            total_in = df[df["科目性質"] == "收入"]["金額"].sum()
            total_out = df[df["科目性質"] == "支出"]["金額"].sum()
            profit = total_in - total_out
            
            # ... (metrics display) ...

            st.subheader("專案獲利結構分析")

            c1, c2, c3, c4 = st.columns(4)
            c1.metric("總流入金額", f"{total_in:,.0f} 萬")
            c2.metric("總流出支出", f"{total_out:,.0f} 萬")
            c3.metric("預計結餘毛利", f"{profit:,.0f} 萬")
            c4.metric(
                "毛利率",
                f"{(profit / total_in * 100):.1f}%" if total_in > 0 else "N/A",
            )

            # 土地取得成本摘要
            land_costs = data.get("land_costs", pd.DataFrame())
            if not land_costs.empty:
                st.divider()
                st.subheader("土地取得成本")
                lc1, lc2, lc3 = st.columns(3)
                coop_value = land_costs[land_costs["取得方式"] == "土地合作"]["權利價值萬"].sum()
                purchase_cost = land_costs[land_costs["取得方式"] == "收購"]["收購價格萬"].sum()
                total_land = coop_value + purchase_cost
                lc1.metric("合作土地價值", f"{coop_value:,.0f} 萬")
                lc2.metric("收購支出", f"{purchase_cost:,.0f} 萬")
                lc3.metric("土地成本總計", f"{total_land:,.0f} 萬")

            # 瀑布圖
            exp_df = (
                df[df["科目性質"] == "支出"]
                .groupby("科目名稱")["金額"]
                .sum()
                .reset_index()
            )
            fig_w = create_waterfall_chart(total_in, exp_df)
            st.plotly_chart(fig_w, use_container_width=True)

            # 預算 vs 實際
            budget_df = get_budget_vs_actual()
            if not budget_df.empty:
                st.subheader("預算 vs 實際支出")
                fig_bva = create_budget_vs_actual_chart(budget_df)
                st.plotly_chart(fig_bva, use_container_width=True)

                # 超支警示
                over_budget = budget_df[budget_df["差異率"] > 10]
                if not over_budget.empty:
                    st.warning(
                        f"⚠️ 以下工項超出預算 10% 以上："
                        f"{', '.join(over_budget['工項名稱'].tolist())}"
                    )
            
            # 報表匯出區塊
            st.divider()
            st.subheader("報表匯出")
            col_pdf, col_excel = st.columns(2)

            with col_pdf:
                pdf_bytes = generate_pdf_report(
                    project_name="都更專案",
                    total_income=total_in,
                    total_expense=total_out,
                    profit=profit,
                    landowner_stats=get_landowner_stats(),
                    milestone_summary=get_milestone_summary(),
                    transactions_df=df,
                    stages_df=data["stages"],
                    risks_df=data["risks"],
                )
                st.download_button(
                    "下載 PDF",
                    pdf_bytes,
                    file_name="財務報告.pdf",
                    mime="application/pdf",
                    use_container_width=True,
                    key="tab1_pdf_download"
                )

            with col_excel:
                excel_bytes = generate_excel_report(
                    transactions_df=df,
                    stages_df=data["stages"],
                    landowners_df=data["landowners"],
                    milestones_df=data["milestones"],
                    risks_df=data["risks"],
                    loans_df=data["loans"],
                    inventory_df=data["inventory"],
                )
                st.download_button(
                    "下載 Excel",
                    excel_bytes,
                    file_name="專案數據.xlsx",
                    mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                    use_container_width=True,
                    key="tab1_excel_download"
                )

        else:
            st.info("請利用左側選單載入數據。")

    # ============ Tab 2: Cash Flow ============
    with tabs[1]:
        # 新增收支項目區塊
        with st.expander("新增收支項目", expanded=False):
            with st.form("tab2_custom_item_form"):
                c1, c2, c3, c4 = st.columns(4)
                f_month = c1.number_input("月份", min_value=1, value=1, key="tab2_month")
                f_name = c2.text_input("項目名稱", key="tab2_name")
                f_type = c3.selectbox("科目性質", ["支出", "收入"], key="tab2_type")
                f_amt = c4.number_input("金額 (萬)", min_value=0.0, key="tab2_amt")
                
                if st.form_submit_button("確認提交", use_container_width=True):
                    if f_name:
                        conn = sqlite3.connect(DB_PATH)
                        conn.execute(
                            "INSERT OR REPLACE INTO finance_transactions "
                            "VALUES (?, '實際', ?, ?, ?)",
                            (f_month, f_name, f_amt, f_type),
                        )
                        conn.commit()
                        conn.close()
                        st.rerun()

        st.subheader("動態現金流流水帳")

        if not df.empty:
            # 月份篩選
            months = sorted(df["月份"].unique())
            month_range = st.slider(
                "選擇月份範圍",
                min_value=int(min(months)),
                max_value=int(max(months)),
                value=(int(min(months)), int(max(months))),
            )
            filtered_df = df[
                (df["月份"] >= month_range[0]) & (df["月份"] <= month_range[1])
            ]

            # 累計現金流圖
            fig_cum = create_cumulative_cashflow_chart(df)
            st.plotly_chart(fig_cum, use_container_width=True)

            # 現金流摘要
            summary = calculate_cashflow_summary(df)
            if summary["min_balance"] < 0:
                st.error(
                    f"⚠️ 資金斷鏈預警：第 {summary['min_balance_month']} 月現金流"
                    f"最低點達 {summary['min_balance']:,.0f} 萬"
                )

            # 轉置表格
            pivot_df = filtered_df.pivot_table(
                index="月份", columns="科目名稱", values="金額", fill_value=0
            ).reset_index()

            # 計算每月累計餘額
            if not pivot_df.empty:
                inc_cols = df[df["科目性質"] == "收入"]["科目名稱"].unique()
                exp_cols = df[df["科目性質"] == "支出"]["科目名稱"].unique()

                def calc_monthly_balance(row: pd.Series) -> float:
                    inc = sum(row.get(c, 0) for c in inc_cols if c in row.index)
                    exp = sum(row.get(c, 0) for c in exp_cols if c in row.index)
                    return inc - exp

                pivot_df["月結餘"] = pivot_df.apply(calc_monthly_balance, axis=1)
                pivot_df["累計餘額"] = pivot_df["月結餘"].cumsum()

                st.dataframe(
                    pivot_df.style.format(precision=0),
                    use_container_width=True,
                    height=400,
                )
        else:
            st.info("請利用左側選單載入數據。")

    # ============ Tab 3: Project Schedule ============
    with tabs[2]:
        # 排程設定
        with st.expander("排程設定", expanded=True):
            new_date = st.date_input("計畫啟動日期", project_start, key="tab3_start_date")
            if new_date != project_start:
                set_metadata("project_start", str(new_date))
                st.rerun()

        # 新增工程階段
        with st.expander("新增工程階段", expanded=False):
            with st.form("tab3_stage_form"):
                c1, c2, c3 = st.columns(3)
                s_name = c1.text_input("工項名稱", key="tab3_s_name")
                s_start = c2.number_input("開始月份 (之後)", min_value=0, value=0, key="tab3_s_start")
                s_dur = c3.number_input("持續月份", min_value=1, value=6, key="tab3_s_dur")
                
                c4, c5 = st.columns(2)
                s_cost = c4.number_input("預算金額 (萬)", min_value=0.0, key="tab3_s_cost")
                s_actual = c5.number_input("實際支出 (萬)", min_value=0.0, key="tab3_s_actual")
                
                if st.form_submit_button("新增工項"):
                    if s_name:
                        conn = sqlite3.connect(DB_PATH)
                        conn.execute(
                            """INSERT INTO project_stages
                            (工項名稱, 開始月份, 持續月份, 成本佔比, 撥款佔比, 預算金額, 實際支出)
                            VALUES (?, ?, ?, 0, 0, ?, ?)""",
                            (s_name, s_start, s_dur, s_cost, s_actual)
                        )
                        conn.commit()
                        conn.close()
                        st.rerun()

        col_gantt, col_milestone = st.columns([2, 1])

        with col_gantt:
            st.subheader("工程排程甘特圖")
            fig_g = create_gantt_chart(data["stages"], project_start)
            if fig_g:
                st.plotly_chart(fig_g, use_container_width=True)
            else:
                st.info("尚無工程階段資料。")

        with col_milestone:
            st.subheader("里程碑進度")
            milestones = data["milestones"]
            if not milestones.empty:
                for _, m in milestones.iterrows():
                    status_emoji = {
                        "已完成": "✅",
                        "進行中": "🔄",
                        "待進行": "⏳",
                    }.get(m["狀態"], "❓")

                    st.markdown(
                        f"{status_emoji} **{m['里程碑名稱']}**  \n"
                        f"預計：{m['預計日期']} | {m['狀態']}"
                    )
            else:
                st.info("尚無里程碑資料。")

        # 合約到期提醒
        st.divider()
        st.subheader("合約到期提醒")
        loans = data["loans"]
        if not loans.empty and "到期日" in loans.columns:
            today = datetime.date.today()
            for _, loan in loans.iterrows():
                if pd.notna(loan.get("到期日")):
                    try:
                        due_date = datetime.datetime.strptime(
                            str(loan["到期日"]), "%Y-%m-%d"
                        ).date()
                        days_left = (due_date - today).days
                        if 0 < days_left <= 90:
                            st.warning(
                                f"⚠️ {loan['貸款名稱']} 將於 {days_left} 天後到期 ({due_date})"
                            )
                    except ValueError:
                        pass

    # ============ Tab 4: Financing & Sales ============
    with tabs[3]:
        # 新增融資合約
        with st.expander("新增融資合約", expanded=False):
            with st.form("tab4_loan_form"):
                c1, c2, c3 = st.columns(3)
                l_name = c1.text_input("貸款名稱", key="tab4_l_name")
                l_amount = c2.number_input("授信額度 (萬)", min_value=0.0, key="tab4_l_amt")
                l_rate = c3.number_input("年利率 (%)", min_value=0.0, value=2.5, key="tab4_l_rate")
                
                c4, c5 = st.columns(2)
                l_drawn = c4.number_input("已動撥金額 (萬)", min_value=0.0, key="tab4_l_drawn")
                l_date = c5.date_input("到期日", key="tab4_l_date")
                
                if st.form_submit_button("新增合約"):
                    if l_name:
                        conn = sqlite3.connect(DB_PATH)
                        conn.execute(
                            """INSERT INTO loan_contracts
                            (貸款名稱, 授信額度, 年利率, 狀態, 動撥金額, 到期日, 備註)
                            VALUES (?, ?, ?, '已動撥', ?, ?, '')""",
                            (l_name, l_amount, l_rate, l_drawn, l_date)
                        )
                        conn.commit()
                        conn.close()
                        st.rerun()

        # 新增銷售庫存
        with st.expander("新增銷售庫存", expanded=False):
            with st.form("tab4_inventory_form"):
                c1, c2, c3 = st.columns(3)
                i_name = c1.text_input("物件名稱", key="tab4_i_name")
                i_total = c2.number_input("總數量", min_value=1, value=10, key="tab4_i_total")
                i_back = c3.number_input("地主分回", min_value=0, value=0, key="tab4_i_back")
                
                c4, c5 = st.columns(2)
                i_price = c4.number_input("預計售價 (萬)", min_value=0.0, key="tab4_i_price")
                i_sold = c5.number_input("已售數量", min_value=0, value=0, key="tab4_i_sold")
                
                if st.form_submit_button("新增庫存"):
                    if i_name:
                        conn = sqlite3.connect(DB_PATH)
                        conn.execute(
                            """INSERT INTO inventory
                            (物件名稱, 預計售價, 總數量, 地主分回數量, 已售數量, 銷售狀態)
                            VALUES (?, ?, ?, ?, ?, '銷售中')""",
                            (i_name, i_price, i_total, i_back, i_sold)
                        )
                        conn.commit()
                        conn.close()
                        st.rerun()

        col_l, col_r = st.columns(2)

        with col_l:
            st.subheader("融資合約清單")
            loans = data["loans"]
            if not loans.empty:
                # 計算融資使用率
                if "授信額度" in loans.columns and "動撥金額" in loans.columns:
                    total_credit = loans["授信額度"].sum()
                    total_drawn = loans["動撥金額"].sum()
                    usage_rate = (total_drawn / total_credit * 100) if total_credit > 0 else 0

                    lm1, lm2 = st.columns(2)
                    lm1.metric("融資使用率", f"{usage_rate:.1f}%")
                    lm2.metric("已動撥金額", f"{total_drawn:,.0f} 萬")

                st.dataframe(loans, use_container_width=True)
            else:
                st.info("尚無融資合約資料。")

        with col_r:
            st.subheader("房屋銷售計畫")
            inventory = data["inventory"]
            if not inventory.empty:
                # 計算銷售進度
                if "總數量" in inventory.columns:
                    total_units = inventory["總數量"].sum()
                    landlord_units = inventory["地主分回數量"].sum()
                    sold_units = inventory.get("已售數量", pd.Series([0])).sum()
                    sellable = total_units - landlord_units
                    sales_rate = (sold_units / sellable * 100) if sellable > 0 else 0

                    sm1, sm2 = st.columns(2)
                    sm1.metric("可售戶數", f"{sellable:.0f} 戶")
                    sm2.metric("銷售進度", f"{sales_rate:.1f}%")

                    # 銷售進度條
                    st.progress(min(sales_rate / 100, 1.0))

                st.dataframe(inventory, use_container_width=True)
            else:
                st.info("尚無銷售計畫資料。")

    # ============ Tab 5: Landowner Management ============
    with tabs[4]:
        st.subheader("地主管理")

        landowners = data["landowners"]
        land_costs = data.get("land_costs", pd.DataFrame())
        stats = get_landowner_stats()

        # 統計卡片
        c1, c2, c3, c4 = st.columns(4)
        c1.metric("地主總數", f"{stats.get('total', 0)} 人")
        c2.metric("同意率", f"{stats.get('agreement_rate', 0):.1f}%")
        c3.metric("總持分", f"{stats.get('total_share', 0):.1f}%")
        c4.metric("已簽署持分", f"{stats.get('agreed_share', 0):.1f}%")

        col_pie, col_table = st.columns([1, 2])

        with col_pie:
            fig_pie = create_landowner_pie_chart(stats)
            if fig_pie.data:
                st.plotly_chart(fig_pie, use_container_width=True)

        with col_table:
            if not landowners.empty:
                st.dataframe(landowners, use_container_width=True, height=300)
            else:
                st.info("尚無地主資料。")

        # 土地取得成本管理
        st.divider()
        st.subheader("土地取得成本")

        col_form, col_list = st.columns([1, 2])

        with col_form:
            with st.form("land_cost_form"):
                st.markdown("**新增土地取得記錄**")
                lc_name = st.text_input("地主姓名")
                lc_type = st.selectbox("取得方式", ["土地合作", "收購"])
                lc_area = st.number_input("土地面積 (坪)", min_value=0.0)
                lc_value = st.number_input("權利價值 (萬)", min_value=0.0)
                lc_price = st.number_input("收購價格 (萬)", min_value=0.0)
                lc_status = st.selectbox("付款狀態", ["待付款", "部分付款", "已付清"])

                if st.form_submit_button("新增記錄"):
                    if lc_name:
                        conn = sqlite3.connect(DB_PATH)
                        conn.execute(
                            """INSERT INTO land_costs
                            (地主姓名, 取得方式, 土地面積坪, 權利價值萬, 收購價格萬, 付款狀態)
                            VALUES (?, ?, ?, ?, ?, ?)""",
                            (lc_name, lc_type, lc_area, lc_value, lc_price, lc_status),
                        )
                        conn.commit()
                        conn.close()
                        st.rerun()

        with col_list:
            if not land_costs.empty:
                st.dataframe(land_costs, use_container_width=True, height=250)

                # 土地成本摘要
                coop_total = land_costs[land_costs["取得方式"] == "土地合作"]["權利價值萬"].sum()
                purchase_total = land_costs[land_costs["取得方式"] == "收購"]["收購價格萬"].sum()
                st.markdown(
                    f"**合作土地價值**: {coop_total:,.0f} 萬 | "
                    f"**收購支出**: {purchase_total:,.0f} 萬"
                )
            else:
                st.info("尚無土地取得記錄。")

    # ============ Tab 6: Risk & Scenario ============
    with tabs[5]:
        col_risk, col_scenario = st.columns(2)

        with col_risk:
            st.subheader("風險事件追蹤")
            risks = data["risks"]
            if not risks.empty:
                # 風險矩陣
                for _, r in risks.iterrows():
                    severity_color = {
                        "高": "🔴",
                        "中": "🟡",
                        "低": "🟢",
                    }.get(r.get("影響程度", ""), "⚪")

                    st.markdown(
                        f"{severity_color} **{r['風險類型']}**: {r['描述']}  \n"
                        f"發生機率: {r['發生機率']} | 影響金額: {r['影響金額']:,.0f} 萬"
                    )
                    st.caption(f"緩解措施: {r.get('緩解措施', 'N/A')}")
                    st.divider()
            else:
                st.info("尚無風險事件資料。")

        with col_scenario:
            st.subheader("情境模擬")

            if not df.empty:
                total_in = df[df["科目性質"] == "收入"]["金額"].sum()
                total_out = df[df["科目性質"] == "支出"]["金額"].sum()
                base_profit = total_in - total_out

                loans = data["loans"]
                loan_amount = loans["授信額度"].sum() if not loans.empty else 0
                base_rate = loans["年利率"].mean() if not loans.empty else 2.5

                # 敏感度分析
                sensitivity_df = run_sensitivity_analysis(
                    base_income=total_in,
                    base_expense=total_out,
                    loan_amount=loan_amount,
                    base_rate=base_rate,
                )

                st.markdown(f"**基準淨利**: {base_profit:,.0f} 萬")
                st.dataframe(
                    sensitivity_df.style.background_gradient(
                        subset=["變動幅度 (%)"],
                        cmap="RdYlGn_r",
                    ),
                    use_container_width=True,
                    height=400,
                )

                # 自訂情境
                st.divider()
                st.markdown("**自訂情境模擬**")

                sc_delay = st.slider("銷售延遲 (月)", 0, 24, 0)
                sc_cost = st.slider("成本上漲 (%)", 0, 30, 0)
                sc_rate = st.slider("利率變動 (%)", -1.0, 3.0, 0.0, 0.1)

                if st.button("計算影響"):
                    params = ScenarioParams(
                        name="自訂情境",
                        delay_months=sc_delay,
                        cost_increase_pct=sc_cost,
                        rate_adjustment=sc_rate,
                    )
                    result = simulate_scenario(
                        base_income=total_in,
                        base_expense=total_out,
                        loan_amount=loan_amount,
                        base_rate=base_rate,
                        monthly_cashflow=[],
                        params=params,
                    )

                    delta = result.profit_change
                    delta_pct = result.profit_change_pct

                    st.metric(
                        "調整後淨利",
                        f"{result.adjusted_profit:,.0f} 萬",
                        delta=f"{delta:+,.0f} 萬 ({delta_pct:+.1f}%)",
                        delta_color="inverse" if delta < 0 else "normal",
                    )
            else:
                st.info("請先載入專案數據。")

    # ============ Tab 7: Real Estate Price Lookup ============
    with tabs[6]:
        st.subheader("實價登錄查詢")
        st.caption("查詢土地、建物、車位的實價登錄資訊，協助評估專案價值")

        col_filter, col_stats = st.columns([1, 1])

        with col_filter:
            st.markdown("**查詢條件**")

            city = st.selectbox("城市", ["台北市", "新北市"])

            if city == "台北市":
                districts = list(TAIPEI_DISTRICTS.keys())
            else:
                districts = ["板橋區", "三重區", "中和區", "永和區", "新莊區"]

            district = st.selectbox("區域", districts)
            property_type = st.selectbox("物件類型", ["土地", "建物", "車位"])

            if st.button("查詢實價登錄", type="primary", use_container_width=True):
                st.session_state["price_query"] = {
                    "city": city,
                    "district": district,
                    "type": property_type,
                }

        with col_stats:
            st.markdown("**區域行情概覽**")

            if district:
                avg_prices = get_average_prices(city, district)
                stats = get_district_statistics(district)

                if avg_prices:
                    pm1, pm2, pm3 = st.columns(3)
                    pm1.metric(
                        "土地均價",
                        f"{avg_prices.get('土地平均單價', 0):.0f} 萬/坪",
                    )
                    pm2.metric(
                        "建物均價",
                        f"{avg_prices.get('建物平均單價', 0):.0f} 萬/坪",
                    )
                    pm3.metric(
                        "車位均價",
                        f"{avg_prices.get('車位平均總價', 0):.0f} 萬/位",
                    )

                    st.markdown(
                        f"**近期交易筆數**: 土地 {stats.get('land_transactions', 0)} 筆 | "
                        f"建物 {stats.get('building_transactions', 0)} 筆 | "
                        f"車位 {stats.get('parking_transactions', 0)} 筆"
                    )
                else:
                    st.info("該區域暫無資料")

        # 查詢結果
        st.divider()
        if "price_query" in st.session_state:
            query = st.session_state["price_query"]
            st.subheader(f"{query['city']} {query['district']} - {query['type']}實價登錄")

            result_df = query_real_prices(query["city"], query["district"], query["type"])

            if not result_df.empty:
                # 統計摘要
                result_df = result_df.sort_values("交易日期", ascending=False)
                
                avg_price = result_df["單價(萬/坪)"].mean()
                max_price = result_df["單價(萬/坪)"].max()
                min_price = result_df["單價(萬/坪)"].min()
                
                date_range = f"{result_df['交易日期'].min()} ~ {result_df['交易日期'].max()}"

                c1, c2, c3, c4, c5 = st.columns(5)
                c1.metric("查詢筆數", f"{len(result_df)} 筆")
                c2.metric("資料期間", date_range)
                c3.metric("平均單價", f"{avg_price:.0f} 萬/坪")
                c4.metric("最高單價", f"{max_price:.0f} 萬/坪")
                c5.metric("最低單價", f"{min_price:.0f} 萬/坪")

                # 結果表格
                st.dataframe(result_df, use_container_width=True, height=300)

                # 價格分布圖
                fig_price = go.Figure()
                fig_price.add_trace(
                    go.Bar(
                        x=result_df["地址"],
                        y=result_df["單價(萬/坪)"],
                        marker_color=COLORS["primary"],
                    )
                )
                fig_price.update_layout(
                    **PLOTLY_LAYOUT,
                    title_text=f"{query['type']}單價分布",
                    xaxis_title="地址",
                    yaxis_title="單價 (萬/坪)",
                )
                st.plotly_chart(fig_price, use_container_width=True)
            else:
                st.warning("查無符合條件的實價登錄資料")

        else:
            st.info("請選擇查詢條件後點擊「查詢實價登錄」按鈕")

        # 使用說明
        with st.expander("關於實價登錄資料"):
            st.markdown("""
            **資料來源說明**：
            - 本系統目前使用模擬資料進行展示
            - 實際資料可從 [內政部不動產交易實價查詢服務網](https://lvr.land.moi.gov.tw/) 取得
            - 開放資料下載：[內政部實價登錄批次資料](https://plvr.land.moi.gov.tw/DownloadOpenData)

            **資料欄位說明**：
            - **土地**：土地交易單價，以每坪計算
            - **建物**：房屋交易單價，含公設
            - **車位**：車位總價，不計入每坪單價

            **注意事項**：
            - 實價登錄資料有 30 天申報期，可能有時間落差
            - 交易條件不同會影響價格（如親友交易、法拍等）
            """)


if __name__ == "__main__":
    main()