<?php

namespace App\Repository;

use App\ResidentialComplex;
use App\RoomCount;
use App\RoomType;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\DB;

class ResidentialComplexRepository
{
    const MAIN_PAGE_COUNT = 10;
    const OFFSET_COUNT = 12;
    const SHOW_RC = 3;
    const WISH_LIST_OFFSET_COUNT = 3;
    const DEVELOPER_OFFSET_COUNT = 6;
    const DELETED_AT = 'deleted_at';
    const DELETED_AT_NULL = null;
    const BUILD = "build";
    const BUILT = "built";
    const SALE = "sale";
    const BEING_BUILT = "being_built";
    const ALL = "all";

    public function listResidentialComplex($parameters = null, $isMainPage = false)
    {
        $user = isset($parameters['user']) ? $parameters['user'] : null;
        $rc = ResidentialComplex::query()
        ->with('developer', 'street_name', 'city', 'icon', 'media');
        if (isset($parameters['developer_id']) || isset($parameters['developer_slug'])) {
            $rc->leftJoin('developers as dv', 'residential_complexes.developer_id', '=', 'dv.id');
        }
        if (isset($parameters['space']) || isset($parameters['price']) || isset($parameters['room_counts']) || isset($parameters['room_types'])) {
            $rc->leftJoin('prices as pr', 'residential_complexes.id', '=', 'pr.residential_complex_id');
        }
        if (isset($parameters['room_counts'])) {
            $rc->leftJoin('room_counts as plrc', 'pr.room_count_id', '=', 'plrc.id');
        }
        if (isset($parameters['room_types'])) {
            $rc->leftJoin('room_types as rt', 'pr.room_type_id', '=', 'rt.id');
        }
        if (isset($parameters['developer_code'])) {
            $rc->leftJoin('stage_of_constructions as sc', 'residential_complexes.stage_of_construction_id', '=', 'sc.id');
        }

            //->leftJoin('customer_ratings as cus_rat', 'residential_complexes.id',
        // '=', 'cus_rat.residental_complex_id')
//            ->leftJoin('room_counts as plrc', 'pr.room_count_id', '=', 'plrc.id')
//            ->leftJoin('room_types as rt', 'pr.room_type_id', '=', 'rt.id');
//            ->leftJoin('sections as section', 'residential_complexes.id', '=', 'section.residental_complex_id')
//            ->leftJoin('plannings as pl', 'pl.section_id', '=', 'section.id')
//            ->leftJoin('room_counts as plrc', 'pl.room_id', '=', 'plrc.id')
//            ->leftJoin('room_types as rt', 'pl.room_type_id', '=', 'rt.id')

            $rc->when(isset($parameters['wishList_user']), function ($query) use ($parameters) {
                $query->leftJoin('wishlist_user_residental_coms as wlrc', 'wlrc.residental_complex_id', '=', 'residential_complexes.id')
                ->where('wlrc.user_id', '=', $parameters['wishList_user']);
            })

            ->when(isset($parameters['wishList_page']), function ($query) use ($parameters) {
                $query->skip($parameters['wishList_page'] * self::WISH_LIST_OFFSET_COUNT)
                    ->take(self::WISH_LIST_OFFSET_COUNT);
            })

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

            ->when(isset($parameters['developer_id']), function ($query) use ($parameters) {
                $query->where('dv.id', '=', $parameters['developer_id']);
            })

            ->when(isset($parameters['hide_residential_complex_id']), function ($query) use ($parameters) {
                $query->where('residential_complexes.id', "!=", $parameters['hide_residential_complex_id']);
            })

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

            ->when(isset($parameters['developer_page']), function ($query) use ($parameters) {
                $query->skip($parameters['developer_page'] * self::DEVELOPER_OFFSET_COUNT)
                    ->take(self::DEVELOPER_OFFSET_COUNT);
            })

            ->when(isset($parameters['developer_code']), function ($query) use ($parameters) {
                if ($parameters['developer_code'] == self::BEING_BUILT) {
                    $query->where('sc.code', '=', self::BEING_BUILT);
                } elseif ($parameters['developer_code'] == self::SALE) {
                    $query->where('start_of_sale', 1);
                } elseif ($parameters['developer_code'] == self::BUILT) {
                    $query->where('sc.code', '=', self::BUILD);
                } elseif ($parameters['developer_code'] == self::ALL) {
                }
            })

            ->when(isset($parameters['page']), function ($query) use ($parameters) {
                $query->skip($parameters['page'] * self::OFFSET_COUNT)->take(self::OFFSET_COUNT);
            })

            ->when(isset($parameters['residential_complex_ids']), function ($query) use ($parameters) {
                    $query->whereIn('residential_complexes.id', $parameters['residential_complex_ids']);
            })

            ->when((isset($parameters['cities']) && count($parameters['cities']) > 0) || (isset($parameters['districts']) && count($parameters['districts']) > 0) || (isset($parameters['villages']) && count($parameters['villages']) > 0), function ($query) use ($parameters) {
                $query
                    ->leftJoin('district as ds', 'residential_complexes.district_id', '=', 'ds.id')
                    ->leftJoin('cities as city', 'residential_complexes.city_id', '=', 'city.id')
                    ->where(function ($query) use ($parameters) {
                        if (isset($parameters['cities']) && count($parameters['cities']) > 0) {
                            $query
                                ->whereIn('city.id', $parameters['cities'], "or");
                        }
                        if (isset($parameters['villages']) && count($parameters['villages']) > 0) {
                            $query
                                ->whereIn('city.id', $parameters['villages'], "or");
                        }
                        if (isset($parameters['districts']) && count($parameters['districts']) > 0) {
                            $query
                                ->whereIn('ds.id', $parameters['districts'], "or");
                        }
                    });
            })

            ->when(isset($parameters['space']), function ($query) use ($parameters) {
                if ($parameters['space']['min']) {
                    /** @var $query Builder */
                    $query->where(function ($queries) use ($parameters) {
                        $queries->where('pr.max_space', '>=', $parameters['space']['min']);
                        $queries->where('pr.min_space', '<=', $parameters['space']['max']);
                    });

                    $query->orWhere(function ($queries) use ($parameters) {
                        $queries->whereBetween('pr.min_space', [$parameters['space']['min'], $parameters['space']['max']]);
                    });
                }
            })

            ->when(isset($parameters['price']), function ($query) use ($parameters) {
                if ($parameters['price']['min'] && $parameters['price']['max']) {
                    /** @var $query Builder */
//                    $query->whereBetween('pl.total_cost', [$parameters['price']['min'], $parameters['price']['max']]);

                    $query->where(function ($queries) use ($parameters) {
                        $queries->where('pr.max_price', '>=', $parameters['price']['min']);
                        $queries->where('pr.min_price', '<=', $parameters['price']['max']);
                    });

                    $query->orWhere(function ($queries) use ($parameters) {
                        $queries->whereBetween('pr.min_price', [$parameters['price']['min'], $parameters['price']['max']]);
                    });
                }
            })

            ->when((isset($parameters['room_counts']) && count($parameters['room_counts']) > 0) || (isset($parameters['room_types']) && count($parameters['room_types']) > 0), function ($query) use ($parameters) {
                $query->where(function ($query) use ($parameters) {
                    if (isset($parameters['room_counts']) && count($parameters['room_counts']) > 0) {
                        $query
                            ->whereIn('plrc.count', $parameters['room_counts'], "or");
                    }

                    if (isset($parameters['room_types']) && count($parameters['room_types']) > 0) {
                        $query
                            ->whereIn('rt.code', $parameters['room_types'], "or");
                    }
                });
            })

            ->when(isset($parameters['has_promotion']), function ($query) use ($parameters) {
                $query->whereExists(function ($query) {
                        $query->select(DB::raw(1))
                            ->from('promotions')
                            ->whereRaw('promotions.residental_complex_id = residential_complexes.id');
                });
            })
            ->when(isset($parameters['min_rating']), function ($query) use ($parameters) {
                $query->where('residential_complexes.average', '>=', $parameters['min_rating']);
            })
            ->when(isset($parameters['is_single_sales_department']), function ($query) use ($parameters) {
                $query->where('residential_complexes.is_repair', '=', $parameters['is_single_sales_department']);
            })

            ->when(isset($parameters['is_credit']), function ($query) use ($parameters) {
                $query->where('residential_complexes.is_credit', '=', $parameters['is_credit']);
            })

            ->when(isset($parameters['is_transport_hub']), function ($query) use ($parameters) {
                $query->where('residential_complexes.is_transport_hub', '=', $parameters['is_transport_hub']);
            })

            ->when(isset($parameters['date']), function ($query) use ($parameters) {
                if (isset($parameters['date']['from']) && isset($parameters['date']['to'])) {
                    /** @var $query Builder */
                    $query
                        ->whereBetween('residential_complexes.date_putting_into_operation',
                            [$parameters['date']['from'], $parameters['date']['to']]);
                }
            })

            ->when(isset($parameters['stage_of_construction']), function ($query) use ($parameters) {
                $query
                    ->whereIn('residential_complexes.stage_of_construction_id', $parameters['stage_of_construction']);
            })

            ->when(isset($parameters['class_housings']), function ($query) use ($parameters) {
                $query
                    ->leftJoin('class_housing_residential_complex as class_housing', 'residential_complexes.id', '=', 'class_housing.residential_complex_id')
                    ->whereIn('class_housing.class_housing_id', $parameters['class_housings']);
            })

            ->when(isset($parameters['heating']), function ($query) use ($parameters) {
                $query
                    ->leftJoin('heating_residential_complex as rc_heating', 'residential_complexes.id', '=', 'rc_heating.residential_complex_id')
                    ->whereIn('rc_heating.heating_id', $parameters['heating']);
            })

            ->when(isset($parameters['technology_buildings']), function ($query) use ($parameters) {
                $query
                    ->leftJoin('residential_complex_technology_building as rc_tb', 'residential_complexes.id', '=', 'rc_tb.residential_complex_id')
                    ->whereIn('rc_tb.technology_building_id', $parameters['technology_buildings']);
            })

            ->when(isset($parameters['other_options']), function ($query) use ($parameters) {
                $query
                    ->leftJoin('other_option_residential_complex as rc_oo', 'residential_complexes.id', '=', 'rc_oo.residential_complex_id')
                    ->whereIn('rc_oo.other_option_id', $parameters['other_options']);
            })

            ->when($user, function ($query) use ($user) {
                $query
                    ->leftJoin('wishlist_user_residental_coms as wl', 'wl.residental_complex_id', '=', 'residential_complexes.id')
                    ->selectRaw("sum(case when (wl.user_id = $user AND wl.deleted_at is null) then 1 else 0 end)
                 as is_subscribed");
            })

            ->select([
                "residential_complexes.id",
            ]);

            $rc = $this->residentialComplexSelect($rc)

            ->where('residential_complexes.enabled', 1)
            ->whereRaw('residential_complexes.deleted_at is null')
//            ->whereRaw('cus_rat.deleted_at is null AND cus_rat.enabled = 1')
            ->when($isMainPage, function ($query) use ($isMainPage) {
                if ($isMainPage) {
                    $query->orderBy('residential_complexes.main_page_priority', "DESC");
                    $query->limit(self::MAIN_PAGE_COUNT);
                }
            })
            ->when($isMainPage == false, function ($query) use ($isMainPage) {
                if ($isMainPage == false) {
                    $query->orderBy('residential_complexes.res_com_page_priority', "DESC");
                    $query->orderBy('residential_complexes.res_com_page_priority', "DESC");
                }
            })

            ->when(isset($parameters['developer_residential_complex']), function ($query) use ($parameters) {
                if ($parameters['developer_residential_complex']) {
                    $query->orderBy('residential_complexes.main_page_priority', "DESC");
                    $query->limit(self::SHOW_RC);
                }
            })

            ->groupBy('residential_complexes.id')
            ->get();

        return $rc;
    }

    protected function residentialComplexSelect($query)
    {
        $query->when(!isset($parameters['count_of_rc']), function ($query) {
            $query
                ->select([
                    "residential_complexes.*",
//                    "dv.name as developer_name",
//                    "dv.phone as developer_phone",
                ]);
//                ->selectRaw("min(case when (pl.deleted_at is null) then pl.total_cost end)
//                     as lowest_price_at_residential_complex");
//
//            /** @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("min(case when (plrc.count = '".$room->count."' AND pl.deleted_at is null) then pl.total_cost end) as ".$room->code."_min_price");
//            }
//
//            /** @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("min(case when (rt.code = '".$roomType->code."' AND pl.deleted_at is null) then pl.total_cost end) as ".$roomType->code."_min_price");
//            };
        });

        return $query;
    }

    public function countResultForDeveloper($parameters)
    {
        $rc = ResidentialComplex::query()
            ->leftJoin('stage_of_constructions as sc',
                'residential_complexes.stage_of_construction_id', '=', 'sc.id')
            ->leftJoin('developers as dv', 'residential_complexes.developer_id', '=', 'dv.id')

            ->when(isset($parameters['developer_code']), function ($query) use ($parameters) {
                if ($parameters['developer_code'] == self::BEING_BUILT) {
                    $query->where('sc.code', '=', self::BEING_BUILT);
                } elseif ($parameters['developer_code'] == self::SALE) {
                    $query->where('start_of_sale', 1);
                } elseif ($parameters['developer_code'] == self::BUILT) {
                    $query->where('sc.code', '=', self::BUILD);
                } elseif ($parameters['developer_code'] == self::ALL) {
                }
            })

            ->when(isset($parameters['developer_id']), function ($query) use ($parameters) {
                $query->where('dv.id', '=', $parameters['developer_id']);
            })

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

            ->where('residential_complexes.enabled', 1)
            ->whereRaw('residential_complexes.deleted_at is null')
            ->select([
                DB::raw("count(residential_complexes.id) as rc_count"),
            ])
            ->get();

        return $rc;
    }

    public function secretBuyerAVGRating($parameters = null)
    {
        $query = ResidentialComplex::query()
            ->leftJoin('secret_buyer_ratings as cus_rat', 'residential_complexes.id', '=', 'cus_rat.residental_complex_id');

        $query
            ->select([
                DB::raw("AVG(case when (cus_rat.deleted_at is null AND cus_rat.enabled = 1) then cus_rat.position end) as rating_position"),
                DB::raw("AVG(case when (cus_rat.deleted_at is null AND cus_rat.enabled = 1) then cus_rat.transport_hub end) as rating_transport_hub"),
                DB::raw("AVG(case when (cus_rat.deleted_at is null AND cus_rat.enabled = 1) then cus_rat.planning end) as rating_planning"),
                DB::raw("AVG(case when (cus_rat.deleted_at is null AND cus_rat.enabled = 1) then cus_rat.price_quality end) as rating_price_quality"),
                DB::raw("AVG(case when (cus_rat.deleted_at is null AND cus_rat.enabled = 1) then cus_rat.infrastructure end) as rating_infrastructure"),
            ])

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

            ->where('residential_complexes.enabled', 1)
            ->whereRaw('residential_complexes.deleted_at is null')

            ->groupBy('residential_complexes.id')
            ->get();

        return $query;
    }

    public function customerAVGRating($parameters = null)
    {
        $query = ResidentialComplex::query()
        ->leftJoin('customer_ratings as cus_rat', 'residential_complexes.id', '=', 'cus_rat.residental_complex_id');

        $query
            ->select([
                DB::raw("AVG(case when (cus_rat.deleted_at is null AND cus_rat.enabled = 1) then cus_rat.position end) as rating_position"),
                DB::raw("AVG(case when (cus_rat.deleted_at is null AND cus_rat.enabled = 1) then cus_rat.transport_hub end) as rating_transport_hub"),
                DB::raw("AVG(case when (cus_rat.deleted_at is null AND cus_rat.enabled = 1) then cus_rat.planning end) as rating_planning"),
                DB::raw("AVG(case when (cus_rat.deleted_at is null AND cus_rat.enabled = 1) then cus_rat.price_quality end) as rating_price_quality"),
                DB::raw("AVG(case when (cus_rat.deleted_at is null AND cus_rat.enabled = 1) then cus_rat.infrastructure end) as rating_infrastructure"),
            ])

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

            ->where('residential_complexes.enabled', 1)
            ->whereRaw('residential_complexes.deleted_at is null')

            ->groupBy('residential_complexes.id')
            ->get();

        return $query;
    }

    public function priceGenerator($parameters = null)
    {
        $rc = ResidentialComplex::query()
            ->leftJoin('developers as dv', 'residential_complexes.developer_id', '=', 'dv.id')
//            ->leftJoin('customer_ratings as cus_rat', 'residential_complexes.id', '=', 'cus_rat.residental_complex_id')
            ->leftJoin('sections as section', 'residential_complexes.id', '=', 'section.residental_complex_id')
            ->leftJoin('plannings as pl', 'pl.section_id', '=', 'section.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['residential_complex_slug']), function ($query) use ($parameters) {
                $query->where('residential_complexes.code', '=', $parameters['residential_complex_slug']);
            });

            $rc = $this->residentialComplexSelect($rc)

//            ->where('residential_complexes.enabled', 1)
//            ->whereRaw('residential_complexes.deleted_at is null')

            ->groupBy('residential_complexes.id')
            ->get();

        return $rc;
    }
}