use crate::AppState;
use axum::{
    extract::{Path, Query, State},
    http::StatusCode,
    response::IntoResponse,
    routing::{get, patch},
    Json, Router,
};
use contracts::auth_middleware::AuthUser;
use serde::{Deserialize, Serialize};
use uuid::Uuid;

// ── Routers ───────────────────────────────────────────────────────────────────

/// Public (unauthenticated) KB routes
pub fn public_router() -> Router<AppState> {
    Router::new()
        .route("/categories", get(public_list_categories))
        .route("/articles", get(public_list_articles))
        .route("/articles/{slug}", get(public_get_article))
        .route("/articles/id/{id}", get(public_get_article_by_id))
}

/// Admin CRUD routes
pub fn admin_router() -> Router<AppState> {
    Router::new()
        // Categories
        .route("/categories", get(admin_list_categories).post(admin_create_category))
        .route(
            "/categories/{id}",
            patch(admin_update_category).delete(admin_delete_category),
        )
        // Articles
        .route("/articles", get(admin_list_articles).post(admin_create_article))
        .route(
            "/articles/{id}",
            get(admin_get_article)
                .patch(admin_update_article)
                .delete(admin_delete_article),
        )
}

// ── Shared types ──────────────────────────────────────────────────────────────

#[derive(Serialize)]
struct CategoryDto {
    id: Uuid,
    name: String,
    slug: String,
    description: Option<String>,
    display_order: i32,
    article_count: Option<i64>,
    is_active: bool,
}

#[derive(Serialize)]
struct PublicArticleDto {
    id: Uuid,
    slug: String,
    title: String,
    summary: Option<String>,
    #[serde(rename = "categoryKey")]
    category_key: String,
    category: String,
    role: String,
    tags: Vec<String>,
    #[serde(rename = "updatedAt")]
    updated_at: String,
    content: String,
}

#[derive(Serialize)]
struct AdminArticleDto {
    id: Uuid,
    title: String,
    slug: String,
    summary: Option<String>,
    category_id: Option<Uuid>,
    category: Option<String>,
    content: String,
    status: String, // "PUBLISHED" | "DRAFT"
    target_roles: Vec<String>,
    tags: Vec<String>,
    views: i32,
    created_at: String,
    updated_at: String,
}

// ── FromRow structs ───────────────────────────────────────────────────────────

#[derive(sqlx::FromRow)]
struct CategoryRow {
    id: Uuid,
    name: String,
    slug: String,
    description: Option<String>,
    display_order: i32,
    is_active: bool,
}

#[derive(sqlx::FromRow)]
struct CategoryWithCountRow {
    id: Uuid,
    name: String,
    slug: String,
    description: Option<String>,
    display_order: i32,
    is_active: bool,
    article_count: Option<i64>,
}

#[derive(sqlx::FromRow)]
struct PublicArticleRow {
    id: Uuid,
    title: String,
    slug: String,
    summary: Option<String>,
    body: String,
    target_roles: Option<Vec<String>>,
    tags: Vec<String>,
    updated_at: chrono::DateTime<chrono::Utc>,
    category_name: String,
    category_slug: String,
}

#[derive(sqlx::FromRow)]
struct AdminArticleRow {
    id: Uuid,
    title: String,
    slug: String,
    summary: Option<String>,
    body: String,
    category_id: Uuid,
    target_roles: Option<Vec<String>>,
    tags: Vec<String>,
    status: String,
    views: i32,
    created_at: chrono::DateTime<chrono::Utc>,
    updated_at: chrono::DateTime<chrono::Utc>,
    category_name: String,
}

#[derive(sqlx::FromRow)]
struct InsertedArticleRow {
    id: Uuid,
    title: String,
    slug: String,
    summary: Option<String>,
    body: String,
    category_id: Uuid,
    target_roles: Option<Vec<String>>,
    tags: Vec<String>,
    status: String,
    views: i32,
    created_at: chrono::DateTime<chrono::Utc>,
    updated_at: chrono::DateTime<chrono::Utc>,
}

// ── Public: categories ────────────────────────────────────────────────────────

async fn public_list_categories(State(state): State<AppState>) -> impl IntoResponse {
    let rows = sqlx::query_as::<_, CategoryRow>(
        r#"
        SELECT id, name, slug, description, display_order, is_active
        FROM kb_categories
        WHERE is_active = true
        ORDER BY display_order ASC, name ASC
        "#,
    )
    .fetch_all(&state.pool)
    .await;

    match rows {
        Ok(rows) => {
            let data: Vec<_> = rows
                .into_iter()
                .map(|r| CategoryDto {
                    id: r.id,
                    name: r.name,
                    slug: r.slug,
                    description: r.description,
                    display_order: r.display_order,
                    article_count: None,
                    is_active: true,
                })
                .collect();
            (StatusCode::OK, Json(serde_json::json!({ "categories": data }))).into_response()
        }
        Err(e) => {
            tracing::error!("Failed to fetch KB categories: {}", e);
            (
                StatusCode::INTERNAL_SERVER_ERROR,
                Json(serde_json::json!({ "error": "Failed to fetch categories" })),
            )
                .into_response()
        }
    }
}

// ── Public: articles list ─────────────────────────────────────────────────────

#[derive(Deserialize)]
struct PublicArticleQuery {
    role: Option<String>,
    category: Option<String>, // category slug
    q: Option<String>,
    page: Option<i64>,
    limit: Option<i64>,
}

async fn public_list_articles(
    State(state): State<AppState>,
    Query(params): Query<PublicArticleQuery>,
) -> impl IntoResponse {
    let page = params.page.unwrap_or(1).max(1);
    let limit = params.limit.unwrap_or(50).clamp(1, 100);
    let offset = (page - 1) * limit;

    let role_filter = params.role.as_deref().unwrap_or("").to_uppercase();
    let cat_slug = params.category.as_deref().unwrap_or("").to_string();
    let q = params.q.as_deref().unwrap_or("").to_lowercase();

    let rows = sqlx::query_as::<_, PublicArticleRow>(
        r#"
        SELECT
            a.id, a.title, a.slug, a.summary, a.body, a.target_roles, a.tags,
            a.updated_at,
            c.name AS category_name, c.slug AS category_slug
        FROM kb_articles a
        JOIN kb_categories c ON c.id = a.category_id
        WHERE a.status = 'PUBLISHED'
          AND c.is_active = true
          AND ($1 = '' OR c.slug = $1)
          AND ($2 = '' OR $2 = 'ALL'
               OR a.target_roles = '{}'
               OR $2 = ANY(a.target_roles))
          AND ($3 = '' OR LOWER(a.title) LIKE '%' || $3 || '%'
               OR LOWER(COALESCE(a.summary, '')) LIKE '%' || $3 || '%')
        ORDER BY a.updated_at DESC
        LIMIT $4 OFFSET $5
        "#,
    )
    .bind(&cat_slug)
    .bind(&role_filter)
    .bind(&q)
    .bind(limit)
    .bind(offset)
    .fetch_all(&state.pool)
    .await;

    match rows {
        Ok(rows) => {
            let articles: Vec<_> = rows
                .into_iter()
                .map(|r| {
                    let role = derive_role(r.target_roles.as_deref().unwrap_or(&[]));
                    PublicArticleDto {
                        id: r.id,
                        slug: r.slug,
                        title: r.title,
                        summary: r.summary,
                        category_key: r.category_slug,
                        category: r.category_name,
                        role,
                        tags: r.tags,
                        updated_at: r.updated_at.to_rfc3339(),
                        content: r.body,
                    }
                })
                .collect();
            (StatusCode::OK, Json(serde_json::json!({ "articles": articles }))).into_response()
        }
        Err(e) => {
            tracing::error!("Failed to fetch KB articles: {}", e);
            (
                StatusCode::INTERNAL_SERVER_ERROR,
                Json(serde_json::json!({ "error": "Failed to fetch articles" })),
            )
                .into_response()
        }
    }
}

// ── Public: single article by slug ────────────────────────────────────────────

async fn public_get_article(
    State(state): State<AppState>,
    Path(slug): Path<String>,
) -> impl IntoResponse {
    let row = sqlx::query_as::<_, PublicArticleRow>(
        r#"
        SELECT
            a.id, a.title, a.slug, a.summary, a.body, a.target_roles, a.tags,
            a.updated_at,
            c.name AS category_name, c.slug AS category_slug
        FROM kb_articles a
        JOIN kb_categories c ON c.id = a.category_id
        WHERE a.slug = $1 AND a.status = 'PUBLISHED' AND c.is_active = true
        "#,
    )
    .bind(&slug)
    .fetch_optional(&state.pool)
    .await;

    // Increment views in background (best-effort, not awaited)
    let pool = state.pool.clone();
    let slug_clone = slug.clone();
    tokio::spawn(async move {
        let _ = sqlx::query(
            "UPDATE kb_articles SET views = views + 1 WHERE slug = $1",
        )
        .bind(slug_clone)
        .execute(&pool)
        .await;
    });

    match row {
        Ok(Some(r)) => {
            let role = derive_role(r.target_roles.as_deref().unwrap_or(&[]));
            let dto = PublicArticleDto {
                id: r.id,
                slug: r.slug,
                title: r.title,
                summary: r.summary,
                category_key: r.category_slug,
                category: r.category_name,
                role,
                tags: r.tags,
                updated_at: r.updated_at.to_rfc3339(),
                content: r.body,
            };
            (StatusCode::OK, Json(dto)).into_response()
        }
        Ok(None) => (
            StatusCode::NOT_FOUND,
            Json(serde_json::json!({ "error": "Article not found" })),
        )
            .into_response(),
        Err(e) => {
            tracing::error!("Failed to fetch KB article {}: {}", slug, e);
            (
                StatusCode::INTERNAL_SERVER_ERROR,
                Json(serde_json::json!({ "error": "Failed to fetch article" })),
            )
                .into_response()
        }
    }
}

// ── Public: single article by ID ───────────────────────────────────────────────

async fn public_get_article_by_id(
    State(state): State<AppState>,
    Path(id): Path<Uuid>,
) -> impl IntoResponse {
    let row = sqlx::query_as::<_, PublicArticleRow>(
        r#"
        SELECT
            a.id, a.title, a.slug, a.summary, a.body, a.target_roles, a.tags,
            a.updated_at,
            c.name AS category_name, c.slug AS category_slug
        FROM kb_articles a
        JOIN kb_categories c ON c.id = a.category_id
        WHERE a.id = $1 AND a.status = 'PUBLISHED' AND c.is_active = true
        "#,
    )
    .bind(id)
    .fetch_optional(&state.pool)
    .await;

    let pool = state.pool.clone();
    tokio::spawn(async move {
        let _ = sqlx::query("UPDATE kb_articles SET views = views + 1 WHERE id = $1")
            .bind(id)
            .execute(&pool)
            .await;
    });

    match row {
        Ok(Some(r)) => {
            let role = derive_role(r.target_roles.as_deref().unwrap_or(&[]));
            let dto = PublicArticleDto {
                id: r.id,
                slug: r.slug,
                title: r.title,
                summary: r.summary,
                category_key: r.category_slug,
                category: r.category_name,
                role,
                tags: r.tags,
                updated_at: r.updated_at.to_rfc3339(),
                content: r.body,
            };
            (StatusCode::OK, Json(dto)).into_response()
        }
        Ok(None) => (
            StatusCode::NOT_FOUND,
            Json(serde_json::json!({ "error": "Article not found" })),
        )
            .into_response(),
        Err(e) => {
            tracing::error!("Failed to fetch KB article by id {}: {}", id, e);
            (
                StatusCode::INTERNAL_SERVER_ERROR,
                Json(serde_json::json!({ "error": "Failed to fetch article" })),
            )
                .into_response()
        }
    }
}

// ── Admin: categories ─────────────────────────────────────────────────────────

async fn admin_list_categories(
    _auth: AuthUser,
    State(state): State<AppState>,
) -> impl IntoResponse {
    let rows = sqlx::query_as::<_, CategoryWithCountRow>(
        r#"
        SELECT
            c.id, c.name, c.slug, c.description, c.display_order, c.is_active,
            COUNT(a.id) AS article_count
        FROM kb_categories c
        LEFT JOIN kb_articles a ON a.category_id = c.id
        GROUP BY c.id
        ORDER BY c.display_order ASC, c.name ASC
        "#,
    )
    .fetch_all(&state.pool)
    .await;

    match rows {
        Ok(rows) => {
            let data: Vec<_> = rows
                .into_iter()
                .map(|r| CategoryDto {
                    id: r.id,
                    name: r.name,
                    slug: r.slug,
                    description: r.description,
                    display_order: r.display_order,
                    article_count: r.article_count,
                    is_active: r.is_active,
                })
                .collect();
            (StatusCode::OK, Json(data)).into_response()
        }
        Err(e) => {
            tracing::error!("Failed to fetch KB categories: {}", e);
            (
                StatusCode::INTERNAL_SERVER_ERROR,
                Json(serde_json::json!({ "error": "Failed to fetch categories" })),
            )
                .into_response()
        }
    }
}

#[derive(Deserialize)]
struct CreateCategoryBody {
    name: String,
    slug: String,
    description: Option<String>,
    display_order: Option<i32>,
}

async fn admin_create_category(
    _auth: AuthUser,
    State(state): State<AppState>,
    Json(body): Json<CreateCategoryBody>,
) -> impl IntoResponse {
    let order = body.display_order.unwrap_or(0);
    let result = sqlx::query_as::<_, CategoryRow>(
        r#"
        INSERT INTO kb_categories (name, slug, description, display_order)
        VALUES ($1, $2, $3, $4)
        RETURNING id, name, slug, description, display_order, is_active
        "#,
    )
    .bind(&body.name)
    .bind(&body.slug)
    .bind(&body.description)
    .bind(order)
    .fetch_one(&state.pool)
    .await;

    match result {
        Ok(r) => (
            StatusCode::CREATED,
            Json(CategoryDto {
                id: r.id,
                name: r.name,
                slug: r.slug,
                description: r.description,
                display_order: r.display_order,
                article_count: Some(0),
                is_active: r.is_active,
            }),
        )
            .into_response(),
        Err(e) if e.to_string().contains("unique") => (
            StatusCode::CONFLICT,
            Json(serde_json::json!({ "error": "Slug already exists" })),
        )
            .into_response(),
        Err(e) => {
            tracing::error!("Failed to create KB category: {}", e);
            (
                StatusCode::INTERNAL_SERVER_ERROR,
                Json(serde_json::json!({ "error": "Failed to create category" })),
            )
                .into_response()
        }
    }
}

#[derive(Deserialize)]
struct UpdateCategoryBody {
    name: Option<String>,
    slug: Option<String>,
    description: Option<String>,
    display_order: Option<i32>,
    is_active: Option<bool>,
}

async fn admin_update_category(
    _auth: AuthUser,
    State(state): State<AppState>,
    Path(id): Path<Uuid>,
    Json(body): Json<UpdateCategoryBody>,
) -> impl IntoResponse {
    let result = sqlx::query_as::<_, CategoryRow>(
        r#"
        UPDATE kb_categories SET
            name          = COALESCE($2, name),
            slug          = COALESCE($3, slug),
            description   = COALESCE($4, description),
            display_order = COALESCE($5, display_order),
            is_active     = COALESCE($6, is_active)
        WHERE id = $1
        RETURNING id, name, slug, description, display_order, is_active
        "#,
    )
    .bind(id)
    .bind(&body.name)
    .bind(&body.slug)
    .bind(&body.description)
    .bind(body.display_order)
    .bind(body.is_active)
    .fetch_optional(&state.pool)
    .await;

    match result {
        Ok(Some(r)) => (
            StatusCode::OK,
            Json(CategoryDto {
                id: r.id,
                name: r.name,
                slug: r.slug,
                description: r.description,
                display_order: r.display_order,
                article_count: None,
                is_active: r.is_active,
            }),
        )
            .into_response(),
        Ok(None) => (
            StatusCode::NOT_FOUND,
            Json(serde_json::json!({ "error": "Category not found" })),
        )
            .into_response(),
        Err(e) => {
            tracing::error!("Failed to update KB category {}: {}", id, e);
            (
                StatusCode::INTERNAL_SERVER_ERROR,
                Json(serde_json::json!({ "error": "Failed to update category" })),
            )
                .into_response()
        }
    }
}

async fn admin_delete_category(
    _auth: AuthUser,
    State(state): State<AppState>,
    Path(id): Path<Uuid>,
) -> impl IntoResponse {
    #[derive(sqlx::FromRow)]
    #[allow(dead_code)]
    struct IdRow { id: Uuid }

    let result = sqlx::query_as::<_, IdRow>(
        "DELETE FROM kb_categories WHERE id = $1 RETURNING id",
    )
    .bind(id)
    .fetch_optional(&state.pool)
    .await;

    match result {
        Ok(Some(_)) => (
            StatusCode::OK,
            Json(serde_json::json!({ "deleted": true })),
        )
            .into_response(),
        Ok(None) => (
            StatusCode::NOT_FOUND,
            Json(serde_json::json!({ "error": "Category not found" })),
        )
            .into_response(),
        Err(e) => {
            tracing::error!("Failed to delete KB category {}: {}", id, e);
            (
                StatusCode::INTERNAL_SERVER_ERROR,
                Json(serde_json::json!({ "error": "Failed to delete category" })),
            )
                .into_response()
        }
    }
}

// ── Admin: articles ───────────────────────────────────────────────────────────

#[derive(Deserialize)]
struct AdminArticleQuery {
    q: Option<String>,
    category_id: Option<Uuid>,
    status: Option<String>,
}

async fn admin_list_articles(
    _auth: AuthUser,
    State(state): State<AppState>,
    Query(params): Query<AdminArticleQuery>,
) -> impl IntoResponse {
    let q = params.q.as_deref().unwrap_or("").to_lowercase();
    let status_filter: Option<String> = params.status.as_deref().map(|s| s.to_string());

    let rows = sqlx::query_as::<_, AdminArticleRow>(
        r#"
        SELECT
            a.id, a.title, a.slug, a.summary, a.body, a.target_roles, a.tags,
            a.status, a.views, a.category_id, a.created_at, a.updated_at,
            c.name AS category_name
        FROM kb_articles a
        JOIN kb_categories c ON c.id = a.category_id
        WHERE ($1 = '' OR LOWER(a.title) LIKE '%' || $1 || '%')
          AND ($2::uuid IS NULL OR a.category_id = $2)
          AND ($3::text IS NULL OR a.status = $3)
        ORDER BY a.updated_at DESC
        LIMIT 200
        "#,
    )
    .bind(&q)
    .bind(params.category_id)
    .bind(status_filter)
    .fetch_all(&state.pool)
    .await;

    match rows {
        Ok(rows) => {
            let articles: Vec<_> = rows
                .into_iter()
                .map(|r| AdminArticleDto {
                    id: r.id,
                    title: r.title,
                    slug: r.slug,
                    summary: r.summary,
                    category_id: Some(r.category_id),
                    category: Some(r.category_name),
                    content: r.body,
status: r.status,
                    target_roles: r.target_roles.unwrap_or_default(),
                    tags: r.tags,
                    views: r.views,
                    created_at: r.created_at.to_rfc3339(),
                    updated_at: r.updated_at.to_rfc3339(),
                })
                .collect();
            (StatusCode::OK, Json(serde_json::json!({ "articles": articles }))).into_response()
        }
        Err(e) => {
            tracing::error!("Failed to fetch admin KB articles: {}", e);
            (
                StatusCode::INTERNAL_SERVER_ERROR,
                Json(serde_json::json!({ "error": "Failed to fetch articles" })),
            )
                .into_response()
        }
    }
}

#[derive(Deserialize)]
struct CreateArticleBody {
    title: String,
    slug: Option<String>,
    summary: Option<String>,
    content: String, // maps to body
    category_id: Option<Uuid>,
    status: Option<String>,       // "PUBLISHED" | "DRAFT"
    target_roles: Option<Vec<String>>,
    tags: Option<Vec<String>>,
}

async fn admin_create_article(
    auth: AuthUser,
    State(state): State<AppState>,
    Json(body): Json<CreateArticleBody>,
) -> impl IntoResponse {
    let slug = body
        .slug
        .filter(|s| !s.is_empty())
        .unwrap_or_else(|| slugify(&body.title));
    let status = body.status.as_deref().unwrap_or("DRAFT").to_string();
    let roles: Vec<String> = body.target_roles.unwrap_or_default();
    let tags: Vec<String> = body.tags.unwrap_or_default();

    let result = sqlx::query_as::<_, InsertedArticleRow>(
        r#"
        INSERT INTO kb_articles
            (title, slug, summary, body, category_id, status, target_roles, tags, created_by)
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
        RETURNING id, title, slug, summary, body, category_id, status,
                  target_roles, tags, views, created_at, updated_at
        "#,
    )
    .bind(&body.title)
    .bind(&slug)
    .bind(&body.summary)
    .bind(&body.content)
    .bind(body.category_id)
    .bind(&status)
    .bind(&roles)
    .bind(&tags)
    .bind(auth.user_id)
    .fetch_one(&state.pool)
    .await;

    match result {
        Ok(r) => (
            StatusCode::CREATED,
            Json(AdminArticleDto {
                id: r.id,
                title: r.title,
                slug: r.slug,
                summary: r.summary,
                category_id: Some(r.category_id),
                category: None,
                content: r.body,
                status: r.status,
                target_roles: r.target_roles.unwrap_or_default(),
                tags: r.tags,
                views: r.views,
                created_at: r.created_at.to_rfc3339(),
                updated_at: r.updated_at.to_rfc3339(),
            }),
        )
            .into_response(),
        Err(e) if e.to_string().contains("unique") => (
            StatusCode::CONFLICT,
            Json(serde_json::json!({ "error": "Slug already exists" })),
        )
            .into_response(),
        Err(e) if e.to_string().contains("not-null") || e.to_string().contains("null value") => (
            StatusCode::BAD_REQUEST,
            Json(serde_json::json!({ "error": "category_id is required" })),
        )
            .into_response(),
        Err(e) => {
            tracing::error!("Failed to create KB article: {}", e);
            (
                StatusCode::INTERNAL_SERVER_ERROR,
                Json(serde_json::json!({ "error": "Failed to create article" })),
            )
                .into_response()
        }
    }
}

async fn admin_get_article(
    _auth: AuthUser,
    State(state): State<AppState>,
    Path(id): Path<Uuid>,
) -> impl IntoResponse {
    let row = sqlx::query_as::<_, AdminArticleRow>(
        r#"
        SELECT
            a.id, a.title, a.slug, a.summary, a.body, a.category_id,
            a.target_roles, a.tags, a.status, a.views,
            a.created_at, a.updated_at,
            c.name AS category_name
        FROM kb_articles a
        JOIN kb_categories c ON c.id = a.category_id
        WHERE a.id = $1
        "#,
    )
    .bind(id)
    .fetch_optional(&state.pool)
    .await;

    match row {
        Ok(Some(r)) => (
            StatusCode::OK,
            Json(AdminArticleDto {
                id: r.id,
                title: r.title,
                slug: r.slug,
                summary: r.summary,
                category_id: Some(r.category_id),
                category: Some(r.category_name),
                content: r.body,
                status: r.status,
                target_roles: r.target_roles.unwrap_or_default(),
                tags: r.tags,
                views: r.views,
                created_at: r.created_at.to_rfc3339(),
                updated_at: r.updated_at.to_rfc3339(),
            }),
        )
            .into_response(),
        Ok(None) => (
            StatusCode::NOT_FOUND,
            Json(serde_json::json!({ "error": "Article not found" })),
        )
            .into_response(),
        Err(e) => {
            tracing::error!("Failed to fetch KB article {}: {}", id, e);
            (
                StatusCode::INTERNAL_SERVER_ERROR,
                Json(serde_json::json!({ "error": "Failed to fetch article" })),
            )
                .into_response()
        }
    }
}

#[derive(Deserialize)]
struct UpdateArticleBody {
    title: Option<String>,
    slug: Option<String>,
    summary: Option<String>,
    content: Option<String>,
    category_id: Option<Uuid>,
    status: Option<String>,
    target_roles: Option<Vec<String>>,
    tags: Option<Vec<String>>,
}

async fn admin_update_article(
    _auth: AuthUser,
    State(state): State<AppState>,
    Path(id): Path<Uuid>,
    Json(body): Json<UpdateArticleBody>,
) -> impl IntoResponse {
    let status: Option<String> = body.status.as_deref().map(|s| s.to_string());
    let result = sqlx::query_as::<_, InsertedArticleRow>(
        r#"
        UPDATE kb_articles SET
            title        = COALESCE($2, title),
            slug         = COALESCE($3, slug),
            summary      = COALESCE($4, summary),
            body         = COALESCE($5, body),
            category_id  = COALESCE($6, category_id),
            status       = COALESCE($7, status),
            target_roles = COALESCE($8, target_roles),
            tags         = COALESCE($9, tags),
            updated_at   = NOW()
        WHERE id = $1
        RETURNING id, title, slug, summary, body, category_id,
                  target_roles, tags, status, views, created_at, updated_at
        "#,
    )
    .bind(id)
    .bind(&body.title)
    .bind(&body.slug)
    .bind(&body.summary)
    .bind(&body.content)
    .bind(body.category_id)
    .bind(&status)
    .bind(body.target_roles.as_deref())
    .bind(body.tags.as_deref())
    .fetch_optional(&state.pool)
    .await;

    match result {
        Ok(Some(r)) => (
            StatusCode::OK,
            Json(AdminArticleDto {
                id: r.id,
                title: r.title,
                slug: r.slug,
                summary: r.summary,
                category_id: Some(r.category_id),
                category: None,
                content: r.body,
                status: r.status,
                target_roles: r.target_roles.unwrap_or_default(),
                tags: r.tags,
                views: r.views,
                created_at: r.created_at.to_rfc3339(),
                updated_at: r.updated_at.to_rfc3339(),
            }),
        )
            .into_response(),
        Ok(None) => (
            StatusCode::NOT_FOUND,
            Json(serde_json::json!({ "error": "Article not found" })),
        )
            .into_response(),
        Err(e) => {
            tracing::error!("Failed to update KB article {}: {}", id, e);
            (
                StatusCode::INTERNAL_SERVER_ERROR,
                Json(serde_json::json!({ "error": "Failed to update article" })),
            )
                .into_response()
        }
    }
}

async fn admin_delete_article(
    _auth: AuthUser,
    State(state): State<AppState>,
    Path(id): Path<Uuid>,
) -> impl IntoResponse {
    #[derive(sqlx::FromRow)]
    #[allow(dead_code)]
    struct IdRow { id: Uuid }

    let result = sqlx::query_as::<_, IdRow>(
        "DELETE FROM kb_articles WHERE id = $1 RETURNING id",
    )
    .bind(id)
    .fetch_optional(&state.pool)
    .await;

    match result {
        Ok(Some(_)) => (
            StatusCode::OK,
            Json(serde_json::json!({ "deleted": true })),
        )
            .into_response(),
        Ok(None) => (
            StatusCode::NOT_FOUND,
            Json(serde_json::json!({ "error": "Article not found" })),
        )
            .into_response(),
        Err(e) => {
            tracing::error!("Failed to delete KB article {}: {}", id, e);
            (
                StatusCode::INTERNAL_SERVER_ERROR,
                Json(serde_json::json!({ "error": "Failed to delete article" })),
            )
                .into_response()
        }
    }
}

// ── Helpers ───────────────────────────────────────────────────────────────────

/// Map target_roles array to a single frontend role label
fn derive_role(roles: &[String]) -> String {
    if roles.is_empty() {
        return "ALL".to_string();
    }
    let profession_keys = [
        "PHOTOGRAPHER", "MAKEUP_ARTIST", "TUTOR", "DEVELOPER",
        "VIDEO_EDITOR", "GRAPHIC_DESIGNER", "SOCIAL_MEDIA_MANAGER",
        "FITNESS_TRAINER", "CATERING_SERVICES",
    ];
    let first = roles[0].as_str();
    if first == "COMPANY" { return "company".to_string(); }
    if first == "JOB_SEEKER" { return "jobSeeker".to_string(); }
    if first == "CUSTOMER" { return "customer".to_string(); }
    if profession_keys.contains(&first) { return "professional".to_string(); }
    "ALL".to_string()
}

/// Simple ASCII slug from title
fn slugify(title: &str) -> String {
    title
        .to_lowercase()
        .chars()
        .map(|c| if c.is_alphanumeric() { c } else { '-' })
        .collect::<String>()
        .split('-')
        .filter(|s| !s.is_empty())
        .collect::<Vec<_>>()
        .join("-")
}
