package sql;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

import org.ini4j.InvalidFileFormatException;
import org.ini4j.Wini;

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 {
			Wini ini = new Wini(new File("Maintenance_Config.ini"));
			Connection con = DriverManager
					.getConnection("jdbc:mysql://"+ini.get("sql", "connection")+"?user="+ini.get("sql", "user")+"&password="+ini.get("sql", "pass")+"");
			con.setAutoCommit(false);
			
			return con;
		} catch (SQLException | IOException e) {
			// TODO Auto-generated catch block
	
			e.printStackTrace();
		}
		return null;
	}
	
	
	public void deleteFTPUsers(){
		try {
			Connection con=getConnection();
			Statement stm = con.createStatement();

			stm.executeUpdate("Delete from bwLehrpool.FtpUsers where CreationDate < date_sub(now(),interval 1 day);");
			con.commit();
			con.close();
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			
			e.printStackTrace();
		}
	}
	
	public void setLectureInactive(){
		try {
			Connection con=getConnection();
			Statement stm_query = con.createStatement();
			Statement stm_update = con.createStatement();
			ResultSet rs=stm_query.executeQuery("SELECT lectureID FROM bwLehrpool.m_VLData_lecture where lastUsed < date_sub(now(),interval 1 day);");
			while(rs.next()){
				System.out.println(rs.getString("lectureID"));
				stm_update.executeUpdate("UPDATE `bwLehrpool`.`m_VLData_lecture` SET `isActive`='0' WHERE `lectureID`='"+rs.getString("lectureID")+"';");
			}
			con.commit();
			con.close();
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			
			e.printStackTrace();
		}
	}

}