import json
import queue
import sqlite3
import threading
import time
from pathlib import Path
from .utils import slugify


def get_all_db_files():
    db_dir = Path("database")
    if not db_dir.exists():
        return []
    return list(db_dir.glob("*.sqlite"))


def get_db_connection(db_path):
    conn = sqlite3.connect(db_path, timeout=30)
    conn.execute("PRAGMA journal_mode=WAL")
    conn.row_factory = sqlite3.Row
    return conn


def create_database(db_path: str, keywords: list):
    conn = sqlite3.connect(db_path, timeout=30)
    conn.execute("PRAGMA journal_mode=WAL")
    cursor = conn.cursor()
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS posts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            keyword TEXT,
            slug TEXT,
            images TEXT,
            snippet TEXT,
            ai_title TEXT,
            ai_content TEXT,
            status INTEGER DEFAULT 0,
            created_at TEXT DEFAULT (datetime('now')),
            updated_at TEXT DEFAULT (datetime('now'))
        )
    """)
    rows = []
    for kw in keywords:
        kw = kw.strip()
        if not kw:
            continue
        rows.append((kw, slugify(kw)))
    cursor.executemany(
        "INSERT INTO posts (keyword, slug, status) VALUES (?, ?, 0)",
        rows
    )
    conn.commit()
    conn.close()


def append_keywords(db_path: str, keywords: list) -> int:
    conn = sqlite3.connect(db_path, timeout=30)
    conn.execute("PRAGMA journal_mode=WAL")
    cursor = conn.cursor()
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS posts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            keyword TEXT,
            slug TEXT,
            images TEXT,
            snippet TEXT,
            ai_title TEXT,
            ai_content TEXT,
            status INTEGER DEFAULT 0,
            created_at TEXT DEFAULT (datetime('now')),
            updated_at TEXT DEFAULT (datetime('now'))
        )
    """)
    cursor.execute("SELECT keyword FROM posts")
    existing = {row[0] for row in cursor.fetchall()}
    rows = []
    for kw in keywords:
        kw = kw.strip()
        if not kw or kw in existing:
            continue
        rows.append((kw, slugify(kw)))
        existing.add(kw)
    cursor.executemany(
        "INSERT INTO posts (keyword, slug, status) VALUES (?, ?, 0)",
        rows
    )
    conn.commit()
    conn.close()
    return len(rows)


def get_pending_keywords(db_path: str) -> list:
    conn = sqlite3.connect(db_path, timeout=30)
    conn.execute("PRAGMA journal_mode=WAL")
    cursor = conn.cursor()
    cursor.execute(
        "SELECT id, keyword FROM posts "
        "WHERE (status = 0 OR status IS NULL) "
        "OR (status = 1 AND (images IS NULL OR images = '[]' OR images = ''))"
    )
    rows = [{"id": r[0], "keyword": r[1]} for r in cursor.fetchall()]
    conn.close()
    return rows


def get_pending_combined(db_path: str) -> list:
    conn = sqlite3.connect(db_path, timeout=30)
    conn.execute("PRAGMA journal_mode=WAL")
    cursor = conn.cursor()
    need_images = "(images IS NULL OR images = '' OR images = '[]')"
    try:
        need_snippet = "(snippet IS NULL OR snippet = '' OR json_extract(snippet, '$.description') IS NULL)"
        cursor.execute(
            "SELECT id, keyword, "
            f"CASE WHEN {need_images} THEN 1 ELSE 0 END, "
            f"CASE WHEN {need_snippet} THEN 1 ELSE 0 END "
            "FROM posts "
            f"WHERE {need_images} OR {need_snippet}"
        )
    except Exception:
        cursor.execute(
            "SELECT id, keyword, "
            f"CASE WHEN {need_images} THEN 1 ELSE 0 END, 1 "
            "FROM posts "
            f"WHERE {need_images} OR (snippet IS NULL OR snippet = '')"
        )
    rows = [
        {"id": r[0], "keyword": r[1], "need_images": bool(r[2]), "need_snippet": bool(r[3])}
        for r in cursor.fetchall()
    ]
    conn.close()
    return rows


def get_pending_relatedkw_keywords(db_path: str, include_empty: bool = False) -> list:
    conn = sqlite3.connect(db_path, timeout=30)
    conn.execute("PRAGMA journal_mode=WAL")
    cursor = conn.cursor()
    try:
        if include_empty:
            cond = ("(json_extract(snippet, '$.related_kw') IS NULL "
                    "OR json_array_length(json_extract(snippet, '$.related_kw')) = 0)")
        else:
            cond = "json_extract(snippet, '$.related_kw') IS NULL"
        cursor.execute(
            "SELECT id, keyword FROM posts "
            f"WHERE snippet IS NOT NULL AND snippet != '' AND {cond}"
        )
    except Exception:
        if include_empty:
            cond = ("(snippet NOT LIKE '%\"related_kw\"%' OR snippet LIKE '%\"related_kw\": []%')"
                    " OR snippet LIKE '%\"related_kw\":null%'")
        else:
            cond = "(snippet NOT LIKE '%\"related_kw\"%' OR snippet LIKE '%\"related_kw\":null%')"
        cursor.execute(
            "SELECT id, keyword FROM posts "
            f"WHERE snippet IS NOT NULL AND snippet != '' AND {cond}"
        )
    rows = [{"id": r[0], "keyword": r[1]} for r in cursor.fetchall()]
    conn.close()
    return rows


def update_images(db_path: str, post_id: int, images_json: str):
    conn = sqlite3.connect(db_path, timeout=30)
    conn.execute("PRAGMA journal_mode=WAL")
    cursor = conn.cursor()
    cursor.execute(
        "UPDATE posts SET images = ?, status = 1, updated_at = datetime('now') WHERE id = ?",
        (images_json, post_id)
    )
    conn.commit()
    conn.close()


def get_pending_snippet_keywords(db_path: str) -> list:
    conn = sqlite3.connect(db_path, timeout=30)
    conn.execute("PRAGMA journal_mode=WAL")
    cursor = conn.cursor()
    try:
        cursor.execute(
            "SELECT id, keyword FROM posts "
            "WHERE images IS NOT NULL AND images != '' AND images != '[]' "
            "AND (snippet IS NULL OR snippet = '' "
            "OR json_extract(snippet, '$.description') IS NULL)"
        )
    except Exception:
        cursor.execute(
            "SELECT id, keyword FROM posts "
            "WHERE images IS NOT NULL AND images != '' AND images != '[]' "
            "AND (snippet IS NULL OR snippet = '')"
        )
    rows = [{"id": r[0], "keyword": r[1]} for r in cursor.fetchall()]
    conn.close()
    return rows


def update_snippet(db_path: str, post_id: int, snippet_text: str):
    conn = sqlite3.connect(db_path, timeout=30)
    conn.execute("PRAGMA journal_mode=WAL")
    cursor = conn.cursor()
    cursor.execute(
        "UPDATE posts SET snippet = ?, updated_at = datetime('now') WHERE id = ?",
        (snippet_text, post_id)
    )
    conn.commit()
    conn.close()


def update_all_status(db_path: str) -> dict:
    conn = sqlite3.connect(db_path, timeout=30)
    conn.execute("PRAGMA journal_mode=WAL")
    cursor = conn.cursor()

    cursor.execute("""
        UPDATE posts SET status = 0, updated_at = datetime('now')
        WHERE (images IS NULL OR images = '' OR images = '[]')
        AND (ai_title IS NULL OR ai_title = '')
        AND (ai_content IS NULL OR ai_content = '')
        AND status != 0
    """)
    s0 = cursor.rowcount

    cursor.execute("""
        UPDATE posts SET status = 1, updated_at = datetime('now')
        WHERE images IS NOT NULL AND images != '' AND images != '[]'
        AND (ai_title IS NULL OR ai_title = '')
        AND (ai_content IS NULL OR ai_content = '')
        AND status != 1
    """)
    s1 = cursor.rowcount

    cursor.execute("""
        UPDATE posts SET status = 2, updated_at = datetime('now')
        WHERE images IS NOT NULL AND images != '' AND images != '[]'
        AND ai_title IS NOT NULL AND ai_title != ''
        AND ai_content IS NOT NULL AND ai_content != ''
        AND status != 2
    """)
    s2 = cursor.rowcount

    conn.commit()
    conn.close()
    return {"status_0": s0, "status_1": s1, "status_2": s2}


class BatchWriter:
    """Single writer thread with batched commits.

    All writes go through one thread and one connection, so read-modify-write
    merges on the snippet JSON are race-free even with high scrape concurrency.
    """

    def __init__(self, db_path: str, batch_size: int = 100, flush_interval: float = 30.0):
        self.db_path = db_path
        self.batch_size = max(1, int(batch_size))
        self.flush_interval = flush_interval
        self._q = queue.Queue()
        self._thread = None
        self._started = False

    def start(self):
        if self._started:
            return self
        self._started = True
        self._thread = threading.Thread(target=self._run, daemon=True)
        self._thread.start()
        return self

    def update_images(self, post_id: int, images_json: str):
        self._q.put(("images", post_id, images_json))

    def update_snippet(self, post_id: int, snippet_text: str):
        self._q.put(("snippet", post_id, snippet_text))

    def update_snippet_description(self, post_id: int, descriptions: list):
        self._q.put(("snippet_desc", post_id, list(descriptions)))

    def update_related_kw(self, post_id: int, related_kw: list):
        self._q.put(("related_kw", post_id, list(related_kw)))

    def flush(self):
        if not self._started:
            return
        ev = threading.Event()
        self._q.put(("flush", ev, None))
        ev.wait(timeout=60)

    def close(self):
        if not self._started:
            return
        ev = threading.Event()
        self._q.put(("stop", ev, None))
        ev.wait(timeout=60)
        if self._thread:
            self._thread.join(timeout=10)
        self._started = False

    def _run(self):
        conn = sqlite3.connect(self.db_path, timeout=30)
        conn.execute("PRAGMA journal_mode=WAL")
        batch = []
        last_commit = time.monotonic()
        while True:
            try:
                item = self._q.get(timeout=1.0)
            except queue.Empty:
                if batch and (time.monotonic() - last_commit) >= self.flush_interval:
                    self._commit(conn, batch)
                    batch = []
                    last_commit = time.monotonic()
                continue

            op = item[0]
            if op in ("flush", "stop"):
                self._commit(conn, batch)
                batch = []
                last_commit = time.monotonic()
                item[1].set()
                self._q.task_done()
                if op == "stop":
                    break
                continue

            batch.append(item)
            if len(batch) >= self.batch_size:
                self._commit(conn, batch)
                batch = []
                last_commit = time.monotonic()
            self._q.task_done()

        conn.close()

    def _commit(self, conn, batch):
        if not batch:
            return
        for _ in range(3):
            try:
                for op, post_id, payload in batch:
                    self._apply(conn, op, post_id, payload)
                conn.commit()
                return
            except sqlite3.Error as e:
                try:
                    conn.rollback()
                except Exception:
                    pass
                time.sleep(1)
                last_err = e
        print(f"BatchWriter commit failed for {self.db_path}: {last_err}")

    def _parse_snippet(self, raw) -> dict:
        snippet = {}
        if raw:
            try:
                parsed = json.loads(raw)
                if isinstance(parsed, dict):
                    snippet = parsed
            except Exception:
                pass
        return snippet

    def _apply(self, conn, op: str, post_id: int, payload):
        if op == "images":
            conn.execute(
                "UPDATE posts SET images = ?, status = 1, updated_at = datetime('now') WHERE id = ?",
                (payload, post_id)
            )
        elif op == "snippet":
            conn.execute(
                "UPDATE posts SET snippet = ?, updated_at = datetime('now') WHERE id = ?",
                (payload, post_id)
            )
        elif op == "snippet_desc":
            row = conn.execute("SELECT snippet FROM posts WHERE id = ?", (post_id,)).fetchone()
            snippet = self._parse_snippet(row[0] if row else None)
            snippet["description"] = payload
            if not isinstance(snippet.get("related_kw"), list):
                snippet.pop("related_kw", None)
            conn.execute(
                "UPDATE posts SET snippet = ?, updated_at = datetime('now') WHERE id = ?",
                (json.dumps(snippet, ensure_ascii=False), post_id)
            )
        elif op == "related_kw":
            row = conn.execute("SELECT snippet FROM posts WHERE id = ?", (post_id,)).fetchone()
            snippet = self._parse_snippet(row[0] if row else None)
            snippet["related_kw"] = payload
            conn.execute(
                "UPDATE posts SET snippet = ?, updated_at = datetime('now') WHERE id = ?",
                (json.dumps(snippet, ensure_ascii=False), post_id)
            )