<?php

namespace App\Repositories;

use App\Interfaces\AkvoradoRepositoryInterface;
use App\Helpers\ClickhouseHandler;

class AkvoradoRepository implements AkvoradoRepositoryInterface 
{
    private $chh = null;

    public function __construct()
    {
        $this->chh = new ClickhouseHandler(
            'default',
            null,
            null,
            null,
            null,
            'http://10.100.18.32:9000/'
        );
    }

    public function getGlobal($start, $end, $address, $direction = 'OUT') {

        $directional_key = $direction == 'OUT' ? 'InIfBoundary' : 'InIfConnectivity';
        $directional_value = $direction == 'OUT' ? 'external' : 'gol';

        $data = $this->chh->selectUnpaged("SELECT
            toStartOfInterval(TimeReceived, INTERVAL 1 SECOND) AS TimeReceived,

            SUM(Bytes * SamplingRate * 8) / 1000 / 1000 / 1000 AS MaxInGbps,
            SUM(Packets * SamplingRate) / 1000000 AS MaxInMpps,
            SUM(Bytes * SamplingRate * 8) / 1000 / 1000 / 1000 AS SumInGbps,
            SUM(Packets * SamplingRate) / 1000000 AS SumInMpps,
            COUNT(Bytes) AS Flows,
            
            arrayCompact(groupArray(SrcAS)) AS SrcAS, 
            arrayCompact(groupArray(SrcCountry)) AS SrcCountry,
            arrayCompact(groupArray(ExporterSite)) AS SrcSite,
            arrayCompact(groupArray(SrcAddr)) AS SrcAddr,
            arrayCompact(groupArray(SrcPort)) AS SrcPort,
            arrayCompact(groupArray(DstPort)) AS DstPort,
            arrayCompact(groupArray(Proto)) AS Protocol
        FROM
            flows 
        WHERE 
            TimeReceived > {p0:integer} AND TimeReceived < {p1:integer} AND DstAddr = {p2:IPv6} AND $directional_key = '$directional_value' 
        GROUP BY TimeReceived
        ORDER BY TimeReceived ASC", [ $start, $end, $address ])->get(false, true)['data'];

        if(sizeof($data) !== 0) {
            return $data;
        }

        return false;
    }

    public function getAsn($start, $end, $address, $direction = 'OUT') {

        $directional_key = $direction == 'OUT' ? 'InIfBoundary' : 'InIfConnectivity';
        $directional_value = $direction == 'OUT' ? 'external' : 'gol';

        $data = $this->chh->selectUnpaged("SELECT 
            MAX(bitrate) / 1000 / 1000 / 1000 AS MaxInGbps,
            MAX(packetrate) / 1000000 AS MaxInMpps,
            (SUM(bitrate)) / 1000 / 1000 / 1000 AS SumInGbps,
            (SUM(packetrate)) / 1000000 AS SumInMpps,
            COUNT(bitrate) AS Flows,
            SrcAS, 
            arrayDistinct(arrayFlatten(groupArray(SrcCountry))) AS SrcCountry,
            arrayDistinct(arrayFlatten(groupArray(SrcSite))) AS SrcSite,
            arrayDistinct(arrayFlatten(groupArray(SrcAddr))) AS SrcAddr,
            arrayDistinct(arrayFlatten(groupArray(SrcPort))) AS SrcPort,
            arrayDistinct(arrayFlatten(groupArray(DstPort))) AS DstPort,
            arrayDistinct(arrayFlatten(groupArray(Protocol))) AS Protocol
        FROM (
            SELECT
                toStartOfInterval(TimeReceived, INTERVAL 1 SECOND) AS TimeReceived,
                SUM(Bytes * SamplingRate * 8) / 1 AS bitrate,
                SUM(Packets * SamplingRate) / 1 AS packetrate,
                SrcAS AS SrcAS, 
                arrayCompact(groupArray(SrcCountry)) AS SrcCountry,
                arrayCompact(groupArray(ExporterSite)) AS SrcSite,
                arrayCompact(groupArray(SrcAddr)) AS SrcAddr,
                arrayCompact(groupArray(SrcPort)) AS SrcPort,
                arrayCompact(groupArray(DstPort)) AS DstPort,
                arrayCompact(groupArray(Proto)) AS Protocol
            FROM
                flows 
            WHERE 
                TimeReceived > {p0:integer} AND TimeReceived < {p1:integer} AND DstAddr = {p2:IPv6} AND $directional_key = '$directional_value' 
            GROUP BY TimeReceived, SrcAS
            ORDER BY TimeReceived ASC
        ) GROUP BY SrcAS", [ $start, $end, $address ])->get(false, true)['data'];

        if(sizeof($data) !== 0) {
            return $data;
        }

        return false;
    }

    public function getCountry($start, $end, $address, $direction = 'OUT') {

        $directional_key = $direction == 'OUT' ? 'InIfBoundary' : 'InIfConnectivity';
        $directional_value = $direction == 'OUT' ? 'external' : 'gol';

        $data = $this->chh->selectUnpaged("SELECT 
            MAX(bitrate) / 1000 / 1000 / 1000 AS MaxInGbps,
            MAX(packetrate) / 1000000 AS MaxInMpps,
            (SUM(bitrate)) / 1000 / 1000 / 1000 AS SumInGbps,
            (SUM(packetrate)) / 1000000 AS SumInMpps,
            COUNT(bitrate) AS Flows,
            arrayDistinct(arrayFlatten(groupArray(SrcAS))) AS SrcAS, 
            SrcCountry,
            arrayDistinct(arrayFlatten(groupArray(SrcSite))) AS SrcSite,
            arrayDistinct(arrayFlatten(groupArray(SrcAddr))) AS SrcAddr,
            arrayDistinct(arrayFlatten(groupArray(SrcPort))) AS SrcPort,
            arrayDistinct(arrayFlatten(groupArray(DstPort))) AS DstPort,
            arrayDistinct(arrayFlatten(groupArray(Protocol))) AS Protocol
        FROM (
            SELECT
                toStartOfInterval(TimeReceived, INTERVAL 1 SECOND) AS TimeReceived,
                SUM(Bytes * SamplingRate * 8) / 1 AS bitrate,
                SUM(Packets * SamplingRate) / 1 AS packetrate,
                arrayCompact(groupArray(SrcAS)) AS SrcAS, 
                SrcCountry,
                arrayCompact(groupArray(ExporterSite)) AS SrcSite,
                arrayCompact(groupArray(SrcAddr)) AS SrcAddr,
                arrayCompact(groupArray(SrcPort)) AS SrcPort,
                arrayCompact(groupArray(DstPort)) AS DstPort,
                arrayCompact(groupArray(Proto)) AS Protocol
            FROM
                flows 
            WHERE 
                TimeReceived > {p0:integer} AND TimeReceived < {p1:integer} AND DstAddr = {p2:IPv6} AND $directional_key = '$directional_value' 
            GROUP BY TimeReceived, SrcCountry
            ORDER BY TimeReceived ASC
        ) GROUP BY SrcCountry", [ $start, $end, $address ])->get(false, true)['data'];

        if(sizeof($data) !== 0) {
            return $data;
        }

        return false;
    }

    public function getSite($start, $end, $address, $direction = 'OUT') {

        $directional_key = $direction == 'OUT' ? 'InIfBoundary' : 'InIfConnectivity';
        $directional_value = $direction == 'OUT' ? 'external' : 'gol';

        $data = $this->chh->selectUnpaged("SELECT 
            MAX(bitrate) / 1000 / 1000 / 1000 AS MaxInGbps,
            MAX(packetrate) / 1000000 AS MaxInMpps,
            (SUM(bitrate)) / 1000 / 1000 / 1000 AS SumInGbps,
            (SUM(packetrate)) / 1000000 AS SumInMpps,
            COUNT(bitrate) AS Flows,
            arrayDistinct(arrayFlatten(groupArray(SrcAS))) AS SrcAS, 
            arrayDistinct(arrayFlatten(groupArray(SrcCountry))) AS SrcCountry,
            SrcSite,
            arrayDistinct(arrayFlatten(groupArray(SrcAddr))) AS SrcAddr,
            arrayDistinct(arrayFlatten(groupArray(SrcPort))) AS SrcPort,
            arrayDistinct(arrayFlatten(groupArray(DstPort))) AS DstPort,
            arrayDistinct(arrayFlatten(groupArray(Protocol))) AS Protocol
        FROM (
            SELECT
                toStartOfInterval(TimeReceived, INTERVAL 1 SECOND) AS TimeReceived,
                SUM(Bytes * SamplingRate * 8) / 1 AS bitrate,
                SUM(Packets * SamplingRate) / 1 AS packetrate,
                arrayCompact(groupArray(SrcAS)) AS SrcAS, 
                arrayCompact(groupArray(SrcCountry)) AS SrcCountry,
                ExporterSite as SrcSite,
                arrayCompact(groupArray(SrcAddr)) AS SrcAddr,
                arrayCompact(groupArray(SrcPort)) AS SrcPort,
                arrayCompact(groupArray(DstPort)) AS DstPort,
                arrayCompact(groupArray(Proto)) AS Protocol
            FROM
                flows 
            WHERE 
                TimeReceived > {p0:integer} AND TimeReceived < {p1:integer} AND DstAddr = {p2:IPv6} AND $directional_key = '$directional_value' 
            GROUP BY TimeReceived, SrcSite
            ORDER BY TimeReceived ASC
        ) GROUP BY SrcSite", [ $start, $end, $address ])->get(false, true)['data'];

        if(sizeof($data) !== 0) {
            return $data;
        }

        return false;
    }

    public function getProtocol($start, $end, $address, $direction = 'OUT') {

        $directional_key = $direction == 'OUT' ? 'InIfBoundary' : 'InIfConnectivity';
        $directional_value = $direction == 'OUT' ? 'external' : 'gol';

        $data = $this->chh->selectUnpaged("SELECT 
            MAX(bitrate) / 1000 / 1000 / 1000 AS MaxInGbps,
            MAX(packetrate) / 1000000 AS MaxInMpps,
            (SUM(bitrate)) / 1000 / 1000 / 1000 AS SumInGbps,
            (SUM(packetrate)) / 1000000 AS SumInMpps,
            COUNT(bitrate) AS Flows,
            arrayDistinct(arrayFlatten(groupArray(SrcAS))) AS SrcAS, 
            arrayDistinct(arrayFlatten(groupArray(SrcCountry))) AS SrcCountry,
            arrayDistinct(arrayFlatten(groupArray(SrcSite))) AS SrcSite,
            arrayDistinct(arrayFlatten(groupArray(SrcAddr))) AS SrcAddr,
            arrayDistinct(arrayFlatten(groupArray(SrcPort))) AS SrcPort,
            arrayDistinct(arrayFlatten(groupArray(DstPort))) AS DstPort,
            Protocol
        FROM (
            SELECT
                toStartOfInterval(TimeReceived, INTERVAL 1 SECOND) AS TimeReceived,
                SUM(Bytes * SamplingRate * 8) / 1 AS bitrate,
                SUM(Packets * SamplingRate) / 1 AS packetrate,
                arrayCompact(groupArray(SrcAS)) AS SrcAS, 
                arrayCompact(groupArray(SrcCountry)) AS SrcCountry,
                arrayCompact(groupArray(SrcAddr)) AS SrcAddr,
                arrayCompact(groupArray(SrcPort)) AS SrcPort,
                arrayCompact(groupArray(DstPort)) AS DstPort,
                arrayCompact(groupArray(ExporterSite)) AS SrcSite,
                Proto as Protocol
            FROM
                flows 
            WHERE 
                TimeReceived > {p0:integer} AND TimeReceived < {p1:integer} AND DstAddr = {p2:IPv6} AND $directional_key = '$directional_value' 
            GROUP BY TimeReceived, Protocol
            ORDER BY TimeReceived ASC
        ) GROUP BY Protocol", [ $start, $end, $address ])->get(false, true)['data'];

        if(sizeof($data) !== 0) {
            return $data;
        }

        return false;
    }

    public function getDestination($start, $end, $address, $direction = 'OUT') {

        $directional_key = $direction == 'OUT' ? 'InIfBoundary' : 'InIfConnectivity';
        $directional_value = $direction == 'OUT' ? 'external' : 'gol';

        $data = $this->chh->selectUnpaged("SELECT 
            MAX(bitrate) / 1000 / 1000 / 1000 AS MaxInGbps,
            MAX(packetrate) / 1000000 AS MaxInMpps,
            (SUM(bitrate)) / 1000 / 1000 / 1000 AS SumInGbps,
            (SUM(packetrate)) / 1000000 AS SumInMpps,
            COUNT(bitrate) AS Flows,
            arrayDistinct(arrayFlatten(groupArray(SrcAS))) AS SrcAS, 
            arrayDistinct(arrayFlatten(groupArray(SrcCountry))) AS SrcCountry,
            arrayDistinct(arrayFlatten(groupArray(SrcSite))) AS SrcSite,
            arrayDistinct(arrayFlatten(groupArray(SrcAddr))) AS SrcAddr,
            arrayDistinct(arrayFlatten(groupArray(SrcPort))) AS SrcPort,
            DstPort,
            arrayDistinct(arrayFlatten(groupArray(Protocol))) AS Protocol
        FROM (
            SELECT
                toStartOfInterval(TimeReceived, INTERVAL 1 SECOND) AS TimeReceived,
                SUM(Bytes * SamplingRate * 8) / 1 AS bitrate,
                SUM(Packets * SamplingRate) / 1 AS packetrate,
                arrayCompact(groupArray(SrcAS)) AS SrcAS, 
                arrayCompact(groupArray(SrcCountry)) AS SrcCountry,
                arrayCompact(groupArray(ExporterSite)) AS SrcSite,
                arrayCompact(groupArray(SrcAddr)) AS SrcAddr,
                arrayCompact(groupArray(SrcPort)) AS SrcPort,
                DstPort,
                arrayCompact(groupArray(Proto)) AS Protocol
            FROM
                flows 
            WHERE 
                TimeReceived > {p0:integer} AND TimeReceived < {p1:integer} AND DstAddr = {p2:IPv6} AND $directional_key = '$directional_value' 
            GROUP BY TimeReceived, DstPort
            ORDER BY TimeReceived
        ) GROUP BY DstPort", [ $start, $end, $address ])->get(false, true)['data'];

        if(sizeof($data) !== 0) {
            return $data;
        }

        return false;
    }
    
    public function getSourcePort($start, $end, $address, $direction = 'OUT') {

        $directional_key = $direction == 'OUT' ? 'InIfBoundary' : 'InIfConnectivity';
        $directional_value = $direction == 'OUT' ? 'external' : 'gol';

        $data = $this->chh->selectUnpaged("SELECT 
            MAX(bitrate) / 1000 / 1000 / 1000 AS MaxInGbps,
            MAX(packetrate) / 1000000 AS MaxInMpps,
            (SUM(bitrate)) / 1000 / 1000 / 1000 AS SumInGbps,
            (SUM(packetrate)) / 1000000 AS SumInMpps,
            COUNT(bitrate) AS Flows,
            arrayDistinct(arrayFlatten(groupArray(SrcAS))) AS SrcAS, 
            arrayDistinct(arrayFlatten(groupArray(SrcCountry))) AS SrcCountry,
            arrayDistinct(arrayFlatten(groupArray(SrcSite))) AS SrcSite,
            arrayDistinct(arrayFlatten(groupArray(SrcAddr))) AS SrcAddr,
            SrcPort,
            arrayDistinct(arrayFlatten(groupArray(DstPort))) AS DstPort,
            arrayDistinct(arrayFlatten(groupArray(Protocol))) AS Protocol
        FROM (
            SELECT
                toStartOfInterval(TimeReceived, INTERVAL 1 SECOND) AS TimeReceived,
                SUM(Bytes * SamplingRate * 8) / 1 AS bitrate,
                SUM(Packets * SamplingRate) / 1 AS packetrate,
                arrayCompact(groupArray(SrcAS)) AS SrcAS, 
                arrayCompact(groupArray(SrcCountry)) AS SrcCountry,
                arrayCompact(groupArray(ExporterSite)) AS SrcSite,
                arrayCompact(groupArray(SrcAddr)) AS SrcAddr,
                SrcPort,
                arrayCompact(groupArray(DstPort)) AS DstPort,
                arrayCompact(groupArray(Proto)) AS Protocol
            FROM
                flows 
            WHERE 
                TimeReceived > {p0:integer} AND TimeReceived < {p1:integer} AND DstAddr = {p2:IPv6} AND $directional_key = '$directional_value' 
            GROUP BY TimeReceived, SrcPort
            ORDER BY TimeReceived ASC
        ) GROUP BY SrcPort", [ $start, $end, $address ])->get(false, true)['data'];

        if(sizeof($data) !== 0) {
            return $data;
        }

        return false;
    }

    public function getSourceAddress($start, $end, $address, $direction = 'OUT') {

        $directional_key = $direction == 'OUT' ? 'InIfBoundary' : 'InIfConnectivity';
        $directional_value = $direction == 'OUT' ? 'external' : 'gol';

        $data = $this->chh->selectUnpaged("SELECT 
            MAX(bitrate) / 1000 / 1000 / 1000 AS MaxInGbps,
            MAX(packetrate) / 1000000 AS MaxInMpps,
            (SUM(bitrate)) / 1000 / 1000 / 1000 AS SumInGbps,
            (SUM(packetrate)) / 1000000 AS SumInMpps,
            COUNT(bitrate) AS Flows,
            arrayDistinct(arrayFlatten(groupArray(SrcAS))) AS SrcAS, 
            arrayDistinct(arrayFlatten(groupArray(SrcCountry))) AS SrcCountry,
            arrayDistinct(arrayFlatten(groupArray(SrcSite))) AS SrcSite,
            SrcAddr,
            arrayDistinct(arrayFlatten(groupArray(SrcPort))) AS SrcPort,
            arrayDistinct(arrayFlatten(groupArray(DstPort))) AS DstPort,
            arrayDistinct(arrayFlatten(groupArray(Protocol))) AS Protocol
        FROM (
            SELECT
                toStartOfInterval(TimeReceived, INTERVAL 1 SECOND) AS TimeReceived,
                SUM(Bytes * SamplingRate * 8) / 1 AS bitrate,
                SUM(Packets * SamplingRate) / 1 AS packetrate,
                arrayCompact(groupArray(SrcAS)) AS SrcAS, 
                arrayCompact(groupArray(SrcCountry)) AS SrcCountry,
                arrayCompact(groupArray(ExporterSite)) AS SrcSite,
                SrcAddr,
                arrayCompact(groupArray(SrcPort)) AS SrcPort,
                arrayCompact(groupArray(DstPort)) AS DstPort,
                arrayCompact(groupArray(Proto)) AS Protocol
            FROM
                flows 
            WHERE 
                TimeReceived > {p0:integer} AND TimeReceived < {p1:integer} AND DstAddr = {p2:IPv6} AND $directional_key = '$directional_value' 
            GROUP BY TimeReceived, SrcAddr
            ORDER BY TimeReceived ASC
        ) GROUP BY SrcAddr", [ $start, $end, $address ])->get(false, true)['data'];

        if(sizeof($data) !== 0) {
            return $data;
        }

        return false;
    }

    public function getGreUsage($start, $end, $node, $endpoint) {

        $data_away = $this->chh->selectUnpaged("SELECT
            toStartOfInterval(TimeReceived, INTERVAL 60 SECOND) AS TimeReceived,
            SUM(Bytes * SamplingRate * 8) / 1000 / 1000 / 1000 / 60 AS Gbps
        FROM
            flows 
        WHERE 
            TimeReceived > {p0:integer} AND TimeReceived <= {p1:integer} AND Proto = 47 AND SrcAddr = {p2:IPv6} AND DstAddr = {p3:IPv6}
        GROUP BY TimeReceived
        ORDER BY TimeReceived ASC", [ $start, $end, $node, $endpoint ])->get(false, true);

        $data_towards = $this->chh->selectUnpaged("SELECT
            toStartOfInterval(TimeReceived, INTERVAL 60 SECOND) AS TimeReceived,
            SUM(Bytes * SamplingRate * 8) / 1000 / 1000 / 1000 / 60 AS Gbps
            FROM
            flows 
            WHERE 
            TimeReceived > {p0:integer} AND TimeReceived <= {p1:integer} AND Proto = 47 AND SrcAddr = {p2:IPv6} AND DstAddr = {p3:IPv6}
            GROUP BY TimeReceived
            ORDER BY TimeReceived ASC", [ $start, $end, $endpoint, $node ])->get(false, true);

        return [
            'away' => isset($data_away['data']) && sizeof($data_away['data']) ? $data_away['data'] : [],
            'towards' => isset($data_towards['data']) && sizeof($data_towards['data']) ? $data_towards['data'] : []
        ];
    }
}