<?php
/**
 * Notification class.
 *
 * @package SAAI\Classes
 */

namespace SAAI\Classes;

use SAAI\Abstracts\Abstract_Class;
use SAAI\Traits\Singleton_Trait;
use SAAI\Helpers\Helper;
use WP_Error;

defined( 'ABSPATH' ) || exit;

/**
 * Notification class
 *
 * Handles operations for StoreAgent notifications system that allows agents to
 * create notifications for store owners through the Dashboard interface.
 *
 * @since 1.0.0
 */
class Notification extends Abstract_Class {

    use Singleton_Trait;

    /**
     * Notification types
     */
    const TYPE_INFO     = 'info';
    const TYPE_WARNING  = 'warning';
    const TYPE_SUCCESS  = 'success';
    const TYPE_DECISION = 'decision';

    /**
     * Notification statuses
     */
    const STATUS_PENDING  = 'pending';
    const STATUS_ACCEPTED = 'accepted';
    const STATUS_REJECTED = 'rejected';
    const STATUS_ARCHIVED = 'archived';

    /**
     * List of allowed callback methods that can be executed from notifications
     * Format: 'Class::method' => true
     *
     * @var array
     */
    private $allowed_callbacks = array(
        'SAAI\AI_Agents\Product_Description_AI::apply_description_update' => true,
        'SAAI\AI_Agents\Product_Description_AI::discard_description_update' => true,
        'SAAI\AI_Agents\Product_Tags_Generator_AI::apply_tags_update' => true,
        'SAAI\AI_Agents\Product_Tags_Generator_AI::discard_tags_update' => true,
        'SAAI\AI_Agents\Review_Summaries_AI::apply_summary_update' => true,
        'SAAI\AI_Agents\Review_Summaries_AI::discard_summary_update' => true,
        'SAAI\AI_Agents\Product_Questions_AI::publish_qa' => true,
        'SAAI\AI_Agents\Product_Questions_AI::discard_qa' => true,
        'SAAI\AI_Agents\Category_Description_AI::apply_description_update' => true,
        'SAAI\AI_Agents\Category_Description_AI::discard_description_update' => true,
        'SAAI\AI_Agents\Product_Summaries_AI::generate_product_short_description' => true,
        'SAAI\AI_Agents\Product_Summaries_AI::dismiss_missing_description_notification' => true,
        'SAAI\AI_Agents\Review_Moderation_AI::approve_review' => true,
        'SAAI\AI_Agents\Review_Moderation_AI::reject_review' => true,
        'SAAI\AI_Agents\Product_Upsells_AI::generate_and_apply_recommendations' => true,
        'SAAI\AI_Agents\Product_Upsells_AI::discard_recommendations' => true,
    );

    /**
     * Get the notifications table name
     *
     * @return string The table name with prefix.
     */
    private function get_table_name() {
        global $wpdb;
        return $wpdb->prefix . 'storeagent_notifications';
    }

    /**
     * Log errors and important events related to notifications
     *
     * @param string $message The message to log.
     * @param string $level The log level.
     * @param array  $data Additional data to log.
     * @return void
     */
    private function log( $message, $level = 'error', $data = array() ) {
        if ( function_exists( 'wc_get_logger' ) ) {
            $logger  = wc_get_logger();
            $context = array(
                'source' => 'storeagent-notifications',
                'data'   => $data,
            );
            $logger->log( $level, $message, $context );
        } else {
            error_log( sprintf( '[StoreAgent Notifications] %s: %s', $level, $message ) ); // phpcs:ignore
        }
    }

    /**
     * Validate actions array structure
     *
     * @param array $actions The actions to validate.
     * @return array|WP_Error The validated actions or error.
     */
    private function validate_actions( $actions ) {
        if ( ! is_array( $actions ) ) {
            return new WP_Error( 'invalid_actions', __( 'Actions must be an array', 'storeagent-ai-for-woocommerce' ) );
        }

        foreach ( $actions as $key => $action ) {
            // Required fields.
            if ( empty( $action['label'] ) || empty( $action['action'] ) ) {
                return new WP_Error( 'invalid_action', __( 'Each action must have a label and action', 'storeagent-ai-for-woocommerce' ) );
            }

            // Sanitize.
            $actions[ $key ]['label']  = sanitize_text_field( $action['label'] );
            $actions[ $key ]['action'] = sanitize_key( $action['action'] );

            // Only validate endpoint if not using direct URL. Eventually we will allow non-StoreAgent endpoints but for now we only allow StoreAgent endpoints.
            if ( ! empty( $action['endpoint'] ) && empty( $action['url'] ) ) {
                // Ensure endpoint follows the expected format.
                if ( ! preg_match( '#^/storeagent/v1/notifications/\{id\}/[a-z0-9_-]+$#', $action['endpoint'] ) ) {
                    return new WP_Error( 'invalid_endpoint', __( 'Invalid endpoint format', 'storeagent-ai-for-woocommerce' ) );
                }
            }

            // Validate callback if present.
            if ( ! empty( $action['callback'] ) ) {
                if ( ! $this->validate_callback( $action['callback'] ) ) {
                    return new WP_Error( 'invalid_callback', __( 'Invalid callback structure', 'storeagent-ai-for-woocommerce' ) );
                }
            }
        }

        return $actions;
    }

    /**
     * Get allowed callbacks with filter support
     *
     * @return array The allowed callbacks array
     */
    public function get_allowed_callbacks() {
        return $this->allowed_callbacks;
    }

    /**
     * Validate a callback structure
     *
     * @param array $callback The callback to validate.
     * @return bool Whether the callback is valid.
     */
    public function validate_callback( $callback ) {
        // Basic structure validation.
        if ( ! is_array( $callback ) ||
            empty( $callback['class'] ) ||
            empty( $callback['method'] ) ||
            ! isset( $callback['args'] ) ||
            ! is_array( $callback['args'] ) ) {
            return false;
        }

        // Validate class exists.
        if ( ! class_exists( $callback['class'] ) ) {
            return false;
        }

        // Check if the callback is in the whitelist.
        $method_signature  = $callback['class'] . '::' . $callback['method'];
        $allowed_callbacks = $this->get_allowed_callbacks();
        if ( ! isset( $allowed_callbacks[ $method_signature ] ) ) {
            // Not in whitelist.
            return false;
        }

        // Validate namespace - STRICT check for StoreAgent\Agents namespace ONLY.
        // Allow both SAAI\AI_Agents and StoreAgent\Agents namespaces.
        if ( strpos( $callback['class'], 'StoreAgent\\Agents\\' ) !== 0 && strpos( $callback['class'], 'SAAI\\AI_Agents\\' ) !== 0 ) {
            return false;
        }

        // Validate method exists and is callable.
        if ( ! method_exists( $callback['class'], $callback['method'] ) ) {
            return false;
        }

        // Check if method is public.
        $reflection = new \ReflectionMethod( $callback['class'], $callback['method'] );
        if ( ! $reflection->isPublic() ) {
            return false;
        }

        // Validate args - implement specific validation for known agent methods.
        $method_signature = $callback['class'] . '::' . $callback['method'];

        // Example: Add specific validation for known methods.
        switch ( $method_signature ) {
            case 'StoreAgent\Agents\ProductImprover::apply_product_description_update':
                if ( ! isset( $callback['args']['product_id'] ) || ! isset( $callback['args']['version'] ) ) {
                    return false;
                }
                $callback['args']['product_id'] = absint( $callback['args']['product_id'] );
                if ( $callback['args']['product_id'] <= 0 ) {
                    return false;
                }
                if ( ! in_array( $callback['args']['version'], array( 'suggested', 'original' ), true ) ) {
                    return false;
                }
                break;

            // Add more known methods here.

            default:
                // For unknown methods, check for known dangerous patterns in args.
                foreach ( $callback['args'] as $key => $value ) {
                    // Don't allow callbacks in args.
                    if ( is_callable( $value ) ) {
                        return false;
                    }

                    // Don't allow file paths that might be used for file operations.
                    if ( is_string( $value ) &&
                        ( strpos( $value, '../' ) !== false ||
                        strpos( $value, '/' ) === 0 ||
                        preg_match( '/[a-zA-Z]:\\\\/', $value ) ) ) {
                        return false;
                    }
                }
        }

        return true;
    }

    /**
     * Check if the agent has exceeded the rate limit for creating notifications
     *
     * @param string $agent The agent name.
     * @return bool Whether the rate limit is exceeded.
     */
    private function check_rate_limit( $agent ) {
        // Get the maximum number of notifications an agent can create per hour.
        $max_per_hour = apply_filters( 'saai_notifications_per_hour_limit', 50, $agent );

        global $wpdb;
        $table_name = $this->get_table_name();

        // Count notifications created by this agent in the last hour.
        $one_hour_ago = gmdate( 'Y-m-d H:i:s', strtotime( '-1 hour' ) );

        // Using proper query format.
        $query = 'SELECT COUNT(*) FROM ' . $table_name . ' WHERE agent = %s AND created_at > %s';
        $sql   = $wpdb->prepare( $query, $agent, $one_hour_ago ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

        $count = $wpdb->get_var( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

        return (int) $count < $max_per_hour;
    }

    /**
     * Creates a new notification
     *
     * @param string   $type           The notification type (info, warning, success, decision).
     * @param string   $title          The notification title.
     * @param string   $message        The notification message.
     * @param string   $agent          The agent creating the notification.
     * @param int|null $product_id     Optional product ID related to the notification.
     * @param array    $extra          Optional extra data (like diffs, reasons, etc).
     * @param array    $actions        Array of actions available for this notification.
     * @param bool     $hide_archive   Whether to hide the archive button for this notification.
     *
     * @return int|WP_Error The notification ID or error.
     */
    public function create( $type, $title, $message, $agent, $product_id = null, $extra = array(), $actions = array(), $hide_archive = false ) {
        // Apply filters to allow other plugins to modify the notification data.
        $notification_data = apply_filters(
            'saai_before_create_notification',
            array(
                'type'         => $type,
                'title'        => $title,
                'message'      => $message,
                'agent'        => $agent,
                'product_id'   => $product_id,
                'extra'        => $extra,
                'actions'      => $actions,
                'hide_archive' => $hide_archive,
            )
        );

        // Extract filtered data.
        extract( $notification_data ); // phpcs:ignore

        // Rate limiting check.
        if ( ! $this->check_rate_limit( $agent ) ) {
            return new WP_Error(
                'rate_limit_exceeded',
                __( 'Rate limit exceeded for notification creation', 'storeagent-ai-for-woocommerce' )
            );
        }

        // Validate type.
        if ( ! in_array( $type, array( self::TYPE_INFO, self::TYPE_WARNING, self::TYPE_SUCCESS, self::TYPE_DECISION ), true ) ) {
            return new WP_Error( 'invalid_type', __( 'Invalid notification type', 'storeagent-ai-for-woocommerce' ) );
        }

        // Never allow protected actions to be added to notifications. Return an error if it's found.
        if ( array_filter(
            $actions,
            function ( $action ) {
                return 'archive' === $action['action'];
            }
        ) ) {
            return new WP_Error( 'invalid_action', __( '"Archive" is a protected action and cannot be added to notifications', 'storeagent-ai-for-woocommerce' ) );
        }

        // Sanitize text inputs.
        $title   = sanitize_text_field( $title );
        $message = wp_kses_post( $message ); // Allow some HTML but sanitize it.
        $agent   = sanitize_text_field( $agent );

        // Validate product_id.
        if ( null !== $product_id ) {
            $product_id = absint( $product_id );
            // Check if product exists.
            if ( $product_id > 0 && function_exists( 'wc_get_product' ) && ! wc_get_product( $product_id ) ) {
                return new WP_Error( 'invalid_product', __( 'Invalid product ID', 'storeagent-ai-for-woocommerce' ) );
            }
        }

        // Validate and sanitize extra data.
        $extra = Helper::sanitize_recursive( $extra );

        // Limit the size of the extra data.
        $extra_json = wp_json_encode( $extra );
        if ( strlen( $extra_json ) > 65535 ) { // MySQL TEXT field limit.
            return new WP_Error(
                'extra_data_too_large',
                __( 'Extra data is too large', 'storeagent-ai-for-woocommerce' )
            );
        }

        // Validate actions.
        $actions = $this->validate_actions( $actions );
        if ( is_wp_error( $actions ) ) {
            return $actions;
        }

        global $wpdb;
        $table = $this->get_table_name();

        // Prepare data for insertion.
        $data = array(
            'type'         => $type,
            'title'        => $title,
            'message'      => $message,
            'agent'        => $agent,
            'product_id'   => $product_id,
            'extra'        => $extra_json,
            'actions'      => wp_json_encode( $actions ),
            'status'       => self::STATUS_PENDING,
            'archived'     => 0,
            'is_read'      => 0,
            'hide_archive' => $hide_archive ? 1 : 0,
            'created_at'   => gmdate( 'Y-m-d H:i:s' ),
            'updated_at'   => gmdate( 'Y-m-d H:i:s' ),
        );

        // Insert into database.
        $result = $wpdb->insert( $table, $data );

        if ( false === $result ) {
            $this->log(
                'Failed to create notification',
                'error',
                array(
                    'error' => $wpdb->last_error,
                    'data'  => $data,
                )
            );
            return new WP_Error( 'db_error', __( 'Failed to create notification', 'storeagent-ai-for-woocommerce' ) );
        }

        return $wpdb->insert_id;
    }

    /**
     * Creates an info notification with a simple Archive action
     *
     * @param string   $title         The notification title.
     * @param string   $message       The notification message.
     * @param string   $agent         The agent creating the notification.
     * @param int|null $product_id    Optional product ID related to the notification.
     * @param array    $extra         Optional extra data.
     * @param array    $actions       Optional custom actions.
     *
     * @return int|WP_Error The notification ID or error.
     */
    public function create_info_notification( $title, $message, $agent, $product_id = null, $extra = array(), $actions = array() ) {
        return $this->create(
            self::TYPE_INFO,
            $title,
            $message,
            $agent,
            $product_id,
            $extra,
            $actions
        );
    }

    /**
     * Creates a warning notification
     *
     * @param string   $title         The notification title.
     * @param string   $message       The notification message.
     * @param string   $agent         The agent creating the notification.
     * @param int|null $product_id    Optional product ID related to the notification.
     * @param array    $extra         Optional extra data.
     * @param array    $actions       Optional custom actions.
     *
     * @return int|WP_Error The notification ID or error.
     */
    public function create_warning_notification( $title, $message, $agent, $product_id = null, $extra = array(), $actions = array() ) {
        return $this->create(
            self::TYPE_WARNING,
            $title,
            $message,
            $agent,
            $product_id,
            $extra,
            $actions
        );
    }

    /**
     * Creates a success notification
     *
     * @param string   $title         The notification title.
     * @param string   $message       The notification message.
     * @param string   $agent         The agent creating the notification.
     * @param int|null $product_id    Optional product ID related to the notification.
     * @param array    $extra         Optional extra data.
     * @param array    $actions       Optional custom actions.
     *
     * @return int|WP_Error The notification ID or error.
     */
    public function create_success_notification( $title, $message, $agent, $product_id = null, $extra = array(), $actions = array() ) {
        return $this->create(
            self::TYPE_SUCCESS,
            $title,
            $message,
            $agent,
            $product_id,
            $extra,
            $actions
        );
    }

    /**
     * Creates a decision notification with Accept and Reject actions
     *
     * @param string   $title           The notification title.
     * @param string   $message         The notification message.
     * @param string   $agent           The agent creating the notification.
     * @param array    $accept_callback The callback to execute on accept.
     * @param array    $reject_callback The callback to execute on reject.
     * @param int|null $product_id      Optional product ID related to the notification.
     * @param array    $extra           Optional extra data (like diffs, reasons, etc).
     * @param array    $actions         Optional custom actions to override defaults.
     *
     * @return int|WP_Error The notification ID or error.
     */
    public function create_decision_notification( $title, $message, $agent, $accept_callback, $reject_callback, $product_id = null, $extra = array(), $actions = array() ) {
        // If custom actions are provided, use them.
        if ( ! empty( $actions ) ) {
            return $this->create(
                self::TYPE_DECISION,
                $title,
                $message,
                $agent,
                $product_id,
                $extra,
                $actions
            );
        }

        // Build actions array with accept/reject callbacks.
        $default_actions = array(
            array(
                'label'    => __( 'Accept', 'storeagent-ai-for-woocommerce' ),
                'action'   => 'accept',
                'endpoint' => '/storeagent/v1/notifications/{id}/accept',
                'callback' => $accept_callback,
            ),
            array(
                'label'    => __( 'Reject', 'storeagent-ai-for-woocommerce' ),
                'action'   => 'reject',
                'endpoint' => '/storeagent/v1/notifications/{id}/reject',
                'callback' => $reject_callback,
            ),
        );

        return $this->create(
            self::TYPE_DECISION,
            $title,
            $message,
            $agent,
            $product_id,
            $extra,
            $default_actions
        );
    }

    /**
     * Get a notification by ID
     *
     * @param int $id The notification ID.
     * @return object|null The notification or null if not found.
     */
    public function get( $id ) {
        global $wpdb;

        $id = absint( $id );
        if ( ! $id ) {
            return null;
        }

        // Get table name.
        $table_name = $this->get_table_name();

        // Using proper query format.
        $query = 'SELECT * FROM ' . $table_name . ' WHERE id = %d';
        $sql   = $wpdb->prepare( $query, $id ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

        $notification = $wpdb->get_row( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

        return $notification;
    }

    /**
     * Run the class
     */
    public function run() {
        // Register init_settings on init hook to ensure textdomain is loaded.
        add_action( 'init', array( $this, 'init_settings' ) );

        // Register a daily cron job to clean up old notifications.
        if ( ! wp_next_scheduled( 'saai_cleanup_notifications' ) ) {
            wp_schedule_event( time(), 'daily', 'saai_cleanup_notifications' );
        }

        add_action( 'saai_cleanup_notifications', array( $this, 'cleanup_notifications' ) );
    }

    /**
     * Initialize settings for notifications
     *
     * @return void
     */
    public function init_settings() {
        // Register the option for notification retention period.
        register_setting(
            'saai_general_options',
            'saai_notification_retention_days',
            array(
                'type'              => 'integer',
                'description'       => __( 'Number of days to keep notifications before automatic cleanup', 'storeagent-ai-for-woocommerce' ),
                'sanitize_callback' => array( $this, 'sanitize_retention_days' ),
                'default'           => 90,
            )
        );
    }

    /**
     * Sanitize the notification retention days setting
     *
     * @param mixed $value The value to sanitize.
     * @return int The sanitized value.
     */
    public function sanitize_retention_days( $value ) {
        $value = absint( $value );

        // Ensure value is between 1 and 365.
        if ( $value < 1 ) {
            $value = 1;
        } elseif ( $value > 365 ) {
            $value = 365;
        }

        return $value;
    }

    /**
     * Run cleanup of old notifications
     *
     * @return void
     */
    public function cleanup_notifications() {
        global $wpdb;

        // Get retention period from settings (default: 90 days).
        $retention_days = get_option( 'saai_notification_retention_days', 90 );

        // Calculate cutoff date.
        $cutoff_date = gmdate( 'Y-m-d H:i:s', strtotime( "-{$retention_days} days" ) );

        // Delete old notifications.
        $table_name = $this->get_table_name();

        // Using proper query format.
        $query = 'DELETE FROM ' . $table_name . ' WHERE created_at < %s';
        $sql   = $wpdb->prepare( $query, $cutoff_date ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

        $wpdb->query( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

        do_action( 'saai_notifications_cleaned_up', $retention_days );
    }

    /**
     * Get all notifications based on filters
     *
     * @param array $args Query arguments.
     * @return array The notifications.
     */
    public function get_all( $args = array() ) {
        global $wpdb;

        // Set defaults.
        $defaults = array(
            'page'        => 1,
            'per_page'    => 10,
            'archived'    => false,
            'status'      => array(),
            'agent'       => '',
            'product_id'  => null,
            'type'        => array(),
            'order'       => 'DESC',
            'orderby'     => 'created_at',
            'date_after'  => '',
            'date_before' => '',
            'date_field'  => 'created_at', // Which date field to filter by (created_at or updated_at).
        );

        $args = wp_parse_args( $args, $defaults );

        // Build query.
        $table_name    = $this->get_table_name();
        $where_clauses = array();
        $where_values  = array();

        // Special handling for archived + status
        // If status is specified and archived is true, we use the status filter instead of the archived filter
        // This allows showing accepted, rejected, and archived notifications together.
        if ( isset( $args['status'] ) && is_array( $args['status'] ) && ! empty( $args['status'] ) ) {
            // If we have a status filter, use it.
            $placeholders     = array_fill( 0, count( $args['status'] ), '%s' );
            $placeholders_str = implode( ', ', $placeholders );
            $where_clauses[]  = 'status IN (' . $placeholders_str . ')';
            $where_values     = array_merge( $where_values, $args['status'] );
        } elseif ( isset( $args['archived'] ) ) {
            // If no status filter but archived is set, use the archived filter.
            $where_clauses[] = 'archived = %d';
            $where_values[]  = (int) $args['archived'];
        }

        // Filter by type.
        if ( isset( $args['type'] ) && is_array( $args['type'] ) && ! empty( $args['type'] ) ) {
            $placeholders     = array_fill( 0, count( $args['type'] ), '%s' );
            $placeholders_str = implode( ', ', $placeholders );
            $where_clauses[]  = 'type IN (' . $placeholders_str . ')';
            $where_values     = array_merge( $where_values, $args['type'] );
        }

        // Filter by agent.
        if ( isset( $args['agent'] ) && ! empty( $args['agent'] ) ) {
            $where_clauses[] = 'agent = %s';
            $where_values[]  = $args['agent'];
        }

        // Filter by product_id.
        if ( isset( $args['product_id'] ) && ! empty( $args['product_id'] ) ) {
            $where_clauses[] = 'product_id = %d';
            $where_values[]  = $args['product_id'];
        }

        // Filter by date range.
        $date_field = in_array( $args['date_field'], array( 'created_at', 'updated_at' ), true ) ? $args['date_field'] : 'created_at';

        // Date after filter.
        if ( ! empty( $args['date_after'] ) ) {
            $date_after = sanitize_text_field( $args['date_after'] );
            if ( strtotime( $date_after ) ) {
                $where_clauses[] = "$date_field > %s";
                $where_values[]  = $date_after;
            }
        }

        // Date before filter.
        if ( ! empty( $args['date_before'] ) ) {
            $date_before = sanitize_text_field( $args['date_before'] );
            if ( strtotime( $date_before ) ) {
                $where_clauses[] = "$date_field < %s";
                $where_values[]  = $date_before;
            }
        }

        // Start building the query.
        $query = 'SELECT * FROM ' . $table_name;

        // Only add the WHERE clause if we have conditions.
        if ( ! empty( $where_clauses ) ) {
            $query .= ' WHERE 1=1 AND ' . implode( ' AND ', $where_clauses );
        }

        $query .= ' ORDER BY ' . sanitize_sql_orderby( $args['orderby'] . ' ' . $args['order'] );

        // Add pagination.
        $query     .= ' LIMIT %d OFFSET %d';
        $query_args = array_merge( $where_values, array( $args['per_page'], ( $args['page'] - 1 ) * $args['per_page'] ) );

        // Prepare and execute the query.
        $prepared_query = $wpdb->prepare( $query, $query_args ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

        $results = $wpdb->get_results( $prepared_query ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

        return $results;
    }

    /**
     * Count notifications with filters
     *
     * @param array $args Query arguments.
     * @return int Total number of notifications.
     */
    public function count_all( $args = array() ) {
        global $wpdb;

        $defaults = array(
            'archived'    => false,
            'status'      => array(),
            'agent'       => '',
            'product_id'  => null,
            'type'        => array(),
            'date_after'  => '',
            'date_before' => '',
            'date_field'  => 'created_at',
        );

        $args = wp_parse_args( $args, $defaults );

        $table_name    = $this->get_table_name();
        $where_clauses = array();
        $where_values  = array();

        // Special handling for archived + status
        // If status is specified and archived is true, we use the status filter instead of the archived filter
        // This allows showing accepted, rejected, and archived notifications together.
        if ( isset( $args['status'] ) && is_array( $args['status'] ) && ! empty( $args['status'] ) ) {
            // If we have a status filter, use it.
            $placeholders     = array_fill( 0, count( $args['status'] ), '%s' );
            $placeholders_str = implode( ', ', $placeholders );
            $where_clauses[]  = 'status IN (' . $placeholders_str . ')';
            $where_values     = array_merge( $where_values, $args['status'] );
        } elseif ( isset( $args['archived'] ) ) {
            // If no status filter but archived is set, use the archived filter.
            $where_clauses[] = 'archived = %d';
            $where_values[]  = (int) $args['archived'];
        }

        // Handle agent filter.
        if ( ! empty( $args['agent'] ) ) {
            $where_clauses[] = 'agent = %s';
            $where_values[]  = $args['agent'];
        }

        // Handle product_id filter.
        if ( ! empty( $args['product_id'] ) ) {
            $where_clauses[] = 'product_id = %d';
            $where_values[]  = absint( $args['product_id'] );
        }

        // Handle type filter.
        if ( ! empty( $args['type'] ) && is_array( $args['type'] ) ) {
            $placeholders     = array_fill( 0, count( $args['type'] ), '%s' );
            $placeholders_str = implode( ', ', $placeholders );
            $where_clauses[]  = 'type IN (' . $placeholders_str . ')';
            $where_values     = array_merge( $where_values, $args['type'] );
        }

        // Handle date filtering.
        $date_field = in_array( $args['date_field'], array( 'created_at', 'updated_at' ), true ) ? $args['date_field'] : 'created_at';

        // Date after filter.
        if ( ! empty( $args['date_after'] ) ) {
            $date_after = sanitize_text_field( $args['date_after'] );
            if ( strtotime( $date_after ) ) {
                $where_clauses[] = "$date_field > %s";
                $where_values[]  = $date_after;
            }
        }

        // Date before filter.
        if ( ! empty( $args['date_before'] ) ) {
            $date_before = sanitize_text_field( $args['date_before'] );
            if ( strtotime( $date_before ) ) {
                $where_clauses[] = "$date_field < %s";
                $where_values[]  = $date_before;
            }
        }

        // Build the complete query.
        $query = "SELECT COUNT(*) FROM $table_name";

        if ( ! empty( $where_clauses ) ) {
            $query .= ' WHERE ' . implode( ' AND ', $where_clauses );
        }

        if ( ! empty( $where_values ) ) {
            $query = $wpdb->prepare( $query, $where_values ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        }

        return (int) $wpdb->get_var( $query ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
    }

    /**
     * Get unarchived notifications
     *
     * @param array $args Query arguments.
     * @return array The notifications.
     */
    public function get_unarchived( $args = array() ) {
        $args['archived'] = 0;
        return $this->get_all( $args );
    }

    /**
     * Get archived notifications
     *
     * @param array $args Query arguments.
     * @return array The notifications.
     */
    public function get_archived( $args = array() ) {
        $args['archived'] = 1;
        return $this->get_all( $args );
    }

    /**
     * Count unarchived notifications
     *
     * @param array $args Query arguments.
     * @return int The count.
     */
    public function count_unarchived( $args = array() ) {
        $args['archived'] = 0;
        return $this->count_all( $args );
    }

    /**
     * Count archived notifications
     *
     * @param array $args Query arguments.
     * @return int The count.
     */
    public function count_archived( $args = array() ) {
        $args['archived'] = 1;
        return $this->count_all( $args );
    }

    /**
     * Update a notification
     *
     * @param int   $id   The notification ID.
     * @param array $data The data to update.
     * @return bool|WP_Error True on success, error on failure.
     */
    public function update( $id, $data ) {
        global $wpdb;

        $id = absint( $id );
        if ( ! $id ) {
            return new WP_Error( 'invalid_id', __( 'Invalid notification ID', 'storeagent-ai-for-woocommerce' ) );
        }

        // Get the notification to check if it exists.
        $notification = $this->get( $id );
        if ( ! $notification ) {
            return new WP_Error( 'not_found', __( 'Notification not found', 'storeagent-ai-for-woocommerce' ) );
        }

        // Add updated_at to the data.
        $data['updated_at'] = gmdate( 'Y-m-d H:i:s' );

        // Sanitize data.
        if ( isset( $data['title'] ) ) {
            $data['title'] = sanitize_text_field( $data['title'] );
        }

        if ( isset( $data['message'] ) ) {
            $data['message'] = wp_kses_post( $data['message'] );
        }

        if ( isset( $data['agent'] ) ) {
            $data['agent'] = sanitize_text_field( $data['agent'] );
        }

        if ( isset( $data['product_id'] ) ) {
            $data['product_id'] = absint( $data['product_id'] );
            // Check if product exists.
            if ( $data['product_id'] > 0 && function_exists( 'wc_get_product' ) && ! wc_get_product( $data['product_id'] ) ) {
                return new WP_Error( 'invalid_product', __( 'Invalid product ID', 'storeagent-ai-for-woocommerce' ) );
            }
        }

        if ( isset( $data['type'] ) ) {
            if ( ! in_array( $data['type'], array( self::TYPE_INFO, self::TYPE_WARNING, self::TYPE_SUCCESS, self::TYPE_DECISION ), true ) ) {
                return new WP_Error( 'invalid_type', __( 'Invalid notification type', 'storeagent-ai-for-woocommerce' ) );
            }
        }

        if ( isset( $data['status'] ) ) {
            if ( ! in_array( $data['status'], array( self::STATUS_PENDING, self::STATUS_ACCEPTED, self::STATUS_REJECTED, self::STATUS_ARCHIVED ), true ) ) {
                return new WP_Error( 'invalid_status', __( 'Invalid notification status', 'storeagent-ai-for-woocommerce' ) );
            }
        }

        if ( isset( $data['archived'] ) ) {
            $data['archived'] = absint( $data['archived'] ) ? 1 : 0;
        }

        if ( isset( $data['is_read'] ) ) {
            $data['is_read'] = absint( $data['is_read'] ) ? 1 : 0;
        }

        if ( isset( $data['extra'] ) ) {
            $data['extra'] = Helper::sanitize_recursive( $data['extra'] );
            $data['extra'] = wp_json_encode( $data['extra'] );
        }

        if ( isset( $data['actions'] ) ) {
            $actions = $this->validate_actions( $data['actions'] );
            if ( is_wp_error( $actions ) ) {
                return $actions;
            }
            $data['actions'] = wp_json_encode( $actions );
        }

        // Update the notification.
        $table  = $this->get_table_name();
        $result = $wpdb->update(
            $table,
            $data,
            array( 'id' => $id )
        );

        if ( false === $result ) {
            $this->log(
                'Failed to update notification',
                'error',
                array(
                    'error' => $wpdb->last_error,
                    'id'    => $id,
                    'data'  => $data,
                )
            );
            return new WP_Error( 'db_error', __( 'Failed to update notification', 'storeagent-ai-for-woocommerce' ) );
        }

        return true;
    }

    /**
     * Archive a notification
     *
     * @param int $id The notification ID.
     * @return bool|WP_Error True on success, error on failure.
     */
    public function archive( $id ) {
        return $this->update(
            $id,
            array(
                'archived' => 1,
                'status'   => self::STATUS_ARCHIVED,
            )
        );
    }

    /**
     * Unarchive a notification
     *
     * @param int $id The notification ID.
     * @return bool|WP_Error True on success, error on failure.
     */
    public function unarchive( $id ) {
        return $this->update(
            $id,
            array(
                'archived' => 0,
                'status'   => self::STATUS_PENDING,
            )
        );
    }

    /**
     * Mark a notification as read
     *
     * @param int $id The notification ID.
     * @return bool|WP_Error True on success, error on failure.
     */
    public function mark_as_read( $id ) {
        return $this->update(
            $id,
            array(
                'is_read' => 1,
            )
        );
    }

    /**
     * Mark a notification as unread
     *
     * @param int $id The notification ID.
     * @return bool|WP_Error True on success, error on failure.
     */
    public function mark_as_unread( $id ) {
        return $this->update(
            $id,
            array(
                'is_read' => 0,
            )
        );
    }

    /**
     * Delete a notification
     *
     * @param int $id The notification ID.
     * @return bool|WP_Error True on success, error on failure.
     */
    public function delete( $id ) {
        global $wpdb;

        $id = absint( $id );
        if ( ! $id ) {
            return new WP_Error( 'invalid_id', __( 'Invalid notification ID', 'storeagent-ai-for-woocommerce' ) );
        }

        // Get the notification to check if it exists.
        $notification = $this->get( $id );
        if ( ! $notification ) {
            return new WP_Error( 'not_found', __( 'Notification not found', 'storeagent-ai-for-woocommerce' ) );
        }

        // Delete the notification.
        $table  = $this->get_table_name();
        $result = $wpdb->delete(
            $table,
            array( 'id' => $id ),
            array( '%d' )
        );

        if ( false === $result ) {
            $this->log(
                'Failed to delete notification',
                'error',
                array(
                    'error' => $wpdb->last_error,
                    'id'    => $id,
                )
            );
            return new WP_Error( 'db_error', __( 'Failed to delete notification', 'storeagent-ai-for-woocommerce' ) );
        }

        return true;
    }

    /**
     * Archive multiple notifications at once
     *
     * @param array $ids The notification IDs.
     * @return int|WP_Error Number of notifications updated or error.
     */
    public function archive_batch( $ids ) {
        if ( ! is_array( $ids ) || empty( $ids ) ) {
            return new WP_Error( 'invalid_ids', __( 'Invalid notification IDs', 'storeagent-ai-for-woocommerce' ) );
        }

        global $wpdb;
        $table_name = $this->get_table_name();

        // Sanitize and validate IDs.
        $ids = array_map( 'absint', $ids );
        $ids = array_filter( $ids );

        if ( empty( $ids ) ) {
            return new WP_Error( 'invalid_ids', __( 'Invalid notification IDs', 'storeagent-ai-for-woocommerce' ) );
        }

        // Generate placeholders for the query.
        $placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );

        // Build query params with status and timestamp.
        $query_params = array(
            self::STATUS_ARCHIVED,
            gmdate( 'Y-m-d H:i:s' ),
        );

        // Add IDs to params.
        $query_params = array_merge( $query_params, $ids );

        // Build the query with table name.
        $query = 'UPDATE ' . $table_name . ' SET archived = 1, status = %s, updated_at = %s WHERE id IN (' . $placeholders . ')';

        // Execute the query.
        $result = $wpdb->query( $wpdb->prepare( $query, $query_params ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

        if ( false === $result ) {
            $this->log(
                'Failed to archive notifications batch',
                'error',
                array(
                    'error' => $wpdb->last_error,
                    'ids'   => $ids,
                )
            );
            return new WP_Error( 'db_error', __( 'Failed to archive notifications', 'storeagent-ai-for-woocommerce' ) );
        }

        return $result;
    }

    /**
     * Unarchive multiple notifications at once
     *
     * @param array $ids The notification IDs.
     * @return int|WP_Error Number of notifications updated or error.
     */
    public function unarchive_batch( $ids ) {
        if ( ! is_array( $ids ) || empty( $ids ) ) {
            return new WP_Error( 'invalid_ids', __( 'Invalid notification IDs', 'storeagent-ai-for-woocommerce' ) );
        }

        global $wpdb;
        $table_name = $this->get_table_name();

        // Sanitize and validate IDs.
        $ids = array_map( 'absint', $ids );
        $ids = array_filter( $ids );

        if ( empty( $ids ) ) {
            return new WP_Error( 'invalid_ids', __( 'Invalid notification IDs', 'storeagent-ai-for-woocommerce' ) );
        }

        // Generate placeholders for the query.
        $placeholders = implode( ',', array_fill( 0, count( $ids ), '%d' ) );

        // Build query params with status and timestamp.
        $query_params = array(
            self::STATUS_PENDING,
            gmdate( 'Y-m-d H:i:s' ),
        );

        // Add IDs to params.
        $query_params = array_merge( $query_params, $ids );

        // Build the query with table name.
        $query = 'UPDATE ' . $table_name . ' SET archived = 0, status = %s, updated_at = %s WHERE id IN (' . $placeholders . ')';

        // Execute the query.
        $result = $wpdb->query( $wpdb->prepare( $query, $query_params ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

        if ( false === $result ) {
            $this->log(
                'Failed to unarchive notifications batch',
                'error',
                array(
                    'error' => $wpdb->last_error,
                    'ids'   => $ids,
                )
            );
            return new WP_Error( 'db_error', __( 'Failed to unarchive notifications', 'storeagent-ai-for-woocommerce' ) );
        }

        return $result;
    }

    /**
     * Execute a callback for a notification action
     *
     * @param int    $notification_id The notification ID.
     * @param string $action The action (accept, reject).
     * @return mixed|WP_Error The result of the callback or error.
     */
    public function execute_callback( $notification_id, $action ) {
        // Get notification from database.
        $notification = $this->get( $notification_id );

        if ( ! $notification ) {
            return new WP_Error( 'notification_not_found', __( 'Notification not found', 'storeagent-ai-for-woocommerce' ) );
        }

        // Check if the notification is already accepted or rejected.
        if ( self::STATUS_ACCEPTED === $notification->status && 'accept' === $action ) {
            return new WP_Error(
                'already_accepted',
                __( 'This notification has already been accepted', 'storeagent-ai-for-woocommerce' )
            );
        }

        if ( self::STATUS_REJECTED === $notification->status && 'reject' === $action ) {
            return new WP_Error(
                'already_rejected',
                __( 'This notification has already been rejected', 'storeagent-ai-for-woocommerce' )
            );
        }

        // Only allow pending notifications to be accepted/rejected.
        if ( self::STATUS_PENDING !== $notification->status ) {
            return new WP_Error(
                'invalid_status',
                __( 'Only pending notifications can be accepted or rejected', 'storeagent-ai-for-woocommerce' )
            );
        }

        // Allow filtering of callback execution.
        $pre = apply_filters( 'saai_pre_execute_notification_callback', null, $notification_id, $action );
        if ( null !== $pre ) {
            return $pre; // Allow complete override of callback execution.
        }

        // Security: Check current user has permission to execute callbacks.
        if ( ! current_user_can( 'manage_woocommerce' ) ) {
            return new WP_Error( 'permission_denied', __( 'You do not have permission to perform this action', 'storeagent-ai-for-woocommerce' ) );
        }

        // Find the requested action.
        $actions     = json_decode( $notification->actions, true );
        $action_data = null;

        foreach ( $actions as $a ) {
            if ( $a['action'] === $action ) {
                $action_data = $a;
                break;
            }
        }

        if ( ! $action_data || empty( $action_data['callback'] ) ) {
            return new WP_Error( 'action_not_found', __( 'Action not found or has no callback', 'storeagent-ai-for-woocommerce' ) );
        }

        // Validate callback.
        $callback = $action_data['callback'];

        if ( ! $this->validate_callback( $callback ) ) {
            return new WP_Error( 'invalid_callback', __( 'Invalid callback', 'storeagent-ai-for-woocommerce' ) );
        }

        // Execute callback in a try-catch block.
        try {
            // Log the callback execution for audit purposes.
            $this->log(
                sprintf(
                    'Executing callback %s::%s for notification #%d with action "%s"',
                    $callback['class'],
                    $callback['method'],
                    $notification_id,
                    $action
                ),
                'info',
                array(
                    'notification_id' => $notification_id,
                    'action'          => $action,
                    'callback'        => $callback,
                )
            );

            // Check if method is static or not.
            $reflection = new \ReflectionMethod( $callback['class'], $callback['method'] );

            if ( $reflection->isStatic() ) {
                // Use static method calling format for static methods.
                $result = call_user_func_array(
                    array( $callback['class'], $callback['method'] ),
                    array( $callback['args'] ) // Pass args as a single array parameter.
                );
            } else {
                // Use instance method calling for non-static methods.
                $result = call_user_func_array(
                    array( new $callback['class'](), $callback['method'] ),
                    array( $callback['args'] ) // Pass args as a single array parameter.
                );
            }

            // Update notification status based on action.
            $this->update(
                $notification_id,
                array(
                    'status'     => 'accept' === $action ? self::STATUS_ACCEPTED : self::STATUS_REJECTED,
                    'updated_at' => gmdate( 'Y-m-d H:i:s' ),
                )
            );

            return $result;
        } catch ( \Exception $e ) {
            // Log the error.
            $this->log(
                sprintf(
                    'Error executing callback for notification #%d: %s',
                    $notification_id,
                    $e->getMessage()
                ),
                'error',
                array(
                    'notification_id' => $notification_id,
                    'action'          => $action,
                    'callback'        => $callback,
                    'error'           => $e->getMessage(),
                )
            );

            return new WP_Error( 'callback_execution_error', $e->getMessage() );
        }
    }

    /**
     * Accept a notification and execute its callback
     *
     * @param int $id The notification ID.
     * @return mixed|WP_Error The result of the callback or error.
     */
    public function accept( $id ) {
        return $this->execute_callback( $id, 'accept' );
    }

    /**
     * Reject a notification and execute its callback
     *
     * @param int $id The notification ID.
     * @return mixed|WP_Error The result of the callback or error.
     */
    public function reject( $id ) {
        return $this->execute_callback( $id, 'reject' );
    }
}
