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.IQuantumConstants;
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_TYPES = { 
        IQuantumConstants.Table, 
        IQuantumConstants.View, 
        IQuantumConstants.Sequence };
        
    private static final List STANDARD_TABLE_TYPES = 
        Collections.synchronizedList(new ArrayList());
        
    static {
        for (int i = 0, length = (ALL_TYPES == null) ? 0 : ALL_TYPES.length;
            i < length;
            i++) {
            STANDARD_TABLE_TYPES.add(ALL_TYPES[i]);
        }
    }
    
    public String[] getEntityTypes() 
        throws NotConnectedException, SQLException {
        return getEntityTypes(this.bookmark.getConnection(), this.bookmark.getSchemas()[0]);
    }


    /**
     * <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
     * @param schema -
     *      This parameter is somewhat bogus.  It is used to determine if the
     *      adapter defines an SQL statement for finding entities of a 
     *      particular types.  
     * @return
     * @throws SQLException
     */
    public String[] getEntityTypes(Connection connection, Schema schema) 
        throws SQLException {
        
        Set set = new HashSet();
        if (this.databaseAdapter.getShowTableQuery(schema.getName(), false) != null) {
            set.add(IQuantumConstants.Table);
        } else if (this.databaseAdapter.getShowViewQuery(schema.getName(), false) != null) {
            set.add(IQuantumConstants.View);
        } else if (this.databaseAdapter.getShowSequenceQuery(schema.getName(), false) != null) {
            set.add(IQuantumConstants.Sequence);
        }
        
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet resultSet = metaData.getTableTypes();
        while (resultSet.next()) {
            String type = resultSet.getString("TABLE_TYPE");
            if (type != null) {
                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 {
            Connection connection = this.bookmark.getConnection();
            DatabaseMetaData metaData = connection.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_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;
    }


    private String getSQL(Bookmark bookmark, String type, Schema schema) {
        if (Entity.TABLE_TYPE.equals(type)) {
            return this.databaseAdapter.getShowTableQuery(schema.getName(), schema.isDefault());
        } else if (Entity.VIEW_TYPE.equals(type)) {
            return this.databaseAdapter.getShowViewQuery(schema.getName(), schema.isDefault());
        } else if (Entity.SEQUENCE_TYPE.equals(type)) {
            return this.databaseAdapter.getShowSequenceQuery(schema.getName(), schema.isDefault());
        } else {
            return null;
        }
    }
    

}