<?php

namespace App\Repository;

use App\District;
use App\ResidentialComplex;
use App\StageOfConstruction;
use Illuminate\Database\Eloquent\Builder;

class DistrictRepository
{
    const BUILD = "built";

    public function districtStatistic()
    {
        $build = self::BUILD;
        $stageOfConstructions = StageOfConstruction::all();
        $ds = ResidentialComplex::query()
            ->leftJoin('stage_of_constructions as sc', 'residential_complexes.stage_of_construction_id', '=', 'sc.id')
            ->rightJoin('district as d', 'residential_complexes.district_id', '=', 'd.id')
            ->select([
                "d.name",
                "d.code"
            ])
            ->whereRaw('d.deleted_at is null')
            ->where('residential_complexes.enabled', 1)
            ->whereNull('residential_complexes.deleted_at')
            ->when(count($stageOfConstructions) > 0,
                function ($query) use ($stageOfConstructions, $build) {
                    $query->selectRaw("sum(case when (sc.code='being_built')then 1 else 0 end)
                         as being_built");
                    $query->selectRaw("sum(case when (sc.code='build' AND
                 residential_complexes.date_putting_into_operation >= 2018-01-01)then 1 else 0 end)
                         as build");
                    $query->selectRaw("sum(case when (residential_complexes.start_of_sale = 1)then 1 else 0 end)
                         as sale");
                })->groupBy("d.name")->get();

        return $ds;
    }

    /**
     * @param Builder $query
     * @param $parameters
     * @return Builder $query
     */
    public function districtFilter(Builder $query, $parameters)
    {
        $query->when(isset($parameters['district_code']), function (Builder $query) use ($parameters) {
            if ($parameters['district_code'] == District::FRANKIVSKYJ) {
                $query->where('ds.code', '=', District::FRANKIVSKYJ);
            } elseif ($parameters['district_code'] == District::HALYTSKYI) {
                $query->where('ds.code', '=', District::HALYTSKYI);
            } elseif ($parameters['district_code'] == District::LYCHAKIV) {
                $query->where('ds.code', '=', District::LYCHAKIV);
            } elseif ($parameters['district_code'] == District::SHEVCHENKIVSKYI) {
                $query->where('ds.code', '=', District::SHEVCHENKIVSKYI);
            } elseif ($parameters['district_code'] == District::SYKHIV) {
                $query->where('ds.code', '=', District::SYKHIV);
            } elseif ($parameters['district_code'] == District::ZALIZNYCHNYI) {
                $query->where('ds.code', '=', District::ZALIZNYCHNYI);
            } elseif ($parameters['district_code'] == District::ZALIZNYCHNYI) {
                $query->where('ds.code', '=', District::ZALIZNYCHNYI);
            } elseif ($parameters['district_code'] == District::REGION) {
                $query->where('ct.region_center', '=', 0)
                ->whereRaw('ct.deleted_at is null');
            }
        });

        return $query;
    }

    public function districtOfLvivRegion()
    {
        return District::query()
            ->leftJoin('cities as c', 'district.city_id', '=', 'c.id')
            ->leftJoin('seo_contents as sc', 'district.code', '=', 'sc.parameter_one')
            ->whereNull('sc.parameter_two')
            ->where(function ($query) {
                $query->whereNull('sc.price');
                $query->orWhere('sc.price', '=', '');
            })

//            ->whereNull('sc.price', '')
            ->where('c.code', 'lviv')
            ->whereNull('district.deleted_at')
            ->select(['district.*', 'sc.code as link'])
            ->groupBy('district.id')
            ->get();
    }
}