<?php

namespace App\Console\Commands;

use Carbon\Carbon;
use Illuminate\Console\Command;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class TablePartioner extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'partition:logs';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Check and Create more partition for the logs table';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $this->info(Carbon::now('utc').' | Checking partition_logs table.....');
        if (!Schema::hasTable('partition_logs')) {
            $this->info(Carbon::now('utc').' | partition_logs table Not Found!');
            $this->info(Carbon::now('utc').' | Creating new table......');
            Schema::create('partition_logs', function (Blueprint $table) {
                $table->string('table_name')->index();
                $table->date('last_date_partition')->index();
                $table->dateTime('created_at')->index();
                $table->primary(['table_name', 'last_date_partition']);
            });
            $this->info(Carbon::now('utc').' | partition_logs has been successfully created!');
            $this->info(Carbon::now('utc').' | Inserting record into table.....');
            DB::table('partition_logs')->insert([
                'table_name' => 'logs',
                'last_date_partition' => '2017-12-01',
                'created_at' => Carbon::now('utc')
            ]);
            DB::table('partition_logs')->insert([
                'table_name' => 'log_received',
                'last_date_partition' => '2017-12-01',
                'created_at' => Carbon::now('utc')
            ]);
            $this->info(Carbon::now('utc').' | record successfully added!');
        } else
            $this->info(Carbon::now('utc').' | partition_logs table found!');


        /**
         * LOGS PARTITION
         */


        $this->info(Carbon::now('utc').' | Checking if logs table has already been partitioned.....');
        $data = DB::select("show table status in ".env('DB_DATABASE', 'hellio')." where Name='logs'");
        if(empty($data) || !isset($data[0])) {
            $this->error('logs table not found in the database!');
            exit(-1);
        }

        $date_to =  new Carbon('first day of this month');
        $date_to->setTime(0,0,0);
        $date_to->addMonths(2);

        $partition_str = "";
        $data_array = [];


        if(!str_contains(strtolower($data[0]->Create_options), 'partitioned')) {
            $this->info(Carbon::now('utc').' | logs table has NOT been partitioned!');
            $this->info(Carbon::now('utc').' | partitioning the logs table.....');

            $date_from = Carbon::parse('2018-01-01 00:00:00');
            while($date_from->lte($date_to)) {
                $partition_str .= "PARTITION p{$date_from->copy()->subMonth()->format('Ym')} VALUES LESS THAN ('{$date_from->format('Y-m-d H:i:s')}'),";
                $data_array[] = [
                    'table_name' => 'logs',
                    'last_date_partition' => $date_from->toDateString(),
                    'created_at' => Carbon::now('utc')
                ];
                $date_from->addMonth();
            }
            $partition_str = substr($partition_str, 0, -1);

            DB::statement("ALTER TABLE `logs` PARTITION BY RANGE COLUMNS (`submit_date`) ($partition_str)");

            DB::table('partition_logs')->insert($data_array);

            $this->info(Carbon::now('utc').' | logs table has been successfully partitioned!');

        } else {
            $this->info(Carbon::now('utc') . ' | logs table has already been partitioned!');
            $this->info(Carbon::now('utc') . ' | checking if more partition should be added......');

            $record = DB::table('partition_logs')->where('table_name', 'logs')->orderBy('last_date_partition', 'desc')->first();
            $this->info(Carbon::now('utc') . ' | Last partition date => '.$record->last_date_partition);

            $date_from = Carbon::parse($record->last_date_partition);
            $date_from->setTime(0,0,0);

            while($date_from->lt($date_to)) {
                $date_from->addMonth(1);
                $partition_str .= "PARTITION p{$date_from->copy()->subMonth()->format('Ym')} VALUES LESS THAN ('{$date_from->format('Y-m-d H:i:s')}'),";
                $data_array[] = [
                    'table_name' => 'logs',
                    'last_date_partition' => $date_from->toDateString(),
                    'created_at' => Carbon::now('utc')
                ];
            }

            if(empty($data_array)) {
                $this->info(Carbon::now('utc') . ' | logs table partitions are up to date!');
            } else {
                $this->info(Carbon::now('utc') . ' | adding more partition to logs table.....');
                $partition_str = substr($partition_str, 0, -1);

                DB::statement("ALTER TABLE `logs` ADD PARTITION ($partition_str)");

                DB::table('partition_logs')->insert($data_array);

                $this->info(Carbon::now('utc') . ' | '.count($data_array).' more partition(s) have been added to the logs table!');
            }
        }

        /**
         * LOG_RECEIVED PARTITION
         */

        $this->info(Carbon::now('utc').' | Checking if log_received table has already been partitioned.....');
        $data = DB::select("show table status in ".env('DB_DATABASE', 'hellio')." where Name='log_received'");
        if(empty($data) || !isset($data[0])) {
            $this->error('log_received table not found in the database!');
            exit(-1);
        }

        $date_to =  new Carbon('first day of this month');
        $date_to->setTime(0,0,0);
        $date_to->addMonths(2);

        $partition_str = "";
        $data_array = [];


        if(!str_contains(strtolower($data[0]->Create_options), 'partitioned')) {
            $this->info(Carbon::now('utc').' | log_received table has NOT been partitioned!');
            $this->info(Carbon::now('utc').' | partitioning the log_received table.....');

            $date_from = Carbon::parse('2018-01-01 00:00:00');
            while($date_from->lte($date_to)) {
                $partition_str .= "PARTITION p{$date_from->copy()->subMonth()->format('Ym')} VALUES LESS THAN ('{$date_from->format('Y-m-d H:i:s')}'),";
                $data_array[] = [
                    'table_name' => 'log_received',
                    'last_date_partition' => $date_from->toDateString(),
                    'created_at' => Carbon::now('utc')
                ];
                $date_from->addMonth();
            }
            $partition_str = substr($partition_str, 0, -1);

            DB::statement("ALTER TABLE `log_received` PARTITION BY RANGE COLUMNS (`delivery_date`) ($partition_str)");

            DB::table('partition_logs')->insert($data_array);

            $this->info(Carbon::now('utc').' | log_received table has been successfully partitioned!');

        } else {
            $this->info(Carbon::now('utc') . ' | log_received table has already been partitioned!');
            $this->info(Carbon::now('utc') . ' | checking if more partition should be added......');

            $record = DB::table('partition_logs')->where('table_name', 'log_received')->orderBy('last_date_partition', 'desc')->first();
            $this->info(Carbon::now('utc') . ' | Last partition date => '.$record->last_date_partition);

            $date_from = Carbon::parse($record->last_date_partition);
            $date_from->setTime(0,0,0);

            while($date_from->lt($date_to)) {
                $date_from->addMonth(1);
                $partition_str .= "PARTITION p{$date_from->copy()->subMonth()->format('Ym')} VALUES LESS THAN ('{$date_from->format('Y-m-d H:i:s')}'),";
                $data_array[] = [
                    'table_name' => 'log_received',
                    'last_date_partition' => $date_from->toDateString(),
                    'created_at' => Carbon::now('utc')
                ];
            }

            if(empty($data_array)) {
                $this->info(Carbon::now('utc') . ' | log_received table partitions are up to date!');
            } else {
                $this->info(Carbon::now('utc') . ' | adding more partition to log_received table.....');
                $partition_str = substr($partition_str, 0, -1);

                DB::statement("ALTER TABLE `log_received` ADD PARTITION ($partition_str)");

                DB::table('partition_logs')->insert($data_array);

                $this->info(Carbon::now('utc') . ' | '.count($data_array).' more partition(s) have been added to the log_received table!');
            }
        }

        $this->info(Carbon::now('utc') . ' | Task Complete!');
    }
}