<?php

namespace Modules\Tickets\Datatables;

use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Modules\Platform\Core\Datatable\PlatformDataTable;
use Modules\Platform\Core\Helper\DataTableHelper;
use Modules\Platform\Core\Helper\FormHelper;
use Modules\Platform\Core\Helper\StringHelper;
use Modules\Platform\Core\QueryBuilderParser\QueryBuilderParser;
use Modules\Tickets\Entities\Ticket;
use Modules\Tickets\Entities\TicketCategory;
use Modules\Tickets\Entities\TicketPriority;
use Modules\Tickets\Entities\TicketSeverity;
use Modules\Tickets\Entities\TicketStatus;
use Modules\Tickets\Service\TicketFieldSettingsService;
use Yajra\DataTables\EloquentDataTable;

class TicketDatatable extends PlatformDataTable
{
    const SHOW_URL_ROUTE = 'tickets.tickets.show';

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

    /** Prefix used for custom_data JSON columns in the DataTable. */
    private const CUSTOM_COL_PREFIX = 'custom__';

    /**
     * Maps DataTable column keys to their corresponding field-settings keys
     * when the names differ (e.g. 'category' column → 'ticket_category_id' field).
     */
    private static array $columnToFieldMap = [
        'category'     => 'ticket_category_id',
        'priority'     => 'ticket_priority_id',
        'severity'     => 'ticket_severity_id',
        'status'       => 'ticket_status_id',
        'ticket_owner' => 'ticket_owner_id',
        'owner'        => 'owned_by',
        'account_name' => 'account_id',
        'contact_name' => 'contact_id',
    ];

    /** Columns that are always shown regardless of field settings. */
    private static array $alwaysVisible = ['id', 'created_at', 'updated_at'];

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

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

        return [
            [
                'id' => 'accounts.name',
                'label' => $getLabel('account_id', 'tickets::tickets.form.account_name'),
                'type' => 'string',
            ],
            [
                'id' => 'contacts.full_name',
                'label' => $getLabel('contact_id', 'tickets::tickets.form.contact_name'),
                'type' => 'string',
            ],
            [
                'id' => 'tickets.name',
                'label' => $getLabel('name', 'tickets::tickets.form.name'),
                'type' => 'string',
            ],
            [
                'id' => 'tickets.ticket_priority_id',
                'label' => $getLabel('ticket_priority_id', 'tickets::tickets.form.ticket_priority_id'),
                'type' => 'integer',
                'input' => 'select',
                'multiple' => true,
                'plugin' => 'select2',
                'plugin_config' => [
                    'multiple' => 'multiple',
                    'width' => '300px',
                ],
                'values' => TicketPriority::pluck('name', 'id'),
                'operators' => [
                    'in',
                    'not_in',
                    'is_null',
                    'is_not_null'
                ]
            ],
            [
                'id' => 'tickets.ticket_owner_id',
                'label' => $getLabel('ticket_owner_id', 'tickets::tickets.form.ticket_owner_id'),
                'type' => 'integer',
                'input' => 'select',
                'multiple' => true,
                'plugin' => 'select2',
                'plugin_config' => [
                    'multiple' => 'multiple',
                    'width' => '300px',
                ],
                'values' => FormHelper::shareWithChoises(),
                'operators' => [
                    'in',
                    'not_in',
                    'is_null',
                    'is_not_null'
                ]
            ],
            [
                'id' => 'tickets.ticket_status_id',
                'label' => $getLabel('ticket_status_id', 'tickets::tickets.form.ticket_status_id'),
                'type' => 'integer',
                'input' => 'select',
                'multiple' => true,
                'plugin' => 'select2',
                'plugin_config' => [
                    'multiple' => 'multiple',
                    'width' => '300px',
                ],
                'values' => TicketStatus::pluck('name', 'id'),
                'operators' => [
                    'in',
                    'not_in',
                    'is_null',
                    'is_not_null'
                ]
            ],
            [
                'id' => 'tickets.ticket_severity_id',
                'label' => $getLabel('ticket_severity_id', 'tickets::tickets.form.ticket_severity_id'),
                'type' => 'integer',
                'input' => 'select',
                'multiple' => true,
                'plugin' => 'select2',
                'plugin_config' => [
                    'multiple' => 'multiple',
                    'width' => '300px',
                ],
                'values' => TicketSeverity::pluck('name', 'id'),
                'operators' => [
                    'in',
                    'not_in',
                    'is_null',
                    'is_not_null'
                ]
            ],
            [
                'id' => 'tickets.ticket_category_id',
                'label' => $getLabel('ticket_category_id', 'tickets::tickets.form.ticket_category_id'),
                'type' => 'integer',
                'input' => 'select',
                'multiple' => true,
                'plugin' => 'select2',
                'plugin_config' => [
                    'multiple' => 'multiple',
                    'width' => '300px',
                ],
                'values' => TicketCategory::pluck('name', 'id'),
                'operators' => [
                    'in',
                    'not_in',
                    'is_null',
                    'is_not_null'
                ]
            ],
            [
                'id' => 'tickets.due_date',
                'label' => $getLabel('due_date', 'tickets::tickets.form.due_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' => 'tickets.created_at',
                'label' => $getLabel('created_at', 'core::core.table.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' => 'tickets.updated_at',
                'label' => $getLabel('updated_at', 'core::core.table.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()
                ]
            ],
        ];
    }

    public static function availableColumns()
    {
        return [
            'id' => [
                'data' => 'id',
                'title' => trans('tickets::tickets.form.id'),
                'data_type' => 'text',
                'filter_type' => 'text',
            ],
            'ticket_num' => [
                'data' => 'ticket_num',
                'title' => trans('tickets::tickets.form.ticket_num'),
                'data_type' => 'text',
                'filter_type' => 'text',
            ],

            'name' => [
                'data' => 'name',
                'title' => trans('tickets::tickets.form.name'),
                'data_type' => 'text',
                'filter_type' => 'text',
            ],

            'category' => [
                'name' => 'tickets_dict_category.name',
                'data' => 'category',
                'title' => trans('tickets::tickets.form.category'),
                'data_type' => 'text',
                'filter_type' => 'text'
            ],
            'priority' => [
                'name' => 'tickets_dict_priority.name',
                'data' => 'priority',
                'title' => trans('tickets::tickets.form.priority'),
                'data_type' => 'text',
                'filter_type' => 'text'
            ],
            'ticket_owner' => [
                'name' => 'users.name',
                'data' => 'ticket_owner',
                'title' => trans('tickets::tickets.form.ticket_owner_id'),
                'data_type' => 'text',
                'filter_type' => 'text'
            ],
            'severity' => [
                'name' => 'tickets_dict_severity.name',
                'data' => 'severity',
                'title' => trans('tickets::tickets.form.severity'),
                'data_type' => 'text',
                'filter_type' => 'text'
            ],
            'status' => [
                'name' => 'tickets_dict_status.name',
                'data' => 'status',
                'title' => trans('tickets::tickets.form.status'),
                'data_type' => 'text',
                'filter_type' => 'text'
            ],
            'account_name' => [
                'name' => 'accounts.name',
                'data' => 'account_name',
                'title' => trans('tickets::tickets.form.account_name'),
                'data_type' => 'text',
                'filter_type' => 'text'
            ],
            'contact_name' => [
                'name' => 'contacts.full_name',
                'data' => 'contact_name',
                'title' => trans('tickets::tickets.form.contact_name'),
                'data_type' => 'text',
                'filter_type' => 'text'
            ],
            'due_date' => [
                'data' => 'due_date',
                'title' => trans('tickets::tickets.form.due_date'),
                'data_type' => 'date',
                'filter_type' => 'bap_date_range_picker'
            ],
            'created_at' => [
                'data' => 'created_at',
                'title' => trans('core::core.table.created_at'),
                'data_type' => 'datetime',
                'filter_type' => 'bap_date_range_picker',
            ],
            'updated_at' => [
                'data' => 'updated_at',
                'title' => trans('core::core.table.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()
            ]
        ];
    }

    protected function setFilterDefinition()
    {
        $this->filterDefinition = self::availableQueryFilters();
    }

    /**
     * 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', function ($query, $keyword) {
            DataTableHelper::queryOwner($query, $keyword, 'tickets');
        });

        $dataTable->editColumn('priority', function ($record) {
            $text = "<span class='badge badge-default $record->priority_color'>$record->priority</span>";
            return DataTableHelper::applyLink($text, route(self::SHOW_URL_ROUTE, $record->id));
        });
        $dataTable->editColumn('severity', function ($record) {
            $text = "<span class='badge badge-default $record->severity_color'>$record->severity</span>";
            return DataTableHelper::applyLink($text, route(self::SHOW_URL_ROUTE, $record->id));
        });

        $dataTable->filterColumn('created_at', function ($query, $keyword) {
            $dates = DataTableHelper::getDatesForFilter($keyword);
            if ($dates != null) {
                $query->whereBetween('tickets.created_at', [$dates[0], $dates[1]]);
            }
        });
        $dataTable->filterColumn('due_date', function ($query, $keyword) {
            $dates = DataTableHelper::getDatesForFilter($keyword);
            if ($dates != null) {
                $query->whereBetween('tickets.due_date', [$dates[0], $dates[1]]);
            }
        });
        $dataTable->filterColumn('updated_at', function ($query, $keyword) {
            $dates = DataTableHelper::getDatesForFilter($keyword);
            if ($dates != null) {
                $query->whereBetween('tickets.updated_at', [$dates[0], $dates[1]]);
            }
        });

        // Register filter callbacks for each visible custom_data field
        if (Auth::check() && Schema::hasColumn('tickets', 'custom_data')) {
            $service = app(TicketFieldSettingsService::class);
            foreach ($service->getVisibleCustomFieldsForDatatable(Auth::user()) as $field) {
                $col      = self::CUSTOM_COL_PREFIX . $field['key'];
                $jsonPath = '$.' . $field['key'];
                $isDateField = DataTableHelper::isDateCustomFieldType($field['type'] ?? null);

                $dataTable->filterColumn($col, function ($q, $keyword) use ($jsonPath, $isDateField) {
                    if ($isDateField) {
                        // Date range picker — keyword is "<from> - <to>"
                        $dates = DataTableHelper::getDatesForFilter($keyword);

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

                        return;
                    }

                    $q->whereRaw(
                        "JSON_UNQUOTE(JSON_EXTRACT(tickets.custom_data, ?)) LIKE ?",
                        [$jsonPath, "%{$keyword}%"]
                    );
                });
            }
        }

        return $dataTable;
    }

    /**
     * Get query source of dataTable.
     *
     *
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function query(Ticket $model)
    {
        $query = $model->with('owner')
            ->leftJoin('tickets_dict_category', 'tickets.ticket_category_id', '=', 'tickets_dict_category.id')
            ->leftJoin('tickets_dict_priority', 'tickets.ticket_priority_id',  '=', 'tickets_dict_priority.id')
            ->leftJoin('tickets_dict_severity', 'tickets.ticket_severity_id',  '=', 'tickets_dict_severity.id')
            ->leftJoin('tickets_dict_status',   'tickets.ticket_status_id',    '=', 'tickets_dict_status.id')
            ->leftJoin('accounts',  'tickets.account_id',       '=', 'accounts.id')
            ->leftJoin('contacts',  'tickets.contact_id',       '=', 'contacts.id')
            ->leftJoin('users',     'tickets.ticket_owner_id',  '=', 'users.id')
            ->newQuery()->select([
                'tickets.*',
                'tickets_dict_category.name as category',
                'tickets_dict_priority.id   as priority_id',
                'tickets_dict_priority.name as priority',
                'tickets_dict_priority.color as priority_color',
                'tickets_dict_severity.id   as severity_id',
                'tickets_dict_severity.name as severity',
                'tickets_dict_severity.color as severity_color',
                'tickets_dict_status.name   as status',
                'accounts.name              as account_name',
                'contacts.full_name         as contact_name',
                'users.name                 as ticket_owner',
            ])
            ->orderBy('tickets.created_at', 'DESC');

        // Add JSON extractions for visible custom_data fields
        if (Auth::check() && Schema::hasColumn('tickets', 'custom_data')) {
            $service = app(TicketFieldSettingsService::class);
            foreach ($service->getVisibleCustomFieldsForDatatable(Auth::user()) as $field) {
                $col      = self::CUSTOM_COL_PREFIX . $field['key'];
                $jsonPath = '$.' . $field['key'];
                $query->addSelect(
                    DB::raw("JSON_UNQUOTE(JSON_EXTRACT(tickets.custom_data, '{$jsonPath}')) as `{$col}`")
                );
            }
        }

        if (!empty($this->filterRules)) {
            $queryBuilderParser = new QueryBuilderParser();
            return $queryBuilderParser->parse($this->filterRules, $query);
        }

        return $query;
    }

    /**
     * Optional method if you want to use html builder.
     *
     * @return \Yajra\DataTables\Html\Builder
     */
    public function html()
    {
        return $this->builder()
            ->columns($this->getColumns())
            ->setTableAttribute('class', 'table table-hover')
            ->parameters([
                'dom' => 'lBfrtip',
                'responsive' => false,
                'stateSave' => false,
                'filterDefinitions' => $this->getFilterDefinition(),
                'filterRules' => $this->filterRules,
                'headerFilters' => true,
                'buttons' => DataTableHelper::buttons(),
                'regexp' => true
            ]);
    }

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

        $allColumns = self::availableColumns();

        // Append any visible custom_data fields as additional columns
        if (Auth::check() && Schema::hasColumn('tickets', 'custom_data')) {
            $service = app(TicketFieldSettingsService::class);
            foreach ($service->getVisibleCustomFieldsForDatatable(Auth::user()) as $field) {
                $col = self::CUSTOM_COL_PREFIX . $field['key'];
                $allColumns[$col] = [
                    'data'        => $col,
                    'title'       => $field['label'],
                    'data_type'   => 'text',
                    'filter_type' => DataTableHelper::isDateCustomFieldType($field['type'] ?? null)
                        ? 'bap_date_range_picker'
                        : 'text',
                    'searchable'  => true,
                ];
            }
        }

        // Apply field-settings visibility, labels, and ordering
        if (Auth::check() && Schema::hasTable('ticket_field_settings')) {
            $service     = app(TicketFieldSettingsService::class);
            $user        = Auth::user();
            $visibleKeys = $service->getVisibleFieldKeys($user);
            $orderMap    = $service->getFieldOrderMap($user);
            $labelMap    = $service->getFieldLabelMap($user);

            $filtered = [];
            foreach ($allColumns as $colKey => $colDef) {
                if (strpos($colKey, self::CUSTOM_COL_PREFIX) === 0) {
                    $filtered[$colKey] = $colDef;
                    continue;
                }
                if (in_array($colKey, self::$alwaysVisible, true)) {
                    $filtered[$colKey] = $colDef;
                    continue;
                }
                $fieldKey = self::$columnToFieldMap[$colKey] ?? $colKey;
                if (in_array($fieldKey, $visibleKeys, true)) {
                    if (isset($labelMap[$fieldKey])) {
                        $colDef['title'] = $labelMap[$fieldKey];
                    }
                    $filtered[$colKey] = $colDef;
                }
            }

            // Sort columns by field order from settings
            uksort($filtered, function ($a, $b) use ($orderMap) {
                $pinFirst = ['id' => -1];
                $pinLast  = array_fill_keys(['created_at', 'updated_at'], 999000);
                $resolveOrder = function ($col) use ($orderMap, $pinFirst, $pinLast) {
                    if (isset($pinFirst[$col])) return $pinFirst[$col];
                    if (isset($pinLast[$col]))  return $pinLast[$col];
                    if (strpos($col, self::CUSTOM_COL_PREFIX) === 0) {
                        $key = substr($col, strlen(self::CUSTOM_COL_PREFIX));
                    } else {
                        $key = self::$columnToFieldMap[$col] ?? $col;
                    }
                    return $orderMap[$key] ?? 99999;
                };
                return $resolveOrder($a) <=> $resolveOrder($b);
            });

            $allColumns = $filtered;
        }

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

        return $result + $allColumns;
    }
}