From c426e270c4da363fd5547cad6ff185d2e7c94e43 Mon Sep 17 00:00:00 2001
From: Daniel Karbach <daniel.karbach@localhorst.tv>
Date: Mon, 13 Mar 2023 12:39:13 +0100
Subject: [PATCH] SRA sync command

---
 .env.example                     |  2 +
 app/Console/Commands/SyncSRA.php | 99 ++++++++++++++++++++++++++++++++
 app/Console/Kernel.php           |  1 +
 config/google.php                |  5 ++
 4 files changed, 107 insertions(+)
 create mode 100644 app/Console/Commands/SyncSRA.php
 create mode 100644 config/google.php

diff --git a/.env.example b/.env.example
index ffdc53b..ccc63e6 100644
--- a/.env.example
+++ b/.env.example
@@ -73,3 +73,5 @@ AOS_URL=https://aos.localhorst.tv
 TWITCH_CLIENT_ID=
 TWITCH_CLIENT_SECRET=
 TWITCH_REDIRECT_URI=
+
+GOOGLE_API_KEY=
diff --git a/app/Console/Commands/SyncSRA.php b/app/Console/Commands/SyncSRA.php
new file mode 100644
index 0000000..45c2002
--- /dev/null
+++ b/app/Console/Commands/SyncSRA.php
@@ -0,0 +1,99 @@
+<?php
+
+namespace App\Console\Commands;
+
+use App\Models\Channel;
+use App\Models\Episode;
+use App\Models\Event;
+use App\Models\Organization;
+use Carbon\Carbon;
+use Illuminate\Console\Command;
+use Illuminate\Database\Eloquent\Builder;
+use Illuminate\Support\Facades\Http;
+use Illuminate\Support\Str;
+
+class SyncSRA extends Command {
+
+	/**
+	 * The name and signature of the console command.
+	 *
+	 * @var string
+	 */
+	protected $signature = 'sync:sra';
+
+	/**
+	 * The console command description.
+	 *
+	 * @var string
+	 */
+	protected $description = 'Synchronize Speed Runners Area schedule';
+
+	/**
+	 * Execute the console command.
+	 *
+	 * @return int
+	 */
+	public function handle() {
+		$this->channel = Channel::where('ext_id', '=', 'sra:SRA')->firstOrFail();
+		$this->org = Organization::where('name', '=', 'sra')->firstOrFail();
+
+		$events = Event::where('external_schedule', 'LIKE', 'sra:%')
+			->where(function (Builder $query) {
+				$query->whereNull('end');
+				$query->orWhere('end', '<', now());
+			})
+			->get();
+
+		$schedule = Http::get('https://sheets.googleapis.com/v4/spreadsheets/1J6iczoFRxG-NKCMqFugFqkvLSpPssegpIEMiNmFjwvI/values/Races', [
+			'alt' => 'json',
+			'key' => config('google.api_key'),
+		])->json();
+
+		foreach ($events as $event) {
+			try {
+				$this->line('syncing '.$event->name);
+				$this->syncEvent($event, $schedule['values']);
+			} catch (\Exception $e) {
+				$this->error('error syncing event '.$event->name.': '.$e->getMessage());
+			}
+		}
+
+		return Command::SUCCESS;
+	}
+
+	private function syncEvent(Event $event, $schedule) {
+		$tag = substr($event->external_schedule, 4);
+		for ($i = 3; $i < count($schedule); ++$i) {
+			if (!Str::startsWith($schedule[$i][2], $tag)) continue;
+			$this->syncSchedule($event, $schedule[$i]);
+		}
+
+	}
+
+	private function syncSchedule(Event $event, $row) {
+		$ext_id = 'sra:'.$row[0].'-'.$row[1];
+		$episode = Episode::firstWhere('ext_id', '=', $ext_id);
+		if (!$episode) {
+			$episode = new Episode();
+			$episode->ext_id = $ext_id;
+		}
+		$episode->event()->associate($event);
+		if (strlen($row[2]) > (strlen($event->external_schedule) - 1)) {
+			$episode->title = substr($row[2], strlen($event->external_schedule) - 1);
+		} else {
+			$episode->title = $row[2];
+		}
+		$start = Carbon::createFromFormat('m/d/Y H:i', $row[0].' '.$row[1], 'America/Chicago');
+		if (!$episode->start || $start->ne($episode->start)) {
+			$episode->start = $start;
+		}
+		$episode->estimate = 90 * 60;
+		$episode->confirmed = true;
+		$episode->save();
+		$episode->channels()->syncWithoutDetaching([$this->channel->id]);
+	}
+
+	private $channel;
+	private $org;
+
+}
diff --git a/app/Console/Kernel.php b/app/Console/Kernel.php
index 2fc689c..904bfd1 100644
--- a/app/Console/Kernel.php
+++ b/app/Console/Kernel.php
@@ -17,6 +17,7 @@ class Kernel extends ConsoleKernel
     {
 		$schedule->command('sync:avatars')->everyFiveMinutes();
 		$schedule->command('sync:speedgaming')->everyFiveMinutes();
+		$schedule->command('sync:sra')->everyFifteenMinutes();
     }
 
     /**
diff --git a/config/google.php b/config/google.php
new file mode 100644
index 0000000..e6915ef
--- /dev/null
+++ b/config/google.php
@@ -0,0 +1,5 @@
+<?php
+
+return [
+	'api_key' => env('GOOGLE_API_KEY', ''),
+];
-- 
2.39.2