<?php

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

class CreateMerchantRelationshipAnalyticsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('merchant_relationship_analytics', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('merchant_id');
            $table->integer('application_received')->comment('Applications received (number)')->nullable();
            $table->integer('case_approved')->comment('Cases approved (number including aborted but not cases that were approved and then rejected)')->nullable();
            $table->integer('case_disbursed')->comment('Cases disbursements (number)')->nullable();
            $table->integer('value_disbursed')->comment('Value of disbursements in Rs L or Cr')->nullable();
            $table->integer('case_cancelled')->comment('Cases cancelled (number)')->nullable();
            $table->integer('nach_registered')->comment('Nach registered % = number of cases originated where NACH was registered / total number of disbursed cases')->nullable();
            $table->integer('processing_fee_collected')->comment('Total processing fees collected (Rs)')->nullable();
            $table->integer('principal_outstanding')->comment('Principal outstanding')->nullable();
            $table->integer('cash_collected')->comment('Total cash collected for EMI repayments (including any bounce charges, moratorium interest, penal interest, etc')->nullable();
            $table->integer('percentage_due')->comment('% collected / due = total cash collected / value of EMIs that have come [for loans falling within disbursement date range]')->nullable();
            $table->integer('over_due_amount')->comment('Overdue Amount = Total EMI due + Total moratorium interest due - total cash collected (only calculate for cases where there is an overdue. dont add cases where prepayment or overpayment has been made)')->nullable();
            $table->integer('case_with_dpd')->comment('# of cases with DPDs > 7 days at least once i.e. at least one EMI has come due for the case AND any one of them had a payment delayed by at least 7 days')->nullable();
            $table->integer('percentage_dpd')->comment('% of cases with DPDs > 7 days at least once = # of cases / total disbursements [during this period]')->nullable();
            $table->integer('average_credit_score')->comment('Average credit score - only consider cases where score is between 300-900')->nullable();
            $table->integer('percentage_new_to_credit')->comment('New to credit % = number of cases where credit score is -1 to 6 / total cases where credit score is available in system')->nullable();
            $table->integer('percentage_npa')->comment('NPA %')->nullable();
            $table->integer('percentage_lgd')->comment('Loss given default %')->nullable();
            $table->softDeletes();
            $table->timestamps();
        });
    }

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