<?php

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

class CreateConsumableView extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        // DB::statement("
        // create view consumable_consumption_tmp_view as 
        // (select ccd.consumable_id, ci.name->>'$.en' as consumable_name , ci.spec , ci.add_spec, ci.weight ,ccd.qty,0 as op_stock_price, 0 as unit_price, 
        // cc.name->>'$.en' as consumable_category, u.alias, ci.category_id, ccd.created_at,ccd.consumed_percentage as consumed_percentage,((ccd.qty*ccd.consumed_percentage)/100) as consumed_qty
        // from consumable_consumpation_details as ccd left join consumable_items ci on ccd.consumable_id=ci.id 
        // left join units as u on ci.opening_stock_unit=u.id 
        // left join consumable_categories as cc on ci.category_id=cc.id 
        // left join consumable_consumpations ccn on ccd.consumpation_id = ccn.id order by ccd.id desc)  
        //  union 
        // select icd.consumable_id, ci.name->>'$.en', ci.spec, ci.add_spec, ci.weight, 0 ,0,icd.unit_price , cc.name->>'$.en' ,NULL, ci.category_id, NULL,NULL,NULL from invoice_consumable_detail icd 
        // left join consumable_items as ci  on icd.consumable_id=ci.id 
        // left join consumable_categories as cc on ci.category_id=cc.id  
        // union 
        // select ci.id, ci.name->>'$.en', ci.spec, ci.add_spec, ci.weight, 0, ci.unit_price, 0 ,cc.name->>'$.en', NULL, ci.category_id, NULL,NULL,NULL from consumable_items ci
        // left join consumable_categories as cc on ci.category_id=cc.id 
        //  ");


         DB::statement(" create view consumpation_price_view as
         select  ccd.*, 
         case when icd.unit_price IS NULL then ci.unit_price else icd.unit_price end as unit_price 
         from consumable_consumpation_details as ccd
         left join  (select consumable_id, unit_price from invoice_consumable_detail order by id desc) as icd on icd.consumable_id=ccd.consumable_id
         left join consumable_items as ci on ci.id=ccd.consumable_id 
         group by ccd.consumable_id   
         ");


        DB::statement("
        create view consumable_stock_view as
        select ci.id, ci.name->>'$.en' as consumable_name , ci.spec, ci.add_spec, ci.weight, ci.opening_stock as qty, ci.unit_price, 
        (ci.opening_stock*ci.unit_price) as stock_value ,cc.name->>'$.en' as category ,
         u.alias, ci.category_id, ci.created_at, NULL as consumed_percentage, NULL as consumed_qty, 'consumable_itme' as type    
        from consumable_items as ci left join units as u on ci.opening_stock_unit=u.id 
        left join consumable_categories as cc on ci.category_id=cc.id 
        union all
        select icd.consumable_id,ci.name->>'$.en', ci.spec,ci.add_spec,ci.weight,icd.qty, 
        icd.unit_price, (icd.qty*icd.unit_price) as stock_value, cc.name->>'$.en', 
        u.alias, ci.category_id, icd.created_at, NULL as consumed_percentage, NULL as consumed_qty, 'invoice_consumable' as type   
        from invoice_consumable_detail as icd left join consumable_items ci on icd.consumable_id=ci.id 
        left join units as u on ci.opening_stock_unit=u.id 
        left join consumable_categories as cc on ci.category_id=cc.id 
        union all
        (select ccd.consumable_id,ci.name->>'$.en', ci.spec,ci.add_spec,ci.weight,-(ccd.qty) as qty, ccd.unit_price, (qty*ccd.unit_price) as stock_value, 
        cc.name->>'$.en', u.alias, ci.category_id, ccd.created_at,ccd.consumed_percentage as consumed_percentage,((ccd.qty*ccd.consumed_percentage)/100) as consumed_qty, 'consumpation' as type    
        from consumpation_price_view as ccd left join consumable_items ci on ccd.consumable_id=ci.id 
        left join units as u on ci.opening_stock_unit=u.id 
        left join consumable_categories as cc on ci.category_id=cc.id 
        left join consumable_consumpations ccn on ccd.consumpation_id = ccn.id order by ccd.id desc)
      ");

    //   DB::statement("
    //   create view consumable_stock_tmp_view as
    //   select id, consumable_name , spec, add_spec, weight, sum(qty) as qty, sum(consumed_qty) as consumed_qty,  
    //   NULL as stock_price, NULL as op_stock_price ,
    //   category , alias , category_id 
    //   from consumable_stock_view csv group by id
    //   union 
    //   (select icd.id, NULL, NULL, NULL, NULL, NULL, NULL ,icd.unit_price , NULL ,NULL, NULL, NULL from invoice_consumable_detail icd)
    //   union
    //   (select ci.id, NULL, NULL, NULL, NULL, NULL, NULL  , ci.unit_price ,NULL, NULL, NULL, NULL from consumable_items ci)
    // ");

    // DB::statement("
    //   create view consumable_stock_sum_view as
    //   select id, consumable_name , spec, add_spec, weight, sum(qty) as qty, sum(consumed_qty) as consumed_qty,  
    //   max(stock_price) as current_price, max(op_stock_price) as op_stock_price,
    //   category , alias , category_id, count(distinct category_id) as product_count  
    //   from consumable_stock_tmp_view  group by id
    // ");



    }

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