package com.example.fp.dao.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.example.fp.User;
import com.example.fp.dao.UserDAO;

public class JDBCUserDAO implements UserDAO {

	private JdbcTemplate jdbcTemplate;

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

	
	@Override
	public void create(User user) {
		
		final String INSERT_SQL = "insert into user_account (username, full_name, password, active) values(?, ?, ?, ?)";

		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(
		    new PreparedStatementCreator() {
		        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
		            PreparedStatement ps =
		                connection.prepareStatement(INSERT_SQL, new String[] {"uid"});
		            ps.setString(1, user.getUsername());
		            ps.setString(2, user.getFullName());
		            ps.setString(3, user.getPassword());
		            ps.setInt(4, user.isActive() ? 1 : 0);
		            return ps;
		        }
		    }, keyHolder);

		// keyHolder.getKey() now contains the generated key
		user.setUid(keyHolder.getKey().intValue());
	}

	@Override
	public void delete(int uid) {
		jdbcTemplate.update("delete from user_account where uid = ?", Integer.valueOf(uid));
	}

	@Override
	public List<User> findAll() {
		return this.jdbcTemplate.query( "select * from user_account", new UserMapper());
	}

	
	@Override
	public User find(int uid) {
		return this.jdbcTemplate.queryForObject( "select * from user_account where uid = ?", new Object[] {Integer.valueOf(uid)}, new UserMapper());
	}


	@Override
	public User find(String username) {
		return this.jdbcTemplate.queryForObject( "select * from user_account where username = ?", new Object[] {username}, new UserMapper());
	}


	@Override
	public void update(User user) {
		this.jdbcTemplate.update("update user_account set username = ?, full_name = ?, password = ?, active = ? where uid = ?",
				user.getUsername(), user.getFullName(), user.getPassword(), user.isActive() ? 1 : 0, user.getUid());
	}

	
	private static final class UserMapper implements RowMapper<User> {

	    public User mapRow(ResultSet rs, int rowNum) throws SQLException {
	        User user = new User();
	        user.setUid(rs.getInt("uid"));
	        user.setUsername(rs.getString("username"));
	        user.setFullName(rs.getString("full_name"));
	        user.setPassword(rs.getString("password"));
	        user.setActive(rs.getBoolean("active"));
	        return user;
	    }        
	}
}