jurong_circle_shopping_black/routes/shopbackend.js

const express = require('express');
const router = express.Router();
const { getDB } = require('../database');

router.get('/', async (req, res) => {
  try {
    res.json({
        success: true,
            data: {
                text: '123'
            }
    });
    console.log(req)
  } catch (error) {
    res.status(500).json({ error: 'Internal server error' });
  }
});

router.get('/product-details/:id', async (req, res) => {
    try {
        const { id } = req.params;
        const query = `
            SELECT id, name, price, description, stock, image_url
            FROM products
            WHERE id = ?
        `;
        const [product] = await getDB().execute(query, [id]);
        if (product.length === 0) {
            return res.status(404).json({ error: 'Product not found' });
        }
        res.json({
            success: true,
            data: product[0]
        });
    } catch (error) {
        res.status(500).json({ error: 'Internal server error', message: error.message });
    }
})

router.get('/flash-products', async (req, res) => {
    try {
        const { page = 1, limit = 10, search = '', category = '' } = req.query;
        
        // 确保参数为有效数字
        const pageNum = Math.max(1, parseInt(page) || 1);
        const limitNum = Math.max(1, Math.min(100, parseInt(limit) || 10)); // 限制最大100条
        const offset = Math.max(0, (pageNum - 1) * limitNum);
        
        console.log('分页参数:', { pageNum, limitNum, offset, search, category});

        let whereClause = 'WHERE 1=1';
        const params = [];
        
        if (search) {
            whereClause += ' AND name LIKE ?';
            params.push(`%${search}%`);
        }
        
        if (category) {
            whereClause += ' AND category = ?';
            params.push(category);
        }
        
        // 获取总数
        const countQuery = `SELECT COUNT(*) as total FROM flash_product ${whereClause}`;
        const [countResult] = await getDB().execute(countQuery, params);
        const total = countResult[0].total;
        
        // 获取商品列表
        const query = `
            SELECT id, start_time, end_time, flash_stock, flash_price, products_id
            FROM flash_product
            ${whereClause}
            ORDER BY id DESC
            LIMIT ${limitNum} OFFSET ${offset}
        `;
            
        // 确保参数数组正确传递
        const queryParams = [...params];
        console.log('Query params:', queryParams, 'Query:', query);
        const [flashProducts] = await getDB().execute(query, queryParams);


        // 处理商品详情
        for (const item of flashProducts) {
            const productQuery = `
                SELECT *
                FROM products
                WHERE id = ?
            `;
            const [product] = await getDB().execute(productQuery, [item.products_id]);
            item.product_name = product[0].name
            item.product_price = product[0].price
        }

        res.json({
            success: true,
            data: {
                flashProducts,
                pagination: {
                page: pageNum,
                limit: limitNum,
                total,
                pages: Math.ceil(total / limitNum)
                }
            }
        });
    } catch (error) {
        res.status(500).json({ error: 'Internal server error', message: error.message });
    }
})

router.get('/flash-product/:id', async (req, res) => {
    try {
        const { id } = req.params;
        const query = `
            SELECT id, start_time, end_time, flash_stock, flash_price, products_id
            FROM flash_product
            WHERE products_id = ?
        `;
        const [flashProduct] = await getDB().execute(query, [id]);
        if (flashProduct.length === 0) {
            return res.status(404).json({ error: 'Flash product not found' });
        }
        res.json({
            success: true,
            data: flashProduct[0]
        });
    } catch (error) {
        res.status(500).json({ error: 'Internal server error', message: error.message });
    }
})

router.delete('/flash-product/:id', async (req, res) => {
    try {
        const { id } = req.params;
        const query = `
            DELETE FROM flash_product
            WHERE products_id = ?
        `;
        const [result] = await getDB().execute(query, [id]);
        if (result.affectedRows === 0) {
            return res.status(404).json({ error: 'Flash product not found' });
        }
        res.json({
            success: true,
            message: 'Flash product deleted successfully'
        });
    } catch (error) {
        res.status(500).json({ error: 'Internal server error', message: error.message });
    }
})

router.post('/flash-product', async (req, res) => {
    try {
        const { start_time, end_time, flash_stock, flash_price, products_id } = req.body;
        const query = `
            INSERT INTO flash_product (start_time, end_time, flash_stock, flash_price, products_id)
            VALUES (?, ?, ?, ?, ?)
        `;
        const [result] = await getDB().execute(query, [start_time, end_time, flash_stock, flash_price, products_id]);
        res.json({
            success: true,
            message: 'Flash product created successfully',
            id: result.insertId
        });
    } catch (error) {
        res.status(500).json({ error: 'Internal server error', message: error.message });
    }
})

router.put('/flash-product/:id', async (req, res) => {
    try {
        const { id } = req.params;
        const { start_time, end_time, flash_stock, flash_price, products_id } = req.body;
        const query = `
            UPDATE flash_product
            SET start_time = ?, end_time = ?, flash_stock = ?, flash_price = ?, products_id = ?
            WHERE products_id = ?
        `;
        const [result] = await getDB().execute(query, [start_time, end_time, flash_stock, flash_price, products_id, id]);
        if (result.affectedRows === 0) {
            return res.status(404).json({ error: 'Flash product not found' });
        }
        res.json({
            success: true,
            message: 'Flash product updated successfully'
        });
    } catch (error) {
        res.status(500).json({ error: 'Internal server error', message: error.message });
    }
})







router.get('/recommend-products', async (req, res) => {
    try {
        const { page = 1, limit = 10, search = '', category = '' } = req.query;
        
        // 确保参数为有效数字
        const pageNum = Math.max(1, parseInt(page) || 1);
        const limitNum = Math.max(1, Math.min(100, parseInt(limit) || 10)); // 限制最大100条
        const offset = Math.max(0, (pageNum - 1) * limitNum);
        
        console.log('分页参数:', { pageNum, limitNum, offset, search, category});

        let whereClause = 'WHERE 1=1';
        const params = [];
        
        if (search) {
            whereClause += ' AND name LIKE ?';
            params.push(`%${search}%`);
        }
        
        if (category) {
            whereClause += ' AND category = ?';
            params.push(category);
        }
        
        // 获取总数
        const countQuery = `SELECT COUNT(*) as total FROM recommend_product ${whereClause}`;
        const [countResult] = await getDB().execute(countQuery, params);
        console.log('countResult:', countResult);
        const total = countResult[0].total;
        
        // 获取商品列表
        const query = `
            SELECT id, products_id
            FROM recommend_product
            ${whereClause}
            ORDER BY id DESC
            LIMIT ${limitNum} OFFSET ${offset}
        `;
            
        // 确保参数数组正确传递
        const queryParams = [...params];
        const [recommendProducts] = await getDB().execute(query, queryParams);

        // 处理商品详情
        for (const item of recommendProducts) {
            const productQuery = `
                SELECT *
                FROM products
                WHERE id = ?
            `;
            const [product] = await getDB().execute(productQuery, [item.products_id]);
            item.product_detail = product[0];
        }

        res.json({
            success: true,
            data: {
                recommendProducts,
                pagination: {
                page: pageNum,
                limit: limitNum,
                total,
                pages: Math.ceil(total / limitNum)
                }
            }
        });
    } catch (error) {
        res.status(500).json({ error: 'Internal server error', message: error.message });
    }
})

router.post('/recommend-product', async (req, res) => {
    try {
        const { products_id } = req.body;
        const query = `
            INSERT INTO recommend_product (products_id)
            VALUES (?)
        `;
        const [result] = await getDB().execute(query, [products_id]);
        res.json({
            success: true,
            message: 'Recommend product created successfully',
            id: result.insertId
        });
    } catch (error) {
        res.status(500).json({ error: 'Internal server error', message: error.message });
    }
})

router.delete('/recommend-product/:id', async (req, res) => {
    try {
        const { id } = req.params;
        const query = `
            DELETE FROM recommend_product
            WHERE products_id = ?
        `;
        const [result] = await getDB().execute(query, [id]);
        if (result.affectedRows === 0) {
            return res.status(404).json({ error: 'Recommend product not found' });
        }
        res.json({
            success: true,
            message: 'Recommend product deleted successfully'
        });
    } catch (error) {
        res.status(500).json({ error: 'Internal server error', message: error.message });
    }
})

// 供应商
router.get('/producer-list', async (req, res) => {
    try {
        const { page = 1, limit = 10, search = '', category = '' } = req.query;
        
        // 确保参数为有效数字
        const pageNum = Math.max(1, parseInt(page) || 1);
        const limitNum = Math.max(1, Math.min(100, parseInt(limit) || 10)); // 限制最大100条
        const offset = Math.max(0, (pageNum - 1) * limitNum);
        
        console.log('分页参数:', { pageNum, limitNum, offset, search, category});

        let whereClause = 'WHERE 1=1';
        const params = [];
        
        if (search) {
            whereClause += ' AND name LIKE ?';
            params.push(`%${search}%`);
        }
        
        if (category) {
            whereClause += ' AND category = ?';
            params.push(category);
        }
        
        // 获取总数
        const countQuery = `SELECT COUNT(*) as total FROM flash_product ${whereClause}`;
        const [countResult] = await getDB().execute(countQuery, params);
        const total = countResult[0].total;
        
        // 获取商品列表
        const query = `
            SELECT id, producer_name
            FROM producer
            ${whereClause}
            ORDER BY id DESC
            LIMIT ${limitNum} OFFSET ${offset}
        `;
            
        // 确保参数数组正确传递
        const queryParams = [...params];
        console.log('Query params:', queryParams, 'Query:', query);
        const [producerList] = await getDB().execute(query, queryParams);
        res.json({
            success: true,
            data: {
                producerList,
                pagination: {
                page: pageNum,
                limit: limitNum,
                total,
                pages: Math.ceil(total / limitNum)
                }
            }
        });
    } catch (error) {
        res.status(500).json({ error: 'Internal server error', message: error.message });
    }
})

router.post('/producer', async (req, res) => {
    try {
        const { producer_name } = req.body;
        const query = `
            INSERT INTO producer (producer_name)
            VALUES (?)
        `;
        const [result] = await getDB().execute(query, [producer_name]);
        res.json({
            success: true,
            message: 'Producer created successfully',
            id: result.insertId
        });
    } catch (error) {
        res.status(500).json({ error: 'Internal server error', message: error.message });
    }
})

router.delete('/producer/:id', async (req, res) => {
    try {
        const { id } = req.params;
        const query = `
            DELETE FROM producer
            WHERE id = ?
        `;
        const [result] = await getDB().execute(query, [id]);
        if (result.affectedRows === 0) {
            return res.status(404).json({ error: 'Producer not found' });
        }
        res.json({
            success: true,
            message: 'Producer deleted successfully'
        });
    } catch (error) {
        res.status(500).json({ error: 'Internal server error', message: error.message });
    }
})

module.exports = router;