path: root/Software/Visual_Studio/Azure/Tango.AzureUtils/Database/DatabaseManager.cs

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Linq;
using Microsoft.Azure.Management.AppService.Fluent;
using Microsoft.Azure.Management.Fluent;
using Microsoft.Azure.Management.Sql.Fluent;
using Microsoft.Azure.Management.Sql.Fluent.Models;
using Tango.BL;
using Tango.BL.Entities;
using Tango.BL.Enumerations;
using Tango.Core;
using Tango.Core.DB;
using Tango.Core.Helpers;

namespace Tango.AzureUtils.Database
{
    public class DatabaseManager : AzureUtilsComponentBase
    {
        #region Constructors

        /// <summary>
        /// Initializes a new instance of the <see cref="DatabaseManager"/> class.
        /// </summary>
        /// <param name="azure">The azure.</param>
        public DatabaseManager(IAzure azure) : base(azure)
        {

        }

        #endregion

        #region SQL Examiner

        /// <summary>
        /// Opens the SQL examiner schema using the specified source app and target app configuration.
        /// </summary>
        /// <param name="sourceApp">The source application.</param>
        /// <param name="targetApp">The target application.</param>
        /// <returns></returns>
        public async Task OpenSQLExaminerSchema(IWebAppBase sourceApp, IWebAppBase targetApp)
        {
            String projectFile = Path.Combine(AssemblyHelper.GetCurrentAssemblyFolder(), "Database", "GENERAL_ENV_UPGRADE.seproj");

            using (Stream stream = GetProjectFileStream(projectFile))
            {
                XElement projectXml = XElement.Load(stream);
                var sourceSettings = await sourceApp.GetMachineServiceSettingsAsync();
                var targetSettings = await targetApp.GetMachineServiceSettingsAsync();
                ApplyDatabaseSettingsToProjectXml(projectXml, sourceSettings, targetSettings);

                var tempFile = TemporaryManager.CreateImaginaryFile(".seproj");
                tempFile.Persist = true;

                File.WriteAllText(tempFile, projectXml.ToString());

                Process.Start(tempFile);
            }
        }

        /// <summary>
        /// Opens the SQL examiner data using the specified source app and target app configuration.
        /// </summary>
        /// <param name="sourceApp">The source application.</param>
        /// <param name="targetApp">The target application.</param>
        /// <param name="forCreation">if set to <c>true</c> [for creation].</param>
        /// <returns></returns>
        public async Task OpenSQLExaminerData(IWebAppBase sourceApp, IWebAppBase targetApp, bool forCreation = false)
        {
            String projectFile = Path.Combine(AssemblyHelper.GetCurrentAssemblyFolder(), "Database", "GENERAL_ENV_UPGRADE.sdeproj");

            if (forCreation)
            {
                projectFile = Path.Combine(AssemblyHelper.GetCurrentAssemblyFolder(), "Database", "GENERAL_ENV_CREATE.sdeproj");
            }

            using (Stream stream = GetProjectFileStream(projectFile))
            {
                XElement projectXml = XElement.Load(stream);
                var sourceSettings = await sourceApp.GetMachineServiceSettingsAsync();
                var targetSettings = await targetApp.GetMachineServiceSettingsAsync();
                ApplyDatabaseSettingsToProjectXml(projectXml, sourceSettings, targetSettings);

                var tempFile = TemporaryManager.CreateImaginaryFile(".sdeproj");
                tempFile.Persist = true;

                File.WriteAllText(tempFile, projectXml.ToString());

                Process.Start(tempFile);
            }
        }

        private Stream GetProjectFileStream(String projectFile)
        {
            byte[] projectBytes = File.ReadAllBytes(projectFile);
            MemoryStream stream = new MemoryStream(projectBytes);
            return stream;
        }

        private void ApplyDatabaseSettingsToProjectXml(XElement projectXml, MachineServiceSettings sourceSettings, MachineServiceSettings targetSettings)
        {
            var sourceElement = projectXml.Elements().SelectMany(x => x.Descendants()).SingleOrDefault(x => x.Name == "Source" && x.Attributes().SingleOrDefault(y => y.Name == "id").Value == "1");
            var targetElement = projectXml.Elements().SelectMany(x => x.Descendants()).SingleOrDefault(x => x.Name == "Source" && x.Attributes().SingleOrDefault(y => y.Name == "id").Value == "2");

            ApplyDatabaseSettingsToSourceElement(sourceElement, sourceSettings);
            ApplyDatabaseSettingsToSourceElement(targetElement, targetSettings);
        }

        private void ApplyDatabaseSettingsToSourceElement(XElement sourceElement, MachineServiceSettings settings)
        {
            sourceElement.Element("ServerName").SetValue(settings.DB_ADDRESS);
            sourceElement.Element("Database").SetValue(settings.DB_CATALOG);
            sourceElement.Element("Login").SetValue(settings.DB_USER_NAME);
            sourceElement.Element("Password").SetValue(settings.DB_PASSWORD);
        }

        #endregion

        #region Azure SQL Database

        public async Task<bool> IsDatabaseExists(String serverName, String databaseName)
        {
            OnProgress(AzureUtilsStage.Database, $"Checking {serverName} database server...");
            var sqlServer = (await Azure.SqlServers.ListAsync()).SingleOrDefault(x => x.Name == serverName);

            var database = (await sqlServer.Databases.ListAsync()).SingleOrDefault(x => x.Name == databaseName);

            return database != null;
        }

        public async Task<ISqlDatabase> AddDatabase(String serverName, String databaseName)
        {
            OnProgress(AzureUtilsStage.Database, $"Checking {serverName} database server...");
            var sqlServer = (await Azure.SqlServers.ListAsync()).SingleOrDefault(x => x.Name == serverName);

            OnProgress(AzureUtilsStage.Database, $"Creating new database '{databaseName}'...");
            var database = await sqlServer.Databases.Define(databaseName).WithEdition(DatabaseEdition.Standard).CreateAsync();

            return database;
        }

        public Task<ISqlDatabase> AddDatabase(String databaseName)
        {
            return AddDatabase("twine", databaseName);
        }

        public async Task RemoveDatabase(String serverName, String databaseName)
        {
            OnProgress(AzureUtilsStage.Database, $"Checking {serverName} database server...");
            var sqlServer = (await Azure.SqlServers.ListAsync()).SingleOrDefault(x => x.Name == serverName);

            OnProgress(AzureUtilsStage.Database, $"Removing database '{databaseName}'...");
            await sqlServer.Databases.DeleteAsync(databaseName);
        }

        #endregion

        #region Users & Permissions

        public async Task AddEnvironmentGroupPermissions(String address, String databaseName, String groupName, String email, String password)
        {
            OnProgress(AzureUtilsStage.Database, $"Adding environment group permissions on '{databaseName}'...");

            using (DbManager db = DbManager.FromDataSource(new DataSource()
            {
                Type = DataSourceType.Azure,
                Address = address,
                Catalog = databaseName,
                UserName = email,
                Password = password,
                IntegratedSecurity = false
            }))
            {
                await db.ExecuteCommandAsync($"CREATE USER [{groupName}] FROM  EXTERNAL PROVIDER");
                await db.ExecuteCommandAsync($"ALTER ROLE db_datareader ADD MEMBER [{groupName}];");
                await db.ExecuteCommandAsync($"ALTER ROLE db_datawriter ADD MEMBER [{groupName}];");
            }
        }

        public async Task AddBackupUser(String address, String databaseName, String userName, String password)
        {
            OnProgress(AzureUtilsStage.Database, $"Adding BackupUser permissions on '{databaseName}'...");

            using (DbManager db = DbManager.FromCredentials(address, databaseName, userName, password))
            {
                await db.ExecuteCommandAsync("CREATE USER [BackupUser] FOR LOGIN [BackupUser] WITH DEFAULT_SCHEMA=[dbo]");
                await db.ExecuteCommandAsync("EXEC sp_addrolemember N'db_owner', N'BackupUser'");
                await db.ExecuteCommandAsync("EXEC sp_addrolemember N'db_accessadmin', N'BackupUser'");
                await db.ExecuteCommandAsync("EXEC sp_addrolemember N'db_securityadmin', N'BackupUser'");
                await db.ExecuteCommandAsync("EXEC sp_addrolemember N'db_backupoperator', N'BackupUser'");
                await db.ExecuteCommandAsync("EXEC sp_addrolemember N'db_datareader', N'BackupUser'");
                await db.ExecuteCommandAsync("EXEC sp_addrolemember N'db_datawriter', N'BackupUser'");
            }
        }

        public async Task AddTangoWebAppUser(String address, String databaseName, String userName, String password)
        {
            OnProgress(AzureUtilsStage.Database, $"Adding Tango WebApp permissions on '{databaseName}'...");

            using (DbManager db = DbManager.FromCredentials(address, databaseName, userName, password))
            {
                await db.ExecuteCommandAsync("CREATE USER [Tango] FOR LOGIN [BackupUser] WITH DEFAULT_SCHEMA=[dbo]");
                await db.ExecuteCommandAsync("EXEC sp_addrolemember N'db_datareader', N'Tango'");
                await db.ExecuteCommandAsync("EXEC sp_addrolemember N'db_datawriter', N'Tango'");
            }
        }

        #endregion

        #region Versions

        public async Task UpgradeMachineStudioVersion(IWebAppBase sourceApp, IWebAppBase targetApp)
        {
            var latestMachineStudioVersion = await GetLatestMachineStudioVersion(sourceApp);
            var targetDataSource = (await targetApp.GetMachineServiceSettingsAsync()).ToDataSource();

            OnProgress(AzureUtilsStage.Database, $"Adding machine studio database entry for version '{latestMachineStudioVersion.Version}'...");

            using (var db = ObservablesContext.CreateDefault(targetDataSource))
            {
                db.MachineStudioVersions.Add(latestMachineStudioVersion);
                await db.SaveChangesAsync();
            }
        }

        public async Task UpgradePPCVersion(IWebAppBase sourceApp, IWebAppBase targetApp)
        {
            var latestPPCVersion = await GetLatestPPCVersion(sourceApp);

            var targetDataSource = (await targetApp.GetMachineServiceSettingsAsync()).ToDataSource();

            OnProgress(AzureUtilsStage.Database, $"Adding PPC database entry for version '{latestPPCVersion.Version}'...");

            using (var db = ObservablesContext.CreateDefault(targetDataSource))
            {
                db.TangoVersions.Add(latestPPCVersion);
                await db.SaveChangesAsync();
            }
        }

        public async Task UpgradeFSEVersion(IWebAppBase sourceApp, IWebAppBase targetApp, FSEBuildVariants build)
        {
            var latestFSEVersion = await GetLatestFSEVersion(sourceApp, build);

            var targetDataSource = (await targetApp.GetMachineServiceSettingsAsync()).ToDataSource();

            OnProgress(AzureUtilsStage.Database, $"Adding {build} database entry for version '{latestFSEVersion.Version}'...");

            using (var db = ObservablesContext.CreateDefault(targetDataSource))
            {
                db.FseVersions.Add(latestFSEVersion);
                await db.SaveChangesAsync();
            }
        }

        public async Task DowngradeMachineStudioVersion(IWebAppBase app)
        {
            var latestMachineStudioVersion = await GetLatestMachineStudioVersion(app);
            var dataSource = (await app.GetMachineServiceSettingsAsync()).ToDataSource();

            OnProgress(AzureUtilsStage.Database, $"Removing machine studio database entry for version '{latestMachineStudioVersion.Version}'...");

            using (var db = ObservablesContext.CreateDefault(dataSource))
            {
                var latest = await db.MachineStudioVersions.SingleOrDefaultAsync(x => x.Guid == latestMachineStudioVersion.Guid);
                db.MachineStudioVersions.Remove(latest);
                await db.SaveChangesAsync();
            }
        }

        public async Task DowngradePPCVersion(IWebAppBase app)
        {
            var latestPPCVersion = await GetLatestPPCVersion(app);
            var dataSource = (await app.GetMachineServiceSettingsAsync()).ToDataSource();

            OnProgress(AzureUtilsStage.Database, $"Removing PPC database entry for version '{latestPPCVersion.Version}'...");

            using (var db = ObservablesContext.CreateDefault(dataSource))
            {
                var latest = await db.TangoVersions.SingleOrDefaultAsync(x => x.Guid == latestPPCVersion.Guid);
                db.TangoVersions.Remove(latest);
                await db.SaveChangesAsync();
            }
        }

        public async Task<MachineStudioVersion> GetLatestMachineStudioVersion(IWebAppBase app)
        {
            OnProgress(AzureUtilsStage.Database, $"Getting latest machine studio version on '{app.Name}'...");

            MachineServiceSettings settings = null;

            try
            {
                settings = await app.GetMachineServiceSettingsAsync();
            }
            catch (Exception ex)
            {
                throw new ArgumentException("Could not fetch machine service settings. Please check that all settings are available.", ex);
            }

            try
            {
                DataSource dataSource = settings.ToDataSource();

                using (var db = ObservablesContext.CreateDefault(dataSource))
                {
                    var versions = await db.MachineStudioVersions.ToListAsync();
                    var latest_machine_version = versions.OrderByDescending(x => Version.Parse(x.Version)).FirstOrDefault();
                    return latest_machine_version;
                }
            }
            catch (Exception ex)
            {
                throw new InvalidDataException($"Could not retrieve '{app.Name}' latest Machine Studio version from database.", ex);
            }
        }

        public async Task<TangoVersion> GetLatestPPCVersion(IWebAppBase app)
        {
            OnProgress(AzureUtilsStage.Database, $"Getting latest PPC version on '{app.Name}'...");

            MachineServiceSettings settings = null;

            try
            {
                settings = await app.GetMachineServiceSettingsAsync();
            }
            catch (Exception ex)
            {
                throw new ArgumentException("Could not fetch machine service settings. Please check that all settings are available.", ex);
            }

            try
            {
                DataSource dataSource = settings.ToDataSource();

                using (var db = ObservablesContext.CreateDefault(dataSource))
                {
                    var versions = await db.TangoVersions.ToListAsync();
                    var latest_version = versions.OrderByDescending(x => Version.Parse(x.Version)).FirstOrDefault();
                    return latest_version;
                }
            }
            catch (Exception ex)
            {
                throw new InvalidDataException($"Could not retrieve '{app.Name}' latest PPC version from database.", ex);
            }
        }

        public async Task<FseVersion> GetLatestFSEVersion(IWebAppBase app, FSEBuildVariants build)
        {
            OnProgress(AzureUtilsStage.Database, $"Getting latest {build} version on '{app.Name}'...");

            MachineServiceSettings settings = null;

            try
            {
                settings = await app.GetMachineServiceSettingsAsync();
            }
            catch (Exception ex)
            {
                throw new ArgumentException("Could not fetch machine service settings. Please check that all settings are available.", ex);
            }

            try
            {
                DataSource dataSource = settings.ToDataSource();

                using (var db = ObservablesContext.CreateDefault(dataSource))
                {
                    var versions = await db.FseVersions.Where(x => x.BuildVariant == (int)build).ToListAsync();
                    var latest_version = versions.OrderByDescending(x => Version.Parse(x.Version)).FirstOrDefault();
                    return latest_version;
                }
            }
            catch (Exception ex)
            {
                throw new InvalidDataException($"Could not retrieve '{app.Name}' latest FSE version from database.", ex);
            }
        }

        public async Task ValidateMachineStudioDatabaseUpgrade(IWebAppBase sourceApp, IWebAppBase targetApp)
        {
            OnProgress(AzureUtilsStage.Validating, "Validating machine studio database upgrade...");

            var sourceSettings = await sourceApp.GetMachineServiceSettingsAsync();
            var targetSettings = await targetApp.GetMachineServiceSettingsAsync();

            var latestSourceMachineStudioVersion = await GetLatestMachineStudioVersion(sourceApp);

            //Check if there is any source machine studio version.
            if (latestSourceMachineStudioVersion == null)
            {
                throw new ValidationException("Could not locate a Machine Studio version entry on the source database.");
            }

            var latestTargetMachineStudioVersion = await GetLatestMachineStudioVersion(targetApp);

            //Check target latest machine studio version is older if there is any.
            if (latestTargetMachineStudioVersion != null && Version.Parse(latestSourceMachineStudioVersion.Version) <= Version.Parse(latestTargetMachineStudioVersion.Version))
            {
                throw new ValidationException($"Machine Studio source version is '{latestSourceMachineStudioVersion.Version}' while target version is '{latestTargetMachineStudioVersion.Version}'.");
            }
        }

        public async Task ValidatePPCDatabaseUpgrade(IWebAppBase sourceApp, IWebAppBase targetApp)
        {
            OnProgress(AzureUtilsStage.Validating, "Validating PPC database upgrade...");

            var sourceSettings = await sourceApp.GetMachineServiceSettingsAsync();
            var targetSettings = await targetApp.GetMachineServiceSettingsAsync();

            var latestSourcePPCVersion = await GetLatestPPCVersion(sourceApp);

            //Check if there is any source PPC version.
            if (latestSourcePPCVersion == null)
            {
                throw new ValidationException("Could not locate a PPC version entry on the source database.");
            }

            var latestTargetPPCVersion = await GetLatestPPCVersion(targetApp);

            //Check target latest PPC version is older if there is any.
            if (latestTargetPPCVersion != null && Version.Parse(latestSourcePPCVersion.Version) <= Version.Parse(latestTargetPPCVersion.Version))
            {
                throw new ValidationException($"PPC source version is '{latestSourcePPCVersion.Version}' while target version is '{latestTargetPPCVersion.Version}'.");
            }
        }

        public async Task ValidateFSEDatabaseUpgrade(IWebAppBase sourceApp, IWebAppBase targetApp, FSEBuildVariants build)
        {
            OnProgress(AzureUtilsStage.Validating, $"Validating {build} database upgrade...");

            var sourceSettings = await sourceApp.GetMachineServiceSettingsAsync();
            var targetSettings = await targetApp.GetMachineServiceSettingsAsync();

            var latestSourceFSEVersion = await GetLatestFSEVersion(sourceApp, build);

            //Check if there is any source FSE version.
            if (latestSourceFSEVersion == null)
            {
                throw new ValidationException($"Could not locate a {build} version entry on the source database.");
            }

            var latestTargetFSEVersion = await GetLatestFSEVersion(targetApp, build);

            //Check target latest FSE version is older if there is any.
            if (latestTargetFSEVersion != null && Version.Parse(latestSourceFSEVersion.Version) <= Version.Parse(latestTargetFSEVersion.Version))
            {
                throw new ValidationException($"{build} source version is '{latestSourceFSEVersion.Version}' while target version is '{latestTargetFSEVersion.Version}'.");
            }
        }

        #endregion
    }
}