<?php

namespace Modules\Leads\Datatables;

use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Str;
use Modules\Contacts\Entities\CustomerLanguage;
use Modules\Leads\Entities\Lead;
use Modules\Leads\Entities\LeadIndustry;
use Modules\Leads\Entities\LeadRating;
use Modules\Leads\Entities\LeadSource;
use Modules\Leads\Entities\LeadStatus;
use Modules\Leads\Service\LeadFieldSettingsService;
use Modules\Platform\Core\Datatable\PlatformDataTable;
use Modules\Platform\Core\Helper\DataTableHelper;
use Modules\Platform\Core\Helper\UserHelper;
use Modules\Platform\Core\QueryBuilderParser\QueryBuilderParser;
use Modules\Platform\Settings\Entities\Country;
use Yajra\DataTables\EloquentDataTable;
use Modules\Leads\Entities\LeadMultiSelect;


/**
 * Class LeadsDatatable
 * @package Modules\Leads\Datatables
 */
class LeadsDatatable extends PlatformDataTable
{
    const SHOW_URL_ROUTE = 'leads.leads.show';

    /**
     * Maximum rows a single listing request may fetch. The DataTables length
     * menu ships with values up to 1.000.000 rows, which freezes the instance
     * on large tables. Full data sets are exported through the lead export
     * endpoint (''leads.leads.export'') instead.
     */
    const MAX_PAGE_LENGTH = 1000;

    const PAGE_LENGTH_MENU = [10, 25, 50, 100, 250, 500, 1000];

    /** Rows per batch when streaming the export. */
    const EXPORT_CHUNK_SIZE = 2000;

    /**
     * Maximum number of ids that are bound as placeholders in an "IN (..)"
     * filter. MySQL refuses prepared statements with more than 65.535
     * placeholders ("General error: 1390"), and the email / multi select
     * filters resolve one id per matching lead - with 200k leads and a broad
     * search value that limit is reached easily. Above this bound the ids are
     * inlined into the SQL instead (they are integers coming from the
     * database, so this is safe).
     */
    const MAX_BOUND_ID_FILTER = 10000;

    private const CUSTOM_COLUMN_PREFIX = 'custom__';

    /**
     * Memoized lead ids for filter values that are resolved through another
     * table (emails, multi selects), keyed by filter and value.
     *
     * @var array
     */
    private static $filterLeadIdCache = [];

    /**
     * Compiled export cell renderers, keyed by column.
     *
     * @var array
     */
    private static $exportRendererCache = [];

    /**
     * Formats + timezones used by the export date rendering.
     *
     * @var array|null
     */
    private static $exportDateFormats;

    protected $maxPageLength = self::MAX_PAGE_LENGTH;

    /**
     * Columns that take part in the global (search box) search.
     */
    private const GLOBAL_SEARCH_COLUMNS = [
        'leads.first_name',
        'leads.last_name',
        'leads.full_name',
        'leads.email',
        'leads.secondary_email',
        'leads.phone',
        'leads.mobile',
        'leads.lead_company',
        'leads.job_title',
        'leads.addr_city',
        'leads.addr_state',
        'leads.addr_street',
        'leads.addr_zip',
        'leads_dict_status.name',
        'leads_dict_source.name',
        'leads_dict_industry.name',
        'leads_dict_rating.name',
    ];

    protected $editRoute = 'leads.leads.edit';

    private static function shouldIncludeCustomIndexColumns()
    {
        if (!Auth::check() || !Schema::hasColumn('leads', 'custom_fields')) {
            return false;
        }

        $routeName = optional(request()->route())->getName();

        return is_string($routeName) && Str::startsWith($routeName, 'leads.leads.');
    }

    private static function customIndexFields()
    {
        if (!self::shouldIncludeCustomIndexColumns()) {
            return [];
        }

        $service = app(LeadFieldSettingsService::class);
        $customFields = $service->getVisibleCustomFieldsForIndex(Auth::user());
        $result = [];

        foreach ($customFields as $field) {
            $key = (string) ($field['key'] ?? '');

            if ($key === '' || !preg_match('/^[A-Za-z0-9_]+$/', $key)) {
                continue;
            }

            $column = self::CUSTOM_COLUMN_PREFIX . $key;

            $result[$column] = [
                'column' => $column,
                'key' => $key,
                'label' => (string) ($field['label'] ?? Str::title(str_replace('_', ' ', $key))),
                'type' => (string) ($field['type'] ?? 'text'),
                'uses_column' => !empty($field['uses_column']),
            ];
        }

        return $result;
    }

    private static function customIndexColumns()
    {
        $columns = [];

        foreach (self::customIndexFields() as $field) {
            $dataType = 'text';

            if ($field['type'] === 'email') {
                $dataType = 'email';
            } elseif ($field['type'] === 'date') {
                $dataType = 'date';
            } elseif ($field['type'] === 'datetime') {
                $dataType = 'datetime';
            }

            $columns[$field['column']] = [
                'data' => $field['column'],
                'title' => $field['label'],
                'data_type' => $dataType,
                'filter_type' => DataTableHelper::isDateCustomFieldType($field['type'])
                    ? 'bap_date_range_picker'
                    : 'text',
                'searchable' => true,
            ];
        }

        return $columns;
    }

public static function availableQueryFilters()
    {
        $labelMap = [];
        if (Auth::check() && Schema::hasTable('lead_user_field_settings')) {
            $service = app(LeadFieldSettingsService::class);
            $labelMap = $service->getFieldLabelMap(Auth::user());
        }

        $getLabel = function ($fieldKey, $defaultTranslation) use ($labelMap) {
            return $labelMap[$fieldKey] ?? trans($defaultTranslation);
        };

        return [
            [
                'id' => 'leads.full_name',
                'label' => $getLabel('full_name', 'leads::leads.form.full_name'),
                'type' => 'string',
            ],
            [
                'id' => 'lead_email.email',
                'label' => $getLabel('email', 'leads::leads.form.email'),
                'type' => 'string',
            ],
            [
                'id' => 'leads.capture_date',
                'label' => $getLabel('capture_date', 'leads::leads.form.capture_date'),
                'type' => 'date',
                'input_event' => 'dp.change',
                'plugin' => 'datetimepicker',
                'plugin_config' => [
                    'locale' => app()->getLocale(),
                    'calendarWeeks' => true,
                    'showClear' => true,
                    'showTodayButton' => true,
                    'showClose' => true,
                    'format' => \Modules\Platform\Core\Helper\UserHelper::userJsDateFormat()
                ]
            ],
            [
                'id' => 'leads.birth_date',
                'label' => $getLabel('birth_date', 'leads::leads.form.birth_date'),
                'type' => 'date',
                'input_event' => 'dp.change',
                'plugin' => 'datetimepicker',
                'plugin_config' => [
                    'locale' => app()->getLocale(),
                    'calendarWeeks' => true,
                    'showClear' => true,
                    'showTodayButton' => true,
                    'showClose' => true,
                    'format' => \Modules\Platform\Core\Helper\UserHelper::userJsDateFormat()
                ]
            ],
            [
                'id' => 'leads.secondary_email',
                'label' => $getLabel('secondary_email', 'leads::leads.form.secondary_email'),
                'type' => 'string'
            ],
            [
                'id' => 'leads.lead_company',
                'label' => $getLabel('lead_company', 'leads::leads.form.company'),
                'type' => 'string'
            ],
            [
                'id' => 'leads.created_at',
                'label' => $getLabel('created_at', 'leads::leads.form.created_at'),
                'type' => 'date',
                'input_event' => 'dp.change',
                'plugin' => 'datetimepicker',
                'plugin_config' => [
                    'locale' => app()->getLocale(),
                    'calendarWeeks' => true,
                    'showClear' => true,
                    'showTodayButton' => true,
                    'showClose' => true,
                    'format' => \Modules\Platform\Core\Helper\UserHelper::userJsDateFormat()
                ]
            ],
            [
                'id' => 'leads.updated_at',
                'label' => $getLabel('updated_at', 'leads::leads.form.updated_at'),
                'type' => 'date',
                'input_event' => 'dp.change',
                'plugin' => 'datetimepicker',
                'plugin_config' => [
                    'locale' => app()->getLocale(),
                    'calendarWeeks' => true,
                    'showClear' => true,
                    'showTodayButton' => true,
                    'showClose' => true,
                    'format' => \Modules\Platform\Core\Helper\UserHelper::userJsDateFormat()
                ]
            ],
            [
                'id' => 'leads.annual_revenue',
                'label' => $getLabel('annual_revenue', 'leads::leads.form.annual_revenue'),
                'type' => 'double',
            ],
            [
                'id' => 'leads.no_of_employees',
                'label' => $getLabel('no_of_employees', 'leads::leads.form.no_of_employees'),
                'type' => 'integer',
            ],
            [
                'id' => 'leads.phone',
                'label' => $getLabel('phone', 'leads::leads.form.phone'),
                'type' => 'string'
            ],
            [
                'id' => 'leads.mobile',
                'label' => $getLabel('mobile', 'leads::leads.form.mobile'),
                'type' => 'string'
            ],
            [
                'id' => 'leads.fax',
                'label' => $getLabel('fax', 'leads::leads.form.fax'),
                'type' => 'string'
            ],
            [
                'id' => 'leads.twitter',
                'label' => $getLabel('twitter', 'leads::leads.form.twitter'),
                'type' => 'string'
            ],
            [
                'id' => 'leads.facebook',
                'label' => $getLabel('facebook', 'leads::leads.form.facebook'),
                'type' => 'string'
            ],
            [
                'id' => 'leads.skype',
                'label' => $getLabel('skype', 'leads::leads.form.skype'),
                'type' => 'string'
            ],
            [
                'id' => 'leads.website',
                'label' => $getLabel('website', 'leads::leads.form.website'),
                'type' => 'string'
            ],
            [
                'id' => 'leads.addr_street',
                'label' => $getLabel('addr_street', 'leads::leads.form.addr_street'),
                'type' => 'string'
            ]
            ,
            [
                'id' => 'leads.addr_state',
                'label' => $getLabel('addr_state', 'leads::leads.form.addr_state'),
                'type' => 'string'
            ],
            [
                'id' => 'leads.addr_country_id',
                'label' => $getLabel('addr_country_id', 'leads::leads.form.addr_country_id'),
                'type' => 'integer',
                'input' => 'select',
                'multiple' => true,
                'plugin' => 'select2',
                'plugin_config' => [
                    'multiple' => 'multiple',
                    'width' => '300px',
                ],
                'values' => Country::pluck('name', 'id'),
                'operators' => [
                    'in',
                    'not_in',
                    'is_null',
                    'is_not_null'
                ]
            ],
            [
                'id' => 'bap_country.continent',
                'label' => $getLabel('continent', 'leads::leads.form.continent'),
                'type' => 'string',
                'input' => 'select',
                'multiple' => true,
                'plugin' => 'select2',
                'plugin_config' => [
                    'multiple' => 'multiple',
                    'width' => '300px',
                ],
                'values' => Country::pluck('continent', 'continent'),
                'operators' => [
                    'in',
                    'not_in',
                    'is_null',
                    'is_not_null'
                ]
            ],
            [
                'id' => 'leads.addr_city',
                'label' => $getLabel('addr_city', 'leads::leads.form.addr_city'),
                'type' => 'string'
            ],
            [
                'id' => 'leads.addr_zip',
                'label' => $getLabel('addr_zip', 'leads::leads.form.addr_zip'),
                'type' => 'string'
            ],
            [
                'id' => 'leads.job_title',
                'label' => $getLabel('job_title', 'leads::leads.form.job_title'),
                'type' => 'string'
            ],
            [
                'id' => 'leads.description',
                'label' => $getLabel('description', 'leads::leads.form.description'),
                'type' => 'string'
            ],
            [
                'id' => 'leads.lead_status_id',
                'label' => $getLabel('lead_status_id', 'leads::leads.form.lead_status_id'),
                'type' => 'integer',
                'input' => 'select',
                'multiple' => true,
                'plugin' => 'select2',
                'plugin_config' => [
                    'multiple' => 'multiple',
                    'width' => '300px',
                ],
                'values' => LeadStatus::pluck('name', 'id'),
                'operators' => [
                    'in',
                    'not_in',
                    'is_null',
                    'is_not_null'
                ]
            ],
            [
                'id' => 'leads.language_id',
                'label' => $getLabel('language_id', 'leads::leads.form.language_id'),
                'type' => 'integer',
                'input' => 'select',
                'multiple' => true,
                'plugin' => 'select2',
                'plugin_config' => [
                    'multiple' => 'multiple',
                    'width' => '300px',
                ],
                'values' => CustomerLanguage::pluck('name', 'id'),
                'operators' => [
                    'in',
                    'not_in',
                    'is_null',
                    'is_not_null'
                ]
            ],
            [
                'id' => 'leads.lead_source_id',
                'label' => $getLabel('lead_source_id', 'leads::leads.form.lead_source_id'),
                'type' => 'integer',
                'input' => 'select',
                'multiple' => true,
                'plugin' => 'select2',
                'plugin_config' => [
                    'multiple' => 'multiple',
                    'width' => '300px',
                ],
                'values' => LeadSource::pluck('name', 'id'),
                'operators' => [
                    'in',
                    'not_in',
                    'is_null',
                    'is_not_null'
                ]
            ],
            [
                'id' => 'leads.lead_industry_id',
                'label' => $getLabel('lead_industry_id', 'leads::leads.form.lead_industry_id'),
                'type' => 'integer',
                'input' => 'select',
                'multiple' => true,
                'plugin' => 'select2',
                'plugin_config' => [
                    'multiple' => 'multiple',
                    'width' => '300px',
                ],
                'values' => LeadIndustry::pluck('name', 'id'),
                'operators' => [
                    'in',
                    'not_in',
                    'is_null',
                    'is_not_null'
                ]
            ],
            [
                'id' => 'leads.lead_rating_id',
                'label' => $getLabel('lead_rating_id', 'leads::leads.form.lead_rating_id'),
                'type' => 'integer',
                'input' => 'select',
                'multiple' => true,
                'plugin' => 'select2',
                'plugin_config' => [
                    'multiple' => 'multiple',
                    'width' => '300px',
                ],
                'values' => LeadRating::pluck('name', 'id'),
                'operators' => [
                    'in',
                    'not_in',
                    'is_null',
                    'is_not_null'
                ]
                ],
                [
                'id' => 'lead_multi_select.multi_select_id',
                'label' => $getLabel('multiSelects', 'leads::leads.form.multiSelects'),
                'type' => 'string',
                'input' => 'select',
                'multiple' => true,
                'plugin' => 'select2',
                'plugin_config' => [
                    'multiple' => 'multiple',
                    'width' => '300px',
                ],
                'values' => LeadMultiSelect::pluck('name', 'id'),
                'operators' => [
                    'in',
                    'not_in',
                    'is_null',
                    'is_not_null'
                ]
            ],

        ];
    }

    /**
     * Maps datatable column keys to their corresponding field settings keys
     * when the names differ (e.g. 'source' column → 'lead_source_id' field).
     */
    private static $columnToFieldMap = [
        'source'        => 'lead_source_id',
        'status'        => 'lead_status_id',
        'industry'      => 'lead_industry_id',
        'rating'        => 'lead_rating_id',
        'multiSelects'  => 'multiSelects',
        'owner'         => 'owned_by',
        'addr_country'  => 'addr_country_id',
        'language'      => 'language_id',
    ];

    /**
     * Resolve the field settings key for a given datatable column key.
     */
    private static function fieldKeyForColumn($columnKey)
    {
        return self::$columnToFieldMap[$columnKey] ?? $columnKey;
    }

    public static function availableColumns()
    {

        if(auth()->user()->cannot('crm.advanced')){
            $allColumns = [
                'full_name' => [
                    'data' => 'full_name',
                    'title' => trans('leads::leads.form.name'),
                    'data_type' => 'text',
                    'filter_type' => 'text',
                ],
                'phone' => [
                    'data' => 'phone',
                    'title' => trans('leads::leads.form.phone'),
                    'data_type' => 'text',
                    'filter_type' => 'text'
                ],
                'mobile' => [
                    'data' => 'mobile',
                    'title' => trans('leads::leads.form.mobile'),
                    'data_type' => 'text',
                    'filter_type' => 'text'
                ],
                'created_at' => [
                    'data' => 'created_at',
                    'title' => trans('leads::leads.form.created_at'),
                    'data_type' => 'datetime',
                    'filter_type' => 'bap_date_range_picker',
                ],
                'owner' => [
                    'data' => 'owner',
                    'title' => trans('core::core.table.assigned_to'),
                    'data_type' => 'assigned_to',
                    'orderable' => false,
                    'column_name' => 'assigned_to',
                    'filter_type' => 'select',
                    'select_type' => 'select2',
                    'select_type_options' => [
                        'theme' => "bootstrap",
                        'width' => '100%'
                    ],
                    'filter_data' => DataTableHelper::filterOwnerDropdown()
                ]
            ];
        }else{
            $allColumns = [
                'id' => [
                    'data' => 'id',
                    'title' => trans('leads::leads.form.id'),
                    'data_type' => 'text',
                    'filter_type' => 'text',
                ],
                'full_name' => [
                    'data' => 'full_name',
                    'title' => trans('leads::leads.form.name'),
                    'data_type' => 'text',
                    'filter_type' => 'text',
                ],
                'phone' => [
                    'data' => 'phone',
                    'title' => trans('leads::leads.form.phone'),
                    'data_type' => 'text',
                    'filter_type' => 'text'
                ],
                'mobile' => [
                    'data' => 'mobile',
                    'title' => trans('leads::leads.form.mobile'),
                    'data_type' => 'text',
                    'filter_type' => 'text'
                ],
                'email' => [
                    'data' => 'email',
                    'title' => trans('leads::leads.form.email'),
                    'data_type' => 'text',
                    'filter_type' => 'text'
                ],
                'capture_date' => [
                    'data' => 'capture_date',
                    'title' => trans('leads::leads.form.capture_date'),
                    'data_type' => 'datetime',
                    'filter_type' => 'bap_date_range_picker'
                ],
                'birth_date' => [
                    'data' => 'birth_date',
                    'title' => trans('leads::leads.form.birth_date'),
                    'data_type' => 'date',
                    'filter_type' => 'bap_date_range_picker'
                ],
                'source' => [
                    'name' => 'leads_dict_source.name',
                    'data' => 'source',
                    'title' => trans('leads::leads.form.source'),
                    'data_type' => 'text',
                    'filter_type' => 'text'
                ],
                'status' => [
                    'name' => 'leads_dict_status.name',
                    'data' => 'status',
                    'title' => trans('leads::leads.form.status'),
                    'data_type' => 'text',
                    'filter_type' => 'text',
                    'column_name' => 'lead_status_id'
                ],

                'industry' => [
                    'name' => 'leads_dict_industry.name',
                    'data' => 'industry',
                    'title' => trans('leads::leads.form.industry'),
                    'data_type' => 'text',
                    'filter_type' => 'text'
                ],
                'rating' => [
                    'name' => 'leads_dict_rating.name',
                    'data' => 'rating',
                    'title' => trans('leads::leads.form.rating'),
                    'data_type' => 'text',
                    'filter_type' => 'text'
                ],
                'multiSelects' => [
                    'data' => 'multi_selects_text',
                    'title' => trans('leads::leads.form.multiSelects'),
                    'data_type' => 'text',
                    'filter_type' => 'text',
                    'orderable' => false,
                    'searchable' => true,
                ],
                'lead_company' => [
                    'data' => 'lead_company',
                    'title' => trans('leads::leads.form.company'),
                    'data_type' => 'text',
                    'filter_type' => 'text'
                ],
                'secondary_email' => [
                    'data' => 'secondary_email',
                    'title' => trans('leads::leads.form.secondary_email'),
                    'data_type' => 'text',
                    'filter_type' => 'text'
                ],
                'annual_revenue' => [
                    'data' => 'annual_revenue',
                    'title' => trans('leads::leads.form.annual_revenue'),
                    'data_type' => 'text',
                    'filter_type' => 'text'
                ],
                'no_of_employees' => [
                    'data' => 'no_of_employees',
                    'title' => trans('leads::leads.form.no_of_employees'),
                    'data_type' => 'text',
                    'filter_type' => 'text'
                ],
                'fax' => [
                    'data' => 'fax',
                    'title' => trans('leads::leads.form.fax'),
                    'data_type' => 'text',
                    'filter_type' => 'text'
                ],
                'twitter' => [
                    'data' => 'twitter',
                    'title' => trans('leads::leads.form.twitter'),
                    'data_type' => 'text',
                    'filter_type' => 'text'
                ],
                'facebook' => [
                    'data' => 'facebook',
                    'title' => trans('leads::leads.form.facebook'),
                    'data_type' => 'text',
                    'filter_type' => 'text'
                ],
                'skype' => [
                    'data' => 'skype',
                    'title' => trans('leads::leads.form.skype'),
                    'data_type' => 'text',
                    'filter_type' => 'text'
                ],
                'website' => [
                    'data' => 'website',
                    'title' => trans('leads::leads.form.website'),
                    'data_type' => 'text',
                    'filter_type' => 'text'
                ],
                'language' => [
                    'name' => 'customer_dict_language.name',
                    'data' => 'language',
                    'title' => trans('leads::leads.form.language'),
                    'data_type' => 'text',
                    'filter_type' => 'text'
                ],
                'addr_street' => [
                    'data' => 'addr_street',
                    'title' => trans('leads::leads.form.addr_street'),
                    'data_type' => 'text',
                    'filter_type' => 'text'
                ],
                'addr_state' => [
                    'data' => 'addr_state',
                    'title' => trans('leads::leads.form.addr_state'),
                    'data_type' => 'text',
                    'filter_type' => 'text'
                ],
                'addr_country' => [
                    'data' => 'addr_country',
                    'name' => 'bap_country.name',
                    'title' => trans('leads::leads.form.addr_country_id'),
                    'data_type' => 'text',
                    'filter_type' => 'text'
                ],
                'addr_city' => [
                    'data' => 'addr_city',
                    'title' => trans('leads::leads.form.addr_city'),
                    'data_type' => 'text',
                    'filter_type' => 'text'
                ],
                'addr_zip' => [
                    'data' => 'addr_zip',
                    'title' => trans('leads::leads.form.addr_zip'),
                    'data_type' => 'text',
                    'filter_type' => 'text'
                ],
                'job_title' => [
                    'data' => 'job_title',
                    'title' => trans('leads::leads.form.job_title'),
                    'data_type' => 'text',
                    'filter_type' => 'text'
                ],
                'description' => [
                    'data' => 'description',
                    'title' => trans('leads::leads.form.description'),
                    'data_type' => 'text',
                    'filter_type' => 'text'
                ],
                'created_at' => [
                    'data' => 'created_at',
                    'title' => trans('leads::leads.form.created_at'),
                    'data_type' => 'datetime',
                    'filter_type' => 'bap_date_range_picker',
                ],
                'updated_at' => [
                    'data' => 'updated_at',
                    'title' => trans('leads::leads.form.updated_at'),
                    'data_type' => 'datetime',
                    'filter_type' => 'bap_date_range_picker',
                ],
                'owner' => [
                    'data' => 'owner',
                    'title' => trans('core::core.table.assigned_to'),
                    'data_type' => 'assigned_to',
                    'orderable' => false,
                    'column_name' => 'assigned_to',
                    'filter_type' => 'select',
                    'select_type' => 'select2',
                    'select_type_options' => [
                        'theme' => "bootstrap",
                        'width' => '100%'
                    ],
                    'filter_data' => DataTableHelper::filterOwnerDropdown()
                ]
            ];

        }

        $allColumns = $allColumns + self::customIndexColumns();

        // Filter columns to only show those that are visible in the user's field settings
        if (Auth::check() && Schema::hasTable('lead_user_field_settings')) {
            $service = app(LeadFieldSettingsService::class);
            $visibleKeys = $service->getVisibleFieldKeys(Auth::user());
            $orderMap = $service->getFieldOrderMap(Auth::user());
            $labelMap = $service->getFieldLabelMap(Auth::user());

            // Always-visible columns not managed by field settings
            $alwaysVisible = ['id', 'created_at', 'updated_at'];

            // full_name column shows if any name field is visible
            $nameFieldVisible = !empty(array_intersect(['first_name', 'last_name', 'full_name'], $visibleKeys));

            $filteredColumns = [];
            foreach ($allColumns as $colKey => $colDef) {
                // Custom columns (prefixed) are already filtered by getVisibleCustomFieldsForIndex
                if (Str::startsWith($colKey, self::CUSTOM_COLUMN_PREFIX)) {
                    $filteredColumns[$colKey] = $colDef;
                    continue;
                }

                if (in_array($colKey, $alwaysVisible, true)) {
                    $filteredColumns[$colKey] = $colDef;
                    continue;
                }

                // Special case: full_name shown if any name-related field is visible
                if ($colKey === 'full_name' && $nameFieldVisible) {
                    $labelKey = Auth::user()->can('crm.advanced') ? 'full_name' : 'name';
                    if (isset($labelMap[$labelKey])) {
                        $colDef['title'] = $labelMap[$labelKey];
                    }
                    $filteredColumns[$colKey] = $colDef;
                    continue;
                }

                $fieldKey = self::fieldKeyForColumn($colKey);
                if (in_array($fieldKey, $visibleKeys, true)) {
                    if (isset($labelMap[$fieldKey])) {
                        $colDef['title'] = $labelMap[$fieldKey];
                    }
                    $filteredColumns[$colKey] = $colDef;
                }
            }

            // Sort columns by field order from settings
            $customPrefix = self::CUSTOM_COLUMN_PREFIX;
            uksort($filteredColumns, function ($a, $b) use ($orderMap, $customPrefix) {
                // Pin id first, created_at/updated_at last
                $pinFirst = ['id' => -2];
                $pinLast = ['created_at' => 999990, 'updated_at' => 999991];

                if (isset($pinFirst[$a]) || isset($pinLast[$a])) {
                    $orderA = $pinFirst[$a] ?? $pinLast[$a];
                } else {
                    $fieldA = self::$columnToFieldMap[$a] ?? $a;
                    if ($fieldA === 'full_name') $fieldA = 'first_name';
                    // Strip custom__ prefix so custom fields resolve to their settings key
                    if (strpos($fieldA, $customPrefix) === 0) {
                        $fieldA = substr($fieldA, strlen($customPrefix));
                    }
                    $orderA = $orderMap[$fieldA] ?? 99999;
                }

                if (isset($pinFirst[$b]) || isset($pinLast[$b])) {
                    $orderB = $pinFirst[$b] ?? $pinLast[$b];
                } else {
                    $fieldB = self::$columnToFieldMap[$b] ?? $b;
                    if ($fieldB === 'full_name') $fieldB = 'first_name';
                    if (strpos($fieldB, $customPrefix) === 0) {
                        $fieldB = substr($fieldB, strlen($customPrefix));
                    }
                    $orderB = $orderMap[$fieldB] ?? 99999;
                }

                return $orderA <=> $orderB;
            });

            return $filteredColumns;
        }

        return $allColumns;
    }

    protected function setFilterDefinition()
    {
        $filters = self::availableQueryFilters();

        // Enforce show_cid: hide phone/mobile query-builder filters for users without the flag.
        if (Auth::check() && ! Auth::user()->show_cid) {
            $filters = array_values(array_filter($filters, function ($f) {
                return ! in_array($f['id'] ?? '', ['leads.phone', 'leads.mobile'], true);
            }));
        }

        $this->filterDefinition = $filters;
    }

    /**
     * Build DataTable class.
     *
     * @param mixed $query Results from query() method.
     * @return \Yajra\DataTables\DataTableAbstract
     */
    public function dataTable($query)
    {

        $dataTable = new EloquentDataTable($query);

        $this->applyLinks($dataTable, self::SHOW_URL_ROUTE);

        $dataTable->filterColumn('owner', [self::class, 'applyOwnerFilter']);

        // $dataTable->editColumn('status', function ($record) {

        //     $text = "<span class='badge badge-default $record->status_color'>$record->status</span>";
        //     return DataTableHelper::applyLink($text,route(self::SHOW_URL_ROUTE,$record->id));
        // });

        $dataTable->editColumn('email', function ($record) {
            return $record->email;
        });

        $dataTable->editColumn('full_name', function ($record) {
            return '<div style="max-width: 150px; white-space: nowrap; overflow: hidden; text-overflow: ellipsis;" title="' . e($record->full_name) . '">'
                . e($record->full_name) .
                '</div>';
        });

        $dataTable->editColumn('description', function ($record) {
            return '<div style="max-width: 150px; white-space: nowrap; overflow: hidden; text-overflow: ellipsis;" title="' . e($record->description) . '">'
                . e($record->description) .
                '</div>';
        });
        
        $dataTable->filterColumn('email', [self::class, 'applyEmailFilter']);

        foreach ([
            'created_at' => 'leads.created_at',
            'updated_at' => 'leads.updated_at',
            'capture_date' => 'leads.capture_date',
            'birth_date' => 'leads.birth_date',
        ] as $column => $sqlColumn) {
            $dataTable->filterColumn($column, function ($query, $keyword) use ($sqlColumn) {
                self::applyDateRangeFilter($query, $sqlColumn, $keyword);
            });
        }

        // One pass over the pivot table instead of an EXISTS sub query that is
        // evaluated for every lead (the pivot has no index on lead_id).
        $dataTable->filterColumn('multi_selects_text', [self::class, 'applyMultiSelectsFilter']);

        foreach (self::customIndexFields() as $field) {
            $column = $field['column'];
            $dataTable->filterColumn($column, function ($query, $keyword) use ($field) {
                self::applyCustomFieldFilter($query, $field, $keyword);
            });
        }

        // Multi selects: rendered from the eager loaded relation. Doing it per
        // row also used to trigger one relation query per row (N+1).
        $dataTable->addColumn('multi_selects_text', function ($record) {
            return self::multiSelectsText($record);
        });

        // The "multiSelects" cell (rendered by applyLinks()) would otherwise
        // embed the whole relation as json inside the cell title attribute.
        $dataTable->editColumn('multiSelects', function ($record) {
            return self::multiSelectsText($record);
        });

        // Keep the JSON payload small: the relations are only needed server
        // side to render the cells, serializing them for every row produces
        // several KB of unused data per record.
        $dataTable->makeHidden(['owner', 'owned_by', 'multiSelects', 'leadEmails']);

        // Enforce show_cid: null out phone/mobile in the JSON payload for users without the flag.
        if (Auth::check() && ! Auth::user()->show_cid) {
            $dataTable->editColumn('phone', function () { return null; });
            $dataTable->editColumn('mobile', function () { return null; });
        }

        return $dataTable;
    }

    /**
     * Filter a query by owner name/keyword.
     */
    public static function applyOwnerFilter($query, $keyword)
    {
        DataTableHelper::queryOwner($query, $keyword, 'leads');
    }

    /**
     * Filter a query by (multi) email address.
     *
     * The matching lead ids are resolved first: with a concrete id list the
     * listing can use the created_at index, while a correlated EXISTS / IN
     * sub query forces a rescan of the whole lead_email table for the page
     * query. The lookup is memoized, so a chunked export resolves it once.
     */
    public static function applyEmailFilter($query, $keyword, $boolean = 'and')
    {
        $leadIds = self::filterLeadIds('email|' . $keyword, function () use ($keyword) {
            return \DB::table('lead_email')
                ->where('lead_email.email', 'LIKE', '%' . $keyword . '%')
                ->pluck('lead_email.lead_id')
                ->all();
        });

        self::applyIdListCondition($query, 'leads.id', $leadIds, $boolean);
    }

    /**
     * Apply a "<from> - <to>" date range filter on a column.
     */
    public static function applyDateRangeFilter($query, $column, $keyword)
    {
        $dates = DataTableHelper::getDatesForFilter($keyword);

        if ($dates != null) {
            $query->whereBetween($column, [$dates[0], $dates[1]]);
        }
    }

    /**
     * Filter a query by multi select values.
     */
    public static function applyMultiSelectsFilter($query, $keyword, $boolean = 'and')
    {
        $leadIds = self::filterLeadIds('multi_selects|' . $keyword, function () use ($keyword) {
            return \DB::table('lead_multi_select')
                ->join('leads_multi_select', 'lead_multi_select.multi_select_id', '=', 'leads_multi_select.id')
                ->where('leads_multi_select.name', 'LIKE', '%' . $keyword . '%')
                ->distinct()
                ->pluck('lead_multi_select.lead_id')
                ->all();
        });

        self::applyIdListCondition($query, 'leads.id', $leadIds, $boolean);
    }

    /**
     * Add a "<column> IN (<lead ids>)" condition to the query.
     *
     * Up to MAX_BOUND_ID_FILTER ids are bound as placeholders as before (the
     * query planner can use the created_at index with a selective id list).
     * Larger lists would exceed the MySQL placeholder limit of a prepared
     * statement, so they are inlined as integers - inlining is safe because
     * the ids are integers coming from the database (they are cast again
     * here).
     *
     * @param  \Illuminate\Database\Eloquent\Builder|\Illuminate\Database\Query\Builder  $query
     * @param  string  $column
     * @param  array  $leadIds
     * @param  string  $boolean  "and" or "or"
     * @return void
     */
    protected static function applyIdListCondition($query, $column, array $leadIds, $boolean = 'and')
    {
        if (count($leadIds) <= self::MAX_BOUND_ID_FILTER) {
            $query->whereIn($column, $leadIds, $boolean);

            return;
        }

        $inlinedIds = implode(',', array_map('intval', $leadIds));

        $query->whereRaw($column . ' in (' . $inlinedIds . ')', [], $boolean);
    }

    /**
     * Resolve (and memoize) the lead ids matching a filter value.
     *
     * @param  string  $cacheKey
     * @param  callable  $resolver
     * @return array
     */
    protected static function filterLeadIds($cacheKey, callable $resolver)
    {
        if (!array_key_exists($cacheKey, self::$filterLeadIdCache)) {
            self::$filterLeadIdCache[$cacheKey] = $resolver();
        }

        return self::$filterLeadIdCache[$cacheKey];
    }

    /**
     * Filter a query by a custom field value.
     *
     * @param  array  $field  Field definition from customIndexFields()
     */
    public static function applyCustomFieldFilter($query, array $field, $keyword)
    {
        $isDateField = DataTableHelper::isDateCustomFieldType($field['type'] ?? null);

        if (!empty($field['uses_column'])) {
            // Real DB column — filter directly
            if ($isDateField) {
                $dates = DataTableHelper::getDatesForFilter($keyword);

                if ($dates != null) {
                    $query->whereBetween(\DB::raw('DATE(`leads`.`' . $field['key'] . '`)'), [$dates[0], $dates[1]]);
                }

                return;
            }

            $query->where('leads.' . $field['key'], 'LIKE', '%' . $keyword . '%');

            return;
        }

        // Legacy JSON storage
        $jsonPath = '$.' . $field['key'];

        if ($isDateField) {
            $dates = DataTableHelper::getDatesForFilter($keyword);

            if ($dates != null) {
                $query->whereRaw(
                    'DATE(JSON_UNQUOTE(JSON_EXTRACT(leads.custom_fields, ?))) BETWEEN ? AND ?',
                    [$jsonPath, $dates[0], $dates[1]]
                );
            }

            return;
        }

        $query->whereRaw(
            "LOWER(COALESCE(JSON_UNQUOTE(JSON_EXTRACT(leads.custom_fields, ?)), '')) LIKE ?",
            [$jsonPath, '%' . mb_strtolower((string) $keyword, 'UTF-8') . '%']
        );
    }

    /**
     * Comma separated list of the multi select values of a lead.
     *
     * @param  Lead  $record
     * @return string
     */
    protected static function multiSelectsText($record)
    {
        if (!is_object($record) || !method_exists($record, 'multiSelects')) {
            return '';
        }

        if (! $record->relationLoaded('multiSelects') || $record->multiSelects === null) {
            return '';
        }

        return $record->multiSelects->pluck('name')->implode(', ');
    }

    /**
     * Columns written to the export (same columns/labels the user sees in the
     * listing, respecting the CRM field settings visibility).
     *
     * @return array
     */
    public static function csvExportColumns()
    {
        $columns = [];

        foreach (self::availableColumns() as $column) {
            if (in_array($column['data_type'] ?? 'text', ['check_select', 'quick_edit', 'unlink', 'none'], true)) {
                continue;
            }

            $columns[] = $column;
        }

        return $columns;
    }

    /**
     * Plain text value of a column for the export.
     *
     * @param  Lead  $lead
     * @param  array  $column
     * @return string
     */
    public static function exportValue($lead, array $column)
    {
        return self::exportRenderer($column)($lead);
    }

    /**
     * Formats + timezone used by the export, resolved once per request.
     *
     * @return array
     */
    protected static function exportDateFormats()
    {
        if (self::$exportDateFormats === null) {
            $user = \Auth::user();

            $timeZone = null;

            if ($user !== null && !empty($user->time_zone)) {
                try {
                    $timeZone = new \DateTimeZone($user->time_zone);
                } catch (\Throwable $e) {
                    $timeZone = null;
                }
            }

            self::$exportDateFormats = [
                'date' => $user !== null ? optional($user->dateFormat)->details : null,
                'time' => $user !== null ? optional($user->timeFormat)->details : null,
                'timezone' => $timeZone,
                'app_timezone' => new \DateTimeZone(config('app.timezone')),
            ];
        }

        return self::$exportDateFormats;
    }

    /**
     * Format a date/datetime value for the export.
     *
     * Same result as UserHelper::formatUserDate()/formatUserDateTime(), but
     * without a Carbon parse per cell - the export formats up to a million
     * dates and Carbon would spend close to a minute on its own.
     *
     * @param  mixed  $value
     * @param  bool  $withTime
     * @return string
     */
    protected static function formatExportDate($value, $withTime)
    {
        if ($value === null || $value === '') {
            return '';
        }

        $formats = self::exportDateFormats();

        if ($withTime) {
            if ($formats['date'] === null || $formats['time'] === null) {
                return (string) $value;
            }

            $format = $formats['date'] . ' ' . $formats['time'];
        } else {
            if ($formats['date'] === null) {
                return (string) $value;
            }

            $format = $formats['date'];
        }

        try {
            // Carbon::parse() uses the application timezone, keep that behaviour.
            $dateTime = new \DateTime((string) $value, $formats['app_timezone']);

            if ($withTime && $formats['timezone'] !== null) {
                $dateTime->setTimezone($formats['timezone']);
            }

            return $dateTime->format($format);
        } catch (\Throwable $e) {
            return (string) $value;
        }
    }

    /**
     * Renderer for an export column. They are compiled once per column so the
     * per cell work is a single function call (exporting 100k+ rows otherwise
     * spends a full minute in type checks / strip_tags / data_get calls).
     *
     * @param  array  $column
     * @return callable
     */
    protected static function exportRenderer(array $column)
    {
        $cacheKey = md5(($column['data'] ?? '') . '|' . ($column['data_type'] ?? 'text'));

        if (!isset(self::$exportRendererCache[$cacheKey])) {
            self::$exportRendererCache[$cacheKey] = self::makeExportRenderer($column);
        }

        return self::$exportRendererCache[$cacheKey];
    }

    /**
     * @param  array  $column
     * @return callable
     */
    protected static function makeExportRenderer(array $column)
    {
        $data = (string) ($column['data'] ?? '');
        $dataType = $column['data_type'] ?? 'text';

        if (in_array($data, ['multiSelects', 'multi_selects_text'], true)) {
            return function ($lead) {
                return self::multiSelectsText($lead);
            };
        }

        if ($dataType === 'assigned_to') {
            return function ($lead) {
                return (string) optional($lead->owned_by)->name;
            };
        }

        if (in_array($dataType, ['check_select', 'quick_edit', 'unlink', 'none'], true)) {
            return function () {
                return '';
            };
        }

        switch ($dataType) {
            case 'datetime':
                return function ($lead) use ($data) {
                    return self::formatExportDate($lead->{$data}, true);
                };

            case 'date':
                return function ($lead) use ($data) {
                    return self::formatExportDate($lead->{$data}, false);
                };

            case 'boolean':
                return function ($lead) use ($data) {
                    return (string) trans('core::core.yes_no.' . $lead->{$data});
                };
        }

        return function ($lead) use ($data) {
            $attributes = $lead->getAttributes();

            if (!array_key_exists($data, $attributes)) {
                $value = $lead->{$data};
            } else {
                $value = $attributes[$data];
            }

            if (is_array($value)) {
                $value = implode(', ', $value);
            }

            if ($value === null) {
                return '';
            }

            $value = (string) $value;

            return strpos($value, '<') === false ? $value : trim(strip_tags($value));
        };
    }

    /**
     * Build the query used by the export and by the "select all" endpoint.
     *
     * It honours the same filters as the listing: the query builder rules,
     * the header/column filters and the global search value. Scopes that were
     * registered on the datatable (the "select all" endpoint registers the
     * ownership scope of the listing) are applied as well.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function exportQuery($request)
    {
        if ($rules = $request->input('rules')) {
            $this->applyFilterRules($rules);
        }

        $query = $this->applyScopes($this->query(new Lead()));

        $requestColumns = (array) $request->input('columns', []);

        foreach ($requestColumns as $column) {
            if (!is_array($column)) {
                continue;
            }

            $keyword = trim((string) data_get($column, 'search.value', ''));

            if ($keyword === '') {
                continue;
            }

            $this->applyRequestColumnFilter($query, $column, $keyword);
        }

        $search = trim((string) $request->input('search.value', ''));

        if ($search !== '') {
            $this->applyGlobalSearch($query, $search, $requestColumns);
        }

        // chunkById() needs a query ordered by the id column only.
        return $query->reorder();
    }

    /**
     * Apply a single DataTables column filter from the request.
     *
     * @param  \Illuminate\Database\Eloquent\Builder  $query
     * @param  array  $column
     * @param  string  $keyword
     * @return void
     */
    protected function applyRequestColumnFilter($query, array $column, $keyword)
    {
        $data = (string) ($column['data'] ?? '');
        $name = (string) ($column['name'] ?? '');

        if (strpos($data, self::CUSTOM_COLUMN_PREFIX) === 0) {
            $fields = self::customIndexFields();

            if (isset($fields[$data])) {
                self::applyCustomFieldFilter($query, $fields[$data], $keyword);
            }

            return;
        }

        switch ($data) {
            case 'owner':
                self::applyOwnerFilter($query, $keyword);

                return;

            case 'email':
                self::applyEmailFilter($query, $keyword);

                return;

            case 'multiSelects':
            case 'multi_selects_text':
                self::applyMultiSelectsFilter($query, $keyword);

                return;

            case 'created_at':
                self::applyDateRangeFilter($query, 'leads.created_at', $keyword);

                return;

            case 'updated_at':
                self::applyDateRangeFilter($query, 'leads.updated_at', $keyword);

                return;

            case 'capture_date':
                self::applyDateRangeFilter($query, 'leads.capture_date', $keyword);

                return;

            case 'birth_date':
                self::applyDateRangeFilter($query, 'leads.birth_date', $keyword);

                return;

            case 'check_select':
            case 'quick_edit':
            case 'unlink':
                return;
        }

        // Plain columns are filtered the same way the listing does (LIKE on
        // the underlying SQL column). The column name always comes from the
        // request, so it has to be validated before being used in the query.
        $sqlColumn = $name !== '' ? $name : $data;

        // Unqualified columns belong to the leads table (the listing prefixes
        // them the same way); without the prefix joined tables would make
        // them ambiguous (e.g. "id").
        if (strpos($sqlColumn, '.') === false) {
            $sqlColumn = 'leads.' . $sqlColumn;
        }

        if (self::isSafeColumnName($sqlColumn)) {
            $query->where($sqlColumn, 'LIKE', '%' . $keyword . '%');
        }
    }

    /**
     * Apply the global (search box) search of the listing.
     *
     * The listing searches every request column that is marked as searchable:
     * the registered column filters (owner, email, multi selects, dates,
     * custom fields) are applied and all other columns are searched with a
     * LIKE on the column itself. Multi word searches are split into single
     * words that are combined with AND (datatables.search.multi_term). The
     * same logic is used here, so "select all" (and the export) cover exactly
     * the records the listing shows - searching e.g. by description, by lead
     * id or with several words used to find nothing here although the listing
     * showed the leads.
     *
     * @param  \Illuminate\Database\Eloquent\Builder  $query
     * @param  string  $search
     * @param  array  $requestColumns  DataTables request columns
     * @return void
     */
    protected function applyGlobalSearch($query, $search, array $requestColumns = [])
    {
        $terms = self::searchTerms($search);

        if (empty($terms)) {
            return;
        }

        $columns = [];

        foreach ($requestColumns as $column) {
            if (!is_array($column) || !self::isRequestColumnSearchable($column)) {
                continue;
            }

            $data = (string) ($column['data'] ?? '');

            if ($data === '' || in_array($data, ['check_select', 'quick_edit', 'unlink'], true)) {
                continue;
            }

            $columns[] = $column;
        }

        // No columns in the request (e.g. the endpoint was called without
        // them): fall back to the columns that were searched before.
        if (empty($columns)) {
            self::applyDefaultGlobalSearch($query, $terms);

            return;
        }

        // The listing searches every word of the search box on its own and
        // combines the words with AND (datatables.search.multi_term), so a
        // multi word search must be applied the same way here.
        foreach ($terms as $term) {
            $query->where(function ($subQuery) use ($columns, $term) {
                foreach ($columns as $column) {
                    $subQuery->orWhere(function ($nested) use ($column, $term) {
                        $this->applyRequestColumnFilter($nested, $column, $term);
                    });
                }
            });
        }
    }

    /**
     * Split the search box value into the terms the listing searches. The
     * listing splits on spaces (Yajra smartGlobalSearch) and skips empty
     * terms.
     *
     * @param  string  $search
     * @return array
     */
    protected static function searchTerms($search)
    {
        $terms = [];

        foreach (explode(' ', (string) $search) as $term) {
            if (trim($term) !== '') {
                $terms[] = $term;
            }
        }

        return $terms;
    }

    /**
     * Check whether the request marks a DataTables column as searchable (the
     * same check Yajra DataTables uses for the listing).
     *
     * @param  array  $column
     * @return bool
     */
    protected static function isRequestColumnSearchable(array $column)
    {
        $searchable = $column['searchable'] ?? true;

        return $searchable === true || $searchable === 'true';
    }

    /**
     * Global search used when the request does not contain the column list.
     *
     * @param  \Illuminate\Database\Eloquent\Builder  $query
     * @param  array  $terms  search terms (one per search box word)
     * @return void
     */
    protected static function applyDefaultGlobalSearch($query, array $terms)
    {
        foreach ($terms as $term) {
            $like = '%' . $term . '%';

            $query->where(function ($subQuery) use ($like, $term) {
                foreach (self::GLOBAL_SEARCH_COLUMNS as $column) {
                    $subQuery->orWhere($column, 'LIKE', $like);
                }

                foreach (self::customIndexFields() as $field) {
                    if (!empty($field['uses_column'])) {
                        $subQuery->orWhere('leads.' . $field['key'], 'LIKE', $like);

                        continue;
                    }

                    $subQuery->orWhereRaw(
                        "LOWER(COALESCE(JSON_UNQUOTE(JSON_EXTRACT(leads.custom_fields, ?)), '')) LIKE ?",
                        ['$.' . $field['key'], '%' . mb_strtolower($term, 'UTF-8') . '%']
                    );
                }

                // Multi selects and (multi) emails are searched by the listing too.
                self::applyMultiSelectsFilter($subQuery, $term, 'or');
                self::applyEmailFilter($subQuery, $term, 'or');
            });
        }
    }

    /**
     * Guard for column names coming from the request.
     *
     * @param  string  $column
     * @return bool
     */
    protected static function isSafeColumnName($column)
    {
        return preg_match('/^[A-Za-z0-9_]+(\.[A-Za-z0-9_]+)?$/', (string) $column) === 1;
    }

    /**
     * Get query source of dataTable.
     *
     *
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function query(Lead $model)
    {
        $query = $this->baseQuery($model);

        if (!empty($this->filterRules)) {
            $query = $this->applyQueryBuilderRules($query);
        }

        return $query;
    }

    /**
     * Base listing query (joins + selected aliases).
     *
     * Performance notes:
     *  - The multi select values are NOT built with a correlated GROUP_CONCAT
     *    sub query anymore. The pivot table has no index on lead_id, so such a
     *    sub query performs a full table scan for EVERY returned row
     *    (~25ms/row with 200k leads => ~30s for 1000 rows, ~5min for 10k).
     *    The relation is eager loaded instead: one single query per page.
     *  - "owner" is the morph relation of the ownership trait; when it is eager
     *    loaded both $lead->owner and $lead->owned_by resolve without extra
     *    queries (they are used when rendering the "assigned to" column).
     *  - leadEmails is lazily loaded only by the filters that need it, it is
     *    not needed to render the listing.
     *
     * @param  Lead  $model
     * @return \Illuminate\Database\Eloquent\Builder
     */
    protected function baseQuery(Lead $model)
    {
        $query = $model->newQuery()
            ->with('owner')
            ->with('multiSelects')
            ->leftJoin('leads_dict_industry', 'leads.lead_industry_id', '=', 'leads_dict_industry.id')
            ->leftJoin('leads_dict_rating', 'leads.lead_rating_id', '=', 'leads_dict_rating.id')
            ->leftJoin('leads_dict_status', 'leads.lead_status_id', '=', 'leads_dict_status.id')
            ->leftJoin('leads_dict_source', 'leads.lead_source_id', '=', 'leads_dict_source.id')
            ->leftJoin('bap_country', 'leads.addr_country_id', '=', 'bap_country.id')
            ->leftJoin('customer_dict_language', 'leads.language_id', '=', 'customer_dict_language.id')
            ->select(
                'leads.*',
                'bap_country.name as addr_country',
                'leads_dict_industry.name as industry',
                'leads_dict_rating.name as rating',
                'leads_dict_status.name as status',
                'leads_dict_status.color as status_color',
                'leads_dict_source.name as source',
                'customer_dict_language.name as language'
            )
            ->orderBy('leads.created_at', 'DESC'); // Order by created_at in descending order

        foreach (self::customIndexFields() as $field) {
            if (!empty($field['uses_column'])) {
                // Real DB column — select it directly
                $query->addSelect(\DB::raw('`leads`.`' . $field['key'] . '` as `' . $field['column'] . '`'));
            } else {
                // Legacy JSON storage
                $jsonPath = '$.' . $field['key'];
                $query->addSelect(\DB::raw(
                    "JSON_UNQUOTE(JSON_EXTRACT(leads.custom_fields, '{$jsonPath}')) as `{$field['column']}`"
                ));
            }
        }

        return $query;
    }

    /**
     * Apply the "advanced query filter" rules to the given query.
     *
     * @param  \Illuminate\Database\Eloquent\Builder  $query
     * @return \Illuminate\Database\Eloquent\Builder
     */
    protected function applyQueryBuilderRules($query)
    {
        $filterRules = json_decode($this->filterRules);

        if (empty($filterRules) || empty($filterRules->rules)) {
            return $query;
        }

        foreach ($filterRules->rules as &$rule) {
            if ($rule->id == 'lead_email.email') { // filter in multi email
                $query->leftJoin('lead_email', 'lead_email.lead_id', '=', 'leads.id');
            }
            if ($rule->id == 'lead_multi_select.multi_select_id') {
                $query->leftJoin('lead_multi_select', 'lead_multi_select.lead_id', '=', 'leads.id');
            }
        }

        $queryBuilderParser = new QueryBuilderParser();

        return $queryBuilderParser->parse(json_encode($filterRules), $query);
    }

    /**
     * Optional method if you want to use html builder.
     *
     * @return \Yajra\DataTables\Html\Builder
     */
    public function html()
    {
        return $this->builder()
            // Use minified ajax payload to avoid very long query strings
            // when many columns (including custom fields) are visible.
            ->minifiedAjax(route('leads.leads.index', [], false))
            ->columns($this->getColumns())
            ->setTableAttribute('class', 'table table-hover')
            ->parameters([
                'dom' => 'lBfrtip',
                'responsive' => false,
                'stateSave' => false,
                'lengthMenu' => self::PAGE_LENGTH_MENU,
                'pageLength' => 10,
                'filterDefinitions' => $this->getFilterDefinition(),
                'filterRules' => $this->filterRules,
                'headerFilters' => true,
                'buttons' => self::buttons(),
                'regexp' => true
            ]);
    }

    /**
     * DataTables buttons of the lead listing.
     *
     * The Excel button is replaced by the server side export endpoint: the
     * bundled client side export only writes the rows of the currently
     * rendered page (e.g. 10 leads), while "leads.leads.export" streams every
     * lead matching the current listing state in chunks.
     *
     * @return array
     */
    protected static function buttons()
    {
        $buttons = DataTableHelper::buttons();

        $exportUrl = route('leads.leads.export');

        foreach ($buttons as $index => $button) {
            if (($button['extend'] ?? null) !== 'excel') {
                continue;
            }

            $buttons[$index] = [
                'text' => $button['text'] ?? trans('core::core.excel'),
                'className' => $button['className'] ?? '',
                // Callback functions are emitted as-is by the html builder.
                'action' => "function (e, dt, node, config) { window.BAP_Leads.exportAllLeads(dt, '" . $exportUrl . "'); }",
            ];
        }

        return $buttons;
    }

    /**
     * @return array
     */
    protected function getColumns()
    {
        if (!empty($this->advancedView)) {
            $result = [];
            if ($this->allowSelect) {
                $result = $this->btnCheck_selection;
            }

            return $result + $this->advancedView + self::customIndexColumns();
        }

        $columns = self::availableColumns();


        $result = [];

        if ($this->allowSelect) {
            $result = $this->btnCheck_selection;
        }
        if ($this->allowUnlink) {
            $result = $this->btnUnlink;
        }
        if ($this->allowQuickEdit) {
            $result = $result + $this->btnQuick_edit;;
        }

        $result = $result + $columns;

        return $result;
    }
}