server.js

const express = require('express');
const cors = require('cors');
const path = require('path');
const multer = require('multer');
const db = require('./config/db');
require('dotenv').config();

const app = express();
const port = process.env.PORT || 3000;
// Multer Config 
const UPLOAD_DIR = 'public/uploads/';

app.use(cors());
app.use(express.json());
app.use(express.urlencoded({ extended: true }));
app.use(express.static(path.join(__dirname, 'public')));

const storage = multer.diskStorage({
    destination: (req, file, cb) => cb(null, UPLOAD_DIR),
    filename: (req, file, cb) => {
        const uniqueSuffix = Date.now() + '-' + Math.round(Math.random() * 1E9);
        cb(null, uniqueSuffix + path.extname(file.originalname));
    }
});
const upload = multer({ storage: storage });

app.use('/api/auth', require('./routes/authRoutes'));
app.use('/api/categories', require('./routes/categoryRoutes'));

// ================= API: BOOKS ================= //
app.get('/api/books', async (req, res) => {
    try {
        const sql = `
            SELECT books.*, users.username as seller_name, 
            GROUP_CONCAT(DISTINCT c.name) as category_names,
            GROUP_CONCAT(DISTINCT images.image_url) as extra_images 
            FROM books 
            JOIN users ON books.seller_id = users.user_id
            LEFT JOIN book_categories bc ON books.book_id = bc.book_id
            LEFT JOIN categories c ON bc.category_id = c.category_id
            LEFT JOIN images ON books.book_id = images.book_id
            WHERE books.status = 'available' 
            GROUP BY books.book_id 
            ORDER BY books.created_at DESC`;

        const [rows] = await db.query(sql);
        res.json(rows);
    } catch (err) {
        console.error(err);
        res.status(500).json({ error: 'DB Error' });
    }
});

app.get('/api/books/search', async (req, res) => {
    try {
        const { q, categories, language, condition, min_price, max_price } = req.query;

        let sql = `
            SELECT books.*, users.username as seller_name,
            GROUP_CONCAT(DISTINCT c.name) as category_names,
            GROUP_CONCAT(DISTINCT images.image_url) as extra_images
            FROM books 
            JOIN users ON books.seller_id = users.user_id
            LEFT JOIN book_categories bc ON books.book_id = bc.book_id
            LEFT JOIN categories c ON bc.category_id = c.category_id
            LEFT JOIN images ON books.book_id = images.book_id
            WHERE books.status = 'available'
        `;
        let params = [];

        if (q) {
            sql += " AND (books.title LIKE ? OR books.author LIKE ?)";
            params.push(`%${q}%`, `%${q}%`);
        }

        if (categories) {
            const catList = categories.split(',');
            const placeholders = catList.map(() => '?').join(',');

            sql += ` AND books.book_id IN (
                SELECT bc_sub.book_id FROM book_categories bc_sub
                JOIN categories c_sub ON bc_sub.category_id = c_sub.category_id
                WHERE c_sub.name IN (${placeholders})
                GROUP BY bc_sub.book_id
                HAVING COUNT(DISTINCT c_sub.name) = ?
            )`;

            params.push(...catList);
            params.push(catList.length);
        }

        if (language) {
            sql += " AND books.language LIKE ?";
            params.push(`${language}%`);
        }
        if (condition) {
            sql += " AND books.`condition` = ?";
            params.push(condition);
        }

        if (min_price) {
            sql += " AND books.price >= ?";
            params.push(min_price);
        }
        if (max_price) {
            sql += " AND books.price <= ?";
            params.push(max_price);
        }

        sql += " GROUP BY books.book_id ORDER BY books.created_at DESC";

        const [rows] = await db.query(sql, params);
        res.json(rows);

    } catch (error) {
        console.error("Search Error:", error);
        res.status(500).json({ error: "Search failed" });
    }
});

app.get('/api/books/:id', async (req, res) => { // book detail per id
    try {
        const [bookRows] = await db.query(`SELECT books.*, 
            users.username as seller_name, 
            GROUP_CONCAT(images.image_url) as extra_images 
            FROM books JOIN users ON books.seller_id = users.user_id LEFT JOIN images ON books.book_id = images.book_id
            WHERE books.book_id = ? GROUP BY books.book_id`, [req.params.id]);
        if (bookRows.length === 0) return res.status(404).json({ error: 'Not found' });

        const [catRows] = await db.query(`SELECT c.category_id, c.name 
            FROM book_categories bc JOIN categories c ON bc.category_id = c.category_id 
            WHERE bc.book_id = ?`, [req.params.id]);
        res.json({ ...bookRows[0], categories: catRows, extra_images: bookRows[0].extra_images ? bookRows[0].extra_images.split(',') : [] });
    } catch (err) { res.status(500).json({ error: 'DB Error' }); }
});

app.post('/api/books', upload.array('book_images', 5), async (req, res) => {
    try {
        const { seller_id, title, author, price, quantity, description, condition, language, categories } = req.body;
        if (!req.files.length) return res.status(400).json({ error: 'No image' });

        const [resBook] = await db.query(`INSERT INTO books 
            (seller_id, title, author, description, price, \`condition\`, language, quantity, cover_image) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
            [seller_id, title, author, description, price, condition, language, quantity, `/uploads/${req.files[0].filename}`]);

        const newId = resBook.insertId;
        if (req.files.length > 1) {
            await Promise.all(req.files.slice(1).map(f => db.query('INSERT INTO images (book_id, image_url) VALUES (?, ?)',
                [newId, `/uploads/${f.filename}`])));
        }
        if (categories) {
            await Promise.all(categories.split(',').map(cid => db.query('INSERT INTO book_categories (book_id, category_id) VALUES (?, ?)',
                [newId, cid])));
        }
        res.json({ success: true });
    } catch (err) { res.status(500).json({ error: err.message }); }
});

app.post('/api/books/update', upload.array('book_images'), async (req, res) => {
    try {
        const { book_id, title, author, price, quantity, description, language, condition, categories } = req.body;

        await db.query(`
            UPDATE books 
            SET title=?, author=?, price=?, quantity=?, description=?, language=?, \`condition\`=? 
            WHERE book_id=?
        `, [title, author, price, quantity, description, language, condition, book_id]);

        if (categories) {
            await db.query('DELETE FROM book_categories WHERE book_id = ?', [book_id]);
            const catArray = categories.split(',').filter(c => c.trim() !== "");
            if (catArray.length > 0) {
                const catValues = catArray.map(catId => [book_id, catId]);
                await db.query('INSERT INTO book_categories (book_id, category_id) VALUES ?', [catValues]);
            }
        }
        res.json({ success: true, message: 'Book updated successfully' });
    } catch (err) {
        console.error("Update Error:", err);
        res.status(500).json({ error: err.message });
    }
});

app.delete('/api/books/:id', async (req, res) => {
    try {
        const bookId = req.params.id;

        const [checkRows] = await db.query('SELECT COUNT(*) as count FROM item WHERE book_id = ?', [bookId]);
        const isSold = checkRows[0].count > 0;

        if (isSold) {
            await db.query(`UPDATE books SET status = 'removed' WHERE book_id = ?`, [bookId]);
            console.log(`Soft deleted book id: ${bookId} (Marked as removed)`);
        } else {
            await db.query('DELETE FROM images WHERE book_id = ?', [bookId]);
            await db.query('DELETE FROM book_categories WHERE book_id = ?', [bookId]);
            await db.query('DELETE FROM books WHERE book_id = ?', [bookId]);
            console.log(`Hard deleted book id: ${bookId} (Removed from DB)`);
        }

        res.json({ success: true, message: isSold ? 'Book archived' : 'Book deleted' });

    } catch (err) {
        console.error("Delete Book Error:", err);
        res.status(500).json({ error: 'Database Error' });
    }
});

// ================= API: USERS ================= //
app.post('/api/users/update', upload.single('profile_picture'), async (req, res) => {
    const connection = await db.getConnection();
    try {
        await connection.beginTransaction();

        const { user_id, name, email, phone, address, bank_name, acc_num, acc_name, password } = req.body;
        let userUpdates = [], userValues = [];
        if (name) { userUpdates.push('username = ?'); userValues.push(name); }
        if (email) { userUpdates.push('email = ?'); userValues.push(email); }
        if (phone) { userUpdates.push('phone_num = ?'); userValues.push(phone.replace(/-/g, '')); }
        if (address) { userUpdates.push('default_address = ?'); userValues.push(address); }
        if (password) { userUpdates.push('password = ?'); userValues.push(password); }
        if (req.file) { userUpdates.push('profile_picture = ?'); userValues.push(`/uploads/${req.file.filename}`); }

        if (userUpdates.length > 0) {
            await connection.query(`UPDATE users SET ${userUpdates.join(', ')} WHERE user_id = ?`, [...userValues, user_id]);
        }

        if (bank_name || acc_num || acc_name) {
            const [existingBank] = await connection.query('SELECT account_id FROM bank_accounts WHERE user_id = ?', [user_id]);

            if (existingBank.length > 0) {
                let bankUpdates = [], bankValues = [];
                if (bank_name) { bankUpdates.push('bank_name = ?'); bankValues.push(bank_name); }
                if (acc_num) { bankUpdates.push('bank_account_number = ?'); bankValues.push(acc_num); }
                if (acc_name) { bankUpdates.push('bank_account_name = ?'); bankValues.push(acc_name); }

                if (bankUpdates.length > 0) {
                    await connection.query(`UPDATE bank_accounts SET ${bankUpdates.join(', ')} WHERE user_id = ?`,
                        [...bankValues, user_id]);
                }
            } else {
                await connection.query(
                    `INSERT INTO bank_accounts (user_id, bank_name, bank_account_number, bank_account_name) VALUES (?, ?, ?, ?)`,
                    [user_id, bank_name, acc_num, acc_name]
                );
            }
        }

        await connection.commit();
        const [updatedUser] = await db.query(`
            SELECT u.*, b.bank_name, b.bank_account_number, b.bank_account_name
            FROM users u LEFT JOIN bank_accounts b ON u.user_id = b.user_id
            WHERE u.user_id = ?`, [user_id]);

        res.json({ success: true, user: updatedUser[0] });

    } catch (err) {
        await connection.rollback();
        res.status(500).json({ error: err.message });
    } finally {
        connection.release();
    }
});

app.get('/api/users/:id', async (req, res) => {
    try {
        const sql = `
            SELECT u.user_id, u.username, u.email, u.phone_num, u.default_address, u.profile_picture, u.user_role,
                   b.bank_name, b.bank_account_number, b.bank_account_name
            FROM users u
            LEFT JOIN bank_accounts b ON u.user_id = b.user_id
            WHERE u.user_id = ?
        `;

        const [rows] = await db.query(sql, [req.params.id]);

        if (rows.length === 0) return res.status(404).json({ error: 'User not found' });

        res.json(rows[0]);
    } catch (err) {
        console.error("Critical Error in /api/users/:id:", err);
        res.status(500).json({ error: 'DB Error: Cannot retrieve user profile data.' });
    }
});

app.get('/api/seller/dashboard/:id', async (req, res) => {
    try {
        const uid = req.params.id;
        const [inv] = await db.query(`SELECT COUNT(*) as count FROM books WHERE seller_id = ? AND status = 'available'`, [uid]);
        const [sales] = await db.query(`
            SELECT 
                COALESCE(SUM(CAST(i.total_item_price AS DECIMAL(10, 2))), 0) as total_balance, 
                COUNT(i.item_id) as sold_count 
            FROM item i 
            JOIN \`transaction\` t ON i.transaction_id = t.transaction_id 
            WHERE i.seller_id = ? AND t.transaction_status = 'Completed'
        `, [uid]);

        const [books] = await db.query(`SELECT * FROM books WHERE seller_id = ? ORDER BY created_at DESC`, [uid]);

        res.json({
            stats: {
                inventory: inv[0].count,
                balance: sales[0].total_balance,
                sold: sales[0].sold_count
            },
            books
        });

    } catch (err) {
        console.error(err);
        res.status(500).json({ error: 'DB Error' });
    }
});

app.get('/api/seller/inventory/:id', async (req, res) => {
    try {
        const [rows] = await db.query(`SELECT * FROM books WHERE seller_id = ? AND status = 'available' ORDER BY created_at DESC`,
            [req.params.id]);
        res.json(rows);
    } catch (err) { res.status(500).json({ error: 'DB Error' }); }
});

app.get('/api/seller/sold/:id', async (req, res) => {
    try {
        const sql = `
            SELECT 
                books.book_id, 
                books.title, 
                books.cover_image, 
                
                SUM(i.quantity) as total_sold,
                i.price_per_unit as sold_price_per_unit,
                
                MAX(t.created_at) as last_sold_at
            FROM item i
            JOIN books ON i.book_id = books.book_id
            JOIN \`transaction\` t ON i.transaction_id = t.transaction_id
            WHERE i.seller_id = ? AND t.transaction_status = 'Completed'
            
            GROUP BY books.book_id, i.price_per_unit 
            ORDER BY last_sold_at DESC
        `;
        const [rows] = await db.query(sql, [req.params.id]);
        res.json(rows);
    } catch (err) { res.status(500).json({ error: 'DB Error' }); }
});

// ================= API: ORDERS ================= //
app.post('/api/orders', async (req, res) => {
    const connection = await db.getConnection();
    try {
        await connection.beginTransaction();

        const { buyer_id, total_amount, items, delivery_address } = req.body;

        let finalAddress = delivery_address;

        if (!finalAddress) {
            const [userRows] = await connection.query('SELECT default_address FROM users WHERE user_id = ?', [buyer_id]);
            finalAddress = userRows[0]?.default_address || '-';
        }

        const [transResult] = await connection.query(
            `INSERT INTO \`transaction\` (buyer_id, total_price, transaction_status, delivery_address) VALUES (?, ?, 'Completed', ?)`,
            [buyer_id, total_amount, finalAddress]
        );

        const transactionId = transResult.insertId;
        for (const item of items) {
            await connection.query(
                `INSERT INTO item (transaction_id, book_id, seller_id, quantity, price_per_unit, total_item_price) VALUES (?, ?, ?, ?, ?, ?)`,
                [
                    transactionId,
                    item.id,
                    item.sellerId,
                    item.qty,
                    item.price,
                    (item.price * item.qty)
                ]
            );

            await connection.query(
                `UPDATE books SET quantity = quantity - ? WHERE book_id = ?`,
                [item.qty, item.id]
            );
        }

        await connection.commit();
        res.json({ success: true, orderId: transactionId });

    } catch (err) {
        await connection.rollback();
        console.error("Order Error:", err);
        res.status(500).json({ error: err.message });
    } finally {
        connection.release();
    }
});

app.get('/', (req, res) => res.redirect('/HTML/homepage.html'));
app.listen(port, () => {
    console.log(`Server running at http://localhost:${port}`);
});