<?php

namespace App\DataTables;

use App\Models\SpeciesSurvey;
use Illuminate\Database\Eloquent\Builder as QueryBuilder;
use Yajra\DataTables\EloquentDataTable;
use Yajra\DataTables\Html\Builder as HtmlBuilder;
use Yajra\DataTables\Html\Button;
use Yajra\DataTables\Html\Column;
use Yajra\DataTables\Services\DataTable;

class SpeciesSurveysDataTable extends DataTable
{
    /**
     * Build the DataTable class.
     *
     * @param QueryBuilder<SpeciesSurvey> $query Results from query() method.
     */
    public function dataTable(QueryBuilder $query): EloquentDataTable
    {
        $statsResults = (clone $query)->get();
        $totalSurveys = $statsResults->count();
        $totalStates = $statsResults->pluck('state_name')->unique()->filter()->count();
        $totalLandingCenters = $statsResults->pluck('landing_center_name')->unique()->filter()->count();

        return (new EloquentDataTable($query))
            ->addIndexColumn()
            ->addColumn('action', function ($row) {
                $editUrl = route('admin.species-surveys.edit', $row->id);
                $imageUrls = $row->image_urls ?? '';
                $speciesName = $row->species_name ?? 'Survey';
                
                return '<div class="btn-list d-flex gap-1 justify-content-center">
                    <a href="#" class="btn btn-sm btn-primary px-3 view-image-btn" data-image-urls="' . $imageUrls . '" data-species-name="' . htmlspecialchars($speciesName) . '">Images</a>
                    <a href="' . $editUrl . '" class="btn btn-sm btn-dark px-3">Edit</a>
                    <button type="button" class="btn btn-sm btn-danger px-2 delete-btn" data-id="' . $row->id . '">
                        <i class="fe fe-trash-2"></i>
                    </button>
                </div>';
            })
            ->editColumn('species_name', function ($row) {
                return $row->species_name ?? '<span class="text-muted">N/A</span>';
            })
            ->editColumn('landing_center_name', function ($row) {
                return $row->landing_center_name ?? '<span class="text-muted">N/A</span>';
            })
            ->editColumn('gear_name', function ($row) {
                return $row->gear_name ?? '<span class="text-muted">N/A</span>';
            })
            ->editColumn('user_name', function ($row) {
                return $row->user_name ?? '<span class="text-muted">N/A</span>';
            })
            ->editColumn('user.email', function ($row) {
                return $row->user?->email ?? '<span class="text-muted">N/A</span>';
            })
            ->editColumn('created_at', function ($row) {
                return $row->created_at ? (\Carbon\Carbon::parse($row->created_at)->format('Y-m-d H:i:s')) : '';
            })
            ->rawColumns(['action', 'species_name', 'landing_center_name', 'gear_name', 'user_name', 'user.email'])
            ->with([
                'totalSurveys' => $totalSurveys,
                'totalStates' => $totalStates,
                'totalLandingCenters' => $totalLandingCenters,
            ]);
    }

    /**
     * Get the query source of dataTable.
     *
     * @return QueryBuilder<SpeciesSurvey>
     */
    public function query(SpeciesSurvey $model): QueryBuilder
    {
        $query = $model->newQuery()->with('user');

        if ($stateId = request('state_id')) {
            $state = \Illuminate\Support\Facades\DB::table('states')->find($stateId);
            if ($state) {
                $query->where('state_name', $state->name);
            }
        }

        if ($speciesId = request('species_id')) {
            $species = \Illuminate\Support\Facades\DB::table('speacies')->find($speciesId);
            if ($species) {
                $query->where('species_name', $species->name);
            }
        }

        if ($landingCenterId = request('landing_center_id')) {
            $landingCenter = \Illuminate\Support\Facades\DB::table('landing_centers')->find($landingCenterId);
            if ($landingCenter) {
                $query->where('landing_center_name', $landingCenter->name);
            }
        }

        if ($userId = request('user_id')) {
            $query->where('user_id', $userId);
        }

        if ($datePeriod = request('date_period')) {
            if ($datePeriod === 'today') {
                $query->whereDate('created_at', \Carbon\Carbon::today());
            } elseif ($datePeriod === 'yesterday') {
                $query->whereDate('created_at', \Carbon\Carbon::yesterday());
            } elseif ($datePeriod === 'last_7_days') {
                $query->where('created_at', '>=', \Carbon\Carbon::today()->subDays(6)->startOfDay());
            } elseif ($datePeriod === 'last_week') {
                $startOfWeek = \Carbon\Carbon::now()->subWeek()->startOfWeek();
                $endOfWeek = \Carbon\Carbon::now()->subWeek()->endOfWeek();
                $query->whereBetween('created_at', [$startOfWeek, $endOfWeek]);
            } elseif ($datePeriod === 'last_30_days') {
                $query->where('created_at', '>=', \Carbon\Carbon::today()->subDays(29)->startOfDay());
            } elseif ($datePeriod === 'this_month') {
                $query->whereBetween('created_at', [
                    \Carbon\Carbon::now()->startOfMonth(),
                    \Carbon\Carbon::now()->endOfMonth()
                ]);
            } elseif ($datePeriod === 'month_to_date') {
                $query->whereBetween('created_at', [
                    \Carbon\Carbon::now()->startOfMonth(),
                    \Carbon\Carbon::now()
                ]);
            } elseif ($datePeriod === 'last_month') {
                $query->whereBetween('created_at', [
                    \Carbon\Carbon::now()->subMonth()->startOfMonth(),
                    \Carbon\Carbon::now()->subMonth()->endOfMonth()
                ]);
            } elseif ($datePeriod === 'this_year') {
                $query->whereYear('created_at', \Carbon\Carbon::now()->year);
            } elseif ($datePeriod === 'custom') {
                if ($startDate = request('start_date')) {
                    $query->whereDate('created_at', '>=', $startDate);
                }
                if ($endDate = request('end_date')) {
                    $query->whereDate('created_at', '<=', $endDate);
                }
            }
        }

        $query->select([
            \Illuminate\Support\Facades\DB::raw('MIN(id) as id'),
            'user_id',
            'species_id',
            \Illuminate\Support\Facades\DB::raw('MIN(species_name) as species_name'),
            'state_id',
            \Illuminate\Support\Facades\DB::raw('MIN(state_name) as state_name'),
            'landing_center_id',
            \Illuminate\Support\Facades\DB::raw('MIN(landing_center_name) as landing_center_name'),
            \Illuminate\Support\Facades\DB::raw('MIN(user_name) as user_name'),
            \Illuminate\Support\Facades\DB::raw('MIN(gear_name) as gear_name'),
            \Illuminate\Support\Facades\DB::raw('GROUP_CONCAT(image) as images'),
            \Illuminate\Support\Facades\DB::raw('GROUP_CONCAT(image_url) as image_urls'),
            'created_at',
        ])
        ->groupBy('user_id', 'species_id', 'state_id', 'landing_center_id', 'created_at');

        return $query;
    }

    /**
     * Optional method if you want to use the html builder.
     */
    public function html(): HtmlBuilder
    {
        $filterScript = "
            data.state_id = $('#filter_state_id').val();
            data.landing_center_id = $('#filter_landing_center_id').val();
            data.species_id = $('#filter_species_id').val();
            data.user_id = $('#filter_user_id').val();
            data.date_period = $('#filter_date_period').val();
            data.start_date = $('#filter_start_date').val();
            data.end_date = $('#filter_end_date').val();
        ";

        return $this->builder()
                    ->setTableId('species-surveys-table')
                    ->columns($this->getColumns())
                    ->minifiedAjax('', $filterScript)
                    ->orderBy(1, 'asc') // Order by SPECIES NAME by default
                    ->selectStyleSingle()
                    ->parameters([
                        'responsive' => true,
                        'autoWidth' => false,
                    ])
                    ->buttons([
                        Button::make('excel'),
                        Button::make('csv'),
                        Button::make('pdf'),
                        Button::make('print'),
                        Button::make('reset'),
                        Button::make('reload')
                    ]);
    }

    /**
     * Get the dataTable columns definition.
     */
    public function getColumns(): array
    {
        return [
            Column::computed('DT_RowIndex')
                ->title('NO')
                ->addClass('align-middle text-center')
                ->width(50)
                ->orderable(false)
                ->searchable(false),
            Column::make('species_name')->title('SPECIES NAME')->addClass('align-middle'),
            Column::make('landing_center_name')->title('LANDING CENTER NAME')->addClass('align-middle'),
            Column::make('gear_name')->title('GEAR NAME')->addClass('align-middle'),
            Column::make('user_name')->title('USER NAME')->addClass('align-middle'),
            Column::make('user.email')->title('EMAIL')->addClass('align-middle')->orderable(false),
            Column::make('created_at')->title('SURVEY DATE')->addClass('align-middle'),
            Column::computed('action')
                ->title('ACTIONS')
                ->exportable(false)
                ->printable(false)
                ->addClass('align-middle text-center'),
        ];
    }

    /**
     * Get the filename for export.
     */
    protected function filename(): string
    {
        return 'SpeciesSurveys_' . date('YmdHis');
    }
}
