<?php

declare(strict_types=1);

namespace ShieldX\Maintenance;

defined('ABSPATH') || exit;

// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared -- Table-size reporting queries server metadata for dynamic WordPress table names.

final class DatabaseTableReporter
{
    /** @var object|null */
    private $wpdb;

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

    public static function fromWordPress(): self
    {
        global $wpdb;

        return new self($wpdb);
    }

    /**
     * @return array<string,mixed>
     */
    public function report(int $limit = 80): array
    {
        if (!is_object($this->wpdb) || !isset($this->wpdb->prefix)) {
            return [
                'available' => false,
                'database' => '',
                'prefix' => '',
                'tables' => [],
                'totals' => self::emptyTotals(),
                'error' => 'Database connection is not available.',
            ];
        }

        $prefix = (string) $this->wpdb->prefix;
        $rows = $this->informationSchemaRows($prefix, max(1, min(300, $limit)));

        return [
            'available' => true,
            'database' => $this->databaseName(),
            'prefix' => $prefix,
            'tables' => $rows,
            'totals' => $this->totals($rows),
            'error' => '',
        ];
    }

    /**
     * @return array<int,array<string,mixed>>
     */
    private function informationSchemaRows(string $prefix, int $limit): array
    {
        if (!method_exists($this->wpdb, 'prepare') || !method_exists($this->wpdb, 'get_results')) {
            return [];
        }

        $like = method_exists($this->wpdb, 'esc_like')
            ? $this->wpdb->esc_like($prefix) . '%'
            : str_replace(['%', '_'], ['\\%', '\\_'], $prefix) . '%';

        $sql = $this->wpdb->prepare(
            'SELECT table_name, engine, table_rows, data_length, index_length, data_free, update_time
             FROM information_schema.TABLES
             WHERE table_schema = DATABASE() AND table_name LIKE %s
             ORDER BY (COALESCE(data_length, 0) + COALESCE(index_length, 0)) DESC, table_name ASC
             LIMIT %d',
            $like,
            $limit
        );
        $format = defined('ARRAY_A') ? ARRAY_A : 'ARRAY_A';
        $rows = $this->wpdb->get_results($sql, $format);

        return $this->normalizeRows(is_array($rows) ? $rows : [], $prefix);
    }

    /**
     * @param array<int,array<string,mixed>> $rows
     * @return array<int,array<string,mixed>>
     */
    private function normalizeRows(array $rows, string $prefix): array
    {
        $tables = [];
        $shieldxPrefix = $prefix . 'shieldx_';

        foreach ($rows as $row) {
            $name = (string) ($row['table_name'] ?? $row['TABLE_NAME'] ?? $row['Name'] ?? '');
            if ($name === '') {
                continue;
            }

            $dataBytes = max(0, (int) ($row['data_length'] ?? $row['DATA_LENGTH'] ?? $row['Data_length'] ?? 0));
            $indexBytes = max(0, (int) ($row['index_length'] ?? $row['INDEX_LENGTH'] ?? $row['Index_length'] ?? 0));
            $freeBytes = max(0, (int) ($row['data_free'] ?? $row['DATA_FREE'] ?? $row['Data_free'] ?? 0));

            $tables[] = [
                'name' => $name,
                'engine' => (string) ($row['engine'] ?? $row['ENGINE'] ?? $row['Engine'] ?? ''),
                'rows' => max(0, (int) ($row['table_rows'] ?? $row['TABLE_ROWS'] ?? $row['Rows'] ?? 0)),
                'data_bytes' => $dataBytes,
                'index_bytes' => $indexBytes,
                'free_bytes' => $freeBytes,
                'total_bytes' => $dataBytes + $indexBytes,
                'updated_at' => (string) ($row['update_time'] ?? $row['UPDATE_TIME'] ?? $row['Update_time'] ?? ''),
                'is_shieldx' => strpos($name, $shieldxPrefix) === 0,
            ];
        }

        return $tables;
    }

    /**
     * @param array<int,array<string,mixed>> $rows
     * @return array<string,int>
     */
    private function totals(array $rows): array
    {
        $totals = self::emptyTotals();

        foreach ($rows as $row) {
            $totals['tables']++;
            $totals['rows'] += (int) ($row['rows'] ?? 0);
            $totals['data_bytes'] += (int) ($row['data_bytes'] ?? 0);
            $totals['index_bytes'] += (int) ($row['index_bytes'] ?? 0);
            $totals['free_bytes'] += (int) ($row['free_bytes'] ?? 0);
            $totals['total_bytes'] += (int) ($row['total_bytes'] ?? 0);
            if (!empty($row['is_shieldx'])) {
                $totals['shieldx_tables']++;
                $totals['shieldx_total_bytes'] += (int) ($row['total_bytes'] ?? 0);
            }
        }

        return $totals;
    }

    /**
     * @return array<string,int>
     */
    private static function emptyTotals(): array
    {
        return [
            'tables' => 0,
            'shieldx_tables' => 0,
            'rows' => 0,
            'data_bytes' => 0,
            'index_bytes' => 0,
            'free_bytes' => 0,
            'total_bytes' => 0,
            'shieldx_total_bytes' => 0,
        ];
    }

    private function databaseName(): string
    {
        if (!method_exists($this->wpdb, 'get_var')) {
            return '';
        }

        $database = $this->wpdb->get_var('SELECT DATABASE()');

        return is_scalar($database) ? (string) $database : '';
    }
}
