<?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",
            DB::raw("MAX(user_informations.dob) AS dob"),
            DB::raw("MAX(user_informations.sex) AS sex"),
            DB::raw("MAX(user_informations.model) AS model"),
            DB::raw("MAX(user_informations.photo) AS userPhoto"),
            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("(
                    SELECT GROUP_CONCAT(DISTINCT c.id)
                    FROM carnets c
                    LEFT JOIN carnet_tags ct ON ct.carnet_id = c.id
                    LEFT JOIN user_tags ut 
                        ON ut.tag_id = ct.tag_id
                        AND ut.academy_user_id IS NULL
                        AND ut.user_id = users.id
                    WHERE c.active = 1
                    AND (
                        ct.carnet_id IS NULL -- Carnet sin tags
                        OR ut.id IS NOT NULL -- Carnet con tag que coincide con usuario
                    )
                ) AS cards"),
            DB::raw("(
                SELECT GROUP_CONCAT(
                    CONCAT_WS('|', 
                        a.id,
                        a.direction,
                        a.district,
                        a.tag,
                        a.active,
                        COALESCE(ci.name, ''),
                        a.lat
                        
                    ) SEPARATOR '||'
                )
                FROM addresses a
                LEFT JOIN cities ci ON ci.id = a.city_id
                WHERE a.user_id = users.id
                AND a.deleted_at IS NULL
            ) AS addresses"),
        ])
            ->leftjoin('document_types', 'document_types.id', '=', 'users.document_type_id')
            ->leftjoin('user_informations', 'user_informations.user_id', '=', 'users.id')
            ->leftJoin('user_tags', function ($join) {
                $join->on('user_tags.user_id', '=', 'users.id')->whereNull('user_tags.academy_user_id');
            })
            ->leftjoin('tags', function ($join) {
                $join->on('tags.id', '=', 'user_tags.tag_id')->where('tags.active', 1);
            })
            ->leftjoin('term_clients', 'term_clients.user_id', '=', 'users.id')
            ->leftjoin('terms', 'terms.id', '=', 'term_clients.term_id')
            ->where("users.rol_id", self::CLIENT_ROLE_ID)
            ->whereNull("users.deleted_at")
            ->groupBy("users.id")
            ->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', function ($join) {
                $join->on('tags.id', '=', 'user_tags.tag_id')->where('tags.active', 1);
            })
            ->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);
    }
}