<?php
  
  namespace App\Console\Commands;
  
  use Illuminate\Console\Command;
  use Illuminate\Support\Facades\DB;
  
  class RemoveOldUsers extends Command
  {
      /**
       * The name and signature of the console command.
       *
       * @var string
       */
      protected $signature = 'users:remove-old';
  
      /**
       * The console command description.
       *
       * @var string
       */
      protected $description = 'Remove old users and related data';
  
      /**
       * Execute the console command.
       */
      public function handle()
      {
          $date = '2024-11-30 00:00:00';
  
          $users = DB::table('users')
              ->where('created_at', '<', $date)
              ->pluck('id');
  
          if ($users->isEmpty()) {
              $this->info('No users to delete.');
              return 0;
          }
  
          $this->info('Found ' . $users->count() . ' users to delete.');
  
          $userRelatedTables = [
              'answers',
              'chats',
              'employers',
              'group_users',
              'group_works',
  //            'like_vacancy',
  //            'like_worker',
              'media',
              'messages',
              'messages_requests',
              'static_workers',
              'workers',
          ];
  
          $workerEmployerRelatedTables = [
              'ad_employers' => 'employer_id',
              'answers' => 'employer_id',
              'employers_mains' => 'employer_id',
              'employer_autolift_options' => 'employer_id',
              'flots' => 'employer_id',
              'place_works' => 'worker_id',
              'prev_company' => 'worker_id',
              'response_works' => 'worker_id',
              'sertifications' => 'worker_id',
              'title_workers' => 'worker_id',
              'worker_autolift_options' => 'worker_id',
          ];
  
          try {
              DB::beginTransaction();
  
              foreach ($userRelatedTables as $table) {
                  DB::table($table)->whereIn('user_id', $users)->delete();
                  $this->info("Removed related records from table: $table");
              }
  
              $workerIds = DB::table('workers')->whereIn('user_id', $users)->pluck('id');
              $employerIds = DB::table('employers')->whereIn('user_id', $users)->pluck('id');
  
              foreach ($workerEmployerRelatedTables as $table => $column) {
                  DB::table($table)->whereIn($column, str_contains($column, 'worker') ? $workerIds : $employerIds)->delete();
                  $this->info("Removed related records for table: $table");
              }
              foreach (['like_vacancy', 'like_worker'] as $table) {
                  DB::table($table)->whereIn('user_id', $users->map(fn($id) => (string)$id))->delete();
                  DB::table($table)->whereIn('user_id', $users->map(fn($id) => (string)$id))->delete();
                  $this->info("Removed related records from table: $table (VARCHAR user_id)");
              }
              DB::table('workers')->whereIn('id', $workerIds)->delete();
              DB::table('employers')->whereIn('id', $employerIds)->delete();
              DB::table('users')->whereIn('id', $users)->delete();
  
              DB::commit();
              $this->info('All related data and users have been removed successfully.');
          } catch (\Exception $e) {
              DB::rollBack();
              $this->error('An error occurred: ' . $e->getMessage());
          }
  
          return 0;
      }
  
  }