package io.teiler.server.persistence.repositories;

import io.teiler.server.persistence.entities.DebtEntity;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import javax.persistence.*;
import java.util.List;

/**
 * Provides database-related operations for debts.
 *
 * @author dthoma
 */
@Repository
public class DebtRepository {

    static final String STATEMENT_SQLMAP = "GET_DEBT_QUERY";

    @Autowired
    private EntityManager entityManager;

    /**
     * Returns a {@link List} of {@link DebtEntity} in the Group with the given Id.
     *
     * @param groupId Id of the Group
     * @return {@link List} of {@link DebtEntity}
     */
    public List<DebtEntity> get(String groupId) {
        String sql =
                "SELECT" +
                "    person.id AS person," +
                "    transactions.credit - profiteers.debt AS balance" +
                "FROM person" +
                "LEFT JOIN (" +
                "    SELECT " +
                "        transaction.payer," +
                "        SUM(profiteer.share) AS credit" +
                "    FROM transaction" +
                "    LEFT JOIN profiteer ON profiteer.transaction=transaction.id" +
                "    LEFT JOIN person ON person.id=profiteer.person" +
                "    WHERE person.\"group\"=?1" +
                "    GROUP BY transaction.payer" +
                ") AS transactions ON transactions.payer=person.id" +
                "LEFT JOIN (" +
                "    SELECT " +
                "        profiteer.person," +
                "        SUM(profiteer.share) AS debt" +
                "    FROM profiteer" +
                "    LEFT JOIN person ON person.id=profiteer.person" +
                "    WHERE person.\"group\"=?1" +
                "    GROUP BY profiteer.person" +
                ") AS profiteers ON profiteers.person=person.id";

        Query query = entityManager.createNativeQuery(sql, STATEMENT_SQLMAP);
        query.setParameter(1, groupId);

        return query.getResultList();
    }
}

@@ -840,24 +840,25 @@
 d AS person,
+ 
 %22 +%0A        
@@ -918,16 +918,17 @@
  balance
+ 
 %22 +%0A    
@@ -947,24 +947,25 @@
 %22FROM person
+ 
 %22 +%0A        
@@ -976,32 +976,33 @@
     %22LEFT JOIN (
+ 
 %22 +%0A            
@@ -1009,32 +1009,33 @@
     %22    SELECT 
+ 
 %22 +%0A            
@@ -1065,16 +1065,17 @@
 n.payer,
+ 
 %22 +%0A    
@@ -1125,16 +1125,17 @@
 S credit
+ 
 %22 +%0A    
@@ -1247,24 +1247,25 @@
 ansaction.id
+ 
 %22 +%0A        
@@ -1315,32 +1315,33 @@
 profiteer.person
+ 
 %22 +%0A            
@@ -1366,32 +1366,33 @@
 son.%5C%22group%5C%22=?1
+ 
 %22 +%0A            
@@ -1426,16 +1426,17 @@
 on.payer
+ 
 %22 +%0A    
@@ -1497,16 +1497,17 @@
 erson.id
+ 
 %22 +%0A    
@@ -1530,16 +1530,17 @@
 T JOIN (
+ 
 %22 +%0A    
@@ -1563,16 +1563,17 @@
  SELECT 
+ 
 %22 +%0A    
@@ -1610,16 +1610,17 @@
 .person,
+ 
 %22 +%0A    
@@ -1668,16 +1668,17 @@
  AS debt
+ 
 %22 +%0A    
@@ -1708,16 +1708,17 @@
 rofiteer
+ 
 %22 +%0A    
@@ -1772,32 +1772,33 @@
 profiteer.person
+ 
 %22 +%0A            
@@ -1831,16 +1831,17 @@
 oup%5C%22=?1
+ 
 %22 +%0A    
@@ -1882,16 +1882,17 @@
 r.person
+ 
 %22 +%0A    
@@ -1950,16 +1950,17 @@
 erson.id
+ 
 %22;%0A%0A    

fix query format