///usr/bin/env jbang "$0" "$@" ; exit $?
//
// This is a JBang script. You need JBang to run this script. 
//
// To learn more go to https://www.jbang.dev and install JBang.
//
// Run this script:
// ./h2tocsv.sh -d <database.mv.db> -t <table> [-f <output file>] [-u db user] [-p db password]
//
//DEPS info.picocli:picocli:4.5.0 com.h2database:h2:1.4.197

import static java.util.Optional.ofNullable;

import java.io.File;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.concurrent.Callable;

import org.h2.Driver;
import org.h2.tools.Csv;
import org.h2.util.JdbcUtils;

import picocli.CommandLine;
import picocli.CommandLine.Command;
import picocli.CommandLine.Option;

/**
 * Converts a table in a H2 database file to CSV
 * 
 * @author John Ahlroos / Devsoap 2021
 */
@Command(name = "h2tocsv", mixinStandardHelpOptions = true, version = "0.1", description = "H2 database to CSV converter")
class h2tocsv implements Callable<Integer> {

    @Option(names = { "--database", "-d" }, required = true, description = "H2 database to import")
    private File database;

    @Option(names = { "--table", "-t" }, required = true, description = "The database table to export")
    private String table;

    @Option(names = { "--user", "-u" }, defaultValue = "sa", description = "Database user name")
    private String user;

    @Option(names = { "--password", "-p" }, defaultValue = "", description = "Database password")
    private String password;

    @Option(names = { "--file", "-f" }, description = "CSV file path to output")
    private String csvFile;

    public static void main(final String... args) {
        Driver.load();
        final int exitCode = new CommandLine(new h2tocsv()).execute(args);
        System.exit(exitCode);
    }

    @Override
    public Integer call() throws Exception {
        csvFile = ofNullable(csvFile).orElse(
                String.format("%s-%s.csv", database.getName().substring(0, database.getName().indexOf(".")), table));

        if (!database.exists()) {
            System.err.println("Database file " + database.getCanonicalPath() + " does not exist!");
            return -1;
        }

        final String path = database.getCanonicalPath().replace(".mv.db", "");
        final String url = "jdbc:h2:file:" + path + ";DB_CLOSE_DELAY=-1";
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, user, password);
            try (Statement statement = conn.createStatement()) {
                final String sql = String.format("select * from %s", table);

                System.out.println(String.format("Exporting table %s...", table));
                final ResultSet result = statement.executeQuery(sql);

                System.out.println("Writing database table to file...");
                new Csv().write(csvFile, result, StandardCharsets.UTF_8.toString());
            }
        } catch (SQLException e) {
            System.err.println("Failed to read database, error message was: " + e.getMessage());
            return e.getErrorCode();
        } finally {
            ofNullable(conn).ifPresent(JdbcUtils::closeSilently);
        }

        System.out.println("Done. CSV file exported to " + csvFile);
        return 0;
    }
}