scraper.py

"""
短道速滑竞赛成绩爬虫
爬取 http://beijing.zhitikeji.com/jscs/ 的数据
"""
import requests
from bs4 import BeautifulSoup
import re
import time
import json
import os
import sys
from datetime import datetime

# 添加项目根目录到路径
sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))
from database import (
    get_db, init_db, upsert_match, upsert_race_group,
    upsert_result, log_update
)

BASE_URL = "http://beijing.zhitikeji.com/jscs/"
CHAOYANG_KEYWORDS = ["朝阳区", "朝阳", "北京市朝阳区第三少儿业余体校", "北京市朝阳区蓓蕾青少年体育俱乐部", "第三少儿体校"]
DATA_SOURCE = "zhitikeji"

# 需要爬取的赛季
TARGET_SEASONS = ["2024-2025赛季", "2025-2026赛季", "2026-2027赛季"]


def is_chaoyang_team(team_name):
    """判断是否为朝阳队"""
    # 排除长春市朝阳区
    if "长春" in team_name and "朝阳" in team_name:
        return False
    return any(kw in team_name for kw in CHAOYANG_KEYWORDS)


def get_session():
    """创建HTTP会话"""
    session = requests.Session()
    session.headers.update({
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36",
        "Accept": "text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8",
        "Accept-Language": "zh-CN,zh;q=0.9,en;q=0.8",
    })
    return session


def get_viewstate(session, url):
    """获取ASP.NET页面的__VIEWSTATE等隐藏字段"""
    r = session.get(url, timeout=15)
    soup = BeautifulSoup(r.text, "html.parser")
    
    viewstate = soup.find("input", {"id": "__VIEWSTATE"})
    viewstategen = soup.find("input", {"id": "__VIEWSTATEGENERATOR"})
    eventvalidation = soup.find("input", {"id": "__EVENTVALIDATION"})
    
    return {
        "__VIEWSTATE": viewstate["value"] if viewstate else "",
        "__VIEWSTATEGENERATOR": viewstategen["value"] if viewstategen else "",
        "__EVENTVALIDATION": eventvalidation["value"] if eventvalidation else "",
    }


def get_season_matches(session, season_name):
    """获取指定赛季的所有比赛"""
    vs = get_viewstate(session, BASE_URL)
    
    data = {
        **vs,
        "ctl00$ContentPlaceHolder1$DropDownList1": season_name,
    }
    
    r = session.post(BASE_URL, data=data, timeout=15)
    soup = BeautifulSoup(r.text, "html.parser")
    
    # 从GridView表格提取比赛信息
    matches = []
    seen_ids = set()
    
    gv = soup.find("table", {"id": "ContentPlaceHolder1_GridView1"})
    if gv:
        rows = gv.find_all("tr")
        for row in rows:
            cells = row.find_all("td")
            if not cells or len(cells) < 6:
                continue
            
            # onclick在td上，取第一个td的onclick获取match_id
            onclick = cells[0].get("onclick", "")
            m = re.search(r"bsrc1\.aspx\?id=(\d+)", onclick)
            if not m:
                continue
            
            match_id = int(m.group(1))
            if match_id in seen_ids:
                continue
            seen_ids.add(match_id)
            
            title = cells[0].get_text(strip=True)
            match_type = cells[1].get_text(strip=True)
            round_info = cells[2].get_text(strip=True)
            date_range = cells[3].get_text(strip=True)
            status = cells[4].get_text(strip=True)
            venue = cells[5].get_text(strip=True)
            
            matches.append({
                "id": match_id,
                "season": season_name,
                "title": title,
                "match_type": match_type,
                "round_info": round_info,
                "date_range": date_range,
                "venue": venue,
                "status": status,
            })
    
    return matches


def get_match_details(session, match_id):
    """获取比赛详情页面中的所有分组"""
    url = f"{BASE_URL}bsrc1.aspx?id={match_id}"
    r = session.get(url, timeout=15)
    soup = BeautifulSoup(r.text, "html.parser")
    
    # 获取比赛标题（从详情页的bsxx span获取完整比赛名称）
    title_el = soup.find("span", {"id": "ContentPlaceHolder1_bsxx"})
    match_title = title_el.get_text(strip=True) if title_el else ""
    
    # 备用标题
    if not match_title:
        title_el2 = soup.find("span", {"id": "ContentPlaceHolder1_Label1"})
        match_title = title_el2.get_text(strip=True) if title_el2 else ""
    
    # 解析分组表格
    groups = []
    current_date = ""
    
    tables = soup.find_all("table")
    for table in tables:
        rows = table.find_all("tr")
        for row in rows:
            # 日期行（蓝色背景行）
            style = row.get("style", "")
            date_cell = row.find("td", colspan=True)
            if date_cell and ("3992d0" in style or "507CD1" in style):
                current_date = date_cell.get_text(strip=True)
                continue
            
            # 表头行
            cells_text = [cell.get_text(strip=True) for cell in row.find_all(["td", "th"])]
            if cells_text and "时间" in cells_text[0]:
                continue
            
            # 数据行
            onclick = row.get("onclick", "")
            if onclick and len(cells_text) >= 5:
                m = re.search(r"rcbh=(\d+)", onclick)
                m2 = re.search(r"bsbh=(\d+)", onclick)
                m3 = re.search(r"id=(\d+)", onclick)
                m4 = re.search(r"md=(\w+)", onclick)
                
                if m:
                    rcbh = int(m.group(1))
                    bsbh = int(m2.group(1)) if m2 else match_id
                    race_id = int(m3.group(1)) if m3 else 0
                    md = m4.group(1) if m4 else "si"
                    
                    groups.append({
                        "rcbh": rcbh,
                        "match_id": match_id,
                        "race_date": current_date,
                        "race_time": cells_text[0] if len(cells_text) > 0 else "",
                        "event": cells_text[1] if len(cells_text) > 1 else "",
                        "category": cells_text[2] if len(cells_text) > 2 else "",
                        "gender": cells_text[3] if len(cells_text) > 3 else "",
                        "round_type": cells_text[4] if len(cells_text) > 4 else "",
                        "bsbh": bsbh,
                        "race_id": race_id,
                        "md": md,
                    })
    
    return groups, match_title


def is_valid_time_result(time_str):
    """校验成绩时间格式是否有效"""
    if not time_str:
        return False
    # 常见格式：01:23.456, 23.456
    time_pattern = r'^(?:[0-9]{1,2}:)?[0-9]{2,3}\.[0-9]{3}$'
    if not re.match(time_pattern, time_str):
        return False
    # 避免00:00.000
    if time_str == '00:00.000' or time_str == '0.000':
        return False
    return True

def is_valid_date(date_str):
    """校验日期格式是否合理"""
    if not date_str:
        return False
    # 支持格式：2024年1月1日, 2024-01-01
    patterns = [
        r'^\d{4}年\d{1,2}月\d{1,2}日$',
        r'^\d{4}-\d{2}-\d{2}$'
    ]
    for pattern in patterns:
        if re.match(pattern, date_str):
            return True
    return False

def _detect_and_clean_rcbh_change(conn, rcbh, upstream_g):
    """检测上游 rcbh 是否被重新分配（event/category/gender 变化）。
    如果变化，删除旧的小组级 race_groups 和 results，返回 True。
    上游 unstable rcbh 会导致男女混合等数据污染。
    """
    base_rcbh = rcbh * 1000
    stored = conn.execute(
        "SELECT rcbh, event, category, gender FROM race_groups "
        "WHERE rcbh BETWEEN ? AND ? AND data_source = ? LIMIT 1",
        (base_rcbh, base_rcbh + 999, DATA_SOURCE)
    ).fetchone()
    if not stored:
        return False  # no existing data, nothing to clean

    # Compare key metadata
    if (stored["event"] != upstream_g.get("event", "") or
        stored["category"] != upstream_g.get("category", "") or
        stored["gender"] != upstream_g.get("gender", "")):
        print(f"  ⚠ rcbh={rcbh} 上游数据变化: "
              f"event {stored['event']}→{upstream_g.get('event','')} "
              f"cat {stored['category']}→{upstream_g.get('category','')} "
              f"gender {stored['gender']}→{upstream_g.get('gender','')} — 清空旧数据")
        # Delete old heat-specific race_groups and results
        conn.execute("DELETE FROM results WHERE rcbh BETWEEN ? AND ?",
                     (base_rcbh, base_rcbh + 999))
        conn.execute("DELETE FROM race_groups WHERE rcbh BETWEEN ? AND ?",
                     (base_rcbh, base_rcbh + 999))
        conn.commit()
        return True
    return False


def get_race_results(session, rcbh, bsbh, race_id, md="si"):
    """获取具体分组比赛的结果，按每个table分配heat_number（小组编号）

    每次请求使用新 session，避免服务器连接重置导致 Broken pipe 中断整个比赛抓取。

    返回的每条结果带 heat_specific_rcbh = rcbh * 1000 + heat_number，
    确保每个小组有独立的 rcbh，避免前端查询对手时跨小组混淆。
    """
    url = f"{BASE_URL}jsfz.aspx?ot=1&md={md}&rcbh={rcbh}&bsbh={bsbh}&id={race_id}"
    try:
        result_session = get_session()
        r = result_session.get(url, timeout=15)
    except (requests.RequestException, OSError):
        return []

    soup = BeautifulSoup(r.text, "html.parser")

    all_results = []
    tables = soup.find_all("table")
    heat_number = 1

    for table in tables:
        rows = table.find_all("tr")
        header = None
        heat_results = []
        seen_athletes = set()  # 去重

        for row in rows:
            cells = [cell.get_text(strip=True) for cell in row.find_all(["td", "th"])]
            if not cells:
                continue

            # 判断标题行
            if any("名次" in c or "PlACE" in c for c in cells):
                header = cells
                continue

            if header and len(cells) >= 5:
                name = cells[3] if len(cells) > 3 else ""
                team = cells[4] if len(cells) > 4 else ""
                time_result = cells[5] if len(cells) > 5 else ""
                qual = cells[6] if len(cells) > 6 else ""

                # 过滤空行
                if not name:
                    continue

                # 去重：同一小组内避免重复数据
                athlete_key = f"{name}_{team}"
                if athlete_key in seen_athletes:
                    continue
                seen_athletes.add(athlete_key)

                # 校验时间格式：无效时间（未开始/DNS/DNF等）保留运动员但清空成绩
                if time_result and not is_valid_time_result(time_result):
                    time_result = ""  # 保留运动员参赛信息，对手对比用

                heat_results.append({
                    "place": cells[0] if len(cells) > 0 else "",
                    "lane": cells[1] if len(cells) > 1 else "",
                    "helmet": cells[2] if len(cells) > 2 else "",
                    "name": name,
                    "team": team,
                    "time_result": time_result,
                    "qual": qual,
                    "finished": cells[7] if len(cells) > 7 else "",
                    "is_chaoyang": 1 if is_chaoyang_team(team) else 0,
                    "heat_number": heat_number,
                    # 小组级独立 rcbh: 原 rcbh * 1000 + 小组编号
                    "rcbh": rcbh * 1000 + heat_number,
                })

        # Only increment heat_number if this table had results
        if heat_results:
            all_results.extend(heat_results)
            heat_number += 1

    return all_results


def _upsert_heat_race_groups(conn, original_rcbh, results, group_template):
    """为每个小组创建独立的 race_group 条目（rcbh = 原 rcbh * 1000 + heat_number）"""
    seen_heats = set()
    for r in results:
        hn = r.get("heat_number", 1)
        if hn in seen_heats:
            continue
        seen_heats.add(hn)
        heat_rcbh = original_rcbh * 1000 + hn
        upsert_race_group(
            conn, heat_rcbh, group_template["match_id"], group_template["race_date"],
            group_template["race_time"], group_template["event"], group_template["category"],
            group_template["gender"], group_template["round_type"],
            group_template["bsbh"], group_template["race_id"],
            data_source=DATA_SOURCE,
        )


def crawl_season(session, season_name, conn, delay=0.5):
    """爬取一个赛季的所有数据"""
    print(f"\n{'='*60}")
    print(f"开始爬取赛季: {season_name}")
    print(f"{'='*60}")

    # 获取比赛列表
    matches = get_season_matches(session, season_name)
    print(f"找到 {len(matches)} 个比赛")

    for match_info in matches:
        match_id = match_info["id"]
        print(f"\n--- 比赛 ID={match_id} ---")

        try:
            # 获取比赛详情
            groups, match_title = get_match_details(session, match_id)

            # 更新比赛信息（优先用详情页获取的完整名称）
            title = match_title or match_info.get("title", "")

            upsert_match(
                conn, match_id, season_name,
                title,
                match_info.get("match_type", ""),
                match_info.get("round_info", ""),
                match_info.get("date_range", ""),
                match_info.get("venue", ""),
                match_info.get("status", ""),
                data_source=DATA_SOURCE,
            )
            conn.commit()

            print(f"  共 {len(groups)} 个分组")

            # 获取每个分组的结果（不存原始 race_group，改为存小组级 heat_rcbh）
            total_results = 0
            for i, g in enumerate(groups):
                if i > 0 and i % 10 == 0:
                    print(f"  已处理 {i}/{len(groups)} 个分组...")
                    conn.commit()

                results = get_race_results(session, g["rcbh"], g["bsbh"], g["race_id"], g.get("md", "si"))

                if results:
                    # 为每个小组创建独立的 race_group
                    _upsert_heat_race_groups(conn, g["rcbh"], results, g)

                for r in results:
                    upsert_result(
                        conn, r["rcbh"], r["place"], r["lane"], r["helmet"],
                        r["name"], r["team"], r["time_result"], r["qual"],
                        r["finished"], r["is_chaoyang"], r.get("heat_number", 1),
                        data_source=DATA_SOURCE,
                    )
                    total_results += 1

                time.sleep(delay)  # 避免请求过于频繁

            log_update(conn, season_name, match_id, "success", f"更新了 {len(groups)} 个分组，{total_results} 条结果")
            conn.commit()

        except Exception as e:
            print(f"  错误: {e}")
            log_update(conn, season_name, match_id, "error", str(e))
            conn.commit()

    print(f"\n赛季 {season_name} 爬取完成")


def crawl_all(target_seasons=None):
    """爬取所有目标赛季数据"""
    if target_seasons is None:
        target_seasons = TARGET_SEASONS
    
    init_db()
    conn = get_db()
    session = get_session()
    
    try:
        for season in target_seasons:
            crawl_season(session, season, conn, delay=0.3)
    finally:
        conn.close()
    
    print("\n全部爬取完成!")


def crawl_update():
    """增量更新 - 爬取新比赛，并检查已有比赛是否有新增分组

    比赛进行中时（is_live），刷新所有已有小组的结果（成绩可能更新）；
    比赛结束后只拉取新出现的小组（新增轮次/项目）。
    结果按 heat_number 拆分到独立 rcbh（原 rcbh * 1000 + heat_number）。
    """
    init_db()
    conn = get_db()
    session = get_session()

    try:
        for season in TARGET_SEASONS:
            matches = get_season_matches(session, season)

            for match_info in matches:
                match_id = match_info["id"]

                existing = conn.execute(
                    "SELECT id, status, title FROM matches WHERE id = ? AND data_source = ?",
                    (match_id, DATA_SOURCE)
                ).fetchone()

                # Determine need_update: new match, missing title, or status contains "进行"/"确认"
                need_update = False
                if not existing:
                    need_update = True
                elif not existing["title"]:
                    need_update = True
                elif "进行" in (existing["status"] or ""):
                    need_update = True
                elif match_info.get("status") and "进行" in match_info["status"]:
                    need_update = True

                # Always update title and status from source
                if existing and match_info.get("title"):
                    old_status = existing["status"]
                    new_status = match_info.get("status", old_status)
                    conn.execute("UPDATE matches SET title=?, status=? WHERE id=? AND data_source=?",
                                 (match_info["title"], new_status, match_id, DATA_SOURCE))
                    conn.commit()
                    # Re-trigger need_update if status changed (e.g. 未开始→正在确认 may add groups)
                    if new_status != old_status:
                        need_update = True

                # Always fetch details and detect new groups
                try:
                    groups, match_title = get_match_details(session, match_id)
                    title = match_title or match_info.get("title", "")

                    upsert_match(conn, match_id, season,
                        title, match_info.get("match_type", ""),
                        match_info.get("round_info", ""),
                        match_info.get("date_range", ""),
                        match_info.get("venue", ""),
                        match_info.get("status", ""),
                        data_source=DATA_SOURCE)
                    conn.commit()

                    is_live = "已结束" not in (match_info.get("status") or "")

                    if need_update:
                        # Full update: fetch all groups' results
                        print(f"更新比赛 ID={match_id}")
                        new_group_count = 0
                        updated_group_count = 0
                        for g in groups:
                            # Check if any heat results exist for this original rcbh
                            base_rcbh = g["rcbh"] * 1000
                            existing_results = conn.execute(
                                "SELECT COUNT(*) as cnt FROM race_groups WHERE rcbh BETWEEN ? AND ? AND data_source=?",
                                (base_rcbh, base_rcbh + 999, DATA_SOURCE)
                            ).fetchone()
                            has_results = existing_results and existing_results["cnt"] > 0

                            if not is_live and has_results:
                                continue
                            if has_results:
                                updated_group_count += 1
                            else:
                                new_group_count += 1

                            print(f"  获取分组 rcbh={g['rcbh']} {g['category']} {g['event']} {g['round_type']}")
                            _detect_and_clean_rcbh_change(conn, g["rcbh"], g)
                            results = get_race_results(session, g["rcbh"], g["bsbh"], g["race_id"], g.get("md", "si"))
                            if results:
                                _upsert_heat_race_groups(conn, g["rcbh"], results, g)
                            for r in results:
                                upsert_result(conn, r["rcbh"], r["place"], r["lane"], r["helmet"],
                                    r["name"], r["team"], r["time_result"], r["qual"],
                                    r["finished"], r["is_chaoyang"], r.get("heat_number", 1),
                                    data_source=DATA_SOURCE)
                            time.sleep(0.3)

                        log_update(conn, season, match_id, "updated",
                                   f"更新了 {len(groups)} 个分组（新获取 {new_group_count} 个，刷新 {updated_group_count} 个）")
                        conn.commit()
                    else:
                        # Check for new groups only
                        new_rcbhs = []
                        for g in groups:
                            base_rcbh = g["rcbh"] * 1000
                            existing_heat = conn.execute(
                                "SELECT rcbh FROM race_groups WHERE rcbh BETWEEN ? AND ? AND data_source=? LIMIT 1",
                                (base_rcbh, base_rcbh + 999, DATA_SOURCE)
                            ).fetchone()
                            if not existing_heat:
                                new_rcbhs.append(g["rcbh"])

                        groups_to_fetch = list(groups) if is_live else [g for g in groups if g["rcbh"] in new_rcbhs]

                        if groups_to_fetch:
                            label = "刷新" if is_live else "发现"
                            print(f"比赛 ID={match_id} {label} {len(groups_to_fetch)} 个分组")
                            for g in groups:
                                if g in groups_to_fetch:
                                    print(f"  获取分组 rcbh={g['rcbh']} {g['category']} {g['event']} {g['round_type']}")
                                    _detect_and_clean_rcbh_change(conn, g["rcbh"], g)
                                    results = get_race_results(session, g["rcbh"], g["bsbh"], g["race_id"], g.get("md", "si"))
                                    if results:
                                        _upsert_heat_race_groups(conn, g["rcbh"], results, g)
                                    for r in results:
                                        upsert_result(conn, r["rcbh"], r["place"], r["lane"], r["helmet"],
                                            r["name"], r["team"], r["time_result"], r["qual"],
                                            r["finished"], r["is_chaoyang"], r.get("heat_number", 1),
                                            data_source=DATA_SOURCE)
                                    time.sleep(0.3)
                            log_update(conn, season, match_id, "updated",
                                       f"{label} {len(groups_to_fetch)} 个分组，共 {len(groups)} 个分组")
                            conn.commit()
                        else:
                            print(f"跳过比赛 ID={match_id}（无新增分组）")
                except Exception as e:
                    print(f"  错误: {e}")
                    log_update(conn, season, match_id, "error", str(e))
                    conn.commit()
    finally:
        conn.close()

    print("更新完成!")


if __name__ == "__main__":
    import argparse
    parser = argparse.ArgumentParser(description="短道速滑竞赛成绩爬虫")
    parser.add_argument("--update", action="store_true", help="增量更新模式")
    parser.add_argument("--season", type=str, help="指定赛季（如 2024-2025赛季）")
    args = parser.parse_args()
    
    if args.update:
        crawl_update()
    elif args.season:
        crawl_all([args.season])
    else:
        crawl_all()