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

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

/// User-facing support routes
pub fn user_router() -> Router<AppState> {
    Router::new()
        .route("/", post(user_create_ticket).get(user_list_tickets))
        .route("/{id}", get(user_get_ticket))
        .route("/{id}/messages", post(user_add_message))
        .route("/ai/create", post(ai_create_ticket))
}

/// Admin support routes
pub fn admin_router() -> Router<AppState> {
    Router::new()
        .route("/", get(admin_list_cases).post(admin_create_case))
        .route("/{id}", get(admin_get_case).patch(admin_update_case))
        .route("/{id}/messages", post(admin_add_message))
}

/// AI-assistant reference number lookup, nested at `/api/support` (sibling of
/// `/api/support/tickets`). Authenticates with the same shared
/// `X-AI-Service-Key` header as `ai_create_ticket`.
pub fn reference_router() -> Router<AppState> {
    Router::new().route("/reference/{number}", get(ai_reference_lookup))
}

// ── DTOs ──────────────────────────────────────────────────────────────────────

#[derive(Serialize)]
struct TicketDto {
    id: Uuid,
    #[serde(rename = "referenceNumber")]
    reference_number: String,
    title: String,
    description: Option<String>,
    #[serde(rename = "type")]
    ticket_type: String,
    priority: String,
    status: String,
    #[serde(rename = "requesterName")]
    requester_name: Option<String>,
    #[serde(rename = "requesterEmail")]
    requester_email: Option<String>,
    #[serde(rename = "assignedTo")]
    assigned_to: Option<Uuid>,
    #[serde(rename = "createdAt")]
    created_at: String,
    #[serde(rename = "updatedAt")]
    updated_at: String,
}

#[derive(Serialize)]
struct MessageDto {
    id: Uuid,
    #[serde(rename = "ticketId")]
    ticket_id: Uuid,
    #[serde(rename = "senderId")]
    sender_id: Uuid,
    body: String,
    #[serde(rename = "isInternal")]
    is_internal: bool,
    #[serde(rename = "createdAt")]
    created_at: String,
}

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

#[derive(sqlx::FromRow)]
struct TicketRow {
    id: Uuid,
    reference_number: String,
    subject: String,
    description: Option<String>,
    category: String,
    priority: String,
    status: String,
    requester_name: Option<String>,
    requester_email: Option<String>,
    assigned_to: Option<Uuid>,
    created_at: chrono::DateTime<chrono::Utc>,
    updated_at: chrono::DateTime<chrono::Utc>,
}

#[derive(sqlx::FromRow)]
struct MessageRow {
    id: Uuid,
    ticket_id: Uuid,
    sender_id: Uuid,
    body: String,
    is_internal: bool,
    created_at: chrono::DateTime<chrono::Utc>,
}

// ── AI Service: create ticket (no user auth required) ────────────────────────

#[derive(Deserialize)]
struct AiCreateTicketBody {
    subject: String,
    description: Option<String>,
    category: Option<String>,
    priority: Option<String>,
    #[serde(rename = "userId")]
    user_id: Option<Uuid>,
}

async fn ai_create_ticket(
    State(state): State<AppState>,
    headers: HeaderMap,
    axum::extract::Json(body): axum::extract::Json<AiCreateTicketBody>,
) -> impl IntoResponse {
    let expected_key = std::env::var("AI_SERVICE_KEY").unwrap_or_default();
    let provided_key = headers
        .get("X-AI-Service-Key")
        .and_then(|v| v.to_str().ok())
        .unwrap_or_default();

    if expected_key.is_empty() || provided_key != expected_key {
        return (
            StatusCode::UNAUTHORIZED,
            Json(serde_json::json!({ "error": "Invalid or missing AI service key" })),
        )
            .into_response();
    }

    let user_id = body.user_id.unwrap_or_else(|| Uuid::nil());
    let category = body.category.clone().unwrap_or_else(|| "ai_assisted".to_string());
    let priority = body.priority.clone().unwrap_or_else(|| "medium".to_string());

    let result = sqlx::query_as::<_, TicketRow>(
        r#"
        INSERT INTO support_tickets (user_id, subject, description, category, priority, status)
        VALUES ($1, $2, $3, $4, $5, 'new')
        RETURNING id, reference_number, subject, description, category, priority, status,
                  requester_name, requester_email, assigned_to, created_at, updated_at
        "#,
    )
    .bind(user_id)
    .bind(&body.subject)
    .bind(&body.description)
    .bind(&category)
    .bind(&priority)
    .fetch_one(&state.pool)
    .await;

    match result {
        Ok(r) => (
            StatusCode::CREATED,
            Json(serde_json::json!({
                "id": r.id,
                "reference_number": r.reference_number,
                "subject": r.subject,
                "description": r.description,
                "category": r.category,
                "priority": r.priority,
                "status": r.status,
            })),
        ).into_response(),
        Err(e) => {
            tracing::error!("AI ticket creation failed: {}", e);
            (StatusCode::INTERNAL_SERVER_ERROR, Json(serde_json::json!({ "error": "Failed to create ticket" }))).into_response()
        }
    }
}

// ── User: create ticket ───────────────────────────────────────────────────────

#[derive(Deserialize)]
struct UserCreateTicketBody {
    subject: String,
    description: Option<String>,
    category: Option<String>,
    priority: Option<String>,
}

async fn user_create_ticket(
    auth: AuthUser,
    State(state): State<AppState>,
    Json(body): Json<UserCreateTicketBody>,
) -> impl IntoResponse {
    let category = body.category.unwrap_or_else(|| "customer_query".to_string());
    let priority = body.priority.unwrap_or_else(|| "medium".to_string());

    let result = sqlx::query_as::<_, TicketRow>(
        r#"
        INSERT INTO support_tickets (user_id, subject, description, category, priority, status)
        VALUES ($1, $2, $3, $4, $5, 'new')
        RETURNING id, reference_number, subject, description, category, priority, status,
                  requester_name, requester_email, assigned_to, created_at, updated_at
        "#,
    )
    .bind(auth.user_id)
    .bind(&body.subject)
    .bind(&body.description)
    .bind(&category)
    .bind(&priority)
    .fetch_one(&state.pool)
    .await;

    match result {
        Ok(r) => {
            // Send confirmation email to user
            if let Ok(user) = db::models::user::UserRepository::get_by_id(&state.pool, auth.user_id).await {
                let response_time = match priority.as_str() {
                    "high" => "2-4 hours",
                    "medium" => "12-24 hours",
                    _ => "24-48 hours",
                };
                let _ = state.mail.send_support_ticket_created_email(
                    &user.email,
                    &format!("{} {}", user.first_name.unwrap_or_default(), user.last_name.unwrap_or_default()),
                    &r.id.to_string(),
                    &body.subject,
                    &category,
                    &priority,
                    response_time
                ).await;
            }

            (
                StatusCode::CREATED,
                Json(TicketDto {
                    id: r.id,
                    reference_number: r.reference_number,
                    title: r.subject,
                    description: r.description,
                    ticket_type: r.category,
                    priority: r.priority,
                    status: r.status,
                    requester_name: r.requester_name,
                    requester_email: r.requester_email,
                    assigned_to: r.assigned_to,
                    created_at: r.created_at.to_rfc3339(),
                    updated_at: r.updated_at.to_rfc3339(),
                }),
            )
                .into_response()
        }
        Err(e) => {
            tracing::error!("Failed to create support ticket: {}", e);
            (
                StatusCode::INTERNAL_SERVER_ERROR,
                Json(serde_json::json!({ "error": "Failed to create ticket" })),
            )
                .into_response()
        }
    }
}

// ── User: list own tickets ────────────────────────────────────────────────────

#[derive(Deserialize)]
struct ListQuery {
    status: Option<String>,
    page: Option<i64>,
    limit: Option<i64>,
}

async fn user_list_tickets(
    auth: AuthUser,
    State(state): State<AppState>,
    Query(params): Query<ListQuery>,
) -> impl IntoResponse {
    let page = params.page.unwrap_or(1).max(1);
    let limit = params.limit.unwrap_or(20).clamp(1, 100);
    let offset = (page - 1) * limit;
    let status_filter = params.status.as_deref().unwrap_or("").to_string();

    let rows = sqlx::query_as::<_, TicketRow>(
        r#"
        SELECT id, reference_number, subject, description, category, priority, status,
               requester_name, requester_email, assigned_to, created_at, updated_at
        FROM support_tickets
        WHERE user_id = $1
          AND ($2 = '' OR status = $2)
        ORDER BY updated_at DESC
        LIMIT $3 OFFSET $4
        "#,
    )
    .bind(auth.user_id)
    .bind(&status_filter)
    .bind(limit)
    .bind(offset)
    .fetch_all(&state.pool)
    .await;

    match rows {
        Ok(rows) => {
            let tickets: Vec<_> = rows
                .into_iter()
                .map(|r| TicketDto {
                    id: r.id,
                    reference_number: r.reference_number,
                    title: r.subject,
                    description: r.description,
                    ticket_type: r.category,
                    priority: r.priority,
                    status: r.status,
                    requester_name: r.requester_name,
                    requester_email: r.requester_email,
                    assigned_to: r.assigned_to,
                    created_at: r.created_at.to_rfc3339(),
                    updated_at: r.updated_at.to_rfc3339(),
                })
                .collect();
            (StatusCode::OK, Json(serde_json::json!({ "tickets": tickets }))).into_response()
        }
        Err(e) => {
            tracing::error!("Failed to list support tickets: {}", e);
            (
                StatusCode::INTERNAL_SERVER_ERROR,
                Json(serde_json::json!({ "error": "Failed to fetch tickets" })),
            )
                .into_response()
        }
    }
}

// ── User: get single ticket with messages ─────────────────────────────────────

async fn user_get_ticket(
    auth: AuthUser,
    State(state): State<AppState>,
    Path(id): Path<Uuid>,
) -> impl IntoResponse {
    let ticket = sqlx::query_as::<_, TicketRow>(
        r#"
        SELECT id, reference_number, subject, description, category, priority, status,
               requester_name, requester_email, assigned_to, created_at, updated_at
        FROM support_tickets
        WHERE id = $1 AND user_id = $2
        "#,
    )
    .bind(id)
    .bind(auth.user_id)
    .fetch_optional(&state.pool)
    .await;

    match ticket {
        Ok(Some(t)) => {
            let messages = sqlx::query_as::<_, MessageRow>(
                r#"
                SELECT id, ticket_id, sender_id, body, is_internal, created_at
                FROM support_ticket_messages
                WHERE ticket_id = $1 AND is_internal = false
                ORDER BY created_at ASC
                "#,
            )
            .bind(id)
            .fetch_all(&state.pool)
            .await
            .unwrap_or_default();

            let msgs: Vec<_> = messages
                .into_iter()
                .map(|m| MessageDto {
                    id: m.id,
                    ticket_id: m.ticket_id,
                    sender_id: m.sender_id,
                    body: m.body,
                    is_internal: m.is_internal,
                    created_at: m.created_at.to_rfc3339(),
                })
                .collect();

            let dto = TicketDto {
                id: t.id,
                reference_number: t.reference_number,
                title: t.subject,
                description: t.description,
                ticket_type: t.category,
                priority: t.priority,
                status: t.status,
                requester_name: t.requester_name,
                requester_email: t.requester_email,
                assigned_to: t.assigned_to,
                created_at: t.created_at.to_rfc3339(),
                updated_at: t.updated_at.to_rfc3339(),
            };

            (StatusCode::OK, Json(serde_json::json!({ "ticket": dto, "messages": msgs }))).into_response()
        }
        Ok(None) => (
            StatusCode::NOT_FOUND,
            Json(serde_json::json!({ "error": "Ticket not found" })),
        )
            .into_response(),
        Err(e) => {
            tracing::error!("Failed to fetch ticket {}: {}", id, e);
            (
                StatusCode::INTERNAL_SERVER_ERROR,
                Json(serde_json::json!({ "error": "Failed to fetch ticket" })),
            )
                .into_response()
        }
    }
}

// ── User: add message ─────────────────────────────────────────────────────────

#[derive(Deserialize)]
struct AddMessageBody {
    body: String,
}

async fn user_add_message(
    auth: AuthUser,
    State(state): State<AppState>,
    Path(id): Path<Uuid>,
    Json(body): Json<AddMessageBody>,
) -> impl IntoResponse {
    // Verify ticket belongs to user
    let exists = sqlx::query_scalar::<_, bool>(
        "SELECT EXISTS(SELECT 1 FROM support_tickets WHERE id = $1 AND user_id = $2)",
    )
    .bind(id)
    .bind(auth.user_id)
    .fetch_one(&state.pool)
    .await
    .unwrap_or(false);

    if !exists {
        return (
            StatusCode::NOT_FOUND,
            Json(serde_json::json!({ "error": "Ticket not found" })),
        )
            .into_response();
    }

    let result = sqlx::query_as::<_, MessageRow>(
        r#"
        INSERT INTO support_ticket_messages (ticket_id, sender_id, body, is_internal)
        VALUES ($1, $2, $3, false)
        RETURNING id, ticket_id, sender_id, body, is_internal, created_at
        "#,
    )
    .bind(id)
    .bind(auth.user_id)
    .bind(&body.body)
    .fetch_one(&state.pool)
    .await;

    // Update ticket updated_at
    let _ = sqlx::query(
        "UPDATE support_tickets SET updated_at = NOW(), status = CASE WHEN status = 'waiting_for_user' THEN 'in_progress' ELSE status END WHERE id = $1",
    )
    .bind(id)
    .execute(&state.pool)
    .await;

    match result {
        Ok(m) => (
            StatusCode::CREATED,
            Json(MessageDto {
                id: m.id,
                ticket_id: m.ticket_id,
                sender_id: m.sender_id,
                body: m.body,
                is_internal: m.is_internal,
                created_at: m.created_at.to_rfc3339(),
            }),
        )
            .into_response(),
        Err(e) => {
            tracing::error!("Failed to add message to ticket {}: {}", id, e);
            (
                StatusCode::INTERNAL_SERVER_ERROR,
                Json(serde_json::json!({ "error": "Failed to add message" })),
            )
                .into_response()
        }
    }
}

// ── Admin: list all cases ─────────────────────────────────────────────────────

#[derive(Deserialize)]
struct AdminListQuery {
    status: Option<String>,
    priority: Option<String>,
    ticket_type: Option<String>,
    page: Option<i64>,
    limit: Option<i64>,
}

#[derive(sqlx::FromRow)]
struct AdminTicketRow {
    id: Uuid,
    reference_number: String,
    subject: String,
    description: Option<String>,
    category: String,
    priority: String,
    status: String,
    requester_name: Option<String>,
    requester_email: Option<String>,
    assigned_to: Option<Uuid>,
    created_at: chrono::DateTime<chrono::Utc>,
    updated_at: chrono::DateTime<chrono::Utc>,
    user_name: Option<String>,
    user_email: Option<String>,
}

async fn admin_list_cases(
    _auth: AuthUser,
    State(state): State<AppState>,
    Query(params): Query<AdminListQuery>,
) -> impl IntoResponse {
    let page = params.page.unwrap_or(1).max(1);
    let limit = params.limit.unwrap_or(50).clamp(1, 200);
    let offset = (page - 1) * limit;
    let status_filter = params.status.as_deref().unwrap_or("").to_string();
    let priority_filter = params.priority.as_deref().unwrap_or("").to_string();
    let type_filter = params.ticket_type.as_deref().unwrap_or("").to_string();

    let rows = sqlx::query_as::<_, AdminTicketRow>(
        r#"
        SELECT
            t.id, t.reference_number, t.subject, t.description, t.category, t.priority, t.status,
            t.requester_name, t.requester_email, t.assigned_to,
            t.created_at, t.updated_at,
            CONCAT(u.first_name, ' ', u.last_name) AS user_name, u.email AS user_email
        FROM support_tickets t
        LEFT JOIN users u ON u.id = t.user_id
        WHERE ($1 = '' OR t.status = $1)
          AND ($2 = '' OR t.priority = $2)
          AND ($3 = '' OR t.category = $3)
        ORDER BY t.updated_at DESC
        LIMIT $4 OFFSET $5
        "#,
    )
    .bind(&status_filter)
    .bind(&priority_filter)
    .bind(&type_filter)
    .bind(limit)
    .bind(offset)
    .fetch_all(&state.pool)
    .await;

    let total: i64 = sqlx::query_scalar::<_, i64>("SELECT COUNT(*) FROM support_tickets")
        .fetch_one(&state.pool)
        .await
        .unwrap_or(0);

    match rows {
        Ok(rows) => {
            let cases: Vec<_> = rows
                .into_iter()
                .map(|r| {
                    // Use user info if available, fall back to requester fields
                    let requester_name = r.requester_name.or(r.user_name);
                    let requester_email = r.requester_email.or(r.user_email);
                    serde_json::json!({
                        "id": r.id,
                        "reference_number": r.reference_number,
                        "title": r.subject,
                        "description": r.description,
                        "type": r.category,
                        "priority": r.priority,
                        "status": r.status,
                        "requesterName": requester_name,
                        "requesterEmail": requester_email,
                        "assignedTo": r.assigned_to,
                        "createdAt": r.created_at.to_rfc3339(),
                        "updatedAt": r.updated_at.to_rfc3339(),
                    })
                })
                .collect();
            (StatusCode::OK, Json(serde_json::json!({ "cases": cases, "total": total }))).into_response()
        }
        Err(e) => {
            tracing::error!("Failed to list support cases: {}", e);
            (
                StatusCode::INTERNAL_SERVER_ERROR,
                Json(serde_json::json!({ "error": "Failed to fetch cases" })),
            )
                .into_response()
        }
    }
}

// ── Admin: create case ────────────────────────────────────────────────────────

#[derive(Deserialize)]
struct AdminCreateCaseBody {
    title: String,
    description: Option<String>,
    #[serde(rename = "type")]
    ticket_type: Option<String>,
    priority: Option<String>,
    #[serde(rename = "requesterName")]
    requester_name: Option<String>,
    #[serde(rename = "requesterEmail")]
    requester_email: Option<String>,
}

async fn admin_create_case(
    _auth: AuthUser,
    State(state): State<AppState>,
    Json(body): Json<AdminCreateCaseBody>,
) -> impl IntoResponse {
    let category = body.ticket_type.unwrap_or_else(|| "customer_query".to_string());
    let priority = body.priority.unwrap_or_else(|| "medium".to_string());

    let result = sqlx::query_as::<_, TicketRow>(
        r#"
        INSERT INTO support_tickets
            (subject, description, category, priority, status,
             requester_name, requester_email)
        VALUES ($1, $2, $3, $4, $5, $6, $7)
        RETURNING id, reference_number, subject, description, category, priority, status,
                  requester_name, requester_email, assigned_to, created_at, updated_at
        "#,
    )
    .bind(&body.title)
        .bind(&body.description)
        .bind(&category)
        .bind(&priority)
        .bind("new")
        .bind(&body.requester_name)
        .bind(&body.requester_email)
    .fetch_one(&state.pool)
    .await;

    match result {
        Ok(r) => (
            StatusCode::CREATED,
            Json(serde_json::json!({
                "id": r.id,
                "reference_number": r.reference_number,
                "title": r.subject,
                "description": r.description,
                "type": r.category,
                "priority": r.priority,
                "status": r.status,
                "requesterName": r.requester_name,
                "requesterEmail": r.requester_email,
                "createdAt": r.created_at.to_rfc3339(),
                "updatedAt": r.updated_at.to_rfc3339(),
            })),
        )
            .into_response(),
        Err(e) => {
            tracing::error!("Failed to create support case: {}", e);
            (
                StatusCode::INTERNAL_SERVER_ERROR,
                Json(serde_json::json!({ "error": "Failed to create case" })),
            )
                .into_response()
        }
    }
}

// ── Admin: get case with messages ─────────────────────────────────────────────

async fn admin_get_case(
    _auth: AuthUser,
    State(state): State<AppState>,
    Path(id): Path<Uuid>,
) -> impl IntoResponse {
    let ticket = sqlx::query_as::<_, AdminTicketRow>(
        r#"
        SELECT
            t.id, t.reference_number, t.subject, t.description, t.category, t.priority, t.status,
            t.requester_name, t.requester_email, t.assigned_to,
            t.created_at, t.updated_at,
            CONCAT(u.first_name, ' ', u.last_name) AS user_name, u.email AS user_email
        FROM support_tickets t
        LEFT JOIN users u ON u.id = t.user_id
        WHERE t.id = $1
        "#,
    )
    .bind(id)
    .fetch_optional(&state.pool)
    .await;

    match ticket {
        Ok(Some(t)) => {
            let messages = sqlx::query_as::<_, MessageRow>(
                r#"
                SELECT id, ticket_id, sender_id, body, is_internal, created_at
                FROM support_ticket_messages
                WHERE ticket_id = $1
                ORDER BY created_at ASC
                "#,
            )
            .bind(id)
            .fetch_all(&state.pool)
            .await
            .unwrap_or_default();

            let msgs: Vec<_> = messages
                .into_iter()
                .map(|m| serde_json::json!({
                    "id": m.id,
                    "ticketId": m.ticket_id,
                    "senderId": m.sender_id,
                    "body": m.body,
                    "isInternal": m.is_internal,
                    "createdAt": m.created_at.to_rfc3339(),
                }))
                .collect();

            let requester_name = t.requester_name.or(t.user_name);
            let requester_email = t.requester_email.or(t.user_email);

            (StatusCode::OK, Json(serde_json::json!({
                "ticket": {
                    "id": t.id,
                    "reference_number": t.reference_number,
                    "title": t.subject,
                    "description": t.description,
                    "type": t.category,
                    "priority": t.priority,
                    "status": t.status,
                    "requesterName": requester_name,
                    "requesterEmail": requester_email,
                    "assignedTo": t.assigned_to,
                    "createdAt": t.created_at.to_rfc3339(),
                    "updatedAt": t.updated_at.to_rfc3339(),
                },
                "messages": msgs,
            }))).into_response()
        }
        Ok(None) => (
            StatusCode::NOT_FOUND,
            Json(serde_json::json!({ "error": "Case not found" })),
        )
            .into_response(),
        Err(e) => {
            tracing::error!("Failed to fetch support case {}: {}", id, e);
            (
                StatusCode::INTERNAL_SERVER_ERROR,
                Json(serde_json::json!({ "error": "Failed to fetch case" })),
            )
                .into_response()
        }
    }
}

// ── Admin: update case status/priority ────────────────────────────────────────

#[derive(Deserialize)]
struct UpdateCaseBody {
    status: Option<String>,
    priority: Option<String>,
    assigned_to: Option<Uuid>,
}

#[derive(sqlx::FromRow)]
struct UpdatedTicketRow {
    id: Uuid,
    reference_number: String,
    subject: String,
    category: String,
    priority: String,
    status: String,
    requester_name: Option<String>,
    requester_email: Option<String>,
    assigned_to: Option<Uuid>,
    created_at: chrono::DateTime<chrono::Utc>,
    updated_at: chrono::DateTime<chrono::Utc>,
}

async fn admin_update_case(
    _auth: AuthUser,
    State(state): State<AppState>,
    Path(id): Path<Uuid>,
    Json(body): Json<UpdateCaseBody>,
) -> impl IntoResponse {
    let result = sqlx::query_as::<_, UpdatedTicketRow>(
        r#"
        UPDATE support_tickets SET
            status      = COALESCE($2, status),
            priority    = COALESCE($3, priority),
            assigned_to = COALESCE($4, assigned_to),
            resolved_at = CASE WHEN $2 = 'resolved' AND resolved_at IS NULL THEN NOW() ELSE resolved_at END,
            updated_at  = NOW()
        WHERE id = $1
        RETURNING id, reference_number, subject, category, priority, status,
                  requester_name, requester_email, assigned_to, created_at, updated_at
        "#,
    )
    .bind(id)
    .bind(&body.status)
    .bind(&body.priority)
    .bind(body.assigned_to)
    .fetch_optional(&state.pool)
    .await;

    match result {
        Ok(Some(r)) => {
            // Send email notification if ticket was resolved
            if body.status.as_deref() == Some("resolved") {
                if let Some(user_email) = &r.requester_email {
                    let user_name = r.requester_name.clone().unwrap_or_default();
                    let _ = state.mail.send_support_ticket_resolved_email(
                        user_email,
                        &user_name,
                        &r.subject
                    ).await;
                }
            }

            (
                StatusCode::OK,
                Json(serde_json::json!({
                    "id": r.id,
                    "reference_number": r.reference_number,
                    "title": r.subject,
                    "type": r.category,
                    "priority": r.priority,
                    "status": r.status,
                    "requesterName": r.requester_name,
                    "requesterEmail": r.requester_email,
                    "assignedTo": r.assigned_to,
                    "createdAt": r.created_at.to_rfc3339(),
                    "updatedAt": r.updated_at.to_rfc3339(),
                })),
            )
                .into_response()
        }
        Ok(None) => (
            StatusCode::NOT_FOUND,
            Json(serde_json::json!({ "error": "Case not found" })),
        )
            .into_response(),
        Err(e) => {
            tracing::error!("Failed to update support case {}: {}", id, e);
            (
                StatusCode::INTERNAL_SERVER_ERROR,
                Json(serde_json::json!({ "error": "Failed to update case" })),
            )
                .into_response()
        }
    }
}

// ── Admin: add message (supports internal notes) ──────────────────────────────

#[derive(Deserialize)]
struct AdminAddMessageBody {
    body: String,
    is_internal: Option<bool>,
}

async fn admin_add_message(
    auth: AuthUser,
    State(state): State<AppState>,
    Path(id): Path<Uuid>,
    Json(body): Json<AdminAddMessageBody>,
) -> impl IntoResponse {
    let is_internal = body.is_internal.unwrap_or(false);

    let exists = sqlx::query_scalar::<_, bool>(
        "SELECT EXISTS(SELECT 1 FROM support_tickets WHERE id = $1)",
    )
    .bind(id)
    .fetch_one(&state.pool)
    .await
    .unwrap_or(false);

    if !exists {
        return (
            StatusCode::NOT_FOUND,
            Json(serde_json::json!({ "error": "Case not found" })),
        )
            .into_response();
    }

    let result = sqlx::query_as::<_, MessageRow>(
        r#"
        INSERT INTO support_ticket_messages (ticket_id, sender_id, body, is_internal)
        VALUES ($1, $2, $3, $4)
        RETURNING id, ticket_id, sender_id, body, is_internal, created_at
        "#,
    )
    .bind(id)
    .bind(auth.user_id)
    .bind(&body.body)
    .bind(is_internal)
    .fetch_one(&state.pool)
    .await;

    // Move to waiting_for_user if this is a non-internal reply
    if !is_internal {
        let _ = sqlx::query(
            "UPDATE support_tickets SET updated_at = NOW(), status = CASE WHEN status = 'new' OR status = 'in_progress' THEN 'waiting_for_user' ELSE status END WHERE id = $1",
        )
        .bind(id)
        .execute(&state.pool)
        .await;
    } else {
        let _ = sqlx::query(
            "UPDATE support_tickets SET updated_at = NOW() WHERE id = $1",
        )
        .bind(id)
        .execute(&state.pool)
        .await;
    }

    match result {
        Ok(m) => {
            // Send email notification to user if this is a non-internal reply
            if !is_internal {
                if let Ok(ticket) = sqlx::query_as::<_, TicketRow>(
                    "SELECT id, reference_number, subject, description, category, priority, status, requester_name, requester_email, assigned_to, created_at, updated_at FROM support_tickets WHERE id = $1"
                )
                .bind(id)
                .fetch_one(&state.pool)
                .await
                {
                    if let Some(user_email) = ticket.requester_email {
                        // Try to get user name from user table
                        let user_name = if let Ok(user) = db::models::user::UserRepository::get_by_email(&state.pool, &user_email).await {
                            format!("{} {}", user.first_name.unwrap_or_default(), user.last_name.unwrap_or_default())
                        } else {
                            ticket.requester_name.unwrap_or_default()
                        };

                        let _ = state.mail.send_support_ticket_replied_email(
                            &user_email,
                            &user_name,
                            &ticket.subject,
                            &body.body
                        ).await;
                    }
                }
            }

            (
                StatusCode::CREATED,
                Json(serde_json::json!({
                    "id": m.id,
                    "ticketId": m.ticket_id,
                    "senderId": m.sender_id,
                    "body": m.body,
                    "isInternal": m.is_internal,
                    "createdAt": m.created_at.to_rfc3339(),
                })),
            )
                .into_response()
        }
        Err(e) => {
            tracing::error!("Failed to add message to case {}: {}", id, e);
            (
                StatusCode::INTERNAL_SERVER_ERROR,
                Json(serde_json::json!({ "error": "Failed to add message" })),
            )
                .into_response()
        }
    }
}

// ── AI Assistant: reference number lookup (no user auth required) ────────────
//
// Dispatches a `NXT-{TYPE}-{YY}-{seq}` reference number to the right table
// based on its {TYPE} segment, and requires the caller-supplied user_id to
// own the record — otherwise 404 (never leak another user's record).

#[derive(Deserialize)]
struct ReferenceLookupQuery {
    #[serde(alias = "userId")]
    user_id: Option<Uuid>,
}

async fn ai_reference_lookup(
    State(state): State<AppState>,
    headers: HeaderMap,
    Path(number): Path<String>,
    Query(q): Query<ReferenceLookupQuery>,
) -> impl IntoResponse {
    let expected_key = std::env::var("AI_SERVICE_KEY").unwrap_or_default();
    let provided_key = headers
        .get("X-AI-Service-Key")
        .and_then(|v| v.to_str().ok())
        .unwrap_or_default();

    if expected_key.is_empty() || provided_key != expected_key {
        return (
            StatusCode::UNAUTHORIZED,
            Json(serde_json::json!({ "error": "Invalid or missing AI service key" })),
        )
            .into_response();
    }

    // Accept user_id as a query param or an X-User-Id header (mirrors the
    // userId-in-body pattern used by ai_create_ticket, adapted for a GET).
    let user_id = q.user_id.or_else(|| {
        headers
            .get("X-User-Id")
            .and_then(|v| v.to_str().ok())
            .and_then(|s| Uuid::parse_str(s).ok())
    });

    let user_id = match user_id {
        Some(uid) => uid,
        None => {
            return (
                StatusCode::BAD_REQUEST,
                Json(serde_json::json!({ "error": "user_id is required" })),
            )
                .into_response();
        }
    };

    // NXT-{TYPE}-{YY}-{seq} -> {TYPE}
    let prefix = number.split('-').nth(1).unwrap_or("").to_uppercase();

    let result = match prefix.as_str() {
        "VER" => lookup_verification_reference(&state, &number, user_id).await,
        "TCK" => lookup_ticket_reference(&state, &number, user_id).await,
        "PAY" => lookup_payment_reference(&state, &number, user_id).await,
        "JOB" => lookup_job_application_reference(&state, &number, user_id).await,
        "LED" => lookup_lead_request_reference(&state, &number, user_id).await,
        _ => Err(StatusCode::NOT_FOUND),
    };

    match result {
        Ok(body) => (StatusCode::OK, Json(body)).into_response(),
        Err(status) => (
            status,
            Json(serde_json::json!({ "error": "Reference number not found" })),
        )
            .into_response(),
    }
}

async fn lookup_verification_reference(
    state: &AppState,
    number: &str,
    user_id: Uuid,
) -> Result<serde_json::Value, StatusCode> {
    use sqlx::Row;

    let row = sqlx::query(
        r#"
        SELECT status, notes, rejection_reason, created_at
        FROM verifications
        WHERE reference_number = $1 AND user_id = $2
        "#,
    )
    .bind(number)
    .bind(user_id)
    .fetch_optional(&state.pool)
    .await
    .map_err(|e| {
        tracing::error!("lookup_verification_reference db error: {}", e);
        StatusCode::INTERNAL_SERVER_ERROR
    })?
    .ok_or(StatusCode::NOT_FOUND)?;

    let status: String = row.try_get("status").unwrap_or_default();
    let notes: Option<String> = row.try_get("notes").ok().flatten();
    let rejection_reason: Option<String> = row.try_get("rejection_reason").ok().flatten();
    let created_at: chrono::DateTime<chrono::Utc> =
        row.try_get("created_at").unwrap_or_else(|_| chrono::Utc::now());

    let summary = if status.eq_ignore_ascii_case("rejected") {
        match rejection_reason {
            Some(reason) => format!("Verification was rejected: {}", reason),
            None => "Verification was rejected.".to_string(),
        }
    } else if let Some(n) = notes {
        format!("Verification is {} — {}", status.to_lowercase(), n)
    } else {
        format!("Verification is currently {}.", status.to_lowercase())
    };

    Ok(serde_json::json!({
        "type": "VERIFICATION",
        "reference_number": number,
        "status": status,
        "summary": summary,
        "created_at": created_at.to_rfc3339(),
    }))
}

async fn lookup_ticket_reference(
    state: &AppState,
    number: &str,
    user_id: Uuid,
) -> Result<serde_json::Value, StatusCode> {
    use sqlx::Row;

    let row = sqlx::query(
        r#"
        SELECT subject, status, created_at
        FROM support_tickets
        WHERE reference_number = $1 AND user_id = $2
        "#,
    )
    .bind(number)
    .bind(user_id)
    .fetch_optional(&state.pool)
    .await
    .map_err(|e| {
        tracing::error!("lookup_ticket_reference db error: {}", e);
        StatusCode::INTERNAL_SERVER_ERROR
    })?
    .ok_or(StatusCode::NOT_FOUND)?;

    let subject: String = row.try_get("subject").unwrap_or_default();
    let status: String = row.try_get("status").unwrap_or_default();
    let created_at: chrono::DateTime<chrono::Utc> =
        row.try_get("created_at").unwrap_or_else(|_| chrono::Utc::now());

    Ok(serde_json::json!({
        "type": "TICKET",
        "reference_number": number,
        "status": status,
        "summary": format!("Support ticket \"{}\" is {}.", subject, status.to_lowercase()),
        "created_at": created_at.to_rfc3339(),
    }))
}

async fn lookup_payment_reference(
    state: &AppState,
    number: &str,
    user_id: Uuid,
) -> Result<serde_json::Value, StatusCode> {
    use sqlx::Row;

    let row = sqlx::query(
        r#"
        SELECT status, amount_inr, created_at
        FROM payments
        WHERE reference_number = $1 AND user_id = $2
        "#,
    )
    .bind(number)
    .bind(user_id)
    .fetch_optional(&state.pool)
    .await
    .map_err(|e| {
        tracing::error!("lookup_payment_reference db error: {}", e);
        StatusCode::INTERNAL_SERVER_ERROR
    })?
    .ok_or(StatusCode::NOT_FOUND)?;

    let status: String = row.try_get("status").unwrap_or_default();
    let amount_inr: i32 = row.try_get("amount_inr").unwrap_or_default();
    let created_at: chrono::DateTime<chrono::Utc> =
        row.try_get("created_at").unwrap_or_else(|_| chrono::Utc::now());

    Ok(serde_json::json!({
        "type": "PAYMENT",
        "reference_number": number,
        "status": status,
        "summary": format!("Payment of ₹{} is {}.", amount_inr, status.to_lowercase()),
        "created_at": created_at.to_rfc3339(),
    }))
}

async fn lookup_job_application_reference(
    state: &AppState,
    number: &str,
    user_id: Uuid,
) -> Result<serde_json::Value, StatusCode> {
    use sqlx::Row;

    let row = sqlx::query(
        r#"
        SELECT status, applied_at
        FROM job_applications
        WHERE reference_number = $1 AND applicant_user_id = $2
        "#,
    )
    .bind(number)
    .bind(user_id)
    .fetch_optional(&state.pool)
    .await
    .map_err(|e| {
        tracing::error!("lookup_job_application_reference db error: {}", e);
        StatusCode::INTERNAL_SERVER_ERROR
    })?
    .ok_or(StatusCode::NOT_FOUND)?;

    let status: String = row.try_get("status").unwrap_or_default();
    let applied_at: chrono::DateTime<chrono::Utc> =
        row.try_get("applied_at").unwrap_or_else(|_| chrono::Utc::now());

    Ok(serde_json::json!({
        "type": "JOB_APPLICATION",
        "reference_number": number,
        "status": status,
        "summary": format!("Job application is {}.", status.to_lowercase()),
        "created_at": applied_at.to_rfc3339(),
    }))
}

async fn lookup_lead_request_reference(
    state: &AppState,
    number: &str,
    user_id: Uuid,
) -> Result<serde_json::Value, StatusCode> {
    use sqlx::Row;

    let row = sqlx::query(
        r#"
        SELECT status, message, created_at
        FROM lead_requests
        WHERE reference_number = $1 AND customer_user_id = $2
        "#,
    )
    .bind(number)
    .bind(user_id)
    .fetch_optional(&state.pool)
    .await
    .map_err(|e| {
        tracing::error!("lookup_lead_request_reference db error: {}", e);
        StatusCode::INTERNAL_SERVER_ERROR
    })?
    .ok_or(StatusCode::NOT_FOUND)?;

    let status: String = row.try_get("status").unwrap_or_default();
    let message: Option<String> = row.try_get("message").ok().flatten();
    let created_at: chrono::DateTime<chrono::Utc> =
        row.try_get("created_at").unwrap_or_else(|_| chrono::Utc::now());

    let summary = match message {
        Some(m) => format!("Lead request is {} — {}", status.to_lowercase(), m),
        None => format!("Lead request is {}.", status.to_lowercase()),
    };

    Ok(serde_json::json!({
        "type": "LEAD_REQUEST",
        "reference_number": number,
        "status": status,
        "summary": summary,
        "created_at": created_at.to_rfc3339(),
    }))
}
