<?php
declare(strict_types=1);

require_once __DIR__ . '/license_signer.php';

function golder_web_session_timeout_seconds(): int
{
    return 86400 * 30; // 30 Gün sabit süre (Rastgele çıkışları engeller)
}

function golder_web_session_heartbeat_interval_seconds(): int
{
    $value = (int)golder_env('GOLDER_WEB_SESSION_HEARTBEAT_SECONDS', '25');
    if ($value <= 0) {
        $value = 25;
    }
    return max(20, min(30, $value));
}

function golder_web_session_gc_days(): int
{
    $value = (int)golder_env('GOLDER_WEB_SESSION_GC_DAYS', '30');
    return max(1, $value);
}

function golder_web_session_timeout_sql(?int $timeoutSeconds = null): string
{
    $seconds = max(60, (int)($timeoutSeconds ?? golder_web_session_timeout_seconds()));
    return (string)$seconds;
}

function golder_session_store_table_exists(PDO $master, string $tableName): bool
{
    $dbName = golder_session_store_database_name($master);
    if ($dbName === '') {
        return false;
    }

    $st = $master->prepare(
        'SELECT COUNT(*)
         FROM information_schema.TABLES
         WHERE TABLE_SCHEMA = :db
           AND TABLE_NAME = :table_name'
    );
    $st->execute([
        ':db' => $dbName,
        ':table_name' => $tableName,
    ]);
    return ((int)$st->fetchColumn()) > 0;
}

function golder_web_session_last_seen_sql(string $alias = 's'): string
{
    return "COALESCE({$alias}.last_heartbeat_at, {$alias}.last_activity, {$alias}.last_active_at, {$alias}.login_at)";
}

function golder_active_web_session_case_sql(string $alias = 's', ?int $timeoutSeconds = null): string
{
    $timeoutSql = golder_web_session_timeout_sql($timeoutSeconds);
    $lastSeenSql = golder_web_session_last_seen_sql($alias);

    return "(
        {$alias}.is_active = 1
        AND {$alias}.logout_at IS NULL
        AND ({$alias}.expires_at IS NULL OR {$alias}.expires_at >= UTC_TIMESTAMP())
        AND {$lastSeenSql} >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL {$timeoutSql} SECOND)
        AND (
            NOT EXISTS (
                SELECT 1
                FROM customer_web_session_tabs t_all
                WHERE t_all.session_uuid = {$alias}.session_uuid
            )
            OR EXISTS (
                SELECT 1
                FROM customer_web_session_tabs t_active
                WHERE t_active.session_uuid = {$alias}.session_uuid
                  AND t_active.disconnected_at IS NULL
                  AND t_active.last_seen_at >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL {$timeoutSql} SECOND)
            )
        )
    )";
}

function golder_active_desktop_token_case_sql(string $alias = 't', ?int $timeoutSeconds = null): string
{
    $timeoutSql = golder_web_session_timeout_sql($timeoutSeconds);
    return "(
        {$alias}.scope = 'desktop_sync'
        AND {$alias}.revoked_at IS NULL
        AND {$alias}.expires_at >= UTC_TIMESTAMP()
        AND COALESCE({$alias}.last_used_at, {$alias}.created_at) >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL {$timeoutSql} SECOND)
    )";
}

function golder_device_group_label(string $deviceType, string $deviceName = '', string $userAgent = ''): string
{
    $type = strtolower(trim($deviceType));
    $haystack = strtolower(trim($deviceName . ' ' . $userAgent));
    if ($type === 'web') {
        return 'Web';
    }
    if (in_array($type, ['mobile', 'phone', 'android', 'ios'], true)) {
        return 'Telefon';
    }
    if (
        str_contains($haystack, 'iphone')
        || str_contains($haystack, 'android')
        || str_contains($haystack, 'telefon')
        || str_contains($haystack, 'mobile')
    ) {
        return 'Telefon';
    }
    return 'Bilgisayar';
}

function golder_pick_latest_datetime(string ...$values): string
{
    $bestValue = '';
    $bestTs = 0;
    foreach ($values as $value) {
        $trimmed = trim($value);
        if ($trimmed === '') {
            continue;
        }
        $ts = strtotime($trimmed);
        if ($ts === false) {
            continue;
        }
        if ($bestValue === '' || $ts > $bestTs) {
            $bestValue = $trimmed;
            $bestTs = $ts;
        }
    }
    return $bestValue;
}

function golder_customer_activity_summary(PDO $master, int $customerId): array
{
    $map = golder_customer_activity_map($master, [$customerId]);
    return $map[$customerId] ?? [
        'active_pc_sessions' => 0,
        'active_pc_devices' => 0,
        'registered_pc_devices' => 0,
        'active_web_devices' => 0,
        'registered_web_devices' => 0,
        'active_web_sessions' => 0,
        'last_seen_at' => '',
    ];
}

function golder_customer_activity_map(PDO $master, array $customerIds): array
{
    golder_ensure_customer_devices_schema($master);
    $customerIds = array_values(array_unique(array_filter(array_map(static fn($id): int => (int)$id, $customerIds), static fn(int $id): bool => $id > 0)));
    if (!$customerIds) {
        return [];
    }

    $placeholders = implode(', ', array_fill(0, count($customerIds), '?'));
    $result = [];
    foreach ($customerIds as $customerId) {
        $result[$customerId] = [
            'active_pc_sessions' => 0,
            'active_pc_devices' => 0,
            'registered_pc_devices' => 0,
            'active_web_devices' => 0,
            'registered_web_devices' => 0,
            'active_web_sessions' => 0,
            'last_seen_at' => '',
        ];
    }

    $webActiveSql = golder_active_web_session_case_sql('s');
    $webSql = "
        SELECT s.customer_id,
               COUNT(*) AS active_web_sessions,
               COUNT(DISTINCT s.device_id) AS active_web_devices,
               MAX(" . golder_web_session_last_seen_sql('s') . ") AS last_web_seen_at
        FROM customer_web_sessions s
        INNER JOIN customer_devices d ON d.id = s.device_id
        WHERE s.customer_id IN ({$placeholders})
          AND d.is_blocked = 0
          AND {$webActiveSql}
        GROUP BY s.customer_id
    ";
    $stWeb = $master->prepare($webSql);
    $stWeb->execute($customerIds);
    foreach ($stWeb->fetchAll() ?: [] as $row) {
        $customerId = (int)($row['customer_id'] ?? 0);
        if ($customerId <= 0 || !isset($result[$customerId])) {
            continue;
        }
        $result[$customerId]['active_web_sessions'] = (int)($row['active_web_sessions'] ?? 0);
        $result[$customerId]['active_web_devices'] = (int)($row['active_web_devices'] ?? 0);
        $result[$customerId]['last_seen_at'] = golder_pick_latest_datetime(
            $result[$customerId]['last_seen_at'],
            (string)($row['last_web_seen_at'] ?? '')
        );
    }

    $registeredWebSql = "
        SELECT customer_id, COUNT(*) AS registered_web_devices
        FROM customer_devices
        WHERE customer_id IN ({$placeholders})
          AND device_type = 'web'
          AND is_blocked = 0
        GROUP BY customer_id
    ";
    $stRegisteredWeb = $master->prepare($registeredWebSql);
    $stRegisteredWeb->execute($customerIds);
    foreach ($stRegisteredWeb->fetchAll() ?: [] as $row) {
        $customerId = (int)($row['customer_id'] ?? 0);
        if ($customerId > 0 && isset($result[$customerId])) {
            $result[$customerId]['registered_web_devices'] = (int)($row['registered_web_devices'] ?? 0);
        }
    }

    $registeredPcSql = "
        SELECT customer_id, COUNT(*) AS registered_pc_devices
        FROM customer_devices
        WHERE customer_id IN ({$placeholders})
          AND device_type = 'pc'
          AND is_blocked = 0
        GROUP BY customer_id
    ";
    $stRegisteredPc = $master->prepare($registeredPcSql);
    $stRegisteredPc->execute($customerIds);
    foreach ($stRegisteredPc->fetchAll() ?: [] as $row) {
        $customerId = (int)($row['customer_id'] ?? 0);
        if ($customerId > 0 && isset($result[$customerId])) {
            $result[$customerId]['registered_pc_devices'] = (int)($row['registered_pc_devices'] ?? 0);
        }
    }

    if (golder_session_store_table_exists($master, 'api_tokens')) {
        $desktopActiveSql = golder_active_desktop_token_case_sql('t');
        $desktopSql = "
            SELECT d.customer_id,
                   COUNT(DISTINCT d.id) AS active_pc_devices,
                   COUNT(DISTINCT t.id) AS active_pc_sessions,
                   MAX(COALESCE(t.last_used_at, t.created_at, d.last_active_at, d.last_login)) AS last_pc_seen_at
            FROM customer_devices d
            INNER JOIN api_tokens t
                ON t.customer_id = d.customer_id
               AND JSON_UNQUOTE(JSON_EXTRACT(t.metadata_json, '$.hardware_id')) = d.device_uid
            WHERE d.customer_id IN ({$placeholders})
              AND d.device_type = 'pc'
              AND d.is_blocked = 0
              AND {$desktopActiveSql}
            GROUP BY d.customer_id
        ";
        $stDesktop = $master->prepare($desktopSql);
        $stDesktop->execute($customerIds);
        foreach ($stDesktop->fetchAll() ?: [] as $row) {
            $customerId = (int)($row['customer_id'] ?? 0);
            if ($customerId <= 0 || !isset($result[$customerId])) {
                continue;
            }
            $result[$customerId]['active_pc_devices'] = (int)($row['active_pc_devices'] ?? 0);
            $result[$customerId]['active_pc_sessions'] = (int)($row['active_pc_sessions'] ?? 0);
            $result[$customerId]['last_seen_at'] = golder_pick_latest_datetime(
                $result[$customerId]['last_seen_at'],
                (string)($row['last_pc_seen_at'] ?? '')
            );
        }
    }

    return $result;
}

function golder_session_store_database_name(PDO $master): string
{
    static $cache = [];
    $key = spl_object_id($master);
    if (isset($cache[$key])) {
        return $cache[$key];
    }

    $name = (string)$master->query('SELECT DATABASE()')->fetchColumn();
    $cache[$key] = $name;
    return $name;
}

function golder_session_store_column_exists(PDO $master, string $tableName, string $columnName): bool
{
    $dbName = golder_session_store_database_name($master);
    if ($dbName === '') {
        return false;
    }

    $st = $master->prepare(
        'SELECT COUNT(*)
         FROM information_schema.COLUMNS
         WHERE TABLE_SCHEMA = :db
           AND TABLE_NAME = :table_name
           AND COLUMN_NAME = :column_name'
    );
    $st->execute([
        ':db' => $dbName,
        ':table_name' => $tableName,
        ':column_name' => $columnName,
    ]);
    return ((int)$st->fetchColumn()) > 0;
}

function golder_session_store_index_exists(PDO $master, string $tableName, string $indexName): bool
{
    $dbName = golder_session_store_database_name($master);
    if ($dbName === '') {
        return false;
    }

    $st = $master->prepare(
        'SELECT COUNT(*)
         FROM information_schema.STATISTICS
         WHERE TABLE_SCHEMA = :db
           AND TABLE_NAME = :table_name
           AND INDEX_NAME = :index_name'
    );
    $st->execute([
        ':db' => $dbName,
        ':table_name' => $tableName,
        ':index_name' => $indexName,
    ]);
    return ((int)$st->fetchColumn()) > 0;
}

function golder_session_store_add_column_if_missing(PDO $master, string $tableName, string $columnName, string $definition): void
{
    if (golder_session_store_column_exists($master, $tableName, $columnName)) {
        return;
    }

    $master->exec(sprintf('ALTER TABLE %s ADD COLUMN %s %s', $tableName, $columnName, $definition));
}

function golder_session_store_add_index_if_missing(PDO $master, string $tableName, string $indexName, string $definition): void
{
    if (golder_session_store_index_exists($master, $tableName, $indexName)) {
        return;
    }

    $master->exec(sprintf('ALTER TABLE %s ADD INDEX %s %s', $tableName, $indexName, $definition));
}

function golder_ensure_customer_devices_schema(PDO $master): void
{
    $master->exec(
        "CREATE TABLE IF NOT EXISTS customer_devices (
            id INT AUTO_INCREMENT PRIMARY KEY,
            customer_id INT NOT NULL,
            hardware_id VARCHAR(100) DEFAULT NULL,
            device_uid VARCHAR(120) DEFAULT NULL,
            device_type VARCHAR(10) NOT NULL DEFAULT 'pc',
            device_name VARCHAR(100) DEFAULT NULL,
            user_agent_hash CHAR(64) DEFAULT NULL,
            ip_address VARCHAR(64) DEFAULT NULL,
            last_login DATETIME DEFAULT CURRENT_TIMESTAMP,
            last_active_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            is_blocked TINYINT(1) NOT NULL DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_device_uid (customer_id, device_type, device_uid),
            KEY idx_customer_type (customer_id, device_type),
            KEY idx_last_active (last_active_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
    );

    $master->exec(
        "CREATE TABLE IF NOT EXISTS customer_web_sessions (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            customer_id INT NOT NULL,
            device_id INT DEFAULT NULL,
            session_uuid CHAR(36) NOT NULL,
            session_token_hash CHAR(64) NOT NULL,
            ip_address VARCHAR(64) DEFAULT NULL,
            user_agent TEXT,
            login_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            last_activity DATETIME DEFAULT CURRENT_TIMESTAMP,
            last_active_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            last_heartbeat_at DATETIME DEFAULT NULL,
            is_active TINYINT(1) NOT NULL DEFAULT 1,
            expires_at DATETIME DEFAULT NULL,
            logout_at DATETIME DEFAULT NULL,
            logout_reason VARCHAR(50) DEFAULT NULL,
            closed_at DATETIME DEFAULT NULL,
            closed_reason VARCHAR(50) DEFAULT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_session_uuid (session_uuid),
            KEY idx_customer_active (customer_id, is_active, logout_at, last_activity),
            KEY idx_session_liveness (is_active, last_activity),
            KEY idx_expires (expires_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
    );

    $master->exec(
        "CREATE TABLE IF NOT EXISTS customer_web_session_tabs (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            session_uuid CHAR(36) NOT NULL,
            tab_uuid CHAR(36) NOT NULL,
            ip_address VARCHAR(64) DEFAULT NULL,
            user_agent VARCHAR(255) DEFAULT NULL,
            last_seen_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            disconnected_at DATETIME DEFAULT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_session_tab (session_uuid, tab_uuid),
            KEY idx_session_tab_active (session_uuid, disconnected_at, last_seen_at),
            KEY idx_last_seen (last_seen_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
    );

    golder_session_store_add_column_if_missing(
        $master,
        'customer_web_sessions',
        'last_activity',
        'DATETIME DEFAULT CURRENT_TIMESTAMP AFTER login_at'
    );
    golder_session_store_add_column_if_missing(
        $master,
        'customer_web_sessions',
        'last_heartbeat_at',
        'DATETIME DEFAULT NULL AFTER last_active_at'
    );
    golder_session_store_add_column_if_missing(
        $master,
        'customer_web_sessions',
        'is_active',
        'TINYINT(1) NOT NULL DEFAULT 1 AFTER last_heartbeat_at'
    );
    golder_session_store_add_column_if_missing(
        $master,
        'customer_web_sessions',
        'closed_at',
        'DATETIME DEFAULT NULL AFTER logout_reason'
    );
    golder_session_store_add_column_if_missing(
        $master,
        'customer_web_sessions',
        'closed_reason',
        'VARCHAR(50) DEFAULT NULL AFTER closed_at'
    );
    golder_session_store_add_index_if_missing(
        $master,
        'customer_web_sessions',
        'idx_customer_active',
        '(customer_id, is_active, logout_at, last_activity)'
    );
    golder_session_store_add_index_if_missing(
        $master,
        'customer_web_sessions',
        'idx_session_liveness',
        '(is_active, last_activity)'
    );

    try {
        $master->exec(
            "UPDATE customer_web_sessions
             SET last_activity = COALESCE(last_activity, last_active_at, login_at, created_at),
                 is_active = CASE
                     WHEN logout_at IS NULL THEN 1
                     ELSE 0
                 END,
                 closed_at = COALESCE(closed_at, logout_at),
                 closed_reason = COALESCE(closed_reason, logout_reason)"
        );
    } catch (Throwable $_ignored) {
    }
}

function golder_device_uid_for_web(string $deviceName, string $ip, string $userAgent, string $hardwareId = ''): string
{
    $hardwareId = substr(trim($hardwareId), 0, 100);
    if ($hardwareId !== '') {
        return hash('sha256', 'web-hw|' . strtolower($hardwareId));
    }

    return hash('sha256', 'web-fallback|' . strtolower(trim($userAgent)) . '|' . trim($ip));
}

function golder_upsert_web_device(PDO $master, int $customerId, string $deviceName, string $ip, string $userAgent, string $hardwareId = ''): int
{
    golder_ensure_customer_devices_schema($master);

    $hardwareId = substr(trim($hardwareId), 0, 100);
    $uid = golder_device_uid_for_web($deviceName, $ip, $userAgent, $hardwareId);
    $uaHash = hash('sha256', strtolower(trim($userAgent)));

    $st = $master->prepare('SELECT id, device_name FROM customer_devices WHERE customer_id = :cid AND device_type = :dt AND device_uid = :du LIMIT 1');
    $st->execute([':cid' => $customerId, ':dt' => 'web', ':du' => $uid]);
    $row = $st->fetch();
    
    if ($row) {
        $deviceId = (int)$row['id'];
        
        // --- CİHAZ İSMİNİ KORUMA KODU ---
        $existingName = (string)($row['device_name'] ?? '');
        // Eğer otomatik giriş "WEB-" gibi bir isim dayatmaya çalışırsa ve veritabanındaki ismimiz düzgünse, eskisini koru!
        if (str_starts_with($deviceName, 'WEB-') && $existingName !== '' && !str_starts_with($existingName, 'WEB-')) {
            $deviceName = $existingName;
        }

        $master->prepare(
            'UPDATE customer_devices
             SET device_name = :dn,
                 hardware_id = COALESCE(NULLIF(:hw, \'\'), hardware_id),
                 user_agent_hash = :uah,
                 ip_address = :ip,
                 last_login = UTC_TIMESTAMP(),
                 last_active_at = UTC_TIMESTAMP(),
                 is_blocked = 0
             WHERE id = :id'
        )->execute([
            ':dn' => $deviceName,
            ':hw' => $hardwareId,
            ':uah' => $uaHash,
            ':ip' => $ip,
            ':id' => $deviceId,
        ]);
        return $deviceId;
    }

    $ins = $master->prepare(
        'INSERT INTO customer_devices
         (customer_id, hardware_id, device_uid, device_type, device_name, user_agent_hash, ip_address, last_login, last_active_at, is_blocked)
         VALUES (:cid, :hw, :du, :dt, :dn, :uah, :ip, UTC_TIMESTAMP(), UTC_TIMESTAMP(), 0)'
    );
    $ins->execute([
        ':cid' => $customerId,
        ':hw' => ($hardwareId !== '' ? $hardwareId : null),
        ':du' => $uid,
        ':dt' => 'web',
        ':dn' => $deviceName,
        ':uah' => $uaHash,
        ':ip' => $ip,
    ]);

    return (int)$master->lastInsertId();
}

function golder_web_session_last_activity(array $row): string
{
    $heartbeat = trim((string)($row['last_heartbeat_at'] ?? ''));
    if ($heartbeat !== '') {
        return $heartbeat;
    }
    $value = trim((string)($row['last_activity'] ?? ''));
    if ($value !== '') {
        return $value;
    }
    $fallback = trim((string)($row['last_active_at'] ?? ''));
    if ($fallback !== '') {
        return $fallback;
    }
    return trim((string)($row['login_at'] ?? ''));
}

function golder_is_web_session_row_active(array $row, ?int $timeoutSeconds = null): bool
{
    if ((int)($row['is_active'] ?? 0) !== 1) {
        return false;
    }
    if (trim((string)($row['logout_at'] ?? '')) !== '') {
        return false;
    }

    $expiresAt = trim((string)($row['expires_at'] ?? ''));
    if ($expiresAt !== '' && strtotime($expiresAt) !== false && strtotime($expiresAt) < time()) {
        return false;
    }

    $lastSeen = golder_web_session_last_activity($row);
    $lastSeenTs = ($lastSeen !== '') ? strtotime($lastSeen) : false;
    if ($lastSeenTs === false) {
        return false;
    }
    $timeout = max(60, (int)($timeoutSeconds ?? golder_web_session_timeout_seconds()));
    if ($lastSeenTs < (time() - $timeout)) {
        return false;
    }

    $trackedTabs = (int)($row['tracked_tabs'] ?? 0);
    if ($trackedTabs > 0 && (int)($row['active_tabs'] ?? 0) <= 0) {
        return false;
    }

    return true;
}

function golder_get_web_session_by_uuid(PDO $master, string $sessionUuid): ?array
{
    golder_ensure_customer_devices_schema($master);
    $sessionUuid = trim($sessionUuid);
    if ($sessionUuid === '') {
        return null;
    }

    $st = $master->prepare(
        'SELECT *
         FROM customer_web_sessions
         WHERE session_uuid = :sid
         LIMIT 1'
    );
    $st->execute([':sid' => $sessionUuid]);
    $row = $st->fetch();
    return is_array($row) ? $row : null;
}

function golder_validate_web_session(PDO $master, string $sessionUuid, string $sessionToken = ''): ?array
{
    $row = golder_get_web_session_by_uuid($master, $sessionUuid);
    if (!$row) {
        return null;
    }

    if ((int)($row['is_active'] ?? 0) === 0 || trim((string)($row['logout_at'] ?? '')) !== '') {
        return null;
    }

    $expiresAt = trim((string)($row['expires_at'] ?? ''));
    if ($expiresAt !== '' && strtotime($expiresAt) !== false && strtotime($expiresAt) < time()) {
        golder_close_web_session($master, $sessionUuid, 'token_expired');
        return null;
    }

    $sessionToken = trim($sessionToken);
    if ($sessionToken !== '') {
        $expectedHash = trim((string)($row['session_token_hash'] ?? ''));
        if ($expectedHash === '' || !hash_equals($expectedHash, hash('sha256', $sessionToken))) {
            golder_close_web_session($master, $sessionUuid, 'token_mismatch');
            return null;
        }
    }

    return $row;
}

function golder_record_web_session_tab_heartbeat(PDO $master, string $sessionUuid, string $tabUuid, string $ip, string $userAgent): void
{
    golder_ensure_customer_devices_schema($master);
    $sessionUuid = trim($sessionUuid);
    $tabUuid = trim($tabUuid);
    if ($sessionUuid === '' || $tabUuid === '') {
        return;
    }

    $master->prepare(
        'INSERT INTO customer_web_session_tabs (session_uuid, tab_uuid, ip_address, user_agent, last_seen_at, disconnected_at, created_at, updated_at)
         VALUES (:sid, :tid, :ip, :ua, UTC_TIMESTAMP(), NULL, UTC_TIMESTAMP(), UTC_TIMESTAMP())
         ON DUPLICATE KEY UPDATE
             ip_address = VALUES(ip_address),
             user_agent = VALUES(user_agent),
             last_seen_at = UTC_TIMESTAMP(),
             disconnected_at = NULL,
             updated_at = UTC_TIMESTAMP()'
    )->execute([
        ':sid' => $sessionUuid,
        ':tid' => substr($tabUuid, 0, 36),
        ':ip' => $ip,
        ':ua' => substr($userAgent, 0, 255),
    ]);
}

function golder_mark_web_session_tab_disconnected(PDO $master, string $sessionUuid, string $tabUuid): int
{
    golder_ensure_customer_devices_schema($master);
    $sessionUuid = trim($sessionUuid);
    $tabUuid = trim($tabUuid);
    if ($sessionUuid === '' || $tabUuid === '') {
        return 0;
    }

    $st = $master->prepare(
        'UPDATE customer_web_session_tabs
         SET disconnected_at = COALESCE(disconnected_at, UTC_TIMESTAMP()),
             updated_at = UTC_TIMESTAMP()
         WHERE session_uuid = :sid
           AND tab_uuid = :tid'
    );
    $st->execute([
        ':sid' => $sessionUuid,
        ':tid' => substr($tabUuid, 0, 36),
    ]);
    return (int)$st->rowCount();
}

function golder_count_active_web_session_tabs(PDO $master, string $sessionUuid, ?int $timeoutSeconds = null): int
{
    golder_ensure_customer_devices_schema($master);
    $sessionUuid = trim($sessionUuid);
    if ($sessionUuid === '') {
        return 0;
    }

    $timeoutSql = golder_web_session_timeout_sql($timeoutSeconds);
    $st = $master->prepare(
        "SELECT COUNT(*)
         FROM customer_web_session_tabs
         WHERE session_uuid = :sid
           AND disconnected_at IS NULL
           AND last_seen_at >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL {$timeoutSql} SECOND)"
    );
    $st->execute([':sid' => $sessionUuid]);
    return (int)$st->fetchColumn();
}

function golder_count_active_web_sessions(PDO $master, int $customerId): int
{
    $summary = golder_customer_activity_summary($master, $customerId);
    return (int)($summary['active_web_sessions'] ?? 0);
}

function golder_count_active_web_devices(PDO $master, int $customerId): int
{
    $summary = golder_customer_activity_summary($master, $customerId);
    return (int)($summary['active_web_devices'] ?? 0);
}

function golder_count_registered_desktop_devices(PDO $master, int $customerId, string $excludeDeviceUid = ''): int
{
    golder_ensure_customer_devices_schema($master);

    $customerId = (int)$customerId;
    if ($customerId <= 0) {
        return 0;
    }

    $excludeDeviceUid = substr(trim($excludeDeviceUid), 0, 120);
    $sql = "SELECT COUNT(*)
            FROM customer_devices
            WHERE customer_id = :cid
              AND device_type = 'pc'
              AND is_blocked = 0";
    $params = [':cid' => $customerId];
    if ($excludeDeviceUid !== '') {
        $sql .= ' AND COALESCE(device_uid, \'\') <> :exclude_uid';
        $params[':exclude_uid'] = $excludeDeviceUid;
    }

    $st = $master->prepare($sql);
    $st->execute($params);
    return (int)($st->fetchColumn() ?: 0);
}

function golder_count_active_desktop_devices(PDO $master, int $customerId, string $excludeDeviceUid = ''): int
{
    golder_ensure_customer_devices_schema($master);
    if (!golder_session_store_table_exists($master, 'api_tokens')) {
        return 0;
    }

    $customerId = (int)$customerId;
    if ($customerId <= 0) {
        return 0;
    }

    $desktopActiveSql = golder_active_desktop_token_case_sql('t');
    $excludeDeviceUid = substr(trim($excludeDeviceUid), 0, 120);
    $excludeSql = '';
    $params = [':cid' => $customerId];
    if ($excludeDeviceUid !== '') {
        $excludeSql = "AND JSON_UNQUOTE(JSON_EXTRACT(t.metadata_json, '$.hardware_id')) <> :exclude_uid";
        $params[':exclude_uid'] = $excludeDeviceUid;
    }

    $st = $master->prepare(
        "SELECT COUNT(DISTINCT JSON_UNQUOTE(JSON_EXTRACT(t.metadata_json, '$.hardware_id')))
         FROM api_tokens t
         WHERE t.customer_id = :cid
           AND {$desktopActiveSql}
           AND COALESCE(JSON_UNQUOTE(JSON_EXTRACT(t.metadata_json, '$.hardware_id')), '') <> ''
           {$excludeSql}"
    );
    $st->execute($params);
    return (int)($st->fetchColumn() ?: 0);
}

function golder_device_has_active_desktop_session(PDO $master, int $customerId, string $deviceUid): bool
{
    golder_ensure_customer_devices_schema($master);
    if (!golder_session_store_table_exists($master, 'api_tokens')) {
        return false;
    }

    $deviceUid = substr(trim($deviceUid), 0, 120);
    if ($customerId <= 0 || $deviceUid === '') {
        return false;
    }

    $desktopActiveSql = golder_active_desktop_token_case_sql('t');
    $st = $master->prepare(
        "SELECT COUNT(*)
         FROM api_tokens t
         WHERE t.customer_id = :cid
           AND {$desktopActiveSql}
           AND JSON_UNQUOTE(JSON_EXTRACT(t.metadata_json, '$.hardware_id')) = :device_uid"
    );
    $st->execute([
        ':cid' => (int)$customerId,
        ':device_uid' => $deviceUid,
    ]);
    return ((int)($st->fetchColumn() ?: 0)) > 0;
}

function golder_revoke_desktop_tokens_by_device_uid(PDO $master, int $customerId, string $deviceUid): int
{
    golder_ensure_customer_devices_schema($master);
    if (!golder_session_store_table_exists($master, 'api_tokens')) {
        return 0;
    }

    $deviceUid = substr(trim($deviceUid), 0, 120);
    if ($customerId <= 0 || $deviceUid === '') {
        return 0;
    }

    $st = $master->prepare(
        "UPDATE api_tokens
         SET revoked_at = COALESCE(revoked_at, UTC_TIMESTAMP())
         WHERE customer_id = :cid
           AND scope = 'desktop_sync'
           AND revoked_at IS NULL
           AND JSON_UNQUOTE(JSON_EXTRACT(metadata_json, '$.hardware_id')) = :device_uid"
    );
    $st->execute([
        ':cid' => (int)$customerId,
        ':device_uid' => $deviceUid,
    ]);
    return (int)$st->rowCount();
}

function golder_reset_customer_desktop_hardware_locks(PDO $master, int $customerId): array
{
    golder_ensure_customer_devices_schema($master);

    $customerId = (int)$customerId;
    if ($customerId <= 0) {
        return ['devices_deleted' => 0, 'tokens_revoked' => 0];
    }

    $tokensRevoked = 0;
    if (golder_session_store_table_exists($master, 'api_tokens')) {
        $stTokens = $master->prepare(
            "UPDATE api_tokens
             SET revoked_at = COALESCE(revoked_at, UTC_TIMESTAMP())
             WHERE customer_id = :cid
               AND scope = 'desktop_sync'
               AND revoked_at IS NULL"
        );
        $stTokens->execute([':cid' => $customerId]);
        $tokensRevoked = (int)$stTokens->rowCount();
    }

    $stDevices = $master->prepare(
        "DELETE FROM customer_devices
         WHERE customer_id = :cid
           AND device_type = 'pc'"
    );
    $stDevices->execute([':cid' => $customerId]);

    return [
        'devices_deleted' => (int)$stDevices->rowCount(),
        'tokens_revoked' => $tokensRevoked,
    ];
}

function golder_customer_web_access_failure(array $customer): ?array
{
    $licenseStatus = strtolower(trim((string)($customer['license_status'] ?? 'active')));
    if (in_array($licenseStatus, ['cancelled', 'suspended'], true)) {
        return [
            'code' => 'LICENSE_INACTIVE',
            'message' => 'Lisans aktif degil. Lutfen yonetici ile iletisime gecin.',
        ];
    }

    $effective = golder_customer_effective_plan($customer);
    if (!golder_plan_supports_web_mobile((string)($effective['plan_type'] ?? ''))) {
        return [
            'code' => 'WEB_ACCESS_DISABLED',
            'message' => 'Bu plan web/mobil erisimini desteklemiyor.',
        ];
    }

    if ((int)($effective['max_web_sessions'] ?? 0) <= 0) {
        return [
            'code' => 'WEB_ACCESS_DISABLED',
            'message' => 'Bu hesap icin web/mobil cihaz limiti 0 olarak ayarlanmis.',
        ];
    }

    $syncStatus = (string)($effective['sync_status'] ?? 'disabled');
    if ($syncStatus === 'expired') {
        return [
            'code' => 'SYNC_EXPIRED',
            'message' => 'Senkronizasyon sureniz doldu. Web/mobil erisimi yeniden etkinlestirmek icin yenileme yapin.',
        ];
    }
    if ($syncStatus !== 'active') {
        return [
            'code' => 'WEB_ACCESS_DISABLED',
            'message' => 'Bu plan icin web/mobil erisimi kapali.',
        ];
    }

    return null;
}

function golder_open_web_session(PDO $master, array $customer, string $deviceName, string $ip, string $userAgent, int $ttlHours = 24, string $hardwareId = ''): array
{
    golder_ensure_customer_devices_schema($master);

    $customerId = (int)($customer['id'] ?? 0);
    if ($customerId <= 0) {
        return ['ok' => false, 'message' => 'Gecersiz musteri kaydi.'];
    }

    $failure = golder_customer_web_access_failure($customer);
    if (is_array($failure)) {
        return [
            'ok' => false,
            'code' => (string)($failure['code'] ?? 'WEB_ACCESS_DISABLED'),
            'message' => (string)($failure['message'] ?? 'Web oturumu acilamadi.'),
        ];
    }
    $effective = golder_customer_effective_plan($customer);
    $maxWebAllowed = (int)($effective['max_web_sessions'] ?? 0);

    $hardwareId = substr(trim($hardwareId), 0, 100);
    $deviceUid = golder_device_uid_for_web($deviceName, $ip, $userAgent, $hardwareId);
    $stExisting = $master->prepare(
        'SELECT id
         FROM customer_devices
         WHERE customer_id = :cid
           AND device_type = :dt
           AND device_uid = :du
         LIMIT 1'
    );
    $stExisting->execute([
        ':cid' => $customerId,
        ':dt' => 'web',
        ':du' => $deviceUid,
    ]);
    $existingDeviceId = (int)($stExisting->fetchColumn() ?: 0);
    if ($existingDeviceId <= 0) {
        $activeDeviceCount = golder_count_active_web_devices($master, $customerId);
        if ($activeDeviceCount >= $maxWebAllowed) {
            return [
                'ok' => false,
                'message' => 'Web cihaz limitiniz dolu. Yeni cihaz eklemek için önce bir cihaz silin.',
            ];
        }
    }

    $deviceId = golder_upsert_web_device($master, $customerId, $deviceName, $ip, $userAgent, $hardwareId);
    
    // AYNI CİHAZA AİT ESKİ AÇIK OTURUMLARI KAPAT (Duplikasyonu Önler)
    $master->prepare(
        "UPDATE customer_web_sessions 
         SET is_active = 0, logout_at = UTC_TIMESTAMP(), logout_reason = 'replaced_by_new_login' 
         WHERE device_id = :did AND is_active = 1"
    )->execute([':did' => $deviceId]);

    $sessionUuid = golder_uuid4();
    $sessionToken = bin2hex(random_bytes(24));
    $tokenHash = hash('sha256', $sessionToken);
    $expiresAt = gmdate('Y-m-d H:i:s', time() + max(1, $ttlHours) * 3600);

    $ins = $master->prepare(
        'INSERT INTO customer_web_sessions
         (customer_id, device_id, session_uuid, session_token_hash, ip_address, user_agent, login_at, last_activity, last_active_at, last_heartbeat_at, is_active, expires_at)
         VALUES (:cid, :did, :sid, :sth, :ip, :ua, UTC_TIMESTAMP(), UTC_TIMESTAMP(), UTC_TIMESTAMP(), UTC_TIMESTAMP(), 1, :exp)'
    );
    $ins->execute([
        ':cid' => $customerId,
        ':did' => $deviceId,
        ':sid' => $sessionUuid,
        ':sth' => $tokenHash,
        ':ip' => $ip,
        ':ua' => $userAgent,
        ':exp' => $expiresAt,
    ]);

    $master->prepare('UPDATE customers SET last_web_login_date = UTC_TIMESTAMP() WHERE id = :id')->execute([':id' => $customerId]);

    return [
        'ok' => true,
        'session_uuid' => $sessionUuid,
        'session_token' => $sessionToken,
        'device_id' => $deviceId,
        'expires_at' => $expiresAt,
    ];
}

function golder_touch_web_session(PDO $master, string $sessionUuid, string $tabUuid = '', string $ip = '', string $userAgent = '', bool $isHeartbeat = false): bool
{
    golder_ensure_customer_devices_schema($master);
    $sessionUuid = trim($sessionUuid);
    if ($sessionUuid === '') {
        return false;
    }

    $check = $master->prepare("SELECT id FROM customer_web_sessions WHERE session_uuid = :sid AND is_active = 1 AND logout_at IS NULL");
    $check->execute([':sid' => $sessionUuid]);
    if (!$check->fetch()) {
        return false;
    }

    $heartbeatSql = $isHeartbeat ? 'UTC_TIMESTAMP()' : 'last_heartbeat_at';
    $st = $master->prepare(
        "UPDATE customer_web_sessions
         SET last_activity = UTC_TIMESTAMP(),
             last_active_at = UTC_TIMESTAMP(),
             last_heartbeat_at = {$heartbeatSql}
         WHERE session_uuid = :sid"
    );
    $st->execute([':sid' => $sessionUuid]);

    if (trim($tabUuid) !== '') {
        golder_record_web_session_tab_heartbeat($master, $sessionUuid, $tabUuid, $ip, $userAgent);
    }

    $master->prepare(
        'UPDATE customer_devices d
         INNER JOIN customer_web_sessions s ON s.device_id = d.id
         SET d.last_active_at = UTC_TIMESTAMP(),
             d.ip_address = COALESCE(NULLIF(:ip, \'\'), d.ip_address)
         WHERE s.session_uuid = :sid'
    )->execute([
        ':sid' => $sessionUuid,
        ':ip' => trim($ip),
    ]);

    return true;
}

function golder_close_web_session(PDO $master, string $sessionUuid, string $reason = 'logout'): void
{
    golder_ensure_customer_devices_schema($master);
    $sessionUuid = trim($sessionUuid);
    if ($sessionUuid === '') {
        return;
    }

    $reason = substr(trim($reason), 0, 50);
    $master->prepare(
        'UPDATE customer_web_sessions
         SET is_active = 0,
             logout_at = COALESCE(logout_at, UTC_TIMESTAMP()),
             logout_reason = COALESCE(logout_reason, :logout_reason),
             closed_at = COALESCE(closed_at, UTC_TIMESTAMP()),
             closed_reason = COALESCE(closed_reason, :closed_reason)
         WHERE session_uuid = :sid'
    )->execute([
        ':sid' => $sessionUuid,
        ':logout_reason' => $reason,
        ':closed_reason' => $reason,
    ]);

    $master->prepare(
        'UPDATE customer_web_session_tabs
         SET disconnected_at = COALESCE(disconnected_at, UTC_TIMESTAMP()),
             updated_at = UTC_TIMESTAMP()
         WHERE session_uuid = :sid
           AND disconnected_at IS NULL'
    )->execute([':sid' => $sessionUuid]);
}

function golder_close_web_sessions_by_device(PDO $master, int $customerId, int $deviceId, string $reason = 'device_logout'): int
{
    golder_ensure_customer_devices_schema($master);

    // --- EKLENEN KISIM: Otomatik giriş döngüsünü kırmak için "Beni Hatırla" anahtarlarını sil ---
    $stDev = $master->prepare('SELECT user_agent_hash FROM customer_devices WHERE id = :id AND customer_id = :cid');
    $stDev->execute([':id' => $deviceId, ':cid' => $customerId]);
    $devRow = $stDev->fetch();
    if ($devRow && !empty($devRow['user_agent_hash'])) {
        try {
            $master->prepare('DELETE FROM customer_remember_tokens WHERE customer_id = :cid AND user_agent_hash = :uah')
                   ->execute([':cid' => $customerId, ':uah' => $devRow['user_agent_hash']]);
        } catch (Throwable $e) {}
    }
    // ------------------------------------------------------------------------------------------

    $reason = substr(trim($reason), 0, 50);
    $st = $master->prepare(
        'UPDATE customer_web_sessions
         SET is_active = 0,
             logout_at = COALESCE(logout_at, UTC_TIMESTAMP()),
             logout_reason = COALESCE(logout_reason, :logout_reason),
             closed_at = COALESCE(closed_at, UTC_TIMESTAMP()),
             closed_reason = COALESCE(closed_reason, :closed_reason)
         WHERE customer_id = :cid
           AND device_id = :did
           AND is_active = 1'
    );
    $st->execute([
        ':logout_reason' => $reason,
        ':closed_reason' => $reason,
        ':cid' => $customerId,
        ':did' => $deviceId,
    ]);

    $master->prepare(
        'UPDATE customer_web_session_tabs t
         INNER JOIN customer_web_sessions s ON s.session_uuid = t.session_uuid
         SET t.disconnected_at = COALESCE(t.disconnected_at, UTC_TIMESTAMP()),
             t.updated_at = UTC_TIMESTAMP()
         WHERE s.customer_id = :cid
           AND s.device_id = :did
           AND t.disconnected_at IS NULL'
    )->execute([
        ':cid' => $customerId,
        ':did' => $deviceId,
    ]);

    return (int)$st->rowCount();
}

function golder_remove_device(PDO $master, int $customerId, int $deviceId): bool
{
    golder_ensure_customer_devices_schema($master);
    $stDevice = $master->prepare(
        'SELECT device_type, device_uid
         FROM customer_devices
         WHERE id = :id AND customer_id = :cid
         LIMIT 1'
    );
    $stDevice->execute([':id' => $deviceId, ':cid' => $customerId]);
    $deviceRow = $stDevice->fetch();
    
    // Oturumu ve beni hatırla token'larını yukarıdaki güncel fonksiyon zaten temizleyecek
    golder_close_web_sessions_by_device($master, $customerId, $deviceId, 'device_removed');
    
    if (is_array($deviceRow) && strtolower(trim((string)($deviceRow['device_type'] ?? ''))) === 'pc') {
        golder_revoke_desktop_tokens_by_device_uid($master, $customerId, (string)($deviceRow['device_uid'] ?? ''));
    }

    $st = $master->prepare('DELETE FROM customer_devices WHERE id = :id AND customer_id = :cid');
    $st->execute([':id' => $deviceId, ':cid' => $customerId]);
    return ((int)$st->rowCount()) > 0;
}

function golder_list_customer_web_sessions(PDO $master, int $customerId): array
{
    golder_ensure_customer_devices_schema($master);
    $timeoutSql = golder_web_session_timeout_sql();
    $st = $master->prepare(
        "SELECT s.id,
                s.session_uuid,
                s.device_id,
                COALESCE(d.device_name, CONCAT('WEB-', LEFT(COALESCE(s.ip_address, ''), 12))) AS device_name,
                s.ip_address,
                s.login_at,
                s.last_activity,
                s.last_active_at,
                s.last_heartbeat_at,
                " . golder_web_session_last_seen_sql('s') . " AS last_seen_at,
                s.is_active,
                s.expires_at,
                s.logout_at,
                s.logout_reason,
                (
                    SELECT COUNT(*)
                    FROM customer_web_session_tabs t_all
                    WHERE t_all.session_uuid = s.session_uuid
                ) AS tracked_tabs,
                (
                    SELECT COUNT(*)
                    FROM customer_web_session_tabs t
                    WHERE t.session_uuid = s.session_uuid
                      AND t.disconnected_at IS NULL
                      AND t.last_seen_at >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL {$timeoutSql} SECOND)
                ) AS active_tabs
         FROM customer_web_sessions s
         LEFT JOIN customer_devices d ON d.id = s.device_id
         WHERE s.customer_id = :cid
         ORDER BY COALESCE(s.last_activity, s.last_active_at, s.login_at) DESC, s.id DESC"
    );
    $st->execute([':cid' => $customerId]);
    $rows = $st->fetchAll() ?: [];

    foreach ($rows as &$row) {
        $isActiveNow = golder_is_web_session_row_active($row) ? 1 : 0;
        $row['device_group'] = 'Web';
        $row['last_seen_at'] = golder_pick_latest_datetime(
            (string)($row['last_seen_at'] ?? ''),
            (string)($row['last_activity'] ?? ''),
            (string)($row['last_active_at'] ?? ''),
            (string)($row['login_at'] ?? '')
        );
        $row['active_sessions'] = $isActiveNow;
        $row['is_active_now'] = $isActiveNow;
        $row['is_active_device'] = $isActiveNow;
    }
    unset($row);

    return $rows;
}

function golder_list_customer_devices(PDO $master, int $customerId): array
{
    golder_ensure_customer_devices_schema($master);
    $st = $master->prepare(
        "SELECT d.id,
                d.customer_id,
                d.device_type,
                d.device_name,
                d.hardware_id,
                d.device_uid,
                d.ip_address,
                d.last_login,
                d.last_active_at,
                d.is_blocked
         FROM customer_devices d
         WHERE d.customer_id = :cid
         ORDER BY COALESCE(d.last_active_at, d.last_login) DESC, d.id DESC"
    );
    $st->execute([':cid' => $customerId]);
    $rows = $st->fetchAll() ?: [];
    if (!$rows) {
        return [];
    }

    $webSessionsByDevice = [];
    $webActiveSql = golder_active_web_session_case_sql('s');
    $stWeb = $master->prepare(
        "SELECT s.device_id,
                COUNT(*) AS active_sessions,
                MAX(" . golder_web_session_last_seen_sql('s') . ") AS last_seen_at
         FROM customer_web_sessions s
         INNER JOIN customer_devices d ON d.id = s.device_id
         WHERE s.customer_id = :cid
           AND d.is_blocked = 0
           AND {$webActiveSql}
         GROUP BY s.device_id"
    );
    $stWeb->execute([':cid' => $customerId]);
    foreach ($stWeb->fetchAll() ?: [] as $row) {
        $deviceId = (int)($row['device_id'] ?? 0);
        if ($deviceId > 0) {
            $webSessionsByDevice[$deviceId] = [
                'active_sessions' => (int)($row['active_sessions'] ?? 0),
                'last_seen_at' => (string)($row['last_seen_at'] ?? ''),
            ];
        }
    }

    $desktopSessionsByDevice = [];
    if (golder_session_store_table_exists($master, 'api_tokens')) {
        $desktopActiveSql = golder_active_desktop_token_case_sql('t');
        $stDesktop = $master->prepare(
            "SELECT d.id AS device_id,
                    COUNT(DISTINCT t.id) AS active_sessions,
                    MAX(COALESCE(t.last_used_at, t.created_at, d.last_active_at, d.last_login)) AS last_seen_at
             FROM customer_devices d
             INNER JOIN api_tokens t
                 ON t.customer_id = d.customer_id
                AND JSON_UNQUOTE(JSON_EXTRACT(t.metadata_json, '$.hardware_id')) = d.device_uid
             WHERE d.customer_id = :cid
               AND d.device_type = 'pc'
               AND d.is_blocked = 0
               AND {$desktopActiveSql}
             GROUP BY d.id"
        );
        $stDesktop->execute([':cid' => $customerId]);
        foreach ($stDesktop->fetchAll() ?: [] as $row) {
            $deviceId = (int)($row['device_id'] ?? 0);
            if ($deviceId > 0) {
                $desktopSessionsByDevice[$deviceId] = [
                    'active_sessions' => (int)($row['active_sessions'] ?? 0),
                    'last_seen_at' => (string)($row['last_seen_at'] ?? ''),
                ];
            }
        }
    }

    foreach ($rows as &$row) {
        $deviceId = (int)($row['id'] ?? 0);
        $deviceType = strtolower(trim((string)($row['device_type'] ?? 'pc')));
        $activity = ($deviceType === 'web')
            ? ($webSessionsByDevice[$deviceId] ?? ['active_sessions' => 0, 'last_seen_at' => ''])
            : ($desktopSessionsByDevice[$deviceId] ?? ['active_sessions' => 0, 'last_seen_at' => '']);
        $activeSessions = (int)($activity['active_sessions'] ?? 0);
        $row['device_group'] = golder_device_group_label(
            (string)($row['device_type'] ?? ''),
            (string)($row['device_name'] ?? '')
        );
        $row['active_sessions'] = $activeSessions;
        $row['is_active_device'] = ($activeSessions > 0 && (int)($row['is_blocked'] ?? 0) === 0) ? 1 : 0;
        $row['last_seen_at'] = golder_pick_latest_datetime(
            (string)($activity['last_seen_at'] ?? ''),
            (string)($row['last_active_at'] ?? ''),
            (string)($row['last_login'] ?? '')
        );
    }
    unset($row);

    return $rows;
}

function golder_rename_device(PDO $master, int $customerId, int $deviceId, string $deviceName): bool
{
    golder_ensure_customer_devices_schema($master);
    $deviceName = trim($deviceName);
    if ($deviceName === '') {
        return false;
    }
    $st = $master->prepare('UPDATE customer_devices SET device_name = :dn WHERE id = :id AND customer_id = :cid');
    $st->execute([':dn' => substr($deviceName, 0, 100), ':id' => $deviceId, ':cid' => $customerId]);
    return ((int)$st->rowCount()) > 0;
}

function golder_block_device(PDO $master, int $customerId, int $deviceId): bool
{
    golder_ensure_customer_devices_schema($master);
    $st = $master->prepare('UPDATE customer_devices SET is_blocked = 1, last_active_at = UTC_TIMESTAMP() WHERE id = :id AND customer_id = :cid');
    $st->execute([':id' => $deviceId, ':cid' => $customerId]);
    if ((int)$st->rowCount() <= 0) {
        return false;
    }
    golder_close_web_sessions_by_device($master, $customerId, $deviceId, 'device_blocked');
    return true;
}

function golder_expire_stale_web_session_tabs(PDO $master, ?int $timeoutSeconds = null): int
{
    golder_ensure_customer_devices_schema($master);
    $timeoutSql = golder_web_session_timeout_sql($timeoutSeconds);
    $st = $master->prepare(
        "UPDATE customer_web_session_tabs
         SET disconnected_at = COALESCE(disconnected_at, UTC_TIMESTAMP()),
             updated_at = UTC_TIMESTAMP()
         WHERE disconnected_at IS NULL
           AND last_seen_at < DATE_SUB(UTC_TIMESTAMP(), INTERVAL {$timeoutSql} SECOND)"
    );
    $st->execute();
    return (int)$st->rowCount();
}

function golder_expire_inactive_web_sessions(PDO $master, ?int $timeoutSeconds = null): int
{
    golder_ensure_customer_devices_schema($master);
    $timeoutSql = golder_web_session_timeout_sql($timeoutSeconds);
    $st = $master->prepare(
        "UPDATE customer_web_sessions
         SET is_active = 0,
             logout_at = COALESCE(logout_at, UTC_TIMESTAMP()),
             logout_reason = COALESCE(
                 logout_reason,
                 CASE
                     WHEN expires_at IS NOT NULL AND expires_at < UTC_TIMESTAMP() THEN 'token_expired'
                     ELSE 'heartbeat_timeout'
                 END
             ),
             closed_at = COALESCE(closed_at, UTC_TIMESTAMP()),
             closed_reason = COALESCE(
                 closed_reason,
                 CASE
                     WHEN expires_at IS NOT NULL AND expires_at < UTC_TIMESTAMP() THEN 'token_expired'
                     ELSE 'heartbeat_timeout'
                 END
             )
         WHERE is_active = 1
           AND logout_at IS NULL
           AND (
               (expires_at IS NOT NULL AND expires_at < UTC_TIMESTAMP())
               OR last_activity < DATE_SUB(UTC_TIMESTAMP(), INTERVAL {$timeoutSql} SECOND)
           )"
    );
    $st->execute();
    return (int)$st->rowCount();
}

function golder_cleanup_web_session_tabs(PDO $master, int $inactiveDays = 30): int
{
    golder_ensure_customer_devices_schema($master);
    $days = max(1, $inactiveDays);
    $st = $master->prepare(
        "DELETE FROM customer_web_session_tabs
         WHERE COALESCE(disconnected_at, last_seen_at, created_at) < DATE_SUB(UTC_TIMESTAMP(), INTERVAL {$days} DAY)"
    );
    $st->execute();
    return (int)$st->rowCount();
}

function golder_cleanup_web_sessions(PDO $master, int $inactiveDays = 30): int
{
    golder_ensure_customer_devices_schema($master);
    $days = max(1, $inactiveDays);
    $st = $master->prepare(
        "DELETE FROM customer_web_sessions
         WHERE COALESCE(closed_at, logout_at, last_activity, last_active_at, created_at) < DATE_SUB(UTC_TIMESTAMP(), INTERVAL {$days} DAY)"
    );
    $st->execute();
    return (int)$st->rowCount();
}

function golder_ensure_pairing_schema(PDO $master): void
{
    $master->exec(
        "CREATE TABLE IF NOT EXISTS device_pairing_requests (
            id BIGINT AUTO_INCREMENT PRIMARY KEY,
            customer_id BIGINT NOT NULL,
            pairing_token CHAR(64) NOT NULL,
            pin_code VARCHAR(10) NOT NULL,
            status VARCHAR(20) NOT NULL DEFAULT 'pending',
            expires_at DATETIME NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_pairing_token (pairing_token),
            KEY idx_pairing_pin (pin_code),
            KEY idx_pairing_expires (expires_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"
    );
}


