<?php

namespace App\Repositories;

use App\Helpers\ClickhouseHandler;
use App\Interfaces\MercuryRepositoryInterface;
use App\Models\Prefixes;
use App\Models\Goliath\GoliathAttackPrefix;
use App\Models\Organisation;
use Carbon\Carbon;
use Illuminate\Support\Arr;
use Illuminate\Support\Facades\Http;

class MercuryRepository implements MercuryRepositoryInterface 
{
    private $chh = null;

    public function __construct($_readonly = true)
    {
        $this->chh = new ClickhouseHandler(
            config('creatia.reporting.db'),
            config('creatia.reporting.host'),
            config('creatia.reporting.port'),
            config('creatia.reporting.user'),
            config('creatia.reporting.pass'),
            null,
            $_readonly
        );
    }

    public function getGraph(int $organisation_id, int $tunnel_id, int $start, int $end): array
    {

        $query = "SELECT
            start_at as x,
            gbps as y,
            direction as cat
        FROM
            mercury_tunnel_usage
        WHERE
            start_at >= :start AND
            start_at < :end AND
            creatia_organisation_id = :org_id AND
            creatia_tunnel_id = :tun_id
        ORDER BY
            start_at ASC";

        $data =  $this->chh->selectUnpaged($query, ['org_id' => $organisation_id, 'tun_id' => $tunnel_id, 'start' => $start, 'end' => $end])->get();

        if(sizeof($data) === 0) {
            return [
                'start'         => 0,
                'end'        => 0,
                'now'           => Carbon::now()->timestamp,
                'data'          => [],
                'categories'    => [['name' => 'Towards Node', 'colour' => "#FF0000"],['name' => 'Towards Endpoint', 'colour' => "#0000FF"]],
                'captioning'    => [
                    'tn_max' => 0,
                    'te_max' => 0,
                    'tn_sum' => 0,
                    'te_sum' => 0
                ]
            ];
        }

        $finish = Carbon::parse($data[array_key_last($data)]['x'])->timestamp;
        $start =  Carbon::parse($data[0]['x'])->timestamp;

        $categorised_data = [];

        $tn_max = $te_max = $tn_sum = $te_sum = 0;

        foreach($data as $x) {

            if($x['cat'] == 1) {
                $tn_max = max($tn_max, $x['y']);
                $tn_sum += $x['y'];
            }
            else {
                $te_max = max($te_max, $x['y']);
                $te_sum += $x['y'];
            }

            $point = [
                'category' => $x['cat'] == 1 ? 'Towards Node' : 'Towards Endpoint',
                'value' => round($x['y'], 2) 
            ];

            $x_ts = Carbon::parse($x['x'])->timestamp;

            if(isset($categorised_data[$x_ts])) {
                $categorised_data[$x_ts]['points'][] = $point;
            }
            else {
                $categorised_data[$x_ts] = [
                    'key' => $x['x'],
                    'points' => [ $point ]
                ];
            }
        }

        return [
            'start'         => $start,
            'end'           => $finish,
            'now'           => $finish,
            'data'          => $categorised_data,
            'categories'    => [['name' => 'Towards Node', 'colour' => "#FF0000"],['name' => 'Towards Endpoint', 'colour' => "#0000FF"]],
            'captioning'    => [
                'tn_max' => round($tn_max, 2),
                'te_max' => round($te_max, 2),
                'tn_sum' => round($tn_sum, 2),
                'te_sum' => round($te_sum, 2)
            ]
        ];
    }

    public function getSummary(int $organisation_id, int $tunnel_id, int $start, int $end): array
    {
        return [];
    }

    public function getAllUtilisation(int $since): array
    {

        $query = "SELECT
                quantileExact(0.95)(total_gbps_per_minute) AS 95th,
                creatia_organisation_id as organisation_id
            FROM
            (
                SELECT
                    minute_interval,
                    sum(avg_gbps_per_direction) AS total_gbps_per_minute,
                    creatia_organisation_id
                FROM
                (
                    SELECT
                        toStartOfInterval(start_at, toIntervalMinute(1)) AS minute_interval,
                        direction,
                        avg(gbps) AS avg_gbps_per_direction,
                        creatia_organisation_id
                    FROM mercury_tunnel_usage
                    WHERE start_at >= :since
                    GROUP BY
                        minute_interval,
                        direction,
                        creatia_organisation_id
                ) AS subquery
                GROUP BY
                    minute_interval,
                    creatia_organisation_id
            ) AS final_query
            GROUP BY creatia_organisation_id";

        $data =  $this->chh->selectUnpaged($query, ['since' => $since])->get();

        return $data;
    }
}