<?php
namespace EasyChat\Model;

class AppSession extends Base {
    public const ACCESS_TTL_SECONDS = 900;
    public const REFRESH_TTL_SECONDS = 2592000;
    public const ABSOLUTE_TTL_SECONDS = 7776000;

    public function deleteSessionsByFcmToken($fcm_token) {
        $escaped = $this->db->escape($fcm_token);
        $this->db->query(
            "UPDATE `{$this->prefix}app_session`
             SET `revoked_at` = COALESCE(`revoked_at`, NOW()),
                 `revoke_reason` = IF(`revoke_reason` = '', 'fcm_reassigned', `revoke_reason`)
             WHERE `fcm_token` = '{$escaped}' AND `access_token_hash` IS NOT NULL"
        );
        $this->db->query(
            "DELETE FROM `{$this->prefix}app_session`
             WHERE `fcm_token` = '{$escaped}' AND `access_token_hash` IS NULL"
        );
    }

    public function deleteSessionsByFcmTokenExcept($fcm_token, $session_token) {
        $fcm = $this->db->escape($fcm_token);
        $token = $this->db->escape($session_token);
        $hash = $this->db->escape(hash('sha256', (string)$session_token));
        $exclude = "NOT ((`access_token_hash` = '{$hash}') OR (`session_token` = '{$token}' AND `session_token` != ''))";
        $this->db->query(
            "UPDATE `{$this->prefix}app_session`
             SET `revoked_at` = COALESCE(`revoked_at`, NOW()),
                 `revoke_reason` = IF(`revoke_reason` = '', 'fcm_reassigned', `revoke_reason`)
             WHERE `fcm_token` = '{$fcm}' AND `access_token_hash` IS NOT NULL AND {$exclude}"
        );
        $this->db->query(
            "DELETE FROM `{$this->prefix}app_session`
             WHERE `fcm_token` = '{$fcm}' AND `access_token_hash` IS NULL AND {$exclude}"
        );
    }

    public function createSession($session_token, $fcm_token, $manager_id, array $client = []) {
        $fcm_token_hash_sql = $this->fcmTokenHashSql($fcm_token);
        $client = $this->normalizeClientMetadata($client);
        $this->db->query("INSERT INTO `{$this->prefix}app_session` SET 
            `session_token` = '" . $this->db->escape($session_token) . "', 
            `fcm_token` = '" . $this->db->escape($fcm_token) . "', 
            `fcm_token_hash` = " . $fcm_token_hash_sql . ",
            `fcm_token_synced_at` = NULL,
            `manager_id` = " . (int)$manager_id . ",
            `app_version_code` = " . (int)$client['app_version_code'] . ",
            `app_version_name` = '" . $this->db->escape($client['app_version_name']) . "',
            `device_label` = '" . $this->db->escape($client['device_label']) . "',
            `device_last_seen_at` = NOW(),
            `date_added` = NOW()");
    }

    



    public function createDeviceSession($device_id, $manager_id, $fcm_token, array $client = []) {
        $access_token = bin2hex(random_bytes(32));
        $refresh_token = bin2hex(random_bytes(48));
        $family = bin2hex(random_bytes(16));
        $client = $this->normalizeClientMetadata($client);
        $fcm_token_hash_sql = $this->fcmTokenHashSql($fcm_token);
        $this->db->query(
            "INSERT INTO `{$this->prefix}app_session` SET
                `session_token` = '',
                `access_token_hash` = '" . $this->db->escape(hash('sha256', $access_token)) . "',
                `refresh_token_hash` = '" . $this->db->escape(hash('sha256', $refresh_token)) . "',
                `token_family` = '" . $this->db->escape($family) . "',
                `access_expires_at` = DATE_ADD(NOW(), INTERVAL " . self::ACCESS_TTL_SECONDS . " SECOND),
                `refresh_expires_at` = DATE_ADD(NOW(), INTERVAL " . self::REFRESH_TTL_SECONDS . " SECOND),
                `absolute_expires_at` = DATE_ADD(NOW(), INTERVAL " . self::ABSOLUTE_TTL_SECONDS . " SECOND),
                `fcm_token` = '" . $this->db->escape((string)$fcm_token) . "',
                `fcm_token_hash` = {$fcm_token_hash_sql},
                `fcm_token_synced_at` = NULL,
                `manager_id` = " . (int)$manager_id . ",
                `app_device_id` = " . (int)$device_id . ",
                `app_version_code` = " . (int)$client['app_version_code'] . ",
                `app_version_name` = '" . $this->db->escape($client['app_version_name']) . "',
                `device_label` = '" . $this->db->escape($client['device_label']) . "',
                `device_last_seen_at` = NOW(),
                `last_used_at` = NOW(),
                `date_added` = NOW()"
        );
        return [
            'session_id' => (int)$this->db->getLastId(),
            'access_token' => $access_token,
            'refresh_token' => $refresh_token,
            'access_expires_in' => self::ACCESS_TTL_SECONDS,
            'refresh_expires_in' => self::REFRESH_TTL_SECONDS,
            'token_family' => $family,
        ];
    }

    public function getSessionByToken($session_token) {
        $session_token = trim((string)$session_token);
        if ($session_token === '') return null;
        $hash = hash('sha256', $session_token);
        $query = $this->db->query(
            "SELECT * FROM `{$this->prefix}app_session`
             WHERE (
                    (`access_token_hash` = '" . $this->db->escape($hash) . "'
                     AND `revoked_at` IS NULL
                     AND `access_expires_at` > NOW())
                 OR (`session_token` = '" . $this->db->escape($session_token) . "'
                     AND `session_token` != ''
                     AND `revoked_at` IS NULL)
             )
             LIMIT 1"
        );
        return $query->num_rows ? $query->row : null;
    }

    public function getSessionByAccessTokenIncludingExpired($access_token) {
        $access_token = trim((string)$access_token);
        if ($access_token === '') return null;
        $hash = hash('sha256', $access_token);
        $query = $this->db->query(
            "SELECT * FROM `{$this->prefix}app_session`
             WHERE `access_token_hash` = '" . $this->db->escape($hash) . "' LIMIT 1"
        );
        return $query->num_rows ? $query->row : null;
    }

    public function touchSession($session_id) {
        $this->db->query(
            "UPDATE `{$this->prefix}app_session`
             SET `last_used_at` = NOW(), `device_last_seen_at` = NOW()
             WHERE `session_id` = " . (int)$session_id . "
               AND (`last_used_at` IS NULL OR `last_used_at` < DATE_SUB(NOW(), INTERVAL 2 MINUTE))"
        );
    }

    public function refreshDeviceSession($refresh_token) {
        $refresh_token = trim((string)$refresh_token);
        if (!preg_match('/^[a-f0-9]{96}$/D', $refresh_token)) {
            return ['success' => false, 'error' => 'refresh_token_invalid'];
        }
        $hash = hash('sha256', $refresh_token);
        $transaction_open = false;
        try {
            $this->db->query('START TRANSACTION');
            $transaction_open = true;
            $query = $this->db->query(
                "SELECT * FROM `{$this->prefix}app_session`
                 WHERE `refresh_token_hash` = '" . $this->db->escape($hash) . "'
                 LIMIT 1 FOR UPDATE"
            );
            if (!$query->num_rows) {
                $history = $this->db->query(
                    "SELECT * FROM `{$this->prefix}app_refresh_token_history`
                     WHERE `token_hash` = '" . $this->db->escape($hash) . "' LIMIT 1"
                );
                if ($history->num_rows) {
                    $family = (string)$history->row['token_family'];
                    $used_at = strtotime((string)$history->row['used_at']);
                    $replacement = $used_at >= time() - 60
                        ? $this->decryptReplacementBundle((string)($history->row['replacement_bundle'] ?? ''))
                        : [];
                    if ($replacement) {
                        $active = $this->db->query(
                            "SELECT `session_id`, `manager_id`, `app_device_id`, `absolute_expires_at`
                             FROM `{$this->prefix}app_session`
                             WHERE `token_family` = '" . $this->db->escape($family) . "'
                               AND `revoked_at` IS NULL LIMIT 1"
                        );
                        $this->db->query('COMMIT');
                        $transaction_open = false;
                        if ($active->num_rows) {
                            return [
                                'success' => true,
                                'session_id' => (int)$active->row['session_id'],
                                'manager_id' => (int)$active->row['manager_id'],
                                'app_device_id' => (int)$active->row['app_device_id'],
                                'access_token' => (string)$replacement['access_token'],
                                'refresh_token' => (string)$replacement['refresh_token'],
                                'access_expires_in' => max(1, (int)$replacement['access_expires_at'] - time()),
                                'refresh_expires_in' => max(1, (int)$replacement['refresh_expires_at'] - time()),
                                'replayed' => 1,
                            ];
                        }
                        return ['success' => false, 'error' => 'refresh_token_invalid'];
                    }
                    $this->revokeFamily($family, 'refresh_token_reuse');
                    $this->db->query('COMMIT');
                    $transaction_open = false;
                    return ['success' => false, 'error' => 'refresh_token_reused'];
                }
                $this->db->query('ROLLBACK');
                $transaction_open = false;
                return ['success' => false, 'error' => 'refresh_token_invalid'];
            }
            $session = $query->row;
            if (!empty($session['revoked_at'])
                || strtotime((string)$session['refresh_expires_at']) <= time()
                || strtotime((string)$session['absolute_expires_at']) <= time()
            ) {
                $this->db->query('ROLLBACK');
                $transaction_open = false;
                return ['success' => false, 'error' => 'refresh_token_expired'];
            }

            $new_access = bin2hex(random_bytes(32));
            $new_refresh = bin2hex(random_bytes(48));
            $access_expires_at = time() + self::ACCESS_TTL_SECONDS;
            $refresh_expires_at = min(
                time() + self::REFRESH_TTL_SECONDS,
                strtotime((string)$session['absolute_expires_at'])
            );
            $replacement_bundle = $this->encryptReplacementBundle([
                'access_token' => $new_access,
                'refresh_token' => $new_refresh,
                'access_expires_at' => $access_expires_at,
                'refresh_expires_at' => $refresh_expires_at,
            ]);
            $session_id = (int)$session['session_id'];
            $family = (string)$session['token_family'];
            $this->db->query(
                "INSERT INTO `{$this->prefix}app_refresh_token_history` SET
                    `session_id` = {$session_id},
                    `token_family` = '" . $this->db->escape($family) . "',
                    `token_hash` = '" . $this->db->escape($hash) . "',
                    `used_at` = NOW(),
                    `expires_at` = '" . $this->db->escape((string)$session['absolute_expires_at']) . "',
                    `replacement_bundle` = '" . $this->db->escape($replacement_bundle) . "'"
            );
            $this->db->query(
                "UPDATE `{$this->prefix}app_session` SET
                    `access_token_hash` = '" . $this->db->escape(hash('sha256', $new_access)) . "',
                    `refresh_token_hash` = '" . $this->db->escape(hash('sha256', $new_refresh)) . "',
                    `access_expires_at` = DATE_ADD(NOW(), INTERVAL " . self::ACCESS_TTL_SECONDS . " SECOND),
                    `refresh_expires_at` = LEAST(
                        DATE_ADD(NOW(), INTERVAL " . self::REFRESH_TTL_SECONDS . " SECOND),
                        `absolute_expires_at`
                    ),
                    `last_used_at` = NOW()
                 WHERE `session_id` = {$session_id}"
            );
            $this->db->query(
                "DELETE FROM `{$this->prefix}app_refresh_token_history`
                 WHERE `expires_at` < DATE_SUB(NOW(), INTERVAL 7 DAY) LIMIT 200"
            );
            $this->db->query('COMMIT');
            $transaction_open = false;
            return [
                'success' => true,
                'session_id' => $session_id,
                'manager_id' => (int)$session['manager_id'],
                'app_device_id' => (int)$session['app_device_id'],
                'access_token' => $new_access,
                'refresh_token' => $new_refresh,
                'access_expires_in' => self::ACCESS_TTL_SECONDS,
                'refresh_expires_in' => max(0, min(
                    self::REFRESH_TTL_SECONDS,
                    strtotime((string)$session['absolute_expires_at']) - time()
                )),
            ];
        } catch (\Throwable $e) {
            if ($transaction_open) {
                try { $this->db->query('ROLLBACK'); } catch (\Throwable $ignored) {}
            }
            throw $e;
        }
    }

    public function revokeDeviceSessions($device_id, $reason, $except_session_id = 0) {
        $reason = $this->normalizeReason($reason);
        $except_sql = (int)$except_session_id > 0
            ? " AND `session_id` != " . (int)$except_session_id
            : '';
        $this->db->query(
            "UPDATE `{$this->prefix}app_session`
             SET `revoked_at` = COALESCE(`revoked_at`, NOW()),
                 `revoke_reason` = IF(`revoke_reason` = '', '" . $this->db->escape($reason) . "', `revoke_reason`)
             WHERE `app_device_id` = " . (int)$device_id . "
               AND `revoked_at` IS NULL" . $except_sql
        );
    }

    



    public function revokeManagerSessions($manager_id, $reason, $except_session_id = 0) {
        $manager_id = (int)$manager_id;
        if ($manager_id <= 0) return;
        $reason = $this->normalizeReason($reason);
        $except_sql = (int)$except_session_id > 0
            ? " AND `session_id` != " . (int)$except_session_id
            : '';
        $this->db->query(
            "UPDATE `{$this->prefix}app_session`
             SET `revoked_at` = COALESCE(`revoked_at`, NOW()),
                 `revoke_reason` = IF(`revoke_reason` = '', '" . $this->db->escape($reason) . "', `revoke_reason`)
             WHERE `manager_id` = {$manager_id}
               AND `revoked_at` IS NULL" . $except_sql
        );
    }

    public function revokeSessionByToken($token, $reason = 'logout') {
        $token = trim((string)$token);
        if ($token === '') return;
        $hash = hash('sha256', $token);
        $reason = $this->normalizeReason($reason);
        $this->db->query(
            "UPDATE `{$this->prefix}app_session`
             SET `revoked_at` = COALESCE(`revoked_at`, NOW()),
                 `revoke_reason` = '" . $this->db->escape($reason) . "'
             WHERE `access_token_hash` = '" . $this->db->escape($hash) . "'
                OR (`session_token` = '" . $this->db->escape($token) . "' AND `session_token` != '')"
        );
    }

    public function revokeSessionById($session_id, $reason) {
        $reason = $this->normalizeReason($reason);
        $this->db->query(
            "UPDATE `{$this->prefix}app_session`
             SET `revoked_at` = COALESCE(`revoked_at`, NOW()),
                 `revoke_reason` = IF(`revoke_reason` = '', '" . $this->db->escape($reason) . "', `revoke_reason`)
             WHERE `session_id` = " . (int)$session_id
        );
    }

    public function revokeFamily($family, $reason) {
        $family = strtolower(trim((string)$family));
        if (!preg_match('/^[a-f0-9]{32}$/D', $family)) return;
        $reason = $this->normalizeReason($reason);
        $this->db->query(
            "UPDATE `{$this->prefix}app_session`
             SET `revoked_at` = COALESCE(`revoked_at`, NOW()),
                 `revoke_reason` = '" . $this->db->escape($reason) . "'
             WHERE `token_family` = '" . $this->db->escape($family) . "'"
        );
    }

    public function getSessionById($session_id) {
        $query = $this->db->query("SELECT * FROM `{$this->prefix}app_session` WHERE `session_id` = " . (int)$session_id . " LIMIT 1");
        return $query->num_rows ? $query->row : null;
    }

    public function getLatestSessionByFcmToken($fcm_token) {
        $fcm_token = trim((string)$fcm_token);
        if ($fcm_token === '') return [];
        $query = $this->db->query(
            "SELECT * FROM `{$this->prefix}app_session`
             WHERE `fcm_token` = '" . $this->db->escape($fcm_token) . "'
             ORDER BY (`revoked_at` IS NULL) DESC, `session_id` DESC LIMIT 1"
        );
        return $query->row ?: [];
    }

    public function getFcmTokenBySessionId($session_id) {
        $query = $this->db->query("SELECT `fcm_token` FROM `{$this->prefix}app_session`
            WHERE `session_id` = " . (int)$session_id . "
              AND `revoked_at` IS NULL
              AND (`refresh_token_hash` IS NULL OR `refresh_expires_at` > NOW())
            LIMIT 1");
        return $query->num_rows ? (string)$query->row['fcm_token'] : '';
    }

    public function getAllFcmTokens() {
        $query = $this->db->query("SELECT DISTINCT `fcm_token` FROM `{$this->prefix}app_session`
            WHERE `fcm_token` IS NOT NULL AND `fcm_token` != ''
              AND `revoked_at` IS NULL
              AND (`refresh_token_hash` IS NULL OR `refresh_expires_at` > NOW())");
        $tokens = [];
        foreach ($query->rows as $row) {
            $tokens[] = $row['fcm_token'];
        }
        return $tokens;
    }

    public function sessionIdsForManagers(array $manager_ids): array {
        $ids = [];
        foreach ($manager_ids as $manager_id) {
            $manager_id = (int)$manager_id;
            if ($manager_id > 0) $ids[$manager_id] = $manager_id;
        }
        if (!$ids) return [];
        $query = $this->db->query(
            "SELECT DISTINCT `session_id` FROM `{$this->prefix}app_session`
             WHERE `manager_id` IN (" . implode(',', $ids) . ")
               AND `fcm_token` IS NOT NULL AND `fcm_token` != ''"
               . " AND `revoked_at` IS NULL
               AND (`refresh_token_hash` IS NULL OR `refresh_expires_at` > NOW())"
        );
        return array_map(static function ($row) { return (int)$row['session_id']; }, $query->rows ?: []);
    }

    public function clearAllSessions() {
        $this->db->query("DELETE FROM `{$this->prefix}app_session`");
    }

    public function getManagerSessionData($session_token) {
        $session = $this->getSessionByToken($session_token);
        return $session ? [
            'manager_id' => (int)$session['manager_id'],
            'session_id' => (int)$session['session_id'],
            'app_device_id' => (int)($session['app_device_id'] ?? 0),
        ] : null;
    }

    public function getManagerIdByToken($session_token) {
        $session = $this->getSessionByToken($session_token);
        return $session ? (int)$session['manager_id'] : 0;
    }

    public function getFcmTokenBySessionToken($session_token) {
        $session = $this->getSessionByToken($session_token);
        return $session ? (string)$session['fcm_token'] : '';
    }

    public function updateFcmTokenBySessionToken($session_token, $fcm_token) {
        $fcm_token_hash_sql = $this->fcmTokenHashSql($fcm_token);
        $this->db->query("UPDATE `{$this->prefix}app_session`
            SET `fcm_token` = '" . $this->db->escape($fcm_token) . "',
                `fcm_token_hash` = " . $fcm_token_hash_sql . ",
                `fcm_token_synced_at` = NULL,
                `last_push_error` = '',
                `last_push_error_at` = NULL
            WHERE " . $this->tokenWhere($session_token));
    }

    public function updateClientMetadataBySessionToken($session_token, array $client = []) {
        $client = $this->normalizeClientMetadata($client);
        $this->db->query("UPDATE `{$this->prefix}app_session`
            SET `app_version_code` = " . (int)$client['app_version_code'] . ",
                `app_version_name` = '" . $this->db->escape($client['app_version_name']) . "',
                `device_label` = '" . $this->db->escape($client['device_label']) . "',
                `device_last_seen_at` = NOW()
            WHERE " . $this->tokenWhere($session_token));
    }

    public function listDeviceVersionsForManager(int $manager_id): array {
        $query = $this->db->query(
            "SELECT s.`session_id`, s.`app_version_code`,
                    COALESCE(NULLIF(s.`app_version_name`, ''), d.`app_version`, '') AS `app_version_name`,
                    COALESCE(NULLIF(s.`device_label`, ''), d.`device_label`, 'Android') AS `device_label`,
                    COALESCE(s.`device_last_seen_at`, d.`last_seen_at`, s.`date_added`) AS `last_seen_at`
             FROM `{$this->prefix}app_session` s
             LEFT JOIN `{$this->prefix}sms_devices` d ON d.`app_session_id` = s.`session_id`
             WHERE s.`manager_id` = " . (int)$manager_id . "
               AND s.`revoked_at` IS NULL
               AND (s.`refresh_token_hash` IS NULL OR s.`refresh_expires_at` > NOW())
               AND COALESCE(s.`device_last_seen_at`, d.`last_seen_at`, s.`date_added`) >= DATE_SUB(NOW(), INTERVAL 90 DAY)
             ORDER BY COALESCE(s.`device_last_seen_at`, d.`last_seen_at`, s.`date_added`) DESC"
        );
        return $query->rows;
    }

    public function getDeviceVersionSummary(): array {
        $query = $this->db->query(
            "SELECT s.`manager_id`, m.`name` AS `manager_name`, s.`app_version_code`,
                    COALESCE(NULLIF(s.`app_version_name`, ''), d.`app_version`, '') AS `app_version_name`,
                    COALESCE(NULLIF(s.`device_label`, ''), d.`device_label`, 'Android') AS `device_label`,
                    COALESCE(s.`device_last_seen_at`, d.`last_seen_at`, s.`date_added`) AS `last_seen_at`
             FROM `{$this->prefix}app_session` s
             INNER JOIN `{$this->prefix}managers` m ON m.`id` = s.`manager_id` AND m.`status` = 1
             LEFT JOIN `{$this->prefix}sms_devices` d ON d.`app_session_id` = s.`session_id`
             WHERE s.`revoked_at` IS NULL
               AND (s.`refresh_token_hash` IS NULL OR s.`refresh_expires_at` > NOW())
               AND COALESCE(s.`device_last_seen_at`, d.`last_seen_at`, s.`date_added`) >= DATE_SUB(NOW(), INTERVAL 90 DAY)
             ORDER BY COALESCE(s.`device_last_seen_at`, d.`last_seen_at`, s.`date_added`) DESC"
        );
        return $query->rows;
    }

    public function markFcmTokenSyncedBySessionToken($session_token) {
        $this->db->query("UPDATE `{$this->prefix}app_session`
            SET `fcm_token_synced_at` = NOW(),
                `last_push_error` = '',
                `last_push_error_at` = NULL
            WHERE " . $this->tokenWhere($session_token));
    }

    public function markFcmTokenSynced($fcm_token) {
        $this->db->query("UPDATE `{$this->prefix}app_session`
            SET `fcm_token_synced_at` = NOW(),
                `last_push_error` = '',
                `last_push_error_at` = NULL
            WHERE `fcm_token` = '" . $this->db->escape($fcm_token) . "'
              AND `revoked_at` IS NULL");
    }

    public function markPushDeliveredByFcmToken($fcm_token) {
        $this->db->query("UPDATE `{$this->prefix}app_session`
            SET `last_push_at` = NOW(),
                `fcm_token_synced_at` = NOW(),
                `last_push_error` = '',
                `last_push_error_at` = NULL
            WHERE `fcm_token` = '" . $this->db->escape($fcm_token) . "'
              AND `revoked_at` IS NULL");
    }

    public function markPushErrorByFcmToken($fcm_token, $error_code) {
        $error_code = substr((string)$error_code, 0, 128);
        $this->db->query("UPDATE `{$this->prefix}app_session`
            SET `last_push_at` = NOW(),
                `last_push_error` = '" . $this->db->escape($error_code) . "',
                `last_push_error_at` = NOW()
            WHERE `fcm_token` = '" . $this->db->escape($fcm_token) . "'
              AND `revoked_at` IS NULL");
    }

    public function deleteSessionByToken($session_token) {
         
         
        $session = $this->getSessionByAccessTokenIncludingExpired($session_token);
        if ($session) {
            $this->revokeSessionByToken($session_token, 'deleted_by_server');
            return;
        }
        $this->db->query("DELETE FROM `{$this->prefix}app_session` WHERE `session_token` = '" . $this->db->escape($session_token) . "'");
    }

    public function getTotalSessions() {
        $query = $this->db->query("SELECT COUNT(*) AS total FROM `{$this->prefix}app_session`
            WHERE `revoked_at` IS NULL
              AND (`refresh_token_hash` IS NULL OR `refresh_expires_at` > NOW())");
        return $query->num_rows ? (int)$query->row['total'] : 0;
    }

    public function getSessionHealthSummary() {
        $summary = [
            'total' => $this->getTotalSessions(),
            'stale_sync' => 0,
            'push_error' => 0,
            'last_error' => '',
            'last_error_at' => '',
        ];

        $query = $this->db->query("SELECT COUNT(*) AS total FROM `{$this->prefix}app_session`
            WHERE `fcm_token` IS NOT NULL
              AND `fcm_token` != ''
              AND `revoked_at` IS NULL
              AND (`refresh_token_hash` IS NULL OR `refresh_expires_at` > NOW())
              AND (`fcm_token_synced_at` IS NULL OR `fcm_token_synced_at` < DATE_SUB(NOW(), INTERVAL 2 DAY))");
        $summary['stale_sync'] = $query->num_rows ? (int)$query->row['total'] : 0;

        $query = $this->db->query("SELECT COUNT(*) AS total FROM `{$this->prefix}app_session`
            WHERE `last_push_error` != ''
              AND `revoked_at` IS NULL
              AND (`refresh_token_hash` IS NULL OR `refresh_expires_at` > NOW())");
        $summary['push_error'] = $query->num_rows ? (int)$query->row['total'] : 0;

        $query = $this->db->query("SELECT `last_push_error`, `last_push_error_at` FROM `{$this->prefix}app_session`
            WHERE `last_push_error` != ''
              AND `revoked_at` IS NULL
              AND (`refresh_token_hash` IS NULL OR `refresh_expires_at` > NOW())
            ORDER BY `last_push_error_at` DESC
            LIMIT 1");
        if ($query->num_rows) {
            $summary['last_error'] = (string)$query->row['last_push_error'];
            $summary['last_error_at'] = (string)$query->row['last_push_error_at'];
        }

        return $summary;
    }

    private function fcmTokenHashSql($fcm_token) {
        $fcm_token = (string)$fcm_token;
        if ($fcm_token === '') {
            return 'NULL';
        }
        return "'" . $this->db->escape(hash('sha256', $fcm_token)) . "'";
    }

    private function normalizeClientMetadata(array $client): array {
        $code = max(0, min(2147483647, (int)($client['app_version_code'] ?? 0)));
        $name = trim((string)($client['app_version_name'] ?? $client['app_version'] ?? ''));
        if ($name !== '' && !preg_match('/^\d+\.\d+\.\d+(?:[-+][A-Za-z0-9.]+)?$/D', $name)) {
            $name = '';
        }
        $label = trim((string)($client['device_label'] ?? ''));
        $label = preg_replace('/[\x00-\x1F\x7F]+/u', ' ', $label) ?: '';
        return [
            'app_version_code' => $code,
            'app_version_name' => mb_substr($name, 0, 32, 'UTF-8'),
            'device_label' => mb_substr($label !== '' ? $label : 'Android', 0, 120, 'UTF-8'),
        ];
    }

    private function normalizeReason($reason) {
        $reason = strtolower(trim((string)$reason));
        $reason = preg_replace('/[^a-z0-9_.-]+/', '_', $reason) ?: '';
        return substr($reason !== '' ? $reason : 'revoked', 0, 64);
    }

    private function tokenWhere($token) {
        $token = trim((string)$token);
        $hash = hash('sha256', $token);
        return "((`access_token_hash` = '" . $this->db->escape($hash) . "' AND `revoked_at` IS NULL)
            OR (`session_token` = '" . $this->db->escape($token) . "' AND `session_token` != '' AND `revoked_at` IS NULL))";
    }

    private function encryptReplacementBundle(array $payload) {
        $key = $this->refreshEncryptionKey();
        $iv = random_bytes(12);
        $tag = '';
        $json = json_encode($payload, JSON_UNESCAPED_SLASHES);
        $ciphertext = openssl_encrypt($json, 'aes-256-gcm', $key, OPENSSL_RAW_DATA, $iv, $tag);
        if (!is_string($ciphertext) || strlen($tag) !== 16) {
            throw new \RuntimeException('refresh_bundle_encryption_failed');
        }
        return rtrim(strtr(base64_encode($iv . $tag . $ciphertext), '+/', '-_'), '=');
    }

    private function decryptReplacementBundle($bundle) {
        $bundle = trim((string)$bundle);
        if ($bundle === '' || !preg_match('/^[A-Za-z0-9_-]+$/D', $bundle)) return [];
        $encoded = strtr($bundle, '-_', '+/');
        $encoded .= str_repeat('=', (4 - strlen($encoded) % 4) % 4);
        $raw = base64_decode($encoded, true);
        if (!is_string($raw) || strlen($raw) < 29) return [];
        $iv = substr($raw, 0, 12);
        $tag = substr($raw, 12, 16);
        $ciphertext = substr($raw, 28);
        $json = openssl_decrypt($ciphertext, 'aes-256-gcm', $this->refreshEncryptionKey(), OPENSSL_RAW_DATA, $iv, $tag);
        $payload = is_string($json) ? json_decode($json, true) : null;
        return is_array($payload)
            && preg_match('/^[a-f0-9]{64}$/D', (string)($payload['access_token'] ?? ''))
            && preg_match('/^[a-f0-9]{96}$/D', (string)($payload['refresh_token'] ?? ''))
                ? $payload
                : [];
    }

    private function refreshEncryptionKey() {
        $settings = new Setting($this->registry);
        $secret = trim((string)$settings->get('app_refresh_encryption_secret', ''));
        if (!preg_match('/^[a-f0-9]{64}$/D', $secret)) {
            $candidate = bin2hex(random_bytes(32));
            $this->db->query(
                "UPDATE `{$this->prefix}settings`
                 SET `value` = '" . $this->db->escape($candidate) . "'
                 WHERE `key` = 'app_refresh_encryption_secret'
                   AND `value` = '" . $this->db->escape($secret) . "'"
            );
            $this->db->query(
                "INSERT INTO `{$this->prefix}settings` (`key`, `value`)
                 VALUES ('app_refresh_encryption_secret', '" . $this->db->escape($candidate) . "')
                 ON DUPLICATE KEY UPDATE `value` = `value`"
            );
            $secret = trim((string)$settings->get('app_refresh_encryption_secret', ''));
        }
        if (!preg_match('/^[a-f0-9]{64}$/D', $secret)) {
            throw new \RuntimeException('app_refresh_encryption_secret_unavailable');
        }
        return hex2bin($secret);
    }
}
