const express = require("express");
const router = express.Router();
const htmlPdf = require('html-pdf');
const mustache = require('mustache');
const fs = require("fs");
const path = require("path");
const { upload, multerErrorHandler } = require("../middlewares/fileUpload");
const { db } = require("../config/dbConfig");
const PDFDocument = require('pdfkit')

router.get('/api/orders', (req, res) => {
    const query = `
    SELECT 
      id,
      enquiry_id as order_id,
      name as customer_name,
      mobile,
      district,
      state,
      order_date,
      total_amount,
      order_details,
      status
    FROM orders
    ORDER BY order_date DESC
  `;

    db.query(query, (err, results) => {
        if (err) {
            console.error('Error fetching orders:', err);
            return res.status(500).json({ message: 'Error fetching orders' });
        }
        res.json(results);
    });
});

// Fetch a single order by ID
router.get("/api/orders/:orderId", (req, res) => {
    const { orderId } = req.params;

    const query = `
    SELECT 
      enquiry_id, 
      status, 
      updated_at 
    FROM orders 
    WHERE enquiry_id = ?
  `;

    db.query(query, [orderId], (error, results) => {
        if (error) {
            console.error("Database error:", error);
            return res.status(500).json({
                success: false,
                message: "Error fetching order details",
            });
        }

        if (results.length === 0) {
            return res.status(404).json({
                success: false,
                message: "Order not found",
            });
        }

        res.json({
            success: true,
            data: results[0],
        });
    });
});


// 
//
// return only uers order details


router.get('/api/orders/by-mobile/:mobile', (req, res) => {
    const mobile = req.params.mobile;

    const query = `
    SELECT 
      id,
      enquiry_id as order_id,
      name as customer_name,
      mobile,
      district,
      state,
      order_date,
      total_amount,
      order_details,
      status
    FROM orders
    WHERE mobile = ?
    ORDER BY order_date DESC
  `;

    db.query(query, [mobile], (err, results) => {
        if (err) {
            console.error('Error fetching orders by mobile:', err);
            return res.status(500).json({ message: 'Error fetching orders by mobile' });
        }

        res.json(results);
    });
});

// router.get('api/orders', async(req, res) => {
//     const { mobile } = req.query;

//     let query = `SELECT * FROM orders`;
//     let params = [];

//     if (mobile) {
//         query += ` WHERE mobile = ?`;
//         params.push(mobile);
//     }

//     try {
//         db.query(query, params, (err, results) => {
//             if (err) {
//                 console.error('Error fetching orders:', err);
//                 return res.status(500).json({ message: 'Internal server error' });
//             }
//             return res.status(200).json(results);
//         });
//     } catch (error) {
//         console.error('Error:', error);
//         res.status(500).json({ message: 'Something went wrong' });
//     }
// });


// Update order status
router.put('/api/orders/:orderId/status', (req, res) => {
    const { orderId } = req.params;
    const { status } = req.body;

    const query = `
    UPDATE orders 
    SET status = ? 
    WHERE enquiry_id = ?
  `;

    db.query(query, [status, orderId], (err, result) => {
        if (err) {
            console.error('Error updating order status:', err);
            return res.status(500).json({ message: 'Error updating order status' });
        }
        res.json({ message: 'Order status updated successfully' });
    });
});

// Delete an order
router.delete("/api/orders/:orderId", (req, res) => {
    const { orderId } = req.params;

    const query = "DELETE FROM orders WHERE enquiry_id = ?";

    db.query(query, [orderId], (err, result) => {
        if (err) {
            console.error("Error deleting order:", err);
            return res.status(500).json({ message: "Error deleting order" });
        }

        if (result.affectedRows === 0) {
            return res.status(404).json({ message: "Order not found" });
        }

        res.json({ message: "Order deleted successfully" });
    });
});


// Add this route in your backend
router.get('/api/get-user-details', async(req, res) => {
    const { mobile } = req.query;

    try {
        // Fetch District and State from users table based on mobile number
        const getUserDetailsQuery = `
        SELECT district, state 
        FROM users 
        WHERE mobile = ?
      `;

        db.query(getUserDetailsQuery, [mobile], (err, results) => {
            if (err || results.length === 0) {
                return res.status(404).json({ message: 'User not found' });
            }

            // Return the user's district and state
            res.status(200).json({
                district: results[0].district,
                state: results[0].state
            });
        });
    } catch (error) {
        console.error('Error fetching user details:', error);
        res.status(500).json({ message: 'Error fetching user details' });
    }
});

// Place a new order
router.post('/api/place-order', async(req, res) => {
    const { userName, userMobile, cart, orderDate, totalAmount } = req.body;

    try {
        // Generate enquiry ID (SGA + YEAR + 5-digit sequential number)
        const year = new Date().getFullYear();

        // Get the last order number for this year
        const getLastOrderQuery = `
            SELECT enquiry_id 
            FROM orders 
            WHERE enquiry_id LIKE 'SGA${year}%' 
            ORDER BY enquiry_id DESC 
            LIMIT 1
        `;

        db.query(getLastOrderQuery, async(err, results) => {
            if (err) {
                console.error('Error getting last order:', err);
                return res.status(500).json({ message: 'Error generating enquiry ID' });
            }

            let orderNumber = 1;
            if (results.length > 0) {
                const lastNumber = parseInt(results[0].enquiry_id.slice(-5));
                orderNumber = lastNumber + 1;
            }

            const enquiryId = `SGA${year}${orderNumber.toString().padStart(5, '0')}`;

            // Fetch District and State from users table based on mobile number
            const getUserDetailsQuery = `
                SELECT district, state 
                FROM users 
                WHERE mobile = ?
            `;

            db.query(getUserDetailsQuery, [userMobile], (err, userResults) => {
                if (err || userResults.length === 0) {
                    return res.status(500).json({ message: 'Error fetching user details' });
                }

                const { district, state } = userResults[0];

                // Fetch cracker names and construct order details
                const crackerIds = Object.keys(cart);
                const getCrackersQuery = `SELECT id, product_name FROM crackers WHERE id IN (?)`;

                db.query(getCrackersQuery, [crackerIds], (err, crackers) => {
                    if (err || crackers.length === 0) {
                        return res.status(500).json({ message: 'Error fetching crackers' });
                    }

                    // Construct order details with cracker names and quantities
                    const orderDetails = crackers.map(cracker => ({
                        name: cracker.product_name,
                        quantity: cart[cracker.id]
                    }));

                    // Insert the order into the orders table
                    const insertOrderQuery = `
                        INSERT INTO orders (
                            enquiry_id, name, mobile, district, state, order_date, total_amount, order_details
                        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                    `;

                    db.query(insertOrderQuery, [
                        enquiryId,
                        userName,
                        userMobile,
                        district,
                        state,
                        orderDate,
                        totalAmount,
                        JSON.stringify(orderDetails)
                    ], (err) => {
                        if (err) {
                            console.error('Error inserting order:', err);
                            return res.status(500).json({ message: 'Error saving order' });
                        }

                        // Update cracker quantities after placing the order
                        Object.entries(cart).forEach(([crackerId, quantity]) => {
                            db.query(
                                'UPDATE crackers SET quantity = quantity - ? WHERE id = ?', [quantity, crackerId],
                                (updateErr) => {
                                    if (updateErr) {
                                        console.error(`Error updating stock for cracker ${crackerId}:`, updateErr);
                                    }
                                }
                            );
                        });

                        res.status(200).json({
                            message: 'Order placed successfully',
                            enquiryId,
                            totalAmount
                        });
                    });
                });
            });
        });
    } catch (error) {
        console.error('Error processing order:', error);
        res.status(500).json({ message: 'Error processing order' });
    }
});


// Fetch orders by user mobile
router.get("/api/user-orders/:mobile", (req, res) => {
    const { mobile } = req.params;

    const query = `
    SELECT o.*, 
           c.id AS product_id, 
           c.product_name, 
           c.price 
    FROM orders o
    LEFT JOIN crackers c ON JSON_CONTAINS(o.order_details, JSON_QUOTE(c.id), '$')
    WHERE o.mobile = ? 
    ORDER BY o.order_date DESC
  `;

    db.query(query, [mobile], (err, results) => {
        if (err) {
            console.error("Error fetching user orders:", err);
            return res.status(500).json({ message: "Error fetching orders" });
        }

        const orders = results.reduce((acc, row) => {
            const order = acc.find((o) => o.id === row.id);
            if (order) {
                if (row.product_id) {
                    order.products.push({
                        id: row.product_id,
                        product_name: row.product_name,
                        price: row.price,
                    });
                }
            } else {
                acc.push({
                    id: row.id,
                    order_id: row.enquiry_id,
                    customer_name: row.name,
                    mobile: row.mobile,
                    order_date: row.order_date,
                    total_amount: row.total_amount,
                    order_details: row.order_details,
                    status: row.status,
                    products: row.product_id ? [{
                        id: row.product_id,
                        product_name: row.product_name,
                        price: row.price,
                    }, ] : [],
                });
            }
            return acc;
        }, []);

        res.json(orders);
    });
});


// Edit order route handler
router.put('/api/orders/:orderId/edit', async(req, res) => {
    const { orderId } = req.params;
    const { name, mobile, district, state, order_details, total_amount } = req.body;

    try {
        // Input validation
        if (!name || !mobile || !district || !state || !order_details || !total_amount) {
            return res.status(400).json({ error: 'Missing required fields' });
        }

        // Begin transaction
        const connection = await pool.getConnection();
        await connection.beginTransaction();

        try {
            // Update order
            const [result] = await connection.query(
                `UPDATE orders 
         SET name = ?,
             mobile = ?,
             district = ?,
             state = ?,
             order_details = ?,
             total_amount = ?,
             updated_at = CURRENT_TIMESTAMP
         WHERE enquiry_id = ?`, [
                    name,
                    mobile,
                    district,
                    state,
                    JSON.stringify(order_details),
                    total_amount,
                    orderId
                ]
            );

            if (result.affectedRows === 0) {
                await connection.rollback();
                return res.status(404).json({ error: 'Order not found' });
            }

            // Get updated order
            const [updatedOrder] = await connection.query(
                'SELECT * FROM orders WHERE enquiry_id = ?', [orderId]
            );

            await connection.commit();

            res.json({
                success: true,
                message: 'Order updated successfully',
                order: updatedOrder[0]
            });

        } catch (error) {
            await connection.rollback();
            throw error;
        } finally {
            connection.release();
        }

    } catch (error) {
        console.error('Error updating order:', error);
        res.status(500).json({ error: 'Error updating order' });
    }
});

// Get order details for editing
router.get('/api/orders/:orderId/edit', async(req, res) => {
    const { orderId } = req.params;

    try {
        const [order] = await pool.query(
            'SELECT * FROM orders WHERE enquiry_id = ?', [orderId]
        );

        if (!order.length) {
            return res.status(404).json({ error: 'Order not found' });
        }

        // Parse the JSON string back to object
        order[0].order_details = JSON.parse(order[0].order_details);

        res.json(order[0]);

    } catch (error) {
        console.error('Error fetching order:', error);
        res.status(500).json({ error: 'Error fetching order details' });
    }
});

//To show to details to customer during payment upload
router.get('/api/orders/:enquiryId/details', (req, res) => {
    try {
        const enquiryId = req.params.enquiryId;

        const query = `
      SELECT 
        o.id,
        o.enquiry_id,
        o.name,
        o.mobile,
        o.order_date,
        o.total_amount,
        o.order_details,
        o.status,
        o.district,
        o.state
      FROM orders o
      WHERE o.enquiry_id = ?
    `;

        db.query(query, [enquiryId], (err, results) => {
            if (err) {
                console.error('Error fetching order details:', err);
                return res.status(500).json({
                    success: false,
                    message: 'Failed to fetch order details'
                });
            }

            if (!results.length) {
                return res.status(404).json({
                    success: false,
                    message: 'Order not found'
                });
            }

            const order = results[0];
            try {
                order.order_details = JSON.parse(order.order_details);
            } catch (parseError) {
                console.error('Error parsing order details:', parseError);
                order.order_details = [];
            }

            // Format response to match frontend expectations
            const formattedOrder = {
                success: true,
                customerName: order.name,
                mobile: order.mobile,
                totalAmount: parseFloat(order.total_amount),
                status: order.status,
                district: order.district,
                state: order.state,
                orderDetails: order.order_details
            };

            res.json(formattedOrder);
        });
    } catch (error) {
        console.error('Error in order details route:', error);
        res.status(500).json({
            success: false,
            message: 'Internal server error'
        });
    }
});


// Update order details in customer file
router.put('/api/orders/:enquiry_id', (req, res) => {
    const enquiryId = req.params.enquiry_id;

    // Validate required parameters
    if (!enquiryId) {
        return res.status(400).json({ error: 'Missing enquiry_id parameter' });
    }

    const { name, mobile, district, state, order_date, total_amount, order_details, status } = req.body;

    // Validate required body parameters
    if (!name || !mobile || !district || !state || !status) {
        return res.status(400).json({ error: 'Missing required fields' });
    }

    const query = `
      UPDATE orders 
      SET name = ?, 
          mobile = ?, 
          district = ?, 
          state = ?, 
          order_date = ?, 
          total_amount = ?, 
          order_details = ?, 
          status = ?
      WHERE enquiry_id = ?
  `;

    db.query(query, [
        name,
        mobile,
        district,
        state,
        order_date,
        total_amount,
        order_details,
        status,
        enquiryId
    ], (error, results) => {
        if (error) {
            console.error('Database error:', error);
            return res.status(500).json({ error: 'Database error' });
        }
        if (results.affectedRows === 0) {
            return res.status(404).json({ error: `Order with ID ${enquiryId} not found` });
        }
        res.json({
            success: true,
            data: {
                enquiry_id: enquiryId,
                status: status,
                updated_at: new Date().toISOString()
            }
        });
    });
});
// In your backend route handler
// In your backend route
router.post('/api/orders/direct-sale', async(req, res) => {
    const { customer_name, mobile, district, state, order_details, order_date, total_amount } = req.body;

    try {
        const year = new Date().getFullYear();
        const getLastOrderQuery = `
      SELECT enquiry_id 
      FROM orders 
    WHERE enquiry_id LIKE 'SGA${year}%' 

      ORDER BY enquiry_id DESC 
      LIMIT 1
    `;

        db.query(getLastOrderQuery, async(err, results) => {
            if (err) {
                console.error('Error getting last order:', err);
                return res.status(500).json({ message: 'Error generating enquiry ID' });
            }

            let orderNumber = 1;
            if (results.length > 0) {
                const lastNumber = parseInt(results[0].enquiry_id.slice(-5));
                orderNumber = lastNumber + 1;
            }

            // const enquiryId = `VGN${year}${orderNumber.toString().padStart(5, '0')}`;
            const enquiryId = `SGA${year}${orderNumber.toString().padStart(5, '0')}`;


            const insertOrderQuery = `
        INSERT INTO orders (
          enquiry_id,
          name,
          mobile,
          district,
          state,
          order_date,
          total_amount,
          order_details,
          is_direct_sale,
          status
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, true, 'pending')
      `;

            // Parse the order_details if it's a string, otherwise use as is
            const parsedOrderDetails = typeof order_details === 'string' ?
                JSON.parse(order_details) : order_details;

            // Format the order details
            const formattedOrderDetails = parsedOrderDetails.map(item => ({
                id: item.id,
                name: item.name,
                code: item.code,
                quantity: item.quantity,
                price: item.price,
                priceTotal: item.price * item.quantity
            }));

            // Stringify the formatted order details
            const orderDetailsJson = JSON.stringify(formattedOrderDetails);

            db.query(insertOrderQuery, [
                enquiryId,
                customer_name,
                mobile,
                district,
                state,
                order_date,
                total_amount,
                orderDetailsJson
            ], (err) => {
                if (err) {
                    console.error('Error inserting order:', err);
                    return res.status(500).json({ message: 'Error saving order' });
                }

                res.status(200).json({
                    message: 'Order placed successfully',
                    enquiryId,
                    total_amount
                });
            });
        });
    } catch (error) {
        console.error('Error processing order:', error);
        res.status(500).json({ message: 'Error processing order' });
    }
});


router.put('/api/orders/:orderId/update-invoice', async(req, res) => {
    const { orderId } = req.params;
    const { order_details, total_amount, customer_name, mobile, district, state } = req.body;

    try {
        // Generate invoice PDF first
        const invoiceResponse = await fetch('https://api.royaalcrackers.com/api/generate-invoice', {
            method: 'POST',
            headers: {
                'Content-Type': 'application/json',
                'Accept': 'application/pdf'
            },
            body: JSON.stringify({
                enquiryId: orderId,
                customerName: customer_name,
                mobile,
                district,
                state,
                orderDate: new Date().toISOString(),
                items: order_details,
                subtotal: total_amount,
                totalAmount: total_amount
            })
        });

        if (!invoiceResponse.ok) {
            throw new Error('Failed to generate invoice');
        }

        // Convert response to buffer
        const pdfBuffer = Buffer.from(await invoiceResponse.arrayBuffer());

        // Update order with PDF data
        const updateOrderQuery = `
      UPDATE orders 
      SET order_details = ?,
          total_amount = ?,
          name = ?,
          mobile = ?,
          district = ?,
          state = ?,
          invoice_pdf = ?,
          updated_at = CURRENT_TIMESTAMP
      WHERE enquiry_id = ?
    `;

        await new Promise((resolve, reject) => {
            db.query(updateOrderQuery, [
                JSON.stringify(order_details),
                total_amount,
                customer_name,
                mobile,
                district,
                state,
                pdfBuffer,
                orderId
            ], (err) => {
                if (err) reject(err);
                resolve();
            });
        });

        res.status(200).json({
            success: true,
            message: "Order updated successfully",
            orderId
        });

    } catch (error) {
        console.error("Error processing update:", error);
        res.status(500).json({ message: "Error processing update" });
    }
});


// Handle invoice downloads
router.get('/api/orders/:orderId/download-invoice', async(req, res) => {
    const { orderId } = req.params;

    try {
        const query = `
      SELECT * FROM orders 
      WHERE enquiry_id = ?
    `;

        db.query(query, [orderId], (err, results) => {
            if (err || results.length === 0) {
                return res.status(404).json({ message: 'Order not found' });
            }

            const order = results[0];
            const doc = new PDFDocument();
            const buffers = [];

            doc.on('data', buffers.push.bind(buffers));
            doc.on('end', () => {
                const pdfData = Buffer.concat(buffers);
                res.setHeader('Content-Type', 'application/pdf');
                res.setHeader('Content-Disposition', `attachment; filename=invoice-${orderId}.pdf`);
                res.send(pdfData);
            });

            // Generate PDF content
            doc.fontSize(20).text('Invoice', { align: 'center' });
            doc.moveDown();
            doc.fontSize(12).text(`Order ID: ${orderId}`);
            doc.text(`Customer: ${order.name}`);
            doc.text(`Mobile: ${order.mobile}`);
            doc.text(`District: ${order.district}`);
            doc.text(`State: ${order.state}`);
            doc.moveDown();

            const items = JSON.parse(order.order_details);
            items.forEach(item => {
                doc.text(`${item.name} - Qty: ${item.quantity} - ₹${item.price * item.quantity}`);
            });

            doc.moveDown();
            doc.fontSize(14).text(`Total Amount: ₹${order.total_amount}`, { align: 'right' });
            doc.end();
        });
    } catch (error) {
        console.error('Error generating invoice:', error);
        res.status(500).json({ message: 'Error generating invoice' });
    }
});


router.post('/api/order-crackers', (req, res) => {
    const { cart } = req.body; // Cart contains product IDs and quantities

    let totalAmount = 0;

    // Loop through the cart items and calculate total amount based on price and quantity
    const productIds = Object.keys(cart);

    db.query(`SELECT * FROM crackers WHERE id IN (${productIds.join(',')})`, (err, products) => {
        if (err || products.length === 0) {
            return res.status(500).send('Error fetching products for order');
        }

        products.forEach(product => {
            const quantityOrdered = cart[product.id];

            if (quantityOrdered > product.quantity) {
                return res.status(400).send(`Insufficient stock for ${product.product_name}`);
            }

            totalAmount += product.price * quantityOrdered;

            // Update cracker quantity in database after order is placed
            db.query(
                'UPDATE crackers SET quantity = quantity - ? WHERE id = ?', [quantityOrdered, product.id],
                (updateErr) => {
                    if (updateErr) console.error(`Error updating stock for ${product.product_name}:`, updateErr);
                }
            );
        });

        // Return total amount to frontend after successful order placement
        res.status(200).json({ message: 'Order placed successfully', totalAmount });
    });
});

router.get('/api/order-details/:enquiryId', async(req, res) => {
    try {
        const { enquiryId } = req.params;

        const query = `
      SELECT o.*, u.name as customerName, u.mobile, u.district, u.state
      FROM orders o
      JOIN users u ON o.user_mobile = u.mobile
      WHERE o.enquiry_id = ?
    `;

        db.query(query, [enquiryId], (err, results) => {
            if (err) {
                console.error('Database error:', err);
                return res.status(500).json({
                    success: false,
                    message: 'Error fetching order details'
                });
            }

            if (!results.length) {
                return res.status(404).json({
                    success: false,
                    message: 'Order not found'
                });
            }

            res.json({
                success: true,
                ...results[0]
            });
        });
    } catch (error) {
        console.error('Error:', error);
        res.status(500).json({
            success: false,
            message: 'Server error'
        });
    }
});




module.exports = router;