require('dotenv').config();
const express = require('express');
const mysql = require('mysql2');
const bodyParser = require('body-parser');
const session = require('express-session');
const multer = require('multer');
const csv = require('csv-parser');
const fs = require('fs');
const path = require('path');

const app = express();
const upload = multer({ dest: 'uploads/' });

// Database Connection
const db = mysql.createPool({
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASS,
    database: process.env.DB_NAME,
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

// Middleware
app.use(bodyParser.urlencoded({ extended: true }));
app.use(express.static(path.join(__dirname, 'public')));
app.set('view engine', 'ejs');
app.use(session({
    secret: process.env.SESSION_SECRET,
    resave: false,
    saveUninitialized: true
}));

const requireLogin = (req, res, next) => {
    if (req.session.loggedIn) next();
    else res.redirect('/');
};

// --- ROUTES ---

// Login
app.get('/', (req, res) => {
    if (req.session.loggedIn) return res.redirect('/dashboard');
    res.render('login', { error: null });
});

app.post('/login', (req, res) => {
    const { username, password } = req.body;
    db.query('SELECT * FROM users WHERE username = ? AND password = ?', [username, password], (err, results) => {
        if (err) throw err;
        if (results.length > 0) {
            req.session.loggedIn = true;
            res.redirect('/dashboard');
        } else {
            res.render('login', { error: 'Invalid credentials' });
        }
    });
});

// Dashboard
app.get('/dashboard', requireLogin, (req, res) => {
    const query = req.query.search || '';
    const sql = `SELECT * FROM raw_materials WHERE code LIKE ? OR description LIKE ? ORDER BY code ASC`;
    const searchParam = `%${query}%`;
    
    db.query(sql, [searchParam, searchParam], (err, results) => {
        if (err) return res.send("Database error");
        res.render('dashboard', { materials: results, search: query });
    });
});

// Add Material
app.post('/add', requireLogin, (req, res) => {
    const { code, description } = req.body;
    db.query('INSERT INTO raw_materials (code, description) VALUES (?, ?)', 
    [code, description], (err) => {
        if (err) console.error(err);
        res.redirect('/dashboard');
    });
});

// Edit Material
app.post('/edit', requireLogin, (req, res) => {
    const { id, code, description } = req.body;
    db.query('UPDATE raw_materials SET code=?, description=? WHERE id=?', 
    [code, description, id], (err) => {
        if (err) console.error(err);
        res.redirect('/dashboard');
    });
});

// Import CSV
app.post('/import', requireLogin, upload.single('csvfile'), (req, res) => {
    if (!req.file) return res.redirect('/dashboard');

    const results = [];
    fs.createReadStream(req.file.path)
        .pipe(csv())
        .on('data', (data) => {
            if(data.code && data.description) {
                results.push([data.code, data.description]);
            }
        })
        .on('end', () => {
            if (results.length > 0) {
                const sql = 'INSERT INTO raw_materials (code, description) VALUES ?';
                db.query(sql, [results], (err) => {
                    if (err) console.error("Import Error:", err);
                    fs.unlinkSync(req.file.path);
                    res.redirect('/dashboard');
                });
            } else {
                fs.unlinkSync(req.file.path);
                res.redirect('/dashboard');
            }
        });
});

// --- NEW USER ROUTES ---

// Add User Page (GET)
app.get('/add-user', requireLogin, (req, res) => {
    res.render('add-user', { message: null, error: null });
});

// Add User Logic (POST)
app.post('/add-user', requireLogin, (req, res) => {
    const { username, password } = req.body;
    
    // Check if user already exists
    db.query('SELECT * FROM users WHERE username = ?', [username], (err, results) => {
        if (err) {
            console.error(err);
            return res.render('add-user', { message: null, error: 'Database error' });
        }
        
        if (results.length > 0) {
            return res.render('add-user', { message: null, error: 'Username already exists' });
        }

        // Insert new user
        db.query('INSERT INTO users (username, password) VALUES (?, ?)', [username, password], (err) => {
            if (err) {
                 console.error(err);
                 return res.render('add-user', { message: null, error: 'Error adding user' });
            }
            res.render('add-user', { message: 'User added successfully!', error: null });
        });
    });
});

app.get('/logout', (req, res) => {
    req.session.destroy();
    res.redirect('/');
});

const PORT = process.env.PORT || 3000;
app.listen(PORT, () => {
    console.log(`Server running on port ${PORT}`);
});