package net.sourceforge.phpdt.sql.adapters;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import net.sourceforge.phpdt.sql.bookmarks.Bookmark;
import net.sourceforge.phpdt.sql.model.Entity;
import net.sourceforge.phpdt.sql.model.EntityFactory;
import net.sourceforge.phpdt.sql.sql.DbElementsConstants;
import net.sourceforge.phpdt.sql.sql.MultiSQLServer;
import net.sourceforge.phpdt.sql.sql.SQLHelper;
import net.sourceforge.phpdt.sql.sql.SQLResults;

/**
 * @author root
 * Abstract base class for all the adapter classes
 */
public abstract class DatabaseAdapter {
    
	public abstract String getShowTableQuery(DatabaseInfo info);
	public String getShowViewQuery(DatabaseInfo info) {
		return null;
	}
	public String getShowSequenceQuery(DatabaseInfo info) {
        return null;
	}
	public final String getShowTableQuery(DatabaseInfo info, String table) {
		String schema = info.getSchema();
		if (schema.equals("")) { //$NON-NLS-1$
			return "SELECT * FROM " + table; //$NON-NLS-1$
		} else {
			return "SELECT * FROM " + info.getSchema() + "." + table; //$NON-NLS-1$ //$NON-NLS-2$
		}
	}
    
    
    
    // BCH: Not used
//	public final String getTableCountQuery(DatabaseInfo info, String table) {
//		String schema = info.getSchema();
//		if (schema.equals("")) { //$NON-NLS-1$
//			return "SELECT COUNT(*) FROM " + table; //$NON-NLS-1$
//		} else {
//			return "SELECT COUNT(*) FROM " + info.getSchema() + "." + table; //$NON-NLS-1$ //$NON-NLS-2$
//		}
//	}
	public String getNextValue(String sequence) {
        return null;
	}
	//Doesn't seem to be used at the moment
//	public String getTableListFilter() {
//		return null;
//	}

	/**
	 * @param table
	 * @return : A query to get an empty ResultSet (null if failed) for that table or view.
	 * Subclassed if needed by the different database adapters
	 */
	public String getEmptySetQuery(String table){
		return "SELECT * FROM " + table + " WHERE (1 = 0)"; //$NON-NLS-1$ //$NON-NLS-2$
	}
    
	/**
	 * Quotes a string according to the type of the column 
	 * @param string to be quoted
	 * @param type according to java.sql.Types
	 * @return
	 */
	public String quote(String string, int type) {
		if (SQLHelper.isText(type)){
			if (string.indexOf('\'') >= 0)
				return '"' + string + '"';
			else
				return "'" + string + "'";						 //$NON-NLS-1$ //$NON-NLS-2$
		}
		else if (type == java.sql.Types.DATE || type == java.sql.Types.TIMESTAMP){		
			string = string.trim();
			String sub = string.substring(string.length() - 2, string.length() - 1);
			if (string.length() > 1 && sub.equals(".")) //$NON-NLS-1$
				string = string.substring(0, string.length() - 2);
			return "'" + string + "'"; //$NON-NLS-1$ //$NON-NLS-2$

		}
		return string;
	}
    
    
    /**
     * Get a list of entities (tables, views, sequences) for a particular
     * bookmark.
     * 
     * @param bookmark -
     *     the bookmark that describes the database that is being accessed.
     * @return 
     *     an array of entity objects representing the tables, views and sequences.
     * @throws SQLException
     */
    public Entity[] getEntities(Bookmark bookmark) throws SQLException {
        Connection connection = bookmark.getConnection();
        Entity[] result = getEntities(bookmark, connection);
        return (result == null) ? new Entity[0] : result;
    }
    
    protected Entity[] getEntities(Bookmark bookmark, Connection connection) throws SQLException {
        
        List list = new ArrayList();
        String[] types = { 
            DbElementsConstants.Table, 
            DbElementsConstants.View, 
            DbElementsConstants.Sequence };
            
        for (int i = 0; i < types.length; i++) {
            list.addAll(getEntitiesList(bookmark, connection, types[i]));
        }

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

    protected List getEntitiesList(Bookmark bookmark, Connection connection, String type) 
        throws SQLException {
            
        String sql = getSQL(bookmark, type);
        List list = new ArrayList();
        
        if (sql != null) {
            SQLResults results = MultiSQLServer.getInstance().execute(connection, sql);
            for (int i = 1, size = (results == null) ? 0 : results.getRowCount(); i <= size; i++) {
                list.add(EntityFactory.getInstance().create(
                    bookmark, bookmark.getSchema(), results.getElement(1, i).toString(), type));
            }
        }
        return list;
    }

    private String getSQL(Bookmark bookmark, String type) {
        DatabaseInfo info = DatabaseInfo.create(bookmark);
        if (DbElementsConstants.Table.equals(type)) {
            return getShowTableQuery(info);
        } else if (DbElementsConstants.View.equals(type)) {
            return getShowViewQuery(info);
        } else if (DbElementsConstants.Sequence.equals(type)) {
            return getShowSequenceQuery(info);
        } else {
            return null;
        }
    }
}