import sqlite3
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'))
        )
    """)
    for kw in keywords:
        kw = kw.strip()
        if not kw:
            continue
        slug = slugify(kw)
        cursor.execute(
            "INSERT INTO posts (keyword, slug, status) VALUES (?, ?, 0)",
            (kw, slug)
        )
    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()}
    added = 0
    for kw in keywords:
        kw = kw.strip()
        if not kw or kw in existing:
            continue
        slug = slugify(kw)
        cursor.execute(
            "INSERT INTO posts (keyword, slug, status) VALUES (?, ?, 0)",
            (kw, slug)
        )
        existing.add(kw)
        added += 1
    conn.commit()
    conn.close()
    return added


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 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_array_length(json_extract(snippet, '$.description')) = 0 "
            "OR json_array_length(json_extract(snippet, '$.related_kw')) = 0)"
        )
    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}