package zaw24;

import java.sql.Connection;
import java.sql.DriverManager;
//import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

public class MYSQLConnector {
	
	  protected Statement statement = null;
	   // private PreparedStatement preparedStatement = null;
	    private ResultSet resultSet = null;
		protected Connection connect = null;
		
		public int verbosity=0;
		
		public ResultSet getRes() {
			return getResultSet();
		}
		public void showRes() {
			if (getResultSet() != null) {
			 try {
				while (this.getResultSet().next()) {
					 System.out.println("Nutzer gefunden");
				     String user = getResultSet().getString("name");
				     String vname = getResultSet().getString("vname");
				     System.out.println(vname);
				     System.out.println(user);
				 }
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		  }
			else {
				System.out.println("Resultset ist NULL, bitte erst eine Abfrage durchführen.");
			}
		}

	    public void readDataBase() throws Exception {
	    	
	    //HZNoack#1643  in Discord	
	    //String url = "jdbc:mysql://lapoc.de:3306/javaKurs";
	    String username = "zaw1";
	    String password = "awaHg-1";	

	        try {
	            // This will load the MySQL driver, each DB has its own driver
	            //Class.forName("com.mysql.jdbc.Driver");
	            // Setup the connection with the DB
	            this.connect = DriverManager
	                    .getConnection("jdbc:mysql://localhost/zaw1?"
	                            + "user="+username+"&password="+password);
	            if (this.verbosity>0) {
	             System.out.println("Datenbank verbunden: mysql localhost für Nutzer " + username + "\n ----------\n" );
	            }
	          

	        } catch (Exception e) {
	            throw e;
	        }

	    }

	    public boolean checkUser(String[] credentials) {
	    	boolean check = false;
	    	//Soll etwa das hier ausführen:
	    	//SELECT * FROM `vhs_users` WHERE `user` LIKE 'hho' AND `passwd` LIKE 'mehh' 
	    	try {
	    	 statement = connect.createStatement();
	    	 setResultSet(statement
	                 .executeQuery("SELECT * FROM `nutzis` WHERE `name` LIKE '"+credentials[0]+"' AND `pass` LIKE '"+credentials[1]+"' ;"));
	    	 
	    	 while (getResultSet().next()) {
	    		 System.out.println("Nutzer gefunden");
	             String user = getResultSet().getString("name");
	             String status = getResultSet().getString("status");
	             String name = getResultSet().getString("vname");
	             Date date = getResultSet().getDate("datum");
	             String comment = getResultSet().getString("remark");
	             System.out.println("UserID: " + getResultSet().getString("uID"));
	             System.out.println("User: " + user);
	             System.out.println("Status: " + status);
	             System.out.println("Name: " + name);
	             System.out.println("Date: " + date);
	             System.out.println("Wahlspruch: " + comment);
	             
	            check = true;
	    	 }
	    	 
	    	}
	    	catch(Exception uCheckE) {
	    		System.out.println("DB geht nî für userCheck weil: "+uCheckE);
	    	}
	    	
	    	return check;
	    }
	    
	    public void writeGeneric(String insert) {
	    	
	    	//INSERT INTO `vhs_users` 
	    	//(`uID`, `user`, `name`, `datum`, `status`, `remark`, `passwd`) 
	    	//VALUES 
	    	//(NULL, 'ma', 'Martina Maus', '2020-12-22', 'sus', 'Zur Zeit nicht an Bord', '');
	    	try {
	       	 statement = connect.createStatement();
	         statement.execute(insert);
	    	}
	    	catch(Exception wgE) {
	    		
	    		System.out.println("No write Generic so sad: "+wgE);
	    	}
	    
	    }
	    
	    private void writeMetaData(ResultSet resultSet) throws SQLException {
	        //  Now get some metadata from the database
	        // Result set get the result of the SQL query

	        System.out.println("The columns in the table are: ");

	        System.out.println("Table: " + resultSet.getMetaData().getTableName(1));
	        for  (int i = 1; i<= resultSet.getMetaData().getColumnCount(); i++){
	            System.out.println("Column " +i  + " "+ resultSet.getMetaData().getColumnName(i));
	        }
	    }

	    private void writeResultSet(ResultSet resultSet) throws SQLException {
	        // ResultSet is initially before the first data set
	        while (resultSet.next()) {
	            // It is possible to get the columns via name
	            // also possible to get the columns via the column number
	            // which starts at 1
	            // e.g. resultSet.getSTring(2);
	            String user = resultSet.getString("user");
	            String status = resultSet.getString("status");
	            String name = resultSet.getString("name");
	            Date date = resultSet.getDate("datum");
	            String comment = resultSet.getString("remark");
	            System.out.println("User: " + user);
	            System.out.println("Status: " + status);
	            System.out.println("Name: " + name);
	            System.out.println("Date: " + date);
	            System.out.println("Wahlspruch: " + comment);
	            System.out.println("\n--------------------------------------\n");
	        }
	    }

	    // You need to close the resultSet
	    public void close() {
	        try {
	            if (getResultSet() != null) {
	                getResultSet().close();
	            }

	            if (statement != null) {
	                statement.close();
	            }

	            if (connect != null) {
	                connect.close();
	            }
	        } catch (Exception e) {

	        }
	    }
		/**
		 * @return the resultSet
		 */
		public ResultSet getResultSet() {
			return resultSet;
		}
		/**
		 * @param resultSet the resultSet to set
		 */
		public void setResultSet(ResultSet resultSet) {
			this.resultSet = resultSet;
		}


}
