<?php

namespace App\Console\Commands\Broadcast;

use App\Console\Commands\BaseCommand;
use App\Helpers\ClickhouseHandler;
use Illuminate\Support\Facades\Redis;
use Symfony\Component\Process\Process;

class CacheMapRates extends BaseCommand
{

    protected $signature = 'broadcast:map-cache';

    protected $description = 'Caches queued map information.';

    protected $notEnterprise = true;

    public function handle()
    {

        $start = microtime(true);

        $chh = new ClickhouseHandler(
            config('creatia.clickhouse.database'),
            config('creatia.clickhouse.endpoint'),
            config('creatia.clickhouse.port'),
            config('creatia.clickhouse.user'),
            config('creatia.clickhouse.password')
        );

        // Store our location data
        $location_data = $chh->selectUnpaged('SELECT name AS identifier, description AS facility_name, lat, long FROM facilities')->get();

        /**
         * This needs to be a multi-process system.
         */

        // Get our general source interfaces
        $source_interface_definitions = $chh->selectUnpaged("SELECT
            id.value as label,
            id.source as source,
            id.interface_name as interface,
            dictGet('interface_operstatus_dict', 'value', (id.source, id.interface_name)) as oper_status,
            dictGet('interface_adminstatus_dict', 'value', (id.source, id.interface_name)) as admin_status,
            dictGet('interface_speeds_dict', 'value', (id.source, id.interface_name)) as max_speed,
            id.timestamp
        FROM interface_descriptions AS id FINAL
        WHERE
            (id.value LIKE 'LAG-BH%' OR id.value LIKE 'BH%')
            AND id.interface_name NOT LIKE 'Port-Channel%'
            AND max_speed != ''
            AND oper_status != 'NOT_PRESENT'
            AND admin_status != 'NOT_PRESENT'
        ")->get();

        // Get our general port speeds for anything in a LAG. We don't need to do this work every single time as speeds change so infrequently. May as well cache it.

        $port_interface_speed_dictionary = Redis::get('weathermap-speeds');

        if($port_interface_speed_dictionary === null) {
            $port_interface_speeds = $chh->selectUnpaged("SELECT
                id.value as label,
                id.source as source,
                id.interface_name as interface,
                dictGet('interface_operstatus_dict', 'value', (id.source, id.interface_name)) as oper_status,
                dictGet('interface_adminstatus_dict', 'value', (id.source, id.interface_name)) as admin_status,
                dictGet('interface_speeds_dict', 'value', (id.source, id.interface_name)) as max_speed,
                id.timestamp
            FROM interface_descriptions AS id FINAL
            WHERE
                id.value LIKE 'BH%'
                AND id.interface_name LIKE 'Port-Channel%'
                AND max_speed != ''
                AND oper_status != 'NOT_PRESENT'
                AND admin_status != 'NOT_PRESENT'
            ")->get();

            foreach($port_interface_speeds as $x) {
                try {
                    $circuit_id = substr(explode(']', explode('[', $x['label'])[3])[0], 2);

                    $port_interface_speed_dictionary[$circuit_id] = $x['max_speed'];
                }
                catch(\Exception $ex) {}
            }

            Redis::setex('weathermap-speeds', 120, json_encode($port_interface_speed_dictionary));
        }
        else {
            $port_interface_speed_dictionary = json_decode($port_interface_speed_dictionary, true);
        }

        $definite_offlines = collect($chh->selectUnpaged("SELECT
            replace(replace(r.id, 'A-', ''), 'Z-', '') AS circuit_id
        FROM interface_mapping_backhaul AS r
        WHERE r.timestamp >= (now() - toIntervalDay(60))
            AND dictGet('interface_operstatus_dict', 'value', (r.source, r.interface_name)) IN ('DOWN', 'LOWER_LAYER_DOWN')
        GROUP BY circuit_id
        HAVING (
            any(dictGet('interface_descriptions_dict', 'value', (r.source, r.interface_name))) LIKE 'BH%'
            OR any(dictGet('interface_descriptions_dict', 'value', (r.source, r.interface_name))) LIKE 'LAG-BH%'
        )")->get())->keyBy('circuit_id');

        $compiled_links = [];
        $queue_rate_tuples = [];
        $link_to_loc = [];

        $time_now = time();

        // Format and compile links
        $exclude_key = [ 'TBD-f82450', 'eurofiber-im-eqxam5-unknown1', 'eurofiber-im-nikhef-unknown2', 'iptp-ny-mia-40g-swap', '69777', '77938', '21525559', '22756110-C1', '90009459', 'subco-dr-asc-40g-swap', 'RGMNS36266', 'MDR_TO_WN', 'MDR_TO_CH', '21405742', '22199838-21689992', 'MCTEQX01-SINEQX03-CEC-00152', 'ADL-PER-TBD', 'VWE298761', 'OQYX/315790//ZYO', 'OQYX/317906//ZYO', 'OQYX/313886//ZYO', 'HK90058363', '90058363', 'syd-akl-tga-10g', 'omantel-mc1-sg3-100g-bbg-swap', 'SRVCKT2282475', 'SINEQX03-PERNXT02-CEC-00174' ];
        
        // Allows us to flip the directionality of a link
        $flip_links = [ '90050486' ];

        foreach($source_interface_definitions as $x) {
            // Needs to be wrapped - It's possible the label will be incorrect.
            try {
                // Break the label down - It's of the form LAG-BH: [type] [provider] [circuit_id] [hostname]
                [$bh, $type, $provider, $circuit_id, $hostname] = explode(' ', str_replace(']', '', str_replace('[', '', $x['label'])));
                
                $circuit_directionality = substr($circuit_id, 0, 1) === 'A' ? 'A->Z' : 'Z->A';

                if(array_search(substr($circuit_id, 2), $flip_links) !== false) {
                    $circuit_directionality = substr($circuit_id, 0, 1) === 'Z' ? 'A->Z' : 'Z->A';
                }

                $circuit_id = substr($circuit_id, 2);

                if(array_search($circuit_id, $exclude_key) !== false) {
                    continue;
                }

                if(!isset($queue_rate_tuples[$circuit_id][$circuit_directionality])) {

                    if($x['oper_status'] !== 'DOWN') {
                        $queue_rate_tuples[$circuit_id][$circuit_directionality] = [ [$x['source'], $x['interface']] ];
                    }

                    $compiled_links[$circuit_id][$circuit_directionality] = [
                        'timestamp' => $time_now,
                        'real-ts' => $time_now,
                        'host-name' => $hostname,
                        'cable-type' => $type,
                        'provider-name' => $provider,
                        'metro' => null,
                        'far-metro' => null,
                        'facility' => null,
                        'far-facility' => null,
                        'is-online' => !isset($definite_offlines[$circuit_id]), 
                        'shallow' => [
                            'max-bps' => ((int)$x['max_speed'] ?: 100000000000),
                            'bps-high' => 0,
                            'bps-low' => 0
                        ],
                        'deep' => [],
                        'waypoints' => []
                    ];
                }
                else {

                    $compiled_links[$circuit_id][$circuit_directionality]['shallow']['max-bps'] = max(
                        $compiled_links[$circuit_id][$circuit_directionality]['shallow']['max-bps'],
                        ((int)$x['max_speed'] ?: 100000000000)
                    );

                    if($x['oper_status'] !== 'DOWN') {
                        $queue_rate_tuples[$circuit_id][$circuit_directionality][] = [$x['source'], $x['interface']];
                    }

                }
            }
            catch(\Exception $ex) {}

            if(!str_contains($x['label'], '[')) {
                continue;
            }
        }

        $allPairs = [];
        $interfaceToCircuit = []; // Map to retrieve results

        foreach($queue_rate_tuples as $circuit_id => $sides) {
            foreach($sides as $directionality => $interfaces) {
                foreach($interfaces as $interface) {
                    // Build tuple string directly
                    $allPairs[] = "('" . addslashes($interface[0]) . "', '" . addslashes($interface[1]) . "')";
                    
                    // Create mapping key for later retrieval
                    $mapKey = $interface[0] . '|' . $interface[1];
                    $interfaceToCircuit[$mapKey] = [$circuit_id, $directionality];
                }
            }
        }

        // Execute single query for all rates if we have any pairs
        if (!empty($allPairs)) {
            $tupleString = implode(', ', $allPairs);

            $allRates = $chh->selectUnpaged("
                WITH latest_rates AS (
                    SELECT 
                        source,
                        interface_name,
                        key,
                        max(timestamp) as x,
                        argMax(value, timestamp) as y
                    FROM interface_rates_queuerates_latest_1m 
                    PREWHERE timestamp >= now() - INTERVAL 120 SECOND
                        AND key IN ('UC0_bitsRate', 'UC1_bitsRate', 'UC2_bitsRate', 'UC3_bitsRate', 'UC4_bitsRate', 'UC5_bitsRate', 'UC6_bitsRate', 'UC7_bitsRate')
                    WHERE (source, interface_name) IN ($tupleString)
                    GROUP BY source, interface_name, key
                )
                SELECT
                    source,
                    interface_name,
                    max(x) as ts,
                    sum(CASE WHEN key != 'UC0_bitsRate' THEN y ELSE 0 END) as value_high,
                    sum(CASE WHEN key = 'UC0_bitsRate' THEN y ELSE 0 END) as value_partial
                FROM latest_rates
                GROUP BY source, interface_name
            ")->get();

            if(sizeof($allRates) === 0) {
                return 0;
            }

            // Initialize aggregated rates for each circuit/direction
            $aggregatedRates = [];
            foreach($queue_rate_tuples as $circuit_id => $sides) {
                foreach($sides as $directionality => $interfaces) {
                    $aggregatedRates[$circuit_id][$directionality] = [
                        'value_high' => 0,
                        'value_partial' => 0
                    ];
                }
            }

            // Map results back to circuit_ids and aggregate
            foreach($allRates as $rate) {
                $mapKey = $rate['source'] . '|' . $rate['interface_name'];
                if (isset($interfaceToCircuit[$mapKey])) {
                    [$circuit_id, $directionality] = $interfaceToCircuit[$mapKey];
                    
                    // Aggregate rates for multiple interfaces per circuit/direction
                    $aggregatedRates[$circuit_id][$directionality]['value_high'] += $rate['value_high'] ?? 0;
                    $aggregatedRates[$circuit_id][$directionality]['value_partial'] += $rate['value_partial'] ?? 0;
                }
            }

            // Apply aggregated rates to compiled_links
            foreach($aggregatedRates as $circuit_id => $sides) {
                foreach($sides as $directionality => $rates) {
                    if (isset($compiled_links[$circuit_id][$directionality])) {
                        $compiled_links[$circuit_id][$directionality]['shallow']['bps-high'] = $rates['value_high'];
                        $compiled_links[$circuit_id][$directionality]['shallow']['bps-low'] = $rates['value_partial'];
                    }
                }
            }
        }

        // Recompile the links
        foreach($compiled_links as $circuit_id => $sides) {

            $new_link = $sides[array_key_first($sides)];
            
            $new_shallow = [
                'A->Z:max-bps'  => 100000000000,
                'Z->A:max-bps'  => 100000000000,
                'A->Z:bps-high' => 0,
                'Z->A:bps-high' => 0,
                'A->Z:bps-low'  => 0,
                'Z->A:bps-low'  => 0
            ];

            foreach($sides as $directionality => $x) {

                $new_shallow["$directionality:max-bps"] = ( isset($port_interface_speed_dictionary[$circuit_id]) ? $port_interface_speed_dictionary[$circuit_id] : $x['shallow']['max-bps'] );

                switch($circuit_id) {
                    case 'CRT-008321': $new_shallow["$directionality:max-bps"] = 100000000000; break;
                    case 'AUC-LSA_NP28': $new_shallow["$directionality:max-bps"] = 100000000000; break;
                    case 'RGWLS32755':
                    case 'VWE298539US':
                    case 'EVC00180035': $new_shallow["$directionality:max-bps"] = 100000000000; break;
                }

                $new_shallow["$directionality:bps-high"] = $x['shallow']['bps-high'];
                $new_shallow["$directionality:bps-low"] = $x['shallow']['bps-low'];

            }

            unset($compiled_links[$circuit_id]);

            $compiled_links[$circuit_id] = $new_link;
            $compiled_links[$circuit_id]['shallow'] = $new_shallow;
        }

        // Retrieve locations and attach them to each link. We only update this occasionally since it doesn't change often enough to warrant regularly checks.
        $location_lookup = Redis::get('weathermap-locations');

        if($location_lookup === null) {
            $location_lookup = collect($chh->selectUnpaged("WITH metro_lookup AS (
                    SELECT
                        name as site,
                        metro
                    FROM facilities
                )
                SELECT 
                    id AS key,
                    facility,
                    this_side.metro AS metro,
                    far_side_facility,
                    that_side.metro AS far_side_metro
                FROM
                    interface_mapping_backhaul FINAL
                JOIN
                    metro_lookup AS this_side ON this_side.site = facility
                JOIN
                    metro_lookup AS that_side ON that_side.site = far_side_facility
                WHERE
                    metro != 'mus' AND far_side_metro != 'mus'
                "
            )->get())->keyBy('key');

            Redis::setex('weathermap-locations', 120, json_encode($location_lookup));
        }
        else {
            $location_lookup = collect(json_decode($location_lookup, true))->keyBy('key');
        }

        foreach(array_keys($compiled_links) as $circuit_id) {

            $location = null;

            if($circuit_id === 'EVC00180035') {
                $location_lookup["A-$circuit_id"] = $location_lookup["Z-VWE298539US"];
                $location_lookup["Z-$circuit_id"] = $location_lookup["A-VWE298539US"];
            }

            if(isset($location_lookup["A-$circuit_id"])) {
                $location = $location_lookup["A-$circuit_id"];
            }

            if(isset($location_lookup["Z-$circuit_id"])) {
                $location = $location_lookup["Z-$circuit_id"];
            }

            if($location === null) {
                continue;
            }

            $compiled_links[$circuit_id]['facility'] = $location['facility'];
            $compiled_links[$circuit_id]['far-facility'] = $location['far_side_facility'];
            $compiled_links[$circuit_id]['metro'] = $location['metro'];
            $compiled_links[$circuit_id]['far-metro'] = $location['far_side_metro'];

            if(!isset($compiled_links[$circuit_id]['shallow']['A->Z:label'])) {
                $compiled_links[$circuit_id]['shallow'] = [
                    ...$compiled_links[$circuit_id]['shallow'],
                    'A->Z:label' => strtoupper(isset($location_lookup["A-$circuit_id"]) ? 
                        ($location_lookup["A-$circuit_id"]['metro'] . ' -> ' . $location_lookup["A-$circuit_id"]['far_side_metro']) :
                        ($location_lookup["Z-$circuit_id"]['far_side_metro'] . ' -> ' . $location_lookup["Z-$circuit_id"]['metro'])),
                    'Z->A:label' => strtoupper(isset($location_lookup["A-$circuit_id"]) ? 
                        ($location_lookup["A-$circuit_id"]['far_side_metro'] . ' -> ' . $location_lookup["A-$circuit_id"]['metro']) :
                        ($location_lookup["Z-$circuit_id"]['metro'] . '->' . $location_lookup["Z-$circuit_id"]['far_side_metro']))
                ];
            }

            $link_to_loc[$circuit_id] = [
                'ts' => $time_now,
                'start' => $location['facility'],
                'end' => $location['far_side_facility']
            ];
        }

        // Iterate over and build our nodes and links...
        $nodes = [];

        foreach($location_data as $location) {
            $nodes[$location['identifier']] = [
                'name' => $location['facility_name'],
                'timestamp' => $time_now,
                'shallow' => [],
                'deep' => [],
                'latitude' => round($location['lat'], 10),
                'longitude' => round($location['long'], 10)
            ];
        }

        // Iterate over the key to coords
        $waypoint_input = [];

        ksort($link_to_loc);
        
        foreach($link_to_loc as $key => $coords) {
            try {
                $waypoint_input[] = $nodes[$coords['start']]['latitude'].','.$nodes[$coords['start']]['longitude'].' '.$nodes[$coords['end']]['latitude'].','.$nodes[$coords['end']]['longitude']." $key";
            }
            catch(\Exception $ex) {}
        }

        $waypoint_process = new Process(['python3', '/var/www/html/creatia/app/Bin/WaypointGenerator.py']);
        $waypoint_process->setInput(implode("\n", $waypoint_input));
        $waypoint_process->run();
                            
        if($waypoint_process->isSuccessful()) {
            foreach(explode(',', trim($waypoint_process->getOutput())) as $line) {

                [$line_key, $line_points] = explode(':', $line);
                $waypoint_list = [];
                
                foreach(explode('>', $line_points) as $point) {
                    if($point !== '') {
                        [$y, $x] = explode('&', $point);
                        $waypoint_list[] = [ round($x, 10), round($y, 10) ];
                    }
                }

                $compiled_links[$line_key]['waypoints'] = $waypoint_list;
            }
        }

        // Convert links from many links with many circuits to many link-circuits.
        $public_link = [];
        $customer_link = [];

        $public_node = [];
        $customer_node = [];

        ksort($nodes);
        ksort($compiled_links);

        foreach($compiled_links as $key => $link) {

            // Has to be wrapped incase, for whatever reason, the maximum is zero.

            try {

                // Anonymise node keys
                $link['name'] = strtoupper($link['metro']) . ' <-> ' . strtoupper($link['far-metro']);
                $link['facility'] = md5($link['facility']);
                $link['far-facility'] = md5($link['far-facility']);

                // Skip not-ready links
                if($link['name'] === ' <-> ') {
                    continue;
                }

                // Override for Indigo
                if($key === 'PERNXT02-SYDEQX03-CEC-00173' || $key === 'SYDEQX03-PERNXT02-00028' || $key === 'SINEQX03-PERNXT02-00029' || $key === 'SINEQX03-PERNXT02-CEC-00174') {
                    $link['shallow'] = [
                        ...$link['shallow'],
                        
                        'A->Z:max-bps'  => 100000000000,
                        'Z->A:max-bps'  => 100000000000
                    ];
                }

                $percent_a_z_high = $link['shallow']['A->Z:bps-high'] === 0 ? 0 : round(($link['shallow']['A->Z:bps-high'] / ($link['shallow']['A->Z:max-bps'] ?: 100000000000)) * 100);
                $percent_z_a_high = $link['shallow']['Z->A:bps-high'] === 0 ? 0 : round(($link['shallow']['Z->A:bps-high'] / ($link['shallow']['Z->A:max-bps'] ?: 100000000000)) * 100);
                $percent_a_z_low = $link['shallow']['A->Z:bps-low'] === 0 ? 0 : round(($link['shallow']['A->Z:bps-low'] / ($link['shallow']['A->Z:max-bps'] ?: 100000000000)) * 100);
                $percent_z_a_low = $link['shallow']['Z->A:bps-low'] === 0 ? 0 : round(($link['shallow']['Z->A:bps-low'] / ($link['shallow']['Z->A:max-bps'] ?: 100000000000)) * 100);

                $user_shallow = [
                    ...$link['shallow'],
                    
                    'A->Z:max-bps'  => 100,
                    'Z->A:max-bps'  => 100,
                    
                    'A->Z:bps-high' => $percent_a_z_high,
                    'Z->A:bps-high' => $percent_z_a_high,
                    'A->Z:bps-low'  => $percent_a_z_low,
                    'Z->A:bps-low'  => $percent_z_a_low
                ];

                unset($link['host-name']);
                unset($link['cable-type']);
                unset($link['provider-name']);

                foreach(['A->Z:bps-high', 'Z->A:bps-high', 'A->Z:bps-low', 'Z->A:bps-low'] as $temp) {
                    if($user_shallow[$temp] > 100) {
                        $user_shallow[$temp] = 100;
                    }
                }
                
                // Build admin version
                $customer_link[md5($key)] = [ ...$link, 'shallow' => $user_shallow ];
                $public_link[md5($key)] = [
                    ...$link,
                    'is-online' => true,
                    'shallow' => [
                        "A->Z:bps-high"     => 1,
                        "A->Z:bps-low"      => 1,
                        "A->Z:label"        => $user_shallow["A->Z:label"],
                        "A->Z:max-bps"      => 100,
                        "Z->A:bps-high"     => 1,
                        "Z->A:bps-low"      => 1,
                        "Z->A:label"        => $user_shallow["Z->A:label"],
                        "Z->A:max-bps"      => 100
                    ]
                ];
            }
            catch(\Throwable $ex) {
                $this->log_error("There was an issue generating a compiled link.", [
                    'exception' => $ex,
                    'key'       => $key,
                    'link'      => $link
                ]);
            }
            
        }

        foreach($nodes as $key => $node) {
            $public_node[md5($key)] = $node;
            $customer_node[md5($key)] = $node;
        }

        Redis::set('websockets-weathermap-standard', json_encode(['nodes' => $customer_node, 'links' => $customer_link ], JSON_UNESCAPED_SLASHES));
        Redis::set('websockets-weathermap-public', json_encode(['nodes' => $public_node, 'links' => $public_link ], JSON_UNESCAPED_SLASHES));

        if(($taken = (microtime(true) - $start)) && $taken > 5) {
            \Log::debug("Map caching has exceeded safe time: $taken");
        }

        return 0;
    }
}