package sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Random;


public class SQL {
	
	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://127.0.0.1/bwLehrpool?user=root&password=slx-ng-open");
			con.setAutoCommit(false);
			return con;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	public int writeFTPUser(Connection con,String user, String pass)
	{
		Statement stm;
		try {
			stm=con.createStatement();
			
			int ret=stm.executeUpdate("INSERT INTO `bwLehrpool`.`FtpUsers`(`User`,`Password`,`Uid`,`Gid`,`Dir`)VALUES('"+user+"',SHA1('"+pass+"'),'10001','12345','/srv/openslx/nfs/temp');");
			con.commit();
			return ret;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return -1;
	}
	
	public int DeleteUser(Connection con, String user)
	{
		Statement stm;
		try {
			stm=con.createStatement();
			
			int ret=stm.executeUpdate("DELETE FROM `bwLehrpool`.`FtpUsers` where User like '"+user+"';");
			con.commit();
			return ret;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return -1;
	}
	
	public ResultSet getImage(Connection con)
	{
		try {
			Statement stm=con.createStatement();
			
			return stm.executeQuery("SELECT image_name FROM bwLehrpool.m_VLData_imageInfo;");
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return null;
	}

	public ResultSet getPathOfImage(Connection con, String image_name)
	{
		try {
			Statement stm=con.createStatement();
			
			return stm.executeQuery("SELECT image_path FROM bwLehrpool.m_VLData_imageInfo where image_name like '"+image_name+"';");
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return null;
	}
	
	public int setInstitution(Connection con,String university)
	{
		try {
			Statement stm=con.createStatement();
			
			
			
			ResultSet ret=stm.executeQuery("SELECT * FROM bwLehrpool.m_institution where name like'"+university+"';");
			if(ret.next()==false)
			{
				Random rand=new Random();
				int id=rand.nextInt();
				stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_institution`(`institutionID`,`name`)VALUES('"+id+"','"+university+"');");
				con.commit();
				ResultSet rs=stm.executeQuery("SELECT institutionID FROM bwLehrpool.m_institution WHERE name like '"+university+"';");
				rs.next();
				return rs.getInt("institutionID");
			}
			else
			{
				return ret.getInt("institutionID");
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return -1;
	}
	
	
	
	public int setPerson(Connection con,String login, String lastname, String firstname, String mail, Date lastlogin,int Institution)
	{
		DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		try {
			Statement stm=con.createStatement();
			
			ResultSet ret=stm.executeQuery("SELECT userID FROM bwLehrpool.m_user where Nachname like '"+lastname+"' and Vorname like '"+firstname+"';");
			if(ret.next()==false)
			{
			Random rand=new Random();
			int id=rand.nextInt();
			stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_user`(`userID`,`loginName`,`nachname`,`vorname`,`mail`,`lastLogin`,`institution`)VALUES('"+id+"','Platzhalter','"+lastname+"','"+firstname+"','"+mail+"','"+formatter.format(new Date())+"','"+Institution+"');");
			con.commit();
			ResultSet rs=stm.executeQuery("SELECT userID FROM bwLehrpool.m_user where Nachname like '"+lastname+"' and Vorname like '"+firstname+"';");
			rs.next();
			return rs.getInt("userID");
			}
			else
			{
				return ret.getInt("userID");
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return -1;
	}
	
	public int setImageData(Connection con, int pk_person, boolean license, boolean internet, int cpu, int ram, String imagename)
	{
		
		DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		
		int internet_bol=0;
		int license_bol=0;
		if(internet==true)
		{
			internet_bol=1;
		}
		else if(license==true)
		{
			license_bol=1;
		}

		try {
			Statement stm=con.createStatement();
			Random random=new Random();
			int uid=random.nextInt();
			stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_VLData_imageInfo`(`GUID_imageID`,`imageVersion`,`image_name`,`image_path`,`image_lastCall`,`image_create_time`,`image_update_time`,`image_owner`,`image_change_by`,`rec_create_time`,`rec_change_time`,`rec_owner`,`rec_change_by`,`content_operatingSystem`,`status_isCompressed`,`status_isSecure`,`status_isOptimzed`,`status_isValid`,`status_isReady`,`status_isDeleted`,`status_isLastOfficialVersion`,`cond_hasLicenseRestriction`,`cond_hasInternetRestriction`,`cond_minRAM`,`cond_minCPUs`)VALUES('"+uid+"',1,'"+imagename+"','/srv/openslx/nfs/temp/','"+formatter.format(new Date())+"','"+formatter.format(new Date())+"','"+formatter.format(new Date())+"','"+pk_person+"','"+pk_person+"','"+formatter.format(new Date())+"','"+formatter.format(new Date())+"','"+pk_person+"','"+pk_person+"',1,1,1,1,1,1,0,0,'"+license_bol+"','"+internet_bol+"','"+ram+"','"+cpu+"');");
			con.commit();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ram;
		
	}
	
	public ResultSet getImageListWithoutLectures(Connection con){
		try {
			Statement stm=con.createStatement();
			
			return stm.executeQuery("SELECT distinct vl.image_name, vl.cond_hasLicenseRestriction, os.name, '',vl.image_update_time,  Concat(u.Nachname,' ',u.Vorname) FROM bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_user u Where vl.content_operatingSystem=os.operatingSystemID and vl.image_owner=u.userID;");
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	public ResultSet getImageListWithLectures(Connection con){
		try {
			Statement stm=con.createStatement();
			
			return stm.executeQuery("SELECT vl.image_name, vl.cond_hasLicenseRestriction, os.name, lect.name,  vl.image_update_time, Concat(u.Nachname,' ',u.Vorname) FROM bwLehrpool.m_VLData_imageInfo vl, bwLehrpool.m_operatingSystem os, bwLehrpool.m_VLData_lecture lect, bwLehrpool.m_user u Where vl.content_operatingSystem=os.operatingSystemID and lect.imageID=vl.GUID_imageID and vl.image_owner=u.userID;");
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	public ResultSet getAllOS(Connection con)
	{
		
		
		try {
			Statement stm=con.createStatement();
			return stm.executeQuery("SELECT name FROM bwLehrpool.m_operatingSystem;");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		

		return null;
		
	}
	
	public ResultSet getPersonData(Connection con,String Vorname, String Nachname)
	{
		try {
			Statement stm=con.createStatement();
			return stm.executeQuery("SELECT u.Nachname, u.Vorname, u.mail, i.name FROM bwLehrpool.m_user u, bwLehrpool.m_institution i where u.Nachname like '"+Nachname+"' and u.Vorname like '"+Vorname+"' and u.institution=i.institutionID;");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	public int setLectureData(Connection con, int pk_person, int pk_image,int imageversion, String name, String desc, String shortdesc,Date start, Date end, boolean isactive)
	{
		
		DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		int active_bol=0;
		if(isactive==true)
		{
			active_bol=1;
		}
		try {
			Statement stm=con.createStatement();
			Random random=new Random();
			int uid=random.nextInt();
			stm.executeUpdate("INSERT INTO `bwLehrpool`.`m_VLData_lecture`(`lectureID`,`name`,`isActive`,`startTime`,`endTime`,`lastUsed`,`shortDescription`,`description`,`imageID`,`imageVersion`,`admin_createTime`,`admin_changeTime`,`admin_owner`,`admin_change_by`)VALUES('"+uid+"','"+name+"','"+active_bol+"','"+formatter.format(start)+"','"+formatter.format(end)+"','"+formatter.format(new Date())+"','"+shortdesc+"','"+desc+"','"+pk_image+"','"+imageversion+"','"+formatter.format(new Date())+"','"+formatter.format(new Date())+"','"+pk_person+"','"+pk_person+"');");
			con.commit();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
		
	}
	
	public ResultSet getImageIDandVersion(Connection con,String name)
	{
		try {
			Statement stm=con.createStatement();
			return stm.executeQuery("SELECT GUID_imageID, imageVersion FROM bwLehrpool.m_VLData_imageInfo where image_name like '"+name+"';");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
}