var express = require('express');
var router = express.Router();

// ==================================================
// Route to list all products on the catalog
// URL: localhost:3039/catalog
// ==================================================
router.get('/', function(req, res, next) {
    let query = "SELECT product_id, productname, prodimage, description, saleprice, stock FROM product";
    db.all(query, (err, result) => {
        if (err) {
            console.log(err);
            res.render('error', {error: err});
        } else {
            res.render('catalog', {allrecs: result});
        }
    });
});

// ==================================================
// Route to add an item to the cart
// URL: localhost:3039/catalog/add
// ==================================================
router.post('/add', function(req, res, next) {
    if (typeof req.session.cart !== 'undefined' && req.session.cart) {
        if (req.session.cart.includes(req.body.product_id)) {
            // Item Exists in Basket - Increase Quantity
            var n = req.session.cart.indexOf(req.body.product_id);
            req.session.qty[n] = parseInt(req.session.qty[n]) + parseInt(req.body.qty);
        } else {
            // Item Being Added First Time
            req.session.cart.push(req.body.product_id);
            req.session.qty.push(req.body.qty);
        }
    } else {
        var cart = [];
        cart.push(req.body.product_id);
        req.session.cart = cart;
        var qty = [];
        qty.push(req.body.qty);
        req.session.qty = qty;
    }
    res.redirect('/catalog/cart');
});

function cartIsEmpty(req) {
    return (!Array.isArray(req.session.cart) || !req.session.cart.length);
}

// ==================================================
// Route to show shopping cart
// URL: localhost:3039/catalog/cart
// ==================================================
router.get('/cart', function(req, res, next) {
    if (cartIsEmpty(req)) {
        res.render('cart', {cartitems: 0});
    } else {
        const placeholders = req.session.cart.map(() => '?').join(',');
        let query = `SELECT product_id, productname, prodimage, saleprice, stock FROM product WHERE product_id IN (${placeholders})`;
        console.log(req.session.cart);
        db.all(query, req.session.cart, (err, result) => {
            if (err) {
                console.log(err);
                res.render('error', {error: err});
            } else {
                res.render('cart', {cartitems: result, qtys: req.session.qty});
            }
        });
    }
});

// ==================================================
// Route to remove an item from the cart
// URL: localhost:3039/catalog/remove
// ==================================================
router.post('/remove', function(req, res, next) {
    // Find the element index of the auto_id that needs to be removed
    var n = req.session.cart.indexOf(req.body.product_id);
    
    // Remove element from cart and quantity arrays
    req.session.cart.splice(n, 1);
    req.session.qty.splice(n, 1);
    res.redirect('/catalog/cart');
});

function checkoutHelper(order_id, index) {
    if (index < req.session.cart.length) {
        let cartitem = req.session.cart[index];
        let insertquery = "INSERT INTO orderdetail(order_id, product_id, saleprice, qty) VALUES (?, ?, (SELECT saleprice FROM product WHERE product_id = ?), ?);";
        db.run(insertquery, [order_id, cartitem, cartitem, req.session.qty[index]], (err) => {
            if (err) {
                console.log(err);
                res.render('error', {error: err});
            } else {
                let updateQuery = "UPDATE product SET stock = stock - ? WHERE product_id = ?;";
                db.run(updateQuery, [req.session.qty[index], cartitem], (err) => {
                    if (err) {
                        console.log(err);
                        res.render('error', {error: err});
                    } else {
                        checkoutHelper(order_id, index + 1);
                    }
                });
            }
        });
    } else {

    }
}

function checkout(order_id) {
    checkoutHelper(order_id, 0);
}

// ==================================================
// Route save cart items to SALEORDER and ORDERDETAILS tables
// URL: localhost:3039/catalog/checkout
// ==================================================
router.get('/checkout', function(req, res, next) {
    // Check to make sure the customer has logged-in
    if (typeof req.session.customer_id !== 'undefined' && req.session.customer_id && !cartIsEmpty(req)) {
        db.serialize(() => {
            let hasError = false;
            let error = null;
            db.exec("BEGIN TRANSACTION", (err) => {
                if (err) {
                    hasError = true;
                    if (error == null) {
                        error = err;
                    }
                }
            });

            // Save SALEORDER Record:
            let insertquery = "INSERT INTO saleorder(customer_id, paymentstatus) VALUES (?, 1)";
            db.run(insertquery, [req.session.customer_id], function(err) {
                if (err) {
                    hasError = true;
                    if (error == null) {
                        error = err;
                    }
                } else {
                    // Obtain the order_id value of the newly created SALEORDER Record
                    console.log(this);
                    var order_id = this.lastID;
                    console.log(order_id);

                    let insertStmt = db.prepare("INSERT INTO orderdetail(order_id, product_id, saleprice, qty) VALUES (?, ?, (SELECT saleprice FROM product WHERE product_id = ?), ?)");
                    let updateStmt = db.prepare("UPDATE product SET stock = stock - ? WHERE product_id = ?");

                    // Save ORDERDETAIL Records
                    // There could be one or more items in the shopping cart
                    req.session.cart.forEach((cartitem, index) => {
                        // Perform ORDERDETAIL table insert
                        insertStmt.run(order_id, cartitem, cartitem, req.session.qty[index], (err) => {
                            if (err) {
                                hasError = true;
                                if (error == null) {
                                    error = err;
                                }
                            }
                        });
                        updateStmt.run(req.session.qty[index], cartitem, (err) => {
                            if (err) {
                                hasError = true;
                                if (error == null) {
                                    error = err;
                                }
                            }
                        });
                    });

                    insertStmt.finalize((err) => {
                        if (err) {
                            hasError = true;
                            if (error == null) {
                                error = err;
                            }
                        }
                    });
                    updateStmt.finalize((err) => {
                        if (err) {
                            hasError = true;
                            if (error == null) {
                                error = err;
                            }
                        } else {
                            if (hasError) {
                                console.log(err);
                                db.run("ROLLBACK");
                                res.render('error', {error: error});
                            } else {
                                db.run("COMMIT", (err) => {
                                    if (err) {
                                        console.log(err);
                                        db.run("ROLLBACK");
                                        res.render('error', {error: error});
                                    }
                                });
                    
                                // Empty out the items from the cart and quantity arrays
                                req.session.cart = [];
                                req.session.qty = [];
                    
                                // Display confirmation page
                                res.render('checkout', {ordernum: order_id});
                            }
                        }
                    });
                }
            });
        });
    } else {
        // Prompt customer to login
        res.redirect('/customer/login');
    }
});

module.exports = router;
