<?php
/**
 * Billing account and collection ledgers.
 *
 * This file owns subscriber advance credit, the financial side of temporary
 * subscriber pauses, and per-installation collection balances. It intentionally
 * contains no page rendering so every desktop/mobile/agent payment path can
 * share it.
 */

require_once __DIR__ . '/helpers.php';

if (!function_exists('billing_accounts_migrate')) {
  function billing_accounts_migrate(mysqli $conn): void {
    static $done = [];
    $connectionKey = spl_object_id($conn);
    if (!empty($done[$connectionKey])) return;
    if (table_exists($conn, 'subscribers')) {
      $columns = [
        'billing_paused' => "TINYINT(1) NOT NULL DEFAULT 0",
        'billing_paused_at' => "DATETIME NULL",
        'billing_paused_by' => "INT NULL",
        'billing_pause_reason' => "VARCHAR(255) NULL",
        'billing_state' => "VARCHAR(24) NOT NULL DEFAULT 'CURRENT'",
        'billing_state_updated_at' => "DATETIME NULL",
        'service_pause_prev_ppp_disabled' => "TINYINT(1) NULL",
        'service_pause_prev_mt_disabled' => "TINYINT(1) NULL",
        'service_pause_prev_speed_state' => "VARCHAR(32) NULL",
      ];
      foreach ($columns as $column => $definition) {
        if (!col_exists($conn, 'subscribers', $column)) {
          @ $conn->query("ALTER TABLE `subscribers` ADD COLUMN `{$column}` {$definition}");
        }
      }
    }

    if (table_exists($conn, 'invoices')) {
      if (!col_exists($conn, 'invoices', 'credit_applied')) {
        @ $conn->query("ALTER TABLE `invoices` ADD COLUMN `credit_applied` DECIMAL(12,2) NOT NULL DEFAULT 0.00");
      }
      if (!col_exists($conn, 'invoices', 'generation_key')) {
        @ $conn->query("ALTER TABLE `invoices` ADD COLUMN `generation_key` VARCHAR(191) NULL");
      }
      try { @ $conn->query("CREATE UNIQUE INDEX `uq_invoice_generation_key` ON `invoices` (`generation_key`)"); } catch (Throwable $e) {}
    }

    if (table_exists($conn, 'payments')) {
      $paymentColumns = [
        'collection_source_type' => "VARCHAR(24) NULL",
        'collection_source_ref' => "VARCHAR(100) NULL",
        'collection_source_name' => "VARCHAR(150) NULL",
        'collection_channel' => "VARCHAR(24) NULL",
      ];
      foreach ($paymentColumns as $column => $definition) {
        if (!col_exists($conn, 'payments', $column)) {
          @ $conn->query("ALTER TABLE `payments` ADD COLUMN `{$column}` {$definition}");
        }
      }
    }

    @ $conn->query("CREATE TABLE IF NOT EXISTS `subscriber_credit_accounts` (
      `subscriber_id` INT NOT NULL PRIMARY KEY,
      `balance` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
      `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      KEY `idx_subscriber_credit_balance` (`balance`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    @ $conn->query("CREATE TABLE IF NOT EXISTS `subscriber_credit_ledger` (
      `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
      `subscriber_id` INT NOT NULL,
      `direction` VARCHAR(10) NOT NULL,
      `entry_type` VARCHAR(32) NOT NULL,
      `amount` DECIMAL(14,2) NOT NULL,
      `balance_before` DECIMAL(14,2) NOT NULL,
      `balance_after` DECIMAL(14,2) NOT NULL,
      `invoice_id` INT NULL,
      `payment_id` INT NULL,
      `source_instance_id` VARCHAR(100) NULL,
      `idempotency_key` VARCHAR(191) NULL,
      `note` VARCHAR(255) NULL,
      `created_by_type` VARCHAR(24) NULL,
      `created_by_id` INT NULL,
      `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      UNIQUE KEY `uq_subscriber_credit_idempotency` (`idempotency_key`),
      KEY `idx_subscriber_credit_history` (`subscriber_id`,`created_at`),
      KEY `idx_subscriber_credit_invoice` (`invoice_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    @ $conn->query("CREATE TABLE IF NOT EXISTS `collection_accounts` (
      `account_key` VARCHAR(130) NOT NULL PRIMARY KEY,
      `account_type` VARCHAR(24) NOT NULL,
      `account_ref` VARCHAR(100) NULL,
      `account_name` VARCHAR(150) NOT NULL,
      `balance` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
      `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      KEY `idx_collection_account_type` (`account_type`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    @ $conn->query("CREATE TABLE IF NOT EXISTS `collection_ledger_entries` (
      `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
      `account_key` VARCHAR(130) NOT NULL,
      `direction` VARCHAR(10) NOT NULL,
      `entry_type` VARCHAR(32) NOT NULL,
      `amount` DECIMAL(14,2) NOT NULL,
      `balance_before` DECIMAL(14,2) NOT NULL,
      `balance_after` DECIMAL(14,2) NOT NULL,
      `payment_id` INT NULL,
      `expense_id` INT NULL,
      `invoice_id` INT NULL,
      `subscriber_id` INT NULL,
      `source_instance_id` VARCHAR(100) NULL,
      `channel` VARCHAR(24) NULL,
      `idempotency_key` VARCHAR(191) NULL,
      `note` VARCHAR(255) NULL,
      `status` VARCHAR(20) NOT NULL DEFAULT 'POSTED',
      `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      UNIQUE KEY `uq_collection_idempotency` (`idempotency_key`),
      KEY `idx_collection_account_history` (`account_key`,`created_at`),
      KEY `idx_collection_payment` (`payment_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    if (!col_exists($conn, 'collection_ledger_entries', 'expense_id')) {
      @ $conn->query("ALTER TABLE collection_ledger_entries ADD COLUMN expense_id INT NULL AFTER payment_id");
      @ $conn->query("CREATE INDEX idx_collection_expense ON collection_ledger_entries(expense_id)");
    }
    $done[$connectionKey] = true;
  }
}

if (!function_exists('billing_subscriber_is_paused')) {
  function billing_subscriber_is_paused(array $subscriber): bool {
    return (int)($subscriber['billing_paused'] ?? 0) === 1;
  }
}

if (!function_exists('billing_set_subscriber_pause')) {
  function billing_set_subscriber_pause(mysqli $conn, int $subscriberId, bool $paused, int $actorId = 0, string $reason = ''): array {
    billing_accounts_migrate($conn);
    $subscriberId = (int)$subscriberId;
    if ($subscriberId <= 0) return ['ok'=>false, 'error'=>'رقم المشترك غير صحيح'];
    $reasonEsc = $conn->real_escape_string(substr(trim($reason), 0, 255));
    $state = $paused ? 'PAUSED' : 'CURRENT';
    $sql = $paused
      ? "UPDATE subscribers SET billing_paused=1,billing_paused_at=NOW(),billing_paused_by=" . ($actorId > 0 ? $actorId : 'NULL') . ",billing_pause_reason='{$reasonEsc}',billing_state='{$state}',billing_state_updated_at=NOW() WHERE id={$subscriberId} LIMIT 1"
      : "UPDATE subscribers SET billing_paused=0,billing_paused_at=NULL,billing_paused_by=NULL,billing_pause_reason=NULL,billing_state='{$state}',billing_state_updated_at=NOW() WHERE id={$subscriberId} LIMIT 1";
    if (!@$conn->query($sql)) return ['ok'=>false, 'error'=>$conn->error];
    if (table_exists($conn, 'billing_action_requests')) {
      if ($paused) {
        @ $conn->query("UPDATE billing_action_requests SET status='paused',resolved_at=NULL
          WHERE subscriber_id={$subscriberId} AND status='pending'");
      } else {
        @ $conn->query("UPDATE billing_action_requests SET status='pending',resolved_at=NULL
          WHERE subscriber_id={$subscriberId} AND status='paused'");
      }
    }
    return ['ok'=>true, 'paused'=>$paused];
  }
}

if (!function_exists('billing_credit_balance')) {
  function billing_credit_balance(mysqli $conn, int $subscriberId, bool $lock = false): float {
    billing_accounts_migrate($conn);
    $subscriberId = (int)$subscriberId;
    if ($subscriberId <= 0) return 0.0;
    @ $conn->query("INSERT IGNORE INTO subscriber_credit_accounts (subscriber_id,balance) VALUES ({$subscriberId},0.00)");
    $suffix = $lock ? ' FOR UPDATE' : '';
    $rs = @ $conn->query("SELECT balance FROM subscriber_credit_accounts WHERE subscriber_id={$subscriberId} LIMIT 1{$suffix}");
    if (!$rs || !($row = $rs->fetch_assoc())) return 0.0;
    return round((float)($row['balance'] ?? 0), 2);
  }
}

if (!function_exists('billing_credit_post')) {
  function billing_credit_post(mysqli $conn, int $subscriberId, string $direction, float $amount, array $options = []): array {
    billing_accounts_migrate($conn);
    $subscriberId = (int)$subscriberId;
    $direction = strtoupper(trim($direction));
    $amount = round(abs($amount), 2);
    if ($subscriberId <= 0 || $amount <= 0 || !in_array($direction, ['CREDIT','DEBIT'], true)) {
      return ['ok'=>false, 'error'=>'بيانات حركة الرصيد غير صحيحة'];
    }
    $key = substr(trim((string)($options['idempotency_key'] ?? '')), 0, 191);
    if ($key !== '') {
      $keyEsc = $conn->real_escape_string($key);
      $old = @ $conn->query("SELECT id,balance_after FROM subscriber_credit_ledger WHERE idempotency_key='{$keyEsc}' LIMIT 1");
      if ($old && ($oldRow = $old->fetch_assoc())) {
        return ['ok'=>true, 'duplicate'=>true, 'id'=>(int)$oldRow['id'], 'balance'=>(float)$oldRow['balance_after']];
      }
    }
    $before = billing_credit_balance($conn, $subscriberId, true);
    if ($direction === 'DEBIT' && $amount > $before + 0.00001) {
      return ['ok'=>false, 'error'=>'رصيد المشترك غير كافٍ'];
    }
    $after = round($direction === 'CREDIT' ? $before + $amount : $before - $amount, 2);
    if (!@$conn->query("UPDATE subscriber_credit_accounts SET balance={$after},updated_at=NOW() WHERE subscriber_id={$subscriberId}")) {
      return ['ok'=>false, 'error'=>$conn->error];
    }
    $entryType = substr(trim((string)($options['entry_type'] ?? ($direction === 'CREDIT' ? 'ADVANCE_PAYMENT' : 'INVOICE_APPLIED'))), 0, 32);
    $invoiceId = (int)($options['invoice_id'] ?? 0);
    $paymentId = (int)($options['payment_id'] ?? 0);
    $expenseId = (int)($options['expense_id'] ?? 0);
    $sourceInstance = substr(trim((string)($options['source_instance_id'] ?? '')), 0, 100);
    $note = substr(trim((string)($options['note'] ?? '')), 0, 255);
    $actorType = substr(trim((string)($options['created_by_type'] ?? 'admin')), 0, 24);
    $actorId = (int)($options['created_by_id'] ?? 0);
    $stmt = $conn->prepare("INSERT INTO subscriber_credit_ledger
      (subscriber_id,direction,entry_type,amount,balance_before,balance_after,invoice_id,payment_id,source_instance_id,idempotency_key,note,created_by_type,created_by_id)
      VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)");
    if (!$stmt) return ['ok'=>false, 'error'=>$conn->error];
    $invoiceDb = $invoiceId > 0 ? $invoiceId : null;
    $paymentDb = $paymentId > 0 ? $paymentId : null;
    $actorDb = $actorId > 0 ? $actorId : null;
    $stmt->bind_param('issdddiiisssi', $subscriberId, $direction, $entryType, $amount, $before, $after, $invoiceDb, $paymentDb, $sourceInstance, $key, $note, $actorType, $actorDb);
    $ok = @$stmt->execute();
    $id = $ok ? (int)$stmt->insert_id : 0;
    $error = $ok ? '' : $stmt->error;
    $stmt->close();
    return ['ok'=>$ok, 'duplicate'=>false, 'id'=>$id, 'balance'=>$after, 'error'=>$error];
  }
}

if (!function_exists('billing_apply_credit_to_invoice')) {
  function billing_apply_credit_to_invoice(mysqli $conn, int $invoiceId): array {
    billing_accounts_migrate($conn);
    $invoiceId = (int)$invoiceId;
    if ($invoiceId <= 0) return ['ok'=>false, 'error'=>'رقم الفاتورة غير صحيح'];
    $rs = @ $conn->query("SELECT id,subscriber_id,amount,paid_amount,remaining_amount,credit_applied,payment_status,status FROM invoices WHERE id={$invoiceId} LIMIT 1 FOR UPDATE");
    if (!$rs || !($invoice = $rs->fetch_assoc())) return ['ok'=>false, 'error'=>'الفاتورة غير موجودة'];
    $subscriberId = (int)($invoice['subscriber_id'] ?? 0);
    $total = round((float)($invoice['amount'] ?? 0), 2);
    $alreadyPaid = round((float)($invoice['paid_amount'] ?? 0), 2);
    $remaining = round((float)($invoice['remaining_amount'] ?? max(0, $total - $alreadyPaid)), 2);
    if ($subscriberId <= 0 || $remaining <= 0) {
      return ['ok'=>true, 'applied'=>0.0, 'remaining'=>max(0, $remaining), 'covered'=>$remaining <= 0];
    }
    $available = billing_credit_balance($conn, $subscriberId, true);
    $apply = round(min($available, $remaining), 2);
    if ($apply <= 0) return ['ok'=>true, 'applied'=>0.0, 'remaining'=>$remaining, 'covered'=>false];
    $posted = billing_credit_post($conn, $subscriberId, 'DEBIT', $apply, [
      'entry_type'=>'INVOICE_APPLIED',
      'invoice_id'=>$invoiceId,
      'idempotency_key'=>'CREDIT_APPLY:' . $invoiceId,
      'note'=>'خصم تلقائي من الرصيد المقدم للفاتورة رقم ' . $invoiceId,
      'created_by_type'=>'system',
    ]);
    if (empty($posted['ok'])) return $posted + ['applied'=>0.0, 'remaining'=>$remaining, 'covered'=>false];
    if (!empty($posted['duplicate'])) {
      $fresh = @ $conn->query("SELECT remaining_amount,payment_status FROM invoices WHERE id={$invoiceId} LIMIT 1");
      $row = $fresh ? $fresh->fetch_assoc() : [];
      $left = round((float)($row['remaining_amount'] ?? $remaining), 2);
      return ['ok'=>true, 'duplicate'=>true, 'applied'=>0.0, 'remaining'=>$left, 'covered'=>$left <= 0];
    }
    $newCreditApplied = round((float)($invoice['credit_applied'] ?? 0) + $apply, 2);
    $newPaid = round($alreadyPaid + $apply, 2);
    $newRemaining = round(max(0, $total - $newPaid), 2);
    $paymentStatus = $newRemaining <= 0.00001 ? 'paid' : 'partial';
    $status = $paymentStatus === 'paid' ? 'مدفوع' : 'مدفوع جزئياً';
    $sets = [
      "credit_applied={$newCreditApplied}",
      "paid_amount={$newPaid}",
      "remaining_amount={$newRemaining}",
      "payment_status='{$paymentStatus}'",
      "status='" . $conn->real_escape_string($status) . "'",
      "updated_at=NOW()",
    ];
    if ($paymentStatus === 'paid') {
      if (col_exists($conn, 'invoices', 'paid_at')) $sets[] = "paid_at=NOW()";
      if (col_exists($conn, 'invoices', 'paid_by_type')) $sets[] = "paid_by_type='credit'";
      if (col_exists($conn, 'invoices', 'paid_by_id')) $sets[] = "paid_by_id=NULL";
    }
    if (!@$conn->query("UPDATE invoices SET " . implode(',', $sets) . " WHERE id={$invoiceId} LIMIT 1")) {
      return ['ok'=>false, 'error'=>$conn->error];
    }
    return ['ok'=>true, 'applied'=>$apply, 'remaining'=>$newRemaining, 'covered'=>$newRemaining <= 0.00001, 'balance'=>(float)($posted['balance'] ?? 0)];
  }
}

if (!function_exists('billing_allocate_available_credit')) {
  function billing_allocate_available_credit(mysqli $conn, int $subscriberId): array {
    billing_accounts_migrate($conn);
    $subscriberId = (int)$subscriberId;
    $out = ['ok'=>true, 'applied'=>0.0, 'covered'=>0, 'items'=>[]];
    if ($subscriberId <= 0) return $out;
    $rs = @ $conn->query("SELECT id FROM invoices WHERE subscriber_id={$subscriberId}
      AND COALESCE(remaining_amount,amount)>0
      AND LOWER(COALESCE(payment_status,'unpaid'))<>'paid'
      ORDER BY COALESCE(due_date,created_at) ASC,id ASC FOR UPDATE");
    if (!$rs) return $out;
    while ($row = $rs->fetch_assoc()) {
      if (billing_credit_balance($conn, $subscriberId, false) <= 0.00001) break;
      $item = billing_apply_credit_to_invoice($conn, (int)$row['id']);
      $out['items'][] = $item;
      if (empty($item['ok'])) { $out['ok'] = false; break; }
      $out['applied'] += (float)($item['applied'] ?? 0);
      if (!empty($item['covered'])) $out['covered']++;
    }
    $out['applied'] = round($out['applied'], 2);
    return $out;
  }
}

if (!function_exists('billing_collection_post')) {
  function billing_collection_post(mysqli $conn, string $accountKey, string $accountType, string $accountRef, string $accountName, string $direction, float $amount, array $options = []): array {
    billing_accounts_migrate($conn);
    $accountKey = substr(trim($accountKey), 0, 130);
    $accountType = substr(trim($accountType), 0, 24);
    $accountRef = substr(trim($accountRef), 0, 100);
    $accountName = substr(trim($accountName), 0, 150);
    $direction = strtoupper(trim($direction));
    $amount = round(abs($amount), 2);
    if ($accountKey === '' || $amount <= 0 || !in_array($direction, ['CREDIT','DEBIT'], true)) {
      return ['ok'=>false, 'error'=>'بيانات حساب التحصيل غير صحيحة'];
    }
    $key = substr(trim((string)($options['idempotency_key'] ?? '')), 0, 191);
    if ($key !== '') {
      $keyEsc = $conn->real_escape_string($key);
      $old = @ $conn->query("SELECT id,balance_after FROM collection_ledger_entries WHERE idempotency_key='{$keyEsc}' LIMIT 1");
      if ($old && ($row = $old->fetch_assoc())) return ['ok'=>true, 'duplicate'=>true, 'id'=>(int)$row['id'], 'balance'=>(float)$row['balance_after']];
    }
    $stmt = $conn->prepare("INSERT INTO collection_accounts (account_key,account_type,account_ref,account_name,balance) VALUES (?,?,?,?,0.00)
      ON DUPLICATE KEY UPDATE
        account_type=IF(VALUES(account_type)<>'',VALUES(account_type),account_type),
        account_ref=IF(VALUES(account_ref)<>'',VALUES(account_ref),account_ref),
        account_name=IF(VALUES(account_name)<>'',VALUES(account_name),account_name)");
    if (!$stmt) return ['ok'=>false, 'error'=>$conn->error];
    $stmt->bind_param('ssss', $accountKey, $accountType, $accountRef, $accountName);
    $stmt->execute();
    $stmt->close();
    $accountKeyEsc = $conn->real_escape_string($accountKey);
    $rs = @ $conn->query("SELECT balance FROM collection_accounts WHERE account_key='{$accountKeyEsc}' LIMIT 1 FOR UPDATE");
    $before = ($rs && ($row = $rs->fetch_assoc())) ? round((float)$row['balance'], 2) : 0.0;
    $after = round($direction === 'CREDIT' ? $before + $amount : $before - $amount, 2);
    if (!@$conn->query("UPDATE collection_accounts SET balance={$after},updated_at=NOW() WHERE account_key='{$accountKeyEsc}'")) {
      return ['ok'=>false, 'error'=>$conn->error];
    }
    $entryType = substr(trim((string)($options['entry_type'] ?? 'PAYMENT')), 0, 32);
    $paymentId = (int)($options['payment_id'] ?? 0);
    $expenseId = (int)($options['expense_id'] ?? 0);
    $invoiceId = (int)($options['invoice_id'] ?? 0);
    $subscriberId = (int)($options['subscriber_id'] ?? 0);
    $sourceInstance = substr(trim((string)($options['source_instance_id'] ?? '')), 0, 100);
    $channel = substr(trim((string)($options['channel'] ?? 'desktop')), 0, 24);
    $note = substr(trim((string)($options['note'] ?? '')), 0, 255);
    $stmt = $conn->prepare("INSERT INTO collection_ledger_entries
      (account_key,direction,entry_type,amount,balance_before,balance_after,payment_id,expense_id,invoice_id,subscriber_id,source_instance_id,channel,idempotency_key,note)
      VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
    if (!$stmt) return ['ok'=>false, 'error'=>$conn->error];
    $paymentDb = $paymentId > 0 ? $paymentId : null;
    $expenseDb = $expenseId > 0 ? $expenseId : null;
    $invoiceDb = $invoiceId > 0 ? $invoiceId : null;
    $subscriberDb = $subscriberId > 0 ? $subscriberId : null;
    $stmt->bind_param('sssdddiiiissss', $accountKey, $direction, $entryType, $amount, $before, $after, $paymentDb, $expenseDb, $invoiceDb, $subscriberDb, $sourceInstance, $channel, $key, $note);
    $ok = @$stmt->execute();
    $id = $ok ? (int)$stmt->insert_id : 0;
    $error = $ok ? '' : $stmt->error;
    $stmt->close();
    return ['ok'=>$ok, 'duplicate'=>false, 'id'=>$id, 'balance'=>$after, 'error'=>$error];
  }
}

if (!function_exists('billing_record_collection_expense')) {
  function billing_record_collection_expense(mysqli $conn, int $expenseId, float $amount, array $source, array $options = []): array {
    billing_accounts_migrate($conn);
    $type = strtolower(trim((string)($source['type'] ?? 'main')));
    if (!in_array($type, ['main','branch','agent'], true)) $type = 'main';
    $ref = substr(trim((string)($source['ref'] ?? '')), 0, 100);
    $name = substr(trim((string)($source['name'] ?? 'النسخة الرئيسية')), 0, 150);
    $accountKey = $type === 'main' ? 'MAIN' : strtoupper($type) . ':' . ($ref !== '' ? $ref : $name);
    $accountEsc = $conn->real_escape_string($accountKey);
    $balanceRs = @$conn->query("SELECT balance FROM collection_accounts WHERE account_key='{$accountEsc}' LIMIT 1 FOR UPDATE");
    $available = ($balanceRs && ($balanceRow = $balanceRs->fetch_assoc())) ? (float)$balanceRow['balance'] : 0.0;
    if ($available + 0.00001 < $amount) return ['ok'=>false,'error'=>'رصيد الصندوق المختار غير كافٍ لتسجيل هذه الدفعة.'];
    if ($accountKey !== 'MAIN') {
      $mainRs = @$conn->query("SELECT balance FROM collection_accounts WHERE account_key='MAIN' LIMIT 1 FOR UPDATE");
      $mainAvailable = ($mainRs && ($mainRow = $mainRs->fetch_assoc())) ? (float)$mainRow['balance'] : 0.0;
      if ($mainAvailable + 0.00001 < $amount) return ['ok'=>false,'error'=>'الرصيد الإجمالي للصندوق الرئيسي لا يغطي هذه الدفعة. راجع ترابط أرصدة النسخ.'];
    }
    $common = [
      'entry_type'=>'EXPENSE','expense_id'=>$expenseId,
      'source_instance_id'=>(string)($source['instance'] ?? ''),'channel'=>(string)($source['channel'] ?? 'desktop'),
      'note'=>(string)($options['note'] ?? ''),
    ];
    $base = 'EXPENSE:' . $expenseId;
    $master = billing_collection_post($conn,'MAIN','main','main','النسخة الرئيسية','DEBIT',$amount,$common+['idempotency_key'=>'COLLECTION:MAIN:'.$base]);
    if (empty($master['ok'])) return $master;
    $selected = $master;
    if ($type !== 'main') {
      $selected = billing_collection_post($conn,$accountKey,$type,$ref,$name,'DEBIT',$amount,$common+['idempotency_key'=>'COLLECTION:SOURCE:'.$base]);
      if (empty($selected['ok'])) return $selected;
    }
    return ['ok'=>true,'master_balance'=>(float)($master['balance']??0),'source_balance'=>(float)($selected['balance']??0),'account_key'=>$accountKey];
  }
}

if (!function_exists('billing_reverse_collection_expense')) {
  function billing_reverse_collection_expense(mysqli $conn, int $expenseId): array {
    billing_accounts_migrate($conn);
    $rs = @$conn->query("SELECT * FROM collection_ledger_entries WHERE expense_id=".(int)$expenseId." AND status='POSTED' AND entry_type='EXPENSE' ORDER BY id ASC FOR UPDATE");
    if (!$rs) return ['ok'=>true,'reversed'=>0];
    $count=0;
    while($entry=$rs->fetch_assoc()){
      $id=(int)$entry['id'];
      $posted=billing_collection_post($conn,(string)$entry['account_key'],'','','','CREDIT',(float)$entry['amount'],[
        'entry_type'=>'EXPENSE_REVERSAL','expense_id'=>$expenseId,'source_instance_id'=>(string)($entry['source_instance_id']??''),
        'channel'=>(string)($entry['channel']??''),'idempotency_key'=>'COLLECTION:EXPENSE:REVERSE:'.$id,'note'=>'عكس المصروف رقم '.$expenseId,
      ]);
      if(empty($posted['ok']))return $posted;
      @$conn->query("UPDATE collection_ledger_entries SET status='REVERSED' WHERE id={$id} LIMIT 1");$count++;
    }
    return ['ok'=>true,'reversed'=>$count];
  }
}

if (!function_exists('billing_record_collection_payment')) {
  function billing_record_collection_payment(mysqli $conn, int $paymentId, int $invoiceId, int $subscriberId, float $amount, array $options = []): array {
    $baseKey = trim((string)($options['idempotency_key'] ?? ('PAYMENT:' . $paymentId)));
    $sourceType = strtolower(trim((string)($options['source_type'] ?? 'main')));
    if (!in_array($sourceType, ['main','branch','agent'], true)) $sourceType = 'main';
    $sourceRef = substr(trim((string)($options['source_ref'] ?? '')), 0, 100);
    $sourceName = substr(trim((string)($options['source_name'] ?? 'النسخة الرئيسية')), 0, 150);
    $sourceInstance = substr(trim((string)($options['source_instance_id'] ?? '')), 0, 100);
    $channel = substr(trim((string)($options['channel'] ?? 'desktop')), 0, 24);
    $common = [
      'entry_type'=>'PAYMENT',
      'payment_id'=>$paymentId,
      'invoice_id'=>$invoiceId,
      'subscriber_id'=>$subscriberId,
      'source_instance_id'=>$sourceInstance,
      'channel'=>$channel,
      'note'=>(string)($options['note'] ?? ''),
    ];
    $master = billing_collection_post($conn, 'MAIN', 'main', 'main', 'النسخة الرئيسية', 'CREDIT', $amount, $common + ['idempotency_key'=>'COLLECTION:MAIN:' . $baseKey]);
    if (empty($master['ok'])) return $master;
    $source = $master;
    if ($sourceType !== 'main') {
      $ref = $sourceRef !== '' ? $sourceRef : ($sourceInstance !== '' ? $sourceInstance : $sourceName);
      $sourceKey = strtoupper($sourceType) . ':' . $ref;
      $source = billing_collection_post($conn, $sourceKey, $sourceType, $ref, $sourceName, 'CREDIT', $amount, $common + ['idempotency_key'=>'COLLECTION:SOURCE:' . $baseKey]);
      if (empty($source['ok'])) return $source;
    }
    return ['ok'=>true, 'master_balance'=>(float)($master['balance'] ?? 0), 'source_balance'=>(float)($source['balance'] ?? 0)];
  }
}

if (!function_exists('billing_reverse_collection_payment')) {
  function billing_reverse_collection_payment(mysqli $conn, int $paymentId): array {
    billing_accounts_migrate($conn);
    $paymentId = (int)$paymentId;
    if ($paymentId <= 0) return ['ok'=>false, 'error'=>'رقم الدفعة غير صحيح'];
    $rs = @ $conn->query("SELECT * FROM collection_ledger_entries WHERE payment_id={$paymentId} AND status='POSTED' ORDER BY id ASC FOR UPDATE");
    if (!$rs) return ['ok'=>true, 'reversed'=>0];
    $count = 0;
    while ($entry = $rs->fetch_assoc()) {
      $id = (int)$entry['id'];
      $accountKey = (string)$entry['account_key'];
      $reverseDirection = strtoupper((string)$entry['direction']) === 'CREDIT' ? 'DEBIT' : 'CREDIT';
      $posted = billing_collection_post($conn, $accountKey, '', '', '', $reverseDirection, (float)$entry['amount'], [
        'entry_type'=>'PAYMENT_REVERSAL',
        'payment_id'=>$paymentId,
        'invoice_id'=>(int)($entry['invoice_id'] ?? 0),
        'subscriber_id'=>(int)($entry['subscriber_id'] ?? 0),
        'source_instance_id'=>(string)($entry['source_instance_id'] ?? ''),
        'channel'=>(string)($entry['channel'] ?? ''),
        'idempotency_key'=>'COLLECTION:REVERSE:' . $id,
        'note'=>'عكس قيد التحصيل رقم ' . $id,
      ]);
      if (empty($posted['ok'])) return $posted;
      @ $conn->query("UPDATE collection_ledger_entries SET status='REVERSED' WHERE id={$id} LIMIT 1");
      $count++;
    }
    return ['ok'=>true, 'reversed'=>$count];
  }
}

if (!function_exists('billing_collection_balances')) {
  function billing_collection_balances(mysqli $conn): array {
    billing_accounts_migrate($conn);
    $rows = [];
    $rs = @ $conn->query("SELECT account_key,account_type,account_ref,account_name,balance,updated_at FROM collection_accounts ORDER BY CASE WHEN account_key='MAIN' THEN 0 ELSE 1 END,account_type,account_name");
    if ($rs) while ($row = $rs->fetch_assoc()) $rows[] = $row;
    return $rows;
  }
}

if (!function_exists('billing_backfill_collection_ledger')) {
  function billing_backfill_collection_ledger(mysqli $conn, int $limit = 5000): array {
    billing_accounts_migrate($conn);
    $out = ['ok'=>true, 'posted'=>0, 'skipped'=>0, 'failed'=>0];
    if (!table_exists($conn, 'payments')) return $out;
    $limit = max(1, min(20000, $limit));
    $rs = @$conn->query("SELECT p.*
      FROM payments p
      LEFT JOIN collection_ledger_entries e
        ON e.payment_id=p.id AND e.account_key='MAIN'
      WHERE COALESCE(p.payment_status,'POSTED')='POSTED'
        AND p.amount>0
        AND e.id IS NULL
      ORDER BY p.id ASC
      LIMIT {$limit}");
    if (!$rs) return ['ok'=>false, 'posted'=>0, 'skipped'=>0, 'failed'=>1, 'error'=>$conn->error];
    while ($payment = $rs->fetch_assoc()) {
      $paymentId = (int)($payment['id'] ?? 0);
      $agentId = (int)($payment['agent_id'] ?? 0);
      $sourceType = strtolower(trim((string)($payment['collection_source_type'] ?? '')));
      if (!in_array($sourceType, ['main','branch','agent'], true)) $sourceType = $agentId > 0 ? 'agent' : 'main';
      $sourceRef = trim((string)($payment['collection_source_ref'] ?? ''));
      if ($sourceRef === '') $sourceRef = $sourceType === 'agent' ? (string)$agentId : ($sourceType === 'main' ? 'main' : (string)($payment['source_instance_id'] ?? ''));
      $sourceName = trim((string)($payment['collection_source_name'] ?? ''));
      if ($sourceName === '') $sourceName = $sourceType === 'agent' ? ('معتمد #' . $agentId) : ($sourceType === 'branch' ? 'نسخة فرعية' : 'النسخة الرئيسية');
      $posted = billing_record_collection_payment(
        $conn,
        $paymentId,
        (int)($payment['invoice_id'] ?? 0),
        (int)($payment['subscriber_id'] ?? 0),
        (float)($payment['amount'] ?? 0),
        [
          'source_type'=>$sourceType,
          'source_ref'=>$sourceRef,
          'source_name'=>$sourceName,
          'source_instance_id'=>(string)($payment['source_instance_id'] ?? ''),
          'channel'=>(string)($payment['collection_channel'] ?? 'legacy'),
          'idempotency_key'=>'PAYMENT:' . $paymentId,
          'note'=>'ترحيل آلي لدفعة سابقة',
        ]
      );
      if (empty($posted['ok'])) {
        $out['ok'] = false;
        $out['failed']++;
      } else {
        $out['posted']++;
      }
    }
    return $out;
  }
}

if (!function_exists('billing_subscriber_outstanding')) {
  function billing_subscriber_outstanding(mysqli $conn, int $subscriberId): float {
    $subscriberId = (int)$subscriberId;
    if ($subscriberId <= 0 || !table_exists($conn, 'invoices')) return 0.0;
    $rs = @ $conn->query("SELECT COALESCE(SUM(CASE WHEN COALESCE(remaining_amount,amount)>0 THEN COALESCE(remaining_amount,amount) ELSE 0 END),0) total
      FROM invoices WHERE subscriber_id={$subscriberId} AND LOWER(COALESCE(payment_status,'unpaid'))<>'paid'");
    if (!$rs || !($row = $rs->fetch_assoc())) return 0.0;
    return round((float)($row['total'] ?? 0), 2);
  }
}
?>
