<?php
/**
 * Database Optimizer - Performance Optimization via Indexing
 *
 * This class manages database performance optimization by creating and
 * maintaining appropriate indexes on plugin tables and WordPress core tables.
 * Proper indexing dramatically improves query performance for:
 *
 * - API usage queries (provider, user, timestamp)
 * - Image usage tracking (image_id, post_id)
 * - Content searches (full-text search capability)
 * - Changelog lookups (image_id, timestamp)
 *
 * Optimization Strategy:
 * - Compound indexes for multi-column WHERE clauses
 * - Timestamp indexes for date range queries
 * - Foreign key indexes for JOIN operations
 * - Full-text indexes for content search (MySQL 5.6+)
 *
 * Safety Features:
 * - Checks if indexes already exist before creating
 * - Verifies table existence before optimization
 * - MySQL version detection for feature compatibility
 * - Non-blocking index creation (doesn't fail silently)
 *
 * Performance Impact:
 * - Read operations: 10-100x faster with proper indexes
 * - Write operations: Slight overhead (acceptable trade-off)
 * - Disk space: Minimal increase (indexes are small)
 *
 * @package   IPAIS\Database
 * @since 1.0.0
 * @author    InfyPress
 * @license   GPL-2.0-or-later
 */

namespace IPAIS\Database;

// Exit if accessed directly.
if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

/**
 * Database Performance Optimizer Class
 *
 * Manages database optimization through strategic index creation and
 * table structure improvements. Called during plugin activation and upgrades.
 *
 * Optimized Tables:
 * 1. infyp_api_usage:
 *    - idx_provider_timestamp: Query by provider and date
 *    - idx_user_timestamp: Query by user and date
 *    - idx_timestamp: Date range queries
 *
 * 2. infyp_image_usage (created):
 *    - Tracks image->post relationships
 *    - Speeds up "where is this image used" queries
 *
 * 3. wp_posts (optional):
 *    - Full-text index on post_content
 *    - Enables fast content searching
 *    - Currently disabled (requires background job)
 *
 * When To Run:
 * - Plugin activation
 * - Plugin upgrade (version changes)
 * - Manual trigger from Debug page
 *
 * @package   IPAIS\Database
 * @since 1.0.0
 *
 * @see       register_activation_hook() WordPress activation hooks
 */
class INFYP_Database_Optimizer {

    /**
     * Optimize database tables by adding performance indexes
     *
     * Creates compound indexes on API usage table for common query patterns.
     * Checks for existing indexes before creating to avoid errors.
     *
     * Index Strategy:
     * - Compound indexes for multi-column WHERE clauses
     * - Timestamp indexes for date range filtering
     * - Provider indexes for usage by AI service
     *
     * @since 1.0.0
     *
     * @global wpdb $wpdb WordPress database object.
     *
     * @return void
     */
    public static function optimize_tables() {
        global $wpdb;
        
        $charset_collate = $wpdb->get_charset_collate();
        
        // API usage table optimization
        $api_usage_table = $wpdb->prefix . 'infyp_api_usage';
        
        // Check if table exists before adding indexes
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Schema check, no caching needed
        $table_exists = $wpdb->get_var(
            $wpdb->prepare("SHOW TABLES LIKE %s", $api_usage_table)
        );
        
        if ( $table_exists ) {
            // Add compound indexes for common queries
            $indexes = [
                'idx_provider_timestamp' => "CREATE INDEX idx_provider_timestamp ON `{$api_usage_table}` (provider, timestamp)",
                'idx_user_timestamp' => "CREATE INDEX idx_user_timestamp ON `{$api_usage_table}` (user_id, timestamp)",
                'idx_timestamp' => "CREATE INDEX idx_timestamp ON `{$api_usage_table}` (timestamp)"
            ];
            
            foreach ( $indexes as $index_name => $index_sql ) {
                // Check if index already exists
                // Table name is safe, comes from $wpdb->prefix
                // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter
                $index_exists = $wpdb->get_results(
                    $wpdb->prepare(
                        "SHOW INDEX FROM `{$api_usage_table}` WHERE Key_name = %s",
                        $index_name
                    )
                );
                // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter

                if ( empty( $index_exists ) ) {
                    // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter -- CREATE INDEX operation, table name from $wpdb->prefix is safe
                    $wpdb->query( $index_sql );
                }
            }
        }
        
        // Add full-text search capability for post content (if MySQL version supports it)
        $mysql_version = $wpdb->db_version();
        if ( version_compare( $mysql_version, '5.6', '>=' ) ) {
            // Check if fulltext index exists
            // Table name is safe - uses $wpdb->posts (core WordPress table)
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Schema check, table name validated
            $fulltext_exists = $wpdb->get_results(
                "SHOW INDEX FROM `{$wpdb->posts}` WHERE Key_name = 'infyp_content_fulltext'"
            );
            
            if ( empty( $fulltext_exists ) ) {
                // Note: This should be done carefully and may require significant time on large databases
                // Consider running during plugin activation or as a background job
                // $wpdb->query("ALTER TABLE {$wpdb->posts} ADD FULLTEXT infyp_content_fulltext (post_content)");
            }
        }
    }
    
    /**
     * Create the image usage tracking table.
     *
     * Stores image-to-post relationships for fast "where is this image used"
     * queries. Includes unique constraint on image_id, post_id, and usage_type.
     *
     * @since 1.0.0
     *
     * @global wpdb $wpdb WordPress database object.
     *
     * @return void
     */
    public static function create_image_usage_table() {
        global $wpdb;
        
        $table_name = $wpdb->prefix . 'infyp_image_usage';
        $charset_collate = $wpdb->get_charset_collate();
        
        $sql = "CREATE TABLE $table_name (
            id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            image_id bigint(20) UNSIGNED NOT NULL,
            post_id bigint(20) UNSIGNED NOT NULL,
            usage_type varchar(50) NOT NULL DEFAULT 'content',
            position int(11) DEFAULT 0,
            created_at datetime DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY unique_usage (image_id, post_id, usage_type),
            KEY idx_image (image_id),
            KEY idx_post (post_id),
            KEY idx_type (usage_type)
        ) $charset_collate;";
        
        if ( ! function_exists( 'dbDelta' ) ) {
            require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
        }
        dbDelta( $sql );
    }

    /**
     * Run all database optimizations.
     *
     * Creates tables, adds indexes, and auto-populates lookup and search
     * index tables when they have not been populated yet.
     *
     * @since 1.0.0
     *
     * @return void
     */
    public static function run_all_optimizations() {
        self::optimize_tables();
        self::create_image_usage_table();
        self::create_image_post_type_lookup_table();
        self::create_search_index_table();

        // Auto-populate indexes so search and filtering work immediately
        // Uses single INSERT...SELECT queries so this is fast even for large libraries
        if ( ! get_option( 'infyp_lookup_table_populated' ) ) {
            self::populate_image_post_type_lookup();
        }
        if ( ! get_option( 'infyp_search_index_populated' ) ) {
            self::populate_search_index();
        }
    }

    /**
     * Creates image-post-type lookup table for fast filtering
     *
     * This denormalized table pre-computes image-to-post-type relationships,
     * replacing expensive subqueries with fast indexed JOINs.
     *
     * Performance improvement: 10-100x faster post type filtering
     *
     * @since 1.0.0
     *
     * @return void
     */
    public static function create_image_post_type_lookup_table() {
        global $wpdb;

        $table_name = $wpdb->prefix . 'infyp_image_post_types';
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE $table_name (
            image_id bigint(20) UNSIGNED NOT NULL,
            post_type varchar(50) NOT NULL,
            relationship_type varchar(20) NOT NULL DEFAULT 'attached',
            PRIMARY KEY (image_id, post_type, relationship_type),
            KEY idx_post_type (post_type),
            KEY idx_image_id (image_id)
        ) $charset_collate;";

        if ( ! function_exists( 'dbDelta' ) ) {
            require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        }
        dbDelta( $sql );
    }

    /**
     * Populates the image-post-type lookup table
     *
     * Processes images in batches to avoid memory issues on large sites.
     * Handles three relationship types:
     * - attached: Image attached to post via post_parent
     * - featured: Image used as featured image (_thumbnail_id)
     * - unattached: Image not associated with any post (media library only)
     *
     * @since 1.0.0
     *
     * @param bool $truncate Whether to clear the table before populating. Default true.
     * @return array Statistics about the population process.
     */
    public static function populate_image_post_type_lookup( $truncate = true ) {
        global $wpdb;

        $table_name = $wpdb->prefix . 'infyp_image_post_types';
        $stats = array(
            'attached'   => 0,
            'featured'   => 0,
            'unattached' => 0,
            'total'      => 0,
        );

        // Check if table exists
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Schema check
        $table_exists = $wpdb->get_var(
            $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name )
        );

        if ( ! $table_exists ) {
            self::create_image_post_type_lookup_table();
        }

        if ( $truncate ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Truncate operation, table name from $wpdb->prefix is safe
            $wpdb->query( "TRUNCATE TABLE `{$table_name}`" );
        }

        // Insert attached images (by post_parent)
        // phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Bulk insert for performance, table names from $wpdb->prefix are safe
        $attached = $wpdb->query( "
            INSERT IGNORE INTO `{$table_name}` (image_id, post_type, relationship_type)
            SELECT DISTINCT
                a.ID,
                p.post_type,
                'attached'
            FROM {$wpdb->posts} a
            INNER JOIN {$wpdb->posts} p ON a.post_parent = p.ID
            WHERE a.post_type = 'attachment'
            AND a.post_mime_type LIKE 'image/%'
            AND a.post_status = 'inherit'
            AND p.post_status IN ('publish', 'draft', 'private', 'pending', 'future')
        " );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $stats['attached'] = $attached !== false ? $attached : 0;

        // Insert featured images (by _thumbnail_id)
        // phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Bulk insert for performance, table names from $wpdb->prefix are safe
        $featured = $wpdb->query( "
            INSERT IGNORE INTO `{$table_name}` (image_id, post_type, relationship_type)
            SELECT DISTINCT
                CAST(pm.meta_value AS UNSIGNED),
                p.post_type,
                'featured'
            FROM {$wpdb->postmeta} pm
            INNER JOIN {$wpdb->posts} p ON pm.post_id = p.ID
            INNER JOIN {$wpdb->posts} a ON CAST(pm.meta_value AS UNSIGNED) = a.ID
            WHERE pm.meta_key = '_thumbnail_id'
            AND pm.meta_value REGEXP '^[0-9]+$'
            AND a.post_type = 'attachment'
            AND a.post_mime_type LIKE 'image/%'
            AND p.post_status IN ('publish', 'draft', 'private', 'pending', 'future')
        " );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $stats['featured'] = $featured !== false ? $featured : 0;

        // Insert unattached images as 'media' type
        // Uses LEFT JOIN anti-pattern instead of NOT IN subquery for better performance at scale
        // phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Bulk insert for performance, table names from $wpdb->prefix are safe
        $unattached = $wpdb->query( "
            INSERT IGNORE INTO `{$table_name}` (image_id, post_type, relationship_type)
            SELECT DISTINCT
                a.ID,
                'media',
                'unattached'
            FROM {$wpdb->posts} a
            LEFT JOIN {$wpdb->postmeta} pm_thumb
                ON CAST(pm_thumb.meta_value AS UNSIGNED) = a.ID
                AND pm_thumb.meta_key = '_thumbnail_id'
                AND pm_thumb.meta_value REGEXP '^[0-9]+$'
            WHERE a.post_type = 'attachment'
            AND a.post_mime_type LIKE 'image/%'
            AND a.post_status = 'inherit'
            AND a.post_parent = 0
            AND pm_thumb.meta_id IS NULL
        " );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $stats['unattached'] = $unattached !== false ? $unattached : 0;

        // Allow extensions to populate additional image relationships (e.g. WooCommerce galleries, taxonomy thumbnails)
        $stats = apply_filters( 'infyp_populate_lookup_table', $stats, $table_name, $wpdb );

        $stats['total'] = array_sum( array_filter( $stats, 'is_int' ) );

        // Update last populated timestamp
        update_option( 'infyp_lookup_table_populated', time() );

        return $stats;
    }

    /**
     * Creates search index table for fast full-text search with weighted relevance
     *
     * Stores searchable fields in separate columns with individual FULLTEXT indexes
     * to enable weighted relevance scoring (filename matches rank higher than title, etc.)
     *
     * Schema v2 - Separate columns for weighted search:
     * - filename: highest weight (exact/partial filename matches)
     * - title: medium-high weight
     * - alt_text: medium weight
     * - combined_text: fallback for broad search (includes post_excerpt + post_content)
     *
     * @since 1.0.0
     * @since 1.0.0 Restructured with separate columns for weighted relevance
     *
     * @return void
     */
    public static function create_search_index_table() {
        global $wpdb;

        // Check MySQL version (FULLTEXT on InnoDB requires 5.6+)
        $mysql_version = $wpdb->db_version();
        if ( version_compare( $mysql_version, '5.6', '<' ) ) {
            return;
        }

        $table_name = $wpdb->prefix . 'infyp_search_index';
        $charset_collate = $wpdb->get_charset_collate();

        // Schema v2: Separate columns for weighted search relevance
        $sql = "CREATE TABLE $table_name (
            image_id bigint(20) UNSIGNED NOT NULL PRIMARY KEY,
            filename varchar(255) DEFAULT '' NOT NULL,
            title text NOT NULL,
            alt_text text NOT NULL,
            combined_text text NOT NULL,
            updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            FULLTEXT KEY idx_filename (filename),
            FULLTEXT KEY idx_title (title),
            FULLTEXT KEY idx_alt (alt_text),
            FULLTEXT KEY idx_combined (combined_text)
        ) ENGINE=InnoDB $charset_collate;";

        if ( ! function_exists( 'dbDelta' ) ) {
            require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        }
        dbDelta( $sql );

        // Check if we need to migrate from old schema (single search_text column)
        self::maybe_migrate_search_index();
    }

    /**
     * Migrate search index from old schema to new weighted schema
     *
     * Detects if the table has the old single-column schema and triggers
     * a repopulation with the new multi-column schema.
     *
     * @since 1.0.0
     *
     * @return void
     */
    private static function maybe_migrate_search_index() {
        global $wpdb;

        $table_name = $wpdb->prefix . 'infyp_search_index';

        // Check if 'filename' column exists (new schema indicator)
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema check; $table_name from $wpdb->prefix + hardcoded suffix
        $columns = $wpdb->get_results( "SHOW COLUMNS FROM `{$table_name}`" );

        $has_filename_column = false;
        $has_old_search_text = false;

        foreach ( $columns as $column ) {
            if ( $column->Field === 'filename' ) {
                $has_filename_column = true;
            }
            if ( $column->Field === 'search_text' ) {
                $has_old_search_text = true;
            }
        }

        // If old schema detected (has search_text but no filename), drop and recreate
        if ( $has_old_search_text && ! $has_filename_column ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema migration; $table_name from $wpdb->prefix + hardcoded suffix
            $wpdb->query( "DROP TABLE IF EXISTS `{$table_name}`" );

            // Recreate with new schema
            $charset_collate = $wpdb->get_charset_collate();
            $sql = "CREATE TABLE $table_name (
                image_id bigint(20) UNSIGNED NOT NULL PRIMARY KEY,
                filename varchar(255) DEFAULT '' NOT NULL,
                title text NOT NULL,
                alt_text text NOT NULL,
                combined_text text NOT NULL,
                updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FULLTEXT KEY idx_filename (filename),
                FULLTEXT KEY idx_title (title),
                FULLTEXT KEY idx_alt (alt_text),
                FULLTEXT KEY idx_combined (combined_text)
            ) ENGINE=InnoDB $charset_collate;";

            if ( ! function_exists( 'dbDelta' ) ) {
                require_once ABSPATH . 'wp-admin/includes/upgrade.php';
            }
            dbDelta( $sql );

            // Mark for repopulation
            delete_option( 'infyp_search_index_populated' );
        }
    }

    /**
     * Populates the search index table with weighted fields
     *
     * Populates separate columns for filename, title, alt_text, and combined_text
     * to enable weighted relevance scoring.
     *
     * @since 1.0.0
     * @since 1.0.0 Updated to populate separate columns for weighted search
     *
     * @param bool $truncate Whether to clear the table before populating. Default true.
     * @return int Number of images indexed.
     */
    public static function populate_search_index( $truncate = true ) {
        global $wpdb;

        $table_name = $wpdb->prefix . 'infyp_search_index';

        // Check if table exists
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Schema check
        $table_exists = $wpdb->get_var(
            $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name )
        );

        if ( ! $table_exists ) {
            self::create_search_index_table();
            // Check again after creation
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Schema check
            $table_exists = $wpdb->get_var(
                $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name )
            );
            if ( ! $table_exists ) {
                return 0; // MySQL version too old
            }
        }

        if ( $truncate ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Truncate operation, table name from $wpdb->prefix is safe
            $wpdb->query( "TRUNCATE TABLE `{$table_name}`" );
        }

        // Populate with separate columns for weighted relevance scoring
        // - filename: extracted from _wp_attached_file (just the filename without path)
        // - title: post_title
        // - alt_text: _wp_attachment_image_alt meta
        // - combined_text: all fields concatenated (includes post_excerpt + post_content for broad search)
        // phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Bulk insert for performance, table names from $wpdb->prefix are safe
        $indexed = $wpdb->query( "
            INSERT INTO `{$table_name}` (image_id, filename, title, alt_text, combined_text)
            SELECT
                p.ID,
                COALESCE(
                    SUBSTRING_INDEX(
                        SUBSTRING_INDEX(pm_file.meta_value, '/', -1),
                        '.',
                        1
                    ),
                    ''
                ) as filename,
                COALESCE(p.post_title, '') as title,
                COALESCE(pm_alt.meta_value, '') as alt_text,
                CONCAT_WS(' ',
                    COALESCE(SUBSTRING_INDEX(SUBSTRING_INDEX(pm_file.meta_value, '/', -1), '.', 1), ''),
                    COALESCE(p.post_title, ''),
                    COALESCE(pm_alt.meta_value, ''),
                    COALESCE(p.post_excerpt, ''),
                    COALESCE(p.post_content, '')
                ) as combined_text
            FROM {$wpdb->posts} p
            LEFT JOIN {$wpdb->postmeta} pm_alt
                ON p.ID = pm_alt.post_id AND pm_alt.meta_key = '_wp_attachment_image_alt'
            LEFT JOIN {$wpdb->postmeta} pm_file
                ON p.ID = pm_file.post_id AND pm_file.meta_key = '_wp_attached_file'
            WHERE p.post_type = 'attachment'
            AND p.post_mime_type LIKE 'image/%'
            AND p.post_status = 'inherit'
            ON DUPLICATE KEY UPDATE
                filename = VALUES(filename),
                title = VALUES(title),
                alt_text = VALUES(alt_text),
                combined_text = VALUES(combined_text),
                updated_at = CURRENT_TIMESTAMP
        " );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

        // Update last populated timestamp
        update_option( 'infyp_search_index_populated', time() );

        return $indexed !== false ? $indexed : 0;
    }

    /**
     * Updates indexes for a single image
     *
     * Called when an image is added, edited, or deleted.
     *
     * @since 1.0.0
     *
     * @param int $image_id The attachment ID.
     * @return void
     */
    public static function update_image_indexes( $image_id ) {
        global $wpdb;

        $image_id = absint( $image_id );
        if ( ! $image_id ) {
            return;
        }

        // Check if attachment is an image
        $mime_type = get_post_mime_type( $image_id );
        if ( ! $mime_type || strpos( $mime_type, 'image/' ) !== 0 ) {
            return;
        }

        $lookup_table = $wpdb->prefix . 'infyp_image_post_types';
        $search_table = $wpdb->prefix . 'infyp_search_index';

        // Check if lookup table exists
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Schema check
        $lookup_exists = $wpdb->get_var(
            $wpdb->prepare( 'SHOW TABLES LIKE %s', $lookup_table )
        );

        if ( $lookup_exists ) {
            // Delete existing entries for this image
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Incremental update
            $wpdb->delete( $lookup_table, array( 'image_id' => $image_id ), array( '%d' ) );

            // Get attachment data
            $attachment = get_post( $image_id );
            if ( ! $attachment ) {
                return;
            }

            // Check if attached to a post
            if ( $attachment->post_parent > 0 ) {
                $parent = get_post( $attachment->post_parent );
                if ( $parent && in_array( $parent->post_status, array( 'publish', 'draft', 'private', 'pending', 'future' ), true ) ) {
                    // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Incremental update
                    $wpdb->insert(
                        $lookup_table,
                        array(
                            'image_id'          => $image_id,
                            'post_type'         => $parent->post_type,
                            'relationship_type' => 'attached',
                        ),
                        array( '%d', '%s', '%s' )
                    );
                }
            }

            // Check if used as featured image anywhere
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Lookup query
            $featured_posts = $wpdb->get_results(
                $wpdb->prepare(
                    "SELECT p.post_type
                    FROM {$wpdb->postmeta} pm
                    INNER JOIN {$wpdb->posts} p ON pm.post_id = p.ID
                    WHERE pm.meta_key = '_thumbnail_id'
                    AND pm.meta_value = %s
                    AND p.post_status IN ('publish', 'draft', 'private', 'pending', 'future')",
                    $image_id
                )
            );

            foreach ( $featured_posts as $post ) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Incremental update
                $wpdb->replace(
                    $lookup_table,
                    array(
                        'image_id'          => $image_id,
                        'post_type'         => $post->post_type,
                        'relationship_type' => 'featured',
                    ),
                    array( '%d', '%s', '%s' )
                );
            }

            // If not attached or featured anywhere, mark as media
            // phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Count query, table name from $wpdb->prefix is safe
            $has_relationships = $wpdb->get_var(
                $wpdb->prepare(
                    "SELECT COUNT(*) FROM `{$lookup_table}` WHERE image_id = %d",
                    $image_id
                )
            );
            // phpcs:enable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

            if ( ! $has_relationships ) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Incremental update
                $wpdb->insert(
                    $lookup_table,
                    array(
                        'image_id'          => $image_id,
                        'post_type'         => 'media',
                        'relationship_type' => 'unattached',
                    ),
                    array( '%d', '%s', '%s' )
                );
            }
        }

        // Update search index
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Schema check
        $search_exists = $wpdb->get_var(
            $wpdb->prepare( 'SHOW TABLES LIKE %s', $search_table )
        );

        if ( $search_exists ) {
            $attachment = get_post( $image_id );
            if ( ! $attachment ) {
                // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Cleanup
                $wpdb->delete( $search_table, array( 'image_id' => $image_id ), array( '%d' ) );
                return;
            }

            $alt_text = get_post_meta( $image_id, '_wp_attachment_image_alt', true );
            $file_path = get_post_meta( $image_id, '_wp_attached_file', true );
            // Extract filename without extension for better search matching
            $filename_with_ext = $file_path ? basename( $file_path ) : '';
            $filename = pathinfo( $filename_with_ext, PATHINFO_FILENAME );

            $title = $attachment->post_title ? $attachment->post_title : '';
            $alt_text = $alt_text ? $alt_text : '';

            // Build combined text including post_excerpt (caption) and post_content (description) for broad search.
            $combined_parts = array( $filename, $title, $alt_text );
            if ( ! empty( $attachment->post_excerpt ) ) {
                $combined_parts[] = $attachment->post_excerpt;
            }
            if ( ! empty( $attachment->post_content ) ) {
                $combined_parts[] = $attachment->post_content;
            }

            /**
             * Filters the combined text parts before they are stored in the search index.
             *
             * Extensions can add custom field data (e.g., caption, description) for search.
             *
             * @since 1.0.0
             * @param array  $combined_parts Array of text strings to concatenate.
             * @param int    $image_id       The attachment ID.
             * @param object $attachment     The WP_Post attachment object.
             */
            $combined_parts = apply_filters( 'infyp_search_index_combined_parts', $combined_parts, $image_id, $attachment );
            $combined_text = implode( ' ', array_filter( $combined_parts ) );

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Incremental update
            $wpdb->replace(
                $search_table,
                array(
                    'image_id'      => $image_id,
                    'filename'      => $filename,
                    'title'         => $title,
                    'alt_text'      => $alt_text,
                    'combined_text' => $combined_text,
                ),
                array( '%d', '%s', '%s', '%s', '%s' )
            );
        }
    }

    /**
     * Removes an image from all indexes
     *
     * @since 1.0.0
     *
     * @param int $image_id The attachment ID.
     * @return void
     */
    public static function remove_image_from_indexes( $image_id ) {
        global $wpdb;

        $image_id = absint( $image_id );
        if ( ! $image_id ) {
            return;
        }

        $lookup_table = $wpdb->prefix . 'infyp_image_post_types';
        $search_table = $wpdb->prefix . 'infyp_search_index';

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Cleanup
        $wpdb->delete( $lookup_table, array( 'image_id' => $image_id ), array( '%d' ) );
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Cleanup
        $wpdb->delete( $search_table, array( 'image_id' => $image_id ), array( '%d' ) );
    }

    /**
     * Checks if lookup tables are populated and up-to-date
     *
     * @since 1.0.0
     *
     * @return array Status of each index.
     */
    public static function get_index_status() {
        global $wpdb;

        $lookup_table = $wpdb->prefix . 'infyp_image_post_types';
        $search_table = $wpdb->prefix . 'infyp_search_index';

        $status = array(
            'lookup' => array(
                'exists'      => false,
                'count'       => 0,
                'last_update' => null,
            ),
            'search' => array(
                'exists'      => false,
                'count'       => 0,
                'last_update' => null,
            ),
        );

        // Check lookup table
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Schema check
        $lookup_exists = $wpdb->get_var(
            $wpdb->prepare( 'SHOW TABLES LIKE %s', $lookup_table )
        );

        if ( $lookup_exists ) {
            $status['lookup']['exists'] = true;
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Count query, table name from $wpdb->prefix is safe
            $status['lookup']['count'] = (int) $wpdb->get_var( "SELECT COUNT(*) FROM `{$lookup_table}`" );
            $status['lookup']['last_update'] = get_option( 'infyp_lookup_table_populated' );
        }

        // Check search table
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery -- Schema check
        $search_exists = $wpdb->get_var(
            $wpdb->prepare( 'SHOW TABLES LIKE %s', $search_table )
        );

        if ( $search_exists ) {
            $status['search']['exists'] = true;
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Count query, table name from $wpdb->prefix is safe
            $status['search']['count'] = (int) $wpdb->get_var( "SELECT COUNT(*) FROM `{$search_table}`" );
            $status['search']['last_update'] = get_option( 'infyp_search_index_populated' );
        }

        return $status;
    }
}