<?php
/**
 * Database Optimizer for Accessibility Audio TTS
 *
 * Provides database query optimization, indexing suggestions,
 * and query performance monitoring for the plugin.
 *
 * @package Accessibility_Audio_TTS
 * @subpackage Core\Utilities
 * @since 2.0.0
 * @author App4You.dev
 */

namespace Accessibility_Audio_TTS\Core\Utilities;

use Accessibility_Audio_TTS\Core\Cache\Cache_Manager;
if ( ! defined( 'ABSPATH' ) ) {
	exit; }

/**
 * Database optimizer for plugin queries.
 *
 * Optimizes database queries, provides indexing recommendations,
 * and monitors query performance to improve plugin efficiency.
 *
 * @since 2.0.0
 */
class Database_Optimizer {

    /**
     * Cache manager instance
     *
     * @var Cache_Manager.
     */
    private Cache_Manager $cache_manager;

    /**
     * Query statistics
     *
     * @var array.
     */
    private array $query_stats = array(
        'total_queries'     => 0,
        'optimized_queries' => 0,
        'cached_queries'    => 0,
        'slow_queries'      => 0,
        'total_time'        => 0,
    );

    /**
     * Slow query threshold (seconds)
     *
     * @var float.
     */
    private float $slow_query_threshold = 0.5;

    /**
     * Query cache TTL
     *
     * @var int.
     */
    private int $cache_ttl = 3600;

    /**
     * Constructor.
     *
     * @param Cache_Manager|null $cache_manager  Cache manager instance or null.
     */
    public function __construct( ?Cache_Manager $cache_manager = null ) {
        $this->cache_manager = $cache_manager ?? new Cache_Manager();
        $this->init_hooks();
    }

    /**
     * Initialize database optimization hooks.
     *
     * @return void.
     */
    private function init_hooks(): void {
        // Query optimization hooks.
        add_filter( 'posts_pre_query', array( $this, 'optimize_posts_query' ), 10, 2 );
        add_filter( 'pre_get_posts', array( $this, 'optimize_post_meta_queries' ) );

        // Query monitoring.
        if ( defined( 'WP_DEBUG' ) && WP_DEBUG ) {
            add_action( 'shutdown', array( $this, 'log_query_stats' ) );
        }

        // Database maintenance.
        add_action( 'wp_scheduled_delete', array( $this, 'cleanup_old_data' ) );
    }

    /**
     * Optimize posts query with caching and query improvements.
     *
     * @param array|null $posts   Posts array or null.
     * @param \WP_Query  $query   Query instance.
     * @return array|null         Posts or null.
     */
    public function optimize_posts_query( $posts, \WP_Query $query ): ?array {
        if ( ! $this->is_plugin_query( $query ) ) {
            return $posts;
        }

        $start_time = microtime( true );
        $cache_key  = $this->generate_query_cache_key( $query );

        // Try cache first.
        $cached_result = $this->cache_manager->get(
            $cache_key,
            null,
            $this->cache_ttl,
            'posts'
        );

        if ( false !== $cached_result ) {
            ++$this->query_stats['cached_queries'];
            accessibility_audio_tts_debug_log( "Database query cache hit: {$cache_key}" );
            return $cached_result;
        }

        // Let WordPress handle the query but monitor performance.
        add_action(
            'posts_selection',
            function ( $selection ) use ( $query, $cache_key, $start_time ) {
                $end_time   = microtime( true );
                $query_time = $end_time - $start_time;

                ++$this->query_stats['total_queries'];
                $this->query_stats['total_time'] += $query_time;

                if ( $query_time > $this->slow_query_threshold ) {
                    ++$this->query_stats['slow_queries'];
                    accessibility_audio_tts_debug_log( "Slow query detected: {$query_time}s - {$cache_key}" );
                }

                // Cache the result.
                $this->cache_manager->set(
                    $cache_key,
                    $selection,
                    $this->cache_ttl,
                    'posts'
                );
            }
        );

        return $posts;
    }

    /**
     * Optimize post meta queries.
     *
     * @param \WP_Query $query Query instance.
     * @return void.
     */
    public function optimize_post_meta_queries( \WP_Query $query ): void {
        if ( ! $this->is_plugin_query( $query ) ) {
            return;
        }

        $meta_query = $query->get( 'meta_query' );
        if ( empty( $meta_query ) ) {
            return;
        }

        $optimized_meta_query = $this->optimize_meta_query_structure( $meta_query );

        if ( $optimized_meta_query !== $meta_query ) {
            $query->set( 'meta_query', $optimized_meta_query );
            ++$this->query_stats['optimized_queries'];
            accessibility_audio_tts_debug_log( 'Optimized meta query structure.' );
        }
    }

    /**
     * Optimize meta query structure.
     *
     * @param array $meta_query  Meta query array.
     * @return array             Optimized meta query.
     */
    private function optimize_meta_query_structure( array $meta_query ): array {
        foreach ( $meta_query as &$clause ) {
            if ( ! is_array( $clause ) || ! isset( $clause['key'] ) ) {
                continue;
            }

            // Optimize comparison operators.
            if ( ! isset( $clause['compare'] ) ) {
                $clause['compare'] = '=';
            }

            // Use EXISTS for checking meta key existence.
            if ( '=' === $clause['compare'] && empty( $clause['value'] ) ) {
                $clause['compare'] = 'EXISTS';
                unset( $clause['value'] );
            }

            // Optimize LIKE queries.
            if ( 'LIKE' === $clause['compare'] && isset( $clause['value'] ) ) {
                // Add wildcards if not present.
                if ( false === strpos( $clause['value'], '%' ) ) {
                    $clause['value'] = '%' . $clause['value'] . '%';
                }
            }

            // Set appropriate data type.
            if ( ! isset( $clause['type'] ) && isset( $clause['value'] ) ) {
                if ( is_numeric( $clause['value'] ) ) {
                    $clause['type'] = is_int( $clause['value'] ) ? 'NUMERIC' : 'DECIMAL';
                } else {
                    $clause['type'] = 'CHAR';
                }
            }
        }

        return $meta_query;
    }

    /**
     * Generate cache key for query.
     *
     * @param \WP_Query $query Query instance.
     * @return string          Cache key.
     */
    private function generate_query_cache_key( \WP_Query $query ): string {
        $key_parts = array(
            'post_type'      => $query->get( 'post_type' ),
            'meta_query'     => $query->get( 'meta_query' ), // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_query -- used for cache key only.
            'posts_per_page' => $query->get( 'posts_per_page' ),
            'paged'          => $query->get( 'paged' ),
            'orderby'        => $query->get( 'orderby' ),
            'order'          => $query->get( 'order' ),
            'post_status'    => $query->get( 'post_status' ),
        );

        $encoded_key_parts = wp_json_encode( $key_parts );
        if ( false === $encoded_key_parts ) {
            $encoded_key_parts = maybe_serialize( $key_parts );
        }
        return 'db_query_' . md5( $encoded_key_parts );
    }

    /**
     * Check if query is related to our plugin.
     *
     * @param \WP_Query $query Query instance.
     * @return bool            True if plugin-related query.
     */
    private function is_plugin_query( \WP_Query $query ): bool {
        $meta_query = $query->get( 'meta_query' );

        if ( empty( $meta_query ) ) {
            return false;
        }

        foreach ( $meta_query as $clause ) {
            if ( is_array( $clause ) && isset( $clause['key'] ) ) {
                if ( 0 === strpos( $clause['key'], 'accessibility_audio_tts_' ) ) {
                    return true;
                }
            }
        }

        return false;
    }

    /**
     * Get optimized posts with audio content.
     *
     * @param array $args Query arguments.
     * @return array       Posts with audio.
     */
    public function get_posts_with_audio( array $args = array() ): array {
        $encoded_args = wp_json_encode( $args );
        if ( false === $encoded_args ) {
            $encoded_args = maybe_serialize( $args );
        }
        $cache_key = 'posts_with_audio_' . md5( $encoded_args );

        return $this->cache_manager->get(
            $cache_key,
            function () use ( $args ) {
                $default_args = array(
                    'post_type'      => 'post',
                    'post_status'    => 'publish',
                    'posts_per_page' => -1,
                    'fields'         => 'ids',
                    // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_query -- required to find posts with plugin-specific postmeta.
                    'meta_query'     => array(
                        array(
                            'key'     => 'accessibility_audio_tts_file',
                            'compare' => 'EXISTS',
                        ),
                    ),
                );

                $query_args = array_merge( $default_args, $args );

                return get_posts( $query_args );
            },
            $this->cache_ttl,
            'posts'
        );
    }

    /**
     * Get audio statistics with caching.
     *
     * @return array Audio statistics.
     */
    public function get_audio_statistics(): array {
        return $this->cache_manager->get(
            'audio_statistics',
            function () {
                $stats = array();

                // Total posts with audio.
                $query                           = new \WP_Query(
                    array(
                        'post_type'              => 'any',
                        'post_status'            => 'publish',
                        'meta_query'             => array( // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_query -- Required for counting posts with audio files.
                            array(
                                'key'     => 'accessibility_audio_tts_file',
                                'compare' => 'EXISTS',
                            ),
                        ),
                        'fields'                 => 'ids',
                        'posts_per_page'         => 1,
                        'no_found_rows'          => false,
                        'update_post_meta_cache' => false,
                        'update_post_term_cache' => false,
                    )
                );
                $stats['total_posts_with_audio'] = (int) $query->found_posts;

                // Total audio files (posts with non-empty audio file meta).
                $query                      = new \WP_Query(
                    array(
                        'post_type'              => 'any',
                        'post_status'            => 'publish',
                        'meta_query'             => array( // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_query -- Required for counting posts with non-empty audio files.
                            array(
                                'key'     => 'accessibility_audio_tts_file',
                                'value'   => '',
                                'compare' => '!=',
                            ),
                        ),
                        'fields'                 => 'ids',
                        'posts_per_page'         => 1,
                        'no_found_rows'          => false,
                        'update_post_meta_cache' => false,
                        'update_post_term_cache' => false,
                    )
                );
                $stats['total_audio_files'] = (int) $query->found_posts;

                // Audio by post type.
                $post_types   = get_post_types( array( 'public' => true ) );
                $by_post_type = array();
                foreach ( array_keys( $post_types ) as $post_type ) {
                    $query = new \WP_Query(
                        array(
                            'post_type'              => $post_type,
                            'post_status'            => 'publish',
                            'meta_query'             => array( // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_query -- Required for counting audio by post type.
                                array(
                                    'key'     => 'accessibility_audio_tts_file',
                                    'compare' => 'EXISTS',
                                ),
                            ),
                            'fields'                 => 'ids',
                            'posts_per_page'         => 1,
                            'no_found_rows'          => false,
                            'update_post_meta_cache' => false,
                            'update_post_term_cache' => false,
                        )
                    );
                    $count = (int) $query->found_posts;
                    if ( $count > 0 ) {
                        $by_post_type[] = array(
                            'post_type' => $post_type,
                            'count'     => $count,
                        );
                    }
                }
                $stats['by_post_type'] = $by_post_type;

                return $stats;
            },
            $this->cache_ttl,
            'statistics'
        );
    }

    /**
     * Cleanup old data.
     *
     * @return void.
     */
    public function cleanup_old_data(): void {
        global $wpdb;
        // Clean up orphaned meta data.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Maintenance cleanup query.
        $orphaned_meta = $wpdb->query(
            "DELETE pm FROM {$wpdb->postmeta} pm\n\t\t LEFT JOIN {$wpdb->posts} p ON pm.post_id = p.ID\n\t\t WHERE p.ID IS NULL\n\t\t AND pm.meta_key LIKE 'accessibility_audio_tts_%'"
        );
        if ( $orphaned_meta > 0 ) {
            accessibility_audio_tts_debug_log( "Cleaned up {$orphaned_meta} orphaned meta entries." );
        }
        // Clean up old transients.
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Maintenance cleanup query.
        $old_transients = $wpdb->query(
            "DELETE FROM {$wpdb->options}\n\t\t WHERE option_name LIKE '_transient_accessibility_audio_tts_%'\n\t\t AND option_name NOT LIKE '_transient_timeout_%'"
        );
        if ( $old_transients > 0 ) {
            accessibility_audio_tts_debug_log( "Cleaned up {$old_transients} old transients." );
        }
    }

    /**
     * Get database optimization recommendations.
     *
     * @return array Recommendations.
     */
    public function get_optimization_recommendations(): array {
        global $wpdb;
        $recommendations = array();
        // Check for missing indexes.
        $missing_indexes = $this->check_missing_indexes();
        if ( ! empty( $missing_indexes ) ) {
            $recommendations[] = array(
                'type'     => 'index',
                'severity' => 'high',
                'message'  => 'Missing database indexes detected',
                'details'  => $missing_indexes,
                'action'   => 'add_indexes',
            );
        }
        // Check query performance.
        if ( $this->query_stats['slow_queries'] > 0 ) {
            $slow_ratio = ( $this->query_stats['slow_queries'] / $this->query_stats['total_queries'] ) * 100;
            if ( $slow_ratio > 10 ) {
                $recommendations[] = array(
                    'type'     => 'performance',
                    'severity' => 'medium',
                    'message'  => "High percentage of slow queries ({$slow_ratio}%)",
                    'action'   => 'optimize_queries',
                );
            }
        }
        // Check cache efficiency.
        if ( $this->query_stats['total_queries'] > 0 ) {
            $cache_ratio = ( $this->query_stats['cached_queries'] / $this->query_stats['total_queries'] ) * 100;
            if ( $cache_ratio < 50 ) {
                $recommendations[] = array(
                    'type'     => 'cache',
                    'severity' => 'low',
                    'message'  => "Low query cache hit ratio ({$cache_ratio}%)",
                    'action'   => 'increase_cache_ttl',
                );
            }
        }
        return $recommendations;
    }

    /**
     * Check for missing database indexes.
     *
     * @return array Missing indexes.
     */
    private function check_missing_indexes(): array {
        global $wpdb;

        $missing = array();

        // Check postmeta indexes for our plugin.
        $meta_keys = array(
            'accessibility_audio_tts_file',
            'accessibility_audio_tts_status',
            'accessibility_audio_tts_generated_at',
        );

        foreach ( $meta_keys as $meta_key ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Metadata inspection, advisory only.
            $index_exists = $wpdb->get_var(
                $wpdb->prepare(
                    "SHOW INDEX FROM {$wpdb->postmeta} WHERE Column_name = 'meta_key' AND Key_name LIKE %s",
                    '%' . $meta_key . '%'
                )
            );

            if ( ! $index_exists ) {
                $missing[] = array(
                    'table'      => $wpdb->postmeta,
                    'column'     => 'meta_key',
                    'value'      => $meta_key,
                    'suggestion' => "CREATE INDEX idx_accessibility_audio_tts_{$meta_key} ON {$wpdb->postmeta} (meta_key, meta_value(20))",
                );
            }
        }

        return $missing;
    }

    /**
     * Log query statistics.
     *
     * @return void.
     */
    public function log_query_stats(): void {
        if ( $this->query_stats['total_queries'] > 0 ) {
            $avg_time = $this->query_stats['total_time'] / $this->query_stats['total_queries'];
            accessibility_audio_tts_debug_log(
                sprintf(
                    'Database Stats - Queries: %d, Optimized: %d, Cached: %d, Slow: %d, Avg Time: %.3fs',
                    $this->query_stats['total_queries'],
                    $this->query_stats['optimized_queries'],
                    $this->query_stats['cached_queries'],
                    $this->query_stats['slow_queries'],
                    $avg_time
                )
            );
        }
    }

    /**
     * Get query statistics.
     *
     * @return array Statistics.
     */
    public function get_stats(): array {
        $stats = $this->query_stats;
        if ( $stats['total_queries'] > 0 ) {
            $stats['avg_query_time']     = $stats['total_time'] / $stats['total_queries'];
            $stats['cache_hit_ratio']    = ( $stats['cached_queries'] / $stats['total_queries'] ) * 100;
            $stats['optimization_ratio'] = ( $stats['optimized_queries'] / $stats['total_queries'] ) * 100;
        } else {
            $stats['avg_query_time']     = 0;
            $stats['cache_hit_ratio']    = 0;
            $stats['optimization_ratio'] = 0;
        }
        return $stats;
    }

    /**
     * Reset statistics.
     *
     * @return void.
     */
    public function reset_stats(): void {
        $this->query_stats = array(
            'total_queries'     => 0,
            'optimized_queries' => 0,
            'cached_queries'    => 0,
            'slow_queries'      => 0,
            'total_time'        => 0,
        );
    }

    /**
     * Set slow query threshold.
     *
     * @param float $threshold Threshold in seconds.
     * @return void.
     */
    public function set_slow_query_threshold( float $threshold ): void {
        $this->slow_query_threshold = max( 0.1, $threshold );
    }

    /**
     * Set cache TTL.
     *
     * @param int $ttl TTL in seconds.
     * @return void.
     */
    public function set_cache_ttl( int $ttl ): void {
        $this->cache_ttl = max( 300, $ttl ); // Minimum 5 minutes.
    }
}