<?php

namespace App\Modules\Network\Commands;

use DB;

use App\Modules\Network\Models\Goliath\GoliathRegion;
use App\Modules\Network\Models\Goliath\GoliathSubregion;
use App\Modules\Network\Models\Goliath\GoliathMetro;

class LocationUpdate extends \App\Console\Commands\BaseCommand
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'location:update';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Updates the location list that exists based upon what is in telemetry.';

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

    /**
     * Execute the console command.
     *
     * @return int
     */
    public function handle()
    {
        $db = new \ClickHouseDB\Client([
            'host' => env('CLICKHOUSE_ENDPOINT'),
            'port' => env('CLICKHOUSE_PORT'),
            'username' => env('CLICKHOUSE_USER'),
            'password' => env('CLICKHOUSE_PASSWORD'),
	    'readonly' => true
        ]);

        $db->database(env('CLICKHOUSE_DB'));
        $db->setTimeout(1.5);      // 1 second , support only Int value
        $db->setTimeout(10);       // 10 seconds
        $db->setConnectTimeOut(10); // 5 seconds
        $db->ping(true); // if can`t connect throw exception

        $stmt_regions = $db->select("SELECT * FROM regions");
        $stmt_metros = $db->select("SELECT * FROM metros");
        $stmt_facilities = $db->select("SELECT * FROM facilities");

        $res_regions = $stmt_regions->rawData();
        $res_metros = $stmt_metros->rawData();
        $res_facilities = $stmt_facilities->rawData();

        $all_regions = GoliathRegion::all()->keyBy('telemetry_identifier');
        $all_subregions = GoliathSubregion::all()->keyBy('telemetry_identifier');
        $all_metros = GoliathMetro::all()->keyBy('telemetry_identifier');

        $insert_regions = [];
        $insert_subregions = [];
        $insert_metros = [];

        foreach($res_regions['data'] as $region) {
            if(!isset($all_regions[$region['name']])) {
                $insert_regions[] = [
                    'name' => strtoupper($region['name']) . ' - ' . $region['description'],
                    'telemetry_identifier' => $region['name'],
                    'visible' => 1
                ];
            }
        }

        DB::table('goliath_regions')->insert($insert_regions);
        $all_regions = GoliathRegion::all()->keyBy('telemetry_identifier');

        foreach($res_metros['data'] as $subregion) {
            if(!isset($all_subregions[$subregion['name']])) {
                $insert_subregions[] = [
                    'name' => strtoupper($subregion['name']) . ' - ' . $subregion['description'],
                    'telemetry_identifier' => $subregion['name'],
                    'visible' => 1,
                    'region_id' => $all_regions[$subregion['region']]->id
                ];
            }
        }

        DB::table('goliath_subregions')->insert($insert_subregions);
        $all_subregions = GoliathSubregion::all()->keyBy('telemetry_identifier');

        foreach($res_facilities['data'] as $metro) {
            if(!isset($all_metros[$metro['name']])) {
                $insert_metros[] = [
                    'name' => strtoupper($metro['name']) . ' - ' . $metro['description'],
                    'telemetry_identifier' => $metro['name'],
                    'visible' => 1,
                    'subregion_id' => $all_subregions[$metro['metro']]->id
                ];
            }
        }

        DB::table('goliath_metro')->insert($insert_metros);

        return 0;
    }
}