// LiteLibrary - A simple library management system
require('dotenv').config();
const path = require('path');
const { app, BrowserWindow, Menu } = require('electron');
const package = require(path.resolve(__dirname, 'package.json'));
const portfinder = require('portfinder');
var ip = require('ip');
const os = require('os');
var process = require('process')

// Quick Database Setup
const { Database } = require("@cs340-24/quick.db");
const books = new Database(path.resolve(__dirname, 'media.sqlite'), { path: path.resolve(__dirname), table: "BOOKS" });
const users = new Database(path.resolve(__dirname, 'users.sqlite'), { path: path.resolve(__dirname), table: "USERS" });

// Express Setup
const express = require('express');
const expressApp = express();
var port = process.env.PORT || 3000;
const fileUpload = require('express-fileupload');
expressApp.engine('html', require('ejs').renderFile);
expressApp.set('view engine', 'html');
expressApp.set('views', __dirname + '/views');
expressApp.use(fileUpload());
let mainWindow;

// Find a free port if the default port is already in use
portfinder.setBasePort(port);
portfinder.getPortPromise()
.then((newport) => {
	port = newport;
	// Start Express Server
	expressApp.listen(port, () => {
		console.log(`LiteLib listening on http://${ip.address()}:${port}`);
	})
})
.catch((err) => {
	console.log("Could not get a free port: " + err);
});

// Hashing and Parsing
const { v4: uuidv4 } = require('uuid');
var hash = require('object-hash');
const bodyParser = require('body-parser');
expressApp.use(bodyParser.urlencoded({ extended: false }));

// User Session Setup
const session = require('express-session');
secret = uuidv4();
if (process.env.SECRET) secret = process.env.SECRET;
expressApp.use(session({
    secret: hash(secret),
    resave: false,
    saveUninitialized: true
}));

// Downloads/Uploads
const json2csv = require('json2csv').parse;
const ExcelJS = require('exceljs');
const fs = require('fs');
const csvParser = require('csv-parser');
const { electron } = require('process');

// Checkout Limit Setup (creates file only if it doesn't already exist)
const defaultCheckoutLimit = -1;
try {fs.writeFileSync(path.resolve(__dirname, './checkoutLimit.txt'), defaultCheckoutLimit.toString(), { flag: 'wx' });} catch {}

expressApp.get('/', (req, res) => {
	res.render('index', { user: req.session.user });
})

const createWindow = () => {
	const { touchBar, setWindowTouch } = require(path.resolve(__dirname, 'macos/touchbar.js'));
	const { setWindowMenu } = require(path.resolve(__dirname, 'macos/menu.js'));
	mainWindow = new BrowserWindow({
	icon:'public/favicon/apple-touch-icon.png',
		width: 1280,
		height: 720,
		autoHideMenuBar: true,
		useContentSize: true,
		resizable: true,
	});
	mainWindow.loadURL(`http://localhost:${port}/`);
	mainWindow.setTouchBar(touchBar);
	setWindowTouch(mainWindow, port);
	setWindowMenu(mainWindow, port);
	mainWindow.focus();
}

if (process.versions.electron) {
	app.on('ready', () => {
		express();
		createWindow();
	
		// Mac OS specific code
		app.on('activate', () => {
			if (BrowserWindow.getAllWindows().length === 0) createWindow()
		});

		// Mac OS specific code
		app.on('window-all-closed', () => {
			if (process.platform !== 'darwin') app.quit()
		});
	});

	// MacOS About Page
	app.setAboutPanelOptions({
		applicationName: "LiteLib", 
		applicationVersion: "App Version",
		version: package.version,
		credits: "Created by " + package.authors
	});

	// Menubar for MacOS
	const { template } = require(path.resolve(__dirname, 'macos/menu.js'));
	const menu = Menu.buildFromTemplate(template)
	Menu.setApplicationMenu(menu)
}

// app.use(express.static('public'));
expressApp.use(express.static(__dirname + '/public'));

// Show all books when user is authenticated
expressApp.get('/books', isAuthenticated, (req, res) => {
    let booksData = books.toJSON() || [];

	const errorMessage = req.query.errorMessage;
    const message = req.query.message;

	// Sort books by name alphabetically
	booksData.sort(function(a, b) {
		var nameA = a.data.name.toLowerCase();
		var nameB = b.data.name.toLowerCase();
		if (nameA > nameB) return 1;
		if (nameA < nameB) return -1;
		return 0;
	});

	let user = users.get(req.session.user.username);

	// Render the users page
	return res.render('books', { books: booksData, errorMessage, message, user});
});

expressApp.get('/add-book', isAdminAuthenticated, (req, res) => {
    // Render the add book page
    res.render('add-book', { user: req.session.user });
});

expressApp.post('/add-book', isAdminAuthenticated, (req, res) => {
    // Retrieve form data directly from req.body
    let { name, author, year, genre, ISBN, ISBN10, copies } = req.body;

    // Check to see if the book is already in the database
    if (books.toJSON().filter(entry => entry.data.name === name).length == 1) {
		// Redirect to add book page with error message
		let errorMessage = encodeURIComponent('Book already exists');
		return res.redirect(`/books?errorMessage=${errorMessage}`);
	}

	let checkout = [];
	year = parseInt(year);
	copies = parseInt(copies);

	// Create a new book object
	let book = { name, author, year, genre, ISBN, ISBN10, copies, checkout };

	// Insert the book into the database
	books.set(uuidv4(), book);

	// Redirect back to the books page after update
	let message = encodeURIComponent(`Added "${name}"`);
	return res.redirect(`/books?message=${message}`);
});

// New route for QR code scanning
expressApp.get('/scan-book', isAuthenticated, (req, res) => {
    res.render('scan-book', { user: req.session.user });
});

expressApp.get('/add-user', isAdminAuthenticated, (req, res) => {
    // Render the add book page
    res.render('add-user', { user: req.session.user });
});

expressApp.post('/add-user', isAdminAuthenticated, (req, res) => {
    // Retrieve form data directly from req.body
    let { username, name, role, password } = req.body;

    // Check to see if the book is already in the database
    if (users.has(username)) {
		// Redirect to add book page with error message
		let errorMessage = encodeURIComponent('User already exists');
		return res.redirect(`/users?errorMessage=${errorMessage}`);
	}

	let currentPassword = hash(password);
	let checkout = [];
	let lowerUsername = username.toLowerCase();

	// Create a new book object
	let user = { username: lowerUsername, name, role, password:currentPassword, checkout };

	// Insert the book into the database
	users.set(lowerUsername, user);

	// Redirect back to the users page after update
	let message = encodeURIComponent(`Added "${username}"`);
	return res.redirect(`/users?message=${message}`);
});

expressApp.get('/edit-book', isAdminAuthenticated, (req, res) => {
    // Teceive query parameters
    const { name, } = req.query;

    // Retrieve the current book information from the database
    const currentBook = books.toJSON().filter(entry => entry.data.name === name)[0];

    // Render the edit-book page
    res.render('edit-book', { book: currentBook, user: req.session.user });
});

expressApp.post('/edit-book', isAdminAuthenticated, (req, res) => {
    // Retrieve form data directly from req.body
    const bookID = req.body.bookID;
    const name = req.body.name;
    const author = req.body.author;
	let year = req.body.year;
	const genre = req.body.genre;
	const ISBN = req.body.ISBN;
	const ISBN10 = req.body.ISBN10;
	let copies = req.body.copies;
	const deleteBook = req.body.delete;

	// Check if the user wants to delete a book
	if (deleteBook === "true") {
		// Go through checkout list and return all books
		const currentBook = books.get(bookID);
		const checkout = currentBook.checkout;
		checkout.forEach(username => {
			let newCheckout = users.get(username).checkout.filter(item => item !== bookID);
			users.set(`${username}.checkout`, newCheckout);
		});

		// Delete the book from the database
		books.delete(bookID);
		let message = encodeURIComponent(`"${name}" was successfully deleted`);
		return res.redirect(`/books?message=${message}`);
	}

    // Check if the book exists in the database
    if (books.has(bookID)) {
		let checkout = books.get(bookID).checkout;
		year = parseInt(year);
		copies = parseInt(copies);

		// Create an updated book object
		let updatedBook = { name, author, year, genre, ISBN, ISBN10, copies, checkout };

        // Insert the updated book into the database
        books.set(bookID, updatedBook);

		let message = encodeURIComponent(`"${name}" updated`);
		return res.redirect(`/books?message=${message}`);
    }
	
    // Redirect back to the books page after update
	let errorMessage = encodeURIComponent(`"${name}" not found`);
    res.redirect(`/books?errorMessage=${errorMessage}`);
});

expressApp.get('/checkout', isAuthenticated, (req, res) =>{
	// Teceive query parameters
    const { name, } = req.query;

    // Retrieve the current book information from the database
    const currentBook = books.toJSON().filter(entry => entry.data.name === name)[0];

    // Render the edit-book page
    res.render('checkout', { book: currentBook, user: req.session.user });
})

expressApp.post('/checkout', isAuthenticated, (req, res) =>{
	// Retrieve form data directly from req.body
	const bookID = req.body.bookID;
	const username = req.session.user.username;
	const role = req.session.user.role;
	const checkoutLimit = parseInt(fs.readFileSync(path.resolve(__dirname, './checkoutLimit.txt')));

	// Retrieve the current book and user information from the database
	const currentBook = books.get(bookID);
	const currentUser = users.get(username);

	// Check if there are any copies of the book available
	if (currentBook.copies <= currentBook.checkout.length) {
		let errorMessage = encodeURIComponent(`No copies of "${currentBook.name}" available.`);
		return res.redirect(`/books?errorMessage=${errorMessage}`);
	}

	// Check if the user already has the book checked out
	if (currentUser.checkout.includes(bookID)) {
		let errorMessage = encodeURIComponent(`Already have "${currentBook.name}" checkout.`);
		return res.redirect(`/books?errorMessage=${errorMessage}`);
	}

	// Check if the user has hit their checkout limit
	if (role !== "admin" && checkoutLimit >= 0 && currentUser.checkout.length >= checkoutLimit) {
		// console.log("limit exceeded");
		let errorMessage = encodeURIComponent(`You have hit checkout limit of ${checkoutLimit} book(s).`);
		return res.redirect(`/books?errorMessage=${errorMessage}`);
	}

	// Checkout the book in each database
	books.push(`${bookID}.checkout`, username);
	users.push(`${username}.checkout`, bookID);

	let message = encodeURIComponent(`Checkout "${currentBook.name}" successful.`);
	return res.redirect(`/books?message=${message}`);
})


expressApp.get('/return', isAuthenticated, (req, res) =>{
	// Teceive query parameters
    const { bookID, } = req.query;
	let otherUser = req.query.otherUser;

    // Retrieve the current book information from the database
    const currentBook = books.get(bookID);
	let currentUser = users.get(req.session.user.username);
	if (otherUser) currentUser = users.get(otherUser);

	// Return the book in each database
	books.set(`${bookID}.checkout`, currentBook.checkout.filter(item => item !== currentUser.username));
	users.set(`${currentUser.username}.checkout`, currentUser.checkout.filter(item => item !== bookID));

	if (otherUser) {
		let message = encodeURIComponent(`Returned "${currentBook.name}" successful by ${otherUser}.`);
		return res.redirect(`/user-books?message=${message}&username=${currentUser.username}&name=${currentUser.name}`);
	}

	let message = encodeURIComponent(`Returned "${currentBook.name}" successful.`);
	return res.redirect(`/books?message=${message}`);
})

expressApp.get('/settings', isAdvancedAuthenticated, (req, res) => {
	let sessionUser = req.session.user;
	const errorMessage = req.query.errorMessage;
    const message = req.query.message;

    // Retrieve the current user information from the database
    const user = users.get(sessionUser.username);

    // Render the settings page with the current user information
    res.render('settings', { user, errorMessage, message});
});

expressApp.post('/settings', isAdvancedAuthenticated, (req, res) => {
	// Retrieve form data directly from req.body
	const oldUsername = req.body.username; 
	const newUsername = req.body.newUsername;
	const name = req.body.name;
	const newPassword = req.body.newPassword;

	// Check if the old username exists in the database
    if (users.has(oldUsername)) {
        // Retrieve the current user information from the database
        const currentUser = users.get(oldUsername);
		let currentPassword = currentUser.password;
		let role = currentUser.role;
		let checkout = currentUser.checkout;

		// Go through checkout list and replace the old username with the new username
		checkout.forEach(bookID => {
			let newCheckout = books.get(bookID).checkout.filter(item => item !== oldUsername);
			newCheckout.push(newUsername);
			books.set(`${bookID}.checkout`, newCheckout);
		});

        // Delete the user with the old username from the database
        users.delete(oldUsername);

		// Check if the current user is the one being updated
		if (oldUsername === req.session.user.username) {
			// Update the session user information
			req.session.user = { name, username: newUsername, role};
		}

		// Add a check to see if the password was changed
		if (newPassword) currentPassword = hash(newPassword);

        // Create an updated user object with the new username and updated information
        const updatedUserInfo = { username: newUsername, password: currentPassword, role, name, checkout };

        // Insert the updated user into the database
        users.set(newUsername, updatedUserInfo);

		let message = encodeURIComponent(`User "${newUsername}" updated`);
		return res.redirect(`/settings?message=${message}`);
    } 
	
    // Redirect back to the users page after update
    let errorMessage = encodeURIComponent(`"${oldUsername}" not found`);
    res.redirect(`/settings?errorMessage=${errorMessage}`);
});

expressApp.get('/edit-users', isAdminAuthenticated, (req, res) => {
    // Teceive query parameters
    const { username, name, role } = req.query;
	let lowerUsername = username.toLowerCase();
    
    // Retrieve the current user information from the database
    const currentUser = users.get(lowerUsername);

    // Render the edit-users page with the current user information and all users
    res.render('edit-users', { username: lowerUsername, name, role, currentUser, user: req.session.user });
});

expressApp.post('/edit-users', isAdminAuthenticated, (req, res) => {
    // Retrieve form data directly from req.body
    const oldUsername = req.body.username.toLowerCase(); 
    const newUsername = req.body.newUsername.toLowerCase();
    const name = req.body.name;
    const role = req.body.role;
	const newPassword = req.body.newPassword;
	const deleteAccount = req.body.delete;

	// Check if the user wants to delete their account
	if (deleteAccount === "true") {
		// Make sure that the user is not deleting the currently signed in user
		if (oldUsername === req.session.user.username) {
			let errorMessage = encodeURIComponent('Cannot delete the currently signed in user');
			return res.redirect(`/users?errorMessage=${errorMessage}`);
		}

		// Go through checkout list and return all books
		const currentUser = users.get(oldUsername);
		const checkout = currentUser.checkout;

		if (checkout.length > 0) {
			checkout.forEach(bookID => {
				let newCheckout = books.get(bookID).checkout.filter(item => item !== oldUsername);
				books.set(`${bookID}.checkout`, newCheckout);
			});
		}

		// Delete the user from the database
		users.delete(oldUsername);
		let message = encodeURIComponent(`User "${oldUsername}" was successfully deleted`);
		return res.redirect(`/users?message=${message}`);
	}

    // Check if the old username exists in the database
    if (users.has(oldUsername)) {
        // Retrieve the current user information from the database
        const currentUser = users.get(oldUsername);
		let currentPassword = currentUser.password;
		let checkout = currentUser.checkout;

		// Go through checkout list and replace the old username with the new username
		checkout.forEach(bookID => {
			let newCheckout = books.get(bookID).checkout.filter(item => item !== oldUsername);
			newCheckout.push(newUsername);
			books.set(`${bookID}.checkout`, newCheckout);
		});

        // Delete the user with the old username from the database
        users.delete(oldUsername);

		// Make sure that we have at least one admin user
		let adminCount = users.toJSON().filter(entry => entry.data.role === 'admin').length;
		if ((adminCount === 0) && (role !== 'admin')) {
			// Add the user back to the database
			users.set(oldUsername, currentUser);

			// Redirect to users page with error message
			let errorMessage = encodeURIComponent('Need at least one user to have the role "admin"');
			return res.redirect(`/users?errorMessage=${errorMessage}`);
		};

		// Check if the current user is the one being updated
		if (oldUsername === req.session.user.username) {
			// Update the session user information
			req.session.user = { name, username: newUsername, role};
		}

		// Add a check to see if the password was changed
		if (newPassword) currentPassword = hash(newPassword);

        // Create an updated user object with the new username and updated information
        const updatedUserInfo = { username: newUsername, password: currentPassword, role, name, checkout };

        // Insert the updated user into the database
        users.set(newUsername, updatedUserInfo);

		let message = encodeURIComponent(`User "${newUsername}" updated`);
		return res.redirect(`/users?message=${message}`);
    } 
	
    // Redirect back to the users page after update
    let errorMessage = encodeURIComponent(`"${name}" not found`);
    res.redirect(`/users?errorMessage=${errorMessage}`);
});


// Show all users when current user is admin and authenticated
expressApp.get('/users', isAdminAuthenticated, (req, res) => {
    userData = users.toJSON() || [];

	const errorMessage = req.query.errorMessage;
    const message = req.query.message;

        // Sort users by name alphabetically
        userData.sort(function(a, b) {
            var nameA = a.data.username.toLowerCase();
            var nameB = b.data.username.toLowerCase();
            if (nameA > nameB) return 1;
            if (nameA < nameB) return -1;
            return 0;
        });

        // Render the users page
        return res.render('users', { users: userData, user: req.session.user, errorMessage, message });
});

expressApp.get('/dashboard', isAdminAuthenticated, (req, res) => {
	let message = req.query.message;
	let errorMessage = req.query.errorMessage;

	userData = users.toJSON() || [];

	userData.sort(function(a, b) {
		var nameA = a.data.username.toLowerCase();
		var nameB = b.data.username.toLowerCase();
		if (nameA > nameB) return 1;
		if (nameA < nameB) return -1;
		return 0;
	});

	checkoutLimit = parseInt(fs.readFileSync(path.resolve(__dirname, './checkoutLimit.txt')));

	server = { 
		ip: ip.address(),
		port: port,
		hostname: os.hostname(),
		platform: os.platform() + "-" +os.arch(),
		version: package.version,
		electron: process.versions.electron,
		memory: process.memoryUsage(),
		uptime: process.uptime(),
		cpu: os.cpus().length,
	};

	return res.render('dashboard', {users: userData, checkoutLimit, server, message, errorMessage});
});

expressApp.post('/dashboard', isAdminAuthenticated, (req, res) => {
	const newCheckoutLimit = parseInt(req.body.newCheckoutLimit);

	if (newCheckoutLimit < -1) {
		let errorMessage = encodeURIComponent('Invalid checkout limit');
		return res.redirect(`/dashboard?errorMessage=${errorMessage}`);
	}

	if (!Number.isNaN(newCheckoutLimit)) {
		fs.writeFileSync(path.resolve(__dirname, './checkoutLimit.txt'), newCheckoutLimit.toString());
	} else {
		let errorMessage = encodeURIComponent('Invalid checkout limit');
		return res.redirect(`/dashboard?errorMessage=${errorMessage}`);
	}
	let message = encodeURIComponent(`Checkout limit updated to ${newCheckoutLimit}`);
	return res.redirect(`/dashboard?message=${message}`);
});

expressApp.get('/user-books', isAdminAuthenticated, (req, res) => {
	const {username, name} = req.query;
	const user = users.get(username);
	let message = req.query.message;

	return res.render('user-books', {username, name, user, books, message});
});

expressApp.get('/checkout-books', isAdminAuthenticated, (req, res) => {
	const {bookID} = req.query;
	let getbook = books.get(bookID);
	let message = req.query.message;

	let book = {
		ID: bookID,
		data: getbook
	};

	return res.render('checkout-books', {book, users, message});
});

// Display all tables in the media database
expressApp.get('/api', (req, res) => {
	res.send(books.tables());
});

// Display /api/:table_name
expressApp.get('/api/:table_name', (req, res) => {
	const table_name = req.params.table_name;
	let database;

	// Determine the table to use
	switch(table_name) {
		case 'books':
			database = books;
			break;
		default:
			res.setHeader('Content-Type', 'text/plain');
			return res.status(404).send('Table not found');
	}

	res.send(database.toJSON());
});

// Allow admin to download the db as a CSV file
expressApp.get('/download-csv/:table_name', isAdminAuthenticated, (req, res) => {
	const table_name = req.params.table_name;
	let database;

	switch(table_name) {
		case 'books':
			database = books;
			break;
		case 'users':
			database = users;
			break;
		default:
			res.setHeader('Content-Type', 'text/plain');
			return res.status(404).send('Table not found');
	}

    try {
		// Get the data from the database
		let jsonData = database.toJSON();
		let extractedData = jsonData.map(item => item.data);

		// Replace checkout array correct data
		if (table_name === 'books') {
			extractedData = extractedData.map(book => {
                const { checkout, ...bookData } = book;

                // Find users in the checkout array and remove passwords
                const usersInCheckout = checkout.map(username => {
                    if (users.has(username)) {
						let user = users.get(username);
						let string = `${user.username} (${user.name})`;
                        return string;
                    }
                    return null;
                });

                return { ...bookData, checkout: usersInCheckout };
            });
		} else if (table_name === 'users') {
			extractedData = extractedData.map(user => {
                const { checkout, ...userData } = user;

                // Find books in the checkout array
                const booksInCheckout = checkout.map(bookID => {
                    if (books.has(bookID)) {
						let book = books.get(bookID);
						let string = `${book.name} (${book.author})`;
                        return string;
                    }
                    return null;
                });

                return { ...userData, checkout: booksInCheckout };
            });

			// Remove the "password" field from users
			extractedData = extractedData.map(item => {
				const { password, ...dataWithoutCheckout } = item;
				return dataWithoutCheckout;
			});
		}

		// Convert JSON to CSV
        const csv = json2csv(extractedData);

        // Set headers to trigger download
        res.setHeader('Content-disposition', `attachment; filename=${table_name}.csv`);
        res.set('Content-Type', 'text/csv');

        // Send the CSV file to the client
        res.status(200).send(csv);
    } catch (err) {
        // Handle errors
        console.error(err);
        res.status(500).send('Internal Server Error');
    }
});

// Allow admin to download the db as an excel file
expressApp.get('/download-excel', isAdminAuthenticated, (req, res) => {
    const workbook = new ExcelJS.Workbook();
    const booksWorksheet = workbook.addWorksheet('Books');
    const usersWorksheet = workbook.addWorksheet('Users');
	let extractedData = NaN;

	try {
        // Handle Books
        let booksData = books.toJSON();
		extractedData = booksData.map(item => item.data);

		extractedData = extractedData.map(book => {
			const { checkout, ...bookData } = book;

			// Find users in the checkout array
			const usersInCheckout = checkout.map(username => {
				if (users.has(username)) {
					let user = users.get(username);
					let string = `${user.username} (${user.name})`;
					return string;
				}
				return null;
			});

			return { ...bookData, checkout: usersInCheckout };
		});

        // Add Books data to worksheet
        booksWorksheet.columns = [
            { header: 'Name', key: 'name', width: 20 },
            { header: 'Author', key: 'author', width: 20 },
            { header: 'Year', key: 'year', width: 10, style: { numFmt: '0' } },
			{ header: 'Genre', key: 'genre', width: 20 },
			{ header: 'ISBN', key: 'ISBN', width: 20 },
			{ header: 'ISBN10', key: 'ISBN10', width: 20 },
			{ header: 'Copies', key: 'copies', width: 10, style: { numFmt: '#,##0' } },
			{ header: 'Checkout', key: 'checkout', width: 40 }
        ];
		booksWorksheet.getRow(1).font = { bold: true }; // Bold header row
        extractedData.forEach(book => {
            booksWorksheet.addRow(book);
        });

        // Handle Users
        let usersData = users.toJSON();
		extractedData = usersData.map(item => item.data);

		extractedData = extractedData.map(user => {
			const { checkout, ...userData } = user;

			// Find books in the checkout array
			const booksInCheckout = checkout.map(bookID => {
				if (books.has(bookID)) {
					let book = books.get(bookID);
					let string = `${book.name} (${book.author})`;
					return string;
				}
				return null;
			});

			return { ...userData, checkout: booksInCheckout };
		});

		// Remove the "password" field from users
		extractedData = extractedData.map(item => {
			const { password, ...dataWithoutCheckout } = item;
			return dataWithoutCheckout;
		});

        // Add Users data to worksheet
        usersWorksheet.columns = [
            { header: 'Username', key: 'username', width: 20 },
            { header: 'Name', key: 'name', width: 20 },
            { header: 'Role', key: 'role', width: 20 },
			{ header: 'Checkout', key: 'checkout', width: 40 }
        ];
		usersWorksheet.getRow(1).font = { bold: true }; // Bold header row
		extractedData.forEach(user => {
			usersWorksheet.addRow(user);
		});

		// Set headers to trigger download
		res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
		res.setHeader('Content-Disposition', 'attachment; filename="Library.xlsx"');

		// Generate Excel file and send to client
		const generateExcel = async () => {
			const buffer = await workbook.xlsx.writeBuffer();
			res.status(200).send(buffer);
		};

		generateExcel();
    } catch (err) {
        // Handle errors
        console.error(err);
        res.status(500).send('Internal Server Error');
    }
});

// Upload excel file
expressApp.get('/upload-excel', isAdminAuthenticated, (req, res) => {
	const errorMessage = req.query.errorMessage;
    const message = req.query.message;

	res.render('upload-excel', { user: req.session.user, errorMessage, message});
});

// Read the db as an excel file
expressApp.post('/upload-excel', isAdminAuthenticated, async (req, res) => {
try {
	if (!req.files || Object.keys(req.files).length === 0) {
		let errorMessage = encodeURIComponent('No files were uploaded. Try Again.');
		return res.redirect(`/upload-excel?errorMessage=${errorMessage}`);
	}

	const excelFile = req.files.excelFile;
	let errorMessages = [];

	fs.mkdir("uploads", { recursive: true }, (err) => {
		if (err) {
			console.error('Error creating directory:', err);
			let errorMessage = encodeURIComponent('Error uploading file. Try Again.');
			return res.redirect(`/upload-excel?errorMessage=${errorMessage}`);
		}

		const id = `${uuidv4()}-${excelFile.name}`;

		excelFile.mv(`uploads/${id}`, (err) => {
			if (err) {
				console.error(err);
				let errorMessage = encodeURIComponent('Error uploading file. Try Again.');
				return res.redirect(`/upload-excel?errorMessage=${errorMessage}`);
			}

			// Load the workbook
			const workbook = new ExcelJS.Workbook();
			workbook.xlsx.readFile(`uploads/${id}`).then(function () {
				// Get the first worksheet
				let worksheet = workbook.getWorksheet(1);

				// Convert the worksheet data to JSON
				const bookData = [];
				worksheet.eachRow({ includeEmpty: true }, function (row, rowNumber) {
					const rowData = {};
					row.eachCell({ includeEmpty: true }, function (cell, colNumber) {
						rowData[`column_${colNumber}`] = cell.value;
					});
					bookData.push(rowData);
				});

				// Book data
				const bookOutput = bookData.slice(1).map((item, index) => {
					const id = uuidv4();
					let checkout = [];

					// Check if the "Checkout" column is not empty
					if (item.column_8.trim() !== '') {
						// Use regular expression to extract usernames
						const checkoutEntries = item.column_8.match(/\b(\w+)\b(?=\s+\()/g);

						// Check if checkoutEntries is not null
						if (checkoutEntries !== null) {
							checkout = checkoutEntries;
						} else {
							errorMessages.push(`Error extracting usernames from "Checkout" column: ${item.column_8}`);
						}
					}

					return {
						ID: id,
						data: {
							name: item.column_1,
							author: item.column_2,
							year: parseInt(item.column_3),
							genre: item.column_4,
							ISBN: item.column_5,
							ISBN10: item.column_6,
							copies: parseInt(item.column_7),
							checkout: checkout
						}
					};
				});

				if (errorMessages.length > 0) {
					let errorMessage = encodeURIComponent(errorMessages.join('; '));
					return res.redirect(`/upload-excel?errorMessage=${errorMessage}`);
				}

				// Get the first worksheet
				worksheet = workbook.getWorksheet(2);

				// Convert the worksheet data to JSON
				const userData = [];
				worksheet.eachRow({ includeEmpty: true }, function (row, rowNumber) {
					const rowData = {};
					row.eachCell({ includeEmpty: true }, function (cell, colNumber) {
						rowData[`column_${colNumber}`] = cell.value;
					});
					userData.push(rowData);
				});

				// User data
				const userOutput = userData.slice(1).map((item, index) => {
					const id = item.column_1;

					// Regular expression to match book names and authors
					const regex = /(\w+(?:\s+\w+)*)\s+\((\w+(?:\s+\w+)*)\)/g;

					// Extract book names and authors from checkout data
					const checkoutEntries = [];
					let match;
					while ((match = regex.exec(item.column_4)) !== null) {
						const [_, bookname, author] = match;

						// Find the book in bookData based on name
						const foundBook = bookOutput.find(book => book.data.name === bookname);
						if (foundBook) {
							checkoutEntries.push(foundBook.ID);
						} else {
							console.error(`Book not found: ${bookname}`);
							errorMessages.push(`${bookname} not found in books`);
						}
					}

					// Determine role to lowercase and validate
					let role = item.column_3.toLowerCase();
					if (role !== 'basic' && role !== 'advanced' && role !== 'admin') {
						console.error(`Invalid role: ${role}`);
						errorMessages.push(`Invalid role for user ${id}: ${role}`);
						role = 'admin';
					}

					return {
						ID: id,
						data: {
							username: item.column_1,
							name: item.column_2,
							role: role,
							password: hash('Password'),
							checkout: checkoutEntries
						}
					};
				});

				if (errorMessages.length > 0) {
					let errorMessage = encodeURIComponent(errorMessages.join('; '));
					return res.redirect(`/upload-excel?errorMessage=${errorMessage}`);
				}

				// Check if both checkouts match
				const checkoutsMatch = bookOutput.every(book => {
					const bookCheckouts = book.data.checkout;
					
					// Check if each username in bookOutput.checkout exists in userOutput
					const validBookCheckouts = bookCheckouts.every(username => {
						return userOutput.some(user => user.data.username === username);
					});

					if (!validBookCheckouts) {
						console.error(`User not found for one or more book checkouts: ${bookCheckouts}`);
						errorMessages.push(`User not found for one or more book checkouts: ${bookCheckouts}`);
						return false;
					}

					// Find the corresponding user data for each username
					const userCheckouts = userOutput.flatMap(user => user.data.checkout);

					// Check if each book ID in userOutput.checkout exists in bookOutput
					const validUserCheckouts = userCheckouts.every(bookId => {
						return bookOutput.some(book => book.ID === bookId);
					});

					if (!validUserCheckouts) {
						console.error(`Book not found for one or more user checkouts: ${userCheckouts}`);
						errorMessages.push(`Book not found for one or more user checkouts: ${userCheckouts}`);
						return false;
					}

					return true;
				});

				// Error Messages
				if (errorMessages.length > 0) {
					let errorMessage = encodeURIComponent(errorMessages.join('; '));
					return res.redirect(`/upload-excel?errorMessage=${errorMessage}`);
				} else if (!checkoutsMatch) {
					let errorMessage = encodeURIComponent('Checkouts do not match.');
					return res.redirect(`/upload-excel?errorMessage=${errorMessage}`);
				}

				// Reset the databases
				books.deleteAll();
				users.deleteAll();

				// Insert the data into the databases
				bookOutput.forEach(book => books.set(book.ID, book.data));
				userOutput.forEach(user => users.set(user.data.username, user.data));

				removeDirectory('uploads');
				
				// Redirect back to the upload page with success message
				let message = encodeURIComponent('Upload successful. Please <a href="/logout">sign out</a> and sign in again.');
				return res.redirect(`/upload-excel?message=${message}`);
			}).catch(err => {
				console.error('Error reading Excel file:', err);
				let errorMessage = encodeURIComponent('Error reading Excel file. Try Again.');
				return res.redirect(`/upload-excel?errorMessage=${errorMessage}`);
			});
		});
	});
} catch (err) {
	console.error(err);
	let errorMessage = encodeURIComponent('Internal Server Error');
	return res.redirect(`/upload-excel?errorMessage=${errorMessage}`);
}
});

// Upload CSV file
expressApp.get('/upload-csv', isAdminAuthenticated, (req, res) => {
	const errorMessage = req.query.errorMessage;
	const message = req.query.message;

	res.render('upload-csv', { user: req.session.user, errorMessage, message});
});

// Read the db as an either csv file
expressApp.post('/upload-csv', isAdminAuthenticated, async (req, res) => {
try {
	if (!req.files || Object.keys(req.files).length === 0) {
		let errorMessage = encodeURIComponent('No files were uploaded. Try Again.');
		return res.redirect(`/upload-csv?errorMessage=${errorMessage}`);
	}

	const csvFile = req.files.csvFile;

	fs.mkdir("uploads", { recursive: true }, (err) => {
		if (err) {
			console.error('Error creating directory:', err);
			let errorMessage = encodeURIComponent('Error uploading file. Try Again.');
			return res.redirect(`/upload-csv?errorMessage=${errorMessage}`);
		}

		const id = `${uuidv4()}-${csvFile.name}`;
		let errorMessages = [];

		csvFile.mv(`uploads/${id}`, (err) => {
			if (err) {
				console.error(err);
				let errorMessage = encodeURIComponent('Error uploading file. Try Again.');
				return res.redirect(`/upload-csv?errorMessage=${errorMessage}`);
			}

			// Now, let's parse the uploaded CSV file
			const results = [];
			fs.createReadStream(`uploads/${id}`)
				.pipe(csvParser())
				.on('data', (data) => results.push(data))
				.on('end', () => {
					if (results.length === 0) {
						let errorMessage = encodeURIComponent('No data found in CSV file');
						return res.redirect(`/upload-csv?errorMessage=${errorMessage}`);
					}

					// Determine the type of object
					const objectType = determineObjectType(results[0]);

					if (objectType === 'book') {
						// Book data
						const bookData = results.map(item => {
							let checkout = [];

							// Check if the "Checkout" column is not empty
							if (item.checkout !== '') {
								// Use regular expression to extract usernames
								const checkoutEntries = item.checkout.match(/\b(\w+)\b(?=\s+\()/g);

								// Check if checkoutEntries is not null
								if (checkoutEntries !== null) {
									checkoutEntries.forEach(username => {
										// Check if username exists in users
										const foundUser = users.find(user => user.data.username === username);
										if (foundUser) {
											checkout.push(username);
										} else {
											errorMessages.push(`Username "${username}" not found in users.`);
										}
									});
								} else {
									errorMessages.push(`Error extracting usernames from "Checkout" column: ${item.checkout}`);
								}
							}

							 // Check if the book already exists in the database
							const existingBook = books.find(existingBook => existingBook.data.name === item.name);
							const bookID = existingBook ? existingBook.ID : uuidv4(); // If the book exists, use its ID, otherwise generate a new one

							return {
								ID: bookID,
								data: {
									name: item.name,
									author: item.author || '',
									year: item.year ? parseInt(item.year) : null,
									genre: item.genre || '', // Use empty string if genre is not provided
									ISBN: item.ISBN || '', // Use empty string if ISBN is not provided
									ISBN10: item.ISBN10 || '', // Use empty string if ISBN10 is not provided
									copies: item.copies ? parseInt(item.copies) : null,
									checkout: checkout
								}
							};
						});

						if (errorMessages.length > 0) {
							let errorMessage = encodeURIComponent(errorMessages.join('; '));
							return res.redirect(`/upload-csv?errorMessage=${errorMessage}`);
						}

						// Reset the database
						books.deleteAll();

						// Insert the data into the databases
						bookData.forEach(book => books.set(book.ID, book.data));

						removeDirectory('uploads');

						// Redirect back to the upload page with success message
						let message = encodeURIComponent('Upload successful. Please <a href="/logout">sign out</a> and sign in again or upload another csv file.');
						return res.redirect(`/upload-csv?message=${message}`);
					} else if (objectType === 'user') {
						// User data
						const userData = results.map(item => {

							// Regular expression to match book names and authors
							const regex = /(\w+(?:\s+\w+)*)\s+\((\w+(?:\s+\w+)*)\)/g;

							// Extract book names and authors from checkout data
							const checkoutEntries = [];
							let match;
							while ((match = regex.exec(item.checkout)) !== null) {
								const [_, bookname, author] = match;

								// Find the book in bookData based on name
								const foundBook = books.find(book => book.data.name === bookname);
								if (foundBook) {
									checkoutEntries.push(foundBook.ID);
								} else {
									console.error(`Book not found: ${bookname}`);
									errorMessages.push(`${bookname} not found in books`);
								}
							}

							return {
								ID: item.username,
								data: {
									username: item.username,
									name: item.name,
									role: item.role,
									password: hash('Password'),
									checkout: checkoutEntries
								}
							};
						});

						if (errorMessages.length > 0) {
							let errorMessage = encodeURIComponent(errorMessages.join('; '));
							return res.redirect(`/upload-csv?errorMessage=${errorMessage}`);
						}

						// Reset the database
						users.deleteAll();

						// Insert the data into the databases
						userData.forEach(user => users.set(user.data.username, user.data));

						removeDirectory('uploads');

						// Redirect back to the upload page with success message
						let message = encodeURIComponent('Upload successful. Please <a href="/logout">sign out</a> and sign in again. or upload another csv file.');
						return res.redirect(`/upload-csv?message=${message}`);
					} else {
						let errorMessage = encodeURIComponent('Invalid CSV file. Try Again.');
						return res.redirect(`/upload-csv?errorMessage=${errorMessage}`);
					}
				});
		});
	});

} catch (err) {
	console.error(err);
	let errorMessage = encodeURIComponent('Internal Server Error');
	return res.redirect(`/upload-csv?errorMessage=${errorMessage}`);
}
});

function determineObjectType(obj) {
	if (obj.hasOwnProperty('name') && obj.hasOwnProperty('author') && obj.hasOwnProperty('year') && obj.hasOwnProperty('genre') && obj.hasOwnProperty('ISBN') && obj.hasOwnProperty('ISBN10') && obj.hasOwnProperty('copies') && obj.hasOwnProperty('checkout')) {
		return 'book';
	} else if (obj.hasOwnProperty('username') && obj.hasOwnProperty('name') && obj.hasOwnProperty('role') && obj.hasOwnProperty('checkout')) {
		return 'user';
	} else {
		return null;
	}
}

// Remove directory and all files within it
function removeDirectory(dirPath) {
    // Read the contents of the directory
    const files = fs.readdirSync(dirPath);

    // Iterate through each file/directory in the directory
    for (const file of files) {
        const filePath = path.join(dirPath, file);

        // Get the stats of the file/directory
        const stats = fs.statSync(filePath);

        // Check if it's a file or directory
        if (stats.isFile()) {
            // If it's a file, remove it
            fs.unlinkSync(filePath);
        } else {
            // If it's a directory, recursively call removeDirectory
            removeDirectory(filePath);
        }
    }

    // Finally, remove the directory itself
    fs.rmdirSync(dirPath);
}

// User Authentication
function isAuthenticated(req, res, next) {
    if (req.session && req.session.user) {
        return next();
    } else {
        res.redirect('/signin');
    }
}

// User Authentication
function isAdvancedAuthenticated(req, res, next) {
    if (req.session && req.session.user && req.session.user.role != "basic") {
        return next();
    } else {
        res.redirect('/signin');
    }
}

// Admin User Authentication
function isAdminAuthenticated(req, res, next) {
    if (req.session && req.session.user && req.session.user.role === "admin") {
        return next();
    } else {
        res.redirect('/');
    }
}

// Sign In Form
expressApp.get('/signin', (req, res) => {
	// If user is already signed in, redirect to /table
	if (req.session.user) {
		res.redirect('/');
	} else {
		res.render('signin');
	}
})

expressApp.get('/login', (req, res) => {
	res.render('signin-failed');
});

// Handle form submission
expressApp.post('/login', (req, res) => {
	if (!req.body.username || !req.body.password) {
		res.render('signin-failed');
	}

	var login = false;

	// Retrieve username and password from form data
	username = req.body.username;
	const password = req.body.password;

	username = username.toString().toLowerCase();

	// Check if the user exists and the password is correct
	if (users.has(username)) {
		const storedPassword = users.get(username).password;
		const role = users.get(username).role;
		const name = users.get(username).name;

		if (hash(password) === storedPassword) {
			login = true;

			// Set user data in session upon successful login
            req.session.user = {
				name: name,
                username: username,
				role: role
            };
			if (process.versions.electron) {
				const { setUserTouch } = require(path.resolve(__dirname, 'macos/touchbar.js'));
				const { setUserMenu } = require(path.resolve(__dirname, 'macos/menu.js'));
				
				setUserTouch(req.session.user);
				setUserMenu(req.session.user);
			}
		}
	}

	if (login) {
		res.redirect('/');
	} else {
		res.render('signin-failed');
	}
});

// Sign Out
expressApp.get('/logout', (req, res) => {
	if (process.versions.electron) {
		const { resetUserTouch } = require(path.resolve(__dirname, 'macos/touchbar.js'));
		const { resetUserMenu } = require(path.resolve(__dirname, 'macos/menu.js'));
		resetUserTouch();
		resetUserMenu();
	}
    req.session.destroy();
    res.redirect('/signin');
});

// Setup Admin User if not already exists
function setupAdminUser() {
	let adminCount = users.toJSON().filter(entry => entry.data.role === 'admin').length;
    if (adminCount === 0) {
        const admin = {
			username: "admin",
			password: hash("Password"),
			role: "admin",
			name: "Admin",
			checkout: []
		};
		users.set(admin.username, admin);
    }
}

setupAdminUser();
