<?php

namespace MintyPHP\Repository\System;

use MintyPHP\DB;

class SystemHealthRepository implements SystemHealthRepositoryInterface
{
    /**
     * @return bool True if DB responds to SELECT 1.
     */
    public function checkDatabaseConnectivity(): bool
    {
        return (int) (DB::selectValue('select 1') ?? 0) === 1;
    }

    /**
     * @return list<string> Table names present in the current schema.
     */
    public function listPresentTables(array $requiredTables): array
    {
        $rows = DB::select(
            'select table_name from information_schema.tables where table_schema = database() and table_name in (???)',
            $requiredTables
        );

        $present = [];
        foreach ((array) $rows as $row) {
            $table = (string) (($row['tables']['table_name'] ?? $row['table_name'] ?? ''));
            if ($table !== '') {
                $present[] = $table;
            }
        }

        return array_values(array_unique($present));
    }

    /**
     * @return array{last_heartbeat_at: string, last_result: string, last_error_code: string}|null
     */
    public function getSchedulerStatus(): ?array
    {
        $row = DB::selectOne('select last_heartbeat_at, last_result, last_error_code from scheduler_runtime_status where id = 1 limit 1');
        $status = is_array($row) ? ($row['scheduler_runtime_status'] ?? $row) : null;
        if (!is_array($status)) {
            return null;
        }

        return [
            'last_heartbeat_at' => trim((string) ($status['last_heartbeat_at'] ?? '')),
            'last_result' => trim((string) ($status['last_result'] ?? 'unknown')),
            'last_error_code' => trim((string) ($status['last_error_code'] ?? '')),
        ];
    }

    /**
     * @return list<string> Active permission keys matching the given list.
     */
    public function listActivePermissionKeys(array $keys): array
    {
        $rows = DB::select(
            'select `key` from permissions where active = 1 and `key` in (???)',
            $keys
        );

        $present = [];
        foreach ((array) $rows as $row) {
            $key = (string) (($row['permissions']['key'] ?? $row['key'] ?? ''));
            if ($key !== '') {
                $present[] = $key;
            }
        }

        return array_values(array_unique($present));
    }

    public function countAdminUsers(): int
    {
        return (int) (DB::selectValue(
            'select count(distinct ur.user_id) from user_roles ur join roles r on r.id = ur.role_id and r.active = 1 where r.description in (?, ?) or r.id = 1',
            'Admin',
            'Administrator'
        ) ?? 0);
    }

    public function countActivePermissions(): int
    {
        return (int) (DB::selectValue('select count(*) from permissions where active = 1') ?? 0);
    }

    public function countInactivePermissions(): int
    {
        return (int) (DB::selectValue('select count(*) from permissions where active = 0') ?? 0);
    }

    /**
     * @return list<string> All active permission keys.
     */
    public function listAllActivePermissionKeys(): array
    {
        $rows = DB::select('select `key` from permissions where active = 1 order by `key`');

        $keys = [];
        foreach ((array) $rows as $row) {
            $key = (string) (($row['permissions']['key'] ?? $row['key'] ?? ''));
            if ($key !== '') {
                $keys[] = $key;
            }
        }

        return $keys;
    }
}
