#!/usr/bin/env python3
"""Template 4: 混合版 - 從 DB 和原始內容都嘗試提取資訊"""
import sys
sys.stdout.reconfigure(encoding='utf-8')

import sqlite3
import requests
import subprocess
import re
from datetime import datetime

# 讀取環境變數
result = subprocess.run(['powershell', '-Command', "[Environment]::GetEnvironmentVariable('TELEGRAM_BOT_TOKEN', 'User')"], capture_output=True, text=True)
TELEGRAM_BOT_TOKEN = result.stdout.strip()
result = subprocess.run(['powershell', '-Command', "[Environment]::GetEnvironmentVariable('TELEGRAM_CHAT_ID', 'User')"], capture_output=True, text=True)
TELEGRAM_CHAT_ID = result.stdout.strip()


def extract_eval_type(subject: str) -> str:
    """從主旨提取評估類型"""
    if "都更" in subject or "都市更新" in subject or "權利變換" in subject:
        return "都更"
    elif "合建" in subject:
        return "合建"
    elif "處分" in subject:
        return "處分"
    elif "取得" in subject:
        return "取得"
    elif "租" in subject:
        return "租賃"
    else:
        return "其他"


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


def extract_from_content(content: str):
    """從原始內容提取資訊（不過濾不適用）"""
    result = {}

    # 事務所
    firm_match = re.search(r'(?:專業估價者事務所|專業估價師事務所|不動產估價師事務所)[^:：]*[:：]\s*([^\n,，]+)', content)
    if firm_match:
        firm = firm_match.group(1).strip()
        if '不適用' not in firm and len(firm) > 2:
            result['firm'] = firm[:40]

    # 估價師姓名
    name_match = re.search(r'(?:專業估價師姓名|不動產估價師姓名)[^:：]*[:：]\s*([^\n]+)', content)
    if name_match:
        names = name_match.group(1).strip()
        if '不適用' not in names:
            # 提取中文姓名
            names_list = re.findall(r'[\u4e00-\u9fff]{2,4}', names)
            if names_list:
                result['names'] = ', '.join(names_list[:3])

    # 金額
    amount_match = re.search(r'估價金額[^0-9]*?(?:新台幣|新臺幣|NT\$?)?\s*([0-9,]+)\s*元', content)
    if amount_match:
        amount = int(amount_match.group(1).replace(',', ''))
        result['amount'] = amount

    # 交易金額（如果沒有估價金額）
    if 'amount' not in result:
        trade_match = re.search(r'交易金額[^0-9]*?(?:新台幣|新臺幣|NT\$?)?\s*([0-9,]+)\s*元', content)
        if trade_match:
            result['amount'] = int(trade_match.group(1).replace(',', ''))

    # 標的
    target_match = re.search(r'(?:標的|交易標的|處分標的|取得標的)[^:：]*[:：]\s*([^\n]{5,50})', content)
    if target_match:
        target = target_match.group(1).strip()
        if '不適用' not in target:
            result['target'] = target[:35]

    return result


# 連接資料庫
conn = sqlite3.connect('data/mops_announcements.db')
cursor = conn.cursor()

# 取得最新一批資料
cursor.execute('SELECT MAX(announce_date) FROM announcements')
latest_date = cursor.fetchone()[0]

# 取得該日期的公告
cursor.execute('''
    SELECT a.id, a.stock_code, a.company_name, a.subject, a.content,
           a.has_appraiser_info, a.is_real_estate_related,
           r.appraiser_firm, r.appraiser_names, r.appraisal_amount, r.notes
    FROM announcements a
    LEFT JOIN appraiser_records r ON a.id = r.announcement_id
    WHERE a.announce_date = ?
''', (latest_date,))

rows = []
for r in cursor.fetchall():
    rows.append({
        'id': r[0],
        'stock_code': r[1],
        'company_name': r[2],
        'subject': r[3],
        'content': r[4],
        'has_appraiser_info': r[5],
        'is_real_estate_related': r[6],
        'db_firm': r[7],
        'db_names': r[8],
        'db_amount': r[9],
        'db_notes': r[10],
    })

appraiser_rows = [r for r in rows if r['has_appraiser_info']]
real_estate_only = [r for r in rows if r.get('is_real_estate_related') and not r['has_appraiser_info']]

# 建構訊息
today_str = datetime.now().strftime('%Y-%m-%d')

tg_message = f"📋 *Template 4* - 混合提取版\n"
tg_message += f"{'─' * 25}\n"
tg_message += f"📊 *MOPS* | {today_str}\n"
tg_message += f"估價 {len(appraiser_rows)} | 不動產 {len(real_estate_only)}\n"
tg_message += "─" * 25 + "\n"

# 估價師相關
if appraiser_rows:
    for row in appraiser_rows:
        stock_code = row['stock_code']
        company_name = row['company_name']
        eval_type = extract_eval_type(row['subject'])

        tg_message += f"\n*[{stock_code}] {company_name}* ({eval_type})\n"

        # 優先使用 DB 資料，否則從內容提取
        firm = row.get('db_firm')
        names = row.get('db_names')
        amount = row.get('db_amount')
        notes = row.get('db_notes')

        # 如果 DB 沒有，從內容提取
        if not firm and not names and not amount:
            extracted = extract_from_content(row.get('content', ''))
            firm = extracted.get('firm')
            names = extracted.get('names')
            amount = extracted.get('amount')
            target = extracted.get('target')
            if target:
                tg_message += f"🎯 {target}\n"

        if firm:
            firm_clean = re.split(r'[;；(（]', firm)[0].strip()[:35]
            tg_message += f"🏛 {firm_clean}\n"

        if names:
            if isinstance(names, str):
                names_list = re.findall(r'[\u4e00-\u9fff]{2,4}', names)
                if names_list:
                    tg_message += f"👤 {', '.join(names_list[:3])}\n"

        if amount:
            tg_message += f"💰 {format_amount(amount)}\n"

        if notes:
            tg_message += f"📝 {notes[:40]}\n"

# 不動產相關
if real_estate_only:
    tg_message += "\n" + "─" * 25 + "\n"
    tg_message += "*🏢 不動產相關*\n"

    for row in real_estate_only[:5]:  # 限制顯示數量
        stock_code = row['stock_code']
        company_name = row['company_name']
        eval_type = extract_eval_type(row['subject'])

        tg_message += f"\n*[{stock_code}] {company_name}* ({eval_type})\n"

# 底部
tg_message += "\n" + "─" * 25 + "\n"
tg_message += "🔗 [MOPS](https://mops.twse.com.tw/)"

# 發送（不使用 Markdown 避免解析錯誤）
print('發送 Template 4...')
url = f'https://api.telegram.org/bot{TELEGRAM_BOT_TOKEN}/sendMessage'

# 移除 Markdown 格式符號
tg_message_plain = tg_message.replace('*', '').replace('`', '')

payload = {
    'chat_id': TELEGRAM_CHAT_ID,
    'text': tg_message_plain,
}
response = requests.post(url, json=payload, timeout=10)
print(f'Response: {response.status_code}')
if response.status_code != 200:
    print(response.text)
else:
    print('發送成功！')

conn.close()