<?php

namespace App\Controllers;

use App\Entities\ProductionEntity;
use App\Models\ProductionModel;

use App\Entities\ProductionCertification;
use App\Models\ProductionCertificationsModel;

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

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

class Production extends BaseController
{
    private ProductionModel $model;
    private ProductionCertificationsModel $productioncertificationsmodel;

    private RecipesModel $recipesmodel;
    private InputsModel $inputsmodel;

    public function __construct()
    {
        $this->model = new ProductionModel;
        $this->recipesmodel = new RecipesModel;
        $this->inputsmodel = new InputsModel;
        $this->productioncertificationsmodel = new ProductionCertificationsModel;
    }

    public function index(): string
    {
        $sql = "select production.*,users.username from production left join users on production.users_id=users.id where ifnull(production.deleted,0)=0";
        $data = $this->model->query($sql)->getResult('App\Entities\ProductionEntity');
        return view("Production/index", [
            "productions" => $data
        ]);
    }

    // public function show($id)
    // {

    //     $inputtype = $this->getInputTypeOr404($id);

    //     return view("InputTypes/show",[
    //         "inputtype" => $inputtype
    //     ]);

    // }

    function new()
    {
        $production = new ProductionEntity;



        // $cupped_by = "<div id='cuppedbyid' style='display:grid; gap:0; grid-template-columns: 80% 5% 15%;'>
        //                 <div style='grid-column:1; grid-row:1; padding:0; margin:0;z-index:1;'>
        //                     <a id='btncuppedby' class='button v-small' style='width:-webkit-fill-available; color:white;' onclick='cuppedbyapproval();'>Click to Approve</a>
        //                 </div>
        //             </div>";

        // // $finalqc = "<div id='finalqcid' style='display:grid; gap:0; grid-template-columns: 70% 20% 10%;'>
        // //                 <div style='grid-column:1; grid-row:1; padding:0; margin:0;z-index:1;'>
        // //                     <a id='btnfinalqcapproval' class='button v-small' style='width:-webkit-fill-available; color:white;' href='#' onclick='finalqcapproval();' disabled>Click to Approve</a>
        // //                 </div>
        // //             </div>";

        // $finalqc = "<div id='finalqcid' style='display:grid; gap:0; grid-template-columns: 70% 20% 10%;'>
        //         <div style='grid-column:1; grid-row:1; padding:0; margin:0;z-index:1;'>
        //             <div id='btnfinalqcapproval' class='button v-small' style='width:-webkit-fill-available; background-color: darkgray;  color:white;' disabled>Final Approval</div>
        //         </div>
        //     </div>";


        // $packagingapproval = "<div id='packagingid' style='grid-column:1; grid-row:1; padding:0; margin:0;z-index:1;'><a id='btnpackaging_qc_by' class='button v-small' style='width:-webkit-fill-available; color:white;' href='#' onclick='packagingapproval();'>Click to Approve</a></div>";

        $operator_approval = "<div id='operator_approval_id' style='grid-column:1; grid-row:1; padding:0; margin:0;z-index:1;'>
                                <a id='operator_approval' class='button v-small' style='width:-webkit-fill-available; color:white;' href='#' onclick='set_operator_approval();'>
                                    Click to Approve
                                </a>
                            </div>";

        $teamleader_approval = "
                <div id='teamleader_approval_id' style='display:grid; gap:0; grid-template-columns: 70% 20% 10%;'>
                    <div style='grid-column:1; grid-row:1; padding:0; margin:0;z-index:1;'>
                        <div id='btn_teamleader_approval' class='button v-small' style='width:-webkit-fill-available; background-color: darkgray;  color:white;' disabled>
                            Click to Approve
                        </div>
                    </div>
                </div>";

        $signatures = array();
        // $signatures['finalqc'] = $finalqc;
        // $signatures['packagingapproval'] = $packagingapproval;
        // $signatures['cupped_by'] = $cupped_by;
        // $signatures['weight_operator'] = $weight_operator;
        // $signatures['weight_leader'] = $weight_leader;

        $signatures['operator_approval'] = $operator_approval;
        $signatures['teamleader_approval'] = $teamleader_approval;


        $production->check_date = date('Y-m-d');
        $production->checked_by = esc(auth()->user()->username);
        $recipes = $this->getRecipes();
        $coffees = $this->getCoffees();
        $certifications = $this->getInitalCertificates();
        $roastcolours = $this->getRoastColours();
        $image = array();
        $image["ImageName"] = null;
        $image["ImageID"] = null;
        $packingimages = $this->getPackingImages();
        return view("Production/new", [
            "production" => $production,
            "recipes" => $recipes,
            "coffees" => $coffees,
            "certifications" => $certifications,
            "signatures" => $signatures,
            "roastcolours" => $roastcolours,
            "image" => (object)$image,
            "packingimages" => $packingimages,
        ]);
    }

    public function create()
    {
        $production = new ProductionEntity($this->request->getPost());

        $production->users_id = esc(auth()->user()->username);

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

        if ($id === false) {
            return redirect()->back()->with("errors", $this->model->errors())->withInput();
        }

        $certifications = $this->getCertsFromRequest($id, $production);

        $this->saveCerts($certifications, $id);

        return redirect()->to("production")->with("message", "Quality Control Checklist saved.");
    }

    private function getCertsFromRequest($productionid, $production)
    {
        $productionarray = $production->toArray();
        $certifications = $this->getAllCertifications();

        // Add ProductionID to certtication
        foreach ($certifications as $cert) {
            $cert->ProductionID = $productionid;
        }

        // for each key value in POST data
        foreach ($productionarray as $k => $v) {
            //$test[] = substr($k, 0, 14);
            // Only want certifications
            if (substr($k, 0, 14) == 'certification_') {
                // Find certification in POST data
                if (array_key_exists($k, $certifications)) {
                    $certifications[$k]->CheckboxValue = $v;
                }
            }
        }
        return $certifications;
    }

    private function saveCerts($certifications, $id)
    {
        $sql = "SELECT ProductionCertificationID, CheckboxName FROM production_certifications where ProductionID=$id";
        $db = db_connect();
        $productionarray = $db->query($sql)->getResult();

        foreach ($certifications as $cert) {
            foreach ($productionarray as $prod) {
                if (strcmp($prod->CheckboxName, $cert->CheckboxName) == 0) {
                    $cert->ProductionCertificationID = $prod->ProductionCertificationID;
                    break;
                }
            }
            $this->productioncertificationsmodel->save($cert);
        }

        return true;
    }

    private function getProductionCertificationOr404($id): ProductionCertification
    {
        $row = $this->productioncertificationsmodel->find($id);

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

        return $row;
    }


    public function edit($id)
    {
        $production = $this->getProductionOr404($id);

        $disable_final_qc = true;
        if (null == $production->operator_approval || empty($production->operator_approval)) {
            if(auth()->user()->can_edit('Coffee Quality Control Checklist')){
            $operator_approval = "<div id='operator_approval_id' style='display:grid; gap:0; grid-template-columns: 80% 5% 15%;'>
                        <div style='grid-column:1; grid-row:1; padding:0; margin:0;z-index:1;'>
                            <a id='btn_operator_approval' class='button v-small' style='width:-webkit-fill-available; color:white;' onclick='set_operator_approval();'>Click to Approve</a>
                        </div>
                    </div>";
            }else{
            $operator_approval = "<div id='operator_approval_id' style='display:grid; gap:0; grid-template-columns: 80% 5% 15%;'>
                        <div style='grid-column:1; grid-row:1; padding:0; margin:0;z-index:1;'>
                            <a id='btn_operator_approval' class='button v-small' style='width:-webkit-fill-available; color:white;' disabled>Click to Approve</a>
                        </div>
                    </div>";

            }
            $disable_final_qc = true;
        } else {
            $operator_approval = "<div id='operator_approval_id' style='display:grid; gap:0; grid-template-columns: 80% 5% 15%;'>
                        <div style='grid-column:1; grid-row:1; padding:0; margin:0;'>
                            <input type='hidden' name='operator_approval' id='operator_approval'
                            value='" . esc($production->operator_approval) . "' >

                            <input type='text' name='operator_approval_display' id='operator_approval_display'
                            class='grid-column:1; grid-cell' style='padding:0; margin:0;height:100%;border-style:solid; border-color:dimgray;text-align:center;'
                            value='" . esc($production->operator_approval) . "' disabled>
                        </div>";
                        if (auth()->user()->inGroup('admin')){
                            $operator_approval .= "<div style='grid-column:2; grid-row:1; padding:0; margin:0;'>
                                <a id='cancel_operator_approval'
                                class='disableifapproved'
                                onclick='remove_operator_approval();'
                                style='align-content:center; justify-self:center; text-align:center; width:100%; height:100%; border-style: solid; border-radius:5px; background-color:dimgrey; color:white; display:block;'
                                >x</a>
                            </div>";
                        }
                    $operator_approval .= "</div>";
            $disable_final_qc = false;
        }


        if($disable_final_qc){
            $teamleader_approval = "<div id='teamleader_approval_id' style='display:grid; gap:0; grid-template-columns: 80% 5% 15%;'>
                        <div style='grid-column:1; grid-row:1; padding:0; margin:0;z-index:1;'>
                            <a id='btn_teamleader_approval' class='button v-small' style='width:-webkit-fill-available; color:white;' disabled>Click to Approve</a>
                        </div>
                    </div>";
        }else{

            if (null == $production->teamleader_approval || empty($production->teamleader_approval)) {
                if(auth()->user()->can_edit('Coffee Quality Control Checklist')){
                $teamleader_approval = "<div id='teamleader_approval_id' style='display:grid; gap:0; grid-template-columns: 80% 5% 15%;'>
                            <div style='grid-column:1; grid-row:1; padding:0; margin:0;z-index:1;'>
                                <a id='btn_teamleader_approval' class='button v-small' style='width:-webkit-fill-available; color:white;' onclick='set_teamleader_approval();'>Click to Approve</a>
                            </div>
                        </div>";
                }else{
                $teamleader_approval = "<div id='teamleader_approval_id' style='display:grid; gap:0; grid-template-columns: 80% 5% 15%;'>
                            <div style='grid-column:1; grid-row:1; padding:0; margin:0;z-index:1;'>
                                <a id='btn_teamleader_approval' class='button v-small' style='width:-webkit-fill-available; color:white;' disabled>Click to Approve</a>
                            </div>
                        </div>";

                }

            } else {
                $teamleader_approval = "<div id='teamleader_approval_id' style='display:grid; gap:0; grid-template-columns: 80% 5% 15%;'>
                            <div style='grid-column:1; grid-row:1; padding:0; margin:0;'>
                                <input type='hidden' name='teamleader_approval' id='teamleader_approval'
                                value='" . esc($production->teamleader_approval) . "' >

                                <input type='text' name='teamleader_approval_display' id='teamleader_approval_display'
                                class='grid-column:1; grid-cell' style='padding:0; margin:0;height:100%;border-style:solid; border-color:dimgray;text-align:center;'
                                value='" . esc($production->teamleader_approval) . "' disabled>
                            </div>";
                            if (auth()->user()->inGroup('admin')){
                            $teamleader_approval .= "<div style='grid-column:2; grid-row:1; padding:0; margin:0;'>
                                <a id='cancel_teamleader_approval'
                                class='disableifapproved'
                                onclick='remove_teamleader_approval();'
                                style='align-content:center; justify-self:center; text-align:center; width:100%; height:100%; border-style: solid; border-radius:5px; background-color:dimgrey; color:white; display:block;'
                                >x</a>
                            </div>";
                            }
                        $teamleader_approval .= "</div>";

            }
        }

        $signatures = array();
        // $signatures['finalqc'] = $finalqc;
        // $signatures['packagingapproval'] = $packagingapproval;
        // $signatures['cupped_by'] = $cupped_by;
        // $signatures['weight_operator'] = $weight_operator;
        // $signatures['weight_leader'] = $weight_leader;

        $signatures['operator_approval'] = $operator_approval;
        $signatures['teamleader_approval'] = $teamleader_approval;

        $recipes = $this->getRecipes();
        $coffees = $this->getCoffees();

        if(auth()->user()->can_edit('Coffee Quality Control Checklist')){
            $disable = !((null == $production->teamleader_approval || empty($production->teamleader_approval)));
        }else{
            $disable = true;
        }
        //dd($disable);

        $certifications = $this->getEditCertificates($id, $disable);

        $roastcolours = $this->getRoastColours();
        $image = $this->getRecipeImage($production->coffee_name);
        $packingimages = $this->getPackingImages();
        //dd($certifications);
        //dd($production);
        //dd($finalqc);
        return view("Production/edit", [
            "production" => $production,
            "recipes" => $recipes,
            "coffees" => $coffees,
            "certifications" => $certifications,
            "signatures" => $signatures,
            "roastcolours" => $roastcolours,
            "image" => $image,
            "packingimages" => $packingimages,
        ]);
    }


    public function update($id)
    {

        $production = $this->getProductionOr404($id);


        // Null all values
        // $a = $production->toArray();
        // foreach ($a as $x => $y) {
        //     $production->__set($x, null);
        // }


//dd($production);

        $posted = $this->request->getPost();
        $production->fill($posted);

//dd($production);

        $production->ProductionID = $id;
        $production->users_id = auth()->user()->id;
        $production->__unset("_method");


        $certifications = $this->getCertsFromRequest($id, $production);

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

        //dd($production);

        if (!$this->model->save($production)) {
            return redirect()->back()->with("errors", $this->model->errors())->withInput();
        }

        $res = $this->saveCerts($certifications, $id);

        return redirect()->to("production")->with("message", "Quality Control Checklist updated");
    }


    public function submitPost($id)
    {


        $success = 'SUCCESS';

        $csrf = csrf_hash();

        $production = $this->getProductionOr404($id);
//dd($production);

//        return json_encode($production);


        $posted = $this->request->getPost();

//        $success = json_encode($posted);

        $production->fill($posted);


//        $success = json_encode($production);


//        return json_encode($production);


//dd($production);

        $production->ProductionID = $id;
        $production->users_id = auth()->user()->id;
        $production->__unset("_method");

        $certifications = $this->getCertsFromRequest($id, $production);

        if (!$production->hasChanged()) {
            $success = 'FAILED - Nothing Changed';
            //return redirect()->to("production")->with("message", "Nothing changed.");
        }else{
//dd($production);
            if (!$this->model->save($production)) {
                $success = 'FAILED - Saving Data ' . json_encode($this->model->errors());
                //return redirect()->back()->with("errors", $this->model->errors())->withInput();
            }else{
                $res = $this->saveCerts($certifications, $id);
            }
        }

        $data = ['token' => $csrf, 'success' => $success];

        //dd($data);

        return json_encode($data);
    }


    public function test()
    {
        return view("Production/test");
    }

    public function getgreencoffees()
    {
        //$recipe = urldecode($recipename);
        $request = $this->request->getPost();
        $recipe = $request['coffeename'];
        $disabled = $request['isdisabled'] == 'yes' ? true : false;
        //$recipe = "Amico Classico";

        $sql = "select recipes.RecipeID, recipes.WastePercent, recipes.RecipeCode, recipes.RoastColourNo, recipes.ImageID, recipes.PackQty, recipe_images.ImageName, roastcolours.FromNumber, roastcolours.ToNumber from recipes left join recipe_images on recipes.ImageID=recipe_images.ImageID left join roastcolours on recipes.RoastColourID=roastcolours.RoastColourID where recipes.RecipeName = ?";
        $db = db_connect();
        $recipeinfo = $db->query($sql, [$recipe])->getRow();
        $recipeinfo->ImageName = base_url("/images/uploaded/recipe_$recipeinfo->RecipeID/$recipeinfo->ImageName");
        $coffees = $this->getCoffees();
        $certificates = $this->getCertificationsRecipe($recipe, $disabled);
        //dd($certificates);

        $recipecoffees = $this->getCoffeesRecipe($recipe, $disabled);

        $num = 1;
        $selects = array();
        $qtys = [];
        foreach ($recipecoffees as $data) {
            //dd($data);
            $recipecoffee = $data->input;
            $qty = $data->qty;
            $qtys[$recipecoffee] = $qty;
            //dd($recipecoffee);
            //$cert = $data->certcheck;
            //dd($cert);
            $select = "<select class='s3 grid-cell inputchange' name='green_coffee$num' id='green_coffee$num' onchange=\"getCertsForCoffee('green_coffee$num')\">";
            foreach ($coffees as $coffee) {
                $select .= "<option value='$coffee->InputName'";
                $select .= $coffee->InputName == $recipecoffee ? ' selected' : '';
                $select .= ">";
                $select .= "$coffee->InputName";
                $select .= "</option>";
            }
            $select .= "</select>";
            $selects = [...$selects, (object) ["green_coffee$num" => $select]];
            $num++;
        }
        for ($i = $num; $i < 6; $i++) {
            $select = "<select class='s3 grid-cell' name='green_coffee$i' id='green_coffee$i'>";
            foreach ($coffees as $coffee) {
                $select .= "<option value='$coffee->InputName'>";
                //    $select .= $coffee->InputName == $recipecoffee->InputName ? ' selected' : '';
                //    $select .= ">";
                $select .= "$coffee->InputName";
                $select .= "</option>";
            }
            $select .= "</select>";
            $selects = [...$selects, (object) ["green_coffee$i" => $select]];
        }

        $certcheck = array();
        $num = 2;
        foreach ($recipecoffees as $data) {
            $cert = $data->certcheck; // ['certcheckbox']; //  $data->certcheck;
            // $certcheck = [...$certcheck, (object) ["cert-row$num" => $cert]];
            //array_push($certcheck,(object) ["cert-row$num" => $cert]);
            $certcheck["cert-row$num"] = $cert;
            $num++;
        }
        //dd($qtys);
        $csrf = csrf_hash();

        $data = ['selects' => $selects, 'certifications' => $certificates, 'certchecks' => $certcheck, 'qty' => $qtys, 'recipeinfo' => $recipeinfo, 'token' => $csrf];
        //        $data = ['selects'=> $selects, 'certifications' => $certificates];

        //dd($data);

        return json_encode($data);
    }

    public function delete()
    {
        $request = $this->request->getPost();
        $productionid = $request['ProductionID'];
        $sql = "update production set deleted=1 where ProductionID=$productionid";
        $data = array();
        $data['token'] = csrf_hash();
        $db = db_connect();
        if ($db->simpleQuery($sql)) {
            // Read new token and assign in $data['token']
            $data['success'] = 1;
        } else {
            $data['success'] = 0;
        }
        return $this->response->setJSON($data);
    }

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

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

        return $data;
    }

    private function getRecipes(): array
    {
        $data = $this->recipesmodel->select('RecipeName')->orderBy('RecipeName', 'asc')->findAll();
        $data = [(object) ['RecipeName' => ''], ...$data];
        return $data;
    }

    private function getCoffees(): array
    {
        $db = db_connect();

        $data = $db->query('Select inputs.InputName From inputs Left Join inputtypes On inputs.InputTypeID=inputtypes.InputTypeID Where ifnull(inputtypes.IsCoffee,0)<>0 order by inputs.Inputname')->getResult();
        $data = [(object) ['InputName' => ''], ...$data];
        return $data;
    }

    private function getCertificationsRecipe($recipe, $isdisabled)
    {
        $db = db_connect();

        $sql = "SELECT DISTINCT Certification, ifnull(c.CertificationID,0) as CertificationID
        FROM certifications
        LEFT JOIN (
        SELECT distinct inputs.CertificationID, inputs.InputName
        FROM recipes
        LEFT JOIN recipeinputs ON recipes.RecipeID=recipeinputs.RecipeID
        LEFT JOIN inputs ON recipeinputs.InputID=inputs.InputID
        LEFT JOIN inputtypes ON inputs.InputTypeID=inputtypes.InputTypeID
        WHERE IFNULL(inputtypes.IsCoffee,0)<>0
        AND recipes.RecipeName=?
        ) c ON certifications.CertificationID = c.CertificationID ORDER BY Certification";

        $data = $db->query($sql, [$recipe])->getResult();
        $cols = count($data);

        $certheader = "<div id='cert-header' style='grid-column:4; grid-row:1;'>
            <div class='v-small' style='display:grid; grid-template-columns:repeat($cols,auto);'>";
        foreach ($data as $cert) {
            $certheader .= "<div style='writing-mode:vertical-lr;margin-top:15px;margin-left:3px;'> $cert->Certification</div>";
        }
        $certheader .= "    </div>
        </div>";

        $certcheckbox = "<div id='certifications' style='padding:0; display:grid; gap:0; grid-template-columns:repeat($cols,auto);'>
        <div style='grid-column: 1 / SPAN 2; grid-row: 2;'>
            <p class='s4' style='padding-top: 3pt;padding-left: 11pt;text-indent: 0pt;text-align: left;'>Certification:</p>
        </div>";
        $col = 3;
        foreach ($data as $cert) {
            $certcheckbox .= "<div class='inputchange' style='grid-column:$col; grid-row:2;'>
            <p class='s3 grid-cell-center'>$cert->Certification</p>
            <input class='grid-cell inputchange disableifapproved' type='checkbox' name='certification_$cert->Certification' id='certification_$cert->Certification' value='1' ";
            $certcheckbox .= intval($cert->CertificationID) > 0 ? "checked " : "";
            if($isdisabled){
                $certcheckbox .= " disabled ";
            }
            $certcheckbox .= "></div>";
            $col++;
        }
        $certcheckbox .= "</div>";

        $ret = array("certcheckbox" => $certcheckbox, "certheader" => $certheader);
        //dd($ret);
        return $ret;
    }

    private function getCoffeesRecipe($recipe, $disabled): array
    {
        $db = db_connect();

        $sql = "SELECT DISTINCT inputs.InputName, certifications.Certification, ifnull(certifications.CertificationID,0) as CertificationID, recipeinputs.Qty
        FROM inputs
        LEFT JOIN inputtypes ON inputs.InputTypeID=inputtypes.InputTypeID
        LEFT JOIN recipeinputs ON inputs.InputID=recipeinputs.InputID
        LEFT JOIN recipes ON recipeinputs.RecipeID=recipes.RecipeID
        LEFT JOIN certifications on inputs.CertificationID=certifications.CertificationID
        WHERE Ifnull(inputtypes.IsCoffee,0)<>0
        AND recipes.RecipeName=?
        ORDER BY certifications.Certification";
        $data = $db->query($sql, $recipe)->getResult();

        $sql = "SELECT certifications.Certification, certifications.CertificationID
        FROM certifications ORDER BY certifications.Certification";
        $certdata = $db->query($sql)->getResult();


        $row = 2;
        $coffee = array();
        //$qty = array();
        foreach ($data as $ele) {
            //$qty[] = array('InputName'=>$ele->InputName, "Qty"=>$ele->Qty);
            $certrow = "<div id='cert-row$row' style='padding:0; display:grid; gap:0; grid-template-columns:repeat(4,auto);align-content:center;'>";
            $col = 1;
            $gridrow = $row - 1;
            foreach ($certdata as $cert) {
                $certtext = $cert->Certification;
                $certrow .= "<div style='grid-column:$col;grid-row:1;' class='grid-cell-checkbox inputchange'><input type='checkbox' name='certification_" . $certtext . $gridrow . "' id='certification_$certtext$gridrow' value='1' ";
                $certrow .= intval($ele->CertificationID) == intval($cert->CertificationID) ? "checked " : "";
                $certrow .= boolval($disabled) ? " disabled " : "";
                $certrow .= "></div>";
                $col++;
            }
            $certrow .= "</div>";

            array_push($coffee, (object) array("input" => $ele->InputName, "certcheck" => $certrow, "qty" => $ele->Qty));
            // array_push($coffee, (object)array("input"=>$ele->InputName));
            $row++;
        }


        for ($i = $row; $i < 7; $i++) {
            $certrow = "<div id='cert-row$i' style='padding:0; display:grid; gap:0; grid-template-columns:repeat(4,auto);align-content:center;'>";
            $col = 1;
            $gridrow = $i - 1;
            foreach ($certdata as $cert) {
                $certtext = $cert->Certification;
                $certrow .= "<div style='grid-column:$col;grid-row:1;' class='grid-cell-checkbox'><input type='checkbox' name='certification_" . $certtext . $gridrow . "' id='certification_$certtext$gridrow' value='1'></div>";
                $col++;
            }
            $certrow .= "</div>";
            array_push($coffee, (object) array("input" => "", "certcheck" => $certrow, "qty" => "0"));
        }

        return $coffee;
    }

    private function getAllProductionCertifications($productionid): array
    {
        $db = db_connect();
        $sql = "SELECT production_certifications.ProductionCertificationID, certifications.Certification, production_certifications.CertificationID, production_certifications.CheckboxName, 0 as CheckboxValue
           FROM production_certifications
           LEFT JOIN certifications on production_certifications.CertificationID=certifications.CertificationID
           WHERE production_certifications.ProductionID=$productionid
           ORDER BY certifications.Certification";
        $data = $db->query($sql)->getResult();
        $ret = array();
        foreach ($data as $row) {
            $ret[$row->CheckboxName] = $row;
        }
        return $ret;

        // $certifications = array();
        // foreach ($data as $cert) {
        //     $certification = new ProductionCertification;
        //     $key = "certification_$cert->Certification";
        //     $certification->ProductionCertificationID = $cert->ProductionCertificationID;
        //     $certification->CertificationID = $cert->CertificationID;
        //     $certification->CheckboxName = $key;
        //     $certification->CheckboxValue = 0;
        //     $certifications[$key] = $certification;
        //     for ($i = 1; $i < 6; $i++) {
        //         $certification = new ProductionCertification;
        //         $key = "certification_$cert->Certification$i";
        //         $certification->CertificationID = $cert->CertificationID;
        //         $certification->CheckboxName = $key;
        //         $certification->CheckboxValue = 0;
        //         $certifications[$key] = $certification;
        //     }
        // }
        // return $certifications;
    }

    private function getAllCertifications(): array
    {
        // Build inital array of all production-certifications with default checkbox value of 0
        $db = db_connect();
        $sql = "SELECT Certification, CertificationID
        FROM certifications ORDER BY Certification";
        $data = $db->query($sql)->getResult();

        $certifications = array();
        foreach ($data as $cert) {
            $certification = new ProductionCertification;
            // Create e.g certification_FT etc.
            $key = "certification_$cert->Certification";
            $certification->CertificationID = $cert->CertificationID;
            $certification->CheckboxName = $key;
            $certification->CheckboxValue = 0;
            $certifications[$key] = $certification;
            // Create certification_FT1 .. certification_FT5
            for ($i = 1; $i < 6; $i++) {
                $certification = new ProductionCertification;
                $key = "certification_$cert->Certification$i";
                $certification->CertificationID = $cert->CertificationID;
                $certification->CheckboxName = $key;
                $certification->CheckboxValue = 0;
                $certifications[$key] = $certification;
            }
        }
        return $certifications;
    }

    private function getInitalCertificates(): array
    {
        $db = db_connect();

        $sql = "SELECT Certification, CertificationID
        FROM certifications ORDER BY Certification";

        $data = $db->query($sql)->getResult();
        $cols = count($data);
        //dd($data);
        $certheader = "<div id='cert-header' style='grid-column:4; grid-row:1;'>
        <div class='v-small' style='display:grid; grid-template-columns:repeat($cols,auto);'>";
        foreach ($data as $cert) {
            $certheader .= "<div style='writing-mode:vertical-lr;margin-top:15px;margin-left:3px;'> $cert->Certification</div>";
        }
        $certheader .= "    </div>
        </div>";

        $certcheckbox = "<div id='certifications' style='padding:0; display:grid; gap:0; grid-template-columns:repeat($cols,auto);'>
        <div style='grid-column: 1 / SPAN 2; grid-row: 2;'>
            <p class='s4' style='padding-top: 3pt;padding-left: 11pt;text-indent: 0pt;text-align: left;'>Certification:</p>
        </div>";
        $col = 3;
        foreach ($data as $cert) {
            $certcheckbox .= "<div style='grid-column:$col; grid-row:2;'>
            <p class='s3 grid-cell-center'>$cert->Certification</p>
            <input class='grid-cell disableifapproved' type='checkbox' name='certification_$cert->Certification' id='certification_$cert->Certification' value='1' ></div>";
            // $certcheckbox .= intval($cert->CertificationID) > 0 ? "checked " : "";
            // $certcheckbox .= "></div>";
            $col++;
        }
        $certcheckbox .= "</div>";
        $certcheck = array();
        $num = 1;
        for ($i = 2; $i <= 6; $i++) {
            $certrow = "<div style='grid-column:4; grid-row:$i;'>";
            $certrow .= "<div id='cert-row$i' style='padding:0; display:grid; gap:0; grid-template-columns:repeat($cols,auto);align-content:center;'>";
            $col = 1;
            foreach ($data as $cert) {
                $certtext = $cert->Certification;
                //                $fld =  "\$production-&gt;{$certtext}{$num}";
                $certrow .= "<div style='grid-column:$col;grid-row:1;' class='grid-cell-checkbox'><input class='disableifapproved' type='checkbox' name='certification_" . $certtext . $num . "' id='certification_$certtext$num' value='1'></div>";
                $col++;
            }
            $certrow .= "</div>";
            $certrow .= "</div>";
            array_push($certcheck, (object) array("certcheck" => $certrow));
            $num++;
        }

        $ret = array("certcheckbox" => $certcheckbox, "certheader" => $certheader, "certcheck" => $certcheck);

        return $ret;
    }

    public function getcertsforcoffee()
    {
        $request = $this->request->getPost();
        $coffeename = $request['coffeename'];
        $rownum = $request['rownum'];
        $gridrow = intval($rownum) + 1;
        $ret = array();
        $ret['token'] = csrf_hash();

        $db = db_connect();

        $sql = "SELECT Certification, ifnull(c.CertificationID,0) as CertificationID
        FROM certifications
        LEFT JOIN (
        SELECT distinct inputs.CertificationID
        FROM inputs
        LEFT JOIN inputtypes ON inputs.InputTypeID=inputtypes.InputTypeID
        WHERE IFNULL(inputtypes.IsCoffee,0)<>0
        AND inputs.InputName='$coffeename'
        ) c ON certifications.CertificationID = c.CertificationID ORDER BY Certification";
        $data = $db->query($sql)->getResult();

        $cols = count($data);

        $certcheckbox = "<div id='cert-row$gridrow' style='padding:0; display:grid; gap:0; grid-template-columns:repeat($cols,auto);align-content:center;'>";
        $col = 1;

        foreach ($data as $cert) {
            $fname = 'certification_' . $cert->Certification . $rownum;
            $fid = 'certification_' . $cert->Certification . $rownum;
            $certcheckbox .= "<div style='grid-column:$col;grid-row:1;' class='grid-cell-checkbox'>";
            $certcheckbox .= "<input class='disableifapproved' type='checkbox' name='$fname' id='$fid' value='1' ";
            $certcheckbox .= intval($cert->CertificationID) > 0 ? " checked " : "";
            $certcheckbox .= ">";
            $certcheckbox .= "</div>";
            $col++;
        }
        $certcheckbox .= "</div>";

        $ret['certcheckbox'] = $certcheckbox;
        $ret['success'] = 1;

        return $this->response->setJSON($ret);
    }

    private function getEditCertificates($id, $disable): array
    {
        $db = db_connect();

        $sql = "SELECT certifications.Certification, certifications.CertificationID,
		ifnull(production_certifications.CheckboxName, CONCAT('certification_',certifications.Certification)) AS CheckboxName,
		ifnull(production_certifications.CheckboxValue,0) as CheckboxValue
		,IFNULL(CAST(RIGHT(production_certifications.CheckboxName,1) AS UNSIGNED),0) AS CheckType
		FROM certifications
		LEFT JOIN production_certifications ON  certifications.CertificationID= production_certifications.CertificationID
		WHERE production_certifications.ProductionID=?
		OR IFNULL(production_certifications.ProductionID,-1) = -1
		ORDER BY certifications.Certification";

        $production_cerifications = $db->query($sql, $id)->getResult();

        if (!is_array($production_cerifications) || count($production_cerifications) == 0) {
            return $this->getInitalCertificates();
        }

        $data = array();
        foreach ($production_cerifications as $production_cerification) {
            if ($production_cerification->CheckType == 0) {
                $data[] = $production_cerification;
            }
        }

        // If there are no Production-certifications for this productionid use the ones from certificates
        if (!is_array($data) || count($data) == 0) {
            return $this->getInitalCertificates();
        }
        //dd($data);
        $cols = count($data);

        $certheader = "<div id='cert-header' style='grid-column:3; grid-row:1;'>
        <div class='v-small' style='display:grid; grid-template-columns:repeat($cols,auto);'>";
        foreach ($data as $cert) {
            $certheader .= "<div style='writing-mode:vertical-lr;margin-top:15px;margin-left:3px;'> $cert->Certification</div>";
        }
        $certheader .= "    </div>
        </div>";


        $certcheckbox = "<div id='certifications' style='padding:0; display:grid; gap:0; grid-template-columns:repeat($cols,auto);'>
        <div style='grid-column: 1 / SPAN 2; grid-row: 2;'>
            <p class='s4' style='padding-top: 3pt;padding-left: 11pt;text-indent: 0pt;text-align: left;'>Certification:</p>
        </div>";
        $col = 3;
        foreach ($data as $cert) {
            $certcheckbox .= "<div style='grid-column:$col; grid-row:2;'>
            <p class='s3 grid-cell-center'>$cert->Certification</p>
            <input class='grid-cell disableifapproved' type='checkbox' name='certification_$cert->Certification' id='certification_$cert->Certification' value='1' ";
            $certcheckbox .= intval($cert->CheckboxValue) == 1 ? "checked" : "";
            if ($disable) {
                $certcheckbox .= " disabled ";
            }
            $certcheckbox .= "></div>";
            $col++;
        }
        $certcheckbox .= "</div>";

        $certcheck = array();

        for ($i = 2; $i <= 6; $i++) {
            $certrow = "<div style='grid-column:3; grid-row:$i;'>\n";
            $certrow .= "<div id='cert-row$i' style='padding:0; display:grid; gap:0; grid-template-columns:repeat($cols,auto);align-content:center;'>\n";
            $col = 1;
            $certnum = $i - 1;
            foreach ($production_cerifications as $cert) {
                if ($cert->CheckType == $certnum) {
                    $certtext = $cert->Certification;
                    $certrow .= "<div style='grid-column:$col;grid-row:1;' class='grid-cell-checkbox'><input class='disableifapproved' type='checkbox' name='certification_" . $certtext . $certnum . "' id='certification_$certtext$certnum' value='1'";
                    $certrow .= intval($cert->CheckboxValue) > 0 ? " checked " : "";
                    if ($disable) {
                        $certrow .= " disabled ";
                    }
                    $certrow .= "></div>\n";
                    $col++;
                }
            }
            $certrow .= "</div>\n";
            $certrow .= "</div>\n";
            array_push($certcheck, (object) array("certcheck" => $certrow));
        }

        $ret = array("certcheckbox" => $certcheckbox, "certheader" => $certheader, "certcheck" => $certcheck);

        return $ret;
    }

    private function getRoastColours()
    {
        $db = db_connect();
        $sql = "SELECT RoastColourID, FromNumber, ToNumber, RoastColourName from roastcolours ORDER BY FromNumber";
        $data = $db->query($sql)->getResult('App\Entities\RoastColour');
        return $data;
    }

    private function getRecipeImage($recipename)
    {
        $sql = "select recipes.RecipeID,recipe_images.ImageID, recipe_images.ImageName from recipe_images left join recipes on recipe_images.ImageID=recipes.ImageID where recipes.RecipeName=?";
        $db = db_connect();
        $data = $db->query($sql,$recipename)->getRow();
        if(null == $data){
            $data = array();
            $data["ImageID"] = null;
            $data["ImageName"] = null;
            $data = (object)$data;
        }else{
            $data->ImageName = base_url("/images/uploaded/recipe_$data->RecipeID/$data->ImageName");
        }
        return $data;
    }
    private function getFullImage($image)
    {
        if($image == ''){
            return '';
        }
        $fullimage  = str_replace('\\', '/',base_url("/images/uploaded/systemimages/$image"));
        return $fullimage;
    }
    private function getPackingImages(): array
    {
        $sql = "select ImageID, UsedFor,ImageName from system_images where UsedFor in ('BAGLABEL','BAGSEAL','EXPDATE','BOXEND');";
		$db = db_connect();
		$data = $db->query($sql)->getResult('App\Entities\SystemImage');
        $images = [ 'BAGLABEL' => ['ImageID' => 0, 'ImageName' => '', 'ImageTitle' => 'Bag Label'],
                    'BAGSEAL' => ['ImageID' => 0, 'ImageName' => '', 'ImageTitle' => 'Bag Seal'],
                    'EXPDATE' => ['ImageID' => 0, 'ImageName' => '', 'ImageTitle' => 'Expiry Date'],
                    'BOXEND' => ['ImageID' => 0, 'ImageName' => '', 'ImageTitle' => 'Box End']
                  ];
        foreach($data as $img){
            if($img->UsedFor == 'BAGLABEL'){
                $images['BAGLABEL'] = ['ImageID' => $img->ImageID, 'ImageName' => $this->getFullImage($img->ImageName), 'ImageTitle' => 'Bag Label'];
            }
            if($img->UsedFor == 'BAGSEAL'){
                $images['BAGSEAL'] = ['ImageID' => $img->ImageID, 'ImageName' => $this->getFullImage($img->ImageName), 'ImageTitle' => 'Bag Seal'];
            }
            if($img->UsedFor == 'EXPDATE'){
                $images['EXPDATE'] = ['ImageID' => $img->ImageID, 'ImageName' => $this->getFullImage($img->ImageName), 'ImageTitle' => 'Expiry Date'];
            }
            if($img->UsedFor == 'BOXEND'){
                $images['BOXEND'] = ['ImageID' => $img->ImageID, 'ImageName' => $this->getFullImage($img->ImageName), 'ImageTitle' => 'Box End'];
            }
        }

        return $images;


    }

}
