<?php

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

class AIEO_Sql_Security {

    const OPTION = 'aieo_sql_security'; 

    
    public static function converge() {
        global $wpdb;
        $done = array('routines' => 0, 'views' => 0, 'failed' => array());
        $prev = $wpdb->suppress_errors(true);

        foreach (self::definer_routines() as $routine) {
            if (false !== $wpdb->query("ALTER {$routine['type']} `{$routine['name']}` SQL SECURITY INVOKER")) {
                $done['routines']++;
            } else {
                $done['failed'][$routine['name']] = (string) $wpdb->last_error;
            }
        }

        foreach (self::definer_views() as $view) {
            $sql = self::invoker_definition($view);
            if ('' !== $sql && false !== $wpdb->query($sql)) {
                $done['views']++;
            } else {
                $done['failed'][$view] = '' !== $sql ? (string) $wpdb->last_error : 'its definition could not be read';
            }
        }

        $wpdb->suppress_errors($prev);
        if ($done['routines'] || $done['views'] || $done['failed']) {
            update_option(self::OPTION, array('checked_at' => time()) + $done, false);
            if (function_exists('aieo_debug_log')) {
                aieo_debug_log(sprintf(
                    'AIEO SQL security: %d routines and %d views now run as their caller%s',
                    $done['routines'],
                    $done['views'],
                    $done['failed'] ? '; refused: ' . implode(', ', array_keys($done['failed'])) : ''
                ));
            }
        }
        return $done;
    }

    
    public static function definer_routines() {
        global $wpdb;
        $found = array();
        $rows  = (array) $wpdb->get_results(
            "SELECT ROUTINE_TYPE, ROUTINE_NAME FROM information_schema.ROUTINES
             WHERE ROUTINE_SCHEMA = DATABASE() AND SECURITY_TYPE = 'DEFINER' AND LOWER(ROUTINE_NAME) LIKE '%aieo%'",
            ARRAY_A
        );
        foreach ($rows as $row) {
            if (in_array($row['ROUTINE_TYPE'], array('PROCEDURE', 'FUNCTION'), true) && preg_match('/^\w+$/', (string) $row['ROUTINE_NAME'])) {
                $found[] = array('type' => $row['ROUTINE_TYPE'], 'name' => (string) $row['ROUTINE_NAME']);
            }
        }
        return $found;
    }

    
    public static function definer_views() {
        global $wpdb;
        $views = (array) $wpdb->get_col($wpdb->prepare(
            "SELECT TABLE_NAME FROM information_schema.VIEWS WHERE TABLE_SCHEMA = DATABASE() AND SECURITY_TYPE = 'DEFINER' AND TABLE_NAME LIKE %s",
            $wpdb->esc_like($wpdb->prefix . 'aieo_') . '%'
        ));
        return array_values(array_filter(array_map('strval', $views), static function ($view) {
            return (bool) preg_match('/^\w+$/', $view);
        }));
    }

    
    private static function invoker_definition($view) {
        global $wpdb;
        $row   = $wpdb->get_row("SHOW CREATE VIEW `{$view}`", ARRAY_N);
        $count = 0;
        $sql   = (string) preg_replace(
            '/^CREATE (ALGORITHM=\w+ )?DEFINER=\S+ SQL SECURITY DEFINER VIEW /',
            'CREATE OR REPLACE $1SQL SECURITY INVOKER VIEW ',
            is_array($row) && isset($row[1]) ? (string) $row[1] : '',
            1,
            $count
        );
        return 1 === $count ? $sql : '';
    }
}