//! Bookmark persistence.

use sqlx::PgPool;
use uuid::Uuid;

use crate::domain::{Bookmark, BookmarkSummary};
use crate::error::{AppError, AppResult};

pub async fn create(
    pool: &PgPool,
    user_id: Uuid,
    manga_id: Uuid,
    chapter_id: Option<Uuid>,
    page: Option<i32>,
) -> AppResult<Bookmark> {
    let result = sqlx::query_as::<_, Bookmark>(
        r#"
        INSERT INTO bookmarks (user_id, manga_id, chapter_id, page)
        VALUES ($1, $2, $3, $4)
        RETURNING id, user_id, manga_id, chapter_id, page, created_at
        "#,
    )
    .bind(user_id)
    .bind(manga_id)
    .bind(chapter_id)
    .bind(page)
    .fetch_one(pool)
    .await;

    match result {
        Ok(b) => Ok(b),
        Err(sqlx::Error::Database(ref db_err)) if db_err.is_unique_violation() => Err(
            AppError::Conflict("bookmark already exists for this manga/chapter".into()),
        ),
        Err(e) => Err(AppError::Database(e)),
    }
}

/// Returns the user's bookmarks enriched with each chapter's number
/// (LEFT JOINed; `chapter_number` is `null` for manga-level bookmarks
/// or for chapter bookmarks whose chapter has since been deleted —
/// `bookmarks.chapter_id` is `ON DELETE SET NULL`). The frontend uses
/// the number to build reader URLs, which are keyed on number, not id.
pub async fn list_for_user(
    pool: &PgPool,
    user_id: Uuid,
    limit: i64,
    offset: i64,
) -> AppResult<(Vec<BookmarkSummary>, i64)> {
    let rows = sqlx::query_as::<_, BookmarkSummary>(
        r#"
        SELECT
            b.id,
            b.user_id,
            b.manga_id,
            m.title AS manga_title,
            m.cover_image_path AS manga_cover_image_path,
            b.chapter_id,
            c.number AS chapter_number,
            b.page,
            b.created_at
        FROM bookmarks b
        INNER JOIN mangas m ON m.id = b.manga_id
        LEFT JOIN chapters c ON c.id = b.chapter_id
        WHERE b.user_id = $1
        -- `id` tiebreaker for deterministic pagination. The bookmarks PK
        -- is `id` (see migration 0001); the (user_id, manga_id) pair
        -- alone is not unique — migration 0004 lets a user keep both a
        -- chapter-level bookmark and a manga-level bookmark on the same
        -- manga, so two rows can tie on (created_at, manga_id). A 0.87.6
        -- earlier revision mistakenly used `manga_id` here.
        ORDER BY b.created_at DESC, b.id DESC
        LIMIT $2 OFFSET $3
        "#,
    )
    .bind(user_id)
    .bind(limit)
    .bind(offset)
    .fetch_all(pool)
    .await?;
    let (total,): (i64,) =
        sqlx::query_as("SELECT count(*) FROM bookmarks WHERE user_id = $1")
            .bind(user_id)
            .fetch_one(pool)
            .await?;
    Ok((rows, total))
}

pub async fn find_owner(pool: &PgPool, id: Uuid) -> AppResult<Option<Uuid>> {
    let row: Option<(Uuid,)> =
        sqlx::query_as("SELECT user_id FROM bookmarks WHERE id = $1")
            .bind(id)
            .fetch_optional(pool)
            .await?;
    Ok(row.map(|(uid,)| uid))
}

pub async fn delete(pool: &PgPool, id: Uuid) -> AppResult<()> {
    sqlx::query("DELETE FROM bookmarks WHERE id = $1")
        .bind(id)
        .execute(pool)
        .await?;
    Ok(())
}

