const express = require('express');
const xlsx = require('xlsx');
const fs = require('fs');
const path = require('path');
const fileUpload = require('express-fileupload');
const db = require('./db'); // Import database connection

const app = express();
const port = 3000;

// Use express-fileupload middleware to handle file uploads
app.use(fileUpload());

// API to import staff using the uploaded Excel file
app.post('/importstaff', async (req, res) => {
    if (!req.files || !req.files.file) {
        return res.status(400).json({ message: 'No file uploaded' });
    }

    const file = req.files.file;
    const filePath = path.join(__dirname, 'uploads', file.name); // Save the file to the 'uploads' directory

    // Ensure the 'uploads' directory exists
    if (!fs.existsSync(path.join(__dirname, 'uploads'))) {
        fs.mkdirSync(path.join(__dirname, 'uploads'));
    }

    // Move the uploaded file to the 'uploads' directory
    file.mv(filePath, async (err) => {
        if (err) {
            return res.status(500).json({ message: 'Error saving the file: ' + err.message });
        }

        try {
            // Read the Excel file
            const workbook = xlsx.readFile(filePath);
            const sheetName = workbook.SheetNames[0]; // Assuming data is in the first sheet
            const sheet = workbook.Sheets[sheetName];

            // Convert sheet data to JSON format
            const jsonData = xlsx.utils.sheet_to_json(sheet);

            // Validate and insert data into the database
            let validRows = [];
            let errors = [];

            for (let row of jsonData) {
                // Validate required fields (name, email, role)
                if (row.name && row.email && row.role && row.role.toLowerCase() === 'staff') {
                    // Check if the user already exists in the tables
                    const [existingUser] = await db.query('SELECT * FROM glb_user WHERE email = ?', [row.email]);
                    const [existingStaff] = await db.query('SELECT * FROM gym_staff WHERE email = ?', [row.email]);
                    const [existingMember] = await db.query('SELECT * FROM gym_member WHERE email = ?', [row.email]);

                    if (existingUser.length > 0 || existingStaff.length > 0 || existingMember.length > 0) {
                        errors.push({
                            row: row,
                            error: 'User already exists in one of the tables (glb_user, gym_staff, or gym_member).'
                        });
                    } else {
                        validRows.push([row.name, row.email, row.role]);
                    }
                } else {
                    errors.push({
                        row: row,
                        error: 'Invalid or missing fields'
                    });
                }
            }

            // If no valid rows, return an error
            if (validRows.length === 0) {
                return res.status(400).json({ message: 'No valid staff data found in the file.', errors });
            }

            // Insert valid rows into the gym_staff table
            const sql = 'INSERT INTO gym_staff (name, email, role) VALUES ?';
            const [result] = await db.query(sql, [validRows]);

            // Clean up: Delete the uploaded file after processing
            fs.unlinkSync(filePath);

            // Return success response
            res.status(200).json({
                message: 'Staff imported successfully',
                importedRows: result.affectedRows,
                errors
            });
        } catch (err) {
            res.status(500).json({ message: 'Error processing the Excel file: ' + err.message });
        }
    });
});

// Start the server
app.listen(port, () => {
    console.log(`Server running on http://localhost:${port}`);
});