Auction.java

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.text. *;
import java.util. *;

public class Auction {
	private static Scanner scanner = new Scanner(System.in);
	private static String username;
	private static Connection conn;
	private static Statement stmt;

	enum Category {
		ELECTRONICS, 
		BOOKS,
		HOME,
		CLOTHING,
		SPORTINGGOODS,
		OTHERS
	}
	enum Condition {
		NEW,
		LIKE_NEW,
		GOOD,
		ACCEPTABLE
	}

	enum BidStatus {
		OPENED,
		TENDERED,
		CLOSED
	}

	private static boolean LoginMenu() {
		String userpass;
		Boolean isSuccess = true;

		do {
			System.out.print("----< User Login >\n" +
					" ** To go back, enter 'back' in user ID.\n" +
					"     user ID: ");
			try{
				username = scanner.next();
				scanner.nextLine();

				if(username.equalsIgnoreCase("back")){
					return false;
				}

				System.out.print("     password: ");
				userpass = scanner.next();
				scanner.nextLine();
			}catch (java.util.InputMismatchException e) {
				System.out.println("Error: Invalid input is entered. Try again.");
				username = null;
				return false;
			}

			/* Your code should come here to check ID and password */ 
			try {
				PreparedStatement pStmt = conn.prepareStatement("SELECT COUNT(*) FROM users WHERE user_id = ? AND pw = ?");
				pStmt.setString(1, username);
				pStmt.setString(2, userpass);
				ResultSet rset = pStmt.executeQuery();

				if (!rset.next() || rset.getInt(1) == 0) {  
					/* If Login Fails */
					System.out.println("Error: Incorrect user name or password\n");
					isSuccess = false; 
				}
				else {
					System.out.println("You are successfully logged in.\n");
					return true;
				}
			}catch (SQLException e) {
				System.out.println("SQLException: " + e.getMessage());
				return false;
			}
		}
		while(!isSuccess);
		return true;
	}

	private static boolean SellMenu() {
		Category category = null;
		Condition condition = null;
		char choice;
		String name, description;
		double startPrice, buynowPrice;
		boolean useCoupon, flag_catg = true, flag_cond = true;
		Integer couponId = null;

		do{
			System.out.println(
					"----< Sell Item >\n" +
					"---- Choose a category.\n" +
					"    1. Electronics\n" +
					"    2. Books\n" +
					"    3. Home\n" +
					"    4. Clothing\n" +
					"    5. Sporting Goods\n" +
					"    6. Other Categories\n" +
					"    P. Go Back to Previous Menu"
					);

			try {
				choice = scanner.next().charAt(0);;
			}catch (java.util.InputMismatchException e) {
				System.out.println("Error: Invalid input is entered. Try again.");
				continue;
			}

			flag_catg = true;

			switch ((int) choice){
				case '1':
					category = Category.ELECTRONICS;
					continue;
				case '2':
					category = Category.BOOKS;
					continue;
				case '3':
					category = Category.HOME;
					continue;
				case '4':
					category = Category.CLOTHING;
					continue;
				case '5':
					category = Category.SPORTINGGOODS;
					continue;
				case '6':
					category = Category.OTHERS;
					continue;
				case 'p':
				case 'P':
					return false;
				default:
					System.out.println("Error: Invalid input is entered. Try again.");
					flag_catg = false;
					continue;
			}
		}while(!flag_catg);

		do{
			System.out.println(
					"---- Select the condition of the item to sell.\n" +
					"   1. New\n" +
					"   2. Like-new\n" +
					"   3. Used (Good)\n" +
					"   4. Used (Acceptable)\n" +
					"   P. Go Back to Previous Menu"
					);

			try {
				choice = scanner.next().charAt(0);;
				scanner.nextLine();
			}catch (java.util.InputMismatchException e) {
				System.out.println("Error: Invalid input is entered. Try again.");
				continue;
			}

			flag_cond = true;

			switch (choice) {
				case '1':
					condition = Condition.NEW;
					break;
				case '2':
					condition = Condition.LIKE_NEW;
					break;
				case '3':
					condition = Condition.GOOD;
					break;
				case '4':
					condition = Condition.ACCEPTABLE;
					break;
				case 'p':
				case 'P':
					return false;
				default:
					System.out.println("Error: Invalid input is entered. Try again.");
					flag_cond = false;
					continue;
			}
		}while(!flag_cond);

		try {
			System.out.println("---- Name of the item: ");
			name = scanner.nextLine();

			System.out.println("---- Description of the item (one line): ");
			description = scanner.nextLine();

			System.out.println("---- Start price: ");

			while (!scanner.hasNextDouble()) {
				scanner.next();
				System.out.println("Invalid input is entered. Please enter Start price: ");
			}

			startPrice = scanner.nextDouble();
			scanner.nextLine();

			System.out.println("---- Buy-It-Now price: ");

			while (!scanner.hasNextDouble()) {
				scanner.next();
				System.out.println("Invalid input is entered. Please enter Buy-It-Now price: ");
			}

			buynowPrice = scanner.nextDouble();
			scanner.nextLine();

			System.out.print("---- Will you use a coupon? (Y/N): ");
			useCoupon = scanner.next().equalsIgnoreCase("Y");
			scanner.nextLine();

			if(useCoupon) {
				System.out.println("---- Coupon ID: ");
				couponId = scanner.nextInt();
				scanner.nextLine();

				PreparedStatement checkStmt = conn.prepareStatement("SELECT COUNT(*) FROM coupon WHERE coupon_id = ? AND available = 'USABLE'");
				checkStmt.setInt(1, couponId);
				ResultSet rset = checkStmt.executeQuery();
				if (rset.next() && rset.getInt(1) == 0) {
					System.out.println("Error: Invalid or unusable coupon ID.");
					throw new Exception();
				}
			}

			System.out.println("---- Bid starting time is now, and Bid closing time 2 weeks later from now.");
		}catch (Exception e) {
			System.out.println("Error: Invalid input is entered. Going back to the previous menu.");
			return false;
		}

		/* TODO: Your code should come here to store the user inputs in your database */
		try{
			PreparedStatement pStmt = conn.prepareStatement("INSERT INTO item VALUES (DEFAULT, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP + INTERVAL '14days', 'OPENED', ?, ?)");
			pStmt.setString(1, name);
			pStmt.setString(2, category.name());
			pStmt.setString(3, condition.name());
			pStmt.setString(4, description);
			pStmt.setDouble(5, startPrice);
			pStmt.setDouble(6, buynowPrice);
			pStmt.setString(7, username);
			if(useCoupon) {
				pStmt.setInt(8, couponId);
			}
			else {
				pStmt.setNull(8, java.sql.Types.INTEGER);
			}
			pStmt.executeUpdate();
		} catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
			return false;
		}

		System.out.println("Your item has been successfully listed.\n");
		return true;
	}

	private static boolean SignupMenu() {
		/* 2. Sign Up */
		String new_username, userpass, usernick, isAdmin;
		System.out.print("----< Sign Up >\n" + 
				" ** To go back, enter 'back' in user ID.\n" +
				"---- user name: ");
		try {
			new_username = scanner.next();
			scanner.nextLine();
			if(new_username.equalsIgnoreCase("back")){
				return false;
			}
			System.out.print("---- password: ");
			userpass = scanner.next();
			scanner.nextLine();
			System.out.print("---- nickname: ");
			usernick = scanner.next();
			scanner.nextLine();
			System.out.print("---- In this user an administrator? (Y/N): ");
			isAdmin = scanner.next();
			scanner.nextLine();
		} catch (java.util.InputMismatchException e) {
			System.out.println("Error: Invalid input is entered. Please select again.");
			return false;
		}

		/* TODO: Your code should come here to create a user account in your database */
		try {
			PreparedStatement checkStmt = conn.prepareStatement("SELECT COUNT(*) FROM users WHERE user_id = ?");
			checkStmt.setString(1, new_username);
			ResultSet rset = checkStmt.executeQuery();

			if(rset.next() && rset.getInt(1) != 0) {
				System.out.println("Error: This user ID is already taken. Please try a different one.");
				return false;
			}

			PreparedStatement insertStmt = conn.prepareStatement("INSERT INTO users VALUES (?, ?, ?, ?)");
			insertStmt.setString(1, new_username);
			insertStmt.setString(2, userpass);
			insertStmt.setString(3, usernick);
			insertStmt.setBoolean(4, isAdmin.equalsIgnoreCase("Y"));
			insertStmt.executeUpdate();

			System.out.println("Your account has been successfully created.\n");
			return true;
		} catch(SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
			return false;
		}
	}

	private static boolean AdminMenu() {
		/* 3. Login as Administrator */
		char choice;
		String adminname, adminpass;
		String keyword, seller, user;
		int percent;
		System.out.print("----< Login as Administrator >\n" +
				" ** To go back, enter 'back' in user ID.\n" +
				"---- admin ID: ");

		try {
			adminname = scanner.next();
			scanner.nextLine();
			if(adminname.equalsIgnoreCase("back")){
				return false;
			}
			System.out.print("---- password: ");
			adminpass = scanner.nextLine();
			// TODO: check the admin's account and password.

			PreparedStatement pStmt = conn.prepareStatement("SELECT COUNT(*) FROM users WHERE user_id = ? AND pw = ? AND isAdmin = TRUE");
			pStmt.setString(1, adminname);
			pStmt.setString(2, adminpass);
			ResultSet rset = pStmt.executeQuery();
	
			if (!rset.next() || rset.getInt(1) == 0) {  
				/* If Login Fails */
				System.out.println("Error: Incorrect user name or password, or you are not an administrator");
				return false; 
			}
	
			System.out.println("You are successfully logged in as an administrator.\n");		
		} catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
			return false;
		} catch (java.util.InputMismatchException e) {
			System.out.println("Error: Invalid input is entered. Try again.");
			return false;
		}

		boolean login_success = true;

		if(!login_success){
			// login failed. go back to the previous menu.
			return false;
		}

		do {
			System.out.println(
					"----< Admin menu > \n" +
					"    1. Print Sold Items per Category \n" +
					"    2. Print Sold Items for Seller \n" +
					"    3. Print Seller Ranking \n" +
					"    4. Print Buyer Ranking \n" +
					"    5. Print All Commissions \n" +
					"    6. Release Coupon to User \n" +
					"    P. Go Back to Previous Menu"
					);

			try {
				choice = scanner.next().charAt(0);;
				scanner.nextLine();
			} catch (java.util.InputMismatchException e) {
				System.out.println("Error: Invalid input is entered. Try again.");
				continue;
			}

			if (choice == '1') {
				System.out.println("----Enter Category to search : ");
				keyword = scanner.next();
				scanner.nextLine();
				/*TODO: Print Sold Items per Category */
				System.out.println("sold item       | sold date           | seller ID   | buyer ID   | price      | commissions");
				System.out.println("-------------------------------------------------------------------------------------------");
				
				String query = "SELECT i.item_name AS sold_item, " +
				"b.selected_date AS sold_date, " +
				"i.seller_id AS seller_id, " +
				"b.bidder_id AS buyer_id, " +
				"b.bid_price AS price, " +
				"FLOOR(CASE " +
				"    WHEN i.seller_coupon_id IS NOT NULL THEN b.bid_price * 0.1 * (1 - c.percent / 100) " +
				"    ELSE b.bid_price * 0.1 " +
				"END) AS commissions " +
				"FROM item i " +
				"JOIN bid b ON i.item_id = b.item_id " +
				"LEFT JOIN coupon c ON i.seller_coupon_id = c.coupon_id " +
				"WHERE i.category = ? " +
				"AND i.bidding_status = 'TENDERED' " +
				"AND b.selected = TRUE";

				try {
					PreparedStatement pStmt = conn.prepareStatement(query);
					pStmt.setString(1, keyword);
					ResultSet rset = pStmt.executeQuery();

					while(rset.next()){
						System.out.printf("%-15s | %-19s | %-10s | %-10s | %-10.2f | %-10d\n", 
						rset.getString("sold_item"), rset.getTimestamp("sold_date").toString(), rset.getString("seller_id"), rset.getString("buyer_id"), rset.getDouble("price"), rset.getInt("commissions"));
					}
				} catch(SQLException e) {
					System.out.println("SQLException: " + e.getMessage());
				}
				continue;

			} else if (choice == '2') {
				/*TODO: Print Sold Items for Seller */
				System.out.println("---- Enter Seller ID to search : ");
				seller = scanner.next();
				scanner.nextLine();
				System.out.println("sold item       | sold date           | buyer ID   | price      | commissions");
				System.out.println("-----------------------------------------------------------------------------");

				String query = "SELECT i.item_name AS sold_item, " +
				"b.selected_date AS sold_date, " +
				"b.bidder_id AS buyer_id, " +
				"b.bid_price AS price, " +
				"FLOOR(CASE " +
				"    WHEN i.seller_coupon_id IS NOT NULL THEN b.bid_price * 0.1 * (1 - c.percent / 100) " +
				"    ELSE b.bid_price * 0.1 " +
				"END) AS commissions " +
				"FROM item i " +
				"JOIN bid b ON i.item_id = b.item_id " +
				"LEFT JOIN coupon c ON i.seller_coupon_id = c.coupon_id " +
				"WHERE i.seller_id = ? " +
				"AND i.bidding_status = 'TENDERED' " +
				"AND b.selected = TRUE";

				try {
					PreparedStatement pStmt = conn.prepareStatement(query);
					pStmt.setString(1, seller);
					ResultSet rset = pStmt.executeQuery();
				  while(rset.next()){
						System.out.printf("%-15s | %-19s | %-10s | %-10.2f | %-10d\n", 
						rset.getString("sold_item"), rset.getTimestamp("sold_date").toString(), rset.getString("buyer_id"), rset.getDouble("price"), rset.getInt("commissions"));
				  }
				} catch(SQLException e) {
					System.out.println("SQLException: " + e.getMessage());
				}
				continue;

			} else if (choice == '3') {
				/*TODO: Print Seller Ranking */
				System.out.println("seller ID   | # of items sold | Total Profit (excluding commissions)");
				System.out.println("--------------------------------------------------------------------");

				String query = "SELECT u.user_id AS seller_id, " +
				"COUNT(a.amount) AS items_sold, " +
				"SUM(a.amount) AS total_profit " +
				"FROM users u " +
				"JOIN account a ON u.user_id = a.user_id " +
				"WHERE a.amount > 0 " +
				"GROUP BY u.user_id " +
				"ORDER BY total_profit DESC";

				try {
					PreparedStatement pStmt = conn.prepareStatement(query);
					ResultSet rset = pStmt.executeQuery();
					while(rset.next()){
						System.out.printf("%-11s | %-15d | %-35.2f\n", rset.getString("seller_id"), rset.getInt("items_sold"), rset.getDouble("total_profit"));
					}
				} catch (SQLException e) {
					System.out.println("SQLException: " + e.getMessage());
				}
				continue;

			} else if (choice == '4') {
				/*TODO: Print Buyer Ranking */
				System.out.println("buyer ID   | # of items purchased | Total Money Spent ");
				System.out.println("------------------------------------------------------");

				String query = "SELECT u.user_id AS buyer_id, " +
				"COUNT(a.amount) AS items_purchased, " +
				"ABS(SUM(a.amount)) AS total_spent " +
				"FROM users u " +
				"JOIN account a ON u.user_id = a.user_id " +
				"WHERE a.amount < 0 " +
				"GROUP BY u.user_id " +
				"ORDER BY total_spent DESC";

				try {
					PreparedStatement pStmt = conn.prepareStatement(query);
					ResultSet rset = pStmt.executeQuery();
					while(rset.next()){
						System.out.printf("%-10s | %-20d | %-17.2f\n", rset.getString("buyer_id"), rset.getInt("item_purchased"), rset.getDouble("total_spent"));
					}
				} catch (SQLException e) {
					System.out.println("SQLException: " + e.getMessage());
				}
				continue;

			} else if (choice == '5') {
				/*TODO: Print All Commissions */
				System.out.println("bid ID | tendered date       | commissions");
				System.out.println("------------------------------------------");

				try {
					PreparedStatement pStmt = conn.prepareStatement("SELECT bid_id, tendered_date, commission_amount FROM commission ORDER BY tendered_date DESC");
					ResultSet rset = pStmt.executeQuery();
					while(rset.next()){
						System.out.printf("%-7d | %-19s | %-12d\n", rset.getInt("bid_id"), rset.getTimestamp("tendered_date").toString(), rset.getInt("commission_amount"));
					}
				} catch (SQLException e) {
					System.out.println("SQLException: " + e.getMessage());
				}
				continue;

			} else if (choice == '6') {
				/*TODO: Release Coupon to User */
				System.out.println("---- Enter User ID to release coupon : ");
				user = scanner.next();
				scanner.nextLine();
				System.out.println("---- Enter what percent coupon you will release : ");
				percent = scanner.nextInt();
				scanner.nextLine();

				try {
					PreparedStatement pStmt = conn.prepareStatement("INSERT INTO coupon VALUES (DEFAULT, ?, ?, 'USABLE', CURRENT_DATE, CURRENT_DATE + INTERVAL '30 days')");
					pStmt.setInt(1, percent);
					pStmt.setString(2, user);
					pStmt.executeUpdate();

					System.out.println("Coupon has been successfully released.\n");
					
				} catch (SQLException e) {
					System.out.println("SQLException: " + e.getMessage());
				}
				continue;

			} else if (choice == 'P' || choice == 'p') {
				return false;
			} else {
				System.out.println("Error: Invalid input is entered. Try again.");
				continue;
			}
		} while(true);
	}

	public static void CheckSellStatus(){
		/* TODO: Check the status of the item the current user is selling */

		System.out.println("item listed in Auction | bidder (buyer ID) | bidding price | bidding date/time \n");
		System.out.println("-------------------------------------------------------------------------------\n");

		String query = "SELECT i.item_name, b.bidder_id, b.bid_price, b.bid_date FROM item i LEFT JOIN bid b ON i.item_id = b.item_id WHERE i.seller_id = ? AND i.bidding_status = 'OPENED'";
		try {
			PreparedStatement pStmt = conn.prepareStatement(query);
			pStmt.setString(1, username);
			ResultSet rset = pStmt.executeQuery();
			while(rset.next()){
				System.out.printf("%-20s | %-15s | %-13s | %-20s\n", rset.getString("item_name"), rset.getString("bidder_id") != null ? rset.getString("bidder_id") : "NONE", rset.getObject("bid_price") != null ? String.format("%.2f", rset.getDouble("bid_price")) : "NONE", rset.getTimestamp("bid_date") != null ? rset.getTimestamp("bid_date").toString() : "NONE");
				}
		} catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
		}
	}

	public static boolean BuyItem(){
		Category category = null;
		Condition condition = null;
		char choice;
		double price, priorPrice = 0, buynowPrice = 0;
		String keyword, seller, datePosted, priorBidder = "NONE", itemname = null;
		boolean flag_catg = true, flag_cond = true;

		do {

			System.out.println( "----< Select category > : \n" +
					"    1. Electronics\n"+
					"    2. Books\n" + 
					"    3. Home\n" + 
					"    4. Clothing\n" + 
					"    5. Sporting Goods\n" +
					"    6. Other categories\n" +
					"    7. Any category\n" +
					"    P. Go Back to Previous Menu"
					);

			try {
				choice = scanner.next().charAt(0);;
				scanner.nextLine();
			} catch (java.util.InputMismatchException e) {
				System.out.println("Error: Invalid input is entered. Try again.");
				return false;
			}

			flag_catg = true;

			switch (choice) {
				case '1':
					category = Category.ELECTRONICS;
					break;
				case '2':
					category = Category.BOOKS;
					break;
				case '3':
					category = Category.HOME;
					break;
				case '4':
					category = Category.CLOTHING;
					break;
				case '5':
					category = Category.SPORTINGGOODS;
					break;
				case '6':
					category = Category.OTHERS;
					break;
				case '7':
					break;
				case 'p':
				case 'P':
					return false;
				default:
					System.out.println("Error: Invalid input is entered. Try again.");
					flag_catg = false;
					continue;
			}
		} while(!flag_catg);

		do {

			System.out.println(
					"----< Select the condition > \n" +
					"   1. New\n" +
					"   2. Like-new\n" +
					"   3. Used (Good)\n" +
					"   4. Used (Acceptable)\n" +
					"   P. Go Back to Previous Menu"
					);
			try {
				choice = scanner.next().charAt(0);;
				scanner.nextLine();
			} catch (java.util.InputMismatchException e) {
				System.out.println("Error: Invalid input is entered. Try again.");
				return false;
			}

			flag_cond = true;

			switch (choice) {
				case '1':
					condition = Condition.NEW;
					break;
				case '2':
					condition = Condition.LIKE_NEW;
					break;
				case '3':
					condition = Condition.GOOD;
					break;
				case '4':
					condition = Condition.ACCEPTABLE;
					break;
				case 'p':
				case 'P':
					return false;
				default:
					System.out.println("Error: Invalid input is entered. Try again.");
					flag_cond = false;
					continue;
				}
		} while(!flag_cond);

		try {
			System.out.println("---- Enter item keyword to search the name : ");
			keyword = scanner.next();
			scanner.nextLine();

			System.out.println("---- Enter Seller ID to search : ");
			System.out.println(" ** Enter 'any' if you want to see items from any seller. ");
			seller = scanner.next();
			scanner.nextLine();

			System.out.println("---- Enter time posted (YYYY-MM-DD HH:MM:SS): ");
			System.out.println(" ** This will search items that have been posted after the designated time.");
			datePosted = scanner.next();
			scanner.nextLine();
		} catch (java.util.InputMismatchException e) {
			System.out.println("Error: Invalid input is entered. Try again.");
			return false;
		}

		try {
			PreparedStatement pStmt = conn.prepareStatement("SELECT i.item_id, i.item_name, i.seller_id, i.closing_date, i.bidding_status, b.bid_id FROM item i LEFT JOIN bid b ON i.item_id = b.item_id WHERE bidding_status = 'OPENED'");
			ResultSet rset = pStmt.executeQuery();

			while(rset.next()) {
				LocalDateTime closingDate = rset.getTimestamp("closing_date").toLocalDateTime();
				LocalDateTime now = LocalDateTime.now();

				if(now.isAfter(closingDate)) {
					if(rset.getInt("bid_id") == 0) {
						PreparedStatement statusStmt = conn.prepareStatement("Update item SET bidding_status = 'CLOSED' WHERE item_id = ?");
						statusStmt.setInt(1, rset.getInt("item_id"));
						statusStmt.executeUpdate();
					}
					else {
						closeBid(rset.getInt("item_id"), rset.getString("item_name"), rset.getString("seller_id"));
					}
				}
			}
		} catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
		}
		

		/* TODO: Query condition: item category */
		/* TODO: Query condition: item condition */
		/* TODO: Query condition: items whose name match the keyword (use LIKE operator) */
		/* TODO: Query condition: items from a particular seller */
		/* TODO: Query condition: posted time of item */
		String selectQuery = "SELECT DISTINCT i.item_id AS item_id, i.item_name AS item_name, " +
		"i.condition AS condition, i.seller_id AS seller, " +
		"i.buy_now_price AS buy_it_now, b.prior_price AS current_bid, " +
		"b.prior_bidder_id AS highest_bidder, TO_CHAR(JUSTIFY_HOURS(i.closing_date - NOW()), 'HH24:MI:SS') AS time_left, " +
		"i.closing_date AS bid_close , i.bidding_status AS bid_status " +
		"FROM item i LEFT JOIN bid b ON i.item_id = b.item_id " +
		"WHERE (? = 'ANY' OR i.category = ?) AND i.condition = ? " +
		"AND i.item_name ILIKE '%' ||  ? || '%' AND (? = 'any' OR i.seller_id = ?) " +
		"AND i.posted_date >= TO_TIMESTAMP(?, 'YYYY-MM-DD HH24:MI:SS') AND i.bidding_status = 'OPENED' " +
		"ORDER BY i.closing_date ASC";

		/* TODO: List all items that match the query condition */
		System.out.println("Item ID | Item name        | Condition | Seller | Buy-It-Now | Current Bid | highest bidder | Time left    | bid close          ");
		System.out.println("--------------------------------------------------------------------------------------------------------------------------------");


		try {
			PreparedStatement selectStmt = conn.prepareStatement(selectQuery);
			selectStmt.setString(1, category == null ? "ANY" : category.name());
			selectStmt.setString(2, category == null ? "ANY" : category.name());
			selectStmt.setString(3, condition.name());
			selectStmt.setString(4, keyword);
			selectStmt.setString(5, seller);
			selectStmt.setString(6, seller);
			selectStmt.setString(7, datePosted);
			ResultSet rset = selectStmt.executeQuery();

			while(rset.next()){ 
				System.out.printf("%-7d | %-16s | %-9s | %-6s | %-10.2f | %-11.2f | %-14s | %-12s | %-19s\n", rset.getInt("item_id"), rset.getString("item_name"), rset.getString("condition"), rset.getString("seller"), rset.getDouble("buy_it_now"), rset.getDouble("current_bid"), rset.getString("highest_bidder"), rset.getString("time_left"), rset.getTimestamp("bid_close").toString());
			}
		} catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
		}
		


		System.out.println("---- Select Item ID to buy or bid: ");
		int itemId;
		try {
			itemId = scanner.nextInt();
			scanner.nextLine();
			System.out.println("     Price: ");
			price = scanner.nextDouble();
			scanner.nextLine();
		} catch (java.util.InputMismatchException e) {
			System.out.println("Error: Invalid input is entered. Try again.");
			return false;
		}

		/* TODO: Buy-it-now or bid: If the entered price is higher or equal to Buy-It-Now price, the bid ends. */
		/* Even if the bid price is higher than the Buy-It-Now price, the buyer pays the B-I-N price. */

		try {
			PreparedStatement pStmt = conn.prepareStatement("SELECT i.item_id, i.item_name, i.seller_id, i.closing_date, i.bidding_status, b.bid_id FROM item i LEFT JOIN bid b ON i.item_id = b.item_id WHERE bidding_status = 'OPENED' AND i.item_id = ?");
			pStmt.setInt(1, itemId);
			ResultSet rset = pStmt.executeQuery();

			int closeFlag = 0;

			while(rset.next()) {
				LocalDateTime closingDate = rset.getTimestamp("closing_date").toLocalDateTime();
				LocalDateTime now = LocalDateTime.now();

				if(now.isAfter(closingDate)) {
					if(rset.getInt("bid_id") == 0) {
						PreparedStatement statusStmt = conn.prepareStatement("Update item SET bidding_status = 'CLOSED' WHERE item_id = ?");
						statusStmt.setInt(1, rset.getInt("item_id"));
						statusStmt.executeUpdate();
					}
					else {
						closeBid(rset.getInt("item_id"), rset.getString("item_name"), rset.getString("seller_id"));
					}
					closeFlag = 1;
				}
			}
			if (closeFlag == 1) {
				System.out.println("Bid Ended.");
				return false;
			}
		} catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
		}

		try {
			PreparedStatement selectStmt = conn.prepareStatement("SELECT bid_price, bidder_id FROM bid WHERE item_id = ? ORDER BY bid_price DESC LIMIT 1");
			selectStmt.setInt(1, itemId);
			ResultSet rset = selectStmt.executeQuery();
				if(rset.next()) {
					priorPrice = rset.getDouble("bid_price");
          priorBidder = rset.getString("bidder_id");
			}

			PreparedStatement insertStmt = conn.prepareStatement("INSERT INTO bid (item_id, bidder_id, bid_price, bid_date, prior_bidder_id, prior_price) VALUES (?, ?, ?, NOW(), ?, ?)");
			insertStmt.setInt(1, itemId);
			insertStmt.setString(2, username);
			insertStmt.setDouble(3, price);
			insertStmt.setString(4, priorBidder);
			insertStmt.setDouble(5, priorPrice);
			insertStmt.executeUpdate();

			PreparedStatement checkStmt = conn.prepareStatement("SELECT item_name, buy_now_price FROM item WHERE item_id = ?");
			checkStmt.setInt(1, itemId);
			rset = checkStmt.executeQuery();
			if (rset.next()) {
				itemname = rset.getString("item_name");
				buynowPrice = rset.getDouble("buy_now_price");
			}

			if(price > priorPrice) {
				PreparedStatement updateStmt = conn.prepareStatement("UPDATE bid SET prior_price = ?, prior_bidder_id = ? WHERE item_id = ?");
				updateStmt.setDouble(1, price);
				updateStmt.setString(2, username);
				updateStmt.setInt(3, itemId);
				updateStmt.executeUpdate();
			}
			if(price > priorPrice && price < buynowPrice) {
				/* TODO: if you are the current highest bidder, print the following */
				System.out.println("Congratulations, you are the highest bidder.\n"); 
				return true;
			}
		} catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
			return false;
		}

		if(price >= buynowPrice) {
			closeBid(itemId, itemname, seller);
			/* TODO: if you won, print the following */
			System.out.println("Congratulations, the item is yours now.\n"); 
		}
		return true;
	}

	public static void CheckBuyStatus(){
		/* TODO: Check the status of the item the current buyer is bidding on */
		/* Even if you are outbidded or the bid closing date has passed, all the items this user has bidded on must be displayed */

		try {
			PreparedStatement pStmt = conn.prepareStatement("SELECT i.item_id, i.item_name, i.seller_id, i.closing_date, i.bidding_status, b.bid_id FROM item i LEFT JOIN bid b ON i.item_id = b.item_id WHERE bidding_status = 'OPENED'");
			ResultSet rset = pStmt.executeQuery();

			while(rset.next()) {
				LocalDateTime closingDate = rset.getTimestamp("closing_date").toLocalDateTime();
				LocalDateTime now = LocalDateTime.now();

				if(now.isAfter(closingDate)) {
					if(rset.getInt("bid_id") == 0) {
						PreparedStatement statusStmt = conn.prepareStatement("Update item SET bidding_status = 'CLOSED' WHERE item_id = ?");
						statusStmt.setInt(1, rset.getInt("item_id"));
						statusStmt.executeUpdate();
					}
					else {
						closeBid(rset.getInt("item_id"), rset.getString("item_name"), rset.getString("seller_id"));
					}
				}
			}
		} catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
		}

		System.out.println("item ID   | item name          | highest bidder | highest bidding price | your bidding price | bid closing date/time");
		System.out.println("--------------------------------------------------------------------------------------------------------------------");

		String query = "SELECT i.item_id AS item_id, i.item_name AS item_name, " +
		"b.prior_bidder_id AS highest_bidder, b.prior_price AS highest_bidding_price, " +
		"b.bid_price AS your_bidding_price, i.closing_date AS bid_closing_date " +
		"FROM bid b " +
		"JOIN item i ON b.item_id = i.item_id " +
		"WHERE b.bidder_id = ? " +
		"ORDER BY i.closing_date DESC";
		try {
			PreparedStatement pStmt = conn.prepareStatement(query);
			pStmt.setString(1, username);
			ResultSet rset = pStmt.executeQuery();
			while(rset.next()){
				System.out.printf("%-10d | %-18s | %-15s | %-22.2f | %-18.2f | %-20s\n", rset.getInt("item_id"), rset.getString("item_name"), rset.getString("highest_bidder"), rset.getDouble("highest_bidding_price"), rset.getDouble("your_bidding_price"), rset.getTimestamp("bid_closing_date").toString());
				}
		} catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
		}
	}

	public static void CheckAccount(){

		try {
			PreparedStatement pStmt = conn.prepareStatement("SELECT i.item_id, i.item_name, i.seller_id, i.closing_date, i.bidding_status, b.bid_id FROM item i LEFT JOIN bid b ON i.item_id = b.item_id WHERE bidding_status = 'OPENED'");
			ResultSet rset = pStmt.executeQuery();

			while(rset.next()) {
				LocalDateTime closingDate = rset.getTimestamp("closing_date").toLocalDateTime();
				LocalDateTime now = LocalDateTime.now();

				if(now.isAfter(closingDate)) {
					if(rset.getInt("bid_id") == 0) {
						PreparedStatement statusStmt = conn.prepareStatement("Update item SET bidding_status = 'CLOSED' WHERE item_id = ?");
						statusStmt.setInt(1, rset.getInt("item_id"));
						statusStmt.executeUpdate();
					}
					else {
						closeBid(rset.getInt("item_id"), rset.getString("item_name"), rset.getString("seller_id"));
					}
				}
			}
		} catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
		}

		/* TODO: Check the balance of the current user.  */
		System.out.println("[Sold Items] \n");
		System.out.println("item category  | item name | sold date           | sold price  | buyer ID | commission  ");
		System.out.println("------------------------------------------------------------------------------");

		String plusQuery = "SELECT i.category AS item_category, i.item_name AS item_name, " +
		"c.tendered_date AS sold_date, b.prior_price AS sold_price, " +
    "b.prior_bidder_id AS buyer_id, c.commission_amount AS commission " +
    "FROM item i " +
    "JOIN bid b ON i.item_id = b.item_id AND b.selected = TRUE " +
    "INNER JOIN commission c ON b.bid_id = c.bid_id " +
    "WHERE i.seller_id = ? AND i.bidding_status = 'TENDERED' " +
    "ORDER BY b.selected_date DESC";
		try {
			PreparedStatement pStmt = conn.prepareStatement(plusQuery);
			pStmt.setString(1, username);
			ResultSet rset = pStmt.executeQuery();
			while(rset.next()){
				System.out.printf("%-15s | %-10s | %-10s | %-19.2f | %-8s | %-10d\n", rset.getString("item_category"), rset.getString("item_name"), rset.getTimestamp("sold_date").toString(), rset.getDouble("sold_price"), rset.getString("buyer_id"), rset.getObject("commission") != null ? rset.getInt("commission") : 0);
			} 
		} catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
		}

		System.out.println("[Purchased Items] \n");
		System.out.println("item category  | item name | purchased date      | puchased price  | seller ID ");
		System.out.println("--------------------------------------------------------------------------");

		String minusQuery = "SELECT DISTINCT i.category AS item_category, i.item_name AS item_name, " +
		"b.selected_date AS purchased_date, b.prior_price AS purchased_price, " +
    "i.seller_id AS seller_id " +
    "FROM item i " +
    "JOIN bid b ON i.item_id = b.item_id AND b.selected = TRUE " +
    "WHERE b.prior_bidder_id = ? " +
    "ORDER BY b.selected_date DESC";

		try{
			PreparedStatement pStmt = conn.prepareStatement(minusQuery);
			pStmt.setString(1, username);
			ResultSet rset = pStmt.executeQuery();
			while(rset.next()){
				System.out.printf("%-15s | %-10s | %-19s | %-15.2f | %-10s\n", rset.getString("item_category"), rset.getString("item_name"), rset.getTimestamp("purchased_date").toString(), rset.getDouble("purchased_price"), rset.getString("seller_id"));
				}
		} catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
		}

	}

	public static void CheckCoupon() {

		System.out.println("[Your Coupons] \n");
		System.out.println("Percent of coupon | available | issue date | expire date ");
		System.out.println("----------------------------------------------------------");

		try {
			PreparedStatement pStmt = conn.prepareStatement("SELECT percent, available, issue_date, expire_date FROM coupon WHERE owner_id = ? ");
			pStmt.setString(1, username);
			ResultSet rset = pStmt.executeQuery();
			while(rset.next()) {
				System.out.printf("%-17d | %-9s | %-10s | %-11s\n", rset.getInt("percent"), rset.getString("available"), rset.getDate("issue_date").toString(), rset.getDate("expire_date").toString());
			}
		} catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
		}
		
	}

	public static void closeBid(int itemId, String itemname, String seller) {
		try {
			conn.setAutoCommit(false);

			String priorBidder = null;
			double priorPrice = 0;

			PreparedStatement priorStmt = conn.prepareStatement("SELECT prior_bidder_id, prior_price FROM bid WHERE item_id = ? ORDER BY prior_price DESC LIMIT 1");
			priorStmt.setInt(1, itemId);
			ResultSet rset = priorStmt.executeQuery();
			if (rset.next()) {
				priorBidder = rset.getString("prior_bidder_id");
				priorPrice = rset.getDouble("prior_price");
			}

			PreparedStatement bidStmt = conn.prepareStatement("UPDATE bid SET selected = TRUE, selected_date = NOW() WHERE item_id = ? AND prior_bidder_id = ?");
			bidStmt.setInt(1, itemId);
			bidStmt.setString(2, priorBidder);
			bidStmt.executeUpdate();

			PreparedStatement itemStmt = conn.prepareStatement("UPDATE item SET bidding_status = 'TENDERED' , closing_date = NOW() WHERE item_id = ?");
			itemStmt.setInt(1, itemId);
			itemStmt.executeUpdate();

			PreparedStatement buyerStmt = conn.prepareStatement("INSERT INTO account VALUES (DEFAULT, ?, ?, ?, NOW())");
			buyerStmt.setString(1, priorBidder);
			buyerStmt.setDouble(2, -priorPrice);
			buyerStmt.setString(3, itemname);
			buyerStmt.executeUpdate();

			double couponDiscount = 0;
			Integer couponId = null;

			PreparedStatement checkStmt = conn.prepareStatement("SELECT seller_coupon_id, seller_id FROM item WHERE item_id = ?");
			checkStmt.setInt(1, itemId);
			rset = checkStmt.executeQuery();
			if(rset.next()) {
				couponId = rset.getInt("seller_coupon_id");
				seller = rset.getString("seller_id");
			}

			if(couponId != null) {
				PreparedStatement percentStmt = conn.prepareStatement("SELECT percent FROM coupon WHERE coupon_id = ?");
				percentStmt.setInt(1, couponId);
				rset = percentStmt.executeQuery();
				if(rset.next()) {
					couponDiscount = rset.getInt("percent") / 100.0;
				}
			}

			int commissionAmount = (int)Math.floor(priorPrice * 0.1 * (1 - couponDiscount));
			double sellerAmount = priorPrice - commissionAmount;

			PreparedStatement sellerStmt = conn.prepareStatement("INSERT INTO account VALUES (DEFAULT, ?, ?, ?, NOW())");
			sellerStmt.setString(1, seller);
			sellerStmt.setDouble(2, sellerAmount);
			sellerStmt.setString(3, itemname);
			sellerStmt.executeUpdate();

			PreparedStatement commissionStmt = conn.prepareStatement("INSERT INTO commission VALUES (DEFAULT, (SELECT bid_id FROM bid WHERE item_id = ? AND bid_price = ?), NOW(), ?, ?)");
			commissionStmt.setInt(1, itemId);
			commissionStmt.setDouble(2, priorPrice);
			commissionStmt.setInt(3, couponId);
			commissionStmt.setInt(4, commissionAmount);
			commissionStmt.executeUpdate();

			if(couponId != null) {
				PreparedStatement couponStmt = conn.prepareStatement("UPDATE coupon SET available = 'USED' WHERE coupon_id = ?");
				couponStmt.setInt(1, couponId);
				couponStmt.executeUpdate();
			}

			conn.commit();


		} catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
			try {
				conn.rollback();
			} catch (SQLException rollbackEx) {
				System.out.println("Rollback failed: " + rollbackEx.getMessage());
			}
		} finally {
			try {
        conn.setAutoCommit(true);
    	} catch (SQLException e) {
        System.out.println("Failed to reset AutoCommit: " + e.getMessage());
    	}
		}
	}

	public static void main(String[] args) {
		char choice;
		boolean ret;
		

		if(args.length<2){
			System.out.println("Usage: java Auction postgres_id password");
			System.exit(1);
		}


		try{
            //    	conn = DriverManager.getConnection("jdbc:postgresql://localhost/"+args[0], args[0], args[1]); 
            conn = DriverManager.getConnection("jdbc:postgresql://localhost/s20314597", "s20314597", "pdudb^1350"); 
						stmt = conn.createStatement();
		}
		catch(SQLException e){
			System.out.println("SQLException : " + e);	
			System.exit(1);
		}

		do {
			username = null;
			System.out.println(
					"----< Login menu >\n" + 
					"----(1) Login\n" +
					"----(2) Sign up\n" +
					"----(3) Login as Administrator\n" +
					"----(Q) Quit"
					);

			try {
				choice = scanner.next().charAt(0);;
				scanner.nextLine();
			} catch (java.util.InputMismatchException e) {
				System.out.println("Error: Invalid input is entered. Try again.");
				continue;
			}

			try {
				switch ((int) choice) {
					case '1':
						ret = LoginMenu();
						if(!ret) continue;
						break;
					case '2':
						ret = SignupMenu();
						if(!ret) continue;
						break;
					case '3':
						ret = AdminMenu();
						if(!ret) continue;
					case 'q':
					case 'Q':
						System.out.println("Good Bye");
						/* TODO: close the connection and clean up everything here */
						stmt.close();
						conn.close();
						System.exit(1);
					default:
						System.out.println("Error: Invalid input is entered. Try again.");
				}
			} catch (SQLException e) {
				System.out.println("SQLException : " + e);	
			}
		} while (username==null || username.equalsIgnoreCase("back"));  

		// logged in as a normal user 
		do {
			System.out.println(
					"---< Main menu > :\n" +
					"----(1) Sell Item\n" +
					"----(2) Status of Your Item Listed on Auction\n" +
					"----(3) Buy Item\n" +
					"----(4) Check Status of your Bid \n" +
					"----(5) Check your Account \n" +
					"----(6) Check your Coupons \n" +
					"----(Q) Quit"
					);

			try {
				choice = scanner.next().charAt(0);;
				scanner.nextLine();
			} catch (java.util.InputMismatchException e) {
				System.out.println("Error: Invalid input is entered. Try again.");
				continue;
			}

			try{
				switch (choice) {
					case '1':
						ret = SellMenu();
						if(!ret) continue;
						break;
					case '2':
						CheckSellStatus();
						break;
					case '3':
						ret = BuyItem();
						if(!ret) continue;
						break;
					case '4':
						CheckBuyStatus();
						break;
					case '5':
						CheckAccount();
						break;
					case '6':
						CheckCoupon();
						break;
					case 'q':
					case 'Q':
						System.out.println("Good Bye");
						/* TODO: close the connection and clean up everything here */
						stmt.close();
						conn.close();
						System.exit(1);
				}
			} catch (SQLException e) {
				System.out.println("SQLException : " + e);	
				System.exit(1);
			}
		} while(true);
	} // End of main 
} // End of class