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

use sqlx::PgPool;

use crate::db::models::DbItem;
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 base_slug = crate::helpers::slugify(&copy_title).to_string();

    // Step 1: Clone item row with a collision-safe slug. A pre-check
    // (`item_slug_exists`) followed by an INSERT races two concurrent
    // duplicates onto the same slug, the loser hit a raw 23505 -> 500. Instead
    // attempt the INSERT under a savepoint and retry with a numeric suffix on a
    // unique violation, so the DB's unique index is the arbiter, not a
    // check-then-write window. Mirrors `helpers::insert_with_unique_slug`,
    // adapted to run inside this transaction: a bare 23505 aborts the tx, so
    // each attempt is wrapped in a savepoint and `ROLLBACK TO SAVEPOINT`
    // recovers the tx for the next try.
    let mut suffix = 1u32;
    let new_item = loop {
        let candidate = if suffix == 1 {
            base_slug.clone()
        } else {
            format!("{base_slug}-{suffix}")
        };
        sqlx::query("SAVEPOINT dup_item_slug")
            .execute(&mut *tx)
            .await?;
        let res = 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(&candidate)
        .fetch_one(&mut *tx)
        .await;
        match res {
            Ok(item) => {
                sqlx::query("RELEASE SAVEPOINT dup_item_slug")
                    .execute(&mut *tx)
                    .await?;
                break item;
            }
            Err(e) => {
                sqlx::query("ROLLBACK TO SAVEPOINT dup_item_slug")
                    .execute(&mut *tx)
                    .await?;
                let app_err = crate::error::AppError::from(e);
                if crate::helpers::is_unique_violation(&app_err) && suffix < 100 {
                    suffix += 1;
                    continue;
                }
                return Err(app_err);
            }
        }
    };

    // 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)
}