import java.sql.*;
import java.util.*;
import java.io.*;
 
/* 
 * Driver
 *
 * $Author: $
 * $Date: $
 * $Revisions: $
 * $Source: $
 *  To create a new Driver
 * 
 *    Driver instance = new Driver
 *     .. call the set methods                       // Set the fields as needed
 *    String key = instance.store();                 // .store() inserts record to database
 *                                                   // DB sequence "driver_seq" is used to get
 *                                                   // next primary key
 *
 *  To get a Driver from the database
 *                                                       // To retrieve a Driver from database
 *    Driver instance = new Driver("1234");              // pass the primary key to constructor
 *    ... call the get() methods                         // and then call the getter methods             
 * 
 *
 *  To update an existing Driver                 
 *                                                        // To update an existing OrderObj
 *    Driver instance = new Driver("1234");               // get it from database, call the setter methods
 *     .. call the set methods                            // and then call store()
 *    instance.store(); 
 *
 *
 */
public class Driver {

  private long key = 0;
  private String firstName = "";
  private String middleName = "";
  private String lastName = "";
  private String gender = "";
  private float licensePoints = 0;
  private long addressId = 0;
  private GregorianCalendar dateOfBirth = null;
  private long contactInfoId = 0;
  private GregorianCalendar created = null;
  private GregorianCalendar updated = null;
  public Address address  = null;
  public ContactInfo contactInfo  = null;

  private ArrayList arrayListOfCars = null;  //Store sub-objects to this one
  

  private boolean isDirty = false;

  /**
   *
   * To get an existing Driver call constructor with primary key
   *
   */
  public Driver(long keyIn) {  
    Connection conn = null;
    Statement stmt = null;
    try {
     conn = getConnection();
     stmt = conn.createStatement();
    } catch (SQLException exp) {             
      log("Driver(key) constructor, Exception: " + exp.getMessage());
    } 

    key = keyIn;
    ResultSet rset = doSelect(conn, stmt);
    address = new Address(rset);
    contactInfo = new ContactInfo(rset);

    try {
      rset.close();
      stmt.close();
      conn.close();
    } catch (SQLException exp) {             
      log("Driver(key) constructor, Exception: " + exp.getMessage());
    } 
    loadCars();

  }


  /**
   *
   * To populate a Driver where the sql select was done earlier by another object
   * call this constructor with the result set.
   *   
   */
  public Driver(ResultSet rset) {  
    applyResultSet(rset);                         //Populate this object with values from db 
    address = new Address(rset);
    contactInfo = new ContactInfo(rset);

  }

  /***
   * To create a new Driver call no args constructor 
   * then call the "set()" methods, and finally "store()"
   */
  public Driver() {  
    address = new Address();
    contactInfo = new ContactInfo();

  }


  /**
   *
   * Persist our properties to the db.  Also call store() on any dependent objects.
   * If no Connection was passed in then we are responisible for beginning the transaction
   * Turn off auto commit, and rollback if we get exception from this or any dependent objects
   *
   */
  public long store() throws Exception { 
    long returnKey = key;
    Connection conn = getConnection();
    try { 
      conn.setAutoCommit(false); 
      log("Driver.store(), Starting transaction");
      returnKey = store(conn);
      conn.commit();  
      log("Driver.store(), No Exceptions Committing");
    }
    catch (Exception exp) {    //Any exception thrown by store causes a rollback. 
      log("Driver.store(), Exception: " + exp.getMessage());
      try {                      
        log("Driver.store(), Failed: RollingBack");
        conn.rollback();
      } catch (SQLException rollbackExp) {             
        log("Driver.store(), Exception: " + rollbackExp.getMessage());
      } 
      throw exp;  //Throw exception back up so that user interface can report error
    }
    finally { 
      try {
        conn.close();
      } catch (SQLException closeExp) {             
        log("Driver.store(), Exception: " + closeExp.getMessage());
      } 
    }
    return returnKey;

  }


  /**
   *
   * Persist our properties to the db.  Also call store() on any dependent objects.
   * If a Connection was passed in then a object higher up is handling the transactions 
   * for us. Throw on any exceptions so that the higher object can catch them and rollback. 
   * This method will do an insert for new Driver and a update for existing ones    
   *
   */
  public long store(Connection conn) throws Exception { 
    long returnKey = key;
      if ( key == 0 ) {   
        addressId = address.store(conn);
        contactInfoId = contactInfo.store(conn);
        returnKey = insertToDB(conn); 
      }
      else { 
        address.store(conn);
        contactInfo.store(conn);
        if ( isDirty ) { updateOnDB(conn); }
      }
    return returnKey;
  }


  /**
   *
   */
  private ResultSet doSelect(Connection conn, Statement stmt) { 
    ResultSet rset = null;

    try { 
      String sqlStmt = "select  \n" + 
      " driver.driver_id as driver_driver_id, \n" +
      " driver.first_name as driver_first_name, \n" +
      " driver.middle_name as driver_middle_name, \n" +
      " driver.last_name as driver_last_name, \n" +
      " driver.gender as driver_gender, \n" +
      " driver.license_points as driver_license_points, \n" +
      " driver.address_id as driver_address_id, \n" +
      " driver.date_of_birth as driver_date_of_birth, \n" +
      " driver.contact_info_id as driver_contact_info_id, \n" +
      " driver.created as driver_created, \n" +
      " driver.updated as driver_updated, \n" +
      " driver_address.address_id as driver_address_address_id, \n" +
      " driver_address.address_line1 as driver_address_address_line1, \n" +
      " driver_address.address_line2 as driver_address_address_line2, \n" +
      " driver_address.city as driver_address_city, \n" +
      " driver_address.state as driver_address_state, \n" +
      " driver_address.zip as driver_address_zip, \n" +
      " driver_address.date_moved_in as driver_address_date_moved_in, \n" +
      " driver_address.created as driver_address_created, \n" +
      " driver_address.updated as driver_address_updated, \n" +
      " contact_info.contact_info_id as contact_info_contact_info_id, \n" +
      " contact_info.home_phone as contact_info_home_phone, \n" +
      " contact_info.work_phone as contact_info_work_phone, \n" +
      " contact_info.email_address as contact_info_email_address, \n" +
      " contact_info.created as contact_info_created, \n" +
      " contact_info.updated as contact_info_updated  \n" + 
      " from driver driver, \n" + 
      " driver_address driver_address, \n" + 
      " contact_info contact_info    \n" +  
      " where driver.driver_id = " + key 
      + " and driver.address_id = driver_address.address_id    \n"
      + " and driver.contact_info_id = contact_info.contact_info_id    \n";
      log("sqlStmt=" + sqlStmt);
      rset = stmt.executeQuery(sqlStmt);
      rset.next();
      applyResultSet(rset);
    }
    catch (Exception exp) {
      log(".doSelect(), SQLException: " + exp.getMessage());
    }

   return rset;

  }


  /**
   *
   */
  private void applyResultSet(ResultSet rset) { 
    TimeZone timezone = TimeZone.getDefault();
    GregorianCalendar gc = null;
    java.sql.Clob textClob = null;
    InputStream in = null;
    int b = 0;
    StringBuffer stringBuffer = null;

    try { 
      key = rset.getLong("driver_driver_id");
      firstName = rset.getString("driver_first_name"); 
      middleName = rset.getString("driver_middle_name"); 
      lastName = rset.getString("driver_last_name"); 
      gender = rset.getString("driver_gender"); 
      licensePoints = rset.getFloat("driver_license_points"); 
      addressId = rset.getLong("driver_address_id"); 
      if ( rset.getTimestamp("driver_date_of_birth") != null) { 
        gc  = new GregorianCalendar(timezone);
        gc.setTime(new java.util.Date( rset.getTimestamp("driver_date_of_birth").getTime())); 
        dateOfBirth = gc;
      }
      contactInfoId = rset.getLong("driver_contact_info_id"); 
      if ( rset.getTimestamp("driver_created") != null) { 
        gc  = new GregorianCalendar(timezone);
        gc.setTime(new java.util.Date( rset.getTimestamp("driver_created").getTime())); 
        created = gc;
      }
      if ( rset.getTimestamp("driver_updated") != null) { 
        gc  = new GregorianCalendar(timezone);
        gc.setTime(new java.util.Date( rset.getTimestamp("driver_updated").getTime())); 
        updated = gc;
      }

      isDirty = false;
    }
    catch (Exception exp) {
      log("Driver.applyResultSet(): SQLException:" + exp.getMessage());
    }

  }


  /**
   *
   */
  private long insertToDB(Connection conn) throws Exception { 
    long newKey = 0;
    String sqlStmt = "";
    ByteArrayInputStream bs = null;
    InputStream in = null;
    Statement stmt = null;
    ResultSet rset = null;

    sqlStmt = "select driver_seq.nextval from dual \n";
    stmt = conn.createStatement();
    rset = stmt.executeQuery(sqlStmt);
    rset.next();
    newKey = rset.getLong(1);
    log("newKey=" + newKey);

    sqlStmt = "insert into driver  " + 
    " ( " + 
    " driver_id, \n" +                      
    " first_name,     \n" +
    " middle_name,     \n" +
    " last_name,     \n" +
    " gender,     \n" +
    " license_points,     \n" +
    " address_id,     \n" +
    " date_of_birth,     \n" +
    " contact_info_id,     \n" +
    " created,     \n" +
    " updated    \n" +
    "   ) " + 
    " values ( ?,  ?, ?, ?, ?, ?, ?, ?, ?, sysdate, sysdate )";
    log("Sql stmt=" + sqlStmt);
    PreparedStatement pStmt = conn.prepareStatement(sqlStmt);
    pStmt.setLong(1,newKey);
    pStmt.setString(2,firstName);
    pStmt.setString(3,middleName);
    pStmt.setString(4,lastName);
    pStmt.setString(5,gender);
    pStmt.setFloat(6,licensePoints);
    pStmt.setLong(7,addressId);
    if ( dateOfBirth != null) { 
      pStmt.setTimestamp(8, new java.sql.Timestamp(dateOfBirth.getTime().getTime() ) );
    }
    else { 
      pStmt.setNull(8, java.sql.Types.DATE);
    }
    pStmt.setLong(9,contactInfoId);

    int rowsInserted = pStmt.executeUpdate();
    log("      rows inserted =" + rowsInserted);
    if ( rowsInserted != 1) { 
      log("Error:  .insertToDB failed no rows inserted");
    }
    pStmt.close();

    isDirty = false;

    return newKey;

  }


  /**
   *
   */
  private void updateOnDB(Connection conn) throws Exception { 

    ByteArrayInputStream bs = null;
    InputStream in = null;
    String sqlStmt = "update driver  " + 
    " set " + 
    " first_name = ?,     \n" +
    " middle_name = ?,     \n" +
    " last_name = ?,     \n" +
    " gender = ?,     \n" +
    " license_points = ?,     \n" +
    " address_id = ?,     \n" +
    " date_of_birth = ?,     \n" +
    " contact_info_id = ?,     \n" +
    " updated = sysdate     \n" +
    " where driver_id = '" + key + "'";

    log("Sql stmt=" + sqlStmt);
    PreparedStatement pStmt = conn.prepareStatement(sqlStmt);
    pStmt.setString(1,firstName);
    pStmt.setString(2,middleName);
    pStmt.setString(3,lastName);
    pStmt.setString(4,gender);
    pStmt.setFloat(5,licensePoints);
    pStmt.setLong(6,addressId);
    if ( dateOfBirth != null) { 
      pStmt.setTimestamp(7, new java.sql.Timestamp(dateOfBirth.getTime().getTime() ) );
    }
    else { 
      pStmt.setNull(7, java.sql.Types.DATE);
    }
    pStmt.setLong(8,contactInfoId);

    int rowsUpdated = pStmt.executeUpdate();
    if ( rowsUpdated != 1) { 
      log("Error:  .insertToDB failed no rows inserted");
    }
    pStmt.close();
    isDirty = false;

  }


  /**
   *
   * Delete an Driver 
   * Also call delete() on any dependent objects.
   * If no Connection was passed in then we are responisible for beginning the transaction
   * Turn off auto commit, and rollback if we get exception from this or any dependent objects
   *
   */
  public void delete() throws Exception { 
    long returnKey = key;
    Connection conn = getConnection();
    try { 
      conn.setAutoCommit(false);  
      delete(conn);
      conn.commit();              
    }
    catch (Exception exp) {    //Any exception thrown by store causes a rollback. 
      log("Driver.delete(), Exception: " + exp.getMessage());
      try {
        conn.rollback(); 
      } catch (SQLException rollbackExp) {             
        log(".delete(), Exception: " + rollbackExp.getMessage());
      } 
      throw exp;  //Throw exception back up so that user interface can report error
    }
    finally { 
      try {
        conn.close();
      } catch (SQLException closeExp) {             
        log("Driver.delete(), Exception: " + closeExp.getMessage());
      } 
    }
  }


  /**
   *
   *
   */
  public void delete(Connection conn) throws Exception {     address.delete(conn);    contactInfo.delete(conn);
    Iterator iteratorOfCars = listCars();
    while (iteratorOfCars.hasNext() ) { 
      Car object = (Car)iteratorOfCars.next();
      object.delete();
    } 
    String sqlStmt = "delete from driver " + 
    "where driver_id = ?";
    PreparedStatement pStmt = conn.prepareStatement(sqlStmt);
    pStmt.setLong(1,key);
    int rowsDeleted = pStmt.executeUpdate(); 
    log("   Deleted row from driver where primary key was " + key);
    if ( rowsDeleted != 1) { 
      log("Error:  Driver.delete failed no rows deleted");
     }
  }


  public long getKey() { return key; } 

  public String getFirstName() { 
    return firstName;
  }


  public String getMiddleName() { 
    return middleName;
  }


  public String getLastName() { 
    return lastName;
  }


  public String getGender() { 
    return gender;
  }


  public float getLicensePoints() { 
    return licensePoints;
  }


  public long getAddressId() { 
    return addressId;
  }


  public GregorianCalendar getDateOfBirth() { 
    return dateOfBirth;
  }


  public long getContactInfoId() { 
    return contactInfoId;
  }


  public GregorianCalendar getCreated() { 
    return created;
  }


  public GregorianCalendar getUpdated() { 
    return updated;
  }


  public void setFirstName(String in) { 
    firstName = in;
    isDirty = true;
  }


  public void setMiddleName(String in) { 
    middleName = in;
    isDirty = true;
  }


  public void setLastName(String in) { 
    lastName = in;
    isDirty = true;
  }


  public void setGender(String in) { 
    gender = in;
    isDirty = true;
  }


  public void setLicensePoints(float in) { 
    licensePoints = in;
    isDirty = true;
  }


  public void setAddressId(long in) { 
    addressId = in;
    isDirty = true;
  }


  public void setDateOfBirth(GregorianCalendar in) { 
    dateOfBirth = in;
    isDirty = true;
  }


  public void setContactInfoId(long in) { 
    contactInfoId = in;
    isDirty = true;
  }


  public void setCreated(GregorianCalendar in) { 
    created = in;
    isDirty = true;
  }


  public void setUpdated(GregorianCalendar in) { 
    updated = in;
    isDirty = true;
  }



  //  Getter Methods for properties in Address

  public String getAddressLine1() { 
    return address.getAddressLine1();
  }

  public String getAddressLine2() { 
    return address.getAddressLine2();
  }

  public String getCity() { 
    return address.getCity();
  }

  public String getState() { 
    return address.getState();
  }

  public String getZip() { 
    return address.getZip();
  }

  //  Getter Methods for properties in ContactInfo

  public String getHomePhone() { 
    return contactInfo.getHomePhone();
  }

  public String getWorkPhone() { 
    return contactInfo.getWorkPhone();
  }

  public String getEmailAddress() { 
    return contactInfo.getEmailAddress();
  }

  //  Setter Methods for properties in Address

  public void setAddressLine1(String in) { 
    address.setAddressLine1(in);
  }

  public void setAddressLine2(String in) { 
    address.setAddressLine2(in);
  }

  public void setCity(String in) { 
    address.setCity(in);
  }

  public void setState(String in) { 
    address.setState(in);
  }

  public void setZip(String in) { 
    address.setZip(in);
  }

  //  Setter Methods for properties in ContactInfo

  public void setHomePhone(String in) { 
    contactInfo.setHomePhone(in);
  }

  public void setWorkPhone(String in) { 
    contactInfo.setWorkPhone(in);
  }

  public void setEmailAddress(String in) { 
    contactInfo.setEmailAddress(in);
  }



  /**
   *   Get all Cars for this Driver
   */
  private void loadCars() { 
    Connection conn = null;
    Statement stmt = null;
    ResultSet rset = null;
    arrayListOfCars = new  ArrayList();
    String sqlStmt = "select  \n" + 
      " car.car_id  as car_car_id ,   \n" +  
      " car.driver_id as car_driver_id,   \n" +  
      " car.make as car_make,   \n" +  
      " car.model as car_model,   \n" +  
      " car.year as car_year,   \n" +  
      " car.mileage as car_mileage,   \n" +  
      " car.created as car_created,   \n" +  
      " car.updated as car_updated  \n" +  
      " from driver driver, " + 
      " car car \n" + 
      " where driver.driver_id = car.driver_id \n" + 
      " and car.driver_id = " + key;
    try {
      conn = getConnection();
      stmt = conn.createStatement();
      log("sqlStmt=" + sqlStmt);
      rset = stmt.executeQuery(sqlStmt);
      while ( rset.next() ) { 
        Car object = new Car(rset);
        arrayListOfCars.add(object);
      }
    }
    catch (Exception exp) {
      log("getCars(), SQLException: " + exp.getMessage());
    }
    finally { 
      try {
        rset.close();
        stmt.close();
        conn.close();
      } catch (SQLException exp) {             
        log("getCars()- closing rset,stmt,conn:  Exception: " + exp.getMessage());
      } 
    }

  }



  /**
   *   Get Iterator of Cars 
   */
  public Iterator listCars() { 
    return arrayListOfCars.iterator();
  }


  /**
   *   Add a Car
   *   To add a Car instantiate one,
   *   call it's set() methods, but dont store().  Call this method passing in the 
   *   Car. This method will link it with this 
   *   object/table and store() it.
   */
  public long addCar(Car in)  throws Exception { 
    long newKey = 0;
    in.setDriverId(key);
    newKey = in.store(); 
    loadCars();   
    return newKey;
  }

  /**
   *   Remove a Car
   *   To delete a Car call this method.
   *   This will delete this row compeletly no just de-link it from this row.
   */
  public void removeCar(Car in) throws Exception { 
    in.delete();
    loadCars();
   }

  /**
   *  Write to Log
   */
  private void log(String messageIn) { 
    System.out.println(messageIn);
  }


  /**
   *  Get JDBC Connection to the DataBase 
   *  Note: this should be returning connections from a pool.
   */
  private Connection getConnection() { 

    Connection conn = null;

    String dbServer   =  "@myserver.mycompnay.com";
    String dbPort     =  "1526";
    String dbSid      =  "mysid";
    String dbUserId   =  "username";
    String dbPassword =  "password";
    try {
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
      String connectString = "jdbc:oracle:thin:" + dbServer + ":" + dbPort + ":" + dbSid;
      conn = DriverManager.getConnection (connectString,  dbUserId, dbPassword);
    }
    catch (SQLException exp) {
      log("Exception: " + exp.getMessage() );
    }
    return conn;
  }


  /**
   *  A main method for testing
   */
  public static void main(String args[] ) { 
    Driver object = new Driver(1);
    System.out.println("firstName = " + object.firstName );
    System.out.println("middleName = " + object.middleName );
    System.out.println("lastName = " + object.lastName );
    System.out.println("gender = " + object.gender );
    System.out.println("licensePoints = " + object.licensePoints );
    System.out.println("addressId = " + object.addressId );
    System.out.println("dateOfBirth = " + object.dateOfBirth );
    System.out.println("contactInfoId = " + object.contactInfoId );
    System.out.println("created = " + object.created );
    System.out.println("updated = " + object.updated );

  }


}