using DbUp;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using DbUp.Engine;
using System.Data.SqlClient;
using System.IO;

namespace Chronos.Database.Console
{
    class Program
    {
        static string BackupDatabaseToFile(string connectionString)
        {
            System.Console.ForegroundColor = ConsoleColor.White;
            System.Console.WriteLine("Creating database backup..");
            using (var connection = new SqlConnection(connectionString))
            {
                //connection.Database
                connection.Open();

                var backupPathSql = "SELECT SERVERPROPERTY('instancedefaultdatapath') AS [DefaultFile]";
                var backupPath = "";

                using(var command = new SqlCommand(backupPathSql, connection))
                {
                    backupPath = (string)command.ExecuteScalar();
                }

                var fileName = String.Concat(connection.Database, "_", DateTime.Now.ToString("yyyyMMddHHmmss"), ".bak");
                var backupFile = String.Join("\\", backupPath.TrimEnd('\\'), fileName);

                System.Console.WriteLine($"Backup file: {backupFile}");

                var escapedBackupFile = backupFile.Replace("'", "''");
                var backupSql = $"BACKUP DATABASE {connection.Database} TO DISK = '{escapedBackupFile}'";

                using (var command = new SqlCommand(backupSql, connection))
                {
                    command.ExecuteNonQuery();
                }

                System.Console.ForegroundColor = ConsoleColor.Green;
                System.Console.WriteLine("Create backup: Success!");
                System.Console.ResetColor();

                System.Console.WriteLine($"Removing existing backup files older than 7 days..");

                // Remove old database backups
                foreach (var file in Directory.GetFiles(backupPath, $"{connection.Database}_*.bak"))
                {
                    // Prevent deletion of the above created backup
                    if (!String.Equals(Path.GetFileName(file), fileName, StringComparison.OrdinalIgnoreCase))
                    { 
                        var fileAge = DateTime.Now - File.GetCreationTime(file);
                        var fileAgeInDays = Math.Round(fileAge.TotalDays, 5);
                        System.Console.WriteLine($"Existing backup: {file}; Age: {fileAgeInDays} days");
                        if (fileAge.TotalDays > 7) {
                            System.Console.WriteLine($"Deleting backup file: {file}");
                            File.Delete(file);
                        }
                    }
                }

                return backupFile;
            }
        }

        static void RestoreDatabaseFromFile(string connectionString, string backupFile)
        {
            System.Console.ForegroundColor = ConsoleColor.White;
            System.Console.WriteLine($"Restoring database backup from file: {backupFile}");
            
            using (var connection = new SqlConnection(connectionString))
            {
                string restoreDatabaseName = connection.Database;
                connection.Open();
                // Switch to the master database to prevent 'Database is in use'
                connection.ChangeDatabase("master");


                var singleUserModeSql = $"ALTER DATABASE {restoreDatabaseName} SET SINGLE_USER WITH ROLLBACK IMMEDIATE";
                using (var command = new SqlCommand(singleUserModeSql, connection))
                {
                    command.ExecuteNonQuery();
                }

                var escapedBackupFile = backupFile.Replace("'", "''");
                var backupSql = $"RESTORE DATABASE {restoreDatabaseName} FROM DISK = '{escapedBackupFile}' WITH REPLACE";

                using (var command = new SqlCommand(backupSql, connection))
                {
                    command.ExecuteNonQuery();
                }

                var multiUserModeSql = $"ALTER DATABASE {restoreDatabaseName} SET MULTI_USER";
                using (var command = new SqlCommand(multiUserModeSql, connection))
                {
                    command.ExecuteNonQuery();
                }

                System.Console.ForegroundColor = ConsoleColor.Green;
                System.Console.WriteLine("Restore backup: Success!");
                System.Console.ResetColor();
            }
        }

        static int Main(string[] args)
        {
            var configConnectionString = ConfigurationManager.ConnectionStrings["Chronos"].ConnectionString;
            var connectionString = args.FirstOrDefault() ?? configConnectionString;

            var databaseBackupFile = "";

            try
            {
                databaseBackupFile = BackupDatabaseToFile(connectionString);
            }
            catch (Exception ex)
            {
                System.Console.ForegroundColor = ConsoleColor.Red;
                System.Console.WriteLine(ex.ToString());
                System.Console.ResetColor();
#if DEBUG
                System.Console.ReadLine();
#endif
                return -1;
            }

            var upgrader =
                DeployChanges.To
                    .SqlDatabase(connectionString)
                    .WithScriptsEmbeddedInAssembly(Assembly.GetExecutingAssembly())
                    .LogToConsole()
                    .Build();                        

            var result = upgrader.PerformUpgrade();

            if (!result.Successful)
            {
                System.Console.ForegroundColor = ConsoleColor.Red;
                System.Console.WriteLine("Database upgrade finished with errors!");
                System.Console.WriteLine(result.Error);
                System.Console.ResetColor();

                // Try a database restore                
                try
                {
                    RestoreDatabaseFromFile(connectionString, databaseBackupFile);
                }
                catch (Exception ex)
                {
                    System.Console.ForegroundColor = ConsoleColor.Red;
                    System.Console.WriteLine(ex.ToString());
                    System.Console.ResetColor();
                }

                System.Console.ForegroundColor = ConsoleColor.Red;
                System.Console.WriteLine("Database upgrade failed!");                
#if DEBUG
                System.Console.ReadLine();
#endif
                return -1;
            }


            System.Console.ForegroundColor = ConsoleColor.Green;
            System.Console.WriteLine("Success!");
            System.Console.ResetColor();
#if DEBUG
            System.Console.ReadLine();
#endif
            return 0;
        }
    }
}