#!/usr/bin/env python3
"""
MOPS 估價案件詳細報告
======================
查詢資料庫中的估價相關公告，生成詳細的 Telegram 通知。

使用方式：
    python detail_report.py              # 查詢最近一個交易日
    python detail_report.py --date 1141226   # 查詢指定日期（民國）
    python detail_report.py --days 3     # 查詢最近 3 天
    python detail_report.py --dry-run    # 只顯示不發送
"""

import sys
sys.stdout.reconfigure(encoding='utf-8')

import sqlite3
import argparse
import os
import requests
from pathlib import Path
from datetime import datetime, timedelta

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

# Telegram 設定
TELEGRAM_BOT_TOKEN = os.environ.get('TELEGRAM_BOT_TOKEN', '')
TELEGRAM_CHAT_ID = os.environ.get('TELEGRAM_CHAT_ID', '')


def escape_html(text: str) -> str:
    """轉義 HTML 特殊字元"""
    if not text:
        return ""
    return (text
            .replace('&', '&')
            .replace('<', '&lt;')
            .replace('>', '&gt;'))


def format_amount(amount: float) -> str:
    """格式化金額顯示"""
    if not amount:
        return ""
    if amount >= 100000000:  # 億
        return f"{amount/100000000:.2f} 億"
    elif amount >= 10000:  # 萬
        return f"{amount/10000:,.0f} 萬"
    else:
        return f"{amount:,.0f}"


def roc_to_date_str(roc_date: str) -> str:
    """民國日期轉西元顯示（1141226 → 2025/12/26）"""
    if not roc_date or len(roc_date) != 7:
        return roc_date
    year = int(roc_date[:3]) + 1911
    month = roc_date[3:5]
    day = roc_date[5:7]
    return f"{year}/{month}/{day}"


def get_weekday_str(roc_date: str) -> str:
    """取得星期幾"""
    if not roc_date or len(roc_date) != 7:
        return ""
    year = int(roc_date[:3]) + 1911
    month = int(roc_date[3:5])
    day = int(roc_date[5:7])
    weekday_names = ['一', '二', '三', '四', '五', '六', '日']
    try:
        d = datetime(year, month, day)
        return weekday_names[d.weekday()]
    except:
        return ""


def send_telegram_message(message: str) -> bool:
    """發送 Telegram 訊息"""
    if not TELEGRAM_BOT_TOKEN or not TELEGRAM_CHAT_ID:
        print("⚠️ Telegram 設定不完整")
        return False

    try:
        url = f"https://api.telegram.org/bot{TELEGRAM_BOT_TOKEN}/sendMessage"
        payload = {
            'chat_id': TELEGRAM_CHAT_ID,
            'text': message,
            'parse_mode': 'HTML',
            'disable_web_page_preview': True,
        }
        response = requests.post(url, json=payload, timeout=10)
        response.raise_for_status()
        return True
    except Exception as e:
        print(f"❌ Telegram 發送失敗: {e}")
        return False


def query_appraiser_announcements(conn, date_filter: str = None, days: int = 1):
    """查詢估價相關公告（優先使用 LLM 結構化資料）"""
    cursor = conn.cursor()

    if date_filter:
        # 指定日期
        cursor.execute('''
            SELECT DISTINCT announce_date FROM announcements
            WHERE has_appraiser_info = 1 AND announce_date = ?
            ORDER BY announce_date DESC
        ''', (date_filter,))
    else:
        # 最近 N 天有估價公告的日期
        cursor.execute('''
            SELECT DISTINCT announce_date FROM announcements
            WHERE has_appraiser_info = 1
            ORDER BY announce_date DESC
            LIMIT ?
        ''', (days,))

    dates = [row[0] for row in cursor.fetchall()]

    results = {}
    for date in dates:
        # 新增 parsed_data 和 category 欄位
        cursor.execute('''
            SELECT
                a.announce_date,
                a.announce_time,
                a.stock_code,
                a.company_name,
                a.subject,
                a.content,
                r.appraiser_firm,
                r.appraiser_names,
                r.appraiser_licenses,
                r.appraisal_amount,
                r.notes,
                a.parsed_data,
                a.category,
                a.subcategory
            FROM announcements a
            LEFT JOIN appraiser_records r ON a.id = r.announcement_id
            WHERE a.has_appraiser_info = 1 AND a.announce_date = ?
            ORDER BY r.appraisal_amount DESC NULLS LAST, a.announce_time DESC
        ''', (date,))
        results[date] = cursor.fetchall()

    return results


def extract_key_info(content: str) -> dict:
    """從公告內容提取關鍵資訊（Fallback 用，優先使用 LLM 結構化資料）"""
    import re
    info = {}

    # 交易總金額
    match = re.search(r'交易總金額[：:]\s*([^\n]+)', content)
    if match:
        info['total_amount'] = match.group(1).strip()[:60]

    # 標的物
    match = re.search(r'標的物之名稱及性質[^:：]*[：:]\s*([^\n]+)', content)
    if match:
        info['target'] = match.group(1).strip()[:80]

    # 取得目的
    match = re.search(r'取得之具體目的[^:：]*[：:]\s*([^\n]+)', content)
    if match:
        info['purpose'] = match.group(1).strip()[:50]

    # 交易相對人
    match = re.search(r'交易相對人[：:]\s*([^\n與]+)', content)
    if match:
        info['counterparty'] = match.group(1).strip()[:30]

    # 關係人交易
    if '本次交易為關係人交易:是' in content or '本次交易為關係人交易：是' in content:
        info['is_related_party'] = True

    # 每坪租金（租賃案）
    match = re.search(r'每單位價格[：:]\s*([^\n]+)', content)
    if match:
        info['unit_price'] = match.group(1).strip()[:40]

    # 估價結果（都更案）
    match = re.search(r'估價結果[：:]\s*([^\n]+)', content)
    if match and '不適用' not in match.group(1):
        info['appraisal_result'] = match.group(1).strip()[:60]

    # 預計投入金額
    match = re.search(r'預計參與投入之金額[：:]\s*([^\n]+)', content)
    if match:
        info['investment'] = match.group(1).strip()[:40]

    return info


def get_info_from_parsed_data(parsed_data_str: str) -> dict:
    """從 LLM 結構化資料提取關鍵資訊"""
    if not parsed_data_str:
        return {}

    try:
        import json
        data = json.loads(parsed_data_str)

        info = {
            'summary': data.get('summary'),
            'category': data.get('category'),
            'subcategory': data.get('subcategory'),
        }

        # 交易資訊
        if 'transaction' in data and data['transaction']:
            tx = data['transaction']
            if tx.get('amount'):
                info['total_amount'] = format_amount(tx['amount']) + '元'
            if tx.get('unit_price'):
                info['unit_price'] = tx['unit_price']
            if tx.get('type'):
                info['tx_type'] = tx['type']

        # 標的物資訊
        if 'target' in data and data['target']:
            target = data['target']
            parts = []
            if target.get('type'):
                parts.append(target['type'])
            if target.get('location'):
                parts.append(target['location'])
            if target.get('area'):
                parts.append(target['area'])
            if parts:
                info['target'] = ' / '.join(parts)

        # 交易對象（新結構：counterparties 陣列）
        if 'counterparties' in data and data['counterparties']:
            cps = data['counterparties']
            # 取第一個交易對象的名稱
            if cps and len(cps) > 0:
                info['counterparty'] = cps[0].get('name', '')
                # 檢查是否有任何關係人
                for cp in cps:
                    if cp.get('is_related_party'):
                        info['is_related_party'] = True
                        break
        # 向下相容舊結構
        elif 'counterparty' in data and data['counterparty']:
            cp = data['counterparty']
            info['counterparty'] = cp.get('name', '')
            if cp.get('is_related_party'):
                info['is_related_party'] = True

        # 估價資訊（新結構：appraisers 是物件陣列，每個包含 firm/names/amount）
        if 'appraisal' in data and data['appraisal']:
            ap = data['appraisal']
            info['has_appraiser'] = ap.get('has_appraiser', False)

            if ap.get('appraisers') and isinstance(ap['appraisers'], list):
                appraisers = ap['appraisers']

                # 檢查是新結構（物件陣列）還是舊結構（字串陣列）
                if appraisers and isinstance(appraisers[0], dict):
                    # 新結構：每個元素是 {firm, names, amount}
                    firms = []
                    all_names = []
                    for appraiser in appraisers[:3]:  # 最多取 3 個事務所
                        if appraiser.get('firm'):
                            firms.append(appraiser['firm'])
                        if appraiser.get('names'):
                            # names 是該事務所的估價師列表
                            all_names.extend(appraiser['names'])
                    if firms:
                        info['appraiser_firm'] = '; '.join(firms)
                    if all_names:
                        info['appraiser_names'] = '; '.join(all_names[:4])  # 最多顯示 4 位
                else:
                    # 舊結構：字串陣列
                    info['appraiser_names'] = '; '.join(appraisers[:2])

            # 舊結構的 firms 欄位（向下相容）
            if ap.get('firms') and not info.get('appraiser_firm'):
                info['appraiser_firm'] = '; '.join(ap['firms'][:2])

            if ap.get('result'):
                info['appraisal_result'] = ap['result']

        # 重要條款
        if 'key_terms' in data and data['key_terms']:
            kt = data['key_terms']
            if kt.get('lease_period'):
                info['lease_period'] = kt['lease_period']
            if kt.get('monthly_rent'):
                info['monthly_rent'] = format_amount(kt['monthly_rent']) + '元/月'
            if kt.get('build_ratio'):
                info['build_ratio'] = kt['build_ratio']
            if kt.get('purpose'):
                info['purpose'] = kt['purpose']

        return info

    except Exception:
        return {}


def build_detail_report(results: dict) -> str:
    """建構詳細報告（優先使用 LLM 結構化資料）"""
    if not results:
        return "📭 查詢期間無估價相關公告"

    msg = "📊 <b>MOPS 估價案件詳細報告</b>\n"
    msg += f"⏰ 產生時間：{datetime.now().strftime('%Y-%m-%d %H:%M')}\n"
    msg += "─" * 28 + "\n"

    total_cases = sum(len(rows) for rows in results.values())
    total_amount = 0

    for date, rows in sorted(results.items(), reverse=True):
        date_str = roc_to_date_str(date)
        weekday = get_weekday_str(date)

        msg += f"\n📅 <b>{date_str}（週{weekday}）</b>\n"
        msg += f"共 {len(rows)} 筆估價相關公告\n"
        msg += "─" * 28 + "\n"

        # 分類：有實際估價 vs 格式欄位但未估價
        real_appraisal = []
        no_appraisal = []

        for row in rows:
            # 新的欄位順序（增加了 parsed_data, category, subcategory）
            (announce_date, announce_time, stock_code, company_name,
             subject, content, firm, names, licenses, amount, notes,
             parsed_data, category, subcategory) = row

            if firm or names or amount:
                real_appraisal.append(row)
            else:
                no_appraisal.append(row)

        # === 有實際委託估價的案件 ===
        if real_appraisal:
            msg += "\n🏛 <b>有委託估價</b>\n"

            for row in real_appraisal:
                (announce_date, announce_time, stock_code, company_name,
                 subject, content, firm, names, licenses, amount, notes,
                 parsed_data, category, subcategory) = row

                # 優先使用 LLM 結構化資料，否則 fallback 到 regex
                if parsed_data:
                    key_info = get_info_from_parsed_data(parsed_data)
                else:
                    key_info = extract_key_info(content)

                msg += f"\n<b>[{escape_html(stock_code)}] {escape_html(company_name)}</b>\n"

                # 如果有 LLM 摘要，優先顯示
                if key_info.get('summary'):
                    msg += f"📋 {escape_html(key_info['summary'])}\n"
                else:
                    subject_short = subject[:45] + "..." if len(subject) > 45 else subject
                    msg += f"📋 {escape_html(subject_short)}\n"

                # 類型標籤（從 LLM）
                if subcategory:
                    msg += f"🏷 {escape_html(subcategory)}\n"

                # 金額
                if amount:
                    total_amount += amount
                    msg += f"💰 <b>{format_amount(amount)}元</b>\n"
                elif key_info.get('total_amount'):
                    msg += f"💰 {escape_html(key_info['total_amount'])}\n"

                # 標的物（從 LLM）
                if key_info.get('target'):
                    msg += f"🏠 {escape_html(key_info['target'][:50])}\n"

                # 估價事務所（優先使用 LLM）
                if key_info.get('appraiser_firm'):
                    msg += f"🏢 {escape_html(key_info['appraiser_firm'][:35])}\n"
                elif firm:
                    firm_short = firm[:35] + "..." if len(firm) > 35 else firm
                    msg += f"🏢 {escape_html(firm_short)}\n"

                # 估價師（優先使用 LLM）
                if key_info.get('appraiser_names'):
                    msg += f"👤 {escape_html(key_info['appraiser_names'])}\n"
                elif names:
                    msg += f"👤 {escape_html(names)}\n"

                # 關係人標記
                if key_info.get('is_related_party'):
                    cp_name = key_info.get('counterparty', '')
                    if cp_name:
                        msg += f"⚠️ 關係人：{escape_html(cp_name[:20])}\n"
                    else:
                        msg += "⚠️ 關係人交易\n"

                # 重要條款（從 LLM）
                extra_info = []
                if key_info.get('lease_period'):
                    extra_info.append(f"租期 {key_info['lease_period']}")
                if key_info.get('monthly_rent'):
                    extra_info.append(f"月租 {key_info['monthly_rent']}")
                if key_info.get('build_ratio'):
                    extra_info.append(key_info['build_ratio'])
                if key_info.get('appraisal_result'):
                    extra_info.append(key_info['appraisal_result'][:30])
                if extra_info:
                    msg += f"📝 {escape_html(' | '.join(extra_info))}\n"
                elif notes:
                    msg += f"📝 {escape_html(notes[:50])}\n"

        # === 格式欄位但未實際估價 ===
        if no_appraisal:
            msg += f"\n📄 <b>未委託估價</b>（{len(no_appraisal)} 筆）\n"

            for row in no_appraisal[:5]:  # 最多顯示 5 筆
                (announce_date, announce_time, stock_code, company_name,
                 subject, content, firm, names, licenses, amount, notes,
                 parsed_data, category, subcategory) = row

                # 優先使用 LLM 結構化資料
                if parsed_data:
                    key_info = get_info_from_parsed_data(parsed_data)
                else:
                    key_info = extract_key_info(content)

                msg += f"• [{escape_html(stock_code)}] {escape_html(company_name)}\n"

                # 優先顯示 LLM 摘要
                if key_info.get('summary'):
                    msg += f"  {escape_html(key_info['summary'][:40])}\n"
                else:
                    subject_short = subject[:30] + "..." if len(subject) > 30 else subject
                    msg += f"  {escape_html(subject_short)}\n"

                if key_info.get('unit_price'):
                    msg += f"  💵 {escape_html(key_info['unit_price'][:25])}\n"
                if key_info.get('is_related_party'):
                    msg += "  ⚠️ 關係人\n"

            if len(no_appraisal) > 5:
                msg += f"  ...還有 {len(no_appraisal) - 5} 筆\n"

    # === 統計 ===
    msg += "\n" + "─" * 28 + "\n"
    msg += f"📈 <b>統計</b>\n"
    msg += f"• 總案件數：{total_cases} 筆\n"
    if total_amount > 0:
        msg += f"• 有金額案件總計：{format_amount(total_amount)}元\n"

    msg += f'\n🔗 <a href="https://mops.twse.com.tw/">MOPS 公開資訊觀測站</a>'

    return msg


def main():
    parser = argparse.ArgumentParser(description='MOPS 估價案件詳細報告')
    parser.add_argument('--date', help='指定日期（民國格式，如 1141226）')
    parser.add_argument('--days', type=int, default=1, help='查詢最近 N 個有估價公告的交易日（預設 1）')
    parser.add_argument('--dry-run', action='store_true', help='只顯示報告，不發送 Telegram')

    args = parser.parse_args()

    if not DB_PATH.exists():
        print(f"❌ 資料庫不存在：{DB_PATH}")
        sys.exit(1)

    conn = sqlite3.connect(DB_PATH)

    # 查詢資料
    results = query_appraiser_announcements(
        conn,
        date_filter=args.date,
        days=args.days
    )

    # 建構報告
    report = build_detail_report(results)

    # 輸出
    print(report.replace('<b>', '').replace('</b>', '')
               .replace('<a href="', '').replace('">', ' ').replace('</a>', ''))
    print()

    # 發送 Telegram
    if not args.dry_run:
        print("─" * 40)
        if send_telegram_message(report):
            print("✅ Telegram 發送成功")
        else:
            print("❌ Telegram 發送失敗")
    else:
        print("─" * 40)
        print("🔍 Dry-run 模式，未發送 Telegram")

    conn.close()


if __name__ == "__main__":
    main()