/*
file: DareSet.java
expl: Manages the DARE collection so that DARE can harvest items through 
      1 OAI set instead of many.
hist:
      2008
      ----
      17 apr gl: Deletes are now handled correctly (in_archive=false AND withdrawn=true items)

      2006
      ----
      01 dec gl: Adapted script to work with Dspace 1.4:
                 select dc_type_id from dctyperegistry       
                   becomes:
                 select metadata_field_id from metadatafieldregistry 
      08 sep gl: Added delete of embargo items in DareSet
      16 jul pr: Added start/end date
                 Added "ERROR" to error-output for automating error recognition
      13 jul pr: changed selection of DARE set from DARE to "DARE%"
      12 jul gl: Creation
*/

import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.dspace.core.*;
import java.util.Date;
import java.text.ParseException;
import java.text.SimpleDateFormat;

public class DareSet 
{
    Connection db;
//    Logger log = Logger.getLogger(DareSet.class.getName());

    public static void main(String[] argv) throws Exception 
    {
        new DareSet();
    }

    public DareSet()
    {
        int dareID=-1;
        int embargoID=-1;
	int records_old=0;
	int records_new=0;
	int records_diff=0;
	int records_embargo=0;
	int records_withdrawn=0;
	int records_withdrawn_old=0;
        String currdate=stripDate(ORG.oclc.oai.server.verb.ServerVerb.createResponseDate(new Date()));
        
	Date now = new Date();
        System.out.println("DARE: START - Re-populating DARE collection at " +  now );
        System.out.println("DARE: Current date=[" + currdate + "]");

        // 0a. Define postgresql driver
        try 
        { 
            Class.forName("org.postgresql.Driver"); 
        } catch (ClassNotFoundException e) 
        {
            //log.info("Couldn't find driver class: [" + e.toString() + "]"); 
            System.err.println("ERROR: Couldn't find driver class: [" + e.toString() + "]"); 
            return;
        }

        // 0b. Init DB
        try 
        {
             db = DriverManager.getConnection("jdbc:postgresql:dspace", "dspace", "dspace");
        } catch (SQLException e) {
            //log.info("Couldn't connect to dspace database [" + e.toString() + "]");
            System.err.println("ERROR: Couldn't connect to dspace database [" + e.toString() + "]");
            return;
        }

        // 1. Get collection_id of collection DARE
        try 
        {
            ResultSet result = execute_select("select collection_id from collection where name like 'DARE%'");
            if (result == null || !result.next())
            {
                System.err.println("ERROR: No rows found: unknown collection_id for DARE");
                return;
            } else 
            {
                //System.out.println("No of items=[" + result.getString(0));
                dareID = result.getInt("collection_id");
                System.out.println("DARE: Dare_collection_id=[" + dareID + "]");
            }
        } catch (Exception e) 
        {
            //log.info("Failure: ["+ e.toString() + "]");
            System.err.println("ERROR: Failure: ["+ e.toString() + "]");
            return;
        }

        // 2. Get previous number of withdrawn items
        try
        {
            ResultSet result = execute_select("select delete_count from ubuaux_dareset where id=0");
            if (result == null || !result.next())
            {
                System.err.println("ERROR: Count of withdrawn DARE items in ubuaux_dareset failed");
                return;
            } else
            {
                records_withdrawn_old = result.getInt("delete_count");
                System.out.println("DARE: Total no of withdrawn items in collection old=[" + records_withdrawn_old + "]");
            }
        } catch (Exception e)
        {
            //log.info("Failure: ["+ e.toString() + "]");
            System.err.println("ERROR: Failure: ["+ e.toString() + "]");
            return;
        }


        // 3. Delete all DARE items in collection2item
        try  
        {
            String sql = "delete from collection2item where collection2item.collection_id=" + dareID;
            int result = execute_update(sql);
            if (result == -1)
            {
                System.err.println("ERROR: Delete of DARE items in collections2item failed");
                return;
            }
            records_old = result-records_withdrawn_old;
            System.out.println("DARE: Total no of items in collection old=[" + records_old + "]");
        } catch (Exception e)
        {
            //log.info("Failure: ["+ e.toString() + "]");
            System.err.println("ERROR: Failure - ["+ e.toString() + "]");
            return;
        }

        // 4. Map all items to DARE in collection2item which have the dareset-flag set in 
        //    dspace2omega_collection
        try
        {
            String sql = "insert into collection2item (id, collection_id, item_id) " +
                         "select getnextid('collection2item')," + dareID + ", collection2item.item_id " +
                         "from collection2item, dspace2omega_collection, item  " +
                         "where collection2item.collection_id = dspace2omega_collection.collection_id " +
                         "and dspace2omega_collection.dareset=true " +
                         "and item.item_id=collection2item.item_id " +
                         "and item.in_archive!=item.withdrawn";

            int result = execute_update(sql);
            if (result == -1)
            {
                System.err.println("ERROR: Insert of DARE items in collections2item failed");
                return;
            }
            records_new = result;

        } catch (Exception e)
        {
            //log.info("Failure: ["+ e.toString() + "]");
            System.err.println("ERROR: Failure: ["+ e.toString() + "]");
            return;
        }

        // 5. Delete embargo items in DareSet
        // a. Get metadata_field_id of date.embargo
        try
        {
            ResultSet result = execute_select("select metadata_field_id from metadatafieldregistry "+
                                              "where element='date' and qualifier='embargo'");
            if (result == null || !result.next())
            {
                System.err.println("ERROR: No rows found: unknown metadata_field_id for date.embargo");
                return;
            } else
            {
                embargoID = result.getInt("metadata_field_id");
                //System.out.println("DARE: metadata_field_id =[" + embargoID + "]");
            }
        } catch (Exception e)
        {
            //log.info("Failure: ["+ e.toString() + "]");
            System.err.println("ERROR: Failure: ["+ e.toString() + "]");
            return;
        }

        // b. Delete items with valid embargo
        try
        {
            String sql = "delete from collection2item " +
                         "where collection2item.collection_id=" + dareID + " and " +
                         "collection2item.item_id IN  ( " +
                         "select collection2item.item_id from dcvalue, collection2item where " +
                         "dcvalue.item_id = collection2item.item_id and " +
                         "dcvalue.dc_type_id=" + embargoID + " and " +
                         "dcvalue.text_value > '" + currdate + "')";

            int result = execute_update(sql);
            if (result == -1)
            {
                System.err.println("ERROR: Delete of DARE embargo items in collections2item failed");
                return;
            }
            records_embargo = result;
            records_new -= records_embargo;
            System.out.println("DARE: Embargo items=[" + records_embargo + "]");
        } catch (Exception e)
        {
            //log.info("Failure: ["+ e.toString() + "]");
            System.err.println("ERROR: Failure: ["+ e.toString() + "]");
            return;
        }

        // 6. Count current withdrawn DARE items in collection2item
        try
        {
            ResultSet result = execute_select("select count(*) from collection2item, item where" +
                                              " collection2item.item_id=item.item_id" +
                                              " and collection2item.collection_id=" + dareID +
                                              " and item.withdrawn=true" +
                                              " and item.in_archive=false");
            if (result == null || !result.next())
            {
                System.err.println("ERROR: Count of withdrawn DARE items in collections2item new failed");
                return;
            } else
            {
                //System.out.println("No of items=[" + result.getInt("count") + "]");
                records_withdrawn = result.getInt("count");
                records_new -= records_withdrawn;
                System.out.println("DARE: Total number of withdrawn items in collection new=[" + records_withdrawn + "]");
            }
        } catch (Exception e)
        {
            //log.info("Failure: ["+ e.toString() + "]");
            System.err.println("ERROR: Failure: ["+ e.toString() + "]");
            return;
        }

        // 7. Save no of withdrawn items for next run
        try
        {
            String sql = "update ubuaux_dareset set delete_count=" + records_withdrawn + "where id=0";
            int result = execute_update(sql);
            if (result == -1)
            {
                System.err.println("ERROR: Update of DARE withdrawn count failed");
                return;
            }
        } catch (Exception e)
        {
            //log.info("Failure: ["+ e.toString() + "]");
            System.err.println("ERROR: Failure - ["+ e.toString() + "]");
            return;
        }

        // 8. Finish
        records_diff = records_new - records_old;
        System.out.println("DARE: Total no of items in collection new=[" + records_new + "]");
        System.out.println("DARE: Collection increased with [" + records_diff + "] items");
        if (records_new < records_old)
        {
            System.out.println("DARE: WARNING - No of DARE items has decreased");
        }

	now = new Date();
        System.out.println("DARE: FINISHED - Re-populating DARE collection at " +  now );
    }


    public ResultSet execute_select(String sql)
    {
        PreparedStatement stat = null;
        ResultSet result = null;

        try
        {
            stat = db.prepareStatement(sql);
            result = stat.executeQuery();
        } catch (Exception e) {
            //log.info("Failed to execute query: [" + sql + "] Message: ["+ e.toString() + "]");
            System.err.println("ERROR: Failed to execute query: [" + sql + "] Message: ["+ e.toString() + "]");
        }

        return result;
    }


    public int execute_update(String sql)
    {
        /*
        public int executeUpdate() throws SQLException
            Executes the SQL statement in this PreparedStatement object, 
            which must be an SQL INSERT, UPDATE or DELETE statement; or an SQL statement 
            that returns nothing, such as a DDL statement.
        Returns:
            either (1) the row count for INSERT, UPDATE, or DELETE statements or (2) 0 for 
            SQL statements that return nothing 
        Throws:
            SQLException - if a database access error occurs or the SQL statement returns a 
                           ResultSet object
        */

        PreparedStatement stat = null;
        int result = -1;

        try
        {
            stat = db.prepareStatement(sql);
            result = stat.executeUpdate();
        } catch (Exception e) {
            //log.info("Failed to execute update: [" + sql + "] Message: ["+ e.toString() + "]");
            System.err.println("ERROR: Failed to execute update: [" + sql + "] Message: ["+ e.toString() + "]");
        }

        return result;
    }


    private String stripDate(String date)
    {
        if (date != null && date.indexOf("T") > 0)
            date = date.substring(0, date.indexOf("T"));
        return date;

    }
}