package importadorbases;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
 *
 * @author emerson.engroff
 */
public class ImportadorBases {

    public static void main(String[] args) throws SQLException {

        String conexaoOrigem = "";
        String conexaoDestino = "";
        String tabelasExcluir = "";
        String tabelasIncluir = "";

        for (int i = 0; i < args.length; i++) {
            if (args[i].equals("-o")) {
                conexaoOrigem = args[i + 1];
            } else if (args[i].equals("-d")) {
                conexaoDestino = args[i + 1];
            } else if (args[i].equals("-T")) {
                tabelasExcluir = args[i + 1];
            } else if (args[i].equals("-t")) {
                tabelasIncluir = args[i + 1];
            }
        }

        if (conexaoOrigem.equals("") || conexaoDestino.equals("")) {
            System.out.println("paramentros: -o conexao de origem -d conexao de destino -t(opcional) regex tabelas a incluir -T(opcional) regex tabelas a excluir");
            System.out.println("formanto conexao: jdbc:DRIVER:HOST:PORTA|USUARIO|SENHA|ESQUEMA(opcional)");
            System.out.println(
                    "Ex: java -jar importadorBases.jar"
                    + " -o \"jdbc:sybase:Tds:10.1.1.133:50768|externo|123456|\""
                    + " -d \"jdbc:postgresql://marte.solucoesglobais.com.br:5432/conversao_betha|postgres|postgres|conversao\""
                    + " -t \"^tomasi.*\" -T \"^tomasi\\.pb.*\"; "
            );
        }

        Connection conn1 = openDatabase(conexaoOrigem);
        Connection conn2 = openDatabase(conexaoDestino);

        ResultSet resultSet = conn1.getMetaData().getCatalogs();
        String[] types = {"TABLE"};
        resultSet = conn1.getMetaData().getTables(conn1.getCatalog(), null, "%", types);

        String table;

        while (resultSet.next()) {
            table = "" + resultSet.getString(2) + "." + resultSet.getString(3) + "";
            if (!tabelasIncluir.equals("") && !table.toLowerCase().matches(tabelasIncluir)) {
                continue;
            }
            if (!tabelasExcluir.equals("") && table.toLowerCase().matches(tabelasExcluir)) {
                continue;
            }
            copyTable(table, conn1, conn2);
        }

        resultSet.close();

        closeDatabase(conn1);
        closeDatabase(conn2);
    }

    public static void copyTable(String table, Connection conn1, Connection conn2) throws SQLException {
        System.out.print("Copiando '" + table + "' ");
        try {
            PreparedStatement s1 = conn1.prepareStatement("SELECT * FROM " + table + ";");
            ResultSet rs = s1.executeQuery();

            ResultSetMetaData meta = rs.getMetaData();
            Integer columnCount = meta.getColumnCount();
            Integer k = 0;
            Integer j = 0;
            String table2 = table.replace(".", "_");

            List<String> columns = new ArrayList<>();
            for (int i = 1; i <= columnCount; i++) {
                columns.add(meta.getColumnName(i));
            }

            conn2.createStatement().execute("CREATE TABLE \"" + table2 + "\" (\"" + columns.stream().collect(Collectors.joining("\" TEXT, \"")) + "\" TEXT);");

            try (PreparedStatement s2 = conn2.prepareStatement(
                    "INSERT INTO " + table2 + " (\""
                    + columns.stream().collect(Collectors.joining("\", \""))
                    + "\") VALUES ("
                    + columns.stream().map(c -> "?").collect(Collectors.joining(", "))
                    + ");"
            )) {

                while (rs.next()) {
                    k++;
                    j++;
                    for (int i = 1; i <= columnCount; i++) {
                        s2.setObject(i, rs.getObject(i));
                    }
                    s2.addBatch();
                    if (k == 1000) {
                        s2.executeBatch();
                        System.gc();
                        k = 0;
                    }
                }
                s2.executeBatch();
                rs.close();
                System.gc();
            }
            System.out.println("OK "+j+" registros");
        } catch (SQLException e) {
            System.out.println("Erro: nao foi possivel copiar '" + table + "' - [" + e.getSQLState() + "] " + e.toString());
        }

    }

    public static Connection openDatabase(String connString) {
        connString += "|-";
        String[] connDataArr = connString.split("\\|");
        String strDriver = "";
        String strURL = connDataArr[0];
        String strUserId = connDataArr[1];
        String strUserPswd = connDataArr[2];
        String strSchema = connDataArr[3];

        Connection conn;

        if (strURL.matches(".*postgresql.*")) {
            strDriver = "org.postgresql.Driver";
        } else if (strURL.matches(".*sybase.*")) {
            strDriver = "com.sybase.jdbc3.jdbc.SybDriver";
        }

        try {
            Class drvClass = Class.forName(strDriver);
            DriverManager.registerDriver((Driver) drvClass.newInstance());
            DriverManager.setLoginTimeout(900);//15 minutos de timeout
            conn = DriverManager.getConnection(strURL, strUserId, strUserPswd);
            if (!strSchema.equals("")) {
                conn.createStatement().execute(
                        "CREATE SCHEMA \"" + strSchema + "\";"
                        + "SET search_path TO \"" + strSchema + "\";"
                );
            }
        } catch (SQLException e) {
            printSQLException(e);
            return null;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return null;
        } catch (InstantiationException e) {
            e.printStackTrace();
            return null;
        } catch (IllegalAccessException e) {
            e.printStackTrace();
            return null;
        }

        return conn;
    }

    public static boolean closeDatabase(Connection conn) {
        if (conn == null) {
            return true;
        }
        try {
            conn.close();
        } catch (SQLException e) {
            return false;
        }
        return true;
    }

    public static void printSQLException(SQLException e) {
        e.printStackTrace();
        while (e != null) {
            System.out.println(e);
            e = e.getNextException();
        }
    }
}