package flex.samples.spring.store;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

public class SimpleProductDAO extends JdbcDaoSupport implements ProductDAO {
	
	public Collection findAll() throws DataAccessException {
		String sql = "SELECT * FROM product";
		
		RowMapper mapper = new RowMapper() {
			
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				Product product = new Product();
				product.setProductId(rs.getLong("product_id"));
				product.setName(rs.getString("name"));
				product.setDescription(rs.getString("description"));
				product.setCategory(rs.getString("category"));
				product.setImage(rs.getString("image"));
				product.setPrice(rs.getDouble("price"));
				product.setQtyInStock(rs.getInt("qty_in_stock"));
				return product;
			}
			
		};
		
		JdbcTemplate template = new JdbcTemplate(this.getDataSource());
		return template.query(sql, mapper);
	}

	public void createProduct(Product product) throws DataAccessException {
		String sql = "INSERT INTO product (name, description, category, image, price, qty_in_stock) VALUES (:name, :description, :category, :image, :price, :qtyInStock)"; 
		NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(this.getDataSource());
		SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(product);
		template.update(sql, namedParameters);
		product.setProductId(getJdbcTemplate().queryForInt("call identity()"));
	}

	public void updateProduct(Product product) throws DataAccessException {
		String sql = "UPDATE product SET name=:name,description=:description,category=:category,image=:image,price=:price,qty_in_stock=:qtyInStock WHERE product_id=:productId"; 
		NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(this.getDataSource());
		SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(product);
		template.update(sql, namedParameters);
	}

	public void deleteProduct(Product product) throws DataAccessException {
		String sql = "DELETE from product WHERE product_id=:productId"; 
		NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(this.getDataSource());
		SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(product);
		template.update(sql, namedParameters);
	}

}