<?php
/**
 * DbCollector — diagnostic read-only stats from WordPress core tables
 * (wp_options, wp_posts) for the audit. Every table/database name is bound as
 * an SQL identifier via the %i placeholder (WordPress 6.2+) and every value
 * (including LIKE patterns) with %s through $wpdb->prepare().
 *
 * These are read-only diagnostic queries that have no persistent cache (a stale
 * snapshot would misreport DB health). Only the non-injection DirectDatabaseQuery
 * sniffs are suppressed file-wide:
 *
 * phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Read-only diagnostic snapshots of core tables for the audit, where a stale cache would misreport DB health. Non-injection sniffs only; identifiers bound with %i, values with %s via $wpdb->prepare(); the PreparedSQL sniffs are never suppressed.
 */

declare(strict_types=1);

namespace AetosSeo\Collectors;

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

class DbCollector
{
    /** @var \wpdb */
    private $wpdb;

    public function __construct()
    {
        global $wpdb;
        $this->wpdb = $wpdb;
    }

    /**
     * @return array<string,mixed>
     */
    public function collect(): array
    {
        $wpdb = $this->wpdb;
        $options_table = $wpdb->options;
        $posts_table = $wpdb->posts;

        $autoload_bytes = (int) $wpdb->get_var($wpdb->prepare("SELECT SUM(LENGTH(option_value)) FROM %i WHERE autoload='yes'", $options_table));
        $top_autoload = $wpdb->get_results(
            $wpdb->prepare("SELECT option_name, LENGTH(option_value) AS size_bytes FROM %i WHERE autoload='yes' ORDER BY size_bytes DESC LIMIT 10", $options_table),
            ARRAY_A
        );

        $transients_count = (int) $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM %i WHERE option_name LIKE %s OR option_name LIKE %s",
                $options_table,
                '_transient_%',
                '_site_transient_%'
            )
        );

        $revisions_count = (int) $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM %i WHERE post_type='revision'", $posts_table));

        // Cron events count.
        $cron = _get_cron_array();
        $cron_events = 0;
        if (is_array($cron)) {
            foreach ($cron as $ts => $hooks) {
                if (!is_array($hooks)) {
                    continue;
                }
                foreach ($hooks as $hook => $events) {
                    if (is_array($events)) {
                        $cron_events += count($events);
                    }
                }
            }
        }

        // Table sizes (best effort).
        $table_stats = [];
        $overhead_bytes = 0;
        $db_name = (string) $wpdb->dbname;
        $prefix = $wpdb->prefix;

        // Try SHOW TABLE STATUS first (works without information_schema privileges).
        $like_pattern = $wpdb->esc_like($prefix) . '%';
        $rows = $wpdb->get_results($wpdb->prepare("SHOW TABLE STATUS FROM %i LIKE %s", $db_name, $like_pattern), ARRAY_A);
        if (is_array($rows)) {
            foreach ($rows as $r) {
                $name = (string) ($r['Name'] ?? '');
                $data_length = (int) ($r['Data_length'] ?? 0);
                $index_length = (int) ($r['Index_length'] ?? 0);
                $data_free = (int) ($r['Data_free'] ?? 0);
                $table_stats[] = [
                    'table' => $name,
                    'size_bytes' => $data_length + $index_length,
                    'data_free_bytes' => $data_free,
                ];
                $overhead_bytes += $data_free;
            }
        }

        usort($table_stats, static function ($a, $b) {
            return ($b['size_bytes'] ?? 0) <=> ($a['size_bytes'] ?? 0);
        });
        $table_stats = array_slice($table_stats, 0, 20);

        return [
            'autoload_bytes' => $autoload_bytes,
            'top_autoload_options' => is_array($top_autoload) ? $top_autoload : [],
            'transients_count' => $transients_count,
            'revisions_count' => $revisions_count,
            'cron_events_count' => $cron_events,
            'table_stats_top20' => $table_stats,
            'table_overhead_bytes' => $overhead_bytes,
        ];
    }
}