<?php

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

class CreateProductionView extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        DB::statement("
        CREATE VIEW production_view AS
            select bmr.material_id, bpp.cycle_time as cycle_time, bpp.expected_mould as expected_mould,bp.name as bond_product,b.name as bond_name, bp.bond_type_id, bpt.name as product_type, bmr.weight,bmr.powder_cost, (select cs.qty from bond_process_consumable as bpc join consumable_settings as cs on bpc.consumable_settings_id = cs.id where bpc.bond_process_id=bmr.bond_process_id) as 'piece_per_mould',
            (select ps.tag as status from bond_recipe_production_status as brps left join production_status ps on  brps.status_id=ps.id where brps.material_id=bmr.material_id order by brps.created_at DESC LIMIT 1) as status,
             NULL as duration, NULL as mould , NULL as remaining_piece, NULL as electricity_out_duration, NULL as other_prod_blocker_duration,
             NULL as qty , NULL as rejection_under_size_qty , NULL as rejection_over_size_qty , NULL as rejection_shape_qty , NULL as net_qty 	
           from bond_material_recipes as bmr left join bond_products as bp on bmr.bond_product_id=bp.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
           union all 
           select brp.material_id,NULL , NULL , NULL , NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,sum(brp.duration) as duration, sum(brp.mould) as mould, sum(brp.remaining_piece) as remaining_piece, sum(brp.electricity_out_duration) as electricity_out_duration, sum(brp.other_prod_blocker_duration) as other_prod_blocker_duration, 
           NULL as qty , NULL as rejection_under_size_qty , NULL as rejection_over_size_qty , NULL as rejection_shape_qty , NULL as net_qty  
           from bond_recipe_productions as brp group by brp.material_id
           union all 
           select qc.material_id, NULL, NULL, NULL, NULL,NULL,NULL,NULL,NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL , sum(qty) as qty , sum(rejection_under_size_qty) as rejection_under_size_qty, sum(rejection_over_size_qty) as rejection_over_size_qty, sum(rejection_shape_qty) as rejection_shape_qty ,
           sum(net_qty) as net_qty from quality_checks as qc group by qc.material_id 
        
      ");


      DB::statement("
        CREATE VIEW production_sum_view AS
            select pv.material_id,pv.cycle_time, pv.expected_mould ,pv.bond_product,pv.bond_name,pv.bond_type_id,pv.product_type,
            sum(pv.weight) as weight, pv.powder_cost, pv.piece_per_mould, pv.status,
            sum(pv.duration) as duration, sum(pv.mould) as mould, sum(pv.remaining_piece) as remaining_piece,
            sum(pv.electricity_out_duration) as electricity_out_duration, sum(pv.other_prod_blocker_duration) as other_prod_blocker_duration,
            sum(pv.qty) as qty, sum(pv.rejection_under_size_qty) as rejection_under_size_qty, 
            sum(pv.rejection_over_size_qty) as rejection_over_size_qty, 
            sum(pv.rejection_shape_qty) as rejection_shape_qty,
            sum(pv.net_qty) as net_qty from production_view as pv group by pv.material_id
      ");

      DB::statement("
        CREATE VIEW stock_view AS
           select psv.material_id, psv.bond_product, psv.bond_name, psv.product_type , psv.qty as qty, psv.rejection_under_size_qty , psv.rejection_over_size_qty,psv.rejection_shape_qty,
           psv.net_qty from production_sum_view as psv 
           union all 
           select ppd.material_id, bp.name as bond_product,b.name as bond,bpt.name as product_type,-(ppd.qty), 0,0,0,0 from bond_invoice_detail bid left join  production_packagings pp on 
           bid.packing_id=pp.packing_id 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 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 packed_stock_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 
            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 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 
           union all 
           select ppd.material_id, ppd.packing_id,bp.name as bond_product,b.name as bond,bpt.name as product_type,-(ppd.qty) from bond_invoice_detail bid left join  production_packagings pp on 
           bid.packing_id=pp.packing_id 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 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('production_view');
        Schema::dropIfExists('production_sum_view');
    }
}
