<?php defined('BASEPATH') or exit('No direct script access allowed');
$is_admin = is_admin();
$i = 0;

// 1. Collect all lead IDs across all statuses
$all_leads_by_status = [];
$all_lead_ids = [];

foreach ($statuses as $status) {
    $kanBan = new \app\services\leads\LeadsKanban($status['id']);
    if (isset($filters)) {
        $kanBan->setCustomFilters($filters);
    }
    $kanBan->search($this->input->get('search'));
    $sortBy = $this->input->get('sort_by');
    $sortOrder = $this->input->get('sort') ?? 'asc';

    if ($sortBy === 'name') {
        $this->db->order_by("SUBSTRING_INDEX(lead_name, ' ', -1)", $sortOrder, false);
    } else {
        $kanBan->sortBy($sortBy, $sortOrder);
    }
    if ($this->input->get('refresh')) {
        $kanBan->refresh($this->input->get('refresh')[$status['id']] ?? null);
    }
    $leads = $kanBan->get();
    $all_leads_by_status[$status['id']] = [
        'leads' => $leads,
        'total_leads' => count($leads),
        'total_pages' => $kanBan->totalPages(),
        'kanBan' => $kanBan,
        'status' => $status,
    ];
    foreach ($leads as $lead) {
        $all_lead_ids[] = $lead['id'];
    }
}

$all_lead_ids = array_unique($all_lead_ids);

// 2. Run all required queries ONCE
$leads_map = [];
$groups_map = [];
$brokerage_map = [];
$bank_map = [];
$transaction_map = [];
$reminder_map = [];
$task_map = [];
$notification_map = [];
$broker_notification_map = [];
$instructions_map = [];

if (!empty($all_lead_ids)) {
    $this->db->select('id, name, assign_group, multiple_cases, status, secondary_name, transaction, brokerage_id, lender_ids, app_email_requested, active_case, addedfrom, upload_doc');
    $this->db->where_in('id', $all_lead_ids);
    $leads_data = $this->db->get(db_prefix() . 'leads')->result_array();
    $leads_map = array_column($leads_data, null, 'id');

    $group_names = array_filter(array_unique(array_column($leads_data, 'assign_group')));
    if (!empty($group_names)) {
        $this->db->select('group_name, group_color');
        $this->db->where_in('group_name', $group_names);
        $groups_data = $this->db->get(db_prefix() . 'group')->result_array();
        $groups_map = array_column($groups_data, 'group_color', 'group_name');
    }

    $brokerage_ids = array_filter(array_unique(array_column($leads_data, 'brokerage_id')));
    if (!empty($brokerage_ids)) {
        $this->db->select('id, name, color');
        $this->db->where_in('id', $brokerage_ids);
        $brokerage_data = $this->db->get(db_prefix() . 'brokerage')->result_array();
        $brokerage_map = array_column($brokerage_data, null, 'id');
    }

    $this->db->select('lead_id, bank_id, transaction_id');
    $this->db->where_in('lead_id', $all_lead_ids);
    $bank_details = $this->db->get(db_prefix() . 'leads_bank_details')->result_array();
    foreach ($bank_details as $bank) {
        $bank_map[$bank['lead_id']] = $bank;
    }

    $transaction_ids = [];
    foreach ($bank_details as $bank) {
        if (!empty($bank['transaction_id'])) {
            $transaction_ids = array_merge($transaction_ids, explode(',', $bank['transaction_id']));
        }
    }
    $transaction_ids = array_unique($transaction_ids);
    if (!empty($transaction_ids)) {
        $this->db->select('id, name');
        $this->db->where_in('id', $transaction_ids);
        $transactions = $this->db->get(db_prefix() . 'leads_transaction_details')->result_array();
        $transaction_map = array_column($transactions, 'name', 'id');
    }

    $this->db->select('rel_id, COUNT(id) as reminder_count');
    $this->db->where_in('rel_id', $all_lead_ids);
    $this->db->group_by('rel_id');
    $reminders = $this->db->get(db_prefix() . 'reminders')->result_array();
    $reminder_map = array_column($reminders, 'reminder_count', 'rel_id');

    $this->db->select('rel_id, COUNT(id) as task_count');
    $this->db->where_in('rel_id', $all_lead_ids);
    $this->db->where('rel_type', 'user-task');
    $this->db->group_by('rel_id');
    $tasks = $this->db->get(db_prefix() . 'tasks')->result_array();
    $task_map = array_column($tasks, 'task_count', 'rel_id');

    $this->db->select('lead_id, COUNT(id) as notification_count');
    $this->db->from(db_prefix() . 'notifications');
    $this->db->where_in('lead_id', $all_lead_ids);
    $this->db->where('isread', '0');
    $this->db->where('touserid', get_staff_user_id());
    $this->db->where_in('additional_data', ['user', 'broker', 'client']);
    $this->db->group_by('lead_id');
    $notifications = $this->db->get()->result_array();
    $notification_map = array_column($notifications, 'notification_count', 'lead_id');

    // $this->db->select('lead_id, COUNT(id) as broker_counter');
    // $this->db->from(db_prefix() . 'read_comments');
    // $this->db->where_in('lead_id', $all_lead_ids);
    // $this->db->where('to_user_id', get_staff_user_id());
    // $this->db->where('case_is_read', 0);
    // $this->db->where('type', 'broker-to-ld');
    // $this->db->group_by('lead_id');
    // $broker_counter = $this->db->get()->result_array();
    // $broker_notification_map = array_column($broker_counter, 'broker_counter', 'lead_id');

    $this->db->select('lead_id, COUNT(id) as instructions_count');
    $this->db->where('stage_id', 2);
    $this->db->where_in('lead_id', $all_lead_ids);
    $this->db->group_by('lead_id');
    $instructions = $this->db->get(db_prefix() . 'instruction')->result_array();
    $instructions_map = array_column($instructions, 'instructions_count', 'lead_id');
}

// Render original Kanban with optimized maps
foreach ($all_leads_by_status as $status_id => $data) {
    $status = $data['status'];
    $leads = $data['leads'];
    $total_leads = $data['total_leads'];
    $total_pages = $data['total_pages'];
    $kanBan = $data['kanBan'];

    include(APPPATH . 'views/admin/leads/_kanban_column.php');
} ?>
