package de.isnow.sqlws.util;

import de.isnow.sqlws.model.WsColumn;
import de.isnow.sqlws.model.WsTable;
import lombok.SneakyThrows;

import javax.persistence.Query;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.Collection;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

public class DbUtil {

    @SneakyThrows
    public static PreparedStatement createLimitedReadQuery(
            WsTable table,
            Long firstRow,
            Long numRows,
            Collection<String> columnsToShow,
            Connection conn) {
        return createLimitedReadQuery(
                table, firstRow, numRows,
                columnsToShow,null, conn);
    }

    @SneakyThrows
    public static PreparedStatement createLimitedReadQuery(
            WsTable table,
            Long firstRow,
            Long numRows,
            Collection<String> columnsToShow,
            Map<WsColumn, String> columnFilters,
            Connection conn) {
        if (null == columnsToShow)
            columnsToShow = table.getColumnsByName().keySet();
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT ");
        String cols = String.join(", ", columnsToShow);
        sb.append(cols);
        sb.append(" FROM ");
        sb.append(table.getFullName());
        if (null != columnFilters) {
            for (WsColumn col : columnFilters.keySet()) {
                String clause = null;
                switch (col.getDataType()) {
                    case "character":
                        clause = " WHERE LOWER(" + col.getName() + ") like ? ESCAPE '!'";
                        break;
                    case "integer":
                        clause = " WHERE " + col.getName() + " = ?";
                        break;
                    case "real":
                        clause = " WHERE " + col.getName() + " = ?";
                        break;
                    default:
                        clause = " WHERE " + col.getName() + " = ?";
                        break;
                }
                sb.append(clause);
            }
        }
        if (null != numRows)
            //sb.append(" fetch first "+numRows + " rows only");
            sb.append(" limit "+numRows+ " offset "+firstRow);

        PreparedStatement p = conn.prepareStatement(sb.toString());
        int cnt = 1;

        if (null != columnFilters) {
            for (WsColumn col : columnFilters.keySet()) {
                String qStr = columnFilters.get(col);
                switch (col.getDataType()) {
                    case "character":
                        qStr = qStr.toLowerCase()
                                .replace("!", "!!")
                                .replace("%", "!%")
                                .replace("_", "!_")
                                .replace("[", "![");
                        p.setString(cnt++, "%" + qStr + "%");
                        break;
                    case "integer":
                        Long qNum = Long.parseLong(qStr);
                        p.setLong(cnt++, qNum);
                        break;
                    case "real":
                        Double qDouble = Double.parseDouble(qStr);
                        p.setDouble(cnt++, qDouble);
                        break;
                    default:
                        p.setString(cnt++, columnFilters.get(col));
                        break;
                }
            }
        }
        System.out.println(p.toString());
        return p;
    }
}

@@ -243,85 +243,9 @@
 til.
-Collection;%0Aimport java.util.List;%0Aimport java.util.Map;%0Aimport java.util.Set
+*
 ;%0Aim
@@ -1307,32 +1307,86 @@
 olumnFilters) %7B%0A
+            List%3CString%3E clauses = new ArrayList%3C%3E();%0A
             for 
@@ -1582,22 +1582,16 @@
 ause = %22
- WHERE
  LOWER(%22
@@ -1727,36 +1727,24 @@
     clause =
- %22 WHERE %22 +
  col.getName
@@ -1849,35 +1849,143 @@
    clause = 
-%22 WHERE %22 +
+col.getName() + %22 = ?%22;%0A                        break;%0A                    case %22bit%22:%0A                        clause =
  col.getName
@@ -2093,20 +2093,8 @@
 se =
- %22 WHERE %22 +
  col
@@ -2183,24 +2183,173 @@
     
-sb.append(clause
+clauses.add(clause);%0A            %7D%0A            if (clauses.size() != 0) %7B%0A                sb.append(%22 WHERE %22 + clauses.stream().collect(Collectors.joining(%22 AND %22))
 );%0A 
@@ -3552,32 +3552,211 @@
          break;%0A
+                    case %22bit%22:%0A                        Boolean qBoolean = ((null != qStr));%0A                        p.setBoolean(cnt++, qBoolean);%0A                        break;%0A
                 

support for boolean filters