2 files changed, 91 insertions, 4 deletions
diff --git a/database/migrations/2020_07_23_185334_add_airing_status_to_view_anime.php b/database/migrations/2020_07_23_185334_add_airing_status_to_view_anime.php
new file mode 100644
index 0000000..aa99e08
--- /dev/null
+++ b/database/migrations/2020_07_23_185334_add_airing_status_to_view_anime.php
@@ -0,0 +1,87 @@
+<?php
+
+use Illuminate\Database\Migrations\Migration;
+use Illuminate\Database\Schema\Blueprint;
+use Illuminate\Support\Facades\Schema;
+
+class AddAiringStatusToViewAnime extends Migration
+{
+    /**
+     * Run the migrations.
+     *
+     * @return void
+     */
+    public function up()
+    {
+	    DB::statement(
+		    "CREATE OR REPLACE VIEW view_anime_index AS
+		    SELECT DISTINCT
+			a.mal_id,
+			url,
+			image_url,
+			title_pref as title,
+			title_pref,
+			anime_type,
+			a.created_at,
+			a.updated_at,
+			episodes,
+			is_airing,
+			synopsis,
+			hashtag,
+			airing_status,
+			(SELECT score FROM stats as b WHERE b.mal_id = a.mal_id ORDER BY created_at  ASC LIMIT 1) as score_begin,
+			(SELECT score FROM stats as b WHERE b.mal_id = a.mal_id ORDER BY created_at DESC LIMIT 1) as score_today,
+			(SELECT watching FROM stats as b WHERE b.mal_id = a.mal_id ORDER BY created_at DESC LIMIT 1) as watching,
+			(SELECT members FROM stats as b WHERE b.mal_id = a.mal_id ORDER BY created_at DESC LIMIT 1) as members
+			FROM
+				anime as a
+			JOIN
+				stats as s ON a.mal_id = s.mal_id
+			WHERE
+				score IS NOT null
+			ORDER BY
+				abs(score_today-score_begin) DESC,
+				is_airing DESC
+		    ;"
+	    );
+    }
+
+    /**
+     * Reverse the migrations.
+     *
+     * @return void
+     */
+    public function down()
+    {
+	    DB::statement(
+		    "ALTER VIEW view_anime_index AS
+		    SELECT DISTINCT
+			a.mal_id,
+			url,
+			image_url,
+			title_pref as title,
+			title_pref,
+			anime_type,
+			a.created_at,
+			a.updated_at,
+			episodes,
+			is_airing,
+			synopsis,
+			hashtag,
+			(SELECT score FROM stats as b WHERE b.mal_id = a.mal_id ORDER BY created_at  ASC LIMIT 1) as score_begin,
+			(SELECT score FROM stats as b WHERE b.mal_id = a.mal_id ORDER BY created_at DESC LIMIT 1) as score_today,
+			(SELECT watching FROM stats as b WHERE b.mal_id = a.mal_id ORDER BY created_at DESC LIMIT 1) as watching,
+			(SELECT members FROM stats as b WHERE b.mal_id = a.mal_id ORDER BY created_at DESC LIMIT 1) as members
+			FROM
+				anime as a
+			JOIN
+				stats as s ON a.mal_id = s.mal_id
+			WHERE
+				score IS NOT null
+			ORDER BY
+				abs(score_today-score_begin) DESC,
+				is_airing DESC
+		    ;"
+	    );
+    }
+}
diff --git a/database/seeds/AiringStatusSeeder.php b/database/seeds/AiringStatusSeeder.php
index 7017842..e968886 100644
--- a/database/seeds/AiringStatusSeeder.php
+++ b/database/seeds/AiringStatusSeeder.php
@@ -12,19 +12,19 @@ class AiringStatusSeeder extends Seeder
     public function run()
     {
 	    DB::table('airing_status')->insert([
-		    'id' => 1,
+		    'id' => env('ANIME_IS_AIRING'),
 		    'status' => 'Currently Airing'
 	    ]);
 	    DB::table('airing_status')->insert([
-		    'id' => 2,
+		    'id' => env('ANIME_FINISHED_AIRING'),
 		    'status' => 'Finished Airing'
 	    ]);
 	    DB::table('airing_status')->insert([
-		    'id' => 3,
+		    'id' => env('ANIME_NOT_YET_AIRED'),
 		    'status' => 'Not Yet Aired'
 	    ]);
 	    DB::table('airing_status')->insert([
-		    'id' => 4,
+		    'id' => env('ANIME_AIRED_UNKNOWN'),
 		    'status' => 'Unknown'
 	    ]);
     }