app.js

import mysql from 'mysql';
import express from 'express';
import bodyParser from 'body-parser';
import session from 'express-session';

const app = express();

// Middleware setup
app.use(bodyParser.urlencoded({ extended: true }));
app.use(express.static('public'));
app.use(session({ secret: 'your-secret-key', resave: false, saveUninitialized: false }));
app.set('view engine', 'ejs');

// Create a MySQL connection pool
const con = mysql.createPool({
    host: "localhost",
    user: "root",
    password: "",
    database: "ecommerce_db",
});

// Utility function to execute database queries
function queryDatabase(query, params) {
    return new Promise((resolve, reject) => {
        con.query(query, params, (error, results) => {
            if (error) {
                reject(error);
            } else {
                resolve(results);
            }
        });
    });
}

// Utility function to calculate total and prepare cart data
function calculateCartDetails(cart, productResults) {
    const productMap = {};
    productResults.forEach(product => {
        productMap[product.id] = product;
    });

    let total = 0;
    cart.forEach(item => {
        const product = productMap[item.productId];
        if (product) {
            total += product.price * item.quantity; // Calculate total
        }
    });

    return { productMap, total };
}

// Product Routes
app.get('/', async (req, res) => {
    try {
        const products = await queryDatabase('SELECT * FROM products', []);
        res.render('pages/product', { products });
    } catch (error) {
        console.error('Database query error:', error);
        return res.status(500).send('Internal Server Error');
    }
});

// Cart Routes
app.get('/cart', async (req, res) => {
    const cart = req.session.cart || []; // Get cart from session

    if (cart.length === 0) {
        return res.render('pages/cart', { cart, total: 0 });
    }

    const productIds = cart.map(item => item.productId);
    const placeholders = productIds.map(() => '?').join(',');

    try {
        const productResults = await queryDatabase(`SELECT id, name, price FROM products WHERE id IN (${placeholders})`, productIds);
        const { productMap, total } = calculateCartDetails(cart, productResults);
        res.render('pages/cart', { cart, productMap, total });
    } catch (error) {
        console.error('Database query error:', error);
        return res.status(500).send('Internal Server Error');
    }
});

// Add to Cart (POST)
app.post('/cart/add', (req, res) => {
    const productId = req.body.productId;
    const quantity = parseInt(req.body.quantity, 10) || 1;

    // Initialize cart if it doesn't exist
    if (!req.session.cart) {
        req.session.cart = [];
    }

    // Check if the product is already in the cart
    const existingProduct = req.session.cart.find(item => item.productId === productId);
    if (existingProduct) {
        existingProduct.quantity += quantity; // Update quantity
    } else {
        req.session.cart.push({ productId, quantity }); // Add new product
    }

    res.redirect('/cart'); // Redirect to cart
});

// Remove from Cart (POST)
app.post('/cart/remove', (req, res) => {
    const productId = req.body.productId;

    // Filter out the product to remove
    req.session.cart = req.session.cart.filter(item => item.productId !== productId);
    res.redirect('/cart'); // Redirect to cart
});

// Payment Routes
app.get('/payment', async (req, res) => {
    const cart = req.session.cart || [];

    if (cart.length === 0) {
        return res.redirect('/'); // Redirect to products if cart is empty
    }

    const productIds = cart.map(item => item.productId);
    const placeholders = productIds.map(() => '?').join(',');

    try {
        const productResults = await queryDatabase(`SELECT id, name, price FROM products WHERE id IN (${placeholders})`, productIds);
        const { productMap, total } = calculateCartDetails(cart, productResults);
        res.render('pages/payment', { cart, productMap, total });
    } catch (error) {
        console.error('Database query error:', error);
        return res.status(500).send('Internal Server Error');
    }
});

// Payment Route
app.post('/payment', async (req, res) => {
    const { orderId, total } = req.body;

    try {
        // Render the payment page with order details
        res.render('pages/payment', { orderId, total });
    } catch (error) {
        console.error('Payment processing error:', error);
        return res.status(500).send('Internal Server Error');
    }
});

// Checkout Route (POST)
app.post('/checkout', async (req, res) => {
    const { orderId, paymentMethod } = req.body;

    try {
        // Update order status to "paid" and save the payment method
        await queryDatabase('UPDATE orders SET status = ?, payment_method = ? WHERE id = ?', ['paid', paymentMethod, orderId]);

        // Clear the cart after payment
        req.session.cart = [];

        // Render a success page or send a confirmation message
        res.render('pages/checkout', { orderId, paymentMethod });
    } catch (error) {
        console.error('Error updating payment status:', error);
        return res.status(500).send('Internal Server Error');
    }
});



// Start the server
app.listen(3001, () => {
    console.log('Server running on http://localhost:3001');
});

// Order Route
app.get('/order', async (req, res) => {
    const cart = req.session.cart || [];

    if (cart.length === 0) {
        return res.redirect('/'); // Redirect to products if cart is empty
    }

    const productIds = cart.map(item => item.productId);
    const placeholders = productIds.map(() => '?').join(',');

    try {
        // Retrieve product details for the cart items
        const productResults = await queryDatabase(
            `SELECT id, name, price FROM products WHERE id IN (${placeholders})`,
            productIds
        );
        const { productMap, total } = calculateCartDetails(cart, productResults);

        // Insert order into the database with status "not paid"
        const orderResult = await queryDatabase(
            'INSERT INTO orders (status, total) VALUES (?, ?)',
            ['not paid', total]
        );
        const orderId = orderResult.insertId;

        // Insert each cart item as an order item linked to the order ID
        await Promise.all(
            cart.map(item =>
                queryDatabase(
                    'INSERT INTO order_items (order_id, product_id, quantity, price) VALUES (?, ?, ?, ?)',
                    [orderId, item.productId, item.quantity, productMap[item.productId].price]
                )
            )
        );

        // Clear the cart after placing the order
        req.session.cart = [];

        // Render confirmation page with order details
        res.render('pages/order_confirmation', { orderId, total });
    } catch (error) {
        console.error('Database query error:', error);
        return res.status(500).send('Internal Server Error');
    }
});

cart.ejs

<body>
    <h1>Your Cart</h1>
    <ul>
        <% cart.forEach(item=> { %>
            <li>
                Product: <%= productMap[item.productId].name %> <br>
                    Price: $<%= productMap[item.productId].price.toFixed(2) %> <br>
                        Quantity: <%= item.quantity %> <br>
                            Total: $<%= (productMap[item.productId].price * item.quantity).toFixed(2) %><br>
                                <form action="/cart/remove" method="POST" style="display:inline;">
                                    <input type="hidden" name="productId" value="<%= item.productId %>">
                                    <button type="submit">Remove</button>
                                </form>
            </li>
            <% }) %>
    </ul>
    <h2>Total Price: $<%= total.toFixed(2) %>
    </h2>
    <a href="/order">Place Order</a>
    <a href="/">Continue Shopping</a>
</body>

</html>

checkout.ejs

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Payment Success</title>
</head>
<body>
    <h1>Pa

yment Successful!</h1>
    <p>Thank you for your payment for Order #<%= orderId %>.</p>
    <p>Your order status is now: Paid</p>
    <a href="/">Return to Home</a>
</body>
</html>

order_confirmation.ejs

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Order Confirmation</title>
</head>
<body>
    <h1>Order Confirmation</h1>
    <p>Thank you for your order!</p>
    <p>Order ID: <%= orderId %></p>
    <p>Total Amount: $<%= total.toFixed(2) %></p>
    <p>Status: Not Paid</p>

    <!-- Payment Button -->
    <form action="/payment" method="POST">
        <input type="hidden" name="orderId" value="<%= orderId %>">
        <input type="hidden" name="total" value="<%= total %>">
        <button type="submit">Proceed to Payment</button>
    </form>
</body>
</html>

payment.ejs

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Payment</title>
</head>
<body>
    <h1>Payment for Order #<%= orderId %></h1>
    <p>Total Amount: $<%= parseFloat(total).toFixed(2) %></p>

    <!-- Payment Form with Payment Method Selection -->
    <form action="/checkout" method="POST">
        <input type="hidden" name="orderId" value="<%= orderId %>">
        
        <h3>Select Payment Method:</h3>
        <label>
            <input type="radio" name="paymentMethod" value="Credit Card" required> Credit Card
        </label><br>
        <label>
            <input type="radio" name="paymentMethod" value="PayPal" required> PayPal
        </label><br>
        <label>
            <input type="radio" name="paymentMethod" value="Bank Transfer" required> Bank Transfer
        </label><br>

        <button type="submit">Complete Payment</button>
    </form>
</body>
</html>

product.ejs

<body>
    <header>
        <%- include('../partials/header') %>
    </header>

    <main>
        <h1>Products</h1>
        <ul>
            <% products.forEach(product => { %>
                <li>
                    <h2><%= product.name %></h2>
                    <p>Price: $<%= product.price %></p>
                    <p>Description: <%= product.description %></p>
                    <img src="<%= product.image %>" alt="<%= product.name %>" />
                    <form action="/cart/add" method="POST">
                        <input type="hidden" name="productId" value="<%= product.id %>">
                        <input type="number" name="quantity" value="1" min="1">
                        <button type="submit">Add to Cart</button>
                    </form>
                </li>
            <% }) %>
        </ul>
    </main>

    <footer>
        <%- include('../partials/footer') %>
    </footer>
</body>
</html>

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    status VARCHAR(20),
    total DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    price DECIMAL(10, 2),
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

ALTER TABLE orders ADD COLUMN payment_method VARCHAR(50);