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 void showTables() {
	 try {
	 statement = connect.createStatement();
	  setResultSet(statement.executeQuery("SHOW TABLES;"));
	  System.out.println(getResultSet().getMetaData().getColumnCount());
	  while (getResultSet().next()) {
		 System.out.println(getResultSet().getString(1)); 
	  }
	  
	  
	  
	}
	catch(Exception showTabE) {
 		System.out.println("DB geht nî für showTables weil: "+showTabE);
 	} 	
	}
	
 	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 {
    	
    String username = "zaw1";
    String password = "awaHg-1";	

     try {
     // This will load the MySQL driver, each DB has its own driver
      // Setup the connection with the DB
      this.connect = DriverManager.getConnection("jdbc:mysql://localhost/fhausen1?" + "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;
    	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;
	}

}
