package org.mpi.openmind.scripts;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * Fixes relations with sources or targets that do not point to the current 
 * versions of the respective entities. 
 * 
 * @author casties
 *
 */
public class FixRelationNonCurrent {
    
    public static int show(String dbUser, String dbPw) {
        Connection conn = DBUtils.getConn(dbUser, dbPw);
        List<Long> sourceRelIds = getRelIds("source", conn);
        for (Long id : sourceRelIds) {
            System.out.println("RELATION "+id+" source is not CURRENT_VERSION");
            int ncv = getCurrentVersionTime(id, conn).size();
            if (ncv == 1) {
                System.out.println("  RELATION "+id+" can be fixed");
            } else {
                System.out.println("  ERROR: RELATION " + id + " has " + ncv + " CURRENT_VERSIONs");                
            }
        }
        System.out.println();
        List<Long> targetRelIds = getRelIds("target", conn);
        for (Long id : targetRelIds) {
            System.out.println("RELATION "+id+" target is not CURRENT_VERSION");
            int ncv = getCurrentVersionTime(id, conn).size();
            if (ncv == 1) {
                System.out.println("  RELATION "+id+" can be fixed");
            } else {
                System.out.println("  ERROR: RELATION "+id+" has " + ncv + " CURRENT_VERSIONs");                
            }
        }
        System.out.println();
        System.out.println(sourceRelIds.size() + " RELATION sources are not CURRENT_VERSION");
        System.out.println(targetRelIds.size() + " RELATION targets are not CURRENT_VERSION");
        try {
            conn.close();
        } catch (SQLException e) {
        }
        if (sourceRelIds.size()  > 0 || targetRelIds.size() > 0) {
            return 1;
        }
        return 0;
    }

    public static int repair(String dbUser, String dbPw) {
        Connection conn = DBUtils.getConn(dbUser, dbPw);
        List<Long> relSourceIds = getRelIds("source", conn);
        int fixedSs = 0;
        for (Long id : relSourceIds) {
            System.out.println("RELATION "+id+" source is not CURRENT_VERSION");
            if (updateCurrentRelation(id, "source", conn)) {
                System.out.println("  RELATION "+id+" target updated to current version");
                fixedSs += 1;
            } else {
                System.out.println("  ERROR: RELATION "+id+" was not fixed!");
            }
        }
        List<Long> relTargetIds = getRelIds("target", conn);
        int fixedTs = 0;
        for (Long id : relTargetIds) {
            System.out.println("RELATION "+id+" target is not CURRENT_VERSION");
            if (updateCurrentRelation(id, "target", conn)) {
                System.out.println("  RELATION "+id+" target updated to current version");
                fixedTs += 1;
            } else {
                System.out.println("  ERROR: RELATION "+id+" was not fixed!");
            }
        }
        System.out.println(relSourceIds.size() + " RELATION sources are not CURRENT_VERSION");
        System.out.println(relTargetIds.size() + " RELATION targets are not CURRENT_VERSION");
        System.out.println(fixedSs + " RELATION sources were fixed.");
        System.out.println(fixedTs + " RELATION targets were fixed.");
        try {
            conn.close();
        } catch (SQLException e) {
        }
        if (relSourceIds.size()  > fixedSs || relTargetIds.size() > fixedTs) {
            return 1;
        }
        return 0;
    }

    public static List<Long> getRelIds(String type, Connection conn) {
        List<Long> relIds = new ArrayList<Long>();
        String qTargetRels = "select rel.id from openmind.node rel, openmind.node ent "
                + "where rel." + type + "_id = ent.id "
                + "and rel." + type + "_modif = ent.modification_time "
                + "and rel.node_type = 'RELATION' "
                + "and rel.system_status = 'CURRENT_VERSION' "
                + "and ent.system_status != 'CURRENT_VERSION' ";
        
        try {
            Statement sTargetRels = conn.createStatement();
            ResultSet rTargetRels = sTargetRels.executeQuery(qTargetRels);
            while (rTargetRels.next()) {
                long id = rTargetRels.getLong(1);
                relIds.add(id);
            }
            sTargetRels.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return relIds;
    }

    public static List<Long> getCurrentVersionTime(Long id, Connection conn) {
        List<Long> times = new ArrayList<Long>();
        String query = "select modification_time from openmind.node "
                + "where id = " + id.toString() + " "
                + "and system_status = 'CURRENT_VERSION' ";
        try {
            Statement statement = conn.createStatement();
            ResultSet results = statement.executeQuery(query);
            while (results.next()) {
                long mtime = results.getLong(1);
                times.add(mtime);
            }
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return times;
    }

    public static boolean updateCurrentRelation(Long id, String type, Connection conn) {
        // get target_id of relation
        String qTargetId = "select " + type + "_id from openmind.node "
                + "where id = " + id.toString() + " "
                + "and system_status = 'CURRENT_VERSION' ";
        try {
            Statement sTargetId = conn.createStatement();
            ResultSet rTargetId = sTargetId.executeQuery(qTargetId);
            if (rTargetId.next()) {
                long target_id = rTargetId.getLong(1);
                // get mtime of current version of target
                List<Long> target_mtimes = getCurrentVersionTime(target_id, conn);
                if (target_mtimes.size() == 1) {
                    // update target_mtime of relation
                    String qUpdate = "update openmind.node "
                            + "set " + type + "_modif = " + target_mtimes.get(0).toString() + " "
                            + "where id = " + id.toString() + " "
                            + "and system_status = 'CURRENT_VERSION' ";
                    Statement sUpdate = conn.createStatement();
                    int rUpdate = sUpdate.executeUpdate(qUpdate);
                    sUpdate.close();
                    return true;
                } else {
                    System.out.println("ERROR: relation " + type + target_id + " has " + target_mtimes.size() + " CURRENT_VERSIONs");
                }
            }
            sTargetId.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * @param args
     */
    public static void main(String[] args) {
        int rc = 0;
        if (args.length > 1 && args.length < 4) {
            String user = args[1];
            String pw = (args.length == 3) ? args[2] : null;
            if (args[0].equalsIgnoreCase("fix")) {
                rc = repair(user, pw);
            } else {
                rc = show(user, pw);
            }
        } else {
            System.out.println("Parameter/s not found! Should be: mode(SHOW/FIX), mysql_user, mysql_password");
            System.out.println("  got: "+args.toString() + "("+args.length+")");
            System.exit(1);
        }
        System.exit(rc);
    }

}