<?php
// admin/quota_lib.php
// إدارة حجم الباقات الشهرية + الإضافات + إنفاذ LIMIT/RESTORE عبر PPPoE username.

require_once __DIR__ . '/helpers.php';

// NOTE:
// quota_runner.php contains the full enforcement flow.
// We expose a lightweight sync function here so pages (like subscribers.php)
// can update monthly usage counters without applying LIMIT/RESTORE actions.
// This is especially important on RouterOS versions where /ppp active doesn't show rx/tx,
// and we must read counters from dynamic interfaces: <pppoe-USER>.

function q_table_exists(mysqli $conn, string $table): bool {
  $t = $conn->real_escape_string($table);
  $rs = $conn->query("SHOW TABLES LIKE '$t'");
  return ($rs && $rs->num_rows > 0);
}

function q_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 ($rs && $rs->num_rows > 0);
}

function q_ensure_tables(mysqli $conn): void {
  $conn->query("CREATE TABLE IF NOT EXISTS quota_addons (
    id INT AUTO_INCREMENT PRIMARY KEY,
    subscriber_id INT NOT NULL,
    username VARCHAR(100) NOT NULL,
    cycle_month VARCHAR(7) NOT NULL,
    quota_bytes BIGINT NOT NULL DEFAULT 0,
    invoice_id INT NULL,
    created_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_q_addons_user_month (username, cycle_month),
    KEY idx_q_addons_sub_month (subscriber_id, cycle_month)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");

  $conn->query("CREATE TABLE IF NOT EXISTS ppp_counters (
    username VARCHAR(100) PRIMARY KEY,
    last_rx BIGINT NOT NULL DEFAULT 0,
    last_tx BIGINT NOT NULL DEFAULT 0,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");

  // قد يكون ppp_usage_monthly موجود بسكيمة قديمة، لذلك لا نفرض ALTER هنا
  if (!q_table_exists($conn,'ppp_usage_monthly')) {
    $conn->query("CREATE TABLE IF NOT EXISTS ppp_usage_monthly (
      username VARCHAR(100) NOT NULL,
      cycle_month VARCHAR(7) NOT NULL,
      used_bytes BIGINT NOT NULL DEFAULT 0,
      updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (username, cycle_month)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
  }

  $conn->query("CREATE TABLE IF NOT EXISTS quota_state (
    username VARCHAR(100) NOT NULL,
    cycle_month VARCHAR(7) NOT NULL,
    is_limited TINYINT(1) NOT NULL DEFAULT 0,
    last_action VARCHAR(20) NULL,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (username, cycle_month)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
}

function q_cycle_month(?DateTime $dt=null): string {
  $dt = $dt ?: new DateTime('now');
  return $dt->format('Y-m');
}

function q_gb_to_bytes(int $gb): int {
  if($gb <= 0) return 0;
  return (int)($gb * 1024 * 1024 * 1024);
}

function q_sum_addons_bytes(mysqli $conn, int $subscriber_id, string $username, string $cycle_month): int {
  $sid = (int)$subscriber_id;
  $u = $conn->real_escape_string($username);
  $m = $conn->real_escape_string($cycle_month);
  $rs = $conn->query("SELECT COALESCE(SUM(quota_bytes),0) AS s FROM quota_addons WHERE (subscriber_id=$sid OR username='$u') AND cycle_month='$m'");
  $row = $rs ? $rs->fetch_assoc() : null;
  return (int)($row['s'] ?? 0);
}

/**
 * قراءة الاستهلاك الشهري بشكل مرن:
 * - سكيمة جديدة: ppp_usage_monthly(username, cycle_month, used_bytes)
 * - سكيمة قديمة: ppp_usage_monthly(subscriber_id, month_key, used_bytes)
 */
function q_get_monthly_used_bytes(mysqli $conn, string $username, string $cycle_month, int $subscriber_id = 0): int {
  if (!q_table_exists($conn,'ppp_usage_monthly')) return 0;
  $m = $conn->real_escape_string($cycle_month);
  $values = [];

  // new schema: username + cycle_month
  if (q_col_exists($conn,'ppp_usage_monthly','username') && q_col_exists($conn,'ppp_usage_monthly','cycle_month')) {
    $uRaw = trim($username);
    $uNorm = preg_replace('/@.*/', '', $uRaw);
    $conds = [];
    foreach (array_unique(array_filter([$uRaw, $uNorm], static fn($v) => $v !== '')) as $candidate) {
      $ce = $conn->real_escape_string($candidate);
      $conds[] = "username='$ce'";
      if (strpos($candidate, '@') === false) {
        $conds[] = "username LIKE '" . $ce . "@%'";
      }
    }
    if (!empty($conds)) {
      $sql = "SELECT COALESCE(SUM(used_bytes),0) AS used_bytes FROM ppp_usage_monthly WHERE cycle_month='$m' AND (" . implode(' OR ', $conds) . ")";
      $rs = @$conn->query($sql);
      if($rs && $rs->num_rows){
        $row = $rs->fetch_assoc();
        $values[] = (int)($row['used_bytes'] ?? 0);
      }
    }
  }

  // old schema: subscriber_id + month_key
  if (q_col_exists($conn,'ppp_usage_monthly','subscriber_id') && q_col_exists($conn,'ppp_usage_monthly','month_key')) {
    $sid = (int)$subscriber_id;
    if ($sid > 0) {
      $rs = @$conn->query("SELECT COALESCE(SUM(used_bytes),0) AS used_bytes FROM ppp_usage_monthly WHERE subscriber_id=$sid AND month_key='$m'");
      if($rs && $rs->num_rows){
        $row = $rs->fetch_assoc();
        $values[] = (int)($row['used_bytes'] ?? 0);
      }
    }
  }

  // عند وجود سكيمتين/مصدرين قديم وجديد لا نجمعهم حتى لا يتضاعف الاستهلاك؛ نأخذ الأكبر كقراءة آمنة.
  return empty($values) ? 0 : max($values);
}

function q_add_addon(mysqli $conn, int $subscriber_id, string $username, string $cycle_month, int $gb, ?int $invoice_id=null): void {
  q_ensure_tables($conn);
  $bytes = q_gb_to_bytes($gb);
  if($bytes <= 0) return;
  $sid = (int)$subscriber_id;
  $u = $conn->real_escape_string($username);
  $m = $conn->real_escape_string($cycle_month);
  $inv = $invoice_id ? (int)$invoice_id : 'NULL';
  if ($invoice_id) {
    $exists = @$conn->query("SELECT id FROM quota_addons WHERE invoice_id=".(int)$invoice_id." LIMIT 1");
    if ($exists && $exists->num_rows) return;
  }
  $conn->query("INSERT INTO quota_addons (subscriber_id, username, cycle_month, quota_bytes, invoice_id) VALUES ($sid, '$u', '$m', $bytes, $inv)");
}

/**
 * ✅ Lightweight monthly usage sync (delta) from live PPP counters.
 * Safe to call frequently; it only updates counters tables.
 * Returns number of users updated (delta > 0).
 */
if (!function_exists('q_sync_monthly_usage_from_active_rows')) {
  function q_sync_monthly_usage_from_active_rows(mysqli $conn, string $cycle_month, array $rows): int {
    q_ensure_tables($conn);

    if (!is_array($rows) || empty($rows)) return 0;

    $updated = 0;
    $me = $conn->real_escape_string($cycle_month);

    // Detect ppp_usage_monthly schema:
    // new: (username, cycle_month)
    // old: (subscriber_id, month_key)
    $hasNew = false;
    $hasOld = false;
    try {
      $cU = $conn->query("SHOW COLUMNS FROM `ppp_usage_monthly` LIKE 'username'");
      $cM = $conn->query("SHOW COLUMNS FROM `ppp_usage_monthly` LIKE 'cycle_month'");
      $hasNew = ($cU && $cU->num_rows) && ($cM && $cM->num_rows);
    } catch (Throwable $e) { $hasNew = false; }

    try {
      $cS = $conn->query("SHOW COLUMNS FROM `ppp_usage_monthly` LIKE 'subscriber_id'");
      $cK = $conn->query("SHOW COLUMNS FROM `ppp_usage_monthly` LIKE 'month_key'");
      $hasOld = ($cS && $cS->num_rows) && ($cK && $cK->num_rows);
    } catch (Throwable $e) { $hasOld = false; }

    // Map username -> subscriber_id for old schema
    $userToSid = [];
    if ($hasOld) {
      try {
        $rs = $conn->query("SELECT id, COALESCE(NULLIF(ppp_username,''), NULLIF(username,'')) AS u FROM subscribers");
        if ($rs) {
          while ($r = $rs->fetch_assoc()) {
            $sid = (int)($r['id'] ?? 0);
            $u = trim((string)($r['u'] ?? ''));
            if ($sid <= 0 || $u === '') continue;
            $u = preg_replace('/@.*/', '', $u);
            $userToSid[$u] = $sid;
          }
          $rs->free();
        }
      } catch (Throwable $e) { /* ignore */ }
    }

    foreach ($rows as $r) {
      if (!is_array($r)) continue;
      $u = trim((string)($r['name'] ?? ''));
      if ($u === '') continue;

      // normalize realm
      $u = preg_replace('/@.*/', '', $u);

      $rx = (int)($r['rx_bytes'] ?? 0);
      $tx = (int)($r['tx_bytes'] ?? 0);
      if ($rx < 0) $rx = 0;
      if ($tx < 0) $tx = 0;

      $ue = $conn->real_escape_string($u);

      // last counters
      $lastRx = 0; $lastTx = 0;
      $rs = $conn->query("SELECT last_rx,last_tx FROM ppp_counters WHERE username='$ue' LIMIT 1");
      if ($rs && $rs->num_rows) {
        $row = $rs->fetch_assoc();
        $lastRx = (int)($row['last_rx'] ?? 0);
        $lastTx = (int)($row['last_tx'] ?? 0);
      }

      // counter reset (reconnect/reboot)
      $dRx = ($rx >= $lastRx) ? ($rx - $lastRx) : $rx;
      $dTx = ($tx >= $lastTx) ? ($tx - $lastTx) : $tx;
      $delta = $dRx + $dTx;

      // store last
      $conn->query("INSERT INTO ppp_counters (username,last_rx,last_tx) VALUES ('$ue',$rx,$tx)
                   ON DUPLICATE KEY UPDATE last_rx=$rx,last_tx=$tx");

      if ($delta <= 0) continue;

      $wrote = false;

      if ($hasOld) {
        $sid = (int)($userToSid[$u] ?? 0);
        if ($sid > 0) {
          $ok = @$conn->query("INSERT INTO ppp_usage_monthly (subscriber_id,month_key,used_bytes,updated_at)
                       VALUES ($sid,'$me',$delta,NOW())
                       ON DUPLICATE KEY UPDATE used_bytes=used_bytes+$delta, updated_at=NOW()");
          if ($ok) $wrote = true;
        }
      }

      if ($hasNew) {
        $ok = @$conn->query("INSERT INTO ppp_usage_monthly (username,cycle_month,used_bytes) VALUES ('$ue','$me',$delta)
                     ON DUPLICATE KEY UPDATE used_bytes=used_bytes+$delta");
        if ($ok) $wrote = true;
      }

      if ($wrote) $updated++;
    }

    return $updated;
  }
}

if (!function_exists('q_sync_monthly_usage')) {
  function q_sync_monthly_usage(mysqli $conn, string $cycle_month): int {
    if (!function_exists('mt_ppp_active')) {
      @require_once __DIR__ . '/mikrotik_lib.php';
    }
    if (!function_exists('mt_ppp_active')) return 0;

    $rows = mt_ppp_active($conn);
    return q_sync_monthly_usage_from_active_rows($conn, $cycle_month, is_array($rows) ? $rows : []);
  }
}
