/******************************************************************************
    * PostersDaoJdbc.java - Implement the Dao interface for the Posters
    * 
    * PicMan - The BuckoSoft Picture Manager in Java
    * Copyright(c) 2006 - Dick Balaska
    * 
    */ 
   package com.buckosoft.PicMan.db;
   
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.Types;
  import java.util.List;
  
  import javax.sql.DataSource;
  
  import org.apache.commons.logging.Log;
  import org.apache.commons.logging.LogFactory;
  import org.springframework.dao.DataAccessException;
  import org.springframework.jdbc.core.SqlParameter;
  import org.springframework.jdbc.object.MappingSqlQuery;
  import org.springframework.jdbc.object.SqlFunction;
  import org.springframework.jdbc.object.SqlUpdate;
  
  import com.buckosoft.PicMan.domain.Poster;
  
  /** Implement the Dao Interface for the {@link com.buckosoft.PicMan.domain.Poster}s.
   * @author Dick Balaska
   * @since 2012/07/22
   * @see <a href="http://cvs.buckosoft.com/Projects/java/PicMan/PicMan/src/com/buckosoft/PicMan/db/PostersDaoJdbc.java">PostersDaoJdbc.java</a>
   */
  public class PostersDaoJdbc implements PostersDao {
  	private static final boolean DEBUG = true;
  	protected final Log logger = LogFactory.getLog(getClass());
  
  	private DataSource ds;
  	
  	/** Set the reference to the JDBC datasource.
  	 * @param ds The datasource as configured by Spring.
  	 * @see <a href="http://cvs.buckosoft.com/Projects/java/PicMan/PicMan/WebContent/WEB-INF/picManDatabase/picManDatabase.xml">picManDatabase.xml</a>
  	 */ 
  	public void setDataSource(DataSource ds) {
  		this.ds = ds;
  	}
  
  	/* (non-Javadoc)
  	 * @see com.buckosoft.PicMan.db.PostersDao#getPoster(int)
  	 */
  	public Poster getPoster(int pid) {
  		if (DEBUG)
  			logger.info("getPoster: " + pid);
  		if (sql_getPosterQUERY == null)
  			sql_getPosterQUERY = new PostersQuery(ds, pid);
          Poster m = (Poster)sql_getPosterQUERY.findObject(pid);
         	getPosterConfig(m);
          return(m);
  	}
  	private	PostersQuery	sql_getPosterQUERY = null;
  
  	/* (non-Javadoc)
  	 * @see com.buckosoft.PicMan.db.PostersDao#getPosters()
  	 */
  	@SuppressWarnings("unchecked")
  	public List<Poster> getPosters() {
  		if (DEBUG)
  			logger.info("GetPostersList");
  		if (sql_getPostersListQUERY == null)
  			sql_getPostersListQUERY = new PostersQuery(ds);
  		List<Poster> PosterList = (List<Poster>)sql_getPostersListQUERY.execute();
  		for (Poster m : PosterList)
  			this.getPosterConfig(m);
  		return(PosterList);
  	}
  	private	PostersQuery	sql_getPostersListQUERY = null;
  
  	/* (non-Javadoc)
  	 * @see com.buckosoft.PicMan.db.PostersDao#deletePoster(int)
  	 */
  	public void deletePoster(int mid) {
  		if (DEBUG)
  			logger.info("deletePoster");
  		// TODO deletePoster not implemented
  		throw new RuntimeException("Not implemented");
  	}
  
  	/* (non-Javadoc)
  	 * @see com.buckosoft.PicMan.db.PostersDao#storePoster(com.buckosoft.PicMan.domain.Poster)
  	 */
  	public void storePoster(Poster poster) {
  		if (DEBUG)
  			logger.info("storePoster " + poster.getPid());
  		if (poster.getPid() <= 0) {
  			PostersInsert mi = new PostersInsert(ds);
  			mi.insert(poster);
  			int c = getPosterCount();
  			poster.setPid(c);
  		} else {
  			PostersUpdate mu = new PostersUpdate(ds);
  			mu.update(poster);
 		}
 		deletePosterConfigs(poster.getPid());
 		if (sql_storePosterConfigsINSERT == null)
 			sql_storePosterConfigsINSERT = new PosterConfigsInsert(ds);
 		for (String key : poster.getConfig().keySet()) {
 			sql_storePosterConfigsINSERT.insert(poster.getPid(), key, poster.getConfig().get(key));
 		}
 	}
 	private	PosterConfigsInsert	sql_storePosterConfigsINSERT = null;
 
 	/** Get the number of Virgins in the database
 	 * @return the count
 	 */
 	public int	getPosterCount() {
 		if (DEBUG)
 			logger.info("getPosterCount:");
 		if (sql_getPosterCountQUERYr == null)
 			sql_getPosterCountQUERYr = new PostersCount(ds);
 		return(sql_getPosterCountQUERYr.run());
 	}
 	private PostersCount	sql_getPosterCountQUERYr;
 	
 	@SuppressWarnings("unchecked")
 	private	void getPosterConfig(Poster poster) {
 		if (poster == null)
 			return;
 		if (sql_getPosterConfigQUERY == null)
 			sql_getPosterConfigQUERY = new PosterConfigsQuery(ds, 0);
 		List<KeyValue>kvlist = (List<KeyValue>)sql_getPosterConfigQUERY.execute(poster.getPid());
 		for (KeyValue kv : kvlist) {
 			poster.getConfig().put(kv.key, kv.value);
 		}
 	}
 
 	private	PosterConfigsQuery	sql_getPosterConfigQUERY = null;
 
 	private void deletePosterConfigs(int sid) throws DataAccessException {
 		PosterConfigsDelete msd = new PosterConfigsDelete(ds);
 		msd.delete(sid);
 	}
 	
 
 	/**
 	 * Object to Query for Posters
 	 */
 	private class PostersQuery extends MappingSqlQuery {
 		
 		PostersQuery(DataSource ds) {
 			super(ds, "SELECT * from posters");
 			compile();
 		}
 		
 		PostersQuery(DataSource ds, int pid) {
 			super(ds, "SELECT * from posters where pid = ?");
 			declareParameter(new SqlParameter(Types.INTEGER));
 			compile();
 		}
 
 
 		protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
 			Poster p = new Poster();
 			p.setName(rs.getString("name"));
 			p.setOutputPath(rs.getString("outputPath"));
 			p.setPid(rs.getInt("pid"));
 			p.setMasterMid(rs.getInt("mosaicId"));
 //			try {
 //				m.setStartTime(rs.getDate("startTime"));
 //			} catch (Exception e) {logger.info(e);}
 			return(p);
 		}
 	}
 	/**
 	 * <code>Virgin</code> Count Object.
 	 */
 	class PostersCount extends SqlFunction {
 		
 		PostersCount(DataSource ds) {
 			super(ds, "SELECT COUNT(*) from posters");
 			compile();
 		}		
 	}
 
 	/**
 	 * <code>Poster</code> Insert Object.
 	 */
 	private class PostersInsert extends SqlUpdate {
 		
 		/**
 		 * Create a new instance of PostersInsert.
 		 * @param ds the DataSource to use for the insert
 		 */
 		protected PostersInsert(DataSource ds) {
 			super(ds, "INSERT INTO posters VALUES(?,?,?,?)");
 			declareParameter(new SqlParameter(Types.INTEGER));
 			declareParameter(new SqlParameter(Types.VARCHAR));
 			declareParameter(new SqlParameter(Types.INTEGER));
 			declareParameter(new SqlParameter(Types.VARCHAR));
 			compile();
 		}
 
 		protected void insert(Poster p) {
 			Object[] objs = new Object[] {
 				null,
 				p.getName(),
 				new Integer(p.getMasterMid()),
 				p.getOutputPath(),
 			};
 			super.update(objs);
 		}
 	}
 	///////////////////////////////////////////////////////////////////////////////////////////////
 	/**
 	 * <code>Poster</code> Update Object.
 	 */
 	private class PostersUpdate extends SqlUpdate {
 		//private	DataSource	ds;
 		
 		/**
 		 * Create a new instance of PostersUpdate.
 		 * @param ds the DataSource to use for the update
 		 */
 		protected PostersUpdate(DataSource ds) {
 			super(ds, "UPDATE posters SET name=?, mosaicId=?, outputPath=? WHERE pid = ? LIMIT 1");
 			declareParameter(new SqlParameter(Types.VARCHAR));
 			declareParameter(new SqlParameter(Types.INTEGER));
 			declareParameter(new SqlParameter(Types.VARCHAR));
 			declareParameter(new SqlParameter(Types.INTEGER));
 			compile();
 		}
 		/**
 		 * Method to update the <code>Set</code>'s data.
 		 * @param List sets The sets that should be stored
 		 * @return 0
 		 */
 
 		protected int update(Poster p) {
 			return this.update(new Object[] {
 					p.getName(),
 					new Integer(p.getMasterMid()),
 					p.getOutputPath(),
 					new Integer(p.getPid())
 			});
 		}
 	}
 	private class KeyValue {
 		public String	key;
 		public String	value;
 	}
 	
 	private class PosterConfigsQuery extends MappingSqlQuery {
 
 		PosterConfigsQuery(DataSource ds, int pid) {
             super(ds, "SELECT * from posterConfigs where pid=?");
 			declareParameter(new SqlParameter(Types.INTEGER));
             compile();
         }
  
         protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
         	KeyValue k = new KeyValue();
         	k.key = rs.getString("key");
         	k.value = rs.getString("value");
         	return(k);
         }
 	}
 
 	/**
 	 * <code>PosterConfig</code> Delete Object.
 	 */
 	protected class PosterConfigsDelete extends SqlUpdate {
 		
 		/**
 		 * Create a new instance of MetaSetRemove.
 		 * @param ds the DataSource to use for the delete
 		 */
 		protected PosterConfigsDelete(DataSource ds) {
 			super(ds, "DELETE FROM posterConfigs WHERE pid = (?)");
 			declareParameter(new SqlParameter(Types.INTEGER));
 			compile();
 		}
 		
 		protected void delete(int sid) {
 			super.update(sid);
 		}
 	}
 	/**
 	 * <code>PosterConfigs</code> Insert Object.
 	 */
 	protected class PosterConfigsInsert extends SqlUpdate {
 		
 		/**
 		 * Create a new instance of MetaSetInsert.
 		 * @param ds the DataSource to use for the insert
 		 */
 		protected PosterConfigsInsert(DataSource ds) {
 			super(ds, "INSERT INTO posterConfigs VALUES(?,?,?)");
 			declareParameter(new SqlParameter(Types.INTEGER));
 			declareParameter(new SqlParameter(Types.VARCHAR));
 			declareParameter(new SqlParameter(Types.VARCHAR));
 			compile();
 		}
 
 		protected void insert(int pid, String key, String value) {
 			Object[] objs = new Object[] {
 					new Integer(pid),
 					key,
 					value,
 			};
 			super.update(objs);
 		}
 	}
 }