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.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.SQLResults;

/**
 * @author BC
 */
public class Database {
    
    private DatabaseAdapter databaseAdapter;
    private Bookmark bookmark;
    
    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 String[] getEntityTypes() 
        throws NotConnectedException, SQLException {
        return getEntityTypes(this.bookmark.getConnection());
    }
    
    public String getUsername() throws NotConnectedException, SQLException {
    	return getMetaData().getUserName();
    }


    /**
     * <p>This method returns a list 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
     * @throws SQLException
     */
    public String[] getEntityTypes(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");
            if (type != null) {
            	// Informix, in particular, pads this with extra spaces
                type = type.trim();
            }
            if (STANDARD_TABLE_TYPES.contains(type)) {
                set.add(type);
            }
        }
        
        return (String[]) set.toArray(new String[set.size()]);
    }

    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));
        }

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

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

        String sql = getSQL(bookmark, type, schema);
        List list = new ArrayList();
        SQLResults results = null;
        if (sql != null) {
            results = MultiSQLServer.getInstance().execute(connection, sql);
            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();
                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);
                    if (entity != null) {
                        list.add(entity);
                    }
                }
            }
        }
        // If we have some results, we go back
        if (results != null) return list;
        // Else, we try 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_SCHEM");
            tempSchema = (tempSchema == null) ? "" : tempSchema.trim();
            String tableName = set.getString("TABLE_NAME");
            tableName = (tableName == null) ? "" : tableName.trim();

            if (tableName != null && tableName.length() > 0) {
                Entity entity = EntityFactory.getInstance().create(bookmark, tempSchema, tableName, type);
                if (entity != null) {
                    list.add(entity);
                }
            }
        }
        set.close();
        return list;
    }
    
    public DataType[] getTypes() throws NotConnectedException, SQLException {
    	DatabaseMetaData metaData = getMetaData();
    	List list = new ArrayList();
    	ResultSet results = metaData.getTypeInfo();
    	try {
	    	while (results.next()) {
	    		String name = results.getString("TYPE_NAME");
	    		int type = results.getInt("DATA_TYPE");
	    		list.add(new DataType(type, name));
	    	}
    	} 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("KEY_SEQ");
				lowestKeySequence = Math.min(lowestKeySequence, keySequence);
				
				if (keySequence == lowestKeySequence || foreignKey == null) {
					foreignKey = new ForeignKeyImpl();
					list.add(foreignKey);
					foreignKey.setName(resultSet.getString("FK_NAME"));
					foreignKey.setDeleteRule(resultSet.getShort("DELETE_RULE"));
					foreignKey.setForeignEntitySchema(resultSet.getString("FKTABLE_SCHEM"));
					foreignKey.setForeignEntityName(resultSet.getString("FKTABLE_NAME"));
					foreignKey.setLocalEntitySchema(resultSet.getString("PKTABLE_SCHEM"));
					foreignKey.setLocalEntityName(resultSet.getString("PKTABLE_NAME"));
				}
				
				foreignKey.addColumns(
						resultSet.getString("PKCOLUMN_NAME"), 
						resultSet.getString("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();
		ResultSet resultSet = metaData.getSchemas();
		try {
			while (resultSet.next()) {
				String schemaName = resultSet.getString("TABLE_SCHEM");
				list.add(new Schema(schemaName));
			}
			return (Schema[]) list.toArray(new Schema[list.size()]);
		} finally {
			resultSet.close();
		}
	}
}