<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class Prefixes extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        // Drop the old cruft
        Schema::dropIfExists('network_organisation_attributes');
        Schema::dropIfExists('geo_ip_organisation_attributes');
        Schema::dropIfExists('firewall_rules');
        Schema::dropIfExists('networks');
        Schema::dropIfExists('geo_ips');
        Schema::dropIfExists('rulesets');
        Schema::dropIfExists('sensitivities');

        // Attributes
        Schema::create('mitigation_organisation_attributes', function (Blueprint $table) {
            // General characteristics - PK, timestamps, soft-deletable
            $table->id();
            $table->timestamps();

            // Foreign identifiers
            $table->foreignId('organisation_id')->cascadeOnDelete()->constrained('organisations');

            // Characteristics
            $table->bigInteger('allowed_supernets')->nullable();
            $table->bigInteger('allowed_subnets')->nullable();

            // Characteristics
            $table->string('ssp_id')->nullable();
        });

        Schema::create('geo_ip_organisation_attributes', function (Blueprint $table) {
            // General characteristics - PK, timestamps, soft-deletable
            $table->id();
            $table->timestamps();

            // Foreign identifiers
            $table->foreignId('organisation_id')->cascadeOnDelete()->constrained('organisations');

            // Characteristics
            $table->bigInteger('allowed_supernets')->nullable();
            $table->bigInteger('allowed_subnets')->nullable();
        });

        // Standard Prefix Management

        Schema::create('prefixes', function (Blueprint $table) {
            $table->id();
            $table->timestamps();
            $table->softDeletes();

            // Prefix Info
            $table->string('name')->nullable();
            $table->ipAddress('address');
            $table->integer('mask');

            // Automation relevant information
            $table->timestamp('seen_at')->nullable();
            $table->tinyInteger('still_announced')->nullable();
            $table->string('created_source');

            // Metadata
            $table->foreignId('organisation_id')->cascadeOnDelete()->constrained('organisations');
            $table->foreignId('asn_id')->nullable()->nullOnDelete()->constrained('asns');
        });

        Schema::create('prefix_inclusions', function (Blueprint $table) {
            $table->id();
            $table->timestamps();
            $table->softDeletes();

            // Prefix Info
            $table->ipAddress('address');
            $table->integer('mask');

            // Metadata
            $table->foreignId('asn_id')->nullable()->nullOnDelete()->constrained('asns');
        });

        Schema::create('prefix_exclusions', function (Blueprint $table) {
            $table->id();
            $table->timestamps();
            $table->softDeletes();

            // Prefix Info
            $table->ipAddress('address');
            $table->integer('mask');

            // Metadata
            $table->foreignId('asn_id')->nullable()->nullOnDelete()->constrained('asns');
        });

        Schema::create('supernets', function (Blueprint $table) {
            $table->id();
            $table->timestamps();
            
            $table->foreignId('supernet_id')->nullable()->cascadeOnDelete()->constrained('prefixes');
            $table->foreignId('subnet_id')->cascadeOnDelete()->constrained('prefixes');

            $table->foreignId('tenant_id')->cascadeOnDelete()->constrained('organisations');
        });

        Schema::create('prefix_subtenants', function(Blueprint $table) {
            $table->tinyInteger('also_subnets')->default(0);
            
            $table->foreignId('prefix_id')->cascadeOnDelete()->constrained('prefixes');
            $table->foreignId('subtenant_id')->cascadeOnDelete()->constrained('organisations');
        });

        // Geo IP Management

        Schema::create('prefix_geo_ip', function(Blueprint $table) {
            // Metadata
            $table->id();
            $table->timestamps();
            $table->softDeletes();
            
            // Foreign ID's
            $table->foreignId('prefix_id')->cascadeOnDelete()->constrained('prefixes');
            $table->foreignId('asn_id')->nullable()->nullOnDelete()->constrained('asns');
            $table->foreignId('organisation_id')->cascadeOnDelete()->constrained('organisations');

            $table->string('country')->nullable();
            $table->string('region')->nullable();
            $table->string('city')->nullable();

            // Who-Is Wizbiz
            $table->string('whois_error')->nullable();
        });

        // Mitigation Management

        Schema::create('mitigation_rulesets', function (Blueprint $table) {
            $table->id();
            $table->timestamps();
            $table->softDeletes();

            $table->string('name');
            $table->string('description');
            $table->string('system_identifier');
        });

        Schema::create('mitigation_sensitivities', function (Blueprint $table) {
            $table->id();
            $table->timestamps();
            $table->softDeletes();

            $table->string('name');
            $table->string('description');
            $table->string('system_identifier');
        });

        Schema::create('prefix_mitigation', function(Blueprint $table) {
            // Metadata
            $table->id();
            $table->timestamps();
            $table->softDeletes();

            // Foreign ID's
            $table->foreignId('prefix_id')->cascadeOnDelete()->constrained('prefixes');
            $table->string('ruleset_ids')->nullable(); // Not truly a foreign ID, but we're keeping it to one column with each value separated by a column.
            $table->foreignId('sensitivity_id')->nullable()->nullOnDelete()->constrained('mitigation_sensitivities');
            $table->foreignId('organisation_id')->cascadeOnDelete()->constrained('organisations');

            // Split Braining Capacity
            $table->timestamp('synced_at')->nullable();
            $table->string('origin')->nullable();
            $table->string('last_source')->nullable();
            $table->tinyInteger('sent')->nullable();
            $table->tinyInteger('in_sync')->nullable();

        });

        DB::table('mitigation_rulesets')->insert([[
            'name' => 'Global Strict',
            'description' => '',
            'system_identifier' => 'global-strict'
        ], [
            'name' => 'Global Ultra-Strict',
            'description' => '',
            'system_identifier' => 'global-ultra-strict'
        ]]);

        DB::table('mitigation_sensitivities')->insert([[
            'name' => 'Global High',
            'description' => '',
            'system_identifier' => 'high-inspect-global'
        ], [
            'name' => 'Global Medium',
            'description' => '',
            'system_identifier' => 'medium-inspect-global'
        ], [
            'name' => 'Global Low',
            'description' => '',
            'system_identifier' => 'low-inspect-global'
        ], [
            'name' => 'Australia High',
            'description' => '',
            'system_identifier' => 'high-inspect-australia'
        ], [
            'name' => 'Asia/EU/NA High',
            'description' => '',
            'system_identifier' => 'high-inspect-asia-eu-na'
        ], [
            'name' => 'Asia/EU/NA Low',
            'description' => '',
            'system_identifier' => 'low-inspect-asia-eu-na'
        ], [
            'name' => 'AU Only',
            'description' => '',
            'system_identifier' => 'block-eu-na-asia'
        ], [
            'name' => 'APAC Only',
            'description' => '',
            'system_identifier' => 'block-eu-na'
        ], [
            'name' => 'EU/APAC Only',
            'description' => '',
            'system_identifier' => 'block-na'
        ]]);

        // Firewall Management
        Schema::create('firewall_rules', function (Blueprint $table) {
            // Metadata
            $table->id();
            $table->timestamps();
            $table->softDeletes();

            $table->enum('type', ['custom', 'acl', 'preset']);

            // Foreign ID's
            $table->foreignId('prefix_id')->cascadeOnDelete()->constrained('prefixes');
            
            // ACL Fields
            $table->smallInteger('port')->nullable();
            $table->string('protocol')->nullable();
            $table->string('action')->nullable();
            $table->string('description')->nullable();
            $table->tinyInteger('approved')->nullable();

            // Preset Fields
            $table->string('preset_rule')->nullable();

            // Custom Fields
            $table->string('bpf_rule')->nullable();

            // Whether still active
            $table->tinyInteger('active');

            // Automation
            $table->timestamp('synced_at')->nullable();
            $table->string('origin')->nullable();
            $table->string('last_source')->nullable();
            $table->tinyInteger('sent')->nullable();
        });

    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('firewall_rules');
        
        Schema::dropIfExists('prefix_geo_ip');
        Schema::dropIfExists('prefix_mitigation');
        Schema::dropIfExists('supernets');
        Schema::dropIfExists('prefix_subtenants');
        Schema::dropIfExists('prefix_inclusions');
        Schema::dropIfExists('prefix_exclusions');
        Schema::dropIfExists('prefixes');

        Schema::dropIfExists('mitigation_sensitivities');
        Schema::dropIfExists('mitigation_rulesets');

        Schema::dropIfExists('mitigation_organisation_attributes');
        Schema::dropIfExists('geo_ip_organisation_attributes');
        
    }
}