<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Models\InventoryDetail;
use App\Models\Journal;
use App\Http\Controllers\Module;
use App\Models\ItemDetail;
use App\Models\CategoryItem;
use DateTime;
use DateTimeZone;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Auth;

class SystemController extends Controller
{
    public function calculate(Request $request) {
        //prep data
        $startDate = $request->start_date;
        $endDate = $request->end_date;
        $awal = new DateTime($startDate);
        $awal->setTimezone(new DateTimeZone('Asia/Jakarta'));
        $akhir = new DateTime($endDate);
        $akhir->setTimezone(new DateTimeZone('Asia/Jakarta'));
        $diff = $awal->diff($akhir);
        $isLoop = $diff->days >= 0 ? true : false;
        $jT = collect([
            (Object)["transaction_type" => "Processing Penggunaan Bahan","HPP" => "Not","Persediaan" => "Credit", "WIP" => "Debet"],
            (Object)["transaction_type" => "Processsing Pengambilan Bahan","HPP" => "Not","Persediaan" => "Debet", "WIP" => "Credit"],
            (Object)["transaction_type" => "Kasir Production Penggunaan Bahan","HPP" => "Not","Persediaan" => "Credit", "WIP" => "Debet"],
            (Object)["transaction_type" => "Kasir Production Pengambilan Bahan","HPP" => "Not","Persediaan" => "Debet", "WIP" => "Credit"],
            (Object)["transaction_type" => "Production Penggunaan Bahan","HPP" => "Not","Persediaan" => "Credit", "WIP" => "Debet"],
            (Object)["transaction_type" => "Production Pengambilan Bahan","HPP" => "Not","Persediaan" => "Debet", "WIP" => "Credit"],
            (Object)["transaction_type" => "Sales Invoice","HPP" => "Debet","Persediaan" => "Credit", "WIP" => "Not"],
            (Object)["transaction_type" => "Sales Return","HPP" => "Credit","Persediaan" => "Debet", "WIP" => "Not"],
            (Object)["transaction_type" => "Purchase Return","HPP" => "Not","Persediaan" => "Credit", "WIP" => "Not"],
            (Object)["transaction_type" => "Correction Out","HPP" => "Not","Persediaan" => "Credit", "WIP" => "Not"],
            (Object)["transaction_type" => "Correction In","HPP" => "Not","Persediaan" => "Debet", "WIP" => "Not"],
            (Object)["transaction_type" => "Opname","HPP" => "Not","Persediaan" => "Not", "WIP" => "Not"]
        ]);
        $time_start = microtime(true);
        DB::beginTransaction();
        $param = DB::table("parameter")->get();
        $coa = DB::table("coa")->get();
        try{
            while($isLoop){
                $data = DB::table("inventory_details as x")
                ->join("sorting as y","x.transaction_type","=","y.transaction_type")
                ->whereBetween("x.document_date",[$awal->format("Y-m-d"),$akhir->format("Y-m-d")])
                ->whereNotIn("x.transaction_type",["Purchase Invoice","Purchase Invoice Makloon","Good Receipt Makloon","Delivery Order Makloon"])
                ->select("y.number","y.times",DB::raw("x.base_quantity as conversion"),"x.*")
                ->orderBy("y.number")->orderBy("x.document_date")->orderBy("x.document_number")
                ->orderBy("x.created_at")->distinct()->get();

                $nomorTrans = array_unique($data->pluck("document_number")->toArray());
                
                $chunk = array_chunk($data->pluck("id")->toArray(),65000,true);
                foreach ($chunk as $new_chunk){
                    DB::table("inventory_details as x")
                    ->whereIn("x.id",$new_chunk)
                    ->delete();
                }
                
                foreach($data as $dat){
                    $cogs = 0;
                    if($dat->transaction_type == "Processsing Pengambilan Bahan"){
                        //hitung COGM Processing
                        $listBahan = collect();
                        $listInputItem = DB::table("processing_penerimaan_bahan as x")
                        ->join("processing_pengeluaran_bahans as y",function ($join) {
                            $join->on("y.process_id","=","x.process_id")
                            ->on("y.plan_id","=","x.plan_id");
                        })
                        ->join("processing_pengeluaran_bahan_details as z","z.pengeluaran_bahan_id","=","y.id")
                        ->where("x.penerimaan_bahan_id",$dat->document_number)
                        ->select("z.item_id",DB::raw("sum(z.qty) qty"))->groupBy("z.item_id")->get();
                        foreach($listInputItem as $inputItem){
                            $hargaTerakhir = DB::table("inventory_details")
                            ->where("document_date","<=",$awal->format("Y-m-d"))->where("item_id",$inputItem->item_id)
                            ->where("qty_actual",">",0)->whereIn("transaction_type",['Sawal','Purchase Invoice','Purchase Invoice Makloon'])
                            ->select(DB::raw("max(document_date) as document_date"),DB::raw("cast(cogs/(quantity * base_quantity) as numeric(20,2)) as price"))
                            ->groupBy(DB::raw("cogs/(quantity * base_quantity)"))->first();
                            if($hargaTerakhir){
                                $listBahan->push(['item_id' => $inputItem->item_id,'qty' => $inputItem->qty,'price' => $hargaTerakhir->price]);
                            }else{
                                $listBahan->push(['item_id' => $inputItem->item_id,'qty' => $inputItem->qty,'price' => 0]);
                            }
                        }
                        if($listBahan->sum('price') > 0){
                            $cogs = $listBahan->sum('price');
                        }
                        $cogs = ($cogs * ($dat->quantity * $dat->conversion)) * ($dat->qty_actual >= 1 ? 1 : -1);
                    }
                    else if($dat->transaction_type == "Production Pengambilan Bahan" || $dat->transaction_type == "Kasir Production Pengambilan Bahan"){
                        //hitung COGM Production
                        $tblProduction = ($dat->transaction_type == "Production Pengambilan Bahan" ? "penerimaan_bahan as x" : "kasir_penerimaan_bahan as x");
                        $colProduction = ($dat->transaction_type == "Production Pengambilan Bahan" ? "x.penerimaan_bahan_id" : "x.kasir_penerimaan_bahan_number");
                        $penerimaan = DB::table($tblProduction)
                        ->join("pengeluaran_bahan as y",function ($join) {
                            $join->on("y.process_id","=","x.process_id")
                            ->on("y.plan_id","=","x.plan_id");
                        })
                        ->join("pengeluaran_bahan_detail as z","z.pengeluaran_bahan_id","=","y.id")
                        ->where($colProduction,$dat->document_number)
                        ->select("z.item_id","z.unit",DB::raw("sum(z.qty) qty"))->groupBy("z.item_id","z.unit");
                        $kasir = DB::table($tblProduction)
                        ->join("kasir_pengeluaran_bahan as y",function ($join) {
                            $join->on("y.process_id","=","x.process_id")
                            ->on("y.plan_id","=","x.plan_id");
                        })
                        ->join("kasir_pengeluaran_bahan_detail as z","z.kasir_pengeluaran_bahan_id","=","y.id")
                        ->where($colProduction,$dat->document_number)
                        ->select("z.item_id","z.unit",DB::raw("sum(z.qty) qty"))->groupBy("z.item_id","z.unit");

                        $listInputItem = DB::query()->select('item_id','unit',DB::raw("sum(qty) qty"))
                        ->from($penerimaan->union($kasir), 'tmp')
                        ->groupBy('item_id','unit')
                        ->get();

                        $listBahan = collect();
                        foreach($listInputItem as $inputItem){
                            $hargaTerakhir = DB::table("inventory_details")
                            ->where("document_date","<=",$awal->format("Y-m-d"))->where("item_id",$inputItem->item_id)->where("unit",$inputItem->unit)
                            ->where("qty_actual",">",0)
                            ->select(DB::raw("max(document_date) as document_date"),DB::raw("cast(cogs/(quantity * base_quantity) as numeric(20,2)) as price"))
                            ->groupBy(DB::raw("cogs/(quantity * base_quantity)"))->first();
                            if($hargaTerakhir){
                                $listBahan->push(['item_id' => $inputItem->item_id,'qty' => $inputItem->qty,'price' => $hargaTerakhir->price]);
                            }else{
                                $listBahan->push(['item_id' => $inputItem->item_id,'qty' => $inputItem->qty,'price' => 0]);
                            }
                        }
                        if($listBahan->sum('price') > 0){
                            $cogs = $listBahan->sum('price');
                        }
                        $cogs = ($cogs * ($dat->quantity * $dat->conversion)) * ($dat->qty_actual >= 1 ? 1 : -1);
                    }
                    else if($dat->transaction_type !== "Purchase Invoice" && $dat->transaction_type !== "Purchase Invoice Makloon" && $dat->transaction_type !== "Good Receipt Makloon" && $dat->transaction_type !== "Delivery Order Makloon"){
                        $cogs = DB::select('select "getCogs"(?,?,?,?,?,\'\')',[$dat->document_date,$dat->item_id,$dat->unit,$dat->conversion,$dat->company_code])[0]->getCogs;
                        $cogs = ($cogs * ($dat->quantity * $dat->conversion)) * ($dat->qty_actual >= 1 ? 1 : -1);
                    }
                    if($dat->transaction_type !== "Purchase Invoice" && $dat->transaction_type !== "Purchase Invoice Makloon" && $dat->transaction_type !== "Good Receipt Makloon" && $dat->transaction_type !== "Delivery Order Makloon"){
                        DB::table("inventory_details")->insert([
                            "document_number" => $dat->document_number,
                            "document_date" => $dat->document_date,
                            "transaction_type" => $dat->transaction_type,
                            "warehouse_id" => $dat->warehouse_id,
                            "item_id" => $dat->item_id,
                            "quantity" => $dat->quantity,
                            "unit" => $dat->unit,
                            "base_quantity" => $dat->base_quantity,
                            "unit_base" => $dat->unit_base,
                            "department_id" => $dat->department_id,
                            "company_code" => $dat->company_code,
                            "total" => $dat->total,
                            "cogs" => $cogs,
                            "qty_actual" => $dat->qty_actual,
                            "created_by" => $dat->created_by,
                            "updated_by" => $dat->updated_by,
                            "created_at" => $dat->created_at,
                            "updated_at" => $dat->updated_at,
                        ]);
                    }
                }

                //Fix Journal HPP dan persediaan
                $aa = DB::table("journal")->whereIn("document_number",$nomorTrans)
                ->groupBy("document_number")->select("document_number")
                ->get()->pluck("document_number")->toArray();
                foreach($aa as $nb){
                    $cInv = DB::table("inventory_details as x")
                    ->join("items as i","i.id","=","x.item_id")
                    ->join("category_items as ci","ci.id","=",DB::raw("i.category::bigint"))
                    ->where("x.document_number",$nb)
                    ->select("x.document_number","x.document_date","x.transaction_type","i.item_name","ci.account_inventory","ci.acc_cogs","ci.acc_number_wip","x.cogs",
                    'x.created_at','x.updated_at','x.created_by','x.updated_by','x.department_id','x.company_code')->get();
                    //delete jurnal cogs dan persediaan krn akunnya bs beda" tiap item
                    $acc_cogs = $cInv->pluck("acc_cogs")->toArray();
                    $acc_inventory = $cInv->pluck("account_inventory")->toArray();
                    $acc_wip = $cInv->pluck("acc_number_wip")->toArray();
                    Journal::where("document_number",$nb)->whereIn("account_number",$acc_cogs)->delete();
                    Journal::where("document_number",$nb)->whereIn("account_number",$acc_inventory)->delete();
                    Journal::where("document_number",$nb)->whereIn("account_number",$acc_wip)->delete();

                    foreach($cInv as $inv){
                        if($jT->where("transaction_type",$inv->transaction_type)->first()->HPP == "Debet"){
                            Journal::create([
                                'document_number' => $inv->document_number,
                                'document_date' => $inv->document_date,
                                'account_number' => $inv->acc_cogs,
                                'notes' => "HPP ".$inv->item_name,
                                'debet_nominal' => abs($inv->cogs),
                                'credit_nominal' => 0,
                                'debet_nominal_base' => abs($inv->cogs),
                                'credit_nominal_base' => 0,
                                'company_code' => $inv->company_code,
                                'department_id' => $inv->department_id,
                                'created_at' => $inv->created_at,
                                'updated_at' => $inv->updated_at,
                                'created_by' => $inv->created_by,
                                'updated_by' => $inv->updated_by,
                            ]);
                        }
                        if($jT->where("transaction_type",$inv->transaction_type)->first()->HPP == "Credit"){
                            Journal::create([
                                'document_number' => $inv->document_number,
                                'document_date' => $inv->document_date,
                                'account_number' => $inv->acc_cogs,
                                'notes' => "HPP ".$inv->item_name,
                                'debet_nominal' => 0,
                                'credit_nominal' => abs($inv->cogs),
                                'debet_nominal_base' => 0,
                                'credit_nominal_base' => abs($inv->cogs),
                                'company_code' => $inv->company_code,
                                'department_id' => $inv->department_id,
                                'created_at' => $inv->created_at,
                                'updated_at' => $inv->updated_at,
                                'created_by' => $inv->created_by,
                                'updated_by' => $inv->updated_by,
                            ]);
                        }

                        if($jT->where("transaction_type",$inv->transaction_type)->first()->Persediaan == "Debet"){
                            Journal::create([
                                'document_number' => $inv->document_number,
                                'document_date' => $inv->document_date,
                                'account_number' => $inv->account_inventory,
                                'notes' => "Inventory ".$inv->item_name,
                                'debet_nominal' => abs($inv->cogs),
                                'credit_nominal' => 0,
                                'debet_nominal_base' => abs($inv->cogs),
                                'credit_nominal_base' => 0,
                                'company_code' => $inv->company_code,
                                'department_id' => $inv->department_id,
                                'created_at' => $inv->created_at,
                                'updated_at' => $inv->updated_at,
                                'created_by' => $inv->created_by,
                                'updated_by' => $inv->updated_by,
                            ]);
                        }
                        if($jT->where("transaction_type",$inv->transaction_type)->first()->Persediaan == "Credit"){
                            Journal::create([
                                'document_number' => $inv->document_number,
                                'document_date' => $inv->document_date,
                                'account_number' => $inv->account_inventory,
                                'notes' => "Inventory ".$inv->item_name,
                                'debet_nominal' => 0,
                                'credit_nominal' => abs($inv->cogs),
                                'debet_nominal_base' => 0,
                                'credit_nominal_base' => abs($inv->cogs),
                                'company_code' => $inv->company_code,
                                'department_id' => $inv->department_id,
                                'created_at' => $inv->created_at,
                                'updated_at' => $inv->updated_at,
                                'created_by' => $inv->created_by,
                                'updated_by' => $inv->updated_by,
                            ]);
                        }

                        if($jT->where("transaction_type",$inv->transaction_type)->first()->WIP == "Debet"){
                            Journal::create([
                                'document_number' => $inv->document_number,
                                'document_date' => $inv->document_date,
                                'account_number' => $inv->acc_number_wip,
                                'notes' => "Inventory ".$inv->item_name,
                                'debet_nominal' => abs($inv->cogs),
                                'credit_nominal' => 0,
                                'debet_nominal_base' => abs($inv->cogs),
                                'credit_nominal_base' => 0,
                                'company_code' => $inv->company_code,
                                'department_id' => $inv->department_id,
                                'created_at' => $inv->created_at,
                                'updated_at' => $inv->updated_at,
                                'created_by' => $inv->created_by,
                                'updated_by' => $inv->updated_by,
                            ]);
                        }
                        if($jT->where("transaction_type",$inv->transaction_type)->first()->WIP == "Credit"){
                            Journal::create([
                                'document_number' => $inv->document_number,
                                'document_date' => $inv->document_date,
                                'account_number' => $inv->acc_number_wip,
                                'notes' => "Inventory ".$inv->item_name,
                                'debet_nominal' => 0,
                                'credit_nominal' => abs($inv->cogs),
                                'debet_nominal_base' => 0,
                                'credit_nominal_base' => abs($inv->cogs),
                                'company_code' => $inv->company_code,
                                'department_id' => $inv->department_id,
                                'created_at' => $inv->created_at,
                                'updated_at' => $inv->updated_at,
                                'created_by' => $inv->created_by,
                                'updated_by' => $inv->updated_by,
                            ]);
                        }

                        if($inv->transaction_type == "Correction Out" || $inv->transaction_type == "Correction In"){
                            $coaKoreksi = DB::table("corrections")->where("correction_number",$inv->document_number)->first()->coa_id;
                            $totKoreksi = DB::table("inventory_details")->where("document_number",$inv->document_number)->sum("cogs");
                            Journal::where("document_number",$inv->document_number)->where("account_number",$coaKoreksi)->delete();
                            Journal::create([
                                'document_number' => $inv->document_number,
                                'document_date' => $inv->document_date,
                                'account_number' => $coaKoreksi,
                                'notes' => "Correction",
                                'debet_nominal' => abs($totKoreksi),
                                'credit_nominal' => 0,
                                'debet_nominal_base' => abs($totKoreksi),
                                'credit_nominal_base' => 0,
                                'company_code' => $inv->company_code,
                                'department_id' => $inv->department_id,
                                'created_at' => $inv->created_at,
                                'updated_at' => $inv->updated_at,
                                'created_by' => $inv->created_by,
                                'updated_by' => $inv->updated_by,
                            ]);
                        }

                        if($inv->transaction_type == "Opname"){
                            Journal::create([
                                'document_number' => $inv->document_number,
                                'document_date' => $inv->document_date,
                                'account_number' => $inv->account_inventory,
                                'notes' => "Inventory ".$inv->item_name,
                                'debet_nominal' => $inv->cogs < 0 ? abs($inv->cogs) : 0,
                                'credit_nominal' => $inv->cogs > 0 ? abs($inv->cogs) : 0,
                                'debet_nominal_base' => $inv->cogs < 0 ? abs($inv->cogs) : 0,
                                'credit_nominal_base' => $inv->cogs > 0 ? abs($inv->cogs) : 0,
                                'company_code' => $inv->company_code,
                                'department_id' => $inv->department_id,
                                'created_at' => $inv->created_at,
                                'updated_at' => $inv->updated_at,
                                'created_by' => $inv->created_by,
                                'updated_by' => $inv->updated_by,
                            ]);


                            $def = $param->where("id","Acc_selisih_nilai_sediaan")->first();
                            $asd = $coa->where("account_number",$def->value)->first();
                            $totOpname = DB::table("inventory_details")->where("document_number",$inv->document_number)->sum("cogs");
                            Journal::where("document_number",$inv->document_number)->where("account_number",$asd->id)->delete();
                            Journal::create([
                                'document_number' => $inv->document_number,
                                'document_date' => $inv->document_date,
                                'account_number' => $asd->id,
                                'notes' => "Opname",
                                'debet_nominal' => $totOpname > 0 ? abs($totOpname) : 0,
                                'credit_nominal' => $totOpname < 0 ? abs($totOpname) : 0,
                                'debet_nominal_base' => $totOpname > 0 ? abs($totOpname) : 0,
                                'credit_nominal_base' => $totOpname < 0 ? abs($totOpname) : 0,
                                'company_code' => $inv->company_code,
                                'department_id' => $inv->department_id,
                                'created_at' => $inv->created_at,
                                'updated_at' => $inv->updated_at,
                                'created_by' => $inv->created_by,
                                'updated_by' => $inv->updated_by,
                            ]);
                        }
                    }
                }

                // $awal = $awal->modify('+1 day');
                // if($diff->days == 0){
                //     $isLoop = false;
                // }else{
                //     $diff = $awal->diff($akhir);
                // }

                $isLoop = false;
            }
            
            DB::commit();
            $time_end = microtime(true);
            $total_time = ($time_end-$time_start)/60;
            return response()->json(null, 201);
        }catch(\Exception $e){
            DB::rollBack();
            dd($e->getTrace());
            Log::error($e->getMessage());
            return $e->getMessage();
        }
    }
}