package jtm.activity13;

import java.sql.Connection;
import java.sql.DriverManager;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

public class TeacherManager {

	protected Connection conn;
	public static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";

	public TeacherManager() {

		// TODO #1 When new TeacherManager is created, create connection to the
		// database server:

		final String url = "jdbc:mysql://localhost/?autoReconnect=true&useSSL=false&characterEncoding=utf8";
		final String user = "root";
		final String pass = "Student007";
		// conn.setAutoCommit(false);

		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(url, user, pass);
			conn.setAutoCommit(true);
		} catch (Exception e) {
			e.printStackTrace();
		}

	}
	// 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

	/**
	 * 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) {

		try {
			Statement stm = conn.createStatement();

			ResultSet rs = stm.executeQuery("SELECT * FROM database_activity.Teacher WHERE id LIKE '" + id + "'");

			while (rs.next()) {
				return new Teacher(rs.getInt("id"), rs.getString("firstname"), rs.getString("lastname"));
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return new Teacher(0, null, null);

		//
		// return null;
		// Hint: Because default database is not set in connection,
		// use full notation for table "database_activity.Teacher"

	}

	/**
	 * 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.
	 * @throws SQLException
	 */
	public List<Teacher> findTeacher(String firstName, String lastName){
		// 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!

		List<Teacher> list = new ArrayList<Teacher>();

		try {
			Statement stm = conn.createStatement();
			ResultSet res = stm.executeQuery("SELECT * FROM database_activity.Teacher WHERE firstname LIKE '%"
					+ firstName + "%' AND lastname LIKE '%" + lastName + "%'");

			while (res.next()) {
				list.add(new Teacher(res.getInt("id"), res.getString("firstname"), res.getString("lastname")));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;

		// conn.commit();

	}

	/**
	 * 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 SQLException
	 */

	public boolean insertTeacher(String firstName, String lastName) {

		try{

			Statement stm = conn.createStatement();
			int status = stm.executeUpdate("INSERT INTO database_activity.Teacher (firstname, lastname) VALUES ('"
					+ firstName + "', '" + lastName + "')");

			while(status !=0){
				return true;
			}

		} catch (SQLException e){
			e.printStackTrace();
		}
		return false;




	}

	/**
	 * Insert teacher object into database
	 *
	 * @param teacher
	 * @return true on success, false on error (e.g. non-unique id)
	 * @throws SQLException
	 */
	public boolean insertTeacher(Teacher teacher) throws SQLException {

		// String sql = "INSERT IGNORE INTO database_activity.Teacher (id,
		// firstname, lastname) VALUES (?, ?, ?)";
		// PreparedStatement preparedStatement = conn.prepareStatement(sql);
		// preparedStatement.setInt(1, teacher.getID());
		// preparedStatement.setString(2, teacher.getFirstName());
		// preparedStatement.setString(3, teacher.getLastName());
		// conn.commit();
		try {
			Statement stm = conn.createStatement();

			int status = stm.executeUpdate("INSERT INTO database_activity.Teacher (id, firstname, lastname) VALUES ('"
					+ teacher.getID() + "', '" + teacher.getFirstName() + "', '" + teacher.getLastName() + "')");
			while (status != 0) {
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;

		//
		// int status = preparedStatement.executeUpdate();

		// return false;

		// TODO #5 Write an sql statement that inserts teacher in database.
		// return false;
	}

	/**
	 * 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.
	 * @throws Exception
	 */
	public boolean updateTeacher(Teacher teacher) throws Exception {

		try {

			Statement stm = conn.createStatement();
			int status = stm.executeUpdate("UPDATE database_activity.Teacher SET firstname='" + teacher.getFirstName()
					+ "', lastname='" + teacher.getLastName() + "' WHERE id=" + teacher.getID());

			while (status != 0) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;

		// TODO #6 Write an sql statement that updates teacher information.

	}

	/**
	 * 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.
	 * @throws SQLException
	 */
	public boolean deleteTeacher(int id) throws SQLException {
		// TODO #7 Write an sql statement that deletes teacher from database.

		try {
			Statement stmt = conn.createStatement();
			int res = stmt.executeUpdate("DELETE FROM database_activity.Teacher WHERE id=" + id);
			while (res != 0) {
				return true;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public void closeConnecion() {

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

	// public static void main(String[] args) throws Exception {
	// TeacherManager manager = new TeacherManager();
	//// manager.insertTeacher("Janis", "Bekeris");
	//// manager.deleteTeacher(3);
	// Teacher lol = new Teacher(13, "Janka", "Garais");
	//// manager.insertTeacher(lol);
	// manager.deleteTeacher(3);
	//
	// }
}