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

use sqlx::PgPool;

use super::models::*;
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> {
    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?;

    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 by ID. Returns true if a row was deleted.
#[tracing::instrument(skip_all)]
pub async fn delete_collection(pool: &PgPool, collection_id: CollectionId) -> Result<bool> {
    let result = sqlx::query("DELETE FROM collections WHERE id = $1")
        .bind(collection_id)
        .execute(pool)
        .await?;

    Ok(result.rows_affected() > 0)
}

/// Get a collection by ID.
#[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
        "#,
    )
    .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
        "#,
    )
    .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 mut tx = pool.begin().await?;
    for (index, item_id) in item_ids.iter().enumerate() {
        sqlx::query(
            "UPDATE collection_items SET position = $1 WHERE collection_id = $2 AND item_id = $3",
        )
        .bind(index as i32)
        .bind(collection_id)
        .bind(item_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(())
}

/// Count how many of a user's collections contain a specific item.
#[tracing::instrument(skip_all)]
pub async fn count_user_collections_containing_item(
    pool: &PgPool,
    user_id: UserId,
    item_id: ItemId,
) -> Result<i64> {
    let (count,): (i64,) = sqlx::query_as(
        r#"
        SELECT COUNT(*) FROM collections c
        JOIN collection_items ci ON ci.collection_id = c.id
        WHERE c.user_id = $1 AND ci.item_id = $2
        "#,
    )
    .bind(user_id)
    .bind(item_id)
    .fetch_one(pool)
    .await?;

    Ok(count)
}

/// 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
        "#,
    )
    .bind(user_id)
    .bind(item_id)
    .fetch_all(pool)
    .await?;

    Ok(rows)
}