<?php
  
  namespace App\Http\Controllers;

  use App\Classes\Tools;
  use App\Mail\MailRegistration;
  use App\Mail\MailRepair;

  use App\Models\Ad_employer;

  use App\Models\Ad_jobs;

  use App\Models\Category;

  use App\Models\Education;

  use App\Models\Employer;

  use App\Models\employers_main;

  use App\Models\Job_title;
  use App\Models\News;
  use App\Models\reclame;

  use App\Models\User;

  use Illuminate\Http\Request;

  use Illuminate\Support\Facades\Auth;

  use Illuminate\Support\Facades\DB;

  use Illuminate\Support\Facades\Hash;
  use Illuminate\Support\Facades\Mail;

  use Illuminate\Support\Facades\Validator;

  use App\Classes\StatusUser;

  
  class MainController extends Controller
  {

      // Главная страница публичной части

      public function index() {

          $news = News::query()->orderBy('id')->limit(6)->get();
  
          $categories = Category::query()->selectRaw('count(ad_employers.id) as cnt, categories.*')
              ->join('ad_employers', 'ad_employers.category_id', '=', 'categories.id')
              ->OrderByDesc('created_at')
              ->GroupBy('categories.id')
              ->get();

          $flot_paluba = DB::table('ad_jobs')->selectRaw('DISTINCT(job_titles.name), job_titles.id as id_title, count(`ad_jobs`.`id`) as cnt')->
          join('job_titles', 'job_titles.id', '=', 'ad_jobs.job_title_id')->
          where('position_ship', 'Палуба');
  
  
  
          $flot_paluba = $flot_paluba->groupby('job_title_id')->get();
          $flot_MO =  DB::table('ad_jobs')->selectRaw('DISTINCT(job_titles.name), job_titles.id as id_title, count(`ad_jobs`.`id`) as cnt')->
          join('job_titles', 'job_titles.id', '=', 'ad_jobs.job_title_id')->
          where('position_ship', '=', 'МО');
  
          $flot_MO = $flot_MO->groupby('ad_jobs.id')->get();
  
          $flot_radovie =  DB::table('ad_jobs')->selectRaw('DISTINCT(job_titles.name), job_titles.id as id_title, count(`ad_jobs`.`id`) as cnt')->
          join('job_titles', 'job_titles.id', '=', 'ad_jobs.job_title_id')->
          where('position_ship', '=', 'Рядовые');
  
          $flot_radovie = $flot_radovie->groupby('ad_jobs.id')->get();
  
          $flot_prochee =  DB::table('ad_jobs')->selectRaw('DISTINCT(job_titles.name), job_titles.id as id_title, count(`ad_jobs`.`id`) as cnt')->
          join('job_titles', 'job_titles.id', '=', 'ad_jobs.job_title_id')->
          where('position_ship', '=', 'Прочее');
          $flot_prochee = $flot_prochee->groupby('ad_jobs.id')->get();

          $employers = employers_main::query()->with('employer')->orderBy('id')->limit(8)->get();

          $vacancy = Ad_jobs::query()->with('job_title')->orderBy('position_ship')->get();

          return view('index', compact('news', 'categories', 'employers', 'vacancy',
              'flot_paluba', 'flot_MO', 'flot_radovie', 'flot_prochee'
          ));

      }

      public function search_vacancies(Request $request) {
          if ($request->has('search')) {
              $search = $request->get('search');
              $job_titles = Job_title::query()->where('name', 'LIKE', "%$search%")->first();
              if (isset($job_titles->id))
                  if ($job_titles->id > 0)
                      return redirect()->route('vacancies', ['job' => $job_titles->id]);
          }
      }

      public function vacancies(Request $request) {
          //должности
          $Job_title = Job_title::query()->orderBy('name')->get();
  
          $categories = Category::query()->selectRaw('count(ad_employers.id) as cnt, categories.*')
                      ->selectRaw('min(ad_employers.salary) as min_salary, max(ad_employers.salary) as max_salary')
                      ->join('ad_employers', 'ad_employers.category_id', '=', 'categories.id')
                      ->join('ad_jobs', 'ad_jobs.ad_employer_id', '=', 'ad_employers.id');
  
          //категории и вакансии

          if (($request->has('job')) && ($request->get('job') > 0)) {

                $categories = $categories->Where('job_title_id', '=', $request->get('job'));

          }

          $categories = $categories->OrderByDesc('created_at')->GroupBy('categories.id')->get();

          $flot_paluba = DB::table('ad_jobs')->selectRaw('DISTINCT(job_titles.name), job_titles.id as id_title, count(`ad_jobs`.`id`) as cnt')->
                                          join('job_titles', 'job_titles.id', '=', 'ad_jobs.job_title_id')->
                                          where('position_ship', 'Палуба');
          if (!empty($request->get('job'))) {
              $flot_paluba = $flot_paluba->where('job_title_id', '=', $request->get('job'));
          }
          $flot_paluba = $flot_paluba->groupby('job_title_id')->get();
  
          $flot_MO =  DB::table('ad_jobs')->selectRaw('DISTINCT(job_titles.name), job_titles.id as id_title, count(`ad_jobs`.`id`) as cnt')->
                                          join('job_titles', 'job_titles.id', '=', 'ad_jobs.job_title_id')->
                                          where('position_ship', '=', 'МО');
          if (!empty($request->get('job'))) {
              $flot_MO = $flot_MO->where('job_title_id', '=', $request->get('job'));
          }
          $flot_MO = $flot_MO->groupby('ad_jobs.id')->get();
  
          $flot_radovie =  DB::table('ad_jobs')->selectRaw('DISTINCT(job_titles.name), job_titles.id as id_title, count(`ad_jobs`.`id`) as cnt')->
                                              join('job_titles', 'job_titles.id', '=', 'ad_jobs.job_title_id')->
                                              where('position_ship', '=', 'Рядовые');
          if (!empty($request->get('job'))) {
              $flot_radovie = $flot_radovie->where('job_title_id', '=', $request->get('job'));
          }
          $flot_radovie = $flot_radovie->groupby('ad_jobs.id')->get();
  
          $flot_prochee =  DB::table('ad_jobs')->selectRaw('DISTINCT(job_titles.name), job_titles.id as id_title, count(`ad_jobs`.`id`) as cnt')->
                                              join('job_titles', 'job_titles.id', '=', 'ad_jobs.job_title_id')->
                                              where('position_ship', '=', 'Прочее');
          if (!empty($request->get('job'))) {
              $flot_prochee = $flot_prochee->where('job_title_id', '=', $request->get('job'));
          }
          $flot_prochee = $flot_prochee->groupby('ad_jobs.id')->get();

          if ($request->ajax()) {

              return view('ajax.new_sky', compact('categories', 'flot_paluba', 'flot_MO', 'flot_radovie', 'flot_prochee'));

          } else {

              return view('new_sky', compact('Job_title', 'categories',
                                                      'flot_paluba', 'flot_MO', 'flot_radovie', 'flot_prochee'));

          }
      }
  
      //Вакансии категория детальная

      public function list_vacancies(Category $categories, Request $request) {

         if (isset(Auth()->user()->id))
             $uid = Auth()->user()->id;
         else
             $uid = 0;

  
         $Query = Ad_employer::with('jobs')->
                               with('cat')->
                               with('employer')->
                               whereHas('jobs_code', function ($query) use ($request) {

                                     if (null !== ($request->get('job')) && ($request->get('job') !== 0)) {

                                         $query->where('job_title_id', $request->get('job'));
                                     }

                              })
                              ->select('ad_employers.*');

         if (isset($categories->id) && ($categories->id > 0)) {

             $Query = $Query->where('category_id', '=', $categories->id);
             $Name_categori = Category::query()->where('id', '=', $categories->id)->get();

         } else {
             $Name_categori = '';

         }

          if ($request->get('sort')) {
              $sort = $request->get('sort');

              switch ($sort) {
                  case 'name_up': $Query = $Query->orderBy('name')->orderBy('id'); break;
                  case 'name_down': $Query = $Query->orderByDesc('name')->orderby('id'); break;
                  case 'created_at_up': $Query = $Query->OrderBy('created_at')->orderBy('id'); break;
                  case 'created_at_down': $Query = $Query->orderByDesc('created_at')->orderBy('id'); break;
                  case 'default': $Query = $Query->orderBy('id')->orderby('updated_at'); break;
                  default: $Query = $Query->orderBy('id')->orderby('updated_at'); break;
              }
          }

         $Job_title = Job_title::query()->OrderBy('name')->get();

         $Query_count = $Query->count();

         $Query = $Query->OrderBy('updated_at')->paginate(3);

         $Reclama = reclame::query()->get();

  
  
         if ($request->ajax()) {

             if ($request->has('title')) {
                 return view('ajax.list_category', compact(
                     'Name_categori'
                 ));
             } else {
                 return view('ajax.list_vacancies', compact('Query',
                     'Query_count',
                     'Name_categori',
                     'Reclama',
                     'categories',
                     'Job_title',
                     'uid'));
             }

         } else {
             //Вернуть все
             return view('list_vacancies', compact('Query',
                                                      'Query_count',
                                                                'Reclama',
                                                                'Name_categori',
                                                                'categories',

                                                                'Job_title',
                                                                'uid'));

         }

      }

      // Образование
      public function education(Request $request) {
          $educations = Education::query();

          if (($request->has('search')) && (!empty($request->get('search')))) {
              $search = trim($request->get('search'));

              $educations = $educations->where('name', 'LIKE', "%$search%");
          }

  
          if ($request->get('sort')) {
              $sort = $request->get('sort');
              switch ($sort) {
                  case 'name_up': $educations = $educations->orderBy('name')->orderBy('id'); break;
                  case 'name_down': $educations = $educations->orderByDesc('name')->orderby('id'); break;
                  case 'created_at_up': $educations = $educations->OrderBy('created_at')->orderBy('id'); break;
                  case 'created_at_down': $educations = $educations->orderByDesc('created_at')->orderBy('id'); break;
                  case 'default': $educations = $educations->orderBy('id')->orderby('updated_at'); break;
                  default: $educations = $educations->orderBy('id')->orderby('updated_at'); break;
              }
          }

          $count_edu = $educations->count();
          $educations = $educations->paginate(6);

          if ($request->ajax()) {
              return view('ajax.education', compact('educations'));
          } else {
              return view('education', compact('educations', 'count_edu'));
          }

      }

      // Контакты
      public function contacts() {
          return view('contacts');

      }

  
      // Вход в личный кабинет
      public function input_login(Request $request)
      {

          $params = $request->all();

          $rules = [
              'email' => 'required|string|email',

              'password' => 'required|string|min:3|max:25',

          ];
  
          $messages = [
              'required' => 'Укажите обязательное поле «:attribute»',
              'email' => 'Введите корректный email',
              'min' => [
                  'string' => 'Поле «:attribute» должно быть не меньше :min символов',
                  'file' => 'Файл «:attribute» должен быть не меньше :min Кбайт'
              ],
              'max' => [
                  'string' => 'Поле «:attribute» должно быть не больше :max символов',
                  'file' => 'Файл «:attribute» должен быть не больше :max Кбайт'
              ],
          ];
  
          $validator = Validator::make($request->all(), $rules, $messages);

          if ($validator->fails()) {

              return redirect()->route('index')->with('Error', "Email или пароль невалидный");

          } else {
              $credentials = $request->only('email', 'password');
  
              if (Auth::attempt($credentials, $request->has('remember'))) {
  
                  if (is_null(Auth::user()->email_verified_at)) {
                      Auth::logout();

                      return json_encode(Array("ERROR" => "Адрес почты не подтвержден"));

                  }
  
                  if (Auth::user()->is_worker) {

                      return json_encode(Array("REDIRECT" => redirect()->route('worker.cabinet')->getTargetUrl()));

                  } else {

                      return json_encode(Array("REDIRECT" => redirect()->route('employer.cabinet')->getTargetUrl()));

                  }

                  return json_encode(Array("SUCCESS" => "Вы успешно вошли в личный кабинет"));

                      //->route('index')
                      //->with('success', 'Вы вошли в личный кабинет.');
              } else {

                  return json_encode(Array("ERROR" => "Неверный логин или пароль!"));

              }
          }
      }

  
      // Восстановление пароля
      public function repair_password(Request $request) {
          $rules = [
              'email' => 'required|string|email',
          ];
  
          $messages = [
              'required' => 'Укажите обязательное поле «:attribute»',
              'email' => 'Введите корректный email',
              'min' => [
                  'string' => 'Поле «:attribute» должно быть не меньше :min символов',
                  'file' => 'Файл «:attribute» должен быть не меньше :min Кбайт'
              ],
              'max' => [
                  'string' => 'Поле «:attribute» должно быть не больше :max символов',
                  'file' => 'Файл «:attribute» должен быть не больше :max Кбайт'
              ],
          ];
  
          $validator = Validator::make($request->all(), $rules, $messages);
  
          if ($validator->fails()) {
              return redirect()->back()->with('Error', "Email невалидный");
          } else {
              $new_password = Tools::generator_id(10);
              $hash_password = Hash::make($new_password);
              $user = User::query()->where('email', $request->get('email'))->first();
              $EditRec = User::find($user->id);
              $EditRec->password = $hash_password;
              $EditRec->save();
  
              foreach ([$request->get('email')] as $recipient) {
                  Mail::to($recipient)->send(new MailRepair($new_password));
              }

              return redirect()->route('index');

          }
  
      }

  
      // Вывод новостей
      public function news(Request $request) {
          $Query = News::query();
          if ($request->has('search')) {
              $search = $request->get('search');
              $Query = $Query->where('title', 'LIKE', "%$search%")->
                               orWhere('text', 'LIKE', "%$search%");
          }
  
          if ($request->ajax()) {
              if ($request->get('sort')) {
                  $sort = $request->get('sort');
                  switch ($sort) {
                      case 'name_up': $Query = $Query->orderBy('title')->orderBy('id'); break;
                      case 'name_down': $Query = $Query->orderByDesc('title')->orderby('id'); break;
                      case 'created_at_up': $Query = $Query->OrderBy('created_at')->orderBy('id'); break;
                      case 'created_at_down': $Query = $Query->orderByDesc('created_at')->orderBy('id'); break;
                      case 'default': $Query = $Query->orderBy('id')->orderby('updated_at'); break;
                      default: $Query = $Query->orderBy('id')->orderby('updated_at'); break;
                  }
              }
          }
          $Query_count = $Query->count();
          $Query = $Query->paginate(6);
  
          if ($request->ajax()) {
              return view('ajax.news-list', compact('Query', 'Query_count'));
          } else {
              return view('news-list', compact('Query', 'Query_count'));
          }
      }
  
      //Детальная новость
      public function detail_new(News $new) {
          // Выборка
          $Query = News::query()->where('id', $new->id)->get();
          $title = $Query[0]->title;
          $All_Query = News::query()->paginate(8);
          return view('detail_new', compact('Query', 'All_Query', 'title'));
      }

  }