#sonic autoAssignReft almost merged

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

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;

import ru.it.metasonic.ruspost.infopoint.utils.BOHelper;
import ru.lbpm.metasonic.businessobjects.DbHelper;
import ru.lbpm.metasonic.businessobjects.DbHelper.ValueWrapper;

import com.jcom1.api.constants.Constants.Storage.BusinessObject20;
import com.jcom1.api.dto.interfaces.IStorageInstanceBean;
import com.jcom1.api.dto.interfaces.ISubjectInstanceBean;
import com.jcom1.api.dto.interfaces.storages.IStorageInstanceItemBeanReducedToId;
import com.jcom1.api.interfaces.IAuthorizationAdministrationApi;
import com.jcom1.api.interfaces.IAuthorizationApi;
import com.jcom1.api.interfaces.IBo20AccessApi;
import com.jcom1.api.interfaces.ICommonApi;
import com.jcom1.api.util.IViewStorageInstanceItemPair;
import com.jcom1.api.util.Pair;
import com.jcom1.runtime.refinement.AbstractRefinement;
import com.jcom1.runtime.refinement.FunctionStateRequest;
import com.jcom1.runtime.refinement.FunctionStateResponse;
import com.jcom1.runtime.refinement.RefinementGenerator;
import com.jcom1.runtime.refinement.SendStateRequest;
import com.jcom1.runtime.refinement.SendStateResponse;

import de.metasonic.businessobjects.model.interfaces.IView;
import de.metasonic.refinement.global.api.ApiUtils;

/**
 * @generated
 */
/**
 * This method is called when the named function state is reached State: Поиск
 * ответственного StateType: FUNCTION
 *
 * Return values "FunctionTransitionDescription_SPOzAXA_EealfcrKqoINvA" when
 * result is "" to go to state Отправка ответственному
 *
 * @return return the id of the transition to follow e.g. // return new
 *         FunctionStateResponse("transitionXXXXXXXX");
 */
@RefinementGenerator(id = "InternalSubjectDescription_MtoBEXAxEealfcrKqoINvA")
public class Маршрутизатор_автоназначенияRefinement extends AbstractRefinement {

    @Autowired
    ICommonApi commonApi;

    @Autowired
    IBo20AccessApi accessApi;

    @Autowired
    IAuthorizationApi authApi;

    @Autowired
    IAuthorizationAdministrationApi administrationApi;

    final String decisionResponsibleSubjectDescId = "InternalSubjectDescription_xRtowSvvEea91xRuMXueEA";
    final String transitionToFollowOnAssignation = "FunctionTransitionDescription_SPOzAXA_EealfcrKqoINvA";

    private final String DB_URL = "jdbc:hsqldb:hsql://localhost/jcom1";
    private final String DB_USER = "sa";
    private final String DB_PSW = "";

    private final String UFPS_INDEX = "UFPS_INDEX";
    private final String UFPS_NAME = "UFPS_NAME";
    private final String POSTAMT_INDEX = "POSTAMT_INDEX";
    private final String POSTAMT_NAME = "POSTAMT_NAME";
    private final String UM_GROUP = "UM_GROUP";
    private final String UM_USER = "UM_USER";
    private final String UM_GROUP_AS_POSTAMT_INDEX = "POSTAMT_INDEX";
    private final String UM_GROUP_AS_RUSPOST_OPERATOR_GROUP_ID = "RUSPOST_OPERATOR_GROUP_ID";
    private final String ID_AS_RUSPOST_OPERATOR_GROUP_ID = "RUSPOST_OPERATOR_GROUP_ID";
    private final String RUSPOST_OPERATOR = "RUSPOST-OPERATOR";

    private final String ACTIVE_TASK_NUMBER = "ACTIVE_TASK_NUMBER";

    private int ruspostOperatorGroupId;

    /**
     * @generated
     */
    private final static Log log = LogFactory
            .getLog("com.jcom1.refinement.p02__обработка_инцидентов.маршрутизатор_автоназначенияrefinement");

    /**
     * This method is called when the named function state is reached State:
     * Поиск ответственного StateType: FUNCTION
     *
     * Return values "FunctionTransitionDescription_SPOzAXA_EealfcrKqoINvA" when
     * result is "" to go to state Отправка ответственному
     *
     * @return return the id of the transition to follow e.g. // return new
     *         FunctionStateResponse("transitionXXXXXXXX");
     */
    @RefinementGenerator(id = "FunctionStateDescription_Kii0I3A_EealfcrKqoINvA")
    public FunctionStateResponse functionПоиск_ответственного(
            FunctionStateRequest functionStateRequest) {
        log.error("autoassign start");

        String ruspostOperatorGroupIdQuery =
                "SELECT ID AS RUSPOST_OPERATOR_GROUP_ID FROM UM_GROUP WHERE NAME = ?";

        String availableOperatorsQuery =
                "SELECT A.UM_GROUP AS POSTAMT_INDEX, A.UM_USER, B.UM_GROUP AS RUSPOST_OPERATOR_GROUP_ID "
                        + "FROM UM_GROUP_UM_USER A "
                        + "LEFT JOIN UM_GROUP_UM_USER B "
                        + "ON A.UM_USER = B.UM_USER WHERE A.UM_GROUP = ? "
                        + "     AND B.UM_GROUP = ?";

        String activeTasksQueryTemplate =
                "SELECT count(*) AS ACTIVE_TASK_NUMBER "
                        + " FROM ((SELECT DROPPED, AVAILABLETRANSITIONS, RT_PROCDESC, RT_SUBDESC, UM_USER_EDITOR "
                        + "         FROM RT_SUBINST "
                        + "         WHERE DROPPED = 0 AND "
                        + "             AVAILABLETRANSITIONS = 1 "
                        + "             AND UM_USER_EDITOR = %s) A "
                        + "         JOIN (SELECT ID, JPASS_ID, NAME "
                        + "                 FROM RT_SUBDESC "
                        + "                 WHERE JPASS_ID = '%s') B "
                        + "         ON A.RT_SUBDESC = B.ID)";


        try (final Connection connection = DbHelper
                .getConnection(DbHelper.PROP_DB_INT);) {
            log.error("connection has been set");


            // find ID for OPERATOR-IP
            // form query as SELECT TOP 1 ID AS ...
            ruspostOperatorGroupId = getRuspostOperatorGroupId(connection, ruspostOperatorGroupIdQuery);
            log.error(RUSPOST_OPERATOR + "'s group ID: " + ruspostOperatorGroupId);


            // retrieve current card's postamt info
            IViewStorageInstanceItemPair primeIViewAndItemEventCard = getPrimeIViewAndItem(this
                    .getSubjectInstance());
            IView primeBOEventCard = (IView) primeIViewAndItemEventCard
                    .getiView();

            if (null != primeBOEventCard) {
                log.error("retrieving postamt data for current EventCard");

                String postamtIndex = (String) BOHelper.getAttribute(
                        primeBOEventCard, "postamtIndex");
                String postamtName = (String) BOHelper.getAttribute(
                        primeBOEventCard, "postamtName");
                log.error("     postamt: " + postamtIndex + "/" + postamtName);


                // get operators from postamts that belong to concrete ufps
                // form query as ... WHERE UM_GROUP = 123456 AND UM_GROUP = 10
                List<Integer> operatorUserIds = getAvailableRuspostOperators(
                        connection, availableOperatorsQuery);


                // now as we got available operators, lets count number of their
                // tasks
                if (0 != operatorUserIds.size()) {
                    log.error("iterating over [" + operatorUserIds.size()
                            + "] available operators");
                    log.error("executing: "
                            + String.format(activeTasksQueryTemplate,
                                    "<sampleUserId>",
                                    "<sampleInternalSubjDesc>"));


                    // iterate over operators and count number of their tasks
                    String lazybonesId = null;
                    int minTaskNum = Integer.MAX_VALUE;
                    for (Integer u : operatorUserIds) {

                        String currUserId = p.getFirstElement();
                        // count number of active tasks for user
                        // form query as ... WHERE A.UM_USER_EDITOR = 1337 ...
                        // WHERE B.JPASS_ID = 'InternalSubjDesc_322'
                        String activeTasksQuery = String.format(
                                activeTasksQueryTemplate, currUserId,
                                decisionResponsibleSubjectDescId);
                        int taskNum = countActiveTasks(connection,
                                activeTasksQuery);

                        log.error("id:taskNum  (" + currUserId + ":" + taskNum
                                + ")");
                        if (taskNum < minTaskNum) {
                            minTaskNum = taskNum;
                            lazybonesId = currUserId;
                        }
                    }

                    log.error("operator to be assigned found: " + lazybonesId
                            + ". attempting to set op into BO");
                    BOHelper.setAttribute(primeBOEventCard, "assignedOperator",
                            lazybonesId);

                    savePrimeIViewAndItem(this.getSubjectInstance(),
                            primeIViewAndItemEventCard);
                    log.error("op has been set");

                } else {
                    log.error("no availble " + RUSPOST_OPERATOR
                            + "s were found by current postamt");
                }
            } else {
                log.error("error retrieving primeBO");
            }

            log.error("all done");
            log.error("\n");

        } catch (Exception e) {
            e.printStackTrace();
        }

        return new FunctionStateResponse(transitionToFollowOnAssignation);
    }

    private int getOperatorIpId(Connection connection, String query) {
        int id = -1;

        try {
            log.error("executing: " + query);
            Statement stmt = connection.createStatement();
            stmt.setMaxRows(1);

            ResultSet cgiSet = stmt.executeQuery(query);

            if (cgiSet.next())
                id = cgiSet.getInt(ID_AS_IP_ID);

            cgiSet.close();

        } catch (SQLException e) {
            log.error("error fetching " + OPERATOR_IP + "'s ID");
        }

        return id;
    }

    private Pair[] getAvailableOperators(Connection connection, String query) {
        List<Pair> userRolePairs = new ArrayList<Pair>();
        try {
            ResultSet result = connection.createStatement().executeQuery(query);
            log.error("executing: " + query);

            while (result.next()) {

                int umGroupAsIndex = result.getInt(UM_GROUP_AS_INDEX);
                int umUserAsId = result.getInt(UM_USER);
                int umGroupAsRole = result.getInt(UM_GROUP_AS_ROLE);
                log.error("  indx/id/role: " + umGroupAsIndex + " / "
                        + umUserAsId + " / " + umGroupAsRole);

                userRolePairs.add(new Pair(String.valueOf(umUserAsId), String
                        .valueOf(umGroupAsRole)));
            }
            result.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userRolePairs.toArray(new Pair[0]);
    }

    private int countActiveTasks(Connection connection, String query) {
        int id = -1;

        try {
            // log.error("executing: " + query);
            Statement stmt = connection.createStatement();
            stmt.setMaxRows(1);

            ResultSet cgiSet = stmt.executeQuery(query);

            if (cgiSet.next())
                id = cgiSet.getInt(ACTIVE_TASK_NUMBER);

            cgiSet.close();

        } catch (SQLException e) {
            log.error("error fetching task number");
        }

        return id;
    }

    private IViewStorageInstanceItemPair getPrimeIViewAndItem(
            ISubjectInstanceBean subjectInstanceBean) throws Exception {
        IBo20AccessApi bo20AccessApi = ApiUtils.lookupBo20AccessApi();
        Set<IStorageInstanceBean> storages = bo20AccessApi.getStorages(
                subjectInstanceBean, null);
        IStorageInstanceBean storageInstanceBean = storages.iterator().next();
        Set<IStorageInstanceItemBeanReducedToId> storageItems = bo20AccessApi
                .getItems(subjectInstanceBean, storageInstanceBean);
        IStorageInstanceItemBeanReducedToId storageItem = storageItems
                .iterator().next();
        String actionType = BusinessObject20.Action.READ;
        IViewStorageInstanceItemPair iViewStorageInstanceItemPair = bo20AccessApi
                .getIViewAndItem(subjectInstanceBean, storageItem, actionType);
        return iViewStorageInstanceItemPair;
    }

    private void savePrimeIViewAndItem(
            ISubjectInstanceBean subjectInstanceBean,
            IViewStorageInstanceItemPair iViewStorageInstanceItemPair)
            throws Exception {
        IBo20AccessApi bo20AccessApi = ApiUtils.lookupBo20AccessApi();
        bo20AccessApi.saveIView(subjectInstanceBean,
                iViewStorageInstanceItemPair);
    }



    private int getRuspostOperatorGroupId(Connection connection, String query)
            throws Exception {
        log.error("getRuspostOperatorGroupId begin");

        List<ValueWrapper> valueWrappers = new ArrayList<ValueWrapper>();
        valueWrappers.add(new ValueWrapper(RUSPOST_OPERATOR, String.class));

        int id = (int) DbHelper.executeQuery(connection, query, valueWrappers,
                true, new DbHelper.ResulSetHandler() {
                    @Override
                    public Object processResultSet(ResultSet rerSet)
                            throws Exception {

                        if (rerSet.next())
                            return rerSet.getInt(ID_AS_RUSPOST_OPERATOR_GROUP_ID);

                        return -1;
                    }
                });
        log.error("getRuspostOperatorGroupId end");
        return id;
    }

    private List<Integer> getAvailableRuspostOperators(Connection connection, String query)
            throws Exception {
        log.error("getAvailableRuspostOperators begin");

        List<ValueWrapper> valueWrappers = new ArrayList<ValueWrapper>();


        List<Integer> userIds = (List<Integer>) DbHelper.executeQuery(connection,
                query, valueWrappers, true, new DbHelper.ResulSetHandler() {
                    @Override
                    public Object processResultSet(ResultSet result)
                            throws Exception {
                        List<Integer> userIds = new ArrayList<Integer>();

                        while (result.next()) {

                            int umGroupAsPostamtIndex = result.getInt(UM_GROUP_AS_POSTAMT_INDEX);
                            int umUser = result.getInt(UM_USER);
                            int umGroupAsRuspostOperatorGroupId = result.getInt(UM_GROUP_AS_RUSPOST_OPERATOR_GROUP_ID);
                            log.error("  indx/id/operGroupId: " + umGroupAsPostamtIndex
                                    + " / " + umUser + " / "
                                    + umGroupAsRuspostOperatorGroupId);

                            userIds.add(umUser);
                        }

                        return userIds.toArray(new Pair[0]);
                    }
                });

        log.error("getAvailableRuspostOperators end");
        return userIds;
    }

    /**
     * This function provides the data to send in the named state. TransitionID
     * SendTransitionDescription_hBDokXA_EealfcrKqoINvA State: Отправка
     * ответственному MessageType: Инцидент TransitionType SEND Destination
     * Subject Ответственный за решение
     *
     * @return // return the id of the transition to follow or null to stay in
     *         this state e.g. // return new
     *         SendStateResponse("transitionXXXXXXXX");
     * @generated
     */
    @RefinementGenerator(id = "SendStateDescription_SPOzA3A_EealfcrKqoINvA")
    public SendStateResponse sendStateОтправка_ответственному(
            SendStateRequest sendStateRequest) {
        de.metasonic.reftmpl.bo20automaticsend.runtime.Bo20AutomaticSendRuntime refinementRuntime = new de.metasonic.reftmpl.bo20automaticsend.runtime.Bo20AutomaticSendRuntime();
        de.metasonic.support.context.ApplicationContextProvider.getInstance()
                .getApplicationContext().getAutowireCapableBeanFactory()
                .autowireBean(refinementRuntime);
        refinementRuntime
                .setStateConfig(DATA_SendStateDescription_SPOzA3A_EealfcrKqoINvA);
        try {
            return (SendStateResponse) refinementRuntime.execute(
                    getSubjectInstance(), getSubjectDescription(), getClass()
                            .getClassLoader());
        } catch (Exception ex) {
            throw new RuntimeException(ex);
        }
    }

    /**
     * @generated
     */
    private static final String DATA_SendStateDescription_SPOzA3A_EealfcrKqoINvA = "PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0iVVRGLTgiPz48Y29uZmlndXJhdGlvbj48U1RBVEVfSUQgdmFsdWU9IlNlbmRTdGF0ZURlc2NyaXB0aW9uX1NQT3pBM0FfRWVhbGZjcktxb0lOdkEiLz48Ym8yMF9maWVsZC5ibyB2YWx1ZT0icnUuaXQubWV0YXNvbmljLnJ1c3Bvc3QuaW5mb3BvaW50LmRhdGEuZXZlbnRjYXJkLkV2ZW50Q2FyZEluaXRWaWV3Ii8+PGJvMjBfZmllbGQuZmllbGQgdmFsdWU9ImFzc2lnbmVkT3BlcmF0b3IiLz48Ym8yMF9maWVsZC50eXBlIHZhbHVlPSJTVFJJTkciLz48dXNlck9yR3JvdXBfZmllbGQudmFsdWUgdmFsdWU9InVzZXJfaWQiLz48Ym8yMF9maWVsZC5tb2RlIHZhbHVlPSJzaW5nbGUiLz48Ym8yMF9maWVsZC5zdG9yYWdlIHZhbHVlPSJCT1N0b3JhZ2VEZXNjcmlwdGlvbl8xSHBnMERlX0VlYVFzcEtDTlFPU25BIi8+PC9jb25maWd1cmF0aW9uPg==";
}