package fi.tekislauta.db.objects;

import fi.tekislauta.db.Database;
import fi.tekislauta.models.DatabaseObject;
import fi.tekislauta.models.Board;
import fi.tekislauta.models.Result;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.ArrayList;

/**
 * Created by Hugo on 14.10.2016.
 */
public class BoardDao implements DatabaseObject {

    public BoardDao() {}

    @Override
    public Object fetch(Database db, String id) throws SQLException {
        PreparedStatement statement = db.getConnection().prepareStatement("SELECT * FROM Board WHERE id= ?");
        statement.setInt(1, Integer.parseInt(id));

        ResultSet rs = statement.executeQuery();
        Board b = new Board();
        if (!rs.next()) {
            b.setError("Cannot find board with id " + id + " :(");
            return b;
        }

        b.setId(rs.getInt(1));
        b.setName(rs.getString(2));
        b.setAbbreviation(rs.getString("abbreviation"));
        b.setDescription(rs.getString(4));

        return b;
    }

    @Override
    public List<Object> fetchAll(Database db, String filter) throws SQLException {
        PreparedStatement statement = db.getConnection().prepareStatement("SELECT * FROM Board");

        ResultSet rs = statement.executeQuery();

        ArrayList<Board> res = new ArrayList();

        while (rs.next()) {
            Board b = new Board();

            b.setId(rs.getInt("id"));
            b.setName(rs.getString("name"));
            b.setAbbreviation(rs.getString("abbreviation"));
            b.setDescription(rs.getString("description"));
            res.add(b);
        }

        return (List)res;
    }

    @Override
    public Object post(Database db, Object o) throws SQLException {
        PreparedStatement statement = db.getConnection().prepareStatement("INSERT INTO Board (name, abbreviation, description) VALUES (?,?,?) ");
        Board b = (Board)o;
        statement.setString(1, b.getName());
        statement.setString(2, b.getAbbreviation());
        statement.setString(3, b.getDescription());
        int rs = statement.executeUpdate();

        if (rs == Statement.EXECUTE_FAILED) {
            b.setError("Query execution failed. Not inserted.");
            return b;
        }
        return b;
    }

    @Override
    public Object delete(Database db, String filter) throws SQLException {
        PreparedStatement statement = db.getConnection().prepareStatement("DELETE FROM Board WHERE id = ?");
        statement.setInt(1, Integer.parseInt(filter));
        int e = statement.executeUpdate();

        if (e == Statement.EXECUTE_FAILED) return new Result("Error");
        return new Result("Success");
    }

}

@@ -198,109 +198,9 @@
 sql.
-PreparedStatement;%0Aimport java.sql.ResultSet;%0Aimport java.sql.SQLException;%0Aimport java.sql.Statement
+*
 ;%0Aim
@@ -2355,180 +2355,463 @@
 n %7B%0A
-        PreparedStatement statement = db.getConnection().prepareStatement(%22DELETE FROM Board WHERE id = ?%22);%0A        statement.setInt(1, Integer.parseInt(filter));%0A
+%0A        Connection con = db.getConnection();%0A        con.setAutoCommit(false);%0A%0A        PreparedStatement deleteBoard;%0A        PreparedStatement deletePosts;%0A%0A        try %7B%0A            deleteBoard = con.prepareStatement(%22DELETE FROM Board WHERE id = ?%22);%0A            deletePosts = con.prepareStatement(%22DELETE FROM Post WHERE board_id = ?%22);%0A%0A            Integer boardId = Integer.parseInt(filter);%0A            deleteBoard.setInt(1, boardId);%0A   
         
 int 
@@ -2810,57 +2810,283 @@
     
+ 
 int 
-e = statement.executeUpdate();%0A%0A        if (e
+boardDeletionSuccess = deleteBoard.executeUpdate();%0A%0A            deletePosts.setInt(1, boardId);%0A            int postsDeletionSuccess = deletePosts.executeUpdate();%0A%0A            if (boardDeletionSuccess == Statement.EXECUTE_FAILED%0A                %7C%7C postsDeletionSuccess
  == 
@@ -3110,16 +3110,34 @@
 _FAILED)
+ %7B%0A               
  return 
@@ -3153,24 +3153,43 @@
 t(%22Error%22);%0A
+            %7D%0A%0A    
         retu
@@ -3210,21 +3210,303 @@
 %22Success%22);%0A
+        %7D catch (SQLException ex) %7B%0A            if (con != null) %7B%0A                con.rollback(); // rollback failed transaction%0A            %7D%0A            throw ex;%0A        %7D finally %7B%0A            if (con != null) %7B%0A                con.setAutoCommit(true);%0A            %7D%0A        %7D%0A
     %7D%0A%0A%7D%0A

Make DELETE /api/boards/:id also delete the board's posts