package user;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import bbs.Bbs;

public class UserDAO {

	private Connection conn;
	private PreparedStatement pstmt;
	private ResultSet rs;
	
	public UserDAO() {
		try {
			String dbURL = "jdbc:oracle:thin:@localhost:1521:ORCL";
			String dbID = "-";
			String dbPassword = "-";
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(dbURL, dbID, dbPassword);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public int login(String ID, String Password) {
		String SQL = "SELECT PASSWORD FROM users WHERE ID = ?";
		try {
			pstmt = conn.prepareStatement(SQL);
			pstmt.setString(1, ID);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				if(rs.getString(1).equals(Password)) {
					return 1; //login success
				} else {
					return 0; //password Exception
				}
			}
			return -1; //non ID
		} catch (Exception e) {
			e.printStackTrace();
		}
		return -2; //DB exception
	}
	
	public int getNext() {
		String SQL = "SELECT USER_NO FROM USERS ORDER BY USER_NO DESC";
		try{

			PreparedStatement pstmt = conn.prepareStatement(SQL);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				return rs.getInt(1) + 1;
			}
			return 1; //현재가 첫 번째 게시글이 ㄴ경우
		}catch (Exception e) {
			e.printStackTrace();
		}
		return -1; //db오류
	}
	
	public ArrayList<User> getuser(){
		String SQL = "SELECT * FROM USERS ORDER BY USER_NO DESC";
		ArrayList<User> list = new ArrayList<User>();
		try {
			 PreparedStatement pstmt = conn.prepareStatement(SQL);
			 rs = pstmt.executeQuery();
			 while (rs.next()) {
				 User user = new User();
				 user.setUser_no(rs.getInt(1));
				 user.setID(rs.getString(2));
				 user.setPassword(rs.getString(3));
				 user.setName(rs.getString(4));
				 user.setPostnum(Integer.parseInt(rs.getString(5)));
				 user.setAddress(rs.getString(6));
				 user.setD_address(rs.getString(7));
				 user.setPhone_number(rs.getString(8));
				 user.setBirth(rs.getInt(9));
				 user.setGender(rs.getInt(10));
				 user.setEmail(rs.getString(11));
				 user.setUser_type(rs.getInt(12));
				 user.setJoindate(rs.getString(13));
				 list.add(user);
			 } 
		 } catch (Exception e) {
			 e.printStackTrace();
		 }
		 return list;
	}
	
	public User userInfo(String id) {
		 String SQL = "SELECT ID, NAME, POSTNUM, ADDRESS, D_ADDRESS, PHONE_NUMBER, BIRTH, GENDER, EMAIL FROM USERS WHERE ID = ?";
		 User u = new User();
		 try {
			 PreparedStatement pstmt = conn.prepareStatement(SQL);
			 pstmt.setString(1, id);
			 rs = pstmt.executeQuery();
			 while (rs.next()) {
				 u.setID(rs.getString(1));
				 u.setName(rs.getString(2));
				 u.setPostnum(rs.getInt(3));
				 u.setAddress(rs.getString(4));
				 u.setD_address(rs.getString(5));
				 u.setPhone_number(rs.getString(6));
				 u.setBirth(rs.getInt(7));
				 u.setGender(rs.getInt(8));
				 u.setEmail(rs.getString(9));
			 } 
		 } catch (Exception e) {
			 e.printStackTrace();
		 }
		 return u;
	}
	
	public int join(String ID, String password, String name, int postnum, String address, String d_address, String phone_number, int birth, int gender, String email) {
		String SQL = "INSERT INTO USERS VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, SYSTIMESTAMP)";
		try {
			pstmt = conn.prepareStatement(SQL);
			pstmt.setInt(1, getNext());
			pstmt.setString(2, ID);
			pstmt.setString(3, password);
			pstmt.setString(4, name);
			pstmt.setInt(5, postnum);
			pstmt.setString(6, address);
			pstmt.setString(7, d_address);
			pstmt.setString(8, phone_number);
			pstmt.setInt(9, birth);
			pstmt.setInt(10, gender);
			pstmt.setString(11, email);
			pstmt.setInt(12, 1);
			return pstmt.executeUpdate();
		} catch(Exception e) {
			e.printStackTrace();
		}
		return -1; //db exception
	}
	
	public int update(String newpassword, String name, int postnum, String address, String d_address, String phone_number, String email, String ID) {
		String SQL = "UPDATE USERS SET PASSWORD = ?, NAME = ?, POSTNUM = ?, ADDRESS = ?, D_ADDRESS = ?, PHONE_NUMBER = ?, EMAIL = ? WHERE ID = ?";
		try{
			PreparedStatement pstmt = conn.prepareStatement(SQL);
			pstmt.setString(1, newpassword);
			pstmt.setString(2, name);
			pstmt.setInt(3, postnum);
			pstmt.setString(4, address);
			pstmt.setString(5, d_address);
			pstmt.setString(6, phone_number);
			pstmt.setString(7, email);
			pstmt.setString(8, ID);
			return pstmt.executeUpdate();
		}catch (Exception e) {
			e.printStackTrace();
		}
		return -1; //db오류
	}
	
	public int fmanager(String userID) {
		String SQL = "SELECT USER_TYPE FROM USERS WHERE ID = ?";
		try {
			pstmt = conn.prepareStatement(SQL);
			pstmt.setString(1, userID);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				return rs.getInt("USER_TYPE");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}return -1;
	}
	
	public String yourname(String userID) {
		String SQL = "SELECT NAME FROM USERS WHERE ID = ?";
		try {
			pstmt = conn.prepareStatement(SQL);
			pstmt.setString(1, userID);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				return rs.getString("NAME");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}return null;
	}
	
	public int yournum(String userID) {
		String SQL = "SELECT USER_NO FROM USERS WHERE ID = ?";
		try {
			pstmt = conn.prepareStatement(SQL);
			pstmt.setString(1, userID);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				return rs.getInt("USER_NO");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}return -1;
	}
}