<?php

namespace App\Controllers;

use App\Models\InputCostsModel;
use App\Entities\InputCost;
use App\Models\InputsModel;
use App\Entities\Input;
use App\Models\RecipesModel;
use App\Entities\Recipe;

use App\Controllers\Recipes;
use App\Models\SysVarsModel;
use App\Entities\SysVar;


use CodeIgniter\Exceptions\PageNotFoundException;
use CodeIgniter\Database\Exceptions\DatabaseException;

class InputCosts extends BaseController
{
    private InputCostsModel $model;
    private InputsModel $inputsmodel;
    private RecipesModel $recipesmodel;
    private bool $donotdropprices;
    private SysVarsModel $sysvarsmodel;

    public function __construct()
    {
        $this->model = new InputCostsModel;
        $this->inputsmodel = new InputsModel;
        $this->recipesmodel = new RecipesModel;
        $this->sysvarsmodel = new SysVarsModel;

        $data = $this->sysvarsmodel->select('ValueText')->where('KeyName','donotdropprices')->first();
        if(null == $data || empty($data)){
            $this->defaultgp = "60";
            $sysvar = new SysVar;
            $sysvar->KeyName = 'donotdropprices';
            $sysvar->ValueText = '1';
            $this->sysvarsmodel->insert($sysvar);
        }else{
           $this->donotdropprices = $data->ValueText == '1' ? true : false;
        }
    }

    public function index($id): string
    {

        //$prev_url = previous_url();

        $data = $this->model
            ->select(("inputcosts.*, users.username"))
            ->join("users", "users.id = inputcosts.users_id", "left")
            ->where('InputID', $id)
            ->orderBy('ValidFromDate', 'ASC')
            ->findAll();


        if (count($data) > 0) {
            $input = $this->inputsmodel->find($data[0]->InputID);
        } else {
            $input = new InputsModel;
        }

        $csrf = array();
        $csrf['token'] = csrf_hash();

        //dd($this->model->getLastQuery());

        return view("InputCosts/index", [
            "inputcosts" => $data,
            "inputid" => $id,
            "csrf" => $csrf,
            "input" => $input,
        ]);
    }



    public function peekList(): string
    {

        $request = $this->request->getPost();
        $id = $request['inputid'];

        $data = $this->model
            ->select(("inputcosts.*, users.username"))
            ->join("users", "users.id = inputcosts.users_id", "left")
            ->where('InputID', $id)
            ->orderBy('ValidFromDate', 'ASC')
            ->findAll();


        $input = $this->inputsmodel->find($id);

        $ret = array();
        $ret['token'] = csrf_hash();
        $ret['view'] = view("InputCosts/peeklist", [
            "inputcosts" => $data,
            "inputid" => $id,
            "input" => $input,
        ]);
        return  json_encode($ret);
        // return view("InputCosts/index",[
        //         "inputcosts" => $data,
        //         "inputid" => $id,
        //         "token" => csrf_hash(),
        //     ]);
    }


    public function test(): string
    {
        return view("InputCosts/test");
    }

    //     public function show($id)
    //     {
    //         $sql = "SELECT recipes.RecipeID, recipes.RecipeName FROM recipes WHERE recipes.CategoryID=$id";
    //         $db = db_connect();
    //         $recipes = $db->query($sql)->getResult('App\Entities\Input');

    //         $category = $this->getCategoryOr404($id);
    //         return view("Categories/show",[
    //             "category" => $category,
    //             "recipes" => $recipes
    //         ]);

    //     }

    public function new($id)
    {
        if (! auth()->user()->can_edit_by_controller(substr(strrchr(__CLASS__, '\\'), 1))) {
            return redirect()->to(strtolower(substr(strrchr(__CLASS__, '\\'), 1)));
        }

        $inputcost = new InputCost;
        $inputcost->InputID = $id;
        $inputcost->Cost = 0.0;

        return view("InputCosts/new", [
            "inputcost" => $inputcost
        ]);
    }

    public function create()
    {

        $inputcost = new InputCost($this->request->getPost());

        $inputid = $inputcost->InputID;

        if (! null == $this->model->where('InputID', $inputid)->where('ValidFromDate', $inputcost->ValidFromDate)->first()) {
            return redirect()->back()->with("errors", ['There is already an Input Cost with this Valid From Date'])->withInput();
        }

        $id = $this->model->insert($inputcost);

        if ($id === false) {

            return redirect()->back()->with("errors", $this->model->errors())->withInput();
        }

        $this->updateInput($inputid);

        return redirect()->to("InputCosts/$inputid")->with("message", "New Price saved.");
    }

    public function edit($id)
    {

        if (! auth()->user()->can_edit_by_controller(substr(strrchr(__CLASS__, '\\'), 1))) {
            return redirect()->to(strtolower(substr(strrchr(__CLASS__, '\\'), 1)) . "/$id");
        }

        $inputcost = $this->getInputCostOr404($id);
        $inputid = $inputcost->InputID;

        return view("InputCosts/edit", [
            "inputcost" => $inputcost,
            "inputid" => $inputid,
        ]);
    }

    public function update($inputcostid)
    {

        $inputcost = $this->getInputCostOr404($inputcostid);

        $inputcost->fill($this->request->getPost());

        $inputcost->__unset("_method");

        $inputid = $inputcost->InputID;

        // Required for Validation to work
        $inputcost->id = $inputid;


        if (!$inputcost->hasChanged()) {
            return redirect()->back()->with("message", "Nothing to update.");
        }

        if (! null == $this->model->query("select InputCostID from inputcosts where InputCostID <> ? and InputID = ? and ValidFromDate = ?", [$inputcostid, $inputid, $inputcost->ValidFromDate])->getRow()) {
            return redirect()->back()->with("errors", ['There is already an Input Cost with this Valid From Date'])->withInput();
        }

        if ($this->model->save($inputcost)) {

            $this->updateInput($inputid);

            return redirect()->to("InputCosts/$inputid")->with("message", "Price updated");
        }

        return redirect()->back()->with("errors", $this->model->errors())->withInput();
    }

    public function delete()
    {
        $request = $this->request->getPost();
        $inputcostid = $request['InputCostID'];
        $data = array();
        $data['token'] = csrf_hash();

        if ($this->model->delete($inputcostid)) {
            $data['success'] = 1;
        } else {
            $data['success'] = 0;
        }
        return $this->response->setJSON($data);
    }

    private function getInputCostOr404($id): InputCost
    {
        $data = $this->model->find($id);

        if ($data === null) {
            throw new PageNotFoundException("InputCost with id $id not found");
        }

        return $data;
    }


    public function updateRecipes($inputid)
    {
        //         $errors = array();
        // $recp = array();
        //         $recipescontroller = new Recipes;

        //         $sql = "SELECT distinct RecipeID
        //         FROM recipeinputs
        //         WHERE InputID=?";
        //         $db = db_connect();
        //         $recipes = $db->query($sql,[$inputid])->getResult();
        //         foreach($recipes as $recipe){
        //             $recipeid = $recipe->RecipeID;
        //             $recp[] = $recipe->RecipeID;
        //             $error = $recipescontroller->updateSellPrices($recipeid);

        //             if($error){
        //                 $errors[] = $error;
        //             }

        // $sql = "UPDATE recipes
        // LEFT JOIN (

        // SELECT recipes.RecipeID,
        // recipes.SellPriceKg,
        // d.ValidFromDate,
        // recipes.NextSellPriceKg,
        // dn.ValidFromDate as NextValidFromDate,
        // recipes.SellPriceGP,
        // recipes.NextSellPriceGP
        // FROM recipes
        // LEFT JOIN categories on recipes.CategoryID=categories.CategoryID
        // LEFT JOIN roastlevels on recipes.RoastLevelID=roastlevels.RoastLevelID
        // LEFT JOIN roastcolours on recipes.RoastColourID = roastcolours.RoastColourID
        // LEFT JOIN (
        //    SELECT  recipeinputs.RecipeID,
        //    sum(round((ifnull(ic.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) as TotalCost,
        //    sum(round((ifnull(icn.Cost,inputs.Cost) * recipeinputs.Qty)/ifnull(inputs.ConversionFactor,1),2)) as NextTotalCost
        //    FROM recipeinputs
        //    LEFT JOIN inputs on recipeinputs.InputID=inputs.InputID
        //    LEFT JOIN (
        //    SELECT InputID ,Cost, ValidFromDate
        //    FROM inputcosts
        //    WHERE ValidFromDate >=
        //     (
        //    SELECT t.ValidFromDate
        //    FROM (
        //    SELECT DISTINCT recipeinputs.RecipeID, inputcosts.ValidFromDate
        //    FROM inputcosts
        //    LEFT JOIN recipeinputs on inputcosts.InputID=recipeinputs.InputID
        //    WHERE recipeinputs.RecipeID=?
        //    AND CURDATE() > inputcosts.ValidFromDate
        //    ORDER BY inputcosts.ValidFromDate DESC
        //    LIMIT 2
        //    ) t
        //    ORDER BY t.ValidFromDate ASC
        //    LIMIT 1
        //    )
        //    ORDER BY ValidFromDate ASC
        //    LIMIT 1
        //     ) ic ON inputs.InputID=ic.InputID
        //    LEFT JOIN (
        //    SELECT InputID ,Cost, ValidFromDate
        //    FROM inputcosts
        //    WHERE ValidFromDate >=
        //     (
        //    SELECT t.ValidFromDate
        //    FROM (
        //    SELECT DISTINCT recipeinputs.RecipeID, inputcosts.ValidFromDate
        //    FROM inputcosts
        //    LEFT JOIN recipeinputs on inputcosts.InputID=recipeinputs.InputID
        //    WHERE recipeinputs.RecipeID=?
        //    AND CURDATE() > inputcosts.ValidFromDate
        //    ORDER BY inputcosts.ValidFromDate DESC
        //    LIMIT 2
        //    ) t
        //    ORDER BY t.ValidFromDate DESC
        //    LIMIT 1
        //    )
        //    ORDER BY ValidFromDate ASC
        //    LIMIT 1
        //     ) icn ON inputs.InputID=icn.InputID
        //    GROUP BY recipeinputs.RecipeID
        // ) c on recipes.RecipeID = c.RecipeID
        // LEFT JOIN (
        //    SELECT t.RecipeID, t.ValidFromDate
        //    FROM (
        //    SELECT DISTINCT recipeinputs.RecipeID, inputcosts.ValidFromDate
        //    FROM inputcosts
        //    LEFT JOIN recipeinputs on inputcosts.InputID=recipeinputs.InputID
        //    WHERE recipeinputs.RecipeID=?
        //    AND CURDATE() > inputcosts.ValidFromDate
        //    ORDER BY inputcosts.ValidFromDate DESC
        //    LIMIT 2
        //    ) t
        //    ORDER BY t.ValidFromDate ASC
        //    LIMIT 1
        // ) d on recipes.RecipeID = d.RecipeID
        // LEFT JOIN (
        //    SELECT t.RecipeID, t.ValidFromDate
        //    FROM (
        //    SELECT DISTINCT recipeinputs.RecipeID, inputcosts.ValidFromDate
        //    FROM inputcosts
        //    LEFT JOIN recipeinputs on inputcosts.InputID=recipeinputs.InputID
        //    WHERE recipeinputs.RecipeID=?
        //    AND CURDATE() > inputcosts.ValidFromDate
        //    ORDER BY inputcosts.ValidFromDate DESC
        //    LIMIT 2
        //    ) t
        //    ORDER BY t.ValidFromDate DESC
        //    LIMIT 1
        // ) dn on recipes.RecipeID = dn.RecipeID
        // ) r ON recipes.RecipeID = r.RecipeID

        // SET
        // recipes.SellPriceKg=r.SellPriceKg,
        // recipes.ValidFromDate=r.ValidFromDate,
        // recipes.NextSellPriceKg=r.NextSellPriceKg,
        // recipes.NextValidFromDate=r.NextValidFromDate,
        // recipes.SellPriceGp=r.SellPriceGp,
        // recipes.NextSellPriceGP=r.NextSellPriceGP

        // WHERE recipes.RecipeID = ?";

        // if(!$db->query($sql,[$recipeid, $recipeid, $recipeid, $recipeid, $recipeid])){
        //     $errors[$inputid] = $db->error();
        // }
        // }
        //         $db->close();

        // dd($recp, $errors);

        //         return $errors;
    }

    public function htmxInputValidFromDates()
    {
        $request = $this->request->getPost();
        $recipeid = $request['recipeid'];
        $validfromdate  = $request['validfromdate'];
        $nextvalidfromdate  = $request['nextvalidfromdate'];

        // $sql = "SELECT inputcosts.InputID, inputs.InputName, inputcosts.Cost, inputcosts.ValidFromDate
        // FROM inputcosts
        // LEFT JOIN (
        // SELECT t.InputID, MAX(t.ValidFromDate) AS ValidFromDate FROM (
        // SELECT inputcosts.InputCostID, inputcosts.InputID ,inputcosts.Cost, inputcosts.ValidFromDate
        // FROM inputcosts
        // LEFT JOIN recipeinputs on inputcosts.InputID=recipeinputs.InputID
        // WHERE recipeinputs.RecipeID=?
        // AND CURDATE() >= inputcosts.ValidFromDate
        // ORDER BY inputcosts.ValidFromDate ASC
        // ) t
        // GROUP BY t.InputID
        // ) d ON inputcosts.InputID=d.InputID
        // LEFT JOIN recipeinputs on inputcosts.InputID=recipeinputs.InputID
        // LEFT JOIN inputs ON recipeinputs.InputID=inputs.InputID
        // WHERE recipeinputs.RecipeID=?
        // AND inputcosts.InputID=d.InputID AND inputcosts.ValidFromDate=d.ValidFromDate";

        $sql = "SELECT inputcosts.InputID, inputs.InputName, inputcosts.Cost, inputcosts.ValidFromDate
        FROM inputcosts
        LEFT JOIN recipeinputs on inputcosts.InputID=recipeinputs.InputID
        LEFT JOIN inputs ON recipeinputs.InputID=inputs.InputID
        WHERE recipeinputs.RecipeID=?
        ORDER BY InputName, ValidFromDate";

        $db = db_connect();
        // $data = $db->query($sql, [$recipeid, $recipeid])->getResult();
        $data = $db->query($sql, [$recipeid])->getResult();
        $db->close();

        $recipe = $this->recipesmodel->find($recipeid);

        $ret = array();
        $ret['token'] = csrf_hash();
        $ret['view'] = view("InputCosts/htmxInputValidFromDates", [
            "inputcosts" => $data,
            "recipeid" => $recipeid,
            "recipe" => $recipe,
            "validfromdate" => $validfromdate,
            "nextvalidfromdate" => $nextvalidfromdate,
        ]);
        return  json_encode($ret);
    }

    function updateInput($inputid)
    {
        $sql = "SELECT DISTINCT inputcosts.InputID, inputcosts.Cost, inputcosts.ValidFromDate
FROM inputcosts
WHERE inputcosts.InputID=?
AND (NOW() >= inputcosts.ValidFromDate
OR (
SELECT DISTINCT inputcosts.ValidFromDate
FROM inputcosts
WHERE inputcosts.InputID=?
AND NOW() >= inputcosts.ValidFromDate
ORDER BY inputcosts.ValidFromDate DESC
LIMIT 1
)
)
ORDER BY inputcosts.ValidFromDate DESC
LIMIT 2";

        $data = $this->model->query($sql, [$inputid, $inputid])->getResult();
        if ($data == false) {
            $rowcount = 0;
        } else {
            $rowcount = count($data);
        }

        if ($this->donotdropprices){
            // If the new Next Cost we are about to apply is less than the current next cost do not drop, use existing next cost
            // Find the current Cost and Next Cost for this Input
            $sql = "SELECT NextCost, NextValidFromDate FROM inputs Where InputID=?";
            $currentcosts = $this->model->query($sql, [$inputid])->getRow();
            if ($currentcosts == false) {
                // Error??
                $nextcost = 0.0;
                $nextvalidfromdate = '';
            } else {
                $nextcost = $currentcosts->NextCost;
                $nextvalidfromdate = $currentcosts->NextValidFromDate;
            }
            // if the next cost we are about to apply is less than the exting next cost use the existing next cost
            if ($data[0]->Cost < $nextcost){
                $newnextcost = $nextcost;
                $newnextvalidfromdate = $nextvalidfromdate;
            }else{
                $newnextcost = $data[0]->Cost;
                $newnextvalidfromdate = $data[0]->ValidFromDate;
            }

        }else{
            $newnextcost = $data[0]->Cost;
            $newnextvalidfromdate = $data[0]->ValidFromDate;
        }

        //dd( [$this->donotdropprices, $nextcost, $data[0]->Cost, $newnextcost] );

        switch ($rowcount) {
            case 2:
                // Returned 2 rows
                $sql = "UPDATE inputs SET Cost=?, ValidFromDate=?, NextCost=?, NextValidFromDate=? Where InputID=?";
                if (! $this->inputsmodel->query($sql, [$data[1]->Cost, $data[1]->ValidFromDate, $newnextcost, $newnextvalidfromdate, $inputid])) {
                    return $this->model->errors();
                }
                break;
            case 1:
                // returned only 1 row
                $sql = "UPDATE inputs SET Cost=?, ValidFromDate=?, NextCost=NULL, NextValidFromDate=NULL Where InputID=?";
                if (! $this->inputsmodel->query($sql, [$data[0]->Cost, $data[0]->ValidFromDate, $inputid])) {
                    return $this->model->errors();
                }
                break;
            case 0:
                // returned no rows
                $sql = "UPDATE inputs SET Cost=NULL, ValidFromDate=NULL, NextCost=NULL, NextValidFromDate=NULL Where InputID=?";
                if (! $this->inputsmodel->query($sql, [$inputid])) {
                    return $this->model->errors();
                }
                break;
        }
    }
}
