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

use sqlx::PgPool;

use super::id_types::*;
use super::models::DbMediaFile;
use crate::error::Result;

/// Insert a new media file record.
#[allow(clippy::too_many_arguments)]
#[tracing::instrument(skip_all)]
pub 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 *
        "#,
    )
    .bind(user_id)
    .bind(folder)
    .bind(filename)
    .bind(s3_key)
    .bind(content_type)
    .bind(file_size_bytes)
    .bind(media_type)
    .bind(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 const MEDIA_LIST_HARD_CAP: i64 = 500;

/// List media files for a user, optionally filtered by folder.
#[tracing::instrument(skip_all)]
pub 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>(
            "SELECT * FROM media_files WHERE user_id = $1 AND folder = $2 AND scan_status = 'clean' ORDER BY created_at DESC LIMIT $3",
        )
        .bind(user_id)
        .bind(f)
        .bind(MEDIA_LIST_HARD_CAP)
        .fetch_all(pool)
        .await?
    } else {
        sqlx::query_as::<_, DbMediaFile>(
            "SELECT * FROM media_files WHERE user_id = $1 AND scan_status = 'clean' ORDER BY created_at DESC LIMIT $2",
        )
        .bind(user_id)
        .bind(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 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",
    )
    .bind(user_id)
    .fetch_all(pool)
    .await?;

    Ok(folders)
}

/// Get a single media file by ID.
#[tracing::instrument(skip_all)]
pub async fn get_by_id(pool: &PgPool, id: MediaFileId) -> Result<Option<DbMediaFile>> {
    let row = sqlx::query_as::<_, DbMediaFile>(
        "SELECT * FROM media_files WHERE id = $1",
    )
    .bind(id)
    .fetch_optional(pool)
    .await?;

    Ok(row)
}

/// Delete a media file by ID.
#[tracing::instrument(skip_all)]
pub async fn delete<'e>(
    executor: impl sqlx::PgExecutor<'e>,
    id: MediaFileId,
) -> Result<Option<DbMediaFile>> {
    let row = sqlx::query_as::<_, DbMediaFile>(
        "DELETE FROM media_files WHERE id = $1 RETURNING *",
    )
    .bind(id)
    .fetch_optional(executor)
    .await?;

    Ok(row)
}

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

    Ok(count)
}