database.py

"""PostgreSQL (Neon) persistence layer for books and reading progress."""

import json
import os
import uuid
import psycopg2
import psycopg2.extras
import settings


def get_db():
    conn = psycopg2.connect(settings.DATABASE_URL)
    return conn


def _row_to_dict(cursor) -> dict | None:
    """Fetch one row as a dict."""
    row = cursor.fetchone()
    if not row:
        return None
    cols = [desc[0] for desc in cursor.description]
    return dict(zip(cols, row))


def _rows_to_dicts(cursor) -> list[dict]:
    """Fetch all rows as dicts."""
    rows = cursor.fetchall()
    cols = [desc[0] for desc in cursor.description]
    return [dict(zip(cols, row)) for row in rows]


def init_db():
    """Ensure upload dirs exist. Tables should be created via SQL migration."""
    os.makedirs(settings.UPLOAD_DIR, exist_ok=True)


def save_book(book_id: str, title: str, filepath: str, cover: str,
              language: str, pages: list[str], folder_id: str | None = None,
              segments: list[dict] | None = None, chapters: list[dict] | None = None):
    conn = get_db()
    cur = conn.cursor()
    cur.execute(
        "INSERT INTO books (id, title, filepath, cover, language, pages, total_pages, folder_id, segments, chapters) "
        "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s) "
        "ON CONFLICT (id) DO UPDATE SET title=EXCLUDED.title, filepath=EXCLUDED.filepath, "
        "cover=EXCLUDED.cover, language=EXCLUDED.language, pages=EXCLUDED.pages, "
        "total_pages=EXCLUDED.total_pages, folder_id=EXCLUDED.folder_id, "
        "segments=EXCLUDED.segments, chapters=EXCLUDED.chapters",
        (book_id, title, filepath, cover, language, json.dumps(pages), len(pages), folder_id,
         json.dumps(segments or []), json.dumps(chapters or []))
    )
    cur.execute(
        "INSERT INTO reading_progress (book_id) VALUES (%s) ON CONFLICT (book_id) DO NOTHING",
        (book_id,)
    )
    conn.commit()
    cur.close()
    conn.close()


def get_all_books(folder_id: str | None = None) -> list[dict]:
    conn = get_db()
    cur = conn.cursor()
    if folder_id:
        cur.execute(
            "SELECT b.id, b.title, b.filepath, b.cover, b.language, b.total_pages, b.folder_id, "
            "COALESCE(rp.current_page, 0) as current_page, "
            "COALESCE(rp.percent_read, 0.0) as percent_read "
            "FROM books b LEFT JOIN reading_progress rp ON b.id = rp.book_id "
            "WHERE b.folder_id = %s ORDER BY rp.last_read_at DESC", (folder_id,)
        )
    else:
        cur.execute(
            "SELECT b.id, b.title, b.filepath, b.cover, b.language, b.total_pages, b.folder_id, "
            "COALESCE(rp.current_page, 0) as current_page, "
            "COALESCE(rp.percent_read, 0.0) as percent_read "
            "FROM books b LEFT JOIN reading_progress rp ON b.id = rp.book_id "
            "ORDER BY rp.last_read_at DESC"
        )
    result = _rows_to_dicts(cur)
    cur.close()
    conn.close()
    return result


def get_book(book_id: str) -> dict | None:
    conn = get_db()
    cur = conn.cursor()
    cur.execute(
        "SELECT b.*, COALESCE(rp.current_page, 0) as current_page, "
        "COALESCE(rp.percent_read, 0.0) as percent_read "
        "FROM books b LEFT JOIN reading_progress rp ON b.id = rp.book_id "
        "WHERE b.id = %s", (book_id,)
    )
    book = _row_to_dict(cur)
    cur.close()
    conn.close()
    if not book:
        return None
    book["pages"] = json.loads(book["pages"])
    return book


def get_book_page(book_id: str, page_num: int) -> dict | None:
    conn = get_db()
    cur = conn.cursor()
    cur.execute("SELECT pages, total_pages FROM books WHERE id = %s", (book_id,))
    row = _row_to_dict(cur)
    cur.close()
    conn.close()
    if not row:
        return None
    pages = json.loads(row["pages"])
    if page_num < 0 or page_num >= len(pages):
        return None
    return {"text": pages[page_num], "total_pages": row["total_pages"]}


def update_progress(book_id: str, current_page: int):
    conn = get_db()
    cur = conn.cursor()
    cur.execute("SELECT total_pages FROM books WHERE id = %s", (book_id,))
    row = _row_to_dict(cur)
    if not row:
        cur.close()
        conn.close()
        return
    total = row["total_pages"]
    percent = round(((current_page + 1) / total) * 100, 1) if total > 0 else 0.0
    cur.execute(
        "UPDATE reading_progress SET current_page = %s, percent_read = %s, "
        "last_read_at = NOW() WHERE book_id = %s",
        (current_page, percent, book_id)
    )
    conn.commit()
    cur.close()
    conn.close()


def update_progress_direct(book_id: str, current_page: int, percent: float):
    conn = get_db()
    cur = conn.cursor()
    cur.execute(
        "UPDATE reading_progress SET current_page = %s, percent_read = %s, "
        "last_read_at = NOW() WHERE book_id = %s",
        (current_page, round(percent, 1), book_id)
    )
    conn.commit()
    cur.close()
    conn.close()


def rename_book(book_id: str, new_title: str):
    conn = get_db()
    cur = conn.cursor()
    cur.execute("UPDATE books SET title = %s WHERE id = %s", (new_title, book_id))
    conn.commit()
    cur.close()
    conn.close()


def update_book_cover(book_id: str, cover_path: str):
    conn = get_db()
    cur = conn.cursor()
    cur.execute("UPDATE books SET cover = %s WHERE id = %s", (cover_path, book_id))
    conn.commit()
    cur.close()
    conn.close()


def delete_book(book_id: str) -> dict | None:
    conn = get_db()
    cur = conn.cursor()
    cur.execute("SELECT filepath, cover FROM books WHERE id = %s", (book_id,))
    row = _row_to_dict(cur)
    if not row:
        cur.close()
        conn.close()
        return None
    cur.execute("DELETE FROM reading_progress WHERE book_id = %s", (book_id,))
    cur.execute("DELETE FROM bookmarks WHERE book_id = %s", (book_id,))
    cur.execute("DELETE FROM books WHERE id = %s", (book_id,))
    conn.commit()
    cur.close()
    conn.close()
    return row


def get_progress(book_id: str) -> dict | None:
    conn = get_db()
    cur = conn.cursor()
    cur.execute(
        "SELECT current_page, percent_read, last_read_at FROM reading_progress WHERE book_id = %s",
        (book_id,)
    )
    row = _row_to_dict(cur)
    cur.close()
    conn.close()
    return row


def get_book_segments(book_id: str) -> dict | None:
    conn = get_db()
    cur = conn.cursor()
    cur.execute("SELECT segments, chapters, language, title FROM books WHERE id = %s", (book_id,))
    row = _row_to_dict(cur)
    cur.close()
    conn.close()
    if not row:
        return None
    return {
        "segments": json.loads(row["segments"] or "[]"),
        "chapters": json.loads(row["chapters"] or "[]"),
        "language": row["language"],
        "title": row["title"],
    }


# ==================== FOLDERS ====================

def create_folder(name: str) -> str:
    folder_id = uuid.uuid4().hex[:10]
    conn = get_db()
    cur = conn.cursor()
    cur.execute("INSERT INTO folders (id, name) VALUES (%s, %s)", (folder_id, name))
    conn.commit()
    cur.close()
    conn.close()
    return folder_id


def get_all_folders() -> list[dict]:
    conn = get_db()
    cur = conn.cursor()
    cur.execute(
        "SELECT f.id, f.name, COUNT(b.id) as book_count "
        "FROM folders f LEFT JOIN books b ON f.id = b.folder_id "
        "GROUP BY f.id, f.name ORDER BY f.name"
    )
    result = _rows_to_dicts(cur)
    cur.close()
    conn.close()
    return result


def rename_folder(folder_id: str, new_name: str):
    conn = get_db()
    cur = conn.cursor()
    cur.execute("UPDATE folders SET name = %s WHERE id = %s", (new_name, folder_id))
    conn.commit()
    cur.close()
    conn.close()


def delete_folder(folder_id: str):
    conn = get_db()
    cur = conn.cursor()
    cur.execute("UPDATE books SET folder_id = NULL WHERE folder_id = %s", (folder_id,))
    cur.execute("DELETE FROM folders WHERE id = %s", (folder_id,))
    conn.commit()
    cur.close()
    conn.close()


def move_book_to_folder(book_id: str, folder_id: str | None):
    conn = get_db()
    cur = conn.cursor()
    cur.execute("UPDATE books SET folder_id = %s WHERE id = %s", (folder_id, book_id))
    conn.commit()
    cur.close()
    conn.close()


# ==================== BOOKMARKS ====================

def get_bookmarks(book_id: str) -> list[dict]:
    conn = get_db()
    cur = conn.cursor()
    cur.execute(
        "SELECT id, name, segment_index, created_at FROM bookmarks "
        "WHERE book_id = %s ORDER BY segment_index",
        (book_id,)
    )
    result = _rows_to_dicts(cur)
    cur.close()
    conn.close()
    return result


def add_bookmark(book_id: str, name: str, segment_index: int) -> int:
    conn = get_db()
    cur = conn.cursor()
    cur.execute(
        "INSERT INTO bookmarks (book_id, name, segment_index) VALUES (%s, %s, %s) RETURNING id",
        (book_id, name, segment_index)
    )
    bm_id = cur.fetchone()[0]
    conn.commit()
    cur.close()
    conn.close()
    return bm_id


def rename_bookmark(bookmark_id: int, new_name: str):
    conn = get_db()
    cur = conn.cursor()
    cur.execute("UPDATE bookmarks SET name = %s WHERE id = %s", (new_name, bookmark_id))
    conn.commit()
    cur.close()
    conn.close()


def delete_bookmark(bookmark_id: int):
    conn = get_db()
    cur = conn.cursor()
    cur.execute("DELETE FROM bookmarks WHERE id = %s", (bookmark_id,))
    conn.commit()
    cur.close()
    conn.close()


# ==================== LAST READ ====================

def get_last_read_book() -> dict | None:
    conn = get_db()
    cur = conn.cursor()
    cur.execute(
        "SELECT b.id, rp.percent_read FROM books b "
        "JOIN reading_progress rp ON b.id = rp.book_id "
        "WHERE rp.percent_read > 0 "
        "ORDER BY rp.last_read_at DESC LIMIT 1"
    )
    row = _row_to_dict(cur)
    cur.close()
    conn.close()
    return row