<?php
  
  namespace App\Http\Controllers;

  use App\Classes\RusDate;

  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\Like_vacancy;
  use App\Models\Like_worker;

  use App\Models\News;

  use App\Models\Positions;

  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()->orderByDesc('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();

          //$Position = Category::query()->where('is_remove', '=', '0')->get();
          $Job_title = Job_title::query()->where('is_remove', '=', '0')->
          where('is_bd', '=', '0')->orderBy('name')->get();

          /*$BigFlot = Array();

          foreach ($Position as $position) {
              $BigFlot[] = DB::table('ad_jobs')->selectRaw('name, job_titles.id as id_title, count(`ad_jobs`.`id`) as cnt, ad_jobs.position_ship')->
              orderBy('job_titles.sort')->
              join('job_titles', 'job_titles.id', '=', 'ad_jobs.job_title_id')->
              where('position_ship', "$position->name")->
              groupby('job_title_id','position_ship')->
              get();
          }

          $BigFlot = Array();

          foreach ($Position as $position) {

              $BigFlot[] = Ad_jobs::query()->with(['job_title' => function($query) {
                  $query->OrderBy('sort');
              }])->whereHas('job_title', function ($query) use ($position) {
                      $query->where('position_id', $position->id);
                  })->
                  distinct('job_title_id')->
                  get();
          }*/

          /*$BigFlot = Array();

          foreach ($Position as $position) {

              $BigFlot[$position->id] = DB::table('ad_jobs')->
              selectRaw('name, count(`ad_jobs`.`id`) as cnt, job_title_id, job_titles.name, job_titles.position_id')->

              orderByDesc('job_titles.sort')->
              join('job_titles', 'job_titles.id', '=', 'ad_jobs.job_title_id')->
              where('job_titles.position_id', $position->id)->
              groupby('job_title_id')->
              get();

          }*/
          $Data = DB::table('job_titles')->
              selectRaw('job_titles.name as jn, count(`ad_jobs`.`job_title_id`) as cnt, job_titles.id as id_job, categories.name as catname, categories.id as id_cat')->

              where('categories.is_remove', '=', '0')->
              where('job_titles.is_remove', '=', '0')->

              where('job_titles.is_bd', '=' , '0')->

              leftJoin('ad_jobs', 'ad_jobs.job_title_id', '=', 'job_titles.id')->
              join('categories', 'categories.id', '=', 'job_titles.position_id')->

              groupBy('job_titles.id')->orderBy('categories.id')->orderByDesc('job_titles.position_id')->

              orderByDesc('job_titles.sort')->get()->toArray();
  
          $Main_Job = array();
          $name_cat = '';
          foreach ($Data as $it) {
              $it_arr = (array)$it;

              if ($name_cat != $it_arr['catname']) $name_cat = $it_arr['catname'];

              $Main_Job[$name_cat][] = $it_arr;

          }

          $employers = employers_main::query()->with('employer')->
              whereHas('employer', function ($query) {
                      $query->where('status_hidden', '=', '0');
              })->
              orderBy('id')->limit(8)->get();

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

          return view('index', compact('news', 'Job_title', 'categories', 'employers', 'vacancy', 'Main_Job'));

      }

      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 like_vacancy(Request $request) {
          $IP_address = RusDate::ip_addr_client();
  
              if ($request->has('code_record')) {
                  if ($request->has('delete')) {

                      $code = $request->get('code_record');

                      $atomic_era = Like_vacancy::select('id')->

                                      where('code_record', '=', $code)->toSql();

                      DB::table('like_vacancy')->where('code_record', $request->get('code_record'))->delete();
  
                  } else {
                      $params = $request->all();
                      $params['ip_address'] = $IP_address;
                      Like_vacancy::create($params);
                  }
              }
      }
  
      // Лайк соискателю.
      public function like_worker(Request $request) {
          $IP_address = RusDate::ip_addr_client();
  
          if ($request->has('code_record')) {
              if ($request->has('delete')) {
                  $atomic_era = Like_worker::select('id')->
                  where('code_record', '=', $request->
                  get('code_record'))->first();
  
                  DB::table('like_worker')->where('code_record', $request->get('code_record'))->delete();
  
                  return "Вот и результат удаления!";
  
              } else {
                  $params = $request->all();
                  $params['ip_address'] = $IP_address;
                  Like_worker::create($params);
              }
          }
      }

      public function vacancies(Request $request) {
          //должности

          $Job_title = Job_title::query()->where('is_remove', '=', '0')->
                          where('is_bd', '=', '0')->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();

          //$Position = Category::query()->where('is_remove', '=', '0')->get();

          /*$BigFlot = Array();

          foreach ($Position as $position) {
              $War_flot = DB::table('ad_jobs')->selectRaw('name, job_titles.id as id_title, count(`ad_jobs`.`id`) as cnt, ad_jobs.position_ship')->
              orderBy('job_titles.sort')->
              join('job_titles', 'job_titles.id', '=', 'ad_jobs.job_title_id')->
              where('position_ship', "$position->name");
              if (($request->has('job')) && ($request->get('job') > 0)) {
                  $War_flot = $War_flot->where('job_title_id', $request->get('job'));
              }
              $War_flot = $War_flot->groupby('job_title_id','position_ship')->get();
              $BigFlot[] = $War_flot;

          }*/

          /*

          $BigFlot = Array();
          foreach ($Position as $position) {
              $WarFlot = DB::table('ad_jobs')->
              selectRaw('name, count(`ad_jobs`.`id`) as cnt, job_title_id, job_titles.name')->
              orderByDesc('job_titles.sort')->
              join('job_titles', 'job_titles.id', '=', 'ad_jobs.job_title_id')->
              where('job_titles.position_id', $position->id);
              if (($request->has('job')) && ($request->get('job') > 0)) {
                  $WarFlot = $WarFlot->where('job_title_id', $request->get('job'));
              }
              $WarFlot = $WarFlot->groupby('job_title_id')->get();
              $BigFlot[] = $WarFlot;

          }

          */
  
          $Data = DB::table('job_titles')->

          selectRaw('job_titles.name as jn, count(`ad_jobs`.`job_title_id`) as cnt, job_titles.id as id_job, categories.name as catname, categories.id as id_cat')->
          where('categories.is_remove', '=', '0')->

          where('job_titles.is_bd', '=' , '0')->

          where('job_titles.is_remove', '=', '0');

          if (($request->has('job')) && ($request->get('job') > 0)) {
              $Data = $Data->where('job_title_id', $request->get('job'));
          }
          $Data = $Data->leftJoin('ad_jobs', 'ad_jobs.job_title_id', '=', 'job_titles.id')->
          join('categories', 'categories.id', '=', 'job_titles.position_id')->

          groupBy('job_titles.id')->orderBy('categories.id')->orderByDesc('job_titles.position_id')->

          orderByDesc('job_titles.sort')->get()->toArray();
  
          $Main_Job = array();
          $name_cat = '';
          foreach ($Data as $it) {
              $it_arr = (array)$it;
              if ($name_cat != $it_arr['catname'])
                  $name_cat = $it_arr['catname'];
              $Main_Job[$name_cat][] = $it_arr;
          }

          if ($request->ajax()) {

              return view('ajax.new_sky', compact('categories', 'Main_Job'));

          } else {

              return view('new_sky', compact('Job_title', 'categories', 'Main_Job'));

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

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

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

         if ($request->get('job') == 0)
             $job_search = '';
         else
             $job_search = $request->get('job');

         $Query = Ad_employer::with('jobs')->
                               with('cat')->
                               with('employer')->

                               whereHas('jobs_code', function ($query) use ($job_search) {
                                     if (!empty($job_search)) {
                                         $query->where('job_title_id', $job_search);
                                     }
                              })->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()->where('is_bd', '=', '0')->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()) {

              if (Auth::check())
                  $user_id = $request->user()->id;
              else
                  $user_id = 0;

              if ($user_id > 0)
                  return json_encode(Array("ERROR" => "Email или пароль невалидный!"));
              else
                  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'));
      }

  }