<?php

namespace App\Controllers;

use App\Models\InputsModel;
use App\Entities\Input;

use App\Models\InputTypesModel;
use App\Models\SuppliersModel;
use App\Models\CountriesModel;
use App\Models\ProcessesModel;
use App\Models\CertificationsModel;

use App\Controllers\InputCosts;

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

class Inputs extends BaseController
{
    private InputsModel $model;
    private InputTypesModel $inputtypesmodel;
    private SuppliersModel $suppliersmodel;
    private CountriesModel $countriesmodel;
    private ProcessesModel $processesmodel;
    private CertificationsModel $certificationsmodel;

    public function __construct()
    {
        $this->model = new InputsModel;
        $this->inputtypesmodel = new InputTypesModel;
        $this->suppliersmodel = new SuppliersModel;
        $this->countriesmodel = new CountriesModel;
        $this->processesmodel = new ProcessesModel;
        $this->certificationsmodel = new CertificationsModel;
    }

    public function index(): string
    {

        $data = $this->getAll(); //  model->orderBy('InputName','asc')->findAll();

        return view("Inputs/index",[
                "inputs" => $data,
            ]);
    }

    public function show($id)
    {

        $input = $this->getInput($id);

		$sql = "SELECT recipes.RecipeID, recipes.RecipeName FROM recipes LEFT JOIN recipeinputs on recipes.RecipeID=recipeinputs.RecipeID WHERE recipeinputs.InputID=$id";
		$db = db_connect();
		$recipes = $db->query($sql)->getResult('App\Entities\Input');

        return view("Inputs/show",[
            "input" => $input,
            "recipes" => $recipes,
        ]);

    }

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

        $input = new Input;
        $input->ConversionFactor = 1;
        $input->WastePercent = 20;
        return view("Inputs/new",[
            "input" => $input,
            "inputtypes" => $this->getInputTypes(),
            "suppliers" => $this->getSuppliers(),
            "countries" => $this->getCountries(),
            "processes" => $this->getProcesses(),
            "certifications" => $this->getCertifications()
        ]);
    }

    public function create()
    {

        $input = new Input($this->request->getPost());
        $input->CostUpdated = date_format(date_create(),'Y-m-d H:i:s');

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

        // Do not need to update recipes here because its a new input and so cannot have been used anywhere

        if($id === false){

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

        }

        return redirect()->to("inputs/$id")->with("message","Input 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" );
		}

        // $prev_url = previous_url();

        $input = $this->getInputOr404($id);

        return view("Inputs/edit",[
            "input" => $input,
            "inputtypes" => $this->getInputTypes(),
            "suppliers" => $this->getSuppliers(),
            "countries" => $this->getCountries(),
            "processes" => $this->getProcesses(),
            "certifications" => $this->getCertifications(),

        ]);

    }


    public function update($id)
    {

        //$model = new InputsModel;

        //$data = $this->request->getPost();
        //dd($input);

        //$model->save($this->request->getPost());

        $input = $this->getInputOr404($id);

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

        if($input->hasChanged('Cost')){
            $input->CostUpdated = date_format(date_create(),'Y-m-d H:i:s');
        }

        $input->__unset("_method");

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

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

            // Update SellPrice ValidFrom etc. for all affected recipes
            $inputcosts = new InputCosts;
            $inputcosts->updateRecipes($id);

            return redirect()->to("inputs/$id")->with("message","Input updated");
        }

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

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

        $input = $this->getInputOr404($id);

        return view("Inputs/delete",[
            "input"=>$input
        ]);
    }

    public function delete($id)
    {
        // Should not be able to delete input if used in any recipes
        try{
            $this->model->delete($id);

            // But just in case have managed to delete input with existing recipes
            $inputcosts = new InputCosts;
            $inputcosts->updateRecipes($id);

        } catch (DatabaseException $e) {
            return redirect()->back()->with("errors",$this->model->errors())->withInput();
        }
        return redirect()->to("inputs")->with("message","Input deleted.");

    }

    private function getAll(): array
    {

    $inputs = $this->model->query("SELECT DISTINCT inputs.InputID, inputs.InputName, inputs.InputCode,
    inputs.SupplierID, inputs.CountryID, inputs.ProcessID,
    inputs.CertificationID,
    ifnull(inputs.Cost,0.0) as Cost,
    ifnull(inputs.NextCost,0.0) as NextCost,
    ifnull(inputs.ValidFromDate,'') as ValidFromDate,
    ifnull(inputs.NextValidFromDate,'') as NextValidFromDate,
    inputs.CostPack, inputs.lastupdated, inputs.StockQty,
    inputs.LeftOnContract, inputs.InputTypeID, inputs.Notes,
    inputs.ConversionFactor,
    inputs.WastePercent,
	suppliers.Supplier,
	countries.Country,
	processes.Process,
	certifications.Certification,
    inputtypes.InputType,
    users.username
	FROM inputs
    LEFT JOIN (Select InputID, Cost from inputcosts )
    inputcosts on inputs.InputID=inputcosts.InputID
    LEFT JOIN inputtypes on inputs.InputTypeID=inputtypes.InputTypeID
	LEFT JOIN suppliers ON inputs.SupplierID=suppliers.SupplierID
	LEFT JOIN countries ON inputs.CountryID=countries.CountryID
	LEFT JOIN processes ON inputs.ProcessID=processes.ProcessID
	LEFT JOIN certifications ON inputs.CertificationID=certifications.CertificationID
    LEFT JOIN users on inputs.users_id=users.id
    ORDER BY inputs.InputName ASC")->getResult();

    return $inputs;

    }

    private function getIndex(): array
    {

    $inputs = $this->model->query("SELECT inputs.InputID,
     inputs.InputName, inputs.InputCode, inputs.SupplierID,
     inputs.CountryID, inputs.ProcessID,
     inputs.CertificationID,
     ifnull(c.Cost, inputs.Cost) as Cost,
     ifnull(c.lastupdated,'')  as CostUpdated,
     ifnull(c.ValidFromDate,'') as ValidFromDate,
     inputs.CostPack, inputs.lastupdated, inputs.StockQty,
     inputs.LeftOnContract, inputs.InputTypeID, inputs.Notes,
     inputs.ConversionFactor,
     inputs.WastePercent,
     suppliers.Supplier,
     countries.Country,
     processes.Process,
     certifications.Certification,
     inputtypes.InputType
     FROM inputs
     LEFT JOIN inputtypes on inputs.InputTypeID=inputtypes.InputTypeID
     LEFT JOIN certifications on inputs.CertificationID=certifications.CertificationID
     LEFT JOIN suppliers ON inputs.SupplierID=suppliers.SupplierID
     LEFT JOIN countries ON inputs.CountryID=countries.CountryID
     LEFT JOIN processes ON inputs.ProcessID=processes.ProcessID
     LEFT JOIN (
         SELECT inputcosts.InputID ,inputcosts.Cost, inputcosts.ValidFromDate, inputcosts.lastupdated
         FROM inputcosts
         LEFT JOIN (
         SELECT t.InputID, MAX(t.ValidFromDate) AS ValidFromDate FROM (
         SELECT inputcosts.InputCostID, inputcosts.InputID ,inputcosts.Cost, inputcosts.ValidFromDate
         FROM inputcosts
         AND CURDATE() >= inputcosts.ValidFromDate
         ORDER BY inputcosts.ValidFromDate ASC
         ) t
         GROUP BY t.InputID
         ) d ON inputcosts.InputID=d.InputID
         AND inputcosts.InputID=d.InputID AND inputcosts.ValidFromDate=d.ValidFromDate
     ) c on inputs.InputID = c.InputID
     ORDER By inputtypes.DisplayOrder, inputs.InputName",)->getResult(\App\Entities\Input::class);

    return $inputs;

    }


    private function getInput($id): Input
    {
        $inputs = $this->model->query("SELECT inputs.InputID,
     inputs.InputName, inputs.InputCode, inputs.SupplierID,
     inputs.CountryID, inputs.ProcessID,
     inputs.CertificationID,
     ifnull(c.Cost, inputs.Cost) as Cost,
     ifnull(c.lastupdated,'')  as CostUpdated,
     ifnull(c.ValidFromDate,'') as ValidFromDate,
     inputs.CostPack, inputs.lastupdated, inputs.StockQty,
     inputs.LeftOnContract, inputs.InputTypeID, inputs.Notes,
     inputs.ConversionFactor,
     inputs.WastePercent,
     suppliers.Supplier,
     countries.Country,
     processes.Process,
     certifications.Certification,
     inputtypes.InputType
     FROM inputs
     LEFT JOIN inputtypes on inputs.InputTypeID=inputtypes.InputTypeID
     LEFT JOIN certifications on inputs.CertificationID=certifications.CertificationID
     LEFT JOIN suppliers ON inputs.SupplierID=suppliers.SupplierID
     LEFT JOIN countries ON inputs.CountryID=countries.CountryID
     LEFT JOIN processes ON inputs.ProcessID=processes.ProcessID
     LEFT JOIN (
         SELECT inputcosts.InputID ,inputcosts.Cost, inputcosts.ValidFromDate, inputcosts.lastupdated
         FROM inputcosts
         LEFT JOIN (
         SELECT t.InputID, MAX(t.ValidFromDate) AS ValidFromDate FROM (
         SELECT inputcosts.InputCostID, inputcosts.InputID ,inputcosts.Cost, inputcosts.ValidFromDate
         FROM inputcosts
         WHERE inputcosts.InputID=?
         AND CURDATE() >= inputcosts.ValidFromDate
         ORDER BY inputcosts.ValidFromDate ASC
         ) t
         GROUP BY t.InputID
         ) d ON inputcosts.InputID=d.InputID
         WHERE inputcosts.InputID=?
         AND inputcosts.InputID=d.InputID AND inputcosts.ValidFromDate=d.ValidFromDate
     ) c on inputs.InputID = c.InputID
     WHERE inputs.InputID=?
     ORDER By inputtypes.DisplayOrder, inputs.InputName", [$id, $id, $id])->getResult(\App\Entities\Input::class);


        if($inputs === null || count($inputs) === 0){
            throw new PageNotFoundException("Input with id $id not found");
        }

        $input = $inputs[0];

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

        return $input;
    }


    private function getInputOr404($id): Input
    {

    $inputs = $this->model->query("SELECT inputs.InputID,
    inputs.InputName, inputs.InputCode, inputs.SupplierID,
    inputs.CountryID, inputs.ProcessID,
    inputs.CertificationID, inputs.Cost, inputs.CostUpdated,
    inputs.CostPack, inputs.lastupdated, inputs.StockQty,
    inputs.LeftOnContract, inputs.InputTypeID, inputs.Notes,
    inputs.ConversionFactor,
    inputs.WastePercent,
	suppliers.Supplier,
	countries.Country,
	processes.Process,
	certifications.Certification,
    inputtypes.InputType
	FROM inputs
    LEFT JOIN inputtypes on inputs.InputTypeID=inputtypes.InputTypeID
	LEFT JOIN suppliers ON inputs.SupplierID=suppliers.SupplierID
	LEFT JOIN countries ON inputs.CountryID=countries.CountryID
	LEFT JOIN processes ON inputs.ProcessID=processes.ProcessID
	LEFT JOIN certifications ON inputs.CertificationID=certifications.CertificationID
    WHERE inputs.InputID=?",[$id])->getResult(\App\Entities\Input::class);

        if($inputs === null || count($inputs) === 0){
            throw new PageNotFoundException("Input with id $id not found");
        }

        $input = $inputs[0];

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

        return $input;
    }

    private function getInputTypes(): array
    {
        $inputTypes  = $this->inputtypesmodel->orderBy('InputType','asc')->findAll();
        return $inputTypes;
    }

    private function getSuppliers(): array
    {
        $suppliers  = $this->suppliersmodel->orderBy('Supplier','asc')->findAll();
        return $suppliers;
    }
    private function getCountries(): array
    {
        $countries  = $this->countriesmodel->orderBy('Country','asc')->findAll();
        return $countries;
    }
    private function getProcesses(): array
    {
        $processes  = $this->processesmodel->orderBy('Process','asc')->findAll();
        return $processes;
    }
    private function getCertifications(): array
    {
        $certifications  = $this->certificationsmodel->orderBy('Certification','asc')->findAll();
        return $certifications;
    }

}
