From 95adeca0ae1cd6c5475242d036ee75584aedd2a1 Mon Sep 17 00:00:00 2001
From: Michael Weimann <mail@michael-weimann.eu>
Date: Thu, 12 Dec 2019 21:30:28 +0100
Subject: Add message model

---
 .../2019_11_25_000000_create_messages_table.php    | 160 +++++++++++++++++++++
 1 file changed, 160 insertions(+)
 create mode 100644 db/migrations/2019_11_25_000000_create_messages_table.php

(limited to 'db')

diff --git a/db/migrations/2019_11_25_000000_create_messages_table.php b/db/migrations/2019_11_25_000000_create_messages_table.php
new file mode 100644
index 00000000..b34a6c19
--- /dev/null
+++ b/db/migrations/2019_11_25_000000_create_messages_table.php
@@ -0,0 +1,160 @@
+<?php
+
+declare(strict_types=1);
+
+namespace Engelsystem\Migrations;
+
+use Carbon\Carbon;
+use Engelsystem\Database\Migration\Migration;
+use Illuminate\Database\Eloquent\Collection;
+use Illuminate\Database\Schema\Blueprint;
+use stdClass;
+
+/**
+ * This migration creates the "messages" table and copies the existing "Messages" table records to the new one.
+ */
+class CreateMessagesTable extends Migration
+{
+    use ChangesReferences;
+    use Reference;
+
+    /**
+     * Creates the "messages" table, copies the data and drops the "Message" table.
+     */
+    public function up(): void
+    {
+        $hasPreviousMessagesTable = $this->schema->hasTable('Messages');
+
+        if ($hasPreviousMessagesTable) {
+            // Rename because some SQL DBMS handle identifiers case insensitive
+            $this->schema->rename('Messages', 'PreviousMessages');
+        }
+
+        $this->createNewMessagesTable();
+
+        if ($hasPreviousMessagesTable) {
+            $this->copyPreviousToNewMessagesTable();
+            $this->changeReferences(
+                'PreviousMessages',
+                'ID',
+                'messages',
+                'id',
+                'unsignedInteger'
+            );
+            $this->schema->drop('PreviousMessages');
+        }
+    }
+
+    /**
+     * Recreates the previous "Messages" table, copies back the data and drops the new "messages" table.
+     */
+    public function down(): void
+    {
+        // Rename as some SQL DBMS handle identifiers case insensitive
+        $this->schema->rename('messages', 'new_messages');
+
+        $this->createPreviousMessagesTable();
+        $this->copyNewToPreviousMessagesTable();
+        $this->changeReferences(
+            'new_messages',
+            'id',
+            'Messages',
+            'ID',
+            'unsignedInteger'
+        );
+
+        $this->schema->drop('new_messages');
+    }
+
+    /**
+     * @return void
+     */
+    private function createNewMessagesTable(): void
+    {
+        $this->schema->create(
+            'messages',
+            function (Blueprint $table) {
+                $table->increments('id');
+                $this->references($table, 'users', 'user_id');
+                $this->references($table, 'users', 'receiver_id');
+                $table->boolean('read')->default(0);
+                $table->text('text');
+                $table->timestamps();
+            }
+        );
+    }
+
+    /**
+     * @return void
+     */
+    private function copyPreviousToNewMessagesTable(): void
+    {
+        $connection = $this->schema->getConnection();
+        /** @var stdClass[] $previousMessageRecords */
+        $previousMessageRecords = $connection
+            ->table('PreviousMessages')
+            ->get();
+
+        foreach ($previousMessageRecords as $previousMessage) {
+            $date = Carbon::createFromTimestamp($previousMessage->Datum);
+            $connection->table('messages')->insert(
+                [
+                    'id'          => $previousMessage->id,
+                    'user_id'     => $previousMessage->SUID,
+                    'receiver_id' => $previousMessage->RUID,
+                    'read'        => $previousMessage->isRead === 'N' ? 0 : 1,
+                    'text'        => $previousMessage->Text,
+                    'created_at'  => $date,
+                    'updated_at'  => $date,
+                ]
+            );
+        }
+    }
+
+    /**
+     * @return void
+     */
+    private function createPreviousMessagesTable(): void
+    {
+        $this->schema->create(
+            'Messages',
+            function (Blueprint $table) {
+                $table->increments('id');
+                $table->integer('Datum');
+                $this->references($table, 'users', 'SUID');
+                $this->references($table, 'users', 'RUID');
+                $table->char('isRead')
+                    ->default('N');
+                $table->text('Text');
+            }
+        );
+    }
+
+    /**
+     * @return void
+     */
+    private function copyNewToPreviousMessagesTable(): void
+    {
+        $connection = $this->schema->getConnection();
+        /** @var Collection|stdClass[] $messageRecords */
+        $messageRecords = $connection
+            ->table('new_messages')
+            ->get();
+
+        foreach ($messageRecords as $messageRecord) {
+            $date = Carbon::createFromFormat('Y-m-d H:i:s', $messageRecord->created_at)
+                ->getTimestamp();
+
+            $connection->table('Messages')->insert(
+                [
+                    'id'     => $messageRecord->id,
+                    'Datum'  => $date,
+                    'SUID'   => $messageRecord->user_id,
+                    'RUID'   => $messageRecord->receiver_id,
+                    'isRead' => $messageRecord->read === 0 ? 'N' : 'Y',
+                    'Text'   => $messageRecord->text,
+                ]
+            );
+        }
+    }
+}
-- 
cgit v1.2.3-54-g00ecf