/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.ctecinf;

import br.com.ctecinf.autocomplete.AutoCompleteModel;
import br.com.ctecinf.swing.OptionPane;
import br.com.ctecinf.table.TableModel;
import br.com.ctecinf.text.DateFormatter;
import br.com.ctecinf.text.NumberFormatter;
import br.com.ctecinf.text.TimeFormatter;
import br.com.ctecinf.text.TimestampFormatter;
import java.io.File;
import java.math.BigDecimal;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

/**
 *
 * @author Cássio Conceição
 * @since 06/11/2018
 * @version 201811
 * @see http://ctecinf.com.br/
 */
public class Database {

    public static final String[] JDBC = {"jdbc:firebirdsql:", "jdbc:mysql://", "jdbc:postgresql://", "jdbc:derby:", "jdbc:oracle:thin:"};

    /**
     * firebird, derby, mysql, postgres, oracle. <br>
     * Default "firebird"
     */
    protected static String DB_JDBC = Config.get("db.jdbc").isEmpty() ? "firebird" : Config.get("db.jdbc");

    /**
     * Default "localhost"
     */
    protected static String DB_HOST = Config.get("db.host").isEmpty() ? "localhost" : Config.get("db.host");

    /**
     * Nome ou caminho do banco de dados.<br>
     * Default "data.fdb" na raíz do sistema
     */
    protected static String DB_NAME = Config.get("db.name").isEmpty() ? new File("data.fdb").getAbsolutePath() : Config.get("db.name");

    /**
     * Default "sysdba"
     */
    protected static String DB_USER = Config.get("db.user").isEmpty() ? "sysdba" : Config.get("db.user");

    /**
     * Default "masterkey"
     */
    protected static String DB_PASS = Config.get("db.pass").isEmpty() ? "masterkey" : Config.get("db.pass");

    private static String getURL(String dbname) throws SQLException {

        String jdbc = DB_JDBC;
        String host = DB_HOST;
        String name = dbname;
        String url = null;

        for (String str : JDBC) {

            if (str.contains(jdbc.toLowerCase())) {

                if (jdbc.equalsIgnoreCase("firebird") || jdbc.equalsIgnoreCase("derby")) {
                    name = new File(name).getAbsolutePath();
                }

                url = str + host + str.substring(str.length() - 1) + name;
            }
        }

        if (url == null || url.contains("null")) {
            throw new SQLException("Falta parâmetro na URL[" + url + "].");
        }

        return url;
    }

    private static String getURL() throws SQLException {
        return getURL(DB_NAME);
    }

    /**
     * Abre conexão com o banco de dados
     *
     * @return Connection
     * @throws SQLException
     */
    public static Connection openConnection() throws SQLException {
        return DriverManager.getConnection(getURL(), DB_USER, DB_PASS);
    }

    /**
     * Migra dados de uma tabela com mesmo metadados de um banco para outro
     *
     * @param table
     * @throws SQLException
     */
    public static void exportData(String table) throws SQLException {
        exportData(table, null);
    }

    /**
     * Migra dados de uma tabela com mesmo metadados de um banco para outro
     *
     * @param table
     * @param fromWhere
     * @throws SQLException
     */
    public static void exportData(String table, String fromWhere) throws SQLException {

        String toURL = getURL(Config.get("db.name.migrate"));

        List<String> cols = new ArrayList();
        List<String> paramsInsert = new ArrayList();
        List<String> paramsUpdate = new ArrayList();

        String columnId = null;

        try (Connection conn = openConnection(); ResultSet rs = conn.getMetaData().getPrimaryKeys(null, null, table)) {
            if (rs.next()) {
                columnId = rs.getString(4).toLowerCase().trim();
            }
        }

        if (columnId == null) {
            throw new SQLException("Tabela [" + table + "] não possui chave primária.");
        }

        String create = "CREATE TABLE " + table + " (";
        String sequence = "seq_" + table + "_" + columnId;

        try (Connection conn = openConnection(); ResultSet rs = conn.getMetaData().getColumns(null, null, table, "%")) {

            while (rs.next()) {

                String name = rs.getString(4).toLowerCase();
                String type = rs.getString(6);

                if (rs.getInt(5) == Types.DECIMAL || rs.getInt(5) == Types.DOUBLE || rs.getInt(5) == Types.FLOAT || rs.getInt(5) == Types.NUMERIC) {
                    type += "(" + rs.getInt(7) + "," + rs.getInt(9) + ")";
                }

                if (rs.getInt(5) == Types.CHAR || rs.getInt(5) == Types.LONGNVARCHAR || rs.getInt(5) == Types.NCHAR || rs.getInt(5) == Types.NVARCHAR || rs.getInt(5) == Types.VARCHAR) {
                    type += "(" + rs.getInt(7) + ")";
                }

                if (!name.equalsIgnoreCase(columnId)) {
                    cols.add(name);
                    paramsInsert.add("?");
                    paramsUpdate.add(name + " = ?");
                }

                if (name.equalsIgnoreCase(columnId)) {
                    create += name + " " + type + " NOT NULL PRIMARY KEY" + (rs.getString(23).equalsIgnoreCase("yes") ? " AUTO_INCREMENT" : "") + ",";
                } else {
                    create += name + " " + type + (rs.getString(18).equalsIgnoreCase("yes") ? "" : " NOT NULL") + ",";
                }
            }

            create = create.substring(0, create.length() - 1) + ")";
        }

        boolean exist = false;

        try (Connection conn = DriverManager.getConnection(toURL, DB_USER, DB_PASS); ResultSet rs = conn.getMetaData().getTables(null, null, "%", new String[]{"TABLE"})) {
            while (rs.next()) {
                if (table.equalsIgnoreCase(rs.getString(3))) {
                    exist = true;
                    break;
                }
            }
        }

        if (!exist) {

            try (
                    Connection conn = DriverManager.getConnection(toURL, DB_USER, DB_PASS);
                    Statement st = conn.createStatement();
                    Connection conn2 = openConnection();
                    Statement st2 = conn2.createStatement();
                    ResultSet rs = st2.executeQuery("SELECT MAX(" + columnId + ") FROM " + table)) {

                st.execute(create);
                st.execute("CREATE SEQUENCE " + sequence);

                if (rs.next()) {
                    st.execute("SET SEQUENCE " + sequence + " TO " + rs.getObject(1));
                }
            }
        }

        Object max = null;

        try (Connection conn = DriverManager.getConnection(toURL, DB_USER, DB_PASS); Statement st = conn.createStatement(); ResultSet rs = st.executeQuery("SELECT MAX(" + columnId + ") FROM " + table)) {
            if (rs.next()) {
                max = rs.getObject(1);
            }
        }

        String where = "";

        if (max != null) {
            where = " WHERE " + columnId + " > " + max + " ";
        }

        String query = "SELECT " + columnId + ", " + Utils.implode(", ", cols) + " FROM " + table + (fromWhere == null || fromWhere.isEmpty() ? where : where + fromWhere);
        String sql = "INSERT INTO " + table + "(" + columnId + ", " + Utils.implode(", ", cols) + ") VALUES (?, " + Utils.implode(", ", paramsInsert) + ")";

        System.out.println(query);
        System.out.println(sql);

        try (
                Connection fromConn = openConnection();
                Connection toConn = DriverManager.getConnection(toURL, DB_USER, DB_PASS);
                Statement fromST = fromConn.createStatement();
                ResultSet fromRS = fromST.executeQuery(query);
                PreparedStatement toInsert = toConn.prepareStatement(sql)) {

            while (fromRS.next()) {

                toInsert.setObject(1, Database.toValue(fromRS, columnId));

                for (int i = 0; i < cols.size(); i++) {
                    toInsert.setObject(i + 2, Database.toValue(fromRS, cols.get(i)));
                }

                toInsert.executeUpdate();
            }
        }

        if (max != null) {

            where = " WHERE " + columnId + " <= " + max + " ";

            query = "SELECT " + columnId + ", " + Utils.implode(", ", cols) + " FROM " + table + (fromWhere == null || fromWhere.isEmpty() ? where : where + fromWhere);
            sql = "UPDATE " + table + " SET " + Utils.implode(", ", paramsUpdate) + " WHERE " + columnId + " = ?";

            System.out.println(query);
            System.out.println(sql);

            try (
                    Connection fromConn = openConnection();
                    Connection toConn = DriverManager.getConnection(toURL, DB_USER, DB_PASS);
                    Statement fromST = fromConn.createStatement();
                    ResultSet fromRS = fromST.executeQuery(query);
                    PreparedStatement toUpdate = toConn.prepareStatement(sql)) {

                while (fromRS.next()) {

                    int index = 1;

                    for (String col : cols) {
                        toUpdate.setObject(index, Database.toValue(fromRS, col));
                        index++;
                    }

                    toUpdate.setObject(index, Database.toValue(fromRS, columnId));

                    toUpdate.executeUpdate();
                }
            }
        }
    }

    /**
     * Atualiza o banco de dados
     *
     * @param sql
     * @return Long Valor do ID caso INSERT
     * @throws SQLException
     */
    public static Long executeUpdate(String sql) throws Exception {

        try (Connection conn = openConnection(); Statement st = conn.createStatement()) {

            st.execute(sql, Statement.RETURN_GENERATED_KEYS);

            try (ResultSet rs = st.getGeneratedKeys()) {
                if (rs.next()) {
                    return rs.getLong(1);
                }
            }
        }

        return null;
    }

    /**
     * Consulta no banco de dados
     *
     * @param query
     * @return ResultSet
     * @throws SQLException
     */
    public static List<Map<String, Object>> executeQuery(String query) throws SQLException {

        List<Map<String, Object>> list = new ArrayList();

        try (Connection conn = openConnection(); Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(query)) {

            while (rs.next()) {

                Map<String, Object> map = new TreeMap();

                for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
                    map.put(rs.getMetaData().getColumnName(i + 1).toLowerCase(), Database.toValue(rs, rs.getMetaData().getColumnLabel(i + 1)));
                }

                list.add(map);
            }
        }

        return list;
    }

    /**
     * Recupera a consulta numa TableModel
     *
     * @param query Consulta
     * @return TableModel
     */
    public static TableModel getTableModel(String query) {
        return Database.getTableModel(query, false);
    }

    /**
     * Recupera a consulta numa TableModel
     *
     * @param query Consulta
     * @param fast
     * @return TableModel
     */
    public static TableModel getTableModel(final String query, boolean fast) {

        final TableModel model = new TableModel();

        if (fast) {
            new Thread(new Runnable() {
                @Override
                public void run() {
                    Database.buildTableModel(model, query);
                }
            }).start();
        } else {
            Database.buildTableModel(model, query);
        }

        return model;
    }

    /**
     * Recupera a consulta numa ListModel
     *
     * @param query Consulta
     * @param columnToString Uma ou mais colunas para exibir na toString do
     * objeto. Ex.: col1, col2, ...
     * @return TableModel
     */
    public static AutoCompleteModel<Map<String, Object>> getAutoCompleteModel(String query, String columnToString) {
        return Database.getAutoCompleteModel(query, columnToString, false);
    }

    /**
     * Recupera a consulta numa ListModel
     *
     * @param query Consulta
     * @param columnToString Uma ou mais colunas para exibir na toString do
     * objeto. Ex.: col1, col2, ...
     * @param fast
     * @return TableModel
     */
    public static AutoCompleteModel<Map<String, Object>> getAutoCompleteModel(final String query, final String columnToString, boolean fast) {

        final AutoCompleteModel<Map<String, Object>> model = new AutoCompleteModel();

        if (fast) {
            new Thread(new Runnable() {
                @Override
                public void run() {
                    Database.buildAutoCompleteModel(model, columnToString, query);
                }
            }).start();
        } else {
            Database.buildAutoCompleteModel(model, columnToString, query);
        }

        return model;
    }

    /**
     * Cria script de include
     *
     * @param table Nome da tabela
     * @param params Nome e valor de cada coluna
     * @throws java.sql.SQLException
     *
     * @see      <code>
     * Derby: id BIGINT GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT BY 1) PRIMARY KEY<br>
     * Mysql: id BIGINT PRIMARY KEY AUTO_INCREMENT<br>
     * Postgres: id SERIAL PRIMARY KEY<br>
     * Firbird: Através de TRIGGER ou SEQUENCE
     * </code>
     *
     * @return String
     */
    public static String insertScript(String table, Map<String, Object> params) throws SQLException {

        String columnId = null;

        try (Connection conn = openConnection(); ResultSet rs = conn.getMetaData().getPrimaryKeys(null, null, table)) {
            if (rs.next()) {
                columnId = rs.getString(4).toLowerCase();
            }
        }

        return insertScript(table, columnId, params);
    }

    /**
     * Cria script de include
     *
     * @param table Nome da tabela
     * @param columnId Coluna chave primária
     * @param params Nome e valor de cada coluna
     *
     * @throws java.sql.SQLException
     *
     * @see      <code>
     * Derby: id BIGINT GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT BY 1) PRIMARY KEY<br>
     * Mysql: id BIGINT PRIMARY KEY AUTO_INCREMENT<br>
     * Postgres: id SERIAL PRIMARY KEY<br>
     * Firbird: Através de TRIGGER ou SEQUENCE
     * </code>
     *
     * @return String
     */
    public static String insertScript(String table, String columnId, Map<String, Object> params) throws SQLException {

        String sequence = null;

        if (getURL().contains("firebird")) {

            String query = "SELECT RDB$GENERATOR_NAME FROM RDB$GENERATORS";

            try (Connection conn = openConnection(); Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(query)) {
                while (rs.next()) {
                    if (rs.getString(1).toLowerCase().contains(table.toLowerCase())) {
                        sequence = rs.getString(1).toLowerCase().trim();
                        break;
                    }
                }
            } catch (SQLException ex) {
                System.err.println(ex);
            }
        }

        return insertScript(table, columnId, params, sequence);
    }

    /**
     * Cria script de include
     *
     * @param table Nome da tabela
     * @param columnId Coluna chave primária
     * @param params Nome e valor de cada coluna
     * @param sequenceName Nome da sequenciador para auto incremento
     *
     * @throws java.sql.SQLException
     *
     * @see      <code>
     * Derby: id BIGINT GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT BY 1) PRIMARY KEY<br>
     * Mysql: id BIGINT PRIMARY KEY AUTO_INCREMENT<br>
     * Postgres: id SERIAL PRIMARY KEY<br>
     * Firebird: Através de TRIGGER ou SEQUENCE
     * </code>
     *
     * @return String
     */
    public static String insertScript(String table, String columnId, Map<String, Object> params, String sequenceName) throws SQLException {

        if (columnId == null) {
            throw new SQLException("Tabela não possui chave primária.");
        }

        List<String> cols = new ArrayList();
        List<Object> vals = new ArrayList();

        Object id = null;

        for (Map.Entry<String, Object> entry : params.entrySet()) {

            if (!entry.getKey().equalsIgnoreCase(columnId)) {

                cols.add(entry.getKey());

                Object value = entry.getValue();

                if (value != null) {
                    switch (value.toString()) {
                        case "true":
                            value = 1;
                            break;
                        case "false":
                            value = 0;
                            break;
                    }
                }

                vals.add(value == null || value.toString().isEmpty() || value.toString().equalsIgnoreCase("null") ? "null" : "'" + value + "'");

            } else {
                id = entry.getValue();
            }
        }

        String col = "";
        for (int i = 0; i < cols.size(); i++) {
            col += cols.get(i) + (i + 1 == cols.size() ? "" : ", ");
        }

        String val = "";
        for (int i = 0; i < vals.size(); i++) {
            val += vals.get(i) + (i + 1 == cols.size() ? "" : ", ");
        }

        if (sequenceName != null && Database.getURL().contains("firebird")) {
            col += ", " + columnId;
            val += id == null ? ", GEN_ID(" + sequenceName.toLowerCase().trim() + ", 1)" : ", " + id;
        } else if (sequenceName == null && id != null) {
            col += ", " + columnId;
            val += ", " + id;
        }

        String sql = "INSERT INTO " + table + "(" + col + ") VALUES (" + val + ")";

        return sql;
    }

    /**
     * Cria script de update
     *
     * @param table Nome da tabela
     * @param params Nome e valor de cada coluna
     *
     * @return String
     * @throws SQLException
     */
    public static String updateScript(String table, Map<String, Object> params) throws SQLException {

        String columnId = null;

        try (Connection conn = openConnection(); ResultSet rs = conn.getMetaData().getPrimaryKeys(null, null, table)) {
            if (rs.next()) {
                columnId = rs.getString(4).toLowerCase();
            }
        }

        return updateScript(table, columnId, params);
    }

    /**
     * Cria script de update
     *
     * @param table Nome da tabela
     * @param columnId Coluna chave primária
     * @param params Nome e valor de cada coluna
     *
     * @return String
     * @throws SQLException
     */
    public static String updateScript(String table, String columnId, Map<String, Object> params) throws SQLException {

        if (columnId == null) {
            throw new SQLException("Tabela não possui chave primária.");
        }

        Object idValue = null;
        List<String> cols = new ArrayList();

        for (Map.Entry<String, Object> entry : params.entrySet()) {

            Object value = entry.getValue();

            if (entry.getKey().equalsIgnoreCase(columnId)) {
                idValue = value;
            } else {

                if (value != null) {
                    switch (value.toString()) {
                        case "true":
                            value = 1;
                            break;
                        case "false":
                            value = 0;
                            break;
                    }
                }

                cols.add(entry.getKey() + " = " + (value == null || value.toString().isEmpty() || value.toString().equalsIgnoreCase("null") ? "null" : "'" + value + "'"));
            }
        }

        if (idValue == null) {
            throw new SQLException("Falta o parâmetro identificador do regsitro.");
        }

        String col = "";
        for (int i = 0; i < cols.size(); i++) {
            col += cols.get(i) + (i + 1 == cols.size() ? "" : ", ");
        }

        String sql = "UPDATE " + table + " SET " + col + " WHERE " + columnId + " = '" + idValue + "'";

        return sql;
    }

    /**
     * Converte a resultado da consulta no tipo específico da coluna
     *
     * @param <T>
     * @param rs
     * @param column
     * @return
     * @throws SQLException
     */
    public static <T> T toValue(ResultSet rs, String column) throws SQLException {

        Object value = null;

        if (column != null && rs.getObject(column) != null) {

            int type = rs.getMetaData().getColumnType(rs.findColumn(column));

            switch (type) {

                case Types.BIGINT:
                    value = rs.getLong(column);
                    break;
                case Types.BINARY:
                    value = rs.getObject(column);
                    break;
                case Types.BIT:
                    value = rs.getBoolean(column);
                    break;
                case Types.BLOB:
                    value = rs.getBlob(column);
                    break;
                case Types.BOOLEAN:
                    value = rs.getBoolean(column);
                    break;
                case Types.CHAR:
                    value = rs.getString(column);
                    break;
                case Types.DATE:
                    value = rs.getDate(column);
                    break;
                case Types.DECIMAL:
                    value = rs.getBigDecimal(column);
                    break;
                case Types.DOUBLE:
                    value = rs.getDouble(column);
                    break;
                case Types.FLOAT:
                    value = rs.getFloat(column);
                    break;
                case Types.INTEGER:
                    value = rs.getInt(column);
                    break;
                case Types.LONGNVARCHAR:
                    value = rs.getString(column);
                    break;
                case Types.LONGVARCHAR:
                    value = rs.getString(column);
                    break;
                case Types.NCHAR:
                    value = rs.getString(column);
                    break;
                case Types.NVARCHAR:
                    value = rs.getString(column);
                    break;
                case Types.SMALLINT:
                    value = rs.getBoolean(column);
                    break;
                case Types.TIME:
                    value = rs.getTime(column);
                    break;
                case Types.TIMESTAMP:
                    value = rs.getTimestamp(column);
                    break;
                case Types.TINYINT:
                    value = rs.getBoolean(column);
                    break;
                case Types.VARCHAR:
                    value = rs.getString(column);
                    break;

                default:
                    value = rs.getObject(column);
                    break;
            }
        }

        return value == null ? null : (T) value;
    }

    /**
     * Converte tipo de dado do banco de dados para tipo Java
     *
     * @param type
     * @return
     */
    public static Class type2Class(int type) {

        switch (type) {

            case Types.BIGINT:
                return Long.class;

            case Types.BINARY:
                return Object.class;

            case Types.BIT:
                return Boolean.class;

            case Types.BLOB:
                return Blob.class;

            case Types.BOOLEAN:
                return Boolean.class;

            case Types.CHAR:
                return String.class;

            case Types.DATE:
                return Date.class;

            case Types.DECIMAL:
                return BigDecimal.class;

            case Types.DOUBLE:
                return Double.class;

            case Types.FLOAT:
                return Float.class;

            case Types.INTEGER:
                return Integer.class;

            case Types.LONGNVARCHAR:
                return String.class;

            case Types.LONGVARCHAR:
                return String.class;

            case Types.NCHAR:
                return String.class;

            case Types.NVARCHAR:
                return String.class;

            case Types.SMALLINT:
                return Boolean.class;

            case Types.TIME:
                return Time.class;

            case Types.TIMESTAMP:
                return Timestamp.class;

            case Types.TINYINT:
                return Boolean.class;

            case Types.VARCHAR:
                return String.class;

            default:
                return Object.class;
        }
    }

    /**
     * Converte tipo de dado do banco de dados para tipo literal
     *
     * @param literal
     * @param type
     * @param length
     * @return
     * @throws java.sql.SQLException
     */
    public static String type2String(String literal, int type, int length) throws SQLException {

        if (literal.equalsIgnoreCase("text") || literal.equalsIgnoreCase("boolean") || literal.equalsIgnoreCase("tinyint")) {
            return literal.toUpperCase();
        }

        switch (type) {

            case Types.BIGINT:
                return "BIGINT";

            case Types.BINARY:
                return "BINARY";

            case Types.BIT:
                return "BIT";

            case Types.BLOB:
                return "BLOB" + (getURL().toLowerCase().contains("firebird") ? " SUB_TYPE 1" : "");

            case Types.BOOLEAN:
                return "BOOLEAN";

            case Types.CHAR:
                return "CHAR(" + length + ")";

            case Types.DATE:
                return "DATE";

            case Types.DECIMAL:
                return "DECIMAL(" + length + ", 2)";

            case Types.DOUBLE:
                return "DOUBLE";

            case Types.FLOAT:
                return "FLOAT";

            case Types.INTEGER:
                return "INT";

            case Types.LONGNVARCHAR:
                return "VARCHAR(" + length + ")";

            case Types.LONGVARCHAR:
                return "TEXT";

            case Types.NCHAR:
                return "VARCHAR(" + length + ")";

            case Types.NVARCHAR:
                return "VARCHAR(" + length + ")";

            case Types.SMALLINT:
                return "SMALLINT";

            case Types.TIME:
                return "TIME";

            case Types.TIMESTAMP:
                return "TIMESTAMP";

            case Types.TINYINT:
                return "TINYINT";

            case Types.VARCHAR:
                return "VARCHAR(" + length + ")";

            default:
                return literal;
        }
    }

    /**
     * Converte um tipo Java para tipo de banco de dados
     *
     * @param cls
     * @return
     */
    public static int class2Type(Class cls) {

        if (cls.isAssignableFrom(Long.class) || cls.isAssignableFrom(long.class)) {
            return Types.BIGINT;
        } else if (cls.isAssignableFrom(Blob.class)) {
            return Types.BLOB;
        } else if (cls.isAssignableFrom(Boolean.class) || cls.isAssignableFrom(boolean.class)) {
            return Types.BOOLEAN;
        } else if (cls.isAssignableFrom(java.util.Date.class) || cls.isAssignableFrom(Date.class)) {
            return Types.DATE;
        } else if (cls.isAssignableFrom(BigDecimal.class)) {
            return Types.DECIMAL;
        } else if (cls.isAssignableFrom(Double.class) || cls.isAssignableFrom(double.class)) {
            return Types.DOUBLE;
        } else if (cls.isAssignableFrom(Float.class) || cls.isAssignableFrom(float.class)) {
            return Types.FLOAT;
        } else if (cls.isAssignableFrom(Integer.class) || cls.isAssignableFrom(int.class)) {
            return Types.INTEGER;
        } else if (cls.isAssignableFrom(Time.class)) {
            return Types.TIME;
        } else if (cls.isAssignableFrom(Timestamp.class)) {
            return Types.TIMESTAMP;
        } else {
            return Types.VARCHAR;
        }
    }

    /**
     * Formata nome para padrão banco de dados
     *
     * @param name
     * @return
     */
    public static String java2Database(String name) {

        String str = "";

        for (char c : name.toCharArray()) {

            if (Character.isUpperCase(c) && !str.isEmpty()) {
                str += '_' + String.valueOf(c).toLowerCase();
            } else {
                str += String.valueOf(c).toLowerCase();
            }
        }

        return str;
    }

    /**
     * Formata nome para padrão Java
     *
     * @param name
     * @return
     */
    public static String database2Java(String name) {
        return database2Java(name, false);

    }

    /**
     * Formata nome para padrão Java
     *
     * @param name
     * @param upper
     * @return
     */
    public static String database2Java(String name, boolean upper) {

        String str = "";

        for (char c : name.toCharArray()) {

            if (c == '_') {
                upper = true;
            } else if (upper) {
                str += String.valueOf(c).toUpperCase();
                upper = false;
            } else {
                str += String.valueOf(c);
            }
        }

        return str;
    }

    public static boolean restore(String path) throws Exception {
        return restore(new File(path));
    }

    public static boolean restore(File file) throws Exception {

        String sql = Utils.readFile(file);

        try (Connection conn = openConnection(); Statement st = conn.createStatement()) {

            List<String> alters = new ArrayList();

            for (String line : sql.replace("\n", "").split(";")) {

                if (line.startsWith("INSERT")) {
                    st.addBatch(line.replace("\r", "\n"));
                } else if (line.startsWith("ALTER")) {
                    alters.add(line);
                } else {
                    st.execute(line.replace("\r", "\n"));
                }
            }

            st.executeBatch();

            for (String alter : alters) {
                st.execute(alter);
            }
        }

        return true;
    }

    public static File backupANSI(String path) throws Exception {
        return backupANSI(new File(path.replace(".sql", "") + ".sql"));
    }

    public static File backupANSI(File file) throws Exception {

        if (!file.getAbsolutePath().endsWith("sql")) {
            file = new File(file.getAbsolutePath().replace(".sql", "") + ".sql");
        }

        StringBuilder sql = new StringBuilder();

        if (getURL().contains("firebird")) {
            sql.append("CREATE DOMAIN TINYINT AS SMALLINT;\n");
            sql.append("CREATE DOMAIN \"BOOLEAN\" AS SMALLINT CHECK(VALUE BETWEEN 0 AND 1);\n");
            sql.append("CREATE DOMAIN TEXT AS BLOB SUB_TYPE 1;\n\n");
        }

        try (Connection conn = openConnection(); Statement st = conn.createStatement()) {

            try (ResultSet rsTab = conn.getMetaData().getTables(null, null, "%", new String[]{"TABLE"})) {

                while (rsTab.next()) {

                    String table = rsTab.getString(3).toLowerCase().trim();

                    sql.append("CREATE TABLE ");
                    sql.append(table);
                    sql.append(" (\n");

                    String primaryKey = "";

                    try (ResultSet rs = conn.getMetaData().getPrimaryKeys(null, null, table)) {
                        if (rs.next()) {
                            primaryKey = rs.getString(4).toLowerCase().trim();
                        }
                    }

                    List<String> columns = new ArrayList();

                    try (ResultSet rs = conn.getMetaData().getColumns(null, null, table, "%")) {

                        while (rs.next()) {

                            String column = rs.getString(4).toLowerCase().trim();
                            String type = type2String(rs.getString(6).trim(), rs.getInt(5), rs.getInt(7)) + (!rs.getString(18).equalsIgnoreCase("yes") ? " NOT NULL" : "") + (column.equalsIgnoreCase(primaryKey) ? " PRIMARY KEY" : "") + (rs.getString(19) != null && rs.getString(19).equalsIgnoreCase("yes") ? " AUTO_INCREMENT" : "");

                            columns.add("  " + column + " " + type);
                        }
                    }

                    sql.append(Utils.implode(",\n", columns));
                    sql.append("\n);\n\n");

                    int maxId = 0;

                    try (ResultSet rs = st.executeQuery("SELECT * FROM " + table)) {

                        String prefix = "INSERT INTO " + table + "(";

                        for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
                            prefix += rs.getMetaData().getColumnLabel(i + 1).toLowerCase().trim() + (i + 1 == rs.getMetaData().getColumnCount() ? "" : ", ");
                        }

                        prefix += ") VALUES (";

                        while (rs.next()) {

                            maxId = Math.max(maxId, rs.getInt(primaryKey));

                            sql.append(prefix);

                            for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {

                                Object value = rs.getObject(rs.getMetaData().getColumnLabel(i + 1));

                                if (value != null) {
                                    value = "'" + value.toString().replace(";", "").replace("\n", "\r").replace("'", "`") + "'";
                                } else {
                                    value = "NULL";
                                }

                                sql.append(value);
                                sql.append(i + 1 == rs.getMetaData().getColumnCount() ? "" : ", ");
                            }

                            sql.append(");\n");
                        }

                        sql.append("\n\n");
                    }

                    if (getURL().contains("firebird")) {

                        try (ResultSet rs = st.executeQuery("SELECT RDB$GENERATOR_NAME FROM RDB$GENERATORS")) {

                            while (rs.next()) {

                                if (rs.getString(1).toLowerCase().contains(table.toLowerCase())) {

                                    sql.append("CREATE SEQUENCE ");
                                    sql.append(rs.getString(1).toLowerCase().trim());
                                    sql.append(";\n");
                                    sql.append("ALTER SEQUENCE ");
                                    sql.append(rs.getString(1).toLowerCase().trim());
                                    sql.append(" RESTART WITH ");
                                    sql.append(maxId);
                                    sql.append(";\n\n");
                                    break;
                                }
                            }
                        }
                    }

                    try (ResultSet rs = conn.getMetaData().getImportedKeys(null, null, table)) {

                        while (rs.next()) {

                            sql.append("ALTER TABLE ");
                            sql.append(table);
                            sql.append(" ADD FOREIGN KEY (");
                            sql.append(rs.getString(8).toLowerCase().trim());
                            sql.append(") REFERENCES ");
                            sql.append(rs.getString(3).toLowerCase().trim());
                            sql.append("(");
                            sql.append(rs.getString(4).toLowerCase().trim());
                            sql.append(");\n");
                        }
                    }

                    sql.append("\n");

                    try (ResultSet rs = conn.getMetaData().getIndexInfo(null, null, table, true, true)) {

                        List<String> uniques = new ArrayList();

                        while (rs.next()) {

                            String column = rs.getString(9).toLowerCase().trim();

                            if (!column.equals(primaryKey)) {
                                uniques.add(column);
                            }
                        }

                        if (!uniques.isEmpty()) {

                            sql.append("ALTER TABLE ");
                            sql.append(table);
                            sql.append(" ADD UNIQUE (");
                            sql.append(Utils.implode(", ", uniques));
                            sql.append(");\n\n");
                        }
                    }

                }
            }

        } finally {
            Utils.writeFile(file, sql.toString(), false);
        }

        return file;
    }

    private static Map<String, Object> newMap(ResultSet rs, String[] cols) throws SQLException {

        final StringBuilder toString = new StringBuilder();

        for (String col : cols) {

            Object obj = Database.toValue(rs, col.trim().toLowerCase());

            if (obj != null) {

                Class cls = obj.getClass();

                if (cls.isAssignableFrom(Long.class) || cls.isAssignableFrom(long.class) || cls.isAssignableFrom(Integer.class) || cls.isAssignableFrom(int.class)) {
                    obj = NumberFormatter.format(0).format(obj);
                } else if (cls.isAssignableFrom(BigDecimal.class) || cls.isAssignableFrom(Double.class) || cls.isAssignableFrom(double.class) || cls.isAssignableFrom(Float.class) || cls.isAssignableFrom(float.class)) {
                    obj = NumberFormatter.format(2).format(obj);
                } else if (cls.isAssignableFrom(Boolean.class) || cls.isAssignableFrom(boolean.class)) {
                    obj = ((Boolean) obj) ? "Sim" : "Não";
                } else if (cls.isAssignableFrom(java.util.Date.class) || cls.isAssignableFrom(Date.class)) {
                    obj = DateFormatter.format().format(obj);
                } else if (cls.isAssignableFrom(Time.class)) {
                    obj = TimeFormatter.format().format(obj);
                } else if (cls.isAssignableFrom(Timestamp.class)) {
                    obj = TimestampFormatter.format().format(obj);
                }

                if (toString.length() == 0) {
                    toString.append(obj.toString());
                } else {
                    toString.append(" - ").append(obj.toString());
                }
            }
        }

        return new TreeMap() {
            @Override
            public String toString() {
                return toString.toString();
            }
        };
    }

    private static void buildAutoCompleteModel(AutoCompleteModel<Map<String, Object>> model, String columnToString, String query) {

        try (Connection conn = openConnection(); Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(query)) {

            String[] cols = columnToString.split(",");

            while (rs.next()) {

                Map<String, Object> map = newMap(rs, cols);

                for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
                    map.put(rs.getMetaData().getColumnName(i + 1).toLowerCase(), Database.toValue(rs, rs.getMetaData().getColumnLabel(i + 1)));
                }

                model.add(map);
            }

            model.endLoad();

        } catch (Exception ex) {
            OptionPane.error(ex);
        }
    }

    private static void buildTableModel(TableModel model, String query) {

        try (Connection conn = openConnection(); Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(query)) {

            for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
                model.addColumn(rs.getMetaData().getColumnLabel(i + 1), type2Class(rs.getMetaData().getColumnType(i + 1)));
            }

            while (rs.next()) {

                Object[] row = new Object[rs.getMetaData().getColumnCount()];

                for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
                    row[i] = Database.toValue(rs, rs.getMetaData().getColumnLabel(i + 1));
                }

                model.addRow(row);
            }

        } catch (Exception ex) {
            OptionPane.error(ex);
        }
    }
}