<?php

namespace webuccino\install\migrations;

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Capsule\Manager as Capsule;

class CreateNorthWindTables
{
    public $prefix;

    public function __construct()
    {
        $this->prefix = 'test_';
    }

    public function run()
    {
        // tables that haven't any foriegnkeys
        $this->createCategoryTable();
        $this->createEmployeeTable();
        $this->createCustomerTable();
        $this->createSupplierTable();
        // $this->createShipperTable();
        // $this->createRegionTable();

        // tables that have any foriegnkeys
        $this->createProductTable();
        // $this->createTerritoryTable();
        // $this->createEmployeeTerritoryTable();
        $this->createSalesOrderTable();
        // $this->createOrderDetailTable();

    }

    public function getTableName()
    {
        return $this->prefix . 'Category';
    }

    public function createCategoryTable()
    {
        $tableName = $this->prefix . 'Category';

        Capsule::schema()->create($tableName, function (Blueprint $table) {
            $table->id('categoryId');
            $table->string('categoryName', 15);
            $table->text('description')->nullable();
            $table->text('picture')->nullable();
            $table->timestamp('created_at')->useCurrent();
            $table->timestamp('updated_at')->nullable();

        });
    }

    public function createEmployeeTable()
    {
        $tableName = $this->prefix . 'Employee';

        Capsule::schema()->create($tableName, function (Blueprint $table) {
            $table->id('employeeId');
            $table->string('lastname', 20);
            $table->string('firstname', 10);
            $table->string('title', 30)->nullable();
            $table->string('titleOfCourtesy', 25)->nullable();
            $table->dateTime('birthDate')->nullable();
            $table->dateTime('hireDate')->nullable();
            $table->string('address', 60)->nullable();
            $table->string('city', 15)->nullable();
            $table->string('region', 15)->nullable();
            $table->string('postalCode', 10)->nullable();
            $table->string('country', 15)->nullable();
            $table->string('phone', 24)->nullable();
            $table->string('extension', 4)->nullable();
            $table->string('mobile', 24)->nullable();
            $table->string('email', 225)->nullable();
            $table->binary('photo')->nullable();
            $table->binary('notes')->nullable();
            $table->integer('mgrId')->nullable();
            $table->string('photoPath')->nullable();
            $table->timestamp('created_at')->useCurrent();
            $table->timestamp('updated_at')->nullable();

        });
    }

    public function createCustomerTable()
    {
        $tableName = $this->prefix . 'Customer';

        Capsule::schema()->create($tableName, function (Blueprint $table) {
            $table->id('custId');
            $table->string('companyName', 40);
            $table->string('contactName', 30)->nullable();
            $table->string('contactTitle', 30)->nullable();
            $table->string('address', 60)->nullable();
            $table->string('city', 15)->nullable();
            $table->string('region', 15)->nullable();
            $table->string('postalCode', 10)->nullable();
            $table->string('country', 15)->nullable();
            $table->string('phone', 24)->nullable();
            $table->string('mobile', 24)->nullable();
            $table->string('email', 225)->nullable();
            $table->string('fax', 24)->nullable();
            $table->timestamp('created_at')->useCurrent();
            $table->timestamp('updated_at')->nullable();

        });
    }

    public function createProductTable()
    {
        $tableName = $this->prefix . 'Product';

        Capsule::schema()->create($tableName, function (Blueprint $table) {
            $table->id('productId');
            $table->string('productName', 40);
            $table->foreignId('supplierId')->nullable()->references('supplierId')
                ->on($this->prefix . 'Supplier')
                ->onUpdate('cascade')
                ->onDelete('set null');

            $table->foreignId('categoryId')->nullable()
                ->references('categoryId')
                ->on($this->prefix . 'Category')
                ->onUpdate('cascade')
                ->onDelete('set null');

            $table->string('quantityPerUnit', 20)->nullable();
            $table->decimal('unitPrice', 10)->nullable();
            $table->smallInteger('unitsInStock')->nullable();
            $table->smallInteger('unitsOnOrder')->nullable();
            $table->smallInteger('reorderLevel')->nullable();
            $table->char('discontinued', 1);
            $table->string('made_in');
            $table->integer('paid');

            $table->timestamp('created_at')->useCurrent();
            $table->timestamp('updated_at')->nullable();

        });
    }

    public function createSupplierTable()
    {
        $tableName = $this->prefix . 'Supplier';

        Capsule::schema()->create($tableName, function (Blueprint $table) {
            $table->id('supplierId');
            $table->string('companyName', 40);
            $table->string('contactName', 30)->nullable();
            $table->string('contactTitle', 30)->nullable();
            $table->string('address', 60)->nullable();
            $table->string('city', 15)->nullable();
            $table->string('region', 15)->nullable();
            $table->string('postalCode', 10)->nullable();
            $table->string('country', 15)->nullable();
            $table->string('phone', 24)->nullable();
            $table->string('email', 225)->nullable();
            $table->string('fax', 24)->nullable();
            $table->text('HomePage')->nullable();
            $table->timestamp('created_at')->useCurrent();
            $table->timestamp('updated_at')->nullable();
        });
    }

    // public function createShipperTable()
    // {
    //     $tableName = $this->prefix . 'Shipper';

    //     Capsule::schema()->create($tableName, function (Blueprint $table) {
    //         $table->id('shipperId');
    //         $table->string('companyName', 40);
    //         $table->string('phone', 44)->nullable();
    //         $table->timestamp('created_at')->useCurrent();
    //         $table->timestamp('updated_at')->nullable();
    //     });
    // }

    // public function createRegionTable()
    // {
    //     $tableName = $this->prefix . 'Region';

    //     Capsule::schema()->create($tableName, function (Blueprint $table) {
    //         $table->id('regionId');
    //         $table->string('regiondescription', 50);
    //         $table->timestamp('created_at')->useCurrent();
    //         $table->timestamp('updated_at')->nullable();

    //     });
    // }
    /*
    public function createTerritoryTable()
    {
    $tableName = $this->prefix . 'Territory';

    Capsule::schema()->create($tableName, function (Blueprint $table) {

    $table->string('territoryId', 20)->primary();
    $table->string('territorydescription', 50);
    $table->foreignId('regionId')->nullable()->references('regionId')->on($this->prefix . 'Region')->onUpdate('cascade')->onDelete('set null');
    $table->timestamp('created_at')->useCurrent();
    $table->timestamp('updated_at')->nullable();

    });
    }

    public function createEmployeeTerritoryTable()
    {
    $tableName = $this->prefix . 'EmployeeTerritory';

    Capsule::schema()->create($tableName, function (Blueprint $table) {

    $table->string('territoryId', 20)->index('territoryid');
    $table->foreignId('employeeId')->references('employeeId')->on($this->prefix . 'Employee')->onUpdate('cascade')->onDelete('cascade');
    $table->foreign('territoryId')->references('territoryId')->on($this->prefix . 'Territory')->onUpdate('cascade')->onDelete('cascade');

    $table->primary(['employeeId', 'territoryId']);
    });
    }
     */
    public function createSalesOrderTable()
    {
        $tableName = $this->prefix . 'SalesOrder';

        Capsule::schema()->create($tableName, function (Blueprint $table) use ($prefix) {

            $table->id('orderId');
            $table->foreignId('custId')->nullable()->references('custId')->on($this->prefix . 'Customer')->onUpdate('cascade')->onDelete('set null');
            $table->foreignId('employeeId')->nullable()->references('employeeId')->on($this->prefix . 'Employee')->onUpdate('cascade')->onDelete('set null');
            $table->foreignId('productId')->nullable()->references('productId')->on($this->prefix . 'Product')->onUpdate('cascade')->onDelete('set null');

            $table->dateTime('orderDate')->nullable();
            $table->dateTime('requiredDate')->nullable();
            $table->dateTime('shippedDate')->nullable();
            // $table->integer('shipperid')->index('shipperid');
            $table->decimal('freight', 10)->nullable();
            $table->string('shipName', 40)->nullable();
            $table->string('shipAddress', 60)->nullable();
            $table->string('shipCity', 15)->nullable();
            $table->string('shipRegion', 15)->nullable();
            $table->string('shipPostalCode', 10)->nullable();
            $table->string('shipCountry', 15)->nullable();

        });
    }

    /*
public function createOrderDetailTable()
{
$tableName = $this->prefix . 'OrderDetail';

Capsule::schema()->create($tableName, function (Blueprint $table) {

$table->id('orderDetailId');
$table->foreignId('orderId')->nullable()->references('orderId')->on($this->prefix . 'SalesOrder')->onUpdate('cascade')->onDelete('set null');
$table->foreignId('productId')->nullable()->references('productId')->on($this->prefix . 'Product')->onUpdate('cascade')->onDelete('set null');
$table->decimal('unitPrice', 10);
$table->smallInteger('quantity');
$table->decimal('discount', 10);
});
}
 */

}
