<?php

/**
 * Keep the subscribers AUTO_INCREMENT counter above every imported display ID.
 * Excel imports may update the primary key after the row already exists; MySQL
 * does not consistently advance the in-memory counter for that operation.
 */
if (!function_exists('ua_subscriber_ensure_id_sequence')) {
  function ua_subscriber_ensure_id_sequence(mysqli $conn): int {
    $maxId = 0;
    $maxRs = @$conn->query('SELECT COALESCE(MAX(id),0) AS max_id FROM subscribers');
    if ($maxRs && ($maxRow = $maxRs->fetch_assoc())) $maxId = max(0, (int)($maxRow['max_id'] ?? 0));
    $target = $maxId + 1;

    $current = 0;
    $statusRs = @$conn->query("SHOW TABLE STATUS LIKE 'subscribers'");
    if ($statusRs && ($status = $statusRs->fetch_assoc())) $current = max(0, (int)($status['Auto_increment'] ?? 0));
    if ($current >= $target) return $current;

    $lockName = 'ua_subscriber_id_sequence';
    $lockEsc = $conn->real_escape_string($lockName);
    $locked = false;
    $lockRs = @$conn->query("SELECT GET_LOCK('$lockEsc',5) AS acquired");
    if ($lockRs && ($lockRow = $lockRs->fetch_assoc())) $locked = (int)($lockRow['acquired'] ?? 0) === 1;
    try {
      $freshRs = @$conn->query('SELECT COALESCE(MAX(id),0) AS max_id FROM subscribers');
      $freshMax = $maxId;
      if ($freshRs && ($freshRow = $freshRs->fetch_assoc())) $freshMax = max($freshMax, (int)($freshRow['max_id'] ?? 0));
      $target = $freshMax + 1;
      if (!@$conn->query('ALTER TABLE subscribers AUTO_INCREMENT=' . $target)) {
        throw new RuntimeException('تعذر مزامنة تسلسل أرقام المشتركين: ' . $conn->error);
      }
      return $target;
    } finally {
      if ($locked) @$conn->query("SELECT RELEASE_LOCK('$lockEsc')");
    }
  }
}

if (!function_exists('ua_subscriber_id_registry_ensure')) {
  function ua_subscriber_id_registry_ensure(mysqli $conn): void {
    $sql = "CREATE TABLE IF NOT EXISTS subscriber_id_registry (
      serial_id BIGINT UNSIGNED NOT NULL,
      first_subscriber_id BIGINT UNSIGNED NOT NULL,
      current_subscriber_id BIGINT UNSIGNED NULL,
      series_prefix VARCHAR(16) NOT NULL DEFAULT '',
      source VARCHAR(32) NOT NULL DEFAULT 'existing',
      first_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      last_used_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      retired_at DATETIME NULL,
      PRIMARY KEY (serial_id),
      KEY idx_subscriber_id_registry_current (current_subscriber_id),
      KEY idx_subscriber_id_registry_series (series_prefix)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
    if (!@$conn->query($sql)) throw new RuntimeException('تعذر إنشاء سجل أرقام المشتركين المستخدمة: ' . $conn->error);

    $seed = "INSERT IGNORE INTO subscriber_id_registry
      (serial_id,first_subscriber_id,current_subscriber_id,series_prefix,source,first_seen_at,last_used_at)
      SELECT id,id,id,IF(CHAR_LENGTH(CAST(id AS CHAR))>=6,LEFT(CAST(id AS CHAR),3),''),'existing',COALESCE(created_at,NOW()),NOW()
      FROM subscribers WHERE id>0";
    if (!@$conn->query($seed)) {
      // Older schemas may not have created_at.
      $fallback = "INSERT IGNORE INTO subscriber_id_registry
        (serial_id,first_subscriber_id,current_subscriber_id,series_prefix,source,first_seen_at,last_used_at)
        SELECT id,id,id,IF(CHAR_LENGTH(CAST(id AS CHAR))>=6,LEFT(CAST(id AS CHAR),3),''),'existing',NOW(),NOW()
        FROM subscribers WHERE id>0";
      if (!@$conn->query($fallback)) throw new RuntimeException('تعذر مزامنة سجل أرقام المشتركين المستخدمة: ' . $conn->error);
    }
  }
}

if (!function_exists('ua_subscriber_id_series_policy')) {
  function ua_subscriber_id_series_policy(mysqli $conn, int $currentId): array {
    $current = (string)max(0, $currentId);
    $prefix = strlen($current) >= 6 ? substr($current, 0, 3) : '';

    if ($prefix === '') {
      $result = @$conn->query("SELECT LEFT(CAST(id AS CHAR),3) series_prefix,COUNT(*) series_count,MAX(id) max_id
        FROM subscribers
        WHERE id BETWEEN 100000 AND 2147483647
        GROUP BY LEFT(CAST(id AS CHAR),3)
        ORDER BY series_count DESC,max_id DESC LIMIT 1");
      if ($result && ($row = $result->fetch_assoc())) $prefix = preg_replace('/\D+/', '', (string)($row['series_prefix'] ?? ''));
    }

    if ($prefix === '') {
      $prefix = strlen($current) >= 3 ? substr($current, 0, 3) : str_pad($current, 3, '0', STR_PAD_LEFT);
    }
    return ['prefix' => $prefix, 'min_length' => 6, 'max_value' => 2147483647];
  }
}

if (!function_exists('ua_subscriber_id_candidate_error')) {
  function ua_subscriber_id_candidate_error(array $policy, int $currentId, string $candidateRaw): string {
    $candidateRaw = trim($candidateRaw);
    if ($candidateRaw === '' || !preg_match('/^[0-9]+$/', $candidateRaw)) return 'الرقم التسلسلي يجب أن يتكون من أرقام فقط.';
    $candidate = (int)$candidateRaw;
    if ($candidate <= 0 || $candidate > (int)($policy['max_value'] ?? 2147483647)) return 'الرقم التسلسلي خارج المجال المسموح.';
    if ($candidate === $currentId) return '';
    $prefix = (string)($policy['prefix'] ?? '');
    $minLength = max(1, (int)($policy['min_length'] ?? 6));
    if (strlen($candidateRaw) < $minLength) return 'الرقم التسلسلي يجب ألا يقل عن ' . $minLength . ' خانات.';
    if ($prefix !== '' && !str_starts_with($candidateRaw, $prefix)) return 'الرقم الجديد يجب أن يبقى ضمن السلسلة ' . $prefix . '.';
    return '';
  }
}

if (!function_exists('ua_subscriber_id_availability_error')) {
  function ua_subscriber_id_availability_error(mysqli $conn, int $currentId, int $candidate): string {
    if ($candidate === $currentId) return '';
    $live = @$conn->query('SELECT id FROM subscribers WHERE id=' . $candidate . ' LIMIT 1');
    if ($live && $live->num_rows > 0) return 'الرقم التسلسلي مستخدم حالياً لمشترك آخر.';
    $used = @$conn->query('SELECT serial_id FROM subscriber_id_registry WHERE serial_id=' . $candidate . ' LIMIT 1');
    if ($used && $used->num_rows > 0) return 'الرقم التسلسلي سبق استخدامه ولا يمكن إعادة تخصيصه.';
    return '';
  }
}

if (!function_exists('ua_subscriber_change_id_in_transaction')) {
  function ua_subscriber_change_id_in_transaction(mysqli $conn, int $oldId, int $newId, string $source = 'subscriber_edit'): array {
    if ($oldId <= 0 || $newId <= 0) throw new RuntimeException('رقم المشترك غير صالح.');
    if ($oldId === $newId) return ['changed' => false, 'tables' => 0];
    $lockName = $conn->real_escape_string('ua_subscriber_id_change');
    $lockResult = @$conn->query("SELECT GET_LOCK('{$lockName}',10) acquired");
    $lockRow = $lockResult ? $lockResult->fetch_assoc() : null;
    if ((int)($lockRow['acquired'] ?? 0) !== 1) throw new RuntimeException('تعذر قفل سجل الأرقام مؤقتاً. حاول مجدداً.');

    $foreignKeysDisabled = false;
    try {
      $current = @$conn->query('SELECT id FROM subscribers WHERE id=' . $oldId . ' LIMIT 1 FOR UPDATE');
      if (!$current || $current->num_rows < 1) throw new RuntimeException('المشترك المطلوب لم يعد موجوداً.');
      $availability = ua_subscriber_id_availability_error($conn, $oldId, $newId);
      if ($availability !== '') throw new RuntimeException($availability);

      $dbResult = @$conn->query('SELECT DATABASE() db_name');
      $dbRow = $dbResult ? $dbResult->fetch_assoc() : null;
      $dbName = (string)($dbRow['db_name'] ?? '');
      if ($dbName === '') throw new RuntimeException('تعذر تحديد قاعدة البيانات الحالية.');
      $dbEsc = $conn->real_escape_string($dbName);

      $tables = [];
      $columns = @$conn->query("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='{$dbEsc}' AND COLUMN_NAME='subscriber_id'");
      if ($columns) while ($item = $columns->fetch_assoc()) {
        $table = (string)($item['TABLE_NAME'] ?? '');
        if ($table !== '' && $table !== 'subscribers') $tables[$table] = $table;
      }

      if (!@$conn->query('SET FOREIGN_KEY_CHECKS=0')) throw new RuntimeException('تعذر تعليق فحص الروابط أثناء تغيير الرقم.');
      $foreignKeysDisabled = true;
      foreach ($tables as $table) {
        $safeTable = str_replace('`', '``', $table);
        if (!@$conn->query("UPDATE `{$safeTable}` SET subscriber_id={$newId} WHERE subscriber_id={$oldId}")) {
          throw new RuntimeException('تعذر تحديث ارتباط الرقم في الجدول ' . $table . ': ' . $conn->error);
        }
      }

      $serialSet = '';
      if (function_exists('col_exists') && col_exists($conn, 'subscribers', 'mikrotik_serial')) {
        $serial = $conn->real_escape_string('ISP-12200-SUB-' . $newId);
        $serialSet = ",mikrotik_serial='{$serial}'";
      }
      if (!@$conn->query("UPDATE subscribers SET id={$newId}{$serialSet} WHERE id={$oldId} LIMIT 1")) {
        throw new RuntimeException('تعذر تحديث الرقم التسلسلي للمشترك: ' . $conn->error);
      }

      $sourceEsc = $conn->real_escape_string(substr($source, 0, 32));
      $prefixEsc = $conn->real_escape_string(substr((string)$newId, 0, 3));
      if (!@$conn->query("UPDATE subscriber_id_registry SET current_subscriber_id={$newId},retired_at=NOW(),last_used_at=NOW() WHERE serial_id={$oldId}")) {
        throw new RuntimeException('تعذر تثبيت الرقم السابق في السجل التاريخي.');
      }
      if (!@$conn->query("INSERT INTO subscriber_id_registry (serial_id,first_subscriber_id,current_subscriber_id,series_prefix,source,first_seen_at,last_used_at) VALUES ({$newId},{$oldId},{$newId},'{$prefixEsc}','{$sourceEsc}',NOW(),NOW())")) {
        throw new RuntimeException('تعذر تسجيل الرقم الجديد: ' . $conn->error);
      }
      return ['changed' => true, 'tables' => count($tables)];
    } finally {
      if ($foreignKeysDisabled) @$conn->query('SET FOREIGN_KEY_CHECKS=1');
      @$conn->query("SELECT RELEASE_LOCK('{$lockName}')");
    }
  }
}
