//! Bulk and structural item operations: cross-project moves, batch
//! publish/unpublish/delete/repricing/tagging, and item duplication.

use sqlx::PgPool;

use crate::db::models::*;
use crate::db::{ItemId, PriceCents, ProjectId, UserId};
use crate::error::Result;

pub async fn move_item(
    pool: &PgPool,
    project_id: ProjectId,
    user_id: UserId,
    item_id: ItemId,
    direction: &str,
) -> Result<()> {
    let mut tx = pool.begin().await?;

    // Lock and fetch item IDs in display order (scoped to projects owned by user)
    let item_ids: Vec<ItemId> = sqlx::query_scalar(
        r#"
        SELECT id FROM items
        WHERE project_id = $1
          AND project_id IN (SELECT id FROM projects WHERE user_id = $2)
        ORDER BY sort_order, created_at DESC LIMIT 500 FOR UPDATE
        "#,
    )
    .bind(project_id)
    .bind(user_id)
    .fetch_all(&mut *tx)
    .await?;

    let Some(pos) = item_ids.iter().position(|id| *id == item_id) else {
        return Ok(());
    };

    let swap_pos = match direction {
        "up" if pos > 0 => pos - 1,
        "down" if pos + 1 < item_ids.len() => pos + 1,
        _ => return Ok(()),
    };

    // Normalize all sort_orders, swapping the target pair (single batch UPDATE)
    let mut ids = Vec::with_capacity(item_ids.len());
    let mut orders = Vec::with_capacity(item_ids.len());
    for (i, id) in item_ids.iter().enumerate() {
        ids.push(*id);
        orders.push(if i == pos {
            swap_pos as i32
        } else if i == swap_pos {
            pos as i32
        } else {
            i as i32
        });
    }
    sqlx::query(
        "UPDATE items SET sort_order = batch.ord FROM UNNEST($1::UUID[], $2::INT[]) AS batch(id, ord) WHERE items.id = batch.id",
    )
    .bind(&ids)
    .bind(&orders)
    .execute(&mut *tx)
    .await?;

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

/// Bulk-publish items: set `is_public = true` and clear any scheduled `publish_at`.
///
/// Only affects items matching both the given IDs and project. Returns rows affected.
#[tracing::instrument(skip_all)]
pub async fn bulk_publish(
    pool: &PgPool,
    item_ids: &[ItemId],
    project_id: ProjectId,
    user_id: UserId,
) -> Result<u64> {
    let result = sqlx::query(
        r#"
        UPDATE items
        SET is_public = true, publish_at = NULL, updated_at = NOW()
        WHERE id = ANY($1) AND project_id = $2
          AND project_id IN (SELECT id FROM projects WHERE user_id = $3)
          AND removed_by_admin = false
        "#,
    )
    .bind(item_ids)
    .bind(project_id)
    .bind(user_id)
    .execute(pool)
    .await?;

    Ok(result.rows_affected())
}

/// Bulk-unpublish items: set `is_public = false`.
///
/// Only affects items matching both the given IDs and project. Returns rows affected.
#[tracing::instrument(skip_all)]
pub async fn bulk_unpublish(
    pool: &PgPool,
    item_ids: &[ItemId],
    project_id: ProjectId,
    user_id: UserId,
) -> Result<u64> {
    let result = sqlx::query(
        r#"
        UPDATE items
        SET is_public = false, updated_at = NOW()
        WHERE id = ANY($1) AND project_id = $2
          AND project_id IN (SELECT id FROM projects WHERE user_id = $3)
        "#,
    )
    .bind(item_ids)
    .bind(project_id)
    .bind(user_id)
    .execute(pool)
    .await?;

    Ok(result.rows_affected())
}

/// Soft-delete items from a project (sets deleted_at, recoverable for 7 days).
///
/// Only affects items matching both the given IDs and project. Returns rows affected.
#[tracing::instrument(skip_all)]
pub async fn bulk_delete(
    pool: &PgPool,
    item_ids: &[ItemId],
    project_id: ProjectId,
    user_id: UserId,
) -> Result<u64> {
    let result = sqlx::query(
        r#"
        UPDATE items SET deleted_at = NOW(), is_public = false
        WHERE id = ANY($1) AND project_id = $2 AND deleted_at IS NULL
          AND project_id IN (SELECT id FROM projects WHERE user_id = $3)
        "#,
    )
    .bind(item_ids)
    .bind(project_id)
    .bind(user_id)
    .execute(pool)
    .await?;

    Ok(result.rows_affected())
}

/// Bulk-update price on selected items.
///
/// Only affects items matching both the given IDs and project. Returns rows affected.
#[tracing::instrument(skip_all)]
pub async fn bulk_update_price(
    pool: &PgPool,
    item_ids: &[ItemId],
    project_id: ProjectId,
    user_id: UserId,
    price_cents: PriceCents,
) -> Result<u64> {
    let result = sqlx::query(
        r#"
        UPDATE items SET price_cents = $4
        WHERE id = ANY($1) AND project_id = $2
          AND project_id IN (SELECT id FROM projects WHERE user_id = $3)
        "#,
    )
    .bind(item_ids)
    .bind(project_id)
    .bind(user_id)
    .bind(price_cents)
    .execute(pool)
    .await?;

    Ok(result.rows_affected())
}

/// Bulk-add a tag to selected items (skips duplicates via ON CONFLICT).
///
/// Returns number of new tag associations created.
#[tracing::instrument(skip_all)]
pub async fn bulk_add_tag(
    pool: &PgPool,
    item_ids: &[ItemId],
    project_id: ProjectId,
    user_id: UserId,
    tag_id: crate::db::TagId,
) -> Result<u64> {
    // Verify all items belong to the project owned by this user,
    // then insert tag associations for each.
    let result = sqlx::query(
        r#"
        INSERT INTO item_tags (item_id, tag_id)
        SELECT i.id, $4
        FROM items i
        JOIN projects p ON i.project_id = p.id
        WHERE i.id = ANY($1) AND i.project_id = $2 AND p.user_id = $3
        ON CONFLICT (item_id, tag_id) DO NOTHING
        "#,
    )
    .bind(item_ids)
    .bind(project_id)
    .bind(user_id)
    .bind(tag_id)
    .execute(pool)
    .await?;

    Ok(result.rows_affected())
}

/// Duplicate an item and its metadata (tags, chapters, content insertion placements).
///
/// Creates a draft copy with "Copy of ..." title. Does not copy versions (S3 files),
/// license keys, download codes, or discount codes.
#[tracing::instrument(skip_all)]
pub async fn duplicate_item(pool: &PgPool, source_id: ItemId, user_id: UserId) -> Result<DbItem> {
    let mut tx = pool.begin().await?;

    // Generate a unique slug for the copy (verify ownership via project)
    let source = sqlx::query_as::<_, DbItem>(
        "SELECT * FROM items WHERE id = $1 AND project_id IN (SELECT id FROM projects WHERE user_id = $2)",
    )
    .bind(source_id)
    .bind(user_id)
    .fetch_one(&mut *tx)
    .await?;
    let copy_title = format!("Copy of {}", &source.title);
    let copy_title: String = copy_title.chars().take(200).collect();
    let mut slug = crate::helpers::slugify(&copy_title);
    if super::item_slug_exists(&mut *tx, source.project_id, &slug).await? {
        let base = slug.clone();
        let mut counter = 2u32;
        loop {
            if counter > 100 {
                return Err(crate::error::AppError::BadRequest(
                    "Too many copies with similar names. Rename an existing copy first.".to_string(),
                ));
            }
            slug = crate::db::validated_types::Slug::from_trusted(format!("{}-{}", base, counter));
            if !super::item_slug_exists(&mut *tx, source.project_id, &slug).await? {
                break;
            }
            counter += 1;
        }
    }

    // Step 1: Clone item row
    let new_item = sqlx::query_as::<_, DbItem>(
        r#"
        INSERT INTO items (
            project_id, title, description, price_cents, item_type, thumbnail_url,
            sort_order, body, word_count, reading_time_minutes, duration_seconds,
            episode_number, enable_license_keys, default_max_activations,
            pwyw_enabled, pwyw_min_cents, is_public, slug
        )
        SELECT
            project_id, LEFT('Copy of ' || title, 200), description, price_cents,
            item_type, thumbnail_url, sort_order, body, word_count,
            reading_time_minutes, duration_seconds, episode_number,
            enable_license_keys, default_max_activations, pwyw_enabled,
            pwyw_min_cents, false, $2
        FROM items WHERE id = $1
        RETURNING *
        "#,
    )
    .bind(source_id)
    .bind(&slug)
    .fetch_one(&mut *tx)
    .await?;

    // Step 2: Copy tags
    sqlx::query(
        r#"
        INSERT INTO item_tags (item_id, tag_id, is_primary)
        SELECT $2, tag_id, is_primary FROM item_tags WHERE item_id = $1
        "#,
    )
    .bind(source_id)
    .bind(new_item.id)
    .execute(&mut *tx)
    .await?;

    // Step 3: Copy chapters
    sqlx::query(
        r#"
        INSERT INTO chapters (item_id, title, start_seconds, sort_order)
        SELECT $2, title, start_seconds, sort_order FROM chapters WHERE item_id = $1
        "#,
    )
    .bind(source_id)
    .bind(new_item.id)
    .execute(&mut *tx)
    .await?;

    // Step 4: Copy content insertion placements
    sqlx::query(
        r#"
        INSERT INTO content_insertion_placements (item_id, insertion_id, position, offset_ms, sort_order)
        SELECT $2, insertion_id, position, offset_ms, sort_order
        FROM content_insertion_placements WHERE item_id = $1
        "#,
    )
    .bind(source_id)
    .bind(new_item.id)
    .execute(&mut *tx)
    .await?;

    tx.commit().await?;

    Ok(new_item)
}