/*
    * 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.sql.Connection;
  import java.sql.DatabaseMetaData;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.util.ArrayList;
  import java.util.HashMap;
  import java.util.Iterator;
  import java.util.List;
  import java.util.Map;
  
  import org.apache.commons.logging.Log;
  import org.apache.commons.logging.LogFactory;
  import org.hibernate.util.StringHelper;
  import org.seasar.framework.exception.SQLRuntimeException;
  import org.seasar.tuigwaa.system.Constants;
  import org.seasar.tuigwaa.util.ajax.AjaxEvent;
  import org.seasar.tuigwaa.util.ajax.Ajaxlizable;
  import org.seasar.tuigwaa.util.ajax.SharedObjects;
  
  
  /***
   * @author someda
   */
  public class DatabaseInfo implements Ajaxlizable {
  
  	private static final String[] COLUMN_TYPES = { "TABLE" };
  
  	private static final String COLUMN_TABLENAME = "TABLE_NAME";
  
  	private static final String COLUMN_TABLESCHEMA = "TABLE_SCHEM";
  
  	private static final String COLUMN_PKTABLE = "PKTABLE_NAME";
  
  	private static final String COLUMN_NAME = "COLUMN_NAME";
  
  	private String name;
  
  	private String databasename;
  
  	private String version;
  
  	private String url;
  
  	private String username;
  
  	private String drivername;
  
  	private DatabaseMetaData metadata;
  
  	private Log log_ = LogFactory.getLog(getClass());
  
  	public DatabaseInfo(Connection con) throws SQLException {
  		try {
  			this.metadata = con.getMetaData();
  			init();
  		} catch (SQLException se) {
  			throw se;
  		}
  	}
  
  	public DatabaseInfo(DatabaseMetaData metadata) {
  		this.metadata = metadata;
  		init();
  	}
  
  	public boolean isSharable() {
  		return false;
  	}
  	
  	public void addEvent(AjaxEvent event) {
  	}
  	
  	public String getSharedKey() {
  		return null;
  	}
  	
  	public void setSharedObject(SharedObjects objs) {
  	}
  	
  	private void init() {
  		if (metadata != null) {
  			try {
 				databasename = metadata.getDatabaseProductName();
 				version = metadata.getDatabaseProductVersion();
 				url = metadata.getURL();
 				username = metadata.getUserName();
 				drivername = metadata.getDriverName();
 			} catch (SQLException se) {
 				log_.error(se.getMessage());
 			}
 		}
 	}
 
 	// ----- [Start] setters and getters -----
 
 	public void setName(String name) {
 		this.name = name;
 	}
 
 	public String getName() {
 		return name;
 	}
 
 	public String getDatabasename() {
 		return databasename;
 	}
 
 	public void setDatabasename(String databasename) {
 		this.databasename = databasename;
 	}
 
 	public String getVersion() {
 		return version;
 	}
 
 	public void setVersion(String version) {
 		this.version = version;
 	}
 
 	public String getUrl() {
 		return url;
 	}
 
 	public void setUrl(String url) {
 		this.url = url;
 	}
 
 	public String getUsername() {
 		return username;
 	}
 
 	public void setUsername(String username) {
 		this.username = username;
 	}
 
 	public String getDrivername() {
 		return drivername;
 	}
 
 	public void setDrivername(String drivername) {
 		this.drivername = drivername;
 	}
 
 	// ----- [End] setters and getters -----
 
 	public List getTablenameNames(String schema) {
 		List list = new ArrayList();
 
 		try {
 			ResultSet rs = null;
 			try {
 				if (metadata.storesUpperCaseIdentifiers()) {
 					rs = metadata.getTables(null, StringHelper
 							.toUpperCase(schema), null, COLUMN_TYPES);
 				} else if (metadata.storesLowerCaseIdentifiers()) {
 					rs = metadata.getTables(null, StringHelper
 							.toLowerCase(schema), null, COLUMN_TYPES);
 				} else {
 					rs = metadata.getTables(null, schema, null, COLUMN_TYPES);
 				}
 				while (rs.next()) {
 					String tableName = rs.getString(COLUMN_TABLENAME);
 					list.add(tableName);
 				}
 			} finally {
 				if (rs != null)
 					rs.close();
 			}
 		} catch (SQLException e) {
 			log_.error(e.getMessage());
 			throw new RuntimeException(e);
 		}
 		return list;
 	}
 
 	public List getSchemaNames() {
 		List list = new ArrayList();
 
 		try {
 			ResultSet rs = null;
 			try {
 				rs = metadata.getSchemas();
 				while (rs.next()) {
 					String schemaName = rs.getString(COLUMN_TABLESCHEMA);
 					list.add(schemaName);
 				}
 			} finally {
 				if (rs != null)
 					rs.close();
 			}
 		} catch (SQLException e) {
 			log_.error(e.getMessage());
 			throw new RuntimeException(e);
 		}
 		return list;
 	}
 
 	public String getTableNamesByAjax(String schema) {
 
 		StringBuffer buf = new StringBuffer();
 		List list = getTablenameNames(schema);
 		if (list != null) {
 			buf.append("<ul>");
 			for (Iterator i = list.iterator(); i.hasNext();) {
 				String table = (String) i.next();
 				if (schema.equalsIgnoreCase("public")) {
 					buf.append("<li>" + table + "</li>");
 				} else {
 					if (table.endsWith("_"))
 						table = table.substring(0, table.length() - 1);
 					String path = "../" + schema.toLowerCase()
 							+ "/listRecord.do?" + Constants.PARAM_ENTITY_NAME
 							+ "=" + table.toLowerCase();
 					buf.append("<li><a href=\"" + path + "\">" + table
 							+ "</a></li>");
 				}
 			}
 			buf.append("</ul>");
 		}
 		return buf.toString();
 	}
 
 	public void sortByReference(String catalog, String schema, List tableList,
 			List processList) {
 
 		if (processList == null)
 			throw new IllegalArgumentException("processList must not be null.");
 
 		if (tableList.size() == 0)
 			return;
 
 		try {
 			for (int i = 0; i < tableList.size(); i++) {
 				String table = (String) tableList.get(i);
 				ResultSet rs = null;
 
 				try {
 
 					List fks = getForeignKeys(catalog, schema, table);
 
 					if (fks.size() == 0) {
 						processList.add(table);
 						tableList.remove(table);
 						i--;
 					} else {
 						boolean allcontained = true;
 						for (Iterator j = fks.iterator(); j.hasNext();) {
 							String fkname = (String) j.next();
 							if (!processList.contains(fkname)) {
 								allcontained = false;
 								break;
 							}
 						}
 						if (allcontained) {
 							processList.add(table);
 							tableList.remove(table);
 							i--;
 						}
 					}
 				} finally {
 					if (rs != null)
 						rs.close();
 				}
 			}
 			if (tableList.size() != 0)
 				sortByReference(catalog, schema, tableList, processList);
 
 		} catch (SQLException se) {
 			se.printStackTrace();
 		}
 	}
 
 	public List getForeignKeys(String catalog, String schema, String tableName) {
 		List fks = new ArrayList();
 		try {
 			ResultSet rs = null;
 			if (metadata.storesUpperCaseIdentifiers()) {
 				rs = metadata.getImportedKeys(
 						StringHelper.toUpperCase(catalog), StringHelper
 								.toUpperCase(schema), StringHelper
 								.toUpperCase(tableName));
 			} else if (metadata.storesLowerCaseIdentifiers()) {
 				rs = metadata.getImportedKeys(
 						StringHelper.toLowerCase(catalog), StringHelper
 								.toLowerCase(schema), StringHelper
 								.toLowerCase(tableName));
 			} else {
 				rs = metadata.getImportedKeys(catalog, schema, tableName);
 			}
 
 			while (rs.next()) {
 				String fkname = rs.getString(COLUMN_PKTABLE);
 				if (log_.isDebugEnabled())
 					log_.debug(tableName + " references " + fkname);
 				fks.add(fkname);
 			}
 		} catch (SQLException e) {
 			e.printStackTrace();
 		}
 		return fks;
 	}
 
 	public Map getForeignKeyInfoMap(String catalog, String schema, String tableName) {
 		Map map = new HashMap();
 		try {
 			ResultSet rs = null;
 			if (metadata.storesUpperCaseIdentifiers()) {
 				rs = metadata.getImportedKeys(
 						StringHelper.toUpperCase(catalog), StringHelper
 								.toUpperCase(schema), StringHelper
 								.toUpperCase(tableName));
 			} else if (metadata.storesLowerCaseIdentifiers()) {
 				rs = metadata.getImportedKeys(
 						StringHelper.toLowerCase(catalog), StringHelper
 								.toLowerCase(schema), StringHelper
 								.toLowerCase(tableName));
 			} else {
 				rs = metadata.getImportedKeys(catalog, schema, tableName);
 			}
 
 			while (rs.next()) {
 				ForeignKeyInfo info = new ForeignKeyInfo(rs);
 				map.put(info.getColumnName(), info);
 			}
 		} catch (SQLException e) {
 			e.printStackTrace();
 		}
 		return map;
 	}
 
 	public List getPrimaryKeys(String catalog, String schema, String tableName) {
 		List pks = new ArrayList();
 		try {
 			ResultSet rs = null;
 			if (metadata.storesUpperCaseIdentifiers()) {
 				rs = metadata.getPrimaryKeys(StringHelper.toUpperCase(catalog),
 						StringHelper.toUpperCase(schema), StringHelper
 								.toUpperCase(tableName));
 			} else if (metadata.storesLowerCaseIdentifiers()) {
 				rs = metadata.getPrimaryKeys(StringHelper.toLowerCase(catalog),
 						StringHelper.toLowerCase(schema), StringHelper
 								.toLowerCase(tableName));
 			} else {
 				rs = metadata.getPrimaryKeys(catalog, schema, tableName);
 			}
 
 			while (rs.next()) {
 				String pkname = rs.getString(COLUMN_NAME);
 				if (log_.isDebugEnabled())
 					log_.debug(tableName + " references " + pkname);
 				pks.add(pkname);
 			}
 		} catch (SQLException e) {
 			e.printStackTrace();
 		}
 		
 		
 		return pks;
 	}
 	
 
 	public static class ForeignKeyInfo{
 		
 		private String columnName;
 		
 		private String referenceTableName;
 		
 		private String referecneColumnName;
 		
 		ForeignKeyInfo(ResultSet rs){
 			try {
 				this.referenceTableName = rs.getString("PKTABLE_NAME");
 				this.referecneColumnName = rs.getString("PKCOLUMN_NAME");
 				this.columnName = rs.getString("FKCOLUMN_NAME");
 			} catch (SQLException e) {
 				throw new SQLRuntimeException(e);
 			}
 		}
 		
 		public String getColumnName() {
 			return columnName;
 		}
 		
 		public String getReferecneColumnName() {
 			return referecneColumnName;
 		}
 		
 		public String getReferenceTableName() {
 			return referenceTableName;
 		}
 		
 	}
 }