<?php

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

class CreateVirtualOrderCardsTables extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        if (Schema::hasTable('virtual_order_cards_partition')) {
            return;
        }

        Schema::create('virtual_order_cards_partition', function (Blueprint $table) {
            $table->increments('id')->comment('自增ID');
            $table->tinyInteger('type')->unsigned()->default(0)->comment('订单类型（0:基础套餐 1:套餐续费 2：续费包 3:加油包 4:可选包 5:附加包）');
            $table->bigInteger('sim')->unsigned()->default(0)->comment('sim号');
            $table->integer('order_id')->unsigned()->default(0)->comment('订单ID');
            $table->integer('company_id')->unsigned()->default(0)->comment('企业ID');
            $table->integer('package_id')->unsigned()->default(0)->comment('套餐ID');
            $table->integer('counts')->unsigned()->default(0)->comment('数量');
            $table->integer('unit_price')->unsigned()->default(0)->comment('单价');
            $table->timestamp('service_start_at')->nullable()->comment('服务开始时间');
            $table->timestamp('service_end_at')->nullable()->comment('服务结束时间');
            $table->bigInteger('original_sim')->unsigned()->default(0)->comment('原始sim号');
            $table->timestamp('refunded_at')->nullable()->comment('退货时间');
            $table->timestamps();
            $table->softDeletes();

            $table->comment("VD卡关联套餐订单");

            $table->partition('type', 'list');

            $table->addPartition('virtual_order_cards', 'list', [0]);
            $table->addPartition('virtual_order_renewal_cards', 'list', [1]);
            $table->addPartition('virtual_order_renewal_package_cards', 'list', [2]);
            $table->addPartition('virtual_order_flows_package_cards', 'list', [3]);
        });

        Schema::table('virtual_order_cards', function (Blueprint $table) {
            $table->unique(['sim', 'order_id', 'deleted_at']);
            $table->index(['type', 'company_id', 'package_id', 'unit_price']);
            $table->index(['service_start_at', 'service_end_at']);
            $table->comment("VD销售订单关联表");
        });

        Schema::table('virtual_order_renewal_cards', function (Blueprint $table) {
            $table->unique(['sim', 'order_id', 'deleted_at']);
            $table->index(['type', 'company_id', 'package_id', 'unit_price']);
            $table->index(['service_start_at', 'service_end_at']);
            $table->comment("VD续费订单关联表");
        });

        Schema::table('virtual_order_renewal_package_cards', function (Blueprint $table) {
            $table->unique(['sim', 'order_id', 'deleted_at']);
            $table->index(['type', 'company_id', 'package_id', 'unit_price']);
            $table->index(['service_start_at', 'service_end_at']);
            $table->comment("VD续费包订单关联表");
        });

        Schema::table('virtual_order_flows_package_cards', function (Blueprint $table) {
            $table->unique(['sim', 'order_id', 'deleted_at']);
            $table->index(['type', 'company_id', 'package_id', 'unit_price']);
            $table->index(['service_start_at', 'service_end_at']);
            $table->comment("VD加油包订单关联表");
        });

        DB::unprepared(File::get(__DIR__ . '/create_virtual_order_cards_func.pgsql'));
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('virtual_order_cards_partition');
    }
}
