using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Reflection;
using System.Diagnostics;
using System.IO;
using System.Xml;
using System.Security.AccessControl;
using System.Security.Principal;
using System.DirectoryServices;

[assembly: AssemblyFileVersion("1.0.0.1")]

namespace CreateSampleDB
{
    class CreateSampleDB
    {
        static void Main(string[] args)
        {
#if DEBUG
            if(args.Length > 0 && args[0] == "-debug")
                System.Diagnostics.Debugger.Break();
#endif
            string configFileName = Path.ChangeExtension(typeof(CreateSampleDB).Assembly.Location, "dbconfig");
            if (args.Length > 0 && args[args.Length-1][0] != '-')
                configFileName = args[args.Length-1];

            string connString = @"Data Source=.\SqlExpress;Integrated Security=True";
            using (var conn = new SqlConnection(connString))
            {
                conn.InfoMessage += (s, e) =>
                {
                    Console.WriteLine(e.Message);
                };
                try
                {
                    conn.Open();
                }
                catch (SqlException e)
                {
                    Console.WriteLine("\r\nCannot connect to the named instance \"SqlExpress\".\r\nMake sure that SQL Server Express is insialled and running.\r\n");
                    throw e;
                }

                XmlReaderSettings settings = new XmlReaderSettings { IgnoreWhitespace = true };
                using (var reader = XmlReader.Create(configFileName, settings))
                {
                    var config = new XmlDocument();
                    config.Load(reader);
                    foreach (XmlNode db in config.FirstChild.ChildNodes)
                    {
                        string sqlScriptPath = Path.GetFullPath(SubstituteMacros(db.Attributes["SqlScript"].Value));
                        string dbPath = SubstituteMacros(Path.ChangeExtension(db.Attributes["DbPath"].Value, "").TrimEnd('.'));
                        if (!Path.IsPathRooted(dbPath))
                            dbPath = Path.GetFullPath(Path.Combine(Path.GetDirectoryName(configFileName), dbPath));
                        string dbName = db.Attributes["DbName"].Value;

                        // ManageSqlSecurity will give the Sql service FullControl in the target directory
                        string dbDirPath = Path.GetDirectoryName(dbPath);
                        if(EnsureDirectoryExistance(dbDirPath))
                        {
                            using (ManageSqlSecurity addTempSqlAccess = new ManageSqlSecurity(dbDirPath))
                            {
                                DetachDatabase(conn, dbName, true);

                                string dbPathMdf = Path.ChangeExtension(dbPath, "mdf");
                                string dbPathLdf = Path.ChangeExtension(dbPath + "_log", "ldf");
                                if (File.Exists(dbPathMdf))
                                    File.Delete(dbPathMdf);
                                if (File.Exists(dbPathLdf))
                                    File.Delete(dbPathLdf);

                                ExecuteSqlScript(conn, sqlScriptPath, "|DataFile|", dbPath);
                                DetachDatabase(conn, dbName, false);

                                ManageSqlSecurity.TakeOwnership(dbPathMdf);    // change owner to current user
                                ManageSqlSecurity.TakeOwnership(dbPathLdf);    // change owner to current user

                                FileSecurity fsNorm = ManageSqlSecurity.GetFileSecurity(configFileName);
                                ManageSqlSecurity.SetFileSecurity(dbPathMdf, fsNorm);
                                ManageSqlSecurity.SetFileSecurity(dbPathLdf, fsNorm);
                            }
                        }
                    }
                }
            }
        }

        static string SubstituteMacros(string path)
        {
            string[] names = Enum.GetNames(typeof(System.Environment.SpecialFolder));
            Array values = Enum.GetValues(typeof(System.Environment.SpecialFolder));
            for (int i = 0; i < names.Length; i++)
            {
                var specialName = "$(" + names[i] + ")";
                if (path.Contains(specialName))
                    path = path.Replace(specialName,  Environment.GetFolderPath((Environment.SpecialFolder)values.GetValue(i)));
            }
            return path;
        }

        static void DetachDatabase(SqlConnection connection, string dbName, bool drop)
        {
            using (SqlCommand cmd = connection.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = string.Format(
                    "USE [master]\r\n" +
                    "IF (db_id('{0}') IS NOT NULL)\r\n" +
                    "begin\r\n" +
                        (drop ? "DROP DATABASE {0}\r\n" :
                        "ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE\r\n" +
                        "EXEC sp_detach_db '{0}'\r\n") +
                    "end",
                    dbName);               

                cmd.ExecuteNonQuery();
            }
        }
            
        static void ExecuteSqlScript(SqlConnection connection, string filename, string paramName, string paramValue)
        {
            string sql = string.Empty;
            using (var reader = new StreamReader(filename))
            {
                sql = reader.ReadToEnd();
            }
            if (!string.IsNullOrEmpty(paramName))
                sql = sql.Replace(paramName, paramValue);
            Regex regex = new Regex("^GO", RegexOptions.IgnoreCase | RegexOptions.Multiline);
            string[] lines = regex.Split(sql);
            using (SqlCommand cmd = connection.CreateCommand())
            {
                cmd.Connection = connection;
                cmd.CommandTimeout = 0;
                foreach (string line in lines)
                {
                    if (line.Length > 0)
                    {
                        cmd.CommandText = line;
                        cmd.CommandType = CommandType.Text;
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            Console.WriteLine();
        }

        static bool EnsureDirectoryExistance(string dirPath)
        {
            try
            {
                DirectoryInfo di = Directory.CreateDirectory(dirPath);
                if(di.Exists) return true;
            }
            catch {}
            return false;
        }
    }

    public class ManageSqlSecurity : IDisposable
    {
        string _dirPath = null;
        DirectorySecurity _dirPathSecurity = null;

        public ManageSqlSecurity(string dirPath)
        {
            _dirPath = null;
            try
            {
                string sqlUserGroup = GetSqlUserGroup();
                if (sqlUserGroup != null)
                {
                    _dirPathSecurity = GetDirectorySecurity(dirPath);
                    AddDirectorySecurity(dirPath, sqlUserGroup, FileSystemRights.FullControl, AccessControlType.Allow);
                    _dirPath = dirPath;
                }
            }
            catch
            {
                _dirPathSecurity = null;
                _dirPath = null;
            }
        }

        ~ManageSqlSecurity() { Dispose(); }

        public void Dispose()
        {
            if(_dirPath != null)
            {
                string dirPath = _dirPath;
                _dirPath = null;

                DirectorySecurity dirPathSecurity = _dirPathSecurity;
                _dirPathSecurity = null;

                try { SetDirectorySecurity(dirPath, dirPathSecurity); }
                catch {}

                GC.SuppressFinalize(this);
            }
        }

        // Adds an ACL entry on the specified directory for the specified account.
        public static void AddDirectorySecurity(string DirName, string Account, FileSystemRights Rights, AccessControlType ControlType)
        {
            // Create a new DirectoryInfo object.
            DirectoryInfo dInfo = new DirectoryInfo(DirName);

            // Get a DirectorySecurity object that represents the current security settings.
            DirectorySecurity dSecurity = dInfo.GetAccessControl();

            // Add the FileSystemAccessRule to the security settings. 
            dSecurity.AddAccessRule(new FileSystemAccessRule(Account, Rights, ControlType));

            // Set the new access settings.
            dInfo.SetAccessControl(dSecurity);
        }

        // Removes an ACL entry on the specified directory for the specified account.
        public static void RemoveDirectorySecurity(string DirName, string Account, FileSystemRights Rights, AccessControlType ControlType)
        {
            // Create a new DirectoryInfo object.
            DirectoryInfo dInfo = new DirectoryInfo(DirName);

            // Get a DirectorySecurity object that represents the current security settings.
            DirectorySecurity dSecurity = dInfo.GetAccessControl();

            // Add the FileSystemAccessRule to the security settings. 
            dSecurity.RemoveAccessRule(new FileSystemAccessRule(Account, Rights, ControlType));

            // Set the new access settings.
            dInfo.SetAccessControl(dSecurity);
        }

        // Gets the ACL entry on the specified directory
        public static DirectorySecurity GetDirectorySecurity(string DirName)
        {
            // Create a new DirectoryInfo object.
            DirectoryInfo dInfo = new DirectoryInfo(DirName);

            // Get a DirectorySecurity object that represents the current security settings.
            return dInfo.GetAccessControl();
        }

        // Gets the ACL entry on the specified file
        public static FileSecurity GetFileSecurity(string FileName)
        {
            // Create a new FileInfo object
            FileInfo fInfo = new FileInfo(FileName);

            // Get a FileSecurity object that represents the current security settings.
            return fInfo.GetAccessControl();
        }

        // Sets the ACL entry on the specified directory to the values in the target DirectorySecurity
        public static void SetDirectorySecurity(string DirName, DirectorySecurity targetSecurity)
        {
            // Create a new DirectoryInfo object.
            DirectoryInfo dInfo = new DirectoryInfo(DirName);

            // Get a DirectorySecurity object that represents the current security settings.
            DirectorySecurity dSecurity = dInfo.GetAccessControl();

            // Add the FileSystemAccessRule to the security settings. 
            dSecurity.SetSecurityDescriptorBinaryForm(targetSecurity.GetSecurityDescriptorBinaryForm());

            // Set the new access settings.
            dInfo.SetAccessControl(dSecurity);
        }

        // Sets the ACL entry on the specified file to the values in the target FileSecurity
        public static void SetFileSecurity(string FileName, FileSecurity targetSecurity)
        {
            // Create a new FileInfo object
            FileInfo fInfo = new FileInfo(FileName);

            // Get a FileSecurity object that represent appropriate security settings.
            FileSecurity fSecurity = fInfo.GetAccessControl();

            // Add the FileSystemAccessRule to the security settings.
            fSecurity.SetSecurityDescriptorBinaryForm(targetSecurity.GetSecurityDescriptorBinaryForm());

            // Set the new access settings
            fInfo.SetAccessControl(fSecurity);
        }

        // Returns a string with the SQLServer ActiveDirectory User Group on this machine.
        public static string GetSqlUserGroup()
        {
            string usergroup = null;
            DirectoryEntry localMachine = new DirectoryEntry("WinNT://" + Environment.MachineName);
            foreach (DirectoryEntry de in localMachine.Children)
            {
                string name = de.Name.ToLower();
                string classname = de.SchemaClassName.ToLower();
                if (classname == "group" && name.Contains("sqlserver") && name.Contains("adhelperuser"))
                {
                    usergroup = Environment.MachineName + "\\" + de.Name;
                }
            }
            return (usergroup == null) ? "Everyone" : usergroup;
        }

        // Takes ownership of the specified file by the current user
        public static void TakeOwnership(string FileName)
        {
            try
            {
                string username = Environment.UserDomainName + "\\" + Environment.UserName;
                FileInfo fi = new FileInfo(FileName);
                FileSecurity fs = fi.GetAccessControl();
                fs.SetOwner(new NTAccount(username));
                fi.SetAccessControl(fs);
            }
            catch { }
        }
    }
}
