<?php

declare(strict_types=1);

namespace App\Modules\Developer;

use App\Core\Database;

/**
 * DeveloperService — Business Logic for Developer Console
 *
 * Provides system introspection capabilities:
 *   - MySQL connection info (version, database name, table count)
 *   - Documentation file status (existence, size, modification time)
 *   - Connection health check
 *
 * @package Inveetaire
 * @ref     DX001
 */
class DeveloperService
{
    /**
     * Documentation directory base path.
     */
    private string $docsPath;

    /**
     * Documentation version defined in DX001.
     */
    private string $docVersion = 'v1.0.0';

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

    /**
     * Retrieve MySQL connection information and statistics.
     *
     * @return array{
     *   connected: bool,
     *   db_name: string,
     *   db_version: string,
     *   total_tables: int,
     *   connection_status: string,
     *   doc_version: string,
     *   last_generated: string|null,
     *   error: string|null
     * }
     */
    public function getMysqlInfo(): array
    {
        try {
            $pdo = Database::getConnection();

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

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

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

            // Last documentation generation — use 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,
                'total_tables'      => $totalTables,
                'connection_status' => 'Connected',
                'doc_version'       => $this->docVersion,
                'last_generated'    => $lastGenTime,
                'error'             => null,
            ];
        } catch (\Throwable $e) {
            return [
                'connected'         => false,
                'db_name'           => '—',
                'db_version'        => '—',
                'total_tables'      => 0,
                'connection_status' => 'Failed',
                'doc_version'       => $this->docVersion,
                'last_generated'    => null,
                'error'             => $e->getMessage(),
            ];
        }
    }

    /**
     * Check the status of all documentation files.
     *
     * @return array{
     *   doc_version: string,
     *   core_docs: array<string, array{exists: bool, path: string, size_kb: string, modified: string|null}>,
     *   tables_dir: array{path: string, exists: bool, count_existing: int, count_expected: int, files: list<string>},
     * }
     */
    public function getDocumentationStatus(): array
    {
        $coreFiles = [
            'README_SCHEMA.md' => $this->docsPath . '/README_SCHEMA.md',
            'AI_CONTEXT.md'    => $this->docsPath . '/AI_CONTEXT.md',
            'RELATIONSHIP.md'  => $this->docsPath . '/RELATIONSHIP.md',
            'CHANGELOG.md'     => $this->docsPath . '/CHANGELOG.md',
            'schema.sql'       => $this->docsPath . '/schema.sql',
        ];

        $coreStatus = [];
        foreach ($coreFiles as $name => $fullPath) {
            $exists = file_exists($fullPath);
            $coreStatus[$name] = [
                'exists'   => $exists,
                'path'     => 'docs/database/' . $name,
                'size_kb'  => $exists ? number_format(filesize($fullPath) / 1024, 1) . ' KB' : '—',
                'modified' => $exists ? date('Y-m-d H:i', filemtime($fullPath)) : null,
            ];
        }

        // Table documentation directory
        $tablesDir = $this->docsPath . '/tables';
        $tableDirExists = is_dir($tablesDir);
        $tableFiles = [];
        if ($tableDirExists) {
            $found = glob($tablesDir . '/*.md');
            $tableFiles = $found ? array_map('basename', $found) : [];
        }

        // Expected table count (from schema — 43 tables)
        $expectedCount = 43;

        return [
            'doc_version' => $this->docVersion,
            'core_docs'   => $coreStatus,
            'tables_dir'  => [
                'path'           => 'docs/database/tables/',
                'exists'         => $tableDirExists,
                'count_existing' => count($tableFiles),
                'count_expected' => $expectedCount,
                'files'          => $tableFiles,
            ],
        ];
    }

    /**
     * Test if the PDO connection is active and responsive.
     *
     * @return bool
     */
    public function isConnected(): bool
    {
        try {
            $pdo = Database::getConnection();
            $pdo->query('SELECT 1');
            return true;
        } catch (\Throwable $e) {
            return false;
        }
    }

    /**
     * Get total registered application routes.
     */
    public function getTotalRoutes(): int
    {
        $routesDir = ROOT_PATH . '/routes';
        if (!is_dir($routesDir)) {
            return 0;
        }

        $files = glob($routesDir . '/*.php');
        if (!$files) {
            return 0;
        }

        $count = 0;
        foreach ($files as $file) {
            $content = file_get_contents($file) ?: '';
            $count += preg_match_all('/\$router->(get|post|put|patch|delete)\s*\(/i', $content);
        }

        return max($count, 12); // Fallback estimate if empty
    }

    /**
     * Get total registered application modules in app/modules/.
     */
    public function getTotalModules(): int
    {
        $modulesDir = ROOT_PATH . '/app/modules';
        if (!is_dir($modulesDir)) {
            return 0;
        }

        $dirs = glob($modulesDir . '/*', GLOB_ONLYDIR);
        return $dirs ? count($dirs) : 0;
    }

    /**
     * Get storage directory disk usage statistics.
     *
     * @return array{bytes: int, formatted: string, file_count: int}
     */
    public function getStorageUsage(): array
    {
        $storageDir = ROOT_PATH . '/storage';
        if (!is_dir($storageDir)) {
            return ['bytes' => 0, 'formatted' => '0 MB', 'file_count' => 0];
        }

        $totalBytes = 0;
        $fileCount  = 0;

        try {
            $iterator = new \RecursiveIteratorIterator(
                new \RecursiveDirectoryIterator($storageDir, \FilesystemIterator::SKIP_DOTS)
            );

            foreach ($iterator as $file) {
                if ($file->isFile()) {
                    $totalBytes += $file->getSize();
                    $fileCount++;
                }
            }
        } catch (\Throwable $e) {
            // Safe fallback on permission issue
        }

        $formatted = $totalBytes >= 1048576
            ? number_format($totalBytes / 1048576, 2) . ' MB'
            : number_format($totalBytes / 1024, 1) . ' KB';

        return [
            'bytes'      => $totalBytes,
            'formatted'  => $formatted,
            'file_count' => $fileCount,
        ];
    }

    /**
     * Get enhanced database overview metrics (Feature 5).
     *
     * @return array{
     *   total_tables: int,
     *   total_columns: int,
     *   total_foreign_keys: int,
     *   db_size_mb: string,
     *   charset: string,
     *   collation: string,
     *   engine: string,
     *   doc_version: string,
     *   last_generated: string|null
     * }
     */
    public function getDatabaseOverview(): array
    {
        try {
            $pdo = Database::getConnection();

            // Total tables
            $stmt = $pdo->query("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE()");
            $totalTables = (int) $stmt->fetchColumn();

            // Total columns
            $stmt = $pdo->query("SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = DATABASE()");
            $totalCols = (int) $stmt->fetchColumn();

            // Total foreign keys
            $stmt = $pdo->query(
                "SELECT COUNT(*) FROM information_schema.referential_constraints WHERE constraint_schema = DATABASE()"
            );
            $totalFks = (int) $stmt->fetchColumn();

            // DB size
            $stmt = $pdo->query(
                "SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 2)
                 FROM information_schema.tables WHERE table_schema = DATABASE()"
            );
            $dbSizeMb = number_format((float) ($stmt->fetchColumn() ?: 0.0), 2) . ' MB';

            // Charset & Collation
            $stmt = $pdo->query(
                "SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
                 FROM information_schema.schemata WHERE schema_name = DATABASE()"
            );
            $meta = $stmt->fetch() ?: [];

            // Storage Engine
            $stmt = $pdo->query(
                "SELECT ENGINE FROM information_schema.tables
                 WHERE table_schema = DATABASE() AND ENGINE IS NOT NULL LIMIT 1"
            );
            $engine = (string) ($stmt->fetchColumn() ?: 'InnoDB');

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

            return [
                'total_tables'       => $totalTables,
                'total_columns'      => $totalCols,
                'total_foreign_keys' => $totalFks,
                'db_size_mb'         => $dbSizeMb,
                'charset'            => $meta['DEFAULT_CHARACTER_SET_NAME'] ?? 'utf8mb4',
                'collation'          => $meta['DEFAULT_COLLATION_NAME'] ?? 'utf8mb4_unicode_ci',
                'engine'             => $engine,
                'doc_version'        => $this->docVersion,
                'last_generated'     => $lastGenTime,
            ];
        } catch (\Throwable $e) {
            return [
                'total_tables'       => 0,
                'total_columns'      => 0,
                'total_foreign_keys' => 0,
                'db_size_mb'         => '0.00 MB',
                'charset'            => 'utf8mb4',
                'collation'          => 'utf8mb4_unicode_ci',
                'engine'             => 'InnoDB',
                'doc_version'        => $this->docVersion,
                'last_generated'     => null,
            ];
        }
    }

    /**
     * Get comprehensive application metadata (Feature 3).
     *
     * @return array
     */
    public function getApplicationInfo(): array
    {
        return [
            'project_name'      => config('app.name', 'INVEETAIRE'),
            'app_version'       => config('app.version', '1.0.0'),
            'dev_version'       => 'DX003 v1.0.0',
            'environment'       => config('app.env', 'local'),
            'php_version'       => PHP_VERSION,
            'php_sapi'          => PHP_SAPI,
            'os'                => PHP_OS_FAMILY . ' (' . php_uname('r') . ')',
            'framework'         => 'Inveetaire Custom MVC Core v1.0',
            'database_engine'   => 'MySQL / MariaDB (InnoDB)',
            'supabase_mirror'   => 'Cloud PostgreSQL Mirror (Read-Only Application Layer)',
            'build_date'        => date('Y-m-d', filemtime(ROOT_PATH . '/index.php')),
            'developer_mode'    => config('app.developer_mode', true) ? 'Enabled (DEVELOPER_MODE=true)' : 'Disabled',
            'doc_version'       => 'v1.0.0 (DX001 / DX002A / DX002B / DX003)',
        ];
    }
}
