<?php

declare(strict_types=1);

namespace App\Core\Customer;

use DateTime;
use Illuminate\Support\Facades\DB;

class CustomerTableService
{
    const CLIENT_ROLE_ID = 4;

    private $query;

    public function getTableQuery($request)
    {
        $this->query = DB::table("users");
        $this->query->select([
                "users.id",
                "users.first_name",
                "users.last_name",
                "users.email",
                "users.active",
                "users.is_subscriber",
                "users.document",
                "users.phone",
                "users.created_at",
                "users.last_session",
                "users.last_session as time_session",
                "document_types.name as documentType",
                "document_types.alias as documentTypeAlias",
                "user_informations.dob",
                "user_informations.sex",
                "user_informations.model",
                DB::raw("GROUP_CONCAT(DISTINCT(tags.name)) AS segmentation"),
                DB::raw("GROUP_CONCAT(DISTINCT(term_clients.accepted)) AS accepted"),
                DB::raw("GROUP_CONCAT(DISTINCT(terms.version)) AS terms_conditions"),
                DB::raw("GROUP_CONCAT(DISTINCT(IF(carnets.active IS TRUE, carnets.id, ''))) AS cards"),
            ])
            ->leftjoin('document_types', 'document_types.id', '=', 'users.document_type_id')
            ->leftjoin('user_informations', 'user_informations.user_id', '=', 'users.id')
            ->leftjoin('user_tags', 'user_tags.user_id', '=', 'users.id')
            ->leftjoin('tags', 'tags.id', '=', 'user_tags.tag_id')
            ->leftjoin('term_clients', 'term_clients.user_id', '=', 'users.id')
            ->leftjoin('terms', 'terms.id', '=', 'term_clients.term_id')
            ->leftjoin('carnet_tags', 'carnet_tags.tag_id', '=', 'user_tags.tag_id')
            ->leftjoin('carnets', 'carnets.id', '=', 'carnet_tags.carnet_id')
            ->where("users.rol_id", self::CLIENT_ROLE_ID)
            ->whereNull("users.deleted_at")
            ->groupBy("users.id")
            ->groupBy("users.first_name")
            ->groupBy("users.last_name")
            ->groupBy("users.email")
            ->groupBy("users.active")
            ->groupBy("users.is_subscriber")
            ->groupBy("users.document")
            ->groupBy("users.phone")
            ->groupBy("users.created_at")
            ->groupBy("users.last_session")
            ->groupBy("document_types.name")
            ->groupBy("document_types.alias")
            ->groupBy("user_informations.dob")
            ->groupBy("user_informations.sex")
            ->groupBy("user_informations.model")
            ->orderBy("users.created_at", 'DESC')
        ;

        $this->addTableQueryFilters($request);

        return $this->query;
    }

    public function getTableCount($request)
    {
        $this->query = DB::table("users");
        $this->query->selectRaw(
                DB::raw("COUNT(DISTINCT(users.id)) as count")
            )
            ->leftjoin('user_tags', 'user_tags.user_id', '=', 'users.id')
            ->leftjoin('tags', 'tags.id', '=', 'user_tags.tag_id')
            ->where("users.rol_id", self::CLIENT_ROLE_ID)
            ->whereNull("users.deleted_at")
        ;

        $this->addTableQueryFilters($request);

        $count = $this->query->pluck("count");
        return $count->first();
    }

    private function addTableQueryFilters($request)
    {
        $subscriber = $request->check;
        $tags = $request->status;
        $from_date = $request->from_date;
        $to_date = $request->to_date;
        $last_entry_from_date = $request->last_entry_from_date;
        $last_entry_to_date = $request->last_entry_to_date;

        if ($subscriber != "0") {
            $this->isSubscriber($subscriber == "1");
        }

        // filtro de registro
        if (!is_null($from_date)) {
            $this->registeredFromDate(new DateTime($from_date));
        }
        if (!is_null($to_date)) {
            $this->registeredToDate(new DateTime($to_date));
        }

        // filtro de ultimo ingreso
        if (!is_null($last_entry_from_date)) {
            $this->lastLoginDateFrom(new DateTime($last_entry_from_date));
        }
        if (!is_null($last_entry_to_date)) {
            $this->lastLoginDateTo(new DateTime($last_entry_to_date));
        }

        if (!is_null($tags)) {
            $this->withTags($tags);
        }
    }

    private function isSubscriber(bool $isSubscriber)
    {
        $this->query->where('users.is_subscriber', $isSubscriber);
    }

    private function registeredFromDate(DateTime $registeredFromDate)
    {
        $this->query->where('users.created_at', '>=', $registeredFromDate);
    }

    private function registeredToDate(DateTime $registeredToDate)
    {
        $this->query->where('users.created_at', '<=', $registeredToDate);
    }

    private function lastLoginDateFrom(DateTime $lastLoginDateFrom)
    {
        $this->query->where('users.last_session', '>=', $lastLoginDateFrom);
    }

    private function lastLoginDateTo(DateTime $lastLoginDateTo)
    {
        $this->query->where('users.last_session', '<=', $lastLoginDateTo);
    }

    private function withTags(array $tags)
    {
        $this->query->whereIn('tags.id', $tags);
    }
}