import sqlite3
import json
import os
import glob
from datetime import datetime, date, timedelta
from bs4 import BeautifulSoup
from flask import Flask, render_template, jsonify, request

app = Flask(__name__)

HEALTH_DB = '/home/debian/.hermes/profiles/health-butler/health.db'
FINANCE_DB = '/home/debian/.hermes/profiles/finance-butler/finance.db'

LMP = date(2026, 3, 11)
DUE_DATE = date(2026, 12, 16)

BABY_SIZE_BY_WEEK = {
    4: ("芝麻粒", "🌱"), 5: ("芝麻粒", "🌱"), 6: ("扁豆", "🫘"), 7: ("藍莓", "🫐"),
    8: ("覆盆子", "🍇"), 9: ("葡萄", "🍇"), 10: ("草莓", "🍓"), 11: ("無花果", "🍈"),
    12: ("萊姆", "🍋"), 13: ("檸檬", "🍋"), 14: ("蘋果", "🍎"), 15: ("甜橙", "🍊"),
    16: ("酪梨", "🥑"), 17: ("石榴", "🍑"), 18: ("甜椒", "🫑"), 19: ("芒果", "🥭"),
    20: ("香蕉", "🍌"), 21: ("胡蘿蔔", "🥕"), 22: ("玉米", "🌽"), 23: ("葡萄柚", "🍊"),
    24: ("玉米棒", "🌽"), 25: ("蕪菁", "🥬"), 26: ("花椰菜", "🥦"), 27: ("茄子", "🍆"),
    28: ("茄子", "🍆"), 29: ("奶油南瓜", "🎃"), 30: ("高麗菜", "🥬"),
    31: ("高麗菜", "🥬"), 32: ("大南瓜", "🎃"), 33: ("鳳梨", "🍍"),
    34: ("甜瓜", "🍈"), 35: ("蜜瓜", "🍈"), 36: ("羅馬萵苣", "🥬"),
    37: ("甜菜", "🥕"), 38: ("韭蔥", "🫛"), 39: ("小西瓜", "🍉"), 40: ("西瓜", "🍉"),
}


def get_health_data():
    data = {}
    try:
        conn = sqlite3.connect(HEALTH_DB)
        conn.row_factory = sqlite3.Row
        c = conn.cursor()

        # Zami sleep - last 7 days
        seven_days_ago = (datetime.now() - timedelta(days=7)).strftime('%Y-%m-%d')
        rows = c.execute(
            "SELECT value, recorded_at FROM metrics "
            "WHERE person='Zami' AND metric='sleep_duration' AND recorded_at >= ? "
            "ORDER BY recorded_at ASC",
            (seven_days_ago,)
        ).fetchall()
        sleep_values = [r['value'] for r in rows]
        data['zami_sleep_avg'] = round(sum(sleep_values) / len(sleep_values), 1) if sleep_values else None
        data['sleep_chart_labels'] = [r['recorded_at'][5:10] for r in rows]
        data['sleep_chart_values'] = [round(r['value'], 1) for r in rows]

        # 書韋 weight - latest
        row = c.execute(
            "SELECT value, unit, recorded_at FROM metrics "
            "WHERE person='書韋' AND metric='weight' ORDER BY recorded_at DESC LIMIT 1"
        ).fetchone()
        data['shuwei_weight'] = dict(row) if row else None

        # Medications for Zami
        rows = c.execute(
            "SELECT name, category, default_units, unit_pills FROM medications WHERE person='Zami' ORDER BY id"
        ).fetchall()
        data['medications'] = [dict(r) for r in rows]

        conn.close()
    except Exception as e:
        data['health_error'] = str(e)

    # Pregnancy calculation (always computed)
    today = date.today()
    days_since_lmp = (today - LMP).days
    weeks = days_since_lmp // 7
    extra_days = days_since_lmp % 7
    days_to_due = (DUE_DATE - today).days
    progress_pct = round(min(days_since_lmp / 280 * 100, 100), 1)
    size_info = BABY_SIZE_BY_WEEK.get(min(weeks, 40), ("小寶貝", "👶"))

    data['pregnancy'] = {
        'lmp': LMP.strftime('%Y/%m/%d'),
        'due_date': DUE_DATE.strftime('%Y/%m/%d'),
        'weeks': weeks,
        'extra_days': extra_days,
        'days_to_due': days_to_due,
        'progress_pct': progress_pct,
        'baby_size': size_info[0],
        'baby_emoji': size_info[1],
    }
    return data


def get_finance_data():
    data = {}
    try:
        conn = sqlite3.connect(FINANCE_DB)
        conn.row_factory = sqlite3.Row
        c = conn.cursor()

        # Find most recent month from expenses
        row = c.execute("SELECT date FROM expenses ORDER BY date DESC LIMIT 1").fetchone()
        if row and row['date']:
            parts = row['date'].split('/')
            y, m = parts[0], parts[1]
            data['monthly_label'] = f"{y}年{m}月"
            row2 = c.execute(
                "SELECT COALESCE(SUM(amount), 0) as total FROM expenses WHERE date LIKE ?",
                (f"{y}/{m}/%",)
            ).fetchone()
            data['monthly_total'] = int(row2['total'] or 0)
        else:
            data['monthly_total'] = 0
            data['monthly_label'] = "本月"

        # Recent 7 expenses
        rows = c.execute(
            "SELECT date, amount, category, note FROM expenses ORDER BY date DESC, id DESC LIMIT 7"
        ).fetchall()
        data['recent_expenses'] = [dict(r) for r in rows]

        # Holdings + stock prices (actual columns: price, change, change_pct)
        holdings = c.execute("SELECT * FROM holdings ORDER BY symbol").fetchall()
        prices = {r['symbol']: dict(r) for r in c.execute("SELECT * FROM stock_prices").fetchall()}

        holdings_list = []
        total_market_value = 0
        total_cost = 0
        has_cost = False

        for h in holdings:
            sym = h['symbol']
            pi = prices.get(sym, {})
            price = pi.get('close_price', 0) or 0
            shares = h['shares'] or 0
            market_val = price * shares
            total_market_value += market_val

            cp = h['cost_per_share']
            tc = h['total_cost']
            cost = tc if tc else (cp * shares if cp else None)
            if cost:
                total_cost += cost
                has_cost = True

            holdings_list.append({
                'symbol': sym,
                'name': h['name'],
                'shares': shares,
                'price': round(price, 2),
                'change': round(pi.get('change_amount', 0) or 0, 2),
                'change_pct': round(pi.get('change_percent', 0) or 0, 2),
                'market_value': round(market_val, 0),
                'cost': round(cost, 0) if cost else None,
            })

        data['holdings'] = holdings_list
        data['total_market_value'] = round(total_market_value, 0)
        data['price_date'] = list(prices.values())[0].get('date', '') if prices else ''

        if has_cost and total_cost > 0:
            pnl = total_market_value - total_cost
            data['total_pnl'] = round(pnl, 0)
            data['total_pnl_pct'] = round(pnl / total_cost * 100, 2)
        else:
            data['total_pnl'] = None
            data['total_pnl_pct'] = None

        conn.close()
    except Exception as e:
        data['finance_error'] = str(e)

    return data


AI_NEWS_DIR = '/home/debian/Projects/ai-news'


def get_ai_news_dates():
    """Return list of available AI news dates (newest first)."""
    pattern = os.path.join(AI_NEWS_DIR, '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9].html')
    files = sorted(glob.glob(pattern), reverse=True)
    return [os.path.basename(f).replace('.html', '') for f in files]


def get_ai_news_data(date=None):
    """Parse an AI news HTML file and extract news items.
    If date is None, use the most recent file."""
    data = {'items': [], 'date': '', 'error': None, 'dates': get_ai_news_dates()}
    try:
        # Find the most recent date-named HTML file
        pattern = os.path.join(AI_NEWS_DIR, '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9].html')
        files = sorted(glob.glob(pattern), reverse=True)
        if not files:
            data['error'] = '找不到 AI 新聞檔案'
            return data

        if date:
            target = os.path.join(AI_NEWS_DIR, f'{date}.html')
            if os.path.exists(target):
                latest_file = target
            else:
                data['error'] = f'找不到 {date} 的新聞檔案'
                return data
        else:
            latest_file = files[0]
        data['date'] = os.path.basename(latest_file).replace('.html', '')

        with open(latest_file, 'r', encoding='utf-8') as f:
            html = f.read()

        soup = BeautifulSoup(html, 'html.parser')

        # Extract news items
        items = []
        for item_div in soup.select('.news-item'):
            tag_el = item_div.select_one('.tag')
            h2_el = item_div.select_one('h2')
            p_el = item_div.select_one('p')
            a_el = item_div.select_one('a')

            if not h2_el:
                continue

            tag_text = tag_el.get_text(strip=True) if tag_el else ''
            tag_class = ' '.join(tag_el.get('class', [])) if tag_el else ''

            # Determine tag type for styling
            tag_type = 'default'
            if 'tag-model' in tag_class:
                tag_type = 'model'
            elif 'tag-industry' in tag_class:
                tag_type = 'industry'
            elif 'tag-policy' in tag_class:
                tag_type = 'policy'
            elif 'tag-app' in tag_class:
                tag_type = 'app'
            elif 'tag-research' in tag_class:
                tag_type = 'research'

            items.append({
                'tag': tag_text,
                'tag_type': tag_type,
                'title': h2_el.get_text(strip=True),
                'desc': p_el.get_text(strip=True) if p_el else '',
                'url': a_el.get('href', '') if a_el else '',
            })

        data['items'] = items[:5]  # show top 5
    except Exception as e:
        data['error'] = str(e)

    return data


@app.route('/')
def index():
    health = get_health_data()
    finance = get_finance_data()
    ai_news = get_ai_news_data()
    today = datetime.now()
    weekdays = ['星期一', '星期二', '星期三', '星期四', '星期五', '星期六', '星期日']
    today_str = today.strftime(f'%Y年%m月%d日 {weekdays[today.weekday()]}')
    return render_template('index.html',
                           health=health,
                           finance=finance,
                           ai_news=ai_news,
                           today=today_str,
                           sleep_labels=json.dumps(health.get('sleep_chart_labels', [])),
                           sleep_values=json.dumps(health.get('sleep_chart_values', [])))


@app.route('/api/data')
def api_data():
    return jsonify({'health': get_health_data(), 'finance': get_finance_data(), 'ai_news': get_ai_news_data()})


@app.route('/api/ai-news')
def api_ai_news():
    date = request.args.get('date')
    return jsonify(get_ai_news_data(date=date))


if __name__ == '__main__':
    app.run(host='0.0.0.0', port=38425, debug=False)