//! SqlX error parser for converting database errors into user-friendly ProcessedError responses.
//!
//! This module handles:
//! - PostgreSQL-specific error codes
//! - Connection and pool errors
//! - Query execution errors
//! - Type conversion errors

use actix_web::http::StatusCode;
use serde_json::{Map, json};
use sqlx::error::DatabaseError;

use super::sql_sanitizer::{extract_metadata, sanitize_error_message};
use super::{ErrorCategory, ProcessedError, generate_trace_id};

/// Converts a sqlx::Error into a ProcessedError with sanitized messages and metadata.
///
/// This function:
/// - Categorizes the error type
/// - Extracts safe metadata (constraint names, SQL state codes)
/// - Sanitizes error messages to remove SQL queries
/// - Generates user-friendly messages and hints
/// - Assigns appropriate HTTP status codes
pub fn process_sqlx_error(err: &sqlx::Error) -> ProcessedError {
    process_sqlx_error_with_context(err, None)
}

/// Converts a sqlx::Error with additional context into a ProcessedError.
///
/// The context parameter can provide table names or other safe identifiers
/// that help make the error message more specific.
pub fn process_sqlx_error_with_context(err: &sqlx::Error, context: Option<&str>) -> ProcessedError {
    let trace_id = generate_trace_id();

    match err {
        sqlx::Error::Database(db_err) => {
            process_database_error(db_err.as_ref(), &trace_id, context)
        }
        sqlx::Error::PoolTimedOut => create_connection_error(
            "pool_timeout",
            "Database connection pool timed out",
            "The system is experiencing high load. Please try again in a moment.",
            &trace_id,
        ),
        sqlx::Error::PoolClosed => create_connection_error(
            "pool_closed",
            "Database connection pool is closed",
            "The database connection pool has been shut down. Please contact support.",
            &trace_id,
        ),
        sqlx::Error::WorkerCrashed => create_internal_error(
            "worker_crashed",
            "Database worker thread crashed",
            &trace_id,
        ),
        sqlx::Error::Io(io_err) => create_connection_error(
            "io_error",
            "Database I/O error",
            &format!("Network or I/O issue occurred: {}", io_err.kind()),
            &trace_id,
        ),
        sqlx::Error::Tls(tls_err) => create_connection_error(
            "tls_error",
            "Database TLS connection error",
            &format!(
                "Secure connection failed: {}",
                sanitize_error_message(&tls_err.to_string())
            ),
            &trace_id,
        ),
        sqlx::Error::ColumnNotFound(column) => {
            let mut error = ProcessedError::new(
                ErrorCategory::QuerySyntax,
                StatusCode::INTERNAL_SERVER_ERROR,
                "column_not_found",
                format!("Column '{}' not found in query results", column),
                trace_id,
            )
            .with_hint(
                "This may indicate a mismatch between the query and expected result structure.",
            )
            .with_metadata("column", json!(column));

            if let Some(ctx) = context {
                error = error.with_metadata("context", json!(ctx));
            }

            error
        }
        sqlx::Error::ColumnIndexOutOfBounds { index, len } => ProcessedError::new(
            ErrorCategory::Internal,
            StatusCode::INTERNAL_SERVER_ERROR,
            "column_index_out_of_bounds",
            format!(
                "Column index {} is out of bounds (total columns: {})",
                index, len
            ),
            trace_id,
        )
        .with_metadata("index", json!(index))
        .with_metadata("column_count", json!(len)),
        sqlx::Error::TypeNotFound { type_name } => ProcessedError::new(
            ErrorCategory::Internal,
            StatusCode::INTERNAL_SERVER_ERROR,
            "type_not_found",
            format!("Database type '{}' not recognized", type_name),
            trace_id,
        )
        .with_metadata("type_name", json!(type_name)),
        sqlx::Error::ColumnDecode { index, source } => {
            let sanitized_msg = sanitize_error_message(&source.to_string());
            ProcessedError::new(
                ErrorCategory::Internal,
                StatusCode::INTERNAL_SERVER_ERROR,
                "column_decode_error",
                format!(
                    "Failed to decode column at index {}: {}",
                    index, sanitized_msg
                ),
                trace_id,
            )
            .with_metadata("column_index", json!(index))
        }
        sqlx::Error::RowNotFound => ProcessedError::new(
            ErrorCategory::NotFound,
            StatusCode::NOT_FOUND,
            "row_not_found",
            "The requested record was not found",
            trace_id,
        )
        .with_hint("Verify the identifier and try again."),
        sqlx::Error::Migrate(_) => ProcessedError::new(
            ErrorCategory::Internal,
            StatusCode::INTERNAL_SERVER_ERROR,
            "migration_error",
            "Database migration error",
            trace_id,
        ),
        sqlx::Error::Configuration(config_err) => {
            let sanitized_msg = sanitize_error_message(&config_err.to_string());
            ProcessedError::new(
                ErrorCategory::Internal,
                StatusCode::INTERNAL_SERVER_ERROR,
                "configuration_error",
                format!("Database configuration error: {}", sanitized_msg),
                trace_id,
            )
        }
        _ => {
            // Fallback for any unhandled error types
            let sanitized_msg = sanitize_error_message(&err.to_string());
            ProcessedError::new(
                ErrorCategory::Internal,
                StatusCode::INTERNAL_SERVER_ERROR,
                "database_error",
                format!("Database operation failed: {}", sanitized_msg),
                trace_id,
            )
        }
    }
}

/// Processes database-specific errors (PostgreSQL errors with codes).
fn process_database_error(
    db_err: &dyn DatabaseError,
    trace_id: &str,
    context: Option<&str>,
) -> ProcessedError {
    let code = db_err.code().map(|c| c.to_string());
    let message = db_err.message();
    let constraint = db_err.constraint();

    // Extract metadata from the error message
    let extracted = extract_metadata(message);
    let mut metadata = Map::new();

    if let Some(sql_code) = &code {
        metadata.insert("sql_state".to_string(), json!(sql_code));
    }

    if let Some(constraint_name) = constraint.or(extracted.constraint_name.as_deref()) {
        metadata.insert("constraint".to_string(), json!(constraint_name));
    }

    if let Some(column_name) = &extracted.column_name {
        metadata.insert("column".to_string(), json!(column_name));
    }

    if let Some(table_name) = &extracted.table_name {
        metadata.insert("table".to_string(), json!(table_name));
    }

    if let Some(ctx) = context {
        metadata.insert("context".to_string(), json!(ctx));
    }

    // Map SQL state codes to user-friendly errors
    match code.as_deref() {
        // Unique violation
        Some("23505") => ProcessedError::new(
            ErrorCategory::DatabaseConstraint,
            StatusCode::CONFLICT,
            "unique_violation",
            "A record with this value already exists",
            trace_id,
        )
        .with_hint("Please use a different value or update the existing record.")
        .with_metadata_map(metadata),

        // Foreign key violation
        Some("23503") => ProcessedError::new(
            ErrorCategory::DatabaseConstraint,
            StatusCode::UNPROCESSABLE_ENTITY,
            "foreign_key_violation",
            "The operation references a non-existent or inaccessible resource",
            trace_id,
        )
        .with_hint("Ensure all referenced resources exist and are accessible.")
        .with_metadata_map(metadata),

        // Not null violation
        Some("23502") => ProcessedError::new(
            ErrorCategory::Validation,
            StatusCode::BAD_REQUEST,
            "not_null_violation",
            "A required field is missing",
            trace_id,
        )
        .with_hint("Please provide all required fields.")
        .with_metadata_map(metadata),

        // Check constraint violation
        Some("23514") => ProcessedError::new(
            ErrorCategory::DatabaseConstraint,
            StatusCode::BAD_REQUEST,
            "check_constraint_violation",
            "The provided value does not meet the required constraints",
            trace_id,
        )
        .with_hint("Please ensure the value meets all validation requirements.")
        .with_metadata_map(metadata),

        // Undefined column
        Some("42703") => ProcessedError::new(
            ErrorCategory::QuerySyntax,
            StatusCode::BAD_REQUEST,
            "undefined_column",
            "The specified column does not exist",
            trace_id,
        )
        .with_hint("Please verify the column name and try again.")
        .with_metadata_map(metadata),

        // Undefined table
        Some("42P01") => ProcessedError::new(
            ErrorCategory::QuerySyntax,
            StatusCode::BAD_REQUEST,
            "undefined_table",
            "The specified table does not exist",
            trace_id,
        )
        .with_hint("Please verify the table name and ensure it has been created.")
        .with_metadata_map(metadata),

        // Syntax error
        Some("42601") => ProcessedError::new(
            ErrorCategory::QuerySyntax,
            StatusCode::BAD_REQUEST,
            "syntax_error",
            "The query contains a syntax error",
            trace_id,
        )
        .with_hint("Please check your query syntax.")
        .with_metadata_map(metadata),

        // Insufficient privilege
        Some("42501") => ProcessedError::new(
            ErrorCategory::Authorization,
            StatusCode::FORBIDDEN,
            "insufficient_privilege",
            "You do not have permission to perform this operation",
            trace_id,
        )
        .with_hint("Please contact an administrator for access.")
        .with_metadata_map(metadata),

        // Connection exceptions (08xxx)
        Some(code) if code.starts_with("08") => ProcessedError::new(
            ErrorCategory::DatabaseConnection,
            StatusCode::SERVICE_UNAVAILABLE,
            "connection_error",
            "Database connection issue",
            trace_id,
        )
        .with_hint("The database is temporarily unavailable. Please try again.")
        .with_metadata_map(metadata),

        // Fallback for other database errors
        _ => {
            let sanitized_msg = sanitize_error_message(message);
            ProcessedError::new(
                ErrorCategory::Internal,
                StatusCode::INTERNAL_SERVER_ERROR,
                "database_error",
                format!("Database operation failed: {}", sanitized_msg),
                trace_id,
            )
            .with_metadata_map(metadata)
        }
    }
}

/// Creates a connection error ProcessedError.
fn create_connection_error(
    code: &'static str,
    message: &str,
    hint: &str,
    trace_id: &str,
) -> ProcessedError {
    ProcessedError::new(
        ErrorCategory::DatabaseConnection,
        StatusCode::SERVICE_UNAVAILABLE,
        code,
        message,
        trace_id,
    )
    .with_hint(hint)
}

/// Creates an internal error ProcessedError.
fn create_internal_error(code: &'static str, message: &str, trace_id: &str) -> ProcessedError {
    ProcessedError::new(
        ErrorCategory::Internal,
        StatusCode::INTERNAL_SERVER_ERROR,
        code,
        message,
        trace_id,
    )
    .with_hint("An unexpected error occurred. Please contact support if this persists.")
}

#[cfg(test)]
mod tests {
    use super::*;

    #[test]
    fn test_row_not_found_error() {
        let err = sqlx::Error::RowNotFound;
        let processed = process_sqlx_error(&err);

        assert_eq!(processed.category, ErrorCategory::NotFound);
        assert_eq!(processed.status_code, StatusCode::NOT_FOUND);
        assert_eq!(processed.error_code, "row_not_found");
        assert!(processed.hint.is_some());
    }

    #[test]
    fn test_pool_timeout_error() {
        let err = sqlx::Error::PoolTimedOut;
        let processed = process_sqlx_error(&err);

        assert_eq!(processed.category, ErrorCategory::DatabaseConnection);
        assert_eq!(processed.status_code, StatusCode::SERVICE_UNAVAILABLE);
        assert_eq!(processed.error_code, "pool_timeout");
    }

    #[test]
    fn test_column_not_found_error() {
        let err = sqlx::Error::ColumnNotFound("email".to_string());
        let processed = process_sqlx_error(&err);

        assert_eq!(processed.category, ErrorCategory::QuerySyntax);
        assert_eq!(processed.error_code, "column_not_found");
        assert_eq!(processed.metadata.get("column").unwrap(), "email");
    }
}
