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()