<?php
//get all products (get which company they belong too)
//get all products in one company
//get all products in one facility
//get all products in one private label
//add product into ingredient table
//get one product
//add product
//edit product
//delete product

class Product
{
    private $db;

    public function __construct()
    {
        $this->db = new Database;
    }

    public function viewProduct($data){
        $this->db->query('SELECT products.*, companies.*, facilities.facility_name, pl_companies.pl_name FROM products LEFT JOIN companies ON products.company_id = companies.company_id LEFT JOIN facilities ON products.facility_id = facilities.facility_id LEFT JOIN pl_companies ON products.pl_id = pl_companies.pl_id WHERE products.product_id = :product_id');

        $this->db->bind(':product_id', $data['product_id']);

        $result = $this->db->single();
        return $result;
    }
// ONLY VAAD
    public function viewProducts(){
        $this->db->query('SELECT products.*, companies.*, facilities.facility_name, pl_companies.pl_name FROM products LEFT JOIN companies ON products.company_id = companies.company_id LEFT JOIN facilities ON products.facility_id = facilities.facility_id LEFT JOIN pl_companies ON products.pl_id = pl_companies.pl_id ORDER BY products.product_eval_status');

        $results = $this->db->resultSet();
        return $results;
    }

    public function viewWorkflowProducts(){
        $this->db->query('SELECT products.*, companies.*, facilities.facility_name, pl_companies.pl_name FROM products LEFT JOIN companies ON products.company_id = companies.company_id LEFT JOIN facilities ON products.facility_id = facilities.facility_id LEFT JOIN pl_companies ON products.pl_id = pl_companies.pl_id WHERE products.product_eval_status IS NULL');

        $results = $this->db->resultSet();
        return $results;
    }

    public function viewCompanyProducts($data){
        $this->db->query('SELECT products.*, companies.*, facilities.facility_name, pl_companies.pl_name FROM products LEFT JOIN companies ON products.company_id = companies.company_id LEFT JOIN facilities ON products.facility_id = facilities.facility_id LEFT JOIN pl_companies ON products.pl_id = pl_companies.pl_id WHERE products.company_id = :company_id ORDER BY products.product_eval_status');

        $this->db->bind(':company_id', $data['company_id']);

        $results = $this->db->resultSet();
        return $results;
    }

    public function viewFacilityProducts($data){
        $this->db->query('SELECT products.*, companies.*, facilities.facility_name, pl_companies.pl_name FROM products LEFT JOIN companies ON products.company_id = companies.company_id LEFT JOIN facilities ON products.facility_id = facilities.facility_id LEFT JOIN pl_companies ON products.pl_id = pl_companies.pl_id WHERE products.facility_id = :facility_id ORDER BY products.product_eval_status');

        $this->db->bind(':facility_id', $data['facility_id']);

        $results = $this->db->resultSet();
        return $results;
    }

    public function viewPLProducts($data){
        $this->db->query('SELECT products.*, companies.*, facilities.facility_name, pl_companies.pl_name FROM products LEFT JOIN companies ON products.company_id = companies.company_id LEFT JOIN facilities ON products.facility_id = facilities.facility_id LEFT JOIN pl_companies ON products.pl_id = pl_companies.pl_id WHERE products.pl_id = :pl_id ORDER BY products.product_eval_status');

        $this->db->bind(':pl_id', $data['pl_id']);

        $results = $this->db->resultSet();
        return $results;
    }

    public function createProduct($data){
        $this->db->query('SELECT * FROM products WHERE product_name = :product_name');
        $this->db->bind(':product_name', $data['product_name']);

        if($this->db->resultSet()){
            return false;
        } else {
            $this->db->query('INSERT INTO products (product_name, product_brand, company_id, facility_id, pl_id) VALUES (:product_name, :product_brand, :company_id, :facility_id, :pl_id)');

            $this->db->bind(':product_name', $data['product_name']);
            $this->db->bind(':product_brand', $data['product_brand']);
            $this->db->bind(':company_id', $data['company_id']);
            $this->db->bind(':facility_id', $data['facility_id']);
            $this->db->bind(':pl_id', $data['pl_id']);

        if($this->db->execute()){
            return true;
        } else {
            return false;
        }
    }
}
    //This is to get the product id while creating the product
    public function getProductID($data){
        $this->db->query('SELECT product_id FROM products WHERE product_name = :product_name && product_brand = :product_brand && company_id = :company_id');

        $this->db->bind(':product_name', $data['product_name']);
        $this->db->bind(':product_brand', $data['product_brand']);
        $this->db->bind(':company_id', $data['company_id']);

        $result = $this->db->single();
        return $result;
    }

    //add ingredients to the formula table
    public function addIngredientsToProduct($data){
        $this->db->query('INSERT INTO formulas (product_id, ingredient_id) VALUES (:product_id, :ingredient_id)');

        $this->db->bind(':product_id', $data['product_id']);
        $this->db->bind(':ingredient_id', $data['ingredient_id']);

        if($this->db->execute()){
            return true;
        } else {
            return false;
        }
    }

    //edit product info
    public function editProduct($data){
        $this->db->query('UPDATE products SET product_name = :product_name, product_brand = :product_brand, product_eval_status = 0, product_eval_date = NULL, user_id = NULL, facility_id = :facility_id, pl_id = :pl_id WHERE product_id = :product_id');

        $this->db->bind(':product_name', $data['product_name']);
        $this->db->bind(':product_brand', $data['product_brand']);
        $this->db->bind(':facility_id', $data['facility_id']);
        $this->db->bind(':pl_id', $data['pl_id']);
        $this->db->bind(':product_id', $data['product_id']);

        if($this->db->execute()){
            return true;
        } else {
            return false;
        }
    }

    //this is used  to delete the relationship between a product and its ingredients
    public function deleteFromFormula($data){
        $this->db->query('DELETE FROM formulas WHERE product_id = :product_id');

        $this->db->bind(':product_id', $data['product_id']);

        if($this->db->execute()){
            return true;
        } else {
            return false;
        }
    }

     //this is used  to delete an ingredient from its product
     public function deleteIngredientFromProduct($data){
        $this->db->query('DELETE FROM formulas WHERE product_id = :product_id && ingredient_id = :ingredient_id');

        $this->db->bind(':product_id', $data['product_id']);
        $this->db->bind(':ingredient_id', $data['ingredient_id']);

        if($this->db->execute()){
            return true;
        } else {
            return false;
        }
    }

    public function deleteProduct($data){
        $this->db->query('DELETE FROM products WHERE product_id = :product_id');

        $this->db->bind(':product_id', $data['product_id']);

        if($this->db->execute()){
            return true;
        } else {
            return false;
        }
    }

    public function editProductStatus($data){
        $this->db->query('UPDATE products SET product_eval_status = :product_eval_status, product_eval_date = :product_eval_date, product_note = :product_note, user_id = :user_id WHERE product_id = :product_id');

        $this->db->bind(':product_eval_status', $data['product_eval_status']);
        $this->db->bind(':product_eval_date', $data['product_eval_date']);
        $this->db->bind(':product_note', $data['product_note']);
        $this->db->bind(':user_id', $data['user_id']);
        $this->db->bind(':product_id', $data['product_id']);

        if($this->db->execute()){
            return true;
        } else {
            return false;
        }
    }

    public function editProductNote($data){
        $this->db->query('UPDATE products SET product_note = :product_note, user_id = :user_id WHERE product_id = :product_id');

        $this->db->bind(':product_note', $data['product_note']);
        $this->db->bind(':user_id', $data['user_id']);
        $this->db->bind(':product_id', $data['product_id']);

        if($this->db->execute()){
            return true;
        } else {
            return false;
        }
    }

    public function findProduct($data){
        $this->db->query('SELECT products.*, companies.company_name, facilities.facility_name, pl_companies.pl_name FROM products LEFT JOIN companies ON products.company_id = companies.company_id LEFT JOIN facilities ON products.facility_id = facilities.facility_id LEFT JOIN pl_companies ON products.pl_id = pl_companies.pl_id WHERE product_name LIKE :product_name ORDER BY products.product_eval_status');
        $this->db->bind(':product_name', '%' . $data['product_name'] . '%');

        $results = $this->db->resultSet();
        return $results;
    }

    public function findProductById($data){
        $this->db->query('SELECT products.*, companies.company_name, facilities.facility_name, pl_companies.pl_name FROM products LEFT JOIN companies ON products.company_id = companies.company_id LEFT JOIN facilities ON products.facility_id = facilities.facility_id LEFT JOIN pl_companies ON products.pl_id = pl_companies.pl_id WHERE product_id = :product_id');
        $this->db->bind(':product_id', $data['product_id']);

        $results = $this->db->resultSet();
        return $results;
    }

    public function findCompanyProduct($data){
        $this->db->query('SELECT products.*, companies.company_name, facilities.facility_name, pl_companies.pl_name FROM products LEFT JOIN companies ON products.company_id = companies.company_id LEFT JOIN facilities ON products.facility_id = facilities.facility_id LEFT JOIN pl_companies ON products.pl_id = pl_companies.pl_id WHERE product_name LIKE :product_name && products.company_id = :company_id ORDER BY products.product_eval_status');

//        $this->db->query('SELECT * FROM products WHERE product_name LIKE :product_name && company_id = :company_id');
        $this->db->bind(':product_name', '%' . $data['product_name'] . '%');
        $this->db->bind(':company_id', $data['company_id']);

        $results = $this->db->resultSet();
        return $results;
    }

}
