package sa.sqlinteractor.model;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Jbdc_Query {

	private String database = "test";
	private String table;
	private String sql = "";
	private String message = "";

	String url = "jdbc:mysql://localhost:3306/" + this.database;
	String username = "root";
	String password = "";

	public void createTable() {

		StringBuilder sb = new StringBuilder();

		try {

			Connection connection = null;

			sb.append("Connecting database..." + "\n");
			connection = DriverManager.getConnection(this.url, this.username, this.password);
			sb.append("Database connected!" + "\n");

			Statement stmt = connection.createStatement();

			try {
				stmt.executeUpdate(this.table);
				sb.append("Table creation process successfully!" + "\n");
			} catch (SQLException s) {
				sb.append("Table already exists!" + "\n");
			}

			setOutputMessage(sb.toString());
			connection.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void insertTable() {

		StringBuilder sb = new StringBuilder();

		Connection conn = null;
		Statement stmt = null;
		try {
			// STEP 2: Register JDBC driver
			Class.forName("com.mysql.jdbc.Driver");

			// STEP 3: Open a connection
			sb.append("Connecting to a selected database..." + "\n");
			conn = DriverManager.getConnection(this.url, this.username, this.password);
			sb.append("Connected database successfully..." + "\n");

			// STEP 4: Execute a query
			sb.append("Inserting records into the table..." + "\n");
			stmt = conn.createStatement();

			stmt.executeUpdate(this.sql);

		} catch (SQLException se) {
			// Handle errors for JDBC
			se.printStackTrace();
		} catch (Exception e) {
			// Handle errors for Class.forName
			e.printStackTrace();
		} finally {
			// finally block used to close resources
			try {
				if (stmt != null)
					conn.close();
			} catch (SQLException se) {
			}// do nothing
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException se) {
				se.printStackTrace();
			}// end finally try
		}// end try
		sb.append("Goodbye!" + "\n");
		setOutputMessage(sb.toString());
	}// end main

	public void viewEmployees(){

		Connection conn = null;
		Statement stmt = null;

		StringBuilder sb = new StringBuilder();

		try {
			try {
				Class.forName("com.mysql.jdbc.Driver");
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			// STEP 3: Open a connection

			conn = DriverManager.getConnection(this.url, this.username, this.password);


			// STEP 4: Execute a query
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(this.sql);
			while (rs.next()) {

				sb.append(rs.getString("Field")+"\t");
				sb.append(rs.getString("Type")+ "\t");
				sb.append(rs.getString("Null")+ "\t");
				sb.append(rs.getString("Key")+ "\t");
				sb.append(rs.getString("Default")+ "\t");
				sb.append(rs.getString("Extra")+ "\n");


				setOutputMessage(sb.toString());

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

	public void viewTable(){

		Connection conn = null;
		Statement stmt = null;

		StringBuilder sb = new StringBuilder();

		try {
			try {
				Class.forName("com.mysql.jdbc.Driver");
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

			// STEP 3: Open a connection

			conn = DriverManager.getConnection(this.url, this.username, this.password);


			// STEP 4: Execute a query
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(this.sql);
			while (rs.next()) {


				sb.append(rs.getInt("EmployeeId")+"\t");
				sb.append(rs.getString("lastname")+"\t");
				sb.append(rs.getString("hiredate")+"\t");
				sb.append(rs.getString("birthdate")+"\t");
				sb.append(rs.getString("sex")+"\t");
				sb.append(rs.getString("jobstatus")+"\t");
				sb.append(rs.getString("paytype")+"\t");
				sb.append(rs.getString("annualsalary")+"\t");
				sb.append(rs.getInt("years_served")+"\n");
				setOutputMessage(sb.toString());

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

	public void setCreationQuery(String table) {
		this.table = table;
	}

	public String setInitCSV(List<Table> tables) {
		StringBuilder tt = new StringBuilder();
		tt.append("INSERT INTO Employees (EmployeeId, lastname, hiredate, birthdate, sex, jobstatus," + "paytype,annualsalary,years_served)VALUES");
		for (Table t : tables) {
			tt.append("\n" + t.toStringQuery(t) + ",");

		}
		tt.setLength(tt.length() - 1);
		tt.append(";");
		this.sql = tt.toString();
		return this.sql;
	}

	public String setSqlQuery(Table table){

		return this.sql = "INSERT INTO Employees (lastname, hiredate, birthdate, sex, jobstatus,"+ "paytype,annualsalary,years_served)VALUES("
		+ table.toString()+");";
	}

	public void setSqlQuery(String sql) {
		this.sql = sql;
	}

	public void setOutputMessage(String message) {
		this.message = message;
	}

	public String getOutputMessage() {
		return this.message;
	}
}