<?php
// Silence may be golden, but sometimes words paint the canvas of understanding.

namespace Plugli\Linksy\SearchAndReplace\Admin\Partials\SearchAndReplace\Helpers;

use Linksy\SearchAndReplace\Plugli\Standard\Helpers\Arr;
use Linksy\SearchAndReplace\Plugli\Standard\Helpers\Str;


class DatabaseReplace extends Helpers
{
    public function get($search_mode, $filter_query, $selected_tables = [])
    {
        global $wpdb;
        $data = [];

        if (empty($selected_tables)) {
            return [];
        }

        if (!empty($this->filters['table']) ) {
            $selected_tables = [$this->filters['table']];
        }

        $search_data = $this->prepareSearchValue($search_mode, $this->search);
        
        if ($search_data === null && $search_mode === 'STARTS_ENDS_WITH') {
            return [];
        }

        $filters = $this->extractFilters();

        foreach ($selected_tables as $table_name) {
            $columns_data = $wpdb->get_results("SHOW COLUMNS FROM `$table_name`", ARRAY_A);
            if (empty($columns_data)) {
                continue;
            }
            $primary_key = $columns_data[0]['Field'];

            $matching_columns = [];
            $columns_results = [];

            foreach ($columns_data as $column) {
                $col_name = $column['Field'];
                $results = $this->applyRowMatchingLogic($table_name, $search_mode, $search_data, $filters, $primary_key, $col_name);

                if (!empty($results)) {
                    $sliced_results = array_slice($results, 0, 10);
                    $matching_columns[] = $col_name;
                    $columns_results[] = [
                        'column_name' => $col_name,
                        'results' => $sliced_results,
                        'current_results_length' => count($sliced_results),
                        'result_length' => count($results)
                    ];
                }
            }

            if (!empty($matching_columns)) {
                $data[$table_name] = [
                    'table_name' => $table_name,
                    'matching_columns' => $matching_columns,
                    'columns_results' => $columns_results,
                ];
            }
        }

        return $data;
    }

    public function getNextItems($search_mode, $item)
    {
        global $wpdb;
        $sliced_results = [];

        $offset = $item->offset;

        $table_name = $item->table_name;
        $column_name = $item->column_name;

        $search_data = $this->prepareSearchValue($search_mode, $this->search);
        if ($search_data === null && $search_mode === 'STARTS_ENDS_WITH') {
            return [];
        }

        $extracted_filters = $this->extractFilters();

        $columns_data = $wpdb->get_results("SHOW COLUMNS FROM `$table_name`", ARRAY_A);
        if (empty($columns_data)) {
            return [];
        }
        $primary_key = $columns_data[0]['Field'];

        $results = $this->applyRowMatchingLogic($table_name, $search_mode, $search_data, $extracted_filters, $primary_key, $column_name);

        if (!empty($results)) {
            $sliced_results = array_slice($results, $offset, 10);
        }

        return $sliced_results;
    }

    private function prepareSearchValue($search_mode, $search_term)
    {
        global $wpdb;
        $search_value = $wpdb->esc_like($search_term);
        $suffix = '';

        switch ($search_mode) {
            case 'STARTS_ENDS_WITH':
                $search = json_decode($search_term);
                if (is_array($search) && count($search) === 2) {
                    $search_value = $wpdb->esc_like($search[0]);
                    $suffix = $wpdb->esc_like($search[1]);
                    return ['value' => $search_value, 'suffix' => $suffix, 'mode' => 'STARTS_ENDS_WITH'];
                } else {
                    return null;
                }
                break;
            case 'CONTAINS':
            default:
                $search_value = '%' . $search_value . '%';
                return ['value' => $search_value, 'suffix' => $suffix, 'mode' => 'CONTAINS'];
        }
    }

    private function extractFilters($filters_param = null)
    {
        $filters = $this->filters;
        if (is_array($filters_param)) {
            $filters = $filters_param;
        }
        return [
            'case_sensitive' => !empty($filters['caseSensitive']),
            'full_match' => !empty($filters['fullMatch']),
            'ignore_img_urls' => !empty($filters['ignoreImgUrls']),
            'ignore_guids' => !empty($filters['ignoreGuids']),
        ];
    }

    private function applyRowMatchingLogic($table_name, $search_mode, $search_data, $filters, $primary_key, $col_name)
    {
        global $wpdb;
        $results = [];
        $case_sensitive = $filters['case_sensitive'];
        $full_match = $filters['full_match'];
        $ignore_img_urls = $filters['ignore_img_urls'];
        $ignore_guids = $filters['ignore_guids'];

        if ($ignore_guids && $table_name === 'wp_posts' && $col_name === 'guid') {
            return $results;
        }

        $search_term_raw = isset($this->search) ? $this->search : (isset($search_data['value']) ? trim($search_data['value'], '%') : ''); // Fallback

        $db_table_name = $table_name;

        if ($search_mode === 'STARTS_ENDS_WITH' && $search_data) {
            $pattern = $search_data['value'] . '%' . $search_data['suffix'];
            $query = "SELECT `$primary_key`, `$col_name` FROM `$db_table_name` WHERE " .
                    ($case_sensitive ? "`$col_name` LIKE BINARY %s" : "`$col_name` LIKE %s");
            $prepared_query = $wpdb->prepare($query, '%' . $pattern . '%');
            $rows = $wpdb->get_results($prepared_query, ARRAY_A);

            foreach ($rows as $row) {
                if (!empty($row[$col_name])) {
                    $regex = '/' . ($full_match ? '\b' : '') . preg_quote($search_data['value'], '/') . '([\w\s,]*)' . preg_quote($search_data['suffix'], '/') . ($full_match ? '\b' : '') . '/' . ($case_sensitive ? '' : 'i');
                    preg_match_all($regex, $row[$col_name], $matches);
                    if (!empty($matches[0])) {
                        foreach ($matches[0] as $match) {
                            if ($ignore_img_urls && $this->isImageUrl($match, $search_term_raw)) {
                                continue;
                            }
                            $results[] = [
                                $primary_key => $row[$primary_key],
                                'value' => $match
                            ];
                        }
                    }
                }
            }
        } else { // CONTAINS
            $query = "SELECT `$primary_key`, `$col_name` FROM `$db_table_name` WHERE " .
                    ($case_sensitive ? "`$col_name` LIKE BINARY %s" : "`$col_name` LIKE %s");
            $prepared_query = $wpdb->prepare($query, $search_data['value']);
            $rows = $wpdb->get_results($prepared_query, ARRAY_A);

            foreach ($rows as $row) {
                if (!empty($row[$col_name])) {
                    if ($full_match) {
                        $regex = '/\b' . preg_quote($search_term_raw, '/') . '\b/' . ($case_sensitive ? '' : 'i');
                        if (!preg_match($regex, $row[$col_name])) {
                            continue;
                        }
                    }
                    if ($ignore_img_urls && $this->isImageUrl($row[$col_name], $search_term_raw)) {
                        continue;
                    }
                    $results[] = [
                        $primary_key => $row[$primary_key],
                        'value' => $row[$col_name]
                    ];
                }
            }
        }
        return $results;
    }

    public function filter($data = [], $query = '')
    {
        if (!$query) {
            return $data;
        }
        $titles = [];

        foreach ($data as $item) {
            $updatedItem = null;

            if (!Arr::isMap($item)) {
                $updatedItem = Arr::filter(
                    array_map(
                        function ($e) use ($query) {
                            return $this->filterByMode($e, $query);
                        },
                        $item
                    ),
                    function ($e) {
                        return !is_null($e);
                    }
                );

                if (count($updatedItem) == 0) {
                    continue;
                }
            } else {
                $updatedItem = $this->filterByMode($item, $query);
            }

            if ($updatedItem) {
                array_push($titles, $updatedItem);
            }
        }

        return $titles;
    }

    protected function filterByMode($item, $query)
    {
        $isFound = Str::contains(strtolower($item[$this->filter_mode]), strtolower($query));
        return $isFound ? $item : false;
    }

    private function isImageUrl($value, $search)
    {
        $imageExtensions = ['jpg', 'jpeg', 'png', 'gif', 'bmp', 'svg', 'webp'];
        $imageExtensionsPattern = implode('|', $imageExtensions);
        
        $pattern = '/https?:\/\/[^\s]+\.(' . $imageExtensionsPattern . ')(\?[^\s]*)?/i';

        if (preg_match_all($pattern, $value, $matches)) {
            foreach ($matches[0] as $url) {

                if (is_array($search)) {
                    foreach ($search as $val) {
                        if (strpos($url, $val) !== false) {
                            return true;
                        }
                    }
                } else {
                    if (strpos($url, $search) !== false) {
                        return true;
                    }
                }
            }
        }

        return false;
    }
}
