<?php
// admin/subscribers_lib.php

require_once __DIR__ . '/helpers.php';
require_once __DIR__ . '/settings_store.php';
require_once __DIR__ . '/mikrotik_lib.php';

/**
 * Return the subscriber-facing part of a RouterOS rate-limit value.
 *
 * RouterOS may append burst rate, burst threshold, burst time, priority and
 * minimum rate as whitespace-separated fields. Those fields are operational
 * settings, not additional subscriber speeds, so printed information should
 * show only the first (base upload/download) pair while the stored value stays
 * untouched for router operations.
 */
function subs_rate_limit_display(string $rateLimit): string {
  $rateLimit = trim((string)preg_replace('/\s+/u', ' ', $rateLimit));
  if ($rateLimit === '') return '';

  $parts = preg_split('/\s+/u', $rateLimit, -1, PREG_SPLIT_NO_EMPTY) ?: [];
  $base = (string)($parts[0] ?? '');
  if (count($parts) > 1 && preg_match('/^[0-9]+(?:\.[0-9]+)?[KMGTP]?(?:\/[0-9]+(?:\.[0-9]+)?[KMGTP]?)?$/iu', $base)) {
    return strtoupper($base);
  }
  return $rateLimit;
}

/**
 * Return the subscriber speed used on printable cards.
 *
 * Some imported subscribers have no explicit speed name while their PPPoE
 * profile still carries it (for example PPPOE-2M). Prefer the explicit value,
 * then derive a concise display value from the active profile.
 */
function subs_speed_display(array $row, string $profile = ''): string {
  foreach (['speed_name', 'speed'] as $key) {
    $value = trim((string)($row[$key] ?? ''));
    if ($value !== '' && $value !== '-' && $value !== '—') return $value;
  }

  $profile = trim($profile);
  if ($profile === '' || $profile === '-' || $profile === '—') return '';

  $withoutPrefix = trim((string)preg_replace('/^(?:PPPOE?|PPP|PROFILE)[\s_-]*/i', '', $profile));
  if (preg_match('/(\d+(?:\.\d+)?)\s*([KMGTP])(?:BPS)?/i', $withoutPrefix, $match)) {
    return $match[1] . strtoupper($match[2]);
  }
  return $withoutPrefix !== '' ? $withoutPrefix : $profile;
}

/** Ensure subscriber columns needed for PPPoE provisioning and safe deletion. */
function subs_ensure_pppoe_cols(mysqli $conn): void {
  if (!table_exists($conn,'subscribers')) return;

  // Soft delete columns
  if (!col_exists($conn,'subscribers','is_deleted')) {
    @ $conn->query("ALTER TABLE `subscribers` ADD COLUMN `is_deleted` TINYINT(1) NOT NULL DEFAULT 0");
  }
  if (!col_exists($conn,'subscribers','deleted_at')) {
    @ $conn->query("ALTER TABLE `subscribers` ADD COLUMN `deleted_at` DATETIME NULL");
  }

  // PPPoE fields (optional but useful for display and edits)
  if (!col_exists($conn,'subscribers','ppp_username')) {
    @ $conn->query("ALTER TABLE `subscribers` ADD COLUMN `ppp_username` VARCHAR(100) NULL");
  }
  if (!col_exists($conn,'subscribers','ppp_password')) {
    @ $conn->query("ALTER TABLE `subscribers` ADD COLUMN `ppp_password` VARCHAR(100) NULL");
  }
  if (!col_exists($conn,'subscribers','ppp_profile')) {
    @ $conn->query("ALTER TABLE `subscribers` ADD COLUMN `ppp_profile` VARCHAR(100) NULL");
  }
  if (!col_exists($conn,'subscribers','ppp_rate_limit')) {
    @ $conn->query("ALTER TABLE `subscribers` ADD COLUMN `ppp_rate_limit` VARCHAR(50) NULL");
  }
  if (!col_exists($conn,'subscribers','ppp_last_sync')) {
    @ $conn->query("ALTER TABLE `subscribers` ADD COLUMN `ppp_last_sync` DATETIME NULL");
  }
  $pendingColumns = [
    'pending_ppp_username' => "VARCHAR(160) NULL",
    'pending_ppp_password' => "VARCHAR(160) NULL",
    'pending_ppp_profile' => "VARCHAR(120) NULL",
    'pending_ppp_rate_limit' => "VARCHAR(80) NULL",
    'pending_ppp_policy_suffix' => "VARCHAR(190) NULL",
    'pending_encryption_at' => "DATETIME NULL",
  ];
  foreach ($pendingColumns as $column => $definition) {
    if (!col_exists($conn, 'subscribers', $column)) {
      @ $conn->query("ALTER TABLE `subscribers` ADD COLUMN `{$column}` {$definition}");
    }
  }
  if (!col_exists($conn,'subscribers','ip_address')) {
    @ $conn->query("ALTER TABLE `subscribers` ADD COLUMN `ip_address` VARCHAR(45) NULL");
  }



  // PPPoE manual cut state (cached in DB to render button without querying MikroTik for each row)
  if (!col_exists($conn,'subscribers','ppp_is_disabled')) {
    @ $conn->query("ALTER TABLE `subscribers` ADD COLUMN `ppp_is_disabled` TINYINT(1) NOT NULL DEFAULT 0");
  }
  if (!col_exists($conn,'subscribers','ppp_disabled_at')) {
    @ $conn->query("ALTER TABLE `subscribers` ADD COLUMN `ppp_disabled_at` DATETIME NULL");
  }

  // MikroTik sync snapshot (generic - PPPoE/Hotspot)
  if (!col_exists($conn,'subscribers','mt_source_type')) {
    @ $conn->query("ALTER TABLE `subscribers` ADD COLUMN `mt_source_type` VARCHAR(20) NULL");
  }
  if (!col_exists($conn,'subscribers','mt_profile')) {
    @ $conn->query("ALTER TABLE `subscribers` ADD COLUMN `mt_profile` VARCHAR(100) NULL");
  }
  if (!col_exists($conn,'subscribers','mt_rate_limit')) {
    @ $conn->query("ALTER TABLE `subscribers` ADD COLUMN `mt_rate_limit` VARCHAR(50) NULL");
  }
  if (!col_exists($conn,'subscribers','mt_limit_bytes_in')) {
    @ $conn->query("ALTER TABLE `subscribers` ADD COLUMN `mt_limit_bytes_in` BIGINT NULL DEFAULT 0");
  }
  if (!col_exists($conn,'subscribers','mt_limit_bytes_out')) {
    @ $conn->query("ALTER TABLE `subscribers` ADD COLUMN `mt_limit_bytes_out` BIGINT NULL DEFAULT 0");
  }
  if (!col_exists($conn,'subscribers','mt_limit_bytes_total')) {
    @ $conn->query("ALTER TABLE `subscribers` ADD COLUMN `mt_limit_bytes_total` BIGINT NULL DEFAULT 0");
  }
  if (!col_exists($conn,'subscribers','mt_disabled')) {
    @ $conn->query("ALTER TABLE `subscribers` ADD COLUMN `mt_disabled` TINYINT(1) NOT NULL DEFAULT 0");
  }
  if (!col_exists($conn,'subscribers','mt_comment')) {
    @ $conn->query("ALTER TABLE `subscribers` ADD COLUMN `mt_comment` VARCHAR(255) NULL");
  }
  if (!col_exists($conn,'subscribers','mt_last_sync')) {
    @ $conn->query("ALTER TABLE `subscribers` ADD COLUMN `mt_last_sync` DATETIME NULL");
  }

}

/** Generate a random password for PPPoE */
function subs_rand_pass(int $len=10): string {
  $alphabet = 'ABCDEFGHJKLMNPQRSTUVWXYZabcdefghijkmnopqrstuvwxyz23456789';
  $out='';
  for($i=0;$i<$len;$i++) $out .= $alphabet[random_int(0, strlen($alphabet)-1)];
  return $out;
}

/** Get PPPoE defaults from settings. */
function subs_ppp_defaults(mysqli $conn, array $subRow): array {
  $profile = trim((string)($subRow['ppp_profile'] ?? ''));
  if ($profile === '') {
    $profile = trim((string)setting_get($conn,'pppoe_default_profile',''));
  }

  // Rate-limit: allow per-speed mapping pppoe_rate_1M etc.
  $rate = trim((string)($subRow['ppp_rate_limit'] ?? ''));
  if ($rate === '' && !empty($subRow['speed_name'])) {
    $k = 'pppoe_rate_' . $subRow['speed_name'];
    $rate = trim((string)setting_get($conn,$k,''));
  }
  if ($rate === '') {
    $rate = trim((string)setting_get($conn,'pppoe_default_rate_limit',''));
  }

  return ['profile'=>$profile,'rate'=>$rate];
}

/** Normalize IPv4 text used as PPPoE static remote-address. */
function subs_static_ip_normalize(string $ip): string {
  $ip = trim($ip);
  if ($ip === '') return '';
  // Remove accidental surrounding whitespace/control chars without changing the address itself.
  $ip = preg_replace('/\s+/', '', $ip) ?? $ip;
  return trim($ip);
}

/** Validate static PPPoE IPv4. Private ranges are allowed; invalid/special addresses are rejected. */
function subs_static_ip_validate(string $ip): string {
  $ip = subs_static_ip_normalize($ip);
  if ($ip === '') return '';
  if (!preg_match('/^(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(\.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3}$/', $ip)) {
    return 'صيغة IP غير صحيحة. اكتب IPv4 مثل 10.10.10.77';
  }
  if (!filter_var($ip, FILTER_VALIDATE_IP, FILTER_FLAG_IPV4)) {
    return 'صيغة IP غير صحيحة.';
  }
  $parts = array_map('intval', explode('.', $ip));
  [$a,$b,$c,$d] = $parts;
  if ($ip === '0.0.0.0' || $ip === '255.255.255.255') return 'لا يمكن استخدام هذا العنوان كـ Static IP.';
  if ($a === 127) return 'لا يمكن استخدام عناوين loopback 127.x.x.x للمشتركين.';
  if ($a === 169 && $b === 254) return 'لا يمكن استخدام عناوين link-local 169.254.x.x للمشتركين.';
  if ($a >= 224) return 'لا يمكن استخدام عناوين multicast/reserved للمشتركين.';
  if ($d === 0 || $d === 255) return 'لا تستخدم عنوان شبكة أو broadcast ينتهي بـ .0 أو .255 للمشترك.';
  return '';
}

/** Check if static IP already belongs to another subscriber in DB. */
function subs_static_ip_db_conflict(mysqli $conn, string $ip, int $currentId=0): ?array {
  $ip = subs_static_ip_normalize($ip);
  if ($ip === '' || !table_exists($conn,'subscribers') || !col_exists($conn,'subscribers','ip_address')) return null;
  $w = "ip_address='".$conn->real_escape_string($ip)."'";
  if ($currentId > 0) $w .= " AND id<>".(int)$currentId;
  if (col_exists($conn,'subscribers','is_deleted')) $w .= " AND IFNULL(is_deleted,0)=0";
  $rs = @ $conn->query("SELECT id, username, full_name, ppp_username, ip_address FROM subscribers WHERE $w LIMIT 1");
  if ($rs && $rs->num_rows) return $rs->fetch_assoc();
  return null;
}

/** Pick the static IP from a subscriber row. */
function subs_static_ip_from_row(array $subRow): string {
  foreach (['static_ip','ip_address','ip'] as $k) {
    $v = subs_static_ip_normalize((string)($subRow[$k] ?? ''));
    if ($v !== '') return $v;
  }
  return '';
}

/**
 * Provision / Update PPPoE secret on MikroTik for a subscriber.
 * Returns ['ok'=>bool,'message'=>string]
 */
function subs_pppoe_provision(mysqli $conn, array $subRow, bool $force=false, bool $ignore_enabled=false): array {
  $enabled = (int)setting_get($conn,'pppoe_auto_provision','1');
  if (!$enabled && !$ignore_enabled) return ['ok'=>true,'message'=>'PPPoE provisioning disabled'];

  $user = trim((string)($subRow['ppp_username'] ?? $subRow['username'] ?? ''));
  if ($user === '') return ['ok'=>false,'message'=>'اسم المستخدم PPPoE فارغ'];

  $pass = trim((string)($subRow['ppp_password'] ?? $subRow['sub_password'] ?? ''));
  if ($pass === '') {
    $pass = subs_rand_pass(10);
    // Store it back
    if (col_exists($conn,'subscribers','ppp_password')) {
      @ $conn->query("UPDATE subscribers SET ppp_password='".$conn->real_escape_string($pass)."' WHERE id=".(int)$subRow['id']);
    }
    if (col_exists($conn,'subscribers','sub_password')) {
      @ $conn->query("UPDATE subscribers SET sub_password='".$conn->real_escape_string($pass)."' WHERE id=".(int)$subRow['id']);
    }
  }

  $defs = subs_ppp_defaults($conn, $subRow);
  $profile = $defs['profile'];
  $rate    = $defs['rate'];

  // Remote address (optional): fixed PPPoE IP saved in subscribers.ip_address.
  $remote = subs_static_ip_from_row($subRow);
  if ($remote !== '') {
    $ipErr = subs_static_ip_validate($remote);
    if ($ipErr !== '') return ['ok'=>false,'message'=>$ipErr];
  }

  $comment = 'SUB#'.(int)$subRow['id'].' '.trim((string)($subRow['full_name'] ?? ''));

  // Ensure profile exists if provided
  if ($profile !== '') {
    if (!mt_ppp_profile_exists($conn, $profile)) {
      if (!mt_ppp_profile_ensure($conn, $profile, $rate)) {
        return ['ok'=>false,'message'=>$GLOBALS['mt_last_error'] ?? 'فشل إنشاء بروفايل PPPoE'];
      }
    } else {
      // Update rate if provided
      if ($rate !== '') {
        @mt_ppp_profile_ensure($conn, $profile, $rate);
      }
    }
  }

  // Upsert secret (create/update PPPoE account without duplication)
  if (!$force && function_exists('has_perm')) {
    // In manual admin screens, require explicit permission to provision PPPoE
    if (!has_perm('can_pppoe_sync') && !has_perm('can_mikrotik_sync')) {
      return ['ok'=>false,'message'=>'ليس لديك صلاحية مزامنة PPPoE على MikroTik'];
    }
  }
  if (!mt_ppp_secret_upsert($conn, $user, $pass, $profile, $remote, $comment, $rate)) {
    return ['ok'=>false,'message'=>$GLOBALS['mt_last_error'] ?? 'فشل إنشاء حساب PPPoE'];
  }
  // If secret exists and rate specified but profile empty, set per-secret rate
  if ($profile === '' && $rate !== '') {
    @mt_ppp_secret_set_rate($conn, $user, $rate);
  }

  // Save back PPP fields
  if (col_exists($conn,'subscribers','ppp_username')) {
    @ $conn->query("UPDATE subscribers SET ppp_username='".$conn->real_escape_string($user)."', ppp_last_sync=NOW() WHERE id=".(int)$subRow['id']);
  }
  return ['ok'=>true,'message'=>'تم تجهيز PPPoE على MikroTik'];
}

/** Remove PPPoE secret */
function subs_pppoe_remove(mysqli $conn, string $pppUser): bool {
  if ($pppUser==='') return true;
  return mt_ppp_secret_remove($conn, $pppUser);
}


/**
 * Generate PPPoE credentials using the same policy used by the add-subscriber page:
 * username: latin(first_name.last_name)@configured-domain, password: first latin letter of mother_name + last 5 phone digits.
 */
function subs_policy_digits(string $v): string {
  return preg_replace('/\D+/', '', $v) ?? '';
}

function subs_policy_normalize_pppoe_domain(string $domain): string {
  $domain = strtolower(ltrim(trim($domain), '@'));
  $domain = preg_replace('/\s+/', '', $domain) ?? '';
  if ($domain === '' || strlen($domain) > 190) return '';
  if (!preg_match('/^[a-z0-9](?:[a-z0-9.-]*[a-z0-9])?$/', $domain) || str_contains($domain, '..')) return '';
  foreach (explode('.', $domain) as $label) {
    if ($label === '' || strlen($label) > 63 || $label[0] === '-' || substr($label, -1) === '-') return '';
  }
  return $domain;
}

function subs_policy_default_pppoe_domain(): string {
  return 'exa.net';
}

function subs_policy_pppoe_domain(mysqli $conn): string {
  // settings_store.php is required by this library, so policy code must read
  // its canonical store directly and must not depend on UI/lang bootstrap.
  $fallback = subs_policy_default_pppoe_domain();
  $configured = (string)setting_get($conn, 'pppoe_account_suffix', $fallback);
  $domain = subs_policy_normalize_pppoe_domain($configured);
  return $domain !== '' ? $domain : $fallback;
}

function subs_policy_username_domain(string $username): string {
  $username = trim($username);
  $at = strrpos($username, '@');
  if ($at === false || $at === strlen($username) - 1) return '';
  return subs_policy_normalize_pppoe_domain(substr($username, $at + 1));
}

/** A staged account may be applied/printed only under the active suffix policy. */
function subs_policy_staged_account_status(mysqli $conn, array $row): array {
  $username = trim((string)($row['pending_ppp_username'] ?? ''));
  $expected = subs_policy_pppoe_domain($conn);
  if ($username === '') {
    return ['ready'=>false,'stale'=>false,'expected_suffix'=>$expected,'actual_suffix'=>'','message'=>'لا توجد بيانات تشفير مجهزة.'];
  }
  $stored = subs_policy_normalize_pppoe_domain((string)($row['pending_ppp_policy_suffix'] ?? ''));
  $actual = $stored !== '' ? $stored : subs_policy_username_domain($username);
  if ($actual === '' || strcasecmp($actual, $expected) !== 0) {
    return [
      'ready'=>false,
      'stale'=>true,
      'expected_suffix'=>$expected,
      'actual_suffix'=>$actual,
      'message'=>'بيانات التشفير المجهزة تتبع لاحقة قديمة. أعد تجهيز التشفير وفق إعداد البرنامج الحالي @'.$expected.'.',
    ];
  }
  return ['ready'=>true,'stale'=>false,'expected_suffix'=>$expected,'actual_suffix'=>$actual,'message'=>''];
}

function subs_policy_ar_to_en_word(string $text): string {
  $text = trim((string)$text);
  if ($text === '') return '';
  if (class_exists('Transliterator')) {
    try {
      $tr = Transliterator::create('Any-Latin; Latin-ASCII');
      if ($tr) {
        $latin = (string)$tr->transliterate($text);
        $latin = strtolower(preg_replace('/[^a-z0-9\s\._-]+/i', ' ', $latin) ?? '');
        $latin = trim(preg_replace('/\s+/', ' ', $latin) ?? '');
        if ($latin !== '') {
          $parts = preg_split('/\s+/', $latin) ?: [];
          $out = [];
          foreach ($parts as $p) {
            $p = preg_replace('/[^a-z0-9]+/', '', trim($p)) ?? '';
            if ($p !== '') $out[] = $p;
          }
          if ($out) return implode(' ', $out);
        }
      }
    } catch (Throwable $e) {}
  }
  $map = [
    'ا'=>'a','أ'=>'a','إ'=>'e','آ'=>'a','ء'=>'','ؤ'=>'o','ئ'=>'e','ى'=>'a','ة'=>'a',
    'ب'=>'b','ت'=>'t','ث'=>'th','ج'=>'j','ح'=>'h','خ'=>'kh','د'=>'d','ذ'=>'dh','ر'=>'r','ز'=>'z',
    'س'=>'s','ش'=>'sh','ص'=>'s','ض'=>'d','ط'=>'t','ظ'=>'z','ع'=>'e','غ'=>'gh','ف'=>'f','ق'=>'q',
    'ك'=>'k','ل'=>'l','م'=>'m','ن'=>'n','ه'=>'h','و'=>'w','ي'=>'y','ﻻ'=>'la','لا'=>'la',
    'َ'=>'','ً'=>'','ُ'=>'','ٌ'=>'','ِ'=>'','ٍ'=>'','ْ'=>'','ّ'=>'',
  ];
  $text = strtolower(strtr($text, $map));
  $text = trim(preg_replace('/\s+/', ' ', preg_replace('/[^a-z0-9\s]+/', ' ', $text) ?? '') ?? '');
  if ($text === '') return '';
  $parts = preg_split('/\s+/', $text) ?: [];
  $out = [];
  foreach ($parts as $p) {
    $p = trim($p);
    if ($p === '') continue;
    if ($p === 'emad' || $p === 'amad') $p = 'emad';
    if ($p === 'ahmd' || $p === 'ahmad' || $p === 'ahmed') $p = 'ahmad';
    if ($p === 'mhmd' || $p === 'mohmd' || $p === 'muhmd') $p = 'mohamad';
    $out[] = $p;
  }
  return implode(' ', $out);
}

function subs_policy_account_name_part(string $name): string {
  $name = trim($name);
  if ($name === '') return '';
  $parts = preg_split('/[\s,،؛;]+/u', $name, 2) ?: [];
  return trim((string)($parts[0] ?? ''));
}

function subs_policy_slug_local(string $firstName, string $lastName): string {
  $firstName = subs_policy_account_name_part($firstName);
  $lastName = subs_policy_account_name_part($lastName);
  $first = preg_replace('/[^a-z0-9]+/', '.', subs_policy_ar_to_en_word($firstName)) ?? '';
  $last  = preg_replace('/[^a-z0-9]+/', '.', subs_policy_ar_to_en_word($lastName)) ?? '';
  $slug = trim(trim($first, '.') . '.' . trim($last, '.'), '.');
  $slug = preg_replace('/\.+/', '.', $slug) ?? '';
  return strtolower($slug);
}

function subs_policy_password(string $motherName, string $phone): string {
  $motherLatin = subs_policy_ar_to_en_word($motherName);
  $initial = strtoupper(substr(preg_replace('/[^a-z]/', '', $motherLatin) ?: 'x', 0, 1));
  if ($initial === '') $initial = 'X';
  $digits = subs_policy_digits($phone);
  $tail = substr(str_pad($digits, 5, '0', STR_PAD_LEFT), -5);
  return $initial . $tail;
}

function subs_policy_pick_names(array $subRow): array {
  $first = trim((string)($subRow['first_name'] ?? ''));
  $last  = trim((string)($subRow['last_name'] ?? ''));
  if ($first === '' || $last === '') {
    $full = trim((string)($subRow['full_name'] ?? ''));
    $parts = preg_split('/\s+/u', $full) ?: [];
    $parts = array_values(array_filter($parts, fn($x) => trim((string)$x) !== ''));
    if ($first === '' && isset($parts[0])) $first = (string)$parts[0];
    if ($last === '' && count($parts) >= 2) $last = (string)$parts[count($parts)-1];
  }
  return [$first, $last];
}

function subs_policy_secret_exists_on_mt(mysqli $conn, string $user): bool {
  $user = trim($user);
  if ($user === '') return false;
  $api = null;
  try {
    $api = mt_api($conn);
    if (!$api) return false;
    $rows = $api->command(['/ppp/secret/print', '?name='.$user]);
    foreach ((array)$rows as $r) {
      if (($r['.type'] ?? '') === '!re' || isset($r['.id']) || (isset($r['name']) && (string)$r['name'] === $user)) return true;
    }
  } catch (Throwable $e) {
  } finally {
    try { if ($api && method_exists($api, 'disconnect')) $api->disconnect(); } catch (Throwable $ignore) {}
  }
  return false;
}

function subs_policy_unique_pppoe(mysqli $conn, string $local, string $domain='', int $excludeId=0, string $oldUser=''): string {
  $domain = subs_policy_normalize_pppoe_domain($domain);
  if ($domain === '') $domain = subs_policy_pppoe_domain($conn);
  $local = trim(strtolower($local), '.');
  if ($local === '') $local = 'subscriber';
  $oldUser = trim($oldUser);
  for ($i=1; $i<=50; $i++) {
    $base = ($i === 1) ? $local : ($local . $i);
    $user = $base . '@' . $domain;
    $uEsc = $conn->real_escape_string($user);
    $userColumns = ["ppp_username='$uEsc'", "username='$uEsc'"];
    if (col_exists($conn, 'subscribers', 'pending_ppp_username')) $userColumns[] = "pending_ppp_username='$uEsc'";
    $where = '(' . implode(' OR ', $userColumns) . ')';
    if ($excludeId > 0) $where .= " AND id<>".(int)$excludeId;
    if (col_exists($conn,'subscribers','is_deleted')) $where .= " AND COALESCE(is_deleted,0)=0";
    $q = @$conn->query("SELECT id FROM subscribers WHERE $where LIMIT 1");
    if ($q && $q->num_rows > 0) continue;
    $existsMt = subs_policy_secret_exists_on_mt($conn, $user);
    if ($existsMt && strcasecmp($user, $oldUser) !== 0) continue;
    return $user;
  }
  return $local . time() . '@' . $domain;
}

function subs_policy_profile_rate_from_row(mysqli $conn, array $row): array {
  $profile = trim((string)($row['ppp_profile'] ?? ''));
  if ($profile === '') $profile = trim((string)($row['pppoe_profile'] ?? ''));
  if ($profile === '') $profile = trim((string)($row['mt_profile'] ?? ''));
  $speed = trim((string)($row['speed_name'] ?? ($row['speed'] ?? '')));
  $package = trim((string)($row['package_name'] ?? ($row['package'] ?? '')));
  if ($profile === '' && function_exists('ppp_profile_from_speed')) {
    $profile = trim((string)ppp_profile_from_speed($speed, $package));
  }
  if ($profile === '' && preg_match('/(\d{1,3})\s*M/i', $speed . ' ' . $package, $m)) {
    $profile = 'PPPOE-' . (int)$m[1] . 'M';
  }
  $rate = trim((string)($row['ppp_rate_limit'] ?? ''));
  if ($rate === '') $rate = trim((string)($row['mt_rate_limit'] ?? ''));
  if ($rate === '' && preg_match('/(\d{1,3})\s*M/i', $speed, $m2)) {
    $rate = ((int)$m2[1]) . 'M/' . ((int)$m2[1]) . 'M';
  }
  return [$profile, $rate];
}

function subs_policy_generate_credentials(mysqli $conn, array $subRow, int $excludeId=0): array {
  [$first, $last] = subs_policy_pick_names($subRow);
  $mother = trim((string)($subRow['mother_name'] ?? ''));
  $phone  = trim((string)($subRow['phone'] ?? ''));
  if ($first === '' || $last === '') {
    return ['ok'=>false,'message'=>'تشفير الحساب يحتاج الاسم الأول والكنية أو اسم كامل مؤلف من كلمتين على الأقل.'];
  }
  if ($mother === '') {
    return ['ok'=>false,'message'=>'تشفير الحساب يحتاج اسم الأم لتوليد كلمة المرور حسب سياسة البرنامج.'];
  }
  if (strlen(subs_policy_digits($phone)) < 5) {
    return ['ok'=>false,'message'=>'تشفير الحساب يحتاج رقم هاتف يحتوي 5 أرقام على الأقل.'];
  }
  $local = subs_policy_slug_local($first, $last);
  if ($local === '') return ['ok'=>false,'message'=>'تعذر توليد اسم PPPoE من الاسم والكنية.'];
  $oldUser = trim((string)($subRow['ppp_username'] ?? ($subRow['username'] ?? '')));
  $user = subs_policy_unique_pppoe($conn, $local, '', $excludeId, $oldUser);
  $pass = subs_policy_password($mother, $phone);
  return ['ok'=>true,'username'=>$user,'password'=>$pass,'first_name'=>$first,'last_name'=>$last];
}

function subs_migrate_username_refs(mysqli $conn, string $oldUser, string $newUser, int $subscriberId): void {
  $oldUser = trim($oldUser); $newUser = trim($newUser);
  if ($oldUser === '' || $newUser === '' || $oldUser === $newUser) return;
  $oldBase = preg_replace('/@.*/', '', $oldUser);
  $newBase = preg_replace('/@.*/', '', $newUser);
  $pairs = [[$oldUser,$newUser]];
  if ($oldBase !== '' && $oldBase !== $oldUser) $pairs[] = [$oldBase,$newBase];
  foreach ($pairs as [$old,$new]) {
    $oe = $conn->real_escape_string($old);
    $ne = $conn->real_escape_string($new);
    if (table_exists($conn,'ppp_usage_monthly') && col_exists($conn,'ppp_usage_monthly','username')) {
      @ $conn->query("UPDATE ppp_usage_monthly SET username='$ne' WHERE username='$oe'");
    }
    if (table_exists($conn,'quota_addons') && col_exists($conn,'quota_addons','username')) {
      @ $conn->query("UPDATE quota_addons SET username='$ne' WHERE username='$oe'");
    }
    if (table_exists($conn,'quota_state') && col_exists($conn,'quota_state','username')) {
      @ $conn->query("UPDATE quota_state SET username='$ne' WHERE username='$oe'");
    }
  }
}

/** Prepare printable policy credentials without changing DB identity or RouterOS. */
function subs_stage_policy_account(mysqli $conn, int $subscriberId): array {
  subs_ensure_pppoe_cols($conn);
  if ($subscriberId <= 0) return ['ok'=>false,'message'=>'مشترك غير صالح.'];
  $rs = @$conn->query("SELECT * FROM subscribers WHERE id=".(int)$subscriberId." LIMIT 1");
  if (!$rs || !$rs->num_rows) return ['ok'=>false,'message'=>'المشترك غير موجود.'];
  $row = $rs->fetch_assoc();
  $gen = subs_policy_generate_credentials($conn, $row, $subscriberId);
  if (empty($gen['ok'])) return $gen;
  [$profile, $rate] = subs_policy_profile_rate_from_row($conn, $row);
  if ($profile === '') return ['ok'=>false,'message'=>'تعذر تحديد PPPoE Profile؛ اضبط السرعة أو البروفايل أولاً.'];

  $user = (string)$gen['username'];
  $pass = (string)$gen['password'];
  $policySuffix = subs_policy_pppoe_domain($conn);
  $stmt = $conn->prepare('UPDATE subscribers SET pending_ppp_username=?,pending_ppp_password=?,pending_ppp_profile=?,pending_ppp_rate_limit=?,pending_ppp_policy_suffix=?,pending_encryption_at=NOW() WHERE id=? LIMIT 1');
  if (!$stmt) return ['ok'=>false,'message'=>'تعذر تجهيز بيانات التشفير: '.$conn->error];
  $stmt->bind_param('sssssi', $user, $pass, $profile, $rate, $policySuffix, $subscriberId);
  $ok = $stmt->execute();
  $stmt->close();
  if (!$ok) return ['ok'=>false,'message'=>'تعذر حفظ بيانات التشفير المجهزة.'];
  @audit_log($conn, 'SUBSCRIBER_ACCOUNT_ENCRYPT_STAGE', 'subscribers', $subscriberId, 'pending='.$user);
  return ['ok'=>true,'staged'=>true,'message'=>'تم تجهيز الحساب المشفر في قاعدة البيانات فقط. يمكنك طباعته قبل التنفيذ.','username'=>$user,'password'=>$pass,'profile'=>$profile,'rate_limit'=>$rate];
}

/** Apply credentials that were explicitly staged and printed/reviewed. */
function subs_apply_staged_policy_account(mysqli $conn, int $subscriberId): array {
  subs_ensure_pppoe_cols($conn);
  $rs = @$conn->query("SELECT * FROM subscribers WHERE id=".(int)$subscriberId." LIMIT 1");
  if (!$rs || !$rs->num_rows) return ['ok'=>false,'message'=>'المشترك غير موجود.'];
  $row = $rs->fetch_assoc();
  $stageStatus = subs_policy_staged_account_status($conn, $row);
  if (empty($stageStatus['ready'])) return ['ok'=>false,'message'=>(string)$stageStatus['message']];
  $newUser = trim((string)($row['pending_ppp_username'] ?? ''));
  $newPass = (string)($row['pending_ppp_password'] ?? '');
  $profile = trim((string)($row['pending_ppp_profile'] ?? ''));
  $rate = trim((string)($row['pending_ppp_rate_limit'] ?? ''));
  if ($newUser === '' || $newPass === '' || $profile === '') {
    return ['ok'=>false,'message'=>'لا توجد بيانات مشفرة مجهزة. نفّذ مرحلة التجهيز أولاً.'];
  }
  return subs_regenerate_policy_account($conn, $subscriberId, [
    'username'=>$newUser,
    'password'=>$newPass,
    'profile'=>$profile,
    'rate_limit'=>$rate,
    'consume_staged'=>true,
  ]);
}

/**
 * Regenerate a subscriber PPPoE account using program policy while keeping the same subscriber id,
 * financial records, consumption rows, and all personal information.
 */
function subs_regenerate_policy_account(mysqli $conn, int $subscriberId, ?array $prepared=null): array {
  subs_ensure_pppoe_cols($conn);
  $subscriberId = (int)$subscriberId;
  if ($subscriberId <= 0) return ['ok'=>false,'message'=>'مشترك غير صالح.'];
  $rs = @$conn->query("SELECT * FROM subscribers WHERE id=$subscriberId LIMIT 1");
  if (!$rs || !$rs->num_rows) return ['ok'=>false,'message'=>'المشترك غير موجود.'];
  $row = $rs->fetch_assoc();
  $oldUser = trim((string)($row['ppp_username'] ?? ($row['username'] ?? '')));
  $gen = $prepared ?: subs_policy_generate_credentials($conn, $row, $subscriberId);
  if (empty($prepared) && empty($gen['ok'])) return $gen;
  $newUser = (string)($gen['username'] ?? '');
  $newPass = (string)($gen['password'] ?? '');
  if ($prepared) {
    $profile = trim((string)($prepared['profile'] ?? ''));
    $rate = trim((string)($prepared['rate_limit'] ?? ''));
  } else {
    [$profile, $rate] = subs_policy_profile_rate_from_row($conn, $row);
  }
  if ($profile === '') return ['ok'=>false,'message'=>'تعذر تحديد PPP Profile للحساب الجديد؛ اضبط السرعة/البروفايل أولاً.'];

  try {
    mt_require_router_online_or_throw($conn, 'تشفير حساب المشترك');
  } catch (Throwable $e) {
    return ['ok'=>false,'message'=>$e->getMessage()];
  }

  $comment = 'SUB#'.$subscriberId.' encrypted-policy '.trim((string)($row['full_name'] ?? ''));
  $createdNewOnMt = false;
  try {
    if (!mt_ppp_profile_ensure($conn, $profile, $rate)) {
      return ['ok'=>false,'message'=>$GLOBALS['mt_last_error'] ?? 'فشل تجهيز PPP Profile على MikroTik'];
    }
    if (!mt_ppp_secret_upsert($conn, $newUser, $newPass, $profile, '', $comment, $rate)) {
      return ['ok'=>false,'message'=>$GLOBALS['mt_last_error'] ?? 'فشل إنشاء الحساب الجديد على MikroTik'];
    }
    $createdNewOnMt = (strcasecmp($newUser, $oldUser) !== 0);
    $disabled = (!empty($row['ppp_is_disabled']) || !empty($row['mt_disabled']));
    @mt_ppp_secret_set_disabled($conn, $newUser, (bool)$disabled);

    $conn->begin_transaction();
    $sets = [];
    if (col_exists($conn,'subscribers','username')) $sets[] = "username='".$conn->real_escape_string($newUser)."'";
    // full_name is deliberately absent: changing PPP credentials must never
    // write to, derive, or normalize the subscriber's personal identity.
    if (col_exists($conn,'subscribers','password_plain')) $sets[] = "password_plain='".$conn->real_escape_string($newPass)."'";
    if (col_exists($conn,'subscribers','ppp_username')) $sets[] = "ppp_username='".$conn->real_escape_string($newUser)."'";
    if (col_exists($conn,'subscribers','ppp_password')) $sets[] = "ppp_password='".$conn->real_escape_string($newPass)."'";
    if (col_exists($conn,'subscribers','ppp_profile')) $sets[] = "ppp_profile='".$conn->real_escape_string($profile)."'";
    if (col_exists($conn,'subscribers','pppoe_profile')) $sets[] = "pppoe_profile='".$conn->real_escape_string($profile)."'";
    if (col_exists($conn,'subscribers','ppp_rate_limit')) $sets[] = "ppp_rate_limit='".$conn->real_escape_string($rate)."'";
    if (col_exists($conn,'subscribers','mt_profile')) $sets[] = "mt_profile='".$conn->real_escape_string($profile)."'";
    if (col_exists($conn,'subscribers','mt_rate_limit')) $sets[] = "mt_rate_limit='".$conn->real_escape_string($rate)."'";
    if (col_exists($conn,'subscribers','ppp_last_sync')) $sets[] = "ppp_last_sync=NOW()";
    if (col_exists($conn,'subscribers','mt_last_sync')) $sets[] = "mt_last_sync=NOW()";
    if (col_exists($conn,'subscribers','updated_at')) $sets[] = "updated_at=NOW()";
    if (!empty($prepared['consume_staged'])) {
      foreach (['pending_ppp_username','pending_ppp_password','pending_ppp_profile','pending_ppp_rate_limit','pending_ppp_policy_suffix','pending_encryption_at'] as $pendingColumn) {
        if (col_exists($conn, 'subscribers', $pendingColumn)) $sets[] = "`{$pendingColumn}`=NULL";
      }
    }
    if (!$sets) throw new RuntimeException('لا توجد أعمدة قابلة لتحديث بيانات PPPoE.');
    if (!@$conn->query("UPDATE subscribers SET ".implode(',', $sets)." WHERE id=$subscriberId LIMIT 1")) {
      throw new RuntimeException('فشل تحديث قاعدة البيانات: '.$conn->error);
    }
    subs_migrate_username_refs($conn, $oldUser, $newUser, $subscriberId);
    $conn->commit();

    $removeWarning = '';
    if ($oldUser !== '' && strcasecmp($oldUser, $newUser) !== 0) {
      if (!subs_pppoe_remove($conn, $oldUser)) {
        $removeWarning = ' — تنبيه: تم إنشاء الحساب الجديد وتحديث البرنامج لكن لم يتم حذف الحساب القديم من MikroTik: '.($GLOBALS['mt_last_error'] ?? 'سبب غير معروف');
      }
    }
    @audit_log($conn, 'SUBSCRIBER_ACCOUNT_ENCRYPT', 'subscribers', $subscriberId, 'old='.$oldUser.'; new='.$newUser.'; profile='.$profile.'; rate='.$rate);
    return ['ok'=>true,'message'=>'تم تشفير الحساب حسب سياسة البرنامج: '.$newUser.$removeWarning,'username'=>$newUser,'password'=>$newPass];
  } catch (Throwable $e) {
    try { $conn->rollback(); } catch (Throwable $ignore) {}
    if ($createdNewOnMt) { @subs_pppoe_remove($conn, $newUser); }
    return ['ok'=>false,'message'=>$e->getMessage()];
  }
}
