22

✏️ Editing: xxxOpportunitiesController.php

<?php
declare(strict_types=1);
require __DIR__ . '/BaseController.php';

class OppsController extends BaseController {

public function index(): void {
  $u = require_login($this->pdo, $this->config);

// branches for filter (already used in your datatable filters)
  $branches = [];
  if (has_role($u, ['ADMIN','GM_SALES','SALES_MGR'])) {
    $branches = $this->pdo->query("SELECT id, name_en, name_ar, city FROM branches WHERE is_active=1 ORDER BY id")->fetchAll();
  } elseif (!empty($u['branch_id'])) {
    $st = $this->pdo->prepare("SELECT id, name_en, name_ar, city FROM branches WHERE is_active=1 AND id=?");
    $st->execute([(int)$u['branch_id']]);
    $b = $st->fetch();
    if ($b) $branches = [$b];
  }

// branch agents for bulk assignment dropdown
  $agents = [];
  if (has_role($u, ['ADMIN','BR_MGR'])) {
    $branchId = (int)($u['branch_id'] ?? 0);

// For Admin: default to NO agents until a branch is selected (safer)
    // We'll still show dropdown but it will be empty unless BR_MGR (or you add AJAX later)
    if (has_role($u, ['BR_MGR']) && $branchId > 0) {
      $st = $this->pdo->prepare("
        SELECT u.id, u.name, u.mobile
        FROM users u
        JOIN roles r ON r.id=u.role_id
        WHERE r.code='BR_AGENT' AND u.is_active=1 AND u.branch_id=?
        ORDER BY u.name
      ");
      $st->execute([$branchId]);
      $agents = $st->fetchAll();
    }
  }

$this->render('opps/index.php', ['u'=>$u, 'branches'=>$branches, 'agents'=>$agents]);
}

public function agents(): void
{
  $u = require_login($this->pdo, $this->config);

// Only Admin/Branch Manager can bulk assign
  if (!has_role($u, ['ADMIN','BR_MGR'])) {
    http_response_code(403);
    echo "Forbidden";
    exit;
  }

$branchId = (int)($_GET['branch_id'] ?? 0);

// BR_MGR can only request their branch
  if (has_role($u, ['BR_MGR'])) {
    $branchId = (int)($u['branch_id'] ?? 0);
  }

if ($branchId <= 0) {
    header('Content-Type: application/json; charset=utf-8');
    echo json_encode(['agents' => []], JSON_UNESCAPED_UNICODE);
    exit;
  }

$st = $this->pdo->prepare("
    SELECT u.id, u.name, u.mobile
    FROM users u
    JOIN roles r ON r.id=u.role_id
    WHERE r.code='BR_AGENT'
      AND u.is_active=1
      AND u.branch_id=?
    ORDER BY u.name
  ");
  $st->execute([$branchId]);
  $agents = $st->fetchAll(PDO::FETCH_ASSOC);

header('Content-Type: application/json; charset=utf-8');
  echo json_encode(['agents' => $agents], JSON_UNESCAPED_UNICODE);
  exit;
}

private function stageMap(): array
{
  return [
    'open_opportunity' => ['Open'],
    'test_drive' => ['Booked', 'Completed', 'No Show', 'Rescheduled'],
    'quotation_offer' => ['Sent', 'Revised', 'Expired'],
    'finance_processing' => ['Submitted', 'Approved', 'Rejected', 'Pending Documents'],
    'negotiation' => ['In Negotiation', 'Waiting Approval', 'Offer Improved'],
    'booking' => ['Booked', 'Payment Pending', 'Cancelled'],
    'delivery_sale' => ['Closed – Won', 'Delivered'],
    'closed_lost' => ['Price', 'Competitor', 'Finance Rejected', 'No Stock', 'Delay', 'No Response'],
    'on_hold' => ['Follow-up', 'On Hold', 'Future Purchase'],
  ];
}

private function stageLabel(string $key): string
{
  return match ($key) {
    'open_opportunity' => 'Open Opportunity',
    'test_drive' => 'Test Drive',
    'quotation_offer' => 'Quotation / Offer',
    'finance_processing' => 'Finance Processing',
    'negotiation' => 'Negotiation',
    'booking' => 'Booking',
    'delivery_sale' => 'Delivery / Sale',
    'closed_lost' => 'Closed – Lost',
    'on_hold' => 'On Hold / Nurturing',
    default => $key,
  };
}

private function deriveOverallStatus(string $stage, ?string $stageStatus): string
{
  // Overall opportunity status
  if ($stage === 'closed_lost') return 'lost';
  if ($stage === 'on_hold') return 'on_hold';
  if ($stage === 'delivery_sale') return 'won';
  // Everything else stays open
  return 'open';
}

public function view(): void {
    $u = require_login($this->pdo, $this->config);
    $id = (int)($_GET['id'] ?? 0);

$st = $this->pdo->prepare("SELECT
    o.*,
    c.country_code, c.mobile, c.full_name, c.city,
    b.name_en AS branch_name_en, b.name_ar AS branch_name_ar, b.location_url,

l.vehicle_of_interest, l.model_year, l.salary, l.salary_range, l.obligation, l.obligation_amount,
    l.pref_time, l.purchase_time,
    l.qualified_at, l.qualify_note,

qb.name_en AS qualified_branch_name_en,
    qb.name_ar AS qualified_branch_name_ar,
    qb.city AS qualified_branch_city,

lo.name AS lead_owner_name,
    qbuser.name AS qualified_by_name

FROM opportunities o
  JOIN contacts c ON c.id=o.contact_id
  JOIN branches b ON b.id=o.branch_id
  JOIN leads l ON l.id=o.lead_id
  LEFT JOIN branches qb ON qb.id = l.qualified_branch_id
  LEFT JOIN users lo ON lo.id = l.assigned_to_user_id
  LEFT JOIN users qbuser ON qbuser.id = l.qualified_by_user_id
  WHERE o.id=?");
    $st->execute([$id]);
    $opp = $st->fetch();
    if (!$opp) { http_response_code(404); echo "Opportunity not found"; exit; }
    if (!can_view_opp($u, $opp)) { http_response_code(403); echo "Forbidden"; exit; }

// capture first view for assigned branch agent
    if (has_role($u, ['BR_AGENT']) && (int)$opp['assigned_to_user_id'] === (int)$u['id'] && empty($opp['first_view_at'])) {
      $st = $this->pdo->prepare("UPDATE opportunities SET first_view_at=?, status=IF(status='assigned','in_progress',status), updated_at=? WHERE id=? AND first_view_at IS NULL");
      $st->execute([now(), now(), $id]);
      $this->log('opportunity', $id, 'VIEW', $u['id'], ['first_view'=>true]);
      // refresh
      $st = $this->pdo->prepare("SELECT
    o.*,

-- contact info
    c.country_code, c.mobile, c.full_name, c.city,

-- opportunity branch info
    b.name_en AS branch_name_en,
    b.name_ar AS branch_name_ar,
    b.location_url,

-- lead info (same fields you show in lead view)
    l.source AS lead_source,
    l.campaign AS lead_campaign,
    l.vehicle_of_interest,
    l.model_year,
    l.salary,
    l.salary_range,
    l.obligation,
    l.obligation_amount,
    l.pref_time,
    l.purchase_time,

-- qualification info
    l.qualified_at,
    l.qualify_note,
    l.qualified_branch_id,

-- qualified branch name (the branch selected by call center)
    qb.name_en AS qualified_branch_name_en,
    qb.name_ar AS qualified_branch_name_ar,
    qb.city AS qualified_branch_city

// branch agents for assignment (only same branch)
    $agents = [];
    if (has_role($u, ['ADMIN','BR_MGR'])) {
      $st = $this->pdo->prepare("SELECT u.id, u.name, u.mobile
                                 FROM users u
                                 JOIN roles r ON r.id=u.role_id
                                 WHERE r.code='BR_AGENT' AND u.is_active=1 AND u.branch_id=? ORDER BY u.name");
      $st->execute([$opp['branch_id']]);
      $agents = $st->fetchAll();
    }

$st = $this->pdo->prepare("SELECT a.*, u.name actor_name FROM activity_logs a LEFT JOIN users u ON u.id=a.actor_user_id
                               WHERE a.entity_type='opportunity' AND a.entity_id=? ORDER BY a.id DESC LIMIT 100");
    $st->execute([$id]);
    $logs = $st->fetchAll();

$this->render('opps/view.php', [
      'u'=>$u,
      'opp'=>$opp,
      'agents'=>$agents,
      'logs'=>$logs
    ]);
  }

public function assign(): void {
    $u = require_login($this->pdo, $this->config);
    require_post(); csrf_check();

$oppId = (int)($_POST['opp_id'] ?? 0);
    $agentId = (int)($_POST['agent_id'] ?? 0);

$st = $this->pdo->prepare("SELECT o.*, c.country_code, c.mobile, c.full_name, b.location_url,
                                      b.name_en AS branch_name_en, b.name_ar AS branch_name_ar
                               FROM opportunities o
                               JOIN contacts c ON c.id=o.contact_id
                               JOIN branches b ON b.id=o.branch_id
                               WHERE o.id=?");
    $st->execute([$oppId]);
    $opp = $st->fetch();
    if (!$opp) { http_response_code(404); echo "Opportunity not found"; exit; }
    if (!can_assign_opp($u, $opp)) { http_response_code(403); echo "Forbidden"; exit; }

$st = $this->pdo->prepare("UPDATE opportunities SET assigned_to_user_id=?, assigned_by_user_id=?, assigned_at=?, status='assigned', updated_at=? WHERE id=?");
    $st->execute([$agentId ?: null, $u['id'], now(), now(), $oppId]);

// get agent info
    $st = $this->pdo->prepare("SELECT id, name, mobile FROM users WHERE id=?");
    $st->execute([$agentId]);
    $agent = $st->fetch();

// queue SMS to contact (MVP)
    $branchName = is_rtl() ? ($opp['branch_name_ar'] ?? '') : ($opp['branch_name_en'] ?? '');
    $msg = "تم تحويل طلبك إلى {$branchName}. ممثل المبيعات: {$agent['name']} - {$agent['mobile']}. رابط الموقع: " . ($opp['location_url'] ?? '');
    if (!is_rtl()) {
      $msg = "Your opportunity has been assigned to {$branchName}. Sales agent: {$agent['name']} - {$agent['mobile']}. Location: " . ($opp['location_url'] ?? '');
    }

$to = ($opp['country_code'] ?? '+966') . ($opp['mobile'] ?? '');
    $st = $this->pdo->prepare("INSERT INTO sms_outbox(to_number, message, status, provider, related_entity_type, related_entity_id, created_by_user_id, created_at)
                               VALUES(?,?,'queued',NULL,'opportunity',?,?,?)");
    $st->execute([$to, $msg, $oppId, $u['id'], now()]);

$this->log('opportunity', $oppId, 'ASSIGN', $u['id'], ['assigned_to'=>$agentId]);
    $this->log('opportunity', $oppId, 'SMS_QUEUED', $u['id'], ['to'=>$to]);

flash_set('success', t('sms.queued'));
    redirect(base_url($this->config, '/index.php?r=opps/view&id=' . $oppId));
  }

public function contact(): void {
    $u = require_login($this->pdo, $this->config);
    require_post(); csrf_check();

$oppId = (int)($_POST['opp_id'] ?? 0);
    $note = trim($_POST['note'] ?? '');

$st = $this->pdo->prepare("SELECT * FROM opportunities WHERE id=?");
    $st->execute([$oppId]);
    $opp = $st->fetch();
    if (!$opp) { http_response_code(404); echo "Opportunity not found"; exit; }
    if (!can_view_opp($u, $opp)) { http_response_code(403); echo "Forbidden"; exit; }

// only assigned agent should mark contact in MVP (admins/managers can too)
    if (has_role($u, ['BR_AGENT']) && (int)$opp['assigned_to_user_id'] !== (int)$u['id']) {
      http_response_code(403); echo "Forbidden"; exit;
    }

$st = $this->pdo->prepare("UPDATE opportunities SET first_contact_at=COALESCE(first_contact_at, ?),
                               status=IF(status IN ('new','assigned','in_progress'),'contacted',status),
                               updated_at=? WHERE id=?");
    $st->execute([now(), now(), $oppId]);

$this->log('opportunity', $oppId, 'CONTACT_SUCCESS', $u['id'], ['note'=>$note]);
    redirect(base_url($this->config, '/index.php?r=opps/view&id=' . $oppId));
  }

public function update_stage(): void {
  $u = require_login($this->pdo, $this->config);
  require_post(); csrf_check();

$oppId = (int)($_POST['opp_id'] ?? 0);
  $stage = trim((string)($_POST['stage'] ?? ''));
  $stageStatus = trim((string)($_POST['stage_status'] ?? ''));
  $status = trim((string)($_POST['status'] ?? ''));
  $notes = trim((string)($_POST['notes'] ?? ''));

$st = $this->pdo->prepare("SELECT * FROM opportunities WHERE id=?");
  $st->execute([$oppId]);
  $opp = $st->fetch();
  if (!$opp) { http_response_code(404); echo "Opportunity not found"; exit; }
  if (!can_view_opp($u, $opp)) { http_response_code(403); echo "Forbidden"; exit; }

if (empty($opp['first_contact_at'])) {
    flash_set('danger', 'Locked until contacted');
    redirect(base_url($this->config, '/index.php?r=opps/view&id=' . $oppId));
  }

if ($stage === '' || $stageStatus === '' || $status === '' || $notes === '') {
    flash_set('danger', 'Stage, Stage Status, Overall Status and Notes are mandatory.');
    redirect(base_url($this->config, '/index.php?r=opps/view&id=' . $oppId));
  }

$map = $this->stageMap();
  $allowedStages = array_keys($map);

if (!in_array($stage, $allowedStages, true)) {
    flash_set('danger', 'Invalid stage selected.');
    redirect(base_url($this->config, '/index.php?r=opps/view&id=' . $oppId));
  }

$allowedOverallStatuses = ['open','won','lost','on_hold'];
  if (!in_array($status, $allowedOverallStatuses, true)) {
    flash_set('danger', 'Invalid overall status selected.');
    redirect(base_url($this->config, '/index.php?r=opps/view&id=' . $oppId));
  }

$allowedStageStatuses = $map[$stage] ?? ['Open'];
  if (!in_array($stageStatus, $allowedStageStatuses, true)) {
    flash_set('danger', 'Invalid stage status selected.');
    redirect(base_url($this->config, '/index.php?r=opps/view&id=' . $oppId));
  }

$old = [
    'stage' => $opp['stage'],
    'stage_status' => $opp['stage_status'],
    'status' => $opp['status']
  ];

$st = $this->pdo->prepare("
    UPDATE opportunities
    SET stage=?,
        stage_status=?,
        status=?,
        updated_at=?
    WHERE id=?
  ");
  $st->execute([$stage, $stageStatus, $status, now(), $oppId]);

$this->log('opportunity', $oppId, 'UPDATE', $u['id'], [
    'old' => $old,
    'new' => [
      'stage' => $stage,
      'stage_status' => $stageStatus,
      'status' => $status,
      'notes' => $notes
    ]
  ]);

flash_set('success', 'Updated');
  redirect(base_url($this->config, '/index.php?r=opps/view&id=' . $oppId));
}
 
 public function datatables(): void
{
  $u = require_login($this->pdo, $this->config);

$draw   = (int)($_GET['draw'] ?? 1);
  $start  = max(0, (int)($_GET['start'] ?? 0));
  $length = (int)($_GET['length'] ?? 25);
  if ($length <= 0 || $length > 200) $length = 25;

$sf = $_SESSION['opps_filters'] ?? [];

$q = trim($_GET['q'] ?? ($sf['q'] ?? ''));

$createdRange  = trim($_GET['created_range'] ?? ($sf['created_range'] ?? ''));
$assignedRange = trim($_GET['assigned_range'] ?? ($sf['assigned_range'] ?? ''));

$status = trim($_GET['status'] ?? ($sf['status'] ?? ''));
$stage  = trim($_GET['stage'] ?? ($sf['stage'] ?? ''));

$allowedStatuses = ['open','won','lost','on_hold'];
if ($status !== '' && !in_array($status, $allowedStatuses, true)) {
  $status = '';
}

$allowedStages = [
  'open_opportunity',
  'test_drive',
  'quotation_offer',
  'finance_processing',
  'negotiation',
  'booking',
  'delivery_sale',
  'closed_lost',
  'on_hold'
];
if ($stage !== '' && !in_array($stage, $allowedStages, true)) {
  $stage = '';
}

$branchId = (int)($_GET['branch_id'] ?? ($sf['branch_id'] ?? 0));

// ordering
  $orderCol = (int)($_GET['order'][0]['column'] ?? 0);
  $orderDir = strtolower($_GET['order'][0]['dir'] ?? 'desc') === 'asc' ? 'ASC' : 'DESC';

// Column whitelist map (must match DataTables column order)
  $colMap = [
    0 => 'o.id',
    1 => 'c.mobile',
    2 => 'c.full_name',
    3 => 'b.name_en',
    4 => 'o.stage',
    5 => 'o.stage_status',
    6 => 'o.status',
    7 => 'o.assigned_at',
    8 => 'o.created_at',
  ];
  $orderBy = $colMap[$orderCol] ?? 'o.id';

// scope restrictions
  $where = "1=1";
  $params = [];

if (has_role($u, ['BR_MGR'])) {
    $where .= " AND o.branch_id=?";
    $params[] = (int)$u['branch_id'];
  } elseif (has_role($u, ['BR_AGENT'])) {
    $where .= " AND o.assigned_to_user_id=?";
    $params[] = (int)$u['id'];
  } elseif (!has_role($u, ['ADMIN','GM_SALES','SALES_MGR'])) {
    $where .= " AND 1=0";
  }

// search
  if ($q !== '') {
    $where .= " AND (c.mobile LIKE ? OR c.full_name LIKE ? OR o.stage_status LIKE ?)";
    $like = '%' . $q . '%';
    $params = array_merge($params, [$like, $like, $like]);
  }

// filters
  if ($status !== '') {
    $where .= " AND o.status = ?";
    $params[] = $status;
  }

if ($stage !== '') {
    $where .= " AND o.stage = ?";
    $params[] = $stage;
  }

if ($branchId > 0) {
    // Only allow branch filter for Admin/GM/Sales Mgr. Branch roles already scoped.
    if (has_role($u, ['ADMIN','GM_SALES','SALES_MGR'])) {
      $where .= " AND o.branch_id = ?";
      $params[] = $branchId;
    }
  }

// Opportunity Creation Date filter (o.created_at)
  if ($createdRange !== '') {
    if ($createdRange === 'today') {
      $where .= " AND DATE(o.created_at) = CURDATE()";
    } elseif ($createdRange === 'yesterday') {
      $where .= " AND DATE(o.created_at) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)";
    } elseif ($createdRange === 'this_week') {
      $where .= " AND YEARWEEK(o.created_at, 1) = YEARWEEK(CURDATE(), 1)";
    } elseif ($createdRange === 'this_month') {
      $where .= " AND YEAR(o.created_at)=YEAR(CURDATE()) AND MONTH(o.created_at)=MONTH(CURDATE())";
    } elseif ($createdRange === 'prev_month') {
      $where .= " AND YEAR(o.created_at)=YEAR(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))
                  AND MONTH(o.created_at)=MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))";
    }
  }

// Assigned Date filter (o.assigned_at)
  if ($assignedRange !== '') {
    $where .= " AND o.assigned_at IS NOT NULL";
    if ($assignedRange === 'today') {
      $where .= " AND DATE(o.assigned_at) = CURDATE()";
    } elseif ($assignedRange === 'yesterday') {
      $where .= " AND DATE(o.assigned_at) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)";
    } elseif ($assignedRange === 'this_week') {
      $where .= " AND YEARWEEK(o.assigned_at, 1) = YEARWEEK(CURDATE(), 1)";
    } elseif ($assignedRange === 'this_month') {
      $where .= " AND YEAR(o.assigned_at)=YEAR(CURDATE()) AND MONTH(o.assigned_at)=MONTH(CURDATE())";
    } elseif ($assignedRange === 'prev_month') {
      $where .= " AND YEAR(o.assigned_at)=YEAR(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))
                  AND MONTH(o.assigned_at)=MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))";
    }
  }

// totals
  $whereTotal = "1=1";
  $paramsTotal = [];

if (has_role($u, ['BR_MGR'])) {
    $whereTotal .= " AND o.branch_id=?";
    $paramsTotal[] = (int)$u['branch_id'];
  } elseif (has_role($u, ['BR_AGENT'])) {
    $whereTotal .= " AND o.assigned_to_user_id=?";
    $paramsTotal[] = (int)$u['id'];
  } elseif (!has_role($u, ['ADMIN','GM_SALES','SALES_MGR'])) {
    $whereTotal .= " AND 1=0";
  }

$st = $this->pdo->prepare("
    SELECT COUNT(*) cnt
    FROM opportunities o
    JOIN contacts c ON c.id = o.contact_id
    JOIN branches b ON b.id = o.branch_id
    WHERE $whereTotal
  ");
  $st->execute($paramsTotal);
  $recordsTotal = (int)$st->fetchColumn();

$st = $this->pdo->prepare("
    SELECT COUNT(*) cnt
    FROM opportunities o
    JOIN contacts c ON c.id = o.contact_id
    JOIN branches b ON b.id = o.branch_id
    WHERE $where
  ");
  $st->execute($params);
  $recordsFiltered = (int)$st->fetchColumn();

// data page
  $sql = "
    SELECT
      o.id,
      c.mobile,
      c.full_name,
      o.branch_id,
      b.name_en AS branch_name_en,
      b.name_ar AS branch_name_ar,
      o.stage,
      o.stage_status,
      o.status,
      o.assigned_at,
      o.created_at
    FROM opportunities o
    JOIN contacts c ON c.id = o.contact_id
    JOIN branches b ON b.id = o.branch_id
    WHERE $where
    ORDER BY $orderBy $orderDir
    LIMIT $length OFFSET $start
  ";
  $st = $this->pdo->prepare($sql);
  $st->execute($params);
  $data = $st->fetchAll(PDO::FETCH_ASSOC);

header('Content-Type: application/json; charset=utf-8');
  echo json_encode([
    'draw' => $draw,
    'recordsTotal' => $recordsTotal,
    'recordsFiltered' => $recordsFiltered,
    'data' => $data,
  ], JSON_UNESCAPED_UNICODE);
  exit;
}

public function save_filters(): void
{
  $u = require_login($this->pdo, $this->config);
  require_post(); csrf_check();

$_SESSION['opps_filters'] = [
    'q'             => trim($_POST['q'] ?? ''),
    'created_range' => trim($_POST['created_range'] ?? ''),
    'assigned_range'=> trim($_POST['assigned_range'] ?? ''),
    'status'        => trim($_POST['status'] ?? ''),
    'stage'         => trim($_POST['stage'] ?? ''),
    'branch_id'     => (int)($_POST['branch_id'] ?? 0),
  ];

header('Content-Type: application/json; charset=utf-8');
  echo json_encode(['ok' => true], JSON_UNESCAPED_UNICODE);
  exit;
}

public function reset_filters(): void
{
  $u = require_login($this->pdo, $this->config);
  require_post(); csrf_check();

unset($_SESSION['opps_filters']);

header('Content-Type: application/json; charset=utf-8');
  echo json_encode(['ok' => true], JSON_UNESCAPED_UNICODE);
  exit;
}

public function bulk_assign(): void
{
  $u = require_login($this->pdo, $this->config);
  if (!has_role($u, ['ADMIN','BR_MGR'])) { http_response_code(403); echo "Forbidden"; exit; }
  require_post(); csrf_check();

$agentId = (int)($_POST['agent_id'] ?? 0);
  $oppIds  = $_POST['opp_ids'] ?? [];

if ($agentId <= 0) {
    flash_set('danger', 'Select an agent');
    redirect(base_url($this->config, '/index.php?r=opps/index'));
  }

$ids = [];
  foreach ($oppIds as $id) {
    $id = (int)$id;
    if ($id > 0) $ids[] = $id;
  }
  $ids = array_values(array_unique($ids));

if (!$ids) {
    flash_set('danger', 'Select at least one opportunity');
    redirect(base_url($this->config, '/index.php?r=opps/index'));
  }

// BR_MGR can only assign within their branch
  if (has_role($u, ['BR_MGR']) && empty($u['branch_id'])) {
    flash_set('danger', 'Branch missing for manager');
    redirect(base_url($this->config, '/index.php?r=opps/index'));
  }

$placeholders = implode(',', array_fill(0, count($ids), '?'));

$this->pdo->beginTransaction();
  try {
    // Load selected opportunities + contact + branch for SMS
    $sql = "
      SELECT o.id, o.branch_id, o.contact_id, c.country_code, c.mobile,
             b.location_url, b.name_en AS branch_name_en, b.name_ar AS branch_name_ar
      FROM opportunities o
      JOIN contacts c ON c.id=o.contact_id
      JOIN branches b ON b.id=o.branch_id
      WHERE o.id IN ($placeholders)
    ";
    $st = $this->pdo->prepare($sql);
    $st->execute($ids);
    $opps = $st->fetchAll(PDO::FETCH_ASSOC);

// Enforce branch scope for BR_MGR
    if (has_role($u, ['BR_MGR'])) {
      foreach ($opps as $o) {
        if ((int)$o['branch_id'] !== (int)$u['branch_id']) {
          throw new RuntimeException('You can only assign opportunities within your branch.');
        }
      }
    }

// Update assignments
    $params = array_merge([$agentId, (int)$u['id'], now(), now()], $ids);
    $st = $this->pdo->prepare("
      UPDATE opportunities
      SET assigned_to_user_id=?,
          assigned_by_user_id=?,
          assigned_at=?,
          status='assigned',
          updated_at=?
      WHERE id IN ($placeholders)
        AND status IN ('new','assigned','in_progress','contacted')
    ");
    $st->execute($params);
    $affected = $st->rowCount();

// Get agent info
    $st = $this->pdo->prepare("SELECT id, name, mobile FROM users WHERE id=?");
    $st->execute([$agentId]);
    $agent = $st->fetch();
    if (!$agent) throw new RuntimeException('Agent not found');

// Queue SMS + log for each opp
    foreach ($opps as $o) {
      $branchName = is_rtl() ? ($o['branch_name_ar'] ?? '') : ($o['branch_name_en'] ?? '');
      $msg = "تم تحويل طلبك إلى {$branchName}. ممثل المبيعات: {$agent['name']} - {$agent['mobile']}. رابط الموقع: " . ($o['location_url'] ?? '');
      if (!is_rtl()) {
        $msg = "Your opportunity has been assigned to {$branchName}. Sales agent: {$agent['name']} - {$agent['mobile']}. Location: " . ($o['location_url'] ?? '');
      }

$to = ($o['country_code'] ?? '+966') . ($o['mobile'] ?? '');

$st = $this->pdo->prepare("
        INSERT INTO sms_outbox(to_number, message, status, provider, related_entity_type, related_entity_id, created_by_user_id, created_at)
        VALUES(?,?,'queued',NULL,'opportunity',?,?,?)
      ");
      $st->execute([$to, $msg, (int)$o['id'], (int)$u['id'], now()]);

$this->log('opportunity', (int)$o['id'], 'ASSIGN_BULK', (int)$u['id'], ['assigned_to'=>$agentId]);
      $this->log('opportunity', (int)$o['id'], 'SMS_QUEUED', (int)$u['id'], ['to'=>$to]);
    }

$this->pdo->commit();
    flash_set('success', "Bulk assigned: $affected opportunities");
  } catch (Throwable $e) {
    $this->pdo->rollBack();
    flash_set('danger', 'Error: ' . $e->getMessage());
  }

redirect(base_url($this->config, '/index.php?r=opps/index'));
}

}

MR SKB