<?php

namespace App\Modules\Network\Commands;

use Carbon\Carbon;
use DB;

// Creatia Core
use App\Models\Asns;

// Module Specifics
use App\Modules\Network\Models\Prefixes;
use App\Modules\Network\Models\Supernets;
use App\Modules\Network\Helpers\ControllerCommons;

use Log;

class PrefixSortThread extends \App\Console\Commands\BaseCommand
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'prefix:sort-thread {--as-max=} {--as-mod=}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Threadable script for prefix sorting.';

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

    /**
     * Execute the console command.
     *
     * @return int
     */
    public function handle()
    {

        $now = Carbon::now();
        Asns::leftJoin('prefixes', 'prefixes.asn_id', '=', 'asns.id')
            ->where(DB::raw('asns.id % ' . $this->option('as-max')), '=', $this->option('as-mod'))
            ->where('prefixes.created_at', '>', Carbon::now()->subHours(1))
            ->where('approved', '=', '1')
            ->whereNotNull('asns.organisation_id')
            ->select('asns.id', 'asns.organisation_id')
            ->distinct()
            ->chunk(env('CHUNK_SIZE'), function($asns) use($now) {

            /* This query forms the axiomatic principle for how we handle this:
                select *, (select string_agg(id::text, ' ') from prefixes a where deleted_at is null and  )  from prefixes b where deleted_at is null;
            */
            foreach($asns as $asn) {
                $v4_prefixes = DB::table('prefixes AS a')->select(
                    'id',
                    'address',
                    'mask',
                    DB::raw("(select string_agg(id::text, ' ') from prefixes AS b where deleted_at is null and asn_id = $asn->id and a.mask < b.mask and concat(b.address,'/',b.mask)::cidr << concat(a.address,'/',a.mask)::cidr) as subnet_ids")
                )
                ->where([['asn_id', '=', $asn->id], [DB::raw('family(address)'), '=', '4'], ['mask', '!=', '32'], ['mask', '>=', 16]])
                ->whereNull('deleted_at')
                ->orderByDesc('mask')
                ->get();

                $v6_prefixes = DB::table('prefixes AS a')->select(
                    'id',
                    'address',
                    'mask',
                    DB::raw("(select string_agg(id::text, ' ') from prefixes AS b where deleted_at is null and asn_id = $asn->id and a.mask < b.mask and concat(b.address,'/',b.mask)::cidr << concat(a.address,'/',a.mask)::cidr) as subnet_ids")
                )
                ->where([['asn_id', '=', $asn->id], [DB::raw('family(address)'), '=', '6'], ['mask', '!=', '128'], ['mask', '>=', 16]])
                ->whereNull('deleted_at')
                ->orderByDesc('mask')
                ->get();

                $supernet_lookup = Supernets::select(DB::raw('concat(supernet_id, \'-\', subnet_id) as lookup'))->whereNotNull('supernet_id')->whereNotNull('subnet_id')->get()->keyBy('lookup');

                $to_insert = [];

                foreach([$v4_prefixes, $v6_prefixes] as $prefix_set) {
                    foreach($prefix_set as $prefix) {
                        // Extract subnets
                        $subnet_ids = explode(' ', $prefix->subnet_ids);

                        if(count($subnet_ids) && $subnet_ids[0] !== "") {
                            foreach($subnet_ids as $subnet_id) {
                                if(!isset($supernet_lookup["$prefix->id-$subnet_id"])) {
                                    // It doesn't exist yet so we can insert it.
                                    $to_insert["$prefix->id-$subnet_id"] = [
                                        'created_at' => $now,
                                        'updated_at' => $now,
                                        'tenant_id' => $asn->organisation_id,
                                        'supernet_id' => $prefix->id,
                                        'subnet_id' => $subnet_id
                                    ];                           
                                }
                            }
                        }
                    }
                }

                foreach(array_chunk($to_insert, env('CHUNK_SIZE')) as $insertion_chunk) {
                    DB::table('supernets')->insert(array_values($insertion_chunk));
                }

            }
        });
    
        return 0;
    }
}