/** You may copy this example and use it for any purpose, commercial or otherwise. */

import com.ricebridge.csvman.CsvManager;
import com.ricebridge.csvman.CustomLineListener;
import com.ricebridge.csvman.CustomLineProvider;
import com.ricebridge.csvman.BadLine;
import com.ricebridge.csvman.CsvLoader;
import com.ricebridge.csvman.CsvSaver;

import java.sql.*;
import java.util.*;
import java.io.*;


/** This is a simple program to import CSV data into a database table and export the same database table
 *  out to CSV. The table definition is given in the file products.csv.
 *  
 *  The program takes two command line arguments: load|save and full|event|stream
 *  Both are optional, the defaults are load full
 *  
 *  The first argument tells the program to either load a CSV file, or save a CSV file.
 *  The second argument determines how the load or save operation will be done. 
 *  - full means that the full data set will be loaded or saved in one go.
 *  - event means that the data set will be loaded or saved one line at a time, with events being passed to
 *    callback interfaces (LineListener or LineProvider)
 *  - stream means that the data set will be loaded or save one line at a time, with the operation
 *    controlled using the CsvLoader or CsvSaver objects.
 *    
 *  To compile this class, you'll need to include lib/csvman.jar in your CLASSPATH.
 */
public class ImportExport {

  /** Main entry point of the program. Handles command line arguments. */
  public static void main( String[] args ) throws Exception {

    // only accept 0, 1 or 2 arguments
    if( -1 < args.length && args.length < 3 ) {

      // these are the defaults
      String action = "load";
      String type   = "full";
      
      if( 1 <= args.length ) {
        action = args[0]; // first argument, if found
      }
      if( 2 <= args.length ) {
        type = args[1]; // second argument, if found
      }

      // do load or save operation using chosen method
      if( "full".equals( type ) ) {
        doFull( action );
      }
      else if( "event".equals( type ) ) {
        doEvent( action );
      }
      else {
        doStream( action );
      }
    }
    else {
      System.out.println( "Usage: java ImportExport [load|save] [full|event|stream]" );
    }
  }


  /** Handle full data set loading and saving. */
  public static void doFull( String pAction ) throws Exception {
    if( "load".equals( pAction ) ) {
      doLoadFull();
    }   
    else {
      doSaveFull();
    }
  }


  /** Handle event-based data set loading and saving. */
  public static void doEvent( String pAction ) throws Exception {
    if( "load".equals( pAction ) ) {
      doLoadEvent();
    }   
    else {
      doSaveEvent();
    }
  }

  
  /** Handle stream-based data set loading and saving. */
  public static void doStream( String pAction ) throws Exception {
    if( "load".equals( pAction ) ) {
      doLoadStream();
    }   
    else {
      doSaveStream();
    }
  }


  /** Load full data set from CSV file and insert into database. */
  public static void doLoadFull() throws Exception {
    System.out.println( "Performing: load full" );

    Connection        con = getConnection();
    PreparedStatement ps  = con.prepareStatement( "INSERT INTO Product VALUES (?,?,?,?)" ); 

    // load all the data at once
    CsvManager csvman = new CsvManager();
    List data = csvman.load( "products.csv" );    
    
    // start at index 1 as first line contains headers names, not data
    for( int line = 1; line < data.size(); line++ ) {
      String[] fields = (String[]) data.get(line);
      insertRow( fields, ps );
    }    

    con.close();
  }


  /** Save full data set from database to CSV file . */
  public static void doSaveFull() throws Exception {
    System.out.println( "Performing: save full" );

    Connection        con = getConnection();
    PreparedStatement ps  = con.prepareStatement( "SELECT * FROM Product" ); 

    CsvManager csvman = new CsvManager();
    // this insures that the first output line will contain the data field header names
    csvman.getCsvSpec().setProperty("ResultSet.saveHeaders", true);
    ResultSet rs = ps.executeQuery();

    // save all the data at once
    csvman.saveResultSet( "products-export.csv", rs );

    con.close();
  }


  /** Load data set one line at a time from CSV file and insert into database, using events. */
  public static void doLoadEvent() throws Exception {
    System.out.println( "Performing: load event" );

    CsvManager csvman = new CsvManager();
    // the first line is header names
    csvman.getCsvSpec().setStartLine(2);
    csvman.load( "products.csv", new DatabaseListener() );
  }


  /** Save data set one line at a time from database to CSV file, using events. */
  public static void doSaveEvent() throws Exception {
    System.out.println( "Performing: save event" );

    CsvManager csvman = new CsvManager();
    csvman.save( "products-export.csv", new DatabaseProvider() );
  }

  
  /** Load data set one line at a time from CSV file and insert into database, using streaming API. */
  public static void doLoadStream() throws Exception {
    System.out.println( "Performing: load stream" );

    Connection        con = getConnection();
    PreparedStatement ps  = con.prepareStatement( "INSERT INTO Product VALUES (?,?,?,?)" ); 

    CsvManager csvman = new CsvManager();
    
    // create a loader to load each data line on demand
    CsvLoader loader = csvman.makeLoader( "products.csv" );    

    boolean first = true;

    loader.begin();
    while( loader.hasNext() ) {
      // ignore headers
      if( first ) { 
        first = false; 
        continue;
      }
      String[] fields = loader.next();
      insertRow( fields, ps );
    }    
    loader.end();
    
    con.close();
  }

  
  /** Save data set one line at a time from database to CSV file, using streaming API. */
  public static void doSaveStream() throws Exception {
    System.out.println( "Performing: save stream" );

    Connection        con = getConnection();
    PreparedStatement ps  = con.prepareStatement( "SELECT * FROM Product" ); 

    CsvManager csvman = new CsvManager();
    ResultSet rs = ps.executeQuery();

    // create a saver to save each data line on demand
    CsvSaver saver = csvman.makeSaver( "products-export.csv" );

    saver.begin();

    // save the header line manually
    String[] fields = new String[] {"id","code","name","price"};
    saver.next( fields );
    
    while( rs.next() ) {
      loadRow( fields, rs );
      saver.next( fields );
    }    
    saver.end();
    
    con.close();
  }


  /** Utility method: insert data row into database using INSERT. */
  public static void insertRow( String[] pFields, PreparedStatement pPS ) throws Exception {
    pPS.setInt(    1, Integer.parseInt( pFields[0] ) );
    pPS.setString( 2, pFields[1] );
    pPS.setString( 3, pFields[2] );
    pPS.setInt(    4, Integer.parseInt( pFields[3] ) );
    pPS.executeUpdate();
  }


  /** Utility method: update String[] array with data row from database. */ 
  public static void loadRow( String[] pFields, ResultSet pResultSet ) throws Exception {
    pFields[0] = String.valueOf(pResultSet.getInt(1));
    pFields[1] = pResultSet.getString(2);
    pFields[2] = pResultSet.getString(3);
    pFields[3] = String.valueOf(pResultSet.getInt(4));
  }


  /** Utility method: get database connection. 
   *  You will have to modify this method to connect to your own database.
   *  */
  public static Connection getConnection() throws Exception {
    // edit these for your local database, 
    // table definition is in product-table.sql
    String dburl 
      = "jdbc:mysql://localhost/mydatabase?"
      + "user=username&password=password";
    Driver d 
      = (Driver) Class.forName( "com.mysql.jdbc.Driver" ).newInstance(); 
    DriverManager.registerDriver( d );
    Connection con = DriverManager.getConnection(dburl);
    return con;
  }


  /** Event listener for loading from CSV file. */
  public static final class DatabaseListener extends CustomLineListener {
    private Connection con = null;
    private PreparedStatement ps = null;

    /** Open the database connection here. */
    protected void startProcessImpl() throws Exception {
      con = getConnection();
      ps  = con.prepareStatement( "INSERT INTO Product VALUES (?,?,?,?)" ); 
    }

    /** Handle each line of data - save it to the database. */
    protected BadLine handleLineImpl( String[] pFields, int pNumFields, long pLineNumber, String pOriginalLine) throws Exception {
      insertRow( pFields, ps );      
      return null;
    }

    /** Close the database connection here. */
    protected void endProcessImpl() throws Exception {
      con.close();
    }
  }


  /** Event provider for loading from database. */ 
  public static final class DatabaseProvider extends CustomLineProvider {
    private Connection con   = null;
    private ResultSet  rs    = null;
    private boolean    first = true; 

    /** Open the database connection here. */
    protected void startProcessImpl() throws Exception {
      con = getConnection();
      PreparedStatement ps  = con.prepareStatement( "SELECT * FROM Product" ); 
      rs = ps.executeQuery();
    }

    /** Check if the ResultSet has any more rows left. */
    protected boolean hasNextLineImpl() throws Exception {
      if( first ) { return true; } // always output header line
      return rs.next();
    }

    protected String[] nextLineImpl() throws Exception {
      String[] fields = new String[4];

      // check for first line, and return headers manually
      if( first ) {
        fields = new String[] {"id","code","name","price"};
        first  = false;
      }
      
      // returnnext row from database
      else {
        loadRow( fields, rs );
      }
      
      return fields;
    }

    /** Close the database connection here. */
    protected void endProcessImpl() throws Exception {
      con.close();
    }
  }

}