<?php
if (!function_exists('sp_col_exists')) {
  function sp_col_exists(mysqli $conn, string $table, string $col): bool {
    if (function_exists('col_exists')) return col_exists($conn, $table, $col);
    $table = $conn->real_escape_string($table);
    $col   = $conn->real_escape_string($col);
    $q = $conn->query("SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='$table' AND COLUMN_NAME='$col' LIMIT 1");
    return $q && $q->num_rows > 0;
  }
}

if (!function_exists('sp_table_exists')) {
  function sp_table_exists(mysqli $conn, string $table): bool {
    if (function_exists('table_exists')) return table_exists($conn, $table);
    $table = $conn->real_escape_string($table);
    $q = $conn->query("SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='$table' LIMIT 1");
    return $q && $q->num_rows > 0;
  }
}

if (!function_exists('subscriber_document_types')) {
  function subscriber_document_types(): array {
    return ['هوية شخصية', 'إخراج قيد', 'جواز سفر'];
  }
}

if (!function_exists('subscriber_document_type_normalize')) {
  function subscriber_document_type_normalize(?string $value, bool $fallbackToDefault = true): string {
    $value = trim((string)$value);
    if ($value === '' && $fallbackToDefault) return 'هوية شخصية';
    return in_array($value, subscriber_document_types(), true) ? $value : '';
  }
}

if (!function_exists('sp_normalize_unit')) {
  function sp_normalize_unit(string $unit): string {
    $u = strtoupper(trim($unit));
    return in_array($u, ['K','M','G'], true) ? $u : 'M';
  }
}

if (!function_exists('sp_trim_num')) {
  function sp_trim_num($value): string {
    if ($value === null || $value === '') return '0';
    $n = (float)$value;
    if (abs($n - round($n)) < 0.00001) return (string)(int)round($n);
    $s = number_format($n, 2, '.', '');
    return rtrim(rtrim($s, '0'), '.');
  }
}

if (!function_exists('sp_format_rate_value')) {
  function sp_format_rate_value($value, string $unit='M'): string {
    return sp_trim_num($value) . sp_normalize_unit($unit);
  }
}

if (!function_exists('sp_format_rate_limit')) {
  function sp_format_rate_limit($upload, $download, string $unit='M'): string {
    return sp_format_rate_value($upload, $unit) . '/' . sp_format_rate_value($download, $unit);
  }
}

if (!function_exists('sp_default_speed_name')) {
  function sp_default_speed_name($upload, $download, string $unit='M'): string {
    $u = sp_format_rate_value($upload, $unit);
    $d = sp_format_rate_value($download, $unit);
    if ((string)$u === (string)$d) return $u;
    return $u . ' / ' . $d;
  }
}

if (!function_exists('sp_default_profile_name')) {
  function sp_default_profile_name($upload, $download, string $unit='M'): string {
    $u = sp_format_rate_value($upload, $unit);
    $d = sp_format_rate_value($download, $unit);
    if ($u === $d) return 'PPPOE-' . $u;
    return 'PPPOE-U' . $u . '-D' . $d;
  }
}

if (!function_exists('sp_parse_rate_text')) {
  function sp_parse_rate_text(string $text): array {
    $text = trim($text);
    if ($text === '') return [null, null, null];

    if (preg_match('/([0-9]+(?:\.[0-9]+)?)\s*([KMG])/iu', $text, $m)) {
      return [(float)$m[1], sp_normalize_unit($m[2]), true];
    }
    if (preg_match('/([0-9]+(?:\.[0-9]+)?)/u', $text, $m)) {
      return [(float)$m[1], 'M', true];
    }
    return [null, null, null];
  }
}

if (!function_exists('subscriber_plan_ensure_schema')) {
  function subscriber_plan_ensure_schema(mysqli $conn): array {
    $notes = [];

    // These reference tables are application-owned.  Older installations and
    // fresh databases did not always receive them, while the page assumed they
    // existed and failed before it could save the first plan.
    @$conn->query("CREATE TABLE IF NOT EXISTS `speeds` (
      `id` INT AUTO_INCREMENT PRIMARY KEY,
      `speed_name` VARCHAR(120) NOT NULL,
      `upload_rate` DECIMAL(10,2) DEFAULT NULL,
      `download_rate` DECIMAL(10,2) DEFAULT NULL,
      `rate_unit` VARCHAR(5) NOT NULL DEFAULT 'M',
      `price` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
      `mikrotik_profile` VARCHAR(100) DEFAULT NULL,
      `rate_limit` VARCHAR(80) DEFAULT NULL,
      `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      UNIQUE KEY `uq_speeds_name` (`speed_name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    @$conn->query("CREATE TABLE IF NOT EXISTS `packages` (
      `id` INT AUTO_INCREMENT PRIMARY KEY,
      `package_name` VARCHAR(160) NOT NULL,
      `speed_id` INT DEFAULT NULL,
      `price` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
      `subscription_fee` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
      `quota_gb` INT DEFAULT NULL,
      `validity_days` INT NOT NULL DEFAULT 30,
      `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      UNIQUE KEY `uq_packages_name` (`package_name`),
      KEY `idx_packages_speed` (`speed_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    @ $conn->query("CREATE TABLE IF NOT EXISTS `payments` (
      `id` INT AUTO_INCREMENT PRIMARY KEY,
      `invoice_id` INT DEFAULT NULL,
      `subscriber_id` INT DEFAULT NULL,
      `amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
      `method` VARCHAR(50) DEFAULT NULL,
      `ref` VARCHAR(120) DEFAULT NULL,
      `reference` VARCHAR(120) DEFAULT NULL,
      `note` VARCHAR(255) DEFAULT NULL,
      `created_by` VARCHAR(100) DEFAULT NULL,
      `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `paid_at` DATETIME DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    if (sp_table_exists($conn, 'speeds')) {
      $speedCols = [
        'upload_rate'      => "ALTER TABLE speeds ADD COLUMN upload_rate DECIMAL(10,2) DEFAULT NULL AFTER speed_name",
        'download_rate'    => "ALTER TABLE speeds ADD COLUMN download_rate DECIMAL(10,2) DEFAULT NULL AFTER upload_rate",
        'rate_unit'        => "ALTER TABLE speeds ADD COLUMN rate_unit VARCHAR(5) NOT NULL DEFAULT 'M' AFTER download_rate",
        'mikrotik_profile' => "ALTER TABLE speeds ADD COLUMN mikrotik_profile VARCHAR(100) DEFAULT NULL AFTER price",
        'rate_limit'       => "ALTER TABLE speeds ADD COLUMN rate_limit VARCHAR(80) DEFAULT NULL AFTER mikrotik_profile",
      ];
      foreach ($speedCols as $col => $sql) {
        if (!sp_col_exists($conn, 'speeds', $col)) { @ $conn->query($sql); }
      }

      $hasUpload = sp_col_exists($conn, 'speeds', 'upload_rate');
      $hasDownload = sp_col_exists($conn, 'speeds', 'download_rate');
      $hasUnit = sp_col_exists($conn, 'speeds', 'rate_unit');
      $hasRateLimit = sp_col_exists($conn, 'speeds', 'rate_limit');
      $hasProfile = sp_col_exists($conn, 'speeds', 'mikrotik_profile');

      $q = $conn->query("SELECT id, speed_name, "
        . ($hasUpload ? "upload_rate," : "NULL AS upload_rate,")
        . ($hasDownload ? "download_rate," : "NULL AS download_rate,")
        . ($hasUnit ? "rate_unit," : "'M' AS rate_unit,")
        . ($hasProfile ? "mikrotik_profile," : "NULL AS mikrotik_profile,")
        . ($hasRateLimit ? "rate_limit" : "NULL AS rate_limit")
        . " FROM speeds");
      if ($q) {
        while ($row = $q->fetch_assoc()) {
          $up = $row['upload_rate'];
          $down = $row['download_rate'];
          $unit = sp_normalize_unit((string)($row['rate_unit'] ?? 'M'));
          $rateLimit = trim((string)($row['rate_limit'] ?? ''));
          $profile = trim((string)($row['mikrotik_profile'] ?? ''));
          $name = trim((string)($row['speed_name'] ?? ''));
          $changed = false;

          if (($up === null || $up === '' || $down === null || $down === '') && $rateLimit !== '') {
            if (preg_match('/([0-9]+(?:\.[0-9]+)?)\s*([KMG])\s*\/\s*([0-9]+(?:\.[0-9]+)?)\s*([KMG])/iu', $rateLimit, $m)) {
              $up = (float)$m[1];
              $down = (float)$m[3];
              $unit = sp_normalize_unit($m[2]);
              $changed = true;
            }
          }

          if (($up === null || $up === '') && ($down === null || $down === '') && $name !== '') {
            [$parsed, $parsedUnit] = sp_parse_rate_text($name);
            if ($parsed !== null) {
              $up = $parsed;
              $down = $parsed;
              $unit = $parsedUnit ?: 'M';
              $changed = true;
            }
          }

          if (($up === null || $up === '') && ($down !== null && $down !== '')) { $up = $down; $changed = true; }
          if (($down === null || $down === '') && ($up !== null && $up !== '')) { $down = $up; $changed = true; }

          if (($up !== null && $up !== '') && ($down !== null && $down !== '')) {
            $computedRate = sp_format_rate_limit($up, $down, $unit);
            if ($rateLimit === '') { $rateLimit = $computedRate; $changed = true; }
            if ($name === '') { $name = sp_default_speed_name($up, $down, $unit); $changed = true; }
            if ($profile === '') { $profile = sp_default_profile_name($up, $down, $unit); $changed = true; }
          }

          if ($changed) {
            $stmt = $conn->prepare("UPDATE speeds SET speed_name=?, upload_rate=?, download_rate=?, rate_unit=?, mikrotik_profile=?, rate_limit=? WHERE id=? LIMIT 1");
            if ($stmt) {
              $id = (int)$row['id'];
              $stmt->bind_param('sddsssi', $name, $up, $down, $unit, $profile, $rateLimit, $id);
              @ $stmt->execute();
              $stmt->close();
            }
          }
        }
      }
    }

    if (sp_table_exists($conn, 'packages')) {
      $packageCols = [
        'quota_gb'         => "ALTER TABLE packages ADD COLUMN quota_gb INT DEFAULT NULL AFTER speed_id",
        'subscription_fee' => "ALTER TABLE packages ADD COLUMN subscription_fee DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER price",
      ];
      foreach ($packageCols as $col => $sql) {
        if (!sp_col_exists($conn, 'packages', $col)) { @ $conn->query($sql); }
      }

      if (sp_col_exists($conn, 'packages', 'quota_gb')) {
        $q = $conn->query("SELECT id, package_name, quota_gb FROM packages");
        if ($q) {
          while ($row = $q->fetch_assoc()) {
            $quota = $row['quota_gb'];
            if (($quota === null || $quota === '') && preg_match('/([0-9]+)\s*(GB|G|جيجا)/iu', (string)$row['package_name'], $m)) {
              $quota = (int)$m[1];
              $stmt = $conn->prepare("UPDATE packages SET quota_gb=? WHERE id=? LIMIT 1");
              if ($stmt) {
                $id = (int)$row['id'];
                $stmt->bind_param('ii', $quota, $id);
                @ $stmt->execute();
                $stmt->close();
              }
            }
          }
        }
      }
    }

    @ $conn->query("CREATE TABLE IF NOT EXISTS subscribers (
      id INT AUTO_INCREMENT PRIMARY KEY,
      username VARCHAR(120) NULL,
      full_name VARCHAR(200) NULL,
      first_name VARCHAR(120) NULL,
      father_name VARCHAR(120) NULL,
      last_name VARCHAR(120) NULL,
      mother_name VARCHAR(120) NULL,
      document_type VARCHAR(40) NOT NULL DEFAULT 'هوية شخصية',
      nationality VARCHAR(120) NOT NULL DEFAULT 'سوري',
      document_number VARCHAR(120) NULL,
      national_id VARCHAR(80) NULL,
      record_number VARCHAR(80) NULL,
      phone VARCHAR(50) NULL,
      address VARCHAR(255) NULL,
      status VARCHAR(50) NULL DEFAULT 'active',
      package_name VARCHAR(160) NULL,
      speed_name VARCHAR(120) NULL,
      speed_state VARCHAR(30) NULL,
      ppp_username VARCHAR(160) NULL,
      ppp_password VARCHAR(160) NULL,
      ppp_profile VARCHAR(120) NULL,
      pppoe_profile VARCHAR(100) NULL,
      ppp_rate_limit VARCHAR(80) NULL,
      ip_address VARCHAR(45) NULL,
      quota_gb DECIMAL(12,2) NULL,
      sector_id INT NULL,
      vlan_id INT NULL,
      provision_status VARCHAR(30) NULL,
      provisioned_at DATETIME NULL,
      is_online TINYINT(1) NOT NULL DEFAULT 0,
      is_deleted TINYINT(1) NOT NULL DEFAULT 0,
      mt_disabled TINYINT(1) NOT NULL DEFAULT 0,
      ppp_is_disabled TINYINT(1) NOT NULL DEFAULT 0,
      last_seen_at DATETIME NULL,
      last_disconnect_at DATETIME NULL,
      registered_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
      created_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
      INDEX (username),
      INDEX (ppp_username),
      INDEX (phone),
      INDEX (is_deleted),
      INDEX (is_online)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    if (sp_table_exists($conn, 'subscribers')) {
      $subscriberCols = [
        'first_name'       => "ALTER TABLE subscribers ADD COLUMN first_name VARCHAR(120) DEFAULT NULL AFTER full_name",
        'last_name'        => "ALTER TABLE subscribers ADD COLUMN last_name VARCHAR(120) DEFAULT NULL AFTER father_name",
        'record_number'    => "ALTER TABLE subscribers ADD COLUMN record_number VARCHAR(80) DEFAULT NULL AFTER national_id",
        'document_type'    => "ALTER TABLE subscribers ADD COLUMN document_type VARCHAR(40) NOT NULL DEFAULT 'هوية شخصية' AFTER birth_date",
        'nationality'      => "ALTER TABLE subscribers ADD COLUMN nationality VARCHAR(120) NOT NULL DEFAULT 'سوري' AFTER document_type",
        'document_number'  => "ALTER TABLE subscribers ADD COLUMN document_number VARCHAR(120) DEFAULT NULL AFTER nationality",
        'sector_id'        => "ALTER TABLE subscribers ADD COLUMN sector_id INT NULL",
        'vlan_id'          => "ALTER TABLE subscribers ADD COLUMN vlan_id INT NULL",
        'provision_status' => "ALTER TABLE subscribers ADD COLUMN provision_status VARCHAR(30) NULL",
        'provisioned_at'   => "ALTER TABLE subscribers ADD COLUMN provisioned_at DATETIME NULL",
        'mt_disabled'      => "ALTER TABLE subscribers ADD COLUMN mt_disabled TINYINT(1) NOT NULL DEFAULT 0",
        'pppoe_profile'    => "ALTER TABLE subscribers ADD COLUMN pppoe_profile VARCHAR(100) NULL",
        'ip_address'       => "ALTER TABLE subscribers ADD COLUMN ip_address VARCHAR(45) NULL",
        'is_online'        => "ALTER TABLE subscribers ADD COLUMN is_online TINYINT(1) NOT NULL DEFAULT 0",
        'is_deleted'       => "ALTER TABLE subscribers ADD COLUMN is_deleted TINYINT(1) NOT NULL DEFAULT 0",
        'ppp_is_disabled'  => "ALTER TABLE subscribers ADD COLUMN ppp_is_disabled TINYINT(1) NOT NULL DEFAULT 0",
        'last_seen_at'     => "ALTER TABLE subscribers ADD COLUMN last_seen_at DATETIME NULL",
        'last_disconnect_at'=> "ALTER TABLE subscribers ADD COLUMN last_disconnect_at DATETIME NULL",
        'registered_at'    => "ALTER TABLE subscribers ADD COLUMN registered_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP",
      ];
      foreach ($subscriberCols as $col => $sql) {
        if (!sp_col_exists($conn, 'subscribers', $col)) { @ $conn->query($sql); }
      }
    }

    return $notes;
  }
}

if (!function_exists('subscriber_identity_nationalities')) {
  function subscriber_identity_nationalities(mysqli $conn): array {
    $values = ['سوري', 'فلسطيني سوري'];
    if (sp_table_exists($conn, 'subscribers') && sp_col_exists($conn, 'subscribers', 'nationality')) {
      $rs = @$conn->query("SELECT DISTINCT TRIM(nationality) value FROM subscribers WHERE nationality IS NOT NULL AND TRIM(nationality)<>'' ORDER BY value LIMIT 100");
      if ($rs) while ($row = $rs->fetch_assoc()) $values[] = trim((string)($row['value'] ?? ''));
    }
    return array_values(array_unique(array_filter($values, static fn($v) => trim((string)$v) !== '')));
  }
}
