/* filename: StateZipInfo.java */
package historyPlaces;

import historyPlaces.*;
import java.sql.*;
import java.util.*;

public class hp{

 /* Holds the connection to database -- created by init() */
 /* By default, transactions are auto-committed */
 private Connection conn = null;
 
 /* A variable for holding Exception error messages, etc. */
 private String debugString = "nothing";
 
 /* A linked list that contains all the children of a particular parent */
 private LinkedList children = new LinkedList();
 
 /* A linked list that contains the crrent hierarchy */
 private LinkedList hierarchy = new LinkedList();
 
 /* An ArrayList that contains all of the IDs needed to query for the timeline */
 private ArrayList photoID = new ArrayList();
 
 /*A LinkedList of Photos in database*/
 private LinkedList photoList = new LinkedList();
 
 /*A LinkedList of Places in database*/
 private LinkedList placeList = new LinkedList();
  
 /* Initializes the connection */
 private void init() {
  try {
 
  /* This statement implicitly loads the driver */
  Class.forName(DataBaseParam.getDriver());
 
  /* Now, attempt to create a connection */
  conn = DriverManager.getConnection(
  DataBaseParam.getDbURL(),
  DataBaseParam.getUserID(),
  DataBaseParam.getUserPassword());
 
  if (conn == null) 
  throw new Exception("Could not connect to " + DataBaseParam.getConnInfo());
  }
  catch(Exception e) {
    debugString = e.toString();
    e.printStackTrace();
  }
 }
 
 private Connection getConn() {
    if (conn == null) {
     init();
    }
    return conn; 
 }
 
 public String getErrorInfo() {
  return "
" + DataBaseParam.getConnInfo() + debugString + "
";
 }
  
 /* This function retuns various pieces meta data about the connection */
 public String getConnectionInfo() {
  String t = "";
  try{
  /* 
  Class connection presents a 'connection' to a database. Using
  connections, you control transactions, db resources, authentication, 
  etc. */
  Connection conn = getConn();
 
  /* 
  Class DatabaseMetaData can be used to retrieve a lot of interesting
  information about the database 
  */
  DatabaseMetaData m = conn.getMetaData();
  
  String url  = m.getURL();
  String user = m.getUserName();
  String p = m.getDatabaseProductName();
  String v = m.getDatabaseProductVersion();
 
  t += "<dbinfo><misc> \n";
  t += "<dburl>"   + url +     "</dburl> \n";
  t += "<dbuser>"   + user + "</dbuser> \n";
  t += "<dbproductname>"   + p +  "</dbproductname> \n";
  t += "<dbproductversion>"   + v +  "</dbproductversion> \n";
  t += "</misc></dbinfo>\n";
 
  /* Determine the names and columns of the tables in the database */
  ResultSet r = m.getTables(null,null,null,null);
  t += "<dbtables>\n";
   while(r.next()) {
   t += "<dbtable>\n";
   t += "<name>";
   String tname = r.getString(3);
   t += tname;
   t += "</name>\n";
 
   /* Determine the column info for the table -- many other attributes */
   t += "<cols>";
   ResultSet rc = m.getColumns(null,null,tname,null);
   while (rc.next()) {
    t += "<col>\n";
    t += "<name>" + rc.getString(4) + "</name>\n";
    t += "<type>" + rc.getString(6) + "</type>\n";
    t += "</col>\n";
   }
   t += "</cols>\n";
   t += "</dbtable>\n";
 
   rc.close();
  }
  t += "</dbtables>\n";
 
  } 
  catch(Exception e) {
  t = e.toString();
   e.printStackTrace();
  }
  return t;
 }
 
 /* Searches for the longitude and latitude by zip code */
 public LinkedList getHierarchy(int q) {
   /* Integer to build */
   String t  = "";
   int pid  = -1;
   int parent = -1;
 
  try{
   Connection conn = getConn();
 
   /* Class statement is used to issue SQL statements to a database */
    Statement s = conn.createStatement();
 
   /* Class ResultSet contains the results from a query */
   ResultSet r; 
 
   /* Build up query */
   String p1 = "SELECT * FROM place ";
   String p2 = "WHERE pid = " + q; 
   String sql = p1 + p2;
 
   /* 
    Execute query -- the results are in r.  If no results are to 
     be returned use int executeUpdate(String q)
   */
   r = s.executeQuery(sql); 
   
 
   /* 
    A 'cursor' is used to move through results -- initially, 
   the cursor is set BEFORE the first row in the result set. 
   While next() returns TRUE then information is available
   */
   if(r.next()) {
    /* 
    Query generated a result -- now extract data by Name & Index
    */
  
    pid    = r.getInt("pid");
    parent    = r.getInt("parent");  
    String  pname  = r.getString("pname");
    String comment = r.getString("comment");
    int photoid  = r.getInt("photoid"); 
  
    place myPlace = new place(pid, parent, pname, comment, photoid);
    hierarchy.add(myPlace);
   }
 
  /*Not necessary here but good habit */
  r.close();
 
  }catch(Exception e) {
   t = e.toString();
   e.printStackTrace();
  }
  if(pid != parent){
   return getHierarchy(parent);
  }else{
   return hierarchy;
  }
 }
 
 /* Searches for the children of a particular parent */
 public LinkedList getChildren(int q) {
    /* Integer to build */
    String t  = "";
    int pid  = -1;
    int parent = -1;
 
   try{
    Connection conn = getConn();
 
    /* Class statement is used to issue SQL statements to a database */
     Statement s = conn.createStatement();
 
    /* Class ResultSet contains the results from a query */
    ResultSet r; 
 
    /* Build up query */
    String p1 = "SELECT * FROM place ";
    String p2 = "WHERE parent = " + q; 
    String sql = p1 + p2;
 
    /* 
     Execute query -- the results are in r.  If no results are to 
      be returned use int executeUpdate(String q)
    */
    r = s.executeQuery(sql); 
   
 
    /* 
     A 'cursor' is used to move through results -- initially, 
    the cursor is set BEFORE the first row in the result set. 
    While next() returns TRUE then information is available
    */
    while(r.next()) {
     /* 
     Query generated a result -- now extract data by Name & Index
     */
  
     pid    = r.getInt("pid");
     parent    = r.getInt("parent");  
     String  pname  = r.getString("pname");
     String comment = r.getString("comment");
     int photoid  = r.getInt("photoid"); 
  
     place myPlace = new place(pid, parent, pname, comment, photoid);
     children.add(myPlace);
    }
 
   /*Not necessary here but good habit */
   r.close();
 
   }catch(Exception e) {
    t = e.toString();
    e.printStackTrace();
   }

    return children;

  }
 
  /* Returns a single place */
  public place getPlace(int q) {
    /* Integer to build */
    String t  = "";
    int pid  = -1;
    int parent = -1;
    place myPlace = new place(-1, -1, "", "", -1);

   try{
    Connection conn = getConn();

    /* Class statement is used to issue SQL statements to a database */
     Statement s = conn.createStatement();

    /* Class ResultSet contains the results from a query */
    ResultSet r; 

    /* Build up query */
    String p1 = "SELECT * FROM place ";
    String p2 = "WHERE placeid = " + q; 
    String sql = p1 + p2;

    /* 
     Execute query -- the results are in r.  If no results are to 
      be returned use int executeUpdate(String q)
    */
    r = s.executeQuery(sql); 
  

    /* 
     A 'cursor' is used to move through results -- initially, 
    the cursor is set BEFORE the first row in the result set. 
    While next() returns TRUE then information is available
    */
    if(r.next()) {
     /* 
     Query generated a result -- now extract data by Name & Index
     */
 
     pid    = r.getInt("pid");
     parent    = r.getInt("parent");  
     String  pname  = r.getString("pname");
     String comment = r.getString("comment");
     int photoid  = r.getInt("photoid"); 
 
     myPlace = new place(pid, parent, pname, comment, photoid);
    }

   /*Not necessary here but good habit */
   r.close();

   }catch(Exception e) {
    t = e.toString();
    e.printStackTrace();
   }
   
   return myPlace;

  }
 
  /* Returns a single photo */
  public photo getPhoto(int q) {
   /* Integer to build */
   String t  = "";
   photo badPhoto = new photo();

   try{
    Connection conn = getConn();

    /* Class statement is used to issue SQL statements to a database */
    Statement s = conn.createStatement();

    /* Class ResultSet contains the results from a query */
    ResultSet r; 

    /* Build up query */
    String p1 = "SELECT * FROM photo ";
    String p2 = "WHERE photoid = " + q; 
    String sql = p1 + p2;
    

    /* 
     Execute query -- the results are in r.  If no results are to 
      be returned use int executeUpdate(String q)
    */
    r = s.executeQuery(sql); 
 

    /* 
     A 'cursor' is used to move through results -- initially, 
    the cursor is set BEFORE the first row in the result set. 
    While next() returns TRUE then information is available
    */
   
    if(r.next()) {
     /* 
     Query generated a result -- now extract data by Name & Index
     */

     int  photoid   = r.getInt("photoid");
     String  name    = r.getString("name");  
     String  url    = r.getString("url");
     String comment   = r.getString("comment");
     java.sql.Date  date  = r.getDate("date");
     String  photographer = r.getString("photographer");
     int  placeid   = r.getInt("placeid");

     String dateString = date.toString();
     System.out.println(photoid);
     System.out.println(name);
     System.out.println(url);
     System.out.println(comment);
     System.out.println(date.toString());
     System.out.println(photographer);
     System.out.println(placeid);

     photo myPhoto = new photo(photoid, name, url, comment, dateString, photographer, placeid);
     return myPhoto;
    }
   
   /*Not necessary here but good habit */
   r.close();

   }catch(Exception e) {
    t = e.toString();
    e.printStackTrace();
   }
  
   return badPhoto;

 }
 
 public ArrayList getPhotoListID() {
    /* Integer to build */
    String t  = "";
    photo badPhoto = new photo();

    try{
     Connection conn = getConn();

     /* Class statement is used to issue SQL statements to a database */
     Statement s = conn.createStatement();

     /* Class ResultSet contains the results from a query */
     ResultSet r; 

     /* Build up query */
     String p1 = "SELECT photoid FROM photo"; 
     String sql = p1;
    

     /* 
      Execute query -- the results are in r.  If no results are to 
       be returned use int executeUpdate(String q)
     */
     r = s.executeQuery(sql); 
 

     /* 
      A 'cursor' is used to move through results -- initially, 
     the cursor is set BEFORE the first row in the result set. 
     While next() returns TRUE then information is available
     */
   
     while(r.next()) {
      /* 
      Query generated a result -- now extract data by Name & Index
      */

      int  photoid  = r.getInt("photoid");
      Integer myInteger = new Integer(photoid);
      
      photoID.add(myInteger);

      
     }
   
     /*Not necessary here but good habit */
     r.close();

    }catch(Exception e) {
     t = e.toString();
     e.printStackTrace();
    }
  
    return photoID;

  }
 
 /* Returns a single photo */
 public LinkedList photoTimeline() {
  LinkedList timeline = new LinkedList();
  
  return timeline;

 }
 
 /* return all photos in photo table*/
 public LinkedList getAllPhoto() {
  String t;
  int photoid;
 
  photo myPhoto = new photo(-1,"","","","","",-1);
  try { 
    Connection conn = getConn();
    Statement s = conn.createStatement();  
    ResultSet r;
    String p1 = "SELECT * FROM photo order by date;";
    String sql = p1;
    
    r = s.executeQuery(sql);
    
    while (r.next()) {
      photoid = r.getInt("photoid");
      String name = r.getString("name");
      String url = r.getString("url");
      String comment = r.getString("comment");
      //java.util.Date date = r.getDate("date");
      String date = r.getString("date");
      String photographer = r.getString("photographer");
      int placeid = r.getInt("placeid");
      
      myPhoto = new photo(photoid, name, url, comment, date,
                          photographer, placeid);
      
      photoList.add(myPhoto);
    }
    r.close();
  }
  catch(Exception e) {
    t = e.toString();
    e.printStackTrace();   
  }
  return photoList;
   
}
 public LinkedList getPeriodSpec(String s1, String s2, String s3) {
  String t;
  int photoid;
 
  photo myPhoto = new photo(-1,"","","","","",-1);
  try { 
    Connection conn = getConn();
    Statement s = conn.createStatement();  
    ResultSet r;
    String p1 = "SELECT * FROM photo f, place p";
    String p2 = " WHERE date >= '" +s1 +"' AND date <= '" +s2+ "' AND p.photoid=f.photoid AND p.pname = '"+s3.trim()+"' " ;
    String p3 = "ORDER BY date DESC;";
    String sql = p1+p2+p3;
    
    r = s.executeQuery(sql);
    
    while (r.next()) {
      photoid = r.getInt("photoid");
      String name = r.getString("name");
      String url = r.getString("url");
      String comment = r.getString("comment");
      //java.util.Date date = r.getDate("date");
      String date = r.getString("date");
      String photographer = r.getString("photographer");
      int placeid = r.getInt("placeid");
      
      myPhoto = new photo(photoid, name, url, comment, date,
                          photographer, placeid);
      
      photoList.add(myPhoto);
    }
    r.close();
  }
  catch(Exception e) {
    t = e.toString();
    e.printStackTrace();   
  }
  return photoList;
 }
 public LinkedList getAllInPeriod(String q1, String q2) {
  String t;
  int photoid;
 
  photo myPhoto = new photo(-1,"","","","","",-1);
  try { 
    Connection conn = getConn();
    Statement s = conn.createStatement();  
    ResultSet r;
    String p1 = "SELECT * FROM photo ";
    String p2 = " WHERE date >= '"+q1 +"' AND date <= '"+q2+"' " ;
    String p3 = "ORDER BY date DESC;";
    String sql = p1+p2+p3;
    
    r = s.executeQuery(sql);
    
    while (r.next()) {
      photoid = r.getInt("photoid");
      String name = r.getString("name");
      String url = r.getString("url");
      String comment = r.getString("comment");
      //java.util.Date date = r.getDate("date");
      String date = r.getString("date");
      String photographer = r.getString("photographer");
      int placeid = r.getInt("placeid");
      
      myPhoto = new photo(photoid, name, url, comment, date,
                          photographer, placeid);
      
      photoList.add(myPhoto);
    }
    r.close();
  }
  catch(Exception e) {
    t = e.toString();
    e.printStackTrace();   
  }
  return photoList;
 
   
 }
 
 
 
 
 public LinkedList getSpecPlaceAllTime(String q) {
  String t;
  int photoid;
 
  photo myPhoto = new photo(-1,"","","","","",-1);
  try { 
    Connection conn = getConn();
    Statement s = conn.createStatement();  
    ResultSet r;
    String p1 = "SELECT * FROM photo f, place p";
    String p2 = " WHERE p.photoid=f.photoid AND p.pname ='"+q.trim()+"'";
    String p3 = "ORDER BY date DESC;";
    String sql = p1+p2+p3;
    
    r = s.executeQuery(sql);
    
    while (r.next()) {
      photoid = r.getInt("photoid");
      String name = r.getString("name");
      String url = r.getString("url");
      String comment = r.getString("comment");
      //java.util.Date date = r.getDate("date");
      String date = r.getString("date");
      String photographer = r.getString("photographer");
      int placeid = r.getInt("placeid");
      
      myPhoto = new photo(photoid, name, url, comment, date,
                          photographer, placeid);
      
      photoList.add(myPhoto);
    }
    r.close();
  }
  catch(Exception e) {
    t = e.toString();
    e.printStackTrace();   
  }
  return photoList;
 }

public int AddPhoto(String photo, String filePath, String comment, String date,
            String photographer, String placeName) {
  String t;
  int add = -1;
  int placeid = -1;
  //boolean add =false;
 
  photo myPhoto = new photo(-1,"","","","","",-1);
  try { 
    Connection conn = getConn();
    Statement s = conn.createStatement();  
    ResultSet r;
    String s1 = "SELECT pid FROM place WHERE pname LIKE '%"+placeName.trim()+"%';";
 
    r = s.executeQuery(s1);
    if (r.next()) {
      placeid = r.getInt("pid");
      System.out.println(placeid);
      String p1 = "INSERT INTO photo (name,url,comment,date,photographer, placeid)";
      String p2 = " VALUES ('" + photo + "', '" + filePath + "','" + comment + "','"+date+"','"+photographer+"'," + placeid + ");";
      String sql = p1+p2;
    
      add = s.executeUpdate(sql);
      //add = true;
    }
    r.close();
   
    
    
   // r.close();
  }
  catch(Exception e) {
    t = e.toString();
    e.printStackTrace(); 
    add = -1;
  }
  return add;
 }
 

/* similar to printParents in Ryan's implementation */
 public String printAllPlace() {
    /* Integer to build */
    String t   = "";
    String output = "";
 
   try{
    Connection conn = getConn();
 
    /* Class statement is used to issue SQL statements to a database */
     Statement s = conn.createStatement();
 
    /* Class ResultSet contains the results from a query */
    ResultSet r; 
 
    /* Build up query */
    String p1 = "SELECT pid, pname FROM place ORDER BY pname"; 
    String sql = p1;
 
    /* 
     Execute query -- the results are in r.  If no results are to 
      be returned use int executeUpdate(String q)
    */
    r = s.executeQuery(sql); 
   
 
    /* 
     A 'cursor' is used to move through results -- initially, 
    the cursor is set BEFORE the first row in the result set. 
    While next() returns TRUE then information is available
    */
    output = "Optional Select";
    while(r.next()) {
     /* 
     Query generated a result -- now extract data by Name & Index
     */
  
     int pid   = r.getInt("pid");  
     String  pname  = r.getString("pname");
  
     output   += "" + pname + "\n";
    }
 
   /*Not necessary here but good habit */
   r.close();
 
   }catch(Exception e) {
    t = e.toString();
    e.printStackTrace();
   }

    return output;

  }
}