<?php

namespace App\Console\Commands\Report;

use App\Helpers\ClickhouseHandler;
use Carbon\Carbon;

class ReportAttackCarpetOngoing extends \App\Console\Commands\BaseCommand
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'report-attack:carpet-ongoing';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Correlates attack triggers and general attacks and builds an enriched object.';

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

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

        // Get list of relevant attack records
        $chh = new ClickhouseHandler(
            config('creatia.reporting.db'),
            config('creatia.reporting.host'),
            config('creatia.reporting.port'),
            config('creatia.reporting.user'),
            config('creatia.reporting.pass'),
            null,
            false
        );

        // Fetch records that do not belong anywhere yet
        $attack_records = collect($chh->selectUnpaged("SELECT
            MIN(start_at) as start_at,
            MAX(finish_at) as finish_at,
            destination_parent,
            destination_addr,
            creatia_organisation_id,
            id
            FROM (
                SELECT
                    report_singular.singular_id AS id,
                    start_at::int as start_at,
                    finish_at::int as finish_at,
                    destination_parent as destination_parent,
                    destination_addr as destination_addr,
                    creatia_organisation_id
                FROM
                    report_singular
                LEFT JOIN
                    report_carpet_singular ON report_carpet_singular.singular_id = report_singular.singular_id
                INNER JOIN
                    report_singular_complete ON report_singular.singular_id = report_singular_complete.singular_id
                WHERE
                    finish_at > :time AND
                    report_carpet_singular.singular_id = 0
            )
            GROUP BY
                id, destination_parent, destination_addr, creatia_organisation_id
        ", [
            'time' => Carbon::now()->subWeek()->timestamp
        ])->get());

        // Get a collection of potentially ongoing attacks - We'll then build this into a quick lookup table...
        $ongoing_records = collect($chh->selectUnpaged("SELECT
            id,
            MIN(start_at) as start_at,
            MAX(finish_at) as finish_at,
            destination_24
            FROM (
                SELECT
                    report_carpet.carpet_id as id,
                    report_singular.start_at::int as start_at,
                    report_singular.finish_at::int as finish_at,
                    report_carpet.destination_parent as destination_24
                FROM
                    report_carpet
                LEFT JOIN
                    report_carpet_singular ON report_carpet_singular.carpet_id = report_carpet.carpet_id
                LEFT JOIN
                    report_singular FINAL ON report_carpet_singular.singular_id = report_singular.singular_id
                WHERE
                    report_singular.finish_at > :time
            )
            GROUP BY id, destination_24
        ", [
            'time' => Carbon::now()->subWeek()->timestamp
        ])->get());

        $ongoing_lookup = [];

        foreach($ongoing_records as $record) {
            $ongoing_lookup[$record['destination_24']][$record['start_at']] = [
                'id' => $record['id'],
                'start_at' => $record['start_at'],
                'finish_at' => $record['finish_at']
            ];
        }

        $to_insert_associations = [];

        foreach($attack_records as $x32) {

            if(isset($ongoing_lookup[$x32['destination_parent']])) {

                foreach($ongoing_lookup[$x32['destination_parent']] as $start_for_24 => $x24) {

                    $end_for_24 = $x24['finish_at'];

                    if(($x32['start_at'] >= $start_for_24) && ($x32['start_at'] <= $end_for_24 + 600)) {

                        \Log::debug("Inserting " . $x32['id'] . ' to ' . $x24['id']);

                        $to_insert_associations[] = [
                            'carpet_id'     => $x24['id'],
                            'singular_id'   => $x32['id']
                        ];
                    }
                }
            }
        }

        if(sizeof($to_insert_associations)) {
            $chh->insert('report_carpet_singular', function($r) {    
                return true;
            }, $to_insert_associations);
        }

        return 0;
    }
}