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.ExtResource;
import org.genemania.dw.entity.EntrezGene;
import org.genemania.dw.entity.EnsemblGene;
import org.genemania.dw.entity.Uniprot;
import org.genemania.dw.util.DefParams;
import org.genemania.dw.util.GenUtil;

/**
 * Uniprot related database functionality. 
 * Assumes that IDMap table's accn column is unique, and that the RefSeq column
 * only has RefSeq protein identifiers.
 * Only the primary key is set for the Xrefs (like Ensembl, Entrez), since its
 * assumed these xrefs will be 'complemented' from their primary sources.
 * May add more CV values for xrefType, instead of partial referencing to
 * ExtResource.
 *
 * @author rashadbadrawi
 */

public class UniprotMirrorTables {

    //tables of interest
    private static final String IDMAP_TABLE = "IDMap";
    private static final String IDMAPGEN_TABLE = "IDMapGen";

    //shared
    private static final String ACCN_COL = "UniProtAccn";
    //IDMap
    private static final String NAME_COL = "UniProtID";
    private static final String ENTREZ_GENE_COL = "EntrezGene";
    private static final String REFSEQ_COL = "RefSeq";
    private static final String GI_COL = "GI";
    private static final String GO_COL = "GO";
    private static final String TAX_ID_COL = "TaxID";
    private static final String OMIM_COL = "MIM";
    private static final String ENSEMBL_COL = "Ensembl";
    //IDMapGen
    private static final String XREF_IDTYPE_COL = "XrefIDType";
    private static final String XREF_ID_COL = "XrefID";

    private static final String XREFIDTYPE_GI = "GI";
    private static final String XREFIDTYPE_ENTREZ = "GeneID";
    private static final String XREFIDTYPE_REFSEQ = "RefSeq";
    //current schema name
    private static String DBName;
    private static PrintWriter log = GenUtil.getDefaultLog();

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

    private UniprotMirrorTables () {}            //no instances allowed

    public static TreeMap <String, ExtResource> loadAll
                                    (int taxID) throws SQLException {

        System.out.println ("loading Uniprot for " + taxID);
        TreeMap <String, ExtResource> uniMap = new TreeMap <String, ExtResource> ();
        String loadQuery = "select " +
            ACCN_COL + ", " + NAME_COL + ", " + ENTREZ_GENE_COL + ", " +
            REFSEQ_COL + ", " + GI_COL  + ", " + GO_COL + ", " +
            TAX_ID_COL + ", " + OMIM_COL + ", " + ENSEMBL_COL +
            " from " + DBName +  "." + IDMAP_TABLE +
            " where " + TAX_ID_COL + " = ?";
        //System.out.println (DBUtil.LOAD + ": " + loadQuery);

        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = DBUtil.getConnection ();
            ps = con.prepareStatement (loadQuery);
            ps.setInt(1, taxID);
            rs = ps.executeQuery();
            int cnt = 0;
            Uniprot uniEntry = null;
            while (rs.next ()) {
                uniEntry = getResults (rs, uniEntry);
                if (uniMap.containsKey(uniEntry.getID())) {
                    System.err.println ("Error: Uniprot Accn already exists: " +
                                        uniEntry.getID ());
                }
                uniMap.put (uniEntry.getID(), uniEntry);        //overwrite
                //System.out.println (uniEntry.toString());
                cnt++;
            }
            System.out.println ("Loaded: " + cnt + " Uniprot entries, for " +
                    "species: " + taxID);
        } catch (SQLException e) {
            throw e;
        } finally {
            if (ps != null) {
                ps.close ();
            }
            if (con != null && !con.isClosed ()) {
                con.close ();
            }
        }

        return uniMap;
    }

    public static TreeMap <String, ExtResource> maptoXref (String xrefType,
                                            String xrefID) throws SQLException {

        TreeMap <String, ExtResource> uniMap = new TreeMap <String, ExtResource> ();
        String loadQuery = "select " + XREF_IDTYPE_COL + ", " +
            ACCN_COL + " from " + DBName +  "." + IDMAPGEN_TABLE + " where ";
        if (xrefType != null) {
            loadQuery += XREF_IDTYPE_COL + " = ? and ";
        }
        //accomodate refseq protein versions.
        if (!xrefID.contains(GenUtil.DOT) && 
            ExtResource.LIST_REFSEQ_PRO.equals (xrefType)) {
            loadQuery += "(" + XREF_ID_COL + " like (?) or " +
                         XREF_ID_COL + " = ? )";
        } else {
            loadQuery += XREF_ID_COL + " = ?";
        }
        log.println (DBUtil.LOAD + ": " + loadQuery);

        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = DBUtil.getConnection ();
            ps = con.prepareStatement (loadQuery);
            int index = 1;
            if (xrefType != null) {
                ps.setString (index++, getUniXrefType (xrefType));
            }
            if (!xrefID.contains (GenUtil.DOT) &&
                ExtResource.LIST_REFSEQ_PRO.equals (xrefType)) {
                ps.setString (index++, xrefID + ".%");
            } 
            ps.setString (index++, xrefID);
            rs = ps.executeQuery();
            int cnt = 0;
            Uniprot uniEntry = null;
            while (rs.next ()) {
                uniEntry = new Uniprot ();
                uniEntry.setID (rs.getString (ACCN_COL));
                if (xrefType != null) {
                    if (xrefType.equals(ExtResource.LIST_GI) ||
                        xrefType.equals (ExtResource.LIST_REFSEQ_PRO)) {
                        addList(uniEntry, ExtResource.RES_ENTREZ, xrefID);
                    } else if (xrefType.equals (ExtResource.RES_ENSEMBL) ||
                               xrefType.equals(ExtResource.LIST_ID)) {
                        addXrefList(uniEntry, ExtResource.RES_ENSEMBL, xrefID);
                    }
                }
                uniMap.put (uniEntry.getID(), uniEntry);        //overwrite
                //System.out.println (uniEntry.toString());
                cnt++;
            }
            //System.out.println ("Loaded: " + cnt + " Uniprot entries, for " +
            //        xrefType + " " + xrefID);
        } catch (SQLException e) {
            throw e;
        } finally {
            if (ps != null) {
                ps.close ();
            }
            if (con != null && !con.isClosed ()) {
                con.close ();
            }
        }

        return uniMap;
    }

    private static Uniprot getResults (ResultSet rs, Uniprot uniEntry)
                                                         throws SQLException {
        
        uniEntry = new Uniprot ();
        uniEntry.setID(rs.getString(UniprotMirrorTables.ACCN_COL));
        uniEntry.setName(rs.getString(UniprotMirrorTables.NAME_COL));
        uniEntry.setSpeciesID(rs.getInt(UniprotMirrorTables.TAX_ID_COL));
        addXrefList (uniEntry, ExtResource.RES_ENTREZ,
                     rs.getString (UniprotMirrorTables.ENTREZ_GENE_COL));
        addXrefList (uniEntry, ExtResource.RES_ENSEMBL,
                     rs.getString(UniprotMirrorTables.ENSEMBL_COL));
        addList (uniEntry, ExtResource.LIST_REFSEQ_PRO,
                 rs.getString(UniprotMirrorTables.REFSEQ_COL));
        addList (uniEntry, ExtResource.LIST_GI,
                 rs.getString(UniprotMirrorTables.GI_COL));

        return uniEntry;
    }

    //helper method
    private static void addXrefList (Uniprot uniEntry, String xrefType, String colVal) {

        if (colVal == null || colVal.length() == 0 || colVal.trim().equals (GenUtil.HYPHEN)) {
            return;
        }
        String colArr [] = colVal.split(GenUtil.SEMICOLON);
        for (int i = 0; i < colArr.length; i++) {
            if (colArr [i].trim ().length () == 0) {
                continue;
            }
            ExtResource extRes = null;
            if (ExtResource.RES_ENTREZ.equals (xrefType)) {
               extRes = new EntrezGene ();
            } else if (ExtResource.RES_ENSEMBL.equals(xrefType)) {
                extRes = new EnsemblGene ();
            }
            extRes.setID(colArr[i].trim());
            uniEntry.addXRef(xrefType, extRes);
        }
    }

    //helper method
    private static void addList (Uniprot uniEntry, String listType, String colVal) {

        if (colVal == null || colVal.length() == 0 || colVal.trim().equals (GenUtil.HYPHEN)) {
            return;
        }
        String colArr [] = colVal.split(GenUtil.SEMICOLON);
        for (int i = 0; i < colArr.length; i++) {
            if (colArr [i].trim ().length () == 0) {
                continue;
            }
            if (ExtResource.LIST_REFSEQ_PRO.equals (listType)) {
               uniEntry.addRefSeqProID(colArr [i].trim ());
            } else if (ExtResource.LIST_GI.equals(listType)) {
               uniEntry.addGI(colArr [i].trim ());
            }
        }
    }

    private static String getUniXrefType (String xrefType) {

        if (ExtResource.LIST_GI.equals (xrefType)) {
            return XREFIDTYPE_GI;
        } else if (ExtResource.LIST_REFSEQ_PRO.equals (xrefType)) {
            return XREFIDTYPE_REFSEQ;
        } else if (ExtResource.LIST_ID.equals (xrefType)) {
            return XREFIDTYPE_ENTREZ;
        } else {
            return null;
        }
    }
}
