//! CRUD operations for fan collections (user-curated lists of items).

use sqlx::PgPool;

use super::models::{DbCollection, DbCollectionItemRow, DbCollectionWithCount};
use super::{CollectionId, ItemId, Slug, UserId};
use crate::error::Result;

/// Create a new collection for a user.
#[tracing::instrument(skip_all)]
pub async fn create_collection(
    pool: &PgPool,
    user_id: UserId,
    slug: &Slug,
    title: &str,
    description: Option<&str>,
    is_public: bool,
) -> Result<DbCollection> {
    // Collections intentionally REJECT a slug clash rather than auto-suffix
    // (unlike projects/items/sections): the slug is user-chosen and they should
    // be told it's taken. Map the per-user unique violation to a clean
    // validation error here so every caller, web and the CLI/service route,
    // gets the same 400 instead of a raw 500. This is the seal: no caller can
    // surface an unhandled 23505 from a collection insert.
    let collection = sqlx::query_as::<_, DbCollection>(
        r"
        INSERT INTO collections (user_id, slug, title, description, is_public)
        VALUES ($1, $2, $3, $4, $5)
        RETURNING *
        ",
    )
    .bind(user_id)
    .bind(slug)
    .bind(title)
    .bind(description)
    .bind(is_public)
    .fetch_one(pool)
    .await
    .map_err(|e| {
        let e = crate::error::AppError::from(e);
        if crate::helpers::is_unique_violation(&e) {
            crate::error::AppError::validation(
                "You already have a collection with this slug".to_string(),
            )
        } else {
            e
        }
    })?;

    Ok(collection)
}

/// Update a collection's title, description, and visibility.
#[tracing::instrument(skip_all)]
pub async fn update_collection(
    pool: &PgPool,
    collection_id: CollectionId,
    title: &str,
    description: Option<&str>,
    is_public: bool,
) -> Result<DbCollection> {
    let collection = sqlx::query_as::<_, DbCollection>(
        r"
        UPDATE collections
        SET title = $2, description = $3, is_public = $4, updated_at = NOW()
        WHERE id = $1
        RETURNING *
        ",
    )
    .bind(collection_id)
    .bind(title)
    .bind(description)
    .bind(is_public)
    .fetch_one(pool)
    .await?;

    Ok(collection)
}

/// Delete a collection owned by `owner_id`. Returns true if a row was deleted.
///
/// Ownership is scoped IN the SQL (defense in depth, mirroring
/// `blog_posts::delete_blog_post` / `media_files::delete`) so this can't remove
/// another user's collection even if a caller skips the upstream ownership check.
#[tracing::instrument(skip_all)]
pub async fn delete_collection(
    pool: &PgPool,
    collection_id: CollectionId,
    owner_id: UserId,
) -> Result<bool> {
    let result = sqlx::query("DELETE FROM collections WHERE id = $1 AND user_id = $2")
        .bind(collection_id)
        .bind(owner_id)
        .execute(pool)
        .await?;

    Ok(result.rows_affected() > 0)
}

#[tracing::instrument(skip_all)]
pub async fn get_collection_by_id(
    pool: &PgPool,
    collection_id: CollectionId,
) -> Result<Option<DbCollection>> {
    let collection = sqlx::query_as::<_, DbCollection>("SELECT * FROM collections WHERE id = $1")
        .bind(collection_id)
        .fetch_optional(pool)
        .await?;

    Ok(collection)
}

/// Get a collection by user ID and slug.
#[tracing::instrument(skip_all)]
pub async fn get_collection_by_user_and_slug(
    pool: &PgPool,
    user_id: UserId,
    slug: &Slug,
) -> Result<Option<DbCollection>> {
    let collection = sqlx::query_as::<_, DbCollection>(
        "SELECT * FROM collections WHERE user_id = $1 AND slug = $2",
    )
    .bind(user_id)
    .bind(slug)
    .fetch_optional(pool)
    .await?;

    Ok(collection)
}

/// Get all collections for a user (with item counts), for the dashboard.
#[tracing::instrument(skip_all)]
pub async fn get_collections_by_user(
    pool: &PgPool,
    user_id: UserId,
) -> Result<Vec<DbCollectionWithCount>> {
    let collections = sqlx::query_as::<_, DbCollectionWithCount>(
        r"
        SELECT c.*, COUNT(ci.item_id) AS item_count
        FROM collections c
        LEFT JOIN collection_items ci ON ci.collection_id = c.id
        WHERE c.user_id = $1
        GROUP BY c.id
        ORDER BY c.updated_at DESC
        LIMIT 500
        ",
    )
    .bind(user_id)
    .fetch_all(pool)
    .await?;

    Ok(collections)
}

/// Get public collections for a user (with item counts), for the profile page.
#[tracing::instrument(skip_all)]
pub async fn get_public_collections_by_user(
    pool: &PgPool,
    user_id: UserId,
) -> Result<Vec<DbCollectionWithCount>> {
    let collections = sqlx::query_as::<_, DbCollectionWithCount>(
        r"
        SELECT c.*, COUNT(ci.item_id) AS item_count
        FROM collections c
        LEFT JOIN collection_items ci ON ci.collection_id = c.id
        WHERE c.user_id = $1 AND c.is_public = true
        GROUP BY c.id
        ORDER BY c.updated_at DESC
        LIMIT 500
        ",
    )
    .bind(user_id)
    .fetch_all(pool)
    .await?;

    Ok(collections)
}

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

    Ok(count)
}

/// Add an item to a collection. Idempotent (ON CONFLICT DO NOTHING).
/// Appends at max(position)+1 atomically via INSERT...SELECT.
#[tracing::instrument(skip_all)]
pub async fn add_item_to_collection(
    pool: &PgPool,
    collection_id: CollectionId,
    item_id: ItemId,
) -> Result<()> {
    sqlx::query(
        r"
        INSERT INTO collection_items (collection_id, item_id, position)
        VALUES ($1, $2, COALESCE((SELECT MAX(position) FROM collection_items WHERE collection_id = $1), -1) + 1)
        ON CONFLICT (collection_id, item_id) DO NOTHING
        ",
    )
    .bind(collection_id)
    .bind(item_id)
    .execute(pool)
    .await?;

    // Touch the collection's updated_at
    sqlx::query("UPDATE collections SET updated_at = NOW() WHERE id = $1")
        .bind(collection_id)
        .execute(pool)
        .await?;

    Ok(())
}

/// Remove an item from a collection. Returns true if a row was deleted.
#[tracing::instrument(skip_all)]
pub async fn remove_item_from_collection(
    pool: &PgPool,
    collection_id: CollectionId,
    item_id: ItemId,
) -> Result<bool> {
    let result =
        sqlx::query("DELETE FROM collection_items WHERE collection_id = $1 AND item_id = $2")
            .bind(collection_id)
            .bind(item_id)
            .execute(pool)
            .await?;

    if result.rows_affected() > 0 {
        sqlx::query("UPDATE collections SET updated_at = NOW() WHERE id = $1")
            .bind(collection_id)
            .execute(pool)
            .await?;
    }

    Ok(result.rows_affected() > 0)
}

/// Get items in a collection, joined with item/project/user data, ordered by position.
#[tracing::instrument(skip_all)]
pub async fn get_collection_items(
    pool: &PgPool,
    collection_id: CollectionId,
) -> Result<Vec<DbCollectionItemRow>> {
    let items = sqlx::query_as::<_, DbCollectionItemRow>(
        r"
        SELECT
            ci.item_id,
            i.title,
            i.description,
            i.price_cents,
            i.item_type,
            u.username,
            p.title AS project_title,
            ci.position,
            ci.added_at
        FROM collection_items ci
        JOIN items i ON i.id = ci.item_id
        JOIN projects p ON p.id = i.project_id
        JOIN users u ON u.id = p.user_id
        WHERE ci.collection_id = $1
        ORDER BY ci.position
        LIMIT 1000
        ",
    )
    .bind(collection_id)
    .fetch_all(pool)
    .await?;

    Ok(items)
}

/// Get item IDs for multiple collections in a single query (batch, avoids N+1).
#[tracing::instrument(skip_all)]
pub async fn get_item_ids_by_collections(
    pool: &PgPool,
    collection_ids: &[CollectionId],
) -> Result<std::collections::HashMap<CollectionId, Vec<ItemId>>> {
    if collection_ids.is_empty() {
        return Ok(std::collections::HashMap::new());
    }
    let rows: Vec<(CollectionId, ItemId)> = sqlx::query_as(
        r"
        SELECT collection_id, item_id
        FROM collection_items
        WHERE collection_id = ANY($1)
        ORDER BY collection_id, position
        ",
    )
    .bind(collection_ids)
    .fetch_all(pool)
    .await?;

    let mut map: std::collections::HashMap<CollectionId, Vec<ItemId>> =
        std::collections::HashMap::new();
    for (cid, iid) in rows {
        map.entry(cid).or_default().push(iid);
    }
    Ok(map)
}

/// Count items in a collection.
#[tracing::instrument(skip_all)]
pub async fn count_collection_items(pool: &PgPool, collection_id: CollectionId) -> Result<i64> {
    let count: i64 =
        sqlx::query_scalar("SELECT COUNT(*) FROM collection_items WHERE collection_id = $1")
            .bind(collection_id)
            .fetch_one(pool)
            .await?;

    Ok(count)
}

/// Reorder items in a collection by assigning position from the given ID sequence.
/// Wrapped in a transaction so a crash mid-reorder doesn't leave inconsistent state.
#[tracing::instrument(skip_all)]
pub async fn reorder_collection_items(
    pool: &PgPool,
    collection_id: CollectionId,
    item_ids: &[ItemId],
) -> Result<()> {
    let ids: Vec<uuid::Uuid> = item_ids.iter().map(|id| *id.as_uuid()).collect();
    let mut tx = pool.begin().await?;
    // Single `UNNEST ... WITH ORDINALITY` update instead of one query per item,
    // atomic within the same transaction that bumps the collection's timestamp.
    sqlx::query(
        r"
        UPDATE collection_items AS ci
        SET position = ord.pos::int - 1
        FROM UNNEST($1::uuid[]) WITH ORDINALITY AS ord(id, pos)
        WHERE ci.collection_id = $2 AND ci.item_id = ord.id
        ",
    )
    .bind(&ids)
    .bind(collection_id)
    .execute(&mut *tx)
    .await?;

    sqlx::query("UPDATE collections SET updated_at = NOW() WHERE id = $1")
        .bind(collection_id)
        .execute(&mut *tx)
        .await?;
    tx.commit().await?;

    Ok(())
}

/// Get a user's collections with membership state for a specific item.
/// Returns (collection_id, title, is_in_collection) for the "add to collection" dropdown.
#[tracing::instrument(skip_all)]
pub async fn get_user_collections_for_item(
    pool: &PgPool,
    user_id: UserId,
    item_id: ItemId,
) -> Result<Vec<(CollectionId, String, bool)>> {
    let rows: Vec<(CollectionId, String, bool)> = sqlx::query_as(
        r"
        SELECT
            c.id,
            c.title,
            EXISTS(
                SELECT 1 FROM collection_items ci
                WHERE ci.collection_id = c.id AND ci.item_id = $2
            ) AS is_in_collection
        FROM collections c
        WHERE c.user_id = $1
        ORDER BY c.updated_at DESC
        LIMIT 500
        ",
    )
    .bind(user_id)
    .bind(item_id)
    .fetch_all(pool)
    .await?;

    Ok(rows)
}