package hu.rbr.sfinapp.transaction;

import hu.rbr.sfinapp.core.db.BaseDao;
import org.sql2o.Connection;
import org.sql2o.Query;

import javax.inject.Inject;
import javax.inject.Singleton;
import javax.sql.DataSource;
import java.util.List;

@Singleton
public class TransactionDao extends BaseDao<Transaction> {

    @Inject
    public TransactionDao(DataSource dataSource) {
        super(dataSource, "transactions", Transaction.class);
    }

    public Transaction get(int id) {
        final String sql =
                "SELECT id, " +
                "       date, " +
                "       amount, " +
                "       description, " +
                "       account_id as accountId, " +
                "       to_account_id as toAccountId, " +
                "       comment " +
                "  FROM transactions" +
                " WHERE id = :id";

        Transaction transaction = get(sql, id);
        return loadTags(transaction);
    }

    public Transaction create(Transaction transaction) {
        final String sql =
                "INSERT INTO transactions " +
                "        (date, amount, description, account_id, to_account_id, comment) " +
                "VALUES (:date, :amount, :description, :accountId, :toAccountId, :comment)";

        try (Connection conn = sql2o.open()) {
            int newId = conn
                    .createQuery(sql, true)
                    .bind(transaction)
                    .executeUpdate()
                    .getKey(Integer.class);

            transaction.id = newId;
            saveTags(transaction);

            return get(newId);
        }
    }

    public Transaction update(int id, Transaction transaction) {
        final String sql =
                "UPDATE transactions" +
                "   SET date = :date, " +
                "       amount = :amount, " +
                "       description = :description, " +
                "       account_id = :accountId, " +
                "       to_account_id = :toAccountId, " +
                "       comment = :comment " +
                " WHERE id = :id";

        transaction.id = id;

        try (Connection conn = sql2o.open()) {
            conn.createQuery(sql)
                .bind(transaction)
                .executeUpdate();

            saveTags(transaction);

            return get(id);
        }
    }

    @Override
    public void delete(final int id) {
        deleteTags(id);
        super.delete(id);
    }

    private Transaction loadTags(Transaction transaction) {
        if (transaction == null) {
            return null;
        }

        transaction.tagIds = getTags(transaction.id);
        return transaction;
    }

    private List<Integer> getTags(final int transactionId) {
        final String sql =
                "SELECT tag_id " +
                "  FROM transaction_tags " +
                " WHERE transaction_id = :transactionId";

        try (Connection conn = sql2o.open()) {
            return conn
                    .createQuery(sql)
                    .addParameter("transactionId", transactionId)
                    .executeScalarList(Integer.class);
        }
    }

    private void saveTags(final Transaction transaction) {
        try (Connection conn = sql2o.open()) {
            final String deleteSql = "DELETE FROM transaction_tags " +
                                     " WHERE transaction_id = :transactionId";

            conn.createQuery(deleteSql)
                .addParameter("transactionId", transaction.id)
                .executeUpdate();


            final String insertSql = "INSERT INTO transaction_tags" +
                                     "       (transaction_id, tag_id) " +
                                     "VALUES (:transactionId, :tagId) ";

            Query query = conn.createQuery(insertSql);

            for (Integer tagId : transaction.tagIds) {
                query.addParameter("transactionId", transaction.id)
                     .addParameter("tagId", tagId)
                     .addToBatch();
            }

            query.executeBatch();
        }
    }

    private void deleteTags(final int transactionId) {
        final String sql =
                "DELETE FROM transaction_tags " +
                      "WHERE transaction_id = :transactionId";

        try (Connection conn = sql2o.open()) {
            conn.createQuery(sql)
                    .addParameter("transactionId", transactionId)
                    .executeUpdate();
        }
    }

}

@@ -1307,35 +1307,47 @@
 on conn = sql2o.
-ope
+beginTransactio
 n()) %7B%0A         
@@ -1580,32 +1580,38 @@
        saveTags(
+conn, 
 transaction);%0A%0A 
@@ -1601,32 +1601,60 @@
  transaction);%0A%0A
+            conn.commit();%0A%0A
             retu
@@ -2214,35 +2214,47 @@
 on conn = sql2o.
-ope
+beginTransactio
 n()) %7B%0A         
@@ -2365,24 +2365,30 @@
    saveTags(
+conn, 
 transaction)
@@ -2382,32 +2382,60 @@
  transaction);%0A%0A
+            conn.commit();%0A%0A
             retu
@@ -3289,24 +3289,41 @@
 id saveTags(
+Connection conn, 
 final Transa
@@ -3347,59 +3347,8 @@
 ) %7B%0A
-        try (Connection conn = sql2o.open()) %7B%0A    
     
@@ -3435,36 +3435,32 @@
                 
-   
- 
 %22 WHERE transact
@@ -3482,28 +3482,24 @@
 actionId%22;%0A%0A
-    
         conn
@@ -3625,28 +3625,24 @@
 ;%0A%0A%0A        
-   
- 
 final String
@@ -3712,36 +3712,32 @@
                 
-    
 %22       (transac
@@ -3782,36 +3782,32 @@
                 
-   
- 
 %22VALUES (:transa
@@ -3823,28 +3823,24 @@
 :tagId) %22;%0A%0A
-    
         Quer
@@ -3887,20 +3887,16 @@
         
-   
- 
 for (Int
@@ -3926,28 +3926,24 @@
 n.tagIds) %7B%0A
-    
             
@@ -4003,36 +4003,32 @@
                 
-   
- 
 .addParameter(%22t
@@ -4054,28 +4054,24 @@
             
-    
 .addToBatch(
@@ -4077,35 +4077,27 @@
 ();%0A        
-   
- 
 %7D%0A%0A
-    
         quer
@@ -4110,34 +4110,24 @@
 uteBatch();%0A
-        %7D%0A
     %7D%0A%0A    p

Save transactions with beginTransaction() and commit()