//! Chapter CRUD: markers for audio items (timestamps, titles, ordering).

use sqlx::PgPool;

use super::models::*;
use super::{ChapterId, ItemId};
use crate::error::Result;

/// List all chapters for an audio item, ordered by sort_order then start time.
///
/// Capped at 500 as a safety limit.
#[tracing::instrument(skip_all)]
pub async fn get_chapters_by_item(pool: &PgPool, item_id: ItemId) -> Result<Vec<DbChapter>> {
    let chapters = sqlx::query_as::<_, DbChapter>(
        "SELECT * FROM chapters WHERE item_id = $1 ORDER BY sort_order, start_seconds LIMIT 500",
    )
    .bind(item_id)
    .fetch_all(pool)
    .await?;

    Ok(chapters)
}

/// Batch-load chapters for multiple items, grouped by item_id.
#[tracing::instrument(skip_all)]
pub async fn get_chapters_by_items(
    pool: &PgPool,
    item_ids: &[ItemId],
) -> Result<std::collections::HashMap<ItemId, Vec<DbChapter>>> {
    let chapters = sqlx::query_as::<_, DbChapter>(
        "SELECT * FROM chapters WHERE item_id = ANY($1) ORDER BY item_id, sort_order, start_seconds",
    )
    .bind(item_ids)
    .fetch_all(pool)
    .await?;

    let mut map: std::collections::HashMap<ItemId, Vec<DbChapter>> = std::collections::HashMap::new();
    for ch in chapters {
        map.entry(ch.item_id).or_default().push(ch);
    }
    Ok(map)
}

/// Insert a new chapter marker for an audio item.
#[tracing::instrument(skip_all)]
pub async fn create_chapter(
    pool: &PgPool,
    item_id: ItemId,
    title: &str,
    start_seconds: f32,
    sort_order: i32,
) -> Result<DbChapter> {
    let chapter = sqlx::query_as::<_, DbChapter>(
        r#"
        INSERT INTO chapters (item_id, title, start_seconds, sort_order)
        VALUES ($1, $2, $3, $4)
        RETURNING *
        "#,
    )
    .bind(item_id)
    .bind(title)
    .bind(start_seconds)
    .bind(sort_order)
    .fetch_one(pool)
    .await?;

    Ok(chapter)
}

/// Update a chapter's title, start time, and sort order.
#[tracing::instrument(skip_all)]
pub async fn update_chapter(
    pool: &PgPool,
    chapter_id: ChapterId,
    title: &str,
    start_seconds: f32,
    sort_order: i32,
) -> Result<DbChapter> {
    let chapter = sqlx::query_as::<_, DbChapter>(
        r#"
        UPDATE chapters
        SET title = $2, start_seconds = $3, sort_order = $4
        WHERE id = $1
        RETURNING *
        "#,
    )
    .bind(chapter_id)
    .bind(title)
    .bind(start_seconds)
    .bind(sort_order)
    .fetch_one(pool)
    .await?;

    Ok(chapter)
}

/// Permanently delete a chapter by ID.
#[tracing::instrument(skip_all)]
pub async fn delete_chapter(pool: &PgPool, chapter_id: ChapterId) -> Result<()> {
    sqlx::query("DELETE FROM chapters WHERE id = $1")
        .bind(chapter_id)
        .execute(pool)
        .await?;

    Ok(())
}

/// Fetch a chapter by primary key. Returns `None` if not found.
#[tracing::instrument(skip_all)]
pub async fn get_chapter_by_id(pool: &PgPool, chapter_id: ChapterId) -> Result<Option<DbChapter>> {
    let chapter = sqlx::query_as::<_, DbChapter>("SELECT * FROM chapters WHERE id = $1")
        .bind(chapter_id)
        .fetch_optional(pool)
        .await?;

    Ok(chapter)
}