<?php

if (!defined('ABSPATH')) {
    exit;
}

require_once dirname(__DIR__) . '/class-aieo-background-queue.php';

class AIEO_Analytics_Collation {

    const OPTION        = 'aieo_analytics_collation';          
    const HOOK          = 'aieo_analytics_collation_convert';  
    const INLINE_ROWS   = 50000;     
    const PAGE_BUDGET   = 15;        
    const CRON_BUDGET   = 240;       
    const STEP_BUDGET   = 20;        
    const LOCK_WAIT     = 5;         
    const BYTES_PER_SEC = 16777216;  
    const MAX_ATTEMPTS  = 5;         
    const LOG_SIZE      = 250;       

    
    public static function tables() {
        return array(
            'aieo_order_stats', 'aieo_order_products_analytics', 'aieo_order_attribution', 'aieo_reciprocal_ids',
            'aieo_store_only_products', 'aieo_sales_predictor', 'aieo_sales_predictor_history',
            'aieo_sales_predictor_accuracy', 'aieo_variations_stock_condition', 'aieo_sp_run',
            'aieo_order_invoice_match_report',
        );
    }

    
    public static function built_tables() {
        return array(
            'aieo_cumulative_order_stats', 'aieo_cumulative_order_stats_new', 'aieo_cumulative_order_stats_old',
            'aieo_order_invoice_match_report_current_month',
            'aieo_order_products_entry', 'aieo_order_products_exit', 'aieo_order_products_single_orders',
            'aieo_order_products_freq_entry', 'aieo_order_products_freq_exit',
            'aieo_order_products_freq_single_item_orders',
            'aieo_sp_prev', 'aieo_sp_pstock', 'aieo_sp_pw', 'aieo_sp_queue', 'aieo_sp_vit',
            'aieo_adv_stats_orders_rn', 'aieo_adv_stats_orders_rn_copy',
            'aieo_product_session_metrics', 'aieo_product_session_metrics_new', 'aieo_product_session_metrics_old',
        );
    }

    
    public static function other_tables() {
        return array('store_order_status_lookup', 'rooster_agent_log');
    }

    
    const WHOLE_OPTION = 'aieo_collation_whole_db';

    
    public static function best_collation() {
        global $wpdb;
        static $memo = null;
        if (null === $memo) {
            $found = $wpdb->get_var("SHOW COLLATION WHERE Collation IN ('uca1400_ai_ci', 'utf8mb4_uca1400_ai_ci')");
            $memo  = null !== $found && '' !== (string) $found ? 'utf8mb4_uca1400_ai_ci' : '';
        }
        return $memo;
    }

    
    private static function left_in(array $state) {
        foreach (array('status', 'site_status', 'wp_status') as $key) {
            if (isset($state[$key]) && 'pending' === $state[$key]) {
                return true;
            }
        }
        return false;
    }

    
    public static function start_whole_database() {
        if ('' === self::best_collation()) {
            return array('ok' => false, 'left' => 0);
        }
        update_option(self::WHOLE_OPTION, '1', false);
        $state = get_option(self::OPTION, array());
        if (is_array($state)) {
            foreach ((array) ($state['tables'] ?? array()) as $table => $rec) {
                if (is_array($rec) && 'failed' === ($rec['state'] ?? '')) {
                    $state['tables'][$table] = array_merge($rec, array('state' => 'waiting', 'attempts' => 0));
                }
            }
            unset($state['checked_at']);
            update_option(self::OPTION, $state, false);
        }
        return self::whole_database_step();
    }

    
    public static function whole_database_step() {
        if ('' === self::best_collation() || !self::whole_database()) {
            return array('ok' => false, 'left' => 0);
        }
        self::converge('step');
        $left = self::left_count();
        if ($left > 0) {
            self::heal();
        }
        return array('ok' => true, 'left' => $left);
    }

    
    public static function left_count() {
        return count(self::left_tables());
    }

    
    public static function left_tables() {
        $out = array();
        foreach (self::targets() as $family => $target) {
            if ('' === $target) {
                continue;
            }
            foreach (self::plan($family, $target, self::protect($target)) as $table => $info) {
                $out[$table] = (int) $info['bytes'];
            }
        }
        return $out;
    }

    
    public static function whole_database_preview() {
        $best = self::best_collation();
        if ('' === $best) {
            return array();
        }
        $out = array();
        foreach (array('analytics', 'site', 'wordpress') as $family) {
            foreach (self::plan($family, $best) as $table => $info) {
                $out[$table] = (int) $info['bytes'];
            }
        }
        return $out;
    }

    
    public static function whole_database() {
        return '1' === (string) get_option(self::WHOLE_OPTION, '');
    }

    
    public static function targets() {
        $site = (string) aieo_get_wp_collation();
        $best = self::best_collation();
        if ('' !== $best && self::whole_database()) {
            return array('analytics' => $best, 'site' => $best, 'wordpress' => $best);
        }
        return array(
            'analytics' => aieo_analytics_target_collation(),
            'site'      => 0 === strpos($site, 'utf8mb4_') ? $site : '',
            'wordpress' => '',
        );
    }

    
    public static function protect($target) {
        $best = self::best_collation();
        return '' !== $best && $target !== $best ? $best : '';
    }

    
    public static function pending() {
        $state = get_option(self::OPTION, array());
        return is_array($state) && isset($state['status']) && 'pending' === $state['status'];
    }

    
    public static function plan($family, $target, $protect = '') {
        global $wpdb;
        $own = array();
        foreach (self::tables() as $table) {
            $own[] = $wpdb->prefix . $table;
        }
        if ('analytics' === $family) {
            $where = 'TABLE_NAME IN (' . implode(', ', array_fill(0, count($own), '%s')) . ')';
            $args  = $own;
        } elseif ('wordpress' === $family) {
            $other = array();
            foreach (self::other_tables() as $table) {
                $other[] = $wpdb->prefix . $table;
            }
            $where = 'TABLE_NAME LIKE %s AND TABLE_NAME NOT LIKE %s AND TABLE_NAME NOT IN (' . implode(', ', array_fill(0, count($other), '%s')) . ')';
            $args  = array_merge(array($wpdb->esc_like($wpdb->prefix) . '%', $wpdb->esc_like($wpdb->prefix . 'aieo_') . '%'), $other);
        } else {
            $skip = $own;
            foreach (self::built_tables() as $table) {
                $skip[] = $wpdb->prefix . $table;
            }
            $other = array();
            foreach (self::other_tables() as $table) {
                $other[] = $wpdb->prefix . $table;
            }
            $where = '(TABLE_NAME LIKE %s OR TABLE_NAME IN (' . implode(', ', array_fill(0, count($other), '%s')) . '))'
                . ' AND TABLE_NAME NOT IN (' . implode(', ', array_fill(0, count($skip), '%s')) . ')';
            $args  = array_merge(array($wpdb->esc_like($wpdb->prefix . 'aieo_') . '%'), $other, $skip);
        }
        $tables  = $wpdb->get_results($wpdb->prepare(
            "SELECT TABLE_NAME AS t, TABLE_COLLATION AS c, TABLE_ROWS AS r, DATA_LENGTH + INDEX_LENGTH AS b
               FROM INFORMATION_SCHEMA.TABLES
              WHERE TABLE_SCHEMA = DATABASE() AND TABLE_TYPE = 'BASE TABLE' AND {$where}",
            $args
        ));
        $columns = $wpdb->get_results($wpdb->prepare(
            "SELECT TABLE_NAME AS t, COLUMN_NAME AS n, CHARACTER_SET_NAME AS cs, COLLATION_NAME AS c
               FROM INFORMATION_SCHEMA.COLUMNS
              WHERE TABLE_SCHEMA = DATABASE() AND COLLATION_NAME IS NOT NULL AND {$where}
              ORDER BY TABLE_NAME, ORDINAL_POSITION",
            $args
        ));
        $by_table = array();
        foreach ((array) $columns as $col) {
            $by_table[(string) $col->t][] = $col;
        }
        $plan = array();
        foreach ((array) $tables as $row) {
            $default = (string) $row->c;
            $from    = '';
            $stale   = array();
            $kept    = array();
            foreach (isset($by_table[(string) $row->t]) ? $by_table[(string) $row->t] : array() as $col) {
                if (self::deliberate((string) $col->cs, (string) $col->c)) {
                    $kept[] = (string) $col->n;
                } elseif ('' !== $protect && (string) $col->c === $protect && $protect !== $target) {
                    $kept[] = (string) $col->n; 
                } elseif ((string) $col->c !== $target) {
                    $stale[] = (string) $col->n;
                    $from    = '' !== $from ? $from : (string) $col->c;
                }
            }
            $own_default = $default !== $target && '' !== $default && ('' === $protect || $default !== $protect) && !self::deliberate(substr($default, 0, (int) strpos($default, '_')), $default);
            if (!$stale && !$own_default) {
                continue;
            }
            $plan[(string) $row->t] = array(
                'rows'    => (int) $row->r,
                'bytes'   => (int) $row->b,
                'from'    => $own_default ? $default : $from,
                'columns' => $stale,
                'kept'    => $kept,
                'default' => $own_default,
            );
        }
        uasort($plan, static function ($a, $b) { return $a['bytes'] <=> $b['bytes']; });
        return $plan;
    }

    
    public static function converge($context = 'page') {
        $state = get_option(self::OPTION, array());
        $state = is_array($state) ? $state : array();
        
        $busy = self::left_in($state);
        if ('page' === $context && $busy && isset($state['checked_at']) && time() - (int) $state['checked_at'] < MINUTE_IN_SECONDS) {
            return !self::pending();
        }

        $targets = self::targets();
        $state   = array_merge(array('tables' => array(), 'log' => array()), $state, array('target' => $targets['analytics'], 'site_target' => $targets['site'], 'wp_target' => $targets['wordpress'], 'checked_at' => time()));
        $budget  = 'cron' === $context ? self::CRON_BUDGET : ('step' === $context ? self::STEP_BUDGET : self::page_budget());
        $inline  = (int) apply_filters('aieo_analytics_collation_inline_rows', self::INLINE_ROWS);
        $t0      = microtime(true);
        $left    = array('analytics' => false, 'site' => false, 'wordpress' => false);
        $did     = 0;

        foreach ($targets as $family => $target) {
            if ('' === $target) {
                continue;
            }
            foreach (self::plan($family, $target, self::protect($target)) as $table => $info) {
                $rec = isset($state['tables'][$table]) && is_array($state['tables'][$table]) ? $state['tables'][$table] : array('attempts' => 0);
                $rec = array_merge(array('attempts' => 0), $rec, array('family' => $family));
                if ((int) $rec['attempts'] >= self::MAX_ATTEMPTS) {
                    continue;
                }
                $estimate = $info['bytes'] / self::BYTES_PER_SEC;
                $spent    = microtime(true) - $t0;
                $fits     = $spent + $estimate <= $budget || ('page' !== $context && 0 === $did);
                if (('page' === $context && $info['rows'] > $inline) || !$fits) {
                    $left[$family] = true;
                    if (!isset($rec['state']) || 'queued' !== $rec['state']) {
                        $state['tables'][$table] = array_merge($rec, array('state' => 'queued', 'from' => $info['from'], 'rows' => $info['rows']));
                        self::log($state, "{$table}: {$info['rows']} rows in {$info['from']}, left to the background step");
                    }
                    continue;
                }
                if ('page' !== $context && function_exists('set_time_limit')) {
                    @set_time_limit((int) ceil($estimate) + self::CRON_BUDGET); 
                }
                $did++;
                $result = self::convert($table, $target, $info);
                $rec    = array_merge($rec, array('from' => $info['from'], 'rows' => $info['rows'], 'secs' => $result['secs'], 'at' => gmdate('Y-m-d H:i:s')));
                if ('' === $result['error']) {
                    if ('analytics' === $family) {
                        
                        delete_option('aieo_analytics_jobs_sproc_version');
                    }
                    $state['tables'][$table] = array_merge($rec, array('state' => 'converted', 'error' => '', 'kept' => $info['kept']));
                    $kept = $info['kept'] ? ', its ' . implode(', ', $info['kept']) . ' kept as declared' : '';
                    self::log($state, "{$table}: {$info['from']} to {$target}, {$info['rows']} rows, {$result['secs']} s{$kept}");
                    continue;
                }
                $rec['attempts'] = (int) $rec['attempts'] + 1;
                $gave_up         = $rec['attempts'] >= self::MAX_ATTEMPTS;
                $state['tables'][$table] = array_merge($rec, array('state' => $gave_up ? 'failed' : 'waiting', 'error' => $result['error']));
                self::log($state, "{$table}: not converted ({$result['error']})" . ($gave_up ? ', left as it is after ' . self::MAX_ATTEMPTS . ' attempts' : ', tried again on the next run'));
                $left[$family] = $left[$family] || !$gave_up;
            }
        }

        $state['status']      = $left['analytics'] ? 'pending' : 'done';
        $state['site_status'] = $left['site'] ? 'pending' : 'done';
        $state['wp_status']   = $left['wordpress'] ? 'pending' : 'done';
        update_option(self::OPTION, $state, false);
        if (!self::left_in($state)) {
            AIEO_Background_Queue::clear(self::HOOK);
        } else {
            
            
            AIEO_Background_Queue::queue(self::HOOK, MINUTE_IN_SECONDS);
        }
        return !$left['analytics'];
    }

    
    public static function ensure_binary_columns($table, array $columns) {
        global $wpdb;
        if (!$columns || !preg_match('/^[A-Za-z0-9_]+$/', (string) $table)) {
            return array();
        }
        $names = array_keys($columns);
        $now   = $wpdb->get_results($wpdb->prepare(
            'SELECT COLUMN_NAME AS n, COLLATION_NAME AS c FROM INFORMATION_SCHEMA.COLUMNS
              WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME IN (' . implode(', ', array_fill(0, count($names), '%s')) . ')',
            array_merge(array($table), $names)
        ));
        $modify  = array();
        $changed = array();
        foreach ((array) $now as $col) {
            $name = (string) $col->n;
            if ('utf8mb4_bin' === (string) $col->c || !preg_match('/^[A-Za-z0-9_]+$/', $name) || false === stripos($columns[$name], 'utf8mb4_bin')) {
                continue;
            }
            $modify[]  = "MODIFY `{$name}` {$columns[$name]}";
            $changed[] = $name;
        }
        if ($modify) {
            $wpdb->query("ALTER TABLE `{$table}` " . implode(', ', $modify));
        }
        return $changed;
    }

    
    public static function heal() {
        $state = get_option(self::OPTION, array());
        if (!is_array($state)) {
            return;
        }
        if (self::left_in($state) && !AIEO_Background_Queue::queued(self::HOOK)) {
            AIEO_Background_Queue::queue(self::HOOK, MINUTE_IN_SECONDS);
        }
    }

    
    private static function deliberate($charset, $collation) {
        return 'utf8mb4' !== $charset || '_bin' === substr($collation, -4);
    }

    
    private static function convert($table, $target, array $info) {
        global $wpdb;
        
        
        if (!preg_match('/^\w+$/', (string) $table) || !preg_match('/^\w+$/', (string) $target)) {
            return array('error' => 'not an identifier', 'secs' => 0.0);
        }
        $suppress = $wpdb->suppress_errors(true);
        $spec     = $info['kept'] ? self::modify_spec($table, $target, $info['columns'], $info['default']) : "CONVERT TO CHARACTER SET utf8mb4 COLLATE {$target}";
        if ('' === $spec) {
            $wpdb->suppress_errors($suppress);
            return array('error' => 'its column definitions could not be read', 'secs' => 0.0);
        }
        $wait = (int) $wpdb->get_var('SELECT @@SESSION.lock_wait_timeout');
        $wpdb->query($wpdb->prepare('SET SESSION lock_wait_timeout = %d', (int) apply_filters('aieo_analytics_collation_lock_wait', self::LOCK_WAIT)));
        $t0    = microtime(true);
        $error = '';
        
        
        foreach (array(', LOCK=NONE', ', LOCK=SHARED', '') as $lock) {
            $wpdb->query("ALTER TABLE `{$table}` {$spec}{$lock}");
            $error = (string) $wpdb->last_error;
            if ('' === $error || false === stripos($error, 'is not supported')) {
                break;
            }
        }
        $secs = round(microtime(true) - $t0, 2);
        if ($wait > 0) {
            $wpdb->query($wpdb->prepare('SET SESSION lock_wait_timeout = %d', $wait));
        }
        $wpdb->suppress_errors($suppress);
        return array('error' => $error, 'secs' => $secs);
    }

    
    private static function modify_spec($table, $target, array $columns, $default) {
        global $wpdb;
        $parts = $default ? array("DEFAULT CHARACTER SET utf8mb4 COLLATE {$target}") : array();
        if ($columns) {
            $create = $wpdb->get_row("SHOW CREATE TABLE `{$table}`", ARRAY_N);
            $types  = array();
            foreach ((array) $wpdb->get_results($wpdb->prepare(
                'SELECT COLUMN_NAME AS n, COLUMN_TYPE AS ty FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s',
                $table
            )) as $col) {
                $types[(string) $col->n] = (string) $col->ty;
            }
            $done = 0;
            foreach (preg_split('/\R/', isset($create[1]) ? (string) $create[1] : '') as $line) {
                if (!preg_match('/^\s*`([^`]+)` (.+?),?$/', $line, $m) || !in_array($m[1], $columns, true)) {
                    continue;
                }
                $type = isset($types[$m[1]]) ? $types[$m[1]] : '';
                if ('' === $type || 0 !== strpos($m[2], $type)) {
                    return '';
                }
                $rest    = (string) preg_replace('/^( CHARACTER SET \w+)?( COLLATE \w+)?/', '', substr($m[2], strlen($type)));
                $parts[] = "MODIFY `{$m[1]}` {$type} CHARACTER SET utf8mb4 COLLATE {$target}{$rest}";
                $done++;
            }
            if ($done !== count($columns)) {
                return '';
            }
        }
        return implode(', ', $parts);
    }

    
    private static function page_budget() {
        $max     = (int) ini_get('max_execution_time');
        $started = isset($_SERVER['REQUEST_TIME_FLOAT']) ? (float) $_SERVER['REQUEST_TIME_FLOAT'] : microtime(true);
        $budget  = (float) self::PAGE_BUDGET;
        if ($max > 0) {
            $budget = min($budget, $max - (microtime(true) - $started) - 10);
        }
        return max(0.0, $budget);
    }

    
    private static function log(array &$state, $line) {
        $state['log'][] = gmdate('Y-m-d H:i:s') . ' ' . $line;
        $state['log']   = array_slice($state['log'], -self::LOG_SIZE);
        if (function_exists('aieo_debug_log')) {
            aieo_debug_log('AIEO table collation: ' . $line);
        }
    }
}