<?php

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class AllCapsuleTable extends Migration
{
	const TABLE = 'table_capsule';
	const TABLE_PREVIEW = 'table_capsule_preview';
	const TABLE_VIDEO = 'table_capsule_video';

	protected function capsuleTable()
	{
		Schema::create(static::TABLE, function (Blueprint $table) {
			$table->increments('id');
			$table->uuid('uuid')->unique();

			$table->unsignedInteger('user_id')->nullable();
			$table->tinyInteger('status')->nullable();
			$table->text('caption')->nullable();

			$table->unsignedInteger('preview_id')->nullable();
			$table->unsignedInteger('video_id')->nullable();
			$table->unsignedInteger('address_id')->nullable();
			$table->unsignedInteger('thumbnail_id')->nullable();

			$table->boolean('is_complete')->nullable()->default(false);
			$table->boolean('is_private')->default(true);
			$table->timestamp('open_at')->nullable();
			$table->boolean('blocked')->default(false);

			$table->unsignedSmallInteger('count_likes')->default(0);
			$table->unsignedSmallInteger('count_comments')->default(0);
			$table->unsignedSmallInteger('count_subscribers')->default(0);
			$table->unsignedSmallInteger('count_tagged_users')->default(0);
			$table->unsignedSmallInteger('count_reports')->default(0);

            $table->boolean('is_test')->nullable();

			$table->timestamps();
			$table->softDeletes();

            $table->foreign('user_id')->references('id')->on(UserTable::TABLE)->onDelete('set null');
            $table->foreign('address_id')->references('id')->on(AddressTable::TABLE)->onDelete('set null');
            $table->foreign('preview_id')->references('id')->on(AllCapsuleTable::TABLE_PREVIEW)->onDelete('set null');
            $table->foreign('video_id')->references('id')->on(AllCapsuleTable::TABLE_VIDEO)->onDelete('set null');
            $table->foreign('thumbnail_id')->references('id')->on(AllCapsuleTable::TABLE_PREVIEW)->onDelete('set null');

            $table->index('status');
            $table->index('open_at');
            $table->index('deleted_at');
            $table->index('is_complete');
            $table->index('is_private');
            $table->index('created_at');
            $table->index('updated_at');
		});

        DB::statement("CREATE INDEX idx_capsule_open_at_desc ON ".AllCapsuleTable::TABLE." (open_at DESC)");
        DB::statement("CREATE INDEX idx_capsule_year_created_at ON ".AllCapsuleTable::TABLE." ((date_part('year', created_at))) ");
        DB::statement("CREATE INDEX idx_capsule_month_created_at ON ".AllCapsuleTable::TABLE." ((date_part('month', updated_at)))");
	}


	protected function capsulePreviewTable()
	{
		Schema::create(static::TABLE_PREVIEW, function (Blueprint $table) {
			$table->increments('id');
			$table->uuid('uuid')->unique();

			$table->string('hash')->nullable();
			$table->string('ext')->nullable();
			$table->string('mime_type')->nullable();
			$table->string('size')->nullable();
			$table->string('width')->nullable();
			$table->string('height')->nullable();

			$table->string('aws_key')->nullable();
			$table->string('aws_bucket')->nullable();

			$table->timestamps();

            $table->softDeletes();
		});
	}


	protected function capsuleVideoTable()
	{
		Schema::create(static::TABLE_VIDEO, function (Blueprint $table) {
			$table->increments('id');
			$table->uuid('uuid')->unique();

			$table->unsignedInteger('duration')->nullable();
			$table->string('hash')->nullable();
			$table->string('ext')->nullable();
			$table->string('mime_type')->nullable();
			$table->string('size')->nullable();

			$table->string('aws_key')->nullable();
			$table->string('aws_bucket')->nullable();

			$table->timestamps();

            $table->softDeletes();
		});
	}


    const TABLE_TAGGED_USER = 'table_capsule_tagged_user';

	protected function capsuleTaggedUserTable()
	{
		Schema::create(static::TABLE_TAGGED_USER, function (Blueprint $table) {

		    $table->increments('id');
		    $table->unsignedInteger('user_id')->nullable();
			$table->unsignedInteger('capsule_id')->nullable();
			$table->unsignedInteger('by_user_id')->nullable();
			$table->timestamp('created_at');

            $table->unique(['user_id', 'capsule_id', 'by_user_id']);

            $table->foreign('user_id')->references('id')->on(UserTable::TABLE)->onDelete('cascade');
            $table->foreign('by_user_id')->references('id')->on(UserTable::TABLE)->onDelete('cascade');

            $table->foreign('capsule_id')->references('id')->on(static::TABLE)->onDelete('cascade');

            $table->index('created_at');
		});
	}


	const TABLE_CAPSULE_LIKE = 'table_capsule_like';

	protected function capsuleLike()
	{
		Schema::create(static::TABLE_CAPSULE_LIKE, function (Blueprint $table) {

            $table->increments('id');
			$table->unsignedInteger('user_id')->nullable();
			$table->unsignedInteger('capsule_id')->nullable();
			$table->timestamp('created_at');

			$table->unique(['user_id', 'capsule_id']);

            $table->foreign('user_id')->references('id')->on(UserTable::TABLE)->onDelete('cascade');
            $table->foreign('capsule_id')->references('id')->on(static::TABLE)->onDelete('cascade');

            $table->index('created_at');
		});
	}


	const TABLE_CAPSULE_SUBSCRIBER = 'table_capsule_subscriber';

	protected function capsuleSubscriber()
	{
		Schema::create(static::TABLE_CAPSULE_SUBSCRIBER, function (Blueprint $table) {

            $table->increments('id');
		    $table->unsignedInteger('user_id')->nullable();
			$table->unsignedInteger('capsule_id')->nullable();

            $table->unique(['user_id', 'capsule_id']);

			$table->timestamp('created_at');

            $table->foreign('user_id')->references('id')->on(UserTable::TABLE)->onDelete('cascade');
            $table->foreign('capsule_id')->references('id')->on(static::TABLE)->onDelete('cascade');

            $table->index('created_at');
		});
	}

	/**
	 * Run the migrations.
	 *
	 * @return void
	 */
	public function up()
	{
		// Preview table
		$this->capsulePreviewTable();

		// Video table
		$this->capsuleVideoTable();

		// Capsule table
		$this->capsuleTable();

		// Capsule tagged users
		$this->capsuleTaggedUserTable();

		// Capsule like
		$this->capsuleLike();

		// Capsule subscriber
		$this->capsuleSubscriber();
	}

	/**
	 * Reverse the migrations.
	 *
	 * @return void
	 */
	public function down()
	{
		Schema::dropIfExists(static::TABLE_TAGGED_USER);
		Schema::dropIfExists(static::TABLE_VIDEO);
		Schema::dropIfExists(static::TABLE_PREVIEW);
		Schema::dropIfExists(static::TABLE);
	}
}