/*
    * Copyright 2004-2006 the Seasar Foundation and the Others.
    *
    * Licensed under the Apache License, Version 2.0 (the "License");
    * you may not use this file except in compliance with the License.
    * You may obtain a copy of the License at
    *
    *     http://www.apache.org/licenses/LICENSE-2.0
    *
   * Unless required by applicable law or agreed to in writing, software
   * distributed under the License is distributed on an "AS IS" BASIS,
   * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, 
   * either express or implied. See the License for the specific language
   * governing permissions and limitations under the License.
   */
  package org.seasar.tuigwaa.database;
  
  import java.io.InputStream;
  import java.sql.Connection;
  import java.sql.ResultSet;
  import java.sql.ResultSetMetaData;
  import java.sql.SQLException;
  import java.sql.Statement;
  import java.util.ArrayList;
  import java.util.Arrays;
  import java.util.Collection;
  import java.util.Collections;
  import java.util.HashMap;
  import java.util.Iterator;
  import java.util.List;
  import java.util.Map;
  
  import javax.sql.DataSource;
  import javax.sql.XADataSource;
  import javax.transaction.TransactionManager;
  
  import org.apache.commons.beanutils.DynaBean;
  import org.apache.commons.beanutils.DynaProperty;
  import org.apache.commons.collections.CollectionUtils;
  import org.apache.commons.logging.Log;
  import org.apache.commons.logging.LogFactory;
  import org.seasar.extension.dbcp.impl.ConnectionPoolImpl;
  import org.seasar.extension.dbcp.impl.DataSourceImpl;
  import org.seasar.extension.dbcp.impl.XADataSourceImpl;
  import org.seasar.framework.exception.SQLRuntimeException;
  import org.seasar.tuigwaa.database.util.CSVfileLoader;
  import org.seasar.tuigwaa.database.util.DatafileLoader;
  import org.seasar.tuigwaa.database.util.DynaBatchHandler;
  import org.seasar.tuigwaa.database.util.DynaPropertyWrapper;
  import org.seasar.tuigwaa.database.util.DynaSelectHandler;
  import org.seasar.tuigwaa.database.util.EntityFactory;
  import org.seasar.tuigwaa.model.common.DomainUtils;
  import org.seasar.tuigwaa.system.Constants;
  import org.seasar.tuigwaa.system.TgwServiceException;
  import org.seasar.tuigwaa.util.TgwPathResolver;
  import org.seasar.tuigwaa.util.TgwResource;
  
  import com.isenshi.util.ResourceUtils;
  import com.isenshi.util.extlib.DiconResource;
  
  /***
   * Need to fix the policy of byte array data backup/restore.
   * 
   * @author someda
   */
  public class BasicDatabaseServiceImpl implements BasicDatabaseService {
  
  	private static final String TRANSACTION_DICON = "dicon/transaction.dicon";
  
  	private MultiDataSource multiDataSource;
  
  	private Log log_ = LogFactory.getLog(getClass());
  	
  	private Map databaseMap = new HashMap();
  	
  	private Map schemaMap = new HashMap();
  
  	private Map driverMap = Collections.synchronizedMap(new HashMap());
  
  	private TransactionManager tmanager;
  
  	public BasicDatabaseServiceImpl(MultiDataSource multiDataSource,
  			DynaSelectHandler selectHandler, DynaBatchHandler batchHandler,
  			TransactionManager tmanager) {
  		this.multiDataSource = multiDataSource;
  //		this.selectHandler_ = selectHandler;
  //		this.batchHandler_ = batchHandler;
  		this.tmanager = tmanager;
  	}
  
  	public String getDriver(String domainName) {
  		String databaseName = getDataBaseName(domainName);
  		String driver = (String) driverMap.get(databaseName);
  		if (driver != null) {
  			return driver;
  		} else {
  			return TgwResource.getProperty("database.driver");
  		}
  	}
 
 	public boolean hasDomain(String databaseName) {
 		Iterator itr = databaseMap.keySet().iterator();
 		while(itr.hasNext()){
 			String domainName = (String)itr.next();
 			String aDatabaseName = (String)databaseMap.get(domainName);
 			if(databaseName.equals(aDatabaseName)){
 				return true;
 			}
 		}
 		return false;		
 	}
 	
 	public String getSchemaName(String domainName) {
 		String schemaName = (String) schemaMap.get(domainName);
 		if (schemaName != null) {
 			return schemaName;
 		}
 		return domainName;
 	}
 
 	public void addExternalDatabase(String dbName, String driver, String user,
 			String password, String url) {
 
 //		 create data source
 		XADataSourceImpl xaDataSource = new XADataSourceImpl();
 		xaDataSource.setDriverClassName(driver);
 		xaDataSource.setUser(user);
 		xaDataSource.setURL(url);
 		xaDataSource.setPassword(password);
 		ConnectionPoolImpl pool = new ConnectionPoolImpl();
 		pool.setXADataSource(xaDataSource);
 		pool.setTransactionManager(tmanager);
 		DataSourceImpl dataSource = new DataSourceImpl(pool);
 
 		String path = TgwPathResolver.getDatabaseFilePath(dbName);
 
 		DiconResource dicon = new DiconResource();
 		dicon.setNamespace(dbName);
 		dicon.setPath(path);
 		dicon.addComponent(xaDataSource, null, new String[] {
 				"driverClassName", "user", "URL", "password" });
 		dicon.addComponent(pool, null, new String[] {});
 		dicon.addComponent(dataSource, null, new String[] {});
 		dicon.save();
 
 		addDataSource(dataSource, dbName, driver);
 	}
 
 	public void loadExternalDatabase() {
 		String[] files = ResourceUtils.getChildrenNames(Constants.DIRECTORY_APP);
 		if (files == null) {
 			return;
 		}
 		for (int i = 0; i < files.length; i++) {
 			if (files[i].endsWith(".db")) {
 				DiconResource dicon = new DiconResource();
 				dicon.setPath(Constants.DIRECTORY_APP + files[i]);
 				dicon.load();
 
 				dicon.bindRoot();
 				dicon.include(TRANSACTION_DICON);
 
 				String name = dicon.getNamespace();
 				DataSource dataSource = (DataSource) dicon
 						.getComponent(DataSource.class);
 
 				XADataSource xa = (XADataSource) dicon
 						.getComponent(XADataSource.class);
 				String driverName = ((XADataSourceImpl) xa)
 						.getDriverClassName();
 
 				addDataSource(dataSource, name, driverName);
 			}
 		}
 	}
 
 	private void addDataSource(DataSource dataSource, String dbName, String driverName){
 		if (checkConnection(dataSource)) {
 			driverMap.put(dbName, driverName);
 			multiDataSource.addDataSource(dbName, dataSource);
 		}
 	}
 	
 	public void deleteExternalDatabase(String databaseName) {
 		driverMap.remove(databaseName);
 		multiDataSource.removeDataSource(databaseName);
 	}
 	
 	private boolean checkConnection(DataSource dataSource) {
 		boolean connFlag = false;
 		try {
 			Connection con = dataSource.getConnection();
 			con.close();
 			connFlag = true;
 		} catch (SQLException e) {
 			log_.warn("can't load external dabtabase");
 		}
 		return connFlag;
 	}
 
 	public boolean useBaseDatabase(String domainName) {
 		return databaseMap.get(domainName) == null;
 	}
 
 	public void setExternalDatabaseMapping(String domainName, String dbName, String schemaName) throws TgwServiceException{
 		if(!multiDataSource.isExistDatabase(dbName)){
 			throw new TgwServiceException("doesn't exit database " + dbName);
 		}
 		putDataBaseName(domainName, dbName);
 		if (schemaName != null) {
 			schemaMap.put(domainName, schemaName);
 		}
 	}
 
 	public void removeExternalDatabaseMapping(String domainName) {
 		removeDataBaseName(domainName);
 		schemaMap.remove(domainName);
 	}
 	
 	public List getTableNames(String domainName) {
 		DatabaseInfo info = getDatabaseInfo(domainName);
 		String schema = getSchemaName(domainName);
 		return info.getTablenameNames(schema);
 	}
 
 	public List getSchemaNames(String domainName) {
 		DatabaseInfo info = getDatabaseInfo(domainName);
 		return info.getSchemaNames();
 	}
 
 	public boolean existSchema(String schema) {
 		Iterator itr = getSchemaNames(schema).iterator();
 		boolean flag = false;
 		while (itr.hasNext()) {
 			String schemaName = (String) itr.next();
 			if (schemaName.equalsIgnoreCase(schema)) {
 				flag = true;
 				break;
 			}
 		}
 		return flag;
 	}
 
 	public Map createEntityMap(String domainName) throws SQLException {
 		String schema = getSchemaName(domainName);
 		DatabaseInfo info = getDatabaseInfo(domainName);
 		EntityFactory factory = new EntityFactory(domainName, schema, info);
 		Connection con = getConnection(domainName);
 
 		try {
 			Statement stmt = con.createStatement();
 
 			for (Iterator i = info.getTablenameNames(schema).iterator(); i
 					.hasNext();) {
 				String tableName = (String) i.next();
 				String sql = DatabaseUtils.buildBackupQuery(schema, tableName);
 				ResultSet rs = stmt.executeQuery(sql);
 				ResultSetMetaData metadata = rs.getMetaData();
 
 				String entityName = tableName.toLowerCase();
 				factory.addEntity(metadata, entityName);
 			}
 		} catch (SQLException e) {
 			e.printStackTrace();
 		}
 		return factory.getCreatedEntityMap();
 	}
 
 	public boolean existTable(String domainName, String name) {
 		Iterator itr = getTableNames(domainName).iterator();
 		boolean flag = false;
 		while (itr.hasNext()) {
 			String tableName = (String) itr.next();
 			if (tableName.equalsIgnoreCase(name)) {
 				flag = true;
 				break;
 			}
 		}
 		return flag;
 	}
 
 	public DatabaseInfo getBaseDatabaseInfo() {
 		return getDatabaseInfo(multiDataSource.getBaseConnection());
 	}
 
 	public DatabaseInfo getDatabaseInfo(String domainName) {
 		try {
 			Connection con = getConnection(domainName);
 			return getDatabaseInfo(con);
 		} catch (SQLException e) {
 			throw new SQLRuntimeException(e);
 		}
 	}
 
 	public DatabaseInfo getDatabaseInfo(Connection conn) {
 		DatabaseInfo info = null;
 		try {
 			info = new DatabaseInfo(conn);
 		} catch (SQLException se) {
 			log_.error(se.getMessage());
 		} finally {
 			DatabaseUtils.closeQueitly(conn);
 		}
 		return info;
 	}
 
 	public String[] getExternalDatabaseNames() {
 		return multiDataSource.getExternalDatabaseNames();
 	}
 
 	public List getExternalDatabaseInfoList() {
 		String[] names = getExternalDatabaseNames();
 		List list = new ArrayList();
 		for (int i = 0; i < names.length; i++) {
 			try {
 				Connection conn = multiDataSource.getConnection(names[i]);
 				DatabaseInfo dbInfo = getDatabaseInfo(conn);
 				dbInfo.setName(names[i]);
 				list.add(dbInfo);
 			} catch (SQLException e) {
 				// TODO error handling when an external database doesn't run
 			}
 		}
 		return list;
 	}
 	
 	public void backup(String dbName, String schema, String[] tableNames, String targetdir) throws
 	TgwServiceException{
 		
 		DatabaseInfo info = getDatabaseInfo(schema);
 		if(dbName != null){
 			try{
 				Connection con = multiDataSource.getConnection(dbName);
 				info = getDatabaseInfo(con);				
 			}catch(SQLException se){
 				throw new TgwServiceException(se);
 			}
 		}
 		
 		Collection backupTableNameList = null;		
 		if(tableNames == null || tableNames.length == 0){// full backup
 			backupTableNameList = info.getTablenameNames(schema);			
 		}else{
 			Collection c = new ArrayList(Arrays.asList(tableNames));			
 			for(int i=0;i<tableNames.length;i++){
 				List list = info.getForeignKeys(null,schema,tableNames[i]);
 				c = CollectionUtils.union(c,list);
 			}
 			backupTableNameList = c;
 		}		
 		DiconResource resource = new DiconResource();
 		resource.setPath(TgwPathResolver.getDatabaseBackupFilePath(targetdir));		
 		
 		for (Iterator i = backupTableNameList.iterator(); i.hasNext();) { // table						
 			String table = (String) i.next();
 			log_.info("back up table... " + table);			
 			backupTable(dbName, schema, table, resource, targetdir);
 		}
 		resource.save();
 	}
 		
 	public void restore(String dbName, String schema, String[] tableNames, String srcdir)
 	throws TgwServiceException{
 		// DatabaseInfo info = getDatabaseInfo(schema);
 		// List tableList = info.getTablenameNames(schema);
 		// List processList = new ArrayList();
 		// info.sortByReference(null, schema, tableList, processList);
 
 		List processList = DomainUtils.getSortedTalbeNames(schema);
 
 		DiconResource resource = new DiconResource();
 		resource.setPath(TgwPathResolver.getDatabaseBackupFilePath(srcdir));
 		resource.load();
 
 		// for initial clean-up
 		// if on-delete-cascade to be set, this reverse operation is unnecessary
 		// FIXME: should ignore table which contains byte[] columns??
 		Collections.reverse(processList);
 		deleteDataAll(dbName, schema, processList, srcdir, resource);
 
 		// for data insert
 		Collections.reverse(processList);
 		restoreDataAll(dbName, schema, processList, srcdir, resource);		
 		
 	}
 
 	public Connection getConnection(String domainName) throws SQLException {
 		String databaseName = getDataBaseName(domainName);
 		if (databaseName != null) {
 			return multiDataSource.getConnection(databaseName);
 		} else {
 			return multiDataSource.getBaseConnection();
 		}
 	}
 			
 	public void insertOnDelete(String dbName, String schema, String table, DatafileLoader loader, String[] columns, String[] types)
 	throws TgwServiceException{
 		delete(dbName, schema,table);
 		insert(dbName, schema,table,loader,columns,types);
 	}	
 		
 	public void insert(String dbName, String schema, String table, DatafileLoader loader, String[] columns, String[] types)
 	throws TgwServiceException{
 		
 		String[] data;
 		List bindDataList = new ArrayList();
 		
 		while((data =loader.load()) != null){
 			Object[] obj = DatabaseUtils.bindArgs(data,types);
 			bindDataList.add(obj);
 		}
 				
 		String sql = DatabaseUtils.buildRestoreQuery(schema, table.toUpperCase(), columns);
 		Class[] argTypes = convertTypes(types);
 		Collections.reverse(bindDataList);
 		
 		log_.info("Starting restore contents of " + table + " " + sql);
 		
 		DynaBatchHandler handler = multiDataSource.getDynaBatchHandler(dbName);
 		
 		try{
 //			batchHandler_.setSql(sql);
 			handler.setSql(sql);
 			int num = handler.execute(bindDataList, argTypes);
 			log_.info(num + " rows inserted.");
 		}catch(SQLRuntimeException e){
 			throw new TgwServiceException(e);
 		}				
 	}
 	
 	public void delete(String dbName, String schema, String table) throws TgwServiceException{		
 
 		String sql = DatabaseUtils.buildCleanQuery(schema, table.toUpperCase());
 		DynaBatchHandler handler = multiDataSource.getDynaBatchHandler("");
 		
 		log_.info("Starting cleanup contents of " + table + " " + sql);		
 		try{
 //			batchHandler_.setSql(sql);
 //			batchHandler_.execute();
 			handler.setSql(sql);
 			handler.execute();
 		}catch(SQLRuntimeException e){
 			throw new TgwServiceException(e);
 		}
 	}
 
 	// [Start] ------ Private Method ------	
 	private void putDataBaseName(String domainName, String databaseName){
 		databaseMap.put(domainName, databaseName);
 	}
 
 	private void removeDataBaseName(String domainName){
 		databaseMap.remove(domainName);
 	}
 	
 	private String getDataBaseName(String domainName){
 		return (String)databaseMap.get(domainName);
 	}
 		
 	private void backupTable(String dbName, String schema, String table,
 			DiconResource resource, String targetdir) {
 
 		Map options = new HashMap();
 		
 		String uppercaseTableName = table.toUpperCase();		
 		options.put(DiconResource.OPTION_NAME, uppercaseTableName);
 
 		String sql = DatabaseUtils.buildBackupQuery(schema, table);
 		DynaSelectHandler handler = multiDataSource.getDynaSelectHandler(dbName);		
 		
 //		selectHandler_.setSql(sql);
 		handler.setSql(sql);
 
 		List result = (List) handler.execute(null);
 
 		// if there is no data, skip backup.
 		if (result.size() == 0) {
 			log_.info(table + " is empty, skip to backup.");
 			return;
 		}
 
 		String path = TgwPathResolver.getTableBackupFilePath(targetdir, uppercaseTableName);				
 		
 		String[] names = null;
 		StringBuffer buf = new StringBuffer();
 		DynaPropertyWrapper propwrapper = new DynaPropertyWrapper();
 
 		for (Iterator rows = result.iterator(); rows.hasNext();) { // resultset
 			DynaBean row = (DynaBean) rows.next();
 
 			if (names == null) { // first row
 				DynaProperty[] props = row.getDynaClass().getDynaProperties();
 				propwrapper.put(props);
 				names = propwrapper.getNames();
 				resource.addComponent(propwrapper, options);
 			}
 			backupColumns(buf, row, names);
 		}
 		ResourceUtils.writeContent(path, buf.toString());
 	}
 
 	private void backupColumns(StringBuffer buf, DynaBean row, String[] names) {
 		boolean firstColumn = true;
 		for (int j = 0; j < names.length; j++) { // each column loop
 
 			if (firstColumn) {
 				firstColumn = false;
 			} else {
 				buf.append(",");
 			}
 
 			Object o = row.get(names[j]);
 			if (o != null) {
 				String s = o.toString().replaceAll("\"", "\"\"");
 				buf.append("\"" + s + "\"");
 			} else {
 				buf.append("\"\"");
 			}
 
 		}
 		buf.append(Constants.LINEBREAK_CODE);
 	}
 
 	private void deleteDataAll(String dbName, String schema, List processList, String srcdir,
 			DiconResource resource) throws TgwServiceException{
 		log_.info("delete order " + processList);
 
 		for (Iterator i = processList.iterator(); i.hasNext();) {
 			String table = (String) i.next();
 			delete(dbName, schema,table);
 		}
 	}
 	
 	private void restoreDataAll(String dbName, String schema, List processList, String srcdir,
 			DiconResource resource) throws TgwServiceException{
 		log_.info("restoring order " + processList);
 		for (Iterator i = processList.iterator(); i.hasNext();) {
 			String table = (String) i.next();
 			table = table.toUpperCase();
 			String path = TgwPathResolver.getTableBackupFilePath(srcdir, table);
 
 			if (ResourceUtils.isExist(path)) {
 				DynaPropertyWrapper wrapper = (DynaPropertyWrapper) resource.getComponent(table);
 				String[] types = wrapper.getTypes();
 				String[] columns = wrapper.getNames();
 				InputStream is = ResourceUtils.readContentAsStream(path);
 				DatafileLoader loader = new CSVfileLoader(is);
 				insert(dbName, schema,table,loader,columns,types);				
 				loader.close();				
 			}
 		}
 	}	
 	
 	private Class[] convertTypes(String[] types){
 		
 		if(types == null){
 			return null;
 		}		
 		Class[] argTypes = new Class[types.length];
 		for(int i=0; i<types.length;i++){			
 			String className = types[i];
 			if("java.sql.Date".equals(className) || "java.sql.Timestamp".equals(className)){
 				className = "java.util.Date";
 			}			
 			try{
 				argTypes[i] = Class.forName(className);			
 			}catch(ClassNotFoundException cnfe){
 				argTypes[i] = Object.class;
 			}			
 		}				
 		return argTypes;		
 	}
 		
 }