/* ===============================================================================
 *
 * Part of the InfoGlue Content Management Platform (www.infoglue.org)
 *
 * ===============================================================================
 *
 *  Copyright (C)
 * 
 * This program is free software; you can redistribute it and/or modify it under
 * the terms of the GNU General Public License version 2, as published by the
 * Free Software Foundation. See the file LICENSE.html for more information.
 * 
 * This program is distributed in the hope that it will be useful, but WITHOUT
 * ANY WARRANTY, including the implied warranty of MERCHANTABILITY or FITNESS
 * FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License along with
 * this program; if not, write to the Free Software Foundation, Inc. / 59 Temple
 * Place, Suite 330 / Boston, MA 02111-1307 / USA.
 *
 * ===============================================================================
 */

package org.infoglue.cmsinstaller;

import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;
import java.io.*;
import java.sql.*;

public abstract class DatabaseCommander
{
	public String databaseName		= null;
	public String driverClass 		= null;
	public String url         		= null;
	public String userName    		= null;
	public String password    		= null;
	public String databaseHostName	= null;
	public String databasePortNumber= null;
	private String databaseInstance  = null;
	public String infoglueDatabaseUserName = null;
	public String infoglueDatabasePassword = null;
	public String databaseSuffix 	= null;
	public String hostName			= null;
	public boolean createDatabase   = false;
	public boolean createInitialData= false;

	public boolean createAntSeekHome= false;
	public boolean createOfficeStand= false;
	public boolean createOfficeStand2= false;
	public boolean createInfoGlueOrg= false;
	
	public DatabaseCommander(String driverClass, String databaseHostName, String databasePortNumber, String databaseInstance, String url, String userName, String password, String infoglueDatabaseUserName, String infoglueDatabasePassword, String databaseName, String databaseSuffix, String hostName, boolean createDatabase, boolean createInitialData, boolean createAntSeekHome, boolean createOfficeStand, boolean createOfficeStand2, boolean createInfoGlueOrg)
	{
		this.driverClass 		= driverClass;	
		this.url         		= url;	
		this.userName    		= userName;	
		this.password    		= password ;	
		this.databaseHostName 	= databaseHostName;
		this.databasePortNumber = databasePortNumber;
		this.databaseInstance   = databaseInstance;
		this.infoglueDatabaseUserName = infoglueDatabaseUserName;
		this.infoglueDatabasePassword = infoglueDatabasePassword;
		this.databaseName		= databaseName;
		this.databaseSuffix 	= databaseSuffix;
		this.hostName			= hostName;
		this.createDatabase 	= createDatabase;
		this.createInitialData 	= createInitialData;
		this.createAntSeekHome  = createAntSeekHome;
		this.createOfficeStand  = createOfficeStand;
		this.createOfficeStand2 = createOfficeStand2;
		this.createInfoGlueOrg  = createInfoGlueOrg;
	}	
	
	public Connection getConnection() throws Exception
	{
		Connection conn = null;

		Logger.logInfo("*****************************************");
        Logger.logInfo("*      The connect phaze starts         *");
        Logger.logInfo("*****************************************");
        
        // Load the JDBC driver
        Logger.logInfo("Loading JDBC driver " + this.driverClass + "\n");
        Class.forName(this.driverClass).newInstance();
    	
        // Connect to the databse
        Logger.logInfo("Connecting to database on " + url + " whith driver " + this.driverClass + " and " + this.userName + " and " + this.password);
        conn = DriverManager.getConnection(this.url, userName, password);
        Logger.logInfo("Connected...");
        
        return conn;
	}	
	
	public Connection getConnection(String url, String userName, String password) throws Exception
	{
		Connection conn = null;
    
        Logger.logInfo("*****************************************");
        Logger.logInfo("*      The connect phaze starts         *");
        Logger.logInfo("*****************************************");
        
        // Load the JDBC driver
        Logger.logInfo("Loading JDBC driver " + this.driverClass + "\n");
        Class.forName(this.driverClass).newInstance();
    	
        // Connect to the databse
        Logger.logInfo("Connecting to database on " + url);
        conn = DriverManager.getConnection(url, userName, password);
        Logger.logInfo("Connected...");
        
        return conn;
	}
	
	public String getHostAddress()
    {
    	String address = null;
    	
    	try
    	{
    		address = java.net.InetAddress.getLocalHost().getHostAddress();
    	}
    	catch(Exception e)
    	{
    		e.printStackTrace();
    	}
    	
    	return address;
    }
	
	
	/**
	 * This method issues special command to the db. I had to build my own adoption of sql to make this feature.
	 */
	
	protected void issueSpecialCommand(Connection conn, String sql)
	{
        Logger.logInfo("Command:" + sql);
                
        try 
        {
        	String tableName 	= null;
        	String columnName 	= null;
        	String image		= null;
        	String idColumn		= null;
        	String idValue 		= null;
        	
        	StringTokenizer st = new StringTokenizer(sql, " ");
			int i = 0;
		    while (st.hasMoreTokens()) 
		    {
		    	String part = st.nextToken();
		    	//Logger.logInfo("Part: " + part);
		    	if(i == 2)
		    		tableName = part;
		    	if(i == 4)
		    		columnName = part;
		    	if(i == 6)
		    		image = part;
		    	if(i == 8)
		    		idColumn = part;
		    	if(i == 10)
		    		idValue = part;
		    	
		    	i++;
			}
			
			File file = new File(image);
	        FileInputStream fis = new FileInputStream(file);
	        byte[] imageByteArray = new byte[(int)file.length()];
	        fis.read(imageByteArray);	
			
			        	
            sql = "UPDATE " + tableName + " SET " + columnName + " = ? WHERE " + idColumn + " = " + idValue + "";
			//Logger.logInfo("newSQL:" + newSQL);
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setBytes(1, imageByteArray);
			ps.executeUpdate();
        }
        catch(Exception ex) 
        {
			Logger.logInfo("Command failed: " + ex.getMessage());
			Logger.logInfo("SQL: " + sql);
            System.err.println("SQLException: " + ex.getMessage());		
        }
	}
	
	public void setupExamples() throws Exception
	{
		/*
	    if(this.createAntSeekHome)
	    {
			File file = new File("assets/www.antseekhome.com.xml");
			InitialDataInstaller initialDataInstaller = new InitialDataInstaller();
			initialDataInstaller.importRepository(file);
	    }
	    if(this.createOfficeStand)
	    {
			File file = new File("assets/www.officestand.com.xml");
			InitialDataInstaller initialDataInstaller = new InitialDataInstaller();
			initialDataInstaller.importRepository(file);
	    }
	    if(this.createOfficeStand2)
	    {
			File file = new File("assets/www.officestand2.com.xml");
			InitialDataInstaller initialDataInstaller = new InitialDataInstaller();
			initialDataInstaller.importRepository(file);
	    }
	    */
	    if(this.createInfoGlueOrg)
	    {
			File file = new File("assets/www.infoglue.org.xml");
			InitialDataInstaller initialDataInstaller = new InitialDataInstaller();
			initialDataInstaller.importRepository(file);
	    }
	}
	
	/**
	 * This method issues special blob-inserts command to the db. 
	 * I had to build my own adoption of sql to make this feature.
	 */

	protected abstract void issueSpecialBlobCommand(Connection conn, String sql);
	
	
	protected List parseColumns(String columnDefinition)
	{
		List columns = new ArrayList();
		
		//System.out.println("columnDefinition:" + columnDefinition);
		columnDefinition = columnDefinition.substring(1, columnDefinition.length() - 1);
		//System.out.println("columnDefinition:" + columnDefinition);
		
		StringTokenizer st = new StringTokenizer(columnDefinition, ",");
		while (st.hasMoreTokens()) 
		{
			String part = st.nextToken();
			//Logger.logInfo("Part: " + part);
			columns.add(part);
		}
		
		return columns;
	}
	
	public Date parseDate(String dateString, String pattern)
	{	
		if(dateString == null)
			return new Date();
    
		Date date = new Date();    
    
		try
		{
			SimpleDateFormat formatter = new SimpleDateFormat(pattern);
			date = formatter.parse(dateString);
		}
		catch(Exception e)
		{
			Logger.logInfo("Error parsing date:" + dateString);
		}
    
		return date;
	}
	
	protected List parseValues(String values)
	{
		List valueList = new ArrayList();
		
		//System.out.println("values:" + values);
		values = values.substring(1, values.length() - 2);
		//System.out.println("values:" + values);
		
		int offset = 0;
		int index = values.indexOf("[,]", offset);
		//StringTokenizer st = new StringTokenizer(values, "[,]");
		while (index > -1) 
		{
			String part = values.substring(offset, index);
			//String part = st.nextToken();
			//Logger.logInfo("Part: " + part);
			valueList.add(part);
			offset = index + 3;
			index = values.indexOf("[,]", offset);
		}
		
		String part = values.substring(offset);
		//Logger.logInfo("Part: " + part);
		valueList.add(part);
		
		return valueList;
	}
	
	public String getDatabaseInstance() 
	{
		return databaseInstance;
	}
	public void setDatabaseInstance(String databaseInstance) 
	{
		this.databaseInstance = databaseInstance;
	}

	public abstract String getDriver();

	public abstract String getUrl(String hostName, String databasePortNumber, String database);

	public abstract String getUnicodeUrl(String hostName, String databasePortNumber, String database);

	public abstract void setupDatabase() throws Exception;
	
	public abstract void upgradeTo1_3(Connection conn) throws Exception;

	public abstract void upgradeTo1_3_2(Connection conn) throws Exception;

	public abstract void upgradeTo2_0(Connection conn) throws Exception;

	public abstract void upgradeTo2_1(Connection conn) throws Exception;

	public abstract void upgradeTo2_3(Connection conn) throws Exception;

	public abstract void upgradeTo2_8(Connection conn) throws Exception;

	public abstract void upgradeTo2_9(Connection conn) throws Exception;

	public abstract void upgradeTo2_9_7_1(Connection conn) throws Exception;

	public abstract void upgradeTo2_9_8_7(Connection conn) throws Exception;

	public abstract String getDatabaseVendor() throws Exception;

	public abstract void createCastorFile() throws Exception;
	
	public abstract void createCastorRootFile() throws Exception;

	//public abstract void createOSWorkflowFile() throws Exception;

	public abstract void createOSPropertiesRootFile() throws Exception;

	public abstract void createOSPropertiesFile() throws Exception;

	public abstract void testSetupDummyDatabase() throws Exception;
	
	public abstract void testConnectDatabase() throws Exception;
		
	}