package org.artgasmator.data.dao.impl;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.artgasmator.data.dao.MemberDao;
import org.artgasmator.data.rowmapper.MemberRowMapper;
import org.artgasmator.model.Member;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.jdbc.support.lob.LobHandler;

public class MemberDaoImpl extends AbstractDao implements MemberDao {

	private MemberRowMapper memberRowMapper;
	private LobHandler lobHandler;
	private SimpleJdbcInsert createMember;
	
	public void init() {
		this.createMember = new SimpleJdbcInsert(dataSource)
				.withTableName("member")
				.usingColumns("member_name", "member_birth_date", "member_birth_place",
						"instruments", "member_occupation", "biography", "creation_date", "artist_id")
				.usingGeneratedKeyColumns("memberId");
	}
	
	public MemberDaoImpl(LobHandler lobHandler) {
		this.lobHandler = lobHandler;
		this.memberRowMapper = new MemberRowMapper(lobHandler);
	}
	
	public void createMember(final Member member) {
		
		MapSqlParameterSource params = new MapSqlParameterSource();
		params.addValue("member_name", member.getMemberName()).
				addValue("member_birth_date", member.getMemberBirthDate())
				.addValue("member_birth_place", member.getMemberBirthPlace())
						.addValue("member_occupation", member.getMemberOccupation())
							.addValue("biography", member.getBiography())
							.addValue("creation_date", new Date(System.currentTimeMillis()))
							.addValue("artist_id", member.getArtistId());
		
	    final Integer createdMemberId = createMember.executeAndReturnKey(params).intValue();
		
		String sql = "UPDATE member SET photo = ? WHERE member_id = ?";
		
		jdbcTemplate.getJdbcOperations().execute(sql, new AbstractLobCreatingPreparedStatementCallback(lobHandler) {
			
			protected void setValues(PreparedStatement ps, LobCreator lobCreator)
					throws SQLException, DataAccessException {
				ps.setInt(2, createdMemberId);
				lobCreator.setBlobAsBytes(ps, 1, member.getPhoto());
			}
		});
	}

	
	public List<Member> loadMembers(Integer artistId) {
		
		String sql = "SELECT * FROM member WHERE artist_id = ?";
		return jdbcTemplate.getJdbcOperations().query(sql, new Object[] {artistId}, memberRowMapper);
	}


	
	public Member loadMember(Integer memberId) {
		
		String sql = "SELECT * FROM member WHERE member_id = ?";
		return jdbcTemplate.getJdbcOperations().queryForObject(sql, memberRowMapper, memberId);
	}

}
