<?php

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

class CreateAccountsView extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        DB::statement("
        CREATE VIEW account_view AS
        
                select a.name,a.id as account_id, ag.name as account_group, ag.id as group_id, e.tr_date, month(e.tr_date) as month, 
                et.name as entrytype, e.invoice_ref_id, ei.cr_total, ei.dr_total, e.narration, ei.entry_id 
                from entryitems ei left join accounts a on ei.account_id=a.id 
                left join entries e on ei.entry_id=e.id 
                left join account_groups ag on a.group_id=ag.id
                left join entry_types et on e.entrytype_id=et.id
        
      ");

      DB::statement("
        CREATE VIEW account_total_view AS
                select av.account_id as account_id, av.group_id as group_id, av.name, av.account_group,av.tr_date, av.month, av.cr_total, av.dr_total from account_view av 
                union all 
                select a.id, ag.id, a.name, ag.name,NULL,NULL, a.balance_cr,a.balance_dr from accounts a 
                left join account_groups ag on a.group_id=ag.id  
        
      ");
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        DB::statement("account_total_view");
        DB::statement("account_view");
    }
}
