/*
 The Broad Institute
 SOFTWARE COPYRIGHT NOTICE AGREEMENT
 This software and its documentation are copyright (2006) by the
 Broad Institute/Massachusetts Institute of Technology. All rights are
 reserved.

 This software is supplied without any warranty or guaranteed support
 whatsoever. Neither the Broad Institute nor MIT can be responsible for its
 use, misuse, or functionality.
 */

package calhoun.gebo.internal.db;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import calhoun.gebo.model.CompoundFeature;
import calhoun.gebo.model.Criterion;
import calhoun.gebo.model.Feature;
import calhoun.gebo.model.FeatureTrack;
import calhoun.gebo.model.Identified;
import calhoun.gebo.model.Propertied;
import calhoun.gebo.model.Property;
import calhoun.gebo.model.Segment;
import calhoun.gebo.model.Sequence;
import calhoun.gebo.model.SequenceGroup;
import calhoun.gebo.model.SubFeature;
import calhoun.gebo.model.ThinSubFeature;
import calhoun.gebo.util.C;
import calhoun.gebo.util.Informer;
import calhoun.gebo.util.Q;

/**
 * Calhoun specific sql shared by CalhounTrackManager, CalhounSequenceManager,
 * and CalhounFeatureFinder.
 * 
 * @author <a href="mailto:reinhard@genome.wi.mit.edu">Reinhard Engels </a>
 */
public abstract class CalhounGuts extends Identified implements Propertied {

    private static Informer I = new Informer(CalhounGuts.class);

    // /////////////////////////////////////////////////////////////////////////
    // Constructors.
    // /////////////////////////////////////////////////////////////////////////

    /**
     * Creates a new <code>CalhounGuts</code> instance.
     * 
     * @param id
     *            a <code>String</code> value
     * @param label
     *            a <code>String</code> value
     * @param connection
     *            a <code>Connection</code> value
     */
    protected CalhounGuts(String id, String label) {
        super(id, label);
        m_id = id;
        m_label = label;
        setConnectionManager();
    }

    // /////////////////////////////////////////////////////////////////////////
    // Propertied Interface.
    // /////////////////////////////////////////////////////////////////////////

    public Property[] getProperties() {
        return new Property[] { new Property("ID", this.getId()),
                new Property("Label", this.getLabel()), };
    }

    // /////////////////////////////////////////////////////////////////////////
    // Protected Methods.
    // /////////////////////////////////////////////////////////////////////////

    protected String getCriteriaSql(Criterion[] criteria) {
        return getCriteriaSql(criteria, null);
    }

    protected String getCriteriaSql(Criterion[] criteria, String alias) {
        StringBuilder b = new StringBuilder();
        for (Criterion c : criteria) {
            String prefix = alias == null ? "" : alias + ".";
            String suffix = "";
            if (!c.getValue().equals("")) {
                b.append(c.toSql(alias));
            }
        }
        return b.toString();
    }

    protected Feature getFeature(CalhounSequenceManager sm, String id) {
        Feature[] features = getFeatures(sm, new String[] { id });
        return features.length > 0 ? features[0] : null;
    }

    private CalhounTrackType getTrackType(String subclass, String ontologyTermId) {
        CalhounTrackType[] trackTypes = CalhounTrackManager.getInstance()
                .getTrackTypes();
        for (int i = 0; i < trackTypes.length; i++) {
            if (trackTypes[i].getSubclass().equals(subclass)
                    && trackTypes[i].getOntologyTerm().getId().equals(
                            ontologyTermId)) {
                return trackTypes[i];
            }
        }
        return CalhounTrackType.getGenericTrackType();
        // I.warn("Unable to get track type for: " + subclass + ","
        // + ontologyTermId);
        // return null;
    }

    private Object[] getTrackAndParentId(String view, String column,
            String featureId) throws SQLException {
        Statement statement = CalhounConnectionManager.getInstance()
                .getConnection().createStatement();
        CalhounTrackType trackType = null;
        CalhounTrack track = null;
        String sql = "SELECT ap_subclass,ap_ontology_term_id,ap_analysis_event_id, ap_parent_feature_id FROM "
                + view + " WHERE " + column + " = " + featureId;
        Q.startStopWatch(sql);
        ResultSet resultSet = statement.executeQuery(sql);
        String parentId = "";
        String analysisEventId = null;
        if (resultSet.next()) {
            // resultSet.getString(1),
            // resultSet.getString(2)
            trackType = getTrackType(resultSet.getString(1), resultSet
                    .getString(2));
            analysisEventId = resultSet.getString(3);
            parentId = resultSet.getString(4);
        } else {
            I.warn("NO RESULTS FOR " + sql);
        }
        Q.stopStopWatch(sql);
        statement.close();
        resultSet.close();
        // I.warn("Track Type: " + trackType);

        if (trackType != null) {
            track = (CalhounTrack) CalhounTrackManager.getInstance().getTrack(
                    trackType.getId() + "_" + analysisEventId);
        }
        return new Object[] { track, parentId };
    }

    protected Feature[] getFeatures(CalhounSequenceManager sm, String[] ids) {
        I.warn("getFeatures" + ids[0]);
        List<Feature> features = C.list();
        for (String id : ids) {
            List featuresForThisId = new ArrayList<CompoundFeature>();
            if (id.matches("^\\d+$")) {
                featuresForThisId.addAll(getFeaturesById(id));
            } else {
                featuresForThisId.addAll(getFeaturesByAssignedId(id));
                // it is so annoying that I have to do this.
                if (featuresForThisId.size() == 0) {
                    featuresForThisId.addAll(getFeaturesByEukGeneAccession(id));
                }
                if (featuresForThisId.size() == 0) {
                    featuresForThisId.addAll(getFeaturesByProGeneAccession(id));
                }
                if (featuresForThisId.size() == 0 && id.matches(".*_.*")) {
                    featuresForThisId.addAll(getFeaturesByParentAssignedId(id));
                }
                if (featuresForThisId.size() == 0) {
                    featuresForThisId.add(CalhounFeatureFinder.getInstance()
                            .getFeatureByLocus(id));
                }
            }
            features.addAll(featuresForThisId);
        }
        return (Feature[]) features.toArray(new Feature[0]);
    }

    private List<Feature> getFeaturesByProGeneAccession(String id) {
        String sql = "select ap_feature_id from ap_gene where UPPER(ap_gene_accession_number) = ?";
        List<Feature> features = C.list();
        List<String> featureIds = C.list();
        Q.startStopWatch(sql);
        try {
            PreparedStatement statement = getConnection().prepareStatement(sql);
            statement.setString(1, id.toUpperCase());
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                featureIds.add(resultSet.getString("AP_FEATURE_ID"));
            }
            // U.success( "Found feature: " + feature );
            Q.stopStopWatch(sql);
            statement.close();
            resultSet.close();
        } catch (SQLException e) {
            I.error("Error getting features by prokaryotic gene accession: "
                    + id, e);
        }
        for (String f : featureIds) {
            features.addAll(getFeaturesById(f));
        }
        return features;
    }

    private List<Feature> getFeaturesByEukGeneAccession(String id) {
        String sql = "select ap_feature_id from ap_annotated_transcript where UPPER(ap_gene_accession_number) = ?";
        List<Feature> features = C.list();
        List<String> transcriptIds = C.list();
        Q.startStopWatch(sql);
        try {
            PreparedStatement statement = getConnection().prepareStatement(sql);
            statement.setString(1, id.toUpperCase());
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                transcriptIds.add(resultSet.getString("AP_FEATURE_ID"));
            }
            // U.success( "Found feature: " + feature );
            Q.stopStopWatch(sql);
            statement.close();
            resultSet.close();
        } catch (SQLException e) {
            I.error("Error getting features by eukaryotic gene accession: "
                    + id, e);
        }
        for (String t : transcriptIds) {
            features.addAll(getFeaturesById(t));
        }
        return features;
    }

    public List<Feature> getFeaturesByParentAssignedId(String parentAssignedId) {
        return getFeaturesBySqlWhere("F.ap_parent_feature_id in (select G.ap_id from ap_feature G where ap_assigned_id like '"
                + parentAssignedId + "%')");
    }

    public List<Feature> getFeaturesByParentId(String parentId) {
        return getFeaturesBySqlWhere("F.ap_parent_feature_id =  " + parentId);
    }

    public List<Feature> getFeaturesById(String id) {
        // I.warn("getFeaturesById: " + id);
        return getFeaturesBySqlWhere("F.ap_id = " + id);
    }

    public List<Feature> getFeaturesByAssignedId(String id) {
        // I.warn("getFeaturesByAssignedId: " + id);
        return getFeaturesBySqlWhere("UPPER( F.ap_assigned_id ) = ? ", id
                .toUpperCase());
    }

    public List<Feature> getFeaturesBySqlWhere(String where) {
        return getFeaturesBySqlWhere(where, new Object[] {});
    }

    public List<Feature> getFeaturesBySqlWhere(String where, Object param) {
        return getFeaturesBySqlWhere(where, new Object[] { param });
    }

    public List<Feature> getFeaturesBySqlWhere(String where, Object[] params) {
        // I.warn("getFeaturesBySqlWhere: " + where + " " + params);
        List<Feature> features = C.list();
        String sql = getApFeatureSelect() + " where " + where;
        Q.startStopWatch(sql);
        try {
            PreparedStatement statement = getConnection().prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                // I.warn("attaching param: " + params[i]);
                if (params[i] instanceof String) {
                    statement.setString(i + 1, (String) params[i]);
                } else if (params[i] instanceof Long) {
                    statement.setLong(i + 1, (Long) params[i]);
                }
            }
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                if (resultSet.getString("AP_SUBCLASS").matches("EGENE")) {
                    features.addAll(getFeaturesByParentId(resultSet
                            .getString("AP_FEATURE_ID")));
                } else if (resultSet.getString("AP_SUBCLASS").matches(
                        "ALSEG|EXON")) {
                    features.addAll(getFeaturesById(resultSet
                            .getString("AP_PARENT_FEATURE_ID")));
                } else {
                    Sequence sequence = CalhounSequenceManager.getInstance()
                            .getSequence(resultSet.getString("AP_SEQUENCE_ID"));
                    String analysisEventId = resultSet
                            .getString("AP_ANALYSIS_EVENT_ID");
                    CalhounTrackType trackType = getTrackType(resultSet
                            .getString("AP_SUBCLASS"), resultSet
                            .getString("AP_ONTOLOGY_TERM_ID"));
                    if (trackType == null) {
                        I.warn("Unable to get track type for sql " + sql);
                        continue;
                    }
                    CalhounTrack track = (CalhounTrack) CalhounTrackManager
                            .getInstance().getTrack(
                                    trackType.getId() + "_" + analysisEventId);
                    Feature feature = createFeature(sequence, track, resultSet);
                    // attach subfeatures.
                    Map featureById = new HashMap();
                    featureById.put(feature.getId(), feature);
                    String componentSql = generateAddComponentFeaturesSqls(
                            track, featureById.keySet())[0];
                    executeSubFeatureSqlAndAttachToParent(componentSql,
                            featureById, feature, feature.getTrack());
                    features.add(feature);
                }
            }
            // U.success( "Found feature: " + feature );
            Q.stopStopWatch(sql);
            statement.close();
            resultSet.close();
        } catch (SQLException e) {
            I.error("Error getting features by sql \n" + sql, e);
        }
        return features;
    }

    protected String getApFeatureSelect() {
        return "SELECT F.ap_start,F.ap_stop,F.ap_id as ap_feature_id,F.ap_strand,F.ap_name,F.ap_assigned_id,ap_stop - ap_start as ap_length,F.ap_sequence_id,F.ap_analysis_event_id,F.ap_subclass,F.ap_ontology_term_id,F.ap_parent_feature_id FROM ap_feature F ";
    }

    protected String getSequenceGroupSql(SequenceGroup[] sequenceGroups) {
        return getSequenceGroupSql(sequenceGroups, null);
    }

    /**
     * Get sql substring to limit by sequence group.
     * 
     * @param sequenceGroups
     *            a <code>SequenceGroup[]</code> value
     * @return a <code>String</code> value
     */
    protected String getSequenceGroupSql(SequenceGroup[] sequenceGroups,
            String alias) {
        StringBuffer buffer = new StringBuffer("");
        alias = alias == null ? "" : alias + ".";
        for (int i = 0; i < sequenceGroups.length; i++) {
            buffer.append("'" + sequenceGroups[i].getId() + "'");
            if (i < sequenceGroups.length - 1) {
                buffer.append(", ");
            }
        }
        if (sequenceGroups.length > 0) {
            buffer.insert(0, " AND " + alias + "ap_group IN ( ");
            buffer.append(" ) ");
        }
        return buffer.toString();
    }

    /**
     * Get sql substring to limit by track.
     * 
     * @param tracks
     *            a <code>Track[]</code> value
     * @return a <code>String</code> value
     */
    protected String getTrackSql(FeatureTrack[] tracks) {
        StringBuffer buffer = new StringBuffer("");
        for (int i = 0; i < tracks.length; i++) {
            buffer.append("'" + tracks[i].getId() + "'");
            if (i < tracks.length - 1) {
                buffer.append(", ");
            }
        }
        if (tracks.length > 0) {
            buffer.insert(0, " AND ap_subtype IN ( ");
            buffer.append(" ) ");
        }
        return buffer.toString();
    }

    public boolean isConnected() {
        return CalhounConnectionManager.getInstance().isConnected();
    }

    public void setConnectionManager() {
        CalhounConnectionManager.getInstance();
        if (!CalhounConnectionManager.getInstance().isConnected()) {
            return;
        }
        try {
            // m_connection = ;
            CalhounConnectionManager.getInstance().getConnection()
                    .setAutoCommit(false);
            m_metaData = CalhounConnectionManager.getInstance().getConnection()
                    .getMetaData();
            m_connected = true;
            m_username = CalhounConnectionManager.getInstance().getUsername();
        } catch (Exception e) {
            I.error("Error setting Connection Manager", e);
        }
    }

    // /////////////////////////////////////////////////////////////////////////
    // Stuff ripped out of calhoun trackmanager in a hacky hurry.
    // /////////////////////////////////////////////////////////////////////////

    // We return an array of sql strings because of the oracle limit
    // of 1000 elements in an IN clause. If we have more than 1000
    // parent ids, we need to return more than one statement.

    protected String[] generateAddComponentFeaturesSqls(CalhounTrack track,
            Collection parentIds) {
        List sqlList = new ArrayList();
        Iterator i = parentIds.iterator();
        while (i.hasNext()) {
            StringBuilder b = new StringBuilder();
            for (int parentCount = 0; i.hasNext() && parentCount < 1000; parentCount++) {
                String parentId = i.next() + ",";
                b.append(parentId);
            }
            String sql = "SELECT ap_parent_feature_id, ap_id, ap_name, ap_start, ap_stop FROM "
                    + " ap_feature WHERE AP_PARENT_FEATURE_ID IN ("
                    + b
                    + ") order by ap_start";
            sql = sql.replaceFirst(",\\)", ")");
            sqlList.add(sql);
        }
        return (String[]) sqlList.toArray(new String[0]);
    }

    protected void executeSubFeatureSqlAndAttachToParent(String sql,
            Map featureById, Segment segment, FeatureTrack track)
            throws SQLException {
        Q.startStopWatch(sql);
        Statement statement = CalhounConnectionManager.getInstance()
                .getConnection().createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            Feature parent = (Feature) featureById.get(resultSet.getString(1));
            // SubFeature child = new FatSubFeature(segment.getSequence(),
            // resultSet
            // .getString(3), resultSet.getString(2), resultSet.getInt(4),
            // resultSet.getInt(5), track.getComponentTrack(null), parent
            // .getStrand(), parent);
            SubFeature child = new ThinSubFeature(parent, resultSet.getInt(4),
                    resultSet.getInt(5), resultSet.getString(2), resultSet
                            .getString(3));
            parent.addSubFeature(child);
        }
        statement.close();
        resultSet.close();
        Q.stopStopWatch(sql);
    }

    public CompoundFeature createFeature(Sequence sequence, CalhounTrack track,
            ResultSet resultSet) throws SQLException {
        CalhounFeature feature;
        if (track.getTrackType().getView().equals(AP.ANNOTATED_TRANSCRIPT_VIEW)) {
            feature = new AnnotatedTranscript(sequence, track, resultSet, 0);
        } else if (track.getTrackType().getView() == AP.UTR_ELEMENT_VIEW) {
            feature = new PolyASignal(sequence, track, resultSet);
        } else if (track.getTrackType().getView().equals(AP.PRO_GENE_VIEW_NEW)) {
            feature = new ProGeneNew(sequence, track, resultSet, 0);
        } else if (track.getTrackType().getSubclass().equals("TRNSCR")
                && track.getTrackType().getOntologyTerm().getId().equals("674")) {
            feature = new CalhounTranscript(sequence, track, resultSet);
        } else if (track.getTrackType().getSubclass().matches("VF|NQSB")) { // |NQSB
            feature = new CalhounValuedFeature(sequence, track, resultSet);
        } else if (track.getTrackType().getSubclass().equals("ALSPN")) {
            feature = new AlignmentSpan(sequence, track, resultSet);
        } else {
            feature = new CalhounFeature(sequence, track, resultSet, false,
                    false, 0);
        }
        // if ( track.get)
        return feature;
    }

    public Feature createSimpleFeature(Sequence sequence, CalhounTrack track,
            ResultSet resultSet) throws SQLException {
        return new CalhounFeature(sequence, track, resultSet, false, false, 0);
    }

    protected Connection getConnection() {
        return CalhounConnectionManager.getInstance().getConnection();
    }

    // private void

    public DatabaseMetaData getMetaData() {
        if (m_metaData == null) {
            setConnectionManager();
        }
        return m_metaData;
    }

    // /////////////////////////////////////////////////////////////////////////
    // Data.
    // /////////////////////////////////////////////////////////////////////////

    protected Map m_trackById = new HashMap();
    // these are all members of above. could get rid of them...
    // protected Connection m_connection;
    protected String m_username;
    protected DatabaseMetaData m_metaData;
    protected boolean m_connected;
}