package sorescu.db;

import groovy.util.Eval;
import oracle.jdbc.driver.OracleConnection;
import oracle.sql.BLOB;
import oracle.sql.CLOB;
import org.apache.commons.lang3.tuple.Pair;
import org.springframework.jdbc.core.JdbcTemplate;
import sun.misc.IOUtils;

import java.sql.*;
import java.sql.Array;
import java.time.LocalDate;
import java.util.*;
import java.util.Date;
import java.util.stream.Collectors;

public class OracleJdbcConnector {
    public final OracleConnection connection;

    public OracleJdbcConnector(String url, String user, String pass) {
        connection = (OracleConnection)TryGet(()->DriverManager.getConnection(url, user, pass));
    }

    @Deprecated
    public OracleJdbcConnector(JdbcTemplate jdbcTemplate) {
        try {
            this.connection = (OracleConnection) jdbcTemplate.getDataSource().getConnection();
        } catch (Throwable t) {
            throw new RuntimeException(t);
        }
    }

    private PreparedStatement prepareStatement(String query) throws SQLException {
        return connection.prepareStatement(query);
    }

    private String getTypeFor(Object o) {
        Set<String> types = new HashSet<>();
        int length = java.lang.reflect.Array.getLength(o);
        for (int i = 0; i < length; i++) {
            Object oo = java.lang.reflect.Array.get(o, i);
            if (oo instanceof Number) types.add("NUMARRAY");
            else if (oo instanceof String) types.add("STRARRAY");
            else if (oo == null) {/*do nothing*/} else throw new RuntimeException("Type not treated: " + oo.getClass());
        }
        if (types.size() > 1)
            throw new RuntimeException("Too many types: " + types);
        return (String) types.toArray()[0];
    }

    public Optional<Map<String, Object>> selectUnique(String query, Object... args) {
        OracleJdbcRowset result = select(query, args);
        if (result.size() == 0) return Optional.empty();
        if (result.size() > 1)
            throw new RuntimeException("Too many results (" + result.size() + ") in Unique for `" + query + "`");
        return Optional.of(result.get(0));
    }

    public Map<String, Object> selectUniqueCached(String query, Object... args) {
        OracleJdbcRowset result = selectCached(query, args);
        if (result.size() == 0)
            throw new RuntimeException("No result in SelectUnique for `" + query + "`");
        if (result.size() == 1) return result.get(0);
        throw new RuntimeException("Too many results (" + result.size() + ") in SelectUnique for `" + query + "`");

    }

    public OracleJdbcRowset select(String query, Object... args) {
        try {
            System.out.println(connection.getUserName() + "@" + connection.getURL());
            System.out.println("SELECT: " + query.trim());
            try (PreparedStatement statement = prepareStatement(query)) {
                for (int i = 0; i < args.length; i++) {
                    if (args[i] != null) {
                        if (args[i] instanceof Date) {
                            args[i] = new java.sql.Timestamp(((Date) args[i]).getTime());
                        }
                        if (args[i].getClass().isArray()) {
                            String type = getTypeFor(args[i]);
                            Array array = connection.createARRAY(type, args[i]);
                            args[i] = array;
                        }
                    }
                    System.out.println("queryArgs[" + i + "]: " + args[i] + " - " + ((args[i] == null) ? "NULL" : (args[i].getClass())));
                    if (args[i] instanceof Array)
                        statement.setArray(i + 1, (Array) args[i]);
                    else
                        statement.setObject(i + 1, args[i]);
                }
                statement.execute();
                ResultSet resultSet = statement.getResultSet();
                int columnsCount = resultSet.getMetaData().getColumnCount();
                List<String> headers = new ArrayList<>();
                for (int columnIndex = 0; columnIndex < columnsCount; columnIndex++)
                    headers.add(resultSet.getMetaData().getColumnLabel(columnIndex + 1));
                List<Map<String, Object>> result = new LinkedList<>();
                while (resultSet.next()) {
                    Map<String, Object> row = new HashMap<>();
                    for (int columnIndex = 0; columnIndex < columnsCount; columnIndex++) {
                        Object data = resultSet.getObject(columnIndex + 1);
                        if (data instanceof oracle.sql.BLOB)
                            data = IOUtils.readFully(((BLOB) data).getStream(), -1, true);
                        if (data instanceof oracle.sql.CLOB)
                            data = ((CLOB) data).getSubString(1, (int) ((CLOB) data).length());
                        row.put(headers.get(columnIndex), data);
                    }
                    result.add(row);
                }
                OracleJdbcRowset rs = new OracleJdbcRowset(result, headers, this, query, args);
                return rs;
            }
        } catch (Throwable t) {
            throw new RuntimeException(t);
        }
    }

    public boolean execute(String query, Object... args) {
        try {
            System.out.println("EXECUTE: " + query.trim());
            try (PreparedStatement statement = prepareStatement(query)) {
                for (int i = 0; i < args.length; i++) {
                    if (args[i] != null) {
                        if (args[i] instanceof LocalDate) {
                            args[i] = java.sql.Timestamp.valueOf(((LocalDate) args[i]).atStartOfDay());
                        } else if (args[i] instanceof Date) {
                            args[i] = new java.sql.Timestamp(((Date) args[i]).getTime());
                        } else if (args[i].getClass().isArray()) {
                            if (args[i].getClass().getComponentType().getTypeName().equals("byte")) {
                                args[i] = connection.createBlob(((byte[]) args[i]));
                            } else {
                                String type = getTypeFor(args[i]);
                                Array array = connection.createARRAY(type, args[i]);
                                args[i] = array;
                            }
                        }
                    }
                    System.out.println("queryArgs[" + i + "] = " + args[i] + " - " + ((args[i] == null) ? "null" : (args[i].getClass())));
                    if (args[i] instanceof Array)
                        statement.setArray(i + 1, (Array) args[i]);
                    else
                        statement.setObject(i + 1, args[i]);
                }
                return statement.execute();
            }
        } catch (SQLIntegrityConstraintViolationException icve) {
            throw new RuntimeException(icve);
        } catch (Throwable t) {
            throw new RuntimeException(t);
        }
    }

    private Map<Pair<String, List<Object>>, OracleJdbcRowset> cache = new HashMap<>();

    public OracleJdbcRowset selectCached(String query, Object... arguments) {
        Pair<String, List<Object>> key = Pair.of(query, Arrays.asList(arguments));
        return cache.computeIfAbsent(key, k -> select(query, arguments));
    }

    public Optional<Map<String, Object>> selectFirst(String query, Object... args) {
        return selectUnique("WITH wrapper_SelectFirst as (" + query + ") select * from wrapper_SelectFirst where rownum<2", args);
    }


    public OracleJdbcRowset getAt(String tableName, Map<String, Object> fields) {
        List<String> keys = new ArrayList<>(fields.keySet());
        String sql = "SELECT * FROM " + tableName + " WHERE " + keys.stream().map(it -> it + "=?").collect(Collectors.joining(" AND "));
        List<Object> arguments = keys.stream().map(fields::get).collect(Collectors.toList());
        return select(sql, arguments.toArray());
    }

    public void deleteAt(String tableName, Map<String, Object> fields) {
        List<String> keys = new ArrayList<>(fields.keySet());
        String sql = "DELETE FROM " + tableName + " WHERE " + keys.stream().map(it -> it + "=?").collect(Collectors.joining(" AND "));
        List<Object> arguments = keys.stream().map(fields::get).collect(Collectors.toList());
        execute(sql, arguments.toArray());
    }

    public OracleJdbcRowset getAt(String tableName, String key, Object value) {
        String sql = "SELECT * FROM " + tableName + " WHERE " + key + "=?";
        return select(sql, value);
    }

    public boolean add(String tableName, Map<String, Object> fields) {
        List<String> keys = fields.entrySet().stream().filter(it -> it.getValue() != null).map(Map.Entry::getKey).collect(Collectors.toList());
        String sql = "INSERT into " + tableName + " (" + keys.stream().collect(Collectors.joining(",")) + ") values (" +
                keys.stream().map(key -> (fields.get(key) instanceof OracleJdbcExpression) ? ("(" + ((OracleJdbcExpression) fields.get(key)).code + ")") : "?").collect(Collectors.joining(",")) + ")";
        List<Object> arguments = keys.stream().filter(key -> !(fields.get(key) instanceof OracleJdbcExpression)).map(fields::get).collect(Collectors.toList());
        return execute(sql, arguments.toArray());
    }
}

package sorescu.db;//db connectivity
public class OracleJdbcRowset extends LinkedList<Map<String, Object>> {
    public final List<String> headers = new LinkedList<>();
    public final String query;
    public final Object[] parameters;
    public final OracleJdbcConnector connector;

    public OracleJdbcRowset(List<Map<String, Object>> result, List<String> headers, OracleJdbcConnector connector, String query, Object[] args) {
        this.addAll(result);
        this.headers.addAll(headers);
        this.connector = connector;
        this.query = query;
        this.parameters = args;
    }

    public List<String> getHeaders() {
        return headers;
    }
}

def conn=new OracleJdbcConnector(URL,USER,PASS);
println conn.select("select 1+? from dual where 2=?",3,4);
println conn.execute("update table where field=? and expr=?+4",value1,value2);