<?php

namespace App\Console\Commands\Report;

use App\Helpers\ClickhouseHandler;
use App\Models\Goliath\GoliathAttackPrefix;

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

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Calculates who should have access to a given report singular';

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

    /**
     * Execute the console command.
     *
     * @return int
     */
    public function handle()
    {
        $chh_reporting = new ClickhouseHandler(
            config('creatia.reporting.db'),
            config('creatia.reporting.host'),
            config('creatia.reporting.port'),
            config('creatia.reporting.user'),
            config('creatia.reporting.pass'),
            null,
            false
        );

        // This will only be running over recent attacks most of the time so the actual performance isn't of significant concern.
        $completed_attacks = collect($chh_reporting->select("SELECT 
                s.singular_id,
                s.creatia_organisation_id AS organisation_id
            FROM report_singular s
            WHERE
                s.singular_id IN (
                    SELECT singular_id 
                    FROM report_singular_complete 
                    WHERE singular_id != 0
                )
                AND
                s.singular_id NOT IN (
                    SELECT singular_id 
                    FROM report_singular_subtenant
                    WHERE singular_id != 0
                )
                AND s.finish_at > (now() - (3600 * 24))
                ORDER BY
                    start_at ASC", 
                0, 20000, []
            )->get());

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

        $to_insert = [];
        $acknowledged_prefix_main_org = [];
        $organisations = [];

        // Fetch all singular relevant data
        $all_subtenancy_info = GoliathAttackPrefix::join('goliath_attacks', 'attack_id', '=', 'goliath_attacks.id')
            ->join('prefixes', 'goliath_attack_prefixes.prefix_id', '=', 'prefixes.id')
            ->leftJoin('prefix_subtenants', 'prefix_subtenants.prefix_id', '=', 'prefixes.id')
            ->whereRaw('((goliath_attacks.started_at >= prefix_subtenants.created_at AND (goliath_attacks.last_at <= prefix_subtenants.deleted_at OR (prefix_subtenants.deleted_at IS NULL))) OR (prefix_subtenants.created_at IS NULL))')
            ->where([
                ['prefixes.mask', '>=', 20],
                ['prefixes.mask', '<=', 32],
                [\DB::raw('family(address)'), '=', 4]
            ])
            ->whereIn('attack_id', $completed_attacks->pluck('singular_id'))
            ->select('attack_id', 'prefix_subtenants.subtenant_id', 'prefixes.organisation_id')
            ->distinct()
            ->get();

        // Index it
        foreach($all_subtenancy_info as $x) {

            if(isset($organisations[$x['attack_id']])) {
                $organisations[$x['attack_id']][] = $x;
            }
            else {
                $organisations[$x['attack_id']] = [ $x ];
            }
        }

        // Iterate
        foreach($completed_attacks as $i => $x) {

            // Only do something IF we've got information tracked in Creatia - It should always be, but let's be defensive in the scenario a row gets deleted...
            if(!isset($organisations[$x['singular_id']])) {
                continue;
            }

            // Fetch organisations that have access to this attack record
            $subtenants = $organisations[$x['singular_id']];
 
            if(sizeof($subtenants) !== 0) {

                // Always create a primary organisation association - This will serve as a marker as to whether we've iterated over this attack before in the initial query at the start of this script.
                if(!isset($acknowledged_prefix_main_org[$x['singular_id']])) {
                    $to_insert[] = [
                        'singular_id'               => $x['singular_id'],
                        'creatia_organisation_id'   => $subtenants[0]->organisation_id
                    ];

                    $acknowledged_prefix_main_org[$x['singular_id']] = true;
                }

                // Generate subtenancy records in general
                foreach($subtenants as $y) {

                    if($y->subtenant_id !== null) {
                        $to_insert[] = [
                            'singular_id'               => $x['singular_id'],
                            'creatia_organisation_id'   => $y->subtenant_id
                        ];
                    }
                }   
            }
        }

        if($to_insert !== []) {
            $chh_reporting->insert('report_singular_subtenant', function($r) { return true; }, $to_insert);
        }

        return 0;
    }
}