package ss06.sl.pj3;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class DBUtil {
	private static DBUtil dbutil = null;
	private Connection conn = null;
	private String dburl = "jdbc:mysql://localhost/project3?user=root&password=620615&useUnicode=true&characterEncoding=UTF-8";
	/*user 字段*/
	public String userid[];
	public String username[];
	public String gender[];
	public int age[];
	public String role[];
	public String address[];
	public String email[];
	/*book字段*/
	public String ISBN[];
	public String bookname[];
	public String bookpress[];
	public String category[];
	public double price[];
	public String pic[];
	public Date publishdate[];
	/*shoppingcart字段*/
	public String cartUserid[];
	public String cartISBN[];
	public int quantity[];
	public String status[];
	public Date ordertime[];
	
	private DBUtil() {}

	public static DBUtil getInstance() {
		if (dbutil == null)
			dbutil = new DBUtil();
		return dbutil;
	}

	public boolean connect2DB() {
		try {
			if (conn != null && !conn.isClosed()) {
				conn.close();
			}
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			conn = DriverManager.getConnection(dburl);
			return true;
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
	
	/*
	 * 用戶管理方面代碼 
	 */
	
	public boolean isUserCanAdd(String name){
		try{
			String sql = "SELECT * FROM user WHERE username = ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			ResultSet rs = ps.executeQuery();
			if(rs.next()) return false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NullPointerException e) {
			throw e;
		}
		return true;
	}
	
	public String getUserName(String userid){
		String result = "";
		try{
			String sql = "select username from user where userid=?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, userid);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				result = rs.getString(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NullPointerException e) {
			throw e;
		}
		return result;
	}
	
	public String getUserDetail(String userid){
		String result = "";
		try{
			String sql = "select * from user where userid=?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, userid);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				result += (rs.getString(2) + "\n");
				result += (rs.getString(3) + "\n");
				result += (rs.getString(5) + "\n");
				result += (rs.getInt(6) + "\n");
				result += (rs.getString(7) + "\n");
				if(rs.getString(8).equals(""))
					result += (" " + "\n");
				else
					result += (rs.getString(8) + "\n");
				if(rs.getString(9).equals(""))
					result += (" ");
				else
					result += (rs.getString(9));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NullPointerException e) {
			throw e;
		}
		return result;
	}
	
	public String getUserMessage(String username){
		String result = "";
		try{
			String sql = "select * from user where username=?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, username);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				result += (rs.getString(2) + "\n");
				result += (rs.getString(3) + "\n");
				result += (rs.getString(4) + "\n");
				result += (rs.getString(5) + "\n");
				result += (rs.getInt(6) + "\n");
				result += (rs.getString(7) + "\n");
				if(rs.getString(8).equals(""))
					result += (" " + "\n");
				else
					result += (rs.getString(8) + "\n");
				if(rs.getString(9).equals(""))
					result += (" ");
				else
					result += (rs.getString(9));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NullPointerException e) {
			throw e;
		}
		return result;
	}
	
	public void updateUser(String userid, String password, int age, String email, String address){
		try{
			String sql = "update user set password=?, age=?, email=?, address=? where " +
					"userid=?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, password);
			ps.setInt(2, age);
			ps.setString(3, email);
			ps.setString(4, address);
			ps.setString(5, userid);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NullPointerException e) {
			throw e;
		}
	}

	public void addUser(String userid, String name, String passwd, char gender, int age,
					String role, String email, String address) {
		try {
			PreparedStatement ps = conn
					.prepareStatement("insert into user (userid, username, password, gender, " +
							"age, role, email, address) values (?,?,?,?,?,?,?,?)");
			ps.setString(1, userid);
			ps.setString(2, name);
			ps.setString(3, passwd);
			ps.setString(4, gender + "");
			ps.setInt(5, age);
			ps.setString(6, role);
			ps.setString(7, email);
			ps.setString(8, address);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NullPointerException e) {
			throw e;
		}
	}
	
	public void getAllUsers(){
		int sum = 0;
		try{
			String sql = "SELECT * FROM user";
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				sum++;
			}
			this.userid = new String[sum];
			this.username = new String[sum];
			this.gender = new String[sum];
			this.age = new int[sum];
			this.role = new String[sum];
			this.address = new String[sum];
			this.email = new String[sum];
			rs.beforeFirst();
			int i = 0;
			while(rs.next()){
				userid[i] = rs.getString(2);
				username[i] = rs.getString(3);
				gender[i] = rs.getString(5);
				age[i] = rs.getInt(6);
				role[i] = rs.getString(7);
				address[i] = rs.getString(8);
				email[i] = rs.getString(9);
				i++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NullPointerException e) {
			throw e;
		}
		//return sum;
	}
	
	public String getUserID(){
		int sum = 1;
		String strNum = "";
		try{
			String sql = "SELECT * FROM user";
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				sum++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NullPointerException e) {
			throw e;
		}
		if(sum >= 0 && sum <= 9)
			strNum = "000" + sum;
		else if(sum >= 10 && sum <= 99){
			strNum = "00" + sum;
		}
		else if(sum >= 100 && sum <= 999){
			strNum = "0" + sum;
		}
		else{
			strNum = "" + sum;
		}
		return "PJ3SS" + strNum;
	}
	
	public String getUseridFromUsername(String username){
		try{
			PreparedStatement ps = conn.prepareStatement("select userid from user where username=?");
			ps.setString(1, username);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				return rs.getString(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NullPointerException e) {
			throw e;
		}
		return null;
	}

	public String checkUser(String name, String passwd) {
		try {
			PreparedStatement ps = conn
					.prepareStatement("select username, password from user where username=?");
			ps.setString(1, name);
			ResultSet rs = ps.executeQuery();
			String username, password;
			while (rs.next()) {
				username = rs.getString(1);
				password = rs.getString(2);
				if (password.compareTo(passwd) == 0)
					return username;
				else
					return null;
			}
			return null;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NullPointerException e) {
			throw e;
		}
		return null;
	}
	
	public String getRole(String username){
		String result = "";
		try{
			PreparedStatement ps = conn.prepareStatement("select role from user where " +
					"username=?");
			ps.setString(1, username);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){