From ec16505e5fc37ac233db64def11347f5ad21824d Mon Sep 17 00:00:00 2001
From: Daniel Karbach <daniel.karbach@localhorst.tv>
Date: Thu, 4 Apr 2024 11:59:13 +0200
Subject: [PATCH] fix database timezone

seriously wtf? why is that not core behaviour?
---
 config/database.php                           |   1 +
 ..._fix_database_timezone_like_wtf_really.php | 190 ++++++++++++++++++
 2 files changed, 191 insertions(+)
 create mode 100644 database/migrations/2024_04_04_092522_fix_database_timezone_like_wtf_really.php

diff --git a/config/database.php b/config/database.php
index 0faebae..a61db88 100644
--- a/config/database.php
+++ b/config/database.php
@@ -57,6 +57,7 @@ return [
             'prefix' => '',
             'prefix_indexes' => true,
             'strict' => true,
+			'timezone' => '+00:00',
             'engine' => null,
             'options' => extension_loaded('pdo_mysql') ? array_filter([
                 PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
diff --git a/database/migrations/2024_04_04_092522_fix_database_timezone_like_wtf_really.php b/database/migrations/2024_04_04_092522_fix_database_timezone_like_wtf_really.php
new file mode 100644
index 0000000..688624d
--- /dev/null
+++ b/database/migrations/2024_04_04_092522_fix_database_timezone_like_wtf_really.php
@@ -0,0 +1,190 @@
+<?php
+
+use Illuminate\Database\Migrations\Migration;
+use Illuminate\Database\Schema\Blueprint;
+use Illuminate\Support\Facades\DB;
+
+return new class extends Migration
+{
+
+	public static $timestamp_fields = [
+		'alttp_seeds' => [
+			'created_at',
+			'updated_at',
+		],
+		'aos_seeds' => [
+			'created_at',
+			'updated_at',
+		],
+		'applications' => [
+			'created_at',
+			'updated_at',
+		],
+		'channels' => [
+			'created_at',
+			'updated_at',
+			'guessing_start',
+			'guessing_end',
+		],
+		'channel_crews' => [
+			'created_at',
+			'updated_at',
+		],
+		'chat_logs' => [
+			'created_at',
+			'updated_at',
+			'evaluated_at',
+		],
+		'discord_bot_commands' => [
+			'executed_at',
+			'created_at',
+			'updated_at',
+		],
+		'discord_channels' => [
+			'created_at',
+			'updated_at',
+		],
+		'discord_guilds' => [
+			'created_at',
+			'updated_at',
+		],
+		'discord_roles' => [
+			'created_at',
+			'updated_at',
+		],
+		'episodes' => [
+			'start',
+			'created_at',
+			'updated_at',
+		],
+		'episode_crews' => [
+			'created_at',
+			'updated_at',
+		],
+		'episode_players' => [
+			'created_at',
+			'updated_at',
+		],
+		'events' => [
+			'start',
+			'end',
+			'created_at',
+			'updated_at',
+		],
+		'failed_jobs' => [
+			'failed_at',
+		],
+		'guessing_guesses' => [
+			'created_at',
+			'updated_at',
+		],
+		'guessing_winners' => [
+			'created_at',
+			'updated_at',
+		],
+		'organizations' => [
+			'created_at',
+			'updated_at',
+		],
+		'participants' => [
+			'created_at',
+			'updated_at',
+		],
+		'password_resets' => [
+			'created_at',
+		],
+		'personal_access_tokens' => [
+			'last_used_at',
+			'created_at',
+			'updated_at',
+		],
+		'protocols' => [
+			'created_at',
+			'updated_at',
+		],
+		'results' => [
+			'created_at',
+			'updated_at',
+		],
+		'rounds' => [
+			'created_at',
+			'updated_at',
+		],
+		'techniques' => [
+			'created_at',
+			'updated_at',
+		],
+		'technique_chapter' => [
+			'created_at',
+			'updated_at',
+		],
+		'technique_maps' => [
+			'created_at',
+			'updated_at',
+		],
+		'technique_relations' => [
+			'created_at',
+			'updated_at',
+		],
+		'technique_translations' => [
+			'created_at',
+			'updated_at',
+		],
+		'tournaments' => [
+			'created_at',
+			'updated_at',
+		],
+		'twitch_bot_commands' => [
+			'executed_at',
+			'created_at',
+			'updated_at',
+		],
+		'twitch_tokens' => [
+			'created_at',
+			'updated_at',
+		],
+		'users' => [
+			'created_at',
+			'updated_at',
+			'avatar_cached',
+		],
+	];
+
+	/**
+	 * Run the migrations.
+	 *
+	 * @return void
+	 */
+	public function up() {
+		foreach (static::$timestamp_fields as $table => $fields) {
+			$updates = [];
+			foreach ($fields as $field) {
+				$updates[$field] = DB::raw('CONVERT_TZ(`'.$field.'`, \'+00:00\', \'SYSTEM\')');
+			}
+			try {
+				DB::table($table)->update($updates);
+			} catch (\Exception $e) {
+				echo $e->getMessage(), PHP_EOL;
+			}
+		}
+	}
+
+	/**
+	 * Reverse the migrations.
+	 *
+	 * @return void
+	 */
+	public function down() {
+		foreach (static::$timestamp_fields as $table => $fields) {
+			$updates = [];
+			foreach ($fields as $field) {
+				$updates[$field] = DB::raw('CONVERT_TZ(`'.$field.'`, \'SYSTEM\', \'+00:00\')');
+			}
+			try {
+				DB::table($table)->update($updates);
+			} catch (\Exception $e) {
+				echo $e->getMessage(), PHP_EOL;
+			}
+		}
+	}
+};
-- 
2.39.2