package hirondelle.web4j.database;

import hirondelle.web4j.BuildImpl;
import hirondelle.web4j.model.AppException;
import hirondelle.web4j.model.Id;
import hirondelle.web4j.readconfig.Config;
import hirondelle.web4j.readconfig.ConfigReader;

import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;

/**
 <span class="highlight">Utility class for the most common database tasks.</span>
 
 <P>This class allows many DAO methods to be implemented in one or two lines 
 of simple code.

<h3><a name="Parameters"></a>SQL Parameters</h3>
 SQL statement parameters are passed to this class using an <tt>Object...</tt> 
 sequence parameter. The objects in these arrays must be one of the classes 
 supported by {@link hirondelle.web4j.database.ConvertColumn}.
  
 <span class="highlight">The number and order of these parameter objects must match 
 the number and order of the '<tt>?</tt>' parameters in the underlying SQL 
 statement</span>.

 <P>For <tt>Id</tt> objects, the underlying column must be modeled as text, not a number. If
 the underlying column is numeric, then the caller must convert an {@link Id} into a numeric form
 using {@link Id#asInteger} or {@link Id#asLong}. 

 <P><tt>Locale</tt> and <tt>TimeZone</tt> objects represent a bit of a special case.
 Take for example a table storing user preferences, which stores locale and time zone. 
 How would you store that information? There are 2 basic styles: 
 <ul>
  <li>just place the <tt>Locale</tt> and <tt>TimeZone</tt> identifiers (en_CA, America/Montreal) in their own 
  columns, as text; this style will repeat the identifiers, and will not be 'normalized data', in database terminology.
  <li>define code tables for <tt>Locale</tt> and <tt>TimeZone</tt>, to define the accepted values, and link the user preferences
  table to them, using a foreign key. 
 </ul> 
 
 <P>The second form is usually more robust, since it's normalized. However, when it is used, passing <tt>Locale</tt> and 
 <tt>TimeZone</tt> objects directly to an <tt>INSERT</tt> statement, for example, using this class, is not appropriate.
 Instead, you'll need to treat them as any other code table, and translate the en_CA (for example) into 
 a corresponding foreign key identifier. In this case, the fact that <tt>Db</tt> supports  <tt>Locale</tt> and <tt>TimeZone</tt>
 becomes irrelevant, since you will translate them into an <tt>Id</tt> anyway.
  
<h3><a name="ConvertColumn"></a>Parsing Columns Into Objects</h3>
 For operations involving a <tt>ResultSet</tt>, this class will always use the application's 
 {@link ConvertColumn} implementation to convert columns into various building block objects. 
 
 <P>In addition, it uses an ordering convention to map <tt>ResultSet</tt> columns to Model 
 Object constructor arguments. See the package overview for more information on this important 
 point. 

 <h3><a name="CompoundObjects"></a>Compound Objects</h3>
 Occasionally, it is desirable to present a large amount of summary information to the user on a single page.
 In this case, an application needs a single large Model Object (a parent) containing collections 
 of other Model Objects (children). Here, these are called <em>Compound Objects</em>.   
 
 <P>Constructing an arbitrary Compound Object can always be performed in multiple steps: first 
 fetch the children, and then construct the parent, by passing the children to 
 the parent's constructor.
 
 <P><em>For the simplest cases</em>, this can be performed conveniently in a single step, using 
 the <tt>fetchCompound</tt> and <tt>listCompound</tt> methods of this class. These 
 methods process a <tt>ResultSet</tt> in a fundamentally different way : instead of translating a 
 <em>single</em> row into a single Model Object, they can translate <em>groups</em> of 1..N rows into 
 a single Model Object instead.
   
 <P>Here is an illustration. The target Model Object constructor has the form (for example):
 <PRE>
 public UserRole (String aUserName, List&lt;Id&gt; aRoles)  {
   ...
 }
 </PRE>
 <span class="highlight">That is, the constructor takes a <em>single</em> {@link List} of Model Objects at 
 the <em>end</em> of its list of arguments.</span> Here, a <tt>List</tt> of {@link Id} objects appears at the end.
 The <tt>List</tt> can be a <tt>List</tt> of Model Objects, or a <tt>List</tt> of Base Objects supported by 
 {@link hirondelle.web4j.model.ConvertParam}. 
  
 <P><span class="highlight">The underlying SELECT statement returns data across a <tt>0..N</tt> relation, with data 
 in the first N columns repeating the parent data, and with the remaining M columns containing the child data</span>. 
 For example:
 <PRE>
 SELECT Name, Role FROM UserRole ORDER BY Role
 </PRE>
 which has a <tt>ResultSet</tt> of the form :
 <table border=1 cellpadding=3 cellspacing=1>
  <tr><th>Name</th><th>Role</th></tr>
  <tr><td>kenarnold</td><td>access-control</td></tr>
  <tr><td>kenarnold</td><td>user-general</td></tr>
  <tr><td>kenarnold</td><td>user-president</td></tr>
  <tr><td>davidholmes</td><td>user-general</td></tr>
 </table>
 
 <P>That is, the repeated parent data (Name) comes first and is attached to the parent, while the 
 child data (Role) <em>appears only in the final columns</em>. <span class="highlight">In addition, changes to the 
 value in the <em>first</em> column must indicate that a new parent has started.</span>

 <P>If the above requirements are satisfied, then a {@code List<UserRole>} is built using 
 {@link #listCompound(Class, Class, int, SqlId, Object[])}, as in: 
 <PRE>
 Db.listCompound(UserRole.class, Id.class, 1, ROLES_LIST_SQL);
 </PRE>
*/ 
public final class Db {

  /**
   <tt>SELECT</tt> operation which returns a single Model Object. 
    
   @param aClass class of the returned Model Object.
   @param aSqlId identifies the underlying SQL statement.
   @param aParams <a href="#Parameters">parameters</a> for the SQL statement.
   @return <tt>null</tt> if no record is found.
  */
  public static <T> T fetch(Class<T> aClass, SqlId aSqlId, Object... aParams) throws DAOException {
    SqlFetcher fetcher = SqlFetcher.forSingleOp(aSqlId, aParams);
    ModelFromRow<T> builder = new ModelFromRow<T>(aClass);
    return fetcher.fetchObject(builder);
  }

  /**
   <tt>SELECT</tt> operation which returns a single 'building block' value such as <tt>Integer</tt>, <tt>BigDecimal</tt>, and so on.
  
   @param aSupportedTargetClass class supported by the configured 
   implementation of {@link ConvertColumn}.
   @param aSqlId identifies the underlying SQL statement.
   @param aParams <a href="#Parameters">parameters</a> for the SQL statement.
   @return <tt>null</tt> if no record is found.
  */
  public static <T> T fetchValue(Class<T> aSupportedTargetClass, SqlId aSqlId, Object... aParams) throws DAOException {
    SqlFetcher fetcher = SqlFetcher.forSingleOp(aSqlId, aParams);
    ModelBuilder<T> builder = new ValueFromRow<T>(aSupportedTargetClass); 
    return fetcher.fetchObject(builder);
  }
  
  /**
   <tt>SELECT</tt> operation which returns <tt>0..N</tt> Model Objects, one per row.  
    
   @param aClass class of the returned Model Objects.
   @param aSqlId identifies the underlying SQL statement.
   @param aParams <a href="#Parameters">parameters</a> for the SQL statement.
   @return an unmodifiable {@link List} of Model Objects. The list may be empty.
  */
  public static <T> List<T> list(Class<T> aClass, SqlId aSqlId, Object... aParams) throws DAOException {
    List<T> result = new ArrayList<T>();
    SqlFetcher fetcher = SqlFetcher.forSingleOp(aSqlId, aParams);
    ModelBuilder<T> builder = new ModelFromRow<T>(aClass);
    fetcher.fetchObjects(builder, result);
    return Collections.unmodifiableList(result);
  }
  
  /**
   <tt>SELECT</tt> operation which returns a <tt>List</tt> of 'building block' values such 
   as <tt>Integer</tt>, <tt>BigDecimal</tt>, and so on.
  
   @param aSupportedTargetClass class supported by the configured 
   implementation of {@link ConvertColumn}.
   @param aSqlId identifies the underlying SQL statement.
   @param aParams <a href="#Parameters">parameters</a> for the SQL statement.
   @return an unmodifiable {@link List} of building block objects. The list may be empty.
  */
  public static <T> List<T> listValues(Class<T> aSupportedTargetClass, SqlId aSqlId, Object... aParams) throws DAOException {
    List<T> result = new ArrayList<T>();
    SqlFetcher fetcher = SqlFetcher.forSingleOp(aSqlId, aParams);
    ModelBuilder<T> builder = new ValueFromRow<T>(aSupportedTargetClass); 
    fetcher.fetchObjects(builder, result);
    return Collections.unmodifiableList(result);
  }
  
  /**
   <tt>SELECT</tt> operation that returns a <tt>List</tt> of Model Objects "subsetted" to 
   a particular range of rows.
   
   <P>This method is intended for paging through long listings. When the underlying 
   <tt>SELECT</tt> returns many pages of items, the records can be "subsetted" by 
   calling this method.
   
   <P>See {@link hirondelle.web4j.ui.tag.Pager}.
   @param aClass class of the returned Model Objects.
   @param aSqlId identifies the underlying SQL statement.
   @param aStartIndex 1-based index indentifying the first row to be returned.
   @param aPageSize number of records to be returned.
   @param aParams <a href="#Parameters">parameters</a> for the SQL statement.
   @return an unmodifiable {@link List} of Model Objects. The list may be empty.
  */
  public static <T> List<T> listRange(Class<T> aClass, SqlId aSqlId, Integer aStartIndex, Integer aPageSize, Object... aParams) throws DAOException  {
    List<T> result = new ArrayList<T>();
    SqlFetcher fetcher = SqlFetcher.forSingleOp(aSqlId, aParams);
    fetcher.limitRowsToRange(aStartIndex, aPageSize);
    ModelBuilder<T> builder = new ModelFromRow<T>(aClass); 
    fetcher.fetchObjects(builder, result);
    return Collections.unmodifiableList(result);
  }

  /**
   <tt>SELECT</tt> operation for listing the result of a user's search with the given {@link DynamicSql} 
   and corresponding parameter values.
   
   <P>This method is called only if the exact underlying criteria are not known beforehand, but are rather
   determined <em>dynamically</em> by user selections. See {@link DynamicSql} for more information.
    
   @param aClass class of the returned Model Objects.
   @param aSqlId identifies the underlying SQL statement.
   @param aSearchCriteria criteria for the given search, containing <tt>WHERE</tt> and <tt>ORDER BY</tt> clauses.
   @param aParams <a href="#Parameters">parameters</a> for the SQL statement, corresponding to the given criteria.
   @return an unmodifiable {@link List} of Model Objects, corresponding to the input criteria. The list may be empty.
  */
  public static <T> List<T> search(Class<T> aClass, SqlId aSqlId, DynamicSql aSearchCriteria, Object... aParams) throws DAOException {
    List<T> result = new ArrayList<T>();
    SqlFetcher fetcher = SqlFetcher.forSearch(aSqlId, aSearchCriteria, aParams);
    ModelBuilder<T> builder = new ModelFromRow<T>(aClass);
    fetcher.fetchObjects(builder, result);
    return Collections.unmodifiableList(result);
  }
   
  /**
   <tt>INSERT</tt>, <tt>UPDATE</tt>, or <tt>DELETE</tt> operations which take parameters.
    
   @param aSqlId identifies the underlying SQL statement.
   @param aParams <a href="#Parameters">parameters</a> for the SQL statement.
   @return the number of records affected by this edit operation.
  */
  public static int edit(SqlId aSqlId, Object... aParams) throws DAOException, DuplicateException {
    SqlEditor change = SqlEditor.forSingleOp(aSqlId, aParams);
    return change.editDatabase();
  }
  
  /**
   <tt>INSERT</tt> operation which returns the database identifier of the added record.
  
   <P>This operation is not supported by all databases. See 
   {@link java.sql.Statement} for more information.
   
   @param aSqlId identifies the underlying SQL statement.
   @param aParams <a href="#Parameters">parameters</a> for the SQL statement.
  */
  public static Id add(SqlId aSqlId, Object... aParams) throws DAOException, DuplicateException  {
    SqlEditor add = SqlEditor.forSingleOp(aSqlId, aParams);
    return new Id(add.addRecord());
  }

  /**
   <tt>DELETE</tt> operation which takes parameters.
    
   @param aSqlId identifies the underlying SQL statement.
   @param aParams identifies the item to be deleted. Often 1 or more {@link Id} objects.
   @return the number of deleted records.
  */
  public static int delete(SqlId aSqlId, Object... aParams) throws DAOException {
    SqlEditor delete = SqlEditor.forSingleOp(aSqlId, aParams);
    return delete.editDatabase();
  }
  
  /**
   <tt>SELECT</tt> operation which typically returns a single item with a <tt>0..N</tt> relation. 
    
   <P>The <tt>ResultSet</tt> is parsed into a single parent Model Object having a <tt>List</tt> of 
   <tt>0..N</tt> child Model Objects. 
   See note on <a href="#CompundObjects">compound objects</a> for more information.
     
   @param aClassParent class of the parent Model Object.
   @param aClassChild class of the child Model Object.
   @param aNumTrailingColsForChildList number of columns appearing at the end of the <tt>ResultSet</tt> which 
   are passed to the <em>child</em> constructor.
   @param aSqlId identifies the underlying SQL statement.
   @param aParams <a href="#Parameters">parameters</a> to the underlying SQL statement.
  */
  public static <T> T fetchCompound(Class<T> aClassParent, Class<?> aClassChild, int aNumTrailingColsForChildList, SqlId aSqlId, Object... aParams) throws DAOException {
    SqlFetcher fetcher = SqlFetcher.forSingleOp(aSqlId, aParams);
    ModelBuilder<T> builder = new ModelFromRow<T>(aClassParent, aClassChild, aNumTrailingColsForChildList);
    return fetcher.fetchObject(builder);
  }

  /**
   <tt>SELECT</tt> operation which typically returns mutliple items item with a <tt>0..N</tt> relation. 
    
   <P>The <tt>ResultSet</tt> is parsed into a <tt>List</tt> of parent Model Objects, each having <tt>0..N</tt> 
   child Model Objects. See note on <a href="#CompoundObjects">compound objects</a> for more information.
     
   @param aClassParent class of the parent Model Object.
   @param aClassChild class of the child Model Object.
   @param aNumTrailingColsForChildList number of columns appearing at the end of the <tt>ResultSet</tt> which 
   are passed to the <em>child</em> constructor.
   @param aSqlId identifies the underlying SQL statement.
   @param aParams <a href="#Parameters">parameters</a> to the underlying SQL statement.
  */
  public static <T> List<T> listCompound(Class<T> aClassParent, Class<?> aClassChild, int aNumTrailingColsForChildList, SqlId aSqlId, Object... aParams) throws DAOException {
    List<T> result = new ArrayList<T>();
    SqlFetcher fetcher = SqlFetcher.forSingleOp(aSqlId, aParams);
    ModelBuilder<T> builder = new ModelFromRow<T>(aClassParent, aClassChild, aNumTrailingColsForChildList);
    fetcher.fetchObjects(builder, result);
    return result;
  }

  /**
   Add an <tt>Id</tt> to a list of parameters already extracted from a Model Object. 
   
   <P>This method exists to avoid repetition in your DAOs regarding the parameters
   passed to <em>add</em> and <em>change</em> operations. 
   
   <P>Take the following example :
   <PRE>
  INSERT INTO Resto (Name, Location, Price, Comment) VALUES (?,?,?,?)
  UPDATE Resto SET Name=?, Location=?, Price=?, Comment=? WHERE Id=?
  </PRE>
   In this case, the parameters are exactly the same, and appear in the same order, 
   <em>except</em> for the <tt>Id</tt> at the end of the <tt>UPDATE</tt> statement.
   
   <P>In such cases, this method can be used to simply append the <tt>Id</tt> to an 
   already existing list of parameters.
   
   @param aBaseParams all parameters used in an <tt>INSERT</tt> statement
   @param aId the <tt>Id</tt> parameter to append to <tt>aBaseParams</tt>,
   @return parameters needed for a <em>change</em> operation 
  */
  public static Object[] addIdTo(Object[] aBaseParams, Id aId){
    List<Object> result = new ArrayList<Object>();
    for(Object thing: aBaseParams){
      result.add(thing);
    }
    result.add(aId);
    return result.toArray(); 
  }
  
  /**
   Initialize the web4j database layer only.
   This method is intended for using the web4j database layer outside of a servlet container (for example, 
   in a command-line application).
    
   <P>The idea is that the services of the web4j data layer can be used in any application, not just web applications.
   Callers will also need to supply an implementation of {@link ConnectionSource}, in the usual way, by either 
   making use of the standard package name and class name ('hirondelle.web4j.config.ConnectionSrc'), or by specifying 
   the class name in the provided settings parameter. Callers that make use of the <tt>Reports</tt> class may also need to 
   supply an implementation of {@link hirondelle.web4j.request.DateConverter}. 
   
   @param aSettings correspond to the same name-value pairs defined in <tt>web.xml</tt>, and take the same values.
   Callers should pay particular attention to the settings related to the database. 
   See the <a href='http://www.web4j.com/UserGuide.jsp'>User Guide</a> for more information.
   @param aRawSql each String in this list corresponds to the contents of a single <tt>.sql</tt> file. 
  */
  public static void initStandalone(Map<String, String> aSettings, List<String> aRawSql) throws AppException{
    Config.init(aSettings); 
    BuildImpl.initDatabaseLayer(aSettings);  //not all interfaces init-ed
    Map<String, String> processedSql = ConfigReader.processRawSql(aRawSql);
    SqlStatement.initSqlStatementsManually(processedSql); 
  }

  // PRIVATE 
  
  private Db() {
    //prevent construction by the caller
  }
}
