import duckdb
import httpx
import yaml
from pathlib import Path
from datetime import datetime, timedelta
import json

CONFIG_PATH = Path(__file__).parent / "config.yaml"
with open(CONFIG_PATH) as f:
    cfg = yaml.safe_load(f)

DB_PATH = Path(__file__).parent / "data" / "market.db"
DB_PATH.parent.mkdir(parents=True, exist_ok=True)

def fetch_market_data() -> dict:
    """Fetch A-share market core indicators"""
    # Replace with real APIs like AKShare, Tushare in production
    return {
        "indices": [
            {"code": "SH000001", "name": "Shanghai Composite", "close": 3285.67, "pct": 0.42},
            {"code": "SZ399001", "name": "Shenzhen Component", "close": 10567.23, "pct": 0.18},
            {"code": "SZ399006", "name": "ChiNext Index", "close": 2156.89, "pct": -0.35},
        ],
        "sectors": [
            {"name": "Semiconductor", "pct": 2.14, "volume_ratio": 1.32},
            {"name": "New Energy", "pct": 1.87, "volume_ratio": 1.15},
            {"name": "Liquor", "pct": -0.52, "volume_ratio": 0.88},
            {"name": "AI Applications", "pct": 3.21, "volume_ratio": 1.67},
        ],
        "capital_flow": {
            "northbound": 12.5,
            "margin": -3.2,
            "main_force": 45.8,
        },
    }

def save_to_duckdb(data: dict) -> None:
    conn = duckdb.connect(str(DB_PATH))
    today = datetime.now().strftime("%Y-%m-%d")

    # Create tables if not exist, delete-then-insert for idempotent daily updates
    conn.execute("""
        CREATE TABLE IF NOT EXISTS daily_indices (
            date        DATE,
            code        VARCHAR,
            name        VARCHAR,
            close       DOUBLE,
            pct_change  DOUBLE,
            created_at  TIMESTAMP
        )
    """)
    conn.execute("DELETE FROM daily_indices WHERE date = ?", [today])
    for idx in data["indices"]:
        conn.execute(
            "INSERT INTO daily_indices VALUES (?, ?, ?, ?, ?, ?)",
            [today, idx["code"], idx["name"], idx["close"], idx["pct"], datetime.now()],
        )

    conn.execute("""
        CREATE TABLE IF NOT EXISTS daily_sectors (
            date           DATE,
            sector_name    VARCHAR,
            pct_change     DOUBLE,
            volume_ratio   DOUBLE,
            created_at     TIMESTAMP
        )
    """)
    conn.execute("DELETE FROM daily_sectors WHERE date = ?", [today])
    for sec in data["sectors"]:
        conn.execute(
            "INSERT INTO daily_sectors VALUES (?, ?, ?, ?, ?)",
            [today, sec["name"], sec["pct"], sec["volume_ratio"], datetime.now()],
        )

    conn.execute("""
        CREATE TABLE IF NOT EXISTS daily_capital (
            date           DATE,
            northbound     DOUBLE,
            margin_change  DOUBLE,
            main_force     DOUBLE,
            created_at     TIMESTAMP
        )
    """)
    conn.execute("DELETE FROM daily_capital WHERE date = ?", [today])
    conn.execute(
        "INSERT INTO daily_capital VALUES (?, ?, ?, ?, ?)",
        [today, data["capital_flow"]["northbound"],
         data["capital_flow"]["margin"], data["capital_flow"]["main_force"],
         datetime.now()],
    )

    conn.commit()
    conn.close()

def analyze_history(days: int = 5) -> dict:
    """Generate trend insights from historical data"""
    conn = duckdb.connect(str(DB_PATH))

    # Shanghai Composite trend over N days
    sh_trend = conn.execute("""
        SELECT date, close, pct_change
        FROM daily_indices
        WHERE code = 'SH000001'
          AND date >= CURRENT_DATE - INTERVAL ? DAY
        ORDER BY date DESC
    """, [days]).fetchall()

    # Sector heat ranking (filter by volume ratio > 1.0)
    hot_sectors = conn.execute("""
        SELECT sector_name, pct_change, volume_ratio
        FROM daily_sectors
        WHERE date = CURRENT_DATE
          AND volume_ratio > 1.0
        ORDER BY pct_change DESC
        LIMIT 3
    """).fetchall()

    # N-day capital flow summary
    capital_summary = conn.execute("""
        SELECT
            SUM(northbound)  AS total_north,
            AVG(main_force)  AS avg_main,
            CASE
                WHEN SUM(northbound) > 0 THEN '🟢 Foreign Capital Inflow'
                ELSE '🔴 Foreign Capital Outflow'
            END AS signal
        FROM daily_capital
        WHERE date >= CURRENT_DATE - INTERVAL ? DAY
    """, [days]).fetchone()

    conn.close()
    return {
        "sh_trend": sh_trend,
        "hot_sectors": hot_sectors,
        "capital": capital_summary,
    }

def build_report(data: dict, insight: dict) -> str:
    today = datetime.now().strftime("%b %d")
    trend = insight["sh_trend"]
    last_close = trend[0][1] if trend else "?"
    prev_pct = trend[1][2] if len(trend) > 1 else 0

    up_days = sum(1 for row in trend if row[2] > 0)
    trend_text = f"Up {up_days}/{len(trend)} days" if trend else "No historical data"

    sector_lines = []
    for name, pct, vr in insight["hot_sectors"]:
        sector_lines.append(f"  · {name}  +{pct:.2f}% (VolRatio {vr:.2f})")
    sector_text = "\n".join(sector_lines) or "  No high-volume sectors"

    capital = insight["capital"]
    capital_text = f"{capital[2]}, N-day northbound累计{'+' if capital[0]>0 else ''}{capital[0]:.1f}B"

    report = f"""📊 【{today} Financial Daily】

━━━ I. Market Overview ━━━
Shanghai Comp {last_close:.2f} ({'↑' if prev_pct>=0 else '↓'}{abs(prev_pct):.2f}%)
{trend_text}

━━━ II. Hot Sectors TOP3 ━━━
{sector_text}

━━━ III. Capital Flow ━━━
{capital_text}

━━━ IV. Tomorrow's Signal ━━━
{generate_signal(insight)}

📌 Data source: Public market data, for reference only"""
    return report

# .github/workflows/daily-cron.yml
name: Daily Financial Report

on:
  schedule:
    - cron: '0 22 * * *'  # 06:00 Beijing time (UTC 18:00)
  workflow_dispatch:      # Also supports manual trigger

jobs:
  run-report:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4

      - name: Set up Python
        uses: actions/setup-python@v5
        with:
          python-version: '3.11'

      - name: Install dependencies
        run: pip install -r requirements.txt

      - name: Run report generator
        env:
          TG_BOT_TOKEN: ${{ secrets.TG_BOT_TOKEN }}
          TG_CHAT_ID: ${{ secrets.TG_CHAT_ID }}
        run: python report.py

      - name: Commit and push results
        run: |
          git config user.name "CI Bot"
          git config user.email "[email protected]"
          git add data/
          git commit -m "Auto update: $(date +%Y-%m-%d)" || echo "No changes"
          git push

# config.yaml
telegram:
  bot_token: "YOUR_BOT_TOKEN"
  chat_id: "YOUR_CHAT_ID"

data_source: "akshare"  # or "tushare", "mock"