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();
    }
}

@@ -866,24 +866,33 @@
        %22    
+COALESCE(
 transactions
@@ -902,11 +902,24 @@
 edit
- - 
+, 0) - COALESCE(
 prof
@@ -929,16 +929,20 @@
 ers.debt
+, 0)
  AS bala
@@ -2112,36 +2112,81 @@
     
-return query.getResultList()
+List%3CDebtEntity%3E results = query.getResultList();%0A%0A        return results
 ;%0A  

fix statement