/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package dataaccess;

import business.User;
import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;

public class UserDB {

    public static int insert(User user) throws IOException, SQLException, ClassNotFoundException {
        // load the driver
        ConnectionPool pool = ConnectionPool.getInstance();
        Connection connection = pool.getConnection();

        PreparedStatement ps = null;

        String query = "INSERT INTO user (fullname, username, emailAddress, password, birthdate, questionNo, answer) "
                + "VALUES (?, ?, ?, ?, ?, ?, ?)";

        try {
            ps = connection.prepareStatement(query);
            ps.setString(1, user.getFullName());
            ps.setString(2, user.getUserName());
            ps.setString(3, user.getEmail());
            ps.setString(4, user.getPassword());
            ps.setString(5, user.getBirthdate());
            ps.setString(6, user.getQuestionNo());
            ps.setString(7, user.getAnswer());
            return ps.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e);
            return 0;
        } finally {
            DBUtil.closePreparedStatement(ps);
            pool.freeConnection(connection);
        }

    }

    public static int updateUser(User user) throws IOException, SQLException, ClassNotFoundException {
        ConnectionPool pool = ConnectionPool.getInstance();
        Connection connection = pool.getConnection();

        PreparedStatement ps = null;

        String query = "Update user set fullname = ?, birthdate = ?, password = ?, questionNo = ?, answer = ? where emailAddress = ?";

        int result = 0;

        try {
            ps = connection.prepareStatement(query);
            ps.setString(1, user.getFullName());
            ps.setString(2, user.getBirthdate());
            ps.setString(3, user.getPassword());
            ps.setString(4, user.getQuestionNo());
            ps.setString(5, user.getAnswer());
            ps.setString(6, user.getEmail());

            result = ps.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e);
        } finally {
            DBUtil.closePreparedStatement(ps);
            pool.freeConnection(connection);
        }

        return result;
    }

    public static int updatePassword(String emailAddress, String Password) throws SQLException, ClassNotFoundException {
        ConnectionPool pool = ConnectionPool.getInstance();
        Connection connection = pool.getConnection();

        PreparedStatement ps = null;

        String query = "Update user set password = ? where emailAddress= ?";

        int result = 0;

        try {
            ps = connection.prepareStatement(query);
            ps.setString(1, Password);
            ps.setString(2, emailAddress);

            result = ps.executeUpdate();

        } catch (SQLException e) {
            System.out.println(e);
            return 0;
        } finally {
            DBUtil.closePreparedStatement(ps);
            pool.freeConnection(connection);
        }
        return result;
    }

    public static User search(String emailAddress) {
        ConnectionPool pool = ConnectionPool.getInstance();
        Connection connection = pool.getConnection();

        PreparedStatement ps = null;
        ResultSet rs = null;

        String query = "SELECT * FROM User "
                + "WHERE emailAddress = ?";

        try {
            ps = connection.prepareStatement(query);
            ps.setString(1, emailAddress);
            rs = ps.executeQuery();

            User user = null;
            if (rs.next()) {
                user = new User();
                user.setFullName(rs.getString("fullName"));
                user.setUserName(rs.getString("userName"));
                user.setEmail(rs.getString("emailAddress"));
                user.setPassword(rs.getString("password"));
                user.setBirthdate(rs.getString("birthdate"));
                user.setQuestionNo(rs.getString("questionNo"));
                user.setAnswer(rs.getString("answer"));
            }
            return user;
        } catch (SQLException e) {
            System.out.println(e);
            return null;
        } finally {
            DBUtil.closePreparedStatement(ps);
            pool.freeConnection(connection);
        }

    }

    public static boolean emailExists(String email) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    public static User select(String emailAddress) {
        ConnectionPool pool = ConnectionPool.getInstance();
        Connection connection = pool.getConnection();

        PreparedStatement ps = null;
        ResultSet rs = null;

        String query = "SELECT * FROM User "
                + "WHERE emailAddress = ?";

        try {
            ps = connection.prepareStatement(query);
            ps.setString(1, emailAddress);
            rs = ps.executeQuery();

            User user = null;
            if (rs.next()) {
                user = new User();
                user.setFullName(rs.getString("fullName"));
                user.setUserName(rs.getString("userName"));
                user.setEmail(rs.getString("emailAddress"));
                user.setPassword(rs.getString("password"));
                user.setBirthdate(rs.getString("birthdate"));
                user.setQuestionNo(rs.getString("questionNo"));
                user.setAnswer(rs.getString("answer"));
            }
            return user;
        } catch (SQLException e) {
            System.out.println(e);
            return null;
        }finally {
            DBUtil.closePreparedStatement(ps);
            pool.freeConnection(connection);
        }
    }

    public static User searchUserName(String emailAddress) {
        ConnectionPool pool = ConnectionPool.getInstance();
        Connection connection = pool.getConnection();

        PreparedStatement ps = null;
        ResultSet rs = null;

        String query = "SELECT * FROM User "
                    + "WHERE username = ?";

        try {
            ps = connection.prepareStatement(query);
            ps.setString(1, emailAddress);
            rs = ps.executeQuery();

            User user = null;
            if (rs.next()) {
                user = new User();
                user.setFullName(rs.getString("fullName"));
                user.setUserName(rs.getString("userName"));
                user.setEmail(rs.getString("emailAddress"));
                user.setPassword(rs.getString("password"));
                user.setBirthdate(rs.getString("birthdate"));
                user.setQuestionNo(rs.getString("questionNo"));
                user.setAnswer(rs.getString("answer"));
            }
            return user;
        } catch (SQLException e) {
            System.out.println(e);
            return null;
        }finally {
            DBUtil.closePreparedStatement(ps);
            pool.freeConnection(connection);
        }
    }
}