<?php
namespace NeoLibraryFull\NeoGlobal; if (!defined("ABSPATH")) { die("neoWP direct access not allowed"); }



function db_entries_image_usage_lookup($img_urls, $max_sql_time_seconds = 10) {
    global $wpdb;


    $img_size_urls = [];
    foreach ($img_urls as $img_url) {
        $img_id = attachment_url_to_postid($img_url);
        if (!$img_id) { continue; }
        $make_img_url_relative_without_first_slash = function ($img_url) { return ltrim(str_replace(site_url(), "", $img_url), "/"); };
        $img_size_urls[$make_img_url_relative_without_first_slash($img_url)] = $img_url;
        $img_meta = wp_get_attachment_metadata($img_id);
        if (isset($img_meta["sizes"])) {
            foreach ($img_meta["sizes"] as $img_size => $img_size_meta) {
                $absolute_size_url = substr($img_url, 0, strrpos($img_url, "/")) . "/" . $img_size_meta["file"];
                $img_size_urls[$make_img_url_relative_without_first_slash($absolute_size_url)] = $img_url;
            }
        }
    }


    $img_size_urls_with_json = [];
    foreach ($img_size_urls as $relative_img_size_url => $absolute_img_url) {
        $img_url_json_escaped = trim(json_encode($relative_img_size_url), '"');
        $img_size_urls_with_json[$img_url_json_escaped] = $absolute_img_url;
    }
    $img_size_urls = array_merge($img_size_urls, $img_size_urls_with_json);
    if (empty($img_size_urls)) { return []; }


    $regexp_any_size_img_url = implode("|", array_map("preg_quote", array_keys($img_size_urls)));
    $max_regex_chunk_len = 32768;

    $regexp_chunks = [];
    $parts = explode("|", $regexp_any_size_img_url);
    $current = "";
    foreach ($parts as $part) {
        if ($current !== "" && strlen($current) + strlen($part) + 1 > $max_regex_chunk_len) {
            $current = $part; $regexp_chunks[] = $current;
        } else {
            $current .= ($current === "" ? "" : "|") . $part;
        }
    }
    if ($current !== "") { $regexp_chunks[] = $current; }


    $db_entries_using_images = [];
    foreach ($img_urls as $img_url) { $db_entries_using_images[$img_url] = []; }

    $timeout_set_result = $wpdb->query($wpdb->prepare("SET SESSION wait_timeout=%d;", $max_sql_time_seconds)); /* phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching */ /* As the user waits for the database call, we shorten the maximum duration in the database. The accessed information is useful but only nice-to-have for the user. */
    if ($timeout_set_result === false) { \NeoLibraryFull\NeoGlobal\throw_global_exception("Failed to set SQL query timeout."); }
    $query_start_time = microtime(true);

    foreach ($wpdb->get_results("SHOW TABLES", ARRAY_N) as $table) { /* phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching */ /* The direct database access is unavoidable because WP doesn't provide a helper function to perform this operation. */
        $table_name = $table[0];
        $already_added_rows_hashes = [];
        if (strpos($table_name, $wpdb->prefix) !== 0) { continue; }
        foreach ($wpdb->get_results("SHOW COLUMNS FROM `$table_name`") as $column) { /* phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter */ /* The direct database access is unavoidable because WP doesn't provide a helper function to perform this operation. */
            if (!(strpos($column->Type, "char") !== false || strpos($column->Type, "text") !== false)) { continue; }
            $column_name_maybe_with_img_url = $column->Field;
            if ($table_name === $wpdb->posts && $column_name_maybe_with_img_url === "guid") { continue; }

            $where_parts = [];
            $args = [];
            foreach ($regexp_chunks as $chunk) {
                $where_parts[] = "`$column_name_maybe_with_img_url` REGEXP %s";
                $args[] = $chunk;
            }
            $where_regexp = "(" . implode(" OR ", $where_parts) . ")";

                 if ($table_name === $wpdb->posts) {    $rows_containing_img_urls = $wpdb->get_results($wpdb->prepare("SELECT * FROM `$table_name`                                     WHERE $where_regexp AND post_type NOT IN ('attachment', 'revision') AND post_status NOT IN ('auto-draft', 'trash');", ...$args)); } /* phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter */ /* The variable $table_name is ok here because we exclusively loop through table names that really exist in the database. The variable $where_regexp is ok here because it is filled with escaped placeholders only a few lines before. The direct database access is unavoidable because WP doesn't provide a helper function to perform this operation. */
            else if ($table_name === $wpdb->postmeta) { $rows_containing_img_urls = $wpdb->get_results($wpdb->prepare("SELECT * FROM `$table_name` JOIN `$wpdb->posts` ON post_id = ID WHERE $where_regexp AND post_type NOT IN ('attachment', 'revision') AND post_status NOT IN ('auto-draft', 'trash');", ...$args)); } /* phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter */ /* The variable $table_name is ok here because we exclusively loop through table names that really exist in the database. The variable $where_regexp is ok here because it is filled with escaped placeholders only a few lines before. The direct database access is unavoidable because WP doesn't provide a helper function to perform this operation. */
            else {                                      $rows_containing_img_urls = $wpdb->get_results($wpdb->prepare("SELECT * FROM `$table_name`                                     WHERE $where_regexp;", ...$args)); }                                                                                                /* phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter */ /* The variable $table_name is ok here because we exclusively loop through table names that really exist in the database. The variable $where_regexp is ok here because it is filled with escaped placeholders only a few lines before. The direct database access is unavoidable because WP doesn't provide a helper function to perform this operation. */
            if ($rows_containing_img_urls === false) { return false; }

            foreach ($rows_containing_img_urls as $row) {

                $row_hash = md5(serialize($row));
                if (in_array($row_hash, $already_added_rows_hashes)) { continue; }
                $already_added_rows_hashes[] = $row_hash;

                foreach ($img_size_urls as $relative_img_size_url => $absolute_img_url) {
                    if (strpos($row->$column_name_maybe_with_img_url, $relative_img_size_url) === false) { continue; }

                    $row_preview = [];
                    foreach ($row as $col_name_for_preview => $value) {

                        $number_of_preview_chars = 256;
                        $includes_img_url = $col_name_for_preview == $column_name_maybe_with_img_url;
                        $img_url_pos = $includes_img_url ? mb_strpos($value, $relative_img_size_url, 0, "UTF-8") : 0;
                        $start_pos = $includes_img_url ? max(0, $img_url_pos - $number_of_preview_chars / 2) : 0;
                        $end_pos = $includes_img_url ? min(mb_strlen($value, "UTF-8"), $img_url_pos + mb_strlen($relative_img_size_url, "UTF-8") + $number_of_preview_chars / 2) : $number_of_preview_chars;
                        $preview = mb_substr($value, $start_pos, $end_pos - $start_pos, "UTF-8");
                        if ($start_pos > 0) $preview = "…" . mb_substr($preview, 1, null, "UTF-8");
                        if (mb_strlen($value, "UTF-8") > $end_pos) $preview = mb_substr($preview, 0, -1, "UTF-8") . "…";
                        $row_preview[$col_name_for_preview] = $preview;
                    }

                    $db_entries_using_images[$absolute_img_url][] = ["tableName" => $table_name, "contentPreview" => $row_preview];
                }
            }

            if (microtime(true) - $query_start_time > $max_sql_time_seconds) { return false; }
        }
    }


    foreach ($img_urls as $img_url) {
        $img_id = attachment_url_to_postid($img_url);
        if (!$img_id) { continue; }
        $rows_containing_img_id = $wpdb->get_results($wpdb->prepare("SELECT * FROM `$wpdb->postmeta` WHERE meta_key = '_thumbnail_id' AND meta_value = %d;", $img_id)); /* phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching */ /* The direct database access is unavoidable because WP doesn't provide a helper function to perform this operation. */
        if ($rows_containing_img_id === false) { continue; }
        foreach ($rows_containing_img_id as $row) {
            $db_entries_using_images[$img_url][] = [
                "tableName" => $wpdb->postmeta,
                "contentPreview" => ["post_id" => $row->post_id, "meta_key" => $row->meta_key, "meta_value" => $row->meta_value],
            ];
        }
    }


    $options_with_img_id = [
        "site_icon" => get_option("site_icon"),
        "site_logo" => get_option("site_logo"),
    ];
    foreach ($img_urls as $img_url) {
        $img_id = attachment_url_to_postid($img_url);
        if (!$img_id) { continue; }
        foreach ($options_with_img_id as $option_name => $option_img_id) {
            if ($option_img_id == $img_id) {
                $db_entries_using_images[$img_url][] = [
                    "tableName" => "wp_options",
                    "contentPreview" => ["option_name" => $option_name, "option_value" => $img_id],
                ];
            }
        }
    }


    foreach ($img_urls as $img_url) {





        $img_id = attachment_url_to_postid($img_url);
        if (!$img_id) { continue; }
        $rows_containing_img_id = $wpdb->get_results($wpdb->prepare("SELECT * FROM `$wpdb->posts` acf_field_post JOIN `$wpdb->postmeta` postmeta ON acf_field_post.post_excerpt = postmeta.meta_key JOIN `$wpdb->posts` post ON postmeta.post_id = post.ID WHERE postmeta.meta_value = %d AND acf_field_post.post_type = 'acf-field' AND post.post_type NOT IN ('attachment', 'revision') AND post.post_status NOT IN ('auto-draft', 'trash');", $img_id)); /* phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching */ /* The direct database access is unavoidable because WP doesn't provide a helper function to perform this operation. */
        if ($rows_containing_img_id === false) { continue; }
        foreach ($rows_containing_img_id as $row) {
            $db_entries_using_images[$img_url][] = [
                "tableName" => $wpdb->postmeta,
                "contentPreview" => ["post_id" => $row->post_id, "meta_key" => $row->meta_key, "meta_value" => $row->meta_value],
            ];
        }
    }


    foreach ($db_entries_using_images as $img_url => &$db_entries_using_img) {
        foreach ($db_entries_using_img as &$db_entry) {

            $row_preview_post = false;
            if ($db_entry["tableName"] === $wpdb->posts) {
                $row_preview_post = get_post($db_entry["contentPreview"]["ID"]);
            } else if ($db_entry["tableName"] === $wpdb->postmeta) {
                $row_preview_post = get_post($db_entry["contentPreview"]["post_id"]);
            }

            if ($row_preview_post !== false) {
                $db_entry["postUrl"]   = get_permalink($row_preview_post);
                $db_entry["postId"]    = $row_preview_post->ID;
                $db_entry["postTitle"] = $row_preview_post->post_title;
            } else {
                $db_entry["postUrl"]   = null;
                $db_entry["postId"]    = null;
                $db_entry["postTitle"] = null;
            }
        }
    }


    foreach ($db_entries_using_images as $img_url => &$db_entries_using_img) {
        $db_entries_using_img = \NeoLibraryFull\NeoGlobal\array_filter_better($db_entries_using_img, function($db_entry) {
            return !($db_entry["tableName"] === "wp_options" && $db_entry["contentPreview"]["option_name"] === "WpFcDeleteCacheLogs");
        });
    }


    return $db_entries_using_images;
}
