From 071885a30f24b980699b337d9cdb65952f8c6c42 Mon Sep 17 00:00:00 2001
From: Daniel Karbach <daniel.karbach@localhorst.tv>
Date: Sat, 18 Feb 2023 18:48:57 +0100
Subject: [PATCH] sync speedgaming schedule for select events

---
 app/Console/Commands/SyncSpeedGaming.php      | 154 ++++++++++++++++++
 app/Console/Kernel.php                        |   2 +-
 app/Http/Controllers/EpisodeController.php    |  23 +++
 app/Http/Controllers/EventController.php      |  21 +++
 app/Models/Episode.php                        |  25 +++
 app/Models/EpisodePlayer.php                  |  25 +++
 app/Models/Event.php                          |  21 +++
 app/Policies/EpisodePolicy.php                |  94 +++++++++++
 app/Policies/EventPolicy.php                  |  94 +++++++++++
 .../2023_02_17_091506_create_events_table.php |  37 +++++
 ...023_02_17_120750_create_episodes_table.php |  38 +++++
 ...17_153122_create_episode_players_table.php |  36 ++++
 routes/api.php                                |   3 +
 13 files changed, 572 insertions(+), 1 deletion(-)
 create mode 100644 app/Console/Commands/SyncSpeedGaming.php
 create mode 100644 app/Http/Controllers/EpisodeController.php
 create mode 100644 app/Http/Controllers/EventController.php
 create mode 100644 app/Models/Episode.php
 create mode 100644 app/Models/EpisodePlayer.php
 create mode 100644 app/Models/Event.php
 create mode 100644 app/Policies/EpisodePolicy.php
 create mode 100644 app/Policies/EventPolicy.php
 create mode 100644 database/migrations/2023_02_17_091506_create_events_table.php
 create mode 100644 database/migrations/2023_02_17_120750_create_episodes_table.php
 create mode 100644 database/migrations/2023_02_17_153122_create_episode_players_table.php

diff --git a/app/Console/Commands/SyncSpeedGaming.php b/app/Console/Commands/SyncSpeedGaming.php
new file mode 100644
index 0000000..177ee19
--- /dev/null
+++ b/app/Console/Commands/SyncSpeedGaming.php
@@ -0,0 +1,154 @@
+<?php
+
+namespace App\Console\Commands;
+
+use App\Models\Episode;
+use App\Models\EpisodePlayer;
+use App\Models\Event;
+use App\Models\User;
+use Carbon\Carbon;
+use Illuminate\Console\Command;
+use Illuminate\Database\Eloquent\Builder;
+use Illuminate\Support\Facades\Http;
+
+class SyncSpeedGaming extends Command {
+
+	/**
+	 * The name and signature of the console command.
+	 *
+	 * @var string
+	 */
+	protected $signature = 'sync:speedgaming';
+
+	/**
+	 * The console command description.
+	 *
+	 * @var string
+	 */
+	protected $description = 'Synchronize SpeedGaming schedule';
+
+	/**
+	 * Execute the console command.
+	 *
+	 * @return int
+	 */
+	public function handle() {
+		$events = Event::where('external_schedule', 'LIKE', 'sg:%')
+			->where(function (Builder $query) {
+				$query->whereNull('end');
+				$query->orWhere('end', '<', now());
+			})
+			->get();
+		foreach ($events as $event) {
+			try {
+				$this->line('syncing '.$event->name);
+				$this->syncEvent($event);
+			} catch (\Exception $e) {
+				$this->error('error syncing event '.$event->name.': '.$e->getMessage());
+			}
+		}
+		return 0;
+	}
+
+	private function syncEvent(Event $event) {
+		$sgHandle = substr($event->external_schedule, 3);
+		$from = now()->sub(1, 'day');
+		$to = now()->add(6, 'day');
+		$sgSchedule = HTTP::get('https://speedgaming.org/api/schedule/', [
+			'event' => $sgHandle,
+			'from' => $from->toIso8601String(),
+			'to' => $to->toIso8601String(),
+		])->json();
+		$this->purgeSchedule($event, $sgSchedule);
+		foreach ($sgSchedule as $sgEntry) {
+			try {
+				$this->syncSchedule($event, $sgEntry);
+			} catch (Exception $e) {
+				$this->error('error syncing episode '.$sgEntry['id'].': '.$e->getMessage());
+			}
+		}
+	}
+
+	private function purgeSchedule(Event $event, $sgSchedule) {
+		$ext_ids = [];
+		foreach ($sgSchedule as $sgEntry) {
+			$ext_ids[] = 'sg:'.$sgEntry['id'];
+		}
+		$event->episodes()->whereNotIn('ext_id', $ext_ids)->delete();
+	}
+
+	private function syncSchedule(Event $event, $sgEntry) {
+		$ext_id = 'sg:'.$sgEntry['id'];
+		$episode = Episode::firstWhere('ext_id', '=', $ext_id);
+		if (!$episode) {
+			$episode = new Episode();
+			$episode->ext_id = $ext_id;
+		}
+		$episode->event()->associate($event);
+		$episode->title = $sgEntry['match1']['title'];
+		$start = Carbon::parse($sgEntry['when']);
+		if ($start->ne($episode->start)) {
+			$episode->start = $start;
+		}
+		$episode->estimate = $sgEntry['length'] * 60;
+		$episode->confirmed = $sgEntry['approved'];
+		$episode->comment = $sgEntry['match1']['note'];
+		$episode->save();
+		$this->purgePlayers($episode, $sgEntry);
+		foreach ($sgEntry['match1']['players'] as $sgPlayer) {
+			try {
+				$this->syncPlayer($episode, $sgPlayer);
+			} catch (Exception $e) {
+				$this->error('error syncing player '.$sgPlayer['id'].': '.$e->getMessage());
+			}
+		}
+	}
+
+	private function purgePlayers(Episode $episode, $sgEntry) {
+		$ext_ids = [];
+		foreach ($sgEntry['match1']['players'] as $sgPlayer) {
+			$ext_ids[] = 'sg:'.$sgPlayer['id'];
+		}
+		$episode->players()->whereNotIn('ext_id', $ext_ids)->delete();
+	}
+
+	private function syncPlayer(Episode $episode, $sgPlayer) {
+		$ext_id = 'sg:'.$sgPlayer['id'];
+		$player = $episode->players()->firstWhere('ext_id', '=', $ext_id);
+		if (!$player) {
+			$player = new EpisodePlayer();
+			$player->ext_id = $ext_id;
+			$player->episode()->associate($episode);
+		}
+		$user = $this->getUserBySGPlayer($sgPlayer);
+		if ($user) {
+			$player->user()->associate($user);
+		} else {
+			$player->user()->disassociate();
+		}
+		if (!empty($sgPlayer['displayName'])) {
+			$player->name_override = $sgPlayer['displayName'];
+		}
+		if (!empty($sgPlayer['streamingFrom'])) {
+			$player->stream_override = $sgPlayer['streamingFrom'];
+		}
+		$player->save();
+	}
+
+	private function getUserBySGPlayer($player) {
+		if (!empty($player['discordId'])) {
+			$user = User::find($player['discordId']);
+			if ($user) return $user;
+		}
+		if (!empty($player['discordTag'])) {
+			$tag = explode('#', $player['discordTag']);
+			$user = User::firstWhere([
+				['username', 'LIKE', $tag[0]],
+				['username', 'LIKE', $tag[1]],
+			]);
+			if ($user) return $user;
+		}
+		return null;
+	}
+
+}
diff --git a/app/Console/Kernel.php b/app/Console/Kernel.php
index d8bc1d2..a0aa9ec 100644
--- a/app/Console/Kernel.php
+++ b/app/Console/Kernel.php
@@ -15,7 +15,7 @@ class Kernel extends ConsoleKernel
      */
     protected function schedule(Schedule $schedule)
     {
-        // $schedule->command('inspire')->hourly();
+		$schedule->command('sync:speedgaming')->everyFifteenMinutes();
     }
 
     /**
diff --git a/app/Http/Controllers/EpisodeController.php b/app/Http/Controllers/EpisodeController.php
new file mode 100644
index 0000000..4e4eeda
--- /dev/null
+++ b/app/Http/Controllers/EpisodeController.php
@@ -0,0 +1,23 @@
+<?php
+
+namespace App\Http\Controllers;
+
+use App\Models\Episode;
+use Illuminate\Http\Request;
+
+class EpisodeController extends Controller
+{
+
+	public function search(Request $request) {
+		$episodes = Episode::with('event')
+			->where('confirmed', '=', true)
+			->where('event.visible', '=', true);
+		return $episodes->get()->toJson();
+	}
+
+	public function single(Request $request, Episode $episode) {
+		$this->authorize('view', $episode);
+		return $episode->toJson();
+	}
+
+}
diff --git a/app/Http/Controllers/EventController.php b/app/Http/Controllers/EventController.php
new file mode 100644
index 0000000..eb74239
--- /dev/null
+++ b/app/Http/Controllers/EventController.php
@@ -0,0 +1,21 @@
+<?php
+
+namespace App\Http\Controllers;
+
+use App\Models\Event;
+use Illuminate\Http\Request;
+
+class EventController extends Controller
+{
+
+	public function search(Request $request) {
+		$events = Event::where('visible', '=', true);
+		return $events->get()->toJson();
+	}
+
+	public function single(Request $request, Event $event) {
+		$this->authorize('view', $event);
+		return $event->toJson();
+	}
+
+}
diff --git a/app/Models/Episode.php b/app/Models/Episode.php
new file mode 100644
index 0000000..b4a4ec8
--- /dev/null
+++ b/app/Models/Episode.php
@@ -0,0 +1,25 @@
+<?php
+
+namespace App\Models;
+
+use Illuminate\Database\Eloquent\Factories\HasFactory;
+use Illuminate\Database\Eloquent\Model;
+
+class Episode extends Model
+{
+
+	use HasFactory;
+
+	public function event() {
+		return $this->belongsTo(Event::class);
+	}
+
+	public function players() {
+		return $this->hasMany(EpisodePlayer::class);
+	}
+
+	protected $casts = [
+		'confirmed' => 'boolean',
+	];
+
+}
diff --git a/app/Models/EpisodePlayer.php b/app/Models/EpisodePlayer.php
new file mode 100644
index 0000000..46a064b
--- /dev/null
+++ b/app/Models/EpisodePlayer.php
@@ -0,0 +1,25 @@
+<?php
+
+namespace App\Models;
+
+use Illuminate\Database\Eloquent\Factories\HasFactory;
+use Illuminate\Database\Eloquent\Model;
+
+class EpisodePlayer extends Model
+{
+	use HasFactory;
+
+	public function episode() {
+		return $this->belongsTo(Episode::class);
+	}
+
+	public function user() {
+		return $this->belongsTo(User::class);
+	}
+
+	protected $hidden = [
+		'created_at',
+		'updated_at',
+	];
+
+}
diff --git a/app/Models/Event.php b/app/Models/Event.php
new file mode 100644
index 0000000..44bc69b
--- /dev/null
+++ b/app/Models/Event.php
@@ -0,0 +1,21 @@
+<?php
+
+namespace App\Models;
+
+use Illuminate\Database\Eloquent\Factories\HasFactory;
+use Illuminate\Database\Eloquent\Model;
+
+class Event extends Model
+{
+
+	use HasFactory;
+
+	public function episodes() {
+		return $this->hasMany(Episode::class);
+	}
+
+	protected $casts = [
+		'visible' => 'boolean',
+	];
+
+}
diff --git a/app/Policies/EpisodePolicy.php b/app/Policies/EpisodePolicy.php
new file mode 100644
index 0000000..18988cf
--- /dev/null
+++ b/app/Policies/EpisodePolicy.php
@@ -0,0 +1,94 @@
+<?php
+
+namespace App\Policies;
+
+use App\Models\Episode;
+use App\Models\User;
+use Illuminate\Auth\Access\HandlesAuthorization;
+
+class EpisodePolicy
+{
+	use HandlesAuthorization;
+
+	/**
+	 * Determine whether the user can view any models.
+	 *
+	 * @param  \App\Models\User  $user
+	 * @return \Illuminate\Auth\Access\Response|bool
+	 */
+	public function viewAny(?User $user)
+	{
+		return true;
+	}
+
+	/**
+	 * Determine whether the user can view the model.
+	 *
+	 * @param  \App\Models\User  $user
+	 * @param  \App\Models\Episode  $episode
+	 * @return \Illuminate\Auth\Access\Response|bool
+	 */
+	public function view(User $user, Episode $episode)
+	{
+		return $episode->event->visible;
+	}
+
+	/**
+	 * Determine whether the user can create models.
+	 *
+	 * @param  \App\Models\User  $user
+	 * @return \Illuminate\Auth\Access\Response|bool
+	 */
+	public function create(User $user)
+	{
+		return $user->isAdmin();
+	}
+
+	/**
+	 * Determine whether the user can update the model.
+	 *
+	 * @param  \App\Models\User  $user
+	 * @param  \App\Models\Episode  $episode
+	 * @return \Illuminate\Auth\Access\Response|bool
+	 */
+	public function update(User $user, Episode $episode)
+	{
+		return $user->isAdmin();
+	}
+
+	/**
+	 * Determine whether the user can delete the model.
+	 *
+	 * @param  \App\Models\User  $user
+	 * @param  \App\Models\Episode  $episode
+	 * @return \Illuminate\Auth\Access\Response|bool
+	 */
+	public function delete(User $user, Episode $episode)
+	{
+		return false;
+	}
+
+	/**
+	 * Determine whether the user can restore the model.
+	 *
+	 * @param  \App\Models\User  $user
+	 * @param  \App\Models\Episode  $episode
+	 * @return \Illuminate\Auth\Access\Response|bool
+	 */
+	public function restore(User $user, Episode $episode)
+	{
+		return false;
+	}
+
+	/**
+	 * Determine whether the user can permanently delete the model.
+	 *
+	 * @param  \App\Models\User  $user
+	 * @param  \App\Models\Episode  $episode
+	 * @return \Illuminate\Auth\Access\Response|bool
+	 */
+	public function forceDelete(User $user, Episode $episode)
+	{
+		return false;
+	}
+}
diff --git a/app/Policies/EventPolicy.php b/app/Policies/EventPolicy.php
new file mode 100644
index 0000000..7acb3b2
--- /dev/null
+++ b/app/Policies/EventPolicy.php
@@ -0,0 +1,94 @@
+<?php
+
+namespace App\Policies;
+
+use App\Models\Event;
+use App\Models\User;
+use Illuminate\Auth\Access\HandlesAuthorization;
+
+class EventPolicy
+{
+	use HandlesAuthorization;
+
+	/**
+	 * Determine whether the user can view any models.
+	 *
+	 * @param  \App\Models\User  $user
+	 * @return \Illuminate\Auth\Access\Response|bool
+	 */
+	public function viewAny(?User $user)
+	{
+		return true;
+	}
+
+	/**
+	 * Determine whether the user can view the model.
+	 *
+	 * @param  \App\Models\User  $user
+	 * @param  \App\Models\Event  $event
+	 * @return \Illuminate\Auth\Access\Response|bool
+	 */
+	public function view(?User $user, Event $event)
+	{
+		return $event->visible;
+	}
+
+	/**
+	 * Determine whether the user can create models.
+	 *
+	 * @param  \App\Models\User  $user
+	 * @return \Illuminate\Auth\Access\Response|bool
+	 */
+	public function create(User $user)
+	{
+		return $user->isAdmin();
+	}
+
+	/**
+	 * Determine whether the user can update the model.
+	 *
+	 * @param  \App\Models\User  $user
+	 * @param  \App\Models\Event  $event
+	 * @return \Illuminate\Auth\Access\Response|bool
+	 */
+	public function update(User $user, Event $event)
+	{
+		return $user->isAdmin();
+	}
+
+	/**
+	 * Determine whether the user can delete the model.
+	 *
+	 * @param  \App\Models\User  $user
+	 * @param  \App\Models\Event  $event
+	 * @return \Illuminate\Auth\Access\Response|bool
+	 */
+	public function delete(User $user, Event $event)
+	{
+		return false;
+	}
+
+	/**
+	 * Determine whether the user can restore the model.
+	 *
+	 * @param  \App\Models\User  $user
+	 * @param  \App\Models\Event  $event
+	 * @return \Illuminate\Auth\Access\Response|bool
+	 */
+	public function restore(User $user, Event $event)
+	{
+		return false;
+	}
+
+	/**
+	 * Determine whether the user can permanently delete the model.
+	 *
+	 * @param  \App\Models\User  $user
+	 * @param  \App\Models\Event  $event
+	 * @return \Illuminate\Auth\Access\Response|bool
+	 */
+	public function forceDelete(User $user, Event $event)
+	{
+		return false;
+	}
+}
diff --git a/database/migrations/2023_02_17_091506_create_events_table.php b/database/migrations/2023_02_17_091506_create_events_table.php
new file mode 100644
index 0000000..2f8a41a
--- /dev/null
+++ b/database/migrations/2023_02_17_091506_create_events_table.php
@@ -0,0 +1,37 @@
+<?php
+
+use Illuminate\Database\Migrations\Migration;
+use Illuminate\Database\Schema\Blueprint;
+use Illuminate\Support\Facades\Schema;
+
+return new class extends Migration
+{
+	/**
+	 * Run the migrations.
+	 *
+	 * @return void
+	 */
+	public function up()
+	{
+		Schema::create('events', function (Blueprint $table) {
+			$table->id();
+			$table->string('name')->unique();
+			$table->text('title');
+			$table->timestamp('start')->nullable()->default(null);
+			$table->timestamp('end')->nullable()->default(null);
+			$table->boolean('visible')->default(false);
+			$table->string('external_schedule')->nullable()->default(null);
+			$table->timestamps();
+		});
+	}
+
+	/**
+	 * Reverse the migrations.
+	 *
+	 * @return void
+	 */
+	public function down()
+	{
+		Schema::dropIfExists('events');
+	}
+};
diff --git a/database/migrations/2023_02_17_120750_create_episodes_table.php b/database/migrations/2023_02_17_120750_create_episodes_table.php
new file mode 100644
index 0000000..27d2b6c
--- /dev/null
+++ b/database/migrations/2023_02_17_120750_create_episodes_table.php
@@ -0,0 +1,38 @@
+<?php
+
+use Illuminate\Database\Migrations\Migration;
+use Illuminate\Database\Schema\Blueprint;
+use Illuminate\Support\Facades\Schema;
+
+return new class extends Migration
+{
+	/**
+	 * Run the migrations.
+	 *
+	 * @return void
+	 */
+	public function up()
+	{
+		Schema::create('episodes', function (Blueprint $table) {
+			$table->id();
+			$table->foreignId('event_id')->constrained();
+			$table->text('title');
+			$table->timestamp('start');
+			$table->integer('estimate')->nullable()->default(null);
+			$table->boolean('confirmed')->default(false);
+			$table->text('comment')->default('');
+			$table->string('ext_id')->nullable()->default(null);
+			$table->timestamps();
+		});
+	}
+
+	/**
+	 * Reverse the migrations.
+	 *
+	 * @return void
+	 */
+	public function down()
+	{
+		Schema::dropIfExists('episodes');
+	}
+};
diff --git a/database/migrations/2023_02_17_153122_create_episode_players_table.php b/database/migrations/2023_02_17_153122_create_episode_players_table.php
new file mode 100644
index 0000000..0739904
--- /dev/null
+++ b/database/migrations/2023_02_17_153122_create_episode_players_table.php
@@ -0,0 +1,36 @@
+<?php
+
+use Illuminate\Database\Migrations\Migration;
+use Illuminate\Database\Schema\Blueprint;
+use Illuminate\Support\Facades\Schema;
+
+return new class extends Migration
+{
+    /**
+     * Run the migrations.
+     *
+     * @return void
+     */
+    public function up()
+    {
+        Schema::create('episode_players', function (Blueprint $table) {
+            $table->id();
+			$table->foreignId('episode_id')->constrained();
+			$table->foreignId('user_id')->nullable()->default(null)->constrained();
+			$table->string('name_override')->default('');
+			$table->string('stream_override')->default('');
+			$table->string('ext_id')->nullable()->default(null);
+            $table->timestamps();
+        });
+    }
+
+    /**
+     * Reverse the migrations.
+     *
+     * @return void
+     */
+    public function down()
+    {
+        Schema::dropIfExists('episode_players');
+    }
+};
diff --git a/routes/api.php b/routes/api.php
index 4c1d8ab..d1bc036 100644
--- a/routes/api.php
+++ b/routes/api.php
@@ -36,6 +36,9 @@ Route::get('discord-guilds', 'App\Http\Controllers\DiscordGuildController@search
 Route::get('discord-guilds/{guild_id}', 'App\Http\Controllers\DiscordGuildController@single');
 Route::get('discord-guilds/{guild_id}/channels', 'App\Http\Controllers\DiscordChannelController@search');
 
+Route::get('event', 'App\Http\Controllers\EventController@search');
+Route::get('event/{event:name}', 'App\Http\Controllers\EventController@single');
+
 Route::get('markers/{map}', 'App\Http\Controllers\TechniqueController@forMap');
 
 Route::get('protocol/{tournament}', 'App\Http\Controllers\ProtocolController@forTournament');
-- 
2.39.2