package jtm.activity13;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.mysql.jdbc.Statement;

public class TeacherManager {

	protected Connection conn;

	public static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
	// public static final String URL =
	// "jdbc:mysql://localhost:3306/database_activity";
	public static final String URL = "jdbc:mysql://localhost/?autoReconnect=true&useSSL=false&characterEncoding=utf8";

	// Database credentials
	public static final String USER = "root";
	public static final String PASS = "Student007";

	public TeacherManager() throws SQLException {
		// TODO #1 When new TeacherManager is created, create connection to the
		// database server:
		// url =
		// "jdbc:mysql://localhost/?autoReconnect=true&useSSL=false&characterEncoding=utf8"
		// user = "root"
		// pass = "Student007"
		// Hints:
		// 1. Do not pass database name into url, because some statements
		// for tests need to be executed server-wise, not just database-wise.
		// 2. Set AutoCommit to false and use conn.commit() where necessary in
		// other methods
		try {
			Class.forName(JDBC_DRIVER); // Load the driver class.

			conn = DriverManager.getConnection(URL, USER, PASS); // Create
																	// connection
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public static Connection getConnection() throws Exception {
		// TODO Auto-generated method stub
		Class.forName(JDBC_DRIVER);
		Connection conn = DriverManager.getConnection(URL, USER, PASS);
		return conn;
	}

	/**
	 * Returns a Teacher instance represented by the specified ID.
	 *
	 * @param id
	 *            the ID of teacher
	 * @return a Teacher object
	 * @throws SQLException
	 */
	public Teacher findTeacher(int id) throws Exception {
		// TODO #2 Write an sql statement that searches teacher by ID.
		// If teacher is not found return Teacher object with zero or null in
		// its fields!
		// Hint: Because default database is not set in connection,
		// use full notation for table "database_activity.Teacher"

		// String sql = "SELECT * FROM database_activity.Teacher WHERE 'id'LIKE
		// '%" + id;
		Connection conn = null;

		try {
			conn = getConnection();
			String sql = "SELECT * FROM database_activity.Teacher WHERE id LIKE '" + id + "'";

			// String sql = "SELECT * FROM database_activity.Teacher WHERE 'id'
			// = ?";
			PreparedStatement stmt = conn.prepareStatement(sql);
			// stmt.setInt(1, id);
			ResultSet rs = stmt.executeQuery();

			if (rs.next()) {
				Teacher t = new Teacher();
				t.setID(rs.getInt(1));
				t.setFirstName(rs.getString(2));
				t.setLastName(rs.getString(3));
				// conn.commit();
				return t;
			} else {
				Teacher t = new Teacher(0, null, null);
				// conn.commit();
				return t;
			}

		} finally {
			closeConnecion();
		}
	}

	/**
	 * Returns a list of Teacher object that contain the specified first name
	 * and last name. This will return an empty List of no match is found.
	 *
	 * @param firstName
	 *            the first name of teacher.
	 * @param lastName
	 *            the last name of teacher.
	 * @return a list of Teacher object.
	 */
	public List<Teacher> findTeacher(String firstName, String lastName) throws Exception {
		// TODO #3 Write an sql statement that searches teacher by first and
		// last name and returns results as ArrayList<Teacher>.
		// Note that search results of partial match
		// in form ...like '%value%'... should be returned
		// Note, that if nothing is found return empty list!

		Connection conn = null;

		try {
			conn = getConnection();
			String sql = "SELECT * FROM `database_activity`.`Teacher` WHERE `firstname` LIKE '%" + firstName
					+ "%' AND`lastname` LIKE '%" + lastName + "%'";
			PreparedStatement stmt = conn.prepareStatement(sql);
			ResultSet rs = stmt.executeQuery();
			List<Teacher> teachers = new ArrayList<>();
			while (rs.next()) {
				Teacher t = new Teacher();
				t.setID(rs.getInt(1));
				t.setFirstName(rs.getString(2));
				t.setLastName(rs.getString(3));
				teachers.add(t);
			}
			return teachers;
		} finally {
			closeConnecion();
		}
	}

	/**
	 * Insert an new teacher (first name and last name) into the repository.
	 *
	 * @param firstName
	 *            the first name of teacher
	 * @param lastName
	 *            the last name of teacher
	 * @return true if success, else false.
	 * @throws Exception
	 */

	public boolean insertTeacher(String firstName, String lastName) throws Exception {
		// TODO #4 Write an sql statement that inserts teacher in database.
		Connection connection = null;
		try {
			connection = getConnection();
			String sql = "INSERT INTO `database_activity`.`Teacher` (`firstname`, `lastname`) VALUES ('" + firstName
					+ "', '" + lastName + "')";
			PreparedStatement stmt = connection.prepareStatement(sql);

			int status = stmt.executeUpdate();
			if (status > 0) {
				return true;
			}
		} finally {
			if (connection != null) {
				connection.close();
			}
		}
		return false;
	}

	/**
	 * Insert teacher object into database
	 *
	 * @param teacher
	 * @return true on success, false on error (e.g. non-unique id)
	 */
	public boolean insertTeacher(Teacher teacher) {
		// // TODO #5 Write an sql statement that inserts teacher in database.
		Connection connection = null;
		try {
			connection = getConnection();
			String sql = "INSERT INTO `database_activity`.`Teacher` (`id`, `firstname`, `lastname`) VALUES(?, ?, ?)";
			PreparedStatement stmt = connection.prepareStatement(sql);

			stmt.setInt(1, teacher.getID());
			stmt.setString(2, teacher.getFirstName());
			stmt.setString(3, teacher.getLastName());

			int status = stmt.executeUpdate();
			return true;
		} catch (Exception e) {
			return false;
		}

		finally {
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}

	/**
	 * Updates an existing Teacher in the repository with the values represented
	 * by the Teacher object.
	 *
	 * @param teacher
	 *            a Teacher object, which contain information for updating.
	 * @return true if row was updated.
	 */
	public boolean updateTeacher(Teacher teacher) throws Exception {

		// TODO #6 Write an sql statement that updates teacher information.
		Connection connection = null;
		try {
			connection = getConnection();
			String sql = "UPDATE `database_activity`.`Teacher` SET firstname=?, lastname=? WHERE id=?";
			PreparedStatement stmt = connection.prepareStatement(sql);

			stmt.setString(1, teacher.getFirstName());
			stmt.setString(2, teacher.getLastName());
			stmt.setInt(3, teacher.getID());

			int status = stmt.executeUpdate();
			if (status > 0) {
				return true;
			}
		} finally {
			if (connection != null) {
				connection.close();
			}
		}
		return false;
	}

	/**
	 * Delete an existing Teacher in the repository with the values represented
	 * by the ID.
	 *
	 * @param id
	 *            the ID of teacher.
	 * @return true if row was deleted.
	 */
	public boolean deleteTeacher(int id) throws Exception {
		// TODO #7 Write an sql statement that deletes teacher from database.
		Connection connection = null;
		try {
			connection = getConnection();
			String sql = "DELETE FROM `database_activity`.`Teacher` WHERE id=?";
			PreparedStatement stmt = connection.prepareStatement(sql);
			stmt.setInt(1, id);

			int status = stmt.executeUpdate();
			if (status > 0) {
				return true;
			}
		} finally {
			if (connection != null) {
				connection.close();
			}
		}
		return false;
	}

	public void closeConnecion() {
		// TODO Close connection if and reset it to release connection to the
		// database server
		// try {
		// if (connection != null) {
		// connection.close();
		// }
		// } catch (SQLException e) {
		// e.printStackTrace();
		// }
//		try {
//			conn.close();
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
		conn = null;
	}
}

// public boolean insertTeacher(Teacher teacher) throws Exception{
// // TODO #5 Write an sql statement that inserts teacher in database.
// Connection connection = null;
// try{
// connection = getConnection();
// String sql = "INSERT INTO `database_activity`.`Teacher` (`id`, `firstname`,
// `lastname`) VALUES(?, ?, ?)";
// PreparedStatement stmt = connection.prepareStatement(sql);
//
//
// stmt.setInt(1, teacher.getID());
// stmt.setString(2, teacher.getFirstName());
// stmt.setString(3, teacher.getLastName());
//
// int status = stmt.executeUpdate();
// if (status > 0){
// return true;
// }
// }finally {
// if (connection != null){
// connection.close();
// }
// }
// return false;
// }