/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dbcopy;

import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.io.LineNumberReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.derby.tools.dblook;
import org.kohsuke.args4j.CmdLineException;
import org.kohsuke.args4j.CmdLineParser;
import org.kohsuke.args4j.Option;

/**
 *
 * @author brett
 */
public class Main {

    @Option(name = "-fromdb", usage = "The path to the database to copy from", required = true)
    protected String fromDbPath;

    /**
     * Get the value of fromDbPath
     *
     * @return the value of fromDbPath
     */
    public String getFromDbPath() {
        return fromDbPath;
    }

    /**
     * Set the value of fromDbPath
     *
     * @param fromDbPath new value of fromDbPath
     */
    public void setFromDbPath(String fromDbPath) {
        this.fromDbPath = fromDbPath;
    }

    @Option(name = "-todb", usage = "The path to the database to copy to", required = true)
    protected String toDbPath;

    /**
     * Get the value of toDbPath
     *
     * @return the value of toDbPath
     */
    public String getToDbPath() {
        return toDbPath;
    }

    /**
     * Set the value of toDbPath
     *
     * @param toDbPath new value of toDbPath
     */
    public void setToDbPath(String toDbPath) {
        this.toDbPath = toDbPath;
    }

    @Option(name = "-username", usage = "The database username to use when connecting", required = true)
    protected String username;

    /**
     * Get the value of username
     *
     * @return the value of username
     */
    public String getUsername() {
        return username;
    }

    /**
     * Set the value of username
     *
     * @param username new value of username
     */
    public void setUsername(String username) {
        this.username = username;
    }

    @Option(name = "-password", usage = "The database password to use when connecting", required = true)
    protected String password;

    /**
     * Get the value of password
     *
     * @return the value of password
     */
    public String getPassword() {
        return password;
    }

    /**
     * Set the value of password
     *
     * @param password new value of password
     */
    public void setPassword(String password) {
        this.password = password;
    }

    protected Connection fromdbConnection;

    /**
     * Get the value of fromdbConnection
     *
     * @return the value of fromdbConnection
     */
    public Connection getFromdbConnection() {
        return fromdbConnection;
    }

    /**
     * Set the value of fromdbConnection
     *
     * @param fromdbConnection new value of fromdbConnection
     */
    public void setFromdbConnection(Connection fromdbConnection) {
        this.fromdbConnection = fromdbConnection;
    }

    protected Connection todbConnection;

    /**
     * Get the value of todbConnection
     *
     * @return the value of todbConnection
     */
    public Connection getTodbConnection() {
        return todbConnection;
    }

    /**
     * Set the value of todbConnection
     *
     * @param todbConnection new value of todbConnection
     */
    public void setTodbConnection(Connection todbConnection) {
        this.todbConnection = todbConnection;
    }

    @Option(name = "-tmpdir", usage = "The directory to create temporary files in", required = false)
    protected String tmpdirPath;

    /**
     * Get the value of tmpdirPath
     *
     * @return the value of tmpdir
     */
    public String getTmpdirPath() {
        return tmpdirPath;
    }

    /**
     * Set the value of tmpdir
     *
     * @param tmpdirPath new value of tmpdirPath
     */
    public void setTmpdirPath(String tmpdirPath) {
        this.tmpdirPath = tmpdirPath;
    }

    protected File tmpDir;

    /**
     * Get the value of tmpDir
     *
     * @return the value of tmpDir
     */
    public File getTmpDir() {
        return tmpDir;
    }

    /**
     * Set the value of tmpDir
     *
     * @param tmpDir new value of tmpDir
     */
    public void setTmpDir(File tmpDir) {
        this.tmpDir = tmpDir;
    }

    protected File ddlFile;

    /**
     * Get the value of ddlFile
     *
     * @return the value of ddlFile
     */
    public File getDdlFile() {
        return ddlFile;
    }

    /**
     * Set the value of ddlFile
     *
     * @param ddlFile new value of ddlFile
     */
    public void setDdlFile(File ddlFile) {
        this.ddlFile = ddlFile;
    }

    protected String databaseClasspath;

    /**
     * Get the value of databaseClasspath
     *
     * @return the value of databaseClasspath
     */
    public String getDatabaseClasspath() {
        return databaseClasspath;
    }

    /**
     * Set the value of databaseClasspath
     *
     * @param databaseClasspath new value of databaseClasspath
     */
    public void setDatabaseClasspath(String databaseClasspath) {
        this.databaseClasspath = databaseClasspath;
    }

    @Option(name = "-verbose", usage = "Output verbose messages", required = false)
    private boolean verbose = false;

    /**
     * Get the value of verbose
     *
     * @return the value of verbose
     */
    public boolean isVerbose() {
        return verbose;
    }

    /**
     * Set the value of verbose
     *
     * @param verbose new value of verbose
     */
    public void setVerbose(boolean verbose) {
        this.verbose = verbose;
    }

    //CALL SQLJ.INSTALL_JAR('file:C:\work\dbcopy\DBJARS\CSEM\CSEMDERBY.jar.G1312564242203', '"CSEM"."CSEMDERBY"', 0);
//    protected Pattern jarPattern = Pattern.compile("CALL SQL.INSTALL_JAR('file:[^']+', ('[^']+'), 0);");
    protected Pattern jarPattern = Pattern.compile("^[^,]+, '\"([^\"]+)\"\\.\"([^\"]+)\"', .*$");

    /**
     * Get the value of jarPattern
     *
     * @return the value of jarPattern
     */
    public Pattern getJarPattern() {
        return jarPattern;
    }

    /**
     * Set the value of jarPattern
     *
     * @param jarPattern new value of jarPattern
     */
    public void setJarPattern(Pattern jarPattern) {
        this.jarPattern = jarPattern;
    }

    private String xxx = "CREATE TABLE \"CSEM\".\"JPAPREFERENCEVALUE\" (\"ID\" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), \"NAME\" VARCHAR(255), \"VAL\" VARCHAR(255));";

    protected Pattern generatedAlwaysPattern = Pattern.compile("CREATE TABLE (\"[^\"]+\"\\.\"[^\"]+\").*(\"[^\"]+\") .* GENERATED ALWAYS AS IDENTITY (.*),.*");

    /**
     * Get the value of generatedAlwaysPattern
     *
     * @return the value of generatedAlwaysPattern
     */
    public Pattern getGeneratedAlwaysPattern() {
        return generatedAlwaysPattern;
    }

    /**
     * Set the value of generatedAlwaysPattern
     *
     * @param generatedAlwaysPattern new value of generatedAlwaysPattern
     */
    public void setGeneratedAlwaysPattern(Pattern generatedAlwaysPattern) {
        this.generatedAlwaysPattern = generatedAlwaysPattern;
    }

    protected ArrayList<String[]> generatedAlwaysList = new ArrayList<String[]>();

    /**
     * Get the value of generatedAlwaysList
     *
     * @return the value of generatedAlwaysList
     */
    public ArrayList<String[]> getGeneratedAlwaysList() {
        return generatedAlwaysList;
    }

    /**
     * Set the value of generatedAlwaysList
     *
     * @param generatedAlwaysList new value of generatedAlwaysList
     */
    public void setGeneratedAlwaysList(ArrayList<String[]> generatedAlwaysList) {
        this.generatedAlwaysList = generatedAlwaysList;
    }

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        new Main().run(args);
    }

    private void run(String[] args) {
//        Matcher m = generatedAlwaysPattern.matcher(xxx);
//        System.out.println("matches is " + m.matches());
//        if (m.matches()) {
//            System.out.println("group 1 is " + m.group(1));
//            System.out.println("group 1 is " + m.group(2));
//        }
//        System.exit(1);
//        String line = "CALL SQLJ.INSTALL_JAR('file:C:\\work\\dbcopy\\DBJARS\\CSEM\\CSEMDERBY.jar.G1312564242203', '\"CSEM\".\"CSEMDERBY\"', 0)";
//        Matcher m = jarPattern.matcher(line);
//        System.out.println("matches is " + m.matches());
//        System.exit(1);
//
        try {
            parseArguments(args);
            getTempDir();
            loadDbDriver(this.fromDbPath);
            loadDbDriver(this.toDbPath);
            System.out.println("Copying " + fromDbPath + " to " + toDbPath);
            createToDb();
            outputDdlFile();
            createTableDdlFromFile();
            copyDatabaseData();
            createOtherDdlFromFile();
            fixGeneratedAlways();
            ddlFile.delete();
            System.out.println("Copy complete");
            System.exit(0);
        } finally {
            closeConnections();
        }
    }

    private void parseArguments(String[] args) {
        CmdLineParser parser = new CmdLineParser(this);
        try {
            // Parse the arguments
            parser.parseArgument(args);
            if (null == this.tmpDir) {
                this.tmpdirPath = System.getProperty("java.io.tmpdir");
            }
        } catch (CmdLineException cmdLineException) {
            System.err.println(cmdLineException.getMessage());
            System.err.println("usage: dbcopy [options..]");
            parser.printUsage(System.err);
            System.exit(1);
        }
    }

    private void loadDbDriver(String dburl) {
        try {
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
        } catch (Exception exception) {
            System.err.println("Failed to load database driver:" + exception.toString());
            System.exit(1);
        }
    }

    private void reloadDbDriver(String dburl) {
        try {
            DriverManager.getConnection("jdbc:derby:;shutdown=true");
        } catch (SQLException sQLException) {
        }
        loadDbDriver(dburl);
        Class
    }

    private void getTempDir() {
        if (null != tmpdirPath) {
            this.tmpDir = new File(tmpdirPath);
            if (!this.tmpDir.isDirectory()) {
                System.err.println(tmpdirPath + " is not a directory");
                System.exit(1);
            }
        }
    }

    private void createToDb() {
        try {
            todbConnection = DriverManager.getConnection("jdbc:derby:" + toDbPath, this.username, this.password);
            System.err.println("Database " + toDbPath + " already exists");
            System.exit(1);
        } catch (SQLException sQLException) {
            if (!"XJ004".equals(sQLException.getSQLState())) {
                System.err.println("Failed to get a connection to " + toDbPath + ":" + sQLException.toString());
                System.exit(1);
            }
            try {
                todbConnection = DriverManager.getConnection("jdbc:derby:" + toDbPath + ";create=true", this.username, this.password);
            } catch (SQLException ex) {
                System.err.println("Failed to create database " + toDbPath + ":" + ex.toString());
                System.exit(1);
            }
        }
    }

    private void copyDatabaseDDL() {
        outputDdlFile();
        createTableDdlFromFile();
    }

    private void outputDdlFile() {
        try {
            this.ddlFile = File.createTempFile("dblook", ".sql", tmpDir);
            System.out.println("Dumping DDL from " + fromDbPath + " to " + ddlFile.getAbsolutePath());
            ddlFile.deleteOnExit();
            String[] args = new String[]{"-d", "jdbc:derby:" + fromDbPath, "-o", ddlFile.getAbsolutePath()};
            dblook.main(args);
        } catch (IOException iOException) {
        }
    }

    private void createTableDdlFromFile() {
        System.out.println("Creating tables in " + toDbPath);
        LineNumberReader rd = null;
        try {
            rd = new LineNumberReader(new FileReader(this.ddlFile));
            String line;

            while (null != (line = rd.readLine())) {
                line = line.trim();
                if (0 == line.length()) {
                    continue;
                }
                if (line.startsWith("--")) {
                    continue;
                }
                if (!line.startsWith("CREATE TABLE") && !line.startsWith("CREATE SCHEMA")) {
                    continue;
                }
//                if (line.startsWith("ALTER TABLE")) {
//                    continue;
//                }
//                if (line.startsWith("CREATE INDEX")) {
//                    continue;
//                }
//                if (line.startsWith("CREATE UNIQUE INDEX")) {
//                    continue;
//                }
//                if (line.startsWith("CREATE VIEW")) {
//                    continue;
//                }
                if (line.contains("GENERATED ALWAYS")) {
                    Matcher m = generatedAlwaysPattern.matcher(line);
                    if (m.matches()) {
                        String schemaAndTable = m.group(1);
                        String column = m.group(2);
                        generatedAlwaysList.add(new String[]{schemaAndTable, column});
                    }
                    line = line.replace("GENERATED ALWAYS", "GENERATED BY DEFAULT");
                }
                if (line.endsWith(";")) {
                    line = line.substring(0, line.length() - 1);
                }
                executeDdlLine(line);
            }
        } catch (IOException iOException) {
            try {
                rd.close();
            } catch (IOException x) {
                // Ignore this
            }
            System.err.println("Error processing DDL file:" + iOException.toString());
        }

    }

    private void createOtherDdlFromFile() {
        System.out.println("Creating database constraints and UDTs in " + toDbPath);
        LineNumberReader rd = null;
        try {
            rd = new LineNumberReader(new FileReader(this.ddlFile));
            String line;

            while (null != (line = rd.readLine())) {
                line = line.trim();
                if (0 == line.length()) {
                    continue;
                }
                if (line.startsWith("--")) {
                    continue;
                }
                if (line.startsWith("CREATE TABLE") || line.startsWith("CREATE SCHEMA")) {
                    continue;
                }
//                if (line.contains("GENERATED ALWAYS")) {
//                    line = line.replace("GENERATED ALWAYS", "GENERATED BY DEFAULT");
//                }
                if (line.endsWith(";")) {
                    line = line.substring(0, line.length() - 1);
                }
                executeDdlLine(line);
            }
        } catch (IOException iOException) {
            try {
                rd.close();
            } catch (IOException x) {
                // Ignore this
            }
            System.err.println("Error processing DDL file:" + iOException.toString());
        }

    }

    private void executeDdlLine(String line) {
        if (verbose) {
            System.out.println("Executing DDL: " + line);
        }
        Statement stmt = null;
        try {
            fromdbConnection = DriverManager.getConnection("jdbc:derby:" + fromDbPath, this.username, this.password);
            stmt = this.todbConnection.createStatement();
            stmt.execute(line);
            if (line.startsWith("CALL SQLJ.INSTALL_JAR")) {
                updateClasspath(line);
            }
        } catch (SQLException ex) {
            System.err.println("Failed to execute DDL statement " + line + ":" + ex.toString());
            System.exit(1);
        } finally {
            if (null != stmt) {
                try {
                    stmt.close();
                } catch (SQLException sQLException) {
                }
            }
        }
    }

    private void closeConnections() {
        if (null != this.fromdbConnection) {
            try {
                this.fromdbConnection.close();
            } catch (SQLException sQLException) {
            }
        }
        if (null != this.todbConnection) {
            try {
                this.todbConnection.close();
            } catch (SQLException sQLException) {
            }
        }
    }

    private void updateClasspath(String line) {
        Matcher m = jarPattern.matcher(line);
        if (m.matches()) {
            String schema = m.group(1);
            String jar = m.group(2);
            if (null != this.databaseClasspath) {
                this.databaseClasspath += ":";
            } else {
                this.databaseClasspath = "";
            }
            this.databaseClasspath += schema + "." + jar;
        }

        String newline = "CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.database.classpath','" + this.databaseClasspath + "')";
        executeDdlLine(newline);
    }

    private void copyDatabaseData() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            stmt = fromdbConnection.createStatement();
            rs = stmt.executeQuery("SELECT schemaname, tablename FROM sys.sysschemas s, sys.systables t WHERE s.schemaid = t.schemaid and t.tabletype = 'T' order by schemaname, tablename");
            while (rs.next()) {
                String schemaname = rs.getString("schemaname");
                String tablename = rs.getString("tablename");

                copyDatabaseTable(schemaname, tablename);
            }
        } catch (SQLException sQLException) {
            System.err.println("Error in querying tables:" + sQLException.toString());
            System.exit(1);
        } finally {
            if (null != rs) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                }
            }
            if (null != stmt) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                }
            }
        }
    }

    private void copyDatabaseTable(String schemaname, String tablename) {
        File exportFile = new File(tmpDir.getPath() + "/" + schemaname + "_" + tablename + ".txt");
        exportFile.deleteOnExit();

        exportDatabaseTable(schemaname, tablename, exportFile);
        importDatabaseTable(schemaname, tablename, exportFile);

        exportFile.delete();
    }

    private void exportDatabaseTable(String schemaname, String tablename, File exportFile) {
        Statement stmt = null;
        try {
            if (verbose) {
                System.out.println("Exporting table " + schemaname + "." + tablename + " to file " + exportFile.toString());
            }
            stmt = fromdbConnection.createStatement();
            stmt.executeUpdate("CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE ('" + schemaname + "', '" + tablename + "', '" + exportFile.getAbsolutePath() + "', null, null, null)");
        } catch (SQLException sQLException) {
            System.err.println("Error exporting table " + schemaname + "." + tablename + ":" + sQLException);
            System.exit(1);
        } finally {
            if (null != stmt) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                }
            }
        }
    }

    private void importDatabaseTable(String schemaname, String tablename, File exportFile) {
        Statement stmt = null;
        try {
            if (verbose) {
                System.out.println("Importing table " + schemaname + "." + tablename + " from file " + exportFile.toString());
            }
            stmt = todbConnection.createStatement();
            stmt.executeUpdate("CALL SYSCS_UTIL.SYSCS_IMPORT_TABLE ('" + schemaname + "', '" + tablename + "', '" + exportFile.getAbsolutePath() + "', null, null, null, 0)");
        } catch (SQLException sQLException) {
            System.err.println("Error importing table " + schemaname + "." + tablename + ":" + sQLException);
//            System.exit(1);
        } finally {
            if (null != stmt) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                }
            }
        }
    }

    private void fixGeneratedAlways() {
        for (String[] tableAndColumn : generatedAlwaysList) {
            String schemaAndTable = tableAndColumn[0];
            String column = tableAndColumn[1];

            fixGeneratedAlwaysFor(schemaAndTable, column);
        }
    }

    private void fixGeneratedAlwaysFor(String table, String col) {
        Statement stmt = null;
        ResultSet rs = null;

        try {
            stmt = todbConnection.createStatement();
            rs = stmt.executeQuery("SELECT MAX(" + col + ") FROM " + table);
            if (rs.next()) {
                long val = rs.getLong(1) + 1;

                stmt.executeUpdate("ALTER TABLE " + table + " ALTER COLUMN " + col + " RESTART WITH " + val);
            }
        } catch (SQLException sQLException) {
            System.err.println("Error updating generated column " + col + " in table " + table + ":" + sQLException.toString());
        } finally {
            if (null != rs) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                }
            }
        }
    }
}