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.Iterator;
import java.util.TreeMap;
import org.genemania.dw.entity.ExtResource;
import org.genemania.dw.entity.ExtResourceGene;
import org.genemania.dw.util.GenUtil;

/**
 * Persistence for the ExtResourceGene class. Currently it represents a 
 * summary/tracking for the gene objects generated during the IDMapping
 * process.
 * 
 * @author rashadbadrawi
 */

public class ExtResourceGeneTable {

    //DB Name
    private static final String GMDW = "DW";
    
    //table names
    private static final String EXT_RES_TABLE = "ExtResourceGene";

    //table column names
    private static final String GMID_COL = "ID";
    private static final String SOURCE_COL = "Source";
    private static final String SPECIES_COL = "Species";
    private static final String GENEID_COL = "GeneID";
    private static final String STATUS_COL = "Status";
    private static final String TIME_STAMP_COL = "TimeStamp";

    private static PrintWriter log = GenUtil.getDefaultLog();

    private ExtResourceGeneTable () {}              //no instances allowed
    
    public static void saveAll (TreeMap
            <String, ExtResource> extResMap) throws SQLException {

        String updateQuery = "update " + GMDW + "." + EXT_RES_TABLE +
                " set " + STATUS_COL + " = ? where " +
                SOURCE_COL + " = ? and " + SPECIES_COL + " = ?";
        //note: 'CURRENT_TIMESTAMP ()', with space, can be problematic
        String insertQuery = "insert into " + GMDW + "." + EXT_RES_TABLE + " (" +
             SOURCE_COL + ", " + SPECIES_COL + ", " + GENEID_COL + ", " +
             STATUS_COL + ", " + TIME_STAMP_COL +
                            ") values (?, ?, ?, ?, CURRENT_TIMESTAMP())";
        String selectQuery = "select " + GMID_COL + " from " +
                GMDW + "." + EXT_RES_TABLE + " where " +
                SOURCE_COL + " = ? " +
                " and " + SPECIES_COL + " = ? " +
                " and " + GENEID_COL + " = ? " +
                " and " + STATUS_COL + " = ?";
        log.println (updateQuery + GenUtil.NEWLINE + insertQuery +
                           GenUtil.NEWLINE + selectQuery);
        Connection con = null;
        PreparedStatement psUpdate = null, psInsert = null, psSelect = null;
        int cnt;
        try {
            con = DBUtil.getConnection ();
            //update the status of all earlier entries.
            psUpdate = con.prepareStatement (updateQuery);
            psUpdate.setString (1, ExtResource.STATUS_OLD);
            psUpdate.setString (2, extResMap.get (extResMap.firstKey()).getSource());
            psUpdate.setString (3, extResMap.get (extResMap.firstKey()).getSpeciesName());
            cnt = psUpdate.executeUpdate ();
            psInsert = con.prepareStatement (insertQuery);
            psSelect = con.prepareStatement(selectQuery);
            Iterator iterator = extResMap.keySet().iterator();
            while (iterator.hasNext()) {
                String extResGeneID = (String)iterator.next();
                ExtResourceGene extResGene = (ExtResourceGene)extResMap.get (extResGeneID);
                //insert the new entry
                psInsert.setString(1, extResGene.getSource());
                psInsert.setString (2, extResGene.getSpeciesName());
                psInsert.setString (3, extResGene.getID());
                psInsert.setString (4, ExtResource.STATUS_CURRENT);
                cnt = psInsert.executeUpdate ();
                //grab the respective DBID
                psSelect = con.prepareStatement (selectQuery);
                psSelect.setString(1, extResGene.getSource());
                psSelect.setString (2, extResGene.getSpeciesName());
                psSelect.setString (3, extResGene.getID());
                psSelect.setString (4, ExtResource.STATUS_CURRENT);
                ResultSet rs = psSelect.executeQuery ();
                cnt = 0;
                while (rs.next()) {
                    int GMID = rs.getInt(GMID_COL);
                    extResGene.setDBID(GMID);
                    extResGene.setStatus (ExtResource.STATUS_CURRENT);
                    cnt++;
                }
                if (cnt != 1) {
                    System.err.println ("Error: Multiple entries for gene: " +
                            extResGene.getSource() + " " + extResGene.getID());
                }
                extResMap.put (extResGeneID, extResGene);
            }
        } catch (SQLException e) {
            con.rollback();
            throw e;
        } finally {
            con.commit();
            if (psUpdate != null) {
                psUpdate.close ();
            }
            if (psInsert != null) {
                psInsert.close ();
            }
            if (psSelect != null) {
                psSelect.close ();
            }
            if (con != null && !con.isClosed ()) {
                con.close ();
            }
        }
    }

    public static void reset () throws SQLException {
        
        String truncExtResQuery = "truncate table " + GMDW + "." +  EXT_RES_TABLE;

        log.println (truncExtResQuery);
        Connection con = null;
        PreparedStatement ps = null;
        int cnt;
        try {
            con = DBUtil.getConnection ();
            ps = con.prepareStatement (truncExtResQuery);
            cnt = ps.executeUpdate();
            System.out.println ("Cleared: " + cnt + " ExtResource entries.");
        } catch (SQLException e) {
            throw e;
        } finally {
            if (ps != null) {
                ps.close ();
            }
            if (con != null && !con.isClosed ()) {
                con.close ();
            }
        }
    }
    
    public static void resetSafe () throws SQLException {
        
        String resetExtResQuery = "update " + 
                GMDW + "." +  EXT_RES_TABLE + " set " + STATUS_COL + " = ?";

        log.println (resetExtResQuery);
        Connection con = null;
        PreparedStatement ps = null;
        int cnt;
        try {
            con = DBUtil.getConnection ();
            ps = con.prepareStatement (resetExtResQuery);
            ps.setString (1, ExtResourceGene.STATUS_OLD);
            cnt = ps.executeUpdate();
            System.out.println ("Reset: " + cnt + " ExtResource entries.");
        } catch (SQLException e) {
            throw e;
        } finally {
            if (ps != null) {
                ps.close ();
            }
            if (con != null && !con.isClosed ()) {
                con.close ();
            }
        }
    }
}
