//! Bundle item management: linking items into bundles and checking bundle-based access.

use sqlx::PgPool;

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

/// Add an item to a bundle at the given sort position.
///
/// Uses `ON CONFLICT DO UPDATE` so re-adding updates the sort position.
#[tracing::instrument(skip_all, fields(%bundle_id, %item_id, sort_order))]
pub async fn add_item_to_bundle(
    pool: &PgPool,
    bundle_id: ItemId,
    item_id: ItemId,
    sort_order: i32,
) -> Result<()> {
    sqlx::query(
        r#"
        INSERT INTO bundle_items (bundle_id, item_id, sort_order)
        VALUES ($1, $2, $3)
        ON CONFLICT (bundle_id, item_id) DO UPDATE SET sort_order = $3
        "#,
    )
    .bind(bundle_id)
    .bind(item_id)
    .bind(sort_order)
    .execute(pool)
    .await?;

    Ok(())
}

/// Remove an item from a bundle.
#[tracing::instrument(skip_all, fields(%bundle_id, %item_id))]
pub async fn remove_item_from_bundle(
    pool: &PgPool,
    bundle_id: ItemId,
    item_id: ItemId,
) -> Result<()> {
    sqlx::query("DELETE FROM bundle_items WHERE bundle_id = $1 AND item_id = $2")
        .bind(bundle_id)
        .bind(item_id)
        .execute(pool)
        .await?;

    Ok(())
}

/// Get all items included in a bundle, ordered by sort_order.
#[tracing::instrument(skip_all, fields(%bundle_id))]
pub async fn get_bundle_items(pool: &PgPool, bundle_id: ItemId) -> Result<Vec<DbItem>> {
    let items = sqlx::query_as::<_, DbItem>(
        r#"
        SELECT i.* FROM items i
        JOIN bundle_items bi ON i.id = bi.item_id
        WHERE bi.bundle_id = $1 AND i.deleted_at IS NULL
        ORDER BY bi.sort_order, bi.added_at
        LIMIT 100
        "#,
    )
    .bind(bundle_id)
    .fetch_all(pool)
    .await?;

    Ok(items)
}

/// Get the IDs of all bundles that contain a given item.
#[tracing::instrument(skip_all, fields(%item_id))]
pub async fn get_bundles_containing_item(
    pool: &PgPool,
    item_id: ItemId,
) -> Result<Vec<ItemId>> {
    let ids: Vec<ItemId> = sqlx::query_scalar(
        "SELECT bundle_id FROM bundle_items WHERE item_id = $1",
    )
    .bind(item_id)
    .fetch_all(pool)
    .await?;

    Ok(ids)
}

/// Check whether a user has access to an item through any purchased bundle.
///
/// Returns true if the user has a completed transaction for any bundle
/// that contains this item.
#[tracing::instrument(skip_all, fields(%user_id, %item_id))]
pub async fn has_access_via_bundle(
    pool: &PgPool,
    user_id: UserId,
    item_id: ItemId,
) -> Result<bool> {
    let exists: bool = sqlx::query_scalar(
        r#"
        SELECT EXISTS(
            SELECT 1 FROM bundle_items bi
            JOIN transactions t ON t.item_id = bi.bundle_id
            WHERE bi.item_id = $1
              AND t.buyer_id = $2
              AND t.status = 'completed'
        )
        "#,
    )
    .bind(item_id)
    .bind(user_id)
    .fetch_one(pool)
    .await?;

    Ok(exists)
}

/// Get all non-bundle items in a project (candidates for inclusion in a bundle).
///
/// Excludes the bundle itself (by `exclude_bundle_id`) and any items that are
/// already bundles (to prevent nesting).
#[tracing::instrument(skip_all, fields(%project_id, ?exclude_bundle_id))]
pub async fn get_bundleable_items(
    pool: &PgPool,
    project_id: ProjectId,
    exclude_bundle_id: Option<ItemId>,
) -> Result<Vec<DbItem>> {
    let items = sqlx::query_as::<_, DbItem>(
        r#"
        SELECT * FROM items
        WHERE project_id = $1
          AND item_type != 'bundle'
          AND deleted_at IS NULL
          AND ($2::UUID IS NULL OR id != $2)
        ORDER BY sort_order, created_at DESC
        LIMIT 500
        "#,
    )
    .bind(project_id)
    .bind(exclude_bundle_id)
    .fetch_all(pool)
    .await?;

    Ok(items)
}

/// Replace the full set of items in a bundle (transactional).
///
/// Deletes all existing bundle_items rows for the bundle and inserts the new set.
/// `item_ids` is an ordered list; sort_order is derived from position.
/// Validates that both the bundle and all items belong to `owner_id`.
#[tracing::instrument(skip_all, fields(%bundle_id, %owner_id, item_count = item_ids.len()))]
pub async fn set_bundle_items(
    pool: &PgPool,
    bundle_id: ItemId,
    item_ids: &[ItemId],
    owner_id: UserId,
) -> Result<()> {
    let mut tx = pool.begin().await?;

    // Verify bundle ownership
    let owns_bundle: bool = sqlx::query_scalar(
        "SELECT EXISTS(SELECT 1 FROM items i JOIN projects p ON p.id = i.project_id WHERE i.id = $1 AND p.user_id = $2)",
    )
    .bind(bundle_id)
    .bind(owner_id)
    .fetch_one(&mut *tx)
    .await?;
    if !owns_bundle {
        return Err(crate::error::AppError::Forbidden);
    }

    // Verify all items belong to the same owner
    if !item_ids.is_empty() {
        let owned_count: i64 = sqlx::query_scalar(
            "SELECT COUNT(*) FROM items i JOIN projects p ON p.id = i.project_id WHERE i.id = ANY($1) AND p.user_id = $2",
        )
        .bind(item_ids)
        .bind(owner_id)
        .fetch_one(&mut *tx)
        .await?;
        if owned_count != item_ids.len() as i64 {
            return Err(crate::error::AppError::BadRequest(
                "All bundle items must belong to you".to_string(),
            ));
        }
    }

    sqlx::query("DELETE FROM bundle_items WHERE bundle_id = $1")
        .bind(bundle_id)
        .execute(&mut *tx)
        .await?;

    if !item_ids.is_empty() {
        let bundle_ids: Vec<ItemId> = vec![bundle_id; item_ids.len()];
        let orders: Vec<i32> = (0..item_ids.len() as i32).collect();
        sqlx::query(
            r#"
            INSERT INTO bundle_items (bundle_id, item_id, sort_order)
            SELECT * FROM UNNEST($1::UUID[], $2::UUID[], $3::INT[])
            ON CONFLICT (bundle_id, item_id) DO UPDATE SET sort_order = EXCLUDED.sort_order
            "#,
        )
        .bind(&bundle_ids)
        .bind(item_ids)
        .bind(&orders)
        .execute(&mut *tx)
        .await?;
    }

    tx.commit().await?;
    Ok(())
}

/// Count how many items are in a bundle.
#[tracing::instrument(skip_all, fields(%bundle_id))]
pub async fn get_bundle_item_count(pool: &PgPool, bundle_id: ItemId) -> Result<i64> {
    let count: i64 = sqlx::query_scalar(
        "SELECT COUNT(*) FROM bundle_items WHERE bundle_id = $1",
    )
    .bind(bundle_id)
    .fetch_one(pool)
    .await?;

    Ok(count)
}

/// Batch child-item counts for several bundles in one query, keyed by bundle.
/// Bundles with no children are absent from the map (callers default to 0).
#[tracing::instrument(skip_all, fields(bundle_count = bundle_ids.len()))]
pub async fn get_bundle_item_counts(
    pool: &PgPool,
    bundle_ids: &[ItemId],
) -> Result<std::collections::HashMap<ItemId, i64>> {
    let rows: Vec<(ItemId, i64)> = sqlx::query_as(
        "SELECT bundle_id, COUNT(*) FROM bundle_items WHERE bundle_id = ANY($1) GROUP BY bundle_id",
    )
    .bind(bundle_ids)
    .fetch_all(pool)
    .await?;

    Ok(rows.into_iter().collect())
}

/// Get all bundle→child relationships for items within a project.
///
/// Returns `(bundle_id, child_item_id)` pairs ordered by bundle then sort order.
#[tracing::instrument(skip_all, fields(%project_id))]
pub async fn get_project_bundle_map(
    pool: &PgPool,
    project_id: ProjectId,
) -> Result<Vec<(ItemId, ItemId)>> {
    let rows: Vec<(ItemId, ItemId)> = sqlx::query_as(
        r#"
        SELECT bi.bundle_id, bi.item_id
        FROM bundle_items bi
        JOIN items i ON i.id = bi.bundle_id
        WHERE i.project_id = $1
        ORDER BY bi.bundle_id, bi.sort_order
        "#,
    )
    .bind(project_id)
    .fetch_all(pool)
    .await?;

    Ok(rows)
}

/// Batch-load bundle maps for multiple projects at once.
///
/// Returns (bundle_id, child_item_id) pairs for all bundles across the given projects.
#[tracing::instrument(skip_all, fields(project_count = project_ids.len()))]
pub async fn get_bundle_maps_by_projects(
    pool: &PgPool,
    project_ids: &[super::ProjectId],
) -> Result<Vec<(ItemId, ItemId)>> {
    let rows: Vec<(ItemId, ItemId)> = sqlx::query_as(
        r#"
        SELECT bi.bundle_id, bi.item_id
        FROM bundle_items bi
        JOIN items i ON i.id = bi.bundle_id
        WHERE i.project_id = ANY($1)
        ORDER BY bi.bundle_id, bi.sort_order
        "#,
    )
    .bind(project_ids)
    .fetch_all(pool)
    .await?;

    Ok(rows)
}

/// Check if an item is a member of a bundle.
#[tracing::instrument(skip_all, fields(%bundle_id, %child_id))]
pub async fn is_bundle_member(pool: &PgPool, bundle_id: ItemId, child_id: ItemId) -> Result<bool> {
    let exists: bool = sqlx::query_scalar(
        "SELECT EXISTS(SELECT 1 FROM bundle_items WHERE bundle_id = $1 AND item_id = $2)",
    )
    .bind(bundle_id)
    .bind(child_id)
    .fetch_one(pool)
    .await?;

    Ok(exists)
}

/// Set the `listed` flag on an item.
#[tracing::instrument(skip_all, fields(%item_id, listed))]
pub async fn set_item_listed(pool: &PgPool, item_id: ItemId, listed: bool) -> Result<()> {
    sqlx::query("UPDATE items SET listed = $2 WHERE id = $1")
        .bind(item_id)
        .bind(listed)
        .execute(pool)
        .await?;

    Ok(())
}

#[cfg(test)]
mod tests {
    use super::*;

    #[test]
    fn item_id_round_trip() {
        let id = ItemId::new();
        let s = id.to_string();
        let parsed: ItemId = s.parse().unwrap();
        assert_eq!(id, parsed);
    }

    #[test]
    fn item_id_nil() {
        let id = ItemId::nil();
        assert_eq!(id.to_string(), "00000000-0000-0000-0000-000000000000");
    }

    #[test]
    fn project_id_constructible() {
        let _id = ProjectId::new();
        let _nil = ProjectId::nil();
    }

    #[test]
    fn user_id_constructible() {
        let _id = UserId::new();
        let _nil = UserId::nil();
    }

    #[test]
    fn item_id_uniqueness() {
        let a = ItemId::new();
        let b = ItemId::new();
        assert_ne!(a, b);
    }

    #[test]
    fn sort_order_vector_generation() {
        // Mirrors the sort_order logic in set_bundle_items
        let item_count = 5;
        let orders: Vec<i32> = (0..item_count).collect();
        assert_eq!(orders, vec![0, 1, 2, 3, 4]);
    }

    #[test]
    fn sort_order_empty() {
        let orders: Vec<i32> = (0..0i32).collect();
        assert!(orders.is_empty());
    }

    #[test]
    fn bundle_id_replication_for_insert() {
        // Mirrors the bundle_ids vector in set_bundle_items
        let bundle_id = ItemId::nil();
        let item_ids = [ItemId::new(); 3];
        let bundle_ids: Vec<ItemId> = vec![bundle_id; item_ids.len()];
        assert_eq!(bundle_ids.len(), 3);
        assert!(bundle_ids.iter().all(|id| *id == bundle_id));
    }
}