<?php

namespace ISPManager\Clients\Tables;

use ISPManager\Clients\Models\Contact;
use ISPManager\Clients\Tables\BulkChanges\ClientStatusBulkChange;
use Botble\Table\Abstracts\TableAbstract;
use Botble\Table\Actions\DeleteAction;
use Botble\Table\Actions\EditAction;
use Botble\Table\BulkActions\DeleteBulkAction;
use Botble\Table\BulkChanges\CreatedAtBulkChange;
use Botble\Table\BulkChanges\NameBulkChange;
use Botble\Table\BulkChanges\StatusBulkChange;
use Botble\Table\Columns\CreatedAtColumn;
use Botble\Table\Columns\IdColumn;
use Botble\Table\Columns\Column;
use Botble\Table\Columns\YesNoColumn;
use Botble\Table\Columns\StatusColumn;
use ISPManager\Clients\Tables\Columns\ClientNameColumn;
use Botble\Table\HeaderActions\CreateHeaderAction;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\DB;

class ContactsTable extends TableAbstract
{
    public function setup(): void
    {
        $this
            ->model(Contact::class)
            ->addHeaderAction(CreateHeaderAction::make()->route('clients.contacts.create'))
            ->addActions([
                EditAction::make()->route('clients.contacts.edit'),
                DeleteAction::make()->route('clients.contacts.destroy'),
            ])
            ->addColumns([
                IdColumn::make()
                    ->title(trans('plugins/clients::clients.id')),
                ClientNameColumn::make('client.name')
                    ->route('clients.edit', ['clients' => 'client.id'])
                    ->title(trans('plugins/clients::clients.client')), 
                Column::make('full_name')
                    ->title(trans('plugins/clients::contacts.contact'))
                    ->alignLeft()
                    ->searchable(true)
                    ->orderable(true),
                YesNoColumn::make('is_primary')->title(trans('plugins/clients::contacts.isprimary')),
                Column::make('email')->title(trans('plugins/clients::contacts.email')),
                Column::make('username')->title(trans('plugins/clients::contacts.username')),
                Column::make('phone1')->title(trans('plugins/clients::contacts.phone')),
                //YesNoColumn::make('notify_sms')->title(trans('plugins/clients::contacts.sms')),
                //YesNoColumn::make('notify_whatsapp')->title(trans('plugins/clients::contacts.whatsapp')),
                StatusColumn::make(),
            ])
            ->addBulkActions([
                DeleteBulkAction::make()->permission('contacts.destroy'),
            ])
            ->addBulkChanges([
                //NameBulkChange::make(),
                ClientStatusBulkChange::make(),
            ])
            ->queryUsing(function (Builder $query) {
                $query->select([
                    'contacts.id',
                    'contacts.status',
                    'contacts.is_primary',
                    'contacts.email',
                    'contacts.username',
                    'contacts.phone1',
                    'contacts.notify_sms',
                    'contacts.notify_whatsapp',
                    'contacts.client_id',
                    'clients.name',
                    DB::raw("CONCAT(contacts.firstname, 
                                IFNULL(CONCAT(' ', contacts.middlename), ''),
                                ' ', contacts.lastname, 
                                IFNULL(CONCAT(' ', contacts.secondlastname), '') ) AS full_name")
                ])
                ->leftJoin('clients', 'clients.id', '=', 'contacts.client_id')
                ->with('client')
                ->orderBy('contacts.id', 'desc');
            })
            ->onAjax(function ($table) {
                return $table->toJson(
                    $table->table
                        ->eloquent($table->query())
                        ->filter(function ($query) {
                            if ($keyword = $this->request->input('search.value')) {
                                // Convert the search term to lowercase and split into words
                                $keywords = preg_split('/\s+/', trim($keyword)); // Divide by spaces
            
                                // Filtrar por las otras columnas
                                $query->where(function ($query) use ($keywords) {
                                    foreach ($keywords as $word) {
                                        $query->orWhere('contacts.id', 'LIKE', "%{$word}%")
                                              ->orWhere('contacts.status', 'LIKE', "%{$word}%")
                                              ->orWhere('contacts.email', 'LIKE', "%{$word}%")
                                              ->orWhere('contacts.username', 'LIKE', "%{$word}%")
                                              ->orWhere('contacts.phone1', 'LIKE', "%{$word}%")
                                              ->orWhere('clients.name', 'LIKE', "%{$word}%");
                                    }
                                });
            
                                // Mapping with translation for the state (English to Spanish)
                                $query->orWhereRaw("LOWER(CASE
                                        WHEN contacts.status = 'enabled' THEN '" . trans('plugins/clients::clients.statuses.enabled') . "'
                                        WHEN contacts.status = 'disabled' THEN '" . trans('plugins/clients::clients.statuses.disabled') . "'
                                        ELSE contacts.status
                                    END) LIKE ?", ['%' . strtolower($keyword) . '%']);
            
                                // Manual filter on individual fields, allowing searching regardless of order
                                $query->orWhere(function ($q) use ($keywords) {
                                    foreach ($keywords as $word) {
                                        $q->where(function ($subQ) use ($word) {
                                            $subQ->orWhere('contacts.firstname', 'LIKE', "%{$word}%")
                                                 ->orWhere('contacts.middlename', 'LIKE', "%{$word}%")
                                                 ->orWhere('contacts.lastname', 'LIKE', "%{$word}%")
                                                 ->orWhere('contacts.secondlastname', 'LIKE', "%{$word}%");
                                        });
                                    }
                                });
                            }
                            return $query;
                        })
                );
            });
            
    }
}
