//! Blog post CRUD with Markdown rendering and publish/unpublish lifecycle.

use chrono::Utc;
use sqlx::PgPool;

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

/// Insert a new blog post and return the created row.
#[allow(clippy::too_many_arguments)]
#[tracing::instrument(skip_all)]
pub async fn create_blog_post(
    pool: &PgPool,
    project_id: ProjectId,
    author_id: UserId,
    title: &str,
    slug: &Slug,
    body_markdown: &str,
    body_html: &str,
    publish: bool,
    web_only: bool,
    show_on_landing: bool,
) -> Result<DbBlogPost> {
    let published_at = if publish { Some(Utc::now()) } else { None };

    let post = sqlx::query_as::<_, DbBlogPost>(
        r#"
        INSERT INTO blog_posts (project_id, author_id, title, slug, body_markdown, body_html, published_at, web_only, show_on_landing)
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
        RETURNING *
        "#,
    )
    .bind(project_id)
    .bind(author_id)
    .bind(title)
    .bind(slug)
    .bind(body_markdown)
    .bind(body_html)
    .bind(published_at)
    .bind(web_only)
    .bind(show_on_landing)
    .fetch_one(pool)
    .await?;

    Ok(post)
}

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

    Ok(post)
}

/// Fetch a blog post by project and slug. Returns `None` if not found.
#[tracing::instrument(skip_all)]
pub async fn get_blog_post_by_slug(
    pool: &PgPool,
    project_id: ProjectId,
    slug: &Slug,
) -> Result<Option<DbBlogPost>> {
    let post = sqlx::query_as::<_, DbBlogPost>(
        "SELECT * FROM blog_posts WHERE project_id = $1 AND slug = $2",
    )
    .bind(project_id)
    .bind(slug)
    .fetch_optional(pool)
    .await?;

    Ok(post)
}

/// List all blog posts in a project (for dashboard), newest first.
#[tracing::instrument(skip_all)]
pub async fn get_blog_posts_by_project(
    pool: &PgPool,
    project_id: ProjectId,
) -> Result<Vec<DbBlogPost>> {
    let posts = sqlx::query_as::<_, DbBlogPost>(
        "SELECT * FROM blog_posts WHERE project_id = $1 ORDER BY created_at DESC LIMIT 500",
    )
    .bind(project_id)
    .fetch_all(pool)
    .await?;

    Ok(posts)
}

/// Batch-load blog posts for multiple projects, grouped by project_id.
#[tracing::instrument(skip_all)]
pub async fn get_blog_posts_by_projects(
    pool: &PgPool,
    project_ids: &[ProjectId],
) -> Result<std::collections::HashMap<ProjectId, Vec<DbBlogPost>>> {
    let posts = sqlx::query_as::<_, DbBlogPost>(
        "SELECT * FROM blog_posts WHERE project_id = ANY($1) ORDER BY project_id, created_at DESC",
    )
    .bind(project_ids)
    .fetch_all(pool)
    .await?;

    let mut map: std::collections::HashMap<ProjectId, Vec<DbBlogPost>> = std::collections::HashMap::new();
    for p in posts {
        map.entry(p.project_id).or_default().push(p);
    }
    Ok(map)
}

/// List published blog posts in a project (for public pages), newest first.
#[tracing::instrument(skip_all)]
pub async fn get_published_blog_posts_by_project(
    pool: &PgPool,
    project_id: ProjectId,
) -> Result<Vec<DbBlogPost>> {
    let posts = sqlx::query_as::<_, DbBlogPost>(
        "SELECT * FROM blog_posts WHERE project_id = $1 AND published_at IS NOT NULL ORDER BY published_at DESC LIMIT 500",
    )
    .bind(project_id)
    .fetch_all(pool)
    .await?;

    Ok(posts)
}

/// Fetch the single landing-page "Last shipped" post: the most recent
/// published, landing-flagged post belonging to a public project with the
/// given slug (CHANGELOG_PROJECT_SLUG). Returns `None` when nothing qualifies,
/// which the landing route uses to suppress the velocity line entirely.
///
/// `show_on_landing` is set on rows across every project, but the slug join
/// confines the landing reader to the changelog project, so the flag is inert
/// elsewhere.
#[tracing::instrument(skip_all)]
pub async fn get_landing_changelog_post(
    pool: &PgPool,
    changelog_slug: &str,
) -> Result<Option<DbBlogPost>> {
    let post = sqlx::query_as::<_, DbBlogPost>(
        r#"
        SELECT bp.*
        FROM blog_posts bp
        JOIN projects p ON p.id = bp.project_id
        WHERE p.slug = $1
          AND p.is_public = true
          AND bp.show_on_landing = true
          AND bp.published_at IS NOT NULL
        ORDER BY bp.published_at DESC
        LIMIT 1
        "#,
    )
    .bind(changelog_slug)
    .fetch_optional(pool)
    .await?;

    Ok(post)
}

/// Update a blog post's fields.
///
/// `publish_at` uses a double-Option: `None` = no change, `Some(None)` = clear schedule,
/// `Some(Some(dt))` = set schedule. When a schedule is set, `published_at` stays NULL
/// (the scheduler will set it when the time comes).
///
/// `web_only` uses `Option<bool>`: `None` = no change, `Some(v)` = update.
#[allow(clippy::too_many_arguments)]
#[tracing::instrument(skip_all)]
pub async fn update_blog_post(
    pool: &PgPool,
    id: BlogPostId,
    title: &str,
    slug: &Slug,
    body_markdown: &str,
    body_html: &str,
    publish: bool,
    publish_at: Option<Option<chrono::DateTime<chrono::Utc>>>,
    web_only: Option<bool>,
    show_on_landing: Option<bool>,
) -> Result<DbBlogPost> {
    let update_publish_at = publish_at.is_some();
    let publish_at_value = publish_at.flatten();

    // Four-way CASE for published_at:
    //   1. Scheduling (publish_at is being set) → keep NULL (scheduler handles it)
    //   2. First publish (publish=true, published_at IS NULL) → set to NOW()
    //   3. Unpublish (publish=false) → clear to NULL
    //   4. Re-save while published → preserve existing timestamp
    let post = sqlx::query_as::<_, DbBlogPost>(
        r#"
        UPDATE blog_posts
        SET title = $2,
            slug = $3,
            body_markdown = $4,
            body_html = $5,
            published_at = CASE
                WHEN $7 = true AND $8 IS NOT NULL THEN NULL
                WHEN $6 = true AND published_at IS NULL THEN NOW()
                WHEN $6 = false THEN NULL
                ELSE published_at
            END,
            publish_at = CASE WHEN $7 THEN $8 ELSE publish_at END,
            web_only = COALESCE($9, web_only),
            show_on_landing = COALESCE($10, show_on_landing),
            updated_at = NOW()
        WHERE id = $1
        RETURNING *
        "#,
    )
    .bind(id)
    .bind(title)
    .bind(slug)
    .bind(body_markdown)
    .bind(body_html)
    .bind(publish)
    .bind(update_publish_at)
    .bind(publish_at_value)
    .bind(web_only)
    .bind(show_on_landing)
    .fetch_one(pool)
    .await?;

    Ok(post)
}

/// Publish all blog posts whose scheduled publish time has passed.
///
/// Atomically sets `published_at = NOW()` and clears `publish_at`, returning
/// the newly published posts for logging.
#[tracing::instrument(skip_all)]
pub async fn publish_scheduled_blog_posts(pool: &PgPool) -> Result<Vec<DbBlogPost>> {
    let posts = sqlx::query_as::<_, DbBlogPost>(
        r#"
        UPDATE blog_posts
        SET published_at = NOW(), publish_at = NULL, updated_at = NOW()
        WHERE publish_at IS NOT NULL AND publish_at <= NOW() AND published_at IS NULL
        RETURNING *
        "#,
    )
    .fetch_all(pool)
    .await?;

    Ok(posts)
}

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

    Ok(())
}

/// Set the linked MT thread ID for a blog post.
#[tracing::instrument(skip_all)]
pub async fn set_mt_thread_id(
    pool: &PgPool,
    blog_post_id: BlogPostId,
    thread_id: MtThreadId,
) -> Result<()> {
    sqlx::query("UPDATE blog_posts SET mt_thread_id = $2 WHERE id = $1")
        .bind(blog_post_id)
        .bind(thread_id)
        .execute(pool)
        .await?;
    Ok(())
}

/// Check if a project has any published blog posts.
#[tracing::instrument(skip_all)]
pub async fn has_published_posts(pool: &PgPool, project_id: ProjectId) -> Result<bool> {
    let exists: bool = sqlx::query_scalar(
        "SELECT EXISTS(SELECT 1 FROM blog_posts WHERE project_id = $1 AND published_at IS NOT NULL)",
    )
    .bind(project_id)
    .fetch_one(pool)
    .await?;

    Ok(exists)
}

/// Atomically mark a blog post as having had its release announced.
/// Returns false if already announced (prevents duplicate announcements on unpublish/republish).
#[tracing::instrument(skip_all)]
pub async fn mark_blog_post_announced(pool: &PgPool, post_id: BlogPostId) -> Result<bool> {
    let result = sqlx::query(
        "UPDATE blog_posts SET release_announced_at = NOW() WHERE id = $1 AND release_announced_at IS NULL",
    )
    .bind(post_id)
    .execute(pool)
    .await?;

    Ok(result.rows_affected() > 0)
}

/// Check if a slug already exists for a project.
#[tracing::instrument(skip_all)]
pub async fn blog_post_slug_exists(
    pool: &PgPool,
    project_id: ProjectId,
    slug: &Slug,
) -> Result<bool> {
    let exists: bool = sqlx::query_scalar(
        "SELECT EXISTS(SELECT 1 FROM blog_posts WHERE project_id = $1 AND slug = $2)",
    )
    .bind(project_id)
    .bind(slug)
    .fetch_one(pool)
    .await?;

    Ok(exists)
}