//! Content insertion CRUD: reusable clips (intros, outros, sponsor reads)
//! and their per-item placements.

use sqlx::PgPool;

use super::models::*;
use super::{ContentInsertionId, ContentInsertionPlacementId, InsertionPosition, ItemId, UserId};
use crate::error::Result;

// ── Insertion library ──

/// Create a new reusable insertion clip for a creator.
#[allow(clippy::too_many_arguments)]
#[tracing::instrument(skip_all)]
pub async fn create_insertion(
    pool: &PgPool,
    user_id: UserId,
    title: &str,
    media_type: &str,
    storage_key: &str,
    duration_ms: i32,
    file_size: i64,
    mime_type: &str,
) -> Result<DbContentInsertion> {
    let row = sqlx::query_as::<_, DbContentInsertion>(
        r#"
        INSERT INTO content_insertions (user_id, title, media_type, storage_key, duration_ms, file_size, mime_type)
        VALUES ($1, $2, $3, $4, $5, $6, $7)
        RETURNING *
        "#,
    )
    .bind(user_id)
    .bind(title)
    .bind(media_type)
    .bind(storage_key)
    .bind(duration_ms)
    .bind(file_size)
    .bind(mime_type)
    .fetch_one(pool)
    .await?;

    Ok(row)
}

/// List all insertion clips for a creator, newest first.
#[tracing::instrument(skip_all)]
pub async fn list_insertions(pool: &PgPool, user_id: UserId) -> Result<Vec<DbContentInsertion>> {
    let rows = sqlx::query_as::<_, DbContentInsertion>(
        "SELECT * FROM content_insertions WHERE user_id = $1 ORDER BY created_at DESC LIMIT 500",
    )
    .bind(user_id)
    .fetch_all(pool)
    .await?;

    Ok(rows)
}

/// Get a single insertion by ID, scoped to the owning user.
#[tracing::instrument(skip_all)]
pub async fn get_insertion(
    pool: &PgPool,
    id: ContentInsertionId,
    user_id: UserId,
) -> Result<Option<DbContentInsertion>> {
    let row = sqlx::query_as::<_, DbContentInsertion>(
        "SELECT * FROM content_insertions WHERE id = $1 AND user_id = $2",
    )
    .bind(id)
    .bind(user_id)
    .fetch_optional(pool)
    .await?;

    Ok(row)
}

/// Rename an insertion clip. Returns true if the row was found and updated.
#[tracing::instrument(skip_all)]
pub async fn update_insertion_title(
    pool: &PgPool,
    id: ContentInsertionId,
    user_id: UserId,
    title: &str,
) -> Result<bool> {
    let result = sqlx::query(
        "UPDATE content_insertions SET title = $3 WHERE id = $1 AND user_id = $2",
    )
    .bind(id)
    .bind(user_id)
    .bind(title)
    .execute(pool)
    .await?;

    Ok(result.rows_affected() > 0)
}

/// Delete an insertion clip (placements cascade). Returns true if deleted.
#[tracing::instrument(skip_all)]
pub async fn delete_insertion(
    pool: &PgPool,
    id: ContentInsertionId,
    user_id: UserId,
) -> Result<bool> {
    let result = sqlx::query(
        "DELETE FROM content_insertions WHERE id = $1 AND user_id = $2",
    )
    .bind(id)
    .bind(user_id)
    .execute(pool)
    .await?;

    Ok(result.rows_affected() > 0)
}

// ── Placements ──

/// Attach an insertion clip to an item at a given position.
#[tracing::instrument(skip_all)]
pub async fn create_placement(
    pool: &PgPool,
    item_id: ItemId,
    insertion_id: ContentInsertionId,
    position: InsertionPosition,
    offset_ms: Option<i32>,
    sort_order: i32,
) -> Result<DbInsertionPlacement> {
    let row = sqlx::query_as::<_, DbInsertionPlacement>(
        r#"
        INSERT INTO content_insertion_placements (item_id, insertion_id, position, offset_ms, sort_order)
        VALUES ($1, $2, $3, $4, $5)
        RETURNING *
        "#,
    )
    .bind(item_id)
    .bind(insertion_id)
    .bind(position)
    .bind(offset_ms)
    .bind(sort_order)
    .fetch_one(pool)
    .await?;

    Ok(row)
}

/// List all placements for an item, joined with insertion metadata.
/// Ordered by position (pre_roll, mid_roll by offset, post_roll) then sort_order.
#[tracing::instrument(skip_all)]
pub async fn list_placements_for_item(
    pool: &PgPool,
    item_id: ItemId,
) -> Result<Vec<DbPlacementWithInsertion>> {
    let rows = sqlx::query_as::<_, DbPlacementWithInsertion>(
        r#"
        SELECT
            p.id, p.item_id, p.insertion_id, p.position, p.offset_ms, p.sort_order, p.created_at,
            i.title AS insertion_title,
            i.duration_ms AS insertion_duration_ms,
            i.storage_key AS insertion_storage_key
        FROM content_insertion_placements p
        JOIN content_insertions i ON i.id = p.insertion_id
        WHERE p.item_id = $1
        ORDER BY
            CASE p.position
                WHEN 'pre_roll' THEN 0
                WHEN 'mid_roll' THEN 1
                WHEN 'post_roll' THEN 2
            END,
            p.offset_ms NULLS LAST,
            p.sort_order
        LIMIT 100
        "#,
    )
    .bind(item_id)
    .fetch_all(pool)
    .await?;

    Ok(rows)
}

/// Delete a single placement by ID. Returns true if deleted.
#[tracing::instrument(skip_all)]
pub async fn delete_placement(
    pool: &PgPool,
    placement_id: ContentInsertionPlacementId,
) -> Result<bool> {
    let result = sqlx::query(
        "DELETE FROM content_insertion_placements WHERE id = $1",
    )
    .bind(placement_id)
    .execute(pool)
    .await?;

    Ok(result.rows_affected() > 0)
}

/// Get a placement by ID (for ownership verification via item).
#[tracing::instrument(skip_all)]
pub async fn get_placement_by_id(
    pool: &PgPool,
    placement_id: ContentInsertionPlacementId,
) -> Result<Option<DbInsertionPlacement>> {
    let row = sqlx::query_as::<_, DbInsertionPlacement>(
        "SELECT * FROM content_insertion_placements WHERE id = $1",
    )
    .bind(placement_id)
    .fetch_optional(pool)
    .await?;

    Ok(row)
}