<?php

namespace App\Http\Controllers;

use DB;
use Exception;
use App\Models\Company;
use App\Models\Periode;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Facades\Auth;
use DateTime;
use DateTimeZone;
use Carbon\Carbon;

class Module extends Controller
{
    public static function defaultData($data,$request,$tblName,$nobukti) {
        $company = Company::first();
        if(Schema::hasColumn($data->getTable(), 'document_date')){
            $date = Carbon::parse($request['document_date']);
            $data->document_date = $date->format("Y-m-d");
        }
        if(Schema::hasColumn($data->getTable(), 'created_by')) $data->created_by = Auth::user()->id;
        if(Schema::hasColumn($data->getTable(), 'updated_by')) $data->updated_by = Auth::user()->id;
        //if(Schema::hasColumn($data->getTable(), 'department_code')) $data->department_code = $user->department;
        if(Schema::hasColumn($data->getTable(), 'company_code')) $data->company_code = $company->companyCode;

        //loop column jika kosong dan ada pasangannya diisi
        $allKeys = array_keys((array)$request);
        foreach(Schema::getColumnListing($tblName) as $col){
            if($col !== "id" && in_array($col,$allKeys)){
                $data->{$col} = $request[$col];
            }
        }

        //nomor bukti
        if(Schema::hasColumn($data->getTable(), $tblName.'_number')) $data->{$tblName.'_number'} = $nobukti;
        if(Schema::hasColumn($data->getTable(), substr($tblName,0,strlen($tblName)-2).'_number')) $data->{substr($tblName,0,strlen($tblName)-2).'_number'} = $nobukti;

        return $data;
    }

    public static function generateDocumentNumber($company,$department,$month,$year,$type,$isSave = 0,$extra = null) {
        $romanMonths = [
            1 => 'I', 2 => 'II', 3 => 'III', 4 => 'IV', 5 => 'V', 6 => 'VI',
            7 => 'VII', 8 => 'VIII', 9 => 'IX', 10 => 'X', 11 => 'XI', 12 => 'XII'
        ];
        $romanMonth = $romanMonths[$month];
        $cur_number = 0;
        $oldNumber = DB::table("document_number")->where("type",$type)->where("extra",$extra)->where("month",$romanMonth)->where("year",$year)
        ->where("company_code",$company)->where("department_code",$department)->lockForUpdate()->first();
        if(is_null($oldNumber)){
            $default = DB::table("document_number")->where("extra",$extra)->where("type",$type)->whereNull("month")->first();
            if(!is_null($default)){
                $ins = [
                    "type" => $type,
					"extra" => $extra,
                    "month" => $romanMonth,
                    "year" => $year,
                    "company_code" => $company,
                    "department_code" => $department,
                    "cur_number" => 1,
                    "number_lenght" => $default->number_lenght
                ];
                DB::table("document_number")->insert($ins);
                $cur_number = 1;
                $oldNumber = DB::table("document_number")->where("type",$type)->where("extra",$extra)->where("month",$romanMonth)->where("year",$year)
                ->where("company_code",$company)->where("department_code",$department)->lockForUpdate()->first();
            }else{
                return "";
            }
        }else{
            $cur_number = $oldNumber->cur_number;
        }

        if ($cur_number == 0) {
            return "";
        }else{
            if($isSave == 1){
                DB::table("document_number")->where("type",$type)->where("extra",$extra)->where("month",$romanMonth)->where("year",$year)
                ->where("company_code",$company)->where("department_code",$department)->update(["cur_number" => $cur_number + 1]);
            }
            $num = str_pad($cur_number, $oldNumber->number_lenght, '0', STR_PAD_LEFT);
            return "{$company}/{$department}/{$type}/{$romanMonth}/{$year}-{$num}{$extra}";
        }
    }

    public static function getCogs($date,$itemId,$company,$department,$warehouse = "",$skipNumber = "") {
        if ($skipNumber == ""){
            $hasil = DB::select('
    select case when x.total_qty <> 0 then x.total_cogs / x.total_qty else 0 end as "cogs",
        ROW_NUMBER() over(ORDER BY item_id, urutan, document_date,created_at, document_number)
    from (
        select document_number, item_id, document_date,
            sum(beginning_qty) beginning_qty, sum(beginning_cogs) beginning_cogs, sum(qty_in) qty_in, sum(cogs_in) cogs_in, sum(qty_out) qty_out, sum(cogs_out) cogs_out,
            sum(sum(beginning_qty) + sum(qty_in) - sum(qty_out)) over (PARTITION BY item_id order by item_id, urutan, document_date, document_number, created_at) total_qty,
            sum(sum(beginning_cogs) + sum(cogs_in) - sum(cogs_out)) over (PARTITION BY item_id order by item_id, urutan, document_date, document_number, created_at) total_cogs
            ,urutan,created_at
        from (
            select cast(null as varchar(50)) document_number, item_id,
                cast(null as date) document_date,
                sum(qty_actual * base_quantity) beginning_qty, sum(cogs) beginning_cogs, cast(0 as decimal(25,3)) qty_in, cast(0 as decimal(25,3)) cogs_in,
                cast(0 as decimal(25,3)) qty_out, cast(0 as decimal(25,3)) cogs_out,0 urutan,max(created_at) created_at
            from inventory_details
            where document_date < ? and company_code = ? and item_id = ?
            group by item_id
            -- beginning balance || sawal
            union all
            select document_number, item_id,
                document_date,
                cast(0 as decimal(25,3)) beginning_qty, cast(0 as decimal(25,3)) beginning_cogs,
                sum(case when qty_actual > 0 then qty_actual * base_quantity else 0 end) qty_in, sum(case when cogs > 0 then cogs else 0 end) cogs_in,
                sum(case when qty_actual < 0 then (qty_actual * base_quantity)*-1 else 0 end) qty_out, sum(case when cogs < 0 then cogs*-1 else 0 end) cogs_out,
                case when sum(qty_actual) > 0 then
                    case lower(max(transaction_type)) when \'system\' then 1 when \'purchase\' then 2 when \'production\' then 3 else 4 end
                else 5 end
                urutan,max(created_at) created_at
            from inventory_details
            where document_date between ? and ? and company_code = ? and item_id = ?
            group by item_id,
                document_date,
                document_number
        ) x group by item_id, document_date, document_number, urutan,created_at
    ) x order by ROW_NUMBER() over(ORDER BY item_id, urutan, document_date, created_at, document_number) desc
    limit 1',[$date,$company,$itemId,$date,$date,$company,$itemId]);
            if(count($hasil)>0){
                return $hasil[0]->cogs;
            }else{
                return 0;
            }
        }else{
            $hasil = DB::select('
    select case when x.total_qty <> 0 then x.total_cogs / x.total_qty else 0 end as "cogs",
        ROW_NUMBER() over(ORDER BY item_id, urutan, document_date,created_at, document_number)
    from (
        select document_number, item_id, document_date,
            sum(beginning_qty) beginning_qty, sum(beginning_cogs) beginning_cogs, sum(qty_in) qty_in, sum(cogs_in) cogs_in, sum(qty_out) qty_out, sum(cogs_out) cogs_out,
            sum(sum(beginning_qty) + sum(qty_in) - sum(qty_out)) over (PARTITION BY item_id order by item_id, urutan, document_date, document_number, created_at) total_qty,
            sum(sum(beginning_cogs) + sum(cogs_in) - sum(cogs_out)) over (PARTITION BY item_id order by item_id, urutan, document_date, document_number, created_at) total_cogs
            ,urutan,created_at
        from (
            select cast(null as varchar(50)) document_number, item_id,
                cast(null as date) document_date,
                sum(qty_actual * base_quantity) beginning_qty, sum(cogs) beginning_cogs, cast(0 as decimal(25,3)) qty_in, cast(0 as decimal(25,3)) cogs_in,
                cast(0 as decimal(25,3)) qty_out, cast(0 as decimal(25,3)) cogs_out,0 urutan,max(created_at) created_at
            from inventory_details
            where document_date < ? and company_code = ? and item_id = ?
            group by item_id
            -- beginning balance || sawal
            union all
            select document_number, item_id,
                document_date,
                cast(0 as decimal(25,3)) beginning_qty, cast(0 as decimal(25,3)) beginning_cogs,
                sum(case when qty_actual > 0 then qty_actual * base_quantity else 0 end) qty_in, sum(case when cogs > 0 then cogs else 0 end) cogs_in,
                sum(case when qty_actual < 0 then (qty_actual * base_quantity)*-1 else 0 end) qty_out, sum(case when cogs < 0 then cogs*-1 else 0 end) cogs_out,
                case when sum(qty_actual) > 0 then
                    case lower(max(transaction_type)) when \'system\' then 1 when \'purchase\' then 2 when \'production\' then 3 else 4 end
                else 5 end
                urutan,max(created_at) created_at
            from inventory_details
            where document_date between ? and ? and company_code = ? and item_id = ? and document_number not in (SELECT unnest(string_to_array(?, \',\')))
            group by item_id,
                document_date,
                document_number
        ) x group by item_id, document_date, document_number, urutan,created_at
    ) x order by ROW_NUMBER() over(ORDER BY item_id, urutan, document_date, created_at, document_number) desc
    limit 1',[$date,$company,$itemId,$date,$date,$company,$itemId,$skipNumber]);
            if(count($hasil)>0){
                return $hasil[0]->cogs;
            }else{
                return 0;
            }
        }
    }

    public static function getStockByDate($itemId, $unitId, $date)
    {
        $hasil = DB::table("inventory_details")->where("item_id", $itemId)
            ->where("unit", $unitId)
            ->where("document_date", "<=", $date)
            ->select(DB::raw("coalesce(sum(qty_actual),0) qty_actual"))->first();
        return $hasil->qty_actual;
    }
    
    public static function checkPeriodeBack($periods, $date)
    {
        $period = $periods->where('periode_start', '<=', $date)
                         ->where('periode_end', '>=', $date)
                         ->first();
        
        if ($period && $period->periode_active === 'closed') {
            return true;
        }
        if(!$period){
            return false;
        }
        return false;
    }

    public function checkPeriode(Request $request)
    {

        $date = $request->input('date');

        if(new DateTime($date) < new DateTime('2025-08-01')){
            return response()->json([
                'closed' => true,
                'message' => 'The period is closed for the selected date (' . $date . '). No transactions can be made in this period.',
            ]);
        }

        $period = Periode::where('periode_start', '<=', $date)
                         ->where('periode_end', '>=', $date)
                         ->first();

        if ($period && $period->periode_active === 'closed') {
            return response()->json([
                'closed' => true,
                'message' => 'The period is closed for the selected date (' . $date . '). No transactions can be made in this period.',
            ]);
        }

        return response()->json([
            'closed' => false,
        ]);
    }
    
    public function listBackup(Request $request){
        $files = Storage::disk('backup')->files();
        return ["data" => $files];
    }

    public function downloadBackup(Request $request){
        return Storage::disk('backup')->download($request->filename);
    }

    public static function getCukaiId($itemId) {
        $hasil = DB::select('select getCukaiId(?)',[$itemId])[0]->getcukaiid;
        return $hasil;
    }

    public function cekAmbri(Request $request){
        $employeeId = [];
        if(is_array($request->employeeId)){
            foreach($request->employeeId as $e){
                array_push($employeeId,$e["employee_id"]);
            }
        }else{
            array_push($employeeId,$request->employeeId);
        }

        $date = Carbon::parse($request->tgl)->format("Y-m-d");
        //->subDays()
        // $pengeluaran = DB::table("pengeluaran_bahan as x")
        // ->join("pengeluaran_bahan_detail as y","x.id","=","y.pengeluaran_bahan_id")
        // ->join("items as i","i.id","=","y.item_id")
        // ->join("item_units as iu","iu.id","=",DB::raw("y.unit::bigint"))
        // ->join("category_items as ci","ci.id","=",DB::raw("i.category::bigint"))
        // ->select("y.item_id","i.item_code","i.item_name","y.unit","iu.unit_name","y.qty as qty_out",DB::raw("cast(0 as numeric(20,3)) as qty_in"),"y.base_qty","x.employee_result")
        // ->where(DB::raw("x.employee_result::text"),"<>",'"[]"')->where("x.document_date","=",$date)
        // ->where("ci.category_item_name","like","'%Ambri%'")->get();

        $kasirpengeluaran = DB::table("kasir_pengeluaran_bahan as x")
        ->join("kasir_pengeluaran_bahan_detail as y","x.id","=","y.kasir_pengeluaran_bahan_id")
        ->join("items as i","i.id","=","y.item_id")
        ->join("item_units as iu","iu.id","=",DB::raw("y.unit::bigint"))
        ->join("category_items as ci","ci.id","=",DB::raw("i.category::bigint"))
        ->select("y.id","y.item_id","i.item_code","i.item_name","y.unit","iu.unit_name","y.qty as qty_out","y.base_qty","x.employee_result")
        ->whereIn("x.employee_result",$employeeId)->where("x.document_date","=",$date)
        ->where(DB::raw("lower(ci.category_item_name)"),"like","%ambri%")->get();
        
        $colKeluar = collect([]);
        foreach($kasirpengeluaran as $k){
            $c = $colKeluar->where("id",$k->id)->first();
            if(!$c){
                $colKeluar->push((Object)[
                    "id" => $k->id,
                    "item_code" => $k->item_code,
                    "item_name" => $k->item_name,
                    "unit_name" => $k->unit_name,
                    "qty_out" => $k->qty_out,
                    "base_qty" => $k->base_qty
                ]);
            }
        }

        // $penerimaan = DB::table("penerimaan_bahan as x")
        // ->join("penerimaan_bahan_detail as y","x.id","=","y.penerimaan_bahan_id")
        // ->join("items as i","i.id","=","y.item_id")
        // ->join("item_units as iu","iu.id","=",DB::raw("y.unit::bigint"))
        // ->join("category_items as ci","ci.id","=",DB::raw("i.category::bigint"))
        // ->select("y.item_id","i.item_code","i.item_name","y.unit","iu.unit_name",DB::raw("cast(0 as numeric(20,3)) as qty_out"),"y.qty as qty_in","y.base_qty","x.employee_result_id as employee_result")
        // ->where("x.document_date","=",$date)->where("iu.id","7")
        // ->where("ci.category_item_name","like","'%Rokok%'");

        $kasirpenerimaan = DB::table("kasir_penerimaan_bahan as x")
        ->join("kasir_penerimaan_bahan_detail as y","x.id","=","y.kasir_penerimaan_bahan_id")
        ->join("items as i","i.id","=","y.item_id")
        ->join("item_units as iu","iu.id","=",DB::raw("y.unit::bigint"))
        ->join("category_items as ci","ci.id","=",DB::raw("i.category::bigint"))
        ->select("y.id","y.item_id","i.item_code","i.item_name","y.unit","iu.unit_name","y.qty as qty_in","y.base_qty","x.employee_result")
        ->where("x.document_date","=",$date)->where("iu.id","7")
        ->where(DB::raw("lower(ci.category_item_name)"),"like","%rokok%")->get();
        $arr = [];
        $col = collect([]);
        foreach($kasirpenerimaan as $k){
            $arr = [];
            $arr[] = json_decode($k->employee_result);
            foreach($employeeId as $e){
                $ada = false;
                foreach($arr[0] as $aa){
                    if($aa->employee_id == $e){
                        $ada = true;
                        break;
                    }
                }
                if($ada){
                    $c = $col->where("id",$k->id)->first();
                    if(!$c){
                        $col->push((Object)[
                            "id" => $k->id,
                            "item_code" => $k->item_code,
                            "item_name" => $k->item_name,
                            "unit_name" => $k->unit_name,
                            "qty_in" => $k->qty_in,
                            "base_qty" => $k->base_qty
                        ]);
                    }
                }
            }
        }

        return ["in" => $col->sum("qty_in"),"out" => $colKeluar->sum("qty_out"),"sisa" => $col->sum("qty_in") - $colKeluar->sum("qty_out")];
    }
}