/*
 ============================================================================================
 
 Copyright(c) 2000 - 2006 by

 I - T e c h n o l o g i e s   C o r p (Itc)

 All Rights Reserved

 THIS PROGRAM IS PROVIDED UNDER THE TERMS OF THE ITC PUBLIC LICENSE VER 1.0 (“AGREEMENT”),
 PROVIDED WITH THIS PROGRAM. ANY USE, REPRODUCTION OR DISTRIBUTION OF THE PROGRAM
 CONSTITUTES RECEIPIENTS ACCEPTANCE OF THIS AGREEMENT.

 Source Name: DbTest.java

 Create Date: Apr 11, 2006
 ============================================================================================
 */
package test.itc.db;

import java.net.URL;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.ResourceBundle;

import junit.framework.Test;
import junit.framework.TestCase;
import junit.framework.TestSuite;
import test.junit.dvo.Account;
import test.junit.dvo.AccountType;
import test.junit.dvo.Acctholder;
import test.junit.dvo.Address;
import test.junit.dvo.BankTransaction;
import test.junit.dvo.NameAddr;
import test.junit.dvo.TranType;

import com.itc.db.ItcColInfo;
import com.itc.db.ItcDatabase;
import com.itc.db.ItcDbResourceMgr;
import com.itc.db.ItcForeignKeyInfo;
import com.itc.db.ItcSqlMgr;
import com.itc.util.ItcDate;
import com.itc.util.ItcLogger;
import com.itc.util.ItcResourceMgr;
import com.itc.util.ItcResourceStore;
import com.itc.util.ItcResourceStoreFactory;

/**
 * @author D. Anthony
 * 
 * TODO To change the template for this generated type comment go to Window -
 * Preferences - Java - Code Style - Code Templates
 */
public class DbTest extends TestCase
{

  private ItcDatabase dbJunit;

  private ItcSqlMgr sqlMgr;

  private ItcDbResourceMgr m_dbResMgr;

  private static ItcResourceStore s_resStore;

  public DbTest( String name )
  {
    super( name );
  }

  public static void main( String[] args )
  {
    try
    {
      s_resStore = ItcResourceStoreFactory.getInstance().getStore();
    }
    catch ( Exception ex )
    {
      ex.printStackTrace();

    }
    String name = DbTest.class.getName();
    junit.textui.TestRunner.main( new String[] { name } );
  }

  public static Test suite()
  {
    System.out.println( "##### In test.itc.db.DbTest suite" );
    return new DbTestDecorator( new TestSuite( DbTest.class ) );
  }

  /**
   * Test the high-level ItcSqlMgr SQL DML statements for database Save
   * 
   */
  public void testItcSqlMgrSave() throws Exception
  {

    // Populate test database tables previously created in the Test Decorator
    // class

    // Populate the Itc generated database table value objects:
    // 1) Populate Accountholder object. . .
    Acctholder acctHolder = new Acctholder();
    acctHolder.setFirstName( "John" );
    acctHolder.setLastName( "Doe" );
    acctHolder.setSsn( "123-45-6789" );
    acctHolder.setCreateDate( new ItcDate() );

    // 2) Populate Address object. . .
    Address address = new Address();

    address.setStreet1( "40 Doe Lane" );
    address.setCity( "Fairfield" );
    address.setState( "CT" );
    address.setZip( "06825" );
    // Set Address into Account Holder
    acctHolder.setAddress( address );

    List listAccts = new ArrayList();

    // 3) Populate Account object. . .
    Account account = new Account();
    account.setAccountBalance( new Double( 2050.88 ) );
    account.setCreateDate( new ItcDate() );

    // 4) Populate Account Type object. . .
    AccountType accountType = new AccountType();
    accountType.setName( "Checking" );
    // Set Account Type into Account
    account.setAccountType( accountType );

    List listTrans = new ArrayList();

    // 5) Populate BankTransaction object (1). . .
    BankTransaction tran = new BankTransaction();
    tran.setTranDate( new ItcDate() );
    tran.setTranamount( new Double( 2096.33 ) );
    // 6) Populate Transaction Type object for Transaction 1 . . .
    TranType tranType = new TranType();
    tranType.setName( "Deposit" );
    // Set Transaction Type into BankTransaction 1
    tran.setTranType( tranType );
    listTrans.add( tran );

    // 5) Populate BankTransaction object (2). . .
    tran = new BankTransaction();
    tran.setTranDate( new ItcDate() );
    tran.setTranamount( new Double( 45.45 ) );
    // 6) Populate Transaction Type object for Transaction 2 . . .
    tranType = new TranType();
    tranType.setName( "Debit" );
    // Set Transaction Type into BankTransaction 2
    tran.setTranType( tranType );
    listTrans.add( tran );

    // Set BankTransactions into Account
    account.setBankTransaction( listTrans );
    listAccts.add( account );

    // Set Accounts into Account Holder
    acctHolder.setAccount( listAccts );

    assertFalse( "Did not expect row to exist", sqlMgr.exists( acctHolder ) );

    // Get The DAO 
    BankDemoDAO dao = BankDemoDAOFactory.getBankDemoDAO();
    
    // Save the Acctholder object graph
    dao.saveAcctHolder( acctHolder );

    assertTrue( "Expected account row to exist", sqlMgr.exists( acctHolder ) );

  }

  /**
   * Test the high-level DAO's finders
   * 
   */
  public void testItcSqlMgrFindBy() throws Exception
  {

    Address address = null;
    Account account = null;
    AccountType accountType = null;
    BankTransaction transaction = null;
    TranType tranType = null;

    // Get The DAO 
    BankDemoDAO dao = BankDemoDAOFactory.getBankDemoDAO();

    Acctholder acctholder = dao.getAcctHolderBySsn( "123-45-6789" );

    // Assert all previously Inserted data is present and in the expected state
    assertNotNull( "Acctholder should not be null", acctholder );

    // object should not be dirty
    assertFalse( "Not expecting Acctholder to be dirty", acctholder.isDirty() );
    assertEquals( "John", acctholder.getFirstName() );
    assertEquals( "Doe", acctholder.getLastName() );

    assertNotNull( "Address should not be null", acctholder.getAddress() );
    address = (Address)acctholder.getAddress();
    assertEquals( "Fairfield", address.getCity() );
    // object should not be dirty
    assertFalse( "Not expecting ddress to be dirty", address.isDirty() );

    assertNotNull( "List Account should not be null", acctholder.getAccount() );
    assertEquals( "There should be 1 Account in list", 1, acctholder.getAccount().size() );

    account = (Account)acctholder.getAccount().get( 0 );

    // object should not be dirty
    assertFalse( "Not expecting Acctount to be dirty", account.isDirty() );

    assertEquals( "Account Balance should equal $2050.88", new Double( 2050.88 ), account.getAccountBalance() );

    assertNotNull( "AccountType should not be null", account.getAccountType() );
    accountType = (AccountType)account.getAccountType();
    // object should not be dirty
    assertFalse( "Not expecting AccountType to be dirty", accountType.isDirty() );

    assertEquals( "Type of Account should be 'Checking'", "Checking", accountType.getName() );

    assertNotNull( "List BankTransaction should not be null", account.getBankTransaction() );
    assertEquals( "There should be 2 BankTransactions in list", 2, account.getBankTransaction().size() );

    transaction = (BankTransaction)account.getBankTransaction().get( 0 );
    // object should not be dirty
    assertFalse( "Not expecting BankTransaction to be dirty", transaction.isDirty() );

    assertEquals( "BankTransaction Amount 1 should equal $2096.33", new Double( 2096.33 ), transaction.getTranamount() );

    assertNotNull( "TranType 1 should not be null", transaction.getTranType() );
    tranType = (TranType)transaction.getTranType();
    // object should not be dirty
    assertFalse( "Not expecting TranType to be dirty", tranType.isDirty() );

    assertEquals( "Transaction Type of BankTransaction 1 should be 'Deposit'", "Deposit", tranType.getName() );

    transaction = null;
    tranType = null;

    transaction = (BankTransaction)account.getBankTransaction().get( 1 );
    assertEquals( "BankTransaction Amount 2 should equal $45.45", new Double( 45.45 ), transaction.getTranamount() );

    assertNotNull( "TranType 2 should not be null", transaction.getTranType() );
    tranType = (TranType)transaction.getTranType();
    assertEquals( "Transaction Type of BankTransaction 2 should be 'Debit'", "Debit", tranType.getName() );

    // *** Get custom query with the NameAddr object

    NameAddr nameAddr = dao.getAcctNameAndAddress( "123-45-6789" );
    assertNotNull( "Did not expect NameAddr object to be null for ssn 123-45-6789", nameAddr );
    assertTrue( "Expected first name to be John but got " + nameAddr.getFirstName(), nameAddr.getFirstName().equals(
        "John" ) );
    assertTrue( "Expected last name to be Doe but got " + nameAddr.getLastName(), nameAddr.getLastName().equals( "Doe" ) );
    assertTrue( "Expected city to be Fairfield but got " + nameAddr.getCity(), nameAddr.getCity().equals( "Fairfield" ) );

  }

  /**
   * Test the high-level ItcSqlMgr SQL DML statements for update
   * 
   */
  public void testItcSqlMgrUpdate() throws Exception
  {

    Address address = null;
    Account account = null;
    BankTransaction transaction = null;
    TranType tranType = null;
    // Get The DAO 
    BankDemoDAO dao = BankDemoDAOFactory.getBankDemoDAO();

    Acctholder acctHolder = dao.getAcctHolderBySsn( "123-45-6789" );

    // Customer Address Update
    address = (Address)acctHolder.getAddress();
    address.setStreet1( "25 Holyfield Dr." );
    address.setCity( "Glastonbury" );
    address.setZip( "06033" );
    assertTrue( "Address Object should be Dirty from value change!", address.isDirty() );

    acctHolder.setAddress( address );

    // Account Balance Update
    List listAccts = new ArrayList();
    listAccts = acctHolder.getAccount();
    account = (Account)listAccts.get( 0 );
    account.setAccountBalance( new Double( 2141.78 ) );
    assertTrue( "Account Object should be Dirty from value change!", account.isDirty() );

    // BankTransaction 2 Adjustment Update
    List listTrans = new ArrayList();
    listTrans = account.getBankTransaction();
    transaction = (BankTransaction)listTrans.get( 1 );
    tranType = (TranType)transaction.getTranType();
    tranType.setName( "Credit" );
    assertTrue( "TranType Object should be Dirty from value change!", tranType.isDirty() );

    transaction.setTranType( tranType );
    listTrans.set( 1, transaction );
    account.setBankTransaction( listTrans );

    listAccts.set( 0, account );
    acctHolder.setAccount( listAccts );
    dao.saveAcctHolder( acctHolder );
    
    acctHolder = null;


    acctHolder = dao.getAcctHolderBySsn( "123-45-6789" );

    // Assert all Updated values are present, and validate for accuracy
    address = (Address)acctHolder.getAddress();
    assertEquals( "25 Holyfield Dr.", address.getStreet1() );
    assertEquals( "Glastonbury", address.getCity() );
    assertEquals( "06033", address.getZip() );

    account = (Account)acctHolder.getAccount().get( 0 );
    assertEquals( "Account Balance should equal $2141.78", new Double( 2141.78 ), account.getAccountBalance() );

    assertNotNull( "List BankTransaction should not be null", account.getBankTransaction() );
    assertEquals( "There should be 2 BankTransactions in list", 2, account.getBankTransaction().size() );

    transaction = (BankTransaction)account.getBankTransaction().get( 1 );
    tranType = (TranType)transaction.getTranType();
    assertEquals( "Transaction Type of BankTransaction 2 should be 'Credit'", "Credit", tranType.getName() );

  }

  /**
   * Test the meta data methods of the ItcDatabase class
   * 
   * @throws Exception
   */
  public void testDatabaseMetaData() throws Exception
  {

    System.out.println( " " );

    System.out.println( "Database Name: " + dbJunit.getDatabaseName() );

    System.out.println( "Database Version: " + dbJunit.getDatabaseVersion() );

    System.out.println( "Database Driver Name: " + dbJunit.getDriverName() );

    System.out.println( "Database Driver Version: " + dbJunit.getDriverVersionNbr() );

    System.out.println( "Database Driver Archive: " + m_dbResMgr.getDbMgr().getArchive() );

    // Get Database Catalog Information (if supported by database driver)
    String[] astrCatalogs = dbJunit.getCatalogs();
    if (astrCatalogs != null)
    {
      for ( int i = 0; i < astrCatalogs.length; i++ )
      {
        System.out.println( "Catalog Name: " + astrCatalogs[i] );
      }
      System.out.println( "****************" );
    }
    else
      System.out.println( "Catalog Name(s) null (not supported by driver)" );

    // Get Database Schema Information
    String[] astrSchemas = dbJunit.getSchemas();
    if (astrSchemas != null)
    {
      for ( int i = 0; i < astrSchemas.length; i++ )
      {
        System.out.println( "Schema Name: " + astrSchemas[i] );
      }
      System.out.println( "***************" );
    }
    else
      System.out.println( "Schema Name(s) null (no schema defined)" );

    // Get Database Table Names for a database catalog and schema (null param
    // for All Tables)
    String[] astrTables = null;
    String tblPatternNamesAll = null;

    if (astrCatalogs != null && astrSchemas != null)
      astrTables = dbJunit.getTables( astrCatalogs[0], astrSchemas[0], tblPatternNamesAll );
    else
      astrTables = dbJunit.getTables( null, null, tblPatternNamesAll );

    if (astrTables != null)
    {
      for ( int i = 0; i < astrTables.length; i++ )
      {
        System.out.println( "Table Name: " + astrTables[i] );
      }
      System.out.println( "**************" );
    }
    else
      System.out.println( "Table Name(s) are null!" );

    // Get List of Columns for the first Table in our Array above
    List tableColumns = new ArrayList();

    if (astrCatalogs != null && astrSchemas != null)
      tableColumns = dbJunit.getColumns( astrCatalogs[0], astrSchemas[0], "ACCOUNT" );
    else
      tableColumns = dbJunit.getColumns( null, null, "ACCOUNT" );

    if (!tableColumns.isEmpty())
    {
      for ( Iterator iColumns = tableColumns.iterator(); iColumns.hasNext(); )
      {
        ItcColInfo itcColInfo = (ItcColInfo)iColumns.next();
        System.out.println( "ACCOUNT Table Column Name: " + itcColInfo.getColumnName() );
      }
      System.out.println( "**************" );
    }
    else
      System.out.println( "Table Column Name(s) is empty!" );

    // Get List of Primary Keys for the first Table in our Array above
    List primaryKeys = new ArrayList();

    primaryKeys = dbJunit.getPrimaryKeys( null, null, "ACCOUNT" );

    if (!primaryKeys.isEmpty())
    {
      for ( Iterator iKeys = primaryKeys.iterator(); iKeys.hasNext(); )
      {
        ItcColInfo itcColInfo = (ItcColInfo)iKeys.next();
        System.out.println( "ACCOUNT Table Primary Key: " + itcColInfo.getColumnName() );
      }
      System.out.println( "**************" );
    }
    else
      System.out.println( "Table Primary Key(s) is empty!" );

    // Get List of Foreign Keys for the first Table in our Array above
    List foreignKeys = new ArrayList();

    foreignKeys = dbJunit.getForeignKeys( null, null, "ACCOUNT" );

    if (!foreignKeys.isEmpty())
    {
      for ( Iterator iKeys = foreignKeys.iterator(); iKeys.hasNext(); )
      {
        ItcForeignKeyInfo itcForeignKeyInfo = (ItcForeignKeyInfo)iKeys.next();
        System.out.println( "ACCOUNT Table Foreign Key: " + itcForeignKeyInfo.getFkColName() );
      }
      System.out.println( "**************" );
    }
    else
      System.out.println( "Table Foreign Key(s) is empty!" );

  }

  /**
   * Test the low-level ItcSqlMgr standard SQL DML statements for Insert and
   * Select
   * 
   */
  public void testItcSqlMgrInsert() throws Exception
  {

    // Populate the Itc generated Address database table value object
    Address address = new Address();
    address.setAddressIdPk( new Long( 332 ) );
    address.setStreet1( "60 Volley Dr." );
    address.setCity( "Cheesetown" );
    address.setState( "MN" );
    address.setZip( "75602" );

    String strSQL = "insert into address (Address_ID_PK, Street1, Street2, City, State, Zip) "
        + "  values ( :AddressIdPk,:Street1,:Street2,:City,:State,:Zip )";

    // Execute SQL Database Insert via the ItcSqlMgr
    sqlMgr.exec( strSQL, address );
    assertEquals( "There should be 1 Row Affected by the Address Insert:", 1, sqlMgr.getRowsAffected() );

    // Populate the Itc generated Accountholder database table value object
    Acctholder acctholder = new Acctholder();
    acctholder.setAcctHolderIdPk( new Long( 333 ) );
    acctholder.setFirstName( "Jean" );
    acctholder.setLastName( "Yoe" );
    acctholder.setSsn( "040-56-7890" );
    acctholder.setCreateDate( new ItcDate() );
    acctholder.setAddressIdFk( new Long( 332 ) );

    strSQL = "insert into acctholder (Acct_Holder_ID_PK, First_Name, Last_Name" + ", SSN, Create_Date, Address_ID_FK) "
        + "  values ( :AcctHolderIdPk,:FirstName,:LastName,:Ssn,:CreateDate,:AddressIdFk )";

    // Execute SQL Database Insert via the ItcSqlMgr
    sqlMgr.setAutoCommit( false );

    sqlMgr.exec( strSQL, acctholder );
    assertEquals( "There should be 1 Row Affected by the Acctholder Insert:", 1, sqlMgr.getRowsAffected() );

    sqlMgr.commit();

    // Now Select the Acctholder just Inserted
    acctholder = new Acctholder();
    acctholder.setSsn( "040-56-7890" );

    strSQL = "select Acct_Holder_ID_PK AcctHolderIdPk ,First_Name FirstName,Last_Name LastName"
        + ", SSN Ssn,Create_Date CreateDate,Address_ID_FK AddressIdFk " + " from acctholder where SSN = :Ssn";

    // Execute SQL Database Select via the ItcSqlMgr
    sqlMgr.exec( strSQL, acctholder );

    acctholder = null;
    acctholder = (Acctholder)sqlMgr.getNext( Acctholder.class );

    // Assert previously Inserted Acctholder data is present and in the expected
    // state
    assertNotNull( "Acctholder should not be null", acctholder );
    assertEquals( "Jean", acctholder.getFirstName() );
    assertEquals( "Yoe", acctholder.getLastName() );

  }

  /**
   * Database connection and login
   * 
   */
  public void setUp() throws Exception
  {
    // Database connection and login:

    ItcResourceMgr.loadBundle( "junit", true );
    String strDAODocName = ItcResourceMgr.getString( "itcxsmdoc" );
    s_resStore = ItcResourceStoreFactory.getInstance().getStore();

    // (JUnit properties) Data Source and Itc DAO document resources
    URL urlDaoDoc = s_resStore.getDocument( strDAODocName );

    // JUnit properties initial database specific parameters
    String strDriver = ItcResourceMgr.getString( "itcdbdrivername" );
    String strUrlId = ItcResourceMgr.getString( "itcdburlid" );
    String strUID = ItcResourceMgr.getString( "itcdbuid" );
    String strPWD = ItcResourceMgr.getString( "itcdbpwd" );

    try
    {
      // Demonstrates standalone use of the ItcDbResourceMgr. Typicially your DAO impl object will extend from this class.
      // Please see BankDemoDAOImpl.java for an example of this 
      m_dbResMgr = new ItcDbResourceMgr( strDriver, strUrlId, urlDaoDoc, ItcLogger.getInstance( "junit.properties" ) );

      // Using the ItcDbMgr, call login to return an ItcDatabase instance.
      // The ItcDatabase instance holds the connection to the JDBC data source.
      dbJunit = m_dbResMgr.getDatabase( strUID, strPWD );
      // dbJunit = dbMgr.login( "system", "pvosbur1");

      // We'll use ItcSqlMgr to execute SQL statements against the ItcDatabase
      // instance.
      // The ItcSqlMgr abstracts the JDBC statement objects and takes care of
      // binding
      // parameters to sql statements and Stored Procedure calls.
      sqlMgr = m_dbResMgr.getSqlMgr( dbJunit );

      // sqlMgr.setAutoCommit( true );
    }
    catch ( Exception ex )
    {
      ex.printStackTrace();
    }

  } // End DbTest setUp()

  public void tearDown() throws Exception
  {

    try
    {
      m_dbResMgr.closeResources( dbJunit, sqlMgr );

    }
    catch ( Exception ex )
    {
      ;
    }

  } // End DbTest tearDown()

} // end class DbTest{}

// *** End of DbTest.java ***