<?php

namespace App\Services;

use App\MatchEvent;
use App\Parameter;
use App\Zone;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;

class TicketsService
{
    const TRIBUNE_BLOCKING_STAGE        = 2;
    const LIMIT_TRIBUNE_CAPACITY_STAGE  = 3;
    const EXCLUSIVE_TRIBUNE_STAGE       = 5;

    public function loadStadiumZones(Request $request)
    {
        $matchEventId   = $request->matchEventId;
        $matchEvent     = MatchEvent::with('season')->where('id', $matchEventId)->first();
        $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', [self::TRIBUNE_BLOCKING_STAGE, self::EXCLUSIVE_TRIBUNE_STAGE]);
            }]);
        }
        $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 == self::EXCLUSIVE_TRIBUNE_STAGE) {
                            $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')
                                ->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 = [];
        DB::statement("SET sql_mode = ''");
        foreach ($data as $key => $zone) {
            $zone->locked = count($zone->match_event_stage);
            unset($zone->match_event_stage);
            $zones[] = $zone;

            $sql = "SELECT z.id, z.name, z.alias, GREATEST((IF(mea.id, 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, 
                        IF(CAST(IFNULL(mes.id, 0) AS UNSIGNED) > 0 || IF(CAST(IFNULL(ob1.zone_id, 0) AS UNSIGNED) > 0 && CAST(IFNULL(ob1.tags, 0) AS UNSIGNED) > 1, 1, 0),1,0) AS locked
                FROM zones z
                LEFT JOIN seats s ON s.zone_id = z.id
                LEFT JOIN pre_subscribers p ON p.seat_id = s.id " . ($userEmail ? " AND p.email != '" . $userEmail . "'" : "") . "
                LEFT JOIN tickets t ON t.seat_id = s.id AND t.match_event_id = " . $matchEventId . " AND t.ticket_status_id IN (1,2)
                LEFT JOIN ticket_user_blocks tu ON tu.seat_id = s.id AND tu.match_event_id = " . $matchEventId . "
                LEFT JOIN match_event_stages me ON me.seat_id = s.id AND me.match_event_id = " . $matchEventId . "
                LEFT JOIN match_event_stages mes ON mes.zone_id = z.id AND mes.stage_type_id = " . self::TRIBUNE_BLOCKING_STAGE . " AND mes.match_event_id = " . $matchEventId . "
                LEFT JOIN match_event_stages mea ON mea.zone_id = z.id AND mea.stage_type_id = " . self::LIMIT_TRIBUNE_CAPACITY_STAGE . " AND mea.match_event_id = " . $matchEventId;

            $sql .= " LEFT JOIN (
                    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 : "") . "
                    WHERE meex.stage_type_id = " . self::EXCLUSIVE_TRIBUNE_STAGE . "
                        AND meex.match_event_id = " . $matchEventId . "
                    GROUP BY meex.zone_id
                ) ob1 ON ob1.zone_id = z.id";

            $sql .= " WHERE z.zone_id = " . $zone->id . " AND z.active = 1";
            $sql .= " GROUP BY z.id";
            $childZones = DB::select(DB::raw($sql));
            if (!$zone->locked) {
                array_push($zones, ...$childZones);
            } else {
                foreach ($childZones as $childZone) {
                    $childZone->locked = 1;
                    array_push($zones, $childZone);
                }
            }
        }
        DB::statement("SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'");
        return response(array("r" => true, "type" => "success", "data" => $zones));
    }
}