using System;
using RedGate.SQL.Shared;
using RedGate.SQLCompare.Engine;

namespace SyncNAntTask
{
    public static class SynchronizeScripts
    {
        public static void Synchronize(string scriptFolder, ConnectionProperties targetConnectionProperties)
        {
            using (Database sourceDatabaseScripts = new Database(), targetDatabase = new Database())
            {
                // Establish the schema from the scripts stored in the sourceDatabaseScripts scripts folder
                // Passing in null for the database information parameter causes SQL Compare to read the
                // XML file supplied in the folder.
                sourceDatabaseScripts.Register(scriptFolder, null, Options.Default);

                // Read the schema for the targetDatabase database
                targetDatabase.Register(targetConnectionProperties, Options.Default);

                // Compare the database against the scripts.
                // Comparing in this order makes the targetDatabase the second database
                Differences sourceDatabaseScriptsVStargetDatabase =
                    sourceDatabaseScripts.CompareWith(targetDatabase, Options.Default);

                // Select all of the differences for synchronization
                foreach (Difference difference in sourceDatabaseScriptsVStargetDatabase)
                {
                    difference.Selected = IsIncludeObject(difference);
                }

                // Calculate the work to do using sensible default options
                // The targetDatabase is to be updated, so the runOnTwo parameter is true
                var work = new Work();
                work.BuildFromDifferences(sourceDatabaseScriptsVStargetDatabase, Options.Default, true);

                // We can now access the messages and warnings
                Console.WriteLine("Messages:");

                foreach (Message message in work.Messages)
                {
                    Console.WriteLine(message.Text);
                }

                Console.WriteLine("Warnings:");

                foreach (Message message in work.Warnings)
                {
                    Console.WriteLine(message.Text);
                }

                // Disposing the execution block when it's not needed any more is important to ensure
                // that all the temporary files are cleaned up
                using (ExecutionBlock block = work.ExecutionBlock)
                {
                    // Display the SQL used to synchronize
                    Console.WriteLine("SQL to synchronize:");
                    Console.WriteLine(block.GetString());

                    // Finally, use a BlockExecutor to run the SQL against the WidgetProduction database
                    var executor = new BlockExecutor();
                    executor.ExecuteBlock(block, targetConnectionProperties.ServerName,
                                          targetConnectionProperties.DatabaseName, false,
                                          targetConnectionProperties.UserName, targetConnectionProperties.Password);
                }
            }
        }

        private static bool IsIncludeObject(Difference difference)
        {
            if (difference.DatabaseObjectType != ObjectType.User &&
                difference.DatabaseObjectType != ObjectType.Role &&
                difference.DatabaseObjectType != ObjectType.Queue &&
                difference.DatabaseObjectType != ObjectType.Service &&
                MeetExcludeSpecialCase(difference) == false)
            {
                return true;
            }

            return false;
        }

        private static bool MeetExcludeSpecialCase(Difference difference)
        {
            if ((difference.DatabaseObjectType == ObjectType.Table &&
                 difference.Name.ToLower().StartsWith("[dbo].[aspnet_sql")) ||
                (difference.DatabaseObjectType == ObjectType.StoredProcedure &&
                 difference.Name.ToLower().StartsWith("[dbo].[aspnet_sql")) ||
                (difference.DatabaseObjectType == ObjectType.StoredProcedure &&
                 difference.Name.ToLower().StartsWith("[dbo].[sqlquery")))
            {
                return true;
            }

            return false;
        }
    }
}