//! Project section CRUD: tabbed markdown content blocks within projects.

use sqlx::PgPool;

use super::models::DbProjectSection;
use super::{ProjectId, ProjectSectionId};
use crate::error::Result;

/// List all sections for a project, ordered by sort_order.
#[tracing::instrument(skip_all)]
pub(crate) async fn list_by_project(
    pool: &PgPool,
    project_id: ProjectId,
) -> Result<Vec<DbProjectSection>> {
    let sections = sqlx::query_as::<_, DbProjectSection>(
        "SELECT * FROM project_sections WHERE project_id = $1 ORDER BY sort_order LIMIT 500",
    )
    .bind(project_id)
    .fetch_all(pool)
    .await?;

    Ok(sections)
}

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

    Ok(section)
}

/// Insert a new section for a project.
#[tracing::instrument(skip_all)]
pub(crate) async fn create(
    pool: &PgPool,
    project_id: ProjectId,
    title: &str,
    slug: &str,
    body: &str,
    sort_order: i32,
) -> Result<DbProjectSection> {
    let section = sqlx::query_as::<_, DbProjectSection>(
        r"
        INSERT INTO project_sections (project_id, title, slug, body, sort_order)
        VALUES ($1, $2, $3, $4, $5)
        RETURNING *
        ",
    )
    .bind(project_id)
    .bind(title)
    .bind(slug)
    .bind(body)
    .bind(sort_order)
    .fetch_one(pool)
    .await?;

    Ok(section)
}

/// Update a section's title, slug, and body.
#[tracing::instrument(skip_all)]
pub(crate) async fn update(
    pool: &PgPool,
    section_id: ProjectSectionId,
    title: &str,
    slug: &str,
    body: &str,
) -> Result<DbProjectSection> {
    let section = sqlx::query_as::<_, DbProjectSection>(
        r"
        UPDATE project_sections
        SET title = $2, slug = $3, body = $4, updated_at = now()
        WHERE id = $1
        RETURNING *
        ",
    )
    .bind(section_id)
    .bind(title)
    .bind(slug)
    .bind(body)
    .fetch_one(pool)
    .await?;

    Ok(section)
}

/// Permanently delete a section by ID.
#[tracing::instrument(skip_all)]
pub(crate) async fn delete(pool: &PgPool, section_id: ProjectSectionId) -> Result<()> {
    sqlx::query("DELETE FROM project_sections WHERE id = $1")
        .bind(section_id)
        .execute(pool)
        .await?;

    Ok(())
}

/// Reorder sections by setting sort_order from an ordered list of IDs.
///
/// A single `UNNEST ... WITH ORDINALITY` update: atomic (no partial ordering on
/// failure) and one round-trip instead of one query per section.
#[tracing::instrument(skip_all)]
pub(crate) async fn reorder(
    pool: &PgPool,
    project_id: ProjectId,
    section_ids: &[ProjectSectionId],
) -> Result<()> {
    let ids: Vec<uuid::Uuid> = section_ids.iter().map(|id| *id.as_uuid()).collect();
    sqlx::query(
        r"
        UPDATE project_sections AS s
        SET sort_order = ord.pos::int - 1, updated_at = now()
        FROM UNNEST($1::uuid[]) WITH ORDINALITY AS ord(id, pos)
        WHERE s.id = ord.id AND s.project_id = $2
        ",
    )
    .bind(&ids)
    .bind(project_id)
    .execute(pool)
    .await?;

    Ok(())
}

/// Count sections for a project.
#[tracing::instrument(skip_all)]
pub(crate) async fn count_by_project(pool: &PgPool, project_id: ProjectId) -> Result<i64> {
    let row: (i64,) = sqlx::query_as("SELECT COUNT(*) FROM project_sections WHERE project_id = $1")
        .bind(project_id)
        .fetch_one(pool)
        .await?;

    Ok(row.0)
}