/*
 * $Id: 1.0+65 SQLiteTools.java 402d39c37049 2021-12-29 od $
 *
 * This file is part of the Cloud Services Integration Platform (CSIP),
 * a Model-as-a-Service framework, API, and application suite.
 *
 * 2012-2024, OMSLab, Colorado State University.
 *
 * OMSLab licenses this file to you under the MIT license.
 * See the LICENSE file in the project root for more information.
 */
package crlmod.utils;

import csip.api.server.Executable;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;

/**
 *
 * @author LYaege
 */
@Deprecated
public class SQLiteTools {

    /**
     *
     * @param sqlite3 - name of the sqlite3 db file
     * @param sqlite2 - name of the sqlite db file
     * @return - error
     * @throws IOException
     * @throws InterruptedException
     *
     * writes the output of the sqlite3 dump to a file sqlite pulls in the sql
     * file to recreate the file in sqlite
     */
    private static String recreateDatabase(File sqlite3, File sqlite2, int versionOut) throws IOException, InterruptedException {
        if (versionOut == 2) {
            // dump sqlite3 to file
            Runtime rt = Runtime.getRuntime();
            Process p = rt.exec("sqlite3 " + sqlite3.getAbsolutePath() + " .dump");
            InputStream stderr = p.getErrorStream();
            InputStreamReader isr = new InputStreamReader(stderr);
            BufferedReader br = new BufferedReader(isr);
            InputStream stout = p.getInputStream();
            InputStreamReader stoutr = new InputStreamReader(stout);
            BufferedReader br2 = new BufferedReader(stoutr);
            String line = null;
            String sql = "";

            while ((line = br2.readLine()) != null) {
                System.out.println(line);
                sql += line;
            }

            File sql_file = new File(sqlite3.getParent() + "/temp.sql");
            FileWriter fw = new FileWriter(sql_file);
            fw.write(sql);
            fw.flush();
            fw.close();

            // read in file and recreate db
            Process pr = rt.exec("sqlite -init " + sql_file.getAbsolutePath() + " " + sqlite2.getAbsolutePath());
            PrintWriter pw = new PrintWriter(new OutputStreamWriter(pr.getOutputStream()));
            pw.println(".exit");
            pw.flush();
            System.out.println("exit: " + pr.waitFor());
            pw.close();

            //Cleanup
            stderr.close();
            isr.close();
            br.close();
            stoutr.close();
            br2.close();
            p.destroy();
            pr.destroy();
        }
        if (versionOut == 3) {
            // dump sqlite3 to file
            Runtime rt = Runtime.getRuntime();
            Process p = rt.exec("sqlite " + sqlite2.getAbsolutePath() + " .dump");
            InputStream stderr = p.getErrorStream();
            InputStreamReader isr = new InputStreamReader(stderr);
            BufferedReader br = new BufferedReader(isr);
            InputStream stout = p.getInputStream();
            InputStreamReader stoutr = new InputStreamReader(stout);
            BufferedReader br2 = new BufferedReader(stoutr);
            String line = null;
            String sql = "";

            while ((line = br2.readLine()) != null) {
                System.out.println(line);
                sql += line;
            }

            File sql_file = new File(sqlite2.getParent() + "/temp.sql");
            FileWriter fw = new FileWriter(sql_file);
            fw.write(sql);
            fw.flush();
            fw.close();

            // read in file and recreate db
            Process pr = rt.exec("sqlite3 -init " + sql_file.getAbsolutePath() + " " + sqlite3.getAbsolutePath());
            PrintWriter pw = new PrintWriter(new OutputStreamWriter(pr.getOutputStream()));
            pw.println(".exit");
            pw.flush();
            System.out.println("exit: " + pr.waitFor());
            pw.close();

            //Cleanup
            stderr.close();
            isr.close();
            br.close();
            stoutr.close();
            br2.close();
            p.destroy();
            pr.destroy();
        }
        return null;
    }


    public static int executeCommand(String command) throws IOException, InterruptedException {
        Runtime rt = Runtime.getRuntime();
        Process p = rt.exec(command);
        InputStream stderr = p.getErrorStream();
        InputStreamReader isr = new InputStreamReader(stderr);
        BufferedReader br = new BufferedReader(isr);
        InputStream stout = p.getInputStream();
        InputStreamReader stoutr = new InputStreamReader(stout);
        BufferedReader br2 = new BufferedReader(stoutr);
        String line = null;
        while ((line = br2.readLine()) != null) {
            System.out.println(line);
        }
        while ((line = br.readLine()) != null) {
            System.out.println(line);
        }
        int exitVal = p.waitFor();

        //Cleanup
        stderr.close();
        isr.close();
        br.close();
        p.destroy();
        return exitVal;
    }


    /**
     *
     * @param sqlite3 File that points to the input sqlite 3 file. Should
     * already contain data.
     * @param sqlite2 File that points to the sqlite 2 file. Should be empty
     * @param ex_cmd
     * @param sqlite_exe
     * @param sqlite3_exe
     * @throws IOException
     * @throws InterruptedException Converts from sqlite 3 database to sqlite 2
     * database.
     */
    public static String sqlite3To2(File sqlite3, File sqlite2, Executable ex_cmd, File sqlite_exe, File sqlite3_exe) throws IOException, InterruptedException {
        if (System.getProperty("os.name").contains("Win")) {
            ex_cmd.setArguments("/c", "start", "/b", sqlite3_exe.getAbsolutePath(), sqlite3.getAbsolutePath(), ".dump", ">", sqlite3.getAbsolutePath() + ".sql");
            System.out.println("sqlite3 dump process exit code: " + ex_cmd.exec());

            ex_cmd.redirectDefaults();

            ex_cmd.setArguments("cmd", "/c", "start", "/b", sqlite_exe.getAbsolutePath(), sqlite2.getAbsolutePath(), "<", sqlite3.getAbsolutePath() + ".sql");
            System.out.println("sqlite2 creation process exit code: " + ex_cmd.exec());
        } else {
            String error = recreateDatabase(sqlite3, sqlite2, 2);
            if (error != null) {
                return error;
            }
        }
        return null;
    }


    public static void sqlite2To3(File sqlite2, File sqlite3, Executable cmd, File exe_sqlite2, File exe_sqlite3) throws IOException, InterruptedException {
        if (System.getProperty("os.name").contains("Win")) {
//            //sqlite OLD.DB .dump | sqlite3 NEW.DB
            //String command1 = "cmd /c start /b sqlite " + sqlite2.getAbsolutePath() + " .dump | sqlite3 " + sqlite3.getAbsolutePath();
            cmd.setArguments("/c", "start", "/b", exe_sqlite2.getAbsolutePath(), sqlite2.getAbsolutePath(), ".dump", "|", exe_sqlite3.getAbsolutePath(), sqlite3.getAbsolutePath());
            int result = cmd.exec();

            System.out.println("SQLite Conversion 2 -> 3 result: " + result);
        } else {
            //recreateDatabase(sqlite3, sqlite2, 3);
        }
    }
}