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 "ci@duckdblab.org"
 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"