namespace ChadSoft.Testing
{
    using System;
    using System.Collections.Generic;
    using System.Data.SqlClient;
    using System.IO;
    using System.Text;
    using Microsoft.VisualStudio.TestTools.UnitTesting;
    using Moq;

    public class TestDatabase : IDisposable
    {
        private readonly string connectionString;
        protected internal readonly string databaseFilename;

        public string ConnectionString { get { return connectionString; } }
        public string Schema { get; set; }
        public string TestDataScript { get; set; }

        /// <summary>
        /// Initializes a new instance of the <see cref="TestDatabase"/> class.
        /// </summary>
        /// <param name="databaseFilename">
        /// The filename of the database to use for testing
        /// </param>
        /// <param name="schema">The schema.</param>
        /// <param name="testData">The test data.</param>
        public TestDatabase(string databaseFilename, string schema, string testData)
        {
            if (string.IsNullOrEmpty(databaseFilename))
                throw new ArgumentNullException("databaseFilename");

            this.databaseFilename = databaseFilename;
            connectionString = string.Format(
                @"Server=.\SQLEXPRESS; Integrated Security=true;AttachDbFileName={0};",
                Path.GetFullPath(databaseFilename));
            Schema = schema;
            TestDataScript = testData;
        }

        public void Dispose()
        {
            DeleteDatabaseFiles();
        }

        /// <summary>
        /// Initializes this instance.  This will
        /// prepare the database from tests.
        /// </summary>
        public virtual void Initialize(bool overwriteExisting)
        {
            if (overwriteExisting && File.Exists(databaseFilename))
                DeleteDatabaseFiles();

            EnsureDatabaseCreated();
        }

        /// <summary>
        /// Helper method to execute a query with no
        /// return parameters 
        /// (or at least none that you need to access).
        /// </summary>
        /// <remarks>
        /// Jess: This was a private helper function that
        /// I decided to make public to make tests easier.
        /// This isn't supposed to be some big DAL.
        /// </remarks>
        /// <param name="query">
        /// A query script of any size.<br/>
        /// Note: you can use the GO keyword to delimit 
        /// multiple queries in the same string.  These
        /// will be executed in individual requests.
        /// </param>
        public virtual void ExecuteQuery(string query)
        {
            using (var conn = new SqlConnection(connectionString))
            {
                conn.Open();

                // Break out the SQL statements
                // terminated by the "GO" statement.
                // (Credit to Jon Wells: http://snurl.com/5nam1)
                var sqlStatements = query.Split(
                    new[] { "GO", "GO\t", "GO\r\n" },
                    StringSplitOptions.RemoveEmptyEntries);

                // Then execute each of them individually
                // (using the same connection w/o closing it)
                foreach (var sql in sqlStatements)
                    new SqlCommand(sql, conn).ExecuteNonQuery();
            }
        }

        /// <summary>
        /// Recreate the test data by executing the script
        /// in the <see cref="TestDataScript" /> property.
        /// </summary>
        public void Recreate()
        {
            EnsureDatabaseCreated();

            if (!string.IsNullOrEmpty(TestDataScript))
                ExecuteQuery(TestDataScript);
        }

        // Create a new file-based SQLEXPRESS database
        // (Credit to Louis DeJardin - thanks! http://snurl.com/5nbrc)
        protected internal virtual void CreateDatabase()
        {
            var databaseName = Path.GetFileNameWithoutExtension(databaseFilename);

            using (var connection = new SqlConnection(
                "Data Source=.\\sqlexpress;Initial Catalog=tempdb;" +
                "Integrated Security=true;User Instance=True;"))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    command.CommandText =
                        "CREATE DATABASE " + databaseName +
                        " ON PRIMARY (NAME=" + databaseName +
                        ", FILENAME='" + databaseFilename + "')";
                    command.ExecuteNonQuery();

                    command.CommandText =
                        "EXEC sp_detach_db '" + databaseName + "', 'true'";
                    command.ExecuteNonQuery();
                }
            }

            // After we've created the database, initialize it with any
            // schema and test data scripts we've been given
            InitializeDatabase();
        }

        protected virtual void DeleteDatabaseFiles()
        {
            // Allow time for connections to be released
            System.Threading.Thread.Sleep(1000);

            if (File.Exists(databaseFilename))
                File.Delete(databaseFilename);

            var logFilename = databaseFilename.Replace(".mdf", "_log.ldf");
            if (File.Exists(logFilename))
                File.Delete(logFilename);
        }

        protected internal virtual void EnsureDatabaseCreated()
        {
            if (File.Exists(databaseFilename)) return;
            CreateDatabase();
        }

        private void InitializeDatabase()
        {
            if (!string.IsNullOrEmpty(Schema))
                ExecuteQuery(Schema);
            if (!string.IsNullOrEmpty(TestDataScript))
                ExecuteQuery(TestDataScript);
        }

        /// <summary>
        /// Builds one monolithic SQL query from a collection of files.
        /// </summary>
        /// <param name="filePaths">The file paths containing SQL queries</param>
        /// <returns>An aggregated SQL query</returns>
        public static string BuildSqlFromFiles(IEnumerable<string> filePaths)
        {
            var sql = new StringBuilder();
            foreach (var path in filePaths)
            {
                sql.AppendLine(File.ReadAllText(path));
                sql.AppendLine("GO");
            }
            return sql.ToString();
        }
    }

    [TestClass]
    public class TestDatabaseTests
    {
        readonly string databaseFilename = Path.GetFullPath("Test.mdf");

        [TestMethod]
        public void Initialize_creates_database_file_if_none_exists()
        {
            if (File.Exists(databaseFilename)) File.Delete(databaseFilename);

            using (var db = new TestDatabase(databaseFilename, null, null))
            {
                db.Initialize(false);
                Assert.IsTrue(File.Exists(databaseFilename));
            }
        }

        [TestMethod]
        public void Initialize_overwrite_database_file_if_it_exists()
        {
            using (var db = new TestDatabase(databaseFilename, null, null))
            {
                Assert.IsFalse(File.Exists(databaseFilename));
                File.Create(databaseFilename).Close();

                db.Initialize(true);
                Assert.IsTrue(File.Exists(databaseFilename));
            }
        }

        [TestMethod]
        public void Initialize_executes_schema_if_provided()
        {
            var schema = "PRINT 'Excellent!'";
            var mockDb = new Mock<TestDatabase>(databaseFilename, schema, null);
            mockDb.Expect(db => db.ExecuteQuery("PRINT 'Excellent!'")).Verifiable();
            using (var db = mockDb.Object)
            {
                db.Initialize(true);
            }
            mockDb.VerifyAll();
        }

        [TestMethod]
        public void Initialize_does_not_execute_schema_if_not_provided()
        {
            var mockDb = new Mock<TestDatabase>(databaseFilename, null, null);
            mockDb.Expect(db => db.ExecuteQuery(It.IsAny<string>()))
                .Throws(new Exception("This shouldn't be called!"));
            using (var db = mockDb.Object)
            {
                db.Initialize(true);
            }
        }
    }
}