package historyPlaces;

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

/**
 *  filename: engine.java
 * 	Created By: Ryan Prins
 * 	Last Modified: May 31, 2004
 * 
 * 	This file is the main java class that runs the project. It
 * 	contins the neeeded methods to generate the output for
 * 	the specific deliverables required for History Places. 
 */
public class engine{

	/** 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();
	
	/** A linked list that contians all of the places */
	private LinkedList allPlaces = new LinkedList();
	
	/** An ArrayList that contains all of the IDs needed to query for the timeline */
	private ArrayList photoID = new ArrayList();
	
	/** A LinkedList that contains all of the photos */
	private LinkedList allPhotos = new LinkedList();
	
	/** 
	 *	Initializes the connection to the database 
	 */
	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();
		}
	}
	
	/**
	 * Gets the connection to the database and returns it to the user
	 * @return the database connection
	 */
	private Connection getConn() {
		  if (conn == null) {
			  init();
		  }
		  return conn; 
	}
	
	/**
	 * Will return to the user all of the Error information
	 * @return database error information
	 */
	public String getErrorInfo() {
		return "<P>" + DataBaseParam.getConnInfo() + debugString + "</P>";
	}
	 
	/**
	 * This function retuns various pieces meta data about the connection 
	 * @return a string with various 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;
	}
	
	/**
	 * Returns the hierarchy to the user in a LinkedList.
	 * @return all places in their hierarchy
	 * @param q the place id to get the hierarchy from
	 */
	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
	 * @return all of the places with their placeid and name
	 */
	public String printParents() {
				/* 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"; 
				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 pid			= r.getInt("pid");		
					String 	pname 	= r.getString("pname");
		
					output			+= "<option value=\"" + pid + "\">" + pname + "(" + pid + ")</optoin>\n";
				}
	
			/*Not necessary here but good habit */
			r.close();
	
			}catch(Exception e) {
				t = e.toString();
				e.printStackTrace();
			}

				return output;

		}
		
	/**
	 * Searches for the children of a particular parent
	 * @return all of 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
		 * @param q the place id for the place which you want data
	     */
		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 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");	
	
					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
		 * @param q the photo id of the photo that you want information on
		 * @return the information about the specific 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;

	}
	
	/**
		 * Returns a single photo
		 * @param q the photo id of the photo that you want information on
		 * @return the information about the specific photo
		 */
		public LinkedList getAllPhoto(int id) {
			/* 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 WHERE placeid = " + id;
				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");
					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);
					allPhotos.add(myPhoto);
				}
		
			/*Not necessary here but good habit */
			r.close();

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

	}
	
	/**
	 * Gives the user a list of photoids
	 * @return all of the photoids
	 */
	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;

		}
		
	/**
	 * Adds a place into the database.
	 * @param parent the parent id of the place
	 * @param name the name of the place
	 * @param comment a comment about the place
	 */
	public void addPlace(int parent, String name, String comment) {
			/* Integer to build */
			String t 	= "";

		try{
			Connection conn = getConn();

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

			/* Build up query */
			String p1 = "INSERT INTO place (parent, pname, comment)";
			String p2 = "VALUES (" + parent + ", '" + name + "', '" + comment + "')";
			String sql = p1 + p2;

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

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

	/**
	 * Returns all of the places to the user
	 * @return all of the places to the user
	 */
	public LinkedList getPlaces() {
			/* 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 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
				*/
			
				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);
				allPlaces.add(myPlace);
			}
		
		/*Not necessary here but good habit */
		r.close();
		
		}catch(Exception e) {
			t = e.toString();
			e.printStackTrace();
		}
	
		return allPlaces;		
	}
	
	/**
	 * Builds a navagation list
	 * @param l the list of places to build it off of
	 * @return the string of the order of places
	 */
	public String buildNav(LinkedList l){
		String nav = "";
		
		for(int i = l.size() - 1; i >= 0; i--){
			place 	curPlace 	= (place)l.get(i);
			
			nav += "<a href=\"./hierarchy.jsp?pid=" + curPlace.getPlaceID() + "\">" + curPlace.getPlaceName() + "</a> > ";
		}
		
		return nav;
	}
	
	/**
	 * Searches for the children of a particular parent
	 * @return all of the places with their placeid and name
	 */
	public LinkedList letteredList(String letter) {
				/* Integer to build */
				String t 		= "";
				String output	= "";
				LinkedList list	= new LinkedList();
	
			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 where pname LIKE '" + letter + "%'OR pname LIKE '" + letter + "%';"; 
				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 pid			= r.getInt("pid");
				int 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);
				list.add(myPlace);
			}
	
			/*Not necessary here but good habit */
			r.close();
	
			}catch(Exception e) {
				t = e.toString();
				e.printStackTrace();
			}

				return list;

		}
		
	/**
	 * Searches for the children of a particular parent
	 * @return all of the places with their placeid and name
	 */
	public int numberOfChildren(int parent) {
				/* Integer to build */
				String t 		= "";
				String output	= "";
				int count 		= -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 COUNT(*) AS count FROM place WHERE parent = " + parent; 
				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
				*/
				if(r.next()) {
				/* 
				Query generated a result -- now extract data by Name & Index
				*/
		
				count	= r.getInt("count");
			}

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

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

				return count;

		}
}