<?php
if (session_status() === PHP_SESSION_NONE) { @session_start(); }
require_once __DIR__ . '/helpers.php';
require_once __DIR__ . '/lang.php';
require_once __DIR__ . '/finance_bootstrap.php';
require_once __DIR__ . '/mikrotik_lib.php';
require_once __DIR__ . '/billing_account_lib.php';
if (!function_exists('billing_cycle_migrate')) {
  function billing_cycle_migrate(mysqli $conn): void {
    if (!table_exists($conn, 'app_settings')) return;
    foreach ([
      'billing_cycle_day' => '1',
      'billing_generation_lead_days' => '3',
      'billing_due_after_days' => '0',
      'billing_reminder_offsets' => '0',
      'billing_reminders_enabled' => '1',
      'grace_days' => get_app_setting($conn, 'grace_days', '0'),
      'warn_after_days' => get_app_setting($conn, 'warn_after_days', '0'),
      'limit_after_days' => get_app_setting($conn, 'limit_after_days', '0'),
      'cut_after_days' => get_app_setting($conn, 'cut_after_days', '0'),
      'billing_limit_enabled' => (
        (int)get_app_setting($conn, 'limit_after_days', '0') > 0
        && get_app_setting($conn, 'billing_skip_limit_stage', '0') !== '1'
      ) ? '1' : '0',
      'billing_cut_stage_enabled' => ((int)get_app_setting($conn, 'cut_after_days', '0') > 0 ? '1' : '0'),
      'billing_skip_limit_stage' => '0',
      'automation_invoices_enabled' => get_app_setting($conn, 'automation_invoices_enabled', '1'),
      'automation_cut_enabled' => get_app_setting($conn, 'automation_cut_enabled', '1'),
      'billing_protected_mode' => get_app_setting($conn, 'billing_protected_mode', '1'),
      'billing_request_retention_days' => get_app_setting($conn, 'billing_request_retention_days', '3'),
    ] as $k => $v) {
      if (get_app_setting($conn, $k, '') === '') set_app_setting($conn, $k, (string)$v);
    }
    @ $conn->query("CREATE TABLE IF NOT EXISTS `billing_action_requests` (
      `id` INT AUTO_INCREMENT PRIMARY KEY,
      `invoice_id` INT NOT NULL,
      `subscriber_id` INT NOT NULL,
      `action_type` VARCHAR(20) NOT NULL,
      `status` VARCHAR(20) NOT NULL DEFAULT 'pending',
      `cycle_label` VARCHAR(80) NULL,
      `effective_days` INT NOT NULL DEFAULT 0,
      `reason` VARCHAR(255) NULL,
      `meta_json` LONGTEXT NULL,
      `request_key` VARCHAR(191) NULL,
      `approved_by` INT NULL,
      `approved_at` DATETIME NULL,
      `executed_at` DATETIME NULL,
      `resolved_at` DATETIME NULL,
      `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      KEY `idx_action_status` (`action_type`,`status`),
      KEY `idx_invoice_status` (`invoice_id`,`status`),
      KEY `idx_subscriber_status` (`subscriber_id`,`status`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    if (!col_exists($conn, 'billing_action_requests', 'request_key')) {
      @ $conn->query("ALTER TABLE `billing_action_requests` ADD COLUMN `request_key` VARCHAR(191) NULL");
    }
    try { @ $conn->query("CREATE UNIQUE INDEX `uq_billing_request_key` ON `billing_action_requests` (`request_key`)"); } catch (Throwable $e) {}
    @ $conn->query("CREATE TABLE IF NOT EXISTS `billing_exceptional_cycles` (
      `id` INT AUTO_INCREMENT PRIMARY KEY,
      `label` VARCHAR(120) NOT NULL,
      `request_token` VARCHAR(64) NOT NULL,
      `status` VARCHAR(20) NOT NULL DEFAULT 'creating',
      `start_date` DATE NOT NULL,
      `due_date` DATE NOT NULL,
      `grace_days` INT NOT NULL DEFAULT 0,
      `limit_after_days` INT NOT NULL DEFAULT 0,
      `cut_after_days` INT NOT NULL DEFAULT 0,
      `reminder_offsets` VARCHAR(255) NULL,
      `reminders_enabled` TINYINT(1) NOT NULL DEFAULT 1,
      `limit_enabled` TINYINT(1) NOT NULL DEFAULT 0,
      `cut_enabled` TINYINT(1) NOT NULL DEFAULT 0,
      `subscriber_count` INT NOT NULL DEFAULT 0,
      `created_invoices` INT NOT NULL DEFAULT 0,
      `covered_by_credit` INT NOT NULL DEFAULT 0,
      `pricing_missing` INT NOT NULL DEFAULT 0,
      `skipped` INT NOT NULL DEFAULT 0,
      `created_by` INT NULL,
      `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `completed_at` DATETIME NULL,
      UNIQUE KEY `uq_exceptional_cycle_token` (`request_token`),
      KEY `idx_exceptional_cycle_created` (`created_at`),
      KEY `idx_exceptional_cycle_status` (`status`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    if (table_exists($conn, 'invoices')) {
      $invoiceColumns = [
        'cycle_label' => "ALTER TABLE `invoices` ADD COLUMN `cycle_label` VARCHAR(80) NULL",
        'cycle_start_date' => "ALTER TABLE `invoices` ADD COLUMN `cycle_start_date` DATE NULL",
        'cycle_end_date' => "ALTER TABLE `invoices` ADD COLUMN `cycle_end_date` DATE NULL",
        'due_date' => "ALTER TABLE `invoices` ADD COLUMN `due_date` DATE NULL",
        'cycle_type' => "ALTER TABLE `invoices` ADD COLUMN `cycle_type` VARCHAR(20) NOT NULL DEFAULT 'AUTOMATIC'",
        'exceptional_cycle_id' => "ALTER TABLE `invoices` ADD COLUMN `exceptional_cycle_id` INT NULL",
      ];
      foreach ($invoiceColumns as $col => $sql) {
        if (!col_exists($conn, 'invoices', $col)) {
          @ $conn->query($sql);
        }
      }
      try { @ $conn->query("CREATE INDEX `idx_invoices_exceptional_cycle` ON `invoices` (`exceptional_cycle_id`)"); } catch (Throwable $e) {}
    }
    billing_accounts_migrate($conn);
  }
}
if (!function_exists('billing_normalize_reminder_offsets')) {
  function billing_normalize_reminder_offsets($value): array {
    $parts = is_array($value) ? $value : preg_split('/[\s,;|]+/', trim((string)$value));
    $offsets = [];
    foreach ((array)$parts as $part) {
      if ($part === '' || $part === null || !preg_match('/^-?\d+$/', trim((string)$part))) continue;
      $offset = min(60, max(-28, (int)$part));
      $offsets[$offset] = $offset;
    }
    ksort($offsets, SORT_NUMERIC);
    return array_values($offsets);
  }
}
if (!function_exists('billing_due_reminder_offset')) {
  function billing_due_reminder_offset(array $offsets, int $dueTs, ?int $now = null): ?int {
    $now = $now ?? time();
    $eligible = null;
    foreach (billing_normalize_reminder_offsets($offsets) as $offset) {
      $eventTs = strtotime(($offset >= 0 ? '+' : '') . $offset . ' day', $dueTs);
      if ($eventTs !== false && $eventTs <= $now) $eligible = (int)$offset;
    }
    return $eligible;
  }
}
if (!function_exists('billing_get_settings')) {
  function billing_get_settings(mysqli $conn): array {
    billing_cycle_migrate($conn);
    $day = (int)get_app_setting($conn, 'billing_cycle_day', '1');
    if ($day < 1) $day = 1; if ($day > 28) $day = 28;
    // The collection reminder is intentionally anchored to the due date.
    $reminderOffsets = [0];
    return [
      'cycle_day' => $day,
      'generation_lead_days' => min(28, max(0, (int)get_app_setting($conn, 'billing_generation_lead_days', '3'))),
      'due_after_days' => max(0, (int)get_app_setting($conn, 'billing_due_after_days', '0')),
      'reminder_offsets' => $reminderOffsets,
      'reminders_enabled' => get_app_setting($conn, 'billing_reminders_enabled', '1') === '1',
      'grace_days' => max(0, (int)get_app_setting($conn, 'grace_days', '0')),
      'warn_after_days' => max(0, (int)get_app_setting($conn, 'warn_after_days', '0')),
      'limit_after_days' => max(0, (int)get_app_setting($conn, 'limit_after_days', '0')),
      'cut_after_days' => max(0, (int)get_app_setting($conn, 'cut_after_days', '0')),
      'limit_enabled' => get_app_setting($conn, 'billing_limit_enabled', '0') === '1',
      'cut_stage_enabled' => get_app_setting($conn, 'billing_cut_stage_enabled', '0') === '1',
      'skip_limit_stage' => get_app_setting($conn, 'billing_skip_limit_stage', '0') === '1',
      'automation_invoices_enabled' => get_app_setting($conn, 'automation_invoices_enabled', '1') === '1',
      'automation_cut_enabled' => get_app_setting($conn, 'automation_cut_enabled', '1') === '1',
      'billing_protected_mode' => get_app_setting($conn, 'billing_protected_mode', '1') !== '0',
      'request_retention_days' => max(1, (int)get_app_setting($conn, 'billing_request_retention_days', '3')),
    ];
  }
}
if (!function_exists('billing_protected_mode_enabled')) {
  function billing_protected_mode_enabled(mysqli $conn): bool {
    $cfg = billing_get_settings($conn);
    return !empty($cfg['billing_protected_mode']);
  }
}
if (!function_exists('billing_manual_enforcement_allowed')) {
  function billing_manual_enforcement_allowed(): bool {
    return defined('ALLOW_BILLING_APPROVAL_EXECUTION') && ALLOW_BILLING_APPROVAL_EXECUTION === true;
  }
}
if (!function_exists('billing_current_cycle')) {
  function billing_current_cycle(mysqli $conn, ?int $ts = null): array {
    $ts = $ts ?: time();
    $cfg = billing_get_settings($conn); $day = (int)$cfg['cycle_day'];
    $y = (int)date('Y', $ts); $m = (int)date('n', $ts); $dom = (int)date('j', $ts);
    if ($dom >= $day) $startTs = strtotime(sprintf('%04d-%02d-%02d 00:00:00', $y, $m, $day));
    else { $pm = $m - 1; $py = $y; if ($pm < 1) { $pm = 12; $py--; } $startTs = strtotime(sprintf('%04d-%02d-%02d 00:00:00', $py, $pm, $day)); }
    $nextStartTs = strtotime('+1 month', $startTs); $endTs = strtotime('-1 day', $nextStartTs);
    $label = date('Y-m-d', $startTs) . ' → ' . date('Y-m-d', $endTs); $dueTs = strtotime('+' . (int)$cfg['due_after_days'] . ' day', $startTs);
    return ['label'=>$label,'start_date'=>date('Y-m-d',$startTs),'end_date'=>date('Y-m-d',$endTs),'month_key'=>date('Y-m',$startTs),'due_date'=>date('Y-m-d',$dueTs),'is_generation_day'=>((int)date('j',$ts)===$day)];
  }
}
if (!function_exists('billing_cycle_from_start')) {
  function billing_cycle_from_start(mysqli $conn, string $startDate): array {
    $cfg = billing_get_settings($conn);
    $startTs = strtotime($startDate . ' 00:00:00') ?: time();
    $nextStartTs = strtotime('+1 month', $startTs);
    $endTs = strtotime('-1 day', $nextStartTs);
    $dueTs = strtotime('+' . (int)$cfg['due_after_days'] . ' day', $startTs);
    return [
      'label'=>date('Y-m-d', $startTs) . ' → ' . date('Y-m-d', $endTs),
      'start_date'=>date('Y-m-d', $startTs),
      'end_date'=>date('Y-m-d', $endTs),
      'month_key'=>date('Y-m', $startTs),
      'due_date'=>date('Y-m-d', $dueTs),
      'is_generation_day'=>false,
    ];
  }
}
if (!function_exists('billing_next_cycle')) {
  function billing_next_cycle(mysqli $conn, ?int $ts = null): array {
    $current = billing_current_cycle($conn, $ts);
    return billing_cycle_from_start($conn, date('Y-m-d', strtotime('+1 month', strtotime($current['start_date']))));
  }
}
if (!function_exists('billing_cycle_generation_date')) {
  function billing_cycle_generation_date(mysqli $conn, array $cycle): string {
    $cfg = billing_get_settings($conn);
    $startTs = strtotime((string)$cycle['start_date']) ?: time();
    return date('Y-m-d', strtotime('-' . (int)$cfg['generation_lead_days'] . ' day', $startTs));
  }
}
if (!function_exists('billing_create_action_request')) {
  function billing_create_action_request(mysqli $conn, int $invoiceId, int $subscriberId, string $actionType, string $cycleLabel, int $effectiveDays, string $reason, array $meta = [], string $requestSuffix = ''): bool {
    $invoiceId = (int)$invoiceId;
    $subscriberId = (int)$subscriberId;
    $actionType = strtoupper(trim($actionType));
    if ($invoiceId <= 0 || $subscriberId <= 0 || !in_array($actionType, ['REMINDER','LIMIT','CUT'], true)) return false;
    $requestSuffix = strtoupper(preg_replace('/[^A-Z0-9:_-]+/i', '', trim($requestSuffix)));
    $requestKey = $actionType . ':INVOICE:' . $invoiceId . ($requestSuffix !== '' ? ':' . $requestSuffix : '');
    $stmt = $conn->prepare("INSERT IGNORE INTO billing_action_requests
      (invoice_id,subscriber_id,action_type,status,cycle_label,effective_days,reason,meta_json,request_key)
      VALUES (?,?,?,'pending',?,?,?,?,?)");
    if (!$stmt) return false;
    $metaJson = json_encode($meta, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    $stmt->bind_param('iississs', $invoiceId, $subscriberId, $actionType, $cycleLabel, $effectiveDays, $reason, $metaJson, $requestKey);
    $ok = @$stmt->execute();
    $created = $ok && $stmt->affected_rows > 0;
    $stmt->close();
    return $created;
  }
}
if (!function_exists('billing_generate_cycle_invoices')) {
  function billing_generate_cycle_invoices(mysqli $conn, array $cycle, array $subscriberIds = []): array {
    $out = ['ok'=>true, 'created'=>0, 'covered_by_credit'=>0, 'reminder_pending'=>0, 'pricing_missing'=>0, 'skipped'=>0, 'cycle'=>$cycle];
    $cfg = billing_get_settings($conn);
    if (!empty($cycle['settings']) && is_array($cycle['settings'])) {
      $cfg = array_replace($cfg, $cycle['settings']);
    }
    $cycleType = strtoupper(trim((string)($cycle['type'] ?? 'AUTOMATIC')));
    if (!in_array($cycleType, ['AUTOMATIC', 'EXCEPTIONAL'], true)) $cycleType = 'AUTOMATIC';
    $exceptionalCycleId = $cycleType === 'EXCEPTIONAL' ? max(0, (int)($cycle['exceptional_cycle_id'] ?? 0)) : 0;
    @ $conn->query("CREATE TABLE IF NOT EXISTS `speeds` (id INT AUTO_INCREMENT PRIMARY KEY, speed_name VARCHAR(50) DEFAULT NULL, price DECIMAL(10,2) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    $subscriberIds = array_values(array_unique(array_filter(array_map('intval', $subscriberIds), fn($id) => $id > 0)));
    $scopeSql = $subscriberIds ? (' WHERE s.id IN (' . implode(',', $subscriberIds) . ')') : '';
    $sql = "SELECT s.id,s.full_name,s.phone,COALESCE(s.is_deleted,0) is_deleted,COALESCE(s.billing_paused,0) billing_paused,
      COALESCE(sp1.price,sp2.price,sp3.price,0) auto_amount,
      COALESCE(sp1.speed_name,sp2.speed_name,sp3.speed_name,'') billed_speed_name
      FROM subscribers s
      LEFT JOIN speeds sp1 ON CONVERT(sp1.speed_name USING utf8mb4) COLLATE utf8mb4_unicode_ci=CONVERT(s.speed_name USING utf8mb4) COLLATE utf8mb4_unicode_ci
      LEFT JOIN speeds sp2 ON CONVERT(sp2.speed_name USING utf8mb4) COLLATE utf8mb4_unicode_ci=CONVERT(s.speed USING utf8mb4) COLLATE utf8mb4_unicode_ci
      LEFT JOIN speeds sp3 ON
        UPPER(CONVERT(TRIM(sp3.rate_limit) USING utf8mb4)) COLLATE utf8mb4_unicode_ci=
        UPPER(CONVERT(SUBSTRING_INDEX(TRIM(COALESCE(NULLIF(s.ppp_rate_limit,''),NULLIF(s.mt_rate_limit,''))), ' ', 1) USING utf8mb4)) COLLATE utf8mb4_unicode_ci
      {$scopeSql}
      ORDER BY s.id ASC";
    $res = @ $conn->query($sql);
    if (!$res) return $out + ['ok'=>false, 'error'=>$conn->error];
    $label = (string)$cycle['label'];
    $start = (string)$cycle['start_date'];
    $end = (string)$cycle['end_date'];
    $due = (string)$cycle['due_date'];
    $month = (string)$cycle['month_key'];
    while ($row = $res->fetch_assoc()) {
      $sid = (int)($row['id'] ?? 0);
      if ($sid <= 0 || (int)($row['is_deleted'] ?? 0) === 1 || (int)($row['billing_paused'] ?? 0) === 1) {
        $out['skipped']++;
        continue;
      }
      $generationKey = $cycleType === 'EXCEPTIONAL'
        ? 'EXCEPTIONAL:' . $exceptionalCycleId . ':' . $sid
        : 'BASE:' . $sid . ':' . $start;
      $keyEsc = $conn->real_escape_string($generationKey);
      $duplicateSql = "generation_key='{$keyEsc}'";
      if ($cycleType !== 'EXCEPTIONAL') {
        $duplicateSql .= " OR (subscriber_id={$sid} AND invoice_type='BASE' AND COALESCE(cycle_type,'AUTOMATIC')<>'EXCEPTIONAL' AND ((cycle_start_date='"
          . $conn->real_escape_string($start) . "') OR cycle_label='" . $conn->real_escape_string($label) . "'))";
      }
      $check = @ $conn->query("SELECT id FROM invoices WHERE {$duplicateSql} LIMIT 1");
      if ($check && $check->num_rows) { $out['skipped']++; continue; }
      $amount = round(max(0, (float)($row['auto_amount'] ?? 0)), 2);
      if ($amount <= 0) {
        $out['pricing_missing']++;
        $out['skipped']++;
        continue;
      }
      try {
        $conn->begin_transaction();
        $pricingNote = ($cycleType === 'EXCEPTIONAL' ? 'دورة استثنائية · ' : '') . 'تسعير آلي حسب سرعة: ' . trim((string)($row['billed_speed_name'] ?? ''));
        $stmt = $conn->prepare("INSERT INTO invoices
          (subscriber_id,amount,status,cycle_month,invoice_type,notes,created_at,paid_amount,remaining_amount,payment_status,updated_at,cycle_label,cycle_start_date,cycle_end_date,due_date,credit_applied,generation_key,cycle_type,exceptional_cycle_id)
          VALUES (?,?,'غير مدفوع',?,'BASE',?,NOW(),0.00,?,'unpaid',NOW(),?,?,?,?,0.00,?,?,NULLIF(?,0))");
        if (!$stmt) throw new RuntimeException($conn->error);
        $stmt->bind_param('idssdssssssi', $sid, $amount, $month, $pricingNote, $amount, $label, $start, $end, $due, $generationKey, $cycleType, $exceptionalCycleId);
        if (!@$stmt->execute()) throw new RuntimeException($stmt->error);
        $invoiceId = (int)$stmt->insert_id;
        $stmt->close();
        $credit = billing_apply_credit_to_invoice($conn, $invoiceId);
        if (empty($credit['ok'])) throw new RuntimeException((string)($credit['error'] ?? 'تعذر تطبيق الرصيد المقدم'));
        if (!empty($credit['covered'])) {
          $out['covered_by_credit']++;
        } else {
          @ $conn->query("UPDATE subscribers SET billing_state='DUE',billing_state_updated_at=NOW() WHERE id={$sid}");
          // The invoice may be generated before its due date, but the payment
          // reminder is created only by billing_queue_requests() when the due
          // date itself is reached. This keeps payment availability and the
          // reminder request on the same business day.
        }
        $conn->commit();
        $out['created']++;
      } catch (Throwable $e) {
        @$conn->rollback();
        $out['ok'] = false;
        $out['skipped']++;
        $out['error'] = $e->getMessage();
      }
    }
    return $out;
  }
}
if (!function_exists('billing_create_exceptional_cycle')) {
  function billing_create_exceptional_cycle(mysqli $conn, int $createdBy = 0, string $requestedLabel = '', string $requestToken = '', array $subscriberIds = []): array {
    billing_cycle_migrate($conn);
    if (!table_exists($conn, 'subscribers') || !table_exists($conn, 'invoices') || !table_exists($conn, 'billing_exceptional_cycles')) {
      return ['ok'=>false, 'error'=>'قاعدة بيانات الفوترة غير مكتملة.'];
    }
    $requestToken = preg_replace('/[^a-zA-Z0-9_-]+/', '', trim($requestToken));
    if (strlen($requestToken) < 16) $requestToken = bin2hex(random_bytes(24));
    $tokenEsc = $conn->real_escape_string($requestToken);
    $existing = @ $conn->query("SELECT * FROM billing_exceptional_cycles WHERE request_token='{$tokenEsc}' LIMIT 1");
    if ($existing && ($existingRow = $existing->fetch_assoc())) {
      return ['ok'=>in_array((string)$existingRow['status'], ['completed','partial'], true), 'idempotent'=>true, 'cycle'=>$existingRow, 'created'=>(int)$existingRow['created_invoices'], 'covered_by_credit'=>(int)$existingRow['covered_by_credit'], 'pricing_missing'=>(int)$existingRow['pricing_missing'], 'skipped'=>(int)$existingRow['skipped']];
    }

    $cfg = billing_get_settings($conn);
    $startDate = date('Y-m-d');
    $dueDate = date('Y-m-d', strtotime('+' . (int)$cfg['due_after_days'] . ' day', strtotime($startDate)));
    $endDate = date('Y-m-d', strtotime('-1 day', strtotime('+1 month', strtotime($startDate))));
    $reminderOffsets = implode(',', billing_normalize_reminder_offsets($cfg['reminder_offsets'] ?? []));
    $label = trim(preg_replace('/\s+/u', ' ', $requestedLabel));
    $label = function_exists('mb_substr') ? mb_substr($label, 0, 80, 'UTF-8') : substr($label, 0, 80);
    $temporaryLabel = $label !== '' ? $label : 'دورة استثنائية قيد الإنشاء';
    $subscriberIds = array_values(array_unique(array_filter(array_map('intval', $subscriberIds), fn($id) => $id > 0)));
    if ($subscriberIds) {
      $subscriberCount = count($subscriberIds);
    } else {
      $countRs = @ $conn->query("SELECT COUNT(*) c FROM subscribers WHERE COALESCE(is_deleted,0)=0 AND COALESCE(billing_paused,0)=0");
      $subscriberCount = $countRs ? (int)($countRs->fetch_assoc()['c'] ?? 0) : 0;
    }
    $remindersEnabled = !empty($cfg['reminders_enabled']) ? 1 : 0;
    $limitEnabled = !empty($cfg['automation_cut_enabled']) && !empty($cfg['limit_enabled']) && empty($cfg['skip_limit_stage']) ? 1 : 0;
    $cutEnabled = !empty($cfg['automation_cut_enabled']) && !empty($cfg['cut_stage_enabled']) ? 1 : 0;
    $graceDays = (int)$cfg['grace_days'];
    $limitAfter = (int)$cfg['limit_after_days'];
    $cutAfter = (int)$cfg['cut_after_days'];
    $stmt = $conn->prepare("INSERT INTO billing_exceptional_cycles
      (label,request_token,status,start_date,due_date,grace_days,limit_after_days,cut_after_days,reminder_offsets,reminders_enabled,limit_enabled,cut_enabled,subscriber_count,created_by)
      VALUES (?,?,'creating',?,?,?,?,?,?,?,?,?,?,NULLIF(?,0))");
    if (!$stmt) return ['ok'=>false, 'error'=>$conn->error];
    $stmt->bind_param('ssssiiisiiiii', $temporaryLabel, $requestToken, $startDate, $dueDate, $graceDays, $limitAfter, $cutAfter, $reminderOffsets, $remindersEnabled, $limitEnabled, $cutEnabled, $subscriberCount, $createdBy);
    if (!@$stmt->execute()) {
      $error = $stmt->error;
      $stmt->close();
      return ['ok'=>false, 'error'=>$error];
    }
    $cycleId = (int)$stmt->insert_id;
    $stmt->close();
    if ($label === '') $label = 'دورة استثنائية #' . $cycleId . ' · ' . date('Y-m-d H:i');
    $label = function_exists('mb_substr') ? mb_substr($label, 0, 80, 'UTF-8') : substr($label, 0, 80);
    $labelEsc = $conn->real_escape_string($label);
    @ $conn->query("UPDATE billing_exceptional_cycles SET label='{$labelEsc}' WHERE id={$cycleId}");

    $cycleSettings = [
      'reminder_offsets'=>billing_normalize_reminder_offsets($reminderOffsets),
      'reminders_enabled'=>$remindersEnabled === 1,
      'grace_days'=>$graceDays,
      'limit_after_days'=>$limitAfter,
      'cut_after_days'=>$cutAfter,
      'limit_enabled'=>$limitEnabled === 1,
      'skip_limit_stage'=>$limitEnabled !== 1,
      'cut_stage_enabled'=>$cutEnabled === 1,
    ];
    $cycle = [
      'label'=>$label,
      'start_date'=>$startDate,
      'end_date'=>$endDate,
      'month_key'=>date('Y-m'),
      'due_date'=>$dueDate,
      'type'=>'EXCEPTIONAL',
      'exceptional_cycle_id'=>$cycleId,
      'settings'=>$cycleSettings,
    ];
    $generated = billing_generate_cycle_invoices($conn, $cycle, $subscriberIds);
    $status = !empty($generated['ok']) ? 'completed' : ((int)($generated['created'] ?? 0) > 0 ? 'partial' : 'failed');
    $statusEsc = $conn->real_escape_string($status);
    $created = (int)($generated['created'] ?? 0);
    $covered = (int)($generated['covered_by_credit'] ?? 0);
    $pricingMissing = (int)($generated['pricing_missing'] ?? 0);
    $skipped = (int)($generated['skipped'] ?? 0);
    @ $conn->query("UPDATE billing_exceptional_cycles SET status='{$statusEsc}',created_invoices={$created},covered_by_credit={$covered},pricing_missing={$pricingMissing},skipped={$skipped},completed_at=NOW() WHERE id={$cycleId}");
    return ['ok'=>!empty($generated['ok']), 'cycle_id'=>$cycleId, 'cycle'=>$cycle, 'created'=>$created, 'covered_by_credit'=>$covered, 'reminder_pending'=>(int)($generated['reminder_pending'] ?? 0), 'pricing_missing'=>$pricingMissing, 'skipped'=>$skipped, 'error'=>(string)($generated['error'] ?? '')];
  }
}
if (!function_exists('billing_recent_exceptional_cycles')) {
  function billing_recent_exceptional_cycles(mysqli $conn, int $limit = 8): array {
    billing_cycle_migrate($conn);
    $limit = min(30, max(1, $limit));
    $rows = [];
    $res = @ $conn->query("SELECT * FROM billing_exceptional_cycles ORDER BY id DESC LIMIT {$limit}");
    if ($res) while ($row = $res->fetch_assoc()) $rows[] = $row;
    return $rows;
  }
}
if (!function_exists('billing_generate_current_cycle_invoices')) {
  function billing_generate_current_cycle_invoices(mysqli $conn, bool $force = false): array {
    billing_cycle_migrate($conn); $cfg = billing_get_settings($conn); $cycle = billing_current_cycle($conn);
    $out = ['ok'=>true,'created'=>0,'covered_by_credit'=>0,'reminder_pending'=>0,'pricing_missing'=>0,'skipped'=>0,'cycle'=>$cycle,'cycles'=>[],'ran'=>false];
    if (!table_exists($conn, 'subscribers') || !table_exists($conn, 'invoices')) return $out;
    if (!$cfg['automation_invoices_enabled'] && !$force) return $out;
    $targets = [];
    $today = date('Y-m-d');
    $currentGenerationDate = billing_cycle_generation_date($conn, $cycle);
    // Do not backfill an already overdue month merely because automation was
    // enabled or the application was installed late in that month.
    if ($force || ($today >= $currentGenerationDate && $today <= (string)$cycle['due_date'])) {
      $targets[] = $cycle;
    }
    $next = billing_next_cycle($conn);
    if ($force || $today >= billing_cycle_generation_date($conn, $next)) $targets[] = $next;
    foreach ($targets as $target) {
      $result = billing_generate_cycle_invoices($conn, $target);
      $out['cycles'][] = $result;
      $out['created'] += (int)($result['created'] ?? 0);
      $out['covered_by_credit'] += (int)($result['covered_by_credit'] ?? 0);
      $out['reminder_pending'] += (int)($result['reminder_pending'] ?? 0);
      $out['pricing_missing'] += (int)($result['pricing_missing'] ?? 0);
      $out['skipped'] += (int)($result['skipped'] ?? 0);
      if (empty($result['ok'])) $out['ok'] = false;
    }
    $out['ran'] = !empty($targets); return $out;
  }
}
if (!function_exists('billing_pending_invoice_rows')) {
  function billing_pending_invoice_rows(mysqli $conn): array {
    if (!table_exists($conn, 'invoices') || !table_exists($conn, 'subscribers')) return [];
    $sql = "SELECT i.id AS invoice_id,i.subscriber_id,i.amount,i.created_at,i.cycle_month,i.cycle_label,i.due_date,
      COALESCE(i.cycle_type,'AUTOMATIC') cycle_type,COALESCE(i.exceptional_cycle_id,0) exceptional_cycle_id,
      ec.grace_days cycle_grace_days,ec.limit_after_days cycle_limit_after_days,ec.cut_after_days cycle_cut_after_days,
      ec.reminder_offsets cycle_reminder_offsets,ec.reminders_enabled cycle_reminders_enabled,
      ec.limit_enabled cycle_limit_enabled,ec.cut_enabled cycle_cut_enabled,
      COALESCE(i.remaining_amount,i.amount) remaining_amount,COALESCE(i.payment_status,'') payment_status,
      COALESCE(i.status,'') invoice_status,s.full_name,s.phone,s.status subscriber_status,s.ppp_username,
      s.ppp_password,s.ppp_profile,s.ppp_rate_limit,s.speed_name,COALESCE(s.speed_state,'') speed_state,
      COALESCE(s.mt_disabled,0) mt_disabled,COALESCE(s.ppp_is_disabled,0) ppp_is_disabled,
      COALESCE(s.billing_paused,0) billing_paused
      FROM invoices i
      INNER JOIN subscribers s ON s.id=i.subscriber_id
      LEFT JOIN billing_exceptional_cycles ec ON ec.id=i.exceptional_cycle_id
      WHERE COALESCE(s.is_deleted,0)=0
        AND COALESCE(s.billing_paused,0)=0
        AND COALESCE(i.remaining_amount,i.amount)>0
        AND LOWER(COALESCE(i.payment_status,'unpaid'))<>'paid'
        AND (i.invoice_type IS NULL OR i.invoice_type='BASE')
      ORDER BY i.subscriber_id ASC,COALESCE(i.due_date,DATE(i.created_at)) ASC,i.id ASC";
    $res = @ $conn->query($sql); $rows = []; if ($res) while ($r = $res->fetch_assoc()) $rows[] = $r; return $rows;
  }
}
if (!function_exists('billing_enqueue_template_message')) {
  function billing_enqueue_template_message(mysqli $conn, string $templateKey, string $phone, array $vars, int $subscriberId = 0): bool {
    $phone = trim($phone);
    if ($phone === '') return false;
    require_once __DIR__ . '/messages_lib.php';
    if (!function_exists('messages_enqueue_template')) return false;
    $result = messages_enqueue_template($conn, $templateKey, $phone, $vars, $subscriberId > 0 ? $subscriberId : null);
    if (empty($result['ok'])) {
      error_log('[NetworkCustomers] billing message enqueue failed template=' . $templateKey
        . ' subscriber=' . $subscriberId . ' error=' . (string)($result['error'] ?? 'unknown'));
    }
    return !empty($result['ok']);
  }
}
if (!function_exists('billing_queue_requests')) {
  function billing_queue_requests(mysqli $conn): array {
    billing_cycle_migrate($conn); $cfg = billing_get_settings($conn); $rows = billing_pending_invoice_rows($conn); $now = time();
    $out = ['reminder_pending'=>0,'limit_pending'=>0,'cut_pending'=>0,'skipped'=>0];
    $oldestBySubscriber = [];
    $rowPolicy = static function (array $row) use ($cfg): array {
      $exceptional = strtoupper((string)($row['cycle_type'] ?? 'AUTOMATIC')) === 'EXCEPTIONAL'
        && (int)($row['exceptional_cycle_id'] ?? 0) > 0;
      if (!$exceptional) return $cfg;
      $limitEnabled = (int)($row['cycle_limit_enabled'] ?? 0) === 1;
      $cutEnabled = (int)($row['cycle_cut_enabled'] ?? 0) === 1;
      return array_replace($cfg, [
        'reminder_offsets'=>billing_normalize_reminder_offsets((string)($row['cycle_reminder_offsets'] ?? '')),
        'reminders_enabled'=>(int)($row['cycle_reminders_enabled'] ?? 0) === 1,
        'automation_cut_enabled'=>$limitEnabled || $cutEnabled,
        'grace_days'=>max(0, (int)($row['cycle_grace_days'] ?? 0)),
        'limit_after_days'=>max(0, (int)($row['cycle_limit_after_days'] ?? 0)),
        'cut_after_days'=>max(0, (int)($row['cycle_cut_after_days'] ?? 0)),
        'limit_enabled'=>$limitEnabled,
        'skip_limit_stage'=>!$limitEnabled,
        'cut_stage_enabled'=>$cutEnabled,
      ]);
    };
    foreach ($rows as $row) {
      $invoiceId = (int)($row['invoice_id'] ?? 0); $sid = (int)($row['subscriber_id'] ?? 0); if ($invoiceId <= 0 || $sid <= 0) { $out['skipped']++; continue; }
      $dueTs = strtotime((string)($row['due_date'] ?? '')) ?: $now;
      $policy = $rowPolicy($row);
      if (!empty($policy['reminders_enabled']) && !empty($policy['reminder_offsets'])) {
        $eligibleOffset = billing_due_reminder_offset([0], $dueTs, $now);
        if ($eligibleOffset !== null) {
          $daysUntilDue = (int)ceil(($dueTs - $now) / 86400);
          if (billing_create_action_request($conn, $invoiceId, $sid, 'REMINDER', (string)($row['cycle_label'] ?? $row['cycle_month'] ?? ''), $eligibleOffset, 'موعد تنبيه مجدول لفاتورة مستحقة', [
            'template_key'=>'DUE_SOON_WARNING',
            'phone'=>(string)($row['phone'] ?? ''),
            'name'=>(string)($row['full_name'] ?? ''),
            'amount'=>(string)($row['remaining_amount'] ?? $row['amount'] ?? ''),
            'cycle_month'=>(string)($row['cycle_month'] ?? ''),
            'days'=>$daysUntilDue,
            'schedule_offset'=>$eligibleOffset,
            'schedule_date'=>date('Y-m-d', strtotime(($eligibleOffset >= 0 ? '+' : '') . $eligibleOffset . ' day', $dueTs)),
          ], 'DAY:' . ($eligibleOffset < 0 ? 'M' . abs($eligibleOffset) : 'P' . $eligibleOffset))) {
            $out['reminder_pending']++;
          }
        }
      }
      if (!isset($oldestBySubscriber[$sid])) $oldestBySubscriber[$sid] = $row;
    }
    foreach ($oldestBySubscriber as $sid => $row) {
      $policy = $rowPolicy($row);
      if (empty($policy['automation_cut_enabled'])) continue;
      $invoiceId = (int)$row['invoice_id'];
      $startDate = trim((string)($row['due_date'] ?? ''));
      if ($startDate === '') $startDate = (string)($row['created_at'] ?? '');
      $startTs = strtotime($startDate) ?: $now;
      $days = (int)floor(($now - $startTs) / 86400);
      $effective = max(0, $days - (int)$policy['grace_days']);
      $stages = [];
      if (!empty($policy['limit_enabled']) && empty($policy['skip_limit_stage'])) $stages['LIMIT'] = (int)$policy['limit_after_days'];
      if (!empty($policy['cut_stage_enabled'])) $stages['CUT'] = (int)$policy['cut_after_days'];
      foreach ($stages as $type => $threshold) {
        if ($threshold < 0 || $effective < $threshold) continue;
        if ($type === 'CUT' && !empty($policy['limit_enabled']) && empty($policy['skip_limit_stage'])) {
          $limitApproved = strtoupper((string)($row['speed_state'] ?? '')) === 'LIMIT';
          if (!$limitApproved) {
            $approvedLimit = @$conn->query("SELECT id FROM billing_action_requests
              WHERE invoice_id={$invoiceId} AND subscriber_id={$sid}
                AND action_type='LIMIT' AND status='approved' LIMIT 1");
            $limitApproved = $approvedLimit && $approvedLimit->num_rows > 0;
          }
          if (!$limitApproved) continue;
        }
        $typeEsc = $conn->real_escape_string($type);
        $existing = @ $conn->query("SELECT id FROM billing_action_requests
          WHERE invoice_id={$invoiceId} AND subscriber_id={$sid}
            AND action_type='{$typeEsc}' AND status IN ('pending','approved') LIMIT 1");
        if ($existing && $existing->num_rows) continue;
        if ($type === 'LIMIT' && strtoupper((string)($row['speed_state'] ?? '')) === 'CUT') continue;
        $reason = $type === 'LIMIT' ? 'جاهز للتخفيض بعد تجاوز المدة المحددة' : 'جاهز للقطع بعد تجاوز المدة المحددة';
        $created = billing_create_action_request($conn, $invoiceId, (int)$sid, $type, (string)($row['cycle_label'] ?? $row['cycle_month'] ?? ''), $effective, $reason, [
          'ppp_username'=>(string)($row['ppp_username'] ?? ''),
          'ppp_password'=>(string)($row['ppp_password'] ?? ''),
          'ppp_profile'=>(string)($row['ppp_profile'] ?? ''),
          'ppp_rate_limit'=>(string)($row['ppp_rate_limit'] ?? ''),
          'speed_name'=>(string)($row['speed_name'] ?? ''),
          'amount'=>(string)($row['remaining_amount'] ?? ''),
          'cut_after_days'=>(int)($policy['cut_after_days'] ?? 0),
        ]);
        if ($created) {
          if ($type === 'LIMIT') $out['limit_pending']++; else $out['cut_pending']++;
        }
      }
    }
    return $out;
  }
}
if (!function_exists('billing_restore_subscriber_after_payment')) {
  function billing_restore_subscriber_after_payment(mysqli $conn, int $subscriberId, int $invoiceId = 0): bool {
    $subscriberId = (int)$subscriberId; if ($subscriberId <= 0) return false;
    if ($invoiceId > 0 && table_exists($conn, 'billing_action_requests')) {
      @ $conn->query("UPDATE billing_action_requests SET status='resolved',resolved_at=NOW()
        WHERE invoice_id={$invoiceId} AND status='pending'");
    }
    if (billing_subscriber_outstanding($conn, $subscriberId) > 0.00001) {
      if (col_exists($conn,'subscribers','billing_state')) {
        @ $conn->query("UPDATE subscribers SET billing_state='DUE',billing_state_updated_at=NOW() WHERE id={$subscriberId}");
      }
      return false;
    }
    $paused = false;
    $pausedRs = @ $conn->query("SELECT COALESCE(billing_paused,0) billing_paused FROM subscribers WHERE id={$subscriberId} LIMIT 1");
    if ($pausedRs && ($pausedRow = $pausedRs->fetch_assoc())) $paused = (int)$pausedRow['billing_paused'] === 1;
    if ($paused) {
      if (col_exists($conn,'subscribers','billing_state')) {
        @ $conn->query("UPDATE subscribers SET billing_state='PAUSED',billing_state_updated_at=NOW() WHERE id={$subscriberId}");
      }
      return false;
    }
    if (col_exists($conn,'subscribers','status')) @ $conn->query("UPDATE subscribers SET status='نشط' WHERE id={$subscriberId}");
    if (col_exists($conn,'subscribers','speed_state')) @ $conn->query("UPDATE subscribers SET speed_state='ORIGINAL' WHERE id={$subscriberId}");
    if (col_exists($conn,'subscribers','current_speed') && col_exists($conn,'subscribers','base_speed')) @ $conn->query("UPDATE subscribers SET current_speed=base_speed WHERE id={$subscriberId}");
    if (col_exists($conn,'subscribers','mt_disabled')) @ $conn->query("UPDATE subscribers SET mt_disabled=0 WHERE id={$subscriberId}");
    if (col_exists($conn,'subscribers','ppp_is_disabled')) @ $conn->query("UPDATE subscribers SET ppp_is_disabled=0 WHERE id={$subscriberId}");
    if (col_exists($conn,'subscribers','billing_state')) {
      @ $conn->query("UPDATE subscribers SET billing_state='CURRENT',billing_state_updated_at=NOW() WHERE id={$subscriberId}");
    }
    if (table_exists($conn, 'billing_action_requests')) {
      @ $conn->query("UPDATE billing_action_requests SET status='resolved',resolved_at=NOW()
        WHERE subscriber_id={$subscriberId} AND status='pending'");
    }
    return true;
  }
}
if (!function_exists('billing_execute_request')) {
  function billing_execute_request(mysqli $conn, array $req, int $approvedBy = 0): array {
    $id = (int)($req['id'] ?? 0); $sid = (int)($req['subscriber_id'] ?? 0); $invoiceId = (int)($req['invoice_id'] ?? 0); $type = strtoupper(trim((string)($req['action_type'] ?? '')));
    $meta = json_decode((string)($req['meta_json'] ?? ''), true); if (!is_array($meta)) $meta = [];
    $sub = null;
    $phoneSelect = col_exists($conn, 'subscribers', 'phone') ? ', phone' : '';
    $q = @ $conn->query("SELECT COALESCE(is_deleted,0) AS is_deleted, ppp_username, ppp_password, ppp_profile, ppp_rate_limit, speed_name{$phoneSelect} FROM subscribers WHERE id={$sid} LIMIT 1"); if ($q && $q->num_rows) $sub = $q->fetch_assoc();
    if ((int)($sub['is_deleted'] ?? 0) === 1) {
      @ $conn->query("UPDATE billing_action_requests SET status='resolved', approved_by=".(int)$approvedBy.", approved_at=NOW(), resolved_at=NOW() WHERE id={$id} LIMIT 1");
      return ['ok'=>true,'id'=>$id,'invoice_id'=>$invoiceId,'subscriber_id'=>$sid,'action_type'=>$type,'error'=>'تم تجاهل الطلب لأن المشترك مؤرشف'];
    }
    if ($invoiceId > 0 && table_exists($conn, 'invoices')) {
      $invoiceRs = @ $conn->query("SELECT COALESCE(remaining_amount,amount) AS remaining_amount,
        COALESCE(payment_status,'') AS payment_status,COALESCE(status,'') AS invoice_status
        FROM invoices WHERE id={$invoiceId} AND subscriber_id={$sid} LIMIT 1");
      $invoiceRow = $invoiceRs ? $invoiceRs->fetch_assoc() : null;
      $invoiceSettled = !$invoiceRow
        || (float)($invoiceRow['remaining_amount'] ?? 0) <= 0.00001
        || strtoupper((string)($invoiceRow['payment_status'] ?? '')) === 'PAID'
        || (string)($invoiceRow['invoice_status'] ?? '') === 'مدفوع';
      if ($invoiceSettled) {
        @ $conn->query("UPDATE billing_action_requests SET status='resolved',approved_by=".(int)$approvedBy.",
          approved_at=NOW(),resolved_at=NOW() WHERE id={$id} LIMIT 1");
        return ['ok'=>true,'id'=>$id,'invoice_id'=>$invoiceId,'subscriber_id'=>$sid,'action_type'=>$type,'error'=>'تم تجاوز الطلب لأن فاتورته سُددت'];
      }
    }
    if (billing_subscriber_outstanding($conn, $sid) <= 0.00001) {
      @ $conn->query("UPDATE billing_action_requests SET status='resolved',approved_by=".(int)$approvedBy.",approved_at=NOW(),resolved_at=NOW() WHERE id={$id} LIMIT 1");
      return ['ok'=>true,'id'=>$id,'invoice_id'=>$invoiceId,'subscriber_id'=>$sid,'action_type'=>$type,'error'=>'تم تجاوز الطلب لأن الرصيد المستحق أصبح صفراً'];
    }
    $pausedRs = @ $conn->query("SELECT COALESCE(billing_paused,0) billing_paused FROM subscribers WHERE id={$sid} LIMIT 1");
    $pausedRow = $pausedRs ? $pausedRs->fetch_assoc() : null;
    if ((int)($pausedRow['billing_paused'] ?? 0) === 1) {
      @ $conn->query("UPDATE billing_action_requests SET status='resolved',approved_by=".(int)$approvedBy.",approved_at=NOW(),resolved_at=NOW() WHERE id={$id} LIMIT 1");
      return ['ok'=>true,'id'=>$id,'invoice_id'=>$invoiceId,'subscriber_id'=>$sid,'action_type'=>$type,'error'=>'تم تجاوز الطلب لأن المشترك متوقف مؤقتاً'];
    }
    if ($type === 'REMINDER') {
      $templateKey = trim((string)($meta['template_key'] ?? 'DUE_SOON_WARNING'));
      $phone = trim((string)($meta['phone'] ?? $sub['phone'] ?? ''));
      $queued = billing_enqueue_template_message($conn, $templateKey, $phone, $meta, $sid);
      $status = $queued ? 'approved' : 'failed';
      @ $conn->query("UPDATE billing_action_requests SET status='{$status}',approved_by=".(int)$approvedBy.",approved_at=NOW(),executed_at=NOW() WHERE id={$id} LIMIT 1");
      return ['ok'=>$queued,'id'=>$id,'invoice_id'=>$invoiceId,'subscriber_id'=>$sid,'action_type'=>$type,'error'=>$queued ? '' : 'تعذر إدراج رسالة التسديد في طابور الإرسال'];
    }
    $pppUser = trim((string)($sub['ppp_username'] ?? $meta['ppp_username'] ?? '')); $pppPass = trim((string)($sub['ppp_password'] ?? $meta['ppp_password'] ?? '')); $profile = trim((string)($sub['ppp_profile'] ?? $meta['ppp_profile'] ?? '')); $origRate = trim((string)($sub['ppp_rate_limit'] ?? $meta['ppp_rate_limit'] ?? '')); if ($origRate === '') { $sn = trim((string)($sub['speed_name'] ?? $meta['speed_name'] ?? '')); if ($sn !== '') $origRate = $sn . '/' . $sn; }
    $limitSpeed = '512k/512k'; if (function_exists('mt_get_settings')) { $ms = mt_get_settings($conn); $limitSpeed = trim((string)($ms['limit_speed'] ?? '512k/512k')); }
    $ok = true; $error = '';
    if ($pppUser !== '' && $pppPass !== '') {
      $ok = mt_ppp_secret_upsert($conn, $pppUser, $pppPass, $profile, '', 'BILLING-'.$type.':'.$invoiceId);
      if ($ok) {
        if ($type === 'LIMIT') {
          mt_ppp_secret_set_disabled($conn, $pppUser, false); if ($limitSpeed !== '') mt_ppp_secret_set_rate($conn, $pppUser, $limitSpeed);
          if (col_exists($conn,'subscribers','speed_state')) @ $conn->query("UPDATE subscribers SET speed_state='LIMIT', current_speed='".$conn->real_escape_string($limitSpeed)."' WHERE id={$sid}");
          if (col_exists($conn,'subscribers','mt_disabled')) @ $conn->query("UPDATE subscribers SET mt_disabled=0 WHERE id={$sid}");
          if (col_exists($conn,'subscribers','ppp_is_disabled')) @ $conn->query("UPDATE subscribers SET ppp_is_disabled=0 WHERE id={$sid}");
          if (col_exists($conn,'subscribers','billing_state')) @ $conn->query("UPDATE subscribers SET billing_state='LIMITED',billing_state_updated_at=NOW() WHERE id={$sid}");
        } elseif ($type === 'CUT') {
          mt_ppp_secret_set_disabled($conn, $pppUser, true); mt_ppp_active_disconnect($conn, $pppUser);
          if (col_exists($conn,'subscribers','speed_state')) @ $conn->query("UPDATE subscribers SET speed_state='CUT' WHERE id={$sid}");
          if (col_exists($conn,'subscribers','status')) @ $conn->query("UPDATE subscribers SET status='متوقف' WHERE id={$sid}");
          if (col_exists($conn,'subscribers','mt_disabled')) @ $conn->query("UPDATE subscribers SET mt_disabled=1 WHERE id={$sid}");
          if (col_exists($conn,'subscribers','ppp_is_disabled')) @ $conn->query("UPDATE subscribers SET ppp_is_disabled=1 WHERE id={$sid}");
          if (col_exists($conn,'subscribers','billing_state')) @ $conn->query("UPDATE subscribers SET billing_state='CUT',billing_state_updated_at=NOW() WHERE id={$sid}");
        }
      } else { $error = (string)($GLOBALS['mt_last_error'] ?? 'فشل التنفيذ على MikroTik'); }
    } else { $error = 'بيانات PPPoE ناقصة'; $ok = false; }
    $status = $ok ? 'approved' : 'failed'; @ $conn->query("UPDATE billing_action_requests SET status='{$status}', approved_by=".(int)$approvedBy.", approved_at=NOW(), executed_at=NOW() WHERE id={$id} LIMIT 1");
    if ($ok) {
      $phone = trim((string)($sub['phone'] ?? ''));
      $effectiveDays = (int)($req['effective_days'] ?? 0);
      $cfg = billing_get_settings($conn);
      $cutAfterDays = array_key_exists('cut_after_days', $meta) ? (int)$meta['cut_after_days'] : (int)($cfg['cut_after_days'] ?? 0);
      $daysLeft = max(0, $cutAfterDays > 0 ? ($cutAfterDays - $effectiveDays) : 0);
      if ($type === 'LIMIT') {
        billing_enqueue_template_message($conn, 'LINE_LIMIT_UNPAID', $phone, [
          'invoice_id'=>$invoiceId,
          'days'=>$daysLeft,
          'cycle_month'=>(string)($req['cycle_label'] ?? ''),
          'amount'=>'',
        ], $sid);
      } elseif ($type === 'CUT') {
        billing_enqueue_template_message($conn, 'LINE_CUT_UNPAID', $phone, [
          'invoice_id'=>$invoiceId,
          'cycle_month'=>(string)($req['cycle_label'] ?? ''),
          'amount'=>'',
        ], $sid);
      }
    }
    return ['ok'=>$ok,'id'=>$id,'invoice_id'=>$invoiceId,'subscriber_id'=>$sid,'action_type'=>$type,'error'=>$error];
  }
}
if (!function_exists('billing_request_retention_days')) {
  function billing_request_retention_days(?mysqli $conn = null): int {
    if ($conn instanceof mysqli) return max(1, (int)get_app_setting($conn, 'billing_request_retention_days', '3'));
    return 3;
  }
}
if (!function_exists('billing_request_warn_hours')) {
  function billing_request_warn_hours(): int { return 24; }
}
if (!function_exists('billing_is_super_admin_actor')) {
  function billing_is_super_admin_actor(): bool {
    $username = (string)($_SESSION['admin_user'] ?? ($_SESSION['admin_username'] ?? ''));
    if (function_exists('is_bypass_admin') && is_bypass_admin($username)) return true;
    $permissions = is_array($_SESSION['perms'] ?? null) ? $_SESSION['perms'] : [];
    return !empty($permissions['all']) || !empty($permissions['ALL']);
  }
}
if (!function_exists('billing_cleanup_stale_requests')) {
  function billing_cleanup_stale_requests(mysqli $conn, bool $performDelete = true): array {
    $out = [
      'deleted_total' => 0,
      'deleted_limit' => 0,
      'deleted_cut' => 0,
      'warn_total' => 0,
      'warn_limit' => 0,
      'warn_cut' => 0,
    ];
    // A fresh/partially restored database may be opened while its schema is
    // being repaired. Never join a prerequisite table that is not present.
    if (!table_exists($conn, 'billing_action_requests') || !table_exists($conn, 'invoices')) return $out;

    $retentionDays = billing_request_retention_days($conn);
    $warnHours = billing_request_warn_hours();
    $sql = "SELECT r.id, r.action_type, r.created_at, i.cycle_end_date
"
         . "FROM billing_action_requests r
"
         . "LEFT JOIN invoices i ON i.id = r.invoice_id
"
         . "WHERE LOWER(COALESCE(r.status,''))='pending'";
    $res = @$conn->query($sql);
    if (!($res instanceof mysqli_result)) return $out;

    $deleteIds = [];
    $now = time();
    while ($row = $res->fetch_assoc()) {
      $type = strtoupper(trim((string)($row['action_type'] ?? '')));
      $createdAt = trim((string)($row['created_at'] ?? ''));
      $deleteAt = $createdAt !== '' ? strtotime($createdAt . ' +' . $retentionDays . ' day') : false;
      if (!$deleteAt) {
        $cycleEnd = trim((string)($row['cycle_end_date'] ?? ''));
        $deleteAt = $cycleEnd !== '' ? strtotime($cycleEnd . ' 23:59:59 +' . $retentionDays . ' day') : false;
      }
      if (!$deleteAt) continue;
      $warnAt = $deleteAt - ($warnHours * 3600);
      if ($now >= $deleteAt) {
        if ($performDelete) {
          $deleteIds[] = (int)($row['id'] ?? 0);
          $out['deleted_total']++;
          if ($type === 'LIMIT') $out['deleted_limit']++;
          if ($type === 'CUT') $out['deleted_cut']++;
        } else {
          $out['warn_total']++;
          if ($type === 'LIMIT') $out['warn_limit']++;
          if ($type === 'CUT') $out['warn_cut']++;
        }
      } elseif ($now >= $warnAt) {
        $out['warn_total']++;
        if ($type === 'LIMIT') $out['warn_limit']++;
        if ($type === 'CUT') $out['warn_cut']++;
      }
    }
    if ($performDelete && $deleteIds) {
      $deleteIds = array_values(array_unique(array_filter($deleteIds, fn($v) => $v > 0)));
      if ($deleteIds) {
        @$conn->query("DELETE FROM billing_action_requests WHERE id IN (" . implode(',', $deleteIds) . ")");
      }
    }
    return $out;
  }
}
if (!function_exists('billing_get_alert_payload')) {
  function billing_get_alert_payload(mysqli $conn): array {
    // This endpoint is read-only. Invoice generation and request creation are
    // owned by the automation runner/cron so refreshing the UI cannot mutate
    // financial data.
    $pricingMissing = 0;
    if (table_exists($conn, 'subscribers') && table_exists($conn, 'speeds')) {
      $missing = @$conn->query("SELECT COUNT(DISTINCT s.id) c
        FROM subscribers s
        LEFT JOIN speeds sp1 ON CONVERT(sp1.speed_name USING utf8mb4) COLLATE utf8mb4_unicode_ci=CONVERT(s.speed_name USING utf8mb4) COLLATE utf8mb4_unicode_ci
        LEFT JOIN speeds sp2 ON CONVERT(sp2.speed_name USING utf8mb4) COLLATE utf8mb4_unicode_ci=CONVERT(s.speed USING utf8mb4) COLLATE utf8mb4_unicode_ci
        LEFT JOIN speeds sp3 ON
          UPPER(CONVERT(TRIM(sp3.rate_limit) USING utf8mb4)) COLLATE utf8mb4_unicode_ci=
          UPPER(CONVERT(SUBSTRING_INDEX(TRIM(COALESCE(NULLIF(s.ppp_rate_limit,''),NULLIF(s.mt_rate_limit,''))), ' ', 1) USING utf8mb4)) COLLATE utf8mb4_unicode_ci
        WHERE COALESCE(s.is_deleted,0)=0 AND COALESCE(s.billing_paused,0)=0
          AND COALESCE(sp1.price,sp2.price,sp3.price,0)<=0");
      if ($missing && ($missingRow = $missing->fetch_assoc())) $pricingMissing = (int)($missingRow['c'] ?? 0);
    }
    $generation = ['created'=>0,'covered_by_credit'=>0,'reminder_pending'=>0,'pricing_missing'=>$pricingMissing,'ran'=>false];
    // Approval requests are financial controls and must never disappear before
    // the super admin resolves them. Retention is informational only.
    $cleanup = billing_cleanup_stale_requests($conn, false);
    $cycle = billing_current_cycle($conn);
    $generatedCount = 0;
    if (table_exists($conn, 'invoices')) {
      $where = [];
      if (col_exists($conn, 'invoices', 'invoice_type')) $where[] = "invoice_type='BASE'";
      if (col_exists($conn, 'invoices', 'cycle_label')) $where[] = "cycle_label='" . $conn->real_escape_string($cycle['label']) . "'";
      elseif (col_exists($conn, 'invoices', 'cycle_month')) $where[] = "cycle_month='" . $conn->real_escape_string($cycle['month_key']) . "'";
      if ($where) {
        $r = @$conn->query("SELECT COUNT(*) c FROM invoices WHERE " . implode(' AND ', $where));
        if ($r && ($row = $r->fetch_assoc())) $generatedCount = (int)($row['c'] ?? 0);
      }
    }
    $pending = ['REMINDER'=>0,'LIMIT'=>0,'CUT'=>0];
    if (table_exists($conn, 'billing_action_requests')) {
      $agg = @$conn->query("SELECT UPPER(COALESCE(action_type,'')) AS action_type, COUNT(*) c FROM billing_action_requests WHERE LOWER(COALESCE(status,''))='pending' GROUP BY UPPER(COALESCE(action_type,''))");
      if ($agg) while ($row = $agg->fetch_assoc()) $pending[(string)($row['action_type'] ?? '')] = (int)($row['c'] ?? 0);
    }
    return [
      'ok' => true,
      'cycle' => $cycle,
      'generation' => $generation,
      'generated_count' => $generatedCount,
      'pending' => [
        'reminder' => (int)($pending['REMINDER'] ?? 0),
        'limit' => (int)($pending['LIMIT'] ?? 0),
        'cut' => (int)($pending['CUT'] ?? 0),
      ],
      'cleanup' => [
        'warn_total' => (int)($cleanup['warn_total'] ?? 0),
        'warn_limit' => (int)($cleanup['warn_limit'] ?? 0),
        'warn_cut' => (int)($cleanup['warn_cut'] ?? 0),
        'deleted_total' => (int)($cleanup['deleted_total'] ?? 0),
        'deleted_limit' => (int)($cleanup['deleted_limit'] ?? 0),
        'deleted_cut' => (int)($cleanup['deleted_cut'] ?? 0),
        'retention_days' => billing_request_retention_days($conn),
      ],
    ];
  }
}
if (!function_exists('billing_pick_first_existing_col')) {
  function billing_pick_first_existing_col(mysqli $db, string $table, array $candidates): ?string {
    foreach ($candidates as $col) {
      $col = trim((string)$col);
      if ($col !== '' && col_exists($db, $table, $col)) return $col;
    }
    return null;
  }
}
if (!function_exists('billing_get_pending_requests')) {
  function billing_get_pending_requests(mysqli $db, string $actionType, int $limit = 1000): array {
    $actionType = strtoupper(trim($actionType));
    $limit = max(1, (int)$limit);
    if (!table_exists($db, 'billing_action_requests') || !table_exists($db, 'subscribers') || !table_exists($db, 'invoices')) return [];

    $subFullName = billing_pick_first_existing_col($db, 'subscribers', ['full_name','name','subscriber_name']);
    $subPhone = billing_pick_first_existing_col($db, 'subscribers', ['phone','mobile','phone_number']);
    $subUser = billing_pick_first_existing_col($db, 'subscribers', ['username','user_name']);
    $subPppUser = billing_pick_first_existing_col($db, 'subscribers', ['ppp_username','pppoe_username','login_username']);
    $subPackage = billing_pick_first_existing_col($db, 'subscribers', ['package_name','package']);
    $subSpeedName = billing_pick_first_existing_col($db, 'subscribers', ['speed_name','speed']);
    $subCurrentSpeed = billing_pick_first_existing_col($db, 'subscribers', ['current_speed']);
    $subBaseSpeed = billing_pick_first_existing_col($db, 'subscribers', ['base_speed']);
    $subMtDisabled = billing_pick_first_existing_col($db, 'subscribers', ['mt_disabled']);
    $subPppDisabled = billing_pick_first_existing_col($db, 'subscribers', ['ppp_is_disabled']);

    $invAmount = billing_pick_first_existing_col($db, 'invoices', ['amount','total_amount','total','price','invoice_total']);
    $invPaid = billing_pick_first_existing_col($db, 'invoices', ['paid_amount','amount_paid']);
    $invRemaining = billing_pick_first_existing_col($db, 'invoices', ['remaining_amount','amount_due','balance']);
    $invDue = billing_pick_first_existing_col($db, 'invoices', ['due_date']);
    $invCycleLabel = billing_pick_first_existing_col($db, 'invoices', ['cycle_label','cycle_month']);
    $invCycleStart = billing_pick_first_existing_col($db, 'invoices', ['cycle_start_date']);
    $invCycleEnd = billing_pick_first_existing_col($db, 'invoices', ['cycle_end_date']);

    $sel = [];
    $sel[] = "r.id";
    $sel[] = "r.subscriber_id";
    $sel[] = "r.invoice_id";
    $sel[] = "UPPER(COALESCE(r.action_type,'')) AS action_type";
    $sel[] = "LOWER(COALESCE(r.status,'')) AS status";
    $sel[] = "r.created_at";
    $sel[] = (col_exists($db,'billing_action_requests','approved_at') ? "r.approved_at" : "NULL AS approved_at");
    $sel[] = (col_exists($db,'billing_action_requests','approved_by') ? "r.approved_by" : "NULL AS approved_by");
    $sel[] = (col_exists($db,'billing_action_requests','effective_days') ? "r.effective_days" : "0 AS effective_days");
    $sel[] = (col_exists($db,'billing_action_requests','reason') ? "r.reason" : "'' AS reason");
    $sel[] = (col_exists($db,'billing_action_requests','meta_json') ? "r.meta_json" : "NULL AS meta_json");
    $sel[] = ($subFullName ? "COALESCE(s.`{$subFullName}`,'') AS full_name" : "'' AS full_name");
    $sel[] = ($subPhone ? "COALESCE(s.`{$subPhone}`,'') AS phone" : "'' AS phone");
    $sel[] = ($subUser ? "COALESCE(s.`{$subUser}`,'') AS username" : "'' AS username");
    $sel[] = ($subPppUser ? "COALESCE(s.`{$subPppUser}`,'') AS ppp_username" : "'' AS ppp_username");
    $sel[] = ($subPackage ? "COALESCE(s.`{$subPackage}`,'') AS package_name" : "'' AS package_name");
    $sel[] = ($subSpeedName ? "COALESCE(s.`{$subSpeedName}`,'') AS speed_name" : "'' AS speed_name");
    $sel[] = ($subCurrentSpeed ? "COALESCE(s.`{$subCurrentSpeed}`,'') AS current_speed" : "'' AS current_speed");
    $sel[] = ($subBaseSpeed ? "COALESCE(s.`{$subBaseSpeed}`,'') AS base_speed" : "'' AS base_speed");
    $sel[] = ($subMtDisabled ? "COALESCE(s.`{$subMtDisabled}`,0) AS mt_disabled" : "0 AS mt_disabled");
    $sel[] = ($subPppDisabled ? "COALESCE(s.`{$subPppDisabled}`,0) AS ppp_is_disabled" : "0 AS ppp_is_disabled");
    $sel[] = ($invAmount ? "COALESCE(i.`{$invAmount}`,0) AS invoice_amount" : "0 AS invoice_amount");
    $sel[] = ($invPaid ? "COALESCE(i.`{$invPaid}`,0) AS paid_amount" : "0 AS paid_amount");
    if ($invRemaining) {
      $sel[] = "COALESCE(i.`{$invRemaining}`,0) AS remaining_amount";
    } elseif ($invAmount) {
      $sel[] = ($invPaid ? "GREATEST(COALESCE(i.`{$invAmount}`,0)-COALESCE(i.`{$invPaid}`,0),0) AS remaining_amount" : "COALESCE(i.`{$invAmount}`,0) AS remaining_amount");
    } else {
      $sel[] = "0 AS remaining_amount";
    }
    $sel[] = ($invDue ? "i.`{$invDue}` AS due_date" : "NULL AS due_date");
    $sel[] = ($invCycleLabel ? "COALESCE(i.`{$invCycleLabel}`,'') AS invoice_cycle_label" : "'' AS invoice_cycle_label");
    $sel[] = ($invCycleStart ? "i.`{$invCycleStart}` AS cycle_start_date" : "NULL AS cycle_start_date");
    $sel[] = ($invCycleEnd ? "i.`{$invCycleEnd}` AS cycle_end_date" : "NULL AS cycle_end_date");

    $sql = "SELECT
  " . implode(",
  ", $sel) . "
FROM billing_action_requests r
LEFT JOIN subscribers s ON s.id = r.subscriber_id AND COALESCE(s.is_deleted,0)=0
LEFT JOIN invoices i ON i.id = r.invoice_id
WHERE LOWER(COALESCE(r.status,''))='pending'
  AND UPPER(COALESCE(r.action_type,''))=?
  AND COALESCE(s.is_deleted,0)=0
ORDER BY COALESCE(" . ($invDue ? "i.`{$invDue}`" : "r.created_at") . ", r.created_at) ASC, r.id ASC
LIMIT {$limit}";
    $stmt = $db->prepare($sql);
    if (!$stmt) return [];
    $stmt->bind_param('s', $actionType);
    $stmt->execute();
    $res = $stmt->get_result();
    $rows = [];
    while ($row = $res->fetch_assoc()) {
      $row['full_name'] = (string)($row['full_name'] ?? '');
      $row['phone'] = (string)($row['phone'] ?? '');
      $row['ppp_username'] = (string)($row['ppp_username'] ?? ($row['username'] ?? ''));
      $row['invoice_cycle_label'] = (string)($row['invoice_cycle_label'] ?? '');
      $row['package_name'] = (string)($row['package_name'] ?? '');
      $row['speed_name'] = (string)($row['speed_name'] ?? '');
      $row['remaining_amount'] = (float)($row['remaining_amount'] ?? 0);
      $isDisabled = ((int)($row['mt_disabled'] ?? 0) === 1) || ((int)($row['ppp_is_disabled'] ?? 0) === 1);
      $row['mikrotik_state'] = $isDisabled ? 'DISABLED' : 'ACTIVE';
      $rows[] = $row;
    }
    $stmt->close();
    return $rows;
  }
}
if (!function_exists('billing_approve_requests')) {
  function billing_approve_requests(mysqli $conn, string $actionType, array $ids = [], int $approvedBy = 0): array {
    $actionType = strtoupper(trim($actionType));
    $ids = array_values(array_unique(array_filter(array_map('intval', $ids), fn($v) => $v > 0)));
    if (!table_exists($conn, 'billing_action_requests')) return ['ok' => false, 'success' => 0, 'failed' => 0, 'items' => [], 'error' => 'جدول الطلبات غير موجود'];

    $where = ["LOWER(COALESCE(status,''))='pending'", "UPPER(COALESCE(action_type,''))='" . $conn->real_escape_string($actionType) . "'"];
    if ($ids) $where[] = 'id IN (' . implode(',', $ids) . ')';
    $res = @$conn->query("SELECT * FROM billing_action_requests WHERE " . implode(' AND ', $where) . " ORDER BY id ASC");
    $items = [];
    $success = 0;
    $failed = 0;
    if ($res) {
      if (!defined('ALLOW_BILLING_APPROVAL_EXECUTION')) define('ALLOW_BILLING_APPROVAL_EXECUTION', true);
      while ($req = $res->fetch_assoc()) {
        $result = billing_execute_request($conn, $req, $approvedBy);
        $items[] = $result;
        if (!empty($result['ok'])) $success++; else $failed++;
      }
    }
    return ['ok' => true, 'success' => $success, 'failed' => $failed, 'items' => $items];
  }
}
?>
