database/migrations/2024_04_04_092522_fix_database_timezone_like_wtf_really.php

   1 <?php
   2
   3 use Illuminate\Database\Migrations\Migration;
   4 use Illuminate\Database\Schema\Blueprint;
   5 use Illuminate\Support\Facades\DB;
   6
   7 return new class extends Migration
   8 {
   9
  10         public static $timestamp_fields = [
  11                 'alttp_seeds' => [
  12                         'created_at',
  13                         'updated_at',
  14                 ],
  15                 'aos_seeds' => [
  16                         'created_at',
  17                         'updated_at',
  18                 ],
  19                 'applications' => [
  20                         'created_at',
  21                         'updated_at',
  22                 ],
  23                 'channels' => [
  24                         'created_at',
  25                         'updated_at',
  26                         'guessing_start',
  27                         'guessing_end',
  28                 ],
  29                 'channel_crews' => [
  30                         'created_at',
  31                         'updated_at',
  32                 ],
  33                 'chat_logs' => [
  34                         'created_at',
  35                         'updated_at',
  36                         'evaluated_at',
  37                 ],
  38                 'discord_bot_commands' => [
  39                         'executed_at',
  40                         'created_at',
  41                         'updated_at',
  42                 ],
  43                 'discord_channels' => [
  44                         'created_at',
  45                         'updated_at',
  46                 ],
  47                 'discord_guilds' => [
  48                         'created_at',
  49                         'updated_at',
  50                 ],
  51                 'discord_roles' => [
  52                         'created_at',
  53                         'updated_at',
  54                 ],
  55                 'episodes' => [
  56                         'start',
  57                         'created_at',
  58                         'updated_at',
  59                 ],
  60                 'episode_crews' => [
  61                         'created_at',
  62                         'updated_at',
  63                 ],
  64                 'episode_players' => [
  65                         'created_at',
  66                         'updated_at',
  67                 ],
  68                 'events' => [
  69                         'start',
  70                         'end',
  71                         'created_at',
  72                         'updated_at',
  73                 ],
  74                 'failed_jobs' => [
  75                         'failed_at',
  76                 ],
  77                 'guessing_guesses' => [
  78                         'created_at',
  79                         'updated_at',
  80                 ],
  81                 'guessing_winners' => [
  82                         'created_at',
  83                         'updated_at',
  84                 ],
  85                 'organizations' => [
  86                         'created_at',
  87                         'updated_at',
  88                 ],
  89                 'participants' => [
  90                         'created_at',
  91                         'updated_at',
  92                 ],
  93                 'password_resets' => [
  94                         'created_at',
  95                 ],
  96                 'personal_access_tokens' => [
  97                         'last_used_at',
  98                         'created_at',
  99                         'updated_at',
 100                 ],
 101                 'protocols' => [
 102                         'created_at',
 103                         'updated_at',
 104                 ],
 105                 'results' => [
 106                         'created_at',
 107                         'updated_at',
 108                 ],
 109                 'rounds' => [
 110                         'created_at',
 111                         'updated_at',
 112                 ],
 113                 'techniques' => [
 114                         'created_at',
 115                         'updated_at',
 116                 ],
 117                 'technique_chapter' => [
 118                         'created_at',
 119                         'updated_at',
 120                 ],
 121                 'technique_maps' => [
 122                         'created_at',
 123                         'updated_at',
 124                 ],
 125                 'technique_relations' => [
 126                         'created_at',
 127                         'updated_at',
 128                 ],
 129                 'technique_translations' => [
 130                         'created_at',
 131                         'updated_at',
 132                 ],
 133                 'tournaments' => [
 134                         'created_at',
 135                         'updated_at',
 136                 ],
 137                 'twitch_bot_commands' => [
 138                         'executed_at',
 139                         'created_at',
 140                         'updated_at',
 141                 ],
 142                 'twitch_tokens' => [
 143                         'created_at',
 144                         'updated_at',
 145                 ],
 146                 'users' => [
 147                         'created_at',
 148                         'updated_at',
 149                         'avatar_cached',
 150                 ],
 151         ];
 152
 153         /**
 154          * Run the migrations.
 155          *
 156          * @return void
 157          */
 158         public function up() {
 159                 foreach (static::$timestamp_fields as $table => $fields) {
 160                         $updates = [];
 161                         foreach ($fields as $field) {
 162                                 $updates[$field] = DB::raw('CONVERT_TZ(`'.$field.'`, \'+00:00\', \'SYSTEM\')');
 163                         }
 164                         try {
 165                                 DB::table($table)->update($updates);
 166                         } catch (\Exception $e) {
 167                                 echo $e->getMessage(), PHP_EOL;
 168                         }
 169                 }
 170         }
 171
 172         /**
 173          * Reverse the migrations.
 174          *
 175          * @return void
 176          */
 177         public function down() {
 178                 foreach (static::$timestamp_fields as $table => $fields) {
 179                         $updates = [];
 180                         foreach ($fields as $field) {
 181                                 $updates[$field] = DB::raw('CONVERT_TZ(`'.$field.'`, \'SYSTEM\', \'+00:00\')');
 182                         }
 183                         try {
 184                                 DB::table($table)->update($updates);
 185                         } catch (\Exception $e) {
 186                                 echo $e->getMessage(), PHP_EOL;
 187                         }
 188                 }
 189         }
 190 };