
/*
 * Copyright Contributors to the OpenCue Project
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */



package com.imageworks.spcue.dao.postgres;

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

import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.imageworks.spcue.DepartmentInterface;
import com.imageworks.spcue.EntityCreationError;
import com.imageworks.spcue.EntityModificationError;
import com.imageworks.spcue.EntityRemovalError;
import com.imageworks.spcue.GroupDetail;
import com.imageworks.spcue.GroupInterface;
import com.imageworks.spcue.JobInterface;
import com.imageworks.spcue.ShowInterface;
import com.imageworks.spcue.dao.GroupDao;
import com.imageworks.spcue.grpc.job.JobState;
import com.imageworks.spcue.util.CueUtil;
import com.imageworks.spcue.util.SqlUtil;

public class GroupDaoJdbc extends JdbcDaoSupport implements GroupDao {

    private static final int MAX_NESTING_LEVEL = 10;

    @Override
    public String getRootGroupId(ShowInterface show) {
        return getJdbcTemplate().queryForObject(
                "SELECT pk_folder FROM folder WHERE pk_show=? AND pk_parent_folder IS NULL",
                String.class, show.getShowId());
    }

    @Override
    public void deleteGroup(GroupInterface group) {

        if (childGroupCount(group) > 0) {
            throw new EntityRemovalError("failed to delete group " + group.getName() +
                    ", still has sub groups");
        }

        if (childJobCount(group) > 0) {
            throw new EntityRemovalError("failed to delete group " + group.getName() +
                    ", still has sub jobs");
        }

        // reparent all jobs to root group
        getJdbcTemplate().update(
                "UPDATE job SET pk_folder=? WHERE pk_folder=?",
                    getRootGroupId(group), group.getId());

        getJdbcTemplate().update(
                "DELETE FROM folder WHERE pk_parent_folder IS NOT NULL AND pk_folder=?", group.getId());
    }

    public static final String INSERT_GROUP =
        "INSERT INTO " +
            "folder " +
        "( " +
            "pk_folder," +
            "pk_parent_folder,"+
            "pk_show, " +
            "pk_dept,"+
            "str_name " +
        ") " +
        "VALUES (?,?,?,?,?)";

    @Override
    public void insertGroup(GroupDetail group) {
        group.id = SqlUtil.genKeyRandom();
        String parentId = group.parentId;
        try {
            getJdbcTemplate().update(INSERT_GROUP,
                    group.id, parentId, group.showId, group.deptId, group.name);
        } catch (Exception e) {
            throw new EntityCreationError("error creating group, " + e);
        }
    }

    @Override
    public void insertGroup(GroupDetail group, GroupInterface parent) {
        if (parent != null) {
            group.parentId = parent.getGroupId();
        }
        insertGroup(group);
    }

    @Override
    public void updateGroupParent(GroupInterface group, GroupInterface dest) {

        if (group.getGroupId().equals(dest.getGroupId())) {
            throw new EntityModificationError("error moving group, " +
                "cannot move group into itself");
        }

        if (!group.getShowId().equals(dest.getShowId())) {
            throw new EntityModificationError("error moving group, " +
                "cannot move groups between shows");
        }

        int recurse = 0;
        String destParent = dest.getGroupId();
        while (true) {
            destParent = getJdbcTemplate().queryForObject(
                    "SELECT pk_parent_folder FROM folder WHERE pk_folder=?",
                    String.class, destParent);
            if (destParent == null) { break; }
            if (destParent.equals(group.getGroupId())) {
                throw new EntityModificationError("error moving group, you cannot move a group " +
                        "into one of its sub groups");
            }
            recurse++;
            if (recurse > MAX_NESTING_LEVEL) {
                throw new EntityModificationError("error moving group, cannot tell " +
                        "if your moving a group into one of its sub groups");
            }
        }

        int result = getJdbcTemplate().update(
                "UPDATE folder SET pk_parent_folder=? WHERE pk_folder=? AND pk_parent_folder IS NOT NULL",
                dest.getId(), group.getId());

        recurseParentChange(group.getId(), dest.getId());
        if (result == 0) {
            throw new EntityModificationError("error moving group, "
                    + group.getName() + ", the group does not exist or its the top level group");
        }
    }

    @Override
    public void updateName(GroupInterface group, String value) {
        getJdbcTemplate().update(
                "UPDATE folder SET str_name=? WHERE pk_folder=?",
                value, group.getId());
    }

    @Override
    public void updateDepartment(GroupInterface group, DepartmentInterface dept) {
        getJdbcTemplate().update(
                "UPDATE folder SET pk_dept=? WHERE pk_folder=?",
                dept.getDepartmentId(), group.getId());
    }

    @Override
    public void updateDefaultJobMaxCores(GroupInterface group, int value) {
        if (value <= 0) { value = CueUtil.FEATURE_DISABLED; }
        if (value < CueUtil.ONE_CORE && value != CueUtil.FEATURE_DISABLED) {
            String msg = "The default max cores for a job must " +
                    "be greater than a single core";
            throw new IllegalArgumentException(msg);
        }
        getJdbcTemplate().update(
                "UPDATE folder SET int_job_max_cores=? WHERE pk_folder=?",
                value, group.getId());
    }

    @Override
    public void updateDefaultJobMinCores(GroupInterface group, int value) {
        if (value <= 0) { value = CueUtil.FEATURE_DISABLED; }
        if (value < CueUtil.ONE_CORE && value != CueUtil.FEATURE_DISABLED) {
            String msg = "The default min cores for a job must " +
                    "be greater than a single core";
            throw new IllegalArgumentException(msg);
        }
        getJdbcTemplate().update(
                "UPDATE folder SET int_job_min_cores=? WHERE pk_folder=?",
                value, group.getId());
    }

    @Override
    public void updateMaxCores(GroupInterface group, int value) {
        if (value < 0) { value = CueUtil.FEATURE_DISABLED; }
        if (value < CueUtil.ONE_CORE && value != CueUtil.FEATURE_DISABLED) {
            String msg = "The group max cores feature must " +
                    "be a whole core or greater, pass in: " + value;
            throw new IllegalArgumentException(msg);
        }

        getJdbcTemplate().update(
                "UPDATE folder_resource SET int_max_cores=? WHERE pk_folder=?",
                value, group.getId());
    }

    @Override
    public void updateMinCores(GroupInterface group, int value) {
        if (value < 0) { value = 0; }
        getJdbcTemplate().update(
                "UPDATE folder_resource SET int_min_cores=? WHERE pk_folder=?",
                value, group.getId());
    }

    private static final String IS_OVER_MIN_CORES =
        "SELECT " +
            "COUNT(1) " +
        "FROM " +
            "job,"+
            "folder_resource fr "+
        "WHERE " +
            "job.pk_folder = fr.pk_folder " +
        "AND " +
            "fr.int_cores > fr.int_min_cores " +
        "AND "+
            "job.pk_job = ?";

    @Override
    public boolean isOverMinCores(JobInterface job) {
        return getJdbcTemplate().queryForObject(IS_OVER_MIN_CORES,
                Integer.class, job.getJobId()) > 0;
    }

    @Override
    public void updateDefaultJobMaxGpus(GroupInterface group, int value) {
        if (value <= 0) { value = CueUtil.FEATURE_DISABLED; }
        getJdbcTemplate().update(
                "UPDATE folder SET int_job_max_gpus=? WHERE pk_folder=?",
                value, group.getId());
    }

    @Override
    public void updateDefaultJobMinGpus(GroupInterface group, int value) {
        if (value <= 0) { value = CueUtil.FEATURE_DISABLED; }
        getJdbcTemplate().update(
                "UPDATE folder SET int_job_min_gpus=? WHERE pk_folder=?",
                value, group.getId());
    }

    @Override
    public void updateMaxGpus(GroupInterface group, int value) {
        if (value < 0) { value = CueUtil.FEATURE_DISABLED; }

        getJdbcTemplate().update(
                "UPDATE folder_resource SET int_max_gpus=? WHERE pk_folder=?",
                value, group.getId());
    }

    @Override
    public void updateMinGpus(GroupInterface group, int value) {
        if (value < 0) { value = 0; }
        getJdbcTemplate().update(
                "UPDATE folder_resource SET int_min_gpus=? WHERE pk_folder=?",
                value, group.getId());
    }

    @Override
    public void updateDefaultJobPriority(GroupInterface group, int value) {
        if (value < 0) { value = CueUtil.FEATURE_DISABLED; }
        getJdbcTemplate().update(
                "UPDATE folder SET int_job_priority=? WHERE pk_folder=?",
                value, group.getId());
        if (value !=  CueUtil.FEATURE_DISABLED) {
            getJdbcTemplate().update(
                    "UPDATE job_resource SET int_priority=? WHERE pk_job IN (" +
                    "SELECT pk_job from job WHERE pk_folder=?)",
                    value, group.getId());
        }
    }

    private static final String GET_GROUP_DETAIL =
        "SELECT " +
            "folder.pk_folder, " +
            "folder.int_job_max_cores,"+
            "folder.int_job_min_cores,"+
            "folder.int_job_max_gpus,"+
            "folder.int_job_min_gpus,"+
            "folder.int_job_priority,"+
            "folder.str_name,"+
            "folder.pk_parent_folder,"+
            "folder.pk_show,"+
            "folder.pk_dept,"+
            "folder_level.int_level, " +
            "folder_resource.int_min_cores,"+
            "folder_resource.int_max_cores," +
            "folder_resource.int_min_gpus,"+
            "folder_resource.int_max_gpus " +
        "FROM " +
            "folder, "+
            "folder_level, " +
            "folder_resource " +
        "WHERE " +
            "folder.pk_folder = folder_level.pk_folder " +
        "AND " +
            "folder.pk_folder = folder_resource.pk_folder";

    private static final String GET_GROUP_DETAIL_BY_JOB =
        "SELECT " +
            "folder.pk_folder, " +
            "folder.int_job_max_cores,"+
            "folder.int_job_min_cores,"+
            "folder.int_job_max_gpus,"+
            "folder.int_job_min_gpus,"+
            "folder.int_job_priority,"+
            "folder.str_name,"+
            "folder.pk_parent_folder,"+
            "folder.pk_show,"+
            "folder.pk_dept,"+
            "folder_level.int_level, " +
            "folder_resource.int_min_cores,"+
            "folder_resource.int_max_cores," +
            "folder_resource.int_min_gpus,"+
            "folder_resource.int_max_gpus " +
        "FROM " +
            "folder, "+
            "folder_level, " +
            "folder_resource, " +
            "job "+
        "WHERE " +
            "folder.pk_folder = folder_level.pk_folder " +
        "AND " +
            "folder.pk_folder = folder_resource.pk_folder " +
        "AND " +
            "job.pk_folder = folder.pk_folder " +
        "AND " +
            "job.pk_job = ?";

    @Override
    public GroupDetail getGroupDetail(String id) {
        return getJdbcTemplate().queryForObject(
                GET_GROUP_DETAIL + " AND folder.pk_folder=?", GROUP_DETAIL_MAPPER, id);
    }

    @Override
    public GroupDetail getGroupDetail(JobInterface job) {
        return getJdbcTemplate().queryForObject(GET_GROUP_DETAIL_BY_JOB,
                GROUP_DETAIL_MAPPER, job.getId());
    }

    @Override
    public GroupDetail getRootGroupDetail(ShowInterface show) {
        return getJdbcTemplate().queryForObject(
                GET_GROUP_DETAIL + " AND folder.pk_show=? AND pk_parent_folder IS NULL",
                GROUP_DETAIL_MAPPER, show.getShowId());
    }

    @Override
    public GroupInterface getGroup(String id) {
        return getJdbcTemplate().queryForObject(
                "SELECT pk_show, pk_folder,str_name FROM folder WHERE pk_folder=?",
                GROUP_MAPPER, id);
    }

    @Override
    public List<GroupInterface> getGroups(List<String> idl) {
        return getJdbcTemplate().query(
                "SELECT pk_show, pk_folder, str_name FROM folder WHERE  " +
                SqlUtil.buildBindVariableArray("pk_folder", idl.size()),
                GROUP_MAPPER, idl.toArray());
    }

    @Override
    public List<GroupInterface> getChildrenRecursive(GroupInterface group) {
        List<GroupInterface> groups = new ArrayList<GroupInterface>(32);
        GroupInterface current = group;
        for (GroupInterface g: getChildren(current)) {
            current = g;
            groups.add(current);
            groups.addAll(getChildrenRecursive(current));
        }
        return groups;
    }

    @Override
    public List<GroupInterface> getChildren(GroupInterface group) {
        return getJdbcTemplate().query(
                "SELECT pk_show, pk_folder, str_name FROM folder WHERE pk_parent_folder = ?",
                GROUP_MAPPER, group.getGroupId());
    }

    private static final String IS_MANAGED =
    	"SELECT " +
    		"COUNT(1) " +
    	"FROM " +
    		"folder, " +
    		"point " +
    	"WHERE " +
    		"folder.pk_show = point.pk_show " +
    	"AND " +
    		"folder.pk_dept = point.pk_dept " +
    	"AND " +
    		"folder.b_exclude_managed = false " +
    	"AND " +
    		"point.b_managed = true " +
    	"AND " +
    		"folder.pk_folder = ?";

    @Override
    public boolean isManaged(GroupInterface group) {
    	return getJdbcTemplate().queryForObject(IS_MANAGED,
    			Integer.class, group.getGroupId()) > 0;
    }

    public static final RowMapper<GroupInterface> GROUP_MAPPER =
        new RowMapper<GroupInterface>() {
            public GroupInterface mapRow(final ResultSet rs, int rowNum) throws SQLException {
                return new GroupInterface() {
                    String id = rs.getString("pk_folder");
                    String show = rs.getString("pk_show");
                    String name = rs.getString("str_name");
                    public String getGroupId() { return id; }
                    public String getShowId() { return show; }
                    public String getId() { return id; }
                    public String getName() { return name; }
                };
        }
    };

    public static final RowMapper<GroupDetail> GROUP_DETAIL_MAPPER =
        new RowMapper<GroupDetail>() {
            public GroupDetail mapRow(ResultSet rs, int rowNum) throws SQLException {
                GroupDetail group = new GroupDetail();
                group.id = rs.getString("pk_folder");
                group.jobMaxCores = rs.getInt("int_job_max_cores");
                group.jobMinCores = rs.getInt("int_job_min_cores");
                group.jobMaxGpus = rs.getInt("int_job_max_gpus");
                group.jobMinGpus = rs.getInt("int_job_min_gpus");
                group.jobPriority = rs.getInt("int_job_priority");
                group.minCores = rs.getInt("int_min_cores");
                group.maxCores = rs.getInt("int_max_cores");
                group.minGpus = rs.getInt("int_min_gpus");
                group.maxGpus = rs.getInt("int_max_gpus");
                group.name = rs.getString("str_name");
                group.parentId = rs.getString("pk_parent_folder");
                group.showId = rs.getString("pk_show");
                group.deptId = rs.getString("pk_dept");
                return group;
        }
    };


    private int childGroupCount(GroupInterface group) {
        return getJdbcTemplate().queryForObject(
                "SELECT COUNT(*) FROM folder WHERE pk_parent_folder=?",
                Integer.class, group.getId());
    }

    private int childJobCount(GroupInterface group) {
        return getJdbcTemplate().queryForObject(
                "SELECT COUNT(*) FROM job WHERE pk_folder=? AND str_state=?",
                Integer.class, group.getId(), JobState.PENDING.toString());
    }

    private void recurseParentChange(final String folderId, final String newParentId) {
        getJdbcTemplate().call(new CallableStatementCreator() {

            public CallableStatement createCallableStatement(Connection con) throws SQLException {
                CallableStatement c = con.prepareCall("{ call recurse_folder_parent_change(?,?) }");
                c.setString(1, folderId);
                c.setString(2, newParentId);
                return c;
            }
        }, new ArrayList<SqlParameter>());
    }
}

