#!/usr/bin/env python3
"""
MOPS 重大訊息瀏覽器
==================
Streamlit 前端界面，用於瀏覽和分析 MOPS 重大訊息資料庫。

執行方式：
    streamlit run app.py
"""

import streamlit as st
import pandas as pd
import sqlite3
from pathlib import Path
from datetime import datetime, timedelta
import plotly.express as px
import plotly.graph_objects as go

# ===== 設定 =====
DB_PATH = Path(__file__).parent / "data" / "mops_announcements.db"

# 頁面設定
st.set_page_config(
    page_title="MOPS 重大訊息瀏覽器",
    page_icon="📊",
    layout="wide",
    initial_sidebar_state="expanded"
)

# ===== 資料庫連線 =====
@st.cache_resource
def get_connection():
    """取得資料庫連線"""
    return sqlite3.connect(DB_PATH, check_same_thread=False)

@st.cache_data(ttl=300)  # 快取 5 分鐘
def load_announcements():
    """載入所有公告"""
    conn = get_connection()
    df = pd.read_sql_query("""
        SELECT
            id,
            publish_date,
            announce_date,
            announce_time,
            stock_code,
            company_name,
            subject,
            content,
            has_appraiser_info,
            is_real_estate_related,
            created_at
        FROM announcements
        ORDER BY announce_date DESC, announce_time DESC
    """, conn)

    # 轉換民國日期為西元
    def convert_roc_date(roc_date):
        if not roc_date or len(roc_date) < 7:
            return None
        try:
            year = int(roc_date[:3]) + 1911
            month = int(roc_date[3:5])
            day = int(roc_date[5:7])
            return datetime(year, month, day)
        except:
            return None

    df['announce_date_tw'] = df['announce_date']  # 保留民國日期
    df['announce_date'] = df['announce_date'].apply(convert_roc_date)

    return df

@st.cache_data(ttl=300)
def load_appraiser_records():
    """載入估價師記錄"""
    conn = get_connection()
    df = pd.read_sql_query("""
        SELECT
            r.*,
            a.stock_code,
            a.company_name,
            a.subject,
            a.announce_date
        FROM appraiser_records r
        JOIN announcements a ON r.announcement_id = a.id
        ORDER BY a.announce_date DESC
    """, conn)
    return df

@st.cache_data(ttl=300)
def load_collection_log():
    """載入收集日誌"""
    conn = get_connection()
    df = pd.read_sql_query("""
        SELECT * FROM collection_log
        ORDER BY collect_date DESC
    """, conn)
    return df

# ===== 側邊欄 =====
st.sidebar.title("📊 MOPS 瀏覽器")
page = st.sidebar.radio(
    "選擇頁面",
    ["🏠 總覽", "📋 公告列表", "🔍 原始資料", "🏛 估價師追蹤", "📈 統計分析", "⚙️ 系統狀態"]
)

# ===== 頁面：總覽 =====
if page == "🏠 總覽":
    st.title("🏠 MOPS 重大訊息總覽")

    df = load_announcements()
    appraiser_df = load_appraiser_records()

    # 統計卡片
    col1, col2, col3, col4 = st.columns(4)

    with col1:
        st.metric("總公告數", f"{len(df):,}")

    with col2:
        appraiser_count = df['has_appraiser_info'].sum()
        st.metric("估價師相關", f"{appraiser_count:,}")

    with col3:
        real_estate_count = df['is_real_estate_related'].sum()
        st.metric("不動產相關", f"{real_estate_count:,}")

    with col4:
        days = df['announce_date'].nunique()
        st.metric("收集天數", f"{days}")

    st.divider()

    # 最新公告
    col1, col2 = st.columns(2)

    with col1:
        st.subheader("📢 最新估價師相關公告")
        recent_appraiser = df[df['has_appraiser_info'] == 1].head(10)
        for _, row in recent_appraiser.iterrows():
            date_str = row['announce_date'].strftime('%m/%d') if row['announce_date'] else '?'
            st.markdown(f"**[{row['stock_code']}] {row['company_name']}** ({date_str})")
            st.caption(row['subject'][:80])

    with col2:
        st.subheader("🏢 最新不動產相關公告")
        recent_real_estate = df[(df['is_real_estate_related'] == 1) & (df['has_appraiser_info'] == 0)].head(10)
        for _, row in recent_real_estate.iterrows():
            date_str = row['announce_date'].strftime('%m/%d') if row['announce_date'] else '?'
            st.markdown(f"**[{row['stock_code']}] {row['company_name']}** ({date_str})")
            st.caption(row['subject'][:80])

# ===== 頁面：公告列表 =====
elif page == "📋 公告列表":
    st.title("📋 公告列表")

    df = load_announcements()

    # 篩選器
    st.sidebar.subheader("篩選條件")

    # 日期範圍
    if df['announce_date'].notna().any():
        min_date = df['announce_date'].min()
        max_date = df['announce_date'].max()

        # 確保預設開始日期不早於 min_date
        default_start = max(min_date, max_date - timedelta(days=7))

        date_range = st.sidebar.date_input(
            "日期範圍",
            value=(default_start, max_date),
            min_value=min_date,
            max_value=max_date
        )

        if len(date_range) == 2:
            start_date, end_date = date_range
            mask = (df['announce_date'] >= pd.Timestamp(start_date)) & (df['announce_date'] <= pd.Timestamp(end_date))
            df = df[mask]

    # 公告類型
    announcement_type = st.sidebar.multiselect(
        "公告類型",
        ["全部", "估價師相關", "不動產相關", "一般公告"],
        default=["全部"]
    )

    if "全部" not in announcement_type:
        type_mask = pd.Series([False] * len(df))
        if "估價師相關" in announcement_type:
            type_mask |= (df['has_appraiser_info'] == 1)
        if "不動產相關" in announcement_type:
            type_mask |= (df['is_real_estate_related'] == 1)
        if "一般公告" in announcement_type:
            type_mask |= ((df['has_appraiser_info'] == 0) & (df['is_real_estate_related'] == 0))
        df = df[type_mask]

    # 搜尋
    search_term = st.sidebar.text_input("🔍 搜尋（公司/股票代號/主旨）")
    if search_term:
        search_mask = (
            df['company_name'].str.contains(search_term, case=False, na=False) |
            df['stock_code'].str.contains(search_term, case=False, na=False) |
            df['subject'].str.contains(search_term, case=False, na=False)
        )
        df = df[search_mask]

    # 顯示結果
    st.info(f"共找到 {len(df):,} 筆公告")

    # 準備顯示的資料
    display_df = df[['announce_date', 'stock_code', 'company_name', 'subject', 'has_appraiser_info', 'is_real_estate_related']].copy()
    display_df['announce_date'] = display_df['announce_date'].dt.strftime('%Y-%m-%d')
    display_df.columns = ['日期', '股票代號', '公司名稱', '主旨', '估價師', '不動產']
    display_df['估價師'] = display_df['估價師'].map({1: '✅', 0: ''})
    display_df['不動產'] = display_df['不動產'].map({1: '🏢', 0: ''})

    # 使用 dataframe 顯示
    st.dataframe(
        display_df,
        use_container_width=True,
        height=600,
        column_config={
            "日期": st.column_config.TextColumn(width="small"),
            "股票代號": st.column_config.TextColumn(width="small"),
            "公司名稱": st.column_config.TextColumn(width="medium"),
            "主旨": st.column_config.TextColumn(width="large"),
            "估價師": st.column_config.TextColumn(width="small"),
            "不動產": st.column_config.TextColumn(width="small"),
        }
    )

    # 詳細檢視
    st.divider()
    st.subheader("📄 公告詳情")

    if len(df) > 0:
        selected_idx = st.selectbox(
            "選擇公告",
            range(len(df)),
            format_func=lambda x: f"[{df.iloc[x]['stock_code']}] {df.iloc[x]['company_name']} - {df.iloc[x]['subject'][:50]}"
        )

        selected = df.iloc[selected_idx]

        col1, col2 = st.columns([1, 3])
        with col1:
            st.markdown(f"**股票代號：** {selected['stock_code']}")
            st.markdown(f"**公司名稱：** {selected['company_name']}")
            if selected['announce_date']:
                st.markdown(f"**發言日期：** {selected['announce_date'].strftime('%Y-%m-%d')}")

        with col2:
            st.markdown(f"**主旨：** {selected['subject']}")

        with st.expander("📝 完整說明", expanded=True):
            st.text(selected['content'] if selected['content'] else "無")

# ===== 頁面：估價師追蹤 =====
elif page == "🏛 估價師追蹤":
    st.title("🏛 估價師事務所追蹤")

    appraiser_df = load_appraiser_records()
    announcements_df = load_announcements()

    if len(appraiser_df) == 0:
        st.warning("尚無估價師記錄資料")
    else:
        # 事務所統計
        st.subheader("📊 事務所案件統計")

        # 按事務所分組
        firm_stats = appraiser_df.groupby('appraiser_firm').agg({
            'id': 'count',
            'appraisal_amount': 'sum',
            'company_name': 'nunique'
        }).reset_index()
        firm_stats.columns = ['事務所', '案件數', '總金額', '服務公司數']
        firm_stats = firm_stats.sort_values('案件數', ascending=False)
        firm_stats = firm_stats[firm_stats['事務所'].notna()]

        if len(firm_stats) > 0:
            # 圖表
            fig = px.bar(
                firm_stats.head(15),
                x='事務所',
                y='案件數',
                title='前 15 大估價師事務所（依案件數）',
                color='案件數',
                color_continuous_scale='Blues'
            )
            fig.update_layout(xaxis_tickangle=-45)
            st.plotly_chart(fig, use_container_width=True)

            # 表格
            st.dataframe(firm_stats, use_container_width=True)

        st.divider()

        # 詳細記錄
        st.subheader("📋 估價師記錄詳情")

        # 篩選
        firms = appraiser_df['appraiser_firm'].dropna().unique().tolist()
        selected_firm = st.selectbox("選擇事務所", ["全部"] + firms)

        if selected_firm != "全部":
            filtered_df = appraiser_df[appraiser_df['appraiser_firm'] == selected_firm]
        else:
            filtered_df = appraiser_df

        display_appraiser = filtered_df[['stock_code', 'company_name', 'appraiser_firm', 'appraiser_names', 'appraisal_amount', 'notes']].copy()
        display_appraiser.columns = ['股票代號', '公司', '事務所', '估價師', '金額', '備註']

        # 格式化金額
        def format_amount(x):
            if pd.isna(x) or x == 0:
                return ''
            if x >= 100000000:
                return f'{x/100000000:.2f} 億'
            elif x >= 10000:
                return f'{x/10000:,.0f} 萬'
            else:
                return f'{x:,.0f}'

        display_appraiser['金額'] = display_appraiser['金額'].apply(format_amount)

        st.dataframe(display_appraiser, use_container_width=True, height=400)

# ===== 頁面：統計分析 =====
elif page == "📈 統計分析":
    st.title("📈 統計分析")

    df = load_announcements()
    appraiser_df = load_appraiser_records()

    # 每日公告數量
    st.subheader("📅 每日公告數量趨勢")

    daily_stats = df.groupby(df['announce_date'].dt.date).agg({
        'id': 'count',
        'has_appraiser_info': 'sum',
        'is_real_estate_related': 'sum'
    }).reset_index()
    daily_stats.columns = ['日期', '總公告', '估價師相關', '不動產相關']

    fig = go.Figure()
    fig.add_trace(go.Scatter(x=daily_stats['日期'], y=daily_stats['總公告'], name='總公告', mode='lines+markers'))
    fig.add_trace(go.Scatter(x=daily_stats['日期'], y=daily_stats['估價師相關'], name='估價師相關', mode='lines+markers'))
    fig.add_trace(go.Scatter(x=daily_stats['日期'], y=daily_stats['不動產相關'], name='不動產相關', mode='lines+markers'))
    fig.update_layout(title='每日公告數量趨勢', xaxis_title='日期', yaxis_title='公告數')
    st.plotly_chart(fig, use_container_width=True)

    st.divider()

    # 公司排行
    col1, col2 = st.columns(2)

    with col1:
        st.subheader("🏆 公告最多的公司")
        company_stats = df.groupby(['stock_code', 'company_name']).size().reset_index(name='公告數')
        company_stats = company_stats.sort_values('公告數', ascending=False).head(15)

        fig = px.bar(
            company_stats,
            x='company_name',
            y='公告數',
            title='前 15 大發佈公司',
            color='公告數',
            color_continuous_scale='Oranges'
        )
        fig.update_layout(xaxis_tickangle=-45, xaxis_title='')
        st.plotly_chart(fig, use_container_width=True)

    with col2:
        st.subheader("💰 估價金額分佈")
        if len(appraiser_df) > 0 and appraiser_df['appraisal_amount'].notna().any():
            amounts = appraiser_df['appraisal_amount'].dropna()
            amounts = amounts[amounts > 0]

            if len(amounts) > 0:
                fig = px.histogram(
                    amounts / 100000000,  # 轉換為億
                    nbins=20,
                    title='估價金額分佈（億元）',
                    labels={'value': '金額（億元）', 'count': '次數'}
                )
                st.plotly_chart(fig, use_container_width=True)
            else:
                st.info("無估價金額資料")
        else:
            st.info("無估價金額資料")

    st.divider()

    # 公告類型分佈
    st.subheader("📊 公告類型分佈")

    type_counts = {
        '估價師相關': df['has_appraiser_info'].sum(),
        '不動產相關（無估價師）': ((df['is_real_estate_related'] == 1) & (df['has_appraiser_info'] == 0)).sum(),
        '一般公告': ((df['has_appraiser_info'] == 0) & (df['is_real_estate_related'] == 0)).sum()
    }

    fig = px.pie(
        values=list(type_counts.values()),
        names=list(type_counts.keys()),
        title='公告類型分佈',
        color_discrete_sequence=px.colors.qualitative.Set2
    )
    st.plotly_chart(fig, use_container_width=True)

# ===== 頁面：原始資料 =====
elif page == "🔍 原始資料":
    st.title("🔍 原始資料檢視")
    st.info("此頁面顯示未經處理的原始公告內容，方便檢視資料品質")

    conn = get_connection()

    # 篩選選項
    col1, col2, col3 = st.columns(3)

    with col1:
        filter_type = st.selectbox(
            "篩選類型",
            ["全部公告", "估價師相關", "不動產相關", "估價師相關（有解析記錄）"]
        )

    with col2:
        # 取得日期範圍
        dates_df = pd.read_sql_query("SELECT DISTINCT announce_date FROM announcements ORDER BY announce_date DESC", conn)
        dates = dates_df['announce_date'].tolist()
        selected_date = st.selectbox("選擇日期（民國）", ["全部"] + dates)

    with col3:
        search = st.text_input("搜尋關鍵字")

    # 建立查詢
    query = "SELECT * FROM announcements WHERE 1=1"
    params = []

    if filter_type == "估價師相關":
        query += " AND has_appraiser_info = 1"
    elif filter_type == "不動產相關":
        query += " AND is_real_estate_related = 1"
    elif filter_type == "估價師相關（有解析記錄）":
        query += " AND id IN (SELECT DISTINCT announcement_id FROM appraiser_records)"

    if selected_date != "全部":
        query += " AND announce_date = ?"
        params.append(selected_date)

    if search:
        query += " AND (company_name LIKE ? OR subject LIKE ? OR content LIKE ?)"
        params.extend([f"%{search}%", f"%{search}%", f"%{search}%"])

    query += " ORDER BY announce_date DESC, announce_time DESC LIMIT 100"

    raw_df = pd.read_sql_query(query, conn, params=params)

    st.caption(f"顯示 {len(raw_df)} 筆資料（最多 100 筆）")

    # 顯示公告列表
    if len(raw_df) > 0:
        for idx, row in raw_df.iterrows():
            with st.expander(
                f"**[{row['stock_code']}] {row['company_name']}** - {row['subject'][:60]}... "
                f"({'📊 估價師' if row['has_appraiser_info'] else ''}"
                f"{'🏢 不動產' if row['is_real_estate_related'] else ''})"
            ):
                col1, col2 = st.columns([1, 2])

                with col1:
                    st.markdown("### 基本資訊")
                    st.markdown(f"**ID:** {row['id']}")
                    st.markdown(f"**股票代號:** {row['stock_code']}")
                    st.markdown(f"**公司名稱:** {row['company_name']}")
                    st.markdown(f"**出表日期:** {row['publish_date']}")
                    st.markdown(f"**發言日期:** {row['announce_date']}")
                    st.markdown(f"**發言時間:** {row['announce_time']}")
                    st.markdown(f"**符合條款:** {row['regulation']}")
                    st.markdown(f"**事實發生日:** {row['event_date']}")

                    st.markdown("### 分類標記")
                    st.markdown(f"**估價師相關:** {'✅ 是' if row['has_appraiser_info'] else '❌ 否'}")
                    st.markdown(f"**不動產相關:** {'✅ 是' if row['is_real_estate_related'] else '❌ 否'}")

                with col2:
                    st.markdown("### 主旨")
                    st.code(row['subject'], language=None)

                    st.markdown("### 完整說明（原始內容）")
                    st.text_area(
                        "原始內容",
                        value=row['content'] if row['content'] else "(無)",
                        height=400,
                        key=f"content_{row['id']}",
                        label_visibility="collapsed"
                    )

                # 如果有估價師解析記錄，也顯示
                if row['has_appraiser_info']:
                    appraiser_records = pd.read_sql_query(
                        "SELECT * FROM appraiser_records WHERE announcement_id = ?",
                        conn,
                        params=[row['id']]
                    )
                    if len(appraiser_records) > 0:
                        st.markdown("### 📊 已解析的估價師資訊")
                        for _, ar in appraiser_records.iterrows():
                            st.markdown(f"- **事務所:** {ar['appraiser_firm'] or '(未解析)'}")
                            st.markdown(f"- **估價師:** {ar['appraiser_names'] or '(未解析)'}")
                            st.markdown(f"- **證書字號:** {ar['appraiser_licenses'] or '(未解析)'}")
                            st.markdown(f"- **金額:** {ar['appraisal_amount']:,} 元" if ar['appraisal_amount'] else "- **金額:** (未解析)")
                            st.markdown(f"- **備註:** {ar['notes'] or '(無)'}")
                    else:
                        st.warning("此公告標記為估價師相關，但未成功解析出估價師資訊")

    else:
        st.warning("沒有符合條件的資料")

# ===== 頁面：系統狀態 =====
elif page == "⚙️ 系統狀態":
    st.title("⚙️ 系統狀態")

    # 資料庫資訊
    st.subheader("💾 資料庫資訊")

    col1, col2 = st.columns(2)

    with col1:
        st.markdown(f"**資料庫路徑：** `{DB_PATH}`")
        if DB_PATH.exists():
            size_mb = DB_PATH.stat().st_size / (1024 * 1024)
            st.markdown(f"**檔案大小：** {size_mb:.2f} MB")
            st.success("資料庫連線正常")
        else:
            st.error("資料庫檔案不存在")

    with col2:
        df = load_announcements()
        if len(df) > 0:
            st.markdown(f"**最早資料：** {df['announce_date'].min().strftime('%Y-%m-%d') if df['announce_date'].notna().any() else 'N/A'}")
            st.markdown(f"**最新資料：** {df['announce_date'].max().strftime('%Y-%m-%d') if df['announce_date'].notna().any() else 'N/A'}")

    st.divider()

    # 收集日誌
    st.subheader("📜 收集日誌")

    log_df = load_collection_log()

    if len(log_df) > 0:
        display_log = log_df[['collect_date', 'csv_date', 'total_count', 'appraiser_count', 'status']].copy()
        display_log.columns = ['收集日期', 'CSV日期', '總筆數', '估價相關', '狀態']
        display_log['狀態'] = display_log['狀態'].map({'success': '✅ 成功', 'failed': '❌ 失敗'})

        st.dataframe(display_log.head(20), use_container_width=True)
    else:
        st.info("尚無收集日誌")

    st.divider()

    # 操作按鈕
    st.subheader("🔧 操作")

    col1, col2 = st.columns(2)

    with col1:
        if st.button("🔄 清除快取並重新載入"):
            st.cache_data.clear()
            st.rerun()

    with col2:
        if st.button("📊 顯示統計"):
            conn = get_connection()
            cursor = conn.cursor()

            cursor.execute("SELECT COUNT(*) FROM announcements")
            total = cursor.fetchone()[0]

            cursor.execute("SELECT COUNT(*) FROM announcements WHERE has_appraiser_info = 1")
            appraiser = cursor.fetchone()[0]

            cursor.execute("SELECT COUNT(*) FROM appraiser_records")
            records = cursor.fetchone()[0]

            st.info(f"總公告: {total:,} | 估價相關: {appraiser:,} | 估價師記錄: {records:,}")

# ===== 頁尾 =====
st.sidebar.divider()
st.sidebar.caption("MOPS 重大訊息瀏覽器 v1.0")
st.sidebar.caption("資料來源：證交所公開資訊觀測站")