//! Ordered image galleries for items and projects (launchplan S.1).
//!
//! Two near-identical tables (`item_images` / `project_images`) keyed by parent,
//! each row an image with an alt string and an explicit `position`. The single
//! `cover_image_url` on items/projects is unaffected — it stays the OG/card
//! image; these rows are the additive carousel gallery.
//!
//! `s3_key` + `file_size_bytes` are stored so a delete decrements storage from
//! the recorded size (no S3 HEAD) and the cleanup garbage-collector recognizes
//! live gallery objects via the S3_KEY_REFS registry.

use sqlx::{PgExecutor, PgPool};
use uuid::Uuid;

use super::{ItemId, ProjectId};
use crate::error::Result;

/// One gallery image row (shared shape for item and project galleries).
#[derive(Debug, Clone, sqlx::FromRow)]
pub struct GalleryImage {
    pub id: Uuid,
    pub s3_key: String,
    pub image_url: String,
    pub alt: String,
    pub position: i32,
    pub file_size_bytes: i64,
}

/// Maximum gallery images per entity. Keeps a single creator from ballooning
/// storage with one listing and bounds the carousel length.
pub const MAX_GALLERY_IMAGES: i64 = 8;

// ---------------------------------------------------------------------------
// Item galleries
// ---------------------------------------------------------------------------

/// List an item's gallery images in display order.
#[tracing::instrument(skip_all)]
pub async fn list_for_item<'e>(executor: impl PgExecutor<'e>, item_id: ItemId) -> Result<Vec<GalleryImage>> {
    let rows = sqlx::query_as::<_, GalleryImage>(
        "SELECT id, s3_key, image_url, alt, position, file_size_bytes \
         FROM item_images WHERE item_id = $1 ORDER BY position, created_at",
    )
    .bind(item_id)
    .fetch_all(executor)
    .await?;
    Ok(rows)
}

/// Count an item's gallery images (for the per-entity cap check).
#[tracing::instrument(skip_all)]
pub async fn count_for_item<'e>(executor: impl PgExecutor<'e>, item_id: ItemId) -> Result<i64> {
    let count: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM item_images WHERE item_id = $1")
        .bind(item_id)
        .fetch_one(executor)
        .await?;
    Ok(count)
}

/// Insert a gallery image at the end (position = current max + 1). Returns the
/// new row id. Takes an executor so it can run inside the confirm transaction.
#[tracing::instrument(skip_all)]
pub async fn insert_for_item<'e>(
    executor: impl PgExecutor<'e>,
    item_id: ItemId,
    s3_key: &str,
    image_url: &str,
    alt: &str,
    file_size_bytes: i64,
) -> Result<Uuid> {
    let id: Uuid = sqlx::query_scalar(
        "INSERT INTO item_images (item_id, s3_key, image_url, alt, position, file_size_bytes) \
         VALUES ($1, $2, $3, $4, \
                 COALESCE((SELECT MAX(position) + 1 FROM item_images WHERE item_id = $1), 0), \
                 $5) \
         RETURNING id",
    )
    .bind(item_id)
    .bind(s3_key)
    .bind(image_url)
    .bind(alt)
    .bind(file_size_bytes)
    .fetch_one(executor)
    .await?;
    Ok(id)
}

/// Delete one item gallery image IF it belongs to an item owned by `user_id`.
/// Returns the deleted row (for storage decrement + S3 cleanup), or None if it
/// did not exist or the caller does not own it.
#[tracing::instrument(skip_all)]
pub async fn delete_for_item<'e>(
    executor: impl PgExecutor<'e>,
    image_id: Uuid,
    user_id: super::UserId,
) -> Result<Option<GalleryImage>> {
    let row = sqlx::query_as::<_, GalleryImage>(
        "DELETE FROM item_images WHERE id = $1 AND item_id IN ( \
             SELECT i.id FROM items i JOIN projects p ON p.id = i.project_id WHERE p.user_id = $2 \
         ) RETURNING id, s3_key, image_url, alt, position, file_size_bytes",
    )
    .bind(image_id)
    .bind(user_id)
    .fetch_optional(executor)
    .await?;
    Ok(row)
}

/// Reorder an item's gallery to match `ordered_ids` (ids not belonging to the
/// item are ignored). Positions are assigned by list order.
#[tracing::instrument(skip_all)]
pub async fn reorder_item(pool: &PgPool, item_id: ItemId, ordered_ids: &[Uuid]) -> Result<()> {
    let mut tx = pool.begin().await?;
    for (pos, id) in ordered_ids.iter().enumerate() {
        sqlx::query("UPDATE item_images SET position = $1 WHERE id = $2 AND item_id = $3")
            .bind(pos as i32)
            .bind(id)
            .bind(item_id)
            .execute(&mut *tx)
            .await?;
    }
    tx.commit().await?;
    Ok(())
}

// ---------------------------------------------------------------------------
// Project galleries
// ---------------------------------------------------------------------------

/// List a project's gallery images in display order.
#[tracing::instrument(skip_all)]
pub async fn list_for_project<'e>(executor: impl PgExecutor<'e>, project_id: ProjectId) -> Result<Vec<GalleryImage>> {
    let rows = sqlx::query_as::<_, GalleryImage>(
        "SELECT id, s3_key, image_url, alt, position, file_size_bytes \
         FROM project_images WHERE project_id = $1 ORDER BY position, created_at",
    )
    .bind(project_id)
    .fetch_all(executor)
    .await?;
    Ok(rows)
}

/// Count a project's gallery images (for the per-entity cap check).
#[tracing::instrument(skip_all)]
pub async fn count_for_project<'e>(executor: impl PgExecutor<'e>, project_id: ProjectId) -> Result<i64> {
    let count: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM project_images WHERE project_id = $1")
        .bind(project_id)
        .fetch_one(executor)
        .await?;
    Ok(count)
}

/// Insert a project gallery image at the end. Returns the new row id.
#[tracing::instrument(skip_all)]
pub async fn insert_for_project<'e>(
    executor: impl PgExecutor<'e>,
    project_id: ProjectId,
    s3_key: &str,
    image_url: &str,
    alt: &str,
    file_size_bytes: i64,
) -> Result<Uuid> {
    let id: Uuid = sqlx::query_scalar(
        "INSERT INTO project_images (project_id, s3_key, image_url, alt, position, file_size_bytes) \
         VALUES ($1, $2, $3, $4, \
                 COALESCE((SELECT MAX(position) + 1 FROM project_images WHERE project_id = $1), 0), \
                 $5) \
         RETURNING id",
    )
    .bind(project_id)
    .bind(s3_key)
    .bind(image_url)
    .bind(alt)
    .bind(file_size_bytes)
    .fetch_one(executor)
    .await?;
    Ok(id)
}

/// Delete one project gallery image IF the project is owned by `user_id`.
/// Returns the deleted row, or None if missing / not owned.
#[tracing::instrument(skip_all)]
pub async fn delete_for_project<'e>(
    executor: impl PgExecutor<'e>,
    image_id: Uuid,
    user_id: super::UserId,
) -> Result<Option<GalleryImage>> {
    let row = sqlx::query_as::<_, GalleryImage>(
        "DELETE FROM project_images WHERE id = $1 AND project_id IN ( \
             SELECT id FROM projects WHERE user_id = $2 \
         ) RETURNING id, s3_key, image_url, alt, position, file_size_bytes",
    )
    .bind(image_id)
    .bind(user_id)
    .fetch_optional(executor)
    .await?;
    Ok(row)
}

/// Reorder a project's gallery to match `ordered_ids`.
#[tracing::instrument(skip_all)]
pub async fn reorder_project(pool: &PgPool, project_id: ProjectId, ordered_ids: &[Uuid]) -> Result<()> {
    let mut tx = pool.begin().await?;
    for (pos, id) in ordered_ids.iter().enumerate() {
        sqlx::query("UPDATE project_images SET position = $1 WHERE id = $2 AND project_id = $3")
            .bind(pos as i32)
            .bind(id)
            .bind(project_id)
            .execute(&mut *tx)
            .await?;
    }
    tx.commit().await?;
    Ok(())
}