package com.quantum.model;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import com.quantum.adapters.DatabaseAdapter;
import com.quantum.sql.MultiSQLServer;
import com.quantum.sql.SQLResultSetResults;

/**
 * @author BC
 */
public class Database {
    
	private static final int TABLE_METADATA_TABLE_SCHEM = 2;
	private static final int TABLE_METADATA_TABLE_NAME = 3;
	
	private static final int TABLE_TYPE_METADATA_TABLE_TYPE = 1;

	private static final int FOREIGN_KEY_METADATA_PKTABLE_SCHEM = 2;
	private static final int FOREIGN_KEY_METADATA_PKTABLE_NAME = 3;
	private static final int FOREIGN_KEY_METADATA_PKCOLUMN_NAME = 4;
	private static final int FOREIGN_KEY_METADATA_FKTABLE_SCHEM = 6;
	private static final int FOREIGN_KEY_METADATA_FKTABLE_NAME = 7;
	private static final int FOREIGN_KEY_METADATA_FKCOLUMN_NAME = 8;
	private static final int FOREIGN_KEY_METADATA_KEY_SEQ = 9;
	private static final int FOREIGN_KEY_METADATA_DELETE_RULE = 11;
	private static final int FOREIGN_KEY_METADATA_FK_NAME = 12;
	
	private static final int TYPE_INFO_METADATA_TYPE_NAME = 1;
	private static final int TYPE_INFO_METADATA_DATA_TYPE = 2;
	private static final int TYPE_INFO_METADATA_PRECISION = 3;
	private static final int TYPE_INFO_METADATA_LITERAL_PREFIX = 4;
	private static final int TYPE_INFO_METADATA_LITERAL_SUFFIX = 5;
	private static final int TYPE_INFO_METADATA_CREATE_PARMS = 6;
	
	private DatabaseAdapter databaseAdapter;
    private Bookmark bookmark;
    
    private List entityTypes;
    
    public Database(Bookmark bookmark) {
        this.bookmark = bookmark;
        this.databaseAdapter = bookmark.getAdapter();
    }

    private static final String[] ALL_TABLE_TYPES = { 
        Entity.TABLE_TYPE, 
        Entity.VIEW_TYPE, 
        Entity.SEQUENCE_TYPE };
        
    private static final List STANDARD_TABLE_TYPES = 
        Collections.synchronizedList(new ArrayList());
        
    static {
        for (int i = 0, length = (ALL_TABLE_TYPES == null) ? 0 : ALL_TABLE_TYPES.length;
            i < length;
            i++) {
            STANDARD_TABLE_TYPES.add(ALL_TABLE_TYPES[i]);
        }
    }
    
    public synchronized String[] getEntityTypes() 
        throws NotConnectedException, SQLException {
    	if (this.entityTypes == null) {
    		Collection collection = initializeEntityTypes(this.bookmark.getConnection());
            this.entityTypes = Collections.synchronizedList(new ArrayList(collection));
    	}
        return (String[]) this.entityTypes.toArray(new String[this.entityTypes.size()]);
    }
    
    public String getUsername() throws NotConnectedException, SQLException {
    	return getMetaData().getUserName();
    }


    /**
     * <p>This method returns a set of entity types supported by the database
     * adapter.  This list will always be limited to Tables, Views and 
     * Sequences.</p>
     * 
     * <p>Not all databases support all types.  MySQL only supports 
     * Tables.  Informix supports Tables and Views.  Oracle and DB2 support
     * Tables, Views and Sequences.</p>
     * 
     * @param connection
     * @return a set of Strings, typically "TABLE", "VIEW", and "SEQUENCE"
     * @throws SQLException
     */
    private Set initializeEntityTypes(Connection connection) throws SQLException {
        
        Set set = new HashSet();
        if (this.databaseAdapter.getShowTableQuery(this.bookmark.getUsername()) != null) {
            set.add(Entity.TABLE_TYPE);
        } else if (this.databaseAdapter.getShowViewQuery(this.bookmark.getUsername()) != null) {
            set.add(Entity.VIEW_TYPE);
        } else if (this.databaseAdapter.getShowSequenceQuery(this.bookmark.getUsername()) != null) {
            set.add(Entity.SEQUENCE_TYPE);
        }
        
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet resultSet = metaData.getTableTypes();
        while (resultSet.next()) {
            String type = resultSet.getString(TABLE_TYPE_METADATA_TABLE_TYPE);
            if (type != null) {
            	// Informix, in particular, pads this with extra spaces
                type = type.trim();
            }
            if (STANDARD_TABLE_TYPES.contains(type)) {
                set.add(type);
            }
        }
        return set;
    }

    public String getInformation() throws SQLException {
        try {
            DatabaseMetaData metaData = getMetaData();
        
            return metaData == null ? null : metaData.getDatabaseProductName() + " " 
                    + metaData.getDatabaseProductVersion();
        } catch (NotConnectedException e) {
            // TODO: think about this...
            return "";
        }
    }
    
    /**
     * Get a list of entities (tables, views, sequences) for a particular
     * bookmark. This function is usually not redefined because it gives
     * an external interface. You will usually redefine the getShowTableQuery(), 
     * getShowViewQuery(), etc.
     * 
     * @param bookmark -
     *     the bookmark that describes the database that is being accessed.
     * @param passwordFinder -
     *     a utility class that knows how to obtain a password, if required
     * @param schema -
     *     the schema from which to extract
     * @param type -
     *     the type ("VIEW", "TABLE", etc.) of entities to extract or null
     *     if all entity types should be extracted
     * @return 
     *     an array of entity objects representing the tables, views and sequences.
     * @throws SQLException
     */
    public Entity[] getEntities(Bookmark bookmark, Schema schema, String type) 
        throws SQLException, NotConnectedException {
        Connection connection = bookmark.getConnection();
        Entity[] result = getEntities(bookmark, connection, schema, type);
        return (result == null) ? new Entity[0] : result;
    }
    
    protected Entity[] getEntities(Bookmark bookmark, Connection connection, Schema schema, String type) 
        throws SQLException {
        
        List list = new ArrayList();
        String[] types = (type == null) ? ALL_TABLE_TYPES : new String[] { type };
            
        for (int i = 0; i < types.length; i++) {
            list.addAll(getEntitiesList(bookmark, connection, types[i], schema));
            // TODO: This should be polished so that synonyms can be shown with different icons as regular Entities
            list.addAll(getSynonymsList(bookmark, connection, types[i], schema));
        }

        return (Entity[]) list.toArray(new Entity[list.size()]);
    }

    protected List getEntitiesList(Bookmark bookmark, Connection connection, String type, Schema schema)
        throws SQLException {

        List list = new ArrayList();
        //      We try first the JDBC driver
        DatabaseMetaData metaData = connection.getMetaData();
        // getTables needs a null schema to get all the schemas. So we don't pass a "" schema, but a null one
		ResultSet set = null;
		if (metaData.supportsSchemasInTableDefinitions()) {
	        set = metaData.getTables(null, (schema != null) ? schema.getName() : null, "%", new String[] { type });
		} else {
			set = metaData.getTables(null, null, "%", new String[] { type });
		}
	    
        while (set.next()) {
            String tempSchema = set.getString(TABLE_METADATA_TABLE_SCHEM);
            tempSchema = (tempSchema == null) ? "" : tempSchema.trim();
            String tableName = set.getString(TABLE_METADATA_TABLE_NAME);
            tableName = (tableName == null) ? "" : tableName.trim();

            if (tableName != null && tableName.length() > 0) {
                Entity entity = EntityFactory.getInstance().create(bookmark, tempSchema, tableName, type, false);
                if (entity != null) {
                    list.add(entity);
                }
            }
        }
        set.close();
        // If we have some results, then the JDBC driver is working, 
        // so we return the results and quit
        if (list.size() > 0) 
        	return list;
        
        
        // If no results, we check also the sql query to get the list of entities
        SQLResultSetResults results = null;
        // Get the proper sql query to the appropiate type of entity
        String sql = getSQL(bookmark, type, schema);
        // If nothing returned, too bad, it seems there is no sql query for that database and entity type
        if (sql != null) {
            results = (SQLResultSetResults) MultiSQLServer.getInstance().execute(
            		bookmark, connection, sql, Integer.MAX_VALUE);
            for (int i = 1, size = (results == null) ? 0 : results.getRowCount(); i <= size; i++) {
                String schemaName = results.getColumnCount() == 1 
                    ? schema.getName() : results.getElement(1, i).toString();
                if (schemaName != null) {
                	schemaName = schemaName.trim();
                }
                String tableName = results.getColumnCount() == 1 
                    ? results.getElement(1, i).toString() 
                    : results.getElement(2, i).toString();
                if (tableName != null && tableName.length() > 0) {
                    Entity entity = EntityFactory.getInstance().create(
                        bookmark, schemaName, tableName, type, false);
                    if (entity != null) {
                        list.add(entity);
                    }
                }
            }
        }
        return list;
    }
    /**
     * Returns a list with the synonym objects of the given type, using a query  
     * @param bookmark
     * @param connection
     * @param type
     * @param schema
     * @return
     * @throws SQLException
     */
    protected List getSynonymsList(Bookmark bookmark, Connection connection, String type, Schema schema)
    throws SQLException {

    List list = new ArrayList();
    //      We try first the JDBC driver
    DatabaseMetaData metaData = connection.getMetaData();
    SQLResultSetResults results = null;
    // Get the proper sql query to the appropiate type of entity
    String sql = this.databaseAdapter.getShowSynonymsQuery(schema.getName(), type);
    // If nothing returned, too bad, it seems there is no sql query for that database and entity type
    if (sql != null) {
        results = (SQLResultSetResults) MultiSQLServer.getInstance().execute(
        		bookmark, connection, sql, Integer.MAX_VALUE);
        for (int i = 1, size = (results == null) ? 0 : results.getRowCount(); i <= size; i++) {
            String schemaName = results.getColumnCount() == 1 
                ? schema.getName() : results.getElement(1, i).toString();
            if (schemaName != null) {
            	schemaName = schemaName.trim();
            }
            String tableName = results.getColumnCount() == 1 
                ? results.getElement(1, i).toString() 
                : results.getElement(2, i).toString();
            if (tableName != null && tableName.length() > 0) {
                Entity entity = EntityFactory.getInstance().create(
                    bookmark, schemaName, tableName, type, true);
                if (entity != null) {
                    list.add(entity);
                }
            }
        }
    }
    return list;
}
    
    public DataType[] getTypes() throws NotConnectedException, SQLException {
    	DatabaseMetaData metaData = getMetaData();
    	List list = new ArrayList();
    	ResultSet results = metaData.getTypeInfo();
    	try {
	    	while (results.next()) {
	    		list.add(new DataType(
	    				results.getInt(TYPE_INFO_METADATA_DATA_TYPE),
	    				results.getString(TYPE_INFO_METADATA_TYPE_NAME),
						results.getLong(TYPE_INFO_METADATA_PRECISION),
						results.getString(TYPE_INFO_METADATA_LITERAL_PREFIX),
						results.getString(TYPE_INFO_METADATA_LITERAL_SUFFIX),
						results.getString(TYPE_INFO_METADATA_CREATE_PARMS)
						));
	    	}
    	} finally {
    		results.close();
    	}
    	return (DataType[]) list.toArray(new DataType[list.size()]);
    }


    /**
	 * @return
	 * @throws NotConnectedException
	 * @throws SQLException
	 */
	private DatabaseMetaData getMetaData() throws NotConnectedException, SQLException {
		Connection connection = this.bookmark.getConnection();
    	DatabaseMetaData metaData = connection.getMetaData();
		return metaData;
	}

	private String getSQL(Bookmark bookmark, String type, Schema schema) {
        if (Entity.TABLE_TYPE.equals(type)) {
            return this.databaseAdapter.getShowTableQuery(schema.getName());
        } else if (Entity.VIEW_TYPE.equals(type)) {
            return this.databaseAdapter.getShowViewQuery(schema.getName());
        } else if (Entity.SEQUENCE_TYPE.equals(type)) {
            return this.databaseAdapter.getShowSequenceQuery(schema.getName());
        } else {
            return null;
        }
    }
	
	public ForeignKey[] getExportedKeys(String schema, String entityName) 
			throws NotConnectedException, SQLException {
		DatabaseMetaData metaData = getMetaData();
		List list = new ArrayList();
		return getForeignKeys(list, metaData.getExportedKeys(null, schema, entityName));
	}

	public ForeignKey[] getImportedKeys(String schema, String entityName) 
			throws NotConnectedException, SQLException {
		DatabaseMetaData metaData = getMetaData();
		List list = new ArrayList();
		return getForeignKeys(list, metaData.getImportedKeys(null, schema, entityName));
	}

	/**
	 * @param list
	 * @param resultSet
	 * @return
	 * @throws SQLException
	 */
	private ForeignKey[] getForeignKeys(List list, ResultSet resultSet) throws SQLException {
		ForeignKeyImpl foreignKey = null;
		
		int lowestKeySequence = Integer.MAX_VALUE;
		try {
			while (resultSet.next()) {
				int keySequence = resultSet.getInt(FOREIGN_KEY_METADATA_KEY_SEQ);
				lowestKeySequence = Math.min(lowestKeySequence, keySequence);
				
				if (keySequence == lowestKeySequence || foreignKey == null) {
					foreignKey = new ForeignKeyImpl();
					list.add(foreignKey);
					foreignKey.setName(resultSet.getString(FOREIGN_KEY_METADATA_FK_NAME));
					foreignKey.setDeleteRule(resultSet.getShort(FOREIGN_KEY_METADATA_DELETE_RULE));
					foreignKey.setForeignEntitySchema(resultSet.getString(FOREIGN_KEY_METADATA_FKTABLE_SCHEM));
					foreignKey.setForeignEntityName(resultSet.getString(FOREIGN_KEY_METADATA_FKTABLE_NAME));
					foreignKey.setLocalEntitySchema(resultSet.getString(FOREIGN_KEY_METADATA_PKTABLE_SCHEM));
					foreignKey.setLocalEntityName(resultSet.getString(FOREIGN_KEY_METADATA_PKTABLE_NAME));
				}
				
				foreignKey.addColumns(
						resultSet.getString(FOREIGN_KEY_METADATA_PKCOLUMN_NAME), 
						resultSet.getString(FOREIGN_KEY_METADATA_FKCOLUMN_NAME));
			}
			return (ForeignKey[]) list.toArray(new ForeignKey[list.size()]);
		} finally {
			resultSet.close();
		}
	}

	/**
	 * @return
	 * @throws SQLException
	 * @throws NotConnectedException
	 */
	public Schema[] getSchemas() throws NotConnectedException, SQLException {
		DatabaseMetaData metaData = getMetaData();
		List list = new ArrayList();
		
		if (metaData.supportsSchemasInTableDefinitions()) {
			ResultSet resultSet = metaData.getSchemas();
			try {
				while (resultSet.next()) {
					String schemaName = resultSet.getString("TABLE_SCHEM");
					list.add(new Schema(schemaName));
				}
			} finally {
				resultSet.close();
			}
		}
		return (Schema[]) list.toArray(new Schema[list.size()]);
	}
}