//! Project CRUD and lookup queries.

use sqlx::PgPool;

use super::models::*;
use super::validated_types::Slug;
use super::{ProjectId, UserId};
use crate::error::Result;

/// Insert a new project and return the created row.
///
/// `project_type` is auto-derived from `features` using [`ProjectFeature::derive_project_type`].
#[tracing::instrument(skip_all)]
pub async fn create_project(
    pool: &PgPool,
    user_id: UserId,
    slug: &Slug,
    title: &str,
    description: Option<&str>,
    features: &[String],
) -> Result<DbProject> {
    let project_type = super::ProjectFeature::derive_project_type(features);
    let project = sqlx::query_as::<_, DbProject>(
        r#"
        INSERT INTO projects (user_id, slug, title, description, project_type, features)
        VALUES ($1, $2, $3, $4, $5, $6)
        RETURNING *
        "#,
    )
    .bind(user_id)
    .bind(slug)
    .bind(title)
    .bind(description)
    .bind(project_type)
    .bind(features)
    .fetch_one(pool)
    .await?;

    Ok(project)
}

/// Fetch a project by primary key. Returns `None` if not found.
#[tracing::instrument(skip_all)]
pub async fn get_project_by_id(pool: &PgPool, id: ProjectId) -> Result<Option<DbProject>> {
    let project = sqlx::query_as::<_, DbProject>("SELECT * FROM projects WHERE id = $1")
        .bind(id)
        .fetch_optional(pool)
        .await?;

    Ok(project)
}

/// Fetch a project by its owning user and URL slug. Returns `None` if not found.
#[tracing::instrument(skip_all)]
pub async fn get_project_by_user_and_slug(
    pool: &PgPool,
    user_id: UserId,
    slug: &Slug,
) -> Result<Option<DbProject>> {
    let project = sqlx::query_as::<_, DbProject>(
        "SELECT * FROM projects WHERE user_id = $1 AND slug = $2",
    )
    .bind(user_id)
    .bind(slug)
    .fetch_optional(pool)
    .await?;

    Ok(project)
}

/// Fetch a public project by user ID and slug (for custom domain routing).
#[tracing::instrument(skip_all)]
pub async fn get_public_project_by_user_and_slug(
    pool: &PgPool,
    user_id: UserId,
    slug: &Slug,
) -> Result<Option<DbProject>> {
    let project = sqlx::query_as::<_, DbProject>(
        "SELECT * FROM projects WHERE user_id = $1 AND slug = $2 AND is_public = true",
    )
    .bind(user_id)
    .bind(slug)
    .fetch_optional(pool)
    .await?;

    Ok(project)
}

/// Return just the IDs of all projects owned by a user (lightweight, for cleanup).
#[tracing::instrument(skip_all)]
pub async fn get_project_ids_for_user(pool: &PgPool, user_id: UserId) -> Result<Vec<ProjectId>> {
    let ids = sqlx::query_scalar::<_, ProjectId>(
        "SELECT id FROM projects WHERE user_id = $1",
    )
    .bind(user_id)
    .fetch_all(pool)
    .await?;

    Ok(ids)
}

/// List all projects owned by a user, newest first.
///
/// Capped at 500 as a safety limit.
#[tracing::instrument(skip_all)]
pub async fn get_projects_by_user(pool: &PgPool, user_id: UserId) -> Result<Vec<DbProject>> {
    let projects = sqlx::query_as::<_, DbProject>(
        "SELECT * FROM projects WHERE user_id = $1 ORDER BY created_at DESC LIMIT 500",
    )
    .bind(user_id)
    .fetch_all(pool)
    .await?;

    Ok(projects)
}

/// Partially update a project's fields (COALESCE keeps existing values when `None`).
///
/// When `features` is `Some`, the project_type is auto-derived from the new features.
#[tracing::instrument(skip_all)]
pub async fn update_project(
    pool: &PgPool,
    id: ProjectId,
    user_id: UserId,
    title: Option<&str>,
    description: Option<&str>,
    features: Option<&[String]>,
    is_public: Option<bool>,
) -> Result<DbProject> {
    let project_type = features.map(super::ProjectFeature::derive_project_type);
    let project = sqlx::query_as::<_, DbProject>(
        r#"
        UPDATE projects
        SET title = COALESCE($3, title),
            description = COALESCE($4, description),
            project_type = COALESCE($5, project_type),
            is_public = COALESCE($6, is_public),
            features = COALESCE($7, features)
        WHERE id = $1 AND user_id = $2
        RETURNING *
        "#,
    )
    .bind(id)
    .bind(user_id)
    .bind(title)
    .bind(description)
    .bind(project_type)
    .bind(is_public)
    .bind(features)
    .fetch_one(pool)
    .await?;

    Ok(project)
}

/// Set or clear a project's category.
#[tracing::instrument(skip_all)]
pub async fn set_project_category(
    pool: &PgPool,
    id: ProjectId,
    user_id: UserId,
    category_id: Option<super::CategoryId>,
) -> Result<()> {
    sqlx::query("UPDATE projects SET category_id = $3 WHERE id = $1 AND user_id = $2")
        .bind(id)
        .bind(user_id)
        .bind(category_id)
        .execute(pool)
        .await?;

    Ok(())
}

/// Permanently delete a project by ID (cascades to items).
#[tracing::instrument(skip_all)]
pub async fn delete_project(pool: &PgPool, id: ProjectId, user_id: UserId) -> Result<()> {
    sqlx::query("DELETE FROM projects WHERE id = $1 AND user_id = $2")
        .bind(id)
        .bind(user_id)
        .execute(pool)
        .await?;

    Ok(())
}

/// Get public projects with item counts in a single query (avoids N+1)
#[tracing::instrument(skip_all)]
pub async fn get_public_projects_with_item_counts(
    pool: &PgPool,
    user_id: UserId,
) -> Result<Vec<DbProjectWithItemCount>> {
    let projects = sqlx::query_as::<_, DbProjectWithItemCount>(
        r#"
        SELECT
            p.id,
            p.user_id,
            p.slug,
            p.title,
            p.description,
            p.project_type,
            p.cover_image_url,
            p.is_public,
            p.created_at,
            p.updated_at,
            COUNT(i.id) as item_count
        FROM projects p
        LEFT JOIN items i ON i.project_id = p.id AND i.is_public = true
        WHERE p.user_id = $1 AND p.is_public = true
        GROUP BY p.id
        ORDER BY p.created_at DESC
        "#,
    )
    .bind(user_id)
    .fetch_all(pool)
    .await?;

    Ok(projects)
}

/// Fetch a public project by its URL slug. Returns `None` if not found or not public.
#[tracing::instrument(skip_all)]
pub async fn get_public_project_by_slug(
    pool: &PgPool,
    slug: &Slug,
) -> Result<Option<DbProject>> {
    let project = sqlx::query_as::<_, DbProject>(
        "SELECT * FROM projects WHERE slug = $1 AND is_public = true ORDER BY created_at ASC LIMIT 1",
    )
    .bind(slug)
    .fetch_optional(pool)
    .await?;

    Ok(project)
}

/// Fetch a public project by slug string (bypasses Slug validation).
/// Used by the inbound patch handler where the slug comes from an email address.
#[tracing::instrument(skip_all)]
pub async fn get_public_project_by_slug_str(
    pool: &PgPool,
    slug: &str,
) -> Result<Option<DbProject>> {
    let project = sqlx::query_as::<_, DbProject>(
        "SELECT * FROM projects WHERE slug = $1 AND is_public = true ORDER BY created_at ASC LIMIT 1",
    )
    .bind(slug)
    .fetch_optional(pool)
    .await?;

    Ok(project)
}

/// Set the linked MT community ID for a project.
#[tracing::instrument(skip_all)]
pub async fn set_mt_community_id(
    pool: &PgPool,
    project_id: ProjectId,
    community_id: uuid::Uuid,
) -> Result<()> {
    sqlx::query("UPDATE projects SET mt_community_id = $2 WHERE id = $1")
        .bind(project_id)
        .bind(community_id)
        .execute(pool)
        .await?;
    Ok(())
}

/// Fetch all projects that don't have an MT community linked.
#[tracing::instrument(skip_all)]
pub async fn get_projects_without_mt_community(pool: &PgPool) -> Result<Vec<DbProject>> {
    let projects = sqlx::query_as::<_, DbProject>(
        "SELECT * FROM projects WHERE mt_community_id IS NULL ORDER BY created_at LIMIT 500",
    )
    .fetch_all(pool)
    .await?;
    Ok(projects)
}

/// Set or clear a project's image URL (stored in cover_image_url column).
///
/// Returns `true` when the row was actually updated, `false` when the
/// ownership filter matched zero rows (project deleted or transferred to a
/// different user between the caller's authorization check and this UPDATE).
/// Callers that fire side-effects after the write — storage credit, scan
/// enqueue, S3 orphan queueing — must check the bool and roll back on false.
#[tracing::instrument(skip_all)]
pub async fn update_project_image_url<'e>(
    executor: impl sqlx::PgExecutor<'e>,
    id: ProjectId,
    user_id: UserId,
    url: &str,
) -> Result<bool> {
    let result = sqlx::query("UPDATE projects SET cover_image_url = $1, updated_at = NOW() WHERE id = $2 AND user_id = $3")
        .bind(url)
        .bind(id)
        .bind(user_id)
        .execute(executor)
        .await?;

    Ok(result.rows_affected() > 0)
}

/// Update a project's AI content tier and disclosure.
#[tracing::instrument(skip_all)]
pub async fn update_project_ai_tier(
    pool: &PgPool,
    id: ProjectId,
    user_id: UserId,
    ai_tier: super::AiTier,
    ai_disclosure: Option<&str>,
) -> Result<()> {
    sqlx::query(
        r#"
        UPDATE projects
        SET ai_tier = $3, ai_disclosure = $4, updated_at = NOW()
        WHERE id = $1 AND user_id = $2
        "#,
    )
    .bind(id)
    .bind(user_id)
    .bind(ai_tier)
    .bind(ai_disclosure)
    .execute(pool)
    .await?;

    Ok(())
}

/// Update a project's pricing model, price, and PWYW minimum.
#[tracing::instrument(skip_all)]
pub async fn update_project_pricing(
    pool: &PgPool,
    id: ProjectId,
    user_id: UserId,
    pricing_model: super::PricingKind,
    price_cents: i32,
    pwyw_min_cents: Option<i32>,
) -> Result<()> {
    sqlx::query(
        r#"
        UPDATE projects
        SET pricing_model = $3, price_cents = $4, pwyw_min_cents = $5, updated_at = NOW()
        WHERE id = $1 AND user_id = $2
        "#,
    )
    .bind(id)
    .bind(user_id)
    .bind(pricing_model)
    .bind(price_cents)
    .bind(pwyw_min_cents)
    .execute(pool)
    .await?;

    Ok(())
}

/// Atomically increment the project's cache generation counter.
/// Call after any write that changes project-visible dashboard data.
#[tracing::instrument(skip_all)]
pub async fn bump_cache_generation(pool: &PgPool, project_id: ProjectId) -> Result<()> {
    sqlx::query("UPDATE projects SET cache_generation = cache_generation + 1 WHERE id = $1")
        .bind(project_id)
        .execute(pool)
        .await?;

    Ok(())
}