, * ai_status?: string, * orientation?: string, * author_id?: int, * date_from?: string, * date_to?: string, * sort?: string, * direction?: string * } $args * @return array{items: list>, total: int, page: int, per_page: int, next_cursor: int|null} */ public function list( array $args ): array { global $wpdb; // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Whitelisted joins, sort columns, and table names. $ids_only = ! empty( $args['_ids_only'] ); $page = max( 1, (int) ( $args['page'] ?? 1 ) ); $per_page = min( $ids_only ? 500 : 100, max( 1, (int) ( $args['per_page'] ?? 20 ) ) ); $offset = ( $page - 1 ) * $per_page; $cursor = max( 0, (int) ( $args['cursor'] ?? 0 ) ); if ( ! in_array( (string) ( $args['sort'] ?? 'date' ), array( '', 'default', 'date' ), true ) || ! empty( $args['folder_id'] ) ) { $cursor = 0; } $site_id = \FoldRocket\Infrastructure\Database\TableNames::site_id(); $assets = \FoldRocket\Infrastructure\Database\TableNames::assert_plugin_table( \FoldRocket\Infrastructure\Database\TableNames::assets() ); $posts = $wpdb->posts; $where = array( 'p.post_type = %s', 'p.post_status = %s' ); $params = array( 'attachment', 'inherit' ); $join_type = ! empty( $args['indexed_only'] ) ? 'INNER' : 'LEFT'; $join = "{$join_type} JOIN {$assets} a ON a.attachment_id = p.ID AND a.site_id = %d"; array_unshift( $params, $site_id ); if ( ! empty( $args['folder_id'] ) ) { $folder_assets = \FoldRocket\Infrastructure\Database\TableNames::assert_plugin_table( \FoldRocket\Infrastructure\Database\TableNames::folder_assets() ); // Placeholder order must match SQL order: site_id, then folder_id, then WHERE args. $join .= " INNER JOIN {$folder_assets} fa ON fa.asset_id = a.id AND fa.folder_id = %d"; array_splice( $params, 1, 0, array( (int) $args['folder_id'] ) ); } $this->apply_wpml_original_attachment_filter( $join, $where ); if ( ! empty( $args['search'] ) ) { $this->apply_keyword_search( (string) $args['search'], $where, $params ); } if ( ! empty( $args['mime'] ) ) { $raw = $args['mime']; if ( is_array( $raw ) ) { $tokens = array_values( array_filter( array_map( 'strval', $raw ) ) ); } else { $tokens = preg_split( '/\s*,\s*/', (string) $raw ) ?: array(); $tokens = array_values( array_filter( $tokens ) ); } $clauses = array(); foreach ( $tokens as $mime ) { $clause = $this->mime_where_clause( $mime, $params ); if ( null !== $clause ) { $clauses[] = $clause; } } if ( 1 === count( $clauses ) ) { $where[] = $clauses[0]; } elseif ( count( $clauses ) > 1 ) { $where[] = '(' . implode( ' OR ', $clauses ) . ')'; } } if ( ! empty( $args['ai_status'] ) ) { $tokens = $this->split_list_param( $args['ai_status'] ); $clauses = array(); foreach ( $tokens as $status ) { if ( 'pending' === $status || 'not_analyzed' === $status ) { $clauses[] = "a.ai_status IN ('pending', 'failed')"; } elseif ( 'analyzed' === $status || 'failed' === $status ) { $clauses[] = 'a.ai_status = %s'; $params[] = $status; } } $clauses = array_values( array_unique( $clauses ) ); if ( 1 === count( $clauses ) ) { $where[] = $clauses[0]; } elseif ( count( $clauses ) > 1 ) { $where[] = '(' . implode( ' OR ', $clauses ) . ')'; } } if ( ! empty( $args['duplicate_status'] ) ) { $where[] = 'a.duplicate_status = %s'; $params[] = (string) $args['duplicate_status']; } if ( array_key_exists( 'usage_max', $args ) && $args['usage_max'] !== null && $args['usage_max'] !== '' ) { $where[] = 'a.usage_count <= %d'; $params[] = (int) $args['usage_max']; if ( 0 === (int) $args['usage_max'] ) { $where[] = 'a.references_scanned_at IS NOT NULL'; } } if ( array_key_exists( 'usage_min', $args ) && $args['usage_min'] !== null && $args['usage_min'] !== '' ) { $where[] = 'a.usage_count >= %d'; $params[] = (int) $args['usage_min']; } if ( ! empty( $args['min_size'] ) ) { $where[] = 'a.file_size >= %d'; $params[] = (int) $args['min_size']; } if ( ! empty( $args['missing_alt'] ) ) { $join .= " LEFT JOIN {$wpdb->postmeta} altmeta ON altmeta.post_id = p.ID AND altmeta.meta_key = '_wp_attachment_image_alt'"; $where[] = "p.post_mime_type LIKE 'image/%'"; $where[] = '(altmeta.meta_value IS NULL OR altmeta.meta_value = \'\')'; } if ( ! empty( $args['missing_file'] ) ) { $join .= " INNER JOIN {$wpdb->postmeta} filemissing ON filemissing.post_id = p.ID AND filemissing.meta_key = '" . self::MISSING_FILE_META . "'"; $where[] = "filemissing.meta_value = '1'"; } if ( ! empty( $args['orientation'] ) ) { $allowed = array( 'landscape', 'portrait', 'square' ); $tokens = array_values( array_filter( $this->split_list_param( $args['orientation'] ), static fn( string $v ): bool => in_array( $v, $allowed, true ) ) ); if ( 1 === count( $tokens ) ) { $where[] = 'a.orientation = %s'; $params[] = $tokens[0]; } elseif ( count( $tokens ) > 1 ) { $placeholders = implode( ', ', array_fill( 0, count( $tokens ), '%s' ) ); $where[] = "a.orientation IN ({$placeholders})"; foreach ( $tokens as $token ) { $params[] = $token; } } } if ( ! empty( $args['author_id'] ) ) { $ids = array_values( array_filter( array_map( 'intval', $this->split_list_param( $args['author_id'] ) ), static fn( int $id ): bool => $id > 0 ) ); if ( 1 === count( $ids ) ) { $where[] = 'p.post_author = %d'; $params[] = $ids[0]; } elseif ( count( $ids ) > 1 ) { $placeholders = implode( ', ', array_fill( 0, count( $ids ), '%d' ) ); $where[] = "p.post_author IN ({$placeholders})"; foreach ( $ids as $id ) { $params[] = $id; } } } if ( ! empty( $args['date_from'] ) ) { $from = $this->sanitize_date_param( (string) $args['date_from'] ); if ( $from ) { $where[] = 'p.post_date >= %s'; $params[] = $from . ' 00:00:00'; } } if ( ! empty( $args['date_to'] ) ) { $to = $this->sanitize_date_param( (string) $args['date_to'] ); if ( $to ) { $where[] = 'p.post_date <= %s'; $params[] = $to . ' 23:59:59'; } } if ( $cursor > 0 ) { $where[] = strtoupper( (string) ( $args['direction'] ?? 'DESC' ) ) === 'ASC' ? 'p.ID > %d' : 'p.ID < %d'; $params[] = $cursor; $offset = 0; $args['_include_total'] = false; } $where_sql = implode( ' AND ', $where ); $sort_map = array( 'date' => 'p.post_date', 'title' => 'p.post_title', 'size' => 'a.file_size', 'usage' => 'a.usage_count', ); $requested = (string) ( $args['sort'] ?? '' ); if ( ! empty( $args['folder_id'] ) && ( '' === $requested || 'default' === $requested ) ) { $sort = 'fa.position ASC, p.ID ASC'; $direction = ''; } else { $sort = $sort_map[ $requested ] ?? 'p.post_date'; $direction = strtoupper( (string) ( $args['direction'] ?? 'DESC' ) ) === 'ASC' ? 'ASC' : 'DESC'; $sort .= ' ' . $direction . ', p.ID'; } $total = -1; if ( ! isset( $args['_include_total'] ) || $args['_include_total'] ) { $count_sql = "SELECT COUNT(DISTINCT p.ID) FROM {$posts} p {$join} WHERE {$where_sql}"; // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Whitelisted joins, sort columns, and table names. $total = (int) $wpdb->get_var( $wpdb->prepare( $count_sql, $params ) ); } $select = $ids_only ? 'p.ID AS attachment_id' : "p.ID AS attachment_id, p.post_title, p.post_name, p.post_mime_type, p.post_date, a.id AS asset_id, a.width, a.height, a.file_size, a.ai_status, a.ai_description, a.usage_count, a.duplicate_status, a.optimization_status"; $list_sql = "SELECT {$select} FROM {$posts} p {$join} WHERE {$where_sql} ORDER BY {$sort} {$direction} LIMIT %d OFFSET %d"; $list_params = $params; $list_params[] = $per_page; $list_params[] = $offset; $rows = $wpdb->get_results( $wpdb->prepare( $list_sql, $list_params ), ARRAY_A ) ?: array(); // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter if ( $ids_only ) { $items = $rows; } else { $attachment_ids = array_values( array_filter( array_map( static fn( array $row ): int => (int) $row['attachment_id'], $rows ) ) ); if ( $attachment_ids ) { _prime_post_caches( $attachment_ids, true, true ); update_meta_cache( 'post', $attachment_ids ); } $asset_ids = array_values( array_filter( array_map( static fn( array $row ): int => (int) ( $row['asset_id'] ?? 0 ), $rows ) ) ); $folder_map = $this->folder_ids_for_assets( $asset_ids ); $items = array_map( fn( array $row ): array => $this->hydrate_list_item( $row, $folder_map[ (int) ( $row['asset_id'] ?? 0 ) ] ?? array() ), $rows ); } return array( 'items' => $items, 'total' => $total, 'page' => $page, 'per_page' => $per_page, 'next_cursor' => count( $rows ) === $per_page ? (int) ( $rows[ count( $rows ) - 1 ]['attachment_id'] ?? 0 ) : null, ); } /** * Keep one visible media item per WPML translation group. * * WPML stores translated attachment posts separately even though they point * to the same physical file. FoldRocket lists the source attachment only; * localized metadata remains available through the multilingual fields. * Attachments that WPML does not manage are left untouched. * * @param string $join SQL joins for the asset query. * @param list $where SQL predicates for the asset query. */ public function apply_wpml_original_attachment_filter( string &$join, array &$where ): void { if ( ! $this->wpml_is_active() ) { return; } global $wpdb; $translations = $wpdb->prefix . 'icl_translations'; if ( ! preg_match( '/^[A-Za-z0-9_]+$/', $translations ) || ! $this->wpml_translation_table_exists( $translations ) ) { return; } $join .= " LEFT JOIN {$translations} frkt_wpml_translation ON frkt_wpml_translation.element_id = p.ID AND frkt_wpml_translation.element_type = 'post_attachment'"; $where[] = '(frkt_wpml_translation.translation_id IS NULL OR frkt_wpml_translation.source_language_code IS NULL)'; } /** * Return every WPML attachment record representing the same logical media. * Translations are ordered before the source so deleting the family cannot * leave the canonical attachment behind after WPML runs its own hooks. * * @return list */ public function wpml_attachment_family_ids( int $attachment_id ): array { $attachment_id = max( 0, $attachment_id ); if ( $attachment_id < 1 || ! $this->wpml_is_active() ) { return $attachment_id > 0 ? array( $attachment_id ) : array(); } global $wpdb; $translations = $wpdb->prefix . 'icl_translations'; if ( ! preg_match( '/^[A-Za-z0-9_]+$/', $translations ) || ! $this->wpml_translation_table_exists( $translations ) ) { return array( $attachment_id ); } $trid = (int) $wpdb->get_var( $wpdb->prepare( "SELECT trid FROM %i WHERE element_id = %d AND element_type = 'post_attachment' LIMIT 1", $translations, $attachment_id ) ); if ( $trid < 1 ) { return array( $attachment_id ); } $ids = $wpdb->get_col( $wpdb->prepare( "SELECT element_id FROM %i WHERE trid = %d AND element_type = 'post_attachment' ORDER BY (source_language_code IS NULL) ASC, element_id ASC", $translations, $trid ) ) ?: array(); $ids = array_values( array_unique( array_filter( array_map( 'intval', $ids ) ) ) ); if ( ! in_array( $attachment_id, $ids, true ) ) { $ids[] = $attachment_id; } return $ids; } private function wpml_is_active(): bool { return defined( 'ICL_SITEPRESS_VERSION' ) || class_exists( 'SitePress' ) || has_filter( 'wpml_object_id' ); } private function wpml_translation_table_exists( string $table ): bool { static $known_tables = array(); if ( array_key_exists( $table, $known_tables ) ) { return $known_tables[ $table ]; } global $wpdb; $found = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table ) ) ); $known_tables[ $table ] = $found === $table; return $known_tables[ $table ]; } /** * Attachment IDs for the same filters as list(), up to $max. * * @param array $args * @return array{ids: list, total: int} */ public function list_attachment_ids( array $args, int $max ): array { $max = max( 1, $max ); $first = $this->list( array_merge( $args, array( 'page' => 1, 'per_page' => min( 500, $max ), '_ids_only' => true, ) ) ); $total = (int) $first['total']; $ids = array(); foreach ( $first['items'] as $item ) { $id = (int) ( $item['attachment_id'] ?? 0 ); if ( $id > 0 ) { $ids[] = $id; } } if ( $total > $max ) { return array( 'ids' => array(), 'total' => $total, ); } $page = 2; while ( count( $ids ) < $total ) { $batch = $this->list( array_merge( $args, array( 'page' => $page, 'per_page' => 500, '_ids_only' => true, '_include_total' => false, ) ) ); if ( ! $batch['items'] ) { break; } foreach ( $batch['items'] as $item ) { $id = (int) ( $item['attachment_id'] ?? 0 ); if ( $id > 0 ) { $ids[] = $id; } } ++$page; if ( count( $ids ) >= $max || $page > (int) ceil( $total / 500 ) + 1 ) { break; } } return array( 'ids' => $ids, 'total' => $total, ); } /** * @return array|null */ public function find_by_attachment( int $attachment_id ): ?array { global $wpdb; $attachment_id = max( 0, $attachment_id ); if ( $attachment_id < 1 ) { return null; } if ( ! $this->attachment_is_live( $attachment_id ) ) { // Drop stale index rows so duplicates / library stop surfacing ghosts. $this->delete_by_attachment( $attachment_id ); return null; } $assets = \FoldRocket\Infrastructure\Database\TableNames::assert_plugin_table( \FoldRocket\Infrastructure\Database\TableNames::assets() ); $site_id = \FoldRocket\Infrastructure\Database\TableNames::site_id(); $row = $wpdb->get_row( $wpdb->prepare( 'SELECT * FROM %i WHERE attachment_id = %d AND site_id = %d', $assets, $attachment_id, $site_id ), ARRAY_A ); if ( ! $row ) { return $this->hydrate_from_attachment( $attachment_id, null ); } return $this->hydrate_from_attachment( $attachment_id, $row ); } /** True when the ID is a live (non-trashed) WordPress attachment. */ public function attachment_is_live( int $attachment_id ): bool { $attachment_id = max( 0, $attachment_id ); if ( $attachment_id < 1 ) { return false; } $post = get_post( $attachment_id ); return $post instanceof \WP_Post && $post->post_type === 'attachment' && $post->post_status === 'inherit'; } /** * @param array $data */ public function upsert( int $attachment_id, array $data ): int { global $wpdb; $assets = \FoldRocket\Infrastructure\Database\TableNames::assert_plugin_table( \FoldRocket\Infrastructure\Database\TableNames::assets() ); $site_id = \FoldRocket\Infrastructure\Database\TableNames::site_id(); $now = current_time( 'mysql' ); $existing = $wpdb->get_var( $wpdb->prepare( 'SELECT id FROM %i WHERE attachment_id = %d AND site_id = %d', $assets, $attachment_id, $site_id ) ); $payload = array_merge( array( 'attachment_id' => $attachment_id, 'site_id' => $site_id, 'updated_at' => $now, ), $data ); if ( $existing ) { $wpdb->update( $assets, $payload, array( 'id' => (int) $existing ) ); // Rebuilding the denormalized document joins locales and tags. Avoid // paying that cost for progress/status-only job updates. if ( array_key_exists( 'ai_description', $data ) ) { $this->refresh_search_document( $attachment_id ); } return (int) $existing; } $payload['created_at'] = $now; $wpdb->insert( $assets, $payload ); $this->refresh_search_document( $attachment_id ); return (int) $wpdb->insert_id; } /** * Rebuild the denormalized document used by the library full-text search. * WordPress remains canonical; plugin metadata only augments the document. */ public function refresh_search_document( int $attachment_id ): void { global $wpdb; $post = get_post( $attachment_id ); if ( ! $post instanceof \WP_Post || 'attachment' !== $post->post_type ) { return; } $assets = \FoldRocket\Infrastructure\Database\TableNames::assert_plugin_table( \FoldRocket\Infrastructure\Database\TableNames::assets() ); $locales = \FoldRocket\Infrastructure\Database\TableNames::assert_plugin_table( \FoldRocket\Infrastructure\Database\TableNames::asset_locales() ); $tags = \FoldRocket\Infrastructure\Database\TableNames::assert_plugin_table( \FoldRocket\Infrastructure\Database\TableNames::tags() ); $pivot = \FoldRocket\Infrastructure\Database\TableNames::assert_plugin_table( \FoldRocket\Infrastructure\Database\TableNames::asset_tags() ); $site_id = \FoldRocket\Infrastructure\Database\TableNames::site_id(); $asset = $wpdb->get_row( $wpdb->prepare( 'SELECT id, ai_description FROM %i WHERE attachment_id = %d AND site_id = %d', $assets, $attachment_id, $site_id ), ARRAY_A ); if ( ! is_array( $asset ) ) { return; } $asset_id = (int) $asset['id']; $locale_rows = $wpdb->get_results( $wpdb->prepare( 'SELECT locale, alt_text, title, caption, description FROM %i WHERE asset_id = %d', $locales, $asset_id ), ARRAY_A ) ?: array(); $tag_rows = $wpdb->get_results( $wpdb->prepare( 'SELECT t.canonical_slug, t.category FROM %i atx INNER JOIN %i t ON t.id = atx.tag_id WHERE atx.asset_id = %d', $pivot, $tags, $asset_id ), ARRAY_A ) ?: array(); $parts = array( $post->post_title, $post->post_name, $post->post_excerpt, $post->post_content, (string) get_post_meta( $attachment_id, '_wp_attachment_image_alt', true ), (string) $asset['ai_description'], ); foreach ( $locale_rows as $row ) { $parts[] = implode( ' ', array_map( 'strval', $row ) ); } foreach ( $tag_rows as $row ) { $parts[] = implode( ' ', array_map( 'strval', $row ) ); } $document = implode( ' ', array_filter( array_map( 'wp_strip_all_tags', $parts ) ) ); $document = preg_replace( '/\s+/u', ' ', trim( $document ) ) ?: ''; $wpdb->update( $assets, array( 'search_document' => $document ), array( 'id' => $asset_id, 'site_id' => $site_id, ) ); } public function delete_by_attachment( int $attachment_id ): void { global $wpdb; $assets = \FoldRocket\Infrastructure\Database\TableNames::assert_plugin_table( \FoldRocket\Infrastructure\Database\TableNames::assets() ); $site_id = \FoldRocket\Infrastructure\Database\TableNames::site_id(); $asset_id = $wpdb->get_var( $wpdb->prepare( 'SELECT id FROM %i WHERE attachment_id = %d AND site_id = %d', $assets, $attachment_id, $site_id ) ); if ( ! $asset_id ) { return; } $asset_id = (int) $asset_id; $wpdb->delete( \FoldRocket\Infrastructure\Database\TableNames::folder_assets(), array( 'asset_id' => $asset_id ) ); $wpdb->delete( \FoldRocket\Infrastructure\Database\TableNames::asset_tags(), array( 'asset_id' => $asset_id ) ); $wpdb->delete( \FoldRocket\Infrastructure\Database\TableNames::asset_locales(), array( 'asset_id' => $asset_id ) ); $wpdb->delete( \FoldRocket\Infrastructure\Database\TableNames::references(), array( 'asset_id' => $asset_id ) ); $wpdb->delete( \FoldRocket\Infrastructure\Database\TableNames::embeddings(), array( 'asset_id' => $asset_id ) ); $wpdb->delete( $assets, array( 'id' => $asset_id ) ); } /** * @param array $row * @return array */ private function hydrate_list_item( array $row, ?array $prefetched_folder_ids = null ): array { $attachment_id = (int) $row['attachment_id']; $url = wp_get_attachment_url( $attachment_id ) ?: ''; $mime = strtolower( (string) ( $row['post_mime_type'] ?? '' ) ); if ( $mime === '' ) { $mime = strtolower( (string) ( get_post_mime_type( $attachment_id ) ?: '' ) ); } // Prefer WP-generated image/poster; never use the original file URL as a thumb for video/audio/docs. $thumb = wp_get_attachment_image_url( $attachment_id, 'medium' ) ?: ''; $thumb_srcset = str_starts_with( $mime, 'image/' ) ? ( wp_get_attachment_image_srcset( $attachment_id, 'medium' ) ?: '' ) : ''; if ( $thumb === '' && str_starts_with( $mime, 'image/' ) ) { $thumb = $url; } $file = get_attached_file( $attachment_id ); $filename = $file ? wp_basename( $file ) : (string) ( $row['post_name'] ?? '' ); $missing_file = '1' === (string) get_post_meta( $attachment_id, self::MISSING_FILE_META, true ) || ! is_string( $file ) || $file === '' || ! is_readable( $file ); $folder_ids = array(); $asset_id = 0; if ( isset( $row['asset_id'] ) && $row['asset_id'] !== null && $row['asset_id'] !== '' ) { $asset_id = (int) $row['asset_id']; } elseif ( isset( $row['id'], $row['attachment_id'] ) && (int) $row['attachment_id'] === $attachment_id ) { // Row from frkt_assets (SELECT *), where `id` is the plugin asset id. $asset_id = (int) $row['id']; } if ( null !== $prefetched_folder_ids ) { $folder_ids = array_map( 'intval', $prefetched_folder_ids ); } elseif ( $asset_id > 0 ) { $folder_ids = $this->folder_ids_for_asset( $asset_id ); } $title = isset( $row['post_title'] ) ? \FoldRocket\Infrastructure\Support\Text::plain( (string) $row['post_title'] ) : \FoldRocket\Infrastructure\Support\Text::post_title( $attachment_id ); return array( 'id' => $asset_id, 'attachmentId' => $attachment_id, 'filename' => $filename, 'title' => $title, 'altText' => \FoldRocket\Infrastructure\Support\Text::plain( (string) get_post_meta( $attachment_id, '_wp_attachment_image_alt', true ) ), 'caption' => \FoldRocket\Infrastructure\Support\Text::post_excerpt( $attachment_id ), 'description' => \FoldRocket\Infrastructure\Support\Text::post_content( $attachment_id ), 'url' => $url, 'thumbnailUrl' => $thumb ?: '', 'thumbnailSrcSet' => $thumb_srcset, 'mimeType' => $mime !== '' ? $mime : (string) ( $row['post_mime_type'] ?? '' ), 'width' => isset( $row['width'] ) ? (int) $row['width'] : null, 'height' => isset( $row['height'] ) ? (int) $row['height'] : null, 'fileSize' => isset( $row['file_size'] ) ? (int) $row['file_size'] : null, 'missingFile' => $missing_file, 'folderIds' => $folder_ids, 'usageCount' => (int) ( $row['usage_count'] ?? 0 ), 'aiStatus' => (string) ( $row['ai_status'] ?? 'pending' ), 'aiDescription' => \FoldRocket\Infrastructure\Support\Text::plain( (string) ( $row['ai_description'] ?? '' ) ), 'duplicateStatus' => (string) ( $row['duplicate_status'] ?? 'unknown' ), 'optimizationStatus' => (string) ( $row['optimization_status'] ?? 'none' ), 'metadata' => array(), ); } /** * @param array|null $row * @return array */ private function hydrate_from_attachment( int $attachment_id, ?array $row ): array { $post = get_post( $attachment_id ); $base = $this->hydrate_list_item( array_merge( array( 'attachment_id' => $attachment_id, 'post_title' => $post ? $post->post_title : '', 'post_name' => $post ? $post->post_name : '', 'post_mime_type' => $post ? $post->post_mime_type : '', ), $row ?? array() ) ); $base['metadata'] = $this->locales_for_asset( (int) ( $base['id'] ?? 0 ) ); $base['tags'] = $this->tags_for_asset( (int) ( $base['id'] ?? 0 ) ); $base['details'] = ( new \FoldRocket\Application\Services\AssetDetailsService() ) ->for_attachment( $attachment_id, $row ); // Ensure default-locale metadata mirrors WordPress attachment fields (canonical). $base['metadata'] = $this->merge_wordpress_into_default_locale( $base['metadata'], array( 'title' => (string) ( $base['title'] ?? '' ), 'altText' => (string) ( $base['altText'] ?? '' ), 'caption' => (string) ( $base['caption'] ?? '' ), 'description' => (string) ( $base['description'] ?? '' ), ) ); return $base; } /** * WordPress attachment title/ALT/caption/description win for the default locale. * * @param array> $metadata * @param array{title: string, altText: string, caption: string, description: string} $wp * @return array> */ private function merge_wordpress_into_default_locale( array $metadata, array $wp ): array { $settings = get_option( 'frkt_settings', array() ); $default = (string) ( $settings['languages']['default'] ?? 'en_US' ); if ( $default === '' ) { $default = 'en_US'; } $current = isset( $metadata[ $default ] ) && is_array( $metadata[ $default ] ) ? $metadata[ $default ] : array(); $metadata[ $default ] = array( 'title' => $wp['title'], 'altText' => $wp['altText'], 'caption' => $wp['caption'], 'description' => $wp['description'], 'source' => (string) ( $current['source'] ?? 'wordpress' ), ); return $metadata; } /** @return list */ private function folder_ids_for_asset( int $asset_id ): array { global $wpdb; $table = \FoldRocket\Infrastructure\Database\TableNames::assert_plugin_table( \FoldRocket\Infrastructure\Database\TableNames::folder_assets() ); $ids = $wpdb->get_col( $wpdb->prepare( 'SELECT folder_id FROM %i WHERE asset_id = %d', $table, $asset_id ) ); return array_map( 'intval', $ids ?: array() ); } /** * @param list $asset_ids * @return array> */ private function folder_ids_for_assets( array $asset_ids ): array { global $wpdb; $asset_ids = array_values( array_unique( array_filter( array_map( 'intval', $asset_ids ) ) ) ); if ( ! $asset_ids ) { return array(); } $table = \FoldRocket\Infrastructure\Database\TableNames::assert_plugin_table( \FoldRocket\Infrastructure\Database\TableNames::folder_assets() ); $placeholders = implode( ', ', array_fill( 0, count( $asset_ids ), '%d' ) ); // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Placeholder count matches the validated integer ID list. $rows = $wpdb->get_results( $wpdb->prepare( "SELECT asset_id, folder_id FROM %i WHERE asset_id IN ({$placeholders})", array_merge( array( $table ), $asset_ids ) ), ARRAY_A ) ?: array(); // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared $out = array_fill_keys( $asset_ids, array() ); foreach ( $rows as $row ) { $asset_id = (int) $row['asset_id']; $out[ $asset_id ][] = (int) $row['folder_id']; } return $out; } /** * @return array> */ private function locales_for_asset( int $asset_id ): array { if ( $asset_id <= 0 ) { return array(); } global $wpdb; $table = \FoldRocket\Infrastructure\Database\TableNames::assert_plugin_table( \FoldRocket\Infrastructure\Database\TableNames::asset_locales() ); $rows = $wpdb->get_results( $wpdb->prepare( 'SELECT * FROM %i WHERE asset_id = %d', $table, $asset_id ), ARRAY_A ) ?: array(); $out = array(); foreach ( $rows as $row ) { $out[ (string) $row['locale'] ] = array( 'altText' => \FoldRocket\Infrastructure\Support\Text::plain( (string) $row['alt_text'] ), 'title' => \FoldRocket\Infrastructure\Support\Text::plain( (string) $row['title'] ), 'caption' => \FoldRocket\Infrastructure\Support\Text::plain( (string) $row['caption'] ), 'description' => \FoldRocket\Infrastructure\Support\Text::plain( (string) $row['description'] ), 'source' => (string) $row['source'], ); } return $out; } /** * Keyword search against the denormalized FULLTEXT document. A small prefix * fallback keeps freshly uploaded, not-yet-indexed attachment names searchable. * * @param list $where * @param list $params */ private function apply_keyword_search( string $search, array &$where, array &$params ): void { $tokens = $this->search_tokens( $search ); if ( ! $tokens ) { $tokens = array( trim( $search ) ); } $boolean = array(); foreach ( $tokens as $token ) { $clean = preg_replace( '/[^\p{L}\p{N}_-]+/u', '', $token ) ?: ''; if ( '' !== $clean ) { $boolean[] = '+' . $clean . '*'; } } if ( ! $boolean ) { return; } $prefix = implode( ' ', $tokens ) . '%'; if ( $this->has_fulltext_search_index() ) { $where[] = '(MATCH(a.search_document) AGAINST (%s IN BOOLEAN MODE) OR p.post_title LIKE %s OR p.post_name LIKE %s)'; $params[] = implode( ' ', $boolean ); } else { // Older or constrained database engines may reject FULLTEXT indexes. // Keep search functional with a bounded prefix fallback instead of // letting the entire library request fail. $where[] = '(a.search_document LIKE %s OR p.post_title LIKE %s OR p.post_name LIKE %s)'; $params[] = $prefix; } $params[] = $prefix; $params[] = sanitize_title( implode( ' ', $tokens ) ) . '%'; } /** Check the migrated index once per request. */ private function has_fulltext_search_index(): bool { static $available = null; if ( null !== $available ) { return $available; } global $wpdb; $table = \FoldRocket\Infrastructure\Database\TableNames::assert_plugin_table( \FoldRocket\Infrastructure\Database\TableNames::assets() ); $rows = $wpdb->get_results( $wpdb->prepare( 'SHOW INDEX FROM %i WHERE Key_name = %s', $table, 'search_document' ), ARRAY_A ); $available = is_array( $rows ) && ! empty( $rows ); return $available; } /** * @return list */ private function search_tokens( string $search ): array { $normalized = strtolower( trim( $search ) ); $normalized = preg_replace( '/[^\p{L}\p{N}\s\-]+/u', ' ', $normalized ) ?? $normalized; $parts = preg_split( '/\s+/u', $normalized ) ?: array(); $stop = array( 'a', 'an', 'the', 'and', 'or', 'of', 'in', 'on', 'at', 'to', 'for', 'from', 'with', 'by', 'is', 'are', 'was', 'were', 'be', 'this', 'that', 'these', 'those', 'my', 'your', 'our', 'me', 'find', 'show', 'search', 'get', 'list', 'open', 'please', 'all', 'any', 'some', 'image', 'images', 'photo', 'photos', 'picture', 'pictures', 'pic', 'pics', 'media', 'file', 'files', 'asset', 'assets', 'library', 'containing', 'contains', 'depicting', 'showing', 'about', 'un', 'una', 'uno', 'il', 'lo', 'la', 'i', 'gli', 'le', 'di', 'da', 'in', 'su', 'per', 'con', 'del', 'della', 'dei', 'delle', 'degli', 'che', 'come', 'cerca', 'trova', 'mostra', 'tutti', 'tutte', 'immagine', 'immagini', 'foto', 'fotografie', 'file', 'media', 'libreria', ); $tokens = array(); foreach ( $parts as $part ) { $part = trim( (string) $part, "-_ \t" ); if ( $part === '' || mb_strlen( $part ) < 2 ) { continue; } if ( in_array( $part, $stop, true ) ) { continue; } $tokens[ $part ] = $part; } return array_values( $tokens ); } /** * Expand a token with light stemming and EN/IT synonyms for common subjects. * * @return list */ private function token_variants( string $token ): array { $variants = array( $token ); // Simple English plurals / stems. if ( str_ends_with( $token, 'ies' ) && mb_strlen( $token ) > 4 ) { $variants[] = mb_substr( $token, 0, -3 ) . 'y'; } elseif ( str_ends_with( $token, 'es' ) && mb_strlen( $token ) > 3 ) { $variants[] = mb_substr( $token, 0, -2 ); $variants[] = mb_substr( $token, 0, -1 ); } elseif ( str_ends_with( $token, 's' ) && ! str_ends_with( $token, 'ss' ) && mb_strlen( $token ) > 2 ) { $variants[] = mb_substr( $token, 0, -1 ); } else { $variants[] = $token . 's'; $variants[] = $token . 'es'; } $synonyms = array( 'child' => array( 'children', 'kid', 'kids', 'bambino', 'bambini', 'bimbo', 'bimbi' ), 'children' => array( 'child', 'kid', 'kids', 'bambino', 'bambini', 'bimbo', 'bimbi' ), 'kid' => array( 'kids', 'child', 'children', 'bambino', 'bambini' ), 'kids' => array( 'kid', 'child', 'children', 'bambino', 'bambini' ), 'bambino' => array( 'bambini', 'child', 'children', 'kid', 'kids', 'bimbo', 'bimbi' ), 'bambini' => array( 'bambino', 'child', 'children', 'kid', 'kids', 'bimbo', 'bimbi' ), 'boy' => array( 'boys', 'ragazzo', 'ragazzi' ), 'girl' => array( 'girls', 'ragazza', 'ragazze' ), 'person' => array( 'people', 'persona', 'persone', 'man', 'woman', 'uomo', 'donna' ), 'people' => array( 'person', 'persona', 'persone' ), 'dog' => array( 'dogs', 'cane', 'cani' ), 'cat' => array( 'cats', 'gatto', 'gatti' ), 'car' => array( 'cars', 'auto', 'automobile', 'macchina' ), 'beach' => array( 'spiaggia' ), 'spiaggia' => array( 'beach' ), 'mountain' => array( 'mountains', 'montagna', 'montagne' ), 'food' => array( 'cibo', 'meal', 'pasto' ), 'office' => array( 'ufficio' ), 'ufficio' => array( 'office' ), ); if ( isset( $synonyms[ $token ] ) ) { foreach ( $synonyms[ $token ] as $syn ) { $variants[] = $syn; } } $unique = array(); foreach ( $variants as $v ) { $v = strtolower( trim( $v ) ); if ( $v !== '' && mb_strlen( $v ) >= 2 ) { $unique[ $v ] = $v; } } return array_values( $unique ); } /** * Authors that have at least one attachment (for library filter). * * @return list */ public function list_attachment_authors(): array { global $wpdb; $rows = $wpdb->get_results( $wpdb->prepare( "SELECT p.post_author AS id, COUNT(p.ID) AS count FROM %i p WHERE p.post_type = 'attachment' AND p.post_status = 'inherit' AND p.post_author > 0 GROUP BY p.post_author ORDER BY count DESC, p.post_author ASC LIMIT 200", $wpdb->posts ), ARRAY_A ) ?: array(); $out = array(); foreach ( $rows as $row ) { $id = (int) ( $row['id'] ?? 0 ); if ( $id <= 0 ) { continue; } $user = get_userdata( $id ); $out[] = array( 'id' => $id, 'name' => $user ? (string) $user->display_name : sprintf( /* translators: %d: user ID */ __( 'User #%d', 'foldrocket' ), $id ), 'count' => (int) ( $row['count'] ?? 0 ), ); } return $out; } /** * @param mixed $value * @return list */ private function split_list_param( $value ): array { if ( is_array( $value ) ) { $tokens = array_map( 'strval', $value ); } else { $tokens = preg_split( '/\s*,\s*/', (string) $value ) ?: array(); } $out = array(); foreach ( $tokens as $token ) { $token = sanitize_key( trim( (string) $token ) ); if ( $token !== '' ) { $out[] = $token; } } return array_values( array_unique( $out ) ); } private function sanitize_date_param( string $value ): ?string { $value = trim( $value ); if ( ! preg_match( '/^\d{4}-\d{2}-\d{2}$/', $value ) ) { return null; } $parts = array_map( 'intval', explode( '-', $value ) ); if ( ! checkdate( $parts[1], $parts[2], $parts[0] ) ) { return null; } return sprintf( '%04d-%02d-%02d', $parts[0], $parts[1], $parts[2] ); } /** * Build a MIME WHERE fragment and append bound params. * * @param list $params */ private function mime_where_clause( string $mime, array &$params ): ?string { global $wpdb; $mime = trim( $mime ); if ( $mime === '' ) { return null; } if ( 'files' === $mime || 'file/' === $mime ) { return "p.post_mime_type NOT LIKE 'image/%' AND p.post_mime_type NOT LIKE 'video/%' AND p.post_mime_type NOT LIKE 'audio/%'"; } if ( 'other' === $mime ) { $known = array( 'image/jpeg', 'image/png', 'image/webp', 'image/gif', 'image/svg+xml', 'image/avif', 'application/pdf', 'video/mp4', 'video/webm', 'video/quicktime', 'audio/mpeg', 'audio/wav', 'audio/ogg', ); $parts = array(); foreach ( $known as $prefix ) { $parts[] = 'p.post_mime_type NOT LIKE %s'; $params[] = $wpdb->esc_like( $prefix ) . '%'; } return '(' . implode( ' AND ', $parts ) . ')'; } $params[] = $wpdb->esc_like( $mime ) . '%'; return 'p.post_mime_type LIKE %s'; } /** @return list */ private function tags_for_asset( int $asset_id ): array { if ( $asset_id <= 0 ) { return array(); } global $wpdb; $tags = \FoldRocket\Infrastructure\Database\TableNames::assert_plugin_table( \FoldRocket\Infrastructure\Database\TableNames::tags() ); $pivot = \FoldRocket\Infrastructure\Database\TableNames::assert_plugin_table( \FoldRocket\Infrastructure\Database\TableNames::asset_tags() ); $rows = $wpdb->get_results( $wpdb->prepare( 'SELECT t.canonical_slug, t.category, at.confidence FROM %i at INNER JOIN %i t ON t.id = at.tag_id WHERE at.asset_id = %d', $pivot, $tags, $asset_id ), ARRAY_A ) ?: array(); return array_map( static function ( array $row ): array { return array( 'slug' => (string) $row['canonical_slug'], 'category' => $row['category'] !== null ? (string) $row['category'] : null, 'confidence' => $row['confidence'] !== null ? (float) $row['confidence'] : null, ); }, $rows ); } }