/** Copyright (c) 2007 Ricebridge. BSD License. */

package com.ricebridge.example.updown;


import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Types;
import java.text.SimpleDateFormat;

import com.ricebridge.data.sc.BooleanStringConverter;
import com.ricebridge.data.sc.DoubleStringConverter;
import com.ricebridge.data.sc.IntegerStringConverter;
import com.ricebridge.data.sc.StringConverter;
import com.ricebridge.data.sc.StringStringConverter;


/** Convert between data values and their String representations. Insert data
 *  values into PreparedStatements and extract them from ResultSets. The table
 *  columns are referenced by the order of the column names given to the
 *  constructor. */
public class Converter {

  /** String to data converters for each table column. */
  private StringConverter[] mStringConverter;

  /** PreparedStatement set methods for each column. */
  private Method[] mSetMethod;

  /** ResultSet get methods for each column. */
  private Method[] mGetMethod;

  /** Column names as used by the database. */
  private String[] mColNames;
  
  
  /** Get the column names in database order. */
  public Converter( String pTableName, ResultSet pResultSet ) 
  throws Exception 
  {
    ResultSetMetaData rsmd = pResultSet.getMetaData();
    int numcols = rsmd.getColumnCount();
    String[] colnames = new String[numcols]; 
    for( int colI = 0; colI < colnames.length; colI++ ) {
      colnames[colI] = rsmd.getColumnName(colI+1);
    }
    initColumns(colnames,pTableName,rsmd);
  }
  
  
  /** Specify the column names in database order, and determine their 
   *  datatypes by connecting to the database. */
  public Converter( String[] pColNames, String pTableName, 
      String pIdentityColumn, Connection pConnection ) throws Exception 
  {
    
    // Get the column names
    PreparedStatement ps = pConnection.prepareStatement( 
      "SELECT * FROM "+pTableName+" WHERE "+pIdentityColumn+" IS NULL;" );
    ResultSet rs = null;
    
    try {
      rs = ps.executeQuery();
      ResultSetMetaData rsmd = rs.getMetaData();
      initColumns(pColNames,pTableName,rsmd);
    }
    finally {
      UpDownSupport.close(rs,ps,pConnection);
    }
  }

  
  /** Initialise each database table column. Determine column datatype
   *  and assign correct get/set methods from JDBC API objects. */
  public void initColumns( String[] pColNames, String pTableName, 
      ResultSetMetaData pRSMD ) throws Exception 
  {
    mColNames = pColNames;
    mSetMethod = new Method[mColNames.length];
    mGetMethod = new Method[mColNames.length];
    mStringConverter = new StringConverter[mColNames.length];

    for( int colI = 0; colI < pColNames.length; colI++ ) {
      String colname = pColNames[colI];
      int colindex = findColIndex(colname,pTableName,pRSMD);
      int coltype = pRSMD.getColumnType(colindex);
      initColumn( colI, coltype );
    }
  }
  
  
  /** Initialise string to data converters and get/set JDBC methods for given 
   *  database table column. */
  public void initColumn( int pColIndex, int pColType ) throws Exception {
    Class psc = PreparedStatement.class;
    Class rs  = ResultSet.class;
    
    // These assignments are very general. You can make them more
    // specific to handle your data types properly.
    if( Types.INTEGER == pColType 
        || Types.TINYINT == pColType 
        || Types.SMALLINT == pColType 
        || Types.BIGINT == pColType
        || Types.BIT == pColType ) {
      mSetMethod[pColIndex] 
        = psc.getMethod("setInt", new Class[]{Integer.TYPE,Integer.TYPE});
      mGetMethod[pColIndex] 
        = rs.getMethod("getInt", new Class[]{Integer.TYPE});
      mStringConverter[pColIndex] = new IntegerStringConverter();
    }
    else if( Types.FLOAT == pColType 
             || Types.DOUBLE == pColType 
             || Types.REAL == pColType 
             || Types.NUMERIC == pColType
             || Types.DECIMAL == pColType ) {
      mSetMethod[pColIndex] 
        = psc.getMethod("setDouble", new Class[]{Integer.TYPE,Double.TYPE});
      mGetMethod[pColIndex]
        = rs.getMethod("getDouble", new Class[]{Integer.TYPE});
      mStringConverter[pColIndex] = new DoubleStringConverter();
    }
    else if( Types.VARCHAR == pColType 
        || Types.CHAR == pColType 
        || Types.LONGVARCHAR == pColType ) {
      mSetMethod[pColIndex] 
        = psc.getMethod("setString", new Class[]{Integer.TYPE,String.class});
      mGetMethod[pColIndex] 
        = rs.getMethod("getString", new Class[]{Integer.TYPE});
      mStringConverter[pColIndex] = new StringStringConverter();
    }
    else if( Types.BOOLEAN == pColType ) {
      mSetMethod[pColIndex] 
        = psc.getMethod("setBoolean", new Class[]{Integer.TYPE,String.class});
      mGetMethod[pColIndex] 
        = rs.getMethod("getBoolean", new Class[]{Integer.TYPE});
      mStringConverter[pColIndex] = new BooleanStringConverter();
    }
    else if( Types.DATE == pColType 
        || Types.TIME == pColType 
        || Types.TIMESTAMP == pColType ) {
      mSetMethod[pColIndex] 
        = psc.getMethod("setDate", new Class[]{Integer.TYPE,String.class});
      mGetMethod[pColIndex] 
        = rs.getMethod("getDate", new Class[]{Integer.TYPE});
      mStringConverter[pColIndex] 
        = new SqlDateStringConverter( 
            new SimpleDateFormat("d MMM yyyy HH:mm:ss") );
    }
    else {
      throw new Exception( "Column '"+mColNames[pColIndex]
                           +"' has an unsupported data type." );
    }
  }
  
  
  /** Find the index of a column by column name. */
  public int findColIndex( String pColName, String pTableName, 
      ResultSetMetaData pRSMD ) throws Exception 
  {
    int numcols = pRSMD.getColumnCount();
    for( int colI = 1; colI <= numcols; colI++ ) {
      String cn = pRSMD.getColumnName(colI);
      if( cn.equals(pColName) ) {
        return colI;
      }
    }
    throw new Exception( "Column '"+pColName
                         +"' not found in table '"+pTableName+"'" );
  }
  
  
  /** Insert data into a PreparedStatement using intialised set methods. */
  public void setData( String[] pData, PreparedStatement pPS ) 
  throws Exception 
  {
    for( int colI = 0; colI < mSetMethod.length; colI++ ) {
      setData( colI+1, colI, pData[colI], pPS );
    }
  }
  
  
  /** Insert indexed column data value into a PreparedStatement. */
  public void setData( int pIndex, int pColumn, String pData, 
      PreparedStatement pPS ) throws Exception 
  {
    Object value = mStringConverter[pColumn].makeObject( pData, false );
    mSetMethod[pColumn].invoke( 
        pPS, new Object[] {new Integer(pIndex), value} );
  }
  

  /** Get data values out of a ResultSet and convert them to a String 
   *  array. */
  public String[] getData( ResultSet pResultSet ) throws Exception {
    String[] data = new String[mGetMethod.length];
    for( int colI = 0; colI < mGetMethod.length; colI++ ) {
      data[colI] = getData( colI+1, colI, pResultSet );
    }
    return data;
  }

  
  /** Get data value of an individual column as a String value. */
  public String getData( int pIndex, int pColumn, ResultSet pResultSet ) 
  throws Exception 
  {
    Object value = mGetMethod[pColumn].invoke( pResultSet, 
        new Object[] {new Integer(pIndex)} );  
    return mStringConverter[pColumn].makeString( value, false );
  }
  
  
  /** Get the names of the database columns. */
  public String[] getColumnNames() {
    return mColNames;
  }
  
}