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 attributes where the current version has no src_id (but older versions have). 
 * 
 * @author casties
 *
 */
public class FixAttributeIdMissing {
    
    /**
     * Print information about relations pointing to non-current entities to stdout.
     * 
     * Returns 0 if there are no such relations, 1 otherwise.
     * 
     * @param dbUser
     * @param dbPw
     * @return
     */
    public static int show(String dbUser, String dbPw) {
        Connection conn = DBUtils.getConn(dbUser, dbPw);
        int fixableCnt = 0;
        int replacedCnt = 0;
        int missingSrcCnt = 0;
        int multiSrcCnt = 0;
        List<Long> brokenAttIds = getBrokenAttIds(conn);
        System.out.println(brokenAttIds.size() + " ATTRIBUTEs without source...");
        for (Long id : brokenAttIds) {
            //System.out.println("ATTRIBUTE "+id+" source is missing");
            List<Long> srcIds = getAttSourceIds(id, conn);
            int ncv = srcIds.size();
            if (ncv == 1) {
                fixableCnt += 1;
                System.out.println("  ATTRIBUTE "+id+" has source (in other version)");
                List<Long> sameAttIds = getSameAttIds(id, srcIds.get(0), conn);
                if (sameAttIds.size() > 0) {
                    replacedCnt += 1;
                    System.out.println("  ATTRIBUTE "+id+" has has been replaced");
                }
            } else if (ncv == 0) {
                missingSrcCnt += 1;
            } else {
                multiSrcCnt += 1;
                System.out.println("  ERROR: ATTRIBUTE " + id + " has " + ncv + " sources!");                
            }
        }
        System.out.println();
        System.out.println(brokenAttIds.size() + " ATTRIBUTES have no source_id");
        System.out.println(fixableCnt + " ATTRIBUTES are fixable (source_id in other version)");
        System.out.println(replacedCnt + " ATTRIBUTES have been replaced");
        System.out.println(missingSrcCnt + " ATTRIBUTES are not fixable (no source_id)");
        System.out.println(multiSrcCnt + " ATTRIBUTES have too many source ids");
        try {
            conn.close();
        } catch (SQLException e) {
        }
        if (brokenAttIds.size()  > 0) {
            return 1;
        }
        return 0;
    }

    /**
     * Repair relations pointing to non-current entities.
     * 
     * Sets the version (*_modif) of the source or target id to the current version of this entity. 
     * 
     * Returns 0 if all relations were fixed, 1 otherwise.
     * 
     * @param dbUser
     * @param dbPw
     * @return
     */
    public static int repair(String dbUser, String dbPw) {
        Connection conn = DBUtils.getConn(dbUser, dbPw);
        int fixableCnt = 0;
        int fixedCnt = 0;
        int unfixableCnt = 0;
        int replacedCnt = 0;
        int missingSrcCnt = 0;
        int multiSrcCnt = 0;
        List<Long> brokenAttIds = getBrokenAttIds(conn);
        System.out.println(brokenAttIds.size() + " ATTRIBUTEs without source...");
        for (Long id : brokenAttIds) {
            List<Long> srcIds = getAttSourceIds(id, conn);
            int ncv = srcIds.size();
            if (ncv == 1) {
                fixableCnt += 1;
                Long srcId = srcIds.get(0);
                System.out.println("  ATTRIBUTE "+id+" has source (in other version)");
                List<Long> sameAttIds = getSameAttIds(id, srcId, conn);
                if (sameAttIds.size() > 0) {
                    replacedCnt += 1;
                    System.out.println("  ATTRIBUTE "+id+" has has been replaced");
                } else {
                    if (updateAttributeSrc(id, srcId, conn)) {
                        fixedCnt += 1;
                        System.out.println("  ATTRIBUTE "+id+" source updated");
                    } else {
                        unfixableCnt += 1;
                        System.out.println("  ERROR: ATTRIBUTE "+id+" could not be fixed");
                    }
                }
            } else if (ncv == 0) {
                missingSrcCnt += 1;
            } else {
                multiSrcCnt += 1;
                System.out.println("  ERROR: ATTRIBUTE " + id + " has " + ncv + " sources!");                
            }
        }
        System.out.println();
        System.out.println(brokenAttIds.size() + " ATTRIBUTES had no source_id");
        System.out.println(fixableCnt + " ATTRIBUTES were fixable (source_id in other version)");
        System.out.println(replacedCnt + " ATTRIBUTES have been replaced");
        System.out.println(missingSrcCnt + " ATTRIBUTES are not fixable (no source_id)");
        System.out.println(multiSrcCnt + " ATTRIBUTES have too many source ids");
        System.out.println();
        System.out.println(fixedCnt + " ATTRIBUTES have been fixed");
        System.out.println(unfixableCnt + " ATTRIBUTES could not be fixed");

        
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * Return a list of ids of attributes with missing source_id.
     * 
     * @param conn
     * @return
     */
    public static List<Long> getBrokenAttIds(Connection conn) {
        List<Long> ids = new ArrayList<Long>();
        String query = "SELECT DISTINCT id FROM openmind.node"
                + " WHERE node_type = 'ATTRIBUTE'"
                + " AND system_status = 'CURRENT_VERSION'"
                + " AND source_id IS NULL";
        
        try {
            Statement s = conn.createStatement();
            ResultSet r = s.executeQuery(query);
            while (r.next()) {
                long id = r.getLong(1);
                ids.add(id);
            }
            s.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ids;
    }

    /**
     * Return a list of source_ids of an attribute.
     * 
     * @param attId
     * @param conn
     * @return
     */
    public static List<Long> getAttSourceIds(Long attId, Connection conn) {
        List<Long> ids = new ArrayList<Long>();
        String query = "SELECT DISTINCT source_id FROM openmind.node "
                + "WHERE node_type = 'ATTRIBUTE' "
                + "AND source_id IS NOT NULL "
                + "AND id = " + attId.toString();
        
        try {
            Statement s = conn.createStatement();
            ResultSet r = s.executeQuery(query);
            while (r.next()) {
                long id = r.getLong(1);
                ids.add(id);
            }
            s.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ids;
    }

    /**
     * Check if another (current) attribute of the same type exists on the source.
     * 
     * @param attId
     * @param srcId
     * @param conn
     * @return
     */
    public static List<Long> getSameAttIds(Long attId, Long srcId, Connection conn) {
        List<Long> ids = new ArrayList<Long>();
        String query = "SELECT DISTINCT id FROM openmind.node"
                + " WHERE node_type = 'ATTRIBUTE'"
                + " AND system_status = 'CURRENT_VERSION'"
                + " AND id != " + attId.toString()
                + " AND source_id = " + srcId.toString()
                + " AND object_class IN ("
                + "  SELECT DISTINCT object_class FROM openmind.node"
                + "  WHERE id = " + attId.toString() + ")";
        
        try {
            Statement s = conn.createStatement();
            ResultSet r = s.executeQuery(query);
            while (r.next()) {
                long id = r.getLong(1);
                ids.add(id);
            }
            s.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ids;
    }

    /**
     * Return the modification_date of the current version of the entity.
     * 
     * @param id
     * @param conn
     * @return
     */
    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;
    }

    /**
     * Update the attribute with the source_id.
     * 
     * Returns true if the relation was fixed.
     * 
     * @param id of the attribute
     * @param id of the source
     * @param conn
     * @return
     */
    public static boolean updateAttributeSrc(Long attId, Long srcId, Connection conn) {
        // get mtime of current version of target
        List<Long> target_mtimes = getCurrentVersionTime(srcId, conn);
        if (target_mtimes.size() == 1) {
            // update target_mtime of relation
            long target_mtime = target_mtimes.get(0);
            String query = "UPDATE openmind.node"
                    + " SET source_id = " + srcId.toString()
                    + ", source_modif = " + target_mtime
                    + " WHERE id = " + attId.toString()
                    + " AND source_id IS NULL";
            Statement s;
            try {
                s = conn.createStatement();
                s.executeUpdate(query);
                s.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            System.out.println("  ATTRIBUTE " + attId + " was updated to source " + srcId + "/"+ target_mtime);
            return true;
        } else {
            System.out.println("ERROR: source " + srcId + " has " + target_mtimes.size() + " CURRENT_VERSIONs");
        }
        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);
    }

}