import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.concurrent.TimeUnit;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import net.sf.l2j.commons.concurrent.ThreadPool;

import net.sf.l2j.L2DatabaseFactory;

public class BackupDBManager
{
    private static final String database_name = "";
    private static final boolean DEBUG_SYSTEM = true;
    private static final long initializeAfterTime = TimeUnit.MINUTES.toMillis(1);
    private static final long checkEveryTime = TimeUnit.HOURS.toMillis(1);
    private static final DateFormat dateFormat = new SimpleDateFormat("dd-MM-yyyy HH-mm-ss");

    protected BackupDBManager()
    {
        ThreadPool.scheduleAtFixedRate(() -> BackupDBToSql(), initializeAfterTime, checkEveryTime);
        System.out.println("Database Backup Manager: Loaded. First load in " + TimeUnit.MILLISECONDS.toMinutes(checkEveryTime) + " Minutes. Delay every " + TimeUnit.MILLISECONDS.toHours(checkEveryTime) + " Hours.");
    }

    public void BackupDBToSql()
    {
        String pathOfMysql = "\"";
        try (Connection con = L2DatabaseFactory.getInstance().getConnection();
            PreparedStatement st = con.prepareStatement("SELECT@@basedir"))
        {
            final ResultSet rs = st.executeQuery();
            while (rs.next())
            {
                pathOfMysql += rs.getString(1);
            }
            rs.close();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        if (pathOfMysql.isEmpty())
        {
            System.out.println("Error on backup database. Empty path of mysql.");
            return;
        }
        // Give the specific path (pathOfMysql out = C:\Program Files\MySQL\MySQL Server 5.7\)
        pathOfMysql += "bin\\mysqldump" + "\"";
        if (DEBUG_SYSTEM)
            System.out.println("Path of mysql: " + pathOfMysql);
        try
        {
            final URL applicationRootPathURL = getClass().getProtectionDomain().getCodeSource().getLocation();
            final File applicationRootPath = new File(applicationRootPathURL.getPath());
            final File myFile = new File(applicationRootPath.getParent());
            final File lastMyFile = new File(myFile.getParent());
            final String dbUser = "your-user";
            final String dbPass = "your-pass";
            final String commandOfMysqlDump = " " + database_name + " --single-transaction -u" + dbUser + " -p" + dbPass + " --skip-create-options --skip-comments --disable-keys > ";
            final String folderPath = "backup";
            final File f1 = new File(folderPath);
            f1.mkdir();
            final String pathUntilDirectory = (lastMyFile.getAbsolutePath() + "\\backup\\").replaceAll("%20", " ");
            final String savePath = ("\"" + pathUntilDirectory + "backup.sql\"").replaceAll("%20", " ");
            final String commandToExecute = "cmd /c " + pathOfMysql + commandOfMysqlDump + savePath;
            if (DEBUG_SYSTEM)
            {
                System.out.println("Save path of sql file: " + savePath);
                System.out.println("Command To Execute: " + commandToExecute);
            }
            // Execute
            final Process runtimeProcess = Runtime.getRuntime().exec(new String[]
            {
                "cmd",
                "/c",
                commandToExecute
            });
            if (DEBUG_SYSTEM)
            {
                final BufferedReader stdInput = new BufferedReader(new InputStreamReader(runtimeProcess.getInputStream()));
                final BufferedReader stdError = new BufferedReader(new InputStreamReader(runtimeProcess.getErrorStream()));
                System.out.println("Here is the standard output of the command:\n");
                String s = null;
                while ((s = stdInput.readLine()) != null)
                {
                    System.out.println(s);
                }
                // read any errors from the attempted command
                System.out.println("Here is the standard error of the command (if any):\n");
                while ((s = stdError.readLine()) != null)
                {
                    System.out.println(s);
                }
            }
            final int processComplete = runtimeProcess.waitFor();
            /* NOTE: processComplete=0 if correctly executed, will contain other values if not */
            if (processComplete == 0)
            {
                System.out.println("Backup to SQL Complete");
                zipAFile(pathUntilDirectory);
                deleteAFile(savePath.replaceAll("\"", ""));
            }
            else
            {
                System.out.println("Backup to SQL Failure");
            }
        }
        catch (IOException | InterruptedException ex)
        {
            System.out.println("Error at Backuprestore" + ex.getMessage());
        }
    }

    private static void zipAFile(String pathToSave)
    {
        final byte[] buffer = new byte[1024];
        try
        {
            final Date date = new Date();
            final FileOutputStream fos = new FileOutputStream(pathToSave + "Backup_" + dateFormat.format(date) + ".zip");
            final ZipOutputStream zos = new ZipOutputStream(fos);
            final ZipEntry ze = new ZipEntry("backup.sql");
            zos.putNextEntry(ze);
            final FileInputStream in = new FileInputStream(pathToSave + "\\backup.sql");
            int len;
            while ((len = in.read(buffer)) > 0)
            {
                zos.write(buffer, 0, len);
            }
            in.close();
            zos.closeEntry();
            zos.close();
            System.out.println("Done the zip of backup.");
        }
        catch (IOException ex)
        {
            ex.printStackTrace();
        }
    }

    private static void deleteAFile(String path)
    {
        try
        {
            final File file = new File(path);
            System.out.println(file.delete() ? (file.getName() + " is deleted!") : ("Delete operation is failed."));
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
    }

    public static BackupDBManager getInstance()
    {
        return SingletonHolder._instance;
    }

    private static class SingletonHolder
    {
        protected static final BackupDBManager _instance = new BackupDBManager();
    }
}