//! Media library queries: user-scoped files for inline markdown content.

use sqlx::PgPool;

use super::id_types::{MediaFileId, UserId};
use super::models::DbMediaFile;
use crate::error::Result;

/// Insert a new media file record.
#[allow(clippy::too_many_arguments)]
#[tracing::instrument(skip_all)]
pub(crate) async fn create<'e>(
    executor: impl sqlx::PgExecutor<'e>,
    user_id: UserId,
    folder: &str,
    filename: &str,
    s3_key: &str,
    content_type: &str,
    file_size_bytes: i64,
    media_type: &str,
    scan_status: &str,
) -> Result<DbMediaFile> {
    let row = sqlx::query_as!(
        DbMediaFile,
        r#"
        INSERT INTO media_files (user_id, folder, filename, s3_key, content_type, file_size_bytes, media_type, scan_status)
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
        RETURNING id AS "id: MediaFileId", user_id AS "user_id: UserId", folder, filename,
                  s3_key, content_type, file_size_bytes, media_type, scan_status,
                  created_at AS "created_at: chrono::DateTime<chrono::Utc>"
        "#,
        user_id as UserId,
        folder,
        filename,
        s3_key,
        content_type,
        file_size_bytes,
        media_type,
        scan_status,
    )
    .fetch_one(executor)
    .await?;

    Ok(row)
}

/// Safety cap on the media picker listing. Hitting it is logged at WARN so a
/// creator with more than this many clean files in one folder isn't silently
/// truncated (mirrors `versions::VERSIONS_LIST_HARD_CAP`).
pub(crate) const MEDIA_LIST_HARD_CAP: i64 = 500;

/// List media files for a user, optionally filtered by folder.
#[tracing::instrument(skip_all)]
pub(crate) async fn list_by_user_folder(
    pool: &PgPool,
    user_id: UserId,
    folder: Option<&str>,
) -> Result<Vec<DbMediaFile>> {
    let rows = if let Some(f) = folder {
        sqlx::query_as!(
            DbMediaFile,
            r#"SELECT id AS "id: MediaFileId", user_id AS "user_id: UserId", folder, filename,
                      s3_key, content_type, file_size_bytes, media_type, scan_status,
                      created_at AS "created_at: chrono::DateTime<chrono::Utc>"
               FROM media_files
               WHERE user_id = $1 AND folder = $2 AND scan_status = 'clean'
               ORDER BY created_at DESC LIMIT $3"#,
            user_id as UserId,
            f,
            MEDIA_LIST_HARD_CAP,
        )
        .fetch_all(pool)
        .await?
    } else {
        sqlx::query_as!(
            DbMediaFile,
            r#"SELECT id AS "id: MediaFileId", user_id AS "user_id: UserId", folder, filename,
                      s3_key, content_type, file_size_bytes, media_type, scan_status,
                      created_at AS "created_at: chrono::DateTime<chrono::Utc>"
               FROM media_files
               WHERE user_id = $1 AND scan_status = 'clean'
               ORDER BY created_at DESC LIMIT $2"#,
            user_id as UserId,
            MEDIA_LIST_HARD_CAP,
        )
        .fetch_all(pool)
        .await?
    };

    if rows.len() as i64 == MEDIA_LIST_HARD_CAP {
        tracing::warn!(
            %user_id, cap = MEDIA_LIST_HARD_CAP,
            "list_by_user_folder hit hard cap; some media omitted from the picker"
        );
    }

    Ok(rows)
}

/// List distinct folder names for a user.
#[tracing::instrument(skip_all)]
pub(crate) async fn list_folders(pool: &PgPool, user_id: UserId) -> Result<Vec<String>> {
    let folders: Vec<String> = sqlx::query_scalar!(
        "SELECT DISTINCT folder FROM media_files WHERE user_id = $1 ORDER BY folder",
        user_id as UserId,
    )
    .fetch_all(pool)
    .await?;

    Ok(folders)
}

/// Get a single media file by ID.
#[tracing::instrument(skip_all)]
pub(crate) async fn get_by_id(pool: &PgPool, id: MediaFileId) -> Result<Option<DbMediaFile>> {
    let row = sqlx::query_as!(
        DbMediaFile,
        r#"SELECT id AS "id: MediaFileId", user_id AS "user_id: UserId", folder, filename,
                  s3_key, content_type, file_size_bytes, media_type, scan_status,
                  created_at AS "created_at: chrono::DateTime<chrono::Utc>"
           FROM media_files WHERE id = $1"#,
        id as MediaFileId,
    )
    .fetch_optional(pool)
    .await?;

    Ok(row)
}

/// Delete a media file owned by `user_id`. Ownership is scoped IN the SQL
/// (`WHERE id = $1 AND user_id = $2`) so the delete can't touch another user's
/// row even if a caller forgot to pre-check (Sec-M2); returns `None` when the row
/// doesn't exist or isn't owned by `user_id`.
#[tracing::instrument(skip_all)]
pub(crate) async fn delete<'e>(
    executor: impl sqlx::PgExecutor<'e>,
    id: MediaFileId,
    user_id: UserId,
) -> Result<Option<DbMediaFile>> {
    let row = sqlx::query_as!(
        DbMediaFile,
        r#"DELETE FROM media_files WHERE id = $1 AND user_id = $2
           RETURNING id AS "id: MediaFileId", user_id AS "user_id: UserId", folder, filename,
                     s3_key, content_type, file_size_bytes, media_type, scan_status,
                     created_at AS "created_at: chrono::DateTime<chrono::Utc>""#,
        id as MediaFileId,
        user_id as UserId,
    )
    .fetch_optional(executor)
    .await?;

    Ok(row)
}

/// Count media files for a user.
#[allow(dead_code)]
#[tracing::instrument(skip_all)]
pub(crate) async fn count_by_user(pool: &PgPool, user_id: UserId) -> Result<i64> {
    let count: i64 = sqlx::query_scalar!(
        r#"SELECT COUNT(*) AS "count!" FROM media_files WHERE user_id = $1"#,
        user_id as UserId,
    )
    .fetch_one(pool)
    .await?;

    Ok(count)
}