package dk.hansen;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import javax.sql.DataSource;

import org.apache.log4j.Logger;

public class DatabaseVhsManager implements VhsManagerIF {

   private DataSource dataSource;

   private static Logger logger = 
     Logger.getLogger(DatabaseVhsManager.class.getName());

   // Contains en Exception when an error has occured   
   private Exception error;
   // Contains an error message
   private String message;

   public void setDataSource(DataSource dataSource) {
      this.dataSource = dataSource;
   }

   public void createVhs(String id, int year, String title, int tapeLength) throws DAOException {
      if (getVhs(id) != null) throw new DAOException("Id " + id + " is already used");
      Connection conn = null;
      PreparedStatement pstmt = null;
      error = null;
      
      try {
         conn = dataSource.getConnection();
         logger.info("DatabaseVhsManager.createVhs, id=" + id);
         // Prepare a statement to insert a record
         String sql = "INSERT INTO vhs (id, year, title, tapeLength) VALUES(?, ?, ?, ?)";
         pstmt = conn.prepareStatement(sql);
         pstmt.setString(1, id);
         pstmt.setInt(2, year);
         pstmt.setString(3, title);
         pstmt.setInt(4, tapeLength);
         pstmt.executeUpdate();
      } catch (SQLException e) {
         error = e;
         message = "Create failed";
      }

      closePrep(pstmt);
      closeConnection(conn);
      checkOK();               
   }

   public Vhs getVhs(String id) throws DAOException {
      Connection conn = null;
      PreparedStatement pstmt = null;
      ResultSet result = null;
      error = null;
      Vhs vhs = null;
      
      try {
         conn = dataSource.getConnection();
         logger.info("DatabaseVhsManager.getVhs, id=" + id);
         // Prepare a statement to insert a record
         String sql = "SELECT * FROM vhs WHERE id=?";
         pstmt = conn.prepareStatement(sql);
         pstmt.setString(1, id);
         result = pstmt.executeQuery();
         if (result.next()) {
            String newId = result.getString("id");
            int newYear = result.getInt("year");
            String newTitle = result.getString("title");
            int newTapeLength = result.getInt("tapeLength");
            vhs = new Vhs(newId, newYear, newTitle, newTapeLength);
         }
      } catch (SQLException e) {
         error = e;
         message = "Read failed";
      }
   
      closeResult(result);
      closePrep(pstmt);
      closeConnection(conn);
      checkOK();       
      
      return vhs;        
   }

   public void updateVhs(String id, int year, String title, int tapeLength) throws DAOException {
      if (getVhs(id) == null) throw new DAOException("Id " + id + " was not found");
      Connection conn = null;
      PreparedStatement pstmt = null;
      error = null;
      
      try {
         conn = dataSource.getConnection();
         logger.info("DatabaseVhsManager.updateVhs, id=" + id);
         // Prepare a statement to update a record
         String sql = "UPDATE vhs SET year=?, title=?, tapeLength=? WHERE id=?";
         pstmt = conn.prepareStatement(sql);
         pstmt.setInt(1, year);
         pstmt.setString(2, title);
         pstmt.setInt(3, tapeLength);
         pstmt.setString(4, id);
         pstmt.executeUpdate();
      } catch (SQLException e) {
         error = e;
         message = "Update failed";
      }

      closePrep(pstmt);
      closeConnection(conn);
      checkOK();               
   }

   public void deleteVhs(String id) throws DAOException {
      if (getVhs(id) == null) throw new DAOException("Id " + id + " was not found");
      Connection conn = null;
      PreparedStatement pstmt = null;
      error = null;
      
      try {
         conn = dataSource.getConnection();
         logger.info("DatabaseVhsManager.deleteVhs, id=" + id);
         // Prepare a statement to delete a record
         String sql = "DELETE FROM vhs WHERE id=?";
         pstmt = conn.prepareStatement(sql);
         pstmt.setString(1, id);
         pstmt.executeUpdate();
      } catch (SQLException e) {
         error = e;
         message = "Delete failed";
      }

      closePrep(pstmt);
      closeConnection(conn);
      checkOK();               
      
   }

   public Collection getAll() throws DAOException {
      Connection conn = null;
      PreparedStatement pstmt = null;
      ResultSet rs = null;
      error = null;
      Collection c = new ArrayList();
      
      try {
         conn = dataSource.getConnection();
         logger.info("DatabaseVhsManager.getAll");
         // Prepare a statement to delete a record
         String sql = "SELECT * FROM vhs";
         pstmt = conn.prepareStatement(sql);
         rs = pstmt.executeQuery(sql);
         while (rs.next()) {
            String id = rs.getString("id");
            int year = rs.getInt("year");
            String title = rs.getString("title");
            int tapeLength = rs.getInt("tapeLength");
            Vhs vhs = new Vhs(id, year, title, tapeLength);
            c.add(vhs);
         } 
      } catch (SQLException e) {
         error = e;
         message = "Getall failed";
      }

      closeResult(rs);
      closePrep(pstmt);
      closeConnection(conn);
      checkOK();               
      return c;
      
   }

   public Collection findVhsTitle(String findTitle) throws DAOException {
      Connection conn = null;
      PreparedStatement pstmt = null;
      ResultSet rs = null;
      error = null;
      Collection c = new ArrayList();
      
      try {
         conn = dataSource.getConnection();
         logger.info("DatabaseVhsManager.findVhsTitle, title=" + findTitle);
         // Prepare a statement to delete a record
         String sql = "SELECT * FROM vhs";
         pstmt = conn.prepareStatement(sql);
         rs = pstmt.executeQuery(sql);
         while (rs.next()) {
            String id = rs.getString("id");
            int year = rs.getInt("year");
            String title = rs.getString("title");
            int tapeLength = rs.getInt("tapeLength");
            Vhs vhs = new Vhs(id, year, title, tapeLength);
            if (vhs.getTitle().toUpperCase().indexOf(findTitle.toUpperCase()) > -1) {
               c.add(vhs);
            }
         } 
      } catch (SQLException e) {
         error = e;
         message = "Find failed";
      }

      closeResult(rs);
      closePrep(pstmt);
      closeConnection(conn);
      checkOK();               
      return c;
      
   }
   
   public Collection findVhsYear(int findYear) throws DAOException {
      Connection conn = null;
      PreparedStatement pstmt = null;
      ResultSet rs = null;
      error = null;
      Collection c = new ArrayList();
      
      try {
         conn = dataSource.getConnection();
         logger.info("DatabaseVhsManager.findVhsYear, year=" + findYear);
         // Prepare a statement to delete a record
         String sql = "SELECT * FROM vhs";
         pstmt = conn.prepareStatement(sql);
         rs = pstmt.executeQuery(sql);
         while (rs.next()) {
            String id = rs.getString("id");
            int year = rs.getInt("year");
            String title = rs.getString("title");
            int tapeLength = rs.getInt("tapeLength");
            Vhs vhs = new Vhs(id, year, title, tapeLength);
            if (vhs.getYear() == year) {
               c.add(vhs);
            }
         } 
      } catch (SQLException e) {
         error = e;
         message = "Find failed";
      }

      closeResult(rs);
      closePrep(pstmt);
      closeConnection(conn);
      checkOK();               
      return c;
      
   }
   
   private void checkOK() throws DAOException {
      if (error != null) {
         throw new DAOException(message, error);
      }
   }
   
   private void closeConnection(Connection connection) {
      if (connection != null) {
         try {
            connection.close();
         } catch (SQLException e) {
            if (error == null) {
               error = e;
               message = "Close conn failed";
            }
         }
      }
   }

   private void closePrep(PreparedStatement prep) {
      if (prep != null) {
         try {
            prep.close();
         } catch (SQLException e) {
            if (error == null) {
               error = e;
               message = "Close prep failed";
            }
         }
      }
   }
   
   private void closeResult(ResultSet rs) {
      if (rs != null) {
         try {
            rs.close();
         } catch (SQLException e) {
            if (error == null) {
               error = e;
               message = "Close result failed";
            }
         }
      }
   }

}