/*
 * Electronic Logistics Management Information System (eLMIS) is a supply chain management system for health commodities in a developing country setting.
 *
 * Copyright (C) 2015  John Snow, Inc (JSI). This program was produced for the U.S. Agency for International Development (USAID). It was prepared under the USAID | DELIVER PROJECT, Task Order 4.
 *
 * This program is free software: you can redistribute it and/or modify it under the terms of the GNU Affero General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */

package org.openlmis.report.mapper.lookup;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.openlmis.report.model.dto.Program;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface ProgramReportMapper {

    @Select("SELECT id, name, description, code " +
            "   FROM " +
            "       programs order by name")
    List<Program> getAll();

  @Select("SELECT id, name, description, code " +
      "   FROM " +
      "       programs where id in (select program_id from vw_user_facilities where user_id = #{userId}) " +
      " order by name")
  List<Program> getAllForUser(@Param("userId") Long userId);

    @Select("SELECT * FROM Programs where code = #{code}")
    Program getProgramByCode(@Param("code") String code);

    @Select("SELECT p.id id, p.name as name, p.description description,p.code code \n" +
            "             FROM programs p\n" +
            "   INNER JOIN regimens rg on rg.programid = p.id \n" +
            "   GROUP BY p.name,p.id,p.description,p.code\n" +
            "        ORDER BY name\n" )
    List<Program>getAllRegimenPrograms();

    @Select( "SELECT DISTINCT p.* \n" +
            "FROM programs p \n" +
            "INNER JOIN role_assignments ra ON p.id = ra.programId \n" +
            "INNER JOIN role_rights rr ON ra.roleId = rr.roleId \n" +
            "WHERE ra.userId = #{userId}\n" +
            "AND ra.supervisoryNodeId IS NOT NULL \n" +
            "AND p.active = TRUE \n" +
            "AND p.push = FALSE \n" +
             " UNION\n" +
            "SELECT DISTINCT p.* \n" +
            "FROM programs p\n" +
            "INNER JOIN programs_supported ps ON p.id = ps.programId\n" +
            "INNER JOIN role_assignments ra ON ra.programId = p.id\n" +
            "INNER JOIN role_rights rr ON rr.roleId = ra.roleId\n" +
            "WHERE ra.supervisoryNodeId IS NULL\n" +
            "AND p.active = TRUE\n" +
            "AND p.push = FALSE\n" +
            "AND ps.active= TRUE\n" +
            "AND ra.userId = #{userId}\n" +
            "ORDER BY name")
    List<Program> getUserSupervisedActivePrograms(@Param("userId") Long userId);
    @Select("   SELECT DISTINCT p.* \n" +
            "            FROM programs p\n" +
            "            INNER JOIN role_assignments ra ON p.id = ra.programId \n" +
            "            INNER JOIN role_rights rr ON ra.roleId = rr.roleId \n" +
            "            WHERE ra.userId = #{userId}\n" +
            "            AND (ra.supervisoryNodeId = #{nodeId} or #{nodeId}=0 ) \n" +
            "            AND p.active = TRUE \n" +
            "            AND p.push = FALSE")
    List<Program> getUserSupervisedActiveProgramsBySupervisoryNode(@Param("userId") Long userId, @Param("nodeId") Long supervisoryNodeId);
    @Select("   SELECT DISTINCT p.* \n" +
            "            FROM programs p\n" +
            "            INNER JOIN role_assignments ra ON p.id = ra.programId \n" +
            "            INNER JOIN role_rights rr ON ra.roleId = rr.roleId \n" +
            "            WHERE (ra.supervisoryNodeId = #{nodeId} or #{nodeId}=0 ) \n" +
            "            AND p.active = TRUE \n" +
            "            AND p.push = FALSE")
    List<Program> getUserSupervisedActiveAllProgramsBySupervisoryNode( @Param("nodeId") Long supervisoryNodeId);

    @Select("select * from programs where budgetingapplies = true")
    List<Program>getAllProgramsWithBudgeting();

}