<?php

namespace App\PackagesAdmin\User\DataTables;

use App\Packages\Capsule\Models\Capsule;
use App\Packages\Core\Models\Gender;
use App\Packages\Friend\Models\FriendRelation;
use App\Packages\User\Models\User;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Http\JsonResponse;
use Yajra\DataTables\DataTables;

/**
 * Class UserDataTable
 * @package App\PackagesAdmin\Admin\DataTables
 */
class UserDataTable
{
	/**  @var DataTables */
	private $dataTablesInstance;

    /**
     * UserDataTable constructor.
     * @param DataTables $dataTablesInstance
     */
    public function __construct(Datatables $dataTablesInstance)
	{
		$this->dataTablesInstance = $dataTablesInstance;
	}

    /**
     * @return JsonResponse
     * @throws \Exception
     */
    public function getDataTable()
    {
        $user_table = (new User)->getTable();

        $full_name_query = "(\"user\".\"first_name\" || ' ' || \"user\".\"last_name\")";
        $is_active_query = "CASE WHEN \"user\".\"is_active\" = true THEN 'Yes' ELSE 'No' END";
        $is_private_query = "CASE WHEN \"user\".\"is_private\" = true THEN 'Yes' ELSE 'No' END";
        $is_suggested_query = "CASE WHEN \"user\".\"is_suggested\" = true THEN 'Yes' ELSE 'No' END";

        $query = \DB::table($user_table . ' as user')
            ->select([
                'user.id',
                'user.uuid',
                \DB::raw($full_name_query . " as full_name"),
                'user.nickname',
                'user.email',
                'user.phone_number',
                'total_capsule.total as count_capsules',
                'user.count_friends',
                \DB::raw($is_active_query . " as is_active"),
                \DB::raw($is_suggested_query . " as is_suggested"),
                \DB::raw($is_private_query . " as is_private"),
                'user.updated_at'
            ])
            ->leftJoin(
                \DB::raw('(SELECT COUNT(*) as total, user_id FROM table_capsule WHERE is_complete = true GROUP BY user_id) as total_capsule'),
                'total_capsule.user_id',
                '=',
                'user.id'
            )
            ->where('is_registered', true);

        return $this->dataTablesInstance
            ->queryBuilder($query)
            ->editColumn('count_capsules', function ($user) {
                return ($user->count_capsules)?:0;
            })
            ->filterColumn('full_name', function ($query, $keyword) use ($full_name_query) {
                /** @var Builder $query */
                $query->whereRaw("LOWER(" . $full_name_query . ") like ?", ["%{$keyword}%"]);
            })
            ->filterColumn('count_capsules', function ($query, $keyword) {
                /** @var Builder $query */
                if (filter_var($keyword, FILTER_VALIDATE_INT) !== FALSE) {
                    if ($keyword == 0) {
                        $query->whereNull("total_capsule.total");
                    } else {
                        $query->where("total_capsule.total", '=', $keyword);
                    }
                }
            })
            ->filterColumn('is_active', function ($query, $keyword) use ($is_active_query) {
                /** @var Builder $query */
                $query->whereRaw("LOWER(CAST(" . $is_active_query . " as TEXT)) like ?", ["%{$keyword}%"]);
            })
            ->filterColumn('is_private', function ($query, $keyword) use ($is_private_query) {
                /** @var Builder $query */
                $query->whereRaw("LOWER(CAST(" . $is_private_query . " as TEXT)) like ?", ["%{$keyword}%"]);
            })
            ->filterColumn('is_suggested', function ($query, $keyword) use ($is_suggested_query) {
                /** @var Builder $query */
                $query->whereRaw("LOWER(CAST(" . $is_suggested_query . " as TEXT)) like ?", ["%{$keyword}%"]);
            })
            ->addColumn('showLink', function ($user) {
                return route('admin.user.show', ['id' => $user->id]);
            })
            ->addColumn('editLink', function ($user) {
                return route('admin.user.edit', ['id' => $user->id]);
            })
            ->addColumn('blockLink', function ($user) {
                return route('admin.user.toggle_block', ['id' => $user->id]);
            })
            ->addColumn('deleteLink', function ($user) {
                return route('admin.user.destroy', ['id' => $user->id]);
            })
            ->make();
    }
}