/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/

package net.beaconhillcott.simsmoodlesync;

import java.io.UnsupportedEncodingException;
import java.rmi.*;
import java.sql.*;
import java.util.*;
import net.beaconhillcott.CommandReporter;
import java.util.logging.*;
import net.beaconhillcott.moodlerest.*;
/**
*
* @author root
*/
public class Main {
  
  /**
  * @param args the command line arguments
  */
  public static void main(String[] args) {
    Connection mysqldb = null;
    try {
      Class.forName("com.mysql.jdbc.Driver").newInstance();
      // 10.10.10.10 is the ficticious IP address of the intermediate MySQL database server
      mysqldb=connectToDB("jdbc:mysql://10.10.10.10:3306/moodlesync?user=some_username&password=some_usernames_password");
    } catch (InstantiationException ex) {
      Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IllegalAccessException ex) {
      Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
    } catch (ClassNotFoundException ex) {
      Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
    }
    
    MoodleRestWebService.init("http://www.a_website_somewhere.com/moodle2/webservice/rest/server.php", "averylongstringwhichjusthappenstobethemoodletoken");
    System.setSecurityManager(new RMISecurityManager());
    // 10.10.10.11 is the ficticious IP address of the RMI server
    CommandReporter cr=new CommandReporter("10.10.10.11","sims_username","sims_users_password");
    
    // Set students status to inactive within intermediate database
    System.out.println("Set all users status to inactive within intermediate database");
    update(mysqldb,"update users set active=0");
    
    // Get student data from SIMS
    System.out.println("Fetching student details from SIMS");
    cr.setCommandReport("/report:\"CR_moodle_students\"");
    try {
      cr.execute();
    } catch (RemoteException ex) {
      Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
    }
    // The fields returned from the SIMS report
    int idField=cr.getFieldID("id");
    int firstnameField=cr.getFieldID("firstname");
    int lastnameField=cr.getFieldID("lastname");
    int usernameField=cr.getFieldID("username");
    ArrayList data=cr.getDataRecords();
    Iterator rec=data.listIterator();
    StringBuilder sql=new StringBuilder();
    
    // Update the intermediate database
    System.out.println("Update the intermediate database");
    while (rec.hasNext()) {
      ArrayList record=(ArrayList) rec.next();
      if (sql.length()>0) sql.delete(0, sql.length());
      if (record.get(usernameField)!=null) {
	if (!isDataItem(mysqldb,"select * from users where role_id=5 and id="+record.get(idField))) {
	  if (((String) record.get(usernameField))!=null && !((String) record.get(usernameField)).equals("")) {
	    sql=sql.append("insert into users (id, firstname, lastname, username, role_id, active) values (").append(record.get(idField)).append(",'").append(record.get(firstnameField)).append("','").append(addSlashes((String)record.get(lastnameField))).append("','").append(addSlashes((String) record.get(usernameField))).append("',5,1)");
	    insert(mysqldb,sql.toString());
	  }
	} else {
	  update(mysqldb,"update users set active=1 where id="+record.get(idField));
	}
      }
    }
    
    // Get staff data from SIMS
    System.out.println("Fetching staff details from SIMS");
    cr.setCommandReport("/report:\"CR_Staff_List\"");
    try {
      cr.execute();
    } catch (RemoteException ex) {
      Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
    }
    // The fields returned from the SIMS report
    idField=cr.getFieldID("id");
    firstnameField=cr.getFieldID("firstname");
    lastnameField=cr.getFieldID("lastname");
    usernameField=cr.getFieldID("username");
    data=cr.getDataRecords();
    rec=data.listIterator();
    sql=new StringBuilder();
    
    // Update the intermediate database
    System.out.println("Update the intermediate database");
    while (rec.hasNext()) {
      ArrayList record=(ArrayList) rec.next();
      if (sql.length()>0) sql.delete(0, sql.length());
      if (record.get(usernameField)!=null) {
	if (!isDataItem(mysqldb,"select * from users where role_id=2 and id="+record.get(idField))) {
	  if (((String) record.get(usernameField))!=null && !((String) record.get(usernameField)).equals("")) {
	    sql=sql.append("insert into users (id, firstname, lastname, username, role_id, active) values (").append(record.get(idField)).append(",'").append(record.get(firstnameField)).append("','").append(addSlashes((String)record.get(lastnameField))).append("','").append(addSlashes((String) record.get(usernameField))).append("',2,1)");
	    insert(mysqldb,sql.toString());
	  }
	} else {
	  update(mysqldb,"update users set active=1 where id="+record.get(idField));
	}
      }
    }
    
    System.out.println("Update Moodle users");
    try {
      MoodleUser user=null;
      Statement statement=mysqldb.createStatement();
      ResultSet rs=statement.executeQuery("select * from users where active=1");
      while (rs.next()) {
	if (rs.getString("moodle_id").equals("0")) {
	  if (rs.getString("role_id").equals("5")) {
	    user=new MoodleUser(rs.getString("username"), "12345", rs.getString("firstname"), rs.getString("lastname"), (rs.getString("firstname").toLowerCase()+"."+rs.getString("lastname")).replaceAll("'", "").replaceAll(" ", "").toLowerCase()+"@theemaildomainnaim.com");
	  } else {
	    user=new MoodleUser(rs.getString("username"), "12345", rs.getString("firstname"), rs.getString("lastname"), (rs.getString("firstname").toLowerCase().substring(0, 1)+"."+rs.getString("lastname")).replaceAll("'", "").replaceAll(" ", "").toLowerCase()+"@theemaildomainnaim.com");
	  }
	  user.setIdNumber(rs.getString("id"));
	  user.setAuth("ldap");
	  user.setCity("Crowborough");
	  user.setCountry("GB");
	  try {
	    MoodleRestUser.createUser(user);
	  } catch (MoodleRestUserException ex) {
	    Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
	  }
	  Statement statement2=mysqldb.createStatement();
	  statement2.executeUpdate("update users set moodle_id="+user.getId()+" where id="+rs.getString("id"));
	  statement2.close();
	}
      }
      rs.close();
      statement.close();
    } catch (SQLException ex) {
      Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
    }
    
    System.out.println("Deleting old users from Moodle");
    try {
      Statement statement = mysqldb.createStatement();
      ResultSet rs=statement.executeQuery("select moodle_id from users where active=0");
      while (rs.next()) {
	try {
	  MoodleRestUser.deleteUser(rs.getLong("moodle_id"));
	} catch (MoodleRestUserException ex) {
	  Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
	} catch (UnsupportedEncodingException ex) {
	  Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
	}
      }
      rs.close();
      statement.close();
    } catch (SQLException ex) {
      Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
    }
    
    System.out.println("Deleting old users from intermediate database");
    update(mysqldb,"delete from users where active=0");

    System.out.println("Set user_course and course entries to inactive");
    update(mysqldb,"update user_course set active=0");
    update(mysqldb,"update courses set active=0");
    
    System.out.println("Fetching class and member details from SIMS");
    cr.setCommandReport("/report:\"CR_moodle_classes\"");
    try {
      cr.execute();
    } catch (RemoteException ex) {
      Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
    }
    System.out.println("Updating user_class and course entries");
    data=cr.getDataRecords();
    rec=data.listIterator();
    sql=new StringBuilder();
    // The fields returned from the SIMS report
    int studentIdField=cr.getFieldID("student_id");
    int classIdField=cr.getFieldID("class_id");
    int classNameField=cr.getFieldID("class_name");
    int teacherIdField=cr.getFieldID("teacher_id");
    while (rec.hasNext()) {
      ArrayList record=(ArrayList) rec.next();
      if (sql.length()>0) sql.delete(0, sql.length());
      if (record.get(teacherIdField)!=null && !record.get(teacherIdField).equals("")) {
	if (!isDataItem(mysqldb,"select * from user_course where course_id="+record.get(classIdField)+" and user_id="+record.get(teacherIdField))) {
	  sql=sql.append("insert into user_course (user_id, course_id, user_role,moodle_role_id) values (").append(record.get(teacherIdField)).append(",").append(record.get(classIdField)).append(",'teacher',2)");
	  insert(mysqldb,sql.toString());
	  sql=new StringBuilder();
	} else {
	  update(mysqldb,"update user_course set active=1 where user_id="+record.get(teacherIdField)+" and course_id="+record.get(classIdField));
	}
      }
      if (!isDataItem(mysqldb,"select * from user_course where course_id="+record.get(classIdField)+" and user_id="+record.get(studentIdField))) {
	sql=sql.append("insert into user_course (user_id, course_id, user_role,moodle_role_id) values (").append(record.get(studentIdField)).append(",").append(record.get(classIdField)).append(",'student',5)");
	insert(mysqldb,sql.toString());
	sql=new StringBuilder();
      } else {
	update(mysqldb,"update user_course set active=1 where user_id="+record.get(studentIdField)+" and course_id="+record.get(classIdField));
      }
      if (!isDataItem(mysqldb,"select * from courses where idnumber="+record.get(classIdField))) {
	sql=sql.append("insert into courses (idnumber, shortname, fullname, moodle_id) values ('").append(record.get(classIdField)).append("','").append(record.get(classNameField)).append("','").append(record.get(classNameField)).append("',0)");
	//System.out.println(sql.toString());
	insert(mysqldb,sql.toString());
	sql=new StringBuilder();
      } else {
	update(mysqldb,"update courses set active=1 where idnumber="+record.get(classIdField));
      }
    }
    // For some reason, SIMS is now allowed to create at least two classes with the same name, something to do with partnership exchange!
    System.out.println("Shifting users in duplicated classes");
    try {
      Statement statement = mysqldb.createStatement();
      ResultSet rs=statement.executeQuery("SELECT shortname FROM courses where active=0");
      while (rs.next()) {
	String fromId=null;
	String toId=null;
	String shortname=rs.getString("shortname");
	Statement statement2 = mysqldb.createStatement();
	ResultSet rs2=statement2.executeQuery("SELECT moodle_id,idnumber FROM courses where shortname='"+shortname+"'");
	while (rs2.next()) {
	  if (rs2.getString("moodle_id").equals("0"))
	    fromId=rs2.getString("idnumber");
	  else
	    toId=rs2.getString("idnumber");
	}
	rs2.close();
	statement2.close();
	update(mysqldb,"update ignore user_course set course_id="+toId+",active=1 where course_id="+fromId);
      }
      rs.close();
      statement.close();
    } catch (SQLException ex) {
      Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
    }
    System.out.println("Local database cleanup");
    update(mysqldb,"update user_course,users set user_course.moodle_user_id=users.moodle_id where user_course.user_id=users.id");
    update(mysqldb,"update user_course,courses set user_course.moodle_course_id=courses.moodle_id where user_course.course_id=courses.idnumber");
    update(mysqldb,"delete from user_course where active=0");
    update(mysqldb,"delete from user_course where moodle_user_id=0");
    update(mysqldb,"delete from user_course where moodle_course_id=0");
    update(mysqldb,"delete from courses where active=0");
    update(mysqldb,"delete from courses where moodle_id=0");
    System.out.println("Complete");
    try {
      mysqldb.close();
    } catch (SQLException ex) {
      Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
    }
    
  }  

  
  public static void update(Connection conn, String sql) {
    insert(conn, sql);
  }
  
  public static Connection connectToDB(String sqlConnection) {
    try {
      return DriverManager.getConnection(sqlConnection);
    } catch (SQLException ex) {
      Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
    }
    return null;
  }
  
  public static boolean isDataItem(Connection conn, String sql) {
    boolean result=false;
    Statement statement;
    try {
      statement = conn.createStatement();
      ResultSet rs=statement.executeQuery(sql);
      result=rs.next();
      rs.close();
      statement.close();
    } catch (SQLException ex) {
      Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
    }
    return result;
  }
  
  public static void insert(Connection conn, String sql) {
    try {
      Statement statement = conn.createStatement();
      statement.executeUpdate(sql);
      statement.close();
    } catch (SQLException ex) {
      Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
    }
    
  }
  
  public static String addSlashes(String Source) {
    int i,length;
    StringBuilder Buffer=new StringBuilder();
    length=Source.length();
    for (i=0;i<length;i++) {
      switch (Source.charAt(i)) {
	case '\\': Buffer.append("\\\\");
	break;
	case '\n': Buffer.append("\\n");
	break;
	case '\t': Buffer.append("\\t");
	break;
	case '\r': Buffer.append("\\r");
	break;
	case '\f': Buffer.append("\\f");
	break;
	case '\'': Buffer.append("\\'");
	break;
	case '\"': Buffer.append("\\\"");
	break;
	default:   Buffer.append(Source.charAt(i));
	break;
      }
    }
    return Buffer.toString();
  }
  
  public static String removeSlashes(String Source) {
    int i,length;
    StringBuilder Buffer=new StringBuilder();
    if (Source!=null) {
      length=Source.length();
      for (i=0;i<length;i++) {
	switch (Source.charAt(i)) {
	  case '\\': if (i+1<length) {
	    switch (Source.charAt(i+1)) {
	      case '\\': Buffer.append('\\');
	      i++;
	      break;
	      case 'n':  Buffer.append('\n');
	      i++;
	      break;
	      case 't':  Buffer.append('\t');
	      i++;
	      break;
	      case 'r':  Buffer.append('\r');
	      i++;
	      break;
	      case 'f':  Buffer.append('\f');
	      i++;
	      break;
	      case '\'': Buffer.append('\'');
	      i++;
	      break;
	      case '\"': Buffer.append('\"');
	      i++;
	      break;
	    }
	  }
	  break;
	      default: Buffer.append(Source.charAt(i));
	      break;
	}
      }
    }
    return Buffer.toString();
  }
}