<?php

namespace App\Services;

use App\Core\Ticket\Entities\TransferStatusEnum;
use App\Core\Ticket\StageTypesEnum;
use App\Core\Ticket\TicketStatusEnum;
use App\Core\Ticket\TicketTypesEnum;
use App\MatchEvent;
use App\Parameter;
use App\Ticket;
use App\Zone;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;

class TicketsService
{
    public function loadStadiumZones(Request $request)
    {
        $matchEventId   = $request->matchEventId;
        $matchEvent     = MatchEvent::with('season')->where('id', $matchEventId)->first();
        if (!$matchEvent->active || $matchEvent->event_start_sale > Carbon::now()->format('Y-m-d H:i:s') || $matchEvent->event_end_sale < Carbon::now()->format('Y-m-d H:i:s')) {
            return response(array("r" => false, "m" => 'El evento no se encuentra disponible para ventas', "data" => null));
        }

        $stadiumsZone   = $matchEvent->zone_id;
        $query          = Zone::select('id', 'name', 'alias', DB::raw('1 AS available_seats'))->where('active', 1)->where('zone_id', $stadiumsZone);
        if ($matchEventId) {
            $query = $query->with(['match_event_stage' => function ($q) use ($matchEventId) {
                $q->where('match_event_id', $matchEventId)
                    ->whereIn('stage_type_id', [StageTypesEnum::BLOCK_TRIBUNE, StageTypesEnum::EXCLUSIVE_TRIBUNE]);
            }]);
        }
        $data = $query->get();
        $parameters = Parameter::select('presuscription')->first();
        $presubscriptionActive = $parameters->presuscription && $matchEvent->season->is_suscription;
        $userEmail = null;
        $userId = null;
        if (Auth::user()) {
            $userEmail = Auth::user()->email;
            $userId = Auth::user()->id;
            foreach ($data as $zone) {
                if (count($zone->match_event_stage)) {
                    foreach ($zone->match_event_stage as $key => $stage) {
                        if ($stage->stage_type_id == StageTypesEnum::EXCLUSIVE_TRIBUNE) {
                            $stageUser = DB::table('match_event_stages')
                                ->leftJoin('match_event_stage_tags', 'match_event_stage_tags.match_event_stage_id', '=', 'match_event_stages.id')
                                ->leftJoin('user_tags', 'user_tags.tag_id', '=', 'match_event_stage_tags.tag_id')
                                ->leftjoin('tags', function ($join) {
                                    $join->on('tags.id', '=', 'user_tags.tag_id')->where('tags.active', 1);
                                })
                                ->where('match_event_stages.id', $stage->id)
                                ->where(function ($q) use ($userId) {
                                    $q->where('user_tags.user_id', $userId)->orWhereNull('match_event_stage_tags.id');
                                })->count();
                            if ($stageUser)
                                unset($zone->match_event_stage[$key]);
                        }
                    }
                }
            }
        }

        $zones = [];
        foreach ($data as $key => $zone) {
            $zone->locked = count($zone->match_event_stage);
            unset($zone->match_event_stage);
            $zones[] = $zone;

            $childZones = DB::table('zones as z')
                ->select(
                    'z.id',
                    'z.name',
                    'z.alias',
                    DB::raw('GREATEST((IF(MAX(mea.id), MAX(mea.salable_capacity), z.salable_capacity) - SUM(IF(IF(t.id, 1, 0) + IF(tu.id, 1, 0) + IF(me.id, 1, 0) + ' . ($presubscriptionActive ? 'IF(p.id, 1, 0)' : '0') . ' > 0, 1, 0))), 0) AS available_seats'),
                    DB::raw('IF(CAST(IFNULL(MAX(mes.id), 0) AS UNSIGNED) > 0 || IF(CAST(IFNULL(ob1.zone_id, 0) AS UNSIGNED) > 0 && CAST(IFNULL(MAX(ob1.tags), 0) AS UNSIGNED) < 1, 1, 0), 1, 0) AS locked')
                )
                ->leftJoin('seats as s', 's.zone_id', '=', 'z.id')
                ->leftJoin('pre_subscribers as p', function ($join) use ($userEmail) {
                    $join->on('p.seat_id', '=', 's.id');
                    if ($userEmail) {
                        $join->where('p.email', '!=', $userEmail);
                    }
                })
                ->leftJoin('tickets as t', function ($join) use ($matchEventId) {
                    $join->on('t.seat_id', '=', 's.id')
                        ->where('t.match_event_id', $matchEventId)
                        ->whereIn('t.ticket_status_id', [TicketStatusEnum::PURCHASED, TicketStatusEnum::CREATED]);
                })
                ->leftJoin('ticket_user_blocks as tu', function ($join) use ($matchEventId) {
                    $join->on('tu.seat_id', '=', 's.id')
                        ->where('tu.match_event_id', $matchEventId);
                })
                ->leftJoin('match_event_stages as me', function ($join) use ($matchEventId) {
                    $join->on('me.seat_id', '=', 's.id')
                        ->where('me.match_event_id', $matchEventId);
                })
                ->leftJoin('match_event_stages as mes', function ($join) use ($matchEventId) {
                    $join->on('mes.zone_id', '=', 'z.id')
                        ->where('mes.stage_type_id', StageTypesEnum::BLOCK_TRIBUNE)
                        ->where('mes.match_event_id', $matchEventId);
                })
                ->leftJoin('match_event_stages as mea', function ($join) use ($matchEventId) {
                    $join->on('mea.zone_id', '=', 'z.id')
                        ->where('mea.stage_type_id', StageTypesEnum::LIMIT_TRIBUNE_CAPACITY)
                        ->where('mea.match_event_id', $matchEventId);
                })
                ->leftJoin(DB::raw('(SELECT meex.zone_id, COUNT(ut.id) AS tags
                                        FROM match_event_stages meex
                                        LEFT JOIN match_event_stage_tags mest ON mest.match_event_stage_id = meex.id
                                        LEFT JOIN user_tags ut ON ut.tag_id = mest.tag_id ' . ($userId ? " AND ut.user_id = " . $userId : "") . '
                                        LEFT JOIN tags ON tags.id = ut.tag_id AND tags.active = 1
                                        WHERE meex.stage_type_id = ' . StageTypesEnum::EXCLUSIVE_TRIBUNE . '
                                            AND meex.match_event_id = ' . $matchEventId . '
                                        GROUP BY meex.zone_id
                                    ) ob1'), 'ob1.zone_id', '=', 'z.id')
                ->where('z.zone_id', $zone->id)
                ->where('z.active', 1)
                ->groupBy('z.id')
                ->get();

            if (!$zone->locked) {
                array_push($zones, ...$childZones);
            } else {
                foreach ($childZones as $childZone) {
                    $childZone->locked = 1;
                    array_push($zones, $childZone);
                }
            }
        }
        return response(array("r" => true, "type" => "success", "data" => $zones));
    }

    public function getComplimentaryTicketsByUser($matchEventId, $zone)
    {
        $userId = Auth::id();
        return Ticket::select([
            'tickets.id',
            'match_event_id',
            'zone',
            'code_ticket',
            'tickets.number_transfers',
            'seat_id',
            DB::raw('DATE_ADD(ticket_user_logs.updated_at, INTERVAL 30 MINUTE) AS expires_at'),
            // 'ticket_user_logs.expires_at',
            'ticket_user_logs.previous_user_id',
            'ticket_user_logs.new_user_id',
            'ticket_user_logs.status',
            'ticket_user_logs.transfer_group',
            'ticket_user_logs.created_at',
            'ticket_user_logs.updated_at',
            'ticket_user_logs.email',
        ])
            ->with(['match_event' => function ($query) {
                $query->addSelect([
                    'id',
                    'name',
                    'event_start',
                    'stadium_to_play',
                    'sales_type',
                ]);
            }])
            ->with('seat:id,code,letter_id,zone_id')
            ->with('ticket_user_log')
            ->leftJoin('ticket_user_logs', function ($join) {
                $join->on('tickets.id', '=', 'ticket_user_logs.ticket_id')
                    ->whereRaw('ticket_user_logs.id = (SELECT MAX(ticket_user_logs.id) FROM ticket_user_logs WHERE ticket_id = tickets.id)');
            })
            ->where(function ($query) use ($userId) {
                $query->where('user_id', $userId)->orWhere(function ($query1) use ($userId) {
                    $query1->where('previous_user_id', $userId)->where('status', TransferStatusEnum::ACCEPTED);
                });
            })
            ->where('ticket_type_id', TicketTypesEnum::COMPLIMENTARY)
            ->whereIn('ticket_status_id', [TicketStatusEnum::PURCHASED, TicketStatusEnum::CREATED])
            ->where('match_event_id', $matchEventId)
            ->where('zone', $zone)
            ->get();
    }

    public function getComplimentaryTicketsSummaryByZoneByUser($userId)
    {
        return Ticket::select([
            'match_event_id',
            'zone',
            DB::raw('count(id) as count')
        ])
            ->with(['match_event' => function ($query) {
                $query->addSelect([
                    'id',
                    'name',
                    'event_start',
                    'stadium_to_play',
                    'sales_type',
                ]);
            }])
            ->where('user_id', $userId)
            ->where('ticket_type_id', TicketTypesEnum::COMPLIMENTARY)
            ->whereIn('ticket_status_id', [TicketStatusEnum::PURCHASED, TicketStatusEnum::CREATED])
            ->groupBy('match_event_id', 'zone')
            ->get();
    }
}