package db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MySQL {
	
	public Connection getConnection()
	{
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (InstantiationException | IllegalAccessException
				| ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
			Connection con=DriverManager.getConnection("jdbc:mysql://141.79.128.121/bwLehrpool?user=root&password=!N4ye,04u.");
			return con;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
		
		
	}
	
	
	public ResultSet getHersteller(Connection con)
	{
		try {
			Statement stm=con.createStatement();
			return stm.executeQuery("SELECT Name FROM bwLehrpool.SWHersteller order by Name asc;");
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return null;
	}
	public ResultSet getSoftware(Connection con, String Hersteller)
	{
		
		try {
			Statement stm=con.createStatement();
			
			return stm.executeQuery("SELECT s.pk,s.Produktname, s.Version, s.Architektur FROM bwLehrpool.Software s left join bwLehrpool.SWHersteller h on s.SWHersteller_pk=h.pk where Name like '"+Hersteller+"' order by s.Produktname;");
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return null;
		
	}
	
	public ResultSet getUserpk(Connection con, String Mail)
	{
		Statement stm;
		try {
			stm = con.createStatement();
			return stm.executeQuery("SELECT pk FROM bwLehrpool.Benutzer where EMail like '"+Mail+"';");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	public ResultSet getVMpk(Connection con, String Userpk)
	{
		Statement stm;
		try {
			stm = con.createStatement();
			return stm.executeQuery("SELECT VM_pk FROM bwLehrpool.Benutzer_has_VM where Benutzer_pk="+Userpk+";");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	public ResultSet getActiveVLs(Connection con, String VMpk)
	{
		Statement stm;
		try {
			stm = con.createStatement();
			return stm.executeQuery("Select Laborname from bwLehrpool.VM where IstAktiv=1 and pk="+VMpk+";");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
				
	}
	
	public ResultSet getInactiveVLs(Connection con, String Mail)
	{
		Statement stm;
		try {
			stm = con.createStatement();
			return stm.executeQuery("Select Laborname from bwLehrpool.VM where IstAktiv=0 and pk=(SELECT VM_pk FROM bwLehrpool.Benutzer_has_VM where Benutzer_pk=(SELECT pk FROM bwLehrpool.Benutzer where EMail like '"+Mail+"'));");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	public ResultSet getOSList(Connection con)
	{
		Statement stm;
		try {
			stm = con.createStatement();
			return stm.executeQuery("SELECT distinct OS FROM bwLehrpool.VM;");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

}