<?php
// admin/vouchers_lib.php
// نظام الكروت (Vouchers) — جداول + دوال مساعدة
// ✅ لا يتم تعديل أي جداول موجودة — فقط إضافة جداول جديدة.

require_once __DIR__ . '/helpers.php';
require_once __DIR__ . '/agent_service.php';

function vouchers_ensure_tables(mysqli $conn): void
{
    // voucher_batches
    if (!table_exists($conn, 'voucher_batches')) {
        $conn->query("CREATE TABLE voucher_batches (
            id INT AUTO_INCREMENT PRIMARY KEY,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            created_by INT NULL,
            title VARCHAR(160) NOT NULL DEFAULT '',
            type ENUM('time','data','both') NOT NULL DEFAULT 'time',
            duration_minutes INT NULL,
            data_bytes BIGINT NULL,
            rate_limit VARCHAR(64) NOT NULL DEFAULT '',
            price DECIMAL(10,2) NULL,
            qty INT NOT NULL DEFAULT 0,
            prefix VARCHAR(16) NOT NULL DEFAULT 'V',
            code_len INT NOT NULL DEFAULT 8,
            notes TEXT NULL,
            INDEX(created_at),
            INDEX(created_by)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    }


    // voucher_usage (must exist before any ALTERs on it)
    if (!table_exists($conn, 'voucher_usage')) {
        $conn->query("CREATE TABLE voucher_usage (
            id INT AUTO_INCREMENT PRIMARY KEY,
            voucher_id INT NOT NULL,
            event VARCHAR(40) NOT NULL,
            event_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            meta_json TEXT NULL,
            actor_id INT NULL,
            actor_type VARCHAR(20) NOT NULL DEFAULT 'admin',
            INDEX(voucher_id),
            INDEX(event),
            INDEX(event_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    }

    // actor columns for voucher_usage (admin/agent attribution)
    if (!col_exists($conn, 'voucher_usage', 'actor_id')) {
        @$conn->query("ALTER TABLE voucher_usage ADD COLUMN actor_id INT NULL AFTER meta_json");
    }
    if (!col_exists($conn, 'voucher_usage', 'actor_type')) {
        @$conn->query("ALTER TABLE voucher_usage ADD COLUMN actor_type VARCHAR(20) NOT NULL DEFAULT 'admin' AFTER actor_id");
    }
    
    // vouchers
    if (!table_exists($conn, 'vouchers')) {
        $conn->query("CREATE TABLE vouchers (
            id INT AUTO_INCREMENT PRIMARY KEY,
            batch_id INT NULL,
            code VARCHAR(64) NOT NULL,
            passcode VARCHAR(64) NOT NULL,
            type ENUM('time','data','both') NOT NULL DEFAULT 'time',
            duration_minutes INT NULL,
            data_bytes BIGINT NULL,
            rate_limit VARCHAR(64) NOT NULL DEFAULT '',
            price DECIMAL(10,2) NULL,
            status ENUM('new','sold','used','expired','cancelled') NOT NULL DEFAULT 'new',
            sold_at DATETIME NULL,
            sold_by INT NULL,
            sold_to VARCHAR(160) NULL,
            shop_sale_id INT NULL,
            payment_id INT NULL,
            qr_token VARCHAR(90) NULL,
            qr_redeemed_at DATETIME NULL,
            bound_mac VARCHAR(32) NULL,
            used_at DATETIME NULL,
            used_ip VARCHAR(64) NULL,
            mt_username VARCHAR(64) NULL,
            mt_password VARCHAR(64) NULL,
            expires_at DATETIME NULL,
            last_seen_at DATETIME NULL,
            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uq_code (code),
            INDEX(batch_id),
            INDEX(status),
            INDEX(sold_at),
            INDEX(shop_sale_id),
            INDEX(payment_id),
            INDEX(used_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    }

    // voucher_accounting: تخزين آخر قراءة counters للكرت (للتقارير وفرض الكوتا)
    if (!table_exists($conn, 'voucher_accounting')) {
        $conn->query("CREATE TABLE voucher_accounting (
            id INT AUTO_INCREMENT PRIMARY KEY,
            voucher_id INT NOT NULL,
            mt_username VARCHAR(64) NULL,
            mac VARCHAR(64) NULL,
            ip VARCHAR(64) NULL,
            bytes_in BIGINT NOT NULL DEFAULT 0,
            bytes_out BIGINT NOT NULL DEFAULT 0,
            uptime_sec INT NOT NULL DEFAULT 0,
            last_update_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY uq_voucher (voucher_id),
            INDEX(mt_username),
            INDEX(last_update_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    }

    // ترقيات لاحقة بدون تعارض
    if (function_exists('col_exists')) {

        // ✅ ترقيات دفعات الكروت (voucher_batches)
        if (!col_exists($conn, 'voucher_batches', 'product_id')) {
            @$conn->query("ALTER TABLE voucher_batches ADD COLUMN product_id INT NULL AFTER created_by");
            @$conn->query("ALTER TABLE voucher_batches ADD INDEX (product_id)");
        }
        if (!col_exists($conn, 'voucher_batches', 'exported_to_mikrotik')) {
            @$conn->query("ALTER TABLE voucher_batches ADD COLUMN exported_to_mikrotik TINYINT(1) NOT NULL DEFAULT 0 AFTER notes");
        }
        if (!col_exists($conn, 'voucher_batches', 'exported_at')) {
            @$conn->query("ALTER TABLE voucher_batches ADD COLUMN exported_at DATETIME NULL AFTER exported_to_mikrotik");
        }
        if (!col_exists($conn, 'voucher_batches', 'exported_by')) {
            @$conn->query("ALTER TABLE voucher_batches ADD COLUMN exported_by INT NULL AFTER exported_at");
        }
        if (!col_exists($conn, 'voucher_batches', 'exported_note')) {
            @$conn->query("ALTER TABLE voucher_batches ADD COLUMN exported_note TEXT NULL AFTER exported_by");
        }

        // ✅ ترقيات جدول vouchers (ربط بالصنف + حالة تصدير)
        if (!col_exists($conn, 'vouchers', 'product_id')) {
            @$conn->query("ALTER TABLE vouchers ADD COLUMN product_id INT NULL AFTER batch_id");
            @$conn->query("ALTER TABLE vouchers ADD INDEX (product_id)");
        }
        if (!col_exists($conn, 'vouchers', 'mt_exported')) {
            @$conn->query("ALTER TABLE vouchers ADD COLUMN mt_exported TINYINT(1) NOT NULL DEFAULT 0 AFTER mt_password");
        }
        if (!col_exists($conn, 'vouchers', 'mt_exported_at')) {
            @$conn->query("ALTER TABLE vouchers ADD COLUMN mt_exported_at DATETIME NULL AFTER mt_exported");
        }
        if (!col_exists($conn, 'vouchers', 'mt_export_error')) {
            @$conn->query("ALTER TABLE vouchers ADD COLUMN mt_export_error TEXT NULL AFTER mt_exported_at");
        }
        if (!col_exists($conn, 'vouchers', 'shop_sale_id')) {
            $conn->query("ALTER TABLE vouchers ADD COLUMN shop_sale_id INT NULL AFTER sold_to");
            $conn->query("ALTER TABLE vouchers ADD INDEX (shop_sale_id)");
        }

        if (!col_exists($conn, 'vouchers', 'payment_id')) {
            $conn->query("ALTER TABLE vouchers ADD COLUMN payment_id INT NULL AFTER shop_sale_id");
            $conn->query("ALTER TABLE vouchers ADD INDEX (payment_id)");
        }

        if (!col_exists($conn, 'vouchers', 'qr_token')) {
            @$conn->query("ALTER TABLE vouchers ADD COLUMN qr_token VARCHAR(90) NULL AFTER payment_id");
            @$conn->query("ALTER TABLE vouchers ADD INDEX (qr_token)");
        }
        if (!col_exists($conn, 'vouchers', 'qr_redeemed_at')) {
            @$conn->query("ALTER TABLE vouchers ADD COLUMN qr_redeemed_at DATETIME NULL AFTER qr_token");
        }
        if (!col_exists($conn, 'vouchers', 'bound_mac')) {
            @$conn->query("ALTER TABLE vouchers ADD COLUMN bound_mac VARCHAR(32) NULL AFTER qr_redeemed_at");
        }

        // عدد مرات تبديل الجهاز (MAC reset) — افتراضي 0
        if (!col_exists($conn, 'vouchers', 'device_resets')) {
            @$conn->query("ALTER TABLE vouchers ADD COLUMN device_resets INT NOT NULL DEFAULT 0 AFTER bound_mac");
        }
    }
}

/**
 * Ensure finance 'payments' table exists (lightweight copy of finance_bootstrap migration)
 * We keep this local to vouchers to avoid including entire finance module.
 */
function vouchers_ensure_payments_table(mysqli $conn): void
{
    if (!table_exists($conn, 'payments')) {
        $conn->query("CREATE TABLE IF NOT EXISTS `payments` (
          `id` INT AUTO_INCREMENT PRIMARY KEY,
          `invoice_id` INT NULL,
          `subscriber_id` INT NULL,
          `amount` DECIMAL(10,2) NOT NULL DEFAULT 0,
          `method` VARCHAR(50) NULL,
          `ref` VARCHAR(120) NULL,
          `reference` VARCHAR(120) NULL,
          `note` VARCHAR(255) NULL,
          `created_by` VARCHAR(100) NULL,
          `agent_id` INT NULL,
          `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
          `paid_at` DATETIME NULL,
          INDEX (`invoice_id`), INDEX (`subscriber_id`), INDEX (`created_at`), INDEX (`paid_at`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    } else {
        // best-effort compatibility
        if (function_exists('col_exists')) {
            if (!col_exists($conn, 'payments', 'reference')) {
                @$conn->query("ALTER TABLE `payments` ADD COLUMN `reference` VARCHAR(120) NULL");
            }
            if (!col_exists($conn, 'payments', 'ref')) {
                @$conn->query("ALTER TABLE `payments` ADD COLUMN `ref` VARCHAR(120) NULL");
            }
            if (!col_exists($conn, 'payments', 'agent_id')) {
                @$conn->query("ALTER TABLE `payments` ADD COLUMN `agent_id` INT NULL");
            }
            if (!col_exists($conn, 'payments', 'paid_at')) {
                @$conn->query("ALTER TABLE `payments` ADD COLUMN `paid_at` DATETIME NULL");
            }
        }
        @$conn->query("UPDATE payments SET paid_at = COALESCE(paid_at, created_at) WHERE paid_at IS NULL");
        @$conn->query("UPDATE payments SET reference = COALESCE(reference, ref) WHERE reference IS NULL");
        @$conn->query("UPDATE payments SET ref = COALESCE(ref, reference) WHERE ref IS NULL");
    }
}

/**
 * Create a cash payment record for a shop sale (used by vouchers sales integration).
 * Returns payment_id or 0.
 */
function vouchers_create_cash_payment_for_shop_sale(mysqli $conn, int $sale_id, float $amount, string $voucher_code, string $created_by = '', ?int $agent_id = null): int
{
    vouchers_ensure_payments_table($conn);
    ua_agent_schema_migrate($conn);

    $sale_id = (int)$sale_id;
    if ($sale_id <= 0) return 0;

    $amount = (float)$amount;
    $reference = 'SHOP_SALE#' . $sale_id;
    $ref = $reference;
    $note = 'بيع كرت: ' . $voucher_code;

    // prevent duplicates
    $stmt = $conn->prepare("SELECT id FROM payments WHERE reference=? OR ref=? LIMIT 1");
    if ($stmt) {
        $stmt->bind_param('ss', $reference, $ref);
        $stmt->execute();
        $res = $stmt->get_result();
        if ($res && $res->num_rows) {
            $row = $res->fetch_assoc();
            $stmt->close();
            $existingPaymentId = (int)($row['id'] ?? 0);
            if ($agent_id && $agent_id > 0 && $existingPaymentId > 0) {
                $ledger = ua_agent_service_charge_sale(
                    $conn,
                    (int)$agent_id,
                    $sale_id,
                    $existingPaymentId,
                    $amount,
                    'بيع كرت ' . $voucher_code
                );
                if (!$ledger['ok']) return 0;
            }
            return $existingPaymentId;
        }
        $stmt->close();
    }

    $method = 'cash';
    $created_by = substr($created_by, 0, 100);
    $created_by = substr($created_by, 0, 100);
    $has_agent_col = col_exists($conn, 'payments', 'agent_id');
    if ($has_agent_col) {
        $stmt = $conn->prepare("INSERT INTO payments (invoice_id, subscriber_id, amount, method, ref, reference, note, created_by, agent_id, paid_at) VALUES (NULL,NULL,?,?,?,?,?,?,?,NOW())");
    } else {
        $stmt = $conn->prepare("INSERT INTO payments (invoice_id, subscriber_id, amount, method, ref, reference, note, created_by, paid_at) VALUES (NULL,NULL,?,?,?,?,?,NOW())");
    }
    if (!$stmt) return 0;
    if ($has_agent_col) {
        $stmt->bind_param('dsssssi', $amount, $method, $ref, $reference, $note, $created_by, $agent_id);
    } else {
        $stmt->bind_param('dsssss', $amount, $method, $ref, $reference, $note, $created_by);
    }
    try {
        $conn->begin_transaction();
        if (!@$stmt->execute()) throw new RuntimeException($stmt->error);
        $pid = (int)$stmt->insert_id;
        $stmt->close();
        if ($agent_id && $agent_id > 0) {
            $ledger = ua_agent_service_charge_sale(
                $conn,
                (int)$agent_id,
                $sale_id,
                $pid,
                $amount,
                'بيع كرت ' . $voucher_code,
                ['manage_transaction'=>false]
            );
            if (!$ledger['ok']) throw new RuntimeException($ledger['error']);
            ua_agent_activity_add($conn, [
                'agent_id'=>(int)$agent_id,
                'event_type'=>'VOUCHER_SOLD',
                'direction'=>'TO_MAIN',
                'entity_type'=>'shop_sale',
                'entity_id'=>(string)$sale_id,
                'summary'=>'تم بيع كرت وترحيل قيمته إلى رصيد المعتمد.',
                'payload'=>['payment_id'=>$pid,'voucher_code'=>$voucher_code,'amount'=>$amount],
                'actor_type'=>'agent',
                'actor_id'=>(int)$agent_id,
            ]);
        }
        $conn->commit();
        return $pid;
    } catch (Throwable $e) {
        @$conn->rollback();
        if (isset($stmt) && $stmt instanceof mysqli_stmt) @$stmt->close();
        return 0;
    }
}

function vouchers_generate_code(int $len = 8, string $prefix = 'V'): string
{
    $chars = 'ABCDEFGHJKLMNPQRSTUVWXYZ23456789'; // بدون 0/O و 1/I
    $out = '';
    for ($i = 0; $i < $len; $i++) {
        $out .= $chars[random_int(0, strlen($chars) - 1)];
    }
    $prefix = preg_replace('/[^A-Z0-9]/', '', strtoupper($prefix));
    return ($prefix !== '' ? ($prefix . '-') : '') . $out;
}

function vouchers_generate_pass(int $len = 6): string
{
    $chars = '23456789ABCDEFGHJKLMNPQRSTUVWXYZ';
    $out = '';
    for ($i = 0; $i < $len; $i++) {
        $out .= $chars[random_int(0, strlen($chars) - 1)];
    }
    return $out;
}

function vouchers_generate_token(int $bytes = 18): string
{
    // URL-safe token
    $raw = random_bytes(max(8, $bytes));
    return rtrim(strtr(base64_encode($raw), '+/', '-_'), '=');
}

/**
 * Ensure a voucher has a qr_token (stable identifier for QR printing).
 * Returns the token.
 */
function vouchers_ensure_qr_token(mysqli $conn, int $voucher_id): string
{
    $voucher_id = (int)$voucher_id;
    $q = $conn->prepare("SELECT qr_token FROM vouchers WHERE id=? LIMIT 1");
    $q->bind_param('i', $voucher_id);
    $q->execute();
    $res = $q->get_result();
    $row = $res ? $res->fetch_assoc() : null;
    $q->close();
    if ($row && !empty($row['qr_token'])) return (string)$row['qr_token'];

    $token = vouchers_generate_token();
    $u = $conn->prepare("UPDATE vouchers SET qr_token=? WHERE id=? AND (qr_token IS NULL OR qr_token='')");
    $u->bind_param('si', $token, $voucher_id);
    @$u->execute();
    @$u->close();

    return $token;
}

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

function vouchers_minutes_from_days(float $days): int
{
    if ($days <= 0) return 0;
    return (int) round($days * 24 * 60);
}

function vouchers_log(mysqli $conn, int $voucher_id, string $event, array $meta = [], ?int $actor_id = null, string $actor_type = 'admin'): void
{
    $event = substr($event, 0, 40);
    $meta_json = $meta ? json_encode($meta, JSON_UNESCAPED_UNICODE) : null;
    $stmt = $conn->prepare("INSERT INTO voucher_usage (voucher_id, event, meta_json, actor_id, actor_type) VALUES (?,?,?,?,?)");
    $stmt->bind_param('issis', $voucher_id, $event, $meta_json, $actor_id, $actor_type);
    @$stmt->execute();
    @$stmt->close();
}

function vouchers_fetch_by_code(mysqli $conn, string $code): ?array
{
    $stmt = $conn->prepare("SELECT * FROM vouchers WHERE code=? LIMIT 1");
    $stmt->bind_param('s', $code);
    $stmt->execute();
    $res = $stmt->get_result();
    $row = $res ? $res->fetch_assoc() : null;
    $stmt->close();
    return $row ?: null;
}


function vouchers_log_many(mysqli $conn, array $voucher_ids, string $event, array $meta = [], ?int $actor_id = null, string $actor_type='admin'): void {
    foreach ($voucher_ids as $vid) {
        vouchers_log($conn, (int)$vid, $event, $meta, $actor_id, $actor_type);
    }
}

/**
 * Resolve MikroTik Hotspot profile for a voucher based on its product.
 * - If shop_products.voucher_mt_profile is set for product_id => use it
 * - Else fallback to global default profile
 *
 * Uses a small in-request cache to avoid repeated DB hits.
 */
function vouchers_resolve_mt_profile(mysqli $conn, ?int $product_id, string $default_profile = 'voucher'): string
{
    static $cache = []; // product_id => profile

    $default_profile = trim((string)$default_profile);
    if ($default_profile === '') $default_profile = 'voucher';

    $pid = (int)($product_id ?? 0);
    if ($pid <= 0) return $default_profile;

    if (array_key_exists($pid, $cache)) {
        $p = trim((string)$cache[$pid]);
        return $p !== '' ? $p : $default_profile;
    }

    // Best-effort read (avoid hard dependency on shop_lib)
    $profile = '';
    $stmt = @$conn->prepare("SELECT voucher_mt_profile FROM shop_products WHERE id=? LIMIT 1");
    if ($stmt) {
        $stmt->bind_param('i', $pid);
        if (@$stmt->execute()) {
            $res = $stmt->get_result();
            if ($res && ($row = $res->fetch_assoc())) {
                $profile = trim((string)($row['voucher_mt_profile'] ?? ''));
            }
        }
        @$stmt->close();
    }

    $cache[$pid] = $profile;
    return $profile !== '' ? $profile : $default_profile;
}


/**
 * Build RouterOS on-login script for vouchers (wallclock validity + optional single-device lock).
 * Used by vouchers export to ensure profile has correct on-login behavior.
 */
function vouchers_mt_build_on_login_script(bool $wallclock_enabled, bool $single_device, bool $device_lock_enabled, string $steps_csv): string {
  if (!$wallclock_enabled && !$single_device) return '';

  $steps = array_values(array_filter(array_map('trim', explode(',', $steps_csv)), fn($x)=>$x!==''));
  $mins = [];
  foreach ($steps as $s) {
    if (preg_match('/^\d+$/', $s)) {
      $n = (int)$s;
      if ($n > 0 && $n <= 240) $mins[] = (string)$n;
    }
  }
  if (!$mins) $mins = ['1','5','10','30'];
  $arr = '{"' . implode('";"', $mins) . '"}';

  $script = ':local u $user; :local m $mac; :local id [/ip hotspot user find name=$u];'
    . ' :if ([:len $id] = 0) do={ :return; }'
    . ' :local c [/ip hotspot user get $id comment];';

  if ($wallclock_enabled) {
    $script .= ' :local pd [:find $c "durm="];'
      . ' :if ($pd != nil) do={'
      . '   :local started ([:find $c "start=1"] != nil);'
      . '   :if (!$started) do={'
      . '     :local rest [:pick $c ($pd+5) [:len $c]]; :local sp [:find $rest " "]; :if ($sp != nil) do={ :set rest [:pick $rest 0 $sp]; }'
      . '     :local dur [:tonum $rest];'
      . '     :if ($dur > 0) do={'
      . '       /ip hotspot user set $id comment=($c . " start=1 rem=" . $dur);'
      . '       :local s ("vexp-" . $u);'
      . '       /system scheduler remove [find name=$s];'
      . '       /system scheduler add name=$s interval=1m on-event=('
      . '         ":local u \\"".$u."\\"; :local s \\"vexp-".$u."\\"; :local id [/ip hotspot user find name=$u];'
      . '          :if ([:len $id]=0) do={ /system scheduler remove [find name=$s]; :return; };'
      . '          :local c [/ip hotspot user get $id comment]; :local pr [:find $c \\"rem=\\"];'
      . '          :if ($pr = nil) do={ /system scheduler remove [find name=$s]; :return; };'
      . '          :local r [:pick $c ($pr+4) [:len $c]]; :local sp [:find $r \\" \\\"]; :if ($sp != nil) do={ :set r [:pick $r 0 $sp]; };'
      . '          :local rem [:tonum $r]; :set rem ($rem-1);'
      . '          :if ($rem <= 0) do={ /ip hotspot active remove [find user=$u]; /ip hotspot cookie remove [find user=$u]; /ip hotspot user disable $id; /system scheduler remove [find name=$s]; }'
      . '          else={'
      . '            :local pre [:pick $c 0 ($pr+4)]; :local rest2 [:pick $c ($pr+4) [:len $c]]; :local sp2 [:find $rest2 \\" \\\"]; :local suf \\"\\"; :if ($sp2 != nil) do={ :set suf [:pick $rest2 $sp2 [:len $rest2]]; };'
      . '            /ip hotspot user set $id comment=($pre . $rem . $suf);'
      . '          }"'
      . '       );'
      . '     }'
      . '   }'
      . ' };';
  }

  if ($single_device) {
    $script .= ' :local cur [/ip hotspot user get $id mac-address];'
      . ' :if ([:len $cur] = 0) do={ /ip hotspot user set $id mac-address=$m; }';
    if ($device_lock_enabled) {
      $script .= ' else={ :if ($cur != $m) do={'
        . ' :local c [/ip hotspot user get $id comment]; :local v 0;'
        . ' :local p [:find $c "viol="];'
        . ' :if ($p != nil) do={ :local rest [:pick $c ($p+5) [:len $c]]; :local sp [:find $rest " "]; :if ($sp != nil) do={ :set rest [:pick $rest 0 $sp]; } :set v [:tonum $rest]; }'
        . ' :set v ($v+1);'
        . ' :local pre $c;'
        . ' :if ($p != nil) do={ :set pre [:pick $c 0 $p]; :local rest2 [:pick $c ($p+5) [:len $c]]; :local sp2 [:find $rest2 " "]; :local suf ""; :if ($sp2 != nil) do={ :set suf [:pick $rest2 $sp2 [:len $rest2]]; } :set c ($pre . "viol=" . $v . $suf); } else={ :set c ($c . " viol=" . $v); }'
        . ' /ip hotspot user set $id comment=$c;'
        . ' :local steps ' . $arr . ';'
        . ' :local idx ($v-1); :if ($idx < 0) do={ :set idx 0; } :if ($idx >= [:len $steps]) do={ :set idx ([:len $steps]-1); }'
        . ' :local mins ($steps->$idx); :local mm $mins; :if ([:len $mm] = 1) do={ :set mm ("0".$mm); }'
        . ' :local interval ("00:".$mm.":00"); :local s ("unlock-".$u);'
        . ' /system scheduler remove [find name=$s];'
        . ' /ip hotspot active remove [find user=$u]; /ip hotspot cookie remove [find user=$u];'
        . ' /ip hotspot user disable $id;'
        . ' /system scheduler add name=$s interval=$interval on-event=("/ip hotspot user enable [find name=\\\"".$u."\\\"]; /system scheduler remove [find name=\\\"".$s."\\\"]; ");'
        . ' } };';
    }
    $script .= ' };';
  }

  return $script;
}

function vouchers_mt_ensure_profile($api, string $profile_name, string $rate_limit, bool $wallclock_enabled, bool $single_device, bool $device_lock_enabled, string $steps_csv): void {
  $profile_name = trim($profile_name);
  if ($profile_name === '') $profile_name = 'voucher';
  $onLogin = vouchers_mt_build_on_login_script($wallclock_enabled, $single_device, $device_lock_enabled, $steps_csv);

  $exists = $api->command(['/ip/hotspot/user/profile/print', '?name=' . $profile_name]);
  $found_id = null;
  foreach ($exists as $r) { if (($r['.type'] ?? '') === '!re' && isset($r['.id'])) { $found_id = $r['.id']; break; } }

  if ($found_id) {
    $cmd = ['/ip/hotspot/user/profile/set', '=.id=' . $found_id];
    if ($rate_limit !== '') $cmd[] = '=rate-limit=' . $rate_limit;
    if ($single_device) $cmd[] = '=shared-users=1';
    if ($onLogin !== '') $cmd[] = '=on-login=' . $onLogin;
    $api->command($cmd);
    return;
  }

  $cmd = ['/ip/hotspot/user/profile/add', '=name=' . $profile_name];
  if ($rate_limit !== '') $cmd[] = '=rate-limit=' . $rate_limit;
  if ($single_device) $cmd[] = '=shared-users=1';
  if ($onLogin !== '') $cmd[] = '=on-login=' . $onLogin;
  $api->command($cmd);
}

/**
 * تصدير دفعة كروت إلى MikroTik مرة واحدة فقط (لمنع التكرار).
 * - يعلّم الدفعة exported_to_mikrotik=1 عند النجاح
 * - يعلّم كل كرت mt_exported=1 عند نجاح إنشائه
 * @return array [ok=>bool, exported=>int, skipped=>int, error=>string|null]
 */
function vouchers_export_batch_to_mikrotik(mysqli $conn, int $batch_id, int $actor_id=0): array {
    $batch_id = (int)$batch_id;
    vouchers_ensure_tables($conn);
    if ($batch_id <= 0) return ['ok'=>false,'exported'=>0,'skipped'=>0,'error'=>'batch_id غير صالح'];

    $bq = $conn->query("SELECT * FROM voucher_batches WHERE id=$batch_id LIMIT 1");
    if (!$bq || !$bq->num_rows) return ['ok'=>false,'exported'=>0,'skipped'=>0,'error'=>'الدفعة غير موجودة'];
    $batch = $bq->fetch_assoc();

    if (!empty($batch['exported_to_mikrotik'])) {
        return ['ok'=>true,'exported'=>0,'skipped'=>0,'error'=>null,'already_exported'=>true];
    }

    require_once __DIR__ . '/mikrotik_lib.php';

    $default_profile = trim((string)get_app_setting($conn, 'voucher_hotspot_profile', 'voucher'));
    $wallclock_enabled = (get_app_setting($conn, 'voucher_time_wallclock', '1') === '1');
    $single_device = (get_app_setting($conn, 'voucher_single_device', '1') === '1');
    $device_lock_enabled = (get_app_setting($conn, 'voucher_device_lock_enabled', '1') === '1');
    $device_lock_steps = (string)get_app_setting($conn, 'voucher_device_lock_steps', '1,5,10,30');
    if ($default_profile==='') $default_profile='voucher';

    $api = null;
    $exported = 0; $skipped = 0;
    $errors = [];
    $ensured_profiles = []; // profile_name => true
    try {
        $api = mt_api_connect($conn);

        // جلب كروت الدفعة غير المصدّرة
        $rows = [];
        $q = $conn->query("SELECT * FROM vouchers WHERE batch_id=$batch_id ORDER BY id ASC");
        if ($q) $rows = $q->fetch_all(MYSQLI_ASSOC);

        foreach ($rows as $v) {
            $vid = (int)$v['id'];
            $username = (string)($v['mt_username'] ?: $v['code']);
            $password = (string)($v['mt_password'] ?: $v['passcode']);

            // إن كان موجوداً على الميكروتك نعتبره skip ونعلّمه exported (لتفادي الإعادة)
            $exists = $api->command(['/ip/hotspot/user/print', '?name=' . $username]);
            $found = false;
            foreach ($exists as $r) { if (($r['.type'] ?? '') === '!re') { $found = true; break; } }

            $rate = trim((string)($v['rate_limit'] ?? ''));
            $limit_uptime = null;
            if (!empty($v['duration_minutes'])) {
                $mins = (int)$v['duration_minutes'];
                $days = intdiv($mins, 1440); $mins -= $days*1440;
                $hours = intdiv($mins, 60); $mins -= $hours*60;
                $parts = [];
                if ($days>0) $parts[] = $days . 'd';
                if ($hours>0) $parts[] = $hours . 'h';
                if ($mins>0) $parts[] = $mins . 'm';
                $limit_uptime = $parts ? implode('', $parts) : null;
            }
            if ($wallclock_enabled) { $limit_uptime = null; }
            $limit_bytes = null;
            if (!empty($v['data_bytes'])) $limit_bytes = (string)((int)$v['data_bytes']);

            // Resolve profile per product (if configured) else fallback
            $profile = vouchers_resolve_mt_profile($conn, (int)($v['product_id'] ?? 0), $default_profile);

            // Ensure profile exists on MikroTik (create/update once per profile)
            if ($profile !== '' && empty($ensured_profiles[$profile])) {
                vouchers_mt_ensure_profile($api, $profile, $rate, $wallclock_enabled, $single_device, $device_lock_enabled, $device_lock_steps);
                // even if ensure fails (offline), we continue to attempt add; MikroTik will error and we'll log it below
                $ensured_profiles[$profile] = true;
            }

            if (!$found) {
                $cmd = ['/ip/hotspot/user/add', '=name=' . $username, '=password=' . $password, '=profile=' . $profile, '=comment=voucher:' . $v['code'] . ($wallclock_enabled && !empty($v['duration_minutes']) ? ' durm='.(int)$v['duration_minutes'] : '')];
                if ($rate !== '') $cmd[] = '=rate-limit=' . $rate;
                if ($limit_uptime) $cmd[] = '=limit-uptime=' . $limit_uptime;
                if ($limit_bytes) $cmd[] = '=limit-bytes-total=' . $limit_bytes;
                $api->command($cmd);
                $exported++;
            } else {
                $skipped++;
            }

            // حفظ الاعتماديات محليًا + علامة تصدير
            $st = $conn->prepare("UPDATE vouchers SET mt_username=?, mt_password=?, mt_exported=1, mt_exported_at=NOW(), mt_export_error=NULL WHERE id=?");
            $st->bind_param('ssi', $username, $password, $vid);
            $st->execute();
            $st->close();

            vouchers_log($conn, $vid, 'export_to_mikrotik', ['batch_id'=>$batch_id,'profile'=>$profile,'default_profile'=>$default_profile,'found'=>$found], $actor_id, 'admin');
        }

        // علّم الدفعة
        $note = ($errors ? implode(' | ', $errors) : null);
        $stmt = $conn->prepare("UPDATE voucher_batches SET exported_to_mikrotik=1, exported_at=NOW(), exported_by=?, exported_note=? WHERE id=?");
        $stmt->bind_param('isi', $actor_id, $note, $batch_id);
        $stmt->execute();
        $stmt->close();

        return ['ok'=>true,'exported'=>$exported,'skipped'=>$skipped,'error'=>null];
    } catch (Throwable $e) {
        // سجل سبب الفشل
        $msg = $e->getMessage();
        @$conn->query("UPDATE voucher_batches SET exported_note=" . ("'".$conn->real_escape_string($msg)."'") . " WHERE id=$batch_id");
        return ['ok'=>false,'exported'=>$exported,'skipped'=>$skipped,'error'=>$msg];
    } finally {
        if ($api && method_exists($api,'disconnect')) { try { $api->disconnect(); } catch(Throwable $e){} }
    }
}
