'use strict';
var fs = require('fs');
var path = require('path');
var express = require('express');
var bodyParser = require('body-parser');
var app = express();
var bcrypt = require('bcrypt');
const jwt = require('jsonwebtoken');
const cookieParser = require('cookie-parser');
const mysql = require('mysql2');

const jwtKey = 'my_secret_key';
const jwtExpirySeconds = 3000;

const con = mysql.createConnection({
    host: "istwebclass.org",
    user: "jbrow160_1UserMe",
    password: "nicesample55",
    database: "jbrow160_capstonedatabase"
});

con.connect(function (err) {
    if (err) throw err;
    console.log("Connected!");
});

app.set('port', (process.env.PORT || 3000));

app.use('/', express.static(path.join(__dirname, 'public')));
app.use(bodyParser.json());
app.use(cookieParser());
app.use(bodyParser.urlencoded({ extended: true }));

app.get('/', function (req, res) {
    res.sendFile(path.join(__dirname + "/public/index.html"));
});

app.get('/getloggedout/', function (req, res) {
    res.cookie('token', 2, { maxAge: 0 });
    res.cookie('custid', '', { maxAge: 0 });
    res.send({ redirect: '/backend/index.html' });
});

app.get('/getloggedin/', function (req, res) {

    var viewpage = 0;
    const validtoken = req.cookies.token;
    console.log('token new:', validtoken);
    var payload;
    
    if(!validtoken) {
        viewpage = 0;
        console.log("NVT");
    } else {
        try {
            payload = jwt.verify(validtoken, jwtKey);
            console.log('payload new:', payload.userid);
            viewpage = payload.userid;
        } catch (e) {
            if (e instanceof jwt.JsonWebTokenError) {
                viewpage = 0;
                console.log("NVT2");
            }
            viewpage = 0;
            console.log("NVT3");
        }
    }
    
    console.log("View: " + viewpage);
    
    return res.send(JSON.stringify(viewpage));

});

app.get('/getuserrole/', function (req, res) {

    var role = 0;
    var loggedin = 0;
    const validtoken = req.cookies.token;
    var payload;

    if (!validtoken) {
        role = 0;
        loggedin = 0;
    } else {
        try {
            payload = jwt.verify(validtoken, jwtKey);
            role = payload.role;
            loggedin = payload.userid;
        } catch (e) {
            role = 0;
            loggedin = 0;
        }
    }

    return res.send(JSON.stringify({ role: role, loggedin: loggedin }));
});



app.get('/getloggedincustomer/', function (req, res) {

    var custid = req.cookies.custid;

    if (!custid) {
        res.send(JSON.stringify([]));
        return;
    }

    var sqlsel = 'SELECT dbCustomerId FROM Customers WHERE dbCustomerId = ? AND dbIsActive = 1';
    var inserts = [custid];
    var sql = mysql.format(sqlsel, inserts);

    console.log(sql);

    con.query(sql, function (err, data) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify([]));
            return;
        }

        res.send(JSON.stringify(data));
    });
});



app.post('/loginemp/', function (req, res) {
    var useremail = req.body.useremail;
    var userpw = req.body.userpw;

    var sqlsel = 'select * from `User` where dbUserEmail = ? and dbIsActive = 1';
    var inserts = [useremail];
    var sql = mysql.format(sqlsel, inserts);
    console.log(sql);

    con.query(sql, function (err, data) {
        if (err) {
            console.error(err);
            res.send({ error: 'Login error' });
            return;
        }

        if (data.length > 0) {
            console.log("User name correct: ");
            var userid = data[0].dbUserId;
            var role = data[0].dbUserRole;

            bcrypt.compare(userpw, data[0].dbUserPassword, function (err, passwordCorrect) {
                if (err) {
                    console.error(err);
                    res.send({ error: 'Login error' });
                    return;
                } else if (!passwordCorrect) {
                    console.log("Password Incorrect");
                    res.send({ error: 'Incorrect user name or password!!' });
                    return;
                } else {
                    console.log("Password Correct");
                    const token = jwt.sign({ userid, role }, jwtKey, {
                        algorithm: 'HS256',
                        expiresIn: jwtExpirySeconds
                    });

                    res.cookie('token', token, { maxAge: jwtExpirySeconds * 1000 });
                    res.send({ redirect: '/backend/searchuser.html' });
                    return;
                }
            });
        } else {
            console.log("Incorrect user name or password!!");
            res.send({ error: 'Incorrect user name or password!!' });
            return;
        }
    });
});







/* =========================
        FRONT CUSTOMERS
========================= */

app.post('/frontorder', function (req, res) {

    var customerid = req.body.customerid;
    var ordertype = req.body.ordertype;
    var orderstatus = req.body.orderstatus;
    var menuitemid = req.body.menuitemid;
    var quantity = req.body.quantity;
    var pickup = req.body.pickup;

    if (!customerid || !ordertype || !orderstatus || !menuitemid || !quantity || !pickup) {
        res.send(JSON.stringify({ error: 'All order fields are required' }));
        return;
    }

    var sqlins = "INSERT INTO `Order` (dbCustomerId, dbOrderDate, dbOrderType, dbOrderStatus) " +
        "VALUES (?, STR_TO_DATE(CONCAT(CURDATE(), ' ', ?, ':00'), '%Y-%m-%d %H:%i:%s'), ?, ?)";

    var inserts = [customerid, pickup, ordertype, orderstatus];
    var sql = mysql.format(sqlins, inserts);

    console.log(sql);

    con.execute(sql, function (err, result) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }

        var orderid = result.insertId;

        var sqldetailins = 'INSERT INTO OrderDetails (dbOrderId, dbMenuItemId, dbOrderDetailQuantity) VALUES (?, ?, ?)';
        var detailinserts = [orderid, menuitemid, quantity];
        var sqldetail = mysql.format(sqldetailins, detailinserts);

        console.log(sqldetail);

        con.execute(sqldetail, function (detailerr, detailresult) {
            if (detailerr) {
                console.error(detailerr);
                res.send(JSON.stringify({ error: detailerr.toString() }));
                return;
            }

            console.log("1 front order record inserted");
            res.send(JSON.stringify({ success: "1 front order record inserted", data: detailresult }));
        });
    });
});


app.post('/frontcustomer', function (req, res) {
    var customername = req.body.customername;
    var customeremail = req.body.customeremail;
    var customerphone = req.body.customerphone;
    var customerpw = req.body.customerpw;
    var customermailer = req.body.customermailer;

    if (customermailer === '' || typeof customermailer === 'undefined') {
        customermailer = 0;
    }

    console.log("PW: " + customerpw);

    var saltRounds = 10;
    var theHashedPW = '';

    bcrypt.hash(customerpw, saltRounds, function (err, hashedPassword) {

        if (err) {
            console.log("Bad on encrypt");
            return;
        } else {

            theHashedPW = hashedPassword;
            console.log("Password Enc: " + theHashedPW);

            var sqlins = 'INSERT INTO Customers (dbCustomerName, dbCustomerEmail, dbCustomerPhone, dbCustomerPassword, dbCustomerMailer) VALUES (?, ?, ?, ?, ?)';
            var inserts = [customername, customeremail, customerphone, theHashedPW, customermailer];

            var sql = mysql.format(sqlins, inserts);

            con.execute(sql, function (err, result) {
                if (err) throw err;
                console.log("1 front customer record inserted");
                res.redirect('customerlogin.html');
                res.end();
            });
        }
    });
});


app.post('/logincust', function (req, res) {
    var customeremail = req.body.customeremail;
    var customerpw = req.body.customerpw;

    var sqlsel = 'select * from Customers where dbCustomerEmail = ? and dbIsActive = 1';
    var inserts = [customeremail];
    var sql = mysql.format(sqlsel, inserts);
    console.log(sql);

    con.query(sql, function (err, data) {
        if (err) {
            console.error(err);
            res.cookie('custid', '', { maxAge: 0 });
            res.send({ error: 'Login error' });
            return;
        }

        if (data.length > 0) {
            console.log("User name correct: ");
            console.log(data[0].dbCustomerId);

            bcrypt.compare(customerpw, data[0].dbCustomerPassword, function (err, passwordCorrect) {
                if (err) {
                    console.error(err);
                    res.cookie('custid', '', { maxAge: 0 });
                    res.send({ error: 'Login error' });
                    return;
                } else if (!passwordCorrect) {
                    console.log("Password Incorrect");
                    res.cookie('custid', '', { maxAge: 0 });
                    res.send({ error: 'Incorrect user name or password!!' });
                    return;
                } else {
                    console.log("Password Correct");
                    res.cookie('custid', data[0].dbCustomerId, { maxAge: jwtExpirySeconds * 1000 });
                    res.send({ redirect: '/order.html' });
                    return;
                }
            });
        } else {
            console.log("Incorrect user name or password!!");
            res.cookie('custid', '', { maxAge: 0 });
            res.send({ error: 'Incorrect user name or password!!' });
            return;
        }
    });
});


/* =========================
   DELETE ROUTES
========================= */

app.post('/deletesingleuser', function (req, res) {
    var deleteid = req.body.deleteid;
    var sqlsel = 'UPDATE `User` SET dbIsActive = 0 WHERE dbUserId = ?';
    var inserts = [deleteid];
    var sql = mysql.format(sqlsel, inserts);
    console.log(sql);
    con.execute(sql, function (err, result) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }
        console.log("1 user record deleted");
        res.send(JSON.stringify({ success: "1 user record deleted", data: result }));
    });
});

app.post('/deletesinglecustomer', function (req, res) {

    var deleteid = req.body.deleteid;

    var sqlsel = 'UPDATE Customers SET dbIsActive = 0 WHERE dbCustomerId = ?';
    var inserts = [deleteid];
    var sql = mysql.format(sqlsel, inserts);

    console.log(sql);

    con.execute(sql, function (err, result) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }
        console.log("1 customer record set inactive");
        res.send(JSON.stringify({ success: "1 customer record set inactive", data: result }));
    });
});

app.post('/deletesinglemenuitem', function (req, res) {
    var deleteid = req.body.deleteid;
    var sqlsel = 'UPDATE MenuItems SET dbIsActive = 0 WHERE dbMenuItemId = ?';
    var inserts = [deleteid];
    var sql = mysql.format(sqlsel, inserts);
    console.log(sql);
    con.execute(sql, function (err, result) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }
        console.log("1 menu item record deleted");
        res.send(JSON.stringify({ success: "1 menu item record deleted", data: result }));
    });
});

app.post('/deletesingleinventory', function (req, res) {
    var deleteid = req.body.deleteid;
    var sqlsel = 'UPDATE Inventory SET dbIsActive = 0 WHERE dbInventoryId = ?';
    var inserts = [deleteid];
    var sql = mysql.format(sqlsel, inserts);
    console.log(sql);
    con.execute(sql, function (err, result) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }
        console.log("1 inventory record deleted");
        res.send(JSON.stringify({ success: "1 inventory record deleted", data: result }));
    });
});

app.post('/deletesingleorder', function (req, res) {

    var deleteid = req.body.deleteid;

    var sqlsel = 'UPDATE `Order` SET dbIsActive = 0 WHERE dbOrderId = ?';
    var inserts = [deleteid];
    var sql = mysql.format(sqlsel, inserts);

    console.log(sql);

    con.execute(sql, function (err, result) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }
        console.log("1 order record deleted");
        res.send(JSON.stringify({ success: "1 order record deleted", data: result }));
    });
});

app.post('/deletesingleorderdetail', function (req, res) {
    var deleteid = req.body.deleteid;
    var sqlsel = 'UPDATE OrderDetails SET dbIsActive = 0 WHERE dbOrderDetailsId = ?';
    var inserts = [deleteid];
    var sql = mysql.format(sqlsel, inserts);
    con.execute(sql, function (err, result) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }
        console.log("1 order detail record deleted");
        res.send(JSON.stringify({ success: "1 order detail record deleted", data: result }));
    });
});

app.post('/deletesinglereservation', function (req, res) {
    var deleteid = req.body.deleteid;
    var sqlsel = 'UPDATE Reservations SET dbIsActive = 0 WHERE dbReservetionId = ?';
    var inserts = [deleteid];
    var sql = mysql.format(sqlsel, inserts);
    console.log(sql);
    con.execute(sql, function (err, result) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }
        console.log("1 reservation record deleted");
        res.send(JSON.stringify({ success: "1 reservation record deleted", data: result }));
    });
});

/* =========================
    CUSTOMERS
========================= */

app.get('/getcustomers/', function (req, res) {

    var customerid = req.query.customerid || '';
    var customername = req.query.customername || '';
    var customeremail = req.query.customeremail || '';
    var customerphone = req.query.customerphone || '';
    var customerpw = req.query.customerpw || '';
    var customermailer = req.query.customermailer || '';
    var customeractive = req.query.customeractive || '';

    var sqlsel = 'SELECT * FROM Customers WHERE dbCustomerId LIKE ? AND dbCustomerName LIKE ? AND dbCustomerEmail LIKE ? AND dbCustomerPhone LIKE ? AND dbCustomerPassword LIKE ?';

    var inserts = [
        '%' + customerid + '%',
        '%' + customername + '%',
        '%' + customeremail + '%',
        '%' + customerphone + '%',
        '%' + customerpw + '%'
    ];

    if (customermailer === '0' || customermailer === '1') {
        sqlsel += ' AND dbCustomerMailer = ?';
        inserts.push(customermailer);
    }

    if (customeractive === '0' || customeractive === '1') {
        sqlsel += ' AND dbIsActive = ?';
        inserts.push(customeractive);
    } else if (customeractive !== '2') {
        sqlsel += ' AND dbIsActive = 1';
    }

    var sql = mysql.format(sqlsel, inserts);

    console.log(sql);

    con.query(sql, function (err, data) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }
        res.send(JSON.stringify(data));
    });
});

app.get('/getcusttypes/', function (req, res) {

    var sqlsel = 'SELECT * FROM CustomerSearchTypes ORDER BY dbCustTypeId DESC';
    var sql = mysql.format(sqlsel);

    console.log(sql);

    con.query(sql, function (err, data) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }
        res.send(JSON.stringify(data));
    });
});

app.get('/getsinglecustomer/', function (req, res) {
 

    var customerid = req.query.upcustomerid;

    var sqlsel = 'SELECT * FROM Customers WHERE dbCustomerId = ? AND dbIsActive = 1';
    var inserts = [customerid];
    var sql = mysql.format(sqlsel, inserts);

    console.log(sql);

    con.query(sql, function (err, data) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }
        res.send(JSON.stringify(data));
    });
});

app.post('/customer', function (req, res) {
   

    var customername = req.body.customername;
    var customeremail = req.body.customeremail;
    var customerphone = req.body.customerphone;
    var customerpw = req.body.customerpw;
    var customermailer = req.body.customermailer;

    if (customermailer === '' || typeof customermailer === 'undefined') {
        customermailer = 0;
    }

    bcrypt.hash(customerpw, 10, function (err, hashedPassword) {
        if (err) {
            console.log("Bad on encrypt");
            res.send(JSON.stringify({ error: 'Password encryption failed' }));
            return;
        } else {
            var sqlins = 'INSERT INTO Customers (dbCustomerName, dbCustomerEmail, dbCustomerPhone, dbCustomerPassword, dbCustomerMailer) VALUES (?, ?, ?, ?, ?)';
            var inserts = [customername, customeremail, customerphone, hashedPassword, customermailer];
            var sql = mysql.format(sqlins, inserts);

            console.log(sql);

            con.execute(sql, function (err, result) {
                if (err) {
                    console.error(err);
                    res.send(JSON.stringify({ error: err.toString() }));
                    return;
                }
                console.log("1 customer record inserted");
                res.send(JSON.stringify({ success: "1 customer record inserted", data: result }));
            });
        }
    });
});


app.get('/getcustomerslist/', function (req, res) {

    var sqlsel = 'SELECT * FROM Customers WHERE dbIsActive = 1 ORDER BY dbCustomerName';
    var sql = mysql.format(sqlsel);

    console.log(sql);

    con.query(sql, function (err, data) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }
        res.send(JSON.stringify(data));
    });
});



app.post('/updatesinglecustomer', function (req, res) {
   

    var customerid = req.body.upcustomerid;
    var customername = req.body.upcustomername;
    var customeremail = req.body.upcustomeremail;
    var customerphone = req.body.upcustomerphone;
    var customerpw = req.body.upcustomerpw;
    var customermailer = req.body.upcustomermailer;

    if (customermailer === '' || typeof customermailer === 'undefined') {
        customermailer = 0;
    }

    if (customerpw && customerpw.trim() !== '') {
        bcrypt.hash(customerpw, 10, function (err, hashedPassword) {
            if (err) {
                console.log("Bad on encrypt");
                res.send(JSON.stringify({ error: 'Password encryption failed' }));
                return;
            }

            var sqlins = 'UPDATE Customers SET dbCustomerName = ?, dbCustomerEmail = ?, dbCustomerPhone = ?, dbCustomerPassword = ?, dbCustomerMailer = ? WHERE dbCustomerId = ?';
            var inserts = [customername, customeremail, customerphone, hashedPassword, customermailer, customerid];
            var sql = mysql.format(sqlins, inserts);

            console.log(sql);

            con.execute(sql, function (err, result) {
                if (err) {
                    console.error(err);
                    res.send(JSON.stringify({ error: err.toString() }));
                    return;
                }
                console.log("1 customer record updated");
                res.send(JSON.stringify({ success: "1 customer record updated", data: result }));
            });
        });
    } else {
        var sqlins = 'UPDATE Customers SET dbCustomerName = ?, dbCustomerEmail = ?, dbCustomerPhone = ?, dbCustomerMailer = ? WHERE dbCustomerId = ?';
        var inserts = [customername, customeremail, customerphone, customermailer, customerid];
        var sql = mysql.format(sqlins, inserts);

        console.log(sql);

        con.execute(sql, function (err, result) {
            if (err) {
                console.error(err);
                res.send(JSON.stringify({ error: err.toString() }));
                return;
            }
            console.log("1 customer record updated");
            res.send(JSON.stringify({ success: "1 customer record updated", data: result }));
        });
    }
});






/* =========================
   USER 
========================= */    


app.get('/getusers/', function (req, res) {

    var userid = req.query.userid || '';
    var username = req.query.username || '';
    var useremail = req.query.useremail || '';
    var userrole = req.query.userrole || '';
    var useractive = req.query.useractive || '';

    var sqlsel = 'SELECT * FROM `User` WHERE dbUserId LIKE ? AND dbUsername LIKE ? AND dbUserEmail LIKE ?';

    var inserts = [
        '%' + userid + '%',
        '%' + username + '%',
        '%' + useremail + '%'
    ];

    if (userrole === '1' || userrole === '2') {
        sqlsel += ' AND dbUserRole = ?';
        inserts.push(userrole);
    }

    if (useractive === '0' || useractive === '1') {
        sqlsel += ' AND dbIsActive = ?';
        inserts.push(useractive);
    } else if (useractive !== '2') {
        sqlsel += ' AND dbIsActive = 1';
    }

    var sql = mysql.format(sqlsel, inserts);

    console.log(sql);

    con.query(sql, function (err, data) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }
        res.send(JSON.stringify(data));
    });
});

app.get('/getuserroles/', function (req, res) {

    var sqlsel = 'SELECT * FROM UserRoles ORDER BY dbUserRoleId';
    var sql = mysql.format(sqlsel);

    console.log(sql);

    con.query(sql, function (err, data) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }
        res.send(JSON.stringify(data));
    });
});

app.get('/getsingleuser/', function (req, res) {

    var userid = req.query.upuserid;

    var sqlsel = 'SELECT * FROM `User` WHERE dbUserId = ? AND dbIsActive = 1';
    var inserts = [userid];
    var sql = mysql.format(sqlsel, inserts);

    console.log(sql);

    con.query(sql, function (err, data) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }
        res.send(JSON.stringify(data));
    });
});

app.post('/user', function (req, res) {

    var username = req.body.username;
    var password = req.body.password;
    var email = req.body.email;
    var role = req.body.role;

    bcrypt.hash(password, 10, function (err, hashedPassword) {
        if (err) {
            console.log("Bad on encrypt");
            return;
        } else {
            var sqlins = 'INSERT INTO `User` (dbUsername, dbUserPassword, dbUserEmail, dbUserRole) VALUES (?, ?, ?, ?)';
            var inserts = [username, hashedPassword, email, role];
            var sql = mysql.format(sqlins, inserts);

            console.log(sql);

            con.execute(sql, function (err, result) {
                if (err) throw err;
                console.log("1 user record inserted");
                res.send(JSON.stringify({ success: "1 user record inserted", data: result }));
            });
        }
    });
});

app.post('/updatesingleuser', function (req, res) {

    var userid = req.body.upuserid;
    var username = req.body.upusername;
    var useremail = req.body.upuseremail;
    var userrole = req.body.upuserrole;

    var sqlins = 'UPDATE `User` SET dbUsername = ?, dbUserEmail = ?, dbUserRole = ? WHERE dbUserId = ?';
    var inserts = [username, useremail, userrole, userid];
    var sql = mysql.format(sqlins, inserts);

    console.log(sql);

    con.execute(sql, function (err, result) {
        if (err) throw err;
        console.log("1 user record updated");
        res.send(JSON.stringify({ success: "1 user record updated", data: result }));
    });
});

/* =========================
   MENU ITEMS
========================= */

app.get('/getmenuitems/', function (req, res) {

    var menuitemid = req.query.menuitemid || '';
    var menuname = req.query.menuname || '';
    var menudescription = req.query.menudescription || '';
    var menuprice = req.query.menuprice || '';

    var sqlsel = 'SELECT * FROM MenuItems WHERE dbIsActive = 1 AND dbMenuItemId LIKE ? AND dbMenuName LIKE ? AND dbMenuDescription LIKE ? AND dbMenuPrice LIKE ?';
    var inserts = ['%' + menuitemid + '%', '%' + menuname + '%', '%' + menudescription + '%', '%' + menuprice + '%'];
    var sql = mysql.format(sqlsel, inserts);

    console.log(sql);

    con.query(sql, function (err, data) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }
        res.send(JSON.stringify(data));
    });
});


app.get('/getmenuitemslist/', function (req, res) {

    var sqlsel = 'SELECT * FROM MenuItems WHERE dbIsActive = 1 ORDER BY dbMenuName';
    var sql = mysql.format(sqlsel);

    console.log(sql);

    con.query(sql, function (err, data) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }
        res.send(JSON.stringify(data));
    });
});


app.get('/getsinglemenuitem/', function (req, res) {

    var menuitemid = req.query.upmenuitemid;

    var sqlsel = 'SELECT * FROM MenuItems WHERE dbMenuItemId = ? AND dbIsActive = 1';
    var inserts = [menuitemid];
    var sql = mysql.format(sqlsel, inserts);

    console.log(sql);

    con.query(sql, function (err, data) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }
        res.send(JSON.stringify(data));
    });
});

app.post('/menuitem', function (req, res) {

    var menuname = req.body.menuname;
    var menudescription = req.body.menudescription;
    var menuprice = req.body.menuprice;

    var sqlins = 'INSERT INTO MenuItems (dbMenuName, dbMenuDescription, dbMenuPrice) VALUES (?, ?, ?)';
    var inserts = [menuname, menudescription, menuprice];
    var sql = mysql.format(sqlins, inserts);

    console.log(sql);

    con.execute(sql, function (err, result) {
        if (err) throw err;
        console.log("1 menu item record inserted");
        res.send(JSON.stringify({ success: "1 menu item record inserted", data: result }));
    });
});

app.post('/updatesinglemenuitem', function (req, res) {

    var menuitemid = req.body.upmenuitemid;
    var menuname = req.body.upmenuname;
    var menudescription = req.body.upmenudescription;
    var menuprice = req.body.upmenuprice;

    var sqlins = 'UPDATE MenuItems SET dbMenuName = ?, dbMenuDescription = ?, dbMenuPrice = ? WHERE dbMenuItemId = ?';
    var inserts = [menuname, menudescription, menuprice, menuitemid];
    var sql = mysql.format(sqlins, inserts);

    console.log(sql);

    con.execute(sql, function (err, result) {
        if (err) throw err;
        console.log("1 menu item record updated");
        res.send(JSON.stringify({ success: "1 menu item record updated", data: result }));
    });
});

/* =========================
   INVENTORY
========================= */

app.get('/getinventory/', function (req, res) {

    var inventoryid = req.query.inventoryid || '';
    var menuitemid = req.query.menuitemid || '';
    var quantityavailable = req.query.quantityavailable || '';

    var sqlsel = 'SELECT * FROM Inventory WHERE dbIsActive = 1 AND dbInventoryId LIKE ? AND dbMenuItemId LIKE ? AND dbQuantityAvailable LIKE ?';
    var inserts = ['%' + inventoryid + '%', '%' + menuitemid + '%', '%' + quantityavailable + '%'];
    var sql = mysql.format(sqlsel, inserts);

    console.log(sql);

    con.query(sql, function (err, data) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }
        res.send(JSON.stringify(data));
    });
});

app.get('/getsingleinventory/', function (req, res) {

    var inventoryid = req.query.upinventoryid;

    var sqlsel = 'SELECT * FROM Inventory WHERE dbInventoryId = ? AND dbIsActive = 1';
    var inserts = [inventoryid];
    var sql = mysql.format(sqlsel, inserts);

    console.log(sql);

    con.query(sql, function (err, data) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }
        res.send(JSON.stringify(data));
    });
});

app.post('/inventory', function (req, res) {

    var menuitemid = req.body.menuitemid;
    var quantityavailable = req.body.quantityavailable;

    var sqlins = 'INSERT INTO Inventory (dbMenuItemId, dbQuantityAvailable) VALUES (?, ?)';
    var inserts = [menuitemid, quantityavailable];
    var sql = mysql.format(sqlins, inserts);

    console.log(sql);

    con.execute(sql, function (err, result) {
        if (err) throw err;
        console.log("1 inventory record inserted");
        res.send(JSON.stringify({ success: "1 inventory record inserted", data: result }));
    });
});

app.post('/updatesingleinventory', function (req, res) {

    var inventoryid = req.body.upinventoryid;
    var menuitemid = req.body.upmenuitemid;
    var quantityavailable = req.body.upquantityavailable;

    var sqlins = 'UPDATE Inventory SET dbMenuItemId = ?, dbQuantityAvailable = ? WHERE dbInventoryId = ?';
    var inserts = [menuitemid, quantityavailable, inventoryid];
    var sql = mysql.format(sqlins, inserts);

    console.log(sql);

    con.execute(sql, function (err, result) {
        if (err) throw err;
        console.log("1 inventory record updated");
        res.send(JSON.stringify({ success: "1 inventory record updated", data: result }));
    });
});

/* =========================
   ORDERS
========================= */

app.get('/getorders/', function (req, res) {

    var orderid = req.query.orderid || '';
    var customerid = req.query.customerid || '';
    var orderdate = req.query.orderdate || '';
    var ordertype = req.query.ordertype || '';
    var orderstatus = req.query.orderstatus || '';

    var sqlsel = 'SELECT * FROM `Order` WHERE dbIsActive = 1 AND dbOrderId LIKE ? AND dbCustomerId LIKE ? AND dbOrderDate LIKE ? AND dbOrderType LIKE ? AND dbOrderStatus LIKE ?';
    var inserts = ['%' + orderid + '%', '%' + customerid + '%', '%' + orderdate + '%', '%' + ordertype + '%', '%' + orderstatus + '%'];
    var sql = mysql.format(sqlsel, inserts);

    console.log(sql);

    con.query(sql, function (err, data) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }
        res.send(JSON.stringify(data));
    });
});

app.get('/getsingleorder/', function (req, res) {

    var orderid = req.query.uporderid;

    var sqlsel = 'SELECT * FROM `Order` WHERE dbOrderId = ? AND dbIsActive = 1';
    var inserts = [orderid];
    var sql = mysql.format(sqlsel, inserts);

    console.log(sql);

    con.query(sql, function (err, data) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }
        res.send(JSON.stringify(data));
    });
});

app.post('/order', function (req, res) {

    var customerid = req.body.customerid;
    var ordertype = req.body.ordertype;
    var orderstatus = req.body.orderstatus;

    var sqlins = 'INSERT INTO `Order` (dbCustomerId, dbOrderDate, dbOrderType, dbOrderStatus) VALUES (?, NOW(), ?, ?)';
    var inserts = [customerid, ordertype, orderstatus];
    var sql = mysql.format(sqlins, inserts);

    console.log(sql);

    con.execute(sql, function (err, result) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }
        console.log("1 order record inserted");
        res.send(JSON.stringify({ success: "1 order record inserted", data: result }));
    });
});

app.post('/updatesingleorder', function (req, res) {

    var orderid = req.body.uporderid;
    var customerid = req.body.upcustomerid;
    var ordertype = req.body.upordertype;
    var orderstatus = req.body.uporderstatus;

    var sqlins = 'UPDATE `Order` SET dbCustomerId = ?, dbOrderType = ?, dbOrderStatus = ? WHERE dbOrderId = ?';
    var inserts = [customerid, ordertype, orderstatus, orderid];
    var sql = mysql.format(sqlins, inserts);

    console.log(sql);

    con.execute(sql, function (err, result) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }
        console.log("1 order record updated");
        res.send(JSON.stringify({ success: "1 order record updated", data: result }));
    });
});

/* =========================
   ORDER DETAILS
========================= */

app.get('/getorderdetails/', function (req, res) {

    var orderdetailsid = req.query.orderdetailsid || '';
    var orderid = req.query.orderid || '';
    var menuitemid = req.query.menuitemid || '';
    var orderdetailquantity = req.query.orderdetailquantity || '';

    var sqlsel = 'SELECT * FROM OrderDetails WHERE dbIsActive = 1 AND dbOrderDetailsId LIKE ? AND dbOrderId LIKE ? AND dbMenuItemId LIKE ? AND dbOrderDetailQuantity LIKE ?';
    var inserts = ['%' + orderdetailsid + '%', '%' + orderid + '%', '%' + menuitemid + '%', '%' + orderdetailquantity + '%'];
    var sql = mysql.format(sqlsel, inserts);

    console.log(sql);

    con.query(sql, function (err, data) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }
        res.send(JSON.stringify(data));
    });
});

app.get('/getsingleorderdetail/', function (req, res) {

    var orderdetailsid = req.query.uporderdetailsid;

    var sqlsel = 'SELECT * FROM OrderDetails WHERE dbOrderDetailsId = ? AND dbIsActive = 1';
    var inserts = [orderdetailsid];
    var sql = mysql.format(sqlsel, inserts);

    console.log(sql);

    con.query(sql, function (err, data) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }
        res.send(JSON.stringify(data));
    });
});

app.post('/orderdetails', function (req, res) {

    var orderid = req.body.orderid;
    var menuitemid = req.body.menuitemid;
    var orderdetailquantity = req.body.orderdetailquantity;

    var sqlins = 'INSERT INTO OrderDetails (dbOrderId, dbMenuItemId, dbOrderDetailQuantity) VALUES (?, ?, ?)';
    var inserts = [orderid, menuitemid, orderdetailquantity];
    var sql = mysql.format(sqlins, inserts);

    console.log(sql);

    con.execute(sql, function (err, result) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }
        console.log("1 order detail record inserted");
        res.send(JSON.stringify({ success: "1 order detail record inserted", data: result }));
    });
});

app.post('/updatesingleorderdetail', function (req, res) {

    var orderdetailsid = req.body.uporderdetailsid;
    var orderid = req.body.uporderid;
    var menuitemid = req.body.upmenuitemid;
    var orderdetailquantity = req.body.uporderdetailquantity;

    var sqlins = 'UPDATE OrderDetails SET dbOrderId = ?, dbMenuItemId = ?, dbOrderDetailQuantity = ? WHERE dbOrderDetailsId = ?';
    var inserts = [orderid, menuitemid, orderdetailquantity, orderdetailsid];
    var sql = mysql.format(sqlins, inserts);

    console.log(sql);

    con.execute(sql, function (err, result) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }
        console.log("1 order detail record updated");
        res.send(JSON.stringify({ success: "1 order detail record updated", data: result }));
    });
});

app.get('/getopenorders/', function (req, res) {

    var sqlsel = 'SELECT * FROM `Order` WHERE dbIsActive = 1 AND dbOrderStatus = ? ORDER BY dbOrderDate DESC';
    var inserts = ['Open'];
    var sql = mysql.format(sqlsel, inserts);

    console.log(sql);

    con.query(sql, function (err, data) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }
        res.send(JSON.stringify(data));
    });
});

app.get('/getpastorders/', function (req, res) {

    var sqlsel = 'SELECT * FROM `Order` WHERE dbIsActive = 1 AND dbOrderStatus <> ? ORDER BY dbOrderDate DESC';
    var inserts = ['Open'];
    var sql = mysql.format(sqlsel, inserts);

    console.log(sql);

    con.query(sql, function (err, data) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }
        res.send(JSON.stringify(data));
    });
});


/* =========================
   TRAINERS
========================= */

app.get('/gettrainers/', function (req, res) {

    var sqlsel = 'SELECT * FROM `User` WHERE dbUserRole = ? AND dbIsActive = 1';
    var inserts = [2];
    var sql = mysql.format(sqlsel, inserts);

    console.log(sql);

    con.query(sql, function (err, data) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }
        res.send(JSON.stringify(data));
    });
});

/* =========================
   RESERVATIONS
========================= */

app.get('/getreservations/', function (req, res) {

    var reservationid = req.query.reservationid || '';
    var customerid = req.query.customerid || '';
    var traineruserid = req.query.traineruserid || '';
    var resdate = req.query.resdate || '';
    var restime = req.query.restime || '';
    var trainingroom = req.query.trainingroom || '';

    var sqlsel = 'SELECT * FROM Reservations WHERE dbIsActive = 1 AND dbReservetionId LIKE ? AND dbCustomerId LIKE ? AND dbTrainerUserId LIKE ? AND dbResDate LIKE ? AND dbResTime LIKE ? AND dbTrainingRoom LIKE ?';
    var inserts = ['%' + reservationid + '%', '%' + customerid + '%', '%' + traineruserid + '%', '%' + resdate + '%', '%' + restime + '%', '%' + trainingroom + '%'];
    var sql = mysql.format(sqlsel, inserts);

    console.log(sql);

    con.query(sql, function (err, data) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }
        res.send(JSON.stringify(data));
    });
});

app.get('/getsinglereservation/', function (req, res) {

    var reservationid = req.query.upreservationid;

    var sqlsel = 'SELECT * FROM Reservations WHERE dbIsActive = 1 AND dbReservetionId = ?';
    var inserts = [reservationid];
    var sql = mysql.format(sqlsel, inserts);

    console.log(sql);

    con.query(sql, function (err, data) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }
        res.send(JSON.stringify(data));
    });
});

app.post('/reservation', function (req, res) {

    var customerid = req.body.customerid;
    var traineruserid = req.body.traineruserid;
    var resdate = req.body.resdate;
    var restime = req.body.restime;
    var trainingroom = req.body.trainingroom;

    if (!customerid || !traineruserid || !resdate || !restime || !trainingroom) {
        res.send(JSON.stringify({ error: 'All reservation fields are required' }));
        return;
    }

    var timeparts = restime.split(':');
    var hour = parseInt(timeparts[0], 10);
    var minute = parseInt(timeparts[1], 10);

    if (isNaN(hour) || isNaN(minute)) {
        res.send(JSON.stringify({ error: 'Invalid reservation time' }));
        return;
    }

    if (hour < 8 || hour > 17) {
        res.send(JSON.stringify({ error: 'Reservation must start between 8 AM and 5 PM' }));
        return;
    }

    if (minute != 0) {
        res.send(JSON.stringify({ error: 'Reservations must begin on the hour' }));
        return;
    }

    var sqltrainer = 'SELECT * FROM `User` WHERE dbUserId = ? AND dbUserRole = ? AND dbIsActive = 1';
    var trainerinserts = [traineruserid, 2];
    var trainersql = mysql.format(sqltrainer, trainerinserts);

    con.query(trainersql, function (err, trainerdata) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }

        if (trainerdata.length == 0) {
            res.send(JSON.stringify({ error: 'Selected user is not a trainer' }));
            return;
        }

        var sqlcheck = 'SELECT * FROM Reservations WHERE dbTrainerUserId = ? AND dbResDate = ? AND dbResTime = ? AND dbIsActive = 1';
        var checkinserts = [traineruserid, resdate, restime];
        var checksql = mysql.format(sqlcheck, checkinserts);

        con.query(checksql, function (err, checkdata) {
            if (err) {
                console.error(err);
                res.send(JSON.stringify({ error: err.toString() }));
                return;
            }

            if (checkdata.length > 0) {
                res.send(JSON.stringify({ error: 'Trainer is already booked for that hour' }));
                return;
            }

            var sqlins = 'INSERT INTO Reservations (dbCustomerId, dbTrainerUserId, dbResDate, dbResTime, dbTrainingRoom) VALUES (?, ?, ?, ?, ?)';
            var inserts = [customerid, traineruserid, resdate, restime, trainingroom];
            var sql = mysql.format(sqlins, inserts);

            console.log(sql);

            con.execute(sql, function (err, result) {
                if (err) throw err;
                res.send(JSON.stringify({ success: 'Reservation inserted successfully' }));
            });
        });
    });
});

app.post('/updatesinglereservation', function (req, res) {

    var reservationid = req.body.upreservationid;
    var customerid = req.body.upcustomerid;
    var traineruserid = req.body.uptraineruserid;
    var resdate = req.body.upreservationdate;
    var restime = req.body.upreservationtime;
    var trainingroom = req.body.uptrainingroom;

    if (!reservationid || !customerid || !traineruserid || !resdate || !restime || !trainingroom) {
        res.send(JSON.stringify({ error: 'All reservation fields are required' }));
        return;
    }

    var timeparts = restime.split(':');
    var hour = parseInt(timeparts[0], 10);
    var minute = parseInt(timeparts[1], 10);

    if (isNaN(hour) || isNaN(minute)) {
        res.send(JSON.stringify({ error: 'Invalid reservation time' }));
        return;
    }

    if (hour < 8 || hour > 17) {
        res.send(JSON.stringify({ error: 'Reservation must start between 8 AM and 5 PM' }));
        return;
    }

    if (minute != 0) {
        res.send(JSON.stringify({ error: 'Reservations must begin on the hour' }));
        return;
    }

    var sqltrainer = 'SELECT * FROM `User` WHERE dbUserId = ? AND dbUserRole = ? AND dbIsActive = 1';
    var trainerinserts = [traineruserid, 2];
    var trainersql = mysql.format(sqltrainer, trainerinserts);

    con.query(trainersql, function (err, trainerdata) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }

        if (trainerdata.length == 0) {
            res.send(JSON.stringify({ error: 'Selected user is not a trainer' }));
            return;
        }

        var sqlcheck = 'SELECT * FROM Reservations WHERE dbTrainerUserId = ? AND dbResDate = ? AND dbResTime = ? AND dbReservetionId <> ? AND dbIsActive = 1';
        var checkinserts = [traineruserid, resdate, restime, reservationid];
        var checksql = mysql.format(sqlcheck, checkinserts);

        con.query(checksql, function (err, checkdata) {
            if (err) {
                console.error(err);
                res.send(JSON.stringify({ error: err.toString() }));
                return;
            }

            if (checkdata.length > 0) {
                res.send(JSON.stringify({ error: 'Trainer is already booked for that hour' }));
                return;
            }

            var sqlins = 'UPDATE Reservations SET dbCustomerId = ?, dbTrainerUserId = ?, dbResDate = ?, dbResTime = ?, dbTrainingRoom = ? WHERE dbReservetionId = ?';
            var inserts = [customerid, traineruserid, resdate, restime, trainingroom, reservationid];
            var sql = mysql.format(sqlins, inserts);

            console.log(sql);

            con.execute(sql, function (err, result) {
                if (err) {
                    console.error(err);
                    res.send(JSON.stringify({ error: err.toString() }));
                    return;
                }
                res.send(JSON.stringify({ success: 'Reservation updated successfully' }));
            });
        });
    });
});

/* =========================
   INVENTORY REPORT
========================= */
app.get('/getinventoryreport/', function (req, res) {

    var sqlsel = 'SELECT i.dbInventoryId, i.dbMenuItemId, m.dbMenuName, i.dbQuantityAvailable FROM Inventory i LEFT JOIN MenuItems m ON i.dbMenuItemId = m.dbMenuItemId WHERE i.dbIsActive = 1 AND m.dbIsActive = 1 ORDER BY m.dbMenuName';
    var sql = mysql.format(sqlsel);

    con.query(sql, function (err, data) {
        if (err) {
            console.error(err);
            res.send(JSON.stringify({ error: err.toString() }));
            return;
        }
        res.send(JSON.stringify(data));
    });
});

app.listen(app.get('port'), function () {
    console.log('Server started: http://localhost:' + app.get('port') + '/');
});