//! Project CRUD and lookup queries.

use sqlx::PgPool;

use super::models::{DbProject, DbProjectWithItemCount};
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);
    // Slug uniqueness is enforced by the per-table unique indexes, including the
    // cross-creator `idx_projects_public_slug` (migration 062). Route the bare
    // INSERT through `insert_with_unique_slug` so a collision auto-suffixes
    // (`slug`, `slug-2`, ...) and retries instead of surfacing a raw 500 (the
    // CHRONIC slug-dedup drift, ultra-fuzz Run 2 UX). This is the seal: there is
    // no public bare-insert constructor for projects.
    crate::helpers::insert_with_unique_slug(slug.as_str(), |candidate| async move {
        let candidate = Slug::from_trusted(candidate);
        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(&candidate)
        .bind(title)
        .bind(description)
        .bind(project_type)
        .bind(features)
        .fetch_one(pool)
        .await
        .map_err(Into::into)
    })
    .await
}

/// 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>(
        // No LIMIT: one creator's project set is naturally bounded; an arbitrary
        // cap silently truncated exports/feeds/dashboard (audit Run 17 Perf).
        "SELECT * FROM projects WHERE user_id = $1 ORDER BY created_at DESC",
    )
    .bind(user_id)
    .fetch_all(pool)
    .await?;

    Ok(projects)
}

/// Of the given candidate slugs, return those already taken by a project owned
/// by `user_id`. One indexed `slug = ANY($2)` query replaces a per-candidate
/// point-query loop (ultra-fuzz Run 6 R6-Perf-M5).
#[tracing::instrument(skip_all)]
pub async fn filter_taken_slugs(
    pool: &PgPool,
    user_id: UserId,
    slugs: &[String],
) -> Result<Vec<String>> {
    let taken: Vec<String> =
        sqlx::query_scalar("SELECT slug FROM projects WHERE user_id = $1 AND slug = ANY($2)")
            .bind(user_id)
            .bind(slugs)
            .fetch_all(pool)
            .await?;
    Ok(taken)
}

/// Count a user's projects without materializing the rows. For callers that only
/// need the total (e.g. stats), this avoids fetching up to 500 full rows just to
/// `.len()` them (ultra-fuzz Run 6 R6-Perf-M4).
#[tracing::instrument(skip_all)]
pub async fn count_projects_by_user(pool: &PgPool, user_id: UserId) -> Result<i64> {
    let count: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM projects WHERE user_id = $1")
        .bind(user_id)
        .fetch_one(pool)
        .await?;
    Ok(count)
}

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

/// Store a project's custom-page source (original, pre-sanitization), stamp
/// `custom_pages_updated_at` (which also invalidates the edge caches of every
/// item page that inherits this project's CSS), and bump the cache generation.
/// Scoped to the owner so a non-owner can't write through this path.
pub async fn update_project_custom_page<'e>(
    executor: impl sqlx::PgExecutor<'e>,
    id: ProjectId,
    user_id: UserId,
    custom_html: &str,
    custom_css: &str,
) -> Result<DbProject> {
    let project = sqlx::query_as::<_, DbProject>(
        r"
        UPDATE projects
        SET custom_html = $3,
            custom_css = $4,
            custom_pages_updated_at = now(),
            cache_generation = cache_generation + 1
        WHERE id = $1 AND user_id = $2
        RETURNING *
        ",
    )
    .bind(id)
    .bind(user_id)
    .bind(custom_html)
    .bind(custom_css)
    .fetch_one(executor)
    .await?;
    Ok(project)
}

/// Clear a project's custom page back to the platform default.
pub async fn reset_project_custom_page(
    pool: &PgPool,
    id: ProjectId,
    user_id: UserId,
) -> Result<()> {
    sqlx::query(
        "UPDATE projects SET custom_html = '', custom_css = '', \
         custom_pages_updated_at = NULL, cache_generation = cache_generation + 1 \
         WHERE id = $1 AND user_id = $2",
    )
    .bind(id)
    .bind(user_id)
    .execute(pool)
    .await?;
    Ok(())
}

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

/// Set or clear a project's creator theme. `None` clears to the platform
/// default. The id is validated against the embedded registry before this call.
#[tracing::instrument(skip_all)]
pub async fn set_project_theme(
    pool: &PgPool,
    id: ProjectId,
    user_id: UserId,
    theme_id: Option<&str>,
) -> Result<()> {
    sqlx::query(
        "UPDATE projects SET theme_id = $3, updated_at = NOW() WHERE id = $1 AND user_id = $2",
    )
    .bind(id)
    .bind(user_id)
    .bind(theme_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.cover_scan_status,
            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, not
/// public, or owned by a sandbox account. Sandbox accounts are hidden from all
/// public surfaces (discover, item pages, user pages); this join closes the gap
/// where their `/p/{slug}`, RSS feeds, and blog pages still rendered publicly.
#[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 p.* FROM projects p \
         JOIN users u ON u.id = p.user_id \
         WHERE p.slug = $1 AND p.is_public = true AND u.is_sandbox = false \
         ORDER BY p.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",
    )
    .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,
    cover_s3_key: Option<&str>,
) -> Result<bool> {
    // Store the bare key alongside the URL so the deletion worker's liveness
    // check matches it exactly (no URL-suffix parsing). The wizard derives the
    // key from the CDN URL it just validated; `None` only when clearing.
    let result = sqlx::query("UPDATE projects SET cover_image_url = $1, cover_s3_key = $4, updated_at = NOW() WHERE id = $2 AND user_id = $3")
        .bind(url)
        .bind(id)
        .bind(user_id)
        .bind(cover_s3_key)
        .execute(executor)
        .await?;

    Ok(result.rows_affected() > 0)
}

/// Confirm an uploaded project cover: set the URL **and** record its byte size,
/// guarded by a compare-and-swap on the existing `cover_image_url`.
///
/// This is the only path that writes `cover_image_size_bytes` (migration 126),
/// which the storage recalc/breakdown read to reconcile project-cover charges,
/// the plain [`update_project_image_url`] setter (used by the wizard) leaves the
/// size untouched and is not a storage-charging operation.
///
/// Returns `false` when the UPDATE matched zero rows: either the ownership
/// filter no-matched (project deleted/transferred mid-flight) OR a concurrent
/// confirm already swapped the cover URL out from under `expected_old_url`. The
/// CAS stops two concurrent confirms from each deducting the old size and
/// orphaning the loser's object (Run #18 Storage B4). Callers fire storage
/// credit + S3 cleanup after this and must roll back on `false`.
#[tracing::instrument(skip_all)]
pub async fn update_project_cover_cas<'e>(
    executor: impl sqlx::PgExecutor<'e>,
    id: ProjectId,
    user_id: UserId,
    expected_old_url: Option<&str>,
    url: &str,
    cover_s3_key: &str,
    file_size_bytes: i64,
) -> Result<bool> {
    // Record the bare key (the confirm handler's `req.s3_key`) so deletion-worker
    // liveness is an exact key match, not a URL-suffix match.
    let result = sqlx::query(
        r"UPDATE projects
           SET cover_image_url = $1, cover_s3_key = $6, cover_image_size_bytes = $4, updated_at = NOW()
           WHERE id = $2 AND user_id = $3
             AND cover_image_url IS NOT DISTINCT FROM $5",
    )
    .bind(url)
    .bind(id)
    .bind(user_id)
    .bind(file_size_bytes)
    .bind(expected_old_url)
    .bind(cover_s3_key)
    .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.
///
/// Takes [`PriceCents`](super::PriceCents) (not raw `i32`) so the `$10k` cap and
/// non-negative floor are enforced by the type at every call site, the only way
/// to obtain a `PriceCents` is the cap-checking `new`/`buy_once` constructors.
/// This is the structural fix for the price-cap-per-writer chronic: a writer
/// cannot pass an uncapped value (ultra-fuzz Run 11 UX F1).
#[tracing::instrument(skip_all)]
pub async fn update_project_pricing(
    pool: &PgPool,
    id: ProjectId,
    user_id: UserId,
    pricing_model: super::PricingKind,
    price_cents: super::PriceCents,
    pwyw_min_cents: Option<super::PriceCents>,
) -> 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.as_i32())
    .bind(pwyw_min_cents.map(super::validated_types::PriceCents::as_i32))
    .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(())
}