<?php
/**
 * Discover tables in the current database and return row counts + storage (shared by /system dashboard and database tools).
 */

/**
 * List BASE TABLEs (excludes VIEWs) with row count, approximate size, and engine.
 *
 * @param PDO $pdo
 * @param list<string> $excludeTables Table names to skip (case-insensitive)
 * @return list<array{name: string, rows: int, size: int, engine: string}>
 */
function nazarethListBaseTablesWithStats(PDO $pdo, array $excludeTables = []): array
{
    $exclude = array_flip(array_map('strtolower', $excludeTables));
    $out = [];

    try {
        $stmt = $pdo->query('SHOW FULL TABLES');
        if (!$stmt) {
            return [];
        }

        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
            if (!$row) {
                break;
            }

            $tableType = $row['Table_type'] ?? 'BASE TABLE';
            $tableName = null;
            foreach ($row as $key => $value) {
                if ($key === 'Table_type') {
                    continue;
                }
                $tableName = (string) $value;
                break;
            }

            if ($tableName === '' || $tableName === null) {
                continue;
            }
            if ($tableType !== 'BASE TABLE') {
                continue;
            }
            if (!preg_match('/^[a-zA-Z0-9_]+$/', $tableName)) {
                continue;
            }
            if (isset($exclude[strtolower($tableName)])) {
                continue;
            }

            $quotedTable = '`' . str_replace('`', '', $tableName) . '`';

            try {
                $count = (int) $pdo->query("SELECT COUNT(*) FROM $quotedTable")->fetchColumn();
                $statusStmt = $pdo->query('SHOW TABLE STATUS LIKE ' . $pdo->quote($tableName));
                $status = $statusStmt ? $statusStmt->fetch(PDO::FETCH_ASSOC) : false;
                $size = (int) ($status['Data_length'] ?? 0) + (int) ($status['Index_length'] ?? 0);
                $engine = (string) ($status['Engine'] ?? 'Unknown');
                $out[] = [
                    'name' => $tableName,
                    'rows' => $count,
                    'size' => $size,
                    'engine' => $engine,
                ];
            } catch (PDOException $e) {
                $out[] = [
                    'name' => $tableName,
                    'rows' => 0,
                    'size' => 0,
                    'engine' => 'Unknown',
                ];
            }
        }
    } catch (PDOException $e) {
        return [];
    }

    usort($out, static function ($a, $b) {
        return strcasecmp($a['name'], $b['name']);
    });

    return $out;
}

/**
 * Build associative arrays used by the system dashboard charts and table.
 *
 * @return array{0: array<string, int>, 1: array<string, int>} [ $dbStats, $dbSizes ]
 */
function nazarethDbStatsAndSizes(PDO $pdo, array $excludeTables = []): array
{
    $dbStats = [];
    $dbSizes = [];
    foreach (nazarethListBaseTablesWithStats($pdo, $excludeTables) as $t) {
        $dbStats[$t['name']] = $t['rows'];
        $dbSizes[$t['name']] = $t['size'];
    }
    return [$dbStats, $dbSizes];
}
