<?php
declare(strict_types=1);

namespace App\Database;

use PDO;
use RuntimeException;

final class SchemaHealthService
{
    /** @var array<string,list<string>> */
    private const REQUIRED_COLUMNS = [
        'api_tokens' => ['token_prefix', 'scopes_json'],
        'categories' => ['seo_title', 'seo_description', 'canonical_url', 'seo_index'],
        'comments' => ['status'],
        'questions' => ['title_normalized', 'body_normalized'],
        'reports' => ['moderator_notes'],
        'tags' => ['description', 'seo_title', 'seo_description', 'canonical_url', 'seo_index'],
        'users' => ['username', 'avatar_url', 'auth_version'],
    ];


    /** @var array<string,list<string>> */
    private const REQUIRED_INDEXES = [
        'questions' => ['idx_questions_status_created', 'idx_questions_status_views', 'idx_questions_status_votes'],
        'tags' => ['uniq_tags_locale_name', 'uniq_tags_slug'],
    ];

    /** @var list<string> */
    private const REQUIRED_TABLES = [
        'answers', 'api_tokens', 'audit_log', 'categories', 'category_follows', 'comments',
        'contact_messages', 'email_tokens', 'languages', 'layout_sections', 'layouts',
        'moderation_actions', 'notification_preferences', 'notifications', 'question_follows',
        'question_tags', 'questions', 'rate_limits', 'reports', 'schema_migrations', 'settings',
        'tag_follows', 'tag_synonyms', 'tags', 'topics', 'translation_overrides', 'user_preferences',
        'users', 'votes', 'widget_translations', 'widgets',
    ];

    public function __construct(
        private readonly PDO $pdo,
        private readonly string $migrationsDirectory,
    ) {
    }

    /** @return array{healthy:bool,runtime_compatible:bool,pending:list<string>,issues:list<string>,blocking_issues:list<string>,latest:?string} */
    public function inspect(): array
    {
        $files = $this->migrationFilenames();
        $latest = $files === [] ? null : $files[array_key_last($files)];
        $tables = $this->existingTables();
        $applied = in_array('schema_migrations', $tables, true) ? $this->appliedMigrationFilenames() : [];
        $pending = array_values(array_diff($files, $applied));
        $issues = [];
        $blockingIssues = [];

        foreach (self::REQUIRED_TABLES as $table) {
            if (!in_array($table, $tables, true)) {
                $issue = 'Missing table: ' . $table;
                $issues[] = $issue;
                $blockingIssues[] = $issue;
            }
        }

        $columns = $this->existingRequiredColumns();
        foreach (self::REQUIRED_COLUMNS as $table => $requiredColumns) {
            if (!in_array($table, $tables, true)) continue;
            foreach ($requiredColumns as $column) {
                if (!isset($columns[$table][$column])) {
                    $issue = sprintf('Missing column: %s.%s', $table, $column);
                    $issues[] = $issue;
                    $blockingIssues[] = $issue;
                }
            }
        }

        $indexes = $this->existingRequiredIndexes();
        foreach (self::REQUIRED_INDEXES as $table => $requiredIndexes) {
            if (!in_array($table, $tables, true)) continue;
            foreach ($requiredIndexes as $index) {
                if (!isset($indexes[$table][$index])) $issues[] = sprintf('Missing index: %s.%s', $table, $index);
            }
        }

        return [
            'healthy' => $pending === [] && $issues === [],
            'runtime_compatible' => $blockingIssues === [],
            'pending' => $pending,
            'issues' => array_values(array_unique($issues)),
            'blocking_issues' => array_values(array_unique($blockingIssues)),
            'latest' => $latest,
        ];
    }

    public function isHealthy(): bool
    {
        return $this->inspect()['healthy'];
    }

    /** @return list<string> */
    private function migrationFilenames(): array
    {
        if (!is_dir($this->migrationsDirectory)) {
            throw new RuntimeException(sprintf('Migration directory not found: %s', $this->migrationsDirectory));
        }
        $files = glob($this->migrationsDirectory . DIRECTORY_SEPARATOR . '*.sql');
        if ($files === false) throw new RuntimeException('Unable to read migration files.');
        $filenames = array_map('basename', $files);
        sort($filenames, SORT_STRING);
        return array_values($filenames);
    }

    /** @return list<string> */
    private function appliedMigrationFilenames(): array
    {
        $rows = $this->pdo->query('SELECT filename FROM schema_migrations ORDER BY id ASC')->fetchAll(PDO::FETCH_COLUMN);
        return array_values(array_map('strval', $rows));
    }

    /** @return list<string> */
    private function existingTables(): array
    {
        $rows = $this->pdo->query(
            'SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() ORDER BY TABLE_NAME'
        )->fetchAll(PDO::FETCH_COLUMN);
        return array_values(array_map('strval', $rows));
    }

    /** @return array<string,array<string,true>> */
    private function existingRequiredIndexes(): array
    {
        $tables = array_keys(self::REQUIRED_INDEXES);
        $placeholders = implode(',', array_fill(0, count($tables), '?'));
        $statement = $this->pdo->prepare(
            "SELECT TABLE_NAME, INDEX_NAME FROM information_schema.STATISTICS
             WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN ({$placeholders})"
        );
        $statement->execute($tables);
        $result = [];
        foreach ($statement->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $table = (string) ($row['TABLE_NAME'] ?? '');
            $index = (string) ($row['INDEX_NAME'] ?? '');
            if ($table !== '' && $index !== '') $result[$table][$index] = true;
        }
        return $result;
    }

    /** @return array<string,array<string,true>> */
    private function existingRequiredColumns(): array
    {
        $tables = array_keys(self::REQUIRED_COLUMNS);
        $placeholders = implode(',', array_fill(0, count($tables), '?'));
        $statement = $this->pdo->prepare(
            "SELECT TABLE_NAME, COLUMN_NAME FROM information_schema.COLUMNS
             WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN ({$placeholders})"
        );
        $statement->execute($tables);
        $result = [];
        foreach ($statement->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $table = (string) ($row['TABLE_NAME'] ?? '');
            $column = (string) ($row['COLUMN_NAME'] ?? '');
            if ($table !== '' && $column !== '') $result[$table][$column] = true;
        }
        return $result;
    }
}
