package com.iit;

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

public class DBConnector {

	private final String connectionURL = "jdbc:mysql://localhost:3306/user_test?serverTimezone=UTC";
	private final String connectionDriver = "com.mysql.cj.jdbc.Driver";
	private final String connectionUser = "root";
	private final String connectionPassword = "1234";
	private Connection conn;

	/**
	 * Insert User Into database
	 * 
	 * @param firstname
	 * @param lastname
	 * @return
	 */
	public boolean InsertUser(String firstname, String lastname) {

		if (conn == null)
			connect();

		System.out.println("Inserting user into database: " + firstname + " " + lastname);

		try {
			PreparedStatement pstmt = conn.prepareStatement("INSERT INTO user (firstname, lastname) VALUES (?,?);");

			pstmt.setString(1, firstname);
			pstmt.setString(2, lastname);
			pstmt.executeUpdate();
			pstmt.close();
			disconnect();
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}

		return true;
	}

	/**
	 * Get all the users
	 */
	public ArrayList<User> getUsers() {

		ArrayList<User> userList = new ArrayList<User>();

		if (conn == null)
			connect();

		try {
			PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM user;");

			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {

				User user = new User();

				user.setRow_id(rs.getInt(1));
				user.setFirstname(rs.getString(2));
				user.setLastname(rs.getString(3));
				user.setCreatedate(rs.getString(4));

				userList.add(user);
			}

			rs.close();
			pstmt.close();
			disconnect();
		} catch (Exception e) {
			e.printStackTrace();
		}

		return userList;
	}

	/**
	 * Check a user exists or not
	 */
	public boolean getAUser(int row_id) {

		boolean exist = false;

		if (conn == null)
			connect();

		try {
			PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM user WHERE id=?;");

			pstmt.setInt(1, row_id);

			ResultSet rs = pstmt.executeQuery();

			if (rs.next()) {
				exist = true;
			}

			rs.close();
			pstmt.close();
			disconnect();
		} catch (Exception e) {
			e.printStackTrace();
		}

		return exist;
	}

	/**
	 * Delete user from database
	 * 
	 * @param row_id
	 * @return
	 */
	public boolean DeleteUser(int row_id) {

		if (conn == null)
			connect();

		System.out.println("Deleting user from database: " + row_id);

		try {
			PreparedStatement pstmt = conn.prepareStatement("DELETE FROM user WHERE id=?;");

			pstmt.setInt(1, row_id);
			pstmt.executeUpdate();
			pstmt.close();
			disconnect();
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}

		return true;
	}

	public void connect() {
		System.out.println("Connecting to mysql...");

		try {
			Class.forName(connectionDriver);
			conn = DriverManager.getConnection(connectionURL, connectionUser, connectionPassword);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void disconnect() {

		System.out.println("Disconnecting from mysql...");

		try {
			conn.close();
			conn = null;
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
