<?php
/*
 * مراقبة MikroTik الحقيقية للمشتركين:
 * - تحديث MAC/IP الحالي وآخر MAC دخل به الحساب.
 * - تسجيل أحداث MikroTik في سجل المشترك حتى لو كانت الواجهة مغلقة، عند توفرها في Log الراوتر.
 * - كشف تغيّر MAC، استعمال MAC على حساب آخر، ودخول IP/MAC بدون جلسة PPPoE.
 * الفحوص الثقيلة محكومة بقفل/TTL حتى لا نرهق الراوتر أو البرنامج.
 */

require_once __DIR__ . '/helpers.php';
require_once __DIR__ . '/subscriber_history_lib.php';
require_once __DIR__ . '/mikrotik_lib.php';
require_once __DIR__ . '/mac_vendor_lib.php';
require_once __DIR__ . '/mac_ownership_lib.php';
require_once __DIR__ . '/security_alerts_lib.php';
require_once __DIR__ . '/subscriber_behavior_monitor_lib.php';

if (!function_exists('mtwatch_norm_user')) {
  function mtwatch_norm_user(string $u): string {
    $u = trim($u);
    $u = preg_replace('/@.*/', '', $u) ?? $u;
    return trim($u);
  }
}

if (!function_exists('mtwatch_norm_mac')) {
  function mtwatch_norm_mac(string $mac): string {
    $mac = strtoupper(trim($mac));
    $mac = preg_replace('/[^0-9A-F]/', '', $mac) ?? '';
    if (strlen($mac) !== 12) return '';
    return implode(':', str_split($mac, 2));
  }
}

if (!function_exists('mtwatch_runtime_dir')) {
  function mtwatch_runtime_dir(): string {
    $dir = __DIR__ . '/cache/runtime';
    if (!is_dir($dir)) @mkdir($dir, 0777, true);
    return $dir;
  }
}

if (!function_exists('mtwatch_gate')) {
  function mtwatch_gate(string $key, int $ttl, bool $force = false): bool {
    $file = mtwatch_runtime_dir() . '/' . preg_replace('/[^a-z0-9_\-]+/i', '_', $key) . '.gate';
    $now = time();
    $last = is_file($file) ? (int)@filemtime($file) : 0;
    if (!$force && $last > 0 && ($now - $last) < $ttl) return false;
    @touch($file);
    return true;
  }
}

if (!function_exists('mtwatch_col_exists')) {
  function mtwatch_col_exists(mysqli $conn, string $table, string $col): bool {
    // لا نخبئ نتيجة الأعمدة هنا لأن هذا الملف يضيف أعمدة تلقائياً ثم يحتاج استخدامها في نفس الطلب.
    $t = $conn->real_escape_string($table);
    $c = $conn->real_escape_string($col);
    $rs = @$conn->query("SHOW COLUMNS FROM `{$t}` LIKE '{$c}'");
    return (bool)($rs && $rs->num_rows > 0);
  }
}

if (!function_exists('mtwatch_table_exists')) {
  function mtwatch_table_exists(mysqli $conn, string $table): bool {
    static $cache = [];
    if (array_key_exists($table, $cache)) return $cache[$table];
    $t = $conn->real_escape_string($table);
    $rs = @$conn->query("SHOW TABLES LIKE '{$t}'");
    return $cache[$table] = (bool)($rs && $rs->num_rows > 0);
  }
}

if (!function_exists('mtwatch_add_col')) {
  function mtwatch_add_col(mysqli $conn, string $table, string $col, string $ddl): void {
    if (!mtwatch_table_exists($conn, $table)) return;
    if (!mtwatch_col_exists($conn, $table, $col)) @ $conn->query("ALTER TABLE `{$table}` ADD COLUMN {$ddl}");
  }
}

if (!function_exists('mtwatch_ensure_schema')) {
  function mtwatch_ensure_schema(mysqli $conn): void {
    static $done = false;
    if ($done) return;
    $done = true;
    @mysqli_report(MYSQLI_REPORT_OFF);
    sh_ensure_schema($conn);
    if (function_exists('macvendor_ensure_schema')) @macvendor_ensure_schema($conn);
    if (function_exists('mtwatch_behavior_ensure_schema')) @mtwatch_behavior_ensure_schema($conn);

    if (mtwatch_table_exists($conn, 'subscribers')) {
      mtwatch_add_col($conn, 'subscribers', 'last_caller_id', "`last_caller_id` VARCHAR(32) NULL");
      mtwatch_add_col($conn, 'subscribers', 'current_caller_id', "`current_caller_id` VARCHAR(32) NULL");
      mtwatch_add_col($conn, 'subscribers', 'last_mac_vendor', "`last_mac_vendor` VARCHAR(120) NULL");
      mtwatch_add_col($conn, 'subscribers', 'last_device_type', "`last_device_type` VARCHAR(120) NULL");
      mtwatch_add_col($conn, 'subscribers', 'current_mac_vendor', "`current_mac_vendor` VARCHAR(120) NULL");
      mtwatch_add_col($conn, 'subscribers', 'current_device_type', "`current_device_type` VARCHAR(120) NULL");
      mtwatch_add_col($conn, 'subscribers', 'current_ip', "`current_ip` VARCHAR(45) NULL");
      mtwatch_add_col($conn, 'subscribers', 'current_uptime', "`current_uptime` VARCHAR(64) NULL");
      mtwatch_add_col($conn, 'subscribers', 'mt_last_logged_out', "`mt_last_logged_out` DATETIME NULL");
      mtwatch_add_col($conn, 'subscribers', 'mt_last_disconnect_reason', "`mt_last_disconnect_reason` VARCHAR(120) NULL");
      mtwatch_add_col($conn, 'subscribers', 'mt_mac_last_changed_at', "`mt_mac_last_changed_at` DATETIME NULL");
      mtwatch_add_col($conn, 'subscribers', 'mt_watch_sync_at', "`mt_watch_sync_at` DATETIME NULL");
    }

    mtwatch_add_col($conn, 'subscriber_activity_log', 'source_hash', "`source_hash` CHAR(64) NULL");
    if (mtwatch_col_exists($conn, 'subscriber_activity_log', 'source_hash')) {
      @ $conn->query("ALTER TABLE subscriber_activity_log ADD INDEX idx_source_hash (`source_hash`)");
    }

    @ $conn->query("CREATE TABLE IF NOT EXISTS subscriber_mac_history (
      id INT UNSIGNED NOT NULL AUTO_INCREMENT,
      subscriber_id INT NOT NULL,
      username VARCHAR(191) NULL,
      mac_address VARCHAR(32) NOT NULL,
      source VARCHAR(40) NULL,
      first_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      last_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      seen_count INT NOT NULL DEFAULT 1,
      PRIMARY KEY(id),
      UNIQUE KEY uniq_sub_mac (subscriber_id, mac_address),
      KEY idx_mac (mac_address),
      KEY idx_username (username)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    @ $conn->query("CREATE TABLE IF NOT EXISTS subscriber_network_alerts (
      id INT UNSIGNED NOT NULL AUTO_INCREMENT,
      alert_key VARCHAR(190) NOT NULL,
      severity VARCHAR(20) NOT NULL DEFAULT 'warn',
      title VARCHAR(191) NOT NULL,
      message TEXT NULL,
      subscriber_id INT NULL,
      username VARCHAR(191) NULL,
      mac_address VARCHAR(32) NULL,
      ip_address VARCHAR(45) NULL,
      source VARCHAR(40) NULL,
      is_open TINYINT(1) NOT NULL DEFAULT 1,
      first_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      last_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      hit_count INT NOT NULL DEFAULT 1,
      meta_json LONGTEXT NULL,
      PRIMARY KEY(id),
      UNIQUE KEY uniq_alert_key (alert_key),
      KEY idx_open_seen (is_open, last_seen_at),
      KEY idx_subscriber (subscriber_id),
      KEY idx_mac (mac_address)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    @ $conn->query("CREATE TABLE IF NOT EXISTS network_ip_watch (
      id INT UNSIGNED NOT NULL AUTO_INCREMENT,
      ip_address VARCHAR(45) NULL,
      mac_address VARCHAR(32) NOT NULL,
      interface_name VARCHAR(120) NULL,
      source VARCHAR(40) NOT NULL,
      subscriber_id INT NULL,
      username VARCHAR(191) NULL,
      is_pppoe_active TINYINT(1) NOT NULL DEFAULT 0,
      is_allowlisted TINYINT(1) NOT NULL DEFAULT 0,
      first_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      last_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      meta_json LONGTEXT NULL,
      PRIMARY KEY(id),
      UNIQUE KEY uniq_ip_mac_source (ip_address, mac_address, source),
      KEY idx_mac (mac_address),
      KEY idx_last_seen (last_seen_at),
      KEY idx_subscriber (subscriber_id),
      KEY idx_allowlisted (is_allowlisted)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    mtwatch_add_col($conn, 'subscriber_network_alerts', 'stage', "`stage` VARCHAR(30) NOT NULL DEFAULT 'confirmed'");
    mtwatch_add_col($conn, 'subscriber_network_alerts', 'decision_status', "`decision_status` VARCHAR(30) NULL");
    mtwatch_add_col($conn, 'subscriber_network_alerts', 'decision_note', "`decision_note` TEXT NULL");
    mtwatch_add_col($conn, 'subscriber_network_alerts', 'confirmed_at', "`confirmed_at` DATETIME NULL");
    mtwatch_add_col($conn, 'subscriber_network_alerts', 'updated_at', "`updated_at` DATETIME NULL");

    mtwatch_add_col($conn, 'network_ip_watch', 'is_allowlisted', "`is_allowlisted` TINYINT(1) NOT NULL DEFAULT 0");
    mtwatch_add_col($conn, 'network_ip_watch', 'seen_count', "`seen_count` INT NOT NULL DEFAULT 1");
    mtwatch_add_col($conn, 'network_ip_watch', 'stage', "`stage` VARCHAR(30) NOT NULL DEFAULT 'observed'");
    mtwatch_add_col($conn, 'network_ip_watch', 'decision_note', "`decision_note` TEXT NULL");

    @ $conn->query("CREATE TABLE IF NOT EXISTS mtwatch_allowlist (
      id INT UNSIGNED NOT NULL AUTO_INCREMENT,
      kind VARCHAR(30) NOT NULL,
      value1 VARCHAR(191) NOT NULL,
      value2 VARCHAR(191) NOT NULL DEFAULT '',
      label VARCHAR(191) NULL,
      note TEXT NULL,
      enabled TINYINT(1) NOT NULL DEFAULT 1,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NULL,
      PRIMARY KEY(id),
      UNIQUE KEY uniq_kind_value (kind, value1, value2),
      KEY idx_enabled_kind (enabled, kind)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    @ $conn->query("INSERT IGNORE INTO mtwatch_allowlist (kind,value1,value2,label,note,enabled) VALUES
      ('ip','10.30.98.250','','جهاز الإدارة','إضافة تلقائية لمنع إنذارات دخول IP بدون PPPoE من جهاز الإدارة.',1),
      ('interface','vlan99-gmt','','واجهة الإدارة','إضافة تلقائية لمنع إنذارات واجهة الإدارة من الظهور كإنذارات مشتركي PPPoE.',1)
    ");

    @ $conn->query("CREATE TABLE IF NOT EXISTS mtwatch_internal_devices (
      id INT UNSIGNED NOT NULL AUTO_INCREMENT,
      ip_address VARCHAR(45) NOT NULL,
      mac_address VARCHAR(32) NULL,
      label VARCHAR(191) NULL,
      role VARCHAR(40) NULL,
      source VARCHAR(80) NULL,
      is_confirmed TINYINT(1) NOT NULL DEFAULT 0,
      seen_count INT NOT NULL DEFAULT 0,
      first_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      last_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      meta_json LONGTEXT NULL,
      PRIMARY KEY(id),
      UNIQUE KEY uniq_ip (ip_address),
      KEY idx_mac (mac_address),
      KEY idx_confirmed_seen (is_confirmed, last_seen_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    @ $conn->query("CREATE TABLE IF NOT EXISTS mtwatch_router_user_state (
      id INT UNSIGNED NOT NULL AUTO_INCREMENT,
      router_key VARCHAR(120) NOT NULL DEFAULT 'default',
      user_name VARCHAR(191) NOT NULL,
      user_group VARCHAR(120) NULL,
      address VARCHAR(191) NULL,
      disabled VARCHAR(20) NULL,
      comment VARCHAR(500) NULL,
      state_hash CHAR(64) NOT NULL,
      first_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      last_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      missing_since DATETIME NULL,
      PRIMARY KEY(id),
      UNIQUE KEY uniq_router_user (router_key, user_name),
      KEY idx_router_seen (router_key, last_seen_at),
      KEY idx_missing (missing_since)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
  }
}

if (!function_exists('mtwatch_event_time')) {
  function mtwatch_event_time(string $raw): string {
    $raw = trim($raw);
    if ($raw === '') return date('Y-m-d H:i:s');
    $raw = str_replace('/', ' ', $raw);
    $ts = strtotime($raw);
    if (!$ts && preg_match('/^\d{2}:\d{2}:\d{2}$/', $raw)) $ts = strtotime(date('Y-m-d') . ' ' . $raw);
    if (!$ts || $ts < 946684800) return date('Y-m-d H:i:s');
    return date('Y-m-d H:i:s', $ts);
  }
}

if (!function_exists('mtwatch_log_event_unique')) {
  function mtwatch_log_event_unique(mysqli $conn, int $subscriberId, string $username, string $actionKey, string $label, string $details, ?string $eventAt = null, string $sourceHash = ''): void {
    if ($subscriberId <= 0) return;
    mtwatch_ensure_schema($conn);
    $eventAt = $eventAt ?: date('Y-m-d H:i:s');
    $username = mtwatch_norm_user($username);
    $hash = $sourceHash !== '' ? $sourceHash : hash('sha256', $subscriberId . '|' . $actionKey . '|' . $eventAt . '|' . $details);
    $he = $conn->real_escape_string($hash);
    if (mtwatch_col_exists($conn, 'subscriber_activity_log', 'source_hash')) {
      $chk = @$conn->query("SELECT id FROM subscriber_activity_log WHERE source_hash='{$he}' LIMIT 1");
      if ($chk && $chk->num_rows) return;
      $stmt = @$conn->prepare("INSERT INTO subscriber_activity_log (subscriber_id, username, action_key, action_label, details, event_at, created_at, source_hash) VALUES (?,?,?,?,?,?,NOW(),?)");
      if ($stmt) {
        $stmt->bind_param('issssss', $subscriberId, $username, $actionKey, $label, $details, $eventAt, $hash);
        @$stmt->execute();
        $stmt->close();
        return;
      }
    }
    $de = $conn->real_escape_string($details);
    $ae = $conn->real_escape_string($actionKey);
    $te = $conn->real_escape_string($eventAt);
    $chk = @$conn->query("SELECT id FROM subscriber_activity_log WHERE subscriber_id=".(int)$subscriberId." AND action_key='{$ae}' AND event_at='{$te}' AND details='{$de}' LIMIT 1");
    if ($chk && $chk->num_rows) return;
    @sh_log_event($conn, $subscriberId, $actionKey, $label, $details, $eventAt, $username);
  }
}

if (!function_exists('mtwatch_ip_in_cidr')) {
  function mtwatch_ip_in_cidr(string $ip, string $cidr): bool {
    $ip = trim($ip);
    $cidr = trim($cidr);
    if ($ip === '' || $cidr === '' || strpos($cidr, '/') === false) return false;
    [$net, $bits] = array_pad(explode('/', $cidr, 2), 2, '');
    $bits = (int)$bits;
    if ($bits < 0 || $bits > 32 || filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV4) === false || filter_var($net, FILTER_VALIDATE_IP, FILTER_FLAG_IPV4) === false) return false;
    $ipLong = sprintf('%u', ip2long($ip));
    $netLong = sprintf('%u', ip2long($net));
    $mask = $bits === 0 ? 0 : ((0xFFFFFFFF << (32 - $bits)) & 0xFFFFFFFF);
    return (((int)$ipLong & $mask) === ((int)$netLong & $mask));
  }
}

if (!function_exists('mtwatch_allowlist_match')) {
  function mtwatch_allowlist_match(mysqli $conn, string $mac = '', string $ip = '', string $iface = '', string $username = '', string $source = ''): array {
    mtwatch_ensure_schema($conn);
    if (!mtwatch_table_exists($conn, 'mtwatch_allowlist')) return ['matched'=>false, 'reason'=>''];
    $mac = mtwatch_norm_mac($mac);
    $ip = trim($ip);
    $ifaceNorm = mb_strtolower(trim($iface), 'UTF-8');
    $usernameNorm = mtwatch_norm_user($username);
    $sourceNorm = mb_strtolower(trim($source), 'UTF-8');
    $rs = @$conn->query("SELECT kind,value1,value2,label FROM mtwatch_allowlist WHERE enabled=1 ORDER BY id ASC");
    if (!$rs) return ['matched'=>false, 'reason'=>''];
    while ($r = $rs->fetch_assoc()) {
      $kind = mb_strtolower(trim((string)($r['kind'] ?? '')), 'UTF-8');
      $v1 = trim((string)($r['value1'] ?? ''));
      $v2 = trim((string)($r['value2'] ?? ''));
      $label = trim((string)($r['label'] ?? ''));
      $matched = false;
      if ($kind === 'mac') {
        $matched = ($mac !== '' && mtwatch_norm_mac($v1) === $mac);
      } elseif ($kind === 'ip') {
        $matched = ($ip !== '' && $ip === $v1);
      } elseif ($kind === 'ip_prefix') {
        $matched = ($ip !== '' && $v1 !== '' && strpos($ip, $v1) === 0);
      } elseif ($kind === 'ip_cidr' || $kind === 'subnet') {
        $matched = ($ip !== '' && mtwatch_ip_in_cidr($ip, $v1));
      } elseif ($kind === 'interface') {
        $matched = ($ifaceNorm !== '' && $ifaceNorm === mb_strtolower($v1, 'UTF-8'));
      } elseif ($kind === 'username') {
        $matched = ($usernameNorm !== '' && $usernameNorm === mtwatch_norm_user($v1));
      } elseif ($kind === 'source') {
        $matched = ($sourceNorm !== '' && $sourceNorm === mb_strtolower($v1, 'UTF-8'));
      } elseif ($kind === 'mac_ip') {
        $matched = ($mac !== '' && $ip !== '' && mtwatch_norm_mac($v1) === $mac && $ip === $v2);
      }
      if ($matched) return ['matched'=>true, 'reason'=>($label !== '' ? $label : ($kind . ':' . $v1))];
    }
    if (function_exists('mtwatch_internal_device_match')) {
      $internal = mtwatch_internal_device_match($conn, $mac, $ip);
      if (!empty($internal['matched'])) return ['matched'=>true, 'reason'=>(string)($internal['reason'] ?? 'جهاز بنية داخلية معروف')];
    }
    return ['matched'=>false, 'reason'=>''];
  }
}

if (!function_exists('mtwatch_valid_ipv4_plain')) {
  function mtwatch_valid_ipv4_plain(string $ip): bool {
    $ip = trim($ip);
    return $ip !== '' && filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV4) !== false;
  }
}

if (!function_exists('mtwatch_internal_targets')) {
  function mtwatch_internal_targets(mysqli $conn): array {
    mtwatch_ensure_schema($conn);
    $targets = [];
    $add = static function(string $ip, string $label, string $role, string $source) use (&$targets): void {
      $ip = trim($ip);
      if ($ip === '' || strpos($ip, '/') !== false || !mtwatch_valid_ipv4_plain($ip)) return;
      $targets[$ip] = [
        'ip'=>$ip,
        'label'=>trim($label) !== '' ? trim($label) : $ip,
        'role'=>trim($role) !== '' ? trim($role) : 'infrastructure',
        'source'=>trim($source) !== '' ? trim($source) : 'internal_targets',
      ];
    };

    if (mtwatch_table_exists($conn, 'routers')) {
      $cols = ['host'];
      foreach (['name','role','model','identity'] as $c) if (mtwatch_col_exists($conn, 'routers', $c)) $cols[] = $c;
      $where = mtwatch_col_exists($conn, 'routers', 'is_enabled') ? 'WHERE COALESCE(is_enabled,1)=1' : '';
      $rs = @$conn->query('SELECT ' . implode(',', array_map(static fn($c) => '`'.$c.'`', array_unique($cols))) . " FROM routers {$where}");
      if ($rs) while ($r = $rs->fetch_assoc()) {
        $label = (string)($r['name'] ?? $r['identity'] ?? $r['model'] ?? 'Router');
        $add((string)($r['host'] ?? ''), $label, 'router', 'routers');
      }
    }

    if (mtwatch_table_exists($conn, 'mikrotik_settings')) {
      $rs = @$conn->query("SELECT host FROM mikrotik_settings WHERE COALESCE(host,'')<>'' ORDER BY id ASC");
      if ($rs) while ($r = $rs->fetch_assoc()) $add((string)($r['host'] ?? ''), 'MikroTik الرئيسي', 'router', 'mikrotik_settings');
    }

    if (mtwatch_table_exists($conn, 'airmax_devices')) {
      $cols = ['host'];
      foreach (['name','device_type','preferred_vendor','detected_vendor','detected_os'] as $c) if (mtwatch_col_exists($conn, 'airmax_devices', $c)) $cols[] = $c;
      $where = mtwatch_col_exists($conn, 'airmax_devices', 'is_enabled') ? 'WHERE COALESCE(is_enabled,1)=1' : '';
      $rs = @$conn->query('SELECT ' . implode(',', array_map(static fn($c) => '`'.$c.'`', array_unique($cols))) . " FROM airmax_devices {$where}");
      if ($rs) while ($r = $rs->fetch_assoc()) {
        $roleRaw = strtolower(trim((string)($r['device_type'] ?? '')));
        $role = in_array($roleRaw, ['station','receiver','client','cpe','powerbeam'], true) ? 'receiver' : 'sector_or_access';
        $label = (string)($r['name'] ?? 'Wireless device');
        $add((string)($r['host'] ?? ''), $label, $role, 'airmax_devices');
      }
    }

    return $targets;
  }
}

if (!function_exists('mtwatch_internal_device_close_alerts')) {
  function mtwatch_internal_device_close_alerts(mysqli $conn, string $mac, string $ip, string $note): void {
    $mac = mtwatch_norm_mac($mac);
    $ip = trim($ip);
    $noteEsc = $conn->real_escape_string($note !== '' ? $note : 'تم استثناء جهاز بنية داخلية معروف.');
    $where = [];
    if ($mac !== '') $where[] = "mac_address='" . $conn->real_escape_string($mac) . "'";
    if ($ip !== '') $where[] = "ip_address='" . $conn->real_escape_string($ip) . "'";
    if (!$where) return;
    @ $conn->query("UPDATE subscriber_network_alerts SET is_open=0, stage='resolved', decision_status='internal_device', decision_note='{$noteEsc}', updated_at=NOW(), last_seen_at=NOW() WHERE is_open=1 AND (" . implode(' OR ', $where) . ")");
  }
}

if (!function_exists('mtwatch_register_internal_device')) {
  function mtwatch_register_internal_device(mysqli $conn, string $ip, string $mac, string $label, string $role, string $source, bool $forceConfirmed = false, array $meta = []): array {
    mtwatch_ensure_schema($conn);
    $ip = trim($ip);
    $mac = mtwatch_norm_mac($mac);
    if ($ip === '' || !mtwatch_valid_ipv4_plain($ip)) return ['ok'=>false,'reason'=>'bad_ip'];
    $label = mb_substr(trim($label) !== '' ? trim($label) : $ip, 0, 191, 'UTF-8');
    $role = mb_substr(trim($role) !== '' ? trim($role) : 'infrastructure', 0, 40, 'UTF-8');
    $source = mb_substr(trim($source) !== '' ? trim($source) : 'internal', 0, 80, 'UTF-8');
    $metaJson = json_encode($meta, JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES);
    if (!is_string($metaJson)) $metaJson = '{}';
    $ipEsc = $conn->real_escape_string($ip);
    $rs = @$conn->query("SELECT * FROM mtwatch_internal_devices WHERE ip_address='{$ipEsc}' LIMIT 1");
    $old = $rs ? $rs->fetch_assoc() : null;
    $seen = max(1, (int)($old['seen_count'] ?? 0) + ($mac !== '' ? 1 : 0));
    $oldMac = mtwatch_norm_mac((string)($old['mac_address'] ?? ''));
    $stable = ($mac !== '' && ($oldMac === '' || $oldMac === $mac) && $seen >= 2);
    $confirmed = $forceConfirmed || !empty($old['is_confirmed']) || $stable;
    $macForDb = $mac !== '' ? $mac : $oldMac;
    if ($old) {
      $stmt = @$conn->prepare("UPDATE mtwatch_internal_devices SET mac_address=NULLIF(?,''), label=?, role=?, source=?, is_confirmed=?, seen_count=?, last_seen_at=NOW(), meta_json=? WHERE ip_address=? LIMIT 1");
      if ($stmt) {
        $conf = $confirmed ? 1 : 0;
        $stmt->bind_param('ssssiiss', $macForDb, $label, $role, $source, $conf, $seen, $metaJson, $ip);
        @$stmt->execute(); $stmt->close();
      }
    } else {
      $stmt = @$conn->prepare("INSERT INTO mtwatch_internal_devices (ip_address, mac_address, label, role, source, is_confirmed, seen_count, first_seen_at, last_seen_at, meta_json) VALUES (?,NULLIF(?,''),?,?,?,?,NOW(),NOW(),?)");
      if ($stmt) {
        $conf = $confirmed ? 1 : 0;
        $stmt->bind_param('ssssiss', $ip, $macForDb, $label, $role, $source, $conf, $seen, $metaJson);
        @$stmt->execute(); $stmt->close();
      }
    }
    if ($confirmed) {
      $note = 'تم التعرف تلقائياً على جهاز بنية داخلية: ' . $label;
      $ipDb = $conn->real_escape_string($ip);
      $macDb = $conn->real_escape_string($macForDb);
      @ $conn->query("UPDATE network_ip_watch SET is_allowlisted=1, stage='allowlisted', decision_note='" . $conn->real_escape_string($note) . "' WHERE ip_address='{$ipDb}'" . ($macForDb !== '' ? " OR mac_address='{$macDb}'" : ""));
      mtwatch_internal_device_close_alerts($conn, $macForDb, $ip, $note);
    }
    return ['ok'=>true,'confirmed'=>$confirmed,'ip'=>$ip,'mac'=>$macForDb,'label'=>$label,'role'=>$role,'seen_count'=>$seen];
  }
}

if (!function_exists('mtwatch_internal_device_match')) {
  function mtwatch_internal_device_match(mysqli $conn, string $mac = '', string $ip = ''): array {
    mtwatch_ensure_schema($conn);
    $mac = mtwatch_norm_mac($mac);
    $ip = trim($ip);
    $conds = [];
    if ($ip !== '') $conds[] = "ip_address='" . $conn->real_escape_string($ip) . "'";
    if ($mac !== '') $conds[] = "mac_address='" . $conn->real_escape_string($mac) . "'";
    if (!$conds) return ['matched'=>false,'reason'=>''];
    $rs = @$conn->query("SELECT label,role,source,is_confirmed FROM mtwatch_internal_devices WHERE is_confirmed=1 AND (" . implode(' OR ', $conds) . ") ORDER BY last_seen_at DESC LIMIT 1");
    $r = $rs ? $rs->fetch_assoc() : null;
    if (!$r) return ['matched'=>false,'reason'=>''];
    $label = trim((string)($r['label'] ?? 'جهاز بنية داخلية'));
    return ['matched'=>true,'reason'=>$label !== '' ? $label : 'جهاز بنية داخلية معروف','role'=>(string)($r['role'] ?? ''),'source'=>(string)($r['source'] ?? '')];
  }
}

if (!function_exists('mtwatch_auto_filter_internal_observations')) {
  function mtwatch_auto_filter_internal_observations(mysqli $conn, array $observedDevices = []): array {
    mtwatch_ensure_schema($conn);
    $targets = mtwatch_internal_targets($conn);
    $registered = 0; $confirmed = 0; $marked = 0;

    foreach ($observedDevices as $d) {
      if (!is_array($d)) continue;
      $ip = trim((string)($d['ip'] ?? ''));
      if ($ip === '' || empty($targets[$ip])) continue;
      $t = $targets[$ip];
      $res = mtwatch_register_internal_device($conn, $ip, (string)($d['mac'] ?? ''), (string)$t['label'], (string)$t['role'], (string)$t['source'], false, ['observed'=>$d]);
      if (!empty($res['ok'])) $registered++;
      if (!empty($res['confirmed'])) $confirmed++;
    }

    if ($targets && mtwatch_table_exists($conn, 'network_ip_watch')) {
      $ips = array_keys($targets);
      $in = "'" . implode("','", array_map([$conn, 'real_escape_string'], $ips)) . "'";
      $rs = @$conn->query("SELECT ip_address, mac_address, MAX(seen_count) AS seen_count FROM network_ip_watch WHERE ip_address IN ({$in}) GROUP BY ip_address, mac_address");
      if ($rs) while ($r = $rs->fetch_assoc()) {
        $ip = trim((string)($r['ip_address'] ?? ''));
        if ($ip === '' || empty($targets[$ip])) continue;
        $t = $targets[$ip];
        $force = (int)($r['seen_count'] ?? 0) >= 2;
        $res = mtwatch_register_internal_device($conn, $ip, (string)($r['mac_address'] ?? ''), (string)$t['label'], (string)$t['role'], (string)$t['source'], $force, ['network_ip_watch_seen_count'=>(int)($r['seen_count'] ?? 0)]);
        if (!empty($res['ok'])) $registered++;
        if (!empty($res['confirmed'])) $confirmed++;
      }
    }

    $rs = @$conn->query("SELECT ip_address, mac_address, label FROM mtwatch_internal_devices WHERE is_confirmed=1");
    if ($rs) while ($r = $rs->fetch_assoc()) {
      $ip = trim((string)($r['ip_address'] ?? ''));
      $mac = mtwatch_norm_mac((string)($r['mac_address'] ?? ''));
      $label = trim((string)($r['label'] ?? 'جهاز بنية داخلية'));
      $note = 'تم استثناء جهاز بنية داخلية معروف: ' . $label;
      $where = [];
      if ($ip !== '') $where[] = "ip_address='" . $conn->real_escape_string($ip) . "'";
      if ($mac !== '') $where[] = "mac_address='" . $conn->real_escape_string($mac) . "'";
      if ($where) {
        $ok = @$conn->query("UPDATE network_ip_watch SET is_allowlisted=1, stage='allowlisted', decision_note='" . $conn->real_escape_string($note) . "' WHERE " . implode(' OR ', $where));
        if ($ok) $marked += max(0, (int)$conn->affected_rows);
        mtwatch_internal_device_close_alerts($conn, $mac, $ip, $note);
      }
    }
    return ['ok'=>true,'targets'=>count($targets),'registered'=>$registered,'confirmed'=>$confirmed,'marked'=>$marked];
  }
}


if (!function_exists('mtwatch_alert')) {
  function mtwatch_alert(mysqli $conn, string $key, string $severity, string $title, string $message, ?int $subscriberId = null, string $username = '', string $mac = '', string $ip = '', string $source = 'watch', array $meta = []): void {
    mtwatch_ensure_schema($conn);
    $key = substr(preg_replace('/\s+/', '_', trim($key)) ?? '', 0, 190);
    if ($key === '') return;
    $severity = in_array($severity, ['info','warn','danger','success'], true) ? $severity : 'warn';
    $mac = mtwatch_norm_mac($mac);
    $username = mtwatch_norm_user($username);
    $allowIface = is_array($meta) ? (string)($meta['interface'] ?? '') : '';
    if (function_exists('mtwatch_allowlist_match')) {
      $allow = mtwatch_allowlist_match($conn, $mac, $ip, $allowIface, $username, $source);
      if (!empty($allow['matched'])) return;
    }
    $keyEsc = $conn->real_escape_string($key);
    $existing = @$conn->query("SELECT is_open, decision_status, updated_at FROM subscriber_network_alerts WHERE alert_key='{$keyEsc}' LIMIT 1");
    if ($existing && ($old = $existing->fetch_assoc())) {
      $manualStatus = strtolower(trim((string)($old['decision_status'] ?? '')));
      $updatedAt = (string)($old['updated_at'] ?? '');
      $updatedTs = $updatedAt !== '' ? (int)strtotime($updatedAt) : 0;
      $quietUntil = $updatedTs > 0 ? ($updatedTs + 3 * 86400) : 0;
      if ((int)($old['is_open'] ?? 1) === 0 && in_array($manualStatus, ['closed','allowed'], true) && $quietUntil > time()) {
        return;
      }
    }
    $stage = 'confirmed';
    if (is_array($meta) && isset($meta['stage'])) {
      $stageRaw = strtolower(trim((string)$meta['stage']));
      if (in_array($stageRaw, ['learning','identifying','suspect','confirmed','closed','allowlisted','resolved'], true)) $stage = $stageRaw;
    }
    $json = json_encode($meta, JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES);
    if (!is_string($json)) $json = '{}';
    $stmt = @$conn->prepare("INSERT INTO subscriber_network_alerts
      (alert_key, severity, title, message, subscriber_id, username, mac_address, ip_address, source, is_open, first_seen_at, last_seen_at, hit_count, meta_json, stage, confirmed_at)
      VALUES (?,?,?,?,?,?,?,?,?,1,NOW(),NOW(),1,?,?,IF(?='confirmed',NOW(),NULL))
      ON DUPLICATE KEY UPDATE severity=VALUES(severity), title=VALUES(title), message=VALUES(message), subscriber_id=VALUES(subscriber_id), username=VALUES(username), mac_address=VALUES(mac_address), ip_address=VALUES(ip_address), source=VALUES(source), is_open=1, last_seen_at=NOW(), hit_count=hit_count+1, meta_json=VALUES(meta_json), stage=VALUES(stage), confirmed_at=IF(VALUES(stage)='confirmed' AND confirmed_at IS NULL,NOW(),confirmed_at), updated_at=NOW()");
    if (!$stmt) return;
    $sid = $subscriberId ?: null;
    $stmt->bind_param('ssssisssssss', $key, $severity, $title, $message, $sid, $username, $mac, $ip, $source, $json, $stage, $stage);
    @$stmt->execute();
    $stmt->close();
  }
}


if (!function_exists('mtwatch_mac_info')) {
  function mtwatch_mac_info(mysqli $conn, string $mac): array {
    return function_exists('macvendor_identify') ? macvendor_identify($conn, $mac) : ['vendor'=>'غير معروف','device_type'=>'غير معروف','label'=>'غير معروف'];
  }
}

if (!function_exists('mtwatch_mac_suffix')) {
  function mtwatch_mac_suffix(mysqli $conn, string $mac): string {
    return function_exists('macvendor_suffix') ? macvendor_suffix($conn, $mac) : '';
  }
}

if (!function_exists('mtwatch_record_mac')) {
  function mtwatch_record_mac(mysqli $conn, int $subscriberId, string $username, string $mac, string $source = 'unknown', ?string $seenAt = null): void {
    $mac = mtwatch_norm_mac($mac);
    if ($subscriberId <= 0 || $mac === '') return;
    mtwatch_ensure_schema($conn);
    $username = mtwatch_norm_user($username);
    $seenAt = $seenAt ?: date('Y-m-d H:i:s');
    $stmt = @$conn->prepare("INSERT INTO subscriber_mac_history (subscriber_id, username, mac_address, source, first_seen_at, last_seen_at, seen_count)
      VALUES (?,?,?,?,?,?,1)
      ON DUPLICATE KEY UPDATE username=VALUES(username), source=VALUES(source), last_seen_at=VALUES(last_seen_at), seen_count=seen_count+1");
    if (!$stmt) return;
    $stmt->bind_param('isssss', $subscriberId, $username, $mac, $source, $seenAt, $seenAt);
    @$stmt->execute();
    $stmt->close();
  }
}

if (!function_exists('mtwatch_subscribers_by_user')) {
  function mtwatch_subscribers_by_user(mysqli $conn): array {
    mtwatch_ensure_schema($conn);
    if (!mtwatch_table_exists($conn, 'subscribers')) return [];
    $hasPpp = mtwatch_col_exists($conn, 'subscribers', 'ppp_username');
    $hasDel = mtwatch_col_exists($conn, 'subscribers', 'is_deleted');
    $userExpr = $hasPpp ? "SUBSTRING_INDEX(COALESCE(NULLIF(ppp_username,''), username),'@',1)" : "SUBSTRING_INDEX(username,'@',1)";
    $where = $hasDel ? "WHERE COALESCE(is_deleted,0)=0" : '';
    $rs = @$conn->query("SELECT *, {$userExpr} AS __ppp_user FROM subscribers {$where}");
    $out = [];
    if ($rs) while ($r = $rs->fetch_assoc()) {
      $u = mtwatch_norm_user((string)($r['__ppp_user'] ?? ''));
      if ($u !== '') $out[$u] = $r;
    }
    return $out;
  }
}

if (!function_exists('mtwatch_subscribers_by_mac')) {
  function mtwatch_subscribers_by_mac(mysqli $conn): array {
    mtwatch_ensure_schema($conn);
    $out = [];
    if (mtwatch_table_exists($conn, 'subscribers')) {
      $cols = ['last_caller_id','current_caller_id'];
      foreach ($cols as $col) {
        if (!mtwatch_col_exists($conn, 'subscribers', $col)) continue;
        $rs = @$conn->query("SELECT id, full_name, username, ppp_username, {$col} AS mac FROM subscribers WHERE {$col} IS NOT NULL AND {$col}<>''");
        if ($rs) while ($r = $rs->fetch_assoc()) {
          $mac = mtwatch_norm_mac((string)($r['mac'] ?? ''));
          if ($mac !== '' && !isset($out[$mac])) $out[$mac] = $r;
        }
      }
    }
    if (mtwatch_table_exists($conn, 'subscriber_mac_history')) {
      $rs = @$conn->query("SELECT h.mac_address AS mac, h.subscriber_id AS id, h.username, s.full_name, s.ppp_username FROM subscriber_mac_history h LEFT JOIN subscribers s ON s.id=h.subscriber_id ORDER BY h.last_seen_at DESC");
      if ($rs) while ($r = $rs->fetch_assoc()) {
        $mac = mtwatch_norm_mac((string)($r['mac'] ?? ''));
        if ($mac !== '' && !isset($out[$mac])) $out[$mac] = $r;
      }
    }
    return $out;
  }
}

if (!function_exists('mtwatch_ppp_uptime_seconds')) {
  function mtwatch_ppp_uptime_seconds(string $uptime): int {
    $uptime = trim($uptime);
    if ($uptime === '') return 0;
    $seconds = 0;
    if (preg_match_all('/(\d+)\s*(w|d|h|m|s)/i', $uptime, $matches, PREG_SET_ORDER)) {
      foreach ($matches as $m) {
        $n = (int)$m[1];
        $unit = strtolower((string)$m[2]);
        if ($unit === 'w') $seconds += $n * 604800;
        elseif ($unit === 'd') $seconds += $n * 86400;
        elseif ($unit === 'h') $seconds += $n * 3600;
        elseif ($unit === 'm') $seconds += $n * 60;
        elseif ($unit === 's') $seconds += $n;
      }
    } elseif (preg_match('/^(\d+):(\d+):(\d+)$/', $uptime, $m)) {
      $seconds = ((int)$m[1] * 3600) + ((int)$m[2] * 60) + (int)$m[3];
    }
    return max(0, $seconds);
  }
}

if (!function_exists('mtwatch_session_started_at')) {
  function mtwatch_session_started_at(string $eventAt, string $uptime): string {
    $ts = strtotime($eventAt) ?: time();
    $up = mtwatch_ppp_uptime_seconds($uptime);
    if ($up <= 0) return date('Y-m-d H:i:s', $ts);
    return date('Y-m-d H:i:s', max(946684800, $ts - $up));
  }
}

if (!function_exists('mtwatch_latest_activity_state_key')) {
  function mtwatch_latest_activity_state_key(mysqli $conn, int $subscriberId): string {
    if ($subscriberId <= 0 || !mtwatch_table_exists($conn, 'subscriber_activity_log')) return '';
    $rs = @$conn->query("SELECT action_key FROM subscriber_activity_log WHERE subscriber_id=".(int)$subscriberId." AND action_key IN ('CONNECTED','DISCONNECTED','MT_LOGGED_OUT') ORDER BY event_at DESC, id DESC LIMIT 1");
    if (!$rs || !$rs->num_rows) return '';
    $row = $rs->fetch_assoc();
    return strtoupper(trim((string)($row['action_key'] ?? '')));
  }
}

if (!function_exists('mtwatch_log_active_connection_if_needed')) {
  function mtwatch_log_active_connection_if_needed(mysqli $conn, int $subscriberId, string $username, string $mac, string $ip, string $uptime, string $eventAt): void {
    if ($subscriberId <= 0) return;
    $latestState = mtwatch_latest_activity_state_key($conn, $subscriberId);
    if ($latestState === 'CONNECTED') return;
    $sessionAt = mtwatch_session_started_at($eventAt, $uptime);
    $parts = ['تم رصد جلسة PPPoE نشطة من MikroTik'];
    if ($ip !== '') $parts[] = 'IP: ' . $ip;
    if ($mac !== '') $parts[] = 'MAC: ' . $mac;
    if ($uptime !== '') $parts[] = 'مدة الاتصال: ' . $uptime;
    if ($uptime !== '' && $sessionAt !== $eventAt) $parts[] = 'بداية الجلسة التقريبية: ' . $sessionAt;
    $details = implode(' - ', $parts);
    mtwatch_log_event_unique(
      $conn,
      $subscriberId,
      $username,
      'CONNECTED',
      'اتصال',
      $details,
      $eventAt,
      hash('sha256', 'active_connected|' . $subscriberId . '|' . $eventAt . '|' . $mac . '|' . $ip)
    );
    if (function_exists('sh_mark_first_activation')) {
      @sh_mark_first_activation($conn, $subscriberId, $sessionAt, 'أول ظهور للمشترك أونلاين');
    }
  }
}

if (!function_exists('mtwatch_update_subscriber_live')) {
  function mtwatch_update_subscriber_live(mysqli $conn, array $sub, string $user, string $mac, string $ip, string $uptime, ?string $eventAt = null, string $source = 'active'): void {
    $sid = (int)($sub['id'] ?? 0);
    if ($sid <= 0) return;
    $mac = mtwatch_norm_mac($mac);
    $user = mtwatch_norm_user($user);
    $eventAt = $eventAt ?: date('Y-m-d H:i:s');
    $sets = [];
    if (mtwatch_col_exists($conn,'subscribers','is_online')) $sets[] = "is_online=1";
    if (mtwatch_col_exists($conn,'subscribers','last_seen_at')) $sets[] = "last_seen_at='".$conn->real_escape_string($eventAt)."'";
    if (mtwatch_col_exists($conn,'subscribers','current_ip')) $sets[] = "current_ip='".$conn->real_escape_string($ip)."'";
    if (mtwatch_col_exists($conn,'subscribers','current_caller_id')) $sets[] = "current_caller_id='".$conn->real_escape_string($mac)."'";
    if (mtwatch_col_exists($conn,'subscribers','current_uptime')) $sets[] = "current_uptime='".$conn->real_escape_string($uptime)."'";
    if (mtwatch_col_exists($conn,'subscribers','mt_watch_sync_at')) $sets[] = "mt_watch_sync_at=NOW()";

    $oldMac = mtwatch_norm_mac((string)($sub['last_caller_id'] ?? ''));
    if ($mac !== '') {
      $dev = mtwatch_mac_info($conn, $mac);
      $devSuffix = mtwatch_mac_suffix($conn, $mac);
      mtwatch_record_mac($conn, $sid, $user, $mac, $source, $eventAt);
      if (mtwatch_col_exists($conn,'subscribers','current_mac_vendor')) $sets[] = "current_mac_vendor='".$conn->real_escape_string((string)($dev['vendor'] ?? ''))."'";
      if (mtwatch_col_exists($conn,'subscribers','current_device_type')) $sets[] = "current_device_type='".$conn->real_escape_string((string)($dev['device_type'] ?? ''))."'";
      if (mtwatch_col_exists($conn,'subscribers','last_mac_vendor')) $sets[] = "last_mac_vendor='".$conn->real_escape_string((string)($dev['vendor'] ?? ''))."'";
      if (mtwatch_col_exists($conn,'subscribers','last_device_type')) $sets[] = "last_device_type='".$conn->real_escape_string((string)($dev['device_type'] ?? ''))."'";
      if ($oldMac !== '' && $oldMac !== $mac) {
        $details = "تغيّر MAC للمشترك من {$oldMac} إلى {$mac}" . ($ip !== '' ? " - IP: {$ip}" : '') . $devSuffix;
        $hash = hash('sha256', 'mac_change|'.$sid.'|'.$oldMac.'|'.$mac.'|'.$eventAt);
        mtwatch_log_event_unique($conn, $sid, $user, 'MAC_CHANGED', 'تغيّر MAC Address', $details, $eventAt, $hash);
        mtwatch_alert($conn, 'mac_changed:'.$sid.':'.$oldMac.':'.$mac, 'danger', 'تغيّر MAC Address', $details, $sid, $user, $mac, $ip, 'active');
        if (mtwatch_col_exists($conn,'subscribers','mt_mac_last_changed_at')) $sets[] = "mt_mac_last_changed_at='".$conn->real_escape_string($eventAt)."'";
      }
      if (mtwatch_col_exists($conn,'subscribers','last_caller_id')) $sets[] = "last_caller_id='".$conn->real_escape_string($mac)."'";
    }
    if ($sets) @ $conn->query("UPDATE subscribers SET ".implode(',', $sets)." WHERE id={$sid} LIMIT 1");
    mtwatch_log_active_connection_if_needed($conn, $sid, $user, $mac, $ip, $uptime, $eventAt);
  }
}

if (!function_exists('mtwatch_reconcile_inactive_subscribers')) {
  function mtwatch_reconcile_inactive_subscribers(mysqli $conn, array $activeUsers, string $eventAt): int {
    mtwatch_ensure_schema($conn);
    if (!mtwatch_table_exists($conn, 'subscribers') || !mtwatch_col_exists($conn, 'subscribers', 'is_online')) return 0;
    $hasPpp = mtwatch_col_exists($conn, 'subscribers', 'ppp_username');
    $hasDel = mtwatch_col_exists($conn, 'subscribers', 'is_deleted');
    $hasDisc = mtwatch_col_exists($conn, 'subscribers', 'last_disconnect_at');
    $userExpr = $hasPpp ? "SUBSTRING_INDEX(COALESCE(NULLIF(ppp_username,''), username),'@',1)" : "SUBSTRING_INDEX(username,'@',1)";
    $where = "COALESCE(is_online,0)=1";
    if ($hasDel) $where .= " AND COALESCE(is_deleted,0)=0";
    $activeUsers = array_values(array_unique(array_filter(array_map('mtwatch_norm_user', $activeUsers))));
    if (!$activeUsers) return 0;
    if ($activeUsers) {
      $in = "'" . implode("','", array_map([$conn, 'real_escape_string'], $activeUsers)) . "'";
      $where .= " AND {$userExpr} NOT IN ({$in})";
    }
    $rs = @$conn->query("SELECT id, username, ppp_username FROM subscribers WHERE {$where}");
    if (!$rs) return 0;
    $offline = [];
    while ($r = $rs->fetch_assoc()) $offline[] = $r;
    if (!$offline) return 0;

    $ids = implode(',', array_map(fn($r) => (int)($r['id'] ?? 0), $offline));
    $eventEsc = $conn->real_escape_string($eventAt);
    $sets = "is_online=0";
    if ($hasDisc) $sets .= ", last_disconnect_at='{$eventEsc}'";
    if (mtwatch_col_exists($conn, 'subscribers', 'current_uptime')) $sets .= ", current_uptime=''";
    @ $conn->query("UPDATE subscribers SET {$sets} WHERE id IN ({$ids})");

    foreach ($offline as $r) {
      $sid = (int)($r['id'] ?? 0);
      if ($sid <= 0) continue;
      $user = mtwatch_norm_user((string)($r['ppp_username'] ?? ''));
      if ($user === '') $user = mtwatch_norm_user((string)($r['username'] ?? ''));
      mtwatch_log_event_unique(
        $conn,
        $sid,
        $user,
        'DISCONNECTED',
        'انقطاع',
        'لم تعد جلسة PPPoE ظاهرة ضمن قائمة MikroTik النشطة',
        $eventAt,
        hash('sha256', 'active_disconnected|' . $sid . '|' . $eventAt)
      );
    }
    return count($offline);
  }
}

if (!function_exists('mtwatch_is_pool_ip')) {
  function mtwatch_is_pool_ip(string $ip): bool {
    return (bool)preg_match('/^10\.10\.(10|11|12|13)\./', trim($ip));
  }
}



if (!function_exists('mtwatch_close_alert_key')) {
  function mtwatch_close_alert_key(mysqli $conn, string $key, string $status = 'resolved', string $note = 'تمت المطابقة ولم تعد الحالة نشطة.'): void {
    mtwatch_ensure_schema($conn);
    $key = substr(preg_replace('/\s+/', '_', trim($key)) ?? '', 0, 190);
    if ($key === '') return;
    $ke = $conn->real_escape_string($key);
    $st = $conn->real_escape_string(substr($status, 0, 30));
    $no = $conn->real_escape_string($note);
    @ $conn->query("UPDATE subscriber_network_alerts SET is_open=0, stage='{$st}', decision_status='{$st}', decision_note='{$no}', updated_at=NOW(), last_seen_at=NOW() WHERE alert_key='{$ke}' AND is_open=1 LIMIT 1");
  }
}


if (!function_exists('mtwatch_source_hash_seen')) {
  function mtwatch_source_hash_seen(mysqli $conn, string $hash): bool {
    $hash = trim($hash);
    if ($hash === '' || !mtwatch_table_exists($conn, 'subscriber_activity_log') || !mtwatch_col_exists($conn, 'subscriber_activity_log', 'source_hash')) return false;
    $he = $conn->real_escape_string($hash);
    $rs = @$conn->query("SELECT id FROM subscriber_activity_log WHERE source_hash='{$he}' LIMIT 1");
    return (bool)($rs && $rs->num_rows > 0);
  }
}

if (!function_exists('mtwatch_active_index_from_rows')) {
  function mtwatch_active_index_from_rows(array $rows): array {
    $out = [];
    foreach ($rows as $r) {
      if (!is_array($r)) continue;
      $user = mtwatch_norm_user((string)($r['name'] ?? $r['user'] ?? $r['username'] ?? ''));
      if ($user === '') continue;
      $mac = mtwatch_norm_mac((string)($r['caller_id'] ?? $r['caller-id'] ?? $r['mac'] ?? ''));
      $ip = trim((string)($r['address'] ?? $r['ip'] ?? ''));
      $uptime = trim((string)($r['uptime'] ?? ''));
      $out[$user] = ['user'=>$user, 'mac'=>$mac, 'ip'=>$ip, 'uptime'=>$uptime];
    }
    return $out;
  }
}

if (!function_exists('mtwatch_extract_mac_from_log')) {
  function mtwatch_extract_mac_from_log(string $msg): string {
    $macRe = '([0-9A-Fa-f]{2}[:\-\.][0-9A-Fa-f]{2}[:\-\.][0-9A-Fa-f]{2}[:\-\.][0-9A-Fa-f]{2}[:\-\.][0-9A-Fa-f]{2}[:\-\.][0-9A-Fa-f]{2})';
    foreach ([
      '/\bfrom\s+' . $macRe . '/iu',
      '/\b(?:caller[-\s]?id|callerid|mac(?:[-\s]?address)?)\s*[=:]?\s*' . $macRe . '/iu',
      '/\b' . $macRe . '\b/u',
    ] as $re) {
      if (preg_match($re, $msg, $m)) {
        $raw = end($m);
        $mac = mtwatch_norm_mac((string)$raw);
        if ($mac !== '') return $mac;
      }
    }
    return '';
  }
}

if (!function_exists('mtwatch_parse_pppoe_duplicate_login_attempt')) {
  function mtwatch_parse_pppoe_duplicate_login_attempt(string $msg): ?array {
    $msg = trim($msg);
    if ($msg === '') return null;
    $strong = (bool)preg_match('/\b(already\s+(?:logged\s+in|active|connected)|is\s+already\s+logged\s+in|only[-\s]?one|simultaneous|duplicate\s+(?:login|session)|another\s+session|session\s+already)\b/iu', $msg);
    $authFailed = (bool)preg_match('/\b(authentication\s+failed|failed\s+to\s+authenticate|login\s+failed|invalid\s+password|password\s+failed|not\s+authenticated|chap\s+failure|pap\s+failure)\b/iu', $msg);
    if (!$strong && !$authFailed) return null;

    $user = '';
    $patterns = [
      '/<pppoe-([^>]+)>/iu',
      '/\buser\s*[=:]?\s*([^\s,;:\[\]\(\)<>]+)/iu',
      '/\bname\s*[=:]?\s*([^\s,;:\[\]\(\)<>]+)/iu',
      '/^([^\s:<]+)\s+.*?(?:authentication\s+failed|failed\s+to\s+authenticate|login\s+failed|already|only[-\s]?one|simultaneous|duplicate|another\s+session)/iu',
    ];
    foreach ($patterns as $re) {
      if (preg_match($re, $msg, $m)) {
        $cand = mtwatch_norm_user((string)$m[1]);
        if ($cand !== '' && !preg_match('/^(ppp|pppoe|info|error|warning|system)$/iu', $cand)) { $user = $cand; break; }
      }
    }
    if ($user === '') return null;
    return ['user'=>$user, 'attempt_mac'=>mtwatch_extract_mac_from_log($msg), 'strong'=>$strong, 'auth_failed'=>$authFailed];
  }
}

if (!function_exists('mtwatch_process_pppoe_duplicate_login_attempt')) {
  function mtwatch_process_pppoe_duplicate_login_attempt(mysqli $conn, array $parsed, array $activeByUser, array $subsByUser, string $time, string $topics, string $msg): bool {
    $user = mtwatch_norm_user((string)($parsed['user'] ?? ''));
    if ($user === '' || !isset($subsByUser[$user])) return false;
    $active = $activeByUser[$user] ?? null;
    if (!$active) return false;
    $activeMac = mtwatch_norm_mac((string)($active['mac'] ?? ''));
    $attemptMac = mtwatch_norm_mac((string)($parsed['attempt_mac'] ?? ''));
    $activeIp = trim((string)($active['ip'] ?? ''));
    if ($activeMac === '') return false;
    if ($attemptMac !== '' && $attemptMac === $activeMac) return false;
    if ($attemptMac === '' && empty($parsed['strong'])) return false;

    $sub = $subsByUser[$user];
    $sid = (int)($sub['id'] ?? 0);
    if ($sid <= 0) return false;
    $sourceHash = hash('sha256', 'pppoe_duplicate_login_attempt|' . $topics . '|' . $time . '|' . $msg);
    if (mtwatch_source_hash_seen($conn, $sourceHash)) return true;

    $key = 'pppoe_duplicate_login:' . $sid . ':' . $user . ':' . ($attemptMac !== '' ? $attemptMac : 'unknown') . ':' . $activeMac;
    $ke = $conn->real_escape_string(substr(preg_replace('/\s+/', '_', $key) ?? $key, 0, 190));
    $prevHits = 0;
    $rs = @$conn->query("SELECT hit_count FROM subscriber_network_alerts WHERE alert_key='{$ke}' LIMIT 1");
    if ($rs && ($row = $rs->fetch_assoc())) $prevHits = (int)($row['hit_count'] ?? 0);
    $nextHits = $prevHits + 1;
    $confirmed = !empty($parsed['strong']) || ($attemptMac !== '' && $nextHits >= 2);
    $stage = $confirmed ? 'confirmed' : 'suspect';
    $severity = $confirmed ? 'danger' : 'warn';
    $displayName = trim((string)($sub['full_name'] ?? ''));
    $attemptTxt = $attemptMac !== '' ? $attemptMac : 'غير معروف من سجل MikroTik';
    $reason = !empty($parsed['strong']) ? 'سجل MikroTik يشير إلى جلسة مكررة/حساب نشط مسبقاً' : 'فشل دخول لنفس الحساب أثناء وجود جلسة فعالة، قيد التحقق لتقليل الإنذارات الكاذبة';
    $title = 'محاولة دخول مكررة لنفس حساب PPPoE';
    $message = "الحساب {$user} متصل حالياً من MAC {$activeMac}" . ($activeIp !== '' ? " / IP {$activeIp}" : '') . "، وتم رصد محاولة دخول أخرى من MAC {$attemptTxt}." . ($displayName !== '' ? " المشترك: {$displayName}." : '') . " السبب: {$reason}" . ($attemptMac !== '' ? mtwatch_mac_suffix($conn, $attemptMac) : '');

    mtwatch_alert($conn, $key, $severity, $title, $message, $sid, $user, $attemptMac !== '' ? $attemptMac : $activeMac, $activeIp, 'pppoe_duplicate_login', [
      'stage'=>$stage,
      'conflict_type'=>'duplicate_login_attempt',
      'active_mac'=>$activeMac,
      'attempt_mac'=>$attemptMac,
      'active_ip'=>$activeIp,
      'router_log'=>$msg,
      'topics'=>$topics,
      'event_time'=>$time,
      'event_hash'=>$sourceHash,
      'verification'=>($stage === 'confirmed' ? 'confirmed_by_router_log_or_repeated_attempt' : 'suspect_until_repeated'),
    ]);
    mtwatch_log_event_unique($conn, $sid, $user, 'PPPOE_DUPLICATE_LOGIN_ATTEMPT', $title, $message, $time, $sourceHash);
    return true;
  }
}

if (!function_exists('mtwatch_subscriber_pppoe_state')) {
  function mtwatch_subscriber_pppoe_state(array $sub): array {
    $flags = [];
    foreach (['ppp_is_disabled'=>'PPPoE مفصول بالبرنامج','mt_disabled'=>'MikroTik مفصول بالبرنامج','is_cut'=>'اشتراك مقطوع'] as $col => $label) {
      $v = $sub[$col] ?? null;
      if ($v !== null && (int)$v === 1) $flags[] = $label;
    }
    $speedState = mb_strtoupper(trim((string)($sub['speed_state'] ?? '')), 'UTF-8');
    if ($speedState === 'CUT' || $speedState === 'DISABLED') $flags[] = 'حالة السرعة مقطوعة';

    $statusRaw = trim((string)($sub['status'] ?? ''));
    $status = mb_strtolower($statusRaw, 'UTF-8');
    if ($status !== '') {
      foreach (['cut','disabled','suspended','blocked','مفصول','مقطوع','موقوف','محظور','ملغى','ملغي'] as $needle) {
        if (mb_strpos($status, mb_strtolower($needle, 'UTF-8'), 0, 'UTF-8') !== false) {
          $flags[] = 'حالة المشترك: ' . $statusRaw;
          break;
        }
      }
    }
    $flags = array_values(array_unique(array_filter($flags)));
    return ['disabled'=>count($flags) > 0, 'reasons'=>$flags, 'label'=>implode('، ', $flags)];
  }
}

if (!function_exists('mtwatch_scan_pppoe_account_conflicts')) {
  function mtwatch_scan_pppoe_account_conflicts(mysqli $conn, ?string $eventAt = null): int {
    mtwatch_ensure_schema($conn);
    if (!mtwatch_table_exists($conn, 'subscribers')) return 0;
    $eventAt = $eventAt ?: date('Y-m-d H:i:s');
    $hasPpp = mtwatch_col_exists($conn, 'subscribers', 'ppp_username');
    $hasUser = mtwatch_col_exists($conn, 'subscribers', 'username');
    if (!$hasPpp && !$hasUser) return 0;
    $hasDel = mtwatch_col_exists($conn, 'subscribers', 'is_deleted');
    $baseUser = $hasPpp && $hasUser ? "COALESCE(NULLIF(TRIM(ppp_username),''), TRIM(username))" : ($hasPpp ? "TRIM(ppp_username)" : "TRIM(username)");
    $userExpr = "LOWER(SUBSTRING_INDEX({$baseUser},'@',1))";
    $where = $hasDel ? "WHERE COALESCE(is_deleted,0)=0" : "";
    $labelExpr = mtwatch_col_exists($conn, 'subscribers', 'full_name')
      ? ($hasUser ? "COALESCE(NULLIF(full_name,''), username, id)" : "COALESCE(NULLIF(full_name,''), id)")
      : ($hasUser ? "COALESCE(username, id)" : "id");
    $sql = "SELECT {$userExpr} AS ppp_user, COUNT(*) AS cnt, GROUP_CONCAT(id ORDER BY id SEPARATOR ',') AS ids, GROUP_CONCAT({$labelExpr} ORDER BY id SEPARATOR '، ') AS names FROM subscribers {$where} GROUP BY ppp_user HAVING ppp_user<>'' AND cnt>1 ORDER BY cnt DESC LIMIT 30";
    $rs = @$conn->query($sql);
    if (!$rs) return 0;
    $made = 0;
    $currentKeys = [];
    while ($r = $rs->fetch_assoc()) {
      $u = mtwatch_norm_user((string)($r['ppp_user'] ?? ''));
      if ($u === '') continue;
      $cnt = (int)($r['cnt'] ?? 0);
      $ids = trim((string)($r['ids'] ?? ''));
      $names = trim((string)($r['names'] ?? ''));
      $key = 'pppoe_duplicate_in_program:'.$u;
      $currentKeys[] = $key;
      $msg = "اسم حساب PPPoE مكرر داخل البرنامج: {$u} — عدد السجلات: {$cnt}" . ($ids !== '' ? " — IDs: {$ids}" : '') . ($names !== '' ? " — الأسماء: {$names}" : '');
      mtwatch_alert($conn, $key, 'warn', 'تعارض حساب PPPoE', $msg, null, $u, '', '', 'pppoe_match', [
        'stage'=>'confirmed',
        'conflict_type'=>'duplicate_in_program',
        'subscriber_ids'=>$ids,
        'names'=>$names,
      ]);
      $made++;
    }
    if (!empty($currentKeys)) {
      $safe = [];
      foreach ($currentKeys as $k) $safe[] = "'" . $conn->real_escape_string($k) . "'";
      @ $conn->query("UPDATE subscriber_network_alerts SET is_open=0, stage='resolved', decision_status='resolved', decision_note='تم حل تكرار حساب PPPoE داخل البرنامج.', updated_at=NOW() WHERE is_open=1 AND alert_key LIKE 'pppoe_duplicate_in_program:%' AND alert_key NOT IN (" . implode(',', $safe) . ")");
    } else {
      @ $conn->query("UPDATE subscriber_network_alerts SET is_open=0, stage='resolved', decision_status='resolved', decision_note='لا توجد حسابات PPPoE مكررة حالياً.', updated_at=NOW() WHERE is_open=1 AND alert_key LIKE 'pppoe_duplicate_in_program:%'");
    }
    return $made;
  }
}

if (!function_exists('mtwatch_process_active_rows')) {
  function mtwatch_process_active_rows(mysqli $conn, array $rows, ?string $eventAt = null): array {
    mtwatch_ensure_schema($conn);
    $eventAt = $eventAt ?: date('Y-m-d H:i:s');
    $subsByUser = mtwatch_subscribers_by_user($conn);
    $ownersByMac = mtwatch_subscribers_by_mac($conn);
    $activeByMac = [];
    $activeUsers = [];
    $processed = 0;

    foreach ($rows as $r) {
      if (!is_array($r)) continue;
      $user = mtwatch_norm_user((string)($r['name'] ?? $r['user'] ?? $r['username'] ?? ''));
      if ($user === '') continue;
      $activeUsers[] = $user;
      $mac = mtwatch_norm_mac((string)($r['caller_id'] ?? $r['caller-id'] ?? $r['mac'] ?? ''));
      $ip = trim((string)($r['address'] ?? $r['ip'] ?? ''));
      $uptime = trim((string)($r['uptime'] ?? ''));
      if ($mac !== '') $activeByMac[$mac][$user] = ['user'=>$user,'ip'=>$ip];
      if (isset($subsByUser[$user])) {
        $sid = (int)($subsByUser[$user]['id'] ?? 0);
        if ($mac !== '' && isset($ownersByMac[$mac])) {
          $owner = $ownersByMac[$mac];
          $ownerSid = (int)($owner['id'] ?? 0);
          if ($ownerSid > 0 && $sid > 0 && $ownerSid !== $sid) {
            $ownerUser = mtwatch_norm_user((string)($owner['ppp_username'] ?? $owner['username'] ?? ''));
            $ownerName = trim((string)($owner['full_name'] ?? ''));
            $currentName = trim((string)($subsByUser[$user]['full_name'] ?? ''));
            $ownerLabel = $ownerName !== '' ? $ownerName : ($ownerUser !== '' ? $ownerUser : ('#' . $ownerSid));
            $currentLabel = $currentName !== '' ? $currentName : $user;
            $msg = "MAC {$mac} المعروف سابقا للمشترك {$ownerLabel} ظهر الآن على حساب PPPoE {$user} للمشترك {$currentLabel}" . ($ip !== '' ? " - IP: {$ip}" : '') . mtwatch_mac_suffix($conn, $mac);
            mtwatch_alert($conn, 'mac_owner_moved_to_account:'.$mac.':'.$ownerSid.':'.$sid.':'.$user, 'danger', 'MAC انتقل إلى حساب آخر', $msg, $ownerSid, $ownerUser, $mac, $ip, 'active', ['stage'=>'confirmed','conflict_type'=>'known_mac_on_other_known_account','old_subscriber_id'=>$ownerSid,'new_subscriber_id'=>$sid,'new_username'=>$user]);
            mtwatch_log_event_unique($conn, $ownerSid, $ownerUser, 'MAC_USED_ON_OTHER_ACCOUNT', 'MAC على حساب آخر', $msg, $eventAt, hash('sha256', 'mac_owner_moved|old|'.$ownerSid.'|'.$sid.'|'.$mac.'|'.$user));
            mtwatch_log_event_unique($conn, $sid, $user, 'MAC_OWNER_MISMATCH', 'MAC عائد لمشترك آخر', $msg, $eventAt, hash('sha256', 'mac_owner_moved|new|'.$ownerSid.'|'.$sid.'|'.$mac.'|'.$user));
          }
        }
        $pppState = function_exists('mtwatch_subscriber_pppoe_state') ? mtwatch_subscriber_pppoe_state($subsByUser[$user]) : ['disabled'=>false,'label'=>''];
        if (function_exists('mtwatch_close_alert_key')) mtwatch_close_alert_key($conn, 'pppoe_active_unknown_in_program:'.$user, 'resolved', 'تم العثور على المشترك المطابق لحساب PPPoE.');
        if (!empty($pppState['disabled'])) {
          $stateLabel = trim((string)($pppState['label'] ?? ''));
          $displayName = trim((string)($subsByUser[$user]['full_name'] ?? ''));
          $msg = "حساب PPPoE {$user} متصل فعلياً على MikroTik بينما حالته في البرنامج غير مسموحة" . ($stateLabel !== '' ? " ({$stateLabel})" : '') . ($displayName !== '' ? " — المشترك: {$displayName}" : '') . ($ip !== '' ? " - IP: {$ip}" : '') . ($mac !== '' ? " - MAC: {$mac}" : '') . ($mac !== '' ? mtwatch_mac_suffix($conn, $mac) : '');
          mtwatch_alert($conn, 'pppoe_active_while_app_disabled:'.$sid.':'.$user, 'danger', 'تعارض حساب PPPoE', $msg, $sid ?: null, $user, $mac, $ip, 'pppoe_match', ['stage'=>'confirmed','conflict_type'=>'active_while_app_disabled','app_state'=>$stateLabel]);
          if ($sid > 0) mtwatch_log_event_unique($conn, $sid, $user, 'PPPOE_ACCOUNT_CONFLICT', 'تعارض حساب PPPoE', $msg, $eventAt, hash('sha256', 'pppoe_active_disabled|'.$sid.'|'.$user.'|'.$ip.'|'.$mac.'|'.$eventAt));
        } else {
          if (function_exists('mtwatch_close_alert_key')) mtwatch_close_alert_key($conn, 'pppoe_active_while_app_disabled:'.$sid.':'.$user, 'resolved', 'عاد حساب PPPoE للتطابق مع حالة البرنامج.');
        }
        mtwatch_update_subscriber_live($conn, $subsByUser[$user], $user, $mac, $ip, $uptime, $eventAt, 'active');
        if ($mac !== '') mtwatch_resolve_ip_without_pppoe_alerts($conn, (int)($subsByUser[$user]['id'] ?? 0), $user, $mac, $ip);
        $processed++;
        if ($ip !== '' && !mtwatch_is_pool_ip($ip)) {
          mtwatch_alert($conn, 'pppoe_ip_outside_pool:'.$user.':'.$ip, 'warn', 'IP خارج رينجات PPPoE', "الحساب {$user} متصل بعنوان {$ip} خارج رينجات 10.10.10-13", $sid, $user, $mac, $ip, 'active');
        }
      } else {
        $msgUnknown = "جلسة PPPoE فعالة على MikroTik باسم غير مطابق لأي مشترك في البرنامج: {$user}" . ($ip !== '' ? " - IP: {$ip}" : '') . ($mac !== '' ? " - MAC: {$mac}" : '') . ($mac !== '' ? mtwatch_mac_suffix($conn, $mac) : '');
        mtwatch_alert($conn, 'pppoe_active_unknown_in_program:'.$user, 'warn', 'تعارض حساب PPPoE', $msgUnknown, null, $user, $mac, $ip, 'pppoe_match', ['stage'=>'confirmed','conflict_type'=>'active_unknown_in_program']);
        if ($mac !== '' && isset($ownersByMac[$mac])) {
        $owner = $ownersByMac[$mac];
        $sid = (int)($owner['id'] ?? 0);
        $ownerUser = mtwatch_norm_user((string)($owner['ppp_username'] ?? $owner['username'] ?? ''));
        $name = trim((string)($owner['full_name'] ?? ''));
        $msg = "MAC {$mac} المعروف للمشترك " . ($name !== '' ? $name : $ownerUser) . " دخل على حساب آخر/غير موجود بالبرنامج: {$user}" . ($ip !== '' ? " - IP: {$ip}" : '') . mtwatch_mac_suffix($conn, $mac);
        mtwatch_alert($conn, 'mac_on_unknown_account:'.$mac.':'.$user, 'danger', 'MAC دخل على حساب آخر', $msg, $sid ?: null, $ownerUser, $mac, $ip, 'active');
        if ($sid > 0) mtwatch_log_event_unique($conn, $sid, $ownerUser, 'MAC_USED_ON_OTHER_ACCOUNT', 'MAC على حساب آخر', $msg, $eventAt, hash('sha256', 'mac_other|'.$mac.'|'.$user.'|'.$eventAt));
        }
      }
    }

    $offlineCount = mtwatch_reconcile_inactive_subscribers($conn, $activeUsers, $eventAt);
    $pppoeConflictCount = function_exists('mtwatch_scan_pppoe_account_conflicts') ? mtwatch_scan_pppoe_account_conflicts($conn, $eventAt) : 0;

    foreach ($activeByMac as $mac => $items) {
      if (count($items) <= 1) continue;
      $users = array_keys($items);
      $msg = 'نفس MAC دخل على أكثر من حساب: ' . $mac . ' — الحسابات: ' . implode(', ', $users) . mtwatch_mac_suffix($conn, $mac);
      foreach ($users as $u) {
        $sid = isset($subsByUser[$u]) ? (int)($subsByUser[$u]['id'] ?? 0) : 0;
        mtwatch_alert($conn, 'same_mac_multiple_active:'.$mac.':'.md5(implode('|',$users)), 'danger', 'نفس MAC على أكثر من حساب', $msg, $sid ?: null, $u, $mac, (string)($items[$u]['ip'] ?? ''), 'active');
        if ($sid > 0) mtwatch_log_event_unique($conn, $sid, $u, 'SAME_MAC_MULTI_ACCOUNT', 'نفس MAC على أكثر من حساب', $msg, $eventAt, hash('sha256', 'same_mac|'.$sid.'|'.$mac.'|'.implode('|',$users).'|'.$eventAt));
      }
    }
    return ['ok'=>true, 'processed'=>$processed, 'offline'=>$offlineCount, 'active_mac_count'=>count($activeByMac), 'pppoe_conflicts'=>$pppoeConflictCount];
  }
}

if (!function_exists('mtwatch_api_re_rows')) {
  function mtwatch_api_re_rows(array $rows): array {
    $out = [];
    foreach ($rows as $r) if (is_array($r) && (($r['.type'] ?? '') === '!re' || !isset($r['.type']))) $out[] = $r;
    return $out;
  }
}

if (!function_exists('mtwatch_secret_rows_to_map')) {
  function mtwatch_secret_rows_to_map(array $rows): array {
    $map = [];
    foreach ($rows as $r) {
      $u = mtwatch_norm_user((string)($r['name'] ?? ''));
      if ($u === '') continue;
      $map[$u] = [
        'name' => $u,
        'profile' => (string)($r['profile'] ?? ''),
        'disabled' => ((string)($r['disabled'] ?? 'false') === 'true' || (string)($r['disabled'] ?? '') === 'yes'),
        'last_logged_out' => (string)($r['last-logged-out'] ?? ''),
        'last_caller_id' => mtwatch_norm_mac((string)($r['last-caller-id'] ?? '')),
        'last_disconnect_reason' => (string)($r['last-disconnect-reason'] ?? ''),
      ];
    }
    return $map;
  }
}

if (!function_exists('mtwatch_process_secrets')) {
  function mtwatch_process_secrets(mysqli $conn, array $secretMap, ?string $eventAt = null): array {
    mtwatch_ensure_schema($conn);
    $eventAt = $eventAt ?: date('Y-m-d H:i:s');
    $subsByUser = mtwatch_subscribers_by_user($conn);
    $count = 0;
    foreach ($secretMap as $user => $s) {
      $user = mtwatch_norm_user($user);
      if ($user === '' || !isset($subsByUser[$user])) continue;
      $sub = $subsByUser[$user];
      $sid = (int)($sub['id'] ?? 0);
      if ($sid <= 0) continue;
      $secretDisabled = !empty($s['disabled']);
      $pppState = function_exists('mtwatch_subscriber_pppoe_state') ? mtwatch_subscriber_pppoe_state($sub) : ['disabled'=>false,'label'=>''];
      $stateLabel = trim((string)($pppState['label'] ?? ''));
      if (!empty($pppState['disabled']) && !$secretDisabled) {
        $details = "حساب PPPoE {$user} مفصول/موقوف في البرنامج لكن Secret على MikroTik ما زال مفعلاً" . ($stateLabel !== '' ? " ({$stateLabel})" : '');
        mtwatch_alert($conn, 'pppoe_secret_enabled_while_app_disabled:'.$sid.':'.$user, 'warn', 'تعارض حساب PPPoE', $details, $sid, $user, '', '', 'pppoe_secret', ['stage'=>'confirmed','conflict_type'=>'secret_enabled_while_app_disabled','app_state'=>$stateLabel]);
        if (function_exists('mtwatch_close_alert_key')) mtwatch_close_alert_key($conn, 'pppoe_secret_disabled_while_app_enabled:'.$sid.':'.$user, 'resolved', 'تمت مطابقة حالة Secret مع البرنامج.');
      } elseif (empty($pppState['disabled']) && $secretDisabled) {
        $details = "حساب PPPoE {$user} ظاهر كفعال في البرنامج لكن Secret على MikroTik معطل";
        mtwatch_alert($conn, 'pppoe_secret_disabled_while_app_enabled:'.$sid.':'.$user, 'warn', 'تعارض حساب PPPoE', $details, $sid, $user, '', '', 'pppoe_secret', ['stage'=>'confirmed','conflict_type'=>'secret_disabled_while_app_enabled']);
        if (function_exists('mtwatch_close_alert_key')) mtwatch_close_alert_key($conn, 'pppoe_secret_enabled_while_app_disabled:'.$sid.':'.$user, 'resolved', 'تمت مطابقة حالة Secret مع البرنامج.');
      } else {
        if (function_exists('mtwatch_close_alert_key')) {
          mtwatch_close_alert_key($conn, 'pppoe_secret_enabled_while_app_disabled:'.$sid.':'.$user, 'resolved', 'تمت مطابقة حالة Secret مع البرنامج.');
          mtwatch_close_alert_key($conn, 'pppoe_secret_disabled_while_app_enabled:'.$sid.':'.$user, 'resolved', 'تمت مطابقة حالة Secret مع البرنامج.');
        }
      }
      $mac = mtwatch_norm_mac((string)($s['last_caller_id'] ?? ''));
      $lastOutRaw = trim((string)($s['last_logged_out'] ?? ''));
      $lastOut = $lastOutRaw !== '' ? mtwatch_event_time($lastOutRaw) : null;
      $reason = trim((string)($s['last_disconnect_reason'] ?? ''));
      $oldMac = mtwatch_norm_mac((string)($sub['last_caller_id'] ?? ''));
      $sets = [];
      if ($mac !== '') {
        $dev = mtwatch_mac_info($conn, $mac);
        $devSuffix = mtwatch_mac_suffix($conn, $mac);
        mtwatch_record_mac($conn, $sid, $user, $mac, 'secret', $lastOut ?: $eventAt);
        if (mtwatch_col_exists($conn,'subscribers','last_mac_vendor')) $sets[] = "last_mac_vendor='".$conn->real_escape_string((string)($dev['vendor'] ?? ''))."'";
        if (mtwatch_col_exists($conn,'subscribers','last_device_type')) $sets[] = "last_device_type='".$conn->real_escape_string((string)($dev['device_type'] ?? ''))."'";
        if ($oldMac !== '' && $oldMac !== $mac) {
          $details = "تغيّر آخر MAC مسجل في MikroTik من {$oldMac} إلى {$mac}" . $devSuffix;
          mtwatch_log_event_unique($conn, $sid, $user, 'MAC_CHANGED', 'تغيّر MAC Address', $details, $lastOut ?: $eventAt, hash('sha256', 'secret_mac_change|'.$sid.'|'.$oldMac.'|'.$mac.'|'.($lastOut ?: $eventAt)));
          mtwatch_alert($conn, 'mac_changed_secret:'.$sid.':'.$oldMac.':'.$mac, 'danger', 'تغيّر MAC Address', $details, $sid, $user, $mac, '', 'secret');
          if (mtwatch_col_exists($conn,'subscribers','mt_mac_last_changed_at')) $sets[] = "mt_mac_last_changed_at='".$conn->real_escape_string($lastOut ?: $eventAt)."'";
        }
        if (mtwatch_col_exists($conn,'subscribers','last_caller_id')) $sets[] = "last_caller_id='".$conn->real_escape_string($mac)."'";
      }
      if ($lastOut && mtwatch_col_exists($conn,'subscribers','mt_last_logged_out')) $sets[] = "mt_last_logged_out='".$conn->real_escape_string($lastOut)."'";
      if ($reason !== '' && mtwatch_col_exists($conn,'subscribers','mt_last_disconnect_reason')) $sets[] = "mt_last_disconnect_reason='".$conn->real_escape_string($reason)."'";
      if (mtwatch_col_exists($conn,'subscribers','mt_watch_sync_at')) $sets[] = "mt_watch_sync_at=NOW()";
      if ($sets) @ $conn->query("UPDATE subscribers SET ".implode(',', $sets)." WHERE id={$sid} LIMIT 1");
      if ($lastOut) {
        $details = 'آخر خروج مسجل من MikroTik' . ($reason !== '' ? " - السبب: {$reason}" : '') . ($mac !== '' ? " - MAC: {$mac}" : '') . ($mac !== '' ? mtwatch_mac_suffix($conn, $mac) : '');
        mtwatch_log_event_unique($conn, $sid, $user, 'MT_LOGGED_OUT', 'خروج PPPoE', $details, $lastOut, hash('sha256', 'secret_logout|'.$sid.'|'.$lastOut.'|'.$mac.'|'.$reason));
      }
      $count++;
    }
    $pppoeConflictCount = function_exists('mtwatch_scan_pppoe_account_conflicts') ? mtwatch_scan_pppoe_account_conflicts($conn, $eventAt) : 0;
    return ['ok'=>true, 'processed'=>$count, 'pppoe_conflicts'=>$pppoeConflictCount];
  }
}

if (!function_exists('mtwatch_process_router_logs')) {
  function mtwatch_process_router_logs(mysqli $conn, array $logRows, array $activeRows = []): array {
    mtwatch_ensure_schema($conn);
    $subsByUser = mtwatch_subscribers_by_user($conn);
    $activeByUser = function_exists('mtwatch_active_index_from_rows') ? mtwatch_active_index_from_rows($activeRows) : [];
    $handled = 0;
    foreach ($logRows as $r) {
      $msg = trim((string)($r['message'] ?? ''));
      if ($msg === '') continue;
      $time = mtwatch_event_time((string)($r['time'] ?? ''));
      $topics = (string)($r['topics'] ?? '');
      $user = '';
      $mac = '';
      $action = '';
      $label = '';
      $details = '';

      if (preg_match('/^([^\s]+)\s+logged in\b.*?\sfrom\s+([0-9A-Fa-f:\-\.]{12,})/u', $msg, $m)) {
        $user = mtwatch_norm_user($m[1]);
        $mac = mtwatch_norm_mac($m[2]);
        $action = 'MT_LOGGED_IN';
        $label = 'دخول PPPoE';
        $details = 'دخول حقيقي من MikroTik' . ($mac !== '' ? " - MAC: {$mac}" : '') . ($mac !== '' ? mtwatch_mac_suffix($conn, $mac) : '');
      } elseif (preg_match('/^([^\s]+)\s+logged out\b.*?\sfrom\s+([0-9A-Fa-f:\-\.]{12,})/u', $msg, $m)) {
        $user = mtwatch_norm_user($m[1]);
        $mac = mtwatch_norm_mac($m[2]);
        $action = 'MT_LOGGED_OUT';
        $label = 'خروج PPPoE';
        $details = 'خروج حقيقي من MikroTik' . ($mac !== '' ? " - MAC: {$mac}" : '') . ($mac !== '' ? mtwatch_mac_suffix($conn, $mac) : '');
      } elseif (preg_match('/<pppoe-([^>]+)>:\s+could not determine remote address, using\s+([0-9\.]+)/iu', $msg, $m)) {
        $user = mtwatch_norm_user($m[1]);
        $ip = trim($m[2]);
        $action = 'MT_IP_OUTSIDE_POOL';
        $label = 'IP غير طبيعي';
        $details = "MikroTik لم يستطع تحديد remote-address واستخدم {$ip}";
      } elseif (preg_match('/ppp secret <([^>]+)> changed .*disabled=(yes|no)/iu', $msg, $m)) {
        $user = mtwatch_norm_user($m[1]);
        $disabled = strtolower($m[2]) === 'yes';
        $action = $disabled ? 'MT_API_CUT' : 'MT_API_RESTORE';
        $label = $disabled ? 'قطع من API' : 'استعادة من API';
        $details = $disabled ? 'تم تعطيل PPP Secret من API حسب سجل MikroTik' : 'تم تفعيل PPP Secret من API حسب سجل MikroTik';
      }

      $dup = function_exists('mtwatch_parse_pppoe_duplicate_login_attempt') ? mtwatch_parse_pppoe_duplicate_login_attempt($msg) : null;
      if ($dup && function_exists('mtwatch_process_pppoe_duplicate_login_attempt') && mtwatch_process_pppoe_duplicate_login_attempt($conn, $dup, $activeByUser, $subsByUser, $time, $topics, $msg)) {
        $handled++;
        continue;
      }

      if ($user === '' || !isset($subsByUser[$user])) continue;
      $sub = $subsByUser[$user];
      $sid = (int)($sub['id'] ?? 0);
      if ($sid <= 0) continue;
      if ($mac !== '') mtwatch_record_mac($conn, $sid, $user, $mac, 'router_log', $time);
      $sourceHash = hash('sha256', 'router_log|'.$topics.'|'.$time.'|'.$msg);
      mtwatch_log_event_unique($conn, $sid, $user, $action, $label, $details !== '' ? $details : $msg, $time, $sourceHash);
      if (function_exists('mtwatch_behavior_record_session_event')) {
        if ($action === 'MT_LOGGED_IN') {
          mtwatch_behavior_record_session_event($conn, $sid, $user, $mac, (string)($activeByUser[$user]['ip'] ?? ''), 'session_login', $time, $sourceHash);
        } elseif ($action === 'MT_LOGGED_OUT') {
          mtwatch_behavior_record_session_event($conn, $sid, $user, $mac, (string)($activeByUser[$user]['ip'] ?? ''), 'session_logout', $time, $sourceHash);
        }
      }
      if ($action === 'MT_IP_OUTSIDE_POOL') {
        mtwatch_alert($conn, 'router_log_ip_outside:'.$user.':'.md5($msg), 'warn', 'IP غير طبيعي لحساب PPPoE', $details, $sid, $user, '', '', 'router_log');
      }
      $handled++;
    }
    return ['ok'=>true, 'handled'=>$handled];
  }
}

if (!function_exists('mtwatch_router_security_extract_ip')) {
  function mtwatch_router_security_extract_ip(string $msg): string {
    if (preg_match('/\bfrom\s+((?:\d{1,3}\.){3}\d{1,3})\b/iu', $msg, $m)) return trim((string)$m[1]);
    if (preg_match('/\baddress\s*[=:]\s*((?:\d{1,3}\.){3}\d{1,3})\b/iu', $msg, $m)) return trim((string)$m[1]);
    if (preg_match('/\bsrc(?:-address)?\s*[=:]\s*((?:\d{1,3}\.){3}\d{1,3})\b/iu', $msg, $m)) return trim((string)$m[1]);
    return '';
  }
}

if (!function_exists('mtwatch_router_security_extract_user')) {
  function mtwatch_router_security_extract_user(string $msg): string {
    $patterns = [
      '/\buser\s+([^\s,;:\[\]\(\)<>]+)\s+(?:logged|changed|added|removed|deleted|disabled|enabled|password)/iu',
      '/\blogin\s+failure\s+for\s+user\s+([^\s,;:\[\]\(\)<>]+)/iu',
      '/\bfor\s+user\s+([^\s,;:\[\]\(\)<>]+)/iu',
      '/^([^\s,;:\[\]\(\)<>]+)\s+logged\s+(?:in|out)\b/iu',
    ];
    foreach ($patterns as $re) {
      if (preg_match($re, $msg, $m)) {
        $user = mtwatch_norm_user((string)$m[1]);
        if ($user !== '' && !preg_match('/^(user|system|account|login|failure|from)$/iu', $user)) return $user;
      }
    }
    return '';
  }
}

if (!function_exists('mtwatch_router_security_classify_log')) {
  function mtwatch_router_security_classify_log(string $topics, string $msg): ?array {
    $topicsLower = strtolower($topics);
    $hay = strtolower($topics . ' ' . $msg);
    if (str_contains($topicsLower, 'ppp') && !preg_match('/\b(account|system|ssh|web|winbox|api|telnet|ftp)\b/i', $hay)) return null;

    $hasAccountChange = preg_match('/\b(user\s+.+\s+(?:changed|added|removed|deleted|disabled|enabled)|password\s+(?:changed|reset)|group\s+changed|policy\s+changed)\b/iu', $hay);
    $hasLoginFail = preg_match('/\b(login\s+failure|authentication\s+failed|invalid\s+password|password\s+failed|failed\s+login|denied)\b/iu', $hay);
    $hasLoginIn = preg_match('/\blogged\s+in\b/iu', $hay);
    $hasLoginOut = preg_match('/\blogged\s+out\b/iu', $hay);
    $hasConfig = preg_match('/\b(configuration\s+changed|changed\s+by|config\s+changed|policy\s+changed)\b/iu', $hay);
    $hasRemoteMgmt = preg_match('/\b(winbox|web|www|ssh|telnet|ftp|api)\b/iu', $hay);

    if ($hasLoginFail) return ['type'=>'router_login_failed','severity'=>'danger','title'=>'فشل دخول إلى الراوتر','message'=>'تم رصد محاولة دخول فاشلة إلى الراوتر.'];
    if ($hasLoginIn && $hasRemoteMgmt) return ['type'=>'router_login_success','severity'=>'warn','title'=>'دخول إلى الراوتر','message'=>'تم تسجيل دخول إلى الراوتر من عنوان أو وسيلة إدارة.'];
    if ($hasLoginOut && $hasRemoteMgmt) return ['type'=>'router_logout','severity'=>'info','title'=>'خروج من الراوتر','message'=>'تم تسجيل خروج من جلسة إدارة الراوتر.'];
    if ($hasAccountChange && preg_match('/\b(password|added|removed|deleted|disabled|enabled|group|policy)\b/iu', $hay)) {
      return ['type'=>'router_account_change','severity'=>'danger','title'=>'تغيير حساس في حسابات الراوتر','message'=>'تم رصد تغيير حساس في حساب أو صلاحيات على الراوتر.'];
    }
    if ($hasAccountChange || $hasConfig) return ['type'=>'router_config_change','severity'=>'warn','title'=>'تغيير إعدادات على الراوتر','message'=>'تم رصد تغيير إداري أو إعدادات على الراوتر.'];
    if ($hasRemoteMgmt && preg_match('/\b(connect|connection|session|accepted|closed)\b/iu', $hay)) return ['type'=>'router_management_session','severity'=>'info','title'=>'جلسة إدارة على الراوتر','message'=>'تم رصد حركة جلسة إدارة على الراوتر.'];
    if (preg_match('/\b(critical|error|warning)\b/iu', $topicsLower) && preg_match('/\b(login|auth|user|password|account|ssh|winbox|web|api|telnet)\b/iu', $hay)) {
      return ['type'=>'router_security_warning','severity'=>'warn','title'=>'تحذير أمني من الراوتر','message'=>'تم رصد تحذير أمني ضمن لوج الراوتر.'];
    }
    return null;
  }
}

if (!function_exists('mtwatch_record_router_security_event')) {
  function mtwatch_record_router_security_event(mysqli $conn, string $type, string $severity, string $title, string $message, string $routerUser, string $ip, string $topics, string $raw, string $eventAt, array $meta = []): bool {
    if (!function_exists('ua_security_alerts_ensure_schema')) return false;
    ua_security_alerts_ensure_schema($conn);
    $type = substr(trim($type), 0, 60);
    $severity = in_array($severity, ['info','warn','danger'], true) ? $severity : 'warn';
    $title = mb_substr(trim($title), 0, 191, 'UTF-8');
    $message = trim($message);
    $routerUser = mb_substr(mtwatch_norm_user($routerUser), 0, 191, 'UTF-8');
    $ip = mb_substr(trim($ip), 0, 45, 'UTF-8');
    $topics = mb_substr(trim($topics), 0, 500, 'UTF-8');
    $raw = trim($raw);
    if ($type === '' || $title === '' || $raw === '') return false;
    $eventAt = mtwatch_event_time($eventAt);
    $fullMessage = $message;
    if ($raw !== '') $fullMessage .= ($fullMessage !== '' ? "\n" : '') . 'اللوج الحقيقي: ' . $raw;
    if ($topics !== '') $fullMessage .= "\nالمواضيع: " . $topics;
    $meta['router_topics'] = $topics;
    $meta['router_raw_log'] = $raw;
    $meta['source'] = 'routeros_log';
    $json = json_encode($meta, JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES);
    if (!is_string($json)) $json = '{}';
    $key = hash('sha256', 'router-security|' . $eventAt . '|' . $topics . '|' . $raw);
    $ua = 'MikroTik RouterOS';
    $stmt = @$conn->prepare("INSERT INTO admin_security_alerts
      (alert_key, alert_type, severity, title, message, username, ip_address, user_agent, request_uri, is_open, first_seen_at, last_seen_at, hit_count, meta_json, created_at)
      VALUES (?,?,?,?,?,?,?,?,?,1,?,?,1,?,NOW())
      ON DUPLICATE KEY UPDATE updated_at=COALESCE(updated_at, updated_at)");
    if (!$stmt) return false;
    $stmt->bind_param('ssssssssssss', $key, $type, $severity, $title, $fullMessage, $routerUser, $ip, $ua, $topics, $eventAt, $eventAt, $json);
    $ok = @$stmt->execute();
    $stmt->close();
    return (bool)$ok;
  }
}

if (!function_exists('mtwatch_process_router_security_logs')) {
  function mtwatch_process_router_security_logs(mysqli $conn, array $logRows): array {
    mtwatch_ensure_schema($conn);
    $handled = 0;
    foreach ($logRows as $r) {
      $msg = trim((string)($r['message'] ?? ''));
      if ($msg === '') continue;
      $topics = (string)($r['topics'] ?? '');
      $class = mtwatch_router_security_classify_log($topics, $msg);
      if (!$class) continue;
      $time = mtwatch_event_time((string)($r['time'] ?? ''));
      $ip = mtwatch_router_security_extract_ip($msg);
      $user = mtwatch_router_security_extract_user($msg);
      $meta = [
        'detected_user'=>$user,
        'detected_ip'=>$ip,
        'router_time'=>(string)($r['time'] ?? ''),
      ];
      if (mtwatch_record_router_security_event($conn, $class['type'], $class['severity'], $class['title'], $class['message'], $user, $ip, $topics, $msg, $time, $meta)) $handled++;
    }
    return ['ok'=>true, 'handled'=>$handled];
  }
}

if (!function_exists('mtwatch_router_security_log_rows')) {
  function mtwatch_router_security_log_rows($api): array {
    $queries = [
      ['/log/print', '?topics~account', '=.proplist=time,topics,message'],
      ['/log/print', '?topics~system', '=.proplist=time,topics,message'],
      ['/log/print', '?topics~critical', '=.proplist=time,topics,message'],
      ['/log/print', '?topics~error', '=.proplist=time,topics,message'],
      ['/log/print', '?topics~warning', '=.proplist=time,topics,message'],
      ['/log/print', '?topics~ssh', '=.proplist=time,topics,message'],
      ['/log/print', '?topics~web', '=.proplist=time,topics,message'],
      ['/log/print', '?topics~winbox', '=.proplist=time,topics,message'],
      ['/log/print', '?message~login', '=.proplist=time,topics,message'],
      ['/log/print', '?message~logged', '=.proplist=time,topics,message'],
      ['/log/print', '?message~password', '=.proplist=time,topics,message'],
      ['/log/print', '?message~user', '=.proplist=time,topics,message'],
    ];
    $out = [];
    $seen = [];
    foreach ($queries as $q) {
      foreach (mtwatch_rows_from_api($api, $q) as $row) {
        $key = (string)($row['time'] ?? '') . '|' . (string)($row['topics'] ?? '') . '|' . (string)($row['message'] ?? '');
        if ($key === '||' || isset($seen[$key])) continue;
        $seen[$key] = true;
        $out[] = $row;
      }
    }
    return $out;
  }
}

if (!function_exists('mtwatch_audit_router_users')) {
  function mtwatch_audit_router_users(mysqli $conn, $api, string $routerKey = 'default'): array {
    mtwatch_ensure_schema($conn);
    $rows = mtwatch_rows_from_api($api, ['/user/print', '=.proplist=name,group,address,disabled,comment']);
    if (!$rows) return ['ok'=>false, 'message'=>'router user list empty or unreadable', 'checked'=>0, 'inserted'=>0, 'changed'=>0, 'missing'=>0];
    $routerKey = mb_substr(trim($routerKey) !== '' ? trim($routerKey) : 'default', 0, 120, 'UTF-8');
    $routerEsc = $conn->real_escape_string($routerKey);
    $hadAnyRs = @$conn->query("SELECT COUNT(*) AS c FROM mtwatch_router_user_state WHERE router_key='{$routerEsc}'");
    $hadAnyRow = $hadAnyRs ? $hadAnyRs->fetch_assoc() : null;
    $hadAny = (int)($hadAnyRow['c'] ?? 0) > 0;
    $seenUsers = [];
    $inserted = 0; $changed = 0; $missing = 0;
    foreach ($rows as $r) {
      $name = mtwatch_norm_user((string)($r['name'] ?? ''));
      if ($name === '') continue;
      $seenUsers[$name] = true;
      $group = trim((string)($r['group'] ?? ''));
      $address = trim((string)($r['address'] ?? ''));
      $disabled = trim((string)($r['disabled'] ?? ''));
      $comment = trim((string)($r['comment'] ?? ''));
      $state = ['group'=>$group, 'address'=>$address, 'disabled'=>$disabled, 'comment'=>$comment];
      $hash = hash('sha256', json_encode($state, JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES));
      $nameEsc = $conn->real_escape_string($name);
      $oldRs = @$conn->query("SELECT * FROM mtwatch_router_user_state WHERE router_key='{$routerEsc}' AND user_name='{$nameEsc}' LIMIT 1");
      $old = $oldRs ? $oldRs->fetch_assoc() : null;
      if (!$old) {
        $stmt = @$conn->prepare("INSERT INTO mtwatch_router_user_state (router_key,user_name,user_group,address,disabled,comment,state_hash,first_seen_at,last_seen_at,missing_since) VALUES (?,?,?,?,?,?,?,NOW(),NOW(),NULL)");
        if ($stmt) {
          $stmt->bind_param('sssssss', $routerKey, $name, $group, $address, $disabled, $comment, $hash);
          @$stmt->execute(); $stmt->close(); $inserted++;
        }
        if ($hadAny) {
          mtwatch_record_router_security_event($conn, 'router_user_added', 'danger', 'إضافة حساب على الراوتر', "تم ظهور حساب جديد على الراوتر: {$name}", $name, $address, 'router_user_snapshot', 'router user added: ' . $name, date('Y-m-d H:i:s'), ['router_key'=>$routerKey, 'state'=>$state]);
        }
        continue;
      }
      $oldHash = (string)($old['state_hash'] ?? '');
      $wasMissing = trim((string)($old['missing_since'] ?? '')) !== '';
      if ($oldHash !== $hash || $wasMissing) {
        $diff = [];
        foreach (['user_group'=>'group','address'=>'address','disabled'=>'disabled','comment'=>'comment'] as $dbCol => $label) {
          $oldVal = trim((string)($old[$dbCol] ?? ''));
          $newVal = $state[$label] ?? '';
          if ($oldVal !== $newVal) $diff[] = $label . ': ' . ($oldVal !== '' ? $oldVal : '-') . ' -> ' . ($newVal !== '' ? $newVal : '-');
        }
        $stmt = @$conn->prepare("UPDATE mtwatch_router_user_state SET user_group=?, address=?, disabled=?, comment=?, state_hash=?, last_seen_at=NOW(), missing_since=NULL WHERE router_key=? AND user_name=? LIMIT 1");
        if ($stmt) {
          $stmt->bind_param('sssssss', $group, $address, $disabled, $comment, $hash, $routerKey, $name);
          @$stmt->execute(); $stmt->close(); $changed++;
        }
        if ($oldHash !== $hash) {
          $detail = $diff ? implode(' | ', $diff) : 'تغيّر بصمة الحساب.';
          mtwatch_record_router_security_event($conn, 'router_user_changed', 'danger', 'تغيير حساب على الراوتر', "تم تغيير حساب الراوتر {$name}: {$detail}", $name, $address, 'router_user_snapshot', 'router user changed: ' . $name . ' | ' . $detail, date('Y-m-d H:i:s'), ['router_key'=>$routerKey, 'old_hash'=>$oldHash, 'new_hash'=>$hash, 'diff'=>$diff, 'state'=>$state]);
        }
      }
    }
    $knownRs = @$conn->query("SELECT user_name FROM mtwatch_router_user_state WHERE router_key='{$routerEsc}' AND missing_since IS NULL");
    if ($knownRs) while ($old = $knownRs->fetch_assoc()) {
      $name = mtwatch_norm_user((string)($old['user_name'] ?? ''));
      if ($name === '' || isset($seenUsers[$name])) continue;
      $nameEsc = $conn->real_escape_string($name);
      @ $conn->query("UPDATE mtwatch_router_user_state SET missing_since=NOW(), last_seen_at=NOW() WHERE router_key='{$routerEsc}' AND user_name='{$nameEsc}' LIMIT 1");
      $missing++;
      if ($hadAny) {
        mtwatch_record_router_security_event($conn, 'router_user_removed', 'danger', 'حذف حساب من الراوتر', "لم يعد حساب الراوتر {$name} ظاهراً في قائمة مستخدمي الراوتر.", $name, '', 'router_user_snapshot', 'router user removed: ' . $name, date('Y-m-d H:i:s'), ['router_key'=>$routerKey]);
      }
    }
    return ['ok'=>true, 'checked'=>count($rows), 'inserted'=>$inserted, 'changed'=>$changed, 'missing'=>$missing, 'baseline'=>!$hadAny];
  }
}

if (!function_exists('mtwatch_run_router_security_audit')) {
  function mtwatch_run_router_security_audit(mysqli $conn, $api, string $routerKey = 'default'): array {
    $logRows = mtwatch_router_security_log_rows($api);
    $logs = mtwatch_process_router_security_logs($conn, $logRows);
    $users = mtwatch_audit_router_users($conn, $api, $routerKey);
    return ['ok'=>true, 'logs'=>$logs, 'users'=>$users, 'log_rows'=>count($logRows)];
  }
}

if (!function_exists('mtwatch_rows_from_api')) {
  function mtwatch_rows_from_api($api, array $sentence): array {
    try {
      // Security monitoring must not read dashboard/UI cache; it needs fresh RouterOS state when it runs.
      if (!in_array('=__ua_cache_bypass=yes', $sentence, true)) $sentence[] = '=__ua_cache_bypass=yes';
      $rows = $api->command($sentence);
      return mtwatch_api_re_rows(is_array($rows) ? $rows : []);
    } catch (Throwable $e) {
      return [];
    }
  }
}

if (!function_exists('mtwatch_run_fast_active_sync')) {
  function mtwatch_run_fast_active_sync(mysqli $conn, bool $force = false, int $ttl = 300): array {
    if (!mtwatch_gate('mtwatch_fast_active_sync', max(60, min(900, $ttl)), $force)) return ['ok'=>true, 'skipped'=>true, 'message'=>'fast active sync gated'];
    mtwatch_ensure_schema($conn);
    $api = mt_api_connect($conn);
    if (!$api) return ['ok'=>false, 'message'=>(string)($GLOBALS['mt_last_error'] ?? 'MikroTik API offline')];
    try {
      $active = mtwatch_rows_from_api($api, ['/ppp/active/print', '=.proplist=name,address,caller-id,uptime,service']);
      $activeRows = [];
      foreach ($active as $r) {
        $activeRows[] = [
          'name' => (string)($r['name'] ?? ''),
          'address' => (string)($r['address'] ?? ''),
          'caller_id' => (string)($r['caller-id'] ?? ''),
          'uptime' => (string)($r['uptime'] ?? ''),
          'service' => (string)($r['service'] ?? ''),
        ];
      }
      $aRes = mtwatch_process_active_rows($conn, $activeRows, date('Y-m-d H:i:s'));
      $behaviorRes = function_exists('mtwatch_behavior_process_active_rows') ? mtwatch_behavior_process_active_rows($conn, $activeRows, date('Y-m-d H:i:s')) : ['ok'=>true,'skipped'=>true];
      $ownRes = function_exists('uamac_resolve_mac_ownership') ? uamac_resolve_mac_ownership($conn, $activeRows, ['auto_cleanup'=>false]) : ['ok'=>true,'skipped'=>true];
      $logRows = mtwatch_rows_from_api($api, ['/log/print', '?topics~ppp', '=.proplist=time,topics,message']);
      if (!$logRows) $logRows = mtwatch_rows_from_api($api, ['/log/print', '?message~ppp', '=.proplist=time,topics,message']);
      $lRes = $logRows ? mtwatch_process_router_logs($conn, $logRows, $activeRows) : ['ok'=>true, 'handled'=>0];
      $secRes = ['ok'=>true,'skipped'=>true,'message'=>'router security audit gated'];
      if (function_exists('mtwatch_run_router_security_audit') && mtwatch_gate('mtwatch_router_security_audit', 60, $force)) {
        $secRes = mtwatch_run_router_security_audit($conn, $api, 'default');
      }
      return ['ok'=>true, 'active'=>$aRes, 'behavior'=>$behaviorRes, 'ownership'=>$ownRes, 'logs'=>$lRes, 'router_security'=>$secRes, 'interval_seconds'=>max(60, min(900, $ttl))];
    } catch (Throwable $e) {
      return ['ok'=>false, 'message'=>$e->getMessage()];
    } finally {
      try { $api->disconnect(); } catch (Throwable $e) {}
    }
  }
}

if (!function_exists('mtwatch_run_hourly_deep_sync')) {
  function mtwatch_run_hourly_deep_sync(mysqli $conn, bool $force = false): array {
    if (!mtwatch_gate('mtwatch_deep_sync', 3600, $force)) return ['ok'=>true, 'skipped'=>true, 'message'=>'deep sync gated'];
    mtwatch_ensure_schema($conn);
    $api = mt_api_connect($conn);
    if (!$api) return ['ok'=>false, 'message'=>(string)($GLOBALS['mt_last_error'] ?? 'MikroTik API offline')];
    try {
      $active = mtwatch_rows_from_api($api, ['/ppp/active/print', '=.proplist=name,address,caller-id,uptime,service']);
      $activeRows = [];
      foreach ($active as $r) {
        $activeRows[] = [
          'name' => (string)($r['name'] ?? ''),
          'address' => (string)($r['address'] ?? ''),
          'caller_id' => (string)($r['caller-id'] ?? ''),
          'uptime' => (string)($r['uptime'] ?? ''),
          'service' => (string)($r['service'] ?? ''),
        ];
      }
      $aRes = mtwatch_process_active_rows($conn, $activeRows, date('Y-m-d H:i:s'));
      $behaviorRes = function_exists('mtwatch_behavior_process_active_rows') ? mtwatch_behavior_process_active_rows($conn, $activeRows, date('Y-m-d H:i:s')) : ['ok'=>true,'skipped'=>true];
      $ownRes = function_exists('uamac_resolve_mac_ownership') ? uamac_resolve_mac_ownership($conn, $activeRows, ['auto_cleanup'=>true]) : ['ok'=>true,'skipped'=>true];

      $secretRows = mtwatch_rows_from_api($api, ['/ppp/secret/print', '=.proplist=name,service,profile,disabled,last-logged-out,last-caller-id,last-disconnect-reason']);
      $sRes = mtwatch_process_secrets($conn, mtwatch_secret_rows_to_map($secretRows), date('Y-m-d H:i:s'));

      $logRows = mtwatch_rows_from_api($api, ['/log/print', '?topics~ppp', '=.proplist=time,topics,message']);
      if (!$logRows) $logRows = mtwatch_rows_from_api($api, ['/log/print', '?message~ppp', '=.proplist=time,topics,message']);
      $lRes = $logRows ? mtwatch_process_router_logs($conn, $logRows, $activeRows) : ['ok'=>true, 'handled'=>0];
      $secRes = ['ok'=>true,'skipped'=>true,'message'=>'router security audit gated'];
      if (function_exists('mtwatch_run_router_security_audit') && mtwatch_gate('mtwatch_router_security_audit', 60, $force)) {
        $secRes = mtwatch_run_router_security_audit($conn, $api, 'default');
      }

      return ['ok'=>true, 'active'=>$aRes, 'behavior'=>$behaviorRes, 'ownership'=>$ownRes, 'secrets'=>$sRes, 'logs'=>$lRes, 'router_security'=>$secRes];
    } catch (Throwable $e) {
      return ['ok'=>false, 'message'=>$e->getMessage()];
    } finally {
      try { $api->disconnect(); } catch (Throwable $e) {}
    }
  }
}

if (!function_exists('mtwatch_collect_network_devices')) {
  function mtwatch_collect_network_devices($api): array {
    $out = [];
    $add = static function(array $r, string $source) use (&$out): void {
      $mac = mtwatch_norm_mac((string)($r['mac-address'] ?? $r['mac'] ?? ''));
      if ($mac === '' || $mac === '00:00:00:00:00:00') return;
      $ip = trim((string)($r['address'] ?? $r['ip-address'] ?? ''));
      if ($ip !== '' && strpos($ip, '/') !== false) $ip = explode('/', $ip)[0];
      $iface = trim((string)($r['interface'] ?? $r['on-interface'] ?? $r['bridge'] ?? ''));
      $status = strtolower(trim((string)($r['status'] ?? '')));
      $completeRaw = strtolower(trim((string)($r['complete'] ?? '')));
      $disabledRaw = strtolower(trim((string)($r['disabled'] ?? '')));
      $isDisabled = in_array($disabledRaw, ['true','yes','1'], true);
      $isComplete = in_array($completeRaw, ['true','yes','1'], true) || in_array($status, ['complete','reachable','stale','delay','probe'], true);
      $isUnresolvedArp = ($source === 'arp') && (in_array($status, ['failed','incomplete'], true) || ($completeRaw !== '' && !$isComplete));
      if ($isDisabled) return;
      $out[] = [
        'ip'=>$ip,
        'mac'=>$mac,
        'interface'=>$iface,
        'source'=>$source,
        'raw'=>$r,
        'arp_status'=>$status,
        'arp_complete'=>$isComplete,
        'arp_unresolved'=>$isUnresolvedArp,
      ];
    };
    foreach (mtwatch_rows_from_api($api, ['/ip/arp/print', '=.proplist=address,mac-address,interface,complete,status,disabled,dynamic']) as $r) $add($r, 'arp');
    foreach (mtwatch_rows_from_api($api, ['/ip/neighbor/print', '=.proplist=address,mac-address,interface,identity,platform']) as $r) $add($r, 'neighbor');
    return $out;
  }
}


if (!function_exists('mtwatch_set_ip_watch_stage')) {
  function mtwatch_set_ip_watch_stage(mysqli $conn, string $mac, string $ip, string $source, string $stage, string $note = ''): void {
    $mac = mtwatch_norm_mac($mac);
    if ($mac === '') return;
    $stage = in_array($stage, ['observed','identifying','suspect','confirmed','active','allowlisted','resolved'], true) ? $stage : 'observed';
    $macEsc = $conn->real_escape_string($mac);
    $ipEsc = $conn->real_escape_string($ip);
    $srcEsc = $conn->real_escape_string($source);
    $noteEsc = $conn->real_escape_string($note);
    $where = "mac_address='{$macEsc}' AND source='{$srcEsc}' AND ";
    $where .= ($ip !== '') ? "ip_address='{$ipEsc}'" : "(ip_address IS NULL OR ip_address='')";
    @ $conn->query("UPDATE network_ip_watch SET stage='{$stage}', decision_note='{$noteEsc}', last_seen_at=last_seen_at WHERE {$where} ORDER BY id DESC LIMIT 1");
  }
}

if (!function_exists('mtwatch_get_ip_watch_state')) {
  function mtwatch_get_ip_watch_state(mysqli $conn, string $mac, string $ip, string $source): array {
    $mac = mtwatch_norm_mac($mac);
    if ($mac === '') return ['first_seen_at'=>date('Y-m-d H:i:s'), 'seen_count'=>1, 'age'=>0];
    $macEsc = $conn->real_escape_string($mac);
    $ipEsc = $conn->real_escape_string($ip);
    $srcEsc = $conn->real_escape_string($source);
    $where = "mac_address='{$macEsc}' AND source='{$srcEsc}' AND ";
    $where .= ($ip !== '') ? "ip_address='{$ipEsc}'" : "(ip_address IS NULL OR ip_address='')";
    $rs = @$conn->query("SELECT first_seen_at, last_seen_at, seen_count, stage FROM network_ip_watch WHERE {$where} ORDER BY id DESC LIMIT 1");
    $r = $rs ? $rs->fetch_assoc() : null;
    $first = (string)($r['first_seen_at'] ?? date('Y-m-d H:i:s'));
    $seen = max(1, (int)($r['seen_count'] ?? 1));
    $age = max(0, time() - (int)(strtotime($first) ?: time()));
    return ['first_seen_at'=>$first, 'seen_count'=>$seen, 'age'=>$age, 'stage'=>(string)($r['stage'] ?? '')];
  }
}


if (!function_exists('mtwatch_resolve_unresolved_arp_alerts')) {
  function mtwatch_resolve_unresolved_arp_alerts(mysqli $conn, string $mac, string $ip, string $note = ''): void {
    $mac = mtwatch_norm_mac($mac);
    if ($mac === '' && $ip === '') return;
    $where = [];
    if ($mac !== '') $where[] = "mac_address='" . $conn->real_escape_string($mac) . "'";
    if ($ip !== '') $where[] = "ip_address='" . $conn->real_escape_string($ip) . "'";
    if (!$where) return;
    $note = $note !== '' ? $note : 'تم إغلاق التنبيه تلقائياً لأن ARP غير مكتمل/فاشل ولا توجد جلسة PPPoE فعالة.';
    $noteEsc = $conn->real_escape_string($note);
    $sql = "UPDATE subscriber_network_alerts
            SET is_open=0,
                stage='resolved',
                decision_status='auto_false_positive',
                decision_note='{$noteEsc}',
                updated_at=NOW()
            WHERE is_open=1
              AND source='ip_watch'
              AND (" . implode(' AND ', $where) . ")";
    @ $conn->query($sql);
  }
}

if (!function_exists('mtwatch_run_network_ip_scan')) {
  function mtwatch_run_network_ip_scan(mysqli $conn, bool $force = false, int $ttl = 60): array {
    if (!mtwatch_gate('mtwatch_ip_scan', max(30, $ttl), $force)) return ['ok'=>true, 'skipped'=>true, 'message'=>'ip scan gated'];
    mtwatch_ensure_schema($conn);
    $api = mt_api_connect($conn);
    if (!$api) return ['ok'=>false, 'message'=>(string)($GLOBALS['mt_last_error'] ?? 'MikroTik API offline')];
    try {
      $activeRows = mtwatch_rows_from_api($api, ['/ppp/active/print', '=.proplist=name,address,caller-id,uptime']);
      $activeMac = [];
      $activeIp = [];
      foreach ($activeRows as $r) {
        $m = mtwatch_norm_mac((string)($r['caller-id'] ?? ''));
        $ip = trim((string)($r['address'] ?? ''));
        if ($m !== '') $activeMac[$m] = true;
        if ($ip !== '') $activeIp[$ip] = true;
      }
      $owners = mtwatch_subscribers_by_mac($conn);
      $devices = mtwatch_collect_network_devices($api);
      $bridgeRows = mtwatch_rows_from_api($api, ['/interface/bridge/host/print', '=.proplist=mac-address,on-interface,bridge,local,dynamic,disabled']);
      $behaviorArp = function_exists('mtwatch_behavior_process_arp_conflicts')
        ? mtwatch_behavior_process_arp_conflicts($conn, $devices, date('Y-m-d H:i:s'))
        : ['ok'=>true,'skipped'=>true];
      $behaviorBridge = function_exists('mtwatch_behavior_process_bridge_hosts')
        ? mtwatch_behavior_process_bridge_hosts($conn, $bridgeRows, date('Y-m-d H:i:s'))
        : ['ok'=>true,'skipped'=>true];
      $internalFilter = function_exists('mtwatch_auto_filter_internal_observations') ? mtwatch_auto_filter_internal_observations($conn, $devices) : ['ok'=>true,'skipped'=>true];
      $routerSecurity = ['ok'=>true, 'skipped'=>true, 'message'=>'router security audit gated'];
      if (function_exists('mtwatch_run_router_security_audit') && mtwatch_gate('mtwatch_router_security_audit', max(30, $ttl), $force)) {
        $routerSecurity = mtwatch_run_router_security_audit($conn, $api, 'default');
      }
      $alerts = 0;
      foreach ($devices as $d) {
        $mac = $d['mac'];
        $ip = $d['ip'];
        $source = $d['source'];
        $iface = $d['interface'];
        $arpStatus = (string)($d['arp_status'] ?? '');
        $arpComplete = !empty($d['arp_complete']);
        $arpUnresolved = !empty($d['arp_unresolved']);
        if ($mac === '') continue;
        $isActive = isset($activeMac[$mac]) || ($ip !== '' && isset($activeIp[$ip]));
        $owner = $owners[$mac] ?? null;
        $sid = $owner ? (int)($owner['id'] ?? 0) : null;
        $ownerUser = $owner ? mtwatch_norm_user((string)($owner['ppp_username'] ?? $owner['username'] ?? '')) : '';
        $name = $owner ? trim((string)($owner['full_name'] ?? '')) : '';

        $allow = function_exists('mtwatch_allowlist_match') ? mtwatch_allowlist_match($conn, $mac, $ip, $iface, $ownerUser, $source) : ['matched'=>false, 'reason'=>''];
        $isAllowlisted = !empty($allow['matched']);
        $json = json_encode([
          'interface'=>$iface,
          'active'=>$isActive,
          'allowlisted'=>$isAllowlisted,
          'allow_reason'=>(string)($allow['reason'] ?? ''),
          'arp_status'=>$arpStatus,
          'arp_complete'=>$arpComplete,
          'arp_unresolved'=>$arpUnresolved,
          'raw'=>$d['raw']
        ], JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES);
        if (!is_string($json)) $json = '{}';
        $stmt = @$conn->prepare("INSERT INTO network_ip_watch (ip_address, mac_address, interface_name, source, subscriber_id, username, is_pppoe_active, is_allowlisted, first_seen_at, last_seen_at, seen_count, meta_json, stage)
          VALUES (?,?,?,?,?,?,?,?,NOW(),NOW(),1,?,IF(?=1,'active',IF(?=1,'allowlisted','observed')))
          ON DUPLICATE KEY UPDATE interface_name=VALUES(interface_name), subscriber_id=VALUES(subscriber_id), username=VALUES(username), is_pppoe_active=VALUES(is_pppoe_active), is_allowlisted=VALUES(is_allowlisted), last_seen_at=NOW(), seen_count=seen_count+1, meta_json=VALUES(meta_json), stage=IF(VALUES(is_pppoe_active)=1,'active',IF(VALUES(is_allowlisted)=1,'allowlisted',stage))");
        if ($stmt) {
          $act = $isActive ? 1 : 0;
          $aw = $isAllowlisted ? 1 : 0;
          $stmt->bind_param('ssssisiisii', $ip, $mac, $iface, $source, $sid, $ownerUser, $act, $aw, $json, $act, $aw);
          @$stmt->execute();
          $stmt->close();
        }

        if ($isActive) {
          mtwatch_set_ip_watch_stage($conn, $mac, $ip, $source, 'active', 'تمت مطابقة الجهاز مع جلسة PPPoE فعالة.');
          if ($owner) mtwatch_resolve_ip_without_pppoe_alerts($conn, $sid, $ownerUser, $mac, $ip);
          continue;
        }
        if ($isAllowlisted) {
          mtwatch_set_ip_watch_stage($conn, $mac, $ip, $source, 'allowlisted', (string)($allow['reason'] ?? 'عنصر مسموح'));
          continue;
        }

        if ($arpUnresolved) {
          $stTxt = $arpStatus !== '' ? $arpStatus : 'not-complete';
          $note = 'ARP غير مكتمل/فاشل (' . $stTxt . ') ولا توجد جلسة PPPoE فعالة؛ تم إبقاء الحالة كرصد أولي لمنع إنذار كاذب.';
          mtwatch_set_ip_watch_stage($conn, $mac, $ip, $source, 'observed', $note);
          mtwatch_resolve_unresolved_arp_alerts($conn, $mac, $ip, $note);
          continue;
        }

        $watchState = mtwatch_get_ip_watch_state($conn, $mac, $ip, $source);
        $age = (int)($watchState['age'] ?? 0);
        $seen = (int)($watchState['seen_count'] ?? 1);
        $baseMeta = [
          'interface'=>$iface,
          'source'=>$source,
          'first_seen_at'=>(string)($watchState['first_seen_at'] ?? ''),
          'seen_count'=>$seen,
          'age_seconds'=>$age,
          'verification'=>'delayed_to_reduce_false_alarms',
          'arp_status'=>$arpStatus,
          'arp_complete'=>$arpComplete,
          'stage'=>'confirmed'
        ];

        if ($owner) {
          $display = $name !== '' ? $name : $ownerUser;
          $confirmDelay = 75;
          if ($age < 30 || $seen < 2) {
            mtwatch_set_ip_watch_stage($conn, $mac, $ip, $source, 'identifying', 'ظهر MAC معروف بدون جلسة PPPoE، بانتظار اكتمال الربط قبل إطلاق تنبيه.');
            continue;
          }
          if ($age < $confirmDelay) {
            mtwatch_set_ip_watch_stage($conn, $mac, $ip, $source, 'suspect', 'MAC معروف ما زال بدون جلسة PPPoE، قيد التحقق قبل التأكيد.');
            continue;
          }
          mtwatch_set_ip_watch_stage($conn, $mac, $ip, $source, 'confirmed', 'استمر ظهور MAC معروف بدون جلسة PPPoE بعد مهلة التحقق.');
          $msg = "MAC معروف للمشترك {$display} ظهر عبر IP بدون جلسة PPPoE" . ($ip !== '' ? " - IP: {$ip}" : '') . ($iface !== '' ? " - Interface: {$iface}" : '') . " - تم التأكيد بعد {$age} ثانية" . mtwatch_mac_suffix($conn, $mac);
          mtwatch_alert($conn, 'known_mac_ip_without_pppoe:'.$mac.':'.$ip, 'danger', 'دخول IP بدون PPPoE', $msg, $sid, $ownerUser, $mac, $ip, 'ip_watch', $baseMeta + ['owner_known'=>true]);
          if ($sid) mtwatch_log_event_unique($conn, $sid, $ownerUser, 'IP_WITHOUT_PPPOE', 'دخول IP بدون PPPoE', $msg, date('Y-m-d H:i:s'), hash('sha256', 'ip_no_pppoe|'.$sid.'|'.$mac.'|'.$ip));
          $alerts++;
        } elseif ($ip !== '' && mtwatch_is_pool_ip($ip)) {
          $confirmDelay = 120;
          if ($age < 45 || $seen < 2) {
            mtwatch_set_ip_watch_stage($conn, $mac, $ip, $source, 'identifying', 'IP من رينج المشتركين ظهر قبل اكتمال مطابقة PPPoE، قيد التعرف.');
            continue;
          }
          if ($age < $confirmDelay) {
            mtwatch_set_ip_watch_stage($conn, $mac, $ip, $source, 'suspect', 'IP من رينج المشتركين ما زال بدون جلسة PPPoE، قيد الاشتباه.');
            continue;
          }
          mtwatch_set_ip_watch_stage($conn, $mac, $ip, $source, 'confirmed', 'استمر ظهور IP من رينج المشتركين بدون PPPoE بعد مهلة التحقق.');
          $msg = "جهاز غير معروف ظهر بعنوان من رينج المشتركين بدون جلسة PPPoE - IP: {$ip} - MAC: {$mac}" . ($iface !== '' ? " - Interface: {$iface}" : '') . " - تم التأكيد بعد {$age} ثانية" . mtwatch_mac_suffix($conn, $mac);
          mtwatch_alert($conn, 'unknown_pool_ip_without_pppoe:'.$mac.':'.$ip, 'warn', 'IP من رينج المشتركين بدون PPPoE', $msg, null, '', $mac, $ip, 'ip_watch', $baseMeta + ['owner_known'=>false]);
          $alerts++;
        } else {
          mtwatch_set_ip_watch_stage($conn, $mac, $ip, $source, 'observed', 'جهاز مرصود خارج شروط تنبيه PPPoE المباشر.');
        }
      }
      return ['ok'=>true, 'devices'=>count($devices), 'alerts'=>$alerts, 'behavior'=>['arp'=>$behaviorArp,'bridge'=>$behaviorBridge], 'internal_filter'=>$internalFilter, 'router_security'=>$routerSecurity];
    } catch (Throwable $e) {
      return ['ok'=>false, 'message'=>$e->getMessage()];
    } finally {
      try { $api->disconnect(); } catch (Throwable $e) {}
    }
  }
}


if (!function_exists('mtwatch_resolve_ip_without_pppoe_alerts')) {
  function mtwatch_resolve_ip_without_pppoe_alerts(mysqli $conn, ?int $subscriberId, string $username, string $mac, string $ip = ''): void {
    mtwatch_ensure_schema($conn);
    $mac = mtwatch_norm_mac($mac);
    if ($mac === '') return;
    $subscriberId = (int)($subscriberId ?? 0);
    $username = mtwatch_norm_user($username);
    $macEsc = $conn->real_escape_string($mac);
    $ipEsc = $conn->real_escape_string($ip);
    $where = "is_open=1 AND (alert_key LIKE 'known_mac_ip_without_pppoe:" . $macEsc . ":%' OR alert_key LIKE 'unknown_pool_ip_without_pppoe:" . $macEsc . ":%')";
    if ($ip !== '') $where .= " AND (ip_address IS NULL OR ip_address='' OR ip_address='" . $ipEsc . "' OR alert_key LIKE 'known_mac_ip_without_pppoe:" . $macEsc . ":%' OR alert_key LIKE 'unknown_pool_ip_without_pppoe:" . $macEsc . ":%')";
    $rs = @$conn->query("SELECT id, subscriber_id, username, ip_address, message FROM subscriber_network_alerts WHERE {$where} ORDER BY id DESC LIMIT 10");
    $had = false;
    if ($rs) {
      while ($r = $rs->fetch_assoc()) {
        $had = true;
        $sid = $subscriberId > 0 ? $subscriberId : (int)($r['subscriber_id'] ?? 0);
        $u = $username !== '' ? $username : mtwatch_norm_user((string)($r['username'] ?? ''));
        $oldIp = trim((string)($r['ip_address'] ?? ''));
        $details = 'عاد الاتصال الطبيعي عبر PPPoE بعد ظهور IP بدون جلسة' . ($oldIp !== '' ? ' - IP السابق: ' . $oldIp : '') . ' - MAC: ' . $mac . mtwatch_mac_suffix($conn, $mac);
        if ($sid > 0) {
          mtwatch_log_event_unique($conn, $sid, $u, 'IP_WITHOUT_PPPOE_RESOLVED', 'عودة الاتصال الطبيعي عبر PPPoE', $details, date('Y-m-d H:i:s'), hash('sha256', 'ip_no_pppoe_resolved|' . $sid . '|' . $mac . '|' . $oldIp . '|' . (int)($r['id'] ?? 0)));
        }
      }
    }
    @ $conn->query("UPDATE subscriber_network_alerts SET is_open=0, last_seen_at=NOW() WHERE {$where}");
    if ($had && $ip !== '') {
      $uIp = $conn->real_escape_string($ip);
      @ $conn->query("UPDATE network_ip_watch SET is_pppoe_active=1, last_seen_at=NOW() WHERE mac_address='{$macEsc}' AND ip_address='{$uIp}'");
    }
  }
}

if (!function_exists('mtwatch_recent_alerts')) {
  function mtwatch_recent_alerts(mysqli $conn, int $limit = 10): array {
    mtwatch_ensure_schema($conn);
    $limit = max(1, min(50, $limit));
    $rows = [];
    $rs = @$conn->query("SELECT * FROM subscriber_network_alerts WHERE is_open=1 AND COALESCE(NULLIF(stage,''),'confirmed')='confirmed' ORDER BY last_seen_at DESC, id DESC LIMIT ".($limit * 3));
    if ($rs) while ($r = $rs->fetch_assoc()) {
      $meta = [];
      $rawMeta = (string)($r['meta_json'] ?? '');
      if ($rawMeta !== '') { $tmp = json_decode($rawMeta, true); if (is_array($tmp)) $meta = $tmp; }
      $iface = (string)($meta['interface'] ?? '');
      $allow = function_exists('mtwatch_allowlist_match') ? mtwatch_allowlist_match($conn, (string)($r['mac_address'] ?? ''), (string)($r['ip_address'] ?? ''), $iface, (string)($r['username'] ?? ''), (string)($r['source'] ?? '')) : ['matched'=>false];
      if (!empty($allow['matched'])) {
        $id = (int)($r['id'] ?? 0);
        if ($id > 0) @ $conn->query("UPDATE subscriber_network_alerts SET is_open=0 WHERE id={$id} LIMIT 1");
        continue;
      }
      $id = (int)($r['id'] ?? 0);
      $r['href'] = $id > 0 ? ('security_monitor.php?type=network&id=' . $id) : 'security_monitor.php';
      $rows[] = $r;
      if (count($rows) >= $limit) break;
    }
    return $rows;
  }
}
