# -*- coding: utf-8 -*-
"""
大乐透 REST API 服务

功能：
  1) 定时获取开奖结果（APScheduler 后台任务，大乐透开奖日 22:00 自动同步）
  2) REST 接口：查询开奖、统计、手动同步
  3) 阿里云百炼（DashScope）Function Calling：用户自然语言提问，如
     "帮我选一组号，用今年出现最多的号码组合"，由 LLM 调用本服务暴露的函数来查库作答

运行：
    python dlt_service.py                       # 默认 0.0.0.0:8000
    python -m uvicorn dlt_service:app --port 8000

配置（环境变量 / .env）：
    DASHSCOPE_API_KEY  百炼 API Key（不填则 /chat 返回 503，其余接口仍可用）
    BAILIAN_MODEL      模型名，默认 qwen-plus
    DLT_DB             SQLite 路径，默认 ./dlt.db
    DLT_EXCEL          Excel 路径，默认 ./dlt_results.xlsx
    HOST / PORT        监听地址与端口
"""

import os
import re
import sys
import json
import random
import requests
import secrets
import hashlib
import smtplib
import sqlite3
from email.mime.text import MIMEText
from datetime import datetime, timedelta

from dotenv import load_dotenv

load_dotenv()

import uvicorn
from fastapi import FastAPI, HTTPException, Request, Depends
from fastapi.middleware.cors import CORSMiddleware
from fastapi.responses import RedirectResponse, JSONResponse, FileResponse
from fastapi.staticfiles import StaticFiles
from pydantic import BaseModel
from apscheduler.schedulers.background import BackgroundScheduler
from apscheduler.triggers.cron import CronTrigger

# 复用抓取/查询模块
from dlt_spider import (
    init_db, DEFAULT_DB, DEFAULT_EXCEL,
    query_ball_frequency, query_latest_draws, query_year_summary,
    run_sync,
)

# ---------------------------------------------------------------- 配置

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
FRONTEND_DIR = os.environ.get("FRONTEND_DIR", os.path.join(os.path.dirname(BASE_DIR), "frontend"))
ROOT_DIR = os.path.dirname(BASE_DIR)
DB_PATH = os.path.abspath(os.environ.get("DLT_DB", DEFAULT_DB))
EXCEL_PATH = os.path.abspath(os.environ.get("DLT_EXCEL", DEFAULT_EXCEL))
API_KEY = os.environ.get("DASHSCOPE_API_KEY")
MODEL = os.environ.get("BAILIAN_MODEL", "qwen-plus")
HOST = os.environ.get("HOST", "0.0.0.0")
PORT = int(os.environ.get("PORT", "8000"))
CURRENT_YEAR = datetime.now().year

# ---------------------------------------------------------------- 用户系统配置
AUTH_DB = os.path.abspath(os.environ.get("AUTH_DB", os.path.join(BASE_DIR, "users.db")))
SMTP_HOST = os.environ.get("SMTP_HOST")
SMTP_PORT = int(os.environ.get("SMTP_PORT", "465"))
SMTP_USER = os.environ.get("SMTP_USER")
SMTP_PASS = os.environ.get("SMTP_PASS")
SMTP_FROM = os.environ.get("SMTP_FROM", SMTP_USER or "noreply@localhost")
ADMIN_EMAIL = (os.environ.get("ADMIN_EMAIL") or "").strip().lower()
ADMIN_PASSWORD = os.environ.get("ADMIN_PASSWORD") or ""
APP_PUBLIC_URL = os.environ.get("APP_PUBLIC_URL", "")   # 用于拼接验证链接（部署到非本机时配置）
GUEST_DAILY_LIMIT = int(os.environ.get("GUEST_DAILY_LIMIT", "3"))   # 游客每日最多提问次数

last_sync = {"time": None, "result": None}   # 供 /sync/status 展示

# ---------------------------------------------------------------- FastAPI

app = FastAPI(title="大乐透开奖数据服务", version="1.0.0")
app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"], allow_credentials=True,
    allow_methods=["*"], allow_headers=["*"],
)


def _conn():
    return init_db(DB_PATH)


# ---------------------------------------------------------------- 鉴权辅助（供依赖注入 / 路由使用，需先于 /sync 定义）

def auth_conn():
    conn = sqlite3.connect(AUTH_DB)
    conn.row_factory = sqlite3.Row
    return conn


def _bearer(request: Request):
    h = request.headers.get("Authorization", "")
    return h[7:] if h.startswith("Bearer ") else None


def _get_user_by_token(token):
    if not token:
        return None
    now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    conn = auth_conn()
    try:
        row = conn.execute(
            "SELECT * FROM users WHERE token=? AND token_expiry>?", (token, now)
        ).fetchone()
    finally:
        conn.close()
    return dict(row) if row else None


def require_admin(request: Request):
    """FastAPI 依赖：校验管理员身份，否则 403"""
    u = _get_user_by_token(_bearer(request))
    if not u or not u["is_admin"]:
        raise HTTPException(403, "仅管理员可执行此操作")
    return u


# ---------------------------------------------------------------- 请求体


class ChatRequest(BaseModel):
    message: str
    history: list[dict] | None = None   # [{"role":"user"/"assistant","content":""}]


class SyncRequest(BaseModel):
    full: bool = False


# ---------------------------------------------------------------- 用户系统请求体


class RegisterRequest(BaseModel):
    email: str
    username: str
    password: str


class LoginRequest(BaseModel):
    email: str
    password: str


class ResendRequest(BaseModel):
    email: str


# ---------------------------------------------------------------- 数据接口


@app.get("/health")
def health():
    conn = _conn()
    try:
        total = conn.execute("SELECT COUNT(*) FROM dlt_draw").fetchone()[0]
        last = conn.execute("SELECT issue, draw_date FROM dlt_draw ORDER BY issue DESC LIMIT 1").fetchone()
    finally:
        conn.close()
    return {
        "status": "ok",
        "db": DB_PATH,
        "total_draws": total,
        "latest_issue": last[0] if last else None,
        "latest_date": last[1] if last else None,
        "llm_ready": bool(API_KEY),
    }


@app.get("/draws/latest")
def latest_draws(limit: int = 5):
    conn = _conn()
    try:
        return {"draws": query_latest_draws(conn, limit)}
    finally:
        conn.close()


@app.get("/draws")
def get_draws(issue: str | None = None, year: int | None = None, limit: int = 50):
    conn = _conn()
    try:
        sql = "SELECT issue, draw_date, front_1, front_2, front_3, front_4, front_5, " \
              "back_1, back_2, draw_result, pool_balance_after FROM dlt_draw WHERE 1=1"
        params = []
        if issue:
            sql += " AND issue = ?"; params.append(issue)
        if year:
            sql += " AND substr(draw_date,1,4) = ?"; params.append(str(year))
        sql += " ORDER BY issue DESC LIMIT ?"
        params.append(int(limit))
        cols = ["issue", "draw_date", "front_1", "front_2", "front_3", "front_4", "front_5",
                "back_1", "back_2", "draw_result", "pool_balance_after"]
        rows = [dict(zip(cols, r)) for r in conn.execute(sql, params).fetchall()]
        return {"draws": rows}
    finally:
        conn.close()


@app.get("/hot")
def hot(year: int = CURRENT_YEAR, top: int = 10):
    """当年前区/后区出现最多的号码"""
    conn = _conn()
    try:
        return {
            "year": year,
            "front": query_ball_frequency(conn, "front", year, top),
            "back": query_ball_frequency(conn, "back", year, top),
        }
    finally:
        conn.close()


@app.get("/stats")
def stats(year: int = CURRENT_YEAR):
    conn = _conn()
    try:
        return query_year_summary(conn, year)
    finally:
        conn.close()


@app.post("/sync")
def sync(req: SyncRequest | None = None, admin: dict = Depends(require_admin)):
    full = req.full if req else False
    result = run_sync(DB_PATH, EXCEL_PATH, full=full)
    last_sync["time"] = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    last_sync["result"] = result
    return {"status": "ok", "mode": "full" if full else "incremental", **result}


@app.get("/sync/status")
def sync_status():
    return last_sync


# ---------------------------------------------------------------- 选号（不依赖 LLM 也可直接用）

def recommend(year: int, strategy: str = "hot") -> dict:
    """strategy: hot(热号) / cold(冷号) / random(随机) / balanced(均衡)"""
    conn = _conn()
    try:
        if strategy == "random":
            front = sorted(random.sample(range(1, 36), 5))
            back = sorted(random.sample(range(1, 13), 2))
        else:
            front_all = query_ball_frequency(conn, "front", year)   # [(ball, cnt)]
            back_all = query_ball_frequency(conn, "back", year)
            if strategy == "cold":
                f = front_all[::-1][:5]
                b = back_all[::-1][:2]
            elif strategy == "balanced":
                f = front_all[::2][:5]          # 间隔取，兼顾热温
                b = back_all[::2][:2]
            else:  # hot
                f = front_all[:5]
                b = back_all[:2]
            front = sorted([v[0] for v in f])
            back = sorted([v[0] for v in b])
        return {
            "year": year, "strategy": strategy,
            "front": [f"{n:02d}" for n in front],
            "back": [f"{n:02d}" for n in back],
            "combination": " ".join(f"{n:02d}" for n in front) + " + " + " ".join(f"{n:02d}" for n in back),
        }
    finally:
        conn.close()


@app.get("/recommend")
def recommend_get(year: int = CURRENT_YEAR, strategy: str = "hot"):
    if strategy not in ("hot", "cold", "random", "balanced"):
        raise HTTPException(400, "strategy 仅支持 hot/cold/random/balanced")
    return recommend(year, strategy)


# ---------------------------------------------------------------- 阿里云百炼 Function Calling

SYSTEM_PROMPT = (
    f"今天是 {datetime.now().strftime('%Y-%m-%d')}，当前年份是 {CURRENT_YEAR} 年。"
    "当用户提到「今年/去年/最近」时，请结合该日期判断年份（「今年」即 "
    f"{CURRENT_YEAR} 年。\n"
    "你是一个「超级大乐透」数据助手。规则：前区从 1-35 选 5 个，后区从 1-12 选 2 个，共 7 个号码为一注。"
    "请尽量调用提供的函数查询数据库，再基于真实数据用中文回答。涉及「选号/推荐」时，"
    "优先用 get_hot_numbers 取当年出现最多的前区/后区号码，或调用 recommend_numbers 直接生成组合。"
    "务必提醒用户：彩票开奖完全随机，历史频率仅供参考、娱乐用途，理性购彩，未满 18 岁禁止购彩。"
)

FUNCTIONS = [
    {
        "name": "get_hot_numbers",
        "description": "查询某一年（或全部历史）前区/后区出现次数最多的号码及其出现次数，用于冷热号分析、热号选号。",
        "parameters": {
            "type": "object",
            "properties": {
                "year": {"type": "integer", "description": "年份，如 2026；不填则用今年"},
                "zone": {"type": "string", "enum": ["front", "back"], "description": "front=前区(1-35)，back=后区(1-12)"},
                "top_n": {"type": "integer", "description": "返回前 N 个最热号码，默认 5"},
            },
            "required": ["zone"],
        },
    },
    {
        "name": "get_ball_frequency",
        "description": "查询某一年（或全部历史）某区域的完整号码出现频率分布。",
        "parameters": {
            "type": "object",
            "properties": {
                "year": {"type": "integer", "description": "年份，不填则全部历史"},
                "zone": {"type": "string", "enum": ["front", "back"], "description": "front 或 back"},
                "top": {"type": "integer", "description": "仅返回前 N 个（按出现次数降序）"},
            },
            "required": ["zone"],
        },
    },
    {
        "name": "get_latest_draws",
        "description": "获取最近若干期开奖结果（期号、日期、前区5个、后区2个、完整号码）。",
        "parameters": {
            "type": "object",
            "properties": {"limit": {"type": "integer", "description": "返回期数，默认 5"}},
            "required": [],
        },
    },
    {
        "name": "get_draw_stats",
        "description": "查询某年的开奖期数、首末日期、前区和值/跨度均值等汇总统计。",
        "parameters": {
            "type": "object",
            "properties": {"year": {"type": "integer", "description": "年份，如 2026"}},
            "required": ["year"],
        },
    },
    {
        "name": "recommend_numbers",
        "description": "按策略直接生成一组大乐透号码（5前区+2后区）。hot=当年最热号，cold=最冷号，random=随机，balanced=均衡。",
        "parameters": {
            "type": "object",
            "properties": {
                "year": {"type": "integer", "description": "年份，默认今年"},
                "strategy": {"type": "string", "enum": ["hot", "cold", "random", "balanced"], "description": "选号策略，默认 hot"},
            },
            "required": [],
        },
    },
]


def _dispatch(name: str, args: dict) -> dict:
    """执行 LLM 选定的函数，返回可序列化的结果 dict"""
    if name == "get_hot_numbers":
        zone = args.get("zone")
        year = args.get("year", CURRENT_YEAR)
        top_n = int(args.get("top_n", 5))
        conn = _conn()
        try:
            return {"zone": zone, "year": year,
                    "hot": [{"ball": b, "count": c}
                            for b, c in query_ball_frequency(conn, zone, year, top_n)]}
        finally:
            conn.close()
    if name == "get_ball_frequency":
        zone = args.get("zone")
        year = args.get("year")
        top = args.get("top")
        conn = _conn()
        try:
            return {"zone": zone, "year": year,
                    "frequency": [{"ball": b, "count": c}
                                  for b, c in query_ball_frequency(conn, zone, year, top)]}
        finally:
            conn.close()
    if name == "get_latest_draws":
        limit = int(args.get("limit", 5))
        conn = _conn()
        try:
            return {"draws": query_latest_draws(conn, limit)}
        finally:
            conn.close()
    if name == "get_draw_stats":
        return query_year_summary(_conn(), args.get("year", CURRENT_YEAR))
    if name == "recommend_numbers":
        return recommend(int(args.get("year", CURRENT_YEAR)), args.get("strategy", "hot"))
    raise ValueError(f"未知函数：{name}")


def _tools_spec():
    """将 FUNCTIONS 转为 OpenAI 兼容的 tools 结构"""
    return [{"type": "function", "function": f} for f in FUNCTIONS]


def _call_llm(messages: list) -> dict:
    """调用百炼（OpenAI 兼容接口），返回 {content, function_call}。

    新版 Qwen 模型（如 qwen3.5-flash）仅在 /compatible-mode 接口提供，
    旧的 dashscope.Generation 接口会返回 'url error'。
    """
    url = "https://dashscope.aliyuncs.com/compatible-mode/v1/chat/completions"
    headers = {
        "Authorization": "Bearer " + API_KEY,
        "Content-Type": "application/json",
    }
    # 历史消息中可能含 function_call / role=function，需转为 OpenAI 兼容格式
    oa_msgs = []
    for m in messages:
        role = m.get("role")
        if role == "function":
            oa_msgs.append({
                "role": "tool",
                "tool_call_id": m.get("tool_call_id") or "call_legacy",
                "name": m.get("name"),
                "content": m.get("content", ""),
            })
        elif role == "assistant" and m.get("function_call"):
            fc = m["function_call"]
            oa_msgs.append({
                "role": "assistant",
                "content": m.get("content") or "",
                "tool_calls": [{
                    "id": fc.get("id", "call_legacy"),
                    "type": "function",
                    "function": {
                        "name": fc["name"],
                        "arguments": fc.get("arguments") or "{}",
                    },
                }],
            })
        else:
            oa_msgs.append({"role": role, "content": m.get("content", "")})

    payload = {
        "model": MODEL,
        "messages": oa_msgs,
        "tools": _tools_spec(),
        "tool_choice": "auto",
    }
    try:
        resp = requests.post(url, headers=headers, json=payload, timeout=60)
    except Exception as exc:  # noqa: BLE001
        raise RuntimeError(f"请求百炼失败：{exc}")
    if resp.status_code != 200:
        raise RuntimeError(f"百炼接口错误：{resp.status_code} {resp.text[:300]}")
    data = resp.json()
    msg = data["choices"][0]["message"]
    fc_out = None
    for tc in msg.get("tool_calls") or []:
        f = tc.get("function", {})
        fc_out = {
            "name": f.get("name"),
            "arguments": f.get("arguments") or "{}",
            "id": tc.get("id"),
        }
        break
    return {"content": msg.get("content"), "function_call": fc_out}


@app.post("/chat")
def chat(req: ChatRequest, request: Request):
    # 游客限额：未登录用户按 IP 每天最多提问 GUEST_DAILY_LIMIT 次
    user = _get_user_by_token(_bearer(request))
    if not user:
        ip = (request.client.host if request.client else None) or "unknown"
        day = datetime.now().strftime("%Y-%m-%d")
        conn = auth_conn()
        try:
            row = conn.execute(
                "SELECT count FROM guest_chat WHERE ip=? AND day=?", (ip, day)
            ).fetchone()
            used = row["count"] if row else 0
            if used >= GUEST_DAILY_LIMIT:
                return JSONResponse(
                    status_code=429,
                    content={"detail": f"游客每日最多提问 {GUEST_DAILY_LIMIT} 次，登录后不限。",
                             "remaining": 0, "limit": GUEST_DAILY_LIMIT},
                )
            # 通过校验即计一次提问（无论后续是否成功）
            conn.execute(
                "INSERT INTO guest_chat (ip, day, count) VALUES (?,?,1) "
                "ON CONFLICT(ip,day) DO UPDATE SET count=count+1",
                (ip, day),
            )
            conn.commit()
        finally:
            conn.close()

    if not API_KEY:
        raise HTTPException(
            503,
            "未配置 DASHSCOPE_API_KEY，无法使用大模型对话。可通过 GET /recommend?strategy=hot 获取热号组合。",
        )

    messages = [{"role": "system", "content": SYSTEM_PROMPT}]
    for h in (req.history or []):
        if h.get("role") in ("user", "assistant"):
            messages.append({"role": h["role"], "content": h["content"]})
    messages.append({"role": "user", "content": req.message})

    tool_calls = []
    for _ in range(6):
        try:
            msg = _call_llm(messages)
        except Exception as exc:  # noqa: BLE001
            raise HTTPException(502, f"调用大模型失败：{exc}")
        fc = msg.get("function_call")
        if not fc:
            final = {"reply": msg.get("content") or "", "tool_calls": tool_calls}
            break
        name = fc["name"]
        try:
            args = json.loads(fc.get("arguments") or "{}")
        except json.JSONDecodeError:
            args = {}
        try:
            result = _dispatch(name, args)
        except Exception as exc:  # noqa: BLE001
            result = {"error": str(exc)}
        tool_calls.append({"name": name, "arguments": args, "result": result})
        # 把函数调用与返回喂回模型，继续多轮
        call_id = fc.get("id") or ("call_%d" % len(tool_calls))
        messages.append({
            "role": "assistant",
            "content": msg.get("content") or "",
            "function_call": {
                "name": name,
                "arguments": fc.get("arguments") or "{}",
                "id": call_id,
            },
        })
        messages.append({
            "role": "function",
            "name": name,
            "tool_call_id": call_id,
            "content": json.dumps(result, ensure_ascii=False),
        })
    else:
        final = {"reply": "（已达到最大调用轮数）", "tool_calls": tool_calls}

    return final


# ---------------------------------------------------------------- 用户系统（邮箱注册/验证/管理员/游客限流）


def init_auth_db():
    conn = auth_conn()
    conn.executescript(
        """
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            email TEXT UNIQUE NOT NULL,
            username TEXT NOT NULL,
            password_hash TEXT NOT NULL,
            email_verified INTEGER NOT NULL DEFAULT 0,
            is_admin INTEGER NOT NULL DEFAULT 0,
            token TEXT,
            token_expiry TEXT,
            created_at TEXT NOT NULL DEFAULT (datetime('now'))
        );
        CREATE TABLE IF NOT EXISTS verify_tokens (
            email TEXT NOT NULL,
            token TEXT NOT NULL,
            expires_at TEXT NOT NULL
        );
        CREATE TABLE IF NOT EXISTS guest_chat (
            ip TEXT NOT NULL,
            day TEXT NOT NULL,
            count INTEGER NOT NULL DEFAULT 0,
            PRIMARY KEY (ip, day)
        );
        """
    )
    conn.commit()
    conn.close()


def _ensure_admin():
    """依据 .env 的 ADMIN_EMAIL / ADMIN_PASSWORD 创建或修正管理员账号"""
    if not (ADMIN_EMAIL and ADMIN_PASSWORD):
        return
    conn = auth_conn()
    try:
        row = conn.execute("SELECT * FROM users WHERE email=?", (ADMIN_EMAIL,)).fetchone()
        if not row:
            conn.execute(
                "INSERT INTO users (email, username, password_hash, email_verified, is_admin) "
                "VALUES (?,?,?,1,1)",
                (ADMIN_EMAIL, "管理员", _hash_pw(ADMIN_PASSWORD)),
            )
            print(f"[auth] 已创建管理员账号：{ADMIN_EMAIL}")
        else:
            conn.execute(
                "UPDATE users SET is_admin=1, email_verified=1 WHERE id=?", (row["id"],)
            )
            print(f"[auth] 已确保管理员账号：{ADMIN_EMAIL}")
        conn.commit()
    finally:
        conn.close()


def _hash_pw(password, salt=None):
    salt = salt or secrets.token_hex(8)
    h = hashlib.sha256((salt + password).encode("utf-8")).hexdigest()
    return f"{salt}${h}"


def _check_pw(password, stored):
    try:
        salt, h = stored.split("$", 1)
    except ValueError:
        return False
    return _hash_pw(password, salt) == stored


def _gen_token():
    return secrets.token_hex(32)


def _public_user(row):
    return {
        "id": row["id"],
        "email": row["email"],
        "username": row["username"],
        "email_verified": bool(row["email_verified"]),
        "is_admin": bool(row["is_admin"]),
    }


def _send_verify_email(email, link):
    """返回 True 表示已通过 SMTP 发送；False 表示未配置（开发模式，仅打印到控制台）"""
    if not (SMTP_HOST and SMTP_USER and SMTP_PASS):
        print(f"[auth] 未配置 SMTP，验证链接（开发模式）：{link}")
        return False
    msg = MIMEText(
        "请点击以下链接完成邮箱验证：\n\n" + link + "\n\n如非本人操作请忽略。",
        "plain", "utf-8",
    )
    msg["Subject"] = "幸运日 - 邮箱验证"
    msg["From"] = SMTP_FROM
    msg["To"] = email
    try:
        with smtplib.SMTP_SSL(SMTP_HOST, SMTP_PORT) as s:
            s.login(SMTP_USER, SMTP_PASS)
            s.sendmail(SMTP_FROM, [email], msg.as_string())
        return True
    except Exception as exc:  # noqa: BLE001
        print(f"[auth] 邮件发送失败：{exc}；验证链接：{link}")
        return False


def _issue_verify(email, request: Request):
    token = _gen_token()
    expires = (datetime.now() + timedelta(hours=24)).strftime("%Y-%m-%d %H:%M:%S")
    conn = auth_conn()
    try:
        conn.execute("DELETE FROM verify_tokens WHERE email=?", (email,))
        conn.execute(
            "INSERT INTO verify_tokens (email, token, expires_at) VALUES (?,?,?)",
            (email, token, expires),
        )
        conn.commit()
    finally:
        conn.close()
    base = APP_PUBLIC_URL.rstrip("/") or str(request.base_url).rstrip("/")
    return f"{base}/auth/verify?token={token}"


@app.post("/auth/register", tags=["auth"])
def auth_register(req: RegisterRequest, request: Request):
    email = req.email.strip().lower()
    if not re.match(r"^[^@\s]+@[^@\s]+\.[^@\s]+$", email):
        raise HTTPException(400, "邮箱格式不正确")
    if len(req.password) < 6:
        raise HTTPException(400, "密码至少 6 位")
    if not req.username.strip():
        raise HTTPException(400, "用户名不能为空")

    conn = auth_conn()
    try:
        if conn.execute("SELECT id FROM users WHERE email=?", (email,)).fetchone():
            raise HTTPException(409, "该邮箱已注册")
        if ADMIN_EMAIL:
            is_admin = 1 if email == ADMIN_EMAIL else 0
        else:
            is_admin = 1 if conn.execute("SELECT COUNT(*) AS c FROM users").fetchone()["c"] == 0 else 0
        conn.execute(
            "INSERT INTO users (email, username, password_hash, email_verified, is_admin) "
            "VALUES (?,?,?,0,?)",
            (email, req.username.strip(), _hash_pw(req.password), is_admin),
        )
        conn.commit()
    finally:
        conn.close()

    link = _issue_verify(email, request)
    sent = _send_verify_email(email, link)
    resp = {
        "email": email,
        "message": "注册成功，请查收验证邮件完成激活。" if sent
                   else "注册成功（开发模式未配置邮件，请使用返回的链接激活）。",
        "verified": False,
    }
    if not sent:
        resp["verify_url"] = link
    return resp


@app.get("/auth/verify", tags=["auth"])
def auth_verify(token: str):
    now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    conn = auth_conn()
    try:
        vt = conn.execute("SELECT * FROM verify_tokens WHERE token=?", (token,)).fetchone()
        if not vt or vt["expires_at"] < now:
            raise HTTPException(400, "验证链接无效或已过期，请重新获取")
        conn.execute("UPDATE users SET email_verified=1 WHERE email=?", (vt["email"],))
        conn.execute("DELETE FROM verify_tokens WHERE token=?", (token,))
        conn.commit()
    finally:
        conn.close()
    if os.path.isdir(FRONTEND_DIR):
        return RedirectResponse(url="/?verified=1")
    return {"ok": True, "message": "邮箱验证成功"}


@app.post("/auth/resend", tags=["auth"])
def auth_resend(req: ResendRequest, request: Request):
    email = req.email.strip().lower()
    conn = auth_conn()
    try:
        row = conn.execute("SELECT email_verified FROM users WHERE email=?", (email,)).fetchone()
    finally:
        conn.close()
    if not row:
        raise HTTPException(404, "该邮箱尚未注册")
    if row["email_verified"]:
        raise HTTPException(400, "该邮箱已验证")
    link = _issue_verify(email, request)
    sent = _send_verify_email(email, link)
    return {"ok": True, "sent": sent, "verify_url": link if not sent else None}


@app.post("/auth/login", tags=["auth"])
def auth_login(req: LoginRequest):
    email = req.email.strip().lower()
    conn = auth_conn()
    try:
        row = conn.execute("SELECT * FROM users WHERE email=?", (email,)).fetchone()
    finally:
        conn.close()
    if not row or not _check_pw(req.password, row["password_hash"]):
        raise HTTPException(401, "邮箱或密码错误")
    if not row["email_verified"]:
        raise HTTPException(403, "邮箱尚未验证，请先完成邮箱验证")
    token = _gen_token()
    expiry = (datetime.now() + timedelta(days=30)).strftime("%Y-%m-%d %H:%M:%S")
    conn = auth_conn()
    try:
        conn.execute(
            "UPDATE users SET token=?, token_expiry=? WHERE id=?", (token, expiry, row["id"])
        )
        conn.commit()
    finally:
        conn.close()
    return {"token": token, "user": _public_user(row)}


@app.get("/auth/me", tags=["auth"])
def auth_me(request: Request):
    row = _get_user_by_token(_bearer(request))
    if not row:
        raise HTTPException(401, "未登录")
    return _public_user(row)


@app.post("/auth/logout", tags=["auth"])
def auth_logout(request: Request):
    row = _get_user_by_token(_bearer(request))
    if row:
        conn = auth_conn()
        try:
            conn.execute(
                "UPDATE users SET token=NULL, token_expiry=NULL WHERE id=?", (row["id"],)
            )
            conn.commit()
        finally:
            conn.close()
    return {"ok": True}


@app.get("/chat/quota", tags=["chat"])
def chat_quota(request: Request):
    """返回当前身份下 AI 提问剩余次数（游客按 IP 每日限流）"""
    if _get_user_by_token(_bearer(request)):
        return {"guest": False, "limit": None, "remaining": None}
    ip = (request.client.host if request.client else None) or "unknown"
    day = datetime.now().strftime("%Y-%m-%d")
    conn = auth_conn()
    try:
        row = conn.execute(
            "SELECT count FROM guest_chat WHERE ip=? AND day=?", (ip, day)
        ).fetchone()
        used = row["count"] if row else 0
    finally:
        conn.close()
    return {"guest": True, "limit": GUEST_DAILY_LIMIT, "remaining": max(0, GUEST_DAILY_LIMIT - used)}


# ---------------------------------------------------------------- 定时任务


def scheduled_sync():
    try:
        result = run_sync(DB_PATH, EXCEL_PATH, full=False)
        last_sync["time"] = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        last_sync["result"] = result
        print(f"[scheduler] 自动同步完成：{result}")
    except Exception as exc:  # noqa: BLE001
        print(f"[scheduler] 同步失败：{exc}")


scheduler = BackgroundScheduler(timezone="Asia/Shanghai")
# 大乐透开奖日：周一、周三、周六晚 21:30 左右开奖，次日 0 点前同步
scheduler.add_job(
    scheduled_sync, CronTrigger(day_of_week="mon,wed,sat", hour=23, minute=0),
    id="dlt_draw_sync", max_instances=1, coalesce=True,
)
# 兜底：每天中午 12:00 再同步一次
scheduler.add_job(
    scheduled_sync, CronTrigger(hour=12, minute=0),
    id="dlt_daily_sync", max_instances=1, coalesce=True,
)


@app.on_event("startup")
def on_startup():
    init_db(DB_PATH)                      # 确保表/视图存在
    init_auth_db()                        # 用户系统表
    _ensure_admin()                       # 依据 .env 创建/修正管理员
    scheduler.start()
    print(f"定时任务已启动（每周一/三/六 23:00 及每日 12:00 自动同步开奖结果）")
    print(f"数据库：{DB_PATH}")
    print(f"大模型：{'已配置(' + MODEL + ')' if API_KEY else '未配置 DASHSCOPE_API_KEY，/chat 不可用'}")


# ---------------------------------------------------------------- 静态托管前端（可选）

@app.get("/", include_in_schema=False)
def root():
    """根路径：若存在 frontend 目录则直接返回首页，否则返回接口列表"""
    if os.path.isdir(FRONTEND_DIR):
        return FileResponse(os.path.join(FRONTEND_DIR, "index.html"))
    return {
        "service": "大乐透开奖数据服务",
        "docs": "/docs",
        "ui": "（未找到 frontend 目录）",
        "endpoints": ["/health", "/draws", "/draws/latest", "/hot", "/stats",
                      "/recommend", "/sync", "/sync/status", "/chat"],
    }


if os.path.isdir(FRONTEND_DIR):
    # 直接挂在根路径，访问 http://localhost:8000/ 即首页（API 路由优先匹配）
    app.mount("/", StaticFiles(directory=FRONTEND_DIR, html=True), name="ui")


if __name__ == "__main__":
    uvicorn.run(app, host=HOST, port=PORT)