package org.genemania.dw.db;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.TreeMap;
import org.genemania.dw.entity.EntrezGene;
import org.genemania.dw.entity.ExtResource;
import org.genemania.dw.entity.ExtResourceGene;
import org.genemania.dw.entity.TAIRGene;
import org.genemania.dw.util.DefParams;
import org.genemania.dw.util.GenUtil;

/**
 * The interface with the local mirror of the TAIR database. It 
 * encapsulates all the queries from that database. As a general rule, if the 
 * annotation-like column differs for different 'versions' of the TAIR locus,
 * the longest one is selected.
 * definition att represents the functiona_descriptions.short_desc
 * annotation att represents the functional_descriptions.curator_summary
 * description att represents the functional_descriptions.description
 * Note that the gene_aliases table can have some entries that are not in the
 * functional_descriptions table (e.g. '18S RRNA'). symbols and full_names 
 * from gene_aliases are loaded as syns, except for the first symbol, which is 
 * listed as name, for display purposes only. 
 * 
 * @author rashadbadrawi
 */

public class TAIRMirrorTables {

    //tables of interest
    private static final String TAIR_NCBI_TABLE ="TAIR2NCBI";
    private static final String GENE_ALIASES_TABLE = "gene_aliases";
    private static final String FUNC_DESC_TABLE = "functional_descriptions";

    //columns of interest
    private static final String NCBI_GENE_ID_COL = "NCBI_GENE_ID";
    private static final String TAIR_ID_COL = "TAIR_ID";
    private static final String LOCUS_NAME_COL = "locus_name";
    private static final String SYMBOL_COL = "symbol";
    private static final String FULL_NAME_COL = "full_name";
    private static final String MODEL_NAME_COL = "Model_name";
    private static final String TYPE_COL = "Type";
    private static final String SHORT_DESC_COL = "Short_description";
    private static final String CURATOR_SUM_COL = "Curator_summary";
    private static final String DESC_COL = "Description";
    //current schema name
    private static String DBName;
    private static PrintWriter log = GenUtil.getDefaultLog();

    static {
        DBName = DefParams.getDefaultProp(DefParams.TAIR_MIRROR);
    }

    private TAIRMirrorTables () {}            //no instances allowed
    
    public static TreeMap <String, ExtResource> loadAll ()
            throws SQLException {
        
        System.out.println ("loading all TAIR genes...");
        TreeMap <String, ExtResource> tairGeneMap = new TreeMap 
                <String, ExtResource> ();
        String loadQuery = "select distinct SUBSTRING_INDEX(" +
             MODEL_NAME_COL + ", '.', 1) as " + MODEL_NAME_COL + 
             " from " + DBName + "." + FUNC_DESC_TABLE;
        log.println (DBUtil.LOAD + ": " + loadQuery);

        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = DBUtil.getConnection ();
            ps = con.prepareStatement (loadQuery);
            rs = ps.executeQuery();
            int cnt = 0;
            TAIRGene DBTairGene = null;
            while (rs.next ()) {
                DBTairGene = loadTAIR (rs.getString (MODEL_NAME_COL));
                tairGeneMap.put (DBTairGene.getID(), DBTairGene);
                cnt++;
            }
            System.out.println ("Loaded: " + cnt + " TAIR entries");
        } catch (SQLException e) {
            if (!con.isClosed ()) {
                con.close ();
            }
            throw e;
        } finally {
            if (ps != null) {
                ps.close ();
            }
            if (!con.isClosed ()) {
                con.close ();
            }
        }
        loadTAIRInfo (tairGeneMap);
        matchAllEntrez (tairGeneMap);
        
        return tairGeneMap;
    }
    
    private static TAIRGene loadTAIR (String tairID) throws SQLException {
        
        String loadQuery = "select distinct " + 
                TYPE_COL + ", " + SHORT_DESC_COL + ", " + CURATOR_SUM_COL + 
                ", " + DESC_COL + 
                " from " + DBName + "." + FUNC_DESC_TABLE +
                " where " + 
                MODEL_NAME_COL + " like (?)"; 
        //log.println (DBUtil.LOAD + ": " + tairID + " " + loadQuery);
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        TAIRGene DBTairGene = null;
        try {
            con = DBUtil.getConnection ();
            ps = con.prepareStatement (loadQuery);
            ps.setString(1, tairID + ".%");
            rs = ps.executeQuery();
            while (rs.next ()) {
                DBTairGene = new TAIRGene();
                DBTairGene.setID (tairID);
                DBTairGene = getResults (rs, DBTairGene);
            }
        } catch (SQLException e) {
            throw e;
        } finally {
            if (ps != null) {
                ps.close ();
            }
        }
        
        return DBTairGene;
    } 
    
    private static TAIRGene getResults (ResultSet rs, TAIRGene DBTairGene) 
            throws SQLException {
        
        String newType = rs.getString (TYPE_COL);
        String newDefinition = rs.getString (SHORT_DESC_COL);
        String newAnnotation = rs.getString (CURATOR_SUM_COL);
        String newDescription = rs.getString (DESC_COL);
        if (newType != null && newType.length() > 0) {
            DBTairGene.setType(newType);
        }
        if (newDefinition != null && newDefinition.trim ().length () > 0) {
            newDefinition = newDefinition.trim ();
            if (DBTairGene.getDefinition() == null || 
                newDefinition.length() > 
                DBTairGene.getDefinition().length()) {
                DBTairGene.setDefinition (newDefinition);
            }
        }
        if (newAnnotation != null && newAnnotation.trim ().length () > 0) {
            newAnnotation = newAnnotation.trim ();
            if (DBTairGene.getAnnotation () == null || 
                newAnnotation.length() > 
                DBTairGene.getAnnotation().length()) {
                DBTairGene.setAnnotation (newAnnotation);
            }
        }
        if (newDescription != null && newDescription.trim ().length () > 0) {
            newDescription = newDescription.trim ();
            if (DBTairGene.getDescription () == null || 
                newDescription.length() > 
                DBTairGene.getDescription ().length()) {
                DBTairGene.setDescription (newDescription);
            }
        }
        
        return DBTairGene;
    }
    
    private static void loadTAIRInfo (TreeMap <String, ExtResource> 
            tairGeneMap) throws SQLException {
        
        System.out.println ("Loading TAIR info ...");
        String loadQuery = " select distinct " + 
                LOCUS_NAME_COL + ", " + SYMBOL_COL + ", " + FULL_NAME_COL + 
                " from " + DBName + "." + GENE_ALIASES_TABLE;
        log.println (DBUtil.LOAD + ": " + loadQuery);
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        TAIRGene DBTairGene = null;
        int cnt = 0;
        try {
            con = DBUtil.getConnection ();
            ps = con.prepareStatement (loadQuery);
            rs = ps.executeQuery();
            while (rs.next ()) {
                String tairID = rs.getString (LOCUS_NAME_COL);
                String syn1 = rs.getString (SYMBOL_COL).trim ();
                String syn2 = rs.getString (FULL_NAME_COL);
                DBTairGene = (TAIRGene)tairGeneMap.get (tairID);
                if (DBTairGene == null) {
                    DBTairGene = new TAIRGene();
                    DBTairGene.setID (tairID);
                    cnt++;
                }
                if (DBTairGene.getName () == null) {
                    DBTairGene.setName(syn1);
                } else {
                    DBTairGene.addSyn (syn1);
                }
                if (syn2 != null && syn2.trim ().length () > 0) {
                    DBTairGene.addSyn (syn2.trim ());
                }
                tairGeneMap.put (DBTairGene.getID (), DBTairGene);
            }
            System.out.println ("Added " + cnt + " TAIR entries.");
        } catch (SQLException e) {
            throw e;
        } finally {
            if (ps != null) {
                ps.close ();
            }
        }
    }
    
    private static void matchAllEntrez (TreeMap <String, ExtResource> 
            tairGeneMap) throws SQLException {
        
        System.out.println ("Mapping Entrez to TAIR...");
        String loadQuery = " select distinct " + 
        NCBI_GENE_ID_COL + ", " + TAIR_ID_COL + 
        " from " + DBName + "." + TAIR_NCBI_TABLE;
        log.println (DBUtil.LOAD + ": " + loadQuery);
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        TAIRGene DBTairGene = null;
        try {
            con = DBUtil.getConnection ();
            ps = con.prepareStatement (loadQuery);
            rs = ps.executeQuery();
            while (rs.next ()) {
                EntrezGene entGene = new EntrezGene ();
                entGene.setID (rs.getString (NCBI_GENE_ID_COL));
                String tairID = rs.getString (TAIR_ID_COL);
                DBTairGene = (TAIRGene)tairGeneMap.get (tairID);
                if (DBTairGene == null) {
                    System.out.println ("Missing TAIR ID: " + tairID);
                    DBTairGene = new TAIRGene();
                    DBTairGene.setID (tairID);
                }
                DBTairGene.addXRef(ExtResource.RES_ENTREZ, entGene);
                tairGeneMap.put (DBTairGene.getID (), DBTairGene);
            }
        } catch (SQLException e) {
            throw e;
        } finally {
            if (ps != null) {
                ps.close ();
            }
        }
    }
}

