//! 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 super::{ImageId, 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: ImageId,
    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>> {
    // Fail-closed render gate: only images whose scan cleared are carouselled.
    // Pending/held rows stay hidden. The per-entity cap (`count_for_item`) still
    // counts every row, so a held image cannot be re-uploaded around the limit.
    let rows = sqlx::query_as::<_, GalleryImage>(
        "SELECT id, s3_key, image_url, alt, position, file_size_bytes \
         FROM item_images WHERE item_id = $1 AND scan_status = 'clean' \
         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<ImageId> {
    let id: ImageId = 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)
}

/// Look up an item gallery row by its `s3_key` (idempotency guard for confirm:
/// a replayed upload-confirm finds the already-inserted row and short-circuits
/// instead of double-inserting + double-charging storage). Takes an executor so
/// it runs inside the confirm transaction under the gallery advisory lock.
#[tracing::instrument(skip_all)]
pub async fn find_for_item_by_key<'e>(
    executor: impl PgExecutor<'e>,
    item_id: ItemId,
    s3_key: &str,
) -> Result<Option<GalleryImage>> {
    let row = sqlx::query_as::<_, GalleryImage>(
        "SELECT id, s3_key, image_url, alt, position, file_size_bytes \
         FROM item_images WHERE item_id = $1 AND s3_key = $2",
    )
    .bind(item_id)
    .bind(s3_key)
    .fetch_optional(executor)
    .await?;
    Ok(row)
}

/// 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: ImageId,
    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.
///
/// One set-based `UPDATE ... FROM UNNEST(...) WITH ORDINALITY` rather than a
/// per-id UPDATE loop, `WITH ORDINALITY` is 1-based, so `position = ord - 1`
/// keeps the prior 0-based ordering.
#[tracing::instrument(skip_all)]
pub async fn reorder_item(pool: &PgPool, item_id: ItemId, ordered_ids: &[ImageId]) -> Result<()> {
    sqlx::query(
        "UPDATE item_images AS t SET position = o.ord - 1 \
         FROM UNNEST($1::uuid[]) WITH ORDINALITY AS o(id, ord) \
         WHERE t.id = o.id AND t.item_id = $2",
    )
    .bind(ordered_ids)
    .bind(item_id)
    .execute(pool)
    .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>> {
    // Fail-closed render gate: only cleared images are carouselled (see
    // `list_for_item`). The cap count (`count_for_project`) still counts all rows.
    let rows = sqlx::query_as::<_, GalleryImage>(
        "SELECT id, s3_key, image_url, alt, position, file_size_bytes \
         FROM project_images WHERE project_id = $1 AND scan_status = 'clean' \
         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<ImageId> {
    let id: ImageId = 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)
}

/// Look up a project gallery row by its `s3_key` (confirm idempotency guard,
/// see [`find_for_item_by_key`]).
#[tracing::instrument(skip_all)]
pub async fn find_for_project_by_key<'e>(
    executor: impl PgExecutor<'e>,
    project_id: ProjectId,
    s3_key: &str,
) -> Result<Option<GalleryImage>> {
    let row = sqlx::query_as::<_, GalleryImage>(
        "SELECT id, s3_key, image_url, alt, position, file_size_bytes \
         FROM project_images WHERE project_id = $1 AND s3_key = $2",
    )
    .bind(project_id)
    .bind(s3_key)
    .fetch_optional(executor)
    .await?;
    Ok(row)
}

/// 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: ImageId,
    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`. Set-based, like
/// [`reorder_item`].
#[tracing::instrument(skip_all)]
pub async fn reorder_project(
    pool: &PgPool,
    project_id: ProjectId,
    ordered_ids: &[ImageId],
) -> Result<()> {
    sqlx::query(
        "UPDATE project_images AS t SET position = o.ord - 1 \
         FROM UNNEST($1::uuid[]) WITH ORDINALITY AS o(id, ord) \
         WHERE t.id = o.id AND t.project_id = $2",
    )
    .bind(ordered_ids)
    .bind(project_id)
    .execute(pool)
    .await?;
    Ok(())
}

// Lifecycle key collection (delete / purge)
//
// Gallery rows CASCADE away when their parent item/project is deleted, so any
// destructive path must collect their `s3_key`s BEFORE the cascade or the S3
// objects orphan with no durable record (Run #18 Storage B2). These collectors
// are the gallery half of the per-entity key sweep the item/version collectors
// already do.

/// S3 keys of every gallery image (item carousel + project carousel) belonging
/// to a project, both `item_images` (via the project's items) and
/// `project_images`. Call before deleting the project.
#[tracing::instrument(skip_all)]
pub async fn s3_keys_for_project<'e>(
    executor: impl PgExecutor<'e>,
    project_id: ProjectId,
) -> Result<Vec<String>> {
    let keys: Vec<String> = sqlx::query_scalar(
        r"
        SELECT ii.s3_key
        FROM item_images ii JOIN items i ON ii.item_id = i.id
        WHERE i.project_id = $1
        UNION ALL
        SELECT pi.s3_key
        FROM project_images pi
        WHERE pi.project_id = $1
        ",
    )
    .bind(project_id)
    .fetch_all(executor)
    .await?;
    Ok(keys)
}

/// S3 keys of item-gallery images belonging to items soft-deleted more than 7
/// days ago (the purge horizon). Call before the purge CASCADE destroys the
/// `item_images` rows. (Project galleries are not soft-deleted, projects are
/// hard-deleted via [`s3_keys_for_project`].)
#[tracing::instrument(skip_all)]
pub async fn s3_keys_for_expired_purged_items(pool: &PgPool) -> Result<Vec<String>> {
    let keys: Vec<String> = sqlx::query_scalar(
        r"
        SELECT ii.s3_key
        FROM item_images ii JOIN items i ON ii.item_id = i.id
        WHERE i.deleted_at IS NOT NULL AND i.deleted_at < NOW() - INTERVAL '7 days'
        ",
    )
    .fetch_all(pool)
    .await?;
    Ok(keys)
}