<?php
require_once('vendor/autoload.php');
require_once('key.php');
use \Psr\Http\Message\ServerRequestInterface as Request;
use \Psr\Http\Message\ResponseInterface as Response;
$app = new \Slim\App;
$app->options('/{routes:.+}', function ($request, $response, $args) {
    return $response;
});
$app->add(function ($req, $res, $next) {
    $response = $next($req, $res);
    return $response->withHeader('Access-Control-Allow-Origin', '*')->withHeader('Access-Control-Allow-Headers', 'X-Requested-With, Content-Type, Accept, Origin, Authorization')->withHeader('Access-Control-Allow-Methods', 'GET, POST, PUT, DELETE, OPTIONS');
});
$app->add(new \Slim\SessionMiddleware(['name' => 'MySessionName']));

/* ==============================================
API: v1.0
- Session
- Admin
- Dashboard
- Client
- Provider
- Product
- Price
- Custom
- Order

================================================ */
/* ------------------
Session
--------------------*/
$app->get('/session/user', function (Request $request, Response $response) {
		$session = new \Slim\Session();
		if($session->user != null){
		  echo json_encode($session->user);
		} else {
		  echo '{"error": {"text": "La session user n\'est pas definie"}}';
		}
});
$app->get('/session/basket', function (Request $request, Response $response) {
		$session = new \Slim\Session();
		if($session->basket != null){
		  echo json_encode($session->basket);
		} else {
		  echo '{"error": {"text": "La session basket n\'est pas definie"}}';
		}
});
$app->get('/session/destroy', function (Request $request, Response $response) {
\Slim\Session::destroy();
		if($session->basket == null && $session->user == null){
		  echo '{"notice": {"text": "La session est d&eacute;truite"}}';
		} else {
		  echo '{"error": {"text": "La session n\'est pas d&eacute;truite"}}';
		}
});
/* ------------------
Admin
--------------------*/
// Check Admin
$app->get('/admin/check', function (Request $request, Response $response) {
    $sql = "SELECT * FROM admin";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->execute();
        $count = $stmt->rowCount();
        $db = null;
        echo $count;
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Add Admin
$app->post('/admin/add', function (Request $request, Response $response) {
    $username = $request->getParam('username');
    $password = md5($request->getParam('password'));
    $email = $request->getParam('email');
    $sql = "INSERT INTO admin (username, password, email) VALUES (:username, :password, :email)";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->bindParam(':username', $username);
        $stmt->bindParam(':password', $password);
        $stmt->bindParam(':email', $email);
        $stmt->execute();
        $id = $db->lastInsertId();
        $db = null;
        echo '{"notice": {"text": "Admin Added", "id": ' . $id . '}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Login Admin
$app->post('/admin/login', function (Request $request, Response $response) {
    $username = $request->getParam('username');
    $password = md5($request->getParam('password'));
    $sql = "SELECT * FROM admin WHERE username = :username AND password = :password";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->bindParam(':username', $username);
        $stmt->bindParam(':password', $password);
        $stmt->execute();
        $result = $stmt->fetch();
        $count = $stmt->rowCount();
        //User Found
        if ($count) {
            $arrRtn['id'] = $result["id"];
            $arrRtn['username'] = $username;
            $db = null;
            echo json_encode($arrRtn);
        } else {
            $db = null;
            echo '{"notice": {"text": "Votre identifiant ou votre mot de passe est incorrect ! Essayez &agrave; nouveau."}}';
        }
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Reset Login Password
$app->put('/admin/reset', function (Request $request, Response $response) {
    $email = $request->getParam('email');
    $sql_select = "SELECT * FROM admin WHERE email = :email";
    $sql_update = "UPDATE admin SET password = :password WHERE email = :email";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql_select);
        $stmt->bindParam(':email', $email);
        $stmt->execute();
        $result = $stmt->fetch();
        $count = $stmt->rowCount();
        //User Found
        if ($count) {
            $Caracteres = 'AaBbCcDdEeFfGgHhIiJjKkLlMmNnOoPpQqRrSsTtUuVvWwXxYyZz0123456789';
            $QuantidadeCaracteres = strlen($Caracteres);
            $QuantidadeCaracteres--;
            $Hash = NULL;
            for ($x = 1;$x <= 8;$x++) {
                $Posicao = rand(0, $QuantidadeCaracteres);
                $Hash.= substr($Caracteres, $Posicao, 1);
            }
            $newPassword = md5($Hash);
            //Update Password
            $stmt = $db->prepare($sql_update);
            $stmt->bindParam(':password', $newPassword);
            $stmt->bindParam(':email', $email);
            $stmt->execute();
            //Send Password
            $db = null;
            echo '{"notice": {"text": "New password has been sent. ' . $Hash . '"}}';
        } else {
            $db = null;
            echo '{"notice": {"text": "L\'adresse e-mail n\'existe pas dans notre base de données."}}';
        }
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
/* ------------------
Dashboard
--------------------*/
// Get All Stats
$app->get('/dashboard', function (Request $request, Response $response) {

    $sql = "SELECT 
    (SELECT COUNT(*) FROM clients) AS clients,
    (SELECT COUNT(*) FROM products) AS products,
    (SELECT COUNT(*) FROM providers) AS providers,
    (SELECT COUNT(*) FROM orders) AS orders";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $stats = $stmt->fetch(PDO::FETCH_OBJ);
        $db = null;
        echo json_encode($stats);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
/* ------------------
Client
--------------------*/
// Get All Clients
$app->get('/clients', function (Request $request, Response $response) {
    $sql = "SELECT * FROM clients";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $clients = $stmt->fetchAll(PDO::FETCH_OBJ);
        $db = null;
        echo json_encode($clients);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Get Single Client
$app->get('/client/{clientId}', function (Request $request, Response $response) {
    $clientId = $request->getAttribute('clientId');
    $sql = "SELECT * FROM clients WHERE clientId = $clientId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $clients = $stmt->fetch(PDO::FETCH_OBJ);
        $db = null;
        echo json_encode($clients);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Login Client
$app->post('/client/login', function (Request $request, Response $response) {
    $username = $request->getParam('username');
    $password = md5($request->getParam('password'));
    $sql = "SELECT * FROM clients WHERE username = :username AND password = :password";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->bindParam(':username', $username);
        $stmt->bindParam(':password', $password);
        $stmt->execute();
        $result = $stmt->fetch();
        $count = $stmt->rowCount();
        //User Found
        if ($count) {
			if($result["isActive"] != 0){
				$arrRtn['id'] = $result["clientId"];
				$arrRtn['username'] = $username;
				$arrRtn['companyName'] = $result["companyName"];
				$arrRtn['street'] = $result["street"];
				$arrRtn['postalCode'] = $result["postalCode"];
				$arrRtn['city'] = $result["city"];
				$session = new \Slim\Session();
				$session->set('user', $arrRtn);
				$db = null;
				echo json_encode($arrRtn);
			} else {
				$db = null;
				echo '{"error": {"text": "Votre compte est en cours d\'activation ! Essayez plus tard."}}';
			}
        } else {
            $db = null;
            echo '{"notice": {"text": "Votre identifiant ou votre mot de passe est incorrect ! Essayez &agrave; nouveau."}}';
        }
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});

// Add Client
$app->post('/client/add', function (Request $request, Response $response) {
    $username = $request->getParam('username');
    $password = md5($request->getParam('password'));
    $companyName = $request->getParam('companyName');
    $email = $request->getParam('email');
    $phone = $request->getParam('phone');
    $street = $request->getParam('street');
    $postalCode = $request->getParam('postalCode');
    $city = $request->getParam('city');
    $country = $request->getParam('country');
    $sql_select = "SELECT * FROM clients WHERE username = :username";
    $sql_insert = "INSERT INTO clients (username, password, companyName, email, phone, street, postalCode, city, country, isActive) VALUES (:username, :password, :companyName, :email, :phone, :street, :postalCode, :city, :country, 1)";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql_select);
        $stmt->bindParam(':username', $username);
        $stmt->execute();
        $count = $stmt->rowCount();
        //Client Not Found
        if ($count) {
            echo '{"error": {"text": "Username ' . $username . ' already exist. Please choose another one"}}';
        } else {
            $stmt = $db->prepare($sql_insert);
            $stmt->bindParam(':username', $username);
            $stmt->bindParam(':password', $password);
            $stmt->bindParam(':companyName', $companyName);
            $stmt->bindParam(':email', $email);
            $stmt->bindParam(':phone', $phone);
            $stmt->bindParam(':street', $street);
            $stmt->bindParam(':postalCode', $postalCode);
            $stmt->bindParam(':city', $city);
            $stmt->bindParam(':country', $country);
            $stmt->execute();
            $id = $db->lastInsertId();
            $db = null;
            echo '{"notice": {"text": "Client Added", "id": ' . $id . '}}';
        }
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Update State Client
$app->put('/client/activate/{clientId}', function (Request $request, Response $response) {
    $clientId = $request->getAttribute('clientId');
    $isActive = $request->getParam('isActive');
    $sql = "UPDATE clients SET
                isActive = :isActive
            WHERE clientId = $clientId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->bindParam(':isActive', $isActive);
        $stmt->execute();
        $db = null;
        echo '{"notice": {"text": "Client Updated"}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Update Client
$app->put('/client/update/{clientId}', function (Request $request, Response $response) {
    $clientId = $request->getAttribute('clientId');
    $username = $request->getParam('username');
    $password = md5($request->getParam('password'));
    $companyName = $request->getParam('companyName');
    $email = $request->getParam('email');
    $phone = $request->getParam('phone');
    $street = $request->getParam('street');
    $postalCode = $request->getParam('postalCode');
    $city = $request->getParam('city');
    $country = $request->getParam('country');
    $sql = "UPDATE clients SET
                username = :username,
                password = :password,
                companyName = :companyName,
                email = :email,
                phone = :phone,
                street = :street,
                postalCode = :postalCode,
                city = :city,
                country = :country
            WHERE clientId = $clientId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->bindParam(':username', $username);
        $stmt->bindParam(':password', $password);
        $stmt->bindParam(':companyName', $companyName);
        $stmt->bindParam(':email', $email);
        $stmt->bindParam(':phone', $phone);
        $stmt->bindParam(':street', $street);
        $stmt->bindParam(':postalCode', $postalCode);
        $stmt->bindParam(':city', $city);
        $stmt->bindParam(':country', $country);
        $stmt->execute();
        $db = null;
        echo '{"notice": {"text": "Client Updated"}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Delete Client
$app->delete('/client/delete/{clientId}', function (Request $request, Response $response) {
    $clientId = $request->getAttribute('clientId');
    $sql = "DELETE FROM clients WHERE clientId = $clientId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->execute();
        $db = null;
        echo '{"notice": {"text": "Client Deleted"}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
/* ------------------
Provider
--------------------*/
// Get All Providers
$app->get('/providers', function (Request $request, Response $response) {
    $sql = "SELECT * FROM providers";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $providers = $stmt->fetchAll(PDO::FETCH_OBJ);
        $db = null;
        echo json_encode($providers);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Get Single Provider
$app->get('/provider/{providerId}', function (Request $request, Response $response) {
    $providerId = $request->getAttribute('providerId');
    $sql = "SELECT * FROM providers WHERE providerId = $providerId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $providers = $stmt->fetch(PDO::FETCH_OBJ);
        $db = null;
        echo json_encode($providers);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Add Provider
$app->post('/provider/add', function (Request $request, Response $response) {
    $name = $request->getParam('name');
    $city = $request->getParam('city');
    $country = $request->getParam('country');
    $sql = "INSERT INTO providers (name, city, country) VALUES (:name, :city, :country)";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->bindParam(':name', $name);
        $stmt->bindParam(':city', $city);
        $stmt->bindParam(':country', $country);
        $stmt->execute();
        $id = $db->lastInsertId();
        $db = null;
        echo '{"notice": {"text": "Provider Added", "id": ' . $id . '}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Update Provider
$app->put('/provider/update/{providerId}', function (Request $request, Response $response) {
    $providerId = $request->getAttribute('providerId');
    $name = $request->getParam('name');
    $city = $request->getParam('city');
    $country = $request->getParam('country');
    $sql = "UPDATE providers SET
                name = :name,
                city = :city,
                country = :country
            WHERE providerId = $providerId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->bindParam(':name', $name);
        $stmt->bindParam(':city', $city);
        $stmt->bindParam(':country', $country);
        $stmt->execute();
        $db = null;
        echo '{"notice": {"text": "Provider Updated"}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Delete Provider
$app->delete('/provider/delete/{providerId}', function (Request $request, Response $response) {
    $providerId = $request->getAttribute('providerId');
    $sql = "DELETE FROM providers WHERE providerId = $providerId";
    $sql2 = "UPDATE products SET providerId = 0 WHERE providerId = $providerId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->execute();
        $stmt2 = $db->prepare($sql2);
        $stmt2->execute();
        $db = null;
        echo '{"notice": {"text": "Provider Deleted"}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
/* ------------------
Stocks
--------------------*/
// Get All Products in stock
$app->get('/stocks', function (Request $request, Response $response) {
    //$sql = "SELECT * FROM products LEFT OUTER JOIN providers ON products.providerId = providers.providerId LEFT OUTER JOIN stocks ON products.productId = stocks.productId";
		$sql = "SELECT * FROM products,providers,stocks,achats WHERE products.productId = stocks.productId AND products.providerId = providers.providerId AND products.productId = achats.productId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $products = $stmt->fetchAll(PDO::FETCH_OBJ);
        $result = array();
        $index = 0;
        foreach ($products as $product) {
            $result[$index]['productId'] = $product->productId;
            if (($product->providerId != 0)) {
							$result[$index]['provider'] = array("providerId" => $product->providerId, "name" => $product->name, "city" => $product->city, "country" => $product->country);
            }
            $result[$index]['code'] = $product->code;
            $result[$index]['designation'] = $product->designation;
            $result[$index]['description'] = $product->description;
            $result[$index]['category'] = $product->category;
            $result[$index]['price'] = $product->price;
						$result[$index]['purchased'] = $product->purchased;
						$result[$index]['date'] = $product->lastDate;
						$result[$index]['TVA'] = $product->TVA;
            $result[$index]['isActive'] = $product->isActive;
            $result[$index]['isFeatured'] = $product->isFeatured;
						$result[$index]['quantity'] = $product->quantity;
						$result[$index]['alert'] = $product->alert;
            $index++;
        }
        $db = null;
        echo json_encode($result);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Add Product
$app->post('/stocks/add', function (Request $request, Response $response) {
    $providerId = $request->getParam('providerId');
    $code = $request->getParam('code');
    $designation = $request->getParam('designation');
    $description = $request->getParam('description');
    $category = $request->getParam('category');
    $price = $request->getParam('price');
    $tags = explode(",", $request->getParam('tags'));
    $sql = "INSERT INTO products (providerId, code, designation, description, price, category, isActive, isFeatured) VALUES (:providerId, :code, :designation, :description, :price, :category, 1, 0)";
    $sql2 = "INSERT INTO customs (productId, description, commodityCode, statisticalCode, grossMass, netMass, permitObligation, commercialGood, customsClearanceType, nonCustomsLawObligation, statisticalValue, preference, vatCode, packagingType, packagingReferenceNumber, originCountry) VALUES (:productId, '', '', '', 0, 0, '', '', '', '', 0, 0, '', '', '', '')";
    $sql3 = "INSERT INTO tags (productId, keyword) VALUES (:productId, :keyword)";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->bindParam(':providerId', $providerId);
        $stmt->bindParam(':code', $code);
        $stmt->bindParam(':designation', $designation);
        $stmt->bindParam(':description', $description);
        $stmt->bindParam(':category', $category);
        $stmt->bindParam(':price', $price);
        $stmt->execute();
        $id = $db->lastInsertId();
        $stmt = $db->prepare($sql2);
        $stmt->bindParam(':productId', $id);
        $stmt->execute();
        foreach ($tags as $tag) {
            $stmt = $db->prepare($sql3);
            $stmt->bindParam(':productId', $id);
            $stmt->bindParam(':keyword', $tag);
            $stmt->execute();
        }
        $db = null;
        echo '{"notice": {"text": "Product Added", "id": ' . $id . '}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
/* ------------------
Product
--------------------*/
// Get All Products
$app->get('/products', function (Request $request, Response $response) {
    $sql = "SELECT * FROM products LEFT OUTER JOIN providers ON products.providerId = providers.providerId";
    $sql2 = "SELECT * FROM prices WHERE productId = :productId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $products = $stmt->fetchAll(PDO::FETCH_OBJ);
        $result = array();
        $index = 0;
        foreach ($products as $product) {
            $result[$index]['productId'] = $product->productId;
            if (($product->providerId != 0)) {
                $result[$index]['provider'] = array("providerId" => $product->providerId, "name" => $product->name, "city" => $product->city, "country" => $product->country);
            }
            $result[$index]['code'] = $product->code;
            $result[$index]['designation'] = $product->designation;
            $result[$index]['description'] = $product->description;
            $result[$index]['category'] = $product->category;
            $result[$index]['price'] = $product->price;
            $result[$index]['isActive'] = $product->isActive;
            $result[$index]['isFeatured'] = $product->isFeatured;
            $stmt = $db->prepare($sql2);
            $stmt->bindParam(':productId', $product->productId);
            $stmt->execute();
            $prices = $stmt->fetchAll(PDO::FETCH_OBJ);
            $result[$index]['countPrices'] = $stmt->rowCount();
            $index++;
        }
        $db = null;
        echo json_encode($result);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Get All Products By Client
$app->get('/products/{clientId}', function (Request $request, Response $response) {
    $clientId = $request->getAttribute('clientId');
    $sql = "SELECT * FROM products LEFT OUTER JOIN providers ON products.providerId = providers.providerId ORDER BY products.designation";
    $sql2 = "SELECT * FROM prices WHERE clientId = $clientId";
    $sql3 = "SELECT keyword FROM tags WHERE productId = :productId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $products = $stmt->fetchAll(PDO::FETCH_OBJ);
        $result = array();
        $index = 0;
        foreach ($products as $product) {
            $result[$index]['productId'] = $product->productId;
            if (($product->providerId != 0)) {
                $result[$index]['provider'] = array("providerId" => $product->providerId, "name" => $product->name, "city" => $product->city, "country" => $product->country);
            }
			$result[$index]['code'] = $product->code;
            $result[$index]['designation'] = $product->designation;
            $result[$index]['description'] = $product->description;
            $result[$index]['category'] = $product->category;
            $result[$index]['price'] = $product->price;
            $result[$index]['isActive'] = $product->isActive;
            $stmt2 = $db->prepare($sql3);
            $stmt2->bindParam(':productId', $product->productId);
            $stmt2->execute();
            $tags = $stmt2->fetchAll(PDO::FETCH_OBJ);
            $keywords = array();
            foreach ($tags as $tag) {
                array_push($keywords, $tag->keyword);
            }
            $result[$index]['tags'] = $keywords;
            $index++;
        }
        $stmt = $db->query($sql2);
        $prices = $stmt->fetchAll(PDO::FETCH_OBJ);
        foreach ($prices as $price) {
            $index = array_search($price->productId, array_column($result, 'productId'));
            $result[$index]['price'] = $price->specialPrice;
        }
        $db = null;
        echo json_encode($result);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Get Products Categories
$app->get('/categories', function (Request $request, Response $response){
    $sql = "SELECT category, count(*) AS count FROM products WHERE products.isActive = 1 GROUP BY category";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $categories = $stmt->fetchAll(PDO::FETCH_OBJ);
        $db = null;
        echo json_encode($categories);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Get Single Product
$app->get('/product/{productId}', function (Request $request, Response $response) {
    $productId = $request->getAttribute('productId');
    $sql = "SELECT * FROM products WHERE productId = $productId";
    $sql2 = "SELECT keyword FROM tags WHERE productId = $productId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $product = $stmt->fetch(PDO::FETCH_OBJ);
        $result = array();
        $result['productId'] = $product->productId;
        $result['providerId'] = $product->providerId;
        $result['code'] = $product->code;
        $result['designation'] = $product->designation;
        $result['description'] = $product->description;
        $result['price'] = $product->price;
        $result['category'] = $product->category;
        $stmt2 = $db->prepare($sql2);
        $stmt2->execute();
        $tags = $stmt2->fetchAll(PDO::FETCH_OBJ);
        $keywords = array();
        foreach ($tags as $tag) {
            array_push($keywords, $tag->keyword);
        }
        $tags = join(",", $keywords);
        $result['tags'] = $tags;
        $db = null;
        echo json_encode($result);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Add Product
$app->post('/product/add', function (Request $request, Response $response) {
    $providerId = $request->getParam('providerId');
    $code = $request->getParam('code');
    $designation = $request->getParam('designation');
    $description = $request->getParam('description');
    $category = $request->getParam('category');
    $price = $request->getParam('price');
    $tags = explode(",", $request->getParam('tags'));
    $sql = "INSERT INTO products (providerId, code, designation, description, price, category, isActive, isFeatured) VALUES (:providerId, :code, :designation, :description, :price, :category, 1, 0)";
    $sql2 = "INSERT INTO customs (productId, description, commodityCode, statisticalCode, grossMass, netMass, permitObligation, commercialGood, customsClearanceType, nonCustomsLawObligation, statisticalValue, preference, vatCode, packagingType, packagingReferenceNumber, originCountry) VALUES (:productId, '', '', '', 0, 0, '', '', '', '', 0, 0, '', '', '', '')";
    $sql3 = "INSERT INTO tags (productId, keyword) VALUES (:productId, :keyword)";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->bindParam(':providerId', $providerId);
        $stmt->bindParam(':code', $code);
        $stmt->bindParam(':designation', $designation);
        $stmt->bindParam(':description', $description);
        $stmt->bindParam(':category', $category);
        $stmt->bindParam(':price', $price);
        $stmt->execute();
        $id = $db->lastInsertId();
        $stmt = $db->prepare($sql2);
        $stmt->bindParam(':productId', $id);
        $stmt->execute();
        foreach ($tags as $tag) {
            $stmt = $db->prepare($sql3);
            $stmt->bindParam(':productId', $id);
            $stmt->bindParam(':keyword', $tag);
            $stmt->execute();
        }
        $db = null;
        echo '{"notice": {"text": "Product Added", "id": ' . $id . '}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Update Product
$app->put('/product/update/{productId}', function (Request $request, Response $response) {
    $productId = $request->getAttribute('productId');
    $providerId = $request->getParam('providerId');
    $code = $request->getParam('code');
    $designation = $request->getParam('designation');
    $description = $request->getParam('description');
    $category = $request->getParam('category');
    $price = $request->getParam('price');
    $tags = explode(",", $request->getParam('tags'));
    $sql = "UPDATE products SET
                providerId = :providerId,
				code = :code,
                designation = :designation,
                description = :description,
                category =  :category,
                price = :price
            WHERE productId = $productId";
    $sql2 = "DELETE FROM tags WHERE productId = $productId";
    $sql3 = "INSERT INTO tags (productId, keyword) VALUES (:productId, :keyword)";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->bindParam(':providerId', $providerId);
        $stmt->bindParam(':code', $code);
        $stmt->bindParam(':designation', $designation);
        $stmt->bindParam(':description', $description);
        $stmt->bindParam(':category', $category);
        $stmt->bindParam(':price', $price);
        $stmt->execute();
        $stmt = $db->prepare($sql2);
        $stmt->execute();
        foreach ($tags as $tag) {
            $stmt = $db->prepare($sql3);
            $stmt->bindParam(':productId', $productId);
            $stmt->bindParam(':keyword', $tag);
            $stmt->execute();
        }
        $db = null;
        echo '{"notice": {"text": "Product Updated"}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Active Product
$app->put('/product/activate/{productId}', function (Request $request, Response $response) {
    $productId = $request->getAttribute('productId');
    $isActive = $request->getParam('isActive');
    $sql = "UPDATE products SET
                isActive = :isActive
            WHERE productId = $productId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->bindParam(':isActive', $isActive);
        $stmt->execute();
        $db = null;
        echo '{"notice": {"text": "Product Updated"}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Featured Product
$app->put('/product/featured/{productId}', function (Request $request, Response $response) {
    $productId = $request->getAttribute('productId');
    $isFeatured = $request->getParam('isFeatured');
    $sql = "UPDATE products SET
                isFeatured = :isFeatured
            WHERE productId = $productId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->bindParam(':isFeatured', $isFeatured);
        $stmt->execute();
        $db = null;
        echo '{"notice": {"text": "Product Updated"}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Delete Product
$app->delete('/product/delete/{productId}', function (Request $request, Response $response) {
    $productId = $request->getAttribute('productId');
    $sql = "DELETE FROM products WHERE productId = $productId";
    $sql2 = "DELETE FROM prices WHERE productId = $productId";
    $sql3 = "DELETE FROM customs WHERE productId = $productId";
    $sql4 = "DELETE FROM tags WHERE productId = $productId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->execute();
        $stmt = $db->prepare($sql2);
        $stmt->execute();
        $stmt = $db->prepare($sql3);
        $stmt->execute();
        $stmt = $db->prepare($sql4);
        $stmt->execute();
        $db = null;
        echo '{"notice": {"text": "Product Deleted"}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
/* ------------------
Price
--------------------*/
// Get All Prices By Product
$app->get('/prices/{productId}', function (Request $request, Response $response) {
    $productId = $request->getAttribute('productId');
    $sql = "SELECT * FROM prices WHERE productId = $productId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $prices = $stmt->fetchAll(PDO::FETCH_OBJ);
        $db = null;
        echo json_encode($prices);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Add Price
$app->post('/price/add', function (Request $request, Response $response) {
    $productId = $request->getParam('productId');
    $clientId = $request->getParam('clientId');
    $specialPrice = $request->getParam('specialPrice');
    $sql = "INSERT INTO prices (productId, clientId, specialPrice) VALUES (:productId, :clientId, :specialPrice)";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->bindParam(':productId', $productId);
        $stmt->bindParam(':clientId', $clientId);
        $stmt->bindParam(':specialPrice', $specialPrice);
        $stmt->execute();
        $db = null;
        echo '{"notice": {"text": "Price Added"}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Update Price
$app->put('/price/update/{productId}/{clientId}', function (Request $request, Response $response) {
    $productId = $request->getAttribute('productId');
    $clientId = $request->getAttribute('clientId');
    $specialPrice = $request->getParam('specialPrice');
    $sql = "UPDATE prices SET
                specialPrice = :specialPrice
            WHERE productId = $productId AND clientId = $clientId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->bindParam(':specialPrice', $specialPrice);
        $stmt->execute();
        $db = null;
        echo '{"notice": {"text": "Price Updated"}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Delete Price
$app->delete('/price/delete/{productId}/{clientId}', function (Request $request, Response $response) {
    $productId = $request->getAttribute('productId');
    $clientId = $request->getAttribute('clientId');
    $sql = "DELETE FROM prices WHERE productId = $productId AND clientId = $clientId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->execute();
        $db = null;
        echo '{"notice": {"text": "Price Deleted"}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
/* ------------------
Custom
--------------------*/
// Get All Customs
$app->get('/customs', function (Request $request, Response $response) {
    $sql = "SELECT * FROM customs";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $customs = $stmt->fetchAll(PDO::FETCH_OBJ);
        $db = null;
        echo json_encode($customs);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Get Single Custom By Product
$app->get('/custom/{productId}', function (Request $request, Response $response) {
    $productId = $request->getAttribute('productId');
    $sql = "SELECT * FROM customs WHERE productId = $productId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $customs = $stmt->fetch(PDO::FETCH_OBJ);
        $db = null;
        echo json_encode($customs);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Update Custom
$app->put('/custom/update/{productId}', function (Request $request, Response $response) {
    $productId = $request->getAttribute('productId');
    $description = $request->getParam('description');
    $commodityCode = $request->getParam('commodityCode');
    $statisticalCode = $request->getParam('statisticalCode');
    $grossMass = $request->getParam('grossMass');
    $netMass = $request->getParam('netMass');
    $permitObligation = $request->getParam('permitObligation');
    $nonCustomsLawObligation = $request->getParam('nonCustomsLawObligation');
    $customsClearanceType = $request->getParam('customsClearanceType');
    $commercialGood = $request->getParam('commercialGood');
    $statisticalValue = $request->getParam('statisticalValue');
    $preference = $request->getParam('preference');
    $packagingType = $request->getParam('packagingType');
    $packagingReferenceNumber = $request->getParam('packagingReferenceNumber');
    $vatCode = $request->getParam('vatCode');
    $originCountry = $request->getParam('originCountry');
    $sql = "UPDATE customs SET
                    description = :description,
                    commodityCode = :commodityCode,
                    statisticalCode = :statisticalCode,
                    grossMass = :grossMass,
                    netMass = :netMass,
                    permitObligation = :permitObligation,
                    nonCustomsLawObligation = :nonCustomsLawObligation,
                    customsClearanceType  = :customsClearanceType,
                    commercialGood = :commercialGood,
                    statisticalValue = :statisticalValue,
                    preference = :preference,
                    packagingType = :packagingType,
                    packagingReferenceNumber = :packagingReferenceNumber,
                    vatCode = :vatCode,
                    originCountry = :originCountry
                 WHERE productId = $productId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->bindParam(':description', $description);
        $stmt->bindParam(':commodityCode', $commodityCode);
        $stmt->bindParam(':statisticalCode', $statisticalCode);
        $stmt->bindParam(':grossMass', $grossMass);
        $stmt->bindParam(':netMass', $netMass);
        $stmt->bindParam(':permitObligation', $permitObligation);
        $stmt->bindParam(':nonCustomsLawObligation', $nonCustomsLawObligation);
        $stmt->bindParam(':customsClearanceType', $customsClearanceType);
        $stmt->bindParam(':commercialGood', $commercialGood);
        $stmt->bindParam(':statisticalValue', $statisticalValue);
        $stmt->bindParam(':preference', $preference);
        $stmt->bindParam(':packagingType', $packagingType);
        $stmt->bindParam(':packagingReferenceNumber', $packagingReferenceNumber);
        $stmt->bindParam(':vatCode', $vatCode);
        $stmt->bindParam(':originCountry', $originCountry);
        $stmt->execute();
        $db = null;
        echo '{"notice": {"text": "Custom Updated"}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
/* ------------------
Order
--------------------*/
// Get All Orders
$app->get('/orders', function (Request $request, Response $response) {
    $sql = "SELECT * FROM orders";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $orders = $stmt->fetchAll(PDO::FETCH_OBJ);
        $db = null;
        echo json_encode($orders);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Get All Orders By Client
$app->get('/orders/client/{clientName}', function (Request $request, Response $response) {
    $clientName = addslashes(urldecode($request->getAttribute('clientName')));
    $sql = "SELECT * FROM orders WHERE clientName = '$clientName'";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $orders = $stmt->fetchAll(PDO::FETCH_OBJ);
        $db = null;
        echo json_encode($orders);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Add Order
$app->post('/order/add', function (Request $request, Response $response) {
    $basket = $request->getParam('basket');
    $companyName = $request->getParam('companyName');
    $street = $request->getParam('street');
    $postalCode = $request->getParam('postalCode');
    $city = $request->getParam('city');
    $total = $request->getParam('total');
    $sql = "INSERT INTO orders(clientName, street, postalCode, city, date, total, state) VALUES (:clientName,:street,:postalCode,:city, NOW(),:total,0)";
    $sql2 = "INSERT INTO articles(orderId, designation, price, tva, quantity, packagingType, providerName, state) VALUES (:orderId, :designation, :price, :tva, :quantity, 'RR', :providerName, 0)";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->prepare($sql);
        $stmt->bindParam(':clientName', $companyName);
        $stmt->bindParam(':street', $street);
        $stmt->bindParam(':postalCode', $postalCode);
        $stmt->bindParam(':city', $city);
        $stmt->bindParam(':total', $total);
        $stmt->execute();
        $orderId = $db->lastInsertId();
        foreach ($basket as $article) {
            $stmt = $db->prepare($sql2);
            $stmt->bindParam(':orderId', $orderId);
            $stmt->bindParam(':designation', $article['designation']);
            $stmt->bindParam(':price', $article['price']);
            $stmt->bindParam(':tva', $article['TVA']);
            $stmt->bindParam(':quantity', $article['quantity']);
            $stmt->bindParam(':providerName', $article['providerName']);
            $stmt->execute();
        }
		$session = new \Slim\Session();
		$session->set('basket', null);
        $db = null;
        echo '{"notice": {"text": "Order Added"}, "orderId": '.$orderId.'}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Get All Articles By Order
$app->get('/articles/{orderId}/{clientId}', function (Request $request, Response $response) {
    $orderId = $request->getAttribute('orderId');
    $clientId = $request->getAttribute('clientId');
    if ($clientId == "admin"){
    	$sql = "SELECT * FROM articles WHERE orderId = $orderId";
    } else {
    	$sql = "SELECT * FROM `articles`,clients,orders WHERE articles.orderId = orders.orderId AND clients.companyName = orders.clientName AND clients.clientId = $clientId AND orders.orderId= $orderId";
    }
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $articles = $stmt->fetchAll(PDO::FETCH_OBJ);
        $db = null;
        echo json_encode($articles);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Get Exports By Orders
$app->get('/exports', function (Request $request, Response $response) {
    $sql = "SELECT * FROM orders WHERE state <> 2";
    $sql2 = "SELECT * FROM articles WHERE orderId = :orderId AND state = 0 ORDER BY providerName";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $orders = $stmt->fetchAll(PDO::FETCH_OBJ);
        $result = array();
        $index = 0;
        foreach ($orders as $order) {
            $result[$index]['orderId'] = $order->orderId;
            $stmt = $db->prepare($sql2);
            $stmt->bindParam(':orderId', $order->orderId);
            $stmt->execute();
            $articles = $stmt->fetchAll(PDO::FETCH_OBJ);
            $result[$index]['articles'] = $articles;
            $index++;
        }
        $db = null;
        echo json_encode($result);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Get Exports By Providers
$app->get('/exports2', function (Request $request, Response $response) {
    $sql = "SELECT DISTINCT providerName FROM articles WHERE state = 0 ORDER BY providerName";
    $sql2 = "SELECT * FROM articles WHERE providerName = :providerName AND state = 0 ORDER BY orderId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $providers = $stmt->fetchAll(PDO::FETCH_OBJ);
        $result = array();
        $index = 0;
        foreach ($providers as $provider) {
            $result[$index]['providerName'] = $provider->providerName;
            $stmt = $db->prepare($sql2);
            $stmt->bindParam(':providerName', $provider->providerName);
            $stmt->execute();
            $articles = $stmt->fetchAll(PDO::FETCH_OBJ);
            $result[$index]['articles'] = $articles;
            $index++;
        }
        $db = null;
        echo json_encode($result);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Update Articles And Orders
$app->put('/orders/update', function (Request $request, Response $response) {
    $articles = $request->getParam('articles');
    $articles_ids = join(',', $articles);
    $sql = "UPDATE articles SET state = 1 WHERE articleId IN ($articles_ids)";
    $sql5 = "SELECT DISTINCT orderId FROM articles WHERE articleId IN ($articles_ids)";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        // Update Articles
        $stmt = $db->prepare($sql);
        $stmt->execute();
        // Select Orders To Update
        $stmt = $db->prepare($sql5);
        $stmt->execute();
        $orders = $stmt->fetchAll(PDO::FETCH_OBJ);
        $orders_update_ids = array();
        $orders_array = array();
        foreach ($orders as $order) {
            array_push($orders_update_ids, $order->orderId);
        }
        $orders_ids = join(',', $orders_update_ids);
        // Select Orders To Update
        $sql4 = "SELECT orderId, state, COUNT(*) AS nbr FROM articles WHERE state IN (0,1) AND orderId IN ($orders_ids) GROUP BY orderId, state";
        $stmt = $db->prepare($sql4);
        $stmt->execute();
        $orders = $stmt->fetchAll(PDO::FETCH_OBJ);
        $orders_update_1 = array();
        $orders_update_2 = array();
        $index = 0;
        $previousId = '';
        foreach ($orders as $order) {
            if ($previousId != $order->orderId) {
                if ($order->state == 0) {
                    $temp = array("orderId" => $order->orderId, "state0" => intval($order->nbr), "state1" => 0);
                } else {
                    $temp = array("orderId" => $order->orderId, "state0" => 0, "state1" => intval($order->nbr));
                }
                array_push($orders_array, $temp);
            } else {
                $orders_array[$index]['state1'] = $order->nbr;
                $index++;
            }
            $previousId = $order->orderId;
        }
        foreach ($orders_array as $order) {
            if ($order['state0'] >= 1 && $order['state1'] >= 1) {
                array_push($orders_update_1, $order['orderId']);
            }
            if ($order['state0'] == 0 && $order['state1'] != 0) {
                array_push($orders_update_2, $order['orderId']);
            }
        }
        // Update Orders 1
        if (sizeof($orders_update_1) != 0) {
            $orders_ids_1 = join(',', $orders_update_1);
            $sql2 = "UPDATE orders SET state = 1 WHERE orderId IN ($orders_ids_1)";
            $stmt = $db->prepare($sql2);
            $stmt->execute();
        }
        // Update Orders 2
        if (sizeof($orders_update_2) != 0) {
            $orders_ids_2 = join(',', $orders_update_2);
            $sql3 = "UPDATE orders SET state = 2 WHERE orderId IN ($orders_ids_2)";
            $stmt = $db->prepare($sql3);
            $stmt->execute();
        }
        echo '{"notice": {"text": "Article Updated"}}';
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Load Basket Favorite
$app->POST('/basketLoading/{orderId}/{clientId}', function (Request $request, Response $response) {
    $orderId = $request->getAttribute('orderId');
    $clientId = $request->getAttribute('clientId');
    $sql = "SELECT designation, quantity FROM articles WHERE articles.orderId = $orderId";
    $sql2 = "SELECT * FROM products LEFT OUTER JOIN providers ON products.providerId = providers.providerId WHERE products.designation = :designation";
    $sql3 = "SELECT packagingType, vatCode FROM customs WHERE productId = :productId";
    $sql4 = "SELECT specialPrice FROM prices WHERE productId = :productId AND clientId = $clientId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $session = new \Slim\Session();
        $result = array();
        $sessionBasket = array();
        while ($articles = $stmt->fetch(PDO::FETCH_OBJ)) {
            $basket = array();
            $stmt2 = $db->prepare($sql2);
            $stmt2->bindParam(':designation', $articles->designation);
            $stmt2->execute();
            $product = $stmt2->fetch(PDO::FETCH_OBJ);
            $stmt2 = $db->prepare($sql3);
            $stmt2->bindParam(':productId', $product->productId);
            $stmt2->execute();
            $custom = $stmt2->fetch(PDO::FETCH_OBJ);
            $stmt2 = $db->prepare($sql4);
            $stmt2->bindParam(':productId', $product->productId);
            $stmt2->execute();
            $price = $stmt2->fetch(PDO::FETCH_OBJ);
            $count = $stmt2->rowCount();
            if($product->isActive == 1)  {
            $basket['productId'] = $product->productId;
            $basket['designation'] = $product->designation;
            $basket['description'] = $product->description;
            if ($count == 0) {
                $basket['price'] = $product->price;
            } else {
                $basket['price'] = $price->specialPrice;
            }
            $basket['quantity'] = $articles->quantity;
            $basket['packagingType'] = $custom->packagingType;
            $basket['vatCode'] = $product->TVA;
            $basket['providerName'] = $product->name;

            array_push($sessionBasket,$basket); 
            }

        }
        $session->set('basket',$sessionBasket);
        $result['basket'] = $sessionBasket;
        $db = null;
        echo json_encode($result);
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Add Product Basket
$app->POST('/basket/{productId}/{clientId}', function (Request $request, Response $response) {
    $productId = $request->getAttribute('productId');
    $clientId = $request->getAttribute('clientId');
	$quantity = $request->getParam('quantity');
    $sql = "SELECT * FROM products  LEFT OUTER JOIN providers ON products.providerId = providers.providerId WHERE products.productId = $productId";
    //$sql2 = "SELECT packagingType, vatCode FROM customs WHERE productId = $productId";
    $sql3 = "SELECT specialPrice FROM prices WHERE productId = $productId AND clientId = $clientId";
    try {
        // Get DB Object
        $db = new db();
        // Connect
        $db = $db->connect();
        $stmt = $db->query($sql);
        $product = $stmt->fetch(PDO::FETCH_OBJ);
        //$stmt = $db->query($sql2);
        //$custom = $stmt->fetch(PDO::FETCH_OBJ);
        $stmt = $db->query($sql3);
        $price = $stmt->fetch(PDO::FETCH_OBJ);
        $count = $stmt->rowCount();
        $result = array();
        $result['productId'] = $product->productId;
        $result['designation'] = $product->designation;
        $result['description'] = $product->description;
		$result['TVA'] = $product->TVA;
        if ($count == 0) {
            $result['price'] = $product->price;
        } else {
            $result['price'] = $price->specialPrice;
        }
        $result['quantity'] = $quantity;
        //$result['packagingType'] = $custom->packagingType;
        //$result['vatCode'] = $custom->vatCode;
        $result['providerName'] = $product->name;
		$session = new \Slim\Session();
		$sessionBasket = $session->basket;
        if ($sessionBasket == null) {
			$sessionBasket = array();
	    }
		else {
			$i = 0;
foreach ($sessionBasket as $article) {
	
    if($article['productId'] == $product->productId){
		break;
	} 
			$i++;
		
}
		}
		
		if($i < sizeOf($sessionBasket) && sizeOf($sessionBasket)>=1){
		$sessionBasket[$i]['quantity'] = $sessionBasket[$i]['quantity'] + $quantity ;
		}
		else{
			
		array_push($sessionBasket,$result);	
		
		}
		$session->set('basket',$sessionBasket);
		$result['basket'] = $sessionBasket;
		$db = null;
        echo json_encode($result);
		
    }
    catch(PDOException $e) {
        echo '{"error": {"text": "' . $e->getMessage() . '"}}';
    }
});
// Edit Product Basket
$app->post('/basket/edit', function (Request $request, Response $response) {
    $productId = $request->getParam('productId');
    $quantity = $request->getParam('quantity');
 	$session = new \Slim\Session();
	$sessionBasket = $session->basket;
	$i = 0;

	foreach ($sessionBasket as $article) {
		if($article['productId'] == $productId){
			break;
		}
		$i++;
		
	}	
	if($quantity == 0){
			$test = array();
	foreach ($sessionBasket as $article) {
		if($article['productId'] != $productId){
			array_push($test,$article);
		}
		$sessionBasket = $test;
	}	
	} else {
		$sessionBasket[$i]['quantity'] = $quantity;		
	}
	$session->set('basket',$sessionBasket);
			  echo json_encode($sessionBasket);
});


// --- Routes pour la gestion de la livraison ---

/**
 * Vérifie la disponibilité de la livraison pour un code postal donné.
 * Retourne le jour de livraison et les créneaux horaires disponibles.
 */
$app->get('/api/delivery/check-availability/{postalCode}', function (Request $request, Response $response, array $args) {
    $postalCode = $args['postalCode'];
    // Horaires fixes pour la livraison
    $availableTimeSlots = ['16h-17h', '17h-18h', '18h-19h', '19h-20h'];

    try {
        $db = $this->get('db'); // Récupérer votre connexion PDO depuis le conteneur DI

        $stmt = $db->prepare("SELECT delivery_day_fr FROM delivery_zones WHERE postal_code = :postal_code AND is_active = 1");
        $stmt->bindParam(':postal_code', $postalCode);
        $stmt->execute();
        $deliveryZone = $stmt->fetch(PDO::FETCH_ASSOC);

        if ($deliveryZone) {
            $data = [
                'is_deliverable' => true,
                'postal_code' => $postalCode,
                'delivery_day_fr' => $deliveryZone['delivery_day_fr'],
                'available_time_slots' => $availableTimeSlots
            ];
            $response->getBody()->write(json_encode($data));
            return $response->withHeader('Content-Type', 'application/json')->withStatus(200);
        } else {
            $data = [
                'is_deliverable' => false,
                'postal_code' => $postalCode,
                'message' => 'Désolé, nous ne livrons pas encore dans ce secteur.'
            ];
            $response->getBody()->write(json_encode($data));
            return $response->withHeader('Content-Type', 'application/json')->withStatus(404); // Not Found, ou 200 avec is_deliverable: false
        }
    } catch (PDOException $e) {
        $error = ["error" => ["text" => $e->getMessage()]];
        $response->getBody()->write(json_encode($error));
        return $response->withHeader('Content-Type', 'application/json')->withStatus(500);
    }
});


// --- Routes pour le paiement Stripe et la création de commande ---

/**
 * Crée une intention de paiement Stripe.
 * Prend le contenu du panier (ou l'ID de la commande en attente) pour calculer le montant.
 */
$app->post('/api/payment/create-payment-intent', function (Request $request, Response $response) {
    $data = $request->getParsedBody();
    // $items = $data['items'] ?? []; // Liste des produits du panier avec id, quantité
    $orderId = $data['orderId'] ?? null; // Si une commande a déjà été pré-créée

    // TODO: Récupérer les détails de la commande (ou calculer le total à partir des items)
    // Exemple de calcul du montant (à adapter IMPÉRATIVEMENT avec votre logique métier)
    // $amountInCents = 0; // Calculez le montant total en centimes (ex: 10.00 EUR = 1000)
    // $currency = 'eur';

    // Exemple: si vous avez un orderId, récupérez la commande et son montant
    if (!$orderId) {
         $response->getBody()->write(json_encode(['error' => 'orderId manquant']));
         return $response->withHeader('Content-Type', 'application/json')->withStatus(400);
    }

    try {
        $db = $this->get('db');
        $stmt = $db->prepare("SELECT grand_total FROM orders WHERE orderId = :orderId");
        $stmt->bindParam(':orderId', $orderId);
        $stmt->execute();
        $order = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$order || $order['grand_total'] <= 0) {
            $response->getBody()->write(json_encode(['error' => 'Commande invalide ou montant nul.']));
            return $response->withHeader('Content-Type', 'application/json')->withStatus(400);
        }

        $amountInCents = (int) ($order['grand_total'] * 100); // Convertir en centimes
        $currency = 'eur';


        \Stripe\Stripe::setApiKey('sk_test_VOTRE_CLE_SECRETE_STRIPE'); // Assurez-vous que la clé est bien configurée

        $paymentIntent = \Stripe\PaymentIntent::create([
            'amount' => $amountInCents,
            'currency' => $currency,
            'automatic_payment_methods' => [
                'enabled' => true,
            ],
            'metadata' => [ // Utile pour lier le paiement à votre commande
                'order_id' => $orderId
            ]
        ]);

        // Mettre à jour la commande avec l'ID de l'intention de paiement
        $updateStmt = $db->prepare("UPDATE orders SET stripe_payment_intent_id = :payment_intent_id WHERE orderId = :orderId");
        $updateStmt->bindParam(':payment_intent_id', $paymentIntent->id);
        $updateStmt->bindParam(':orderId', $orderId);
        $updateStmt->execute();


        $output = [
            'clientSecret' => $paymentIntent->client_secret,
            'paymentIntentId' => $paymentIntent->id,
        ];

        $response->getBody()->write(json_encode($output));
        return $response->withHeader('Content-Type', 'application/json')->withStatus(200);

    } catch (\Stripe\Exception\ApiErrorException $e) {
        $error = ["error" => ["text" => "Erreur Stripe: " . $e->getMessage()]];
        $response->getBody()->write(json_encode($error));
        return $response->withHeader('Content-Type', 'application/json')->withStatus(500);
    } catch (PDOException $e) {
        $error = ["error" => ["text" => "Erreur BD: " . $e->getMessage()]];
        $response->getBody()->write(json_encode($error));
        return $response->withHeader('Content-Type', 'application/json')->withStatus(500);
    }
});

/**
 * Route pour créer/mettre à jour une commande avant le paiement.
 * Cette route serait appelée lorsque l'utilisateur valide son adresse et son créneau de livraison,
 * AVANT d'être redirigé vers le paiement Stripe.
 * Elle crée une commande avec un statut "en attente de paiement".
 */
$app->post('/api/orders/initiate', function (Request $request, Response $response) {
    $data = $request->getParsedBody();

    // Récupération des données (à valider rigoureusement)
    $clientId = $data['clientId'] ?? null; // Assurez-vous que le client est connecté et récupérez son ID
    // $cartItems = $data['cartItems'] ?? []; // Les articles du panier

    $deliveryDetails = $data['delivery'] ?? [];
    $delivery_address_line1 = $deliveryDetails['address_line1'] ?? null;
    $delivery_postal_code = $deliveryDetails['postal_code'] ?? null;
    $delivery_city = $deliveryDetails['city'] ?? null;
    $delivery_country = $deliveryDetails['country'] ?? 'France'; // Valeur par défaut
    $delivery_slot = $deliveryDetails['slot'] ?? null;
    $delivery_instructions = $deliveryDetails['instructions'] ?? null;

    // TODO: Valider les données (clientId, cartItems, détails de livraison)
    if (!$clientId || empty($delivery_address_line1) || empty($delivery_postal_code) || empty($delivery_city) || empty($delivery_slot)) {
        $response->getBody()->write(json_encode(['error' => 'Données de commande incomplètes.']));
        return $response->withHeader('Content-Type', 'application/json')->withStatus(400);
    }

    // TODO: Calculer le montant total des produits (`total` dans la table orders)
    // $totalProductsAmount = 0;
    // foreach ($cartItems as $item) {
    //    // Récupérer le prix du produit depuis la DB et calculer le sous-total
    // }

    // TODO: Calculer les frais de livraison (`delivery_fee`) si applicable
    // $deliveryFee = 0.00; // Logique pour les frais de livraison

    try {
        $db = $this->get('db');
        $db->beginTransaction();

        // Exemple simplifié de calcul du total (vous devez implémenter la logique de panier)
        // Pour cet exemple, on suppose que le total des produits est passé ou calculé ici.
        $totalProductsAmount = $data['total_products_amount'] ?? 0; // A remplacer par un vrai calcul
        $deliveryFee = $data['delivery_fee'] ?? 0; // A remplacer par un vrai calcul

        if ($totalProductsAmount <= 0) {
             $response->getBody()->write(json_encode(['error' => 'Le montant des produits ne peut être nul ou négatif.']));
             return $response->withHeader('Content-Type', 'application/json')->withStatus(400);
        }

        $sql = "INSERT INTO orders (clientId, street, postalCode, city, date, total, delivery_fee,
                    delivery_address_line1, delivery_postal_code, delivery_city, delivery_country, delivery_slot, delivery_instructions,
                    order_status, payment_status)
                VALUES (:clientId, :street, :postalCode, :city, NOW(), :total, :delivery_fee,
                    :d_address1, :d_postalCode, :d_city, :d_country, :d_slot, :d_instructions,
                    0, 'pending')"; // order_status = 0 (En attente paiement)

        $stmt = $db->prepare($sql);

        // Ces champs correspondent à l'adresse de FACTURATION du client, à récupérer depuis la table `clients`
        // Vous devrez peut-être ajuster cela si l'adresse de facturation peut être différente.
        $clientInfoStmt = $db->prepare("SELECT street, postalCode, city FROM clients WHERE clientId = :clientId");
        $clientInfoStmt->bindParam(':clientId', $clientId);
        $clientInfoStmt->execute();
        $clientInfo = $clientInfoStmt->fetch(PDO::FETCH_ASSOC);

        if (!$clientInfo) {
            $db->rollBack();
            $response->getBody()->write(json_encode(['error' => 'Client non trouvé.']));
            return $response->withHeader('Content-Type', 'application/json')->withStatus(404);
        }

        $stmt->bindParam(':clientId', $clientId);
        $stmt->bindParam(':street', $clientInfo['street']); // Adresse de facturation principale
        $stmt->bindParam(':postalCode', $clientInfo['postalCode']); // Adresse de facturation principale
        $stmt->bindParam(':city', $clientInfo['city']); // Adresse de facturation principale

        $stmt->bindParam(':total', $totalProductsAmount);
        $stmt->bindParam(':delivery_fee', $deliveryFee);

        $stmt->bindParam(':d_address1', $delivery_address_line1);
        $stmt->bindParam(':d_postalCode', $delivery_postal_code);
        $stmt->bindParam(':d_city', $delivery_city);
        $stmt->bindParam(':d_country', $delivery_country);
        $stmt->bindParam(':d_slot', $delivery_slot);
        $stmt->bindParam(':d_instructions', $delivery_instructions);

        $stmt->execute();
        $orderId = $db->lastInsertId();

        // TODO: Insérer les articles de la commande (order_items) dans la table `articles`
        // foreach ($cartItems as $item) {
        //   $articleSql = "INSERT INTO articles (orderId, designation, price, tva, quantity, packagingType, state)
        //                  VALUES (:orderId, :designation, :price, :tva, :quantity, :packagingType, 1)"; // state = 1 (actif/commandé)
        //   $articleStmt = $db->prepare($articleSql);
        //   $articleStmt->bindParam(':orderId', $orderId);
        //   // ... lier les autres paramètres de l'article ...
        //   $articleStmt->execute();
        // }

        $db->commit();

        $response->getBody()->write(json_encode([
            'message' => 'Commande initiée avec succès.',
            'orderId' => $orderId
        ]));
        return $response->withHeader('Content-Type', 'application/json')->withStatus(201); // 201 Created

    } catch (PDOException $e) {
        if ($db->inTransaction()) {
            $db->rollBack();
        }
        $error = ["error" => ["text" => "Erreur BD: " . $e->getMessage()]];
        $response->getBody()->write(json_encode($error));
        return $response->withHeader('Content-Type', 'application/json')->withStatus(500);
    }
});


/**
 * Webhook Stripe pour gérer les événements de paiement (ex: payment_intent.succeeded).
 * C'est la méthode RECOMMANDÉE pour confirmer un paiement de manière fiable.
 */
$app->post('/api/payment/webhook-stripe', function (Request $request, Response $response) {
    // Assurez-vous d'avoir configuré votre endpoint de webhook dans le dashboard Stripe
    // et de vérifier la signature de l'événement pour la sécurité.
    $payload = $request->getBody();
    // Remplacez par votre clé secrète de signature de webhook (whsec_...)
    $sig_header = $_SERVER['HTTP_STRIPE_SIGNATURE'] ?? null;
    $endpoint_secret = 'whsec_VOTRE_SECRET_DE_WEBHOOK_STRIPE';
    $event = null;

    try {
        $event = \Stripe\Webhook::constructEvent(
            $payload, $sig_header, $endpoint_secret
        );
    } catch(\UnexpectedValueException $e) {
        // Invalid payload
        return $response->withStatus(400);
    } catch(\Stripe\Exception\SignatureVerificationException $e) {
        // Invalid signature
        return $response->withStatus(400);
    }

    // Gérer l'événement
    if ($event->type == 'payment_intent.succeeded') {
        $paymentIntent = $event->data->object; // Contient l'objet PaymentIntent

        $orderId = $paymentIntent->metadata->order_id ?? null;
        $stripePaymentIntentId = $paymentIntent->id;

        if ($orderId) {
            try {
                $db = $this->get('db');
                // Mettre à jour le statut de la commande et le statut du paiement
                $sql = "UPDATE orders
                        SET order_status = 1, -- 1: Payée/En préparation
                            payment_status = 'succeeded',
                            stripe_payment_intent_id = :stripe_pi_id -- S'assurer qu'il est bien enregistré
                        WHERE orderId = :orderId AND (stripe_payment_intent_id = :stripe_pi_id OR stripe_payment_intent_id IS NULL)";
                        // La condition sur stripe_payment_intent_id est une sécurité supplémentaire

                $stmt = $db->prepare($sql);
                $stmt->bindParam(':stripe_pi_id', $stripePaymentIntentId);
                $stmt->bindParam(':orderId', $orderId);
                $stmt->execute();

                if ($stmt->rowCount() > 0) {
                    // TODO: Logique supplémentaire post-paiement réussi
                    // (ex: envoyer email de confirmation, notifier l'admin, décrémenter les stocks)
                } else {
                    // La commande n'a pas été trouvée ou déjà mise à jour, logguer cette situation
                }

            } catch (PDOException $e) {
                // Logguer l'erreur, mais répondre 200 à Stripe pour éviter les re-tentatives inutiles sur cette erreur.
                // Vous devrez gérer manuellement cette commande plus tard.
                error_log("Erreur BD lors de la mise à jour de la commande via webhook Stripe: " . $e->getMessage());
            }
        } else {
            // order_id non trouvé dans les métadonnées, logguer cette situation
             error_log("Webhook Stripe: order_id manquant dans les métadonnées pour payment_intent " . $paymentIntent->id);
        }

    } else if ($event->type == 'payment_intent.payment_failed') {
        $paymentIntent = $event->data->object;
        $orderId = $paymentIntent->metadata->order_id ?? null;
        $stripePaymentIntentId = $paymentIntent->id;

        if ($orderId) {
             try {
                $db = $this->get('db');
                $sql = "UPDATE orders
                        SET order_status = 0, -- 0: En attente paiement (ou un statut d'échec spécifique)
                            payment_status = 'failed'
                        WHERE orderId = :orderId AND stripe_payment_intent_id = :stripe_pi_id";
                $stmt = $db->prepare($sql);
                $stmt->bindParam(':stripe_pi_id', $stripePaymentIntentId);
                $stmt->bindParam(':orderId', $orderId);
                $stmt->execute();
             } catch (PDOException $e) {
                error_log("Erreur BD lors de la mise à jour (échec paiement) de la commande via webhook Stripe: " . $e->getMessage());
             }
        }
    } else {
        // Gérer d'autres types d'événements si nécessaire
        error_log('Événement Stripe non géré reçu: ' . $event->type);
    }

    return $response->withStatus(200); // Toujours retourner 200 à Stripe pour les webhooks bien reçus
});
