<?php

namespace LibreNMS;

use App\Facades\LibrenmsConfig;
use DateTime;
use DateTimeZone;
use Illuminate\Support\Str;
use LibreNMS\Util\Number;

class Billing
{
    public static function formatBytes($value): string
    {
        return Number::formatBase($value, LibrenmsConfig::get('billing.base'));
    }

    public static function formatBytesShort($value): string
    {
        return Number::formatBase($value, LibrenmsConfig::get('billing.base'), 2, 0, '');
    }

    public static function getDates($dayofmonth, $months = 0): array
    {
        $dayofmonth = Str::padLeft($dayofmonth, 2, '0');
        $year = date('Y');
        $month = date('m');

        if (date('d') > $dayofmonth) {
            // Billing day is past, so it is next month
            $date_end = date_create($year . '-' . $month . '-' . $dayofmonth);
            $date_start = date_create($year . '-' . $month . '-' . $dayofmonth);
            date_add($date_end, date_interval_create_from_date_string('1 month'));
        } else {
            // Billing day will happen this month, therefore started last month
            $date_end = date_create($year . '-' . $month . '-' . $dayofmonth);
            $date_start = date_create($year . '-' . $month . '-' . $dayofmonth);
            date_sub($date_start, date_interval_create_from_date_string('1 month'));
        }

        if ($months > 0) {
            date_sub($date_start, date_interval_create_from_date_string($months . ' month'));
            date_sub($date_end, date_interval_create_from_date_string($months . ' month'));
        }

        // date_sub($date_start, date_interval_create_from_date_string('1 month'));
        date_sub($date_end, date_interval_create_from_date_string('1 day'));

        $date_from = date_format($date_start, 'Ymd') . '000000';
        $date_to = date_format($date_end, 'Ymd') . '235959';

        date_sub($date_start, date_interval_create_from_date_string('1 month'));
        date_sub($date_end, date_interval_create_from_date_string('1 month'));

        $last_from = date_format($date_start, 'Ymd') . '000000';
        $last_to = date_format($date_end, 'Ymd') . '235959';

        $return = [];
        $return['0'] = $date_from;
        $return['1'] = $date_to;
        $return['2'] = $last_from;
        $return['3'] = $last_to;

        return $return;
    }

    public static function getPredictedUsage($bill_day, $cur_used): float|int
    {
        $tmp = self::getDates($bill_day, 0);
        $start = new DateTime($tmp[0], new DateTimeZone(date_default_timezone_get()));
        $end = new DateTime($tmp[1], new DateTimeZone(date_default_timezone_get()));
        $now = new DateTime(date('Y-m-d'), new DateTimeZone(date_default_timezone_get()));
        $total = $end->diff($start)->format('%a');
        $since = $now->diff($start)->format('%a');

        return $cur_used / $since * $total;
    }

    public static function getValue($host, $port, $id, $inout): int
    {
        $oid = 'IF-MIB::ifHC' . $inout . 'Octets.' . $id;
        $device = dbFetchRow('SELECT * from `devices` WHERE `hostname` = ? LIMIT 1', [$host]);
        $value = snmp_get($device, $oid, '-Oqv');

        if (! is_numeric($value)) {
            $oid = 'IF-MIB::if' . $inout . 'Octets.' . $id;
            $value = snmp_get($device, $oid, '-Oqv');
        }

        return (int) $value;
    }

    public static function getLastPortCounter($port_id, $bill_id): array
    {
        $return = [];
        $row = dbFetchRow('SELECT timestamp, in_counter, in_delta, out_counter, out_delta FROM bill_port_counters WHERE `port_id` = ? AND `bill_id` = ?', [$port_id, $bill_id]);
        if (! is_null($row)) {
            $return['timestamp'] = $row['timestamp'];
            $return['in_counter'] = $row['in_counter'];
            $return['in_delta'] = $row['in_delta'];
            $return['out_counter'] = $row['out_counter'];
            $return['out_delta'] = $row['out_delta'];
            $return['state'] = 'ok';
        } else {
            $return['state'] = 'failed';
        }

        return $return;
    }

    public static function getLastMeasurement($bill_id): array
    {
        $return = [];
        $row = dbFetchRow('SELECT timestamp,delta,in_delta,out_delta FROM bill_data WHERE bill_id = ? ORDER BY timestamp DESC LIMIT 1', [$bill_id]);
        if (! is_null($row)) {
            $return['delta'] = $row['delta'];
            $return['in_delta'] = $row['in_delta'];
            $return['out_delta'] = $row['out_delta'];
            $return['timestamp'] = $row['timestamp'];
            $return['state'] = 'ok';
        } else {
            $return['state'] = 'failed';
        }

        return $return;
    }

    private static function get95thagg($bill_id, $datefrom, $dateto): float
    {
        $mq_sql = 'SELECT count(delta) FROM bill_data WHERE bill_id = ?';
        $mq_sql .= ' AND timestamp > ? AND timestamp <= ?';
        $measurements = dbFetchCell($mq_sql, [$bill_id, $datefrom, $dateto]);
        $measurement_95th = (round($measurements / 100 * 95) - 1);

        $q_95_sql = 'SELECT (delta / period * 8) AS rate FROM bill_data  WHERE bill_id = ?';
        $q_95_sql .= ' AND timestamp > ? AND timestamp <= ? ORDER BY rate ASC';
        $a_95th = dbFetchColumn($q_95_sql, [$bill_id, $datefrom, $dateto]);
        $m_95th = $a_95th[$measurement_95th];

        return round($m_95th, 2);
    }

    private static function get95thIn($bill_id, $datefrom, $dateto): float
    {
        $mq_sql = 'SELECT count(delta) FROM bill_data WHERE bill_id = ?';
        $mq_sql .= ' AND timestamp > ? AND timestamp <= ?';
        $measurements = dbFetchCell($mq_sql, [$bill_id, $datefrom, $dateto]);
        $measurement_95th = (round($measurements / 100 * 95) - 1);

        $q_95_sql = 'SELECT (in_delta / period * 8) AS rate FROM bill_data  WHERE bill_id = ?';
        $q_95_sql .= ' AND timestamp > ? AND timestamp <= ? ORDER BY rate ASC';
        $a_95th = dbFetchColumn($q_95_sql, [$bill_id, $datefrom, $dateto]);
        $m_95th = $a_95th[$measurement_95th] ?? 0;

        return round($m_95th, 2);
    }

    private static function get95thout($bill_id, $datefrom, $dateto): float
    {
        $mq_sql = 'SELECT count(delta) FROM bill_data WHERE bill_id = ?';
        $mq_sql .= ' AND timestamp > ? AND timestamp <= ?';
        $measurements = dbFetchCell($mq_sql, [$bill_id, $datefrom, $dateto]);
        $measurement_95th = (round($measurements / 100 * 95) - 1);

        $q_95_sql = 'SELECT (out_delta / period * 8) AS rate FROM bill_data  WHERE bill_id = ?';
        $q_95_sql .= ' AND timestamp > ? AND timestamp <= ? ORDER BY rate ASC';
        $a_95th = dbFetchColumn($q_95_sql, [$bill_id, $datefrom, $dateto]);
        $m_95th = $a_95th[$measurement_95th] ?? 0;

        return round($m_95th, 2);
    }

    public static function getRates($bill_id, $datefrom, $dateto, $dir_95th): array
    {
        $data = [];

        $sum_data = self::getSum($bill_id, $datefrom, $dateto);
        $mtot = $sum_data['total'];
        $mtot_in = $sum_data['inbound'];
        $mtot_out = $sum_data['outbound'];
        $ptot = $sum_data['period'];

        $data['rate_95th_in'] = self::get95thIn($bill_id, $datefrom, $dateto);
        $data['rate_95th_out'] = self::get95thout($bill_id, $datefrom, $dateto);

        if ($dir_95th == 'agg') {
            $data['rate_95th'] = self::get95thagg($bill_id, $datefrom, $dateto);
            $data['dir_95th'] = 'agg';
        } else {
            if ($data['rate_95th_out'] > $data['rate_95th_in']) {
                $data['rate_95th'] = $data['rate_95th_out'];
                $data['dir_95th'] = 'out';
            } else {
                $data['rate_95th'] = $data['rate_95th_in'];
                $data['dir_95th'] = 'in';
            }
        }

        $data['total_data'] = $mtot;
        $data['total_data_in'] = $mtot_in;
        $data['total_data_out'] = $mtot_out;
        $data['rate_average'] = ! empty($ptot) ? ($mtot / $ptot * 8) : 0;
        $data['rate_average_in'] = ! empty($ptot) ? ($mtot_in / $ptot * 8) : 0;
        $data['rate_average_out'] = ! empty($ptot) ? ($mtot_out / $ptot * 8) : 0;

        return $data;
    }

    private static function getSum($bill_id, $datefrom, $dateto)
    {
        $sum = dbFetchRow('SELECT SUM(period) as period, SUM(delta) as total, SUM(in_delta) as inbound, SUM(out_delta) as outbound FROM bill_data WHERE bill_id = ? AND timestamp > ? AND timestamp <= ?', [$bill_id, $datefrom, $dateto]);

        return $sum;
    }

    public static function getPeriod($bill_id, $datefrom, $dateto): array
    {
        $ptot = dbFetchRow('SELECT SUM(period) as `period`, MAX(in_delta) as `peak_in`, MAX(out_delta) as `peak_out`  FROM bill_data WHERE bill_id = ? AND timestamp > ? AND timestamp <= ?', [$bill_id, $datefrom, $dateto]);

        return $ptot;
    }

    public static function getHistoryBitsGraphData($bill_id, $bill_hist_id, $reducefactor): ?array
    {
        $histrow = dbFetchRow('SELECT UNIX_TIMESTAMP(bill_datefrom) as `from`, UNIX_TIMESTAMP(bill_dateto) AS `to`, rate_95th, rate_average, bill_type FROM bill_history WHERE bill_id = ? AND bill_hist_id = ?', [$bill_id, $bill_hist_id]);

        if (is_null($histrow)) {
            return null;
        }

        $graph_data = self::getBitsGraphData($bill_id, $histrow['from'], $histrow['to'], $reducefactor);

        // Overwrite the rate data with the historical version
        $graph_data['rate_95th'] = $histrow['rate_95th'];
        $graph_data['rate_average'] = $histrow['rate_average'];
        $graph_data['bill_type'] = $histrow['bill_type'];

        return $graph_data;
    }

    public static function getBitsGraphData($bill_id, $from, $to, $reducefactor): array
    {
        $i = '0';
        $iter = 0;
        $first = null;
        $last = null;
        $iter_in = 0;
        $iter_out = 0;
        $iter_period = 0;
        $max_in = 0;
        $max_out = 0;
        $tot_in = 0;
        $tot_out = 0;
        $tot_period = 0;
        $in_delta = null;
        $out_delta = null;
        $period = null;
        $in_data = [];
        $out_data = [];
        $tot_data = [];
        $ticks = [];

        if (! isset($reducefactor) || ! is_numeric($reducefactor) || $reducefactor < 1) {
            // Auto calculate reduce factor
            $expectedpoints = ceil(($to - $from) / 300);
            $desiredpoints = 400;
            $reducefactor = max(1, floor($expectedpoints / $desiredpoints));
        }

        $bill_data = dbFetchRow('SELECT * from `bills` WHERE `bill_id`= ? LIMIT 1', [$bill_id]);

        foreach (dbFetchRows('SELECT *, UNIX_TIMESTAMP(timestamp) AS formatted_date FROM bill_data WHERE bill_id = ? AND `timestamp` >= FROM_UNIXTIME( ? ) AND `timestamp` <= FROM_UNIXTIME( ? ) ORDER BY timestamp ASC', [$bill_id, $from, $to]) as $row) {
            $timestamp = $row['formatted_date'];
            if (! $first) {
                $first = $timestamp;
            }

            $period = $row['period'];
            $in_delta = $row['in_delta'] * 8;
            $out_delta = $row['out_delta'] * 8;
            $last = $timestamp;

            $iter_in += $in_delta;
            $iter_out += $out_delta;
            $iter_period += $period;

            if ($period > 0) {
                $max_in = max($max_in, $in_delta / $period);
                $max_out = max($max_out, $out_delta / $period);
                $tot_in += $in_delta;
                $tot_out += $out_delta;
                $tot_period += $period;

                if (++$iter >= $reducefactor) {
                    $out_data[$i] = round($iter_out / $iter_period, 2);
                    $in_data[$i] = round($iter_in / $iter_period, 2);
                    $tot_data[$i] = ($out_data[$i] + $in_data[$i]);
                    $ticks[$i] = $timestamp;
                    $i++;
                    $iter = 0;
                    $iter_out = 0;
                    $iter_in = 0;
                    $iter_period = 0;
                }
            }
        }//end foreach

        if (! empty($iter_in)) {  // Write last element
            $out_data[$i] = round($iter_out / $iter_period, 2);
            $in_data[$i] = round($iter_in / $iter_period, 2);
            $tot_data[$i] = ($out_data[$i] + $in_data[$i]);
            $ticks[$i] = $timestamp ?? time();
            $i++;
        }
        $result = [
            'from' => $from,
            'to' => $to,
            'first' => $first,
            'last' => $last,

            'in_data' => $in_data,
            'out_data' => $out_data,
            'tot_data' => $tot_data,
            'ticks' => $ticks,

            'rate_95th' => $bill_data['rate_95th'],
            'rate_average' => $bill_data['rate_average'],
            'bill_type' => $bill_data['bill_type'],
        ];

        if ($period) {
            $result['max_in'] = $max_in;
            $result['max_out'] = $max_out;
            $result['ave_in'] = $tot_in / $tot_period;
            $result['ave_out'] = $tot_out / $tot_period;
            $result['last_in'] = $in_delta / $period;
            $result['last_out'] = $out_delta / $period;
        }

        return $result;
    }

    public static function getHistoricTransferGraphData($bill_id): array
    {
        $i = '0';

        $in_data = [];
        $out_data = [];
        $tot_data = [];
        $allow_data = [];
        $ave_data = [];
        $overuse_data = [];
        $ticklabels = [];
        $allowed_val = null;

        foreach (dbFetchRows('SELECT * FROM `bill_history` WHERE `bill_id` = ? ORDER BY `bill_datefrom` DESC LIMIT 12', [$bill_id]) as $data) {
            $datefrom = date('Y-m-d', strtotime((string) $data['bill_datefrom']));
            $dateto = date('Y-m-d', strtotime((string) $data['bill_dateto']));
            $datelabel = $datefrom . ' - ' . $dateto;

            array_push($ticklabels, $datelabel);
            array_push($in_data, $data['traf_in']);
            array_push($out_data, $data['traf_out']);
            array_push($tot_data, $data['traf_total']);
            array_push($allow_data, $allowed_val = ($data['bill_type'] == 'Quota' ? $data['bill_allowed'] : 0));
            array_push($overuse_data, $data['bill_type'] == 'Quota' ? $data['bill_overuse'] : 0);
            $i++;
        }//end foreach

        if ($i < 12) {
            $y = (12 - $i);
            for ($x = 0; $x < $y; $x++) {
                $allowed = (($x == '0') ? $allowed_val : '0');
                array_push($in_data, '0');
                array_push($out_data, '0');
                array_push($tot_data, '0');
                array_push($allow_data, $allowed);
                array_push($overuse_data, '0');
                array_push($ticklabels, '');
            }
        }

        $graph_name = 'Historical bandwidth over the last 12 billing periods';

        return [
            'graph_name' => $graph_name,
            'in_data' => $in_data,
            'out_data' => $out_data,
            'tot_data' => $tot_data,
            'allow_data' => $allow_data,
            'ave_data' => $ave_data,
            'overuse_data' => $overuse_data,
            'ticklabels' => $ticklabels,
        ];
    }

    public static function getBandwidthGraphData($bill_id, $bill_hist_id, $from, $to, $imgtype): ?array
    {
        if (is_numeric($bill_hist_id)) {
            $histrow = dbFetchRow('SELECT UNIX_TIMESTAMP(bill_datefrom) as `from`, UNIX_TIMESTAMP(bill_dateto) AS `to`, rate_95th, rate_average FROM bill_history WHERE bill_id = ? AND bill_hist_id = ?', [$bill_id, $bill_hist_id]);

            if (is_null($histrow)) {
                return null;
            }
            $from = $histrow['from'];
            $to = $histrow['to'];
        } else {
            if (! is_numeric($from) || ! is_numeric($to)) {
                throw new \Exception('Must supply from and to if bill_hist_id is not supplied');
            }
        }

        $in_data = [];
        $out_data = [];
        $tot_data = [];
        $allow_data = [];
        $ave_data = [];
        $overuse_data = [];
        $ticklabels = [];

        $data = [];
        $average = 0;
        if ($imgtype == 'day') {
            foreach (dbFetchRows('SELECT DISTINCT UNIX_TIMESTAMP(timestamp) as timestamp, SUM(delta) as traf_total, SUM(in_delta) as traf_in, SUM(out_delta) as traf_out FROM bill_data WHERE `bill_id` = ? AND `timestamp` >= FROM_UNIXTIME(?) AND `timestamp` <= FROM_UNIXTIME(?) GROUP BY DATE(timestamp) ORDER BY timestamp ASC', [$bill_id, $from, $to]) as $data) {
                array_push($ticklabels, date('Y-m-d', $data['timestamp']));
                array_push($in_data, $data['traf_in'] ?? 0);
                array_push($out_data, $data['traf_out'] ?? 0);
                array_push($tot_data, $data['traf_total'] ?? 0);
                $average += $data['traf_total'];
            }

            $ave_count = count($tot_data);

            // Add empty items for the days not yet passed
            $days = (date('j', $to - $from) - $ave_count - 1);
            for ($x = 0; $x < $days; $x++) {
                array_push($ticklabels, '');
                array_push($in_data, 0);
                array_push($out_data, 0);
                array_push($tot_data, 0);
            }
        } elseif ($imgtype == 'hour') {
            foreach (dbFetchRows('SELECT DISTINCT HOUR(timestamp) as hour, SUM(delta) as traf_total, SUM(in_delta) as traf_in, SUM(out_delta) as traf_out FROM bill_data WHERE `bill_id` = ? AND `timestamp` >= FROM_UNIXTIME(?) AND `timestamp` <= FROM_UNIXTIME(?) GROUP BY HOUR(timestamp) ORDER BY HOUR(timestamp) ASC', [$bill_id, $from, $to]) as $data) {
                array_push($ticklabels, sprintf('%02d', $data['hour']) . ':00');
                array_push($in_data, $data['traf_in'] ?? 0);
                array_push($out_data, $data['traf_out'] ?? 0);
                array_push($tot_data, $data['traf_total'] ?? 0);
                $average += $data['traf_total'];
            }

            $ave_count = count($tot_data);
        } else {
            exit("Unknown graph type $imgtype");
        }//end if

        $average = $ave_count ? ($average / $ave_count) : 0;
        $tot_data_size = count($tot_data);
        for ($x = 0; $x <= $tot_data_size; $x++) {
            array_push($ave_data, $average);
        }

        $graph_name = date('M j g:ia', $from) . ' - ' . date('M j g:ia', $to);

        return [
            'graph_name' => $graph_name,
            'in_data' => $in_data,
            'out_data' => $out_data,
            'tot_data' => $tot_data,
            'allow_data' => $allow_data,
            'ave_data' => $ave_data,
            'overuse_data' => $overuse_data,
            'ticklabels' => $ticklabels,
        ];
    }
}
