EcommerceSystem.java

package DBMS;

import java.sql.*;
import java.util.*;

public class EcommerceSystem {

    static final String URL = "jdbc:mysql://localhost:3306/ecommerce1";
    static final String USER = "root";
    static final String PASS = "root123";

    static Scanner sc = new Scanner(System.in);

    public static void main(String[] args) {

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (Exception e) {
            System.out.println("Driver Error!");
        }

        while (true) {
            try {
                printMainMenu();
                int choice = Integer.parseInt(sc.nextLine());

                switch (choice) {
                    case 1 -> userLogin();
                    case 2 -> sellerLogin();
                    case 3 -> registerUser();
                    case 4 -> registerSeller();
                    case 5 -> {
                        System.out.println("👋 Exiting...");
                        return;
                    }
                    default -> System.out.println("❌ Invalid choice!");
                }

            } catch (Exception e) {
                System.out.println("❌ Invalid input!");
            }
        }
    }

    // ================= UI =================

    static void printMainMenu() {
        System.out.println("\n╔══════════════════════════════════════╗");
        System.out.println("║        🛒 E-COMMERCE SYSTEM         ║");
        System.out.println("╠══════════════════════════════════════╣");
        System.out.println("║ 1. 👤 User Login                    ║");
        System.out.println("║ 2. 🏪 Seller Login                  ║");
        System.out.println("║ 3. 📝 Register User                 ║");
        System.out.println("║ 4. 🏬 Register Seller               ║");
        System.out.println("║ 5. ❌ Exit                          ║");
        System.out.println("╚══════════════════════════════════════╝");
        System.out.print("👉 Enter choice: ");
    }

    static void line() {
        System.out.println("────────────────────────────────────────────");
    }

    // ================= USER =================

    static void registerUser() {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS)) {

            System.out.print("Name: ");
            String name = sc.nextLine();
            System.out.print("Email: ");
            String email = sc.nextLine();
            System.out.print("Password: ");
            String pass = sc.nextLine();

            String sql = "INSERT INTO users(name, email, password) VALUES (?, ?, ?)";
            PreparedStatement ps = conn.prepareStatement(sql);

            ps.setString(1, name);
            ps.setString(2, email);
            ps.setString(3, pass);

            ps.executeUpdate();
            System.out.println("✔ User Registered!");

        } catch (Exception e) {
            System.out.println("❌ " + e.getMessage());
        }
    }

    static void userLogin() {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS)) {

            System.out.print("Email: ");
            String email = sc.nextLine();
            System.out.print("Password: ");
            String pass = sc.nextLine();

            String sql = "SELECT * FROM users WHERE email=? AND password=?";
            PreparedStatement ps = conn.prepareStatement(sql);

            ps.setString(1, email);
            ps.setString(2, pass);

            ResultSet rs = ps.executeQuery();

            if (rs.next()) {
                int userId = rs.getInt("user_id");

                updateLastLogin(userId);

                System.out.println("✔ Welcome " + rs.getString("name"));
                Timestamp lastLogin = rs.getTimestamp("last_login");

                if (lastLogin == null) {
                    System.out.println("🕒 First Login!");
                } else {
                    System.out.println("🕒 Last Login: " + lastLogin);
                }

                userMenu(userId);
            } else {
                System.out.println("❌ Invalid login!");
            }

        } catch (Exception e) {
            System.out.println("❌ " + e.getMessage());
        }
    }

    static void updateLastLogin(int userId) {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS)) {

            String sql = "UPDATE users SET last_login=CURRENT_TIMESTAMP WHERE user_id=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, userId);
            ps.executeUpdate();

        } catch (Exception e) {
            System.out.println("❌ Last login update error");
        }
    }

    static void userMenu(int userId) {
        while (true) {
            System.out.println("\n1 View Products");
            System.out.println("2 Add to Cart");
            System.out.println("3 View Cart");
            System.out.println("4 Place Order");
            System.out.println("5 Logout");

            int ch = Integer.parseInt(sc.nextLine());

            if (ch == 1) viewProducts();
            else if (ch == 2) addToCart(userId);
            else if (ch == 3) viewCart(userId);
            else if (ch == 4) placeOrder(userId);
            else if (ch == 5) return;
        }
    }

    static void viewProducts() {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS)) {

            String sql = "SELECT * FROM products";
            ResultSet rs = conn.createStatement().executeQuery(sql);

            System.out.println("\n📦 AVAILABLE PRODUCTS");
            System.out.println("────────────────────────────────────────────────────────");
            System.out.printf("%-5s %-20s %-15s %-10s %-10s\n",
                    "ID", "Name", "Category", "Price", "Stock");
            System.out.println("────────────────────────────────────────────────────────");

            while (rs.next()) {
                System.out.printf("%-5d %-20s %-15s ₹%-9.2f %-10d\n",
                        rs.getInt("product_id"),
                        rs.getString("product_name"),
                        rs.getString("product_category"),
                        rs.getDouble("price"),
                        rs.getInt("stock_quantity"));
            }

            System.out.println("────────────────────────────────────────────────────────");

        } catch (Exception e) {
            System.out.println("❌ " + e.getMessage());
        }
    }

    // ================= SELLER =================

    static void registerSeller() {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS)) {

            System.out.print("Enter your email: ");
            String email = sc.nextLine();

            PreparedStatement ps = conn.prepareStatement("SELECT user_id FROM users WHERE email=?");
            ps.setString(1, email);

            ResultSet rs = ps.executeQuery();

            if (!rs.next()) {
                System.out.println("User not found!");
                return;
            }

            int userId = rs.getInt("user_id");

            System.out.print("Shop name: ");
            String shop = sc.nextLine();

            PreparedStatement ps2 = conn.prepareStatement(
                    "INSERT INTO sellers(user_id, shop_name) VALUES (?, ?)");
            ps2.setInt(1, userId);
            ps2.setString(2, shop);

            ps2.executeUpdate();
            System.out.println("✔ Seller Registered!");

        } catch (Exception e) {
            System.out.println("❌ " + e.getMessage());
        }
    }

    static void sellerLogin() {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS)) {

            System.out.print("Email: ");
            String email = sc.nextLine();

            PreparedStatement ps = conn.prepareStatement(
                    "SELECT s.seller_id FROM sellers s JOIN users u ON s.user_id=u.user_id WHERE u.email=?");
            ps.setString(1, email);

            ResultSet rs = ps.executeQuery();

            if (rs.next()) {
                sellerMenu(rs.getInt("seller_id"));
            } else {
                System.out.println("❌ Not seller!");
            }

        } catch (Exception e) {
            System.out.println("❌ " + e.getMessage());
        }
    }

    static void sellerMenu(int sellerId) {
        while (true) {
            System.out.println("\n1 Add Product");
            System.out.println("2 View My Products");
            System.out.println("3 Logout");

            int ch = Integer.parseInt(sc.nextLine());

            if (ch == 1) addProduct(sellerId);
            else if (ch == 2) viewMyProducts(sellerId);
            else if (ch == 3) return;
        }
    }

    static void addProduct(int sellerId) {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS)) {

            System.out.print("Name: ");
            String name = sc.nextLine();
            System.out.print("Category: ");
            String cat = sc.nextLine();
            System.out.print("Price: ");
            double price = Double.parseDouble(sc.nextLine());
            System.out.print("Stock: ");
            int stock = Integer.parseInt(sc.nextLine());

            PreparedStatement ps = conn.prepareStatement(
                    "INSERT INTO products(seller_id, product_name, product_category, price, stock_quantity) VALUES (?, ?, ?, ?, ?)");

            ps.setInt(1, sellerId);
            ps.setString(2, name);
            ps.setString(3, cat);
            ps.setDouble(4, price);
            ps.setInt(5, stock);

            ps.executeUpdate();
            System.out.println("✔ Product Added!");

        } catch (Exception e) {
            System.out.println("❌ " + e.getMessage());
        }
    }

    static void viewMyProducts(int sellerId) {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS)) {

            PreparedStatement ps = conn.prepareStatement(
                    "SELECT * FROM products WHERE seller_id=?");
            ps.setInt(1, sellerId);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                System.out.println(rs.getString("product_name"));
            }

        } catch (Exception e) {
            System.out.println("❌ " + e.getMessage());
        }
    }

    // ================= CART =================

    static void addToCart(int userId) {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS)) {

            System.out.print("Product ID: ");
            int pid = Integer.parseInt(sc.nextLine());

            System.out.print("Quantity: ");
            int qty = Integer.parseInt(sc.nextLine());

            PreparedStatement ps = conn.prepareStatement(
                    "INSERT INTO cart(user_id, product_id, quantity) VALUES (?, ?, ?)");

            ps.setInt(1, userId);
            ps.setInt(2, pid);
            ps.setInt(3, qty);

            ps.executeUpdate();
            System.out.println("✔ Added to cart!");

        } catch (Exception e) {
            System.out.println("❌ " + e.getMessage());
        }
    }

    static void viewCart(int userId) {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS)) {

            String sql = "SELECT p.product_name, p.price, c.quantity " +
                         "FROM cart c JOIN products p ON c.product_id = p.product_id " +
                         "WHERE c.user_id=?";

            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setInt(1, userId);

            ResultSet rs = ps.executeQuery();

            double total = 0;

            System.out.println("\n🛒 YOUR CART");
            System.out.println("────────────────────────────────────────────");
            System.out.printf("%-15s %-10s %-10s %-10s\n", "Product", "Price", "Qty", "Total");
            System.out.println("────────────────────────────────────────────");

            while (rs.next()) {
                String name = rs.getString("product_name");
                double price = rs.getDouble("price");
                int qty = rs.getInt("quantity");

                double subTotal = price * qty;
                total += subTotal;

                System.out.printf("%-15s ₹%-9.2f %-10d ₹%-10.2f\n", name, price, qty, subTotal);
            }

            System.out.println("────────────────────────────────────────────");
            System.out.println("Total Amount: ₹" + total);

        } catch (Exception e) {
            System.out.println("❌ " + e.getMessage());
        }
    }

    static void placeOrder(int userId) {
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS)) {

            // Get cart items
            PreparedStatement ps = conn.prepareStatement(
                    "SELECT product_id, quantity FROM cart WHERE user_id=?");
            ps.setInt(1, userId);

            ResultSet rs = ps.executeQuery();

            double total = 0;

            while (rs.next()) {
                int pid = rs.getInt("product_id");
                int qty = rs.getInt("quantity");

                // Get price
                PreparedStatement ps2 = conn.prepareStatement(
                        "SELECT price, stock_quantity FROM products WHERE product_id=?");
                ps2.setInt(1, pid);

                ResultSet rs2 = ps2.executeQuery();

                if (rs2.next()) {
                    double price = rs2.getDouble("price");
                    int stock = rs2.getInt("stock_quantity");

                    if (stock < qty) {
                        System.out.println("❌ Not enough stock for product ID " + pid);
                        return;
                    }

                    total += price * qty;

                    // Reduce stock
                    PreparedStatement ps3 = conn.prepareStatement(
                            "UPDATE products SET stock_quantity = stock_quantity - ? WHERE product_id=?");
                    ps3.setInt(1, qty);
                    ps3.setInt(2, pid);
                    ps3.executeUpdate();
                }
            }

            if (total == 0) {
                System.out.println("Cart empty!");
                return;
            }

            System.out.println("🎉 Order placed! Total ₹" + total);

            // Clear cart
            conn.createStatement().executeUpdate("DELETE FROM cart WHERE user_id=" + userId);

        } catch (Exception e) {
            System.out.println("❌ " + e.getMessage());
        }
    }}