/*
 * This program was produced for the U.S. Agency for International Development. It was prepared by the USAID | DELIVER PROJECT, Task Order 4. It is part of a project which utilizes code originally licensed under the terms of the Mozilla Public License (MPL) v2 and therefore is licensed under MPL v2 or later.
 *
 * This program is free software: you can redistribute it and/or modify it under the terms of the Mozilla Public License as published by the Mozilla Foundation, either version 2 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 Mozilla Public License for more details.
 *
 * You should have received a copy of the Mozilla Public License along with this program. If not, see http://www.mozilla.org/MPL/
 */

package org.openlmis.rnr.repository.mapper;

import org.apache.ibatis.annotations.*;
import org.openlmis.core.domain.Product;
import org.openlmis.rnr.domain.EquipmentLineItem;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface EquipmentLineItemMapper {

  @Insert("INSERT INTO equipment_status_line_items (rnrId, code, equipmentName, equipmentCategory, equipmentModel, equipmentSerial, equipmentInventoryId, operationalStatusId, testCount, totalCount, daysOutOfUse, remarks, createdBy, createdDate, modifiedBy, modifiedDate) " +
                                    "values " +
                                    "(#{rnrId}, #{code}, #{equipmentName}, #{equipmentCategory}, #{equipmentModel}, #{equipmentSerial}, #{equipmentInventoryId}, #{operationalStatusId}, #{testCount}, #{totalCount}, #{daysOutOfUse}, #{remarks}, #{createdBy}, #{createdDate}, #{modifiedBy}, #{modifiedDate})")
  @Options(useGeneratedKeys = true)
  Integer insert(EquipmentLineItem item);

  @Update("UPDATE equipment_status_line_items " +
      " SET " +
      "code = #{code}, equipmentName = #{equipmentName}, equipmentCategory = #{equipmentCategory}, equipmentModel = #{equipmentModel}, equipmentSerial = #{equipmentSerial}, equipmentInventoryId = #{equipmentInventoryId} " +
      " , operationalStatusId = #{operationalStatusId}, testCount = #{testCount}, totalCount = #{totalCount}, daysOutOfUse = #{daysOutOfUse}, remarks = #{remarks}, modifiedBy = #{modifiedBy}, modifiedDate = NOW()" +
      " where id = #{id}")
  Integer update(EquipmentLineItem item);

  @Select("SELECT esli.id, sq.id as programEquipmentId, esli.* from equipment_status_line_items esli " +
      "JOIN equipment_inventories inv ON esli.equipmentInventoryId = inv.id " +
      " LEFT JOIN ( select * from equipment_programs e where e.programId in " +
      " (select max(programId) from requisitions where id = #{rnrId} ) ) sq" +
      " ON sq.equipmentId = inv.equipmentId" +
      " where " +
      " rnrId = #{rnrId} ")
  @Results(
      value = {
          @Result(property = "id", column = "id"),
          @Result(property = "relatedProducts", javaType = List.class, column = "id", many = @Many(select = "org.openlmis.rnr.repository.mapper.EquipmentLineItemMapper.getRelatedRnrLineItems"))
  })
  List<EquipmentLineItem> getEquipmentLineItemsByRnrId(@Param("rnrId") Long rnrId);


  @Select("select rli.id, p.primaryName, p.code from " +
                " requisitions r " +
      "         JOIN requisition_line_items rli on r.id = rli.rnrId " +
      "         JOIN products p on p.code::text = rli.productCode::text " +
      "         JOIN equipment_status_line_items esli on esli.rnrId = r.id " +
      "         JOIN equipment_programs pe on pe.programId = r.programId " +
      "         JOIN equipment_products ep on pe.id = ep.programEquipmentId " +
      "               and p.id = ep.productId " +
      " WHERE " +
      "       esli.id = #{id}")

   List<Product> getRelatedRnrLineItems(@Param("id") Long id);

  @Select("select * from equipment_status_line_items where id = #{id}")
  EquipmentLineItem getById( @Param("id") Long id);
}

@@ -2532,16 +2532,30 @@
  esli.id
+ %22 +%0A      %22  
 , sq.id 
@@ -2579,21 +2579,46 @@
 ntId
-, esli.* from
+ %22 +%0A      %22  , esli.* %22 +%0A      %22FROM
  equ
@@ -2658,16 +2658,18 @@
 %0A      %22
+  
 JOIN equ
@@ -2740,16 +2740,17 @@
 %0A      %22
+ 
  LEFT JO
@@ -2757,22 +2757,75 @@
 IN (
- select * from
+SELECT etp.* %22 +%0A      %22    , e.id AS equipmentId %22 +%0A      %22  FROM
  equ
@@ -2823,32 +2823,37 @@
  FROM equipment_
+type_
 programs e where
@@ -2850,49 +2850,130 @@
 ms e
- where e.programId in %22 +%0A      %22 (select
+tp %22 +%0A      %22    JOIN equipments e ON etp.equipmentTypeId = e.equipmentTypeId %22 +%0A      %22  WHERE etp.programId IN (SELECT
  max
@@ -3002,21 +3002,21 @@
 sitions 
-where
+WHERE
  id = #%7B
@@ -3025,26 +3025,13 @@
 rId%7D
- ) 
+)
 ) sq
-%22 +%0A      %22
  ON 
@@ -3062,33 +3062,16 @@
 ipmentId
-%22 +%0A      %22 where
  %22 +%0A   
@@ -3074,16 +3074,21 @@
 %0A      %22
+WHERE
  rnrId =
@@ -3816,16 +3816,21 @@
 uipment_
+type_
 programs
@@ -3898,16 +3898,21 @@
 uipment_
+type_
 products
@@ -3937,32 +3937,36 @@
 programEquipment
+Type
 Id %22 +%0A      %22  

Fix SQL in EquipmentLineItemMapper from db changes