<?php

declare(strict_types=1);

namespace App\Modules\Developer;

use App\Core\Database;

/**
 * MySqlInspectorService — Full MySQL Database Inspector
 *
 * Provides comprehensive MySQL/MariaDB inspection for the Developer Console.
 * All queries are read-only SELECT statements against information_schema.
 *
 * Extracted from DeveloperService to maintain single-responsibility.
 * DeveloperService::getMysqlInfo() is preserved for backward compatibility.
 *
 * @package Inveetaire
 * @ref     DX002A
 */
class MySqlInspectorService
{
    /**
     * Documentation directory for last-generated detection.
     */
    private string $docsPath;

    /**
     * Documentation version (DX001 baseline).
     */
    private string $docVersion = 'v1.0.0';

    public function __construct()
    {
        $this->docsPath = ROOT_PATH . '/docs/database';
    }

    /**
     * Retrieve the full MySQL database inspection report.
     *
     * @return array{
     *   connected: bool,
     *   db_name: string,
     *   db_version: string,
     *   db_version_short: string,
     *   character_set: string,
     *   collation: string,
     *   default_engine: string,
     *   total_tables: int,
     *   total_rows: int,
     *   db_size_mb: string,
     *   doc_version: string,
     *   last_generated: string|null,
     *   host: string,
     *   port: string,
     *   error: string|null
     * }
     */
    public function inspect(): array
    {
        try {
            $pdo = Database::getConnection();

            // Database name
            $dbName = (string) $pdo->query('SELECT DATABASE()')->fetchColumn();

            // Full version string
            $dbVersion = (string) $pdo->query('SELECT @@version')->fetchColumn();

            // Short version (strip build suffix)
            $dbVersionShort = explode('-', $dbVersion)[0];

            // Character set and collation
            $charSet   = (string) $pdo->query("SELECT @@character_set_database")->fetchColumn();
            $collation = (string) $pdo->query("SELECT @@collation_database")->fetchColumn();

            // Most common storage engine
            $engineStmt = $pdo->prepare(
                "SELECT ENGINE, COUNT(*) as cnt
                 FROM information_schema.tables
                 WHERE table_schema = DATABASE() AND ENGINE IS NOT NULL
                 GROUP BY ENGINE ORDER BY cnt DESC LIMIT 1"
            );
            $engineStmt->execute();
            $defaultEngine = (string) ($engineStmt->fetchColumn() ?: 'InnoDB');

            // Total tables
            $tablesStmt = $pdo->prepare(
                'SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE()'
            );
            $tablesStmt->execute();
            $totalTables = (int) $tablesStmt->fetchColumn();

            // Total rows (sum across all tables — approximate from information_schema)
            $rowsStmt = $pdo->prepare(
                'SELECT COALESCE(SUM(TABLE_ROWS), 0)
                 FROM information_schema.tables
                 WHERE table_schema = DATABASE()'
            );
            $rowsStmt->execute();
            $totalRows = (int) $rowsStmt->fetchColumn();

            // Database size in MB
            $sizeStmt = $pdo->prepare(
                "SELECT COALESCE(
                    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2),
                    0
                 )
                 FROM information_schema.tables
                 WHERE table_schema = DATABASE()"
            );
            $sizeStmt->execute();
            $dbSizeMb = number_format((float) $sizeStmt->fetchColumn(), 2);

            // Documentation last generated — README_SCHEMA.md mtime
            $readmePath  = $this->docsPath . '/README_SCHEMA.md';
            $lastGenTime = file_exists($readmePath)
                ? date('Y-m-d H:i:s', filemtime($readmePath))
                : null;

            return [
                'connected'        => true,
                'db_name'          => $dbName,
                'db_version'       => $dbVersion,
                'db_version_short' => $dbVersionShort,
                'character_set'    => $charSet,
                'collation'        => $collation,
                'default_engine'   => $defaultEngine,
                'total_tables'     => $totalTables,
                'total_rows'       => $totalRows,
                'db_size_mb'       => $dbSizeMb,
                'doc_version'      => $this->docVersion,
                'last_generated'   => $lastGenTime,
                'host'             => (string) config('database.host', '127.0.0.1'),
                'port'             => (string) config('database.port', '3306'),
                'error'            => null,
            ];
        } catch (\Throwable $e) {
            return [
                'connected'        => false,
                'db_name'          => '—',
                'db_version'       => '—',
                'db_version_short' => '—',
                'character_set'    => '—',
                'collation'        => '—',
                'default_engine'   => '—',
                'total_tables'     => 0,
                'total_rows'       => 0,
                'db_size_mb'       => '0.00',
                'doc_version'      => $this->docVersion,
                'last_generated'   => null,
                'host'             => '—',
                'port'             => '—',
                'error'            => $e->getMessage(),
            ];
        }
    }
}
