<?php
/**
 * DatabaseException class for database errors.
 *
 * @package    UltimateSecurity
 * @subpackage Support\Exceptions
 * @since      1.0.0
 */

namespace WPUltimateSecurity\UltimateSecurity\Support\Exceptions;

use Exception;
use WPUltimateSecurity\UltimateSecurity\Support\Result;

/**
 * Exception thrown when database operations fail.
 *
 * Provides context about the failed operation and affected table.
 *
 * Usage:
 *   throw DatabaseException::queryFailed($wpdb->last_error, 'SELECT * FROM table');
 *   throw DatabaseException::insertFailed('users', $wpdb->last_error);
 *   throw DatabaseException::connectionFailed();
 *
 * @since 1.0.0
 */
class DatabaseException extends Exception {

    /**
     * Error code for programmatic handling.
     *
     * @since 1.0.0
     * @var string
     */
    private string $error_code;

    /**
     * HTTP status code for this exception.
     *
     * @since 1.0.0
     * @var int
     */
    private int $status_code = 500;

    /**
     * The table name involved in the error.
     *
     * @since 1.0.0
     * @var string|null
     */
    private ?string $table_name;

    /**
     * The SQL query that failed.
     *
     * @since 1.0.0
     * @var string|null
     */
    private ?string $query;

    /**
     * Additional context data.
     *
     * @since 1.0.0
     * @var array<string, mixed>
     */
    private array $context;

    /**
     * Common error codes.
     */
    public const CODE_CONNECTION_FAILED  = 'db_connection_failed';
    public const CODE_QUERY_FAILED       = 'db_query_failed';
    public const CODE_INSERT_FAILED      = 'db_insert_failed';
    public const CODE_UPDATE_FAILED      = 'db_update_failed';
    public const CODE_DELETE_FAILED      = 'db_delete_failed';
    public const CODE_TABLE_NOT_FOUND    = 'db_table_not_found';
    public const CODE_DUPLICATE_ENTRY    = 'db_duplicate_entry';
    public const CODE_CONSTRAINT_VIOLATION = 'db_constraint_violation';
    public const CODE_TRANSACTION_FAILED = 'db_transaction_failed';
    public const CODE_MIGRATION_FAILED   = 'db_migration_failed';
    public const CODE_SCHEMA_ERROR       = 'db_schema_error';
    public const CODE_RECORD_NOT_FOUND   = 'db_record_not_found';
    public const CODE_INTEGRITY_ERROR    = 'db_integrity_error';

    /**
     * Create a new DatabaseException.
     *
     * @since 1.0.0
     *
     * @param string              $message     Error message.
     * @param string              $error_code  Error code for programmatic handling.
     * @param string|null         $table_name  Table name involved.
     * @param string|null         $query       The SQL query that failed.
     * @param array<string,mixed> $context     Additional context data.
     * @param Exception|null      $previous    Previous exception.
     */
    public function __construct(
        string $message,
        string $error_code = 'database_error',
        ?string $table_name = null,
        ?string $query = null,
        array $context = [],
        ?Exception $previous = null
    ) {
        $this->error_code = $error_code;
        $this->table_name = $table_name;
        $this->query      = $query;
        $this->context    = $context;

        parent::__construct( $message, 0, $previous );
    }

    /**
     * Create exception for connection failure.
     *
     * @since 1.0.0
     *
     * @param string|null $db_error Database error message.
     * @return self
     */
    public static function connectionFailed( ?string $db_error = null ): self {
        $message = __( 'Failed to connect to the database.', 'ultimate-security' );

        $context = [];
        if ( $db_error ) {
            $context['db_error'] = $db_error;
        }

        return new self(
            $message,
            self::CODE_CONNECTION_FAILED,
            null,
            null,
            $context
        );
    }

    /**
     * Create exception for query failure.
     *
     * @since 1.0.0
     *
     * @param string      $db_error Database error message.
     * @param string|null $query    The SQL query that failed.
     * @return self
     */
    public static function queryFailed( string $db_error, ?string $query = null ): self {
        return new self(
            sprintf(
                /* translators: %s: database error message */
                __( 'Database query failed: %s', 'ultimate-security' ),
                $db_error
            ),
            self::CODE_QUERY_FAILED,
            null,
            $query,
            [ 'db_error' => $db_error ]
        );
    }

    /**
     * Create exception for insert failure.
     *
     * @since 1.0.0
     *
     * @param string      $table_name Table name.
     * @param string|null $db_error   Database error message.
     * @param array       $data       Data that failed to insert.
     * @return self
     */
    public static function insertFailed(
        string $table_name,
        ?string $db_error = null,
        array $data = []
    ): self {
        $context = [];
        if ( $db_error ) {
            $context['db_error'] = $db_error;
        }
        if ( ! empty( $data ) ) {
            // Remove sensitive data before storing in context.
            $safe_data = self::sanitizeDataForContext( $data );
            $context['data'] = $safe_data;
        }

        return new self(
            sprintf(
                /* translators: %s: table name */
                __( 'Failed to insert record into %s.', 'ultimate-security' ),
                $table_name
            ),
            self::CODE_INSERT_FAILED,
            $table_name,
            null,
            $context
        );
    }

    /**
     * Create exception for update failure.
     *
     * @since 1.0.0
     *
     * @param string      $table_name Table name.
     * @param string|null $db_error   Database error message.
     * @param array       $where      Where conditions.
     * @return self
     */
    public static function updateFailed(
        string $table_name,
        ?string $db_error = null,
        array $where = []
    ): self {
        $context = [];
        if ( $db_error ) {
            $context['db_error'] = $db_error;
        }
        if ( ! empty( $where ) ) {
            $context['where'] = $where;
        }

        return new self(
            sprintf(
                /* translators: %s: table name */
                __( 'Failed to update record in %s.', 'ultimate-security' ),
                $table_name
            ),
            self::CODE_UPDATE_FAILED,
            $table_name,
            null,
            $context
        );
    }

    /**
     * Create exception for delete failure.
     *
     * @since 1.0.0
     *
     * @param string      $table_name Table name.
     * @param string|null $db_error   Database error message.
     * @param array       $where      Where conditions.
     * @return self
     */
    public static function deleteFailed(
        string $table_name,
        ?string $db_error = null,
        array $where = []
    ): self {
        $context = [];
        if ( $db_error ) {
            $context['db_error'] = $db_error;
        }
        if ( ! empty( $where ) ) {
            $context['where'] = $where;
        }

        return new self(
            sprintf(
                /* translators: %s: table name */
                __( 'Failed to delete record from %s.', 'ultimate-security' ),
                $table_name
            ),
            self::CODE_DELETE_FAILED,
            $table_name,
            null,
            $context
        );
    }

    /**
     * Create exception for missing table.
     *
     * @since 1.0.0
     *
     * @param string $table_name Table name.
     * @return self
     */
    public static function tableNotFound( string $table_name ): self {
        return new self(
            sprintf(
                /* translators: %s: table name */
                __( 'Database table %s does not exist.', 'ultimate-security' ),
                $table_name
            ),
            self::CODE_TABLE_NOT_FOUND,
            $table_name
        );
    }

    /**
     * Create exception for duplicate entry.
     *
     * @since 1.0.0
     *
     * @param string      $table_name Table name.
     * @param string      $field      Field with duplicate value.
     * @param string|null $value      The duplicate value.
     * @return self
     */
    public static function duplicateEntry(
        string $table_name,
        string $field,
        ?string $value = null
    ): self {
        $context = [ 'field' => $field ];
        if ( $value ) {
            $context['value'] = $value;
        }

        return new self(
            sprintf(
                /* translators: 1: field name, 2: table name */
                __( 'Duplicate value for %1$s in %2$s.', 'ultimate-security' ),
                $field,
                $table_name
            ),
            self::CODE_DUPLICATE_ENTRY,
            $table_name,
            null,
            $context
        );
    }

    /**
     * Create exception for constraint violation.
     *
     * @since 1.0.0
     *
     * @param string      $table_name     Table name.
     * @param string      $constraint     Constraint that was violated.
     * @param string|null $db_error       Database error message.
     * @return self
     */
    public static function constraintViolation(
        string $table_name,
        string $constraint,
        ?string $db_error = null
    ): self {
        $context = [ 'constraint' => $constraint ];
        if ( $db_error ) {
            $context['db_error'] = $db_error;
        }

        return new self(
            sprintf(
                /* translators: 1: constraint name, 2: table name */
                __( 'Constraint %1$s violated in %2$s.', 'ultimate-security' ),
                $constraint,
                $table_name
            ),
            self::CODE_CONSTRAINT_VIOLATION,
            $table_name,
            null,
            $context
        );
    }

    /**
     * Create exception for transaction failure.
     *
     * @since 1.0.0
     *
     * @param string      $operation Operation that failed (start, commit, rollback).
     * @param string|null $db_error  Database error message.
     * @return self
     */
    public static function transactionFailed( string $operation, ?string $db_error = null ): self {
        $context = [ 'operation' => $operation ];
        if ( $db_error ) {
            $context['db_error'] = $db_error;
        }

        return new self(
            sprintf(
                /* translators: %s: operation name */
                __( 'Database transaction %s failed.', 'ultimate-security' ),
                $operation
            ),
            self::CODE_TRANSACTION_FAILED,
            null,
            null,
            $context
        );
    }

    /**
     * Create exception for migration failure.
     *
     * @since 1.0.0
     *
     * @param string      $migration  Migration identifier.
     * @param string|null $db_error   Database error message.
     * @return self
     */
    public static function migrationFailed( string $migration, ?string $db_error = null ): self {
        $context = [ 'migration' => $migration ];
        if ( $db_error ) {
            $context['db_error'] = $db_error;
        }

        return new self(
            sprintf(
                /* translators: %s: migration name */
                __( 'Database migration %s failed.', 'ultimate-security' ),
                $migration
            ),
            self::CODE_MIGRATION_FAILED,
            null,
            null,
            $context
        );
    }

    /**
     * Create exception for schema error.
     *
     * @since 1.0.0
     *
     * @param string      $description Description of the schema error.
     * @param string|null $table_name  Affected table.
     * @return self
     */
    public static function schemaError( string $description, ?string $table_name = null ): self {
        return new self(
            sprintf(
                /* translators: %s: error description */
                __( 'Database schema error: %s', 'ultimate-security' ),
                $description
            ),
            self::CODE_SCHEMA_ERROR,
            $table_name
        );
    }

    /**
     * Create exception for record not found.
     *
     * @since 1.0.0
     *
     * @param string     $table_name Table name.
     * @param int|string $id         Record ID.
     * @return self
     */
    public static function recordNotFound( string $table_name, $id ): self {
        // Use 404 status for not found.
        $exception              = new self(
            sprintf(
                /* translators: 1: record ID, 2: table name */
                __( 'Record %1$s not found in %2$s.', 'ultimate-security' ),
                $id,
                $table_name
            ),
            self::CODE_RECORD_NOT_FOUND,
            $table_name,
            null,
            [ 'id' => $id ]
        );
        $exception->status_code = 404;

        return $exception;
    }

    /**
     * Create exception for integrity error.
     *
     * @since 1.0.0
     *
     * @param string $description Description of the integrity issue.
     * @param string $table_name  Affected table.
     * @return self
     */
    public static function integrityError( string $description, string $table_name ): self {
        return new self(
            sprintf(
                /* translators: 1: table name, 2: error description */
                __( 'Data integrity error in %1$s: %2$s', 'ultimate-security' ),
                $table_name,
                $description
            ),
            self::CODE_INTEGRITY_ERROR,
            $table_name
        );
    }

    /**
     * Create exception from wpdb error.
     *
     * @since 1.0.0
     *
     * @param \wpdb       $wpdb       WordPress database object.
     * @param string|null $table_name Table name if known.
     * @return self
     */
    public static function fromWpdb( \wpdb $wpdb, ?string $table_name = null ): self {
        $error = $wpdb->last_error ?: __( 'Unknown database error.', 'ultimate-security' );
        $query = $wpdb->last_query;

        return new self(
            sprintf(
                /* translators: %s: database error message */
                __( 'Database error: %s', 'ultimate-security' ),
                $error
            ),
            self::CODE_QUERY_FAILED,
            $table_name,
            $query,
            [ 'db_error' => $error ]
        );
    }

    /**
     * Get the error code.
     *
     * @since 1.0.0
     *
     * @return string
     */
    public function getErrorCode(): string {
        return $this->error_code;
    }

    /**
     * Get the HTTP status code.
     *
     * @since 1.0.0
     *
     * @return int
     */
    public function getStatusCode(): int {
        return $this->status_code;
    }

    /**
     * Get the table name.
     *
     * @since 1.0.0
     *
     * @return string|null
     */
    public function getTableName(): ?string {
        return $this->table_name;
    }

    /**
     * Get the SQL query.
     *
     * @since 1.0.0
     *
     * @return string|null
     */
    public function getQuery(): ?string {
        return $this->query;
    }

    /**
     * Get the context data.
     *
     * @since 1.0.0
     *
     * @return array<string, mixed>
     */
    public function getContext(): array {
        return $this->context;
    }

    /**
     * Get a specific context value.
     *
     * @since 1.0.0
     *
     * @param string $key     The context key.
     * @param mixed  $default Default value if not found.
     * @return mixed
     */
    public function getContextValue( string $key, $default = null ) {
        return $this->context[ $key ] ?? $default;
    }

    /**
     * Get the original database error.
     *
     * @since 1.0.0
     *
     * @return string|null
     */
    public function getDbError(): ?string {
        return $this->context['db_error'] ?? null;
    }

    /**
     * Convert to a Result object.
     *
     * @since 1.0.0
     *
     * @return Result
     */
    public function toResult(): Result {
        $result = Result::failure( $this->getMessage(), $this->error_code );

        if ( $this->table_name ) {
            $result = $result->withMetadata( 'table_name', $this->table_name );
        }

        foreach ( $this->context as $key => $value ) {
            $result = $result->withMetadata( $key, $value );
        }

        return $result;
    }

    /**
     * Convert to array format suitable for REST API responses.
     *
     * Note: Sensitive information (queries, internal errors) are omitted in production.
     *
     * @since 1.0.0
     *
     * @param bool $include_debug Whether to include debug information.
     * @return array{success: bool, error: string, code: string, context?: array}
     */
    public function toArray( bool $include_debug = false ): array {
        $response = [
            'success' => false,
            'error'   => __( 'A database error occurred. Please try again later.', 'ultimate-security' ),
            'code'    => $this->error_code,
        ];

        // Include detailed error info only in debug mode.
        if ( $include_debug || ( defined( 'WP_DEBUG' ) && WP_DEBUG ) ) {
            $response['error']   = $this->getMessage();
            $response['context'] = [
                'table' => $this->table_name,
            ];

            if ( ! empty( $this->context['db_error'] ) ) {
                $response['context']['db_error'] = $this->context['db_error'];
            }
        }

        return $response;
    }

    /**
     * Convert to a WP_Error object.
     *
     * @since 1.0.0
     *
     * @return \WP_Error
     */
    public function toWpError(): \WP_Error {
        return new \WP_Error(
            $this->error_code,
            $this->getMessage(),
            [
                'status'     => $this->status_code,
                'table_name' => $this->table_name,
                'query'      => $this->query,
                'context'    => $this->context,
            ]
        );
    }

    /**
     * Convert to a REST response.
     *
     * @since 1.0.0
     *
     * @return \WP_REST_Response
     */
    public function toRestResponse(): \WP_REST_Response {
        return new \WP_REST_Response( $this->toArray(), $this->status_code );
    }

    /**
     * Check if this is a specific error type.
     *
     * @since 1.0.0
     *
     * @param string $code Error code to check.
     * @return bool
     */
    public function is( string $code ): bool {
        return $this->error_code === $code;
    }

    /**
     * Check if this is a retryable error.
     *
     * @since 1.0.0
     *
     * @return bool
     */
    public function isRetryable(): bool {
        return in_array(
            $this->error_code,
            [
                self::CODE_CONNECTION_FAILED,
                self::CODE_QUERY_FAILED,
                self::CODE_TRANSACTION_FAILED,
            ],
            true
        );
    }

    /**
     * Check if this error should trigger a schema repair.
     *
     * @since 1.0.0
     *
     * @return bool
     */
    public function requiresSchemaRepair(): bool {
        return in_array(
            $this->error_code,
            [
                self::CODE_TABLE_NOT_FOUND,
                self::CODE_SCHEMA_ERROR,
            ],
            true
        );
    }

    /**
     * Sanitize data for storing in context.
     *
     * Removes sensitive fields that should not be logged.
     *
     * @since 1.0.0
     *
     * @param array $data Data to sanitize.
     * @return array Sanitized data.
     */
    private static function sanitizeDataForContext( array $data ): array {
        $sensitive_keys = [
            'password',
            'pass',
            'secret',
            'token',
            'key',
            'api_key',
            'auth',
            'credentials',
            'credit_card',
            'cc_number',
            'cvv',
            'ssn',
        ];

        $sanitized = [];

        foreach ( $data as $key => $value ) {
            $lower_key = strtolower( $key );

            // Check if key contains sensitive words.
            $is_sensitive = false;
            foreach ( $sensitive_keys as $sensitive ) {
                if ( strpos( $lower_key, $sensitive ) !== false ) {
                    $is_sensitive = true;
                    break;
                }
            }

            if ( $is_sensitive ) {
                $sanitized[ $key ] = '[REDACTED]';
            } elseif ( is_array( $value ) ) {
                $sanitized[ $key ] = self::sanitizeDataForContext( $value );
            } else {
                $sanitized[ $key ] = $value;
            }
        }

        return $sanitized;
    }
}
