<?php

namespace App\Repository;

use App\Planning;
use App\ResidentialComplex;
use App\RoomCount;
use App\RoomType;
use App\Section;
use Illuminate\Database\Query\Builder;

class PlanningRepository
{
    const PLANNING_FIRST_PAGE = 12;
    const PLANNING_PAGE = 12;

    public function showFilterParams($parameters = [])
    {
        $query = Planning::query();
        $query->whereNull('plannings.deleted_at')
            ->where('plannings.is_sold', 0);
        if (isset($parameters['residential_complex_filter'])) {
            $query
                ->selectRaw("min(case when (plannings.deleted_at is null) then plannings.space end)
                    as min_space")
                ->selectRaw("max(case when (plannings.deleted_at is null) then plannings.space end)
                    as max_space")
                ->selectRaw("min(case when (plannings.deleted_at is null) then plannings.total_cost end)
                    as min_price")
                ->selectRaw("max(case when (plannings.deleted_at is null) then plannings.total_cost end)
                    as max_price");
        }
        if (!isset($parameters['residential_complex_filter'])) {
            $query
                ->leftJoin('sections as s', 'plannings.section_id', '=', 's.id')
                ->leftJoin('residential_complexes as rc', 's.residental_complex_id', '=', 'rc.id')
                ->leftJoin('room_counts as room_count', 'plannings.room_id', '=', 'room_count.id')
                ->leftJoin('room_types as rt', 'plannings.room_type_id', '=', 'rt.id')
                ->when(isset($parameters['rc_slug']), function ($query) use ($parameters) {
                    $query->where('rc.code', $parameters['rc_slug']);
                });

            /** @var RoomCount $room */
            foreach (RoomCount::all() as $room) {
                $query
                    ->selectRaw("sum(case when (room_count.count = '".$room->count."' AND plannings.deleted_at is null) then 1 end) as ".$room->code."_room");
            }

            /** @var RoomType $roomType */
            foreach (RoomType::all() as $roomType) {
                $query
                    ->selectRaw("sum(case when (rt.code = '".$roomType->code."' AND plannings.deleted_at is null) then 1 end) as ". "room_" . $roomType->code);
            };
            $query
                ->selectRaw("min(case when (plannings.deleted_at is null) then plannings.space end)
                    as min_space")
                ->selectRaw("max(case when (plannings.deleted_at is null) then plannings.space end)
                    as max_space");
        }

        return $query->get();
    }

    public function filter($parameters)
    {
        $query = Planning::query();
        $query
            ->leftJoin('sections as s', 'plannings.section_id', '=', 's.id')
            ->leftJoin('residential_complexes as rc',
                's.residental_complex_id', '=', 'rc.id')
            ->leftJoin('room_counts as room_count', 'plannings.room_id', '=', 'room_count.id')
            ->leftJoin('room_types as rt', 'plannings.room_type_id', '=', 'rt.id')
            ->leftJoin('floor_planning as fp', 'plannings.id', '=', 'fp.planning_id')
            ->leftJoin('floors as f', 'fp.floor_id', '=', 'f.id')
            ->whereNull('plannings.deleted_at')

            ->where('plannings.is_sold', 0)

            ->where('s.is_planning', 1)

            ->when(isset($parameters['rc_slug']), function ($query) use ($parameters) {
                    $query->where('rc.code', $parameters['rc_slug']);
            })

            ->when(isset($parameters['section_ids']) && count($parameters['section_ids']) > 0,
                function ($query) use ($parameters) {
                    /** @var $query Builder */
                    $query->whereIn('s.id', $parameters['section_ids']);
                })
            ->when((isset($parameters['room']) && count($parameters['room']) > 0) ||
                (isset($parameters['room_types']) && count($parameters['room_types']) > 0),
                function ($query) use ($parameters) {
                    $query->where(function ($query) use ($parameters) {
                        if (isset($parameters['room']) && count($parameters['room']) > 0) {
                            $query->whereIn('room_count.count', $parameters['room'], "or");
                        }
                        if (isset($parameters['room_types']) && count($parameters['room_types']) > 0) {
                            $query->whereIn('rt.code', $parameters['room_types'], "or");
                        }
                    });
            })
            ->when(isset($parameters['space']), function ($query) use ($parameters) {
                if ($parameters['space']['min']) {
                    /** @var $query Builder */
                    $query->whereBetween('plannings.space', [$parameters['space']['min'], $parameters['space']['max']]);
                }
            })->when(isset($parameters['planning_page']), function ($query) use ($parameters) {
                if ($parameters['planning_page'] == 0) {
                    $parameters['planning_page'] = 1;
                    $resultCount = $parameters['planning_page'] * self::PLANNING_PAGE;
                    $skip = $parameters['planning_page'] * self::PLANNING_PAGE;
                } else {
                    $parameters['planning_page'] += 1;
                    $resultCount = self::PLANNING_PAGE;
                    $skip = $parameters['planning_page'] * self::PLANNING_PAGE;
                }
                $query->skip($skip)->take($resultCount);
            })
            ->when(isset($parameters['floors']) && count($parameters['floors']) > 0 && count($parameters['floors']) > 0,
                function ($query) use ($parameters) {
                    $query->whereIn('f.code', $parameters['floors']);
            });
        $query->select('plannings.*')
//            ->orderBy('s.number')
            ->orderBy('room_count.count')
            ->orderBy('plannings.space')
            ->groupBy('plannings.id');

        return $query->get();
    }

    public function residentialComplex($parameters)
    {
        $query = Section::query();
        $query
            ->leftJoin('residential_complexes as residential_complexes', 'residential_complexes.id', '=', 'sections.residental_complex_id')
            ->leftJoin('plannings as pl', 'pl.section_id', '=', 'sections.id')
            ->leftJoin('room_counts as plrc', 'pl.room_id', '=', 'plrc.id')
            ->leftJoin('room_types as rt', 'pl.room_type_id', '=', 'rt.id')
            ->when(isset($parameters['rc_slug']), function ($query) use ($parameters) {
                $query->where('residential_complexes.code', $parameters['rc_slug']);

                if (isset($parameters['section_rooms_status'])) {
                    $query->select('sections.*');
                } elseif (isset($parameters['residential_complex_rooms_status'])) {
                    $query->select('residential_complexes.*');
                }

                /** @var RoomCount $room */
                foreach (RoomCount::all() as $room) {
                    $query
                        ->selectRaw("min(case when (plrc.count = '".$room->count."' AND pl.deleted_at is null) then pl.space end) as ".$room->code."_min_space")
                        ->selectRaw("max(case when (plrc.count = '".$room->count."' AND pl.deleted_at is null) then pl.space end) as ".$room->code."_max_space")

                        ->selectRaw("min(case when (plrc.count = '".$room->count."' AND pl.deleted_at is null) then pl.total_cost end) as ".$room->code."_min_price")
                        ->selectRaw("max(case when (plrc.count = '".$room->count."' AND pl.deleted_at is null) then pl.total_cost end) as ".$room->code."_max_price")

                        ->selectRaw("min(case when (plrc.count = '".$room->count."' AND pl.deleted_at is null) then pl.cost_per_square_meter end) as ".$room->code."_min_cost_per_square_meter")

                        ->selectRaw("sum(case when (plrc.count = '".$room->count."' AND pl.deleted_at is null AND pl.is_sold = 0) then pl.amount_of_apartment end) as ".$room->code."_amount_of_apartment")
                        ->selectRaw("count(case when (plrc.count = '".$room->count."' AND pl.deleted_at is null AND pl.is_sold = 0) then 1 end) as ".$room->code."_available")
                        ->selectRaw("count(case when (plrc.count = '".$room->count."' AND pl.deleted_at is null AND pl.is_sold = 1) then pl.amount_of_apartment end) as ".$room->code."_sold");
                }

                /** @var RoomType $roomType */
                foreach (RoomType::all() as $roomType) {
                    $query
                        ->selectRaw("min(case when (rt.code = '".$roomType->code."' AND pl.deleted_at is null) then pl.space end) as ".$roomType->code."_min_space")
                        ->selectRaw("max(case when (rt.code = '".$roomType->code."' AND pl.deleted_at is null) then pl.space end) as ".$roomType->code."_max_space")

                        ->selectRaw("min(case when (rt.code = '".$roomType->code."' AND pl.deleted_at is null) then pl.total_cost end) as ".$roomType->code."_min_price")
                        ->selectRaw("max(case when (rt.code = '".$roomType->code."' AND pl.deleted_at is null) then pl.total_cost end) as ".$roomType->code."_max_price")

                        ->selectRaw("min(case when (rt.code = '".$roomType->code."' AND pl.deleted_at is null) then pl.cost_per_square_meter end) as ".$roomType->code."_min_cost_per_square_meter")

                        ->selectRaw("sum(case when (rt.code = '".$roomType->code."' AND pl.deleted_at is null AND pl.is_sold = 0) then pl.amount_of_apartment end) as ".$roomType->code."_amount_of_apartment")
                        ->selectRaw("count(case when (rt.code = '".$roomType->code."' AND pl.deleted_at is null AND pl.is_sold = 0) then 1 end) as ".$roomType->code."_available")
                        ->selectRaw("count(case when (rt.code = '".$roomType->code."' AND pl.deleted_at is null AND pl.is_sold = 1) then pl.amount_of_apartment end) as ".$roomType->code."_sold");
                }
            });

        if (isset($parameters['section_rooms_status'])) {
            $query->groupBy('sections.id');
        } elseif (isset($parameters['residential_complex_rooms_status'])) {
            $query->groupBy('residential_complexes.id');
        }

        return $query->get();
    }
}