<?php

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

class DasboardView extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        DB::statement("
        CREATE VIEW bond_production_view AS 
         select brp.id, brp.material_id,bp.name, bp.id as bond_product_id , b.name as bond_name, b.id as bond_id, bp.name as bond_product ,bp.bond_type_id ,bpt.name as product_type,brp.duration as duration, brp.mould as mould,
          IFNULL((brp.remaining_piece),0) as remaining_piece, brp.electricity_out_duration as electricity_out_duration, IFNULL((brp.other_prod_blocker_duration),0) as other_prod_blocker_duration, 
           u.alias as unit, brp.start_time, brp.end_time, brp.created_at, brp.updated_at, brp.created_by, brp.updated_by,
           month(end_time) as end_month, date(brp.end_time) as end_date,date(brp.start_time) as start_date, bpp.expected_mould, bpp.cycle_time, 
           bpc.qty as piece_per_mould     
           from bond_recipe_productions as brp 
           left join bond_material_recipes as bmr on brp.material_id=bmr.material_id
           left join bond_products as bp on bmr.bond_product_id=bp.id 
           left join units as u on u.id=bp.qty_unit_id
           left join bond_product_types as bpt on bp.bond_type_id=bpt.id 
           left join bonds as b on bp.bond_id=b.id 
           left join bond_process_params as bpp on bmr.bond_process_id=bpp.id
           left join ( select cs.qty,bpc.bond_process_id from bond_process_consumable as bpc  left join consumable_settings cs on  cs.id=bpc.consumable_settings_id and cs.type='MOULD') as bpc on bpc.bond_process_id=bpp.id ");

           DB::statement("
           CREATE VIEW qc_view AS 
           select qc.material_id, bp.name as bond_product_name,bp.id as bond_product_id, b.name as bond_name,b.id as bond_id,
           bp.bond_type_id,bpt.name as product_type, qty , rejection_under_size_qty, 
           rejection_over_size_qty, rejection_shape_qty ,
           net_qty, u.alias as unit, qc.created_at, qc.updated_at, qc.created_by, qc.updated_by, 
           month(qc.created_at) as created_month, date(qc.created_at) as created_date from quality_checks as qc     
           left join bond_material_recipes as bmr on bmr.material_id=qc.material_id
           left join bond_products as bp on bmr.bond_product_id=bp.id 
           left join units as u on u.id=bp.qty_unit_id
           left join bond_product_types as bpt on bp.bond_type_id=bpt.id 
           left join bonds as b on bp.bond_id=b.id 
           left join bond_process_params as bpp on bmr.bond_process_id=bpp.id");  

           DB::statement("
           CREATE VIEW packing_view AS 
           select ppd.material_id, ppd.packing_id, bp.name as bond_product,b.name as bond_name,
           bpt.name as product_type, ppd.qty as qty, u.alias as unit, pp.created_at, month(pp.created_at) as created_month, date(pp.created_at) as created_date   
            from production_packagings as pp 
            left join production_packaging_detail as ppd on pp.packing_id=ppd.packing_id 
            left join bond_material_recipes bmr on ppd.material_id=bmr.material_id 
            left join bond_products as bp on bmr.bond_product_id=bp.id 
            left join units as u on u.id=bp.qty_unit_id
            left join bond_product_types as bpt on bp.bond_type_id=bpt.id 
            left join bonds as b on bp.bond_id=b.id 
            left join bond_process_params as bpp on bmr.bond_process_id=bpp.id");  

    }

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