use sqlx::{PgExecutor, PgPool};
use uuid::Uuid;

use crate::domain::tag::{Tag, TagRef};
use crate::error::{AppError, AppResult};

/// True when this manga-tag attachment is new (so the API can return
/// 201 vs 200), plus the canonical tag row.
pub struct AttachOutcome {
    pub tag: Tag,
    pub created_attachment: bool,
}

pub async fn upsert_by_name<'e, E: PgExecutor<'e>>(
    executor: E,
    name: &str,
) -> AppResult<Tag> {
    let trimmed = name.trim();
    if trimmed.is_empty() {
        return Err(AppError::ValidationFailed {
            message: "tag name cannot be empty".into(),
            details: serde_json::json!({ "name": "required" }),
        });
    }
    if trimmed.chars().count() > 64 {
        return Err(AppError::ValidationFailed {
            message: "tag name too long".into(),
            details: serde_json::json!({ "name": "max 64 characters" }),
        });
    }
    let row = sqlx::query_as::<_, Tag>(
        r#"
        INSERT INTO tags (name)
        VALUES ($1)
        ON CONFLICT (lower(name)) DO UPDATE SET name = tags.name
        RETURNING id, name, created_at
        "#,
    )
    .bind(trimmed)
    .fetch_one(executor)
    .await?;
    Ok(row)
}

/// Attach a tag to a manga, idempotently. Returns whether a new
/// attachment row was created so the API can pick 201 vs 200.
pub async fn attach_to_manga(
    pool: &PgPool,
    manga_id: Uuid,
    name: &str,
    added_by: Uuid,
) -> AppResult<AttachOutcome> {
    let mut tx = pool.begin().await?;
    let tag = upsert_by_name(&mut *tx, name).await?;
    let rows_affected = sqlx::query(
        r#"
        INSERT INTO manga_tags (manga_id, tag_id, added_by)
        VALUES ($1, $2, $3)
        ON CONFLICT (manga_id, tag_id) DO NOTHING
        "#,
    )
    .bind(manga_id)
    .bind(tag.id)
    .bind(added_by)
    .execute(&mut *tx)
    .await?
    .rows_affected();
    tx.commit().await?;
    Ok(AttachOutcome { tag, created_attachment: rows_affected > 0 })
}

/// Look up who attached a given (manga, tag) pair, if any. Returns
/// `None` if the attachment doesn't exist. The outer `AppResult` only
/// surfaces DB errors.
pub async fn find_attacher(
    pool: &PgPool,
    manga_id: Uuid,
    tag_id: Uuid,
) -> AppResult<Option<Option<Uuid>>> {
    let row: Option<(Option<Uuid>,)> = sqlx::query_as(
        "SELECT added_by FROM manga_tags WHERE manga_id = $1 AND tag_id = $2",
    )
    .bind(manga_id)
    .bind(tag_id)
    .fetch_optional(pool)
    .await?;
    Ok(row.map(|(added_by,)| added_by))
}

pub async fn detach(pool: &PgPool, manga_id: Uuid, tag_id: Uuid) -> AppResult<()> {
    sqlx::query("DELETE FROM manga_tags WHERE manga_id = $1 AND tag_id = $2")
        .bind(manga_id)
        .bind(tag_id)
        .execute(pool)
        .await?;
    Ok(())
}

pub async fn list_for_manga(pool: &PgPool, manga_id: Uuid) -> AppResult<Vec<TagRef>> {
    let rows = sqlx::query_as::<_, TagRef>(
        r#"
        SELECT t.id, t.name, mt.added_by
          FROM manga_tags mt
          JOIN tags t ON t.id = mt.tag_id
         WHERE mt.manga_id = $1
         ORDER BY lower(t.name)
        "#,
    )
    .bind(manga_id)
    .fetch_all(pool)
    .await?;
    Ok(rows)
}

/// Tag autocomplete. Substring + trigram, no `added_by` (tags are
/// global), capped at the caller's limit.
pub async fn list(
    pool: &PgPool,
    search: Option<&str>,
    limit: i64,
) -> AppResult<Vec<Tag>> {
    let rows = sqlx::query_as::<_, Tag>(
        r#"
        SELECT id, name, created_at
          FROM tags
         WHERE $1::text IS NULL
            OR name ILIKE '%' || $1 || '%'
            OR name % $1
         ORDER BY CASE WHEN $1::text IS NULL THEN 0 ELSE similarity(name, $1) END DESC,
                  lower(name) ASC
         LIMIT $2
        "#,
    )
    .bind(search)
    .bind(limit)
    .fetch_all(pool)
    .await?;
    Ok(rows)
}

pub async fn find_by_id(pool: &PgPool, id: Uuid) -> AppResult<Tag> {
    sqlx::query_as::<_, Tag>(
        "SELECT id, name, created_at FROM tags WHERE id = $1",
    )
    .bind(id)
    .fetch_optional(pool)
    .await?
    .ok_or(AppError::NotFound)
}
