router.post('/inventory/stocks/add/(:id)', authenticationMiddleware(), function(req, res, next) {
    const db = require('../db.js')
    let sql = `SELECT product_slug FROM inventory_tbl WHERE product_no = ?;`

    db.query(sql, [req.params.id], (error, results, fields) => {
        if (error) throw error
        let promises = []

        var productno = req.params.id
        var cog = req.body.cog
        var productslug = results[0].product_slug
        var sizeslug = req.body.sizeslug
        var sizename = req.body.sizename
        var initialstock = req.body.initialstock
        var stock = []
        var rowinserted = 0
        var pastIds = []
        var batch_id = []

        for (var x = 0; x < sizeslug.length; x++) {
            var productsku = productslug + sizeslug[x]
            var slug = sizeslug[x]
            var name = sizename[x]
            var initstock = initialstock[x]
            if (sizeslug != '') {
                stock.push({
                    product_sku: productsku,
                    product_no: productno,
                    size_slug: slug,
                    size_name: name,
                    total_stock: initstock,
                    available_stock: initstock,
                    reserved_stock: '0'
                })
            }
        }

        promises.push(new Promise((resolve, reject) => {
            for (var i = 0; i < stock.length; i++) {
                let sql = `INSERT INTO product_tbl(product_sku, product_no, product_sizeslug, product_sizename, total_stock, available_stock, reserved_stock) VALUES (?, ?, ?, ?, ?, ?, ?);`
                db.query(sql, [stock[i].product_sku, req.params.id, stock[i].size_slug, stock[i].size_name, stock[i].total_stock, stock[i].available_stock, stock[i].reserved_stock], (error, results, fields) => {
                    if(error) reject(error)

                    let sql = `INSERT INTO stocks_tbl(product_sku, product_no, production_date, batch_cog, initial_stock, stock_left) VALUES (?, ?, CURRENT_DATE, ?, ?, ?);`
                    db.query(sql, [stock[i].product_sku, req.params.id, cog, stock[i].total_stock, stock[i].available_stock], (error, results, fields) => {
                        if(error) reject(error)

                        db.query(`SELECT LAST_INSERT_ID() AS id;`, (error, results, fields) => {
                            if(error) reject(error)

                            batch_id[i] = results[0].id
                        })
                    })
                })
                pastIds[i] = stock[i].product_sku
                rowinserted = rowinserted + 1
            }
        }))

        promises.push(new Promise((resolve, reject) => {
            if (rowinserted == sizeslug.length) {
                let sql = `SELECT SUM(total_stock) as total_stock, SUM(available_stock) as available_stock, SUM(reserved_stock) as reserved_stock FROM product_tbl WHERE product_no = ?`

                db.query(sql, [req.params.id], (error, results, fields) => {
                    if (error) throw error

                    let sql = `UPDATE inventory_tbl SET total_stock = ?, available_stock = ?, reserved_stock = ? WHERE product_no = ?`

                    db.query(sql, [results[0].total_stock, results[0].available_stock, results[0].reserved_stock, req.params.id], (error, results, fields) => {
                        if (error) throw error
                    })
                })
            }
        }))

        promises.push(new Promise((resolve, reject) => {
            if (rowinserted != sizeslug.length) {
                for (x = 0; x < rowinserted; x++) {
                    let sql = `DELETE FROM product_tbl WHERE product_sku = ?`
                    db.query(sql, [pastIds[x]], (error, results, fields) => {
                        if (error) throw error
                        let sql = `DELETE FROM stocks_tbl WHERE batch_no = ?`
                        db.query(sql, [batch_id[x]], (error, results, fields) => {
                            if (error) throw error
                        })
                    })
                }
            }
        }))

        Promise.all(promises).then(results => {
            if (rowinserted != sizeslug.length) {
                req.flash('error', error)

                res.redirect('/admin/inventory')
            } else {
                req.flash('success', 'Data added successfully!')
                res.redirect('/admin/inventory')
            }
        })
    })
})