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

/**
 *  This class provides a data access mechanism for the video database.
 *  Methods are available to get a list of video products.   <p>
 *
 *
 *  Usage Example:
 *  <pre>
 *
 *    // create the data accessor
 *    VideoDB myDataAccessor = VideoDB.getInstance();
 *
 *
 *  </pre>
 *
 *  @author 570 Development Team
 */
public class VideoDB {

	protected ConnectionPool myConnPool;
    protected String tabela;
	/**
	 *  Constructs the data accessor
	 */
	public VideoDB() 
	  throws SQLException, ClassNotFoundException {

		
		try
		{
			myConnPool = ConnectionPool.getInstance();			
		}
		catch (SQLException exc) {
			exc.printStackTrace();
		}
		catch (ClassNotFoundException exc) {
			exc.printStackTrace();
		}

		

	}


	/**
	 *  Get a list of video categories
	 */
	public  ArrayList getVideoCategories() throws SQLException {

		ArrayList theList = new ArrayList();

		Connection tempConn = null;
	    String categoria;
		try {
			tempConn = myConnPool.getConnection();

			Statement myStmt = tempConn.createStatement();
			ResultSet myRs = myStmt.executeQuery("SELECT name FROM Video_Categories");

			while (myRs.next()) {
			    categoria = myRs.getString("name");
				theList.add(categoria);
			}

			myRs.close();
			myStmt.close();
		}
		catch (ConnectionUnavailableException exc) {
			log(exc);
			throw new SQLException(exc.getMessage());
		}
		finally {
			if (tempConn != null) {
				myConnPool.releaseConnection(tempConn);
			}
		}

		return theList;
	}

	/**
	 *  Get a list of video products from the database for the given category
	 *
	 *  @return a list of VideoRecording objects
	 *  @exception SQLException thrown for SQL errors
	 */
	public ArrayList getVideoRecordings(String category) throws SQLException {


		ArrayList theList = new ArrayList();

		Connection tempConn = null;

		try {
			tempConn = myConnPool.getConnection();

			Statement myStmt = tempConn.createStatement();
			ResultSet myRs = myStmt.executeQuery("SELECT recording_id, director, title, category, image_name, duration, rating, year_released, price, stock_count FROM Video_Recordings WHERE category='" + category + "'");

			while (myRs.next()) {
				theList.add(new VideoRecording(myRs));
			}

			myRs.close();
			myStmt.close();
		}
		catch (ConnectionUnavailableException exc) {
			log(exc);
			throw new SQLException(exc.getMessage());
		}
		finally {
			if (tempConn != null) {
				myConnPool.releaseConnection(tempConn);
			}
		}

		return theList;
	}

     /**
	 *  Get a list of video products from the database for the given Varias category
	 *
	 *  @return a list of VideoRecording objects
	 *  @exception SQLException thrown for SQL errors
	 */
	public ArrayList getVideoSearch(String title) throws SQLException {


		ArrayList theList = new ArrayList();

		Connection tempConn = null;

		try {
			tempConn = myConnPool.getConnection();
			System.out.println("Query = " + title);
			Statement myStmt = tempConn.createStatement();
			String query = "SELECT recording_id, director, title, category, image_name, duration, rating, year_released, price, stock_count FROM Video_Recordings WHERE title Like '%" + title + "%'";
            //String query = "SELECT recording_id, director, title, category, image_name, duration, rating, year_released, price, stock_count FROM Video_Recordings WHERE title = 'Apocalypse Now'";
            ResultSet myRs = myStmt.executeQuery(query);
       		System.out.println(query);		

			while (myRs.next()) {
				theList.add(new VideoRecording(myRs));
             	System.out.println("Query = " + title + "Achou");		
            }

			myRs.close();
			myStmt.close();
		}
		catch (ConnectionUnavailableException exc) {
			log(exc);
			throw new SQLException(exc.getMessage());

                        			
		}
		finally {
			if (tempConn != null) {
				myConnPool.releaseConnection(tempConn);
			}
		}

		return theList;
	}

	/**
	 *  Returns an Video product based on the id
	 *
	 */

	public VideoRecording getVideoRecording(int recordingId)
	  throws RecordingNotFoundException, SQLException {

		VideoRecording tempRecording = null;

		Connection tempConn = null;

		try {
			tempConn = myConnPool.getConnection();
			Statement myStmt = tempConn.createStatement();
			ResultSet myRs;

			System.out.println("reccording id = " + recordingId);
			myRs = myStmt.executeQuery("SELECT recording_id, director, title, category, image_name, duration, rating, year_released, price, stock_count FROM Video_Recordings WHERE recording_id=" + recordingId);

			while (myRs.next()) {
				tempRecording = new VideoRecording(myRs);
			}

			System.out.println("Recording = " + tempRecording);

			myRs.close();
			myStmt.close();
		}
		catch (ConnectionUnavailableException exc) {
			log(exc);
			throw new SQLException(exc.getMessage());
		}
		finally {
			log("finally");
			if (tempConn != null) {
				myConnPool.releaseConnection(tempConn);
			}
		}

		return tempRecording;
	}


    public Vector executeLogin(String login, String password)
    			   		   throws SQLException {

		Connection tempConn = null;
		Vector vl_retorno = new Vector();

	    try {
			tempConn = myConnPool.getConnection();
			Statement myStmt = tempConn.createStatement();
			ResultSet myRs = myStmt.executeQuery("SELECT login, Id_Login, password, status FROM Login WHERE login like '"+login+"'");			
			if (myRs == null){
		       System.out.println("o rs não pegou nada!!!");
			} else {
   			  while (myRs.next()) {
			     vl_retorno.addElement(myRs.getString("login"));
			     vl_retorno.addElement(myRs.getString("Id_Login"));
			     vl_retorno.addElement(myRs.getString("password"));
			     vl_retorno.addElement(myRs.getString("status"));
			  }
	        }
			myRs.close();
			myStmt.close();
		    
	    } catch(SQLException e) {
		    throw new SQLException("Erro no executeLogin " + login + "-" + password + " : " + e.getMessage());
	    }
		finally {
			if (tempConn != null) {
				myConnPool.releaseConnection(tempConn);
			}
		}
		return vl_retorno;
    } 


	/**
	 *  Convenience method for logging messages to standard out.
	 */
	protected void log(Object msg) {
		System.out.println("VideoDB: " + msg);
	}

}