package com.quantum.model;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.quantum.adapters.AdapterFactory;
import com.quantum.adapters.DatabaseAdapter;
import com.quantum.sql.MultiSQLServer;
import com.quantum.sql.SQLMetaDataResults;
import com.quantum.util.sql.SQLStates;

/**
 * This class models a table or view.
 * 
 * @author bcholmes
 */
abstract class EntityImpl implements Entity {
	
	// The JDBC-ODBC Driver is more happy if you look up metadata values
	// using the column number than if you use the column name
	
	private static final int INDEX_METADATA_INDEX_NAME = 6;
	private static final int INDEX_METADATA_COLUMN_NAME = 9;
	private static final int INDEX_METADATA_ASC_OR_DESC = 10;
	
	private static final int PRIMARY_KEYS_METADATA_COLUMN_NAME = 4;
	private static final int PRIMARY_KEYS_METADATA_KEY_SEQ = 5;
	
	private static final int COLUMN_METADATA_COLUMN_NAME = 4;
	private static final int COLUMN_METATDATA_DATA_TYPE = 5;
	private static final int COLUMN_METATDATA_TYPE_NAME = 6;
	private static final int COLUMN_METADATA_COLUMN_SIZE = 7;
	private static final int COLUMN_METADATA_DECIMAL_DIGITS = 9;
	private static final int COLUMN_METADATA_REMARKS = 12;
	private static final int COLUMN_METADATA_ORDINAL_POSITION = 17;
	private static final int COLUMN_METADATA_IS_NULLABLE = 18;
	
	private String schema;
    private String name;
    private String type;
    private Bookmark bookmark;
    private Boolean exists = Boolean.TRUE;
    private boolean isSynonym = false;	// Tells if its a synonym or not
    // Columns will be cached in this array, as sometimes asking for them to the JDBC driver is very costy
    // (for example in Oracle when synonyms and remarks are asked for )
    private Column[] columns = null; 
    
    public EntityImpl(Bookmark bookmark, String schema, String name, String type, boolean isSynonym) {
        this.schema = schema;
        this.name = name;
        this.type = type;
        this.bookmark = bookmark;
        this.isSynonym = isSynonym;
    }
    public Bookmark getBookmark() {
        return this.bookmark;
    }
    public String getName() {
        return this.name;
    }
    public String getSchema() {
        return this.schema;
    }
    public String getType() {
        return this.type;
    }
    public String getQualifiedName() {
        return (this.schema == null || this.schema.length() == 0) ?
            this.name : this.schema + "." + this.name;
    }
    public Column getColumn(String columnName) throws NotConnectedException, SQLException  {
        Column column = null;
        if (this.columns == null) this.columns = getColumns();
        for (int i = 0, length = (this.columns == null) ? 0 : this.columns.length;
            column == null && i < length;
            i++) {
            if (columnName != null && columnName.equals(this.columns[i].getName())) {
                column = this.columns[i];
            }
        }
        return column;
    }
    public Column[] getColumns() throws NotConnectedException, SQLException {
        if (this.columns != null) return this.columns;
        Connection connection = this.bookmark.getConnection();
        try {
        	this.columns = getColumnsFromMetaData(connection);
        	return this.columns;
        } catch (SQLException e) {
        	if (SQLStates.ODBC_DRIVER_NOT_CAPABLE.equals(e.getSQLState()) 
        			&& AdapterFactory.JDBC_ODBC_BRIDGE.equals(
        					getBookmark().getJDBCDriver().getType())) {
        		this.columns = getColumnsFromQuery(connection);
        		return this.columns;
        	} else {
        		throw e;
        	}
        }
        
    }
    
    /**
	 * @param connection
	 * @return
	 * @throws SQLException
	 */
	private Column[] getColumnsFromMetaData(Connection connection) throws SQLException {
		Map temp = new HashMap();
		DatabaseMetaData metaData = connection.getMetaData();
		ResultSet resultSet = metaData.getColumns(null, getSchema(), getName(), null);
		try {
		    while (resultSet.next()) {
		        ColumnImpl column = new ColumnImpl(
		            this, 
		            resultSet.getString(COLUMN_METADATA_COLUMN_NAME),
		            resultSet.getString(COLUMN_METATDATA_TYPE_NAME),
		            resultSet.getInt(COLUMN_METATDATA_DATA_TYPE),
		            resultSet.getInt(COLUMN_METADATA_COLUMN_SIZE),
		            resultSet.getInt(COLUMN_METADATA_DECIMAL_DIGITS),
		            "YES".equalsIgnoreCase(resultSet.getString(COLUMN_METADATA_IS_NULLABLE)),
		            resultSet.getInt(COLUMN_METADATA_ORDINAL_POSITION),
					resultSet.getString(COLUMN_METADATA_REMARKS)
		            );
		        temp.put(column.getName(), column);
		    }
		} finally {
			resultSet.close();
		}

		resultSet = metaData.getPrimaryKeys(null, getSchema(), getName());
		try {
		    while (resultSet.next()) {
		        String name = resultSet.getString(PRIMARY_KEYS_METADATA_COLUMN_NAME);
		        short keySequence = resultSet.getShort(PRIMARY_KEYS_METADATA_KEY_SEQ);
		        ColumnImpl column = (ColumnImpl) temp.get(name);
		        if (column != null) {
		            column.setPrimaryKeyOrder(keySequence);
		        }
		        
		    }
		    
		    List columnList = Collections.synchronizedList(
		        new ArrayList(temp.values()));
		    Collections.sort(columnList);
		    return (Column[]) columnList.toArray(new Column[columnList.size()]);
		    
		} finally {
			resultSet.close();
		}
	}
	
	/**
	 * Some databases, (in particular, MS Access under ODBC) aren't terribly friendly
	 * about supporting metadata.  This method scrapes out the data the old-fashioned way.
	 * 
	 * @param temp
	 * @param connection
	 * @throws SQLException
	 */
	private Column[] getColumnsFromQuery(Connection connection) throws SQLException {
		List temp = new ArrayList();
		SQLMetaDataResults results = 
				(SQLMetaDataResults) MultiSQLServer.getInstance().getMetaData(
					this, connection);
		SQLMetaDataResults.Row[] rows = results.getRows();
		for (int i = 0, length = results.getRowCount(); i < length; i++) {
		    ColumnImpl column = new ColumnImpl(
		            this, 
		            (String) rows[i].get(1),
		            (String) rows[i].get(2),
		            ((Integer) rows[i].get(7)).intValue(),
		            ((Long) rows[i].get(3)).longValue(),
		            ((Integer) rows[i].get(4)).intValue(),
		            "Nullable".equalsIgnoreCase((String) rows[i].get(5)),
		            i+1, "");
		        temp.add(column);
		}
		return (Column[]) temp.toArray(new Column[temp.size()]);
	}
	/**
     * Some JDBC drivers (Oracle for example) won't return the comments
     * We recheck with a custom query, if it's defined
	 * @param iniComment The already got comment
	 * @param tableName The fully qualified table name
	 * @param columnName The column name
	 * 
	 *  NO LONGER USED, there is a parameter (remarksReporting) in the JDBC connection that makes ORACLE return the
	 * remarks for tables and columns. Is slower, so an option will be used.
	 * 
	 * The function is kept in case other JDBC drivers have the same problem
	 */
	private String getComments( String iniComment, String tableName, String columnName) {
		if (iniComment != null && iniComment.length() > 0) 
			return iniComment;
		String comment = "";
		try {
			Connection con = this.bookmark.getConnection();
			DatabaseAdapter adapter = this.bookmark.getAdapter();
			Statement stmt = con.createStatement();
			try {
				if (adapter != null && stmt != null 
						&& adapter.getCommentsQuery(tableName, columnName) != null) {
				
					stmt.execute(adapter.getCommentsQuery(tableName, columnName));
					ResultSet set = stmt.getResultSet();
					try {
						if (set.next()) {
							comment = set.getString(1);
						}
					} finally {
						set.close();
					}
				}
			} finally {
				stmt.close();
			}
		} catch (NotConnectedException e) {
		} catch (SQLException e) {
		}
            
		return comment;
	}
	
	public Index[] getIndexes() {
        
        List indexList = new ArrayList();
        Map temp = new HashMap();
        try {
            Connection connection = this.bookmark.getConnection();
            DatabaseMetaData metaData = connection.getMetaData();
            ResultSet resultSet = metaData.getIndexInfo(null, getSchema(), getName(), false, false);
            
            while (resultSet.next()) {
                String indexName = resultSet.getString(INDEX_METADATA_INDEX_NAME);
                IndexImpl index = (IndexImpl) temp.get(indexName);
                if (index == null) {
                    index = new IndexImpl(this, indexName);
                    temp.put(indexName, index);
                }
                String columnName = resultSet.getString(INDEX_METADATA_COLUMN_NAME);
                String ascending = resultSet.getString(INDEX_METADATA_ASC_OR_DESC);
                index.addColumn(columnName, ascending == null 
                    ? null : (ascending.toUpperCase().startsWith("A") 
                        ? Boolean.TRUE : Boolean.FALSE));
            }
            resultSet.close();
            indexList.addAll(temp.values());
            
        } catch (NotConnectedException e) {
        } catch (SQLException e) {
        }
        return (Index[]) indexList.toArray(new Index[indexList.size()]);
    }
    
    public Boolean exists() {
        return this.exists;
    }
    
    
    /**
     * @see com.quantum.model.Entity#getQuotedTableName()
     */
    public String getQuotedTableName() {
        return getBookmark().getAdapter().filterTableName(getQualifiedName());
    }

    public ForeignKey[] getExportedKeys() throws SQLException, NotConnectedException {
    	return this.bookmark.getDatabase().getExportedKeys(getSchema(), getName());
    }

    public ForeignKey[] getImportedKeys() throws SQLException, NotConnectedException {
    	return this.bookmark.getDatabase().getImportedKeys(getSchema(), getName());
    }
    public ForeignKey[] getReferences() throws SQLException, NotConnectedException {
    	ForeignKey[] importedKeys = getImportedKeys();
    	ForeignKey[] exportedKeys = getExportedKeys();
    	
    	List list = new ArrayList(); // if we could guarantee JDK 1.4, we'd use LinkedHashSet 
    	for (int i = 0, length = importedKeys == null ? 0 : importedKeys.length; i < length; i++) {
			list.add(importedKeys[i]);
		}
    	for (int i = 0, length = exportedKeys == null ? 0 : exportedKeys.length; i < length; i++) {
    		if (!list.contains(exportedKeys[i])) {
    			list.add(exportedKeys[i]);
    		}
		}
    	return (ForeignKey[]) list.toArray(new ForeignKey[list.size()]);
    }
    
    public int compareTo(Object object) {
		Entity that = (Entity) object;
		if (that.getQualifiedName() == null && this.getQualifiedName() != null) {
			return 1;
		} else if (this.getQualifiedName() == null && that.getQualifiedName() != null) {
			return -1;
		} else if (this.getQualifiedName() == null && that.getQualifiedName() == null) {
			return 0;
		} else {
			return this.getQualifiedName().compareTo(that.getQualifiedName());
		}
	}
    
    /**
	 * @return Returns the isSynonym.
	 */
	public boolean isSynonym() {
		return isSynonym;
	}
	
}