const express = require('express');
const router = express.Router();
const db = require('../database/db');

// Middleware to check if user is logged in
const isAuthenticated = (req, res, next) => {
    if (req.session.userId) {
        next();
    } else {
        res.redirect('/auth/login');
    }
};

// Get all products
router.get('/', isAuthenticated, async (req, res) => {
    try {
        const { search } = req.query;
        
        // Build query with search condition
        let sql = `
            SELECT p.*, c.name as category_name 
            FROM products p
            LEFT JOIN categories c ON p.category_id = c.id
        `;
        
        let params = [];
        if (search) {
            sql += ` WHERE p.name LIKE ? OR p.location LIKE ?`;
            params = [`%${search}%`, `%${search}%`];
        }
        
        // Execute query
        const [products] = await db.query(sql, params);
        const [categories] = await db.query('SELECT * FROM categories');

        // Add helper function to be available in template
        const getStockStatus = (current, minimum) => {
            current = parseInt(current) || 0;
            minimum = parseInt(minimum) || 0;
            
            if (current <= 0) {
                return { text: 'หมด', class: 'status-out' };
            }
            if (current <= minimum) {
                return { text: 'ต่ำ', class: 'status-low' };
            }
            return { text: 'ปกติ', class: 'status-normal' };
        };
        
        // Pass search value back to template
        res.render('products', { 
            products, 
            categories,
            searchQuery: search || '',
            getStockStatus // Pass the function to the template
        });
    } catch (err) {
        console.error(err);
        res.status(500).json({ error: 'Database error' });
    }
});

// Add product
router.post('/', isAuthenticated, async (req, res) => {
    try {
        const { 
            name, 
            price, 
            stock_quantity, 
            minimum_stock, 
            location, 
            category_id 
        } = req.body;

        await db.query(
            `INSERT INTO products (
                name, 
                price, 
                stock_quantity, 
                minimum_stock, 
                location, 
                category_id, 
                user_id
            ) VALUES (?, ?, ?, ?, ?, ?, ?)`,
            [
                name, 
                price, 
                stock_quantity || 0, 
                minimum_stock || 0, 
                location || '', 
                category_id,
                req.session.userId
            ]
        );
        res.redirect('/products');
    } catch (err) {
        console.error(err);
        res.status(400).json({ error: 'Error adding product' });
    }
});

// Get edit form
router.get('/edit/:id', isAuthenticated, async (req, res) => {
    try {
        const [products] = await db.query(
            'SELECT * FROM products WHERE id = ?', 
            [req.params.id]
        );
        const [categories] = await db.query('SELECT * FROM categories');

        if (products.length === 0) {
            return res.status(404).send('Product not found');
        }

        res.render('edit-product', { 
            product: products[0],
            categories: categories 
        });
    } catch (err) {
        console.error(err);
        res.status(500).json({ error: 'Database error' });
    }
});

// Get product by ID
router.get('/:id', isAuthenticated, async (req, res) => {
    try {
        const [products] = await db.query(
            'SELECT * FROM products WHERE id = ?', 
            [req.params.id]
        );
        
        if (products.length === 0) {
            return res.status(404).json({ error: 'Product not found' });
        }
        
        res.json(products[0]);
    } catch (err) {
        console.error(err);
        res.status(500).json({ error: 'Database error' });
    }
});

// Update product
router.put('/:id', isAuthenticated, async (req, res) => {
    try {
        const { 
            name, 
            price, 
            stock_quantity, 
            minimum_stock, 
            location, 
            category_id 
        } = req.body;

        await db.query(`
            UPDATE products 
            SET name = ?, 
                price = ?, 
                stock_quantity = ?,
                minimum_stock = ?,
                location = ?,
                category_id = ?
            WHERE id = ?
        `, [
            name, 
            price, 
            stock_quantity, 
            minimum_stock, 
            location, 
            category_id, 
            req.params.id
        ]);

        res.json({ message: 'Product updated successfully' });
    } catch (err) {
        console.error(err);
        res.status(500).json({ error: 'Database error' });
    }
});

// Delete product
router.delete('/:id', isAuthenticated, async (req, res) => {
    try {
        await db.query('DELETE FROM products WHERE id = ?', [req.params.id]);
        res.json({ message: 'Product deleted successfully' });
    } catch (err) {
        console.error(err);
        res.status(400).json({ error: 'Error deleting product' });
    }
});

// เพิ่มสต็อก
router.post('/stock/add', isAuthenticated, async (req, res) => {
    try {
        const { product_id, quantity, description } = req.body;
        
        await db.query('START TRANSACTION');
        
        // บันทึกการเพิ่มสต็อก
        await db.query(
            'INSERT INTO stock_movements (product_id, quantity, type, user_id, description) VALUES (?, ?, "in", ?, ?)',
            [product_id, quantity, req.session.userId, description]
        );
        
        // อัพเดตจำนวนสต็อก
        await db.query(
            'UPDATE products SET stock_quantity = stock_quantity + ? WHERE id = ?',
            [quantity, product_id]
        );
        
        await db.query('COMMIT');
        res.json({ message: 'Stock added successfully' });
    } catch (err) {
        await db.query('ROLLBACK');
        console.error(err);
        res.status(400).json({ error: 'Error adding stock' });
    }
});

// ตัดสต็อก
router.post('/stock/remove', isAuthenticated, async (req, res) => {
    try {
        const { product_id, quantity, description } = req.body;
        
        await db.query('START TRANSACTION');
        
        // ตรวจสอบจำนวนสต็อก
        const [product] = await db.query(
            'SELECT stock_quantity FROM products WHERE id = ?',
            [product_id]
        );
        
        if (product[0].stock_quantity < quantity) {
            await db.query('ROLLBACK');
            return res.status(400).json({ error: 'Insufficient stock' });
        }
        
        // บันทึกการตัดสต็อก
        await db.query(
            'INSERT INTO stock_movements (product_id, quantity, type, user_id, description) VALUES (?, ?, "out", ?, ?)',
            [product_id, quantity, req.session.userId, description]
        );
        
        // อัพเดตจำนวนสต็อก
        await db.query(
            'UPDATE products SET stock_quantity = stock_quantity - ? WHERE id = ?',
            [quantity, product_id]
        );
        
        await db.query('COMMIT');
        res.json({ message: 'Stock removed successfully' });
    } catch (err) {
        await db.query('ROLLBACK');
        console.error(err);
        res.status(400).json({ error: 'Error removing stock' });
    }
});

// ดูประวัติการเคลื่อนไหวของสต็อก
router.get('/stock/history/:productId', isAuthenticated, async (req, res) => {
    try {
        const [movements] = await db.query(`
            SELECT sm.*, u.username, p.name as product_name
            FROM stock_movements sm
            JOIN users u ON sm.user_id = u.id
            JOIN products p ON sm.product_id = p.id
            WHERE sm.product_id = ?
            ORDER BY sm.date DESC
        `, [req.params.productId]);
        
        res.json(movements);
    } catch (err) {
        console.error(err);
        res.status(500).json({ error: 'Error fetching stock history' });
    }
});

module.exports = router;