"""本地 SQLite 管理层 — 加工数据中心"""

import sqlite3
import config
from pathlib import Path


def get_conn() -> sqlite3.Connection:
    conn = sqlite3.connect(config.LOCAL_DB)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA foreign_keys=ON")
    return conn


def init_db():
    """初始化本地 DB 表结构"""
    conn = get_conn()
    conn.executescript("""
    -- 消息处理追踪
    CREATE TABLE IF NOT EXISTS processed_messages (
        msg_id       INTEGER PRIMARY KEY,
        chatroom_id  TEXT,
        talker       TEXT,
        msg_type     INTEGER,
        processed_at TEXT DEFAULT (datetime('now'))
    );

    -- 群聊日总结
    CREATE TABLE IF NOT EXISTS chat_summaries (
        id           INTEGER PRIMARY KEY AUTOINCREMENT,
        chatroom_id  TEXT,
        date         TEXT,
        summary      TEXT,
        msg_count    INTEGER DEFAULT 0,
        created_at   TEXT DEFAULT (datetime('now'))
    );

    -- 文章队列
    CREATE TABLE IF NOT EXISTS article_queue (
        id            INTEGER PRIMARY KEY AUTOINCREMENT,
        source_msg_id INTEGER,
        chatroom_id   TEXT,
        talker        TEXT,
        url           TEXT,
        title         TEXT,
        status        TEXT DEFAULT 'pending',
        extracted_at  TEXT DEFAULT (datetime('now'))
    );

    -- 图片分析
    CREATE TABLE IF NOT EXISTS image_analysis (
        id            INTEGER PRIMARY KEY AUTOINCREMENT,
        source_msg_id INTEGER,
        chatroom_id   TEXT,
        talker        TEXT,
        image_path    TEXT,
        ocr_text      TEXT,
        description   TEXT,
        analyzed_at   TEXT DEFAULT (datetime('now'))
    );

    -- 日报
    CREATE TABLE IF NOT EXISTS daily_reports (
        id            INTEGER PRIMARY KEY AUTOINCREMENT,
        chatroom_id   TEXT,
        chatroom_name TEXT,
        report_date   TEXT,
        content       TEXT,
        msg_count     INTEGER DEFAULT 0,
        image_count   INTEGER DEFAULT 0,
        article_count INTEGER DEFAULT 0,
        dedup_key     TEXT,
        article_url   TEXT,
        created_at    TEXT DEFAULT (datetime('now'))
    );

    -- 日报图片
    CREATE TABLE IF NOT EXISTS report_images (
        id            INTEGER PRIMARY KEY AUTOINCREMENT,
        report_id     INTEGER,
        image_path    TEXT,
        style         TEXT DEFAULT 'wsj',
        created_at    TEXT DEFAULT (datetime('now'))
    );
    """)
    conn.commit()

    # 为已有表补加列（兼容旧表）
    migrations = [
        "ALTER TABLE daily_reports ADD COLUMN dedup_key TEXT",
        "ALTER TABLE daily_reports ADD COLUMN article_url TEXT",
    ]
    for sql in migrations:
        try:
            conn.execute(sql)
        except sqlite3.OperationalError:
            pass  # 列已存在
    conn.commit()

    # 加索引加速去重查询
    try:
        conn.execute("CREATE INDEX IF NOT EXISTS idx_daily_reports_dedup ON daily_reports(dedup_key, report_date)")
    except Exception:
        pass
    conn.commit()
    conn.close()


# ── 日报图片管理 ────────────────────────────────────

def report_image_list(limit: int = 100) -> list[dict]:
    conn = get_conn()
    rows = conn.execute(
        """SELECT ri.*, r.chatroom_name, r.report_date
           FROM report_images ri
           LEFT JOIN daily_reports r ON ri.report_id = r.id
           ORDER BY ri.id DESC LIMIT ?""",
        (limit,)
    ).fetchall()
    conn.close()
    return [dict(r) for r in rows]


def report_image_add(report_id: int, image_path: str, style: str = "wsj") -> int:
    conn = get_conn()
    cur = conn.execute(
        "INSERT INTO report_images (report_id, image_path, style) VALUES (?, ?, ?)",
        (report_id, image_path, style)
    )
    conn.commit()
    row_id = cur.lastrowid
    conn.close()
    return row_id


def report_image_delete(image_id: int):
    conn = get_conn()
    conn.execute("DELETE FROM report_images WHERE id = ?", (image_id,))
    conn.commit()
    conn.close()


# ── 日报管理 ────────────────────────────────────────

def report_list(limit: int = 100) -> list[dict]:
    conn = get_conn()
    rows = conn.execute(
        "SELECT * FROM daily_reports ORDER BY report_date DESC, id DESC LIMIT ?",
        (limit,)
    ).fetchall()
    conn.close()
    return [dict(r) for r in rows]


def report_get(report_id: int) -> dict | None:
    conn = get_conn()
    row = conn.execute("SELECT * FROM daily_reports WHERE id = ?", (report_id,)).fetchone()
    conn.close()
    return dict(row) if row else None


def report_add(chatroom_id: str, chatroom_name: str, report_date: str,
               content: str, msg_count: int = 0, image_count: int = 0,
               article_count: int = 0, dedup_key: str = "",
               article_url: str = "") -> int:
    conn = get_conn()
    cur = conn.execute(
        """INSERT INTO daily_reports (chatroom_id, chatroom_name, report_date, content,
           msg_count, image_count, article_count, dedup_key, article_url)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)""",
        (chatroom_id, chatroom_name, report_date, content,
         msg_count, image_count, article_count,
         dedup_key or None, article_url or None)
    )
    conn.commit()
    row_id = cur.lastrowid
    conn.close()
    return row_id


def report_delete(report_id: int):
    conn = get_conn()
    conn.execute("DELETE FROM daily_reports WHERE id = ?", (report_id,))
    conn.commit()
    conn.close()


# ── 文章摘要去重查询 ────────────────────────────────

def find_article_summary(dedup_key: str, report_type: str) -> dict | None:
    """查某篇文章是否已有摘要/学习结果。
    report_type: 'summary' 或 'learn'
    """
    conn = get_conn()
    row = conn.execute(
        """SELECT * FROM daily_reports
           WHERE dedup_key = ? AND report_date = ?
           ORDER BY id DESC LIMIT 1""",
        (dedup_key, report_type)
    ).fetchone()
    conn.close()
    return dict(row) if row else None


def find_article_summaries_batch(dedup_keys: list[str], report_type: str = "summary") -> dict[str, dict]:
    """批量查哪些 dedup_key 已有摘要。
    返回 {dedup_key: {id, content, created_at}, ...}
    report_type: 'summary' 或 'learn' 或 ''（查任何类型）
    """
    if not dedup_keys:
        return {}
    conn = get_conn()
    placeholders = ",".join(["?"] * len(dedup_keys))
    if report_type:
        sql = f"""SELECT dedup_key, id, content, report_date, created_at
                  FROM daily_reports
                  WHERE dedup_key IN ({placeholders}) AND report_date = ?
                  ORDER BY id DESC"""
        rows = conn.execute(sql, [*dedup_keys, report_type]).fetchall()
    else:
        sql = f"""SELECT dedup_key, id, content, report_date, created_at
                  FROM daily_reports
                  WHERE dedup_key IN ({placeholders})
                  ORDER BY id DESC"""
        rows = conn.execute(sql, dedup_keys).fetchall()
    conn.close()
    result = {}
    for row in rows:
        d = dict(row)
        dk = d.pop("dedup_key")
        if dk and dk not in result:
            result[dk] = d
    return result


# ── 文章队列 ────────────────────────────────────────

def article_list(status: str = "pending", limit: int = 100) -> list[dict]:
    conn = get_conn()
    rows = conn.execute(
        """SELECT * FROM article_queue WHERE status = ? ORDER BY id DESC LIMIT ?""",
        (status, limit)
    ).fetchall()
    conn.close()
    return [dict(r) for r in rows]


def article_add(source_msg_id: int, chatroom_id: str, talker: str,
                url: str, title: str) -> int:
    conn = get_conn()
    cur = conn.execute(
        """INSERT INTO article_queue (source_msg_id, chatroom_id, talker, url, title)
           VALUES (?, ?, ?, ?, ?)""",
        (source_msg_id, chatroom_id, talker, url, title)
    )
    conn.commit()
    row_id = cur.lastrowid
    conn.close()
    return row_id


def article_update_status(article_id: int, status: str):
    conn = get_conn()
    conn.execute(
        "UPDATE article_queue SET status = ? WHERE id = ?",
        (status, article_id)
    )
    conn.commit()
    conn.close()


# ── 图片分析 ────────────────────────────────────────

def image_list(limit: int = 100) -> list[dict]:
    conn = get_conn()
    rows = conn.execute(
        "SELECT * FROM image_analysis ORDER BY id DESC LIMIT ?",
        (limit,)
    ).fetchall()
    conn.close()
    return [dict(r) for r in rows]


def image_add(source_msg_id: int, chatroom_id: str, talker: str,
              image_path: str, ocr_text: str = "", description: str = "") -> int:
    conn = get_conn()
    cur = conn.execute(
        """INSERT INTO image_analysis (source_msg_id, chatroom_id, talker, image_path, ocr_text, description)
           VALUES (?, ?, ?, ?, ?, ?)""",
        (source_msg_id, chatroom_id, talker, image_path, ocr_text, description)
    )
    conn.commit()
    row_id = cur.lastrowid
    conn.close()
    return row_id


# ── 群聊总结 ────────────────────────────────────────

def summary_list(limit: int = 50) -> list[dict]:
    conn = get_conn()
    rows = conn.execute(
        "SELECT * FROM chat_summaries ORDER BY date DESC, id DESC LIMIT ?",
        (limit,)
    ).fetchall()
    conn.close()
    return [dict(r) for r in rows]


def summary_add(chatroom_id: str, date: str, summary: str, msg_count: int = 0) -> int:
    conn = get_conn()
    cur = conn.execute(
        """INSERT INTO chat_summaries (chatroom_id, date, summary, msg_count)
           VALUES (?, ?, ?, ?)""",
        (chatroom_id, date, summary, msg_count)
    )
    conn.commit()
    row_id = cur.lastrowid
    conn.close()
    return row_id