//! Item media metadata: post-upload file-size writebacks and per-content-type
//! S3 key/URL/metadata updates (audio, cover, video).

use sqlx::PgPool;

use crate::db::models::*;
use crate::db::{ItemId, UserId};
use crate::error::Result;

/// Get the audio, cover, and video file sizes for an item (for storage decrement on delete).
#[tracing::instrument(skip_all)]
pub async fn get_item_file_sizes(
    pool: &PgPool,
    id: ItemId,
) -> Result<crate::db::models::ItemFileSizes> {
    let row = sqlx::query_as::<_, (Option<i64>, Option<i64>, Option<i64>)>(
        "SELECT audio_file_size_bytes, cover_file_size_bytes, video_file_size_bytes FROM items WHERE id = $1",
    )
    .bind(id)
    .fetch_optional(pool)
    .await?;

    match row {
        Some((audio, cover, video)) => Ok(crate::db::models::ItemFileSizes {
            audio_file_size_bytes: audio,
            cover_file_size_bytes: cover,
            video_file_size_bytes: video,
        }),
        None => Ok(crate::db::models::ItemFileSizes {
            audio_file_size_bytes: None,
            cover_file_size_bytes: None,
            video_file_size_bytes: None,
        }),
    }
}

/// Update the audio file size on an item (defense-in-depth: verifies ownership).
#[tracing::instrument(skip_all)]
pub async fn update_item_audio_file_size(
    pool: &PgPool,
    item_id: ItemId,
    user_id: UserId,
    bytes: i64,
) -> Result<()> {
    sqlx::query(
        "UPDATE items SET audio_file_size_bytes = $2 WHERE id = $1 AND project_id IN (SELECT id FROM projects WHERE user_id = $3)",
    )
    .bind(item_id)
    .bind(bytes)
    .bind(user_id)
    .execute(pool)
    .await?;

    Ok(())
}

/// Update the cover image URL for an item (defense-in-depth: verifies ownership).
#[tracing::instrument(skip_all)]
pub async fn update_item_cover_image_url(
    pool: &PgPool,
    item_id: ItemId,
    user_id: UserId,
    url: &str,
) -> Result<()> {
    sqlx::query(
        "UPDATE items SET cover_image_url = $2, updated_at = NOW() WHERE id = $1 AND project_id IN (SELECT id FROM projects WHERE user_id = $3)",
    )
    .bind(item_id)
    .bind(url)
    .bind(user_id)
    .execute(pool)
    .await?;

    Ok(())
}

/// Atomically update cover image URL, S3 key, and file size in a single UPDATE
/// (defense-in-depth: verifies ownership).
///
/// Returns `true` when the row was actually updated, `false` when the
/// ownership filter matched zero rows (item deleted or moved between
/// projects mid-flight). Callers that fire side-effects after the write —
/// storage credit, scan enqueue, S3 orphan queueing — must check the bool
/// and roll back on false.
#[tracing::instrument(skip_all)]
pub async fn update_item_cover<'e>(
    executor: impl sqlx::PgExecutor<'e>,
    item_id: ItemId,
    user_id: UserId,
    url: &str,
    s3_key: &str,
    file_size_bytes: i64,
) -> Result<bool> {
    let result = sqlx::query(
        r#"UPDATE items
           SET cover_image_url = $2, cover_s3_key = $3, cover_file_size_bytes = $4, updated_at = NOW()
           WHERE id = $1
             AND project_id IN (SELECT id FROM projects WHERE user_id = $5)"#,
    )
    .bind(item_id)
    .bind(url)
    .bind(s3_key)
    .bind(file_size_bytes)
    .bind(user_id)
    .execute(executor)
    .await?;

    Ok(result.rows_affected() > 0)
}

/// Update the cover file size on an item (defense-in-depth: verifies ownership).
#[tracing::instrument(skip_all)]
pub async fn update_item_cover_file_size(
    pool: &PgPool,
    item_id: ItemId,
    user_id: UserId,
    bytes: i64,
) -> Result<()> {
    sqlx::query(
        "UPDATE items SET cover_file_size_bytes = $2 WHERE id = $1 AND project_id IN (SELECT id FROM projects WHERE user_id = $3)",
    )
    .bind(item_id)
    .bind(bytes)
    .bind(user_id)
    .execute(pool)
    .await?;

    Ok(())
}

/// Update the video S3 key for an item (defense-in-depth: verifies ownership).
#[tracing::instrument(skip_all)]
pub async fn update_item_video_s3_key(
    pool: &PgPool,
    item_id: ItemId,
    user_id: UserId,
    s3_key: &str,
) -> Result<DbItem> {
    let item = sqlx::query_as::<_, DbItem>(
        r#"
        UPDATE items
        SET video_s3_key = $2, updated_at = NOW()
        WHERE id = $1
          AND project_id IN (SELECT id FROM projects WHERE user_id = $3)
        RETURNING *
        "#,
    )
    .bind(item_id)
    .bind(s3_key)
    .bind(user_id)
    .fetch_one(pool)
    .await?;

    Ok(item)
}

/// Update the video file size on an item (defense-in-depth: verifies ownership).
#[tracing::instrument(skip_all)]
pub async fn update_item_video_file_size(
    pool: &PgPool,
    item_id: ItemId,
    user_id: UserId,
    bytes: i64,
) -> Result<()> {
    sqlx::query(
        "UPDATE items SET video_file_size_bytes = $2 WHERE id = $1 AND project_id IN (SELECT id FROM projects WHERE user_id = $3)",
    )
    .bind(item_id)
    .bind(bytes)
    .bind(user_id)
    .execute(pool)
    .await?;

    Ok(())
}

/// Update video metadata (duration, resolution) on an item (defense-in-depth: verifies ownership).
#[tracing::instrument(skip_all)]
pub async fn update_item_video_metadata(
    pool: &PgPool,
    item_id: ItemId,
    user_id: UserId,
    duration_seconds: Option<i32>,
    width: Option<i32>,
    height: Option<i32>,
) -> Result<()> {
    sqlx::query(
        r#"
        UPDATE items
        SET video_duration_seconds = $2, video_width = $3, video_height = $4, updated_at = NOW()
        WHERE id = $1
          AND project_id IN (SELECT id FROM projects WHERE user_id = $5)
        "#,
    )
    .bind(item_id)
    .bind(duration_seconds)
    .bind(width)
    .bind(height)
    .bind(user_id)
    .execute(pool)
    .await?;

    Ok(())
}