import java.io.FileWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Vector;





/*
 * Created on 10 mai 2006
 */


/**
 * Id: Migration.java,v 1.0 10 mai 2006
 * Copyright (c) 2005 Esup Portail (www.esup-portail.org)
 * Classes: Migration
 * Original Author: Yohan Colmant
 * 
 */
public class Migration {
	
	
	
	
	/**
	 * Main
	 * @param args
	 * @throws Exception
	 */
	public static void main(String args[]) throws Exception {
		
		// variables
		String dataBaseDriver = args[2];
		String dataBaseURL = args[3];
		String dataBaseLogin = "";
		if (args.length>4) {
			dataBaseLogin = args[4];
		}
		String dataBasePassword = "";
		if (args.length>5) {
			dataBasePassword = args[5];
		}
		
		// connection
		System.out.println(dataBaseDriver);
		System.out.println(dataBaseURL);
		System.out.println(dataBaseLogin);
		System.out.println(dataBasePassword);
		
		Class.forName(dataBaseDriver);
		Connection cnt = DriverManager.getConnection(dataBaseURL, dataBaseLogin, dataBasePassword);

		// migration
		//createFileFromAdministratedServers(args[0], args[1], cnt);
		
		// migration
		migration(cnt);
		
		// delete
		//deleteFromAdministratedServers(cnt);
		
		// deconnecion
		cnt.close();
	}
	
	
	
	/**
	 * Creation du fichier
	 * @param cnt
	 */
	private static void createFileFromAdministratedServers(String filePath, String loginAttribute, Connection cnt) throws Exception {
		
		// le buffer
		StringBuffer xmlBuff = new StringBuffer();
		xmlBuff.append("<SPACES personalization=\"true\">\n\n");
		
		// lit chaque tuple
		Statement stt = cnt.createStatement();
		String request = "SELECT id, xml_server from storage_available_spaces where from_key='administrated_server'";
    	ResultSet rs = stt.executeQuery(request);
    	
    	// parcours chaque space
    	while (rs.next()) {
    		String id = rs.getString(1);
    		String xml = rs.getString(2);

    		OldServerTool tool = new OldServerTool(xml);
    		OldServer server = tool.getServer();
    		
    		// recherche aclSecurity
    		ArrayList spaces = server.getSpaces();
    		String aclSecurity = null;
    		for (int i=0; aclSecurity==null && i<spaces.size(); i++) {
    			OldSpace space = (OldSpace)spaces.get(i);
    			aclSecurity = space.getAclSecurity();
    		}
    		
    		if (aclSecurity!=null) {
    			server.setAclSecurity(aclSecurity);
    		}
    		
    		// set allowed
    		stt = cnt.createStatement();
    		request = "SELECT target_key, target_type from storage_targets where id_storage_available_spaces='"+id+"'";
        	ResultSet rs2 = stt.executeQuery(request);
        	
        	while (rs2.next()) {
        		OldAuthorizationSpaceParameter auth = new OldAuthorizationSpaceParameter();
        		String targetKey = rs2.getString(1);
        		String targetType = rs2.getString(2);
        		auth.setValue(targetKey);
        		if (targetType.equals("G")) {
        			auth.setAttribute("portal_group");
        		}
        		else {
        			if (targetType.equals("U")) {
            			auth.setAttribute(loginAttribute);
            		}
        		}
        		((OldSpace)server.getSpaces().get(0)).setAuthorization(auth);
        	}
        	
    		
    		String newXml = server.getXml();
    		
    		// mise a jour
    		xmlBuff.append(newXml+"\n");
    	}
    	stt.close();
    	
    	xmlBuff.append("</SPACES>");
    	
    	// écrire le fichier
    	FileWriter file = new FileWriter(filePath);
    	file.write(xmlBuff.toString());
    	file.close();
	}
	
	
	/**
	 * Creation du fichier
	 * @param cnt
	 */
	private static void deleteFromAdministratedServers(Connection cnt) throws Exception {
		
		// lit chaque tuple
		Statement stt = cnt.createStatement();
		String request = "SELECT id from storage_available_spaces where from_key='administrated_server'";
    	ResultSet rs = stt.executeQuery(request);
    	
    	// parcours chaque space
    	while (rs.next()) {
    		String idSpace = rs.getString(1);
    		
    		// get from storage_targets
    		stt = cnt.createStatement();
    		request = "SELECT id from storage_targets where id_storage_available_spaces='"+idSpace+"'";
        	ResultSet rs2 = stt.executeQuery(request);
        	
        	while (rs2.next()) {
        		String idTarget = rs2.getString(1);
        		
        		// delete from storage_users_preferences
        		cnt.createStatement();
        		request = "DELETE FROM storage_users_preferences where id_storage_targets='"+idTarget+"'";
        		stt.executeUpdate(request);
        	}

    		// delete from storage_users_preferences
    		cnt.createStatement();
    		request = "DELETE FROM storage_targets where id_storage_available_spaces='"+idSpace+"'";
    		stt.executeUpdate(request);
    		
    	}
    	
		// delete from storage_available_spaces
		cnt.createStatement();
		request = "DELETE FROM storage_available_spaces where from_key='administrated_server'";
		stt.executeUpdate(request);
		
    	stt.close();
	}
	
	
	
	/**
	 * Migration
	 * @param cnt
	 */
	private static void migration(Connection cnt) throws Exception {
		
		// lit chaque tuple
		Statement stt = cnt.createStatement();
		//String request = "SELECT id, xml_server from storage_available_spaces;";
		String request = "SELECT * from storage_available_spaces";
    	ResultSet rs = stt.executeQuery(request);
    	
    	Vector updates = new Vector();
    	
    	while (rs.next()) {
    		//String id = rs.getString(1);
    		//String xml = rs.getString(2);
    		
    		String id = rs.getString("id");
    		String xml = rs.getString("xml_server");
    		
    		//System.out.println(xml);
    		
    		OldServerTool tool = new OldServerTool(xml);
    		OldServer server = tool.getServer();
    		
    		// recherche aclSecurity
    		ArrayList spaces = server.getSpaces();
    		String aclSecurity = null;
    		for (int i=0; aclSecurity==null && i<spaces.size(); i++) {
    			OldSpace space = (OldSpace)spaces.get(i);
    			aclSecurity = space.getAclSecurity();
    		}
    		
    		if (aclSecurity!=null) {
    			server.setAclSecurity(aclSecurity);
    		}
    		
    		String newXml = server.getXml();
    		
    		//System.out.println(newXml);
    		
    		// mise a jour
    		
    		newXml=newXml.replace("'", "''");
    		String requestUp = "UPDATE storage_available_spaces SET xml_server='"+newXml+"' WHERE id='"+id+"'";
    		
    		updates.addElement(requestUp);
    	}	        
	    
    	Statement sttUp = cnt.createStatement();
    	for (int i=0; i<updates.size(); i++) {
    		String requestUp = (String)updates.elementAt(i);
    		stt.executeUpdate(requestUp);
    	}
    	sttUp.close();
    	
    	stt.close();
		
	}
	
}
