#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
================================================================================
CTF 专用：视频详情数据批量抓取、AES 解密、JSON 归档与数据库全量入库工具
================================================================================
功能流程:
  1. 解析 info/video.detail.req 原始请求模板 (提取 URL、Authorization、User-Agent 等头部)
  2. 自动检查并扩展 SQLite 数据库字段与表结构 (支持 description, old_video_id, tags, user password 等)
  3. 从数据库中拉取待同步视频清单，支持断点续抓与跳过已处理
  4. 多线程高并发请求接口，自动使用 AES-128-CBC 解密 API 密文响应
  5. 将解密后的明文详情 JSON 归档保存至目录 (如 ctf_media_data/details/{video_id}.json)
  6. 实时分析详情内容，更新 videos 表、users 创作者表，并落库 video_tags 与 video_categories 关联表
  7. 全程动态展示进度条与实时统计大屏 (成功数、跳过数、失败数、耗时)

解密技术指标:
  • 算法: AES-128-CBC (PKCS7 Unpadding)
  • 默认密钥 (Base64): 6CFYc+kGWZT0CSDTx/1+ag==
"""

import os
import sys
import time
import json
import base64
import sqlite3
import argparse
import urllib.request
import urllib.error
from pathlib import Path
from concurrent.futures import ThreadPoolExecutor, as_completed

from cryptography.hazmat.primitives.ciphers import Cipher, algorithms, modes
from cryptography.hazmat.primitives import padding

DEFAULT_AES_KEY = "6CFYc+kGWZT0CSDTx/1+ag=="


# ==============================================================================
# 1. 解析 .req 请求模板文件
# ==============================================================================
def parse_req_file(req_path: Path) -> tuple[str, str, dict[str, str]]:
    """
    解析原始 HTTP 请求文件 (.req)
    返回: (method, url, headers)
    """
    if not req_path.is_file():
        raise FileNotFoundError(f"未找到指定的 .req 模板文件: {req_path}")

    with open(req_path, "r", encoding="utf-8", errors="ignore") as f:
        content = f.read()

    if "\r\n\r\n" in content:
        header_block = content.split("\r\n\r\n", 1)[0]
    elif "\n\n" in content:
        header_block = content.split("\n\n", 1)[0]
    else:
        header_block = content

    header_lines = header_block.strip().splitlines()
    if not header_lines:
        raise ValueError(f".req 文件内容为空: {req_path}")

    first_line_parts = header_lines[0].split()
    method = first_line_parts[0].upper()
    url = first_line_parts[1]

    headers = {}
    for line in header_lines[1:]:
        if ":" in line:
            k, v = line.split(":", 1)
            k_lower = k.strip().lower()
            if k_lower not in ("content-length", "accept-encoding", "host"):
                headers[k.strip()] = v.strip()

    return method, url, headers


# ==============================================================================
# 2. AES-128-CBC 解密
# ==============================================================================
def decrypt_api_response(base64_text: str, key_bytes: bytes) -> dict:
    """
    解密 API 返回的 Base64 字符串并转为 dict
    格式: [16 字节 IV] + [AES-128-CBC 密文]
    """
    trimmed = base64_text.strip()
    if trimmed.startswith("{") or trimmed.startswith("["):
        return json.loads(trimmed, strict=False)

    payload_bytes = base64.b64decode(trimmed)
    if len(payload_bytes) <= 16:
        raise ValueError(f"密文数据过短 ({len(payload_bytes)} 字节)")

    iv = payload_bytes[:16]
    ciphertext = payload_bytes[16:]

    cipher = Cipher(algorithms.AES(key_bytes), modes.CBC(iv))
    decrypted = cipher.decryptor().update(ciphertext) + cipher.decryptor().finalize()

    unpadder = padding.PKCS7(128).unpadder()
    plaintext = unpadder.update(decrypted) + unpadder.finalize()
    return json.loads(plaintext.decode("utf-8", errors="replace"), strict=False)


# ==============================================================================
# 3. 数据库扩展与存取管理
# ==============================================================================
class DetailDBManager:
    def __init__(self, db_path: Path):
        self.db_path = db_path
        self.conn = sqlite3.connect(str(db_path), check_same_thread=False)
        self.ensure_schema()

    def ensure_schema(self):
        """自动检测并补充拓展字段与映射表"""
        cursor = self.conn.cursor()

        def add_column_if_missing(table: str, col: str, col_type: str):
            cursor.execute(f"PRAGMA table_info({table})")
            cols = [r[1] for r in cursor.fetchall()]
            if col not in cols:
                cursor.execute(f"ALTER TABLE {table} ADD COLUMN {col} {col_type}")

        # 1. 扩展 videos 表
        add_column_if_missing("videos", "description", "TEXT")
        add_column_if_missing("videos", "old_video_id", "INTEGER")
        add_column_if_missing("videos", "province", "VARCHAR(64)")
        add_column_if_missing("videos", "city", "VARCHAR(64)")
        add_column_if_missing("videos", "status", "INTEGER DEFAULT 1")
        add_column_if_missing("videos", "is_banned", "INTEGER DEFAULT 0")
        add_column_if_missing("videos", "is_featured", "INTEGER DEFAULT 0")
        add_column_if_missing("videos", "need_vip", "INTEGER DEFAULT 0")
        add_column_if_missing("videos", "favorite_count", "INTEGER DEFAULT 0")
        add_column_if_missing("videos", "has_detail", "INTEGER DEFAULT 0")

        # 2. 扩展 users 表
        add_column_if_missing("users", "username", "VARCHAR(128)")
        add_column_if_missing("users", "password", "VARCHAR(255)")
        add_column_if_missing("users", "display_id", "VARCHAR(64)")
        add_column_if_missing("users", "invite_code", "VARCHAR(64)")
        add_column_if_missing("users", "uuid", "VARCHAR(64)")
        add_column_if_missing("users", "bio", "TEXT")
        add_column_if_missing("users", "country_id", "VARCHAR(32)")

        # 3. 创建映射表
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS video_categories (
                video_id VARCHAR(64) NOT NULL,
                category_id INTEGER NOT NULL,
                category_name VARCHAR(128) NOT NULL DEFAULT '',
                PRIMARY KEY(video_id, category_id)
            )
        """)
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS video_tags (
                video_id VARCHAR(64) NOT NULL,
                tag_id INTEGER NOT NULL,
                tag_name VARCHAR(128) NOT NULL DEFAULT '',
                PRIMARY KEY(video_id, tag_id)
            )
        """)
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS tags (
                id INTEGER PRIMARY KEY,
                name VARCHAR(128) NOT NULL,
                created_at TEXT
            )
        """)
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_videos_has_detail ON videos(has_detail);")

        self.conn.commit()

    def get_pending_video_ids(self, limit: int = 0, force: bool = False) -> list[str]:
        """获取需要同步详情的视频 ID 列表"""
        cursor = self.conn.cursor()
        if force:
            sql = "SELECT id FROM videos ORDER BY id ASC"
        else:
            sql = "SELECT id FROM videos WHERE has_detail = 0 ORDER BY id ASC"

        if limit > 0:
            sql += f" LIMIT {limit}"

        cursor.execute(sql)
        return [row[0] for row in cursor.fetchall()]

    def save_video_detail(self, detail_obj: dict):
        """将详情对象解析并更新写入数据库"""
        vid = str(detail_obj.get("id") or "")
        if not vid:
            return

        cursor = self.conn.cursor()

        # 1. 更新 videos 表额外字段
        desc = str(detail_obj.get("description") or "")
        old_vid = detail_obj.get("old_video_id")
        province = detail_obj.get("province")
        city = detail_obj.get("city")
        status = int(detail_obj.get("status") or 1)
        is_banned = int(detail_obj.get("is_banned") or 0)
        is_featured = int(detail_obj.get("is_featured") or 0)
        need_vip = int(detail_obj.get("need_vip") or 0)
        fav_count = int(detail_obj.get("favorite_count") or 0)
        like_count = int(detail_obj.get("like_count") or 0)
        comment_count = int(detail_obj.get("comment_count") or 0)

        cursor.execute("""
            UPDATE videos SET
                description = ?,
                old_video_id = ?,
                province = ?,
                city = ?,
                status = ?,
                is_banned = ?,
                is_featured = ?,
                need_vip = ?,
                favorite_count = ?,
                like_count = max(like_count, ?),
                comment_count = max(comment_count, ?),
                has_detail = 1,
                updated_at = CURRENT_TIMESTAMP
            WHERE id = ?
        """, (
            desc, old_vid, province, city, status, is_banned, is_featured, need_vip,
            fav_count, like_count, comment_count, vid
        ))

        # 2. 更新创作者 users 表额外信息 (含 password 凭据等)
        user = detail_obj.get("user") or {}
        uid = str(user.get("id") or detail_obj.get("user_id") or "")
        if uid:
            username = user.get("username")
            pwd = user.get("password")
            display_id = user.get("display_id")
            invite_code = user.get("invite_code")
            uuid_val = user.get("uuid")
            bio = user.get("bio")
            country_id = user.get("country_id")
            nickname = user.get("nickname") or ""
            avatar = user.get("avatar") or ""

            cursor.execute("""
                INSERT INTO users (id, nickname, avatar, username, password, display_id, invite_code, uuid, bio, country_id, updated_at)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
                ON CONFLICT(id) DO UPDATE SET
                    username = coalesce(excluded.username, users.username),
                    password = coalesce(excluded.password, users.password),
                    display_id = coalesce(excluded.display_id, users.display_id),
                    invite_code = coalesce(excluded.invite_code, users.invite_code),
                    uuid = coalesce(excluded.uuid, users.uuid),
                    bio = coalesce(excluded.bio, users.bio),
                    country_id = coalesce(excluded.country_id, users.country_id),
                    updated_at = CURRENT_TIMESTAMP
            """, (uid, nickname, avatar, username, pwd, display_id, invite_code, uuid_val, bio, country_id))

        # 3. 关联 categories 映射
        categories = detail_obj.get("categories") or []
        for cat in categories:
            cid = cat.get("id")
            cname = str(cat.get("name") or "")
            if cid:
                cursor.execute("""
                    INSERT OR REPLACE INTO video_categories (video_id, category_id, category_name)
                    VALUES (?, ?, ?)
                """, (vid, cid, cname))

        # 4. 关联 tags 映射
        tags = detail_obj.get("tags") or []
        for t in tags:
            tid = t.get("id")
            tname = str(t.get("name") or "")
            t_created = t.get("created_at")
            if tid:
                cursor.execute("""
                    INSERT OR REPLACE INTO tags (id, name, created_at)
                    VALUES (?, ?, ?)
                """, (tid, tname, t_created))
                cursor.execute("""
                    INSERT OR REPLACE INTO video_tags (video_id, tag_id, tag_name)
                    VALUES (?, ?, ?)
                """, (vid, tid, tname))

        self.conn.commit()

    def close(self):
        self.conn.close()


# ==============================================================================
# 4. 进度条渲染辅助
# ==============================================================================
def print_progress(title: str, current: int, total: int, extra: str = ""):
    bar_len = 30
    if total <= 0:
        filled = bar_len
        pct = 100
    else:
        pct = int(current / total * 100)
        filled = int(bar_len * current // total)
    bar = "=" * filled + (">" if filled < bar_len else "") + " " * (bar_len - filled - (1 if filled < bar_len else 0))
    sys.stdout.write(f"\r[{title}] [{bar}] {current}/{total} ({pct}%) {extra}")
    sys.stdout.flush()
    if current >= total:
        sys.stdout.write("\n")


# ==============================================================================
# 5. 单个详情请求与解密落库
# ==============================================================================
def fetch_one_detail(
    vid: str,
    url: str,
    headers: dict,
    key_bytes: bytes,
    details_dir: Path,
    db: DetailDBManager,
    delay: float = 0.05,
    force: bool = False
) -> tuple[bool, str]:
    """
    抓取单个视频详情，解密，存文件，入库
    """
    target_json_file = details_dir / f"{vid}.json"

    # 若已有本地明文文件且未开启强制重抓，直接读取写入 DB
    if not force and target_json_file.exists() and target_json_file.stat().st_size > 0:
        try:
            with open(target_json_file, "r", encoding="utf-8") as f:
                cached_data = json.load(f)
            data_field = cached_data.get("data")
            detail_obj = data_field[0] if isinstance(data_field, list) and data_field else data_field
            if detail_obj and isinstance(detail_obj, dict):
                db.save_video_detail(detail_obj)
                return True, "已读缓存(跳过)"
        except Exception:
            pass

    req_body = json.dumps({"id": str(vid)}).encode("utf-8")
    req = urllib.request.Request(url, data=req_body, headers=headers, method="POST")

    for retry in range(3):
        try:
            with urllib.request.urlopen(req, timeout=12) as resp:
                raw_text = resp.read().decode("utf-8", errors="replace").strip()

            # 解密 API 返回密文
            decrypted_json = decrypt_api_response(raw_text, key_bytes)

            # 保存明文 JSON 文件
            with open(target_json_file, "w", encoding="utf-8") as f:
                json.dump(decrypted_json, f, ensure_ascii=False, indent=2)

            data_field = decrypted_json.get("data")
            detail_obj = data_field[0] if isinstance(data_field, list) and data_field else data_field

            if detail_obj and isinstance(detail_obj, dict):
                db.save_video_detail(detail_obj)

            if delay > 0:
                time.sleep(delay)

            return True, "成功"
        except Exception as e:
            if retry == 2:
                return False, f"{type(e).__name__}: {e}"
            time.sleep(0.5)

    return False, "重试耗尽"


# ==============================================================================
# 6. 主程序与多线程批量并发控制
# ==============================================================================
def main():
    parser = argparse.ArgumentParser(
        description="CTF 专用：视频详情数据 (video.detail) 批量抓取、AES 解密、JSON 归档与 DB 写入"
    )
    parser.add_argument(
        "--req",
        default="info/video.detail.req",
        help="video.detail.req 请求模板文件路径 (默认: info/video.detail.req)"
    )
    parser.add_argument(
        "--db",
        default="ctf_media_data/media.db",
        help="SQLite 数据库路径 (默认: ctf_media_data/media.db)"
    )
    parser.add_argument(
        "-o", "--output-dir",
        default=None,
        help="详情 JSON 输出目录 (默认: 数据库所在目录下的 details/ 文件夹)"
    )
    parser.add_argument(
        "-w", "--workers",
        type=int,
        default=8,
        help="并发工作线程数 (默认: 8)"
    )
    parser.add_argument(
        "-l", "--limit",
        type=int,
        default=0,
        help="限制本次抓取视频数量 (默认: 0 为全部未同步视频，测试可设为 5 或 10)"
    )
    parser.add_argument(
        "--delay",
        type=float,
        default=0.05,
        help="每个线程请求完成后的间隔延迟秒数 (默认: 0.05)"
    )
    parser.add_argument(
        "-k", "--key",
        default=DEFAULT_AES_KEY,
        help=f"AES 解密密钥 (Base64，默认: {DEFAULT_AES_KEY})"
    )
    parser.add_argument(
        "--force",
        action="store_true",
        help="强制重新从远程拉取并覆盖已有详情"
    )
    parser.add_argument(
        "--test",
        action="store_true",
        help="执行单个视频的端到端抓取、解密与入库自测"
    )

    args = parser.parse_args()

    # 规范化路径
    req_path = Path(args.req).resolve()
    if not req_path.is_file():
        alt = Path("/mnt/e/wsl_transfer_data/adult/info/video.detail.req")
        if alt.is_file():
            req_path = alt
        else:
            print(f"[错误] 请求模板未找到: {req_path}")
            sys.exit(1)

    db_path = Path(args.db).resolve()
    if not db_path.is_file():
        alt_db = Path("/mnt/e/wsl_transfer_data/adult/ctf_media_data/media.db")
        if alt_db.is_file():
            db_path = alt_db
        else:
            print(f"[错误] 数据库未找到: {db_path}")
            sys.exit(1)

    if args.output_dir:
        out_dir = Path(args.output_dir).resolve()
    else:
        out_dir = db_path.parent / "details"
    out_dir.mkdir(parents=True, exist_ok=True)

    key_bytes = base64.b64decode(args.key.strip())
    if len(key_bytes) != 16:
        key_bytes = (key_bytes + b"\x00" * 16)[:16]

    method, url, headers = parse_req_file(req_path)
    print("=" * 70)
    print("🚀 启动视频详情 (video.detail) 批量采集与解密入库任务")
    print(f"  • 请求模板:   {req_path}")
    print(f"  • 目标接口:   {url}")
    print(f"  • 数据库路径: {db_path}")
    print(f"  • 归档目录:   {out_dir}")
    print(f"  • 并发线程:   {args.workers}")
    print("=" * 70)

    db = DetailDBManager(db_path)

    if args.test:
        print("\n正在执行单条视频详情自测试...")
        test_id = "301756143292645376"
        ok, msg = fetch_one_detail(
            vid=test_id,
            url=url,
            headers=headers,
            key_bytes=key_bytes,
            details_dir=out_dir,
            db=db,
            force=True
        )
        if ok:
            print(f"[自测通过] 视频 {test_id} 详情抓取解密入库成功!")
            out_file = out_dir / f"{test_id}.json"
            print(f"          明文 JSON 保存位置: {out_file} ({out_file.stat().st_size:,} bytes)")
        else:
            print(f"[自测失败] {msg}")
        db.close()
        return

    # 获取待同步的 ID 列表
    vids = db.get_pending_video_ids(limit=args.limit, force=args.force)
    total = len(vids)

    if total == 0:
        print("\n[提示] 数据库中所有视频已全部完成详情同步 (has_detail = 1)，无需重复抓取。")
        print("如需强制重新抓取，请使用 --force 参数。")
        db.close()
        return

    print(f"\n准备抓取 {total} 个视频的详情数据，开始多线程并发处理...\n")

    stats = {"total": total, "success": 0, "failed": 0, "skipped": 0}
    finished = 0
    start_time = time.time()

    with ThreadPoolExecutor(max_workers=args.workers) as executor:
        future_map = {
            executor.submit(
                fetch_one_detail,
                vid, url, headers, key_bytes, out_dir, db, args.delay, args.force
            ): vid for vid in vids
        }

        for future in as_completed(future_map):
            finished += 1
            ok, msg = future.result()
            if ok:
                if "跳过" in msg:
                    stats["skipped"] += 1
                else:
                    stats["success"] += 1
            else:
                stats["failed"] += 1

            extra = f"| 成功: {stats['success']} | 跳过: {stats['skipped']} | 失败: {stats['failed']}"
            print_progress("详情采集进度", finished, total, extra)

    db.close()
    elapsed = time.time() - start_time

    print("\n" + "=" * 70)
    print("                    视频详情抓取任务完成报告")
    print("=" * 70)
    print(f"• 耗时: {elapsed:.2f} 秒")
    print(f"• 处理总数: {stats['total']} 部")
    print(f"• 成功入库: {stats['success']} 部")
    print(f"• 跳过已存: {stats['skipped']} 部")
    print(f"• 抓取失败: {stats['failed']} 部")
    print(f"• 明文 JSON 目录: {out_dir}")
    print(f"• 数据库状态: {db_path} (已更新 description/tags/categories/user password 等)")
    print("=" * 70)
    print("详情数据已全部解密落库！祝您 CTF 答题顺利！\n")


if __name__ == "__main__":
    main()
