/*
 *  Copyright 2019-2020 Zheng Jie
 *
 *  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.youchain.businessdata.repository;

import com.youchain.businessdata.domain.AgvTask;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Set;

/**
 * @author baobinglin
 * @website https://eladmin.vip
 * @date 2023-08-18
 **/
public interface AgvTaskRepository extends JpaRepository<AgvTask, Long>, JpaSpecificationExecutor<AgvTask> {

    /**
     * 根据容器去重
     *
     * @param stockCode 容器
     */
    @Query(value = " from AgvTask agv where agv.stockCode=:stockCode and agv.startSlotCode=:srcPointCode and agv.endSlotCode=:endPointCode  and agv.status not in ('CANCEL','FINISH') ")
    List<AgvTask> findRepeat(String stockCode, String srcPointCode, String endPointCode);

    /**
     * 查询待执行的备料、叫料、叫料返库任务
     * 'PICK','CALL_PICK','CALL_RETURN'
     */
    @Query(value = " from AgvTask agv where agv.type=:type and agv.status='OPEN' order by agv.createTime ")
    List<AgvTask> findOpenByAgvTask(String type);

    /**
     * 翻包返库时，查询起点是否有多条不同容器的任务(不包括当前任务)
     *
     * @param agvTaskId    任务
     * @param srcPointCode 点位
     */
    @Query(value = "select count(agv.id) from AgvTask agv where agv.startSlotCode=:srcPointCode and agv.id!=:agvTaskId and agv.status not in ('CANCEL','FINISH') ")
    int existsBySrcPoint(Long agvTaskId, String srcPointCode);

    /**
     * 本周任务数统计
     *
     * @return
     */
    @Query(value = " SELECT " +
            "    weekDay," +
            "    taskCount" +
            " FROM (" +
            "    SELECT " +
            "        0 AS sort_order," +
            "        NULL AS date," +
            "        '总计' AS weekDay," +
            "        SUM(task_count) AS taskCount" +
            "    FROM (" +
            "        SELECT " +
            "            DATE(create_time) AS date," +
            "            COUNT(id) AS task_count" +
            "        FROM data_agv_task  " +
            "        WHERE " +
            "        WEEK(create_time, 1) = WEEK(CURDATE(), 1)" +
            "        GROUP BY DATE(create_time)" +
            "    ) sub" +
            "    UNION ALL" +
            "    SELECT " +
            "        1 AS sort_order," +
            "        dr.date," +
            "        CASE " +
            "            WHEN WEEKDAY(dr.date) = 0 THEN '周一'" +
            "            WHEN WEEKDAY(dr.date) = 1 THEN '周二'" +
            "            WHEN WEEKDAY(dr.date) = 2 THEN '周三'" +
            "            WHEN WEEKDAY(dr.date) = 3 THEN '周四'" +
            "            WHEN WEEKDAY(dr.date) = 4 THEN '周五'" +
            "            WHEN WEEKDAY(dr.date) = 5 THEN '周六'" +
            "            WHEN WEEKDAY(dr.date) = 6 THEN '周日'" +
            "        END AS weekDay," +
            "        COALESCE(dt.task_count, 0) AS taskCount" +
            "    FROM " +
            "        (SELECT " +
            "            CURDATE() - INTERVAL (WEEKDAY(CURDATE())) DAY + INTERVAL weeks DAY AS date" +
            "         FROM " +
            "            (SELECT 0 AS weeks UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) weeks" +
            "        ) dr" +
            "    LEFT JOIN (" +
            "        SELECT " +
            "            DATE(create_time) AS date," +
            "            COUNT(id) AS task_count" +
            "        FROM data_agv_task  " +
            "        WHERE " +
            "        WEEK(create_time, 1) = WEEK(CURDATE(), 1)" +
            "        GROUP BY DATE(create_time)" +
            "    ) dt " +
            "    ON dr.date = dt.date" +
            " ) result" +
            " ORDER BY sort_order, date;", nativeQuery = true)
    List<Object[]> queryWeeklyTaskCounts();

    @Query(value = " from AgvTask agv where agv.status=:status ")
    List<AgvTask> findByStatus(String status);
}
