#!/usr/bin/env python3
"""Template 3: 結構化版 - 有詳細資訊時顯示，沒有時顯示主旨"""
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 clean_firm_name(firm: str) -> str:
    """清理事務所名稱"""
    if not firm:
        return None
    firm = re.split(r'[;；(（]', firm)[0].strip()
    if firm.startswith('董事會'):
        parts = firm.split('、')
        if len(parts) > 1:
            firm = parts[1]
        else:
            return None
    if len(firm) > 30:
        firm = firm[:30] + "..."
    return firm


# 連接資料庫
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.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],
        'has_appraiser_info': r[4],
        'is_real_estate_related': r[5],
        'firm': r[6],
        'names': r[7],
        'amount': r[8],
        'notes': r[9],
    })

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 3* - 結構化版\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"

        has_detail = False

        firm = clean_firm_name(row.get('firm'))
        if firm:
            tg_message += f"🏛 {firm}\n"
            has_detail = True

        names = row.get('names')
        if names:
            names_clean = re.sub(r'\([^)]+\)', '', names)
            names_clean = re.sub(r'[：:].*?(?=、|$)', '', names_clean)
            names_list = re.findall(r'[\u4e00-\u9fff]{2,4}', names_clean)
            if names_list:
                tg_message += f"👤 {', '.join(names_list[:3])}\n"
                has_detail = True

        amount_str = format_amount(row.get('amount'))
        if amount_str:
            tg_message += f"💰 {amount_str}\n"
            has_detail = True

        notes = row.get('notes')
        if notes:
            tg_message += f"📝 {notes[:50]}\n"
            has_detail = True

        if not has_detail:
            subject_short = row['subject'][:45]
            tg_message += f"📄 {subject_short}\n"

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

    for row in real_estate_only:
        stock_code = row['stock_code']
        company_name = row['company_name']
        eval_type = extract_eval_type(row['subject'])
        subject_short = row['subject'][:40]

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

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

# 發送（不使用 Markdown 避免解析錯誤）
print('發送 Template 3...')
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()