<?php

namespace FleekDash\API\Performance;

/**
 * Collects database statistics (tables, sizes, cleanup candidates)
 *
 * @package FleekDash\API\Performance
 * @since 2.2.0
 */
class DatabaseStatsCollector
{
    /** @var \wpdb */
    private $wpdb;

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

    /**
     * @return array<string, mixed> Stats with wp_table_count, database_size, overhead_size, cleanup counts
     */
    public function getStats(): array
    {
        $tables = $this->fetchTables();
        $totals = $this->computeTableTotals($tables);
        $cleanup = $this->fetchCleanupCounts();
        return array_merge(
            [
                'wp_table_count'   => $totals['wp_table_count'],
                'database_size'   => $this->formatBytes($totals['total_size']),
                'overhead_size'   => $this->formatBytes($totals['total_overhead']),
            ],
            $cleanup
        );
    }

    /**
     * @return array<object>
     */
    private function fetchTables(): array
    {
        $dbName = DB_NAME;
        $tables = $this->wpdb->get_results($this->wpdb->prepare(
            "SELECT table_name AS name, engine, data_length, index_length, data_free
            FROM information_schema.tables
            WHERE table_schema = %s",
            $dbName
        ));
        $needsFallback = empty($tables) || !$this->tablesHaveSizeData($tables);
        if (!$needsFallback) {
            return $tables;
        }
        $likePattern = $this->wpdb->esc_like($this->wpdb->prefix) . '%';
        $fallbackSql = $this->wpdb->prepare(
            "SHOW TABLE STATUS FROM `" . \esc_sql($dbName) . "` LIKE %s",
            $likePattern
        );
        return $this->wpdb->get_results($fallbackSql) ?: [];
    }

    /**
     * @param array<object> $tables
     */
    private function tablesHaveSizeData(array $tables): bool
    {
        foreach ($tables as $row) {
            $dataLength = $row->data_length ?? null;
            $indexLength = $row->index_length ?? null;
            $hasData = (is_numeric($dataLength) && (int) $dataLength > 0)
                || (is_numeric($indexLength) && (int) $indexLength > 0);
            if ($hasData) {
                return true;
            }
        }
        return false;
    }

    /**
     * @param array<object> $tables
     * @return array{total_size: int, total_overhead: int, wp_table_count: int}
     */
    private function computeTableTotals(array $tables): array
    {
        $totalSize = 0;
        $totalOverhead = 0;
        $wpTableCount = 0;
        $prefix = $this->wpdb->prefix;
        foreach ($tables as $t) {
            $tableName = $t->name ?? ($t->Name ?? '');
            $dataLength = $t->data_length ?? ($t->Data_length ?? 0);
            $indexLength = $t->index_length ?? ($t->Index_length ?? 0);
            $dataFree = $t->data_free ?? ($t->Data_free ?? 0);
            $totalSize += (int) $dataLength + (int) $indexLength;
            $totalOverhead += (int) $dataFree;
            if ($tableName !== '' && strpos($tableName, $prefix) === 0) {
                $wpTableCount++;
            }
        }
        return ['total_size' => $totalSize, 'total_overhead' => $totalOverhead, 'wp_table_count' => $wpTableCount];
    }

    /**
     * @return array<string, int>
     */
    private function fetchCleanupCounts(): array
    {
        $options = $this->wpdb->prefix . 'options';
        $posts = $this->wpdb->prefix . 'posts';
        $postmeta = $this->wpdb->prefix . 'postmeta';
        $comments = $this->wpdb->prefix . 'comments';
        $commentmeta = $this->wpdb->prefix . 'commentmeta';
        $termRel = $this->wpdb->prefix . 'term_relationships';
        $now = time();

        $expiredTransients = (int) $this->wpdb->get_var($this->wpdb->prepare(
            "SELECT COUNT(*) FROM {$options} WHERE option_name LIKE '_transient_timeout_%' AND option_value < %d",
            $now
        ));
        return [
            'expired_transients'         => $expiredTransients,
            'post_revisions'             => (int) $this->wpdb->get_var("SELECT COUNT(*) FROM {$posts} WHERE post_type='revision'"),
            'auto_drafts'                => (int) $this->wpdb->get_var("SELECT COUNT(*) FROM {$posts} WHERE post_status='auto-draft'"),
            'trash_posts'                => (int) $this->wpdb->get_var("SELECT COUNT(*) FROM {$posts} WHERE post_status='trash'"),
            'spam_comments'              => (int) $this->wpdb->get_var("SELECT COUNT(*) FROM {$comments} WHERE comment_approved='spam'"),
            'trash_comments'             => (int) $this->wpdb->get_var("SELECT COUNT(*) FROM {$comments} WHERE comment_approved='trash'"),
            'orphan_postmeta'            => (int) $this->wpdb->get_var("SELECT COUNT(pm.meta_id) FROM {$postmeta} pm LEFT JOIN {$posts} p ON p.ID = pm.post_id WHERE p.ID IS NULL"),
            'orphan_commentmeta'         => (int) $this->wpdb->get_var("SELECT COUNT(cm.meta_id) FROM {$commentmeta} cm LEFT JOIN {$comments} c ON c.comment_ID = cm.comment_id WHERE c.comment_ID IS NULL"),
            'orphan_term_relationships' => (int) $this->wpdb->get_var("SELECT COUNT(tr.object_id) FROM {$termRel} tr LEFT JOIN {$posts} p ON p.ID = tr.object_id WHERE p.ID IS NULL"),
        ];
    }

    private function formatBytes(int $bytes): string
    {
        $units = ['B', 'KB', 'MB', 'GB', 'TB'];
        $i = 0;
        while ($bytes > 1024 && $i < count($units) - 1) {
            $bytes /= 1024;
            $i++;
        }
        return round($bytes, 2) . ' ' . $units[$i];
    }
}
