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

use sqlx::PgPool;

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

/// List all sections for a project, ordered by sort_order.
#[tracing::instrument(skip_all)]
pub 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 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 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 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 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.
#[tracing::instrument(skip_all)]
pub async fn reorder(pool: &PgPool, project_id: ProjectId, section_ids: &[ProjectSectionId]) -> Result<()> {
    for (i, id) in section_ids.iter().enumerate() {
        sqlx::query(
            "UPDATE project_sections SET sort_order = $1, updated_at = now() WHERE id = $2 AND project_id = $3",
        )
        .bind(i as i32)
        .bind(id)
        .bind(project_id)
        .execute(pool)
        .await?;
    }

    Ok(())
}

/// Count sections for a project.
#[tracing::instrument(skip_all)]
pub 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)
}