//! CRUD operations for user-profile custom links.

use sqlx::PgPool;

use super::models::*;
use super::{CustomLinkId, UserId};
use crate::error::Result;

/// Create a custom link for a user, appended to the end of their link list.
/// Uses a single INSERT...SELECT to atomically compute the next sort_order.
#[tracing::instrument(skip_all)]
pub async fn create_custom_link(
    pool: &PgPool,
    user_id: UserId,
    url: &str,
    title: &str,
    description: Option<&str>,
) -> Result<DbCustomLink> {
    let link = sqlx::query_as::<_, DbCustomLink>(
        r#"
        INSERT INTO custom_links (user_id, url, title, description, sort_order)
        VALUES ($1, $2, $3, $4, COALESCE((SELECT MAX(sort_order) FROM custom_links WHERE user_id = $1), 0) + 1)
        RETURNING *
        "#,
    )
    .bind(user_id)
    .bind(url)
    .bind(title)
    .bind(description)
    .fetch_one(pool)
    .await?;

    Ok(link)
}

/// List all custom links for a user, ordered by sort_order.
///
/// Capped at 100 as a safety limit.
#[tracing::instrument(skip_all)]
pub async fn get_custom_links_by_user(pool: &PgPool, user_id: UserId) -> Result<Vec<DbCustomLink>> {
    let links = sqlx::query_as::<_, DbCustomLink>(
        "SELECT * FROM custom_links WHERE user_id = $1 ORDER BY sort_order LIMIT 100",
    )
    .bind(user_id)
    .fetch_all(pool)
    .await?;

    Ok(links)
}

/// Partially update a custom link's fields (COALESCE keeps existing values when `None`).
#[tracing::instrument(skip_all)]
pub async fn update_custom_link(
    pool: &PgPool,
    id: CustomLinkId,
    user_id: UserId,
    url: Option<&str>,
    title: Option<&str>,
    description: Option<&str>,
) -> Result<DbCustomLink> {
    let link = sqlx::query_as::<_, DbCustomLink>(
        r#"
        UPDATE custom_links
        SET url = COALESCE($2, url),
            title = COALESCE($3, title),
            description = COALESCE($4, description)
        WHERE id = $1 AND user_id = $5
        RETURNING *
        "#,
    )
    .bind(id)
    .bind(url)
    .bind(title)
    .bind(description)
    .bind(user_id)
    .fetch_one(pool)
    .await?;

    Ok(link)
}

/// Permanently delete a custom link by ID.
#[tracing::instrument(skip_all)]
pub async fn delete_custom_link(pool: &PgPool, id: CustomLinkId, user_id: UserId) -> Result<()> {
    sqlx::query("DELETE FROM custom_links WHERE id = $1 AND user_id = $2")
        .bind(id)
        .bind(user_id)
        .execute(pool)
        .await?;

    Ok(())
}

/// Reorder a user's custom links by assigning sort_order 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_custom_links(pool: &PgPool, user_id: UserId, link_ids: &[CustomLinkId]) -> Result<()> {
    let mut tx = pool.begin().await?;
    for (index, link_id) in link_ids.iter().enumerate() {
        sqlx::query("UPDATE custom_links SET sort_order = $1 WHERE id = $2 AND user_id = $3")
            .bind(index as i32)
            .bind(link_id)
            .bind(user_id)
            .execute(&mut *tx)
            .await?;
    }
    tx.commit().await?;

    Ok(())
}

/// Check if a custom link belongs to a user (efficient ownership check)
#[tracing::instrument(skip_all)]
pub async fn user_owns_custom_link(pool: &PgPool, user_id: UserId, link_id: CustomLinkId) -> Result<bool> {
    let exists: bool = sqlx::query_scalar(
        "SELECT EXISTS(SELECT 1 FROM custom_links WHERE id = $1 AND user_id = $2)",
    )
    .bind(link_id)
    .bind(user_id)
    .fetch_one(pool)
    .await?;

    Ok(exists)
}